COUNTIF関数とスピルは相性が悪い
本稿は、COUNTIF関数やSUMIF関数って、スピル系の他関数と組み合わせるときは、注意が必要だよって話です。このネタ、いつか書こうと思っていました。ああ、ちなみに、本稿では最も簡単なCOUNTIF関数を例にしますが、SUMIF関数でもCOUNTIFS関数でもSUMIFS関数でも、まったく同じことです。COUNTIF関数やSUMIF関数などが、他の関数たちと異なっているってことです。余談ですけど、よくセミナーなどで受講者から質問を受けていると
受「特定のデータだけ合計してるんです」
田「ああ、SUMIFをやってるんですね」
受「いや！違います！SUMIFSです！」
田「…(一緒だよっ!!!!)」
というやりとりを何度かしました。なんでしょう？みなさん、SUMIF関数とSUMIFS関数って、何か全く別の関数だと感じているのでしょうか。両者は、条件が単一か複数かの違いであって、やってることは同じなんですよ。しかも、カウントだろうが合計だろうが、重要なことは、Excelがどうやって(内部で)特定のデータを見つけているかです。なので、私の感覚では、COUNTIF関数とCOUNTIFS関数とSUMIF関数とSUMIFS関数は、どれも同じ仕組みの「ひとつの関数」みたいな認識です。
ということなので、本稿では最も記述が短くなるCOUNTIF関数を例にします。まずは、不思議な現象をご覧ください。いくつかの例をご紹介します。
合体させるとエラーになる
元データは1列だけのテーブル「Data」とします。複数列あっても同じことです。
まずは、このデータを、何らかの関数を使って、列ごと抽出してみます。まずはXLOOKUP関数から。
この関数の結果から"田中"の個数をCOUNTIF関数でカウントします。
うまくいきますね。では、2つの関数を合体してみましょう。
別々のセルに入力してうまくいくのですから、合体させても問題はありません。
結果：XLOOKUP関数＋COUNTIF関数→成功
では次。今度はFILTER関数で抽出してみましょう。手順は一緒です。
先と同じように、2つの関数を合体させてみます。
エラーです。てゆーか、入力できません。数式として認識されないんです。
結果：FILTER関数＋COUNTIF関数→エラー
列を抽出するのなら、TAKE関数も使えますね。
合体させます。
こちらは、問題ないようですね。
結果：TAKE関数＋COUNTIF関数→成功
列の抽出といえば、専用のCHOOSECOLS関数もあります。
合体させましょう。今度はどうかな。
結果：CHOOSECOLS関数＋COUNTIF関数→エラー
最後にINDEX関数を試してみましょう。ご存じない方が多いですけど、INDEX関数でも列を抽出できるんですよ。
いざ合体。
結果：INDEX関数＋COUNTIF関数→成功
今日は、このくらいで、かんべんしてやりましょうか。
COUNTIF関数は何を見ているのか
上記の結果をまとめてみましょう。
結果：XLOOKUP関数＋COUNTIF関数→成功
結果：FILTER関数＋COUNTIF関数→エラー
結果：TAKE関数＋COUNTIF関数→成功
結果：CHOOSECOLS関数＋COUNTIF関数→エラー
結果：INDEX関数＋COUNTIF関数→成功
これを見て、原因が分かる人いますか？原因について、自信を持って言える方は、関数について相当詳しい知識をお持ちですね。あなたとは、美味しい酒が飲めそうです。さて、どうして関数によっては合体させるとエラーになるのか、今回は詳し～く解説します。ただし、どうしても難しい話が(ほんの少し)含まれますので、そのへんは覚悟してください。
まず、COUNTIF関数が、ほかのVLOOKUP関数などと異なる点を、ひとつご紹介します。
A列には日付(シリアル値)を入力しています。このA列から"2025/9/3"をVLOOKUP関数で探すとエラーになります。ここは、シリアル値を指定しないと見つかりません。
毎回シリアル値を調べるのは現実的ではないので、こんなときは、DATE関数などを使います。
MATCH関数も一緒です。
では、COUNTIF関数は、どうでしょう。
COUNTIF関数は、VLOOKUP関数などと違い、関数の引数に記述した"2025/9/3"という文字列を、内部でシリアル値に変換してくれます。なので、シリアル値に変換可能な文字列形式であれば、たとえば"9月3日"などでもOKです。さて、これは、ちょっとした違いなのですが、本題はここからです。ややこしい話になりますので、超簡単なケースから始めます。
セルA1に入力されている値の"田中"から、LEFT関数で左1文字を抽出しています。LEFT関数の引数には「セル」を指定しました。しかし、次のように書いても抽出できます。
今度はLEFT関数の引数に、セルではなく"田中"という「値」を指定しました。これ、同じことなんて考えないでください。セルは、さまざまな情報を持っています。入力されている値だけではなく、アドレスや表示形式、塗りつぶしの色や条件付き書式などなど。LEFT関数の引数に「セル」を指定したとき、LEFT関数は、それら多くの情報の中から「値」だけを使いました。いわば「値」に変換してくれたんです。対して、引数に"田中"を指定したとき、この"田中"には"田中"という「文字列である」という情報しかありません。「値」は「値」でしかなく、それ以外の要素はありません。つまり、引数に"田中"を指定したときLEFT関数は、何も変換作業はしていないということです。この「変換するか」「変換しないか」と違いは、すごく重要なので覚えておいてください。
では、次のケースで考えてみましょう。
今度は引数に「A1:A3」を指定しました。これは3つのセル範囲ですから、この「値」は「{"田中","小原","佐倉"}」という配列になります。配列の要素数は3です。COUNTA関数がカウントしたのは「{"田中","小原","佐倉"}」です。だったら、先の"田中"と同じように、直接引数に記述しても、同じことになるはずです。
引数に「セル」を指定したときは変換してくれましたが、「値」を指定したときは必要がないので変換していません。では、これをCOUNTIF関数でやってみましょう。
COUNTIF関数は、「セル」を「{"田中","小原","佐倉"}」という配列に変換しました。でも、
COUNTIF関数に直接「値」を記述すると入力できません。そもそも、COUNTIF関数の第1引数には「セル」を指定しなければならず、直接「値」を指定することは、できないということです。COUNTIF関数には、こうした仕様があります。ここが、重要なポイントであり、COUNTIF関数などが、ほかの関数たちと異なる点です。さて、話は少し変わります。Excelには、「セル」を返す関数と、「値」を返す関数の2種類があります。両者の違いを調べるのは簡単です。関数が返す結果の"アドレス"を調べてやればいいんです。ここでは簡単に、ROW関数を使って行番号を調べてみます。行番号が表示されたら、関数は「セル」を返していて、エラーになったら「値」を返しています。
INDEX関数は、セル範囲A1:B3内で「2行目, 2列目」の"200"を返します。INDEX関数の結果をROW関数で調べてみましょう。
行番号の"2"が返りました。つまり、INDEX関数の返り値である"200"というのは、"200"という「値」ではなく"200"が入力されている「セル」でした。次に、VLOOKUP関数で調べてみます。
VLOOKUP関数も同じように"200"を返しました。行番号を調べてみます。
入力できません。ということは、VLOOKUP関数が返した"200"は、"200"が入力されているセルB2ではなく、"200"という「値」だったんです。このことから、INDEX関数は「セル」を返す関数であり、VLOOKUP関数は「値」を返す関数だと分かります。
ややこしくなってきましたので、ここまでの話をまとめます。
- Excelには「セル」を返す関数と、「値」を返す関数がある
- COUNTIF関数などは、第1引数に「セル」を指定しなければいけない
「セル」を返す関数のこと"参照型関数"と呼び、「値」を返す関数を"値型関数"といいます。
本稿を執筆している2025年9月現在、Excelには500以上の関数が搭載されています。そのうちの、ほとんどが「値型関数」です。セルそのものを返す「参照型関数」は、今のところ次の8個しかありません。
- INDEX関数
- OFFSET関数
- CHOOSE関数
- INDIRECT関数
- XLOOKUP関数
- TAKE関数
- DROP関数
- TRIMRANGE関数
さて、これを踏まえて、上記「合体するとエラーになる」の結果を、もう一度ご覧ください。なぜ、関数によってはエラーになるのか、ご理解いただけるはずです。
結果：XLOOKUP関数(参照型関数)＋COUNTIF関数→成功
結果：FILTER関数(値型関数)＋COUNTIF関数→エラー
結果：TAKE関数(参照型関数)＋COUNTIF関数→成功
結果：CHOOSECOLS関数(値型関数)＋COUNTIF関数→エラー
結果：INDEX関数(参照型関数)＋COUNTIF関数→成功
上記のように、列を抽出するには、さまざまな関数を使用できますが、その結果は異なります。FILTER関数やCHOOSECOLS関数で抽出した結果は"値"です。対して参照型関数であるXLOOKUP関数やTAKE関数などが返すのは"セル(への参照)"です。見た目は同じでも、返しているモノが違うんです。
実務では、両者の違いを意識しなければいけないケースって、それほど多くありません。ほとんどの場合は、どちらでも支障がないです。ただし、COUNTIF関数のように「引数にはセル(そのもの)を指定しなければいけない」ような場合は、注意が必要です。
代替案
COUNTIF関数やSUMIF関数は、引数に「セル(そのもの)」を指定しなければなりません。なので、FILTER関数やCHOOSECOLS関数などの計算結果を、引数に指定することはできません。では、どーするかと言えば、「条件を指定した計算」にCOUNTIF関数やSUMIF関数を"使わない"という発想で対処してください。ケースにもよりますが、ほとんどの場合はFILTER関数で対応できます。
"田中"の件数をカウントするのなら、FILTER関数で絞り込んだ結果の行数をカウントします。
"田中"の数値だけを合計したいのでしたら、FILTER関数が返す範囲を[数値]列にして、その結果をSUM関数で合計すればいいです。
COUNTIF関数やSUMIF関数は、とても便利な関数なので、こうした計算を一発で行えました。それに慣れている方が多いと思いますけど、何とか工夫してください。もちろん、一発で計算する手もあります。GROUPBY関数です。GROUPBY関数の詳しい解説は、下記のページをご覧ください。
解説は以上です。本稿はタイトルを「COUNTIF関数とスピルは相性が悪い」としました。これは、COUNTIF関数やSUMIF関数の引数にはセル(そのもの)を指定しなければならない→XLOOKUP関数やFILTER関数など、"スピルありき"で作られた新しい関数たちは、その関数の結果が"セル"なのか"値"なのかがわかりにくい→だから、COUNTIF関数やSUMIF関数に直接指定できないものもあるから注意してね、という意味です。"相性が悪い"という表現は、ちょっと大げさでしたかねｗ サムネ詐欺ならぬタイトル詐欺みたいで、なんか、すみませんです。