COUNTIF, SUMIF, AVERAGEIF

ワークシート関数で集計をする際に重宝するのが、COUNTIF, SUMIF, AVERAGEIF などの集計用関数です。私が分析中のExcelはほぼこれらの関数で埋め尽くされています。

難易度 ☆☆☆
便利度 ☆☆☆☆
面白度 ☆☆☆☆

条件に合う行の○○を取る

先の記事ではCOUNTIFを使いましたが、これは中に記した条件にマッチする行数を数え上げるもの。これと同じように、SUMIFは条件にマッチする行の値を足し上げますし、AVERAGEIFは条件にマッチする行の平均を取ります。

また、これらの複数条件版である、COUNTIFS, SUMIFS, AVERAGEIFS もあります。単に条件式を複数記入できるというだけの違いです。二種類あるのは過去の経緯でしょうか、複数条件版だけで十分だと思うのですが。実は、複数条件版しかない MAXIFS, MINIFS というのもあったりして、こちらは見たらわかるように、条件に合う行の中から最大、もしくは最小を取ってくる関数ですが、複数条件版しかありません。複数条件版でも、条件を一つしか書かなければ単一版と同じになりますが、記入する順序が違ったりするので注意が必要です。

例：三つのアイテムの売り上げデータ

sample_ooif.xlsx

181 KB

ファイルダウンロードについて

ダウンロード

（2019/5/14 ファイルに一カ所ミスがあったので、修正しました。）

この例はある商品の売り上げデータで、itemがA,B,Cの三種類あり、その販売価格=priceと販売個数=Nが記載されています。

まず、COUNTIFでA,B,Cそれぞれ何行ずつデータがあるか調べましょう。

=COUNTIF($B$8:$B$6256,"A")

すると、2063行あることがわかります。同様に、"A"のところを"B"や"C"にすると、2123行、2063行であることもわかります。ただ、これでは毎回ワークシートの関数の内部を書き換えるのが面倒なので、A,B,CをI行に書き、それを参照するようにします。

=COUNTIF($B$8:$B$6256,$I8)

すると、同じ関数でA,B,Cすべての行数をカウントできました。

次は販売価格の平均を取ってみます。AVERGEIFを使いますが、COUNTIFとの違いは「平均対象範囲」にC行を加えることです。

=AVERAGEIF($B$8:$B$6256,$I8,$C$8:$C$6256)

これで、itemが"A"のものだけの平均販売価格が取れました。102.2円です。SUMIFも同様です。

=SUMIF($B$8:$B$6256,$I8,$D$8:$D$6256)

Aは3619個売れていますね。

ところで、総売上は価格×個数を足し上げるのですが、これはこのままでは○○IFの関数で作ることができません。そこで、E列に価格×個数の行を足します。つまり、集計関数だけではなくて、集計するための前処理としてデータを増やすことも、頻繁にやることの一つです。では、E行にsales=price×Nの行を追加して、これをSUMIFで足し上げましょう。

=SUMIF($B$8:$B$6256,$I8,$E$8:$E$6256)

アイテムAの売上は369,679円であることがわかりました。

もう少し複雑な集計として、例えば、「価格が安いときと高いときで、購入個数に違いがあるか？」という問いを観察したいとします。例えば、「安いとき」を平均価格より安い場合、「高いとき」を平均価格より高い場合として、次のような関数で平均個数を出してみましょう。平均価格は上の関数で計算してK列に入っているので、これを使います。

=AVERAGEIFS($D$8:$D$6256,$B$8:$B$6256,$I8,$C$8:$C$6256,"<="&$K8)

式の中の、「"<="&$K8」の部分が、平均より安い場合を示しています。高い場合はこの不等号を逆にすればOK。なお、使用する関数がAVERAGEIFからAVERAGEIFSに変わったので、条件と集計値の順番が変わっていることに注意です。アイテムAは、価格が安いときの平均個数が1.732個、高いときの平均個数が1.778個とほぼ同じですので、おそらく価格にかかわらずに売れていると思われます。

A,B,Cそれぞれの最高値、最安値などもそれぞれMAXIFS, MINIFSで算出できます。サンプルファイルに記載しておきます。

おわりに

こうやって、ワークシート関数を用いて集計をしておく最も大きなメリットは、何を集計したのかがわかりやすいというところです。式を見れば、どんな条件でどのような集計をしたのかがわかりやすく、また、それがワークシートに残っていて、結果が表示されているというところがメリットです。

注意点をいくつか。

メリットの一つとして、データが変われば値もすぐに変わってくれるという効能があるのですが、実はこれにはデメリットもあって、データの行数が変わってしまうと関数を修正しなければならなくなるという問題も抱えています。可変行のデータに対応できないのはExcelの大きな弱点で、他のツールを使用する動機にもなったりするところなので、注意が必要です。

さらにもう一つ、計算時間の問題です。この例で採り上げたような、高々数千行のデータでは全く気にならないのですが、10万行を超えるとこれらの集計関数はかなり時間がかかるようになります。それもそのはず、全ての条件をすべての行に対して逐一確認しているので、データが10万行ならばCOUNTIFで10万回、SIMIFSやAVERAGEIFSなど条件が複数存在する場合はそのぶんだけ、データのマッチングや比較をしており、その分だけ時間がかかります。ExcelはデフォルトではPCに積んである全てのCPUを使って計算してくれるので、4Core8スレッドですと勝手に8並列計算をしてくれますが、それでも行数が多いと数分～数十分かかったりもします。あまりに時間がかかりすぎる場合は、その計算を一旦やめて別の方法をとった方が良い場合もあります。（例えば、power pivotを使うなど。）