ただいま整理中。
Blogspot (Blogger?) に移行しました!
タイムライン | 技術系 | TIPS | ライフハック系 | その他 | 左記カテゴリ以外は、右の欄の下のタグクラウドへ!
.

2011年03月24日

[Excel][VBA]COUNTIF関数を複数条件で使うには?
カテゴリ: 技術 | タグ: EXCEL COUNTIF 関数 複数条件 VBA

※[ブックマーク]ボタンの中に 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関数で複数条件を扱う方法を、紹介する。  



《スポンサードリンク》

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

今回の方法は、方式としては:

  • 複数条件を、複数の個別の条件からなる複合条件とみなす
  • 作業列を設けて、そこに各個別の条件の適合結果を求める
  • 論理関数を任意に組み合わせて、各条件の適合結果から、複合条件を構成して評価する
  • 複合条件の適合結果を、カウントする
というものである。 

まず、この方式による処理の全体の流れを、確認しておこう。 前提として、

  • n個の個別の条件:条件1 〜 条件n がある(n は 2以上の自然数とする)
  • ひとつひとつの条件は、2つのセル(または値)と、それら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列は、それぞれの人が、涼宮ハルヒの憂鬱で、一番好きなキャラ
とする。  

このときに、複合条件

エヴァでは綾波がすきで、かつ、ハルヒでは長門もしくはみくるがすき
である人数を数えたい場合、どのようにするか?つまり、
綾波と長門が好きな人の数+綾波とみくるが好きな人の数
を求めるにはどうしたらいいだろうか。  

◯ステップ1:個別の条件i のTRUE/FALSEを求める

複合条件をもう一度見てみよう。

エヴァでは綾波がすきで、かつ、ハルヒでは長門もしくはみくるがすき
 

ここで、条件を分解してみる。すると

  条件1.エヴァで綾波が好き   条件2-1. ハルヒで長門が好き   条件2-2. ハルヒでみくるが好き
の3つの個別の条件から、構成されていることが、分かる。そして、これらは全体として
複合条件 = 条件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
のように、直接、期待値を記述しても構わない。  

◯ステップ2:複合条件のTRUE/FALSEの結果を求める

ステップ2−1:ハルヒで、長門かみくるが好きな人はだれか、TRUE/FALSEを求める

次に、ステップ1の結果を使って、ハルヒで長門もしくはみくるが好きな人はだれか、 求めてみよう。 いま、

  F列には、条件2-1:ハルヒで長門が好き、   G列には、条件2-2:ハルヒでみくるが好き
という結果が、もとめられている。  

したがって、長門もしくはみくるが好き、というのは:

=OR($F3,$G3)
のようにかける(Aくんの場合。Bくん以下はコピペで求めればよい)。これを、I列に書いておこう。

ステップ2−2:エヴァで綾波が好きで、かつ、ハルヒで長門かみくるが好きな人は誰か、TRUE/FALSEを求める

前のステップ2−1において、I列に長門もしくはみくるが好きである、という結果は 求められている。また、ステップ1において、条件1:綾波が好きである の結果をすでに求めている。

だから、複合条件の結果は:

=AND($E3,$I3)
とかける(Aくんの場合。Bくん以下はコピペで求めればよい)。これを、J列に書いておこう。  

◯ステップ3:複合条件の結果のTRUEを数える

前のステップ2−2までに、複合条件の結果を得ることができた。いよいよ最後だ。 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
 


posted by もふもふ at 04:49 | ロンドン | Comment(0) | TrackBack(0) | カテゴリ: 技術 | このブログの読者になる | 更新情報をチェックする
この記事へのコメント
コメントを書く
お名前:

メールアドレス:

ホームページアドレス:

コメント: [必須入力]

※ブログオーナーが承認したコメントのみ表示されます。

この記事へのトラックバック
トラックバックURLは,"Trackback(x)"のリンクを押すと表示されます.
×

この広告は180日以上新しい記事の投稿がないブログに表示されております。