FILTER関数によって生成されたスピル結果から特定の列を選択し、その列のデータを集計する方法

ExcelのFILTER関数は、特定の条件に基づいてデータを抽出する強力なツールです。この記事では、FILTER関数で生成されたスピル結果からINDEX関数を用いて特定の列を選択し、さらにその列のデータを集計する方法について解説します。

スピル結果から生成された列(スピル範囲の一部)をSUM、MAX、MIN、AVERAGE、といった関数で集計することができるようになります。

FILTER関数の基本

FILTER関数は、指定された範囲から条件に合致するデータのみを抽出する関数です。これにより、大量のデータから必要な情報だけを素早く取り出すことが可能になります。FILTER関数は、以下の引数を使用しています。詳しくはリンク先を確認してください。

=FILTER(配列, 含む, 空の場合)

例えば下記の式はA2:E28のリストからE列が「無」であるレコードを抽出する式です。

=FILTER(A2:E28,E2:E28="無")

以下は練習用の表です。

FILTER関数によるスピル結果から特定の列を取得するには、INDEX関数を使用します。INDEX関数は、指定された範囲内の特定の位置にあるデータを返す関数です。これをFILTER関数の結果に適用することで、求める列のデータを抽出することができます。

=INDEX(FILTER関数(～～),,列番号)

この式では、FILTER関数で抽出したデータの中から特定の列番号のデータのみを取得します。列番号は第三引数で指定します。

上記で例に挙げたFILTER関数の式をINDEX関数で囲み第三引数へ「4」と入力すれば金額の列のみが作成されます。

=INDEX(FILTER(A2:E28,E2:E28="無"),,4)

抽出した列のデータを集計するには、SUM関数やAVERAGE関数やMAX関数やMIN関数やCOUNTA関数などの集計関数を使用します。INDEX関数で取得した列をこれらの関数の引数として渡すことで、求める集計結果を得ることができます。

これまでに作成した式をSUM関数で囲みます。

=SUM(INDEX(FILTER関数(～～)～～))

すなわち以下のような形式になりますね。

=SUM(INDEX(FILTER(範囲, 条件),,列番号))

この式は、FILTER関数で抽出したデータの中から特定の列をINDEX関数で取得し、その列の合計を計算します。

上の例をSUM関数で囲えば下記の式になります。FILTER関数で抽出したレコードから4列目の値を用いて合計させる式です。

=SUM(INDEX(FILTER(A2:E28,E2:E28="無"),,4))

FILTER関数とINDEX関数、そして集計関数を組み合わせることで、複雑なデータから必要な情報を抽出し、効率的に集計することが可能になります。このテクニックは、大量のデータを扱う際に特に役立ちます。