この記事では「エクセルにてフィルターをかけ個数や件数をカウントする（見えている数を数える）方法」について解説していきます。
方法としては下記のようなものが一般的に利用することになります。
それでは、具体例を用いて解説しましょう。
エクセルにてフィルターをかけ可視セルをカウントする方法1【SUBTOTAL関数】
まずはSUBTOTAL関数を使用し、フィルターをかけ見えているセルをカウントする方法をお話しします。
下記サンプルは、プレーヤー10人とそのスコアが記録されたリストです。
ここから、特定の条件に該当するスコアのプレーヤーの人数を数えるというのが、今回の想定です。
セルB12に「=SUBTOTAL(2,B2:B11)」 と入力します。
引数は数値とセル範囲なので、”(ダブルクォーテーション)で囲まないようにしましょう。
なお、見やすくするために、セルの背景色を変えていますが、してもしなくても結果は同じです。
この関数の意味を解説していきます。
SUBTOTAL関数は、フィルター機能により抽出されたデータに対し複数の行う場合に使用され、
と入力します。
各引数には
・集計方法(2)：集計方法(後述)
・参照(B2:B11)：対象のデータ、セル範囲でもOK
をそれぞれ指定します。
集計方法で適用できる値は下記の通りです。
1:平均値
2:数値の個数
3:データの個数
4:最大値
5:最小値
6:積
7:不変標準偏差
8:標本標準偏差
9:合計値
10:不変分散
11:標本分散
今回の対象は「数値の個数」に相当するので、2を使用します。
ENTERを押すと、現在表示中の数値の個数=10が出力されます。
ここで、フィルターを設定してみましょう。
リストのタイトル(サンプルデータではセルA1,B1が該当)を選択している状態でリボンより「データ」-「フィルター」をクリックします。
タイトルであるセルA1,B1に▼が表示されていればOKです。
ここでSCOREの▼をクリックし、「数値フィルター」-「指定の値以上」をクリックします。
「カスタムオートフィルター」ダイアログが開くので、抽出条件を設定します。
設定可能な範囲で任意ではありますが、下記では「30以上」を設定しています。
OKを押すと、SUBTOTAL関数の出力値が10→7へと変化しました。
ここまでできたら、本題クリアです。
このようにして、フィルターをかけた可視セルのカウントが完了となります。
エクセルにてフィルターをかけ個数をカウントする方法２【テーブル使用】
テーブルを利用して見えているセルをカウントする方法を解説します。
なお、サンプルデータは方法1と全く同じです。
リボンより「挿入」-「テーブル」をクリックします。
「テーブルの作成」ダイアログが開くので、テーブルに変換するデータ範囲をドラッグし、OKを押します。
なお、下記ではタイトル行が存在しているので、「先頭行をテーブルの見出しとして使用する」にチェックを入れてあります。
セルに背景色が自動で設定され、かつフィルター機能で目にしたような▼が表示されていれば成功です。
次に、リボンより「テーブルデザイン」-「集計行」をクリックし、チェックを入れます。
「テーブルデザイン」が表示されていない場合、カーソルがテーブル内のセルにあるか、確認しましょう。
初期状態では「合計」が適用されますが、これを「個数」に変更します。
集計値が出力されたセルにカーソルを当てると、▼が表示されます。
ドロップダウンより「個数」を選択します。
サンプルデータにおいて、データ数は10個なのでこれでOKです。
任意で抽出条件を設定し、該当したデータ数が出力されることを確認しましょう。
設定は方法1で紹介したテーブル機能と同様です。
エクセルにてフィルターをかけ個数をカウントする方法3【VBAマクロ使用】
最後にVBAマクロを利用しフィルターをかけたセルの個数を数える方法を紹介します。
Alt+F11同時押しでマクロを作成するためのエディタを開くことができます。
下記のような「Microsoft Visual Basic for Applications…」というタイトルのウィンドウが開けばOKです。
左端のツリー上で右クリックし、コンテキストメニューより「挿入」-「標準モジュール」を実行します。
白紙のエディタが開いたら、下記コードを記述してください。
Sub Work()
Dim rng As Range
Dim k As Integer
For Each rng In Range(“B2:B11”).SpecialCells(xlCellTypeVisible)
k = k + 1
Next
Range(“D1”) = k
End Sub
コードの意味について解説します。
Dim …は変数の宣言で
Dim (変数名) As (型)
と記述します。
For Each ~ Nextはループ処理となり、指定した条件に基づきループが行われます。
Rangeはセルまたはセルの範囲を意味し、
Range(A1) →セルA1
Range(A1:A10) →セルA1, A2, …A10
と記述します。
SpecialCellsは指定した条件に一致するセルを抽出します。
ここでは「xlCellTypeVisible=表示状態である」を指定しています。
上記をまとめると
・B2:B11において表示状態である→抽出されたデータ1つにつきkを1加算
・その結果をセルD1へ書き出す
といったところになり、結果的に抽出されたデータの個数を数えることになります。
コードが記述できたら、エディタを閉じ、シート上でAlt+F8を同時押しします。
作成したマクロ「Work」を選択し、実行をクリックします。
コードで記述した通り、セルD1に個数が書き出されます。
任意で抽出条件を変更し、抽出されている個数が書き出されることを確認しましょう。
まとめ エクセルにてフィルターをかけ見えている個数をカウントする方法
この記事では「エクセルにてフィルターをかけ個数をカウントする方法」について解説しました。
特定の条件に該当するデータの個数、合計値などを知りたいケースはビジネスの世界ではよくあることですが、エクセルはこのような非常に強力な機能を備えています。
この記事を読んだことで、また1つ、新たな知識が身についたとなれば、とても幸いです。
コメント