Excelでセルの値で範囲を指定する：SUMIF、INDIRECT、CONCATENATE

Excelにはいろいろと便利な機能がある。
特に集計関数は強力だと思う。
個人的には「SUMIF」お気に入りです。もちろん「IF」も好きです。


下記の表があったとします。
お菓子の合計を計算したいなら下記の関数になります。
（式１）
=SUMIF(C3:C12,"=お菓子",B3:B12)

ここで、INDIRECTを使ってみます。
INDIRECTでは、文字列から範囲を作成することが可能です。
INDIRECT("C3:C12")で範囲になります。
上記の式をINDIRECTで変えます。

（式２）
=SUMIF(INDIRECT("C3:C12"),"=お菓子",INDIRECT("B3:B12"))

INDIRECTの分、長くなっただけで意味無いですね！
さらに、セルの値から参照してみます。

検索範囲は、C3:C12なので、CONCATENATE(F3,F5,":",F3,F6)をINDIRECTで指定。
合計範囲は、B3:B12なので、CONCATENATE(F4,F5,":",F4,F6)をINDIRECTで指定。
検索の値は、F2です。
これで、上記のSUMIFは下記のようになります。

（式３）
=SUMIF(INDIRECT(CONCATENATE(F3,F5,":",F3,F6)),"="&F2,INDIRECT(CONCATENATE(F4,F5,":",F4,F6)))

すごく長くて、一見では動作が不明です。
一番最初の式１と同じ結果なのに、複雑になっています。
こんなことに意味があるのか？
たぶん、使うシーンによってそれぞれだと思います。


あらためて式１と式３を比べてみます。
結果は同じなのに、何が異なるのか？

（式１）
=SUMIF(C3:C12,"=お菓子",B3:B12)

（式３）
=SUMIF(INDIRECT(CONCATENATE(F3,F5,":",F3,F6)),"="&F2,INDIRECT(CONCATENATE(F4,F5,":",F4,F6)))


わかりにくいと思いますが、式１は、式の中に定数がたくさん含まれています。
式３は、定数少なくて、代わりにセルの参照値が含まれています。
つまり、式１は定数指定、式３は変数指定と考えられます。


式１の場合、検索範囲や検査値に変更があるときは、式の「定数を変更」します。
式３の場合、検索範囲や検査値に変更があるときは、指定の「セルの値」を変更します。


どっちが便利なのかは、扱うデータによって異なります。
個人的には、多数の集計をしたいときには、式３を使っています。
手っ取り早く集計したいときは、式１を使っています。

式３を見てわかる通り、これを書くだけでも面倒です。
ただし、きっちり書いてしまえば、後は式３をコピーするだけで似たような集計が簡単にできます。
範囲の変更も簡単にできます。
もちろん、ExcelのVBAを使えばもっと簡単にもっと複雑なことが出来ます。
でも、関数を使っておけば、LibreOfficeやKingsoftOfficeでも転用可能です。
場合によりけりですが、知っておいて損は無いと思います。