COUNTIF関数を使った条件判定とフィルター

TAKESAN

2024年7月13日 12:28

はじめに

この記事は初学者向けではありません。基本的な数式・関数の仕組みや、引数と戻り値、行・列などの用語を把握しており、テーブルと構造化参照を理解しているのが前提です。

追記：2024年7月19日

補足説明の記事を書きました。

比較演算による条件判定

おさらい

以前の記事です↓

ここでは、FILTER関数の第2引数が、行または列の可視/不可視を決める、真偽値か数値からなる配列である、というしくみを説明しました。その中で、引数の内容を明確にするために、参照先のテーブルに作業列を追加して、そこで条件判定の結果を表示させたのでした。

完全一致

FILTER関数の第2引数によく入れられるのは、たとえば

sushi_tabeta[ネタ]="まぐろ"

このように、判定したい列と、比較したいものとを等式で結ぶものです。単なる文字列と結んだ等式ですので、これは完全一致を判定します。上の式の場合、ネタ列にある文字列が《まぐろ》と一致する場合のみ、TRUEを返します。

部分一致とワイルドカード

しかし実務では、条件判定させたいのは完全一致だけではありません。たとえば、《大トロ》と《中トロ》を両方抽出したいような場合もあります。この時、="トロ"としてはいけません。そうすると、セル内容が《トロ》である時しかTRUEを返さないからです。

部分一致を判定したい場合は、ワイルドカードが用いられます。たとえば、
トロを含む文字列
を判定させたい場合、"*トロ*"のようにします。半角アスタリスクは、
どのような文字が何文字（0文字でも）あっても良い
という意味なので、"*トロ*"は、トロが文字列中のどこかにあれば当てはまると判定されます。
では、FILTER関数にワイルドカード入りの文字列を投入してみましょう。

エラーが出ましたね。FILTER関数では、ワイルドカードを受け付けてくれないようです。それもそのはず、

上手を見れば明らかなように、そもそも、シンプルに文字列同士を結んだ等式で、片側にワイルドカードを入れても、TRUEは返さないのです。※A列に数式を入れ、隣の列にFORMULATEXT関数で数式を表示させています

このように書けないのでは困りますね。ではどうすれば良いでしょうか。

文字列の検索

文字列検索関数

Excelのワークシート関数には、
文字列の中で文字列を検索する
ものがあります。FIND関数やSEARCH関数がありますが、ここではSEARCH関数を使ってみましょう。

SEARCH関数の引数は、

見つけたい文字列
探しに行く文字列

です（オプションは省略）。したがって今は、

"トロ"を
"中トロ"に探しに行く

というしくみです。返ってくるのは、
見つけたい文字列が、探しに行く文字列の何文字目から始まるか
の数値です。オプションを指定しない場合、探し始めるのは1文字目で、《トロ》は《中トロ》の2文字目から始まるので、返るのは2となる寸法です。

SEARCH関数を投入

これで部分一致が判定出来ました。では、FILTERに投入してみます。

上手く行きませんね。エラーが出ています。原因を探るため、SEARCH関数部分を抜き出します。

SEARCH関数の第2引数、つまり見つけに行く文字列に範囲を投入しても、関数は正常に働いているのが判ります。結果が配列となりスピルされているからです。ではどこがおかしいのかと言うと、SEARCH関数で見つからない、すなわち今で言えば、中トロ以外の文字列がセルにある場合、エラーを返すからです。そして、
FILTER関数の第2引数に投入する配列にエラーが属する場合、FILTER関数はエラーを返す
のです。明確にするために、作業列を使用したやりかたで見てみます。

ここでネタ判定列には、

=SEARCH(conditions[ネタ条件],[@ネタ])

この数式を入れています。上にある条件指定のテーブルのネタ条件には《トロ》を入れてあり、メインのテーブルのネタ列で、トロを探しているため、中トロ以外はエラーが出ています。そして、エラーは足してもかけてもエラーですので、FILTER関数の第2引数には、エラー入りの配列が投入される事となり、結果的にエラーが出力されるわけです。
実際、Microsoftの説明にも、

