LWP | Excelの「フィルターで見えている行」を数式で判定する
Excelの「フィルターで見えている行」を数式で判定する
空白の売上行を置き去りにしない、SUBTOTALと表示フラグの使い方
Copyright © 2026 LWP 山中 一弘
本資料は、出典を明記いただければ、商用・非商用を問わず、ご自由に複製・改変・再配布していただけます。なお、著作権表示は改変せず、そのまま記載してご利用くださいますようお願いいたします。
ストーリー
東京の行だけを表示した売上一覧。フナさんは、売上がまだ入力されていない行も確認対象にしたいのに、数式の判定から外れていることに気づきました。
隣にいたココさんは、学校で学んだスプレッドシートの抽出を思い出します。二人は「セルの中身」と「行の見え方」を、小さな表で比べていきます。
1 見えているのに、数えられない？
図1｜「0だから非表示」と決める前に、参照しているセルを確かめます。
2 「0」には二つの理由がある
図2｜本当に空のセルと、数式が返す空文字列は区別します。
3 必ず入っているIDを数える
図3｜売上の有無から切り離し、全行に値があるID列を判定の足場にします。
図中の「（空）」は説明用の印です。実際の売上セルには、この文字を入力しません。
4 3と103は、手動非表示が違う
図4｜手動で隠した行を含めるかどうかで、集計方法の指定を選びます。
5 FILTERの条件と、画面の表示は別
図5｜元のセル参照で表示状態を判定し、その結果を抽出条件として渡します。
6 表示フラグを、次の処理へ渡す
図6｜確認対象を取り出せたら、残った未入力の売上を確認する仕事へ戻ります。
まとめ
今回の仕掛け
SUBTOTALの103は、非表示行を除き、空でないセルを数える指定です。参照先が売上セルでは、表示中でも未入力なら0になります。全行に通常の値が入ったIDセルを一つずつ参照すると、表示中を1、非表示を0として扱えます。
この判定に必要なのはIDの一意性ではなく、各行の参照セルが非空であることです。入れ子のSUBTOTALが入ったセルは判定の土台にしません。手動非表示を含める場合は3を選び、含めない場合は103を選びます。SUBTOTALの公式仕様
ここでいう空のセルは、値も数式も入っていないセルです。数式の結果が空文字列でも、COUNTAでは数えられます。COUNTAの公式仕様
数式例 売上セルとIDセルを比べる
01 |
|
|---|---|
02 |
|
数式の説明
1行目は売上セル、2行目はIDセルを一つ参照します。式を一括で同じ場所へ貼るのではなく、1行目をD2、2行目をE2へそれぞれ入力します。
入力・概要
A1・B1・C1にID・エリア・売上を置きます。2～5行目は順に「1・東京・100」「2・大阪・200」「3・東京・未入力」「4・大阪・400」です。C4は何も入れない状態にし、D2とE2の式をそれぞれ5行目までコピーします。エリア列を東京でフィルターします。
結果
表示されるID1はD2・E2とも1です。ID3は売上を数えるD4が0、IDを数えるE4が1になります。大阪の行は非表示となり、両方の判定が0になります。
読むポイント
図1のC4は、ID3の売上セルです。掲載値は公式仕様から求めた説明例で、今回の制作ではExcelでの実行測定は行っていません。
数式例 テーブルに表示フラグを持たせる
01 |
|
|---|
数式の説明
[@ID]は、同じテーブルの現在行にあるIDセルを参照します。各行で計算した1と0を、後の抽出に使う列として持たせます。
入力・概要
別の練習シートへ前の例のA1:C5だけを複写し、見出し付きのExcelテーブルにして、テーブル名を「売上一覧」にします。右隣にVisibleFlag列を追加し、最初のデータセルへ式を入力して全データ行へ反映します。比較用のD・E列の式は複写しません。
結果
東京だけを表示し、手動で隠した行がなければ、ID1とID3のVisibleFlagが1になります。フィルターで除かれたID2とID4は0です。
読むポイント
フィルターや手動非表示を変えた後は、再計算後のフラグを確認してください。手動で隠しただけで常に即時更新されるとは扱いません。
数式例 表示中のIDとエリアを抽出する
01 |
|
|---|---|
02 |
|
数式の説明
第1引数はID列からエリア列まで、第2引数はVisibleFlagが1の行という条件です。第3引数は該当行がない場合の表示です。2行で示していますが、一つの数式として入力します。
入力・概要
FILTERに対応するExcelを使います。上記テーブルの外に、結果が広がる空きセル範囲を確保して入力します。図6と同じく、売上列ではなくIDとエリアだけを抽出します。
結果
東京だけ表示した例では「1・東京」と「3・東京」の2行が返ります。売上が未入力のID3も対象です。VisibleFlagに1がなければ「該当なし」と表示します。
読むポイント
FILTERは与えた条件で抽出します。元の表の非表示状態を自動的な条件として期待せず、先に表示フラグへ変換して渡します。表の表示を変えたときは、フラグと抽出結果を確認します。[FILTERの公式仕様](https://support.microsoft.com/en-us/excel/functions/filter-function)
参照と代替手段の補足
値の配列だけでは、元のワークシート上でどの行が隠されていたかを表せません。元のセルを参照できる段階で表示判定を行い、その結果を値の配列として後の処理へ渡す順序が、この例の要点です。
VBAでは行全体のHiddenプロパティから非表示かどうかを調べられますが、その値だけではフィルターと手動非表示の理由を区別できません。AGGREGATEも集計の候補ですが、参照形式・配列形式・オプションにより扱いが異なり、この表示フラグの式へ無条件に置き換えるものではありません。
出典メモ
元ネタ：『Excelの「フィルターで見えている行」を数式で判定する』（2026年9月11日整理）。本記事はTakiLibを使用しません。人物の会話と場面は、技術を説明するための創作です。
主要仕様は本文中のMicrosoft公式資料と照合しました。補足資料：Range.Hidden、AGGREGATE。確認日：2026年9月15日。図の操作画面は概念図です。