Search

2013/10/07

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


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


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



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

(式2)
=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は下記のようになります。

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

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


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

(式1)
=SUMIF(C3:C12,"=お菓子",B3:B12)

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


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


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


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

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

0 件のコメント:

コメントを投稿