Excelで基準値外の値の個数を計算~CountifでOR集計出来る

Excelで条件マッチの個数をカウントする課題

Excelで計測値の集計シートを作る手伝いをすることになりました。

  1. シートのセルに計測した値を次々入力して行くと、基準値の範囲外のセルに色がつく様にして欲しい。
  2. 基準値の範囲外である個数を集計して欲しい。

この二つの要件に応じるには、二つのテクニックを使えば良いという事はすぐに思いついた。

1については、条件付き書式で容易に実現出来る。条件はセル範囲に対しても行えるし、複数のセル範囲にも行えるので活用すると便利だと思う。あくまでも見た目の操作になるので色を変えるとかフォントを変えるとか、入力値が基準内にあるのかどうかのチェックにはもってこいだ。

ちなみに空白セルは0として扱われるので、ある値以上、ある値以下で条件づけると以下にマッチしてしまう。0にマッチした場合には見た目変化しない様にする条件を定義する必要があり、ある値以上、ある値以下、ゼロの三つを定義した。

課題は、値がある値以上、ある値以下をチェックしてその個数をカウントする方だ。どうやって実現しようかなと思って適当に関数名をCountと入力すると「COUNTIF」という関数がサジェストされてきた。加えて「COUNTIFS」という複数条件を定義出来るCOUNTIFも有る事を知ったのでこれは行けそうだなと思った。

実際にExcelで関数を使って試してみる

具体的に例を挙げると、セルの値のチェックで、14以下、26以上を範囲外とする場合はCOUNTIFSでは思い通りに行かないというトラップにはまった。COUNTIFの複数条件対応版(高機能版)だと思っていたのだが、条件がアンド(and)になっているので、「以上または以下」というオア(or)条件には使えないという事に気づくまで少し苦戦した。

結局、下記の様な数式で、範囲外(以下、以上)の個数をカウント出来た。これはプログラマー的には当たり前なんだろうが、一般人には落とし穴なので将来の自分の為に記しておく。面倒くさいので以下、以上と表現しているが正確には「小なり<」、「大なり>」だ。以上、以下なら=<とか=>としてやる必要がある。

一つだけの条件(14より小さい)を満たすセルの数を数える

一つずつ関数の挙動を検証して行く意味もあって、「countif」で試してみる。

=countif(範囲,条件)
=countif(C1:N1,"<14")Code language: JavaScript (javascript)

複数条件(and)を満たすセルの数を数える

次に[countifs」を試してみる。

=countifs(範囲,条件,範囲,条件)
=countifs(C1:N1,"<14",C1:N1,">26")Code language: JavaScript (javascript)

範囲と条件をセットにしてやる必要があるので、同じ範囲でもその都度定義しなくてはならず冗長した感じは否めない。注意点としてはandでマッチングさせるので条件に全て合致したものをカウントする点がポイント。and条件(以下であり以上であること)を満たせないので結果は0個になるハズ。

※countifsは失敗例

複数条件(or)を満たすセルの数を数える

countifsという関数に期待したが今回の用途では使えないと分かった。そして条件にマッチした個数を足し算すれば良いという事に気づいた。

物事をシンプルに考えることを考えさせられた。ある値以下の結果とある値以上の結果の個数を単純に足せば良いと基本に立ち返った。

=countif(範囲,条件)+countif(範囲,条件)
=countif(C1:N1,"<14") + countif(C1:N1,">26")Code language: JavaScript (javascript)

これで14より小さい個数と、26より大きい個数を合計してカウント出来る

条件となる数値に直に値を指定するのではなく、セルを参照したいのであれば下記の様な感じになって、ちょっと読み辛くて非プログラマー的には嫌な雰囲気になるが、Excelの仕様上仕方がない。

=countif(C1:N1,"<"&A1) + countif(C1:N1,">"&B1)Code language: JavaScript (javascript)

以上で目的は達成出来たのでよしとしよう。

最後に

結局、最初にCOUNT関係の関数をみて使えるかも?と思った「COUTIFS」という高機能な関数は今回のケースでは使わ(え)なかった。シンプルな関数を組み合わせるという基本を再認識させられた気がする。

コメント

タイトルとURLをコピーしました