2008/08/02

Excel 二つの列に値の入っている行をカウントする IF文の中でのAND条件OR条件 及び フィルター機能とSUBTOTAL関数テク

今回のお題はExcelに二つの列があり、A列、B列、ともに値の入っている行の数をカウントしたいというもの。

IFのネスト構造でも書けますが、AND関数を使った方がエレガントですね。プログラマの方の場合、論理演算子をパッと思い浮かべるところでしょうが、Excelのセルの中で

=IF((A2<>"") AND (B2<>"")),0,1)

などとやると即エラーです。ExcelにはAND関数、OR関数という関数が用意されていて、ワークシート上ではこの関数を使うわけですね。使い方は簡単。

上記のネタであれば

=IF(AND(A2<>"",B2<>""),1,0)

と書いてやれば、A2、B2のセルの二つがそろって空ではない場合のときのみAND関数からはTRUEがかえってきます。よって上記式では2つのセルに値が入っているときのみ、それ以外はすべて0を返してきます。これを最終行でSUMすれば、二つの列に値が入っている行数がでてきますね。

Ω Ω Ω

▲まずSUBTOTAL関数を仕込みます。

また、別解としてフィルター機能とSUBTOTAL関数での調べ方も紹介します。SUBTOTAL関数とフィルター機能は意外とベンリなんですね。フィルターで抽出したもののみを対象にSUBTOTAL関数は集計作業を行ってくれるから。

書式
SUBTOTAL(集計方法, 範囲1, 範囲2, ...)

集計方法は1~11までの数値で指定します。

集計方法関数
1AVERAGE関数
2COUNT関数
3COUNTA関数
4MAX関数
5MIN関数
6PRODUCT関数
7STDEV関数
8STDEVP関数
9SUM関数
10VAR関数
11VARP関数

ですから、ここではたとえばB列の末尾に

=SUBTOTAL(3,B2:B24)

▲フィルター機能で抽出したものを対象に集計結果を返しています。

と入力したおきます。集計方法は3なのでCOUNTA関数、つまり引数リストの各項目に含まれるデータの個数の合計を返す関数ですね。で、フィルター機能でA列、B列の条件に「空白以外のセル」をいれてあげればSUBTOTAL関数が反応してくれるわけですね。

0 件のコメント: