まずセルフチェックから始めます
いきなりですが、次の3つに1つでも当てはまるものがあれば、この記事はあなたのための記事です。
- IFの中にVLOOKUPが入っていて、そのVLOOKUPが2回以上出てくる
- 「この式、何してるんだっけ」と、作った自分ですら数か月後に固まったことがある
- 誰かに引き継ぐとき、「ここだけは触らないでください」と口頭で伝えたセルがある
1つでも心当たりがあれば、それは偶然ではありません。Excelの式は、条件を1つ足し、また1つ足し、という積み重ねで気づかないうちに膨らんでいきます。最初から複雑にしようと思って書く人は、まずいません。
実際にあった話：42行のズレに気づくまで1時間20分
これは、ある月次の実績集計表で実際に起きたことです。
4人チームで使っていた実績集計シートに、「担当者区分によって単価を切り替える」という式が入っていました。もとはシンプルな条件分岐だったのですが、途中で「特定の取引先だけ例外」「月末締めのときだけ倍率を変える」という条件が次々に追加され、最終的にはIFのネストが4段、VLOOKUPが式の中に3回登場する状態になっていました。
作った担当者が異動した2週間後、後任の方が「先月の集計だけ数字が合わない」と気づきました。原因を探ったところ、式の中に埋め込まれていた条件のひとつが、決算月の特例（本来は年に1回だけ適用すべき条件）だったにもかかわらず、毎月そのまま計算され続けていたことが分かりました。影響を受けていたのは42行分のデータで、集計のズレは合計23,400円。金額としては大きくありませんでしたが、「どこにその条件が書かれているのか」を式の中から見つけ出すのに、1時間20分かかりました。似たような数値が式の中に複数登場していたため、「本当にこの数値で合っているのか」を確認するために、前任者に問い合わせる必要まで出てきたそうです。
この話のポイントは、式自体は動いていた、ということです。エラーは一切出ていません。数字は毎月それらしく表示され続けていました。だからこそ、誰も気づけなかった。1セル完結の式は、壊れたときではなく、動き続けているときにこそ怖さがあります。
なぜ1セル完結は危険なのか：3つの原因
原因① 役割が1つのセルに混ざっている
たとえばこんな式です。
=IF(A2="A",
VLOOKUP(B2,$F$2:$G$10,2,FALSE)*1.1,
VLOOKUP(B2,$F$2:$G$10,2,FALSE))この式には、条件判定・単価取得・計算処理という3つの役割が同居しています。しかも同じVLOOKUPが2回登場しているため、参照範囲を修正する際は2箇所とも直す必要があります。片方だけ直して、もう片方を直し忘れる、というミスは実務でよく起こります。
役割が混ざっている式は、動作の面でも無駄があります。条件によって結果が変わるのはIFの部分だけなのに、比較的重い処理であるVLOOKUPを毎回2回実行しているからです。データ量が少ないうちは気になりませんが、行数が数千、数万と増えてくると、ファイル全体の計算速度にじわじわ影響してきます。
原因② 修正箇所が式の中に埋もれる
「Aのときの倍率を1.2に変更して」と言われたとき、式のどこを触るべきか一瞬で分かるでしょうか。条件が1つならまだ耐えられますが、実務では「あとから例外を追加する」ことがほぼ必ず起こります。半年後には、誰も全体を把握できない長さのカッコとネストになっている、というのはよくある話です。
さらに厄介なのは、こうした式がコピー＆ペーストで他のセルにも広がっていくことです。修正が必要になったとき、その式が何行・何列にわたってコピーされているかを把握するところから始めなければなりません。
原因③ 引き継ぎができなくなる
実務でいちばん深刻なのはこれです。1セル完結の式は、書いた本人しか理解できないことが多く、引き継ぎのときにこう言われます。「シートは分かるけど、この式が怖い」。担当者が異動や退職でいなくなった瞬間、そのファイルは誰も触れない状態のまま放置されます。
この「役割の混在」「修正箇所の不明瞭さ」「引き継ぎ不能」という3つは、単独で起きているのではなく、互いに原因と結果になっています。役割が混ざっているから修正箇所が分からず、修正箇所が分からないから他人には引き継げない、という一本の線でつながっているのです。この根っこの部分は、これまでこのシリーズで扱ってきたIFのネストやSUMIFSの集計ズレとも共通しています。
Before / After：分解するとどう変わるか
言葉だけでは伝わりにくいので、先ほどの式を分解した場合の違いを整理します。
| 項目 | Before（1セル完結） | After（分解後） |
|---|---|---|
| セルの役割 | 条件判定・検索・計算が1セルに混在 | B列＝検索、C列＝判定、D列＝計算 |
| VLOOKUPの実行回数 | 1セルあたり2回 | 1セルあたり1回 |
| 倍率を変更するとき | 式全体を読んで該当箇所を探す | C列だけを見ればよい |
| エラーが出たとき | 3つの処理のどれが原因か式全体を読む | エラーが出ている列を見れば原因の見当がつく |
| 引き継ぎ時の説明 | 口頭で「ここは触らないで」と伝える必要 | 列見出しを見れば役割が分かる |
分解した式は、実際には次のようになります。
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点だけ注意が必要です。非表示にした列を誰かが誤って削除してしまわないよう、シートを保護する、あるいは作業列であることが分かるように見出しに注記を残しておく、といった一手間を加えておくと安心です。実際、先ほどの42行のズレのケースでも、後任の方が最初に困ったのは「どの列が計算用で、どの列が入力用か分からなかった」ことでした。見出しに一言「※計算用・削除不可」と書いてあるだけで、混乱はかなり減らせます。
分解の実践手順：5つのステップ
「分解したほうがいいのは分かったけれど、実際どこから手をつければいいのか」という声もよく聞きます。ここでは、既存の1セル完結の式を安全に分解するための手順を、順番に紹介します。
ステップ1 式をコピーして退避させる
いきなり元のセルを書き換えるのは危険です。まずは対象のセルをコピーし、別のシートか、離れた場所のセルに貼り付けておきます。分解の途中で計算結果がおかしくなっても、いつでも元の式に戻せる状態を作っておくことが、最初の一手です。今回の42行のケースでも、後任の方は真っ先に元のシートを複製してから作業を始めたそうです。これがなければ、確認作業自体がもっと長引いていた可能性があります。
ステップ2 式の中の「かたまり」を洗い出す
次に、式の中にいくつの処理が含まれているかを数えます。条件判定（IF）、検索（VLOOKUP）、計算（掛け算・足し算など）というように、機能ごとに色分けしてみると分かりやすくなります。1つの式の中に3種類以上の機能が混ざっていたら、分解の優先度は高いと考えてよいでしょう。
ステップ3 1つの機能ごとに列を用意する
洗い出した機能ごとに、新しい作業列を1つずつ用意します。このとき大事なのは、1列につき1つの機能だけを担当させることです。「検索と計算を両方やる列」を作ってしまうと、結局もとの木阿弥に戻ってしまいます。
ステップ4 列見出しに役割を明記する
「B列：単価取得」「C列：倍率判定」のように、各列の役割を見出し行に書きます。これは省略したくなる工程ですが、実はいちばん効果が大きいステップです。今回のケースでも、見出しさえあれば、後任の方が1時間20分もかけて式を読み解く必要はなかったはずです。
ステップ5 最終セルで合算し、答え合わせをする
分解した列を組み合わせて最終結果を出すセルを作り、もとの1セル完結の式の結果と一致するかを確認します。一致すれば分解は成功です。一致しない場合は、どこかの列で条件の取りこぼしがある可能性が高いので、1列ずつ順番に見直していきます。すべての行で一致を確認できたら、もとの式は削除して、分解後の構成に置き換えて完了です。
この5ステップは、1つの式であれば10分もかからずに終わることがほとんどです。慣れてくると、複雑な式を見た瞬間に「これは3つに分けられそうだ」と見当がつくようになります。
よくある質問
Q. すべての式を分解するべきですか？
いいえ、そうとは限りません。条件が1つだけで、今後も増える見込みがない簡単な式であれば、無理に分解する必要はありません。大切なのは「あとから読めるかどうか」を基準に判断することです。
Q. 分解の基準がよく分かりません。何を見て判断すればいいですか？
次の3つのうち、1つでも当てはまるなら分解を検討するサインです。
- 条件判定・検索・計算のうち、2つ以上が同時に1つの式に入っていないか
- 同じ関数（VLOOKUPなど）を、式の中で2回以上使っていないか
- 半年後の自分が見て、何をしているかすぐに説明できるか
Q. 作業列を増やすとシートが複雑に見えませんか？
見た目の複雑さと、構造の複雑さは別物です。作業列は非表示にできますが、1セルに詰め込まれた式は隠しようがありません。見た目をすっきりさせたいなら、むしろ作業列に分けて非表示にするほうが、結果的にきれいな仕上がりになります。
Q. すでに1セル完結で組んでしまった大量の式は、全部作り直すべきですか？
すべてを一度に直す必要はありません。まずは「今後も条件が増えそうな式」「複数人が触るシート」から優先的に分解するのが現実的です。今回の42行のケースのように、実際に問題が起きてから慌てて解読するよりも、影響範囲が大きい式から手をつけておくほうが、結果的に時間の節約になります。
実務で覚えておきたい基準
Excelは「賢く見せるツール」ではなく、「再現性を持たせるツール」です。一見遠回りに見える「分解する」という作業ですが、実際にやってみると、式そのものを考える時間は意外と変わりません。むしろ、1つのセルに全部詰め込もうとして頭の中で条件を組み立てる時間のほうが、長くかかっていることも多いのです。
分解する際に、もう一つ意識しておきたいのが、作業列の見出しをきちんと付けておくことです。「B列：単価取得」「C列：倍率判定」のように、各列が何をしているのかを見出し行に書いておくだけで、後から見た人の理解速度は大きく変わります。式そのものを分解しても、見出しがないと「なぜこの列が必要なのか」が伝わらず、結局は元の複雑さの一部が形を変えて残ってしまいます。
こんな式にも同じ考え方が使えます
ここまではIFとVLOOKUPの組み合わせを例にしてきましたが、「1セル完結が危険」という考え方は、他の関数の組み合わせでも同じように当てはまります。実務でよく見かける2つのパターンを紹介します。
パターン1 SUMIFSとIFERRORを重ねた集計式
「条件に合う売上を合計し、該当がなければ0を表示する」というだけの目的で、次のような式が組まれているのを見かけたことがあります。
=IFERROR(SUMIFS($D$2:$D$500,$A$2:$A$500,A2,$B$2:$B$500,">="&B2,$C$2:$C$500,"<="&C2)/COUNTIFS($A$2:$A$500,A2,$B$2:$B$500,">="&B2,$C$2:$C$500,"<="&C2),0)これは、条件付き合計・条件付き件数・平均計算・エラー処理という4つの役割を1つのセルに詰め込んだ例です。以前、あるチームでこの形式の式を使った月次レポートを確認したところ、担当者が期間条件の演算子（以上・以下の向き）を1か所だけ間違えていたことに、3か月間誰も気づけていませんでした。件数にすると18件分の月が対象期間からずれて集計されており、報告済みの数値を後日訂正する事態になっています。SUMIFSの集計ズレそのものについては別の記事で詳しく扱っていますが、根本原因はやはり「1つのセルに詰め込みすぎた」ことにあります。これも、合計を出す列、件数を出す列、平均を出す列、エラー処理をする列と、4つに分ければ、演算子の向きが逆になっていることに、もっと早い段階で気づけたはずです。
パターン2 文字列結合と条件分岐を組み合わせた表示用の式
見積書や請求書のフォーマットで、「金額によって表示する文言を変える」という式もよく見かけます。
=IF(D2>=100000,"要承認："&TEXT(D2,"#,##0")&"円（上長確認必須）",IF(D2>=50000,TEXT(D2,"#,##0")&"円（要確認）",TEXT(D2,"#,##0")&"円"))金額の判定、承認要否の文言、金額の書式設定という3つの役割が1つのセルに混ざっています。この形式の式は、見た目にはそれらしい文章が表示されるため一見問題なさそうに見えますが、「50,000円」という金額の基準を変更したくなったときに、式の中のどこにその数値が書かれているかを探す手間が発生します。実際、ある担当者はこの基準額を変更する際に、似たような数値が式のあちこちに登場していたため、誤って別の数値を書き換えてしまい、本来「要確認」と表示されるべき金額の見積書が、承認確認なしでそのまま先方に送られてしまったことがありました。金額判定の基準を別セルに切り出しておくだけで、こうした事故は防げます。
このように、「1セル完結が危険」という考え方は、特定の関数だけに当てはまるものではありません。条件判定・検索・集計・表示整形のうち、2つ以上が1つの式に同居していないか、という視点で自分の式を見直してみると、思わぬところに分解すべき式が見つかることがあります。
分解を後回しにするとどうなるか
「あとで直せばいい」と思っているうちに、式はどんどん育っていきます。実際、今回の42行のケースも、最初は条件がひとつだけのシンプルな式でした。それが半年かけて条件が4つ、VLOOKUPが3回というところまで膨らんでいったのです。途中で誰かが「そろそろ分解したほうがいいのでは」と気づく機会は、おそらく何度かあったはずです。ですが、目の前の業務を優先してしまい、式の見直しは後回しにされ続けました。
分解にかかる時間は、慣れていれば1つの式あたり数分程度です。一方で、放置した式を後から読み解く時間は、今回のように1時間を超えることも珍しくありません。「今すぐ数分」か「あとで1時間以上」かという選択だと考えると、分解しておくメリットは分かりやすいはずです。とくに、複数人で共有するシートや、毎月・毎週使い続けるシートほど、早めに分解しておく価値は大きくなります。
まとめ
1セル完結は、一見きれいに見えます。しかし実務では、修正に弱い、引き継ぎに弱い、ミスが起きやすいという欠点があります。今回紹介した42行・23,400円・1時間20分という数字は、決して特別なケースではありません。エラーが出ないまま静かに進行するタイプのトラブルだからこそ、気づいたときには影響範囲が広がっていることが多いのです。
1セルに詰め込みたくなる気持ちは、よく分かります。セルの見た目がすっきりしていると、なんとなく仕事ができている気がするものです。ですが、実務で本当に評価されるのは、見た目のスマートさではなく、後から誰が見ても直せる状態を保てているかどうかです。この視点を持っておくだけで、Excelファイルの作り方は大きく変わっていきます。
もし今、自分の作った式を見て「これ、人に説明できるだろうか」と少しでも不安になったなら、それは分解を検討するタイミングです。今日、1つの式だけでもいいので、役割ごとに分けてみてください。
式の設計だけでなく、表そのものの作り方に問題があるケースも少なくありません。自分しか触れないExcelになる原因を扱った記事では、入力・計算・出力を分離する考え方を、もう少し表全体の視点から整理しています。あわせて読むと、今回の「分解」の考え方がより実感しやすくなるはずです。
なお、今回のような複合的な条件が絡む式を、実際の業務データに近い形でどう分解していくかについては、もう少し複雑な実務シナリオを想定した練習素材をnoteの方でまとめています。もう一歩踏み込みたい方は、自分しか触れないExcelを「人に渡せる形」にする改修手順（note）ものぞいてみてください。
コメント