エクセルでフィルターを設定した一覧から、表示されているデータだけの個数を数えたい場面は少なくありません。
担当者別の件数、在庫がある商品の数、期限が近い案件数などを確認する際に、単純なCOUNT関数では非表示行まで数えてしまうことがあります。
そこで役立つのが、フィルターで絞り込んだ行だけを集計できるSUBTOTAL関数やAGGREGATE関数です。
フィルター後の個数を数える基本は、SUBTOTAL関数を使う方法です。
文字列を数える場合は SUBTOTAL(103,範囲)、数値を数える場合は SUBTOTAL(102,範囲) を入力します。
この記事では、オートフィルターで表示されたデータの件数をカウントする方法、数式の意味、空白セルがある場合の注意点まで順番に解説していきます。
エクセルのフィルター後に個数を数える方法
それではまず、フィルターで表示されたデータだけを数える基本操作について解説していきます。
| 担当者 | 商品名 | 売上金額 |
|---|---|---|
| 田中 | ノート | 1200 |
| 佐藤 | ペン | 500 |
| 田中 | ファイル | 800 |
| 鈴木 | 付箋 | 300 |
たとえば担当者列を田中で絞り込んだ場合、表示されるデータは2件です。
このようなケースでは、集計用セルにSUBTOTAL関数を入力することで、絞り込み結果と連動した件数を表示できます。
SUBTOTAL関数による基本の数式
SUBTOTAL関数を使った基本のカウント方法を確認していきます。
サンプルデータで1行目が見出し、2行目から5行目までがデータである場合、商品名の個数を数える数式は次のとおりです。
=SUBTOTAL(103,B2:B5)
この数式では、B列の商品名が入力されたセルのうち、フィルターによって表示されている行だけを数えます。
103は非表示行を除外して、空白ではないセルを数えるための集計番号です。
フィルター前は4件と表示され、担当者を田中に絞り込むと2件に変わります。
通常のCOUNTA関数では、画面に見えていない佐藤さんや鈴木さんの行も数えられてしまいますが、SUBTOTAL関数なら表示状態を判定してくれます。
集計結果を表の最下部や表の外側に置くと、並べ替えやフィルター操作をしても数式範囲を確認しやすくなります。
オートフィルターを設定する手順
続いては、一覧表にオートフィルターを設定する操作を確認していきます。
まず、見出しを含むデータ範囲内の任意のセルをクリックします。
次に、エクセルのデータタブからフィルターを選択すると、1行目の見出しセルに下向きの矢印が表示されます。
担当者列の矢印をクリックし、すべて選択のチェックを外してから田中だけにチェックを入れましょう。
フィルターの対象範囲には、必ず列見出しを含めることが重要です。
見出し行がない状態で操作すると、意図しないデータを見出しとして認識することがあるため注意が必要です。
フィルターを設定した直後にSUBTOTAL関数の数式を入力すれば、条件を切り替えるたびに個数が自動で更新されます。
フィルターの件数表示を確認する場所
続いては、フィルター操作後に表示される件数の確認方法を見ていきます。
エクセルでは、フィルターを適用すると画面下部のステータスバーに、全件数と抽出件数が表示される場合があります。
ただし、ステータスバーの表示は選択しているセル範囲やエクセルの設定によって変わるため、常に同じ形で確認できるとは限りません。
そのため、業務用の表では数式を使って件数をセルに表示しておく方法が便利です。
セルに集計結果を作成しておけば、印刷時や共有時にもフィルター後の件数を明確に伝えられます。
【操作のポイント】件数を確認するだけならステータスバーも使えますが、報告用の一覧ではSUBTOTAL関数の結果を表内に表示しておくと安心です。
SUBTOTAL関数の集計番号と使い分け
続いては、SUBTOTAL関数で使用する集計番号と、数値や文字列の数え分けについて確認していきます。
| 集計番号 | 集計内容 | 主な用途 |
|---|---|---|
| 102 | 数値セルの個数 | 金額や数量の件数 |
| 103 | 空白以外のセル数 | 氏名や商品名の件数 |
| 109 | 合計 | フィルター後の売上合計 |
SUBTOTAL関数は個数だけでなく、合計、平均、最大値、最小値などをフィルターと連動して集計できます。
個数をカウントする目的では、102と103の違いを理解しておくことが特に大切です。
数値を数える102の指定
数値が入力されたセルだけを数える場合は、102を指定する方法を解説していきます。
売上金額がC2からC5までに入力されている場合、数式は次のようになります。
=SUBTOTAL(102,C2:C5)
102はCOUNTと同じように、数値が入力されたセルだけを対象にします。
たとえば売上金額が未入力の行がある場合、その行は件数に含まれません。
数量、単価、金額のように数値入力が保証されている列では、102を使うと自然な件数になります。
一方で、数字に見えても文字列として入力されているデータは数えられない可能性があります。
左揃えで表示されている数値や、先頭にアポストロフィが付いた番号は、文字列扱いになっていないか確認しましょう。
文字列も含めて数える103の指定
続いては、文字列を含む入力済みセルを数える103の指定を確認していきます。
氏名、商品名、受付番号など、数値と文字列が混在する列では103が役立ちます。
商品名がB2からB5に入力されているなら、次の数式を使用します。
=SUBTOTAL(103,B2:B5)
103はCOUNTAに近い働きをするため、文字列、数値、日付など、空白ではないセルをまとめて数えます。
何件のレコードが表示されているかを知りたい場合は、原則として空白の少ない列に103を指定する方法が向いています。
顧客名列に空欄が発生する可能性があるなら、必ず値が入る管理番号列や受付日列を参照範囲に選ぶとよいでしょう。
1から11と101から111の違い
続いては、SUBTOTAL関数の集計番号にある二つのグループについて解説していきます。
SUBTOTAL関数には、1から11までの番号と、101から111までの番号があります。
たとえば3はCOUNTA、103もCOUNTAに対応する番号です。
フィルターで非表示になった行はどちらの番号でも除外されますが、手動で行を非表示にした場合の扱いが異なります。
101から111の番号を使うと、フィルターで隠れた行だけでなく、手動で非表示にした行も集計から除外できます。
日常的なフィルター集計では103を選んでおくと、途中で不要な行を手動で隠した場合にも集計値がずれにくくなります。
【操作のポイント】レコード数を数えるなら103、数値が入った金額や数量だけを数えるなら102を選び、参照先には空欄が少ない列を指定します。
条件を絞り込んだ件数の集計例
続いては、担当者や状態などの条件でフィルターをかけた際の実践的な集計例を確認していきます。
| 案件番号 | 担当者 | 進捗 | 金額 |
|---|---|---|---|
| A001 | 田中 | 進行中 | 50000 |
| A002 | 佐藤 | 完了 | 30000 |
| A003 | 田中 | 進行中 | 70000 |
| A004 | 鈴木 | 保留 | 20000 |
案件一覧では、担当者と進捗を組み合わせて絞り込み、現在対応中の件数を確認する使い方ができます。
複数列のフィルターによる絞り込み
複数の条件を組み合わせたフィルター操作について解説していきます。
担当者を田中、進捗を進行中に設定すると、両方の条件に一致する案件だけが表示されます。
このとき案件番号列に対して次の数式を設定すると、表示中の案件数をカウントできます。
=SUBTOTAL(103,A2:A5)
案件番号は各レコードに必ず入力する運用であれば、空白による数え漏れが起こりにくい列です。
フィルター条件が一つでも複数でも、SUBTOTAL関数の数式は変更する必要がありません。
表示されている行だけを数える仕組みなので、条件を追加した後も同じ集計セルをそのまま使えます。
抽出件数と金額合計の同時表示
続いては、件数と金額の合計を並べて表示する方法を確認していきます。
案件数だけでは状況を判断しにくい場合、表示件数の近くにフィルター後の金額合計も配置すると便利です。
件数の数式 =SUBTOTAL(103,A2:A5)
金額合計の数式 =SUBTOTAL(109,D2:D5)
109はSUMに対応する集計番号で、非表示行を除外した合計を返します。
件数と合計金額を一緒に確認すると、案件の量と金額規模を同時に把握できます。
担当者別の実績、月別の受注、ステータス別の売上など、さまざまな一覧表に応用できる考え方です。
オートフィルによる数式のコピー
続いては、複数の集計項目に数式をコピーする際のオートフィルについて解説していきます。
たとえば月別の表で各月ごとに同じ形式の一覧があり、同じ列構成で集計欄を作る場合は、最初の数式をコピーして利用できます。
数式を入力したセルの右下にある小さな四角を下方向または横方向へドラッグすると、相対参照に合わせて範囲が調整されます。
オートフィル後は、参照範囲が意図した行や列に移動しているか数式バーで必ず確認しましょう。
固定したい参照がある場合は、ドル記号を使った絶対参照も活用できます。
【操作のポイント】複数条件で抽出しても、レコードごとに必ず入力される案件番号や管理番号を103で数えると、件数が安定します。
空白セルや非表示行がある場合の注意点
続いては、フィルターで個数をカウントするときに起こりやすい空白セルや非表示行の注意点を確認していきます。
| 管理番号 | 顧客名 | 備考 |
|---|---|---|
| 1001 | 山田商店 | 確認済み |
| 1002 | 名称未登録 | |
| 1003 | 高橋商会 |
顧客名や備考のように空欄があり得る列を参照すると、実際のレコード数より少なく表示されることがあります。
空欄がある列を参照した場合のずれ
空白セルがある場合のカウント結果について解説していきます。
顧客名列に空欄がある状態で、=SUBTOTAL(103,B2:B4) と入力すると、顧客名が入った2件だけが数えられます。
しかし、表のレコード数は管理番号が3件あるため、実際には3件として扱いたい場合もあるでしょう。
件数を数える基準列には、全レコードで入力が必須になっている列を選ぶことが基本です。
この例なら、管理番号列に対して=SUBTOTAL(103,A2:A4)を設定します。
管理番号が必ず発番される運用なら、顧客名や備考の入力状況に影響されず、正しい表示件数を得られます。
手動で隠した行とフィルター行の違い
続いては、手動で非表示にした行とフィルターで非表示になった行の扱いを確認していきます。
フィルターで隠れた行を除外するだけなら、3や103など、どちらのグループの集計番号でも対応できます。
一方で、行番号を右クリックして非表示にした行まで除外したい場合は、101から111の番号を選ぶ必要があります。
フィルターだけを考慮する例 =SUBTOTAL(3,A2:A100)
フィルターと手動非表示を考慮する例 =SUBTOTAL(103,A2:A100)
手動で隠した行も集計から外したいなら、103を使う方法が分かりやすい選択です。
部署別に一部の行を隠して確認する表では、この違いが集計結果に影響するため覚えておきましょう。
テーブル機能を使う場合の参照範囲
続いては、エクセルのテーブル機能とSUBTOTAL関数を組み合わせる場合について解説していきます。
データ範囲をテーブルとして設定すると、見出しにフィルターが自動で追加され、行が増えても表の範囲が拡張されます。
テーブルの集計行を有効にすると、列ごとに個数や合計を選ぶ操作も可能です。
データが毎月追加される一覧では、テーブル化しておくと数式範囲の修正を減らせます。
ただし、表の外にSUBTOTAL関数を置く場合は、集計行まで範囲に含めないよう注意が必要です。
【操作のポイント】空欄が起こりやすい列ではなく、管理番号など必須入力の列を103で数えると、抽出件数のずれを防げます。
COUNTIF関数やAGGREGATE関数との違い
続いては、SUBTOTAL関数とCOUNTIF関数、AGGREGATE関数の違いを確認していきます。
| 関数 | 主な役割 | フィルター対応 |
|---|---|---|
| COUNTIF | 条件に一致するセル数 | 非表示行も数える |
| SUBTOTAL | 表示中のデータを集計 | 対応する |
| AGGREGATE | 高度な集計とエラー除外 | 対応する |
どの関数を選ぶかは、条件そのものを数えたいのか、現在画面に表示されている行を数えたいのかで決まります。
COUNTIF関数で数えられる対象
COUNTIF関数による条件付きカウントについて解説していきます。
たとえば担当者が田中のデータを数えるなら、次の数式を使えます。
=COUNTIF(B2:B5,”田中”)
COUNTIF関数は、田中という条件に一致するセルを数えるため、フィルターを設定していなくても利用できます。
ただし、画面上で別の条件により行を非表示にしても、COUNTIF関数は元の範囲にある田中のデータをすべて数えます。
フィルター後に見えている行だけを数えたい目的では、COUNTIF関数だけでは対応できません。
条件別の総件数を求めたい場合はCOUNTIF、抽出後の表示件数を求めたい場合はSUBTOTALと覚えると使い分けやすくなります。
AGGREGATE関数による高度な集計
続いては、AGGREGATE関数を使う場面について確認していきます。
AGGREGATE関数は、SUBTOTAL関数よりも多くの集計方法を選べる関数です。
非表示行、エラー値、入れ子になった集計結果などを無視する指定ができるため、複雑な一覧で役立つことがあります。
数値の個数をフィルターと連動して数える例は、次のとおりです。
=AGGREGATE(2,5,C2:C100)
最初の2はCOUNT、次の5は非表示行を無視する指定です。
通常の件数確認ではSUBTOTAL関数の方が覚えやすく、エラー除外などが必要な場合にAGGREGATE関数を検討します。
複数の集計式が混在する表では、どの行を無視する設定かを他の利用者にも分かるようにしておくと安心です。
用途別に選ぶ関数の考え方
続いては、用途ごとに適した関数を選ぶ考え方を解説していきます。
商品名が特定の文字列である件数を、フィルターと無関係に数えたいならCOUNTIF関数が適しています。
フィルターで担当者や期間を変更しながら、その時点で表示されている行数を見たいならSUBTOTAL関数が適しています。
非表示行に加え、エラー値も除外したいなどの細かな条件があるならAGGREGATE関数が候補になります。
最初に確認したいのは、条件一致の総数か、画面に表示中の件数かという違いです。
目的を整理してから関数を選ぶことで、数式の作り直しを減らせます。
【操作のポイント】フィルター連動の件数にはSUBTOTAL、条件一致の総数にはCOUNTIF、より複雑な除外条件にはAGGREGATEを使い分けます。
まとめ エクセルのフィルターで個数をカウントする方法
エクセルのフィルターで個数をカウントするには、表示中の行だけを集計できるSUBTOTAL関数を使う方法が基本です。
文字列や管理番号など、空白以外のセルを数える場合は、=SUBTOTAL(103,範囲) を使用します。
金額や数量など、数値だけを数える場合は、=SUBTOTAL(102,範囲) を使用しましょう。
参照する列には、顧客名や備考のような空欄が発生しやすい項目ではなく、管理番号のように必ず入力される列を選ぶことが大切です。
フィルター後の売上合計も確認したい場合は、=SUBTOTAL(109,範囲) を組み合わせると、表示件数と金額を同時に把握できます。
また、条件に一致する全件数を知りたいだけならCOUNTIF関数、エラー値なども除外したい場合はAGGREGATE関数が役立つことがあります。
まずは一覧表にフィルターを設定し、表の外側にSUBTOTAL関数による件数セルを作ってみましょう。
抽出条件を切り替えるたびに結果が自動更新されるため、日々の確認作業を効率化できます。
