数式リファクタリングでスプレッドシートを整理する方法
要約
数式リファクタリングとは、既存の数式や表の構造を、結果を変えずに整理し直すことです。この記事ではExtract Method、Replace Temp with Query、カプセル化、重複排除、条件式の置き換えという5つの手法を、具体的なスプレッドシート例つきで紹介します。今日から実践できる内容です。
数式リファクタリングとは、ちゃんと動いている数式や表の構造を、結果を変えずに整理し直すことです。締切前の夜に勢いで書いた「動くけど二度と開きたくない数式」を、来期も安心して使えるものに変える作業だと思ってください。
この数式、ビスケットがもう咥えて持ってきましたよ。実際に効果があるやり方だけを紹介します。
なぜ動いている数式なのに気持ち悪いのか
よくあるサインはこれです。半年前に自分で作ったファイルを開いて、1つのセルの意味を理解するのに3分かかる。あるいは数式を別の列にコピーしたら、どこかに埋め込まれた固定の行参照のせいで静かに壊れる。
リファクタリングは結果を変えません。行や列が増えたり、シート名が変わったりしても、構造が壊れないようにするための整理です。
やらなくていい場合:誰も触らない一回限りの計算なら、手間をかける意味はありません。整理にはコスト(時間、混乱のリスク)がかかるので、得られるものがゼロならROIもゼロです。
やる価値がある場合:複数人がそのファイルを使う、定期的に更新する、複数シートにまたがって数式が連鎖している。こうした条件が1つでもあれば整理の予定を組む価値があります。
注意すべきサインの例:
100%表示でも列幅に収まらない長い数式
ネストしたIFが2段階を超えている
同じ数字が3か所以上に直接書かれている
「このセル、何やってるか説明して」と同僚に聞かれる
どれか1つでも当てはまれば、整理のタイミングです。
Extract Method:1つの数式を役割ごとに分ける
スプレッドシートでも、プログラミングと同じくらい効果が大きいテクニックです。考え方はシンプルで、1つの数式が3つのことをやっているなら、3つのセルに分けて1ステップずつ担当させ、最後に結果を組み合わせます。
Before(1セルで全部やる場合):
=IF(AND(A2<>"",DATEDIF(A2,TODAY(),"Y")>30),"シニア","ジュニア")After:
セル
C2:=DATEDIF(A2,TODAY(),"Y")、名前は「経過年数」セル
D2:=AND(A2<>"",C2>30)、名前は「シニア判定」セル
E2:=IF(D2,"シニア","ジュニア")、最終結果
実際のセルに入れるとこうなります。1ステップずつ単体で読めるようになります。しきい値が30から25に変わっても、変更するのは1つのセルの1つの数字だけ。DATEDIFが日付欠損で壊れても、どこで壊れているか一目でわかります。
トレードオフは列数が増えることです。横に広い表では無駄に見えるかもしれません。列が足りないなら、別シートにヘルパー用のエリアを作りましょう。シート名は「Helpers」や「計算用」など、開いた瞬間に役割がわかる名前にするのがコツです。
Excel、Googleスプレッドシートどちらでも同じやり方が使えます。バージョンによる制限はありません。
Replace Temp with Query:計算できるものは保存しない
スプレッドシートの「一時変数」とは、他のどこからも直接参照されないのに、次のステップに進むためだけに値を入れているセルのことです。
こうしたセルが役立つ場面もあります(上のセクション参照)。ただし、5つのヘルパーセルが連鎖していて、最後の1つしか意味がなく、途中の値が二度と変わらないなら、1つの数式にまとめてしまいましょう。
1つの結果に向かう3つのヘルパーセル:
=B2*C2、小計=D2*0.2、消費税額=D2+E2、合計
まとめた場合:
=(B2*C2)+(B2*C2*0.2)税率を名前付き範囲にするなら:
=B2*C2*(1+TaxRate)ルールはこうです。あるヘルパーセルが、たった1つの他のセルからしか参照されていないなら、そのままインライン化できるくらい短いか確認する。短ければインライン化する。長ければヘルパーとして残し、きちんと名前を付ける。
線引きの目安:80文字を超える数式は、たいていインライン化には長すぎます。その場合は上のExtract Methodのほうが向いています。読みやすさに本当に貢献するヘルパーだけ残し、ノイズになっているものは消しましょう。
Encapsulate Field:変わってはいけない値を1か所にまとめる
スプレッドシートにおけるカプセル化とは、税率、しきい値、参照値といった固定の入力値を、数式の中にバラバラに直接書くのではなく、1か所にまとめておくことです。
忘れられがちなのに、結局みんなが必要になる整理がこれです。
消費税率0.2をワークブック内の47個の数式に直接書いてしまうと、それはメンテナンスの罠になります。税率が変わったら47か所すべてを探さなければいけません。3か所見逃せば計算ミスです。
直し方:
「設定」シートを作り、セルに
VATRate = 0.2という名前を付けるすべての数式で参照する:
=B2*VATRate税率が変わったら1セルだけ更新すれば、あとは全部それに従う
これは日付の基準(今日の日付を固定した基準点として使う場合)、通貨換算レート、カテゴリーのしきい値にも当てはまります。ある値が3回以上登場し、将来変わる可能性があるなら、名前付き範囲にする価値があります。
名前付き範囲の作り方:
Excel:数式タブ > 名前の管理 > 新規作成
Googleスプレッドシート:データ > 名前付き範囲
命名規則は重要です。TaxRateはわかりやすい。TRはわかりにくい。tmp_3論外です。自分が書いたのではない数式を見た人が、3秒で理解できる名前を選びましょう。
重複を消す:数式版のDRY原則
DRY原則(同じことを繰り返さない)はソフトウェア工学でもっとも引用される原則の1つですが、スプレッドシートにもそのまま当てはまります。同じVLOOKUPを微妙に変えながら15列にコピーしているなら、いずれメンテナンスの問題が爆発します。
状況に応じた2つのアプローチ:
**Excel 365 / Googleスプレッドシート:**XLOOKUPやINDEX-MATCHの配列展開を使えば、複数列を1つの数式で取得できます。
=XLOOKUP(A2,LookupTable[ID],LookupTable[[Name]:[Status]])これで名前とステータスを一度に取得でき、列ごとにコピペする必要がありません。
**古いExcel(2016以前):**ヘルパーセルに1回だけMATCHを書き、そのポジションをINDEXで参照します。
G2:=MATCH(A2,LookupTable[ID],0)、全列共通の1つのMATCHH2:=INDEX(LookupTable[Name],G2)I2:=INDEX(LookupTable[Status],G2)
どこでも同じMATCH結果を参照しているので、検索対象の列が変わっても更新は1か所だけです。
バージョンの補足:XLOOKUPはEnterprise版とMicrosoft 365版のExcel 2019から使えます。チームがExcel 2016を使っているなら、INDEX-MATCHのやり方を選びましょう。Googleスプレッドシートは2022年以降、全プランでXLOOKUPに対応しています。
ネストしたIFを卒業する
ネストしたIFは、スプレッドシートが読めなくなる典型パターンです。3段階も入れ子にすれば、ほとんどの読者、それどころか半年後の自分すら置いていかれます。
Before(ネストしたIFの連鎖):
=IF(A2>10000,"A",IF(A2>5000,"B",IF(A2>1000,"C","D")))選択肢1:IFS(Excel 2016以降 / Googleスプレッドシート)
=IFS(A2>10000,"A",A2>5000,"B",A2>1000,"C",TRUE,"D")フラットになりますが、それでも1つの数式に4つの条件があります。許容範囲です。
選択肢2:参照テーブルを使ったVLOOKUP(意外と過小評価されているやり方)
小さな参照テーブルを作ります:
10001以上:カテゴリーA
5001〜10000:カテゴリーB
1001〜5000:カテゴリーC
0〜1000:カテゴリーD
そして:=VLOOKUP(A2,ThresholdTable,2,TRUE)
TRUE(近似一致)がソート済みテーブルを下にたどり、条件に合う最後のカテゴリーを返します。ロジックは数式の中ではなくテーブルの中にあるので、監査しやすくなります。しきい値を変えたい?テーブルの1行を編集するだけです。
奮発する価値があるのは参照テーブル方式です。数式を読まない人にもロジックが見える形になります。急いでいるなら:条件が3つ以下ならIFSで十分です。
SWITCH関数もExcel 2019以降とGoogleスプレッドシートで使える選択肢です。範囲ではなく完全一致で判定したいときは、IFSより見通しよく書けます。
書き直した方が早いとき:代入アルゴリズムの発想
数式が間違っているわけではなく、アプローチそのものが合っていないこともあります。CONCATENATEと&でテキストをつないだ200文字の数式を引き継いで、そこにもう1項目足す必要が出てきたとします。正解は延長することではなく、TEXTJOINで書き直すことです。
Before:
=A2&" | "&B2&" | "&C2&" | "&D2After:
=TEXTJOIN(" | ",TRUE,A2:D2)「明日、表の形が変わってもこの数式は付いてきます」というのがまさにこれです。TEXTJOINは空セルも配列も扱え、E列を追加しても壊れません。
同じ発想は次にも当てはまります:
完全一致+欠損値処理が必要ならVLOOKUPをXLOOKUPに置き換える
IFERRORで包む代わりに、新しい関数のネイティブなエラー処理を使う
Ctrl+Shift+Enterで入力した配列数式を、Excel 365のネイティブな動的配列に置き換える
同じ原則はもっと大きな規模にも広がります。INDIRECT参照、外部接続、名前付き範囲が別の名前付き範囲を指すといった絡み合いに育ってしまったデータパイプラインをスプレッドシートの中で維持しているなら、ロジックを正式なツール(SQL、Python、Power Query)に出して、スプレッドシートは表示専用にするのが正しい整理になることもあります。大きな決断ですが、考え方は同じカテゴリーです。
始める前に:壊さないための3つの準備
数式リファクタリングがうまくいくのは、取り返しがつかない壊し方をしない場合だけです。スプレッドシートで言うと、次の3つが必要です。
**日付入りのバックアップ。**整理を始める前に、ワークブックのコピーを日付付きで保存する。同じフォルダへの上書き保存ではなく、別の場所(クラウドバックアップ、自分宛メールなど)へ。何かあったときに自分を救うのはこれです。
**今の正しい結果を把握しておく。**変更前に、合計や集計値、主要なセルの値をメモしておく。変更のたびに同じセルと見比べます。10秒の確認で、崩れをすぐ発見できます。
**一度に1つだけ変える。**同じセッションでExtract Methodと3つの名前付き範囲のカプセル化とネストIFのフラット化を同時にやらない。何かが壊れたとき、どの変更が原因かわからなくなります。1つやって、確認して、次に進みます。
この3つの準備には5分もかかりません。予想外の何かが起きた整理作業では、その5分をはるかに超えて助けてくれています。
リファクタリングに終わりはない(そしてどう止めるか)
整理の落とし穴は完璧主義です。すでに問題なく動いているワークブックを丸一日かけて整理し直し、結果はほんの少しきれいになっただけで半日を失う、なんてこともあり得ます。
始める前に時間を区切りましょう。「これに45分」と決める。実際に問題を起こしている数式、みんなが読み間違える数式、通常使用で壊れる数式、追加したい新機能をブロックしている数式を優先します。
それ以外は放っておいて大丈夫です。ちゃんと動いていて、誰も触らず、1年間エラーも出ていない数式は、それでいいのです。「十分きれいならそれで止まる」は正当な選択です。
整理は完璧なワークブックを作ることが目的ではありません。次の変更をやりやすくすることが目的です。変更のたびに自分に聞いてみてください。これをやると次の作業が速くなるか、安全になるか。イエスならやる。ノーなら止める。
きちんとしたリファクタリングは静かなものです。誰も気づきません。それが目指すところです。