FILTER関数を使って、「任意の項目だけ」を抽出する

FILTER関数を使えば、条件に合う項目を簡単に抽出することができます

FILTER関数で、任意のフィールド（項目）だけを抽出させるには工夫が必要です

　従来の関数は、「何かを表示させる」ためには必ずその場所に"数式"を入力する必要がありました。

　しかし、FILTER関数は全く新しい機能「スピル」によって、表示させたい場所には何も入力せず「空白セル」にしておくだけで、「結果」がスピル（溢れる）ように表示されます。

　数式は表示させたい"範囲"の左上の1カ所に入れるだけです。

　今までのように「絶対参照」「複合参照」に悩まされる必要もありません。

　しかし、FILTER関数も元データのフィールド（項目）全てを抽出する場合は簡単ですが、任意のフィールド（項目）を抽出する場合は少し複雑になります。

FILTER関数は「範囲」に指定した部分が抽出されます

　引数「範囲」を"表全体"に指定すれば、全てのフィールド（項目）が抽出されます。

　　　

表の一部分を抽出したい「範囲」に指定することもできます

　抽出したい範囲が「連続」している場合は、その部分を範囲指定するだけでそのフィールド（項目）だけを抽出することができます。

　　　

FILTER関数は、真か偽か（TRUE or FALSE）で表示するかしないかを決めています

　引数「条件（含む）」は、"真"か"偽"かの真偽値（TRUE・FALSE）によって表示するかどうかを決めています。

　この特性を使って任意のフィールド（項目）だけを抽出します。

連続していない「項目」を選択する

　「ミネラルウォーター」の一覧には「商品名」は不要なので「日付」、「顧客名」と、少し離れた「金額」の項目を抽出します。

　このように、離れている特定の項目だけを"抜き出す"ように指定するためには、FILTER関数の引数「条件（含む）」に真偽値（TRUE・FALSE）の配列を指定します。

　　　

「配列リテラル」で表示する「項目（フィールド）」を選択する

　配列リテラルはJavaScriptで用いられる形式です。

　FILTER関数の引数「条件（含む）」は真偽値（TRUE、FALSE）で表します。

　この引数を「配列リテラル」で書いてやることで、表示する「列（項目）」が選択できるようになります。

「配列リテラル」の書き方

　縦の配列　➡　" {} "の中に各要素を "；"（セミコロン）で区切って書きます

　　　

　横の配列　➡　" {} "の中に各要素を " , "（カンマ）で区切って書きます

　　※ 今回の場合は、こちらを使います

　　　

「横の配列」で引数"含む"を設定する

　「日付」、「顧客名」、「商品名」、「金額」だけを抽出します。

　TRUE（真）は「1」、FALSE（偽）は「0」で代用します。

　抽出したい項目を「１」、抽出しない項目を「０」にして配列リテラルを書きます。　

　　　

FILTER関数を「ネスト（入れ子構造）」にして、条件に合う任意の項目を抽出

　FILTER関数をネストして、引数「範囲」にFILTER関数で条件に合うもの抽出した結果を入れます。

外側のFILTER関数で任意の列を抽出し、内側のFILTER関数で条件「ミネラルウォーター」を抽出

　引数「範囲」を「ミネラルウォーター」を抽出する数式（入れ子）にし、引数「条件」を項目を選択する配列リテラルにします。

　　　

COUNTIF関数で、表示する「列」の選択を自動化する

　「配列リテラル」の数式の作成は手間がかかります、さらに抽出する項目名を変更するのは面倒な作業になります。

　COUNTIF関数を使って「配列リテラル」を作成することで、表示する列の選択を自動化することができます。

事前に見出しにする「文字列」を入れておく

　抽出したい項目名を「見出し」としてあらかじめ用意しておき、その見出し行をCOUNTIF関数の「引数」に使います。

　　　

COUNTIF関数の結果を「配列リテラル」に使う

　COUNTIF関数の結果を、配列リテラルの代わりにします。

　　　

商品名「ミネラルウォーター」を条件に、項目「日付」「顧客名」「商品名」「金額」が抽出されました

　　　

COUNTIF関数を使えば、「抽出する項目を変更」しても、数式の変更は不要

　用意する「見出し」の内容を変えるだけで、抽出される項目が自動で変更されます。

「商品名」を削除して、抽出する項目を３つにする

　抽出する項目を３つに減らしても、数式を変更する必要はありません。

　※ 抽出項目を"増やす"場合は、COUNTIF関数の引数「範囲」の範囲指定に注意

　　　

抽出する条件を「セル指定」にする

　これまでは、抽出条件を「="ミネラルウォーター"」のように文字列で指定してきましたが、抽出条件を「セル」で指定することもできます。

　引数「条件」を文字列ではなくセルで指定することで、「条件」の変更も引数に指定したセルの「内容」を変えるだけなので、数式を修正する必要がなくなります。

　　　

条件を入力セルに「入力リスト」を設定する

　引数"条件"を入力するセルに「リスト入力」の設定をすれば、さらに簡単に抽出条件を変更することができます。

　リスト入力の「元データ」の作成に、FILTER関数と同様に「スピル」特性を持つ、UNIQUE関数を使います。

UNIQUE関数で、リスト入力の「元データ」を作成

　UNIQUE関数でリスト入力の「元データ」を作成すれば、UNIQUE関数が持つ「スピル」特性により「商品名」の種類が増えても自動的に更新されます。

　　　

UNIQUE関数を使って、リスト入力の「元の値」を自動更新する - Excelの機能を活用して、事務作業の省力化や経営分析をする

引数を「テーブル名」で指定すれば、データが増えても自動対応

　FILTER関数の引数「範囲」や「条件」の範囲指定をセル番地ではなくテーブル名で指定すれば、構造化参照となり元データの行数が増えても、範囲指定をやり直す必要がありません。

引数「範囲」と「条件」の範囲指定を「テーブル名」で指定

　範囲指定を「B4:I30」のようにセル番地で指定すると、「28番目」のデータを追加した場合、「B4:I31」のように範囲指定をやり直す必要があります。

　範囲指定に"テーブル名"を使えば、構造化参照となりデータ（行）が増えてもテーブルが自動的に拡張され「テーブル名」は変わらないので数式を修正する必要がありません。

　　　

表を「テーブル化」して作業効率を上げる - Excelの機能を活用して、事務作業の省力化や経営分析をする

「構造化参照」でVLOOKUP関数の修正を不要にする - Excelの機能を活用して、事務作業の省力化や経営分析をする

　新しく追加された「FILTER関数」を使えば、必要なデータを簡単に抽出することができます。

　従来からある「オートフィルタ機能」でも同様のことは可能ですが、FILTER関数は「関数」であるため、「数式」を入力すれば任意の場所や別のシートにも表示させることができます。

　また、元データを変更すれば自動でその変更内容が反映されます。

　その他にも、オートフィルタ機能とは異なり、「元のデータ」を加工するのではなく、別の場所に抽出するので、同時に複数の異なる条件で表示させることができます。

　「オートフィルタ機能」にできて「FILTER関数」にはできないこともあるので、それぞれの長所を生かして使い分けてください。

　FILTER関数が持つ「スピル」特性は使いこなせば、今までVBAを使わなければできなかったようなことが可能になる画期的な関数です、積極的に使ってください。 

k-ohmori9616.hatenablog.com