オートフィルタで絞り込んだ結果を計算する
上図のような表があったとします。この表を「A列の[名前]が"田中"と等しい」で、オートフィルタしてみます。このとき、絞り込んだ結果の「件数」と、C列の「合計」を計算するには、どうしたらいいでしょう。
このように、シンプルな表でしたら、それほど難しくありません。次のようにします。
SUBTOTAL関数の詳しい解説は、下記ページをご覧ください。第1引数に指定する番号で、1番台と100番台の違いも詳しく説明しています。
ちなみに、SUBTOTAL関数の第2引数(計算する範囲)には、列全体を指定しています。なぜなら、表の大きさ(行数)が分からないからです。
計算範囲にエラーがあったら
実務は、一筋縄ではいきません。今回のような集計で、じゃ、もし合計を計算する範囲に「数式エラー」が含まれていたら。
SUBTOTAL関数は11種類の計算ができますが、実際には、それぞれの関数を呼び出しているに過ぎません。今回「計算の種類」に指定した"9"は、SUM関数を呼び出しています。もちろん、SUM関数の制約を超えることはできません。SUM関数は、計算範囲の中にエラーが含まれると、SUM関数自体がエラーになっちまいます。
こんなときは、SUBTOTAL関数ではなく、AGGREGATE関数を使います。AGGREGATE関数に関しては、まだ当サイト内で詳しい解説をしていませんので、使い方や特徴などは、下の動画をご覧ください。
こんな感じです。
列全体を指定できないとき
この手の「オートフィルタで絞り込んだ結果を計算する」的な解説をするときって、私だけじゃありませんけど、たいていは
のようなレイアウトを使います。つまり、オートフィルタで操作する列(ここではA列からC列)とは別の列(ここではE列からH列)に、SUBTOTAL関数などを入力します。理由は、先に書いたとおり。表の大きさが分からないのですから、SUBTOTAL関数では"列全体"を指定します。そうなると、もし表の下に何らかの文字列や数値が入力されていると、それが計算の対象に含まれてしまうからです。でも、実務では、次のようにしたいこともありますよね。
いや、むしろ、この方が自然です。でも、オートフィルタの対象となっている全体のセル範囲は分かりません。さらに、この手の表になると、データが増減するのが普通です。これ、もしVBAでしたら「Range("A1").CurrentRegion」などを使えば、オートフィルタの対象範囲を取得できるのですが、ワークシート関数だけでは無理です。いや、正直に言うと"裏技"を使えば、何とかなる場合もあります。せっかくですから、ご紹介しましょうか。
表にオートフィルタを設定すると、Excelは絞り込みの対象範囲を自動的に取得します。今回でしたら、セル範囲A1:C13です。このとき、Excelは内部で、このセル範囲に非表示の名前を設定します。オートフィルタって超複雑な処理ですから、おそらく、そのために必要なんでしょうね。その名前は「_FilterDatabase」です。ただし、シートレベルの名前ですから、前にシート名がついて「Sheet1!_FilterDatabase」みたくなります。セル範囲に設定された名前がわかれば、それを使って行数を調べることも可能です。
セルを編集状態にすると、正しく参照されているのが分かります。
表の行数が分かれば、オートフィルタの対象セル範囲だけを決め打ちできます。
まぁ、できるっちゃできますけど、このやり方はオススメしません。てゆーか、実務ではやらない方がいいです。本コンテンツを書くにあたって、あれこれ試してみましたが、表のデータが増減したり、オートフィルタのオン/オフをしたり、絞り込みを解除したりと、どんなタイミングで「_FilterDatabase」が更新されるかは不定です。通常の数式みたいに、セルの様子が変わったら、リアルタイムで変更されるものではありません。そりゃそうです。この非表示の名前は、Excelが内部で使うものであって、われわれユーザーが数式で使用することなど、微塵も想定していません。だから、こちらの望みどおりに動作しないのは当然です。この裏技を「何とか実務で応用しよう」ではなく、「これは実務で使えない仕組みだ」って考えてください。
では、どーするのか。簡単です。悩む必要はありません。表をテーブル形式にすればいいんです。それだけで解決です。
「テーブルって、よく分からない」という方もいます。でも、頑張ってください！分かってください！これからの時代、実務でExcelを活用するとき、知っておかなければならない技術が3つあります。「テーブル」「Power Query」「スピル」です。この3項目って、一般のExcelユーザーだけでなく、Excelを教えているトレーナーであっても「よく分からない」という人が多いです。そういう人は、よく分からないから「いいや、昔ながらの使い方で十分だしｗ」って逃げます。そういう声を聞くとガッカリしますし、心の中で「学ぶことをサボるな！」という気持ちになります。特に「テーブル」は必須です。これからのExcelでは、テーブルを"使わない理由はない"と言っても過言ではありません。
私のセミナーで毎回言っていますが、オートフィルタというのは、決して"ゴール"や"目的"ではありません。オートフィルタは、絞り込んだ後で、必ず何かをします。それは、絞り込んだ結果のコピー出会ったり、今回のように、絞り込んだ結果の計算などです。