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

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

Excelで計測値の集計シートを作る手伝いをすることになりました。やりたいことを聞いてみるとExcelで実現したいことは下記の2点でした。

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

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

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

ちなみに空白セルは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をコピーしました