エクセルのフィルターで抽出した値は関数でカウントしよう！

通常、セルの数をカウントしたい場合、セルに入力されている値が数字なのか文字なのかによって使用する関数が変わります。

しかし、フィルターをかけたときは、値が数字であっても文字であってもSUBTOTAL関数でカウントすることができます。

SUBTOTAL関数は、関数の引数を変更するだけでカウント以外にも合計、平均、最小値、最大値などが計算できる便利な関数です。

本記事では、SUBTOTAL関数を使ってフィルターをかけた後に表示されているセルだけをカウントする方法を紹介していますので、参考にしてみてください。

フィルターをかけたセルをカウントする方法以外にも、エクセルのフィルターに関連する記事があります。

こちらの記事もぜひ参考にしてみてください。

エクセルのSUBTOTAL関数を使うとフィルターをかけたあとのセルだけをカウントできる

エクセルでフィルターをかけた後のセルだけをカウントしたい場合は、SUBTOTAL(サブトータル)関数を使います。

通常、セルをカウントしたい場合は、COUNT(カウント)関数やCOUNTA(カウントエー)関数を使います。

しかし、COUNT関数またはCOUNTA関数は、フィルターで非表示になっているセルもカウントされてしまうため、フィルターをかけたデータには使用することができません。

SUBTOTAL関数を使えば、フィルターをかけた後に表示されているセルだけをカウントすることが可能です。

SUBTOTAL関数の関数構文は、「=SUBTOTAL(集計方法,参照)」です。

集計方法に入力する引数は、計算したい内容によって特定の数字が決まっています。

主な集計方法の引数は、以下の通りです。

集計方法	集計の種類	引数で表示される関数名
1または101	平均値を計算	AVERAGE
2または102	数値の個数を計算	COUNT
3または103	空白以外のセルの個数を計算	COUNTA
4または104	最大値を計算	MAX
5または105	最小値を計算	MIN
9または109	合計値を計算	SUM

セルをカウントする場合は、数値の個数を計算する「2」または「102」、空白以外のセルの個数を計算する「3」または「103」を使います。

通常は、集計方法の数字が1桁の「2」または「3」を入力しますが、表データがグループ化されていた場合は、「102」または「103」の使用がおすすめです。

フィルターをかけた値のみを合計したい場合も、SUBTOTAL関数を使用します。
集計方法の1桁と3桁の違いは、こちらで詳しく解説していますので参考にしてみてください。

SUBTOTAL関数を使ってフィルターで抽出した値をカウントする手順

SUBTOTAL関数を使って、フィルターで抽出したセルだけをカウントする手順を紹介します。

今回は、文字が入力されているセルをカウントする手順で解説しています。
数字が入力されているセルだけをカウントしたい場合は、集計方法の引数を「2-COUNT」に変更してください。

エクセルのSUBTOTAL関数とCOUNT関数・COUNTA関数の違いと使い分け

フィルターをかけた後のセルの数だけではなく、データが入力されているセルの数をカウントしたいケースは意外と多いですよね。

セルの数を正しくカウントするために、この3つの関数の違いをしっかり覚えておきましょう。

SUBTOTAL関数

SUBTOTAL関数は、フィルターをかけた後のセルの数を数えたいときに便利な関数です。

前述で紹介したように、フィルターをかけなければすべてのセルの数をカウントすることも可能です。

集計方法を変えると、数値が入力されているセルも文字が入力されているセルも両方カウントすることが可能です。

また、カウント以外にも平均や最大値、最小値などを求めることができるので覚えておくと便利です。

COUNT関数

COUNT関数は、数値が入力されているセルだけをカウントしたいときに使用する関数です。

フィルターを設定していない場合や、同じ列に数字と文字が混在しているデータから数字が入力されているセルだけをカウントしたい場合は、COUNT関数を使います。

ただし、フィルターをかけた値だけをカウントしたい場合、COUNT関数は使用できないので注意してください。

COUNTA関数

COUNTA関数は、空白以外のセル、つまり数字・文字・記号など何らかのデータが入力されているセルをカウントしたいときに使用する関数です。

上記のように、空白以外の数字・文字・記号が入力されているセルをカウントすることができます。

ただし、数字と文字が混在していた場合、数字だけまたは文字だけのように分けてカウントすることはできません。

COUNTA関数もCOUNT関数と同様で、フィルターをかけた後に表示されているセルだけをカウントすることはできないので注意しましょう。

