Excel COUNTIF関数とCOUNTIFS関数で条件に合致するデータの件数をカウント

はじめに

今回は、Excel関数の中でもトップ5くらいには重要な
COUNTIF関数
COUNTIFS関数
について扱う。

これらは超重要関数だけど、実際に頻繁に使うかと言われたら微妙な面もある。

私も今回の記事を使うに当たり、自分が作ったExcelファイルでCOUNTIF関数が使われてるものを探したが、意外と見付からなかった。

でも、いざというとき自由に使えないとお話にならない関数であるのは確かなので、必ず使えるようになろう。

COUNTIF関数を使うには、絶対参照・相対参照の考え方も必須になるので、必ず押さえておこう。

Excel 絶対参照・相対参照 SUMIF関数を題材にして語る

2022年4月9日

Sが付くCOUNTIFS関数は、Sが付かないCOUNTIF関数の完全な上位互換だと言い切れるから、本当はCOUNTIFS関数だけ紹介すればいいのかもしれない。

でもCOUNTIFS関数を使うまでもなくCOUNTIF関数のみで済むよって仕事の人も多いと思うので、一応COUNTIF関数から述べていく。

COUNTIFS関数はExcel2003までは使えなかった関数で、もう元号も令和になった今でもそんなことに配慮をしなきゃいけない人も、いるにはいるだろうしね。

COUNTIF関数の例題

COUNTIF関数は、条件に合うデータが幾つあるのかをカウントできる関数だ。

だから、
1万行のデータの中に「ABC」というデータが200件ありました
というように、何件ヒットしたというヒット件数をカウントできる。

・・・のだけど、私の感覚としては、そういう使い方はあまりしない気がする。
別に20件ヒットだろうが200件ヒットだろうが、そういうヒット数を問うことはあまりないということ。

私が考えるに、どちらかというと、「ヒット件数が0件が否か」つまり「有るのか無いのか」を調べるだけの使い方が多いのではないかと思う。

たとえば下図では、会社マスタの一覧表(①)と会社ごとの残高表(②)がある。
で、①の会社コードの中で②に登場しているもの(=残高がある会社)をチェックするといった場合に、COUNTIF関数が適している。

COUNTIF関数の構成は

というものだ。

それに従い、①会社マスタシートのC2セルに

と入れて、それをC列の縦方向にずっとコピペしていけば良い。

この例題なんかでは、②シートに書かれた会社コードが①シート中でヒットするかどうかだけ分かれば良い。
すなわちCOUNTIF関数の結果は「0かそれ以外か」だけが分かれば良い。

私がCOUNTIF関数を使う時は、こういうケースが多い。

大したことない例題だけど、例題のExcelファイルをこちらに添付しときます。

COUNTIF関数:文字列部分一致の例題

COUNTIF関数というか、ワイルドカードってやつの例題になるけど、COUNTIF関数とワイルドカードを使って文字列を部分一致で検索する例題。

ワイルドカードっていうのが、文字列のマッチング、要は部分一致の検索をする時に使うもの。

「」「?」「~」とかあるんだけど、とりあえず「」だけ覚えておけば良い。

この「*」が、任意の長さの任意の文字列を表すってことで、とにかく何か文字列が含まれる可能性があるときにこの記号を使えば良いってこと。

で、今回の例題のファイルでB列にある会社名について

「*」が任意の文字列を表すということで

条件を複数指定できるCOUNTIFS関数の例題

COUNTIF関数では、検索条件を1つしか指定できないが、それを複数指定できるように拡張したのがCOUNTIFS関数だ。

上記の例題では会社コードだけを検索キーにしてたけど、実務では更に、事業部とか課とかまで加わった複数のキーで検索することも多い。

下図が、C列の事業部、D列の課と検索キーを追加した例だ。
会社・事業部・課の3つが全て同じものが揃ったものを、②シートから検索するわけで、それにCOUNTIFS関数を使っている。

COUNTIFS関数の構成は、COUNTIF関数を単純に拡張しただけで

と、検索条件と範囲を幾つも延々と追加していける。

このCOUNTIFS関数の例題Excelファイルはこちらです。

SUMIF関数に対するSUMIFS関数は、ちょっと引数の順序がSUMIF関数と変わるので注意が必要だったけど、COUNTIFS関数は何も考えずCOUNTIF関数に引数を付け足していけば良い。

全てCOUNTIFS関数に一本化しても良いだろう。