FILTER関数によって生成されたスピル結果から特定の列を選択し、その列のデータを集計する方法
ExcelのFILTER関数は、特定の条件に基づいてデータを抽出する強力なツールです。この記事では、FILTER関数で生成されたスピル結果からINDEX関数を用いて特定の列を選択し、さらにその列のデータを集計する方法について解説します。
スピル結果から生成された列(スピル範囲の一部)をSUM、MAX、MIN、AVERAGE、といった関数で集計することができるようになります。
FILTER関数の基本
FILTER関数は、指定された範囲から条件に合致するデータのみを抽出する関数です。これにより、大量のデータから必要な情報だけを素早く取り出すことが可能になります。FILTER関数は、以下の引数を使用しています。詳しくはリンク先を確認してください。
=FILTER(配列, 含む, 空の場合)
- 「配列」はフィルタリング対象となるデータ範囲です。
- 「含む」は論理式フィルタリング条件を指定します。条件に合致する場合はTRUE、そうでない場合はFALSEを返します。
- 「空の場合」は、条件に合致するデータがない場合に表示する値を指定します。ここでは説明を省略します。
例えば下記の式はA2:E28のリストからE列が「無」であるレコードを抽出する式です。
=FILTER(A2:E28,E2:E28="無")
以下は練習用の表です。
| 書籍番号 | 書名 | 発刊日 | 金額 | 在庫 |
|---|---|---|---|---|
| 1001 | ケーススタディ民法 | 2016/10/28 | 1400 | 有 |
| 1002 | 交通六法 | 2016/02/27 | 2700 | 無 |
| 1003 | 法人税法の基礎 | 2015/12/27 | 2700 | 無 |
| 1004 | 会社法 | 2014/10/27 | 2800 | 無 |
| 1005 | 民法総則 | 2015/03/27 | 2700 | 無 |
| 1006 | 判例六法 | 2016/01/27 | 2400 | 有 |
| 1007 | 基礎法入門 | 2014/09/27 | 2500 | 有 |
| 1008 | やさしい経済法 | 2015/08/27 | 2500 | 無 |
| 1009 | 刑法各論 | 2016/07/28 | 2100 | 有 |
| 1010 | 現近代民法 | 2014/10/27 | 1400 | 有 |
| 1011 | 会社法概論 | 2014/04/27 | 2000 | 有 |
| 1012 | 商法総則 | 2014/04/27 | 1900 | 有 |
| 1013 | 憲法 | 2016/09/28 | 2500 | 無 |
| 1014 | 商行為法 | 2015/11/27 | 1600 | 有 |
| 1015 | 倒産法入門 | 2014/01/27 | 2300 | 無 |
| 1016 | 破産法 | 2015/02/27 | 1200 | 有 |
| 1017 | 法社会学 | 2016/10/28 | 1500 | 無 |
| 1018 | 民事訴訟法学 | 2016/08/28 | 2800 | 有 |
| 1019 | 法学入門 | 2015/09/27 | 2500 | 有 |
| 1020 | 労働法の要点 | 2015/05/27 | 2400 | 無 |
| 1021 | はやわかり刑法 | 2016/05/28 | 3000 | 無 |
| 1022 | 講義式憲法 | 2016/10/28 | 3000 | 有 |
| 1023 | 刑法講義 | 2016/05/28 | 2600 | 有 |
| 1024 | 会社法 | 2016/09/27 | 1300 | 無 |
| 1025 | 民法事例集 | 2015/06/27 | 1200 | 無 |
| 1026 | 六法全書 | 2014/03/27 | 2300 | 無 |
| 1027 | 労働法実務辞典 | 2014/09/27 | 2700 | 有 |
スピル結果から特定の列を選択する方法
FILTER関数によるスピル結果から特定の列を取得するには、INDEX関数を使用します。INDEX関数は、指定された範囲内の特定の位置にあるデータを返す関数です。これをFILTER関数の結果に適用することで、求める列のデータを抽出することができます。
INDEX関数の使用例
=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関数、そして集計関数を組み合わせることで、複雑なデータから必要な情報を抽出し、効率的に集計することが可能になります。このテクニックは、大量のデータを扱う際に特に役立ちます。
コメント