重複データを除いてフィルターをかけた値だけをカウントしたいときはIF関数とCOUNTIF関数を活用する

UNIQUE(ユニーク)関数とは、Office365またはExcel2021以降のバージョンで使用できる関数で、重複のないデータが簡単に抽出できる関数です。

これは、UNIQUE関数を使って重複データを除いた画像です。

Office365またはExcel2021以降のエクセルを使用している場合は、重複データを取り除ける便利な関数ですが、それ以外のエクセルを使用している場合はこの関数は使用できません。

UNIQUE関数はエクセルのバージョンによっては使用できないケースがあるため、今回はIF関数とCOUNTIF関数を組み合わせた方法を紹介します。

この画像の表のデータは、同じ色で塗りつぶされているものがすべて重複しています。

このデータのブロック名でフィルターをかけ、重複データを除いた支店数をカウントしてみましょう。

こちらの方法は作業用の列を作成して操作を行うため、それぞれの作業手順を分けて解説しています。
下記の見出しの手順をすべて行うことで、重複データを除き、フィルター後のセルの数をカウントすることができます。

データを結合する

はじめに、「ブロック名」と「支店名」を組み合わせたデータの中に重複するデータがあるかを確認するために、データの結合を行います。

IF関数とCOUNTIF関数を使って1度だけ出てきたデータを調べる

連結したデータには重複したデータが含まれているため、IF関数とCOUNTIF関数を使って1度だけ出てきたデータを調べましょう。

SUBTOTAL関数を使って重複していないセルの数をカウントする

先ほど表示した番号を使って、重複していないセルの数をカウントするためのSUBTOTAL関数を挿入します。

複数の関数を組み合わせているため少し難しく感じてしまうかもしれませんが、実際のデータのセル番地に置き換えるだけで重複データを除いたセル数がカウントできるので試してみてください。

エクセルのフィルターで抽出した値のカウントに関するQ＆A

エクセルのフィルターで全データのセルの数をカウントしたい場合はどうしたらいいですか？

データに挿入されているすべてのフィルターを解除したあとに、空いているセルに集計方法に「2」または「3」を指定したSUBTOTAL関数を挿入します。

集計方法は、数字のみをカウントしたい場合は「2」を、空白以外のすべてのセルをカウントしたい場合は「3」を指定してください。

フィルターをかけた場合は表示されているセルの数のみがカウントされますが、すべてのフィルターを解除すると全データのセルの数をカウントすることができます。

エクセルでフィルターをかけたあとのセルをカウントする関数を教えてください。

フィルターをかけた後に表示されているセルだけをカウントしたい場合は、SUBTOTAL関数を使います。

SUBTOTAL関数の詳しい使い方は、本文をご覧ください。

フィルターをかけたあとに重複データを除いたセルをカウントすることはできますか？

いくつか方法がありますが、本記事ではIF関数とCOUNTIF関数を組み合わせた方法を紹介しています。

重複データを除いてフィルターをかけた値だけをカウントしたいときはIF関数とCOUNTIF関数を活用するで詳しい手順を解説していますので、参考にしてみてください。

SUBTOTAL関数を使うと値が表示されているセルだけをカウントできる！

数字が入力されているセルや空白以外のセルをカウントしたいときは、COUNT関数またはCOUNTA関数を使用します。

しかし、フィルターをかけた後に表示されているデータだけをカウントしたい場合は、COUNT関数やCOUNTA関数は使えません。

フィルターをかけた後に表示されているセルをカウントしたい場合は、SUBTOTAL関数を使いましょう。

SUBTOTAL関数の集計方法を変えると、数字も空白以外のセルも両方カウントできるので、使い方を覚えておくと便利です。

最後に、フィルターをかけた後のセルの数をカウントする方法をおさらいしておきましょう。

COUNT関数やCOUNTA関数は使ったことがあっても、SUBTOTAL関数はあまり使ったことがないという方もいらっしゃるかもしれません。

しかし、フィルターをかけたセルをカウントしたい場合は、COUNT関数やCOUNTA関数は使えないので注意しましょう。

また、今回は重複データを除いたセルをカウントする方法も紹介していますので、ぜひ参考にしてみてください。

フィルターをかけたセルをカウントする方法以外にも、エクセルのフィルターに関する関連記事があります。

こちらの記事もおすすめです。