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関数→成功
これを見て、原因が分かる人いますか？原因について、自信を持って言える方は、関数について相当詳しい知識をお持ちですね。あなたとは、美味しい酒が飲めそうです。さて、どうして関数によっては合体させるとエラーになるのか、今回は詳し～く解説します。ただし、どうしても難しい話が(ほんの少し)含まれますので、そのへんは覚悟してください。
まず、関数って、セルの何を見ているかを考えてみましょう。下図のA列には、日付(シリアル値)を入力してあります。そして「ｍ月d日」という表示形式を設定しました。
この日付をVLOOKUP関数で扱ってみます。
VLOOKUP関数の"検索値"に「"10月3日"」という表示されている文字を指定したところ、エラーになりました。では、10月3日を表すシリアル値を指定してみましょう。
今度は見つかります。つまりVLOOKUP関数は、セルに表示されている文字ではなく、セルに入力されている値を探していると分かります。では、今度はCOUNTIF関数を試してみましょう。
VLOOKUP関数と同じように、検索値にシリアル値を指定したら1件カウントされました。COUNTIF関数も、セルに入力されている値を探しているようです。しかし、驚くべきことに「"10月3日"」のような文字列を指定しても見つかるんです。
指定したセル範囲A1:A4の中に「"10月3日"」という値が入力されているセルは存在しません。「"10月3日"」というのは、表示形式によって表示されているだけです。でも、そちらも見つかりました。つまりCOUNTIF関数は、セルに入力されている値と、セルに表示されている文字の両方を扱っていると分かります。
セルは多機能です。セルには、さまざまな情報が含まれていますが、ここでは、セルに「表示されている文字」と「入力されている値」のふたつだけを考えてみます。
VLOOKUP関数は、表示されている文字を指定するとエラーになり、入力されている値を指定したら正しく動作しました。つまり、VLOOKUP関数はセルに入力されている値を扱っていたわけです。
もっと正確に言うなら、VLOOKUP関数にとって必要なのは"値"です。たとえば次のように、VLOOKUP関数内に直接"値"を指定しても動作します。
では、COUNTIF関数はどうでしょう。COUNTIF関数は、セルに表示されている文字でも、セルに入力されている値でも、どちらも動作します。つまりCOUNTIF関数は、両者を両方とも扱っていたわけです。
これ、内部で2種類の処理を別々に行っているとは考えにくいです。ということは、COUNTIF関数って、両者を両方とも操作できる、上位の存在である「セル」そのものを扱っていると考えられます。
だから、先のVLOOKUP関数と同じように、関数内に直接"値"を指定するとエラーになります。「"値"じゃなく"セル"を指定してくれなきゃダメ」という意味のエラーです。
ここまでは、よろしいですか？「COUNTIF関数には、セルそのものを指定しなければならない」ということだけ理解してください。実はこれ、VBAを学習しているユーザーでしたら、別の表現をした方が分かりやすいと思います。なので、ちょっとだけVBA的な解説をします。VBAが分からない方は、読み飛ばしてください。セルに入力されている値って、Valueプロパティですよね。そして、セルに表示されている文字は、Textプロパティです。
VLOOKUP関数が扱うのはValueプロパティであり、そこで取得できるのはLong型やString型などの"値"です。
対してCOUNTIF関数は、ValueプロパティとTextプロパティの両方を操作したいので、それらのプロパティを持っているRangeオブジェクトを受け取ると。
もし、VLOOKUP関数やCOUNTIF関数を、VBAで実装するのなら、受け取る引数の型は次のようなイメージになるでしょう。
あくまでイメージです。もし本気で実装するのなら、もっと複雑になるでしょうけどｗ
関数は何を返すのか
大事な話なので、もう一度書きます。重要なポイントは「COUNTIF関数には、セル(そのもの)を指定しなければならない」ということです。さて、そこを理解していただいたところで、次に考えるべきは「関数は何を返すのか」です。
関数は大きく2つに分類できます。ひとつは、何らかの計算結果などを返す「値型関数」です。ふたつめは、別のセルを参照し、セルそのものを返す「参照型関数」です。本当は、もう少し複雑に分類できますが、ここでは、この2つを考えればいいでしょう。
本稿を執筆している2024年9月現在、Excelには519個の関数(隠し関数を含む)が搭載されています。こないだ、AからZまで、目でひとつずつ数えましたｗ そのうちの、ほとんどが「値型関数」です。セルそのものを返す「参照型関数」は、今のところ次の8個しかありません。
- INDEX関数
- OFFSET関数
- CHOOSE関数
- INDIRECT関数
- XLOOKUP関数
- TAKE関数
- DROP関数
- TRIMRANGE関数
TRIMRANGE関数は、今はまだInsiderでしか使えません。さて、これを踏まえて、上記「合体するとエラーになる」の結果を、もう一度ご覧ください。なぜ、関数によってはエラーになるのか、ご理解いただけるはずです。
結果：XLOOKUP関数(参照型関数)＋COUNTIF関数→成功
結果：FILTER関数(値型関数)＋COUNTIF関数→エラー
結果：TAKE関数(参照型関数)＋COUNTIF関数→成功
結果：CHOOSECOLS関数(値型関数)＋COUNTIF関数→エラー
結果：INDEX関数(参照型関数)＋COUNTIF関数→成功
上記のように、列を抽出するには、さまざまな関数を使用できますが、その結果は異なります。FILTER関数やCHOOSECOLS関数で抽出した結果は"値"です。対して参照型関数であるXLOOKUP関数やTAKE関数などが返すのは"セル(への参照)"です。見た目は同じでも、返しているモノが違うんです。
実務では、両者の違いを意識しなければいけないケースって、それほど多くありません。ほとんどの場合は、どちらでも支障がないです。ただし、COUNTIF関数のように「引数にはセル(そのもの)を指定しなければいけない」ような場合は、注意が必要です。
さて、このようにCOUNTIF関数やSUMIF関数などは、ちょっと異質です。なぜ、COUNTIF関数やSUMIF関数だけが、こんな仕様になっているのでしょうか。以下は私の推測です。そもそもCOUNTIF関数とSUMIF関数は、Excel 2002で実装された関数です。じゃ、それまで「条件を指定したカウント」や「条件を指定した合計」などを、どうやって計算していたかというと、使われていたのは「D関数」でした。
「D関数」というのは、DCOUNT関数やDSUM関数など、関数名の先頭に"D"がつく関数群のことで、通称「D関数」や「データベース関数」などと呼びます。ほかにも、DMAX関数やDAVERAGE関数などもありますね。これら「D関数」たちは、実務でたいへん重宝しました。しかし、上図のように「D関数」では、条件を"別のセル"に入力しなければなりません。この仕様に、多くのExcelユーザーが不満を抱いていました。使いにくいと。そこでMicrosoftは、一発で「指定した条件の計算」を行える関数を開発しました。それが、COUNTIF関数やSUMIF関数です。でも、困ったことに、それまで使われていた「D関数」は、「セルに入力されている値(Value)」だけでなく「セルに表示されている文字(Text)」も条件に指定できたんです。まぁ、便利ではあったのですけど。
「D関数」は、Excelの登場よりもはるか昔、MS-DOSの表計算として一世を風靡していたLotus 1-2-3時代から存在する関数です。すでに世界中で、多くの表計算ユーザーが使用していました。COUNTIF関数やSUMIF関数は、(一発で計算できるという面で)それまでのDCOUNT関数やDSUM関数の"上位版"です。であるなら、少なくとも「D関数」と同じことができなければなりません。そんな経緯で開発されたCOUNTIF関数とSUMIF関数ですから、こういう特殊な仕様になっているのではないかと。ある種の"Microsoftの苦悩"を感じられる関数です。
代替案
COUNTIF関数やSUMIF関数は、引数に「セル(そのもの)」を指定しなければなりません。なので、FILTER関数やCHOOSECOLS関数などの計算結果を、引数に指定することはできません。では、どーするかと言えば、「条件を指定した計算」にCOUNTIF関数やSUMIF関数を"使わない"という発想で対処してください。ケースにもよりますが、ほとんどの場合はFILTER関数で対応できます。
"田中"の件数をカウントするのなら、FILTER関数で絞り込んだ結果の行数をカウントします。
"田中"の数値だけを合計したいのでしたら、FILTER関数が返す範囲を[数値]列にして、その結果をSUM関数で合計すればいいです。
COUNTIF関数やSUMIF関数は、とても便利な関数なので、こうした計算を一発で行えました。それに慣れている方が多いと思いますけど、何とか工夫してください。もちろん、一発で計算する手もあります。GROUPBY関数です。GROUPBY関数の詳しい解説は、下記のページをご覧ください。
解説は以上です。本稿はタイトルを「COUNTIF関数とスピルは相性が悪い」としました。これは、COUNTIF関数やSUMIF関数の引数にはセル(そのもの)を指定しなければならない→XLOOKUP関数やFILTER関数など、"スピルありき"で作られた新しい関数たちは、その関数の結果が"セル"なのか"値"なのかがわかりにくい→だから、COUNTIF関数やSUMIF関数に直接指定できないものもあるから注意してね、という意味です。"相性が悪い"という表現は、ちょっと大げさでしたかねｗ サムネ詐欺ならぬタイトル詐欺みたいで、なんか、すみませんです。