Edited at

「分かりにくくて再計算が遅い」Excelを改善するための数式

More than 1 year has passed since last update.

Excelで複雑な集計をしようとしてファイルが大きくなってくると、数式の依存関係が分かりにくくて修正できなくなったり、セルを編集するたびに再計算に時間がかかったりと、ストレスの溜まる状況になりがちです。

「わかりやすく」「効率の良い」数式を書くことで、このような状況を少しは緩和することが可能です。そのために普段私が気を付けていることをいくつか紹介します。(★の数は個人的に使用頻度が高いかどうかを3段階で表しています)


VLOOKUP()は使わない。INDEX(, MATCH())を使う(★★★)

次の簡単な例を考えてみてください。

品名
定価
在庫数
単位

りんご
120
50

バナナ
120
40

オレンジ
100
60

いちご
380
30
パック

このデータを「在庫」という名前のテーブルにしてあるとします。ここから「オレンジ」の「在庫数」を拾いたい場合、

=VLOOKUP("オレンジ", 在庫, 3, FALSE)

のようにVLOOKUP()を使うのが普通だと思います。

ですが、あとで説明するように、VLOOKUP()には問題があります。代わりにINDEX()MATCH()を使ってこのように書いてください。

=INDEX(在庫[在庫数], MATCH("オレンジ", 在庫[品名], 0))

これだけで、再計算が軽くなりメンテナンスしやすくなります。

VLOOKUP()は非常によく使われている関数ですが、弊害が多く使うべきではありません。

上で紹介した例は単純ですが、もっと大きなテーブルになった場合、いろいろな問題が出てきます。

在庫数が何列目にあるかわざわざ数えなければなりません。何十列もあるようなデータの場合、このストレスは耐え難いものです。また、入力された数式内の3というパラメータだけを見ても、どの列を表しているのか不明です。テーブルを編集して在庫数が3列目でなくなった場合、数式を修正しなければなりません。

VLOOKUP()では、キー列は一番左にある必要があります。2列目以降にキーがあるデータの場合、VLOOKUP()を使うだけのために、キー列を一番左に移動する必要があります。

在庫テーブル全体を参照しているため、たとえば定価などの関係ないセルが編集されても再計算が走ってしまいます。そのため、VLOOKUP()を多用しているシートは、1つのセルを編集するたびに再計算でフリーズするような、致命的な状況に陥る場合があります。

これらの問題のため、VLOOKUP()を多用したシートはメンテナンスしにくく、再計算が遅くなる傾向があります。

INDEX(, MATCH())によって書き換えるとことで、これらの問題は解決できます。



  • 在庫数が何列目にあるか数える必要がありません。

  • 数式を見ただけで、在庫数を見ているということが一目瞭然です。

  • 列の順序が変わっても何も問題ありません。

  • キー列が一番左になくてもかまいません。


  • 定価など関係ない列が編集されても再計算しません。

このように、VLOOKUP()INDEX(,MATCH())に書き換えることで、見通しがよく、再計算が軽くなります。

VLOOKUP()は今日で忘れてください。代わりにINDEX(,MATCH())を使いましょう。


可能な限り、MATCH()の照合パラメータは1にする(★★)

MATCH()は計算量の大きな関数です。MATCH()を多用したシートは再計算が遅くなる傾向があります。

MATCH()関数には第3引数に「照合の種類」をオプションで指定することができます。通常は0を指定する場合が多いですが、省略するか1を指定すると、より効率の良いアルゴリズムで計算してくれます。

VLOOKUP()を多用して重くなったシートでは、上で紹介したINDEX(,MATCH())への書き換えと組み合わせて使うことで、再計算が劇的に速くなる場合があります。

たとえば上の例では、あらかじめ在庫テーブルを品名で昇順ソートしておき、=INDEX(在庫[在庫数],MATCH("オレンジ",在庫[品名],1))のようにすれば、"オレンジ"の在庫数を高速に取得できます。どのくらい高速かというと、仮に在庫テーブルが100万行あって、検索する品名が1万個あったとしても、ほぼ一瞬だと思います。

