Excelで複数条件抽出&集計 - もうカツ丼はいいよな

f:id:Rion778:20160713213905p:plain
このようなデータがあって、条件1と条件2に基づいて何らかの集計をしたいとする。

AVERAGEIFS

平均値を計算したいのであれば、AVERAGEIFS関数があるので、例えばこのように入力する。

=AVERAGEIFS(平均対象範囲,条件範囲1,条件1,条件範囲2,条件2)

最初の例で言えば、条件がこのように入力されているとして、
f:id:Rion778:20160713215530p:plain
入力は次のようになる。

=AVERAGEIFS(C:C,A:A,F3,B:B,G3)

AVERAGEIFSは高速に動作するので、列全体を範囲指定してもあまり問題が無い。
同様の集計関数にはCOUNTIFS、SUMIFSなどがあり、いずれも高速である。なお、Excel2007以降で使える。

配列数式

平均、カウント、合計以外の集計をしたい場合、悪名高き配列数式を使うという手段がある。
配列数式は配列を渡して配列が返ってくる仕組みで、一人で使っている分には便利だが、他人にファイルを引き継ごうとすると配列数式の説明を漏れなく行う必要があり、大変面倒くさいという代物である。
AVERAGEやVAR.Sなどのセル範囲を引数として受け取る関数は、セル範囲の代わりに配列を渡しても良いので、IFを組み合わせて配列を作成し、それを渡せば良い。

=AVERAGE(IF(IF(A1:A10000=F3,TRUE,FALSE)*
            IF(B1:B10000=G3,TRUE,FALSE),
            C1:C10000,""))

IF文中のTRUE、FALSEは1、0でも良い。
この式をCtrl+Shiftを押下しながら確定すると、配列数式に変換されて式の両端に{}が付く。見た目には{}以外の変化が無いので、大変分かりにくい。
そして配列数式の最大の欠点として、「遅い」というものがある。上記例ではわざわざセル範囲を1万行に制限してあるが、これをしないと1秒くらいの計算時間がかかってしまう。配列数式を使う場合、範囲指定は狭ければ狭いほど良い。

名前付き範囲

この配列数式の欠点を補う方法として、名前付き範囲を使用するという方法がある。
Excelにおける名前とは変数名のようなもので、指定のセル範囲を指定の名前で表記できるというものである。例えば、前述の式を次のように記述できる。

=AVERAGE(IF(IF(条件1=F3,1,0)*
            IF(条件2=G3,1,0),
            値,""))

これで大分可読性が向上する。
名前はリボンの[数式]から[名前の定義]で定義する。
ここで、次のように入力する。
f:id:Rion778:20160713222641p:plain
このようにOFFSET関数で参照範囲を定義することで、参照範囲が入力状況に対応して自動的に伸縮し、必要最低限の参照範囲となり、計算時間を節約できる。
なお、COUNTAで参照する列は、定義から想像出来ると思うが、全ての行に値が入っているような列でなければならない。MATCH関数等と組み合わせることで空白を許容することも可能であるが、長くなるのでここでは説明しない*1。
なお、名前には拡大倍率を40%以下にするとどこに名前を設定しているか一目で確認できるという便利機能があるが、上記の方法で設定した名前にはこの手段が使えない。
f:id:Rion778:20160713223803p:plain