【Excel】SUBTOTALやAGGREGATEから呼び出せない関数で非表示セルを除外して集計する方法

フィルターや行の非表示などで、非表示になった行を除外して集計するには通常はSUBTOTALやAGGREGATEを使用します。

ただ、これらの関数では以下の関数以外では集計を行うことができません。

SUBTOTAL

AGGREGATE

今回は、SUBTOTALやAGGREGATEから呼び出せない関数で非表示セルを除外して集計する方法について説明していきます。

関数の実行手順

以下の表例を例に関数を実行する方法を説明していきます。

準備

1.表に「フラグ」列を追加し、各行に以下の数式を入力します。

基準セルは絶対に空欄にならないセルを入力します。

数式入力後はを「フラグ」列は非表示にしてもOKです。

=SUBTOTAL(3,基準セル)

表例で実行する場合は「基準セル」を「A2」にしてオートフィルで全行に入力します。

=SUBTOTAL(3,A2)

2.以下のセルを適当なセルにコピーします。

=LET(flgCell,フラグセル,
outRng,出力範囲,
result,FILTER(outRng,OFFSET(flgCell,,,ROWS(outRng))=1),
result
)

フラグセルには「手順1」で数式を入力した行の先頭セルを入力します。
出力範囲には出力するセル範囲を入力します。

表例で実行する場合は「基準セル」に「E2」、「出力範囲」に「B2:D6」と入力します。

=LET(flgCell,E2,
outRng,B2:D6,
result,FILTER(outRng,OFFSET(flgCell,,,ROWS(outRng))=1),
result)

3.実行後、非表示列以外のセルが表示されればOKです。

関数を実行する

1.「準備」の「手順3」で入力した数式の最終行の「result」を関数の引数として渡します。

表例で「mid関数」と組み合わて上位二文字を取得する場合は以下のようになります。

=LET(flgCell,E2,
outRng,B2:D6,
result,FILTER(outRng,OFFSET(flgCell,,,ROWS(outRng))=1),
MID(result,1,2))

実行結果は以下のようになります。