bar_1

contents_map

2011年3月24日木曜日

[Excel][VBA]COUNTIF関数を複数条件で使うには？

■背景と問題点


以前のエントリ ([EXCEL][VBA] COUNTIF関数で複数条件を扱うには？(OR条件編), 2008.)で、COUNTIF関数によるORで結合された複数条件を扱う方法を紹介した。しかし、この方法だと場合によっては以下のような不具合がある：
  • 条件を満たすセルの数を、二重にカウントしてしまう
  • AND条件については触れていない
また、この問題をググってみても、どうもこれといって、分かりやすい解法が載っているサイトを見つけることはできない（2011年3月現在）; 多くのサイトでは、SUMPRODUCT関数、COUNTIFS関数（Excel 2007以降）を使った方法が紹介されているが、この方法では、AND条件しか、指定することができない。
上述の以前のエントリは、本ブログに挙げて以来、継続的にアクセスがある。 COUNTIF関数で複数条件を扱うのに困っている人は、多そうだ。
この問題は、根本的には、COUNTIF関数の第2の引数である検索条件の記述の不自由さに由来するものと、考える。なぜならば（ヘルプ・ドキュメントによれば、検索条件には「式」を書くことが出来ることになっているが、実際には）、検索条件には、「TRUEもしくはFALSEを返す、2項限定の(論理)式」しか、指定 することが出来ないからだ。
そこで、上記の問題点を踏まえ、今回のエントリでは：
  • 以前のエントリのように、二重にカウントすることはない
  • ブーリアン演算の指定、すなわち：
    • 条件指定は、AND条件・OR条件どちらも同時に使用可能
    • 条件項目は、いくつでも可能
  • VBAマクロは使用せず、既存の関数のみの単純な方法
であるような、一般化したCOUNTIF関数で複数条件を扱う方法を、紹介する。



■COUNTIF関数で複数条件を扱う方法

今回の方法は、方式としては：
  • 複数条件を、複数の個別の条件からなる複合条件とみなす
  • 作業列を設けて、そこに各個別の条件の適合結果を求める
  • 論理関数を任意に組み合わせて、各条件の適合結果から、複合条件を構成して評価する
  • 複合条件の適合結果を、カウントする
というものである。
まず、この方式による処理の全体の流れを、確認しておこう。
前提として、
  • n個の個別の条件：条件1 ～ 条件n がある（n は 2以上の自然数とする）
  • ひとつひとつの条件は、２つのセル（または値）と、それら２つを接続する計算演算子などのTRUE/FALSEを返す演算子から、構成されている
  • 複合条件は、個別の条件を論理関数で接続したものである
ものとする。

このとき、
  1. 個別の条件i のTRUE/FALSEの結果を、求める (計算演算子 = を使用)
    ここで：
    i: 1, 2, ..., n
    である。
  2. 複合条件 のTRUE/FALSEの結果を、求める (論理関数OR, AND を使用)
  3. 複合条件の結果のTRUEを数える (COUNTIF関数を使用)
という手順で処理を行うことで、所望の結果(複数条件でのCOUNTIF)を、得ることができる。
1), 2)は、作業列を用意して行う。

以下では、この手順を使って、チュートリアル式に、複数条件のCOUNTIFの使い方を示してみる。

■COUNTIFで複数条件チュートリアル

下表のようなデータがあったとする。

conds.PNG
データの意味は：
  • サンプル列は、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を実現できた。
また無論この方法で、
  • 計算演算子に別なモノを使って、別の条件を試す
  • 条件をもっと増やす
  • 論理関数を変えて、もっと複雑な複数条件にする
ことも可能である（計算演算子の一覧、論理関数の一覧は、Referenceにあげておいた）。

Conclusion

  • 3つの条件とANDとORを使った論理的な組み合わせからなる複数条件を、COUNTIF関数で扱う方法を示した
  • 条件を増やし、複雑な複数条件にすることも可能である

References

  1. MS, “COUNTIF”, http://office.microsoft.com/ja-jp/excel-help/HP005209029.aspx?pid=CH062528311041
  2. MS, “計算演算子について”, http://office.microsoft.com/ja-jp/excel-help/HP005198697.aspx?CTT=1
  3. MS, “論理関数”, http://office.microsoft.com/ja-jp/excel-help/HP005201141.aspx?CTT=1
  4. MS, “Excel 2003 のヘルプと使い方”, http://office.microsoft.com/ja-jp/excel-help/HP005201141.aspx?CTT=1
  5. “エヴァ物知り辞典　人物＆使徒”, http://www3.ocn.ne.jp/~lucifer/eva_db/db02_idx.htm
投稿者 時刻:
ラベル: , , , , ,

0 件のコメント:

コメントを投稿

何かありましたら、どうぞ:

登録: コメントの投稿 (Atom)