2011年03月24日
[Excel][VBA]COUNTIF関数を複数条件で使うには？
※[ブックマーク]ボタンの中に delicious, reddit, digg, Google+(G+) のボタンもあります。■背景と問題点
以前のエントリで、COUNTIF関数によるORで結合された複数条件を扱う方法を紹介した。しかし、この方法だと場合によっては以下のような不具合がある：
- 条件を満たすセルの数を、二重にカウントしてしまう
- AND条件については触れていない
また、この問題をググってみても、どうもこれといって、分かりやすい解法が載っているサイトを見つけることはできない（2011年3月現在）; 多くのサイトでは、SUMPRODUCT関数、COUNTIFS関数（Excel 2007以降）を使った方法が紹介されているが、この方法では、AND条件しか、指定することができない。
上述の以前のエントリは、本ブログに挙げて以来、継続的にアクセスがある。 COUNTIF関数で複数条件を扱うのに困っている人は、多そうだ。
この問題は、根本的には、COUNTIF関数の第2の引数である検索条件の記述の不自由さに由来するものと、考える。なぜならば（ヘルプ・ドキュメントによれば、検索条件には「式」を書くことが出来ることになっているが、実際には）、検索条件には、「TRUEもしくはFALSEを返す、2項限定の(論理)式」しか、指定 することが出来ないからだ。
そこで、上記の問題点を踏まえ、今回のエントリでは：
- 以前のエントリのように、二重にカウントすることはない
-
ブーリアン演算の指定、すなわち：
- 条件指定は、AND条件・OR条件どちらも同時に使用可能
- 条件項目は、いくつでも可能
- VBAマクロは使用せず、既存の関数のみの単純な方法
■COUNTIF関数で複数条件を扱う方法
今回の方法は、方式としては：
- 複数条件を、複数の個別の条件からなる複合条件とみなす
- 作業列を設けて、そこに各個別の条件の適合結果を求める
- 論理関数を任意に組み合わせて、各条件の適合結果から、複合条件を構成して評価する
- 複合条件の適合結果を、カウントする
まず、この方式による処理の全体の流れを、確認しておこう。 前提として、
- n個の個別の条件：条件1 〜 条件n がある（n は 2以上の自然数とする）
- ひとつひとつの条件は、２つのセル（または値）と、それら２つを接続する計算演算子などのTRUE/FALSEを返す演算子から、構成されている
- 複合条件は、個別の条件を論理関数で接続したものである
このとき、
-
個別の条件i のTRUE/FALSEの結果を、求める (計算演算子 = を使用)
ここで：
i: 1, 2, ..., n
である。 - 複合条件 のTRUE/FALSEの結果を、求める (論理関数OR, AND を使用)
- 複合条件の結果のTRUEを数える (COUNTIF関数を使用)
以下では、この手順を使って、チュートリアル式に、複数条件のCOUNTIFの使い方を示してみる。
■COUNTIFで複数条件チュートリアル
下表のようなデータがあったとする。
データの意味は：
- サンプル列は、Aくん〜Eくんの人
- EVA列は、それぞれの人が、エヴァンゲリオンで、一番好きなキャラ
- SHY列は、それぞれの人が、涼宮ハルヒの憂鬱で、一番好きなキャラ
このときに、複合条件
エヴァでは綾波がすきで、かつ、ハルヒでは長門もしくはみくるがすきである人数を数えたい場合、どのようにするか？つまり、
綾波と長門が好きな人の数＋綾波とみくるが好きな人の数を求めるにはどうしたらいいだろうか。
◯ステップ１：個別の条件i のTRUE/FALSEを求める
複合条件をもう一度見てみよう。
エヴァでは綾波がすきで、かつ、ハルヒでは長門もしくはみくるがすき
ここで、条件を分解してみる。すると
条件1.エヴァで綾波が好き 条件2-1. ハルヒで長門が好き 条件2-2. ハルヒでみくるが好きの３つの個別の条件から、構成されていることが、分かる。そして、これらは全体として
複合条件 ＝ 条件1 AND (条件2-1 OR 条件2-2)という形になっている。
そこで、まずAくんからEくんまで、条件1, 2-1, 2-2のそれぞれの条件を評価した作業列を3列、作ってみよう。すると、E列、F列、G列のようになるはずだ。記述の中身は：
=($B3=$D$3) ……Aくんの条件1 =($C3=$D$4) ……Aくんの条件2-1 =($C3=$D$5) ……Aくんの条件2-2である。Bくん以下については、コピペで求めることが出来る。
またもちろんこれは、
=($B3="綾波") ……Aくんの条件1のように、直接、期待値を記述しても構わない。
◯ステップ２：複合条件のTRUE/FALSEの結果を求める
ステップ２−１：ハルヒで、長門かみくるが好きな人はだれか、TRUE/FALSEを求める
次に、ステップ１の結果を使って、ハルヒで長門もしくはみくるが好きな人はだれか、 求めてみよう。 いま、
F列には、条件2-1:ハルヒで長門が好き、 G列には、条件2-2:ハルヒでみくるが好きという結果が、もとめられている。
したがって、長門もしくはみくるが好き、というのは：
=OR($F3,$G3)のようにかける（Aくんの場合。Bくん以下はコピペで求めればよい）。これを、I列に書いておこう。
ステップ２−２：エヴァで綾波が好きで、かつ、ハルヒで長門かみくるが好きな人は誰か、TRUE/FALSEを求める
前のステップ２−１において、I列に長門もしくはみくるが好きである、という結果は 求められている。また、ステップ１において、条件1：綾波が好きである の結果をすでに求めている。
だから、複合条件の結果は：
=AND($E3,$I3)とかける（Aくんの場合。Bくん以下はコピペで求めればよい）。これを、J列に書いておこう。
◯ステップ３：複合条件の結果のTRUEを数える
前のステップ２−２までに、複合条件の結果を得ることができた。いよいよ最後だ。 TRUEとなった人の数を数えよう。
セルJ8において、
=COUNTIF(J3:J7, TRUE)とかく。これで、
エヴァでは綾波がすきで、かつ、ハルヒでは長門もしくはみくるがすきである人の数を求めることができた。
以上により、複数条件（条件1, 2-1, 2-2）で、AND関数とOR関数による複合条件を使ったCOUNTIFを実現できた。
また無論この方法で、
- 計算演算子に別なモノを使って、別の条件を試す
- 条件をもっと増やす
- 論理関数を変えて、もっと複雑な複数条件にする
Conclusion
- 3つの条件とANDとORを使った論理的な組み合わせからなる複数条件を、COUNTIF関数で扱う方法を示した
- 条件を増やし、複雑な複数条件にすることも可能である
References
- MS, “COUNTIF”, http://office.microsoft.com/ja-jp/excel-help/HP005209029.aspx?pid=CH062528311041
- MS, “計算演算子について”, http://office.microsoft.com/ja-jp/excel-help/HP005198697.aspx?CTT=1
- MS, “論理関数”, http://office.microsoft.com/ja-jp/excel-help/HP005201141.aspx?CTT=1
- MS, “Excel 2003 のヘルプと使い方”, http://office.microsoft.com/ja-jp/excel-help/HP005201141.aspx?CTT=1
- “エヴァ物知り辞典 人物＆使徒”, http://www3.ocn.ne.jp/~lucifer/eva_db/db02_idx.htm
