AGGREGATE関数でもリストの最終行をフィルターに含めないようにするには？

Question

Excelで作成したリストの集計行（最終行）にSUBTOTAL関数を使用してリストの集計を行っています。

そのリストにオートフィルター機能でフィルターをかけると、集計行を除外してフィルターすることが出来ていました。

しかし、その集計行の関数をSUBTOTALからAGGREGATEに変換したところ、フィルターの範囲が集計行も含むようになってしまいました。

AGGREGATE関数を利用しながら集計行がフィルター範囲に含まれないようにすることは可能でしょうか。

公式のドキュメントではいずれの関数も「リストまたはデータベースの集計値を返します。」と同じ目的が謳われているため、AGGREGATEの時だけ集計行がフィルターに含まれてしまうのは違和感があるのですが、仕様なんでしょうか。

AGGREGATE 関数 (microsoft.com)
SUBTOTAL 関数 (microsoft.com)

■バージョン

Microsoft Excel for Microsoft 365 MSO (バージョン 2209 ビルド 16.0.15629.20196) 32 ビット

Windows 10 Pro 21H2

Answer 1

こんにちは。横合いから失礼します。

手元の環境（ Excel2021 と Excel2016 ）でも、AGGREGATE 関数の入っているセル（集計行）がフィルターのチェック項目一覧に表示されてしまうのを確認しました。

また、フィルターを変更するごとに自動的にそのセル（集計行）のチェックが外れてしまうことも確認しました。

＞集計行がフィルターに含まれてしまうのは違和感があるのですが、仕様なんでしょうか。

これが、不具合なのか仕様なのかについては良く分かりません。

ただ、AGGREGATE 関数については Excel2010 から追加された関数のようですので、その頃から不具合が放置されてきたとも考えづらく、仕様と考えたほうが良いのかもしれません・・・。

いずれにしろ、使い勝手が悪いということでしたら、改善を期待して「ファイル」の「フィードバック」から報告を上げてみてはいかがでしょう？。

＞AGGREGATE関数を利用しながら集計行がフィルター範囲に含まれないようにすることは可能でしょうか。

これについてですが、データの入っているセル範囲と集計行の間に空行を挿入することでフィルター範囲から切り離すことが可能になることを確認しました。

もし、こういった方法は既にお試し済みで他の方法をお探しなのでしたら、以下全て読み飛ばしてください。

※以下は、この方法の検証過程と結果です。

図１は、B6セルに下記の AGGREGATE 関数を入れ、B2セルを見出しとしてフィルターを設定した状態です。

=AGGREGATE(9,1,B3:B5)

・図１

図１の状態では、図２のように AGGREGATE 関数の結果（ここでは B6セルの内容）がフィルターの一覧に含まれてしまいます。

・図２

図３は、図１の状態から 5行目と 6行目の間に 1行挿入し、一旦フィルターを解除してから、改めて見出し行の B2セルのみ選択してフィルターを掛けた状態です。

・図３

図３の状態では、図４のように AGGREGATE 関数の結果（ここでは B7セルの内容）はフィルターの一覧に表示されなくなります。

・図４

図４で特定の数値のみ表示されないようにチェックを外してフィルターを掛けたとしても、AGGREGATE 関数としては問題なく機能し、表示されているセルのみの集計結果が表示されることを確認しています。

以下の組み合わせで動作確認しています。

・Windows11 と Microsoft Excel 2021 MSO (バージョン2209 ビルド 16.0.15629.20200) 64ビット

・Windows10 と Microsoft Excel 2016 MSO (バージョン2209 ビルド 16.0.15629.20196) 32ビット

余分な空行が必要になりますが、よろしければお試しになってみてください。

ご参考になれば幸いです。

Answer 2

返信ありがとうございます。

＞いずれにしろ、使い勝手が悪いということでしたら、改善を期待して「ファイル」の「フィードバック」から報告を上げてみてはいかがでしょう？。

AGGREGATE関数は未だに比較的新しい関数の印象を持ってましたが、リリースしてからもう結構経ってるんですよね。

SUBTOTAL関数は単に互換性のために残っているのかと思ってましたが、もしかすると別の存在意義があるのかもしれず、だからそれぞれ別の仕様が与えられていると理解できなくもないです。

が、紛らわしい事に変わりないので「フィードバック」してみます。

＞これについてですが、データの入っているセル範囲と集計行の間に空行を挿入することでフィルター範囲から切り離すことが可能になることを確認しました。

なるほど、言われてみれば当然ですが、フィルターをかけたいリストと集計用の行を強制的に切り離せばいいんですね。

区切りの空行を非表示にしておけば見た目上は「フィルター付きリスト＋集計行」の従来形式と変わりません。

一手間増えますがこれで対応してみます。

Answer 3

返信ありがとうございます。

用語が紛らわしく混乱させてしまってすみません。

今回は構造化されたテーブルは使用せず、構造化されていない素のリストを対象としています。

まさに3，4枚目の画像の状態です。

勿論、テーブルであれば集計行として正常に機能しますが、テーブル化しない状態で使いたいシーンも多く、今回の質問をした次第です。

sk.exeさんの画像の例で言いますと、12行目に入れているAGGREGATE関数をSUBTOTAL関数に変換すると、この12行目が自動的にフィルター範囲から除外されます。

従来はこの仕様を便利に使用していたのですが、この仕様だけはAGGREGATEで代替できない様です。

Answer 4

> その集計行の関数をSUBTOTALからAGGREGATEに変換したところ、> フィルターの範囲が集計行も含むようになってしまいました。

リスト（テーブル）の集計行のセルにではなく、
リスト（テーブル）化されていない範囲の最後の行のセルに
数式を記述されているからではないでしょうか。

> 公式のドキュメントではいずれの関数も「リストまたはデータベースの> 集計値を返します。」と同じ目的が謳われているため、AGGREGATEの時だけ> 集計行がフィルターに含まれてしまうのは違和感があるのですが、> 仕様なんでしょうか。

この場合、「 SUBTOTAL 関数を使用した数式セルについては、
テーブルではなく範囲を集計対象としていても暗黙的に集計行とみなし、
フィルターの対象からは除外する」ような挙動をしているとも
解釈できます。

（仕様なのかどうかについては私には判りかねます）