数式が長いこと自体が問題ではありません。問題は、何を計算しているか説明できず、修正すると別の場所が壊れる状態です。
まず、計算の途中結果を見えるようにし、同じ判定を何度も繰り返していないか、元データの形を直せないかを確認します。
先に結論
セルの計算として自然なら関数を整理します。データの取込・整形を関数で無理に行っているならPower Query、複数の操作や出力を数式で代用しているならVBAなどへ役割を分けます。
この作業は自動化候補です
次の項目が多いほど、手作業をそのまま続けるより、自動化の効果が出やすくなります。
- IFやXLOOKUPが何段にも重なっている
- 同じ計算式を何列にもコピーしている
- IFERRORで原因不明のエラーを空欄にしている
- 別ファイル参照が多く、開くたび警告が出る
- 数式を直せる人が一人しかいない
作業を三つに分けて考える
- 1計算の目的を書く
各列が何を決めているか、言葉で説明します。
- 2途中結果を分ける
一つの長い式を補助列や名前付き式へ分けます。
- 3処理の役割を移す
取込はPower Query、操作はVBAなどに分担します。
向いている方法の考え方
最近のExcel関数で短くできる場合もありますが、バージョン差がある職場では、対応環境を先に確認します。数式を美しくすることより、入力、計算、出力の境目を明確にする方が重要です。
既存ファイルを直すときは、現在の正しい結果をテストデータとして残し、変更前後で一致するか比較します。見た目だけ合っていても、端数や空欄の扱いが変わることがあります。
相談前にそろえるもの
完成形を細かく設計する必要はありません。今の作業が再現できる材料をそろえる方が重要です。
- 現在使っているファイルのコピー
- 正しい結果になる入力例
- エラーになった入力例
- 対応させるExcelのバージョン
- 変更してはいけない帳票や外部参照
専門家へ頼んだ方がよい目安
- 数式の意味を誰も説明できない
- ファイルが重く、入力のたびに固まる
- 別ファイル参照が頻繁に切れる
- 修正後のテスト項目を作れない
よくある失敗
- 長い式を別の長い式へ置き換えるだけ
- エラーをすべて空欄で隠す
- 入力データの表記ゆれを数式だけで吸収する
- 元の正解データを残さず修正する
よくある疑問
VBAに作り直せば速くなりますか？
必ず速くなるわけではありません。数式の量、再計算、データ構造が原因なら、表の設計やPower Queryの方が効果的な場合があります。
補助列を増やすのは悪いことですか？
途中結果が分かり、保守しやすくなるなら有効です。非表示にして一つの巨大な式へ詰め込むより、確認しやすいことがあります。
古いExcelでも動くようにできますか？
可能な範囲はありますが、使える関数が変わります。利用者全員のバージョンを確認してから方法を決めます。