ただし、MATCH(, , 1)を使えない場合もありますので、注意が必要です。MATCH(, , 1)を使えるのは次の条件を全部満たす場合に限られます。


  • 検査範囲は事前に昇順にソートされている。

  • 検査値と完全一致するデータが存在することが事前に分かっている。または、不完全一致(検査値以下の最大の値との一致)が許容される。

  • 検査値にワイルドカード(? や *)を使用しない。


追記:一致するデータが存在するか分からない場合の高速MATCH()

では、検索したい"オレンジ"という品名が在庫[品名]の中に存在するかどうか分からない場合はどうでしょうか。

在庫テーブルに"オレンジ"が存在しない場合、結果は#N/Aになってほしいところですが、INDEX(,MATCH(, , 1))では不完全一致を許容してしまうので、別の商品のデータを取得してしまいます。

この場合、一般的にはMATCH()の照合パラメータを0にして、完全一致することで解決します。あまりデータ量が多くない場合や、再計算が頻繁に発生しない場合には、これでもいいかもしれません。(私の環境 Excel 2013 32bit / Intel(R) Core(TM) i5-5300U CPU @ 2.30 GHz では、100万行の範囲を参照するMATCH()関数1万個で体感3秒ぐらいでした。)

これでは遅い場合は、MATCH(, , 1)の結果を一旦別のセルに保持しておき、この値が正しいかどうかを確認してから使用することで、高速化できます。

具体的には、例えばセルA1に=MATCH("オレンジ",在庫[品名],1)を入力しておき、それを利用して下のような数式を書きます。

=IF(

INDEX(在庫[品名],$A$1)="オレンジ",
INDEX(在庫[在庫数],$A$1),
NA()
)

なお、MATCH()の結果を保存するためだけのセルを用意したくない場合は、MATCH()を2回使って入れ子にしても構いません。MATCH()を2回使うので計算時間は遅くはなります。ですが、MATCH(,,0)を使うよりははるかに速いです。

=IF(

INDEX(在庫[品名],MATCH("オレンジ",在庫[品名],1))="オレンジ",
INDEX(在庫[在庫数],MATCH("オレンジ",在庫[品名],1)),
NA()
)

同じことですが、1つ名のINDEX(,MATCH())VLOOKUP()に置き換えれば、少し見やすさは改善します。(これはVLOOKUP()関数を使っても良い、数少ない例外パターンの一つです。)

=IF(

VLOOKUP("オレンジ",在庫[品名],1,TRUE)="オレンジ",
INDEX(在庫[在庫数],MATCH("オレンジ",在庫[品名],1)),
NA()
)


SUMIFS()よりSUMPRODUCT()を使う(★★)

SUMIFS()は複数の条件を満たすデータの合計を求める関数です。SUMPRODUCT()を使うと、条件にANDやORを組み合わせるなど、より柔軟なことができます。

SUMPRODUCT()は配列の要素の積の和を計算する関数です。たとえば、SUMPRODUCT({1, 2, 3}, {4, 5, 6}, {0, 1, 0})は、(1 * 4 * 0) + (2 * 5 * 1) + (3 * 6 * 0) = 10となります。

これを応用すると、SUMIFS()の上位互換として使えます。

たとえば、このような在庫テーブルで、「定価が100円以下で個売りする商品の合計在庫数」を求めたいとします。

品名
定価
在庫数
単位

りんご
120
50

バナナ
120
40

オレンジ
100
60

いちご
380
30
パック

SUMIFS()ならこうなります。

=SUMIFS(在庫[在庫数], 在庫[定価], "<=100", 在庫[単位], "個")

同じことを、SUMPRODUCT()ならこうやります。

=SUMPRODUCT(在庫[在庫数], N(在庫[定価]<=100), N(在庫[単位]="個"))

