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

Excel これができなきゃ給料泥棒

スポンサーリンク

はじめに

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

について扱う。

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

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

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

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

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

2018年5月29日

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

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

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

COUNTIF関数の例題

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

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

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

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

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

COUNTIF関数の構成は

    =COUNTIF(範囲, 検索条件)


というものだ。

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

    =COUNTIF(②残高!$A:$A, $A2)


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

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

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

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

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

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

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

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

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

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

  • 「合資会社」で終わる
  • 「昭和」を含む
  • 「株式会社」で始まる
という3つの検索方法で、ヒットが何件あるかを調べるのにCOUNTIF関数とワイルドカード「*」を活用したものが、下図のようになる。

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

ワイルドカード「*」の使い方
「○で終わる」→ *
「○を含む」 → **
「○で始まる」→ ○*
となる。

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

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

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

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

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

    =COUNTIFS(範囲01, 検索条件01, 範囲02, 検索条件02, 範囲03, 検索条件03 ・・・・・・)


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

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

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

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

Excel2007以降のSUMIFS関数 ―SUMIF以上だがSUMPRODUCT未満―

2018年3月17日

COUNTIFS関数:数値の条件を追加してみた例

では上記のCOUNTIFS関数の例題で、会社コードごとに250,000円以上のデータが幾つ該当するかをチェックするという例を考えてみる。

そういう分析をするケースも実務でたまにあり、そういうときもCOUNTIFS関数が役に立つ。

で、そういう「250,000円以上」とかいう固定条件は、数式の中に書き込んだりするのはスマートさを欠くので、どこか作業用セルを作ってそこに書いてしまおう。

「以上」という条件は不等号の記号を使って「>=」と書けば良く、250,000円以上というのを関数の指定条件として書くなら「>=250000」と書けば良い。

それをG1セルに書いてみたCOUNTIFS関数が、下図のようになり、②シートのE列(残高)を$G$1セル(250,000円以上)と照合するようになっている。

スポンサーリンク