include 引数の値がエラー (#N/A、#VALUEなど) であるか、ブール型 (Boolean) に変換できない場合、FILTER 関数はエラーを返します。

FILTER 関数 - Microsoft サポート

このように書かれています。要するに、
行または列の可視/不可視が定義出来ない
のでフィルターも出来ないのです。

エラーハンドリング

これに対応するために、エラーハンドリングしましょう。定番のIFERROR関数を使います。いまは、見つからない場合に0を返すようにすれば良いでしょう。

上手く行きましたね。数式は

=IFERROR(SEARCH(conditions[ネタ条件],[@ネタ]),0)

こうです。等式による条件判定では真偽値が返りますが、SEARCH関数は数値を返します。

入れ子と可読性

これで上手く行きましたが、
IFERRORでSEARCHを包んでいる
ことによってごちゃごちゃしています。入れ子になったからですね。関数を包む事でエラーハンドリングしているので、あまり綺麗ではありません。たとえば、これをFILTER関数に直接投入すると、

=FILTER(
sushi_tabeta,
IFERROR(SEARCH("トロ",sushi_tabeta[ネタ]),0)
)

入れ子が深くなって見づらいですね。しかもこれは、1つの条件です。ここにANDやORを入れ込むとなると、かなり複雑です。何か他の方法は無いでしょうか。

COUNTIF関数による条件判定

新しいテーブル

ここで、説明のための別のテーブルを作成しましょう。

いつもの通り寿司の表ですが、今回は、スシローのメニューを使わせてもらいました。下記リンク先、スシローの公式サイトを参照して表にしてあります。

なお、テーブル名はsushi_tabetaですが、便宜上、テーブルを複製したりしているので、少し異なる場合があります（sushi_tabeta2のように）。また、スシローの価格は地域により異なり引用しにくいので、メニューとカロリーを表にしてあります。
※カロリーは、100mlあたりという表現もあるので、文字列データとしてkcal入りで入力しました

SEARCH関数でフィルター

では、先ほどやってみたSEARCH関数を使います。今、鮪の文字が入ったメニューでフィルターしたいとします。作業列を追加し、SEARCH関数を入力しましょう。

上手く行きました。鮪が含まれるメニューはゼロ以外が返り、含まれないメニューではゼロが返ります。ゼロが返るのは、IFERROR関数でエラーハンドリングをしているからです。

今は1つの条件だったので、条件を増やします。うなぎが含まれるメニューにしましょうか。

《うなぎ》が含まれるメニューは離れた所にありますので、条件の作業列を追加して、それに基づいてオートフィルターをかけました。2種類のメニューに対し、ゼロ以外が返ってきています。成功です。
では、

鮪
うなぎ

このどちらか（OR）が含まれるという条件を設定します。

前の記事で説明したように、FILTER関数の第2引数の要素は、ゼロが不可視でそれ以外が可視となるので、足してゼロ以外になればORは満たせます。だから単純に+で足せば良いです。後は、これを使ってFILTER関数でフィルターすれば良い、つまり、FILTER関数の第2引数で、OR条件列を参照してやれば良いのですがその前に、関数の基本的な説明として、FILTER関数の中で直接条件式を書いて加算させるやりかたがよく紹介されますから、それに倣ってやってみましょうか。

出来ました。……が、

=FILTER(sushi_tabeta[[ネタ]:[カロリー]],IFERROR(SEARCH("鮪",sushi_tabeta[ネタ]),0)+IFERROR(SEARCH("うなぎ",sushi_tabeta[ネタ]),0))

長い上に、関数を複数使って入れ子にして演算しているので、そうとう読みづらいです。特に、エラーハンドリングするためのIFERROR関数が読みづらくしています。もちろん、この読みづらさを避けるために、作業列を使ってそれを参照するやりかたを紹介したわけで、今あえて、1つの数式に全部を詰め込んだのは、SEARCH関数をエラーハンドリングすると途端に解りにくくなる事を見て頂くためでした。作業列を使うにしても、このエラー回避の構造は変わりません。

COUNTIF関数を使う

そこで、SEARCH関数では無く、COUNTIF関数を使います。
COUNTIF関数といえば通常、複数のセルが属する範囲を参照させ、そこに所望の条件を満たすセルがいくつあるか、を求めるような使いかたをします。返すのは数です。では、数を返す関数をどのように、
作業列を使って条件判定する
用途に使いましょうか。次のようにします。

COUNTIF関数の引数は、

調べに行く範囲
調べる条件

です。ですから通常は、

ネタ列に
鮪が含まれるセルはいくつあるか

このように使います。鮪が入るメニューは7つあるので、返って来るのは7です。しかし実際に入れた数式は

=COUNTIF([@ネタ],"*鮪*")

こうです。これは、

同じ行のネタ列にある範囲に
鮪が含まれる数

を返すような数式です。同じ行にある別の列の範囲は1つのセルです。そしてCOUNTIF関数は個数を返すので、結局この数式は、
0か1を返す
よう機能します。つまり、これ自体が、
条件判定を、まるで真偽値判定をするかのようにおこなえる
のです。COUNTIF関数が便利なのは、

ワイルドカードが使える
数値の判定にも使える

所です。これはかなりの強みです。SEARCH関数は文字列内検索が出来ますが、返すのは出現位置ですし、現れない場合はエラーを返します。また、数値の比較は出来ません。SEARCH関数（IFERRORで包む）と比較すると、

=IFERROR(SEARCH("鮪",[@ネタ]),0)

=COUNTIF([@ネタ],"*鮪*")

すっきりしていますね。

条件を増やす

条件を増やします。次のようです。メニューに

鮪が含まれる
うなぎが含まれる
えびが含まれる
たらが含まれる

この4条件を、または（OR）で結びます。それをFILTER関数に投入すれば、

=FILTER(sushi_tabeta[[ネタ]:[カロリー]],sushi_tabeta[OR条件])

作業列を使っているので、第2引数はシンプルです。また、OR条件の連結を見ると、

SUM関数で計算しています。前の記事では、等式で比較演算していました。結果はTRUEかFALSEで、それからなる範囲はSUM関数で合計出来ませんでした（いくつTRUEがあっても0になる）。しかし今は、COUNTIF関数を使っています。返すのは0以上の数値ですから、当然SUM関数で合計出来ます。条件が増えてもすっきりしますね。これはAND条件も同じです。

AND条件と数値比較

次の図をご覧ください。

これは、

ネタにえびが含まれていて
100kcal以上

のメニューをフィルターしています。なお、カロリーは数値にしてあります。下図を見ると判るように、えびが含まれていてもカロリーが低いものは、ちゃんと弾かれています。

作業列の数式を見てみましょう。まず条件2。

先ほど説明したように、COUNTIF関数は、数値の比較が出来ます。したがって、100kcal以上といった条件も判定出来ます。当然、SEARCH関数ではそのような事は出来ません。次にAND条件を見ます。

COUNTIF関数が返すのは数値ですから、PRODUCT関数が使えます。これは、入力された数値を全て掛け合わせる関数です（SUMが総和に対し、PRODUCTは総乗）。例は2条件ですが、もちろん条件を増やしても同じです。1つでもゼロが属していれば、結果はゼロになります。

条件指定用テーブル

応用しましょう。前の記事でやったように、条件を入れるテーブルを用意し、メインのテーブルからそこを参照させます。今回は、別シートに条件テーブルを作りました。

↑これを参照させます。※2024年7月14日追記：下図2枚、貼る画像を誤っていたので差し替え

そして、OR条件を定義した条件結合列をFILTER関数に参照させれば、

みごと、複数条件フィルターが完成しました。ポイントは、条件として投入する文字列にワイルドカードを付加出来る所です。COUNTIF関数の第2引数、つまり判定条件は、文字列で入力出来るからです。たとえば<>*鮪*とすれば、鮪が含まれていないという条件を指定出来ます。
もちろん、AND条件も指定出来ます。まず、条件5としてカロリー列の条件判定を追加します。

次に、条件結合を、SUMではなくPRODUCTにします。そうすれば、AND条件となります。

条件に>=100を入れれば、カロリーの値が100以上のものをフィルター出来ます。ここでは、えびが含まれていてカロリーが100kcal以上のフィルターにしましょう。

条件1と条件5以外は指定しないので、何が入っていても良いという意味でワイルドカードとしてアスタリスクを1つ入れておきます。更に、チーズが入ったメニューに絞り込みたいなら、*チーズ*なる条件を増やします。

えび、チーズが含まれていて、100kcal以上のメニューに上手く絞り込まれました。

OR/ANDの自動切り替え

このままでは、ORとANDを切り替えるために、わざわざ数式を書き換えなければいけないので面白くありません。改良しましょう。
まず、条件テーブルに、条件判定の列を追加し、入力規則でOR/ANDをリスト入力出来るようにします。

次にメインのテーブルの条件結合を、IF関数で分岐させます。

=IF(conditions[条件判定]="OR",
SUM(sushi_tabeta[@[条件1]:[条件5]]),
PRODUCT(sushi_tabeta[@[条件1]:[条件5]])
)

こうすれば、ORとANDを簡単に切り替えられますね。えびとチーズで実際にやってみます。

えび、チーズの**どちらか**が含まれているメニュー
※空白をセットすれば、条件判定は0になる（空白というメニューは無いから）
※空白を入れるのが気持ち悪ければ、未指定など専用の文字列でも良い

えびとチーズの両方が含まれているメニュー
※条件3と条件4は何でも良いのでワイルドカード
※条件5は何でも良いが、数値判定なので0より大とする

高度な条件判定と入力の省略

他にも、

条件の文字列を入れる
先頭一致や末尾一致、含まれていないなどの一致条件をリストで指定する

などを分離するといった事も出来るでしょう。わざわざ一致条件を*や<>などを入れて指定するのは面倒ですし。これらを駆使すれば、オートフィルターやフィルターオプション詳細設定で実行するような機能を、数式のみで実現出来るわけですね。一例を示します。

上図は、検索する文字列を記入して、一致条件をリストで選べば、COUNTIF関数への引数が自動的に作られるようになっています。ORなので、

鮪が含まれる
甘えびである
厳選で始まる
サーモンで終わる
カロリーが296である

これらのいずれかを満たすものがフィルターされます。
AND条件の場合は、

こういう感じです。ここでは、

えびで始まり
チーズを含み
200kcal以下

これを同時に満たすメニューがフィルターされました。他の条件は不要なのでアスタリスクを入れてあります。工夫すれば、色々なバリエーションの検索フィルターが作れる事でしょう。

終わりに

COUNTIF関数は通常、条件を満たすセルの個数を求めるために使いますが、本記事で説明したように、単一セルの条件判定を、エラーハンドリングをせず実施出来て、とても便利です。作業列化、構造化参照と組み合わせて可読性を高める事も出来ます。
実務でも応用する機会があるので、覚えておいて損は無いと思います。