IF関数と配列の利用
スピル配列が使えることを前提とする(使えないときには静的配列を用いる)。
条件が一つのとき
まずは「担当」が「藤本」である「金額」の合計を求めよう。
「担当」が「藤本」である「金額」を抽出する。
担当:藤本である金額の配列
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 |
| 5300 | 2300 | 1500 | FALSE | FALSE | 2000 | 2500 | 1250 | FALSE | FALSE | FALSE | 1560 | 2500 | 1500 | FALSE | FALSE | 5500 | 1700 | 1200 | FALSE | FALSE |
IF(担当="藤本",金額)で担当が藤本であるときはそのときの金額が配列に格納される。担当が藤本でないときはFALSEが配列に格納される。
配列の中身を暫定的に書き出してみた
| | E | F | G |
| 1 | | | | |
| 2 | | | =IF(担当=A25,金額) | |
| 3 | | | 2300 | |
| 4 | | | 1500 | |
| 5 | | | FALSE | |
| 6 | | | FALSE | |
| 7 | | | 2000 | |
この数値と「FALSE」(数値集計関数からは無視される)からなる21個の配列を集計する。
SUM(担当:藤本である金額の配列)
| 1 | 2 | 3 | | 21 |
| 5300 | 2300 | 1500 | | |
| 24 | 担当 | 金額合計 |
| 25 | 藤本 | =SUM(IF(担当=A25,金額)) |
| 26 | 松浦 | |
平均値(AVERAGE)も同様に導ける。
「担当者」が藤本であるデータの件数を数えるときには、「金額」を指定せず、単に適当な数値(例えば「1」)を指定すれば良い(「金額」に相当するデータがないケースでも対応可)。
| 24 | 担当 | | 件数 |
| 25 | 藤本 | | =COUNT(IF(担当=A25,1)) |
条件が複数あるとき
であるときの金額の合計を求めたい。
IF(AND(担当=J2,費目=K2),金額)と書きたくなるが、これは使えない。
AND関数はSUM関数やAVERAGE関数と同様配列を集約してしまう。AND(担当=J2,費目=K2)は各行すべての条件がTRUEのときのみ、TRUEとなる。
今回は「担当=松浦」のときにFALSEとなるので、全体もFALSEとなってしまい、配列はすべて空白となる。
AND関数・OR関数が使えないのであれば、論理演算を用いる。AND関数相当は論理積「*」である。
担当:藤本かつ費目:交通費である金額の配列
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 |
| 5300 | FALSE | FALSE | FALSE | FALSE | 2000 | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | 2500 | FALSE | FALSE | FALSE | 5500 | 1700 | FALSE | FALSE | FALSE |
配列の中身を暫定的に書き出してみた
| | E | F | G |
| 1 | | | | |
| 2 | | | =IF((担当=A34)*(費目=B34),金額) | |
| 3 | | | FALSE | |
| 4 | | | FALSE | |
| 5 | | | FALSE | |
| 6 | | | FALSE | |
| 7 | | | 2000 | |
この配列から合計を求める。
| 33 | 担当 | 費目 | 金額合計 |
| 34 | 藤本 | 交通費 | =SUM(IF((担当=A34)*(費目=B34),金額)) |
| 35 | 藤本 | 接待費 | |
-IFS関数
IF関数と配列を用いる上記方法は応用範囲が広く(COUNT、SUM, AVERAGE、MAX、MIN以外の関数でも使える)、強力だが、Excel初学者には大変分かりづらい。またスピル配列が使えないバージョンだと静的配列という扱いづらい配列を用いるため、余りお勧めできない。
そこでEXCELには条件を伴った集計を行う専用の関数が複数用意されている。
条件付きデータ個数:COUNTIFS(条件範囲1,条件1,条件範囲2,条件2,…)
条件範囲から条件に当てはまるデータの個数を返す。条件は複数指定ができる。
COUNTIFS関数の記述例
| 33 | 担当 | 費目 | 件数 | |
| 34 | 藤本 | 交通費 | =COUNTIFS(担当,A34,費目,B34) | |
| 35 | 藤本 | 接待費 | | |
- 古いEXCEL解説ものにはCOUNTIF関数(末尾にSがつかない)が説明されていることがあるが、この関数は前時代の遺物なので、さっさと忘れよう。
大なり(>)小なり(<)を含む条件
COUNTIFS関数など条件付き集計関数の「条件」は論理式ではない。前例のように完全一致の時はあまり意識しないが、大なり(>)小なり(<)を含む条件を書くときの書式は正確に理解する必要がある。
「>, <, =」を条件に書くときにはダブルコーテーション(")で括る(ex.1000以上→">=1000")。
金額が1000円以上の件数
| 41 | 件数 | |
| 42 | =COUNTIFS(金額,">=1000") | |
| 43 | | |
比較対象(今回なら「1000」)がセルに書かれている場合は&演算子を用いて、セル参照と条件文を文字列結合する。
COUNTIFS関数でセル参照を用いる
| 44 | 1000 | |
| 45 | | |
| 46 | 件数 | |
| 47 | =COUNTIFS(金額,">="&A44) | |
| 48 | | |
IF関数と配列の組み合わせ
| 44 | 1000 | |
| 45 | | |
| 46 | 件数 | |
| 47 | =COUNT(IF(金額>=A44,1)) | |
| 48 | | |
IF関数と配列を組み合わせた場合は上のようになる。
条件付き合計:SUMIFS(集計対象範囲,条件範囲1,条件1,条件範囲2,条件2,…)
条件付き平均:AVERAGEIFS(集計対象範囲,条件範囲1,条件1,条件範囲2,条件2,…)
条件範囲から条件に当てはまる数値の集計結果を返す。条件は複数指定ができる。
SUMIFS関数の記述例
| 33 | 担当 | 費目 | 金額合計 |
| 34 | 藤本 | 交通費 | =SUMIFS(金額,担当,A34,費目,B34) |
| 35 | 藤本 | 接待費 | |
- 古いEXCEL解説ものにはSUMIF関数、AVERAGEIF関数(末尾にSがつかない)が説明されていることがあるが、この関数は前時代の遺物なので、さっさと忘れよう。
書式はシンプルで便利だが、集計できる関数の種類が限られるのが難点。合計(SUM)・平均(AVERAGE)・最大(MAX)・最小(MIN)は求められるが、例えば分散(VAR.S:本実習では未学習だが、統計分野で用いる)は求められない。ということでIF関数と配列を組み合わせたやり方も理解しておく必要がある。
形式)