N(在庫[定価]<=100)N(在庫[単位]="個")は見慣れない表現だと思います。どういうことかというと、


  • まず、在庫[定価]<=100{FALSE, FALSE, TRUE, FALSE}という配列として評価されます。


  • N()で囲むことで、{0, 0, 1, 0}に変換されます。

  • つまり、定価が100円以下なら1で、そのほかは0の配列になります。

  • 同様に、N(在庫[単位]="個")は、{1, 0, 1, 0}と評価されます。


  • SUMPRODUCT()は配列要素同士の掛け算をするので、いずれかが0の部分は消えて、全部が1の部分だけが集計されます。

このように、集計条件を01の配列にしてSUMPRODUCT()に入れてやることで、複数の条件を満たすデータだけを集計できるのです。(配列数式なので入力した後Ctrl + Shift + Enterを押す必要があります)

では、「袋またはパック単位で販売する商品の定価ベースの在庫合計金額」を求めたいとします。SUMIFS()では不可能ですが、SUMPRODUCT()では一発でいけます。

=SUMPRODUCT(在庫[定価], 在庫[在庫数], N(在庫[単位]="袋") + N(在庫[単位]="パック"))

先ほどの要領で、N(在庫[単位]="袋"){0, 1, 0, 0}N(在庫[単位]="パック"){0, 0, 0, 1}と評価されます。

今回は「袋またはパック」というOR条件なので、掛け合わせるのではなく足し合わせます。{0, 1, 0, 0}{0, 0, 0, 1}を足すので、{0, 1, 0, 1}になります。

SUMIFS()だと中間集計列を用意しなければできないような複雑な条件集計も、このやり方ならセルの中だけでできます。しかも、数式を読んで意味が分かりやすいので、あとから修正するのも比較的簡単です。


数式内にコメント(★)

数式が長く複雑になると、あとで見たときにロジックが理解しにくく、メンテしにくいシートになってしまいます。数式はできるだけシンプルに書くのを原則とすべきです。

ただ、どうしても長く分かりにくい数式になってしまう場合もあります。そのような場合に、数式内に一言コメントを書いておきたい場合があります。

Excelには数式内にコメントを記述する機能はありませんが、N()という関数を使うことでコメントの代用にできます。

N(文字列)0と評価されます。0を足しても計算結果に影響がない場合はN("コメント")を数式に組み込むことで、コメントの代わりにできます。


元の数式

=IFERROR(

CHOOSE(MATCH(集計項目, {"実績", "達成率", "対前年", "対前年比"}, 0),
SUMIFS(実績[売上], 実績[担当者], 担当者, 実績[年度], 年度),
SUMIFS(実績[売上], 実績[担当者], 担当者, 実績[年度], 年度) / SUMIFS(実績[予算], 実績[担当者], 担当者, 実績[年度], 年度),
SUMIFS(実績[売上], 実績[担当者], 担当者, 実績[年度], 年度) - SUMIFS(実績[売上], 実績[担当者], 担当者, 実績[年度], 年度-1),
SUMIFS(実績[売上], 実績[担当者], 担当者, 実績[年度], 年度) / SUMIFS(実績[売上], 実績[担当者], 担当者, 実績[年度], 年度-1)
), "NA")


コメントを追加した数式

=IFERROR(

CHOOSE(MATCH(集計項目, {"実績", "達成率", "対前年", "対前年比"}, 0),
N("/* 実績 */")+
SUMIFS(実績[売上], 実績[担当者], 担当者, 実績[年度], 年度),
N("/* 達成率 */")+
SUMIFS(実績[売上], 実績[担当者], 担当者, 実績[年度], 年度) / SUMIFS(実績[予算], 実績[担当者], 担当者, 実績[年度], 年度),
N("/* 対前年 */")+
SUMIFS(実績[売上], 実績[担当者], 担当者, 実績[年度], 年度) - SUMIFS(実績[売上], 実績[担当者], 担当者, 実績[年度], 年度-1),
N("/* 対前年比 */")+
SUMIFS(実績[売上], 実績[担当者], 担当者, 実績[年度], 年度) / SUMIFS(実績[売上], 実績[担当者], 担当者, 実績[年度], 年度-1)
),
IF(N("/* 集計項目がエラーの場合""NA""を表示 */"),,"NA"))