はじめに
Excelでフィルターや手動非表示を多用する際、SUMやCOUNTが隠れた行まで計算して困ったことはありませんか？ SUBTOTAL関数を使えば解決しますが、集計の種類ごとに引数を変えるのは面倒ですし、複雑な条件（FILTER関数など）と組み合わせるのが難しいのが難点でした。
今回は、「表示されているかどうか」の真偽値（TRUE/FALSE）を配列で一気に取得し、あらゆる関数に応用する最強のテンプレートを紹介します。
VBA は使いません。
純粋にワークシート関数だけで実現します。
結論：この「型」だけ覚えればいい
=LET(
範囲, A:C,
表示行, BYROW(INDEX(範囲,,1), LAMBDA(r, SUBTOTAL(103, r) > 0)),
表示データ, FILTER(範囲, 表示行),
"ここに「表示データ」を使った計算式を書く"
)
2026/1/29 追記
LAMBDAが使えないバージョン用
=LET(
範囲, A:C,
各行, OFFSET(INDEX(範囲,1,1), SEQUENCE(ROWS(範囲)) - 1, 0),
表示データ, FILTER(範囲, SUBTOTAL(103, 各行)),
"ここに「表示データ」を使った計算式を書く"
)
何ができるのか
- オートフィルターの 現在の状態をそのまま反映
- 表示されている行だけを 常に最新の状態で抽出
- フィルター条件が変わっても式の修正は不要
- 対象外にしたい行はグレーアウトし、フィルターの「色で抽出」を使えば、
数式を変えずに簡単に除外できます
つまり、
「今、目で見えている行」＝「抽出結果」
という関係を作れます。
応用例
=LET(
範囲, A:C,
表示行, BYROW(INDEX(範囲,,1), LAMBDA(r, SUBTOTAL(103, r) > 0)),
表示データ, FILTER(範囲, 表示行),
"ここに「表示データ」を使った計算式を書く"
)
表示されているデータだけを合計
SUM(表示データ)
表示されている行だけで重複を除外
UNIQUE(表示データ)
文字列連結
TEXTJOIN(",", TRUE, 表示データ)
本来は非表示無視に対応していない関数も問題なく使えます。
このように、
- フィルター操作はシート側で行う
- 数式は「表示データ」を前提に組み立てる
という分離ができるため、
条件変更に強く、読みやすい構成になります。