はじめに
エクセルの表計算を、スピルとINDIRECT関数でちょっとだけ簡単・効率化します。
サンプルとする明細
品名に対する金額を取得し、個数と掛け算して金額を算出します。
A～C列：手入力
D列：K～L列の表から品名に紐づく金額を取得します。
E列：単価と個数をかけて金額を算出します。
K～L列：品名に対する金額を設定、LookUPで参照します。
D・E列の計算式は、明細数分コピペする必要があります。
明細が追加される都度、コピペするのは面倒です。
→5行目を追加しましたが、当然それだけでは計算されません。
スピル機能を使って少し簡単にする
D列、E列の計算式を変更します。
D列：
（明確な意図はないですが、xlookupにしました）
E列：
結果：
2行目に計算式を指定するだけで、同じ結果が取得できます。（見た目は変わりませんが）
ただし、これでも明細が追加される都度、計算式の修正が必要です。
5行目の明細追加に対応：
それぞれスピル範囲を、B2:B4→B2:B5、C2:C4→C2:C5 に変更しています。
もっと簡単にしたいです。
INDIRECT関数で明細の追加に追従させる
D、E列の計算式のスピル範囲を、INDIRECT関数でを指定します。
D列：
E列：
スピル範囲を動的にするため、INDIRECT関数を使用します。
D列の場合、変更前 B2:B4 → 変更後 INDIRECT("B2:B"&COUNTA(A:A)) としました。
スピル範囲の最終行（変更前の"4"）を動的にするため、INDIRECTで**"B2:B"まで文字列指定、
最終行をCOUNTA(A:A)**で取得して&で文字列結合します。これにより、明細が追加される都度、
COUNTA(A:A)で最終行を取得するため、計算式も明細に追従するようになります。
A～C列を入力するだけで、D・E列の計算式が追従します。
注意点
-
COUNTAで明細行を取得する行は、必須項目である必要がある
→未入力があり得る項目だと、行数計算がおかしくなります。 -
行・列に挿入があると関数の再設定が必要
→INDIRECT関数で固定文字列で指定しているため、行・列挿入に相対的に変更されません。
項目を追加しようとB列を挿入しましたが、正しい計算結果が得られなくなりました。 -
INDIRECT関数を使ったことにより、関数の視認性が悪くなる
→なれるしかない。