その式、本当に1セルで書く必要ありますか？
業務でよく見かけるのが、「とにかく1セルで完結させた数式」です。
IFもVLOOKUPもSUMIFSも、全部まとめて1つのセルに詰め込む。
一見すると、
- セルが少なくてスッキリ
- 高度なことをしている感じがする
- できる人っぽい
しかし数か月後、こうなります。
「この式、誰も触れない。」
作った本人でさえ、しばらく時間が経つと自分の式を忘れてしまいます。「あのとき、なぜこの数値をここに入れたんだっけ」と、過去の自分に問いかけたくなる瞬間は、決して珍しいことではありません。
実は、1セル完結は”読めないExcel”を生む最大の原因です。エラーが出ているわけではないのに、誰も触れなくなってしまう。これは、これまでこのシリーズで取り上げてきたIFのネストやSUMIFSの集計ズレとも、根っこでつながっている問題です。今回は、その根本原因である「1セル完結」について、じっくり整理していきます。
そもそも、なぜ1セルに詰め込んでしまうのでしょうか。多くの場合、最初から「1セルで完結させよう」と決めて式を書いているわけではありません。最初はシンプルな式だったものに、条件を1つ足し、また1つ足し、というように少しずつ育っていった結果、気づいたときには複雑な式になっている、というケースがほとんどです。作業列を増やすのが面倒に感じて、つい既存の式の中に条件を追加してしまう。この積み重ねが、後から読めない式を生み出します。
なぜ1セル完結は危険なのか
① 役割が混ざる
例えばこんな式。
=IF(A2="A",
VLOOKUP(B2,$F$2:$G$10,2,FALSE)*1.1,
VLOOKUP(B2,$F$2:$G$10,2,FALSE))この式には、条件判定・単価取得・計算処理、すべてが入っています。何をしているのか理解するには、頭の中で分解する必要があります。読む人に負担をかける設計です。
しかも、この式にはもう一つ落とし穴があります。同じVLOOKUPが2回登場している点です。IFの条件がTRUEのときもFALSEのときも、結局は同じ検索を2回行っているだけなので、参照範囲を修正する際は、2箇所とも直さなければなりません。片方だけ直して、もう片方を直し忘れる、というミスは、この手の式で本当によく起こります。
さらに言えば、このような式は動作も無駄になりがちです。条件によって結果が変わるのはIFの部分だけなのに、VLOOKUPという比較的重い処理を毎回2回実行しています。データ量が少ないうちは気になりませんが、行数が数千、数万と増えてくると、ファイル全体の計算速度にじわじわと影響してくることもあります。役割を分けて、VLOOKUPを1回だけ実行するように書き直すだけで、正確さだけでなく、動作の軽さも改善できることがあります。
② 修正箇所が分かりにくい
例えば「Aのときの倍率を1.2に変更して」と言われた場合。式のどこを触るのか、一瞬で分かりますか。さらに条件が増えたらどうなるか。ネストが増え、カッコが増え、スクロールが必要になります。修正のたびにリスクが高まります。
条件が1つだけならまだ耐えられますが、実務では「あとから例外を追加する」ということがほぼ必ず起こります。最初はシンプルだった式が、半年後には誰も全体を把握できない長さになっている、というのはよくある話です。
さらに厄介なのは、こうした式はコピー&ペーストで他のセルにも広がっていくことです。1つのセルで動作を確認できた式は、そのまま同じ列の他の行にコピーされます。修正が必要になったときには、その式が何行、何列にわたってコピーされているかを把握するところから始めなければなりません。1セル完結の式が増えれば増えるほど、修正の影響範囲を正確に把握することが難しくなっていきます。
③ 引き継ぎができない
実務で一番問題なのはこれです。自分しか触れないExcelになること。1セル完結の式は、書いた本人しか理解できないことが多い。引き継ぎのときにこう言われます。
「シートは分かるけど、この式が怖い。」
これでは業務改善とは言えません。担当者が異動や退職でいなくなった瞬間、そのファイルは”誰も触れない黒魔術”として、そのまま放置されることになります。
実際にあった話を一つ紹介します。ある担当者が作った1セル完結の式は、条件が5つ、VLOOKUPが3回、ネストが4段という、かなり複雑なものでした。その担当者が異動したあと、後任の方が「1箇所だけ単価を変えたい」と相談してきたのですが、式のどこにその単価が書かれているのか、見つけるだけで30分以上かかりました。しかも、似たような数値が式の中に複数登場していたため、「本当にこの数値で合っているのか」を確認するために、もとの担当者に問い合わせる羽目になったそうです。もし式が分解されていれば、この確認作業自体が発生しなかったはずです。属人化した式は、担当者本人にとっても、後任者にとっても、時間を奪う存在になってしまいます。
改善方法：分解する
解決策はシンプルです。1セル1役割にする。
例えば先ほどの式を分解します。
B列：単価取得
=VLOOKUP(B2,$F$2:$G$10,2,FALSE)C列：倍率判定
=IF(A2="A",1.1,1)D列：最終計算
=B2*C2これだけで、式が短くなる、役割が明確になる、修正が楽になる。一気に”読めるExcel”になります。VLOOKUP関数の基本をあらためて確認しておくと、分解した後の式でも、参照方法の指定ミスなどに気づきやすくなります。
なお、分解したからといって、必ずしもセルの数が増えて見た目が悪くなるわけではありません。作業列として使うB列・C列は、あとから非表示にしてしまえば、見た目はもとの1セル完結とほとんど変わりません。それでいて、いざというときはいつでも中身を確認できる。この「隠せるけれど、開けば読める」状態こそが、実務でちょうどいいバランスです。
作業列を非表示にする際は、1点だけ注意しておきたいことがあります。それは、非表示にした列を誰かが誤って削除してしまわないようにしておくことです。見えない列は、存在自体を忘れられやすく、「使っていない列だと思って削除したら、他のセルの計算が全部おかしくなった」というトラブルにつながることがあります。シートを保護する、あるいは作業列であることが分かるように見出しに注記を残しておく、といった一手間を加えておくと安心です。
1セル完結にしないメリット
- 修正が安全になる
- 他人が理解できる
- エラー箇所を特定しやすい
- 将来の変更に強い
とくに「エラー箇所を特定しやすい」というのは、実務では見落とされがちなメリットです。1セル完結の式でエラーが出た場合、「条件判定・検索・計算」のどの部分が原因なのかを、式全体を読み解きながら切り分けなければなりません。分解してあれば、B列がエラーなら検索の問題、C列がエラーなら判定の問題、というように、エラーが出ている列を見るだけで、原因の見当をすぐにつけられます。
Excelは「賢く見せるツール」ではありません。再現性を持たせるツールです。
一見遠回りに見える「分解する」という作業ですが、実際にやってみると、式そのものを考える時間は意外と変わりません。むしろ、1つのセルに全部詰め込もうとして頭の中で条件を組み立てる時間のほうが、長くかかっていることも多いのです。分解して考えることは、式を書く本人にとっても、思考の整理になります。
実務で覚えておきたい基準
すべての式を分解するべきかというと、そうではありません。大切なのは、「複雑になりそうな兆候」を早めに見極めることです。迷ったら、次のように考えてみてください。
- この式の中に、条件判定・検索・計算のうち、2つ以上が同時に入っていないか
- 同じ関数（VLOOKUPなど）を、式の中で2回以上使っていないか
- 半年後の自分が見て、何をしているかすぐに説明できるか
このうち1つでも引っかかるなら、分解を検討するサインです。1セル完結は、”設計を後回しにした結果”です。逆に言えば、条件が1つだけで、今後も増える見込みがないような簡単な式であれば、無理に分解する必要はありません。すべてを分解することが目的ではなく、「あとから読めるかどうか」を基準に判断することが大切です。
分解する際に、もう一つ意識しておきたいのが、作業列の見出しをきちんと付けておくことです。「B列：単価取得」「C列：倍率判定」のように、各列が何をしているのかを見出し行に書いておくだけで、後から見た人の理解速度は大きく変わります。式そのものを分解しても、見出しがないと「なぜこの列が必要なのか」が伝わらず、結局は元の複雑さの一部が形を変えて残ってしまうことになります。
まとめ
1セル完結は、一見きれいに見えます。しかし実務では、修正に弱い、引き継ぎに弱い、ミスが起きやすいという欠点があります。
Excelは「短い式」よりも「読める構造」が重要です。もし今、複雑な式を書いているなら、一度分解してみてください。それだけで、あなたのExcelは一段レベルが上がります。
1セルに詰め込みたくなる気持ちは、私自身よく分かります。セルの見た目がすっきりしていると、なんとなく仕事ができている気がするものです。ですが、実務で本当に評価されるのは、見た目のスマートさではなく、後から誰が見ても直せる状態を保てているかどうかです。この視点を持っておくだけで、Excelファイルの作り方は大きく変わっていきます。
今回は「1つの式」の設計について取り上げましたが、この考え方は式だけでなく、表全体の作り方にもそのままつながっています。データと計算を同じ場所に置かない、担当者しか理解できない構造にしない、といった表設計全体の話は、また別の機会に整理していく予定です。
関数の使い方シリーズや実務トラブル解決シリーズでは、他の関数や設計についても実務目線で整理していますので、あわせてご覧ください。
コメント