FILTER関数を使えば "様々な条件" でDATAを抽出することができます

FILTER関数は色々な抽出条件を設定することができます

"成績一覧表"から、条件に合うものを抜き出す

　FILTER関数はOffice2019やOffice365から追加された新しい関数です。

　この関数の最大の特徴は画期的な「スピル特性」です。

　FILTER関数は今までの関数にはなかった優れた特徴の"スピル特性" のおかげで「結果が複数になる条件」も簡単に設定できるようになりました。

"成績一覧表" から、「算数が80点以上」の条件で抜き出す

　下のような "一覧表" から、関数を使って特定の条件で抜き出すのは簡単なようで難しい作業でした。

　算数が80点以上の "DATA（行）" は複数あるので、そのままではVLOOKUP関数等は使うことができません。

　仮にVLOOKUP関数を使う場合には、項目を追加する必要があります。

　　　

VLOOKUPで「該当結果が複数ある」データを抽出して表示する - Excelの機能を活用して、事務作業の省力化や経営分析をする

"フィルター機能" で、算数の点数が「80点以上」の者を抜き出す

 　フィルター機能を使えば簡単な操作で、特定の条件で"絞り込んで表示"することができます。

　しかし、この方法の欠点は関数ではないので"元のデータが更新" された場合はその都度操作する必要があることです。

　　　

FILTER関数を使って、情報を抽出する

　FILTER関数を使って情報を"抽出" する方法は、フィルター機能を使って"絞り込んで表示"する方法とは異なり、情報が自動的に更新されます。

　また、"他の関数と組み合わせる"ことにより複雑な条件を設定することができます。

1. 「算数」か「理科」の"どちらかが85を超える"人を抽出
2. 「算数」と「理科」の"両方が85を超える"人を抽出
3. LEN関数と組み合わせて、「氏名」が"２文字ではない"人を抽出
4. DATE関数と組み合わせて、特定の"日付"のデータを抽出
5. DATE関数と組み合わせて、特定の"期間"のデータを抽出
6. MONTH関数と組み合わせて、特定の"月"のデータを抽出
7. SORT関数と組み合わせて、特定の"月のデータを大きい順"に並べ替える
8. FIND関数と組み合わせて、特定の"文字を含む"データを抽出する
9. COUNT関数と組み合わせて、特定の"曜日"のデータを抽出する
10. 抽出対象の「表」から"必要な項目だけ"を抽出する

１．「算数」か「理科」の "どちらかが85を超える" 人を抽出

　FILTER関数の指数は「=FILTER ( 配列 , 含む , 空の場合 ) 」になります。

• 指数 "配列" は、表全体に設定します
• 指数 "含む" は、「算数が85より大きい」と「理科が85より大きい」の２つの数式を「あるいは（OR）」 を意味する " ＋ " でつなげます。

　　　

数式がない部分にも「ゴースト」が表示される

　VLOOKUP関数などの関数を使って「結果」を表示させるためには、当然のことながら「表示させるセルには"数式"が入力されている」必要があります。

　FILTER関数には"スピル特性" があるので、「結果」が複数ある場合は数式がない部分にも溢れる（スピル）ように表示されます。

　抽出「結果」が増えれば自動的に拡張されて表示されます。　

　　　

２．「算数」と「理科」の"両方が85を超える"人を抽出

 　２つの条件の両方に合致したものを抽出する場合は、「かつ（AND）」を意味する "＊" でつなげます。

　　　

３．LEN関数と組み合わせて、「氏名」が "２文字ではない人"を抽出

　NOT関数を使うことにより「ではない」を条件にすることができます。

　FILTER関数の引数 "含む" に「 NOT(LEN(B3:B9)=2) 」を入力して"２文字ではない人" の条件を設定します。

　引数 "空の場合" には「"該当なし"」と入力して、条件に該当するデータがない場合に「該当なし」と表示するようにします。

　　　

４．DATE関数と組み合わせて、特定の"日付"のデータを抽出

　DATE関数を使うことにより「日付」を抽出条件にすることができます。

　「日付」の項目の条件を「 =DATE(2016,4,15) 」にして、特定の日付 "4月15日" を抜き出します。

　注意する点は、日付が "シリアル値" で表示されるので書式設定で希望する表示方法に修正してやります。

　　　

「セルの書式設定」で修正する

　FILTER関数で"抜き出した" データの「日付」は "シリアル値" で表示されるので「セルの書式設定」で変更します。

　　　

５．DATE関数と組み合わせて、特定の"期間"のデータを抽出

　２つのDATE関数を使うことで「期間」を抽出条件にできます。

　２つの日付の条件を "AND条件"の " ＊ " で結合して「期間」を条件にします。

　　①　4月1日以降「 >=DATE(2016,4,1) 」

　　②　4月末　　  「 <DATE(2016,5,1) 」

　　　

 ６．MONTH関数と組み合わせて、特定の"月"のデータを抽出

　MONTH関数を使うことで「月」を抽出条件にすることができます。

　「4月」を抽出する条件「 MONTH(B3:B:21)=4 」を設定します。

　　　

７．SORT関数と組み合わせて、特定の"月のデータを大きい順"に並べ替える

　SORT関数を使うことで、抽出した結果を "並べ替えて表示" することができます。

　SORT関数の引数は「 =SORT( 配列 , 位置 , 順序 , 基準 ) 」です。

• 配列 「 ＝FILTER(B3:D21 , MONTH(B3:B21)=4 , "無し" 」　4月分を抽出
• 位置 「３」　表の範囲の"3列目"
• 順序 「－１」（※ 降順、昇順の場合は"１"）

　　　

８．FIND関数と組み合わせて、特定の"文字を含む"データを抽出する

　FIND関数を使えば、特定の "文字列" を含む「データ（行）」を抽出することができます。

　FIND関数の構造は「 =FIND( 検索文字列 , 対象 , 開始位置）」となります。

　　引数 検索文字列を "コーヒー" にします。

　　引数 対象を " C3:C21"にします。

　そして、このFIND関数の結果が「ゼロにならない（＞０）」ことを "抽出条件" にします。

　しかし、今回のように列全体を指定した場合は、 "エラー（#VALUE!）" になってしまうのでIFERROR関数でエラーを回避します。　

　　　

９．COUNT関数と組み合わせて、特定の"曜日"を抽出する

　COUNT関数を使えば、指定した「日付」の範囲内の "金曜日 のデータだけ"を抽出する。

　TEXT関数で「日付」を "曜日に変換" して、抽出条件にします。

　　　

１０．抽出対象の「表」から"必要な項目だけ"を抽出する

　FILTER関数は引数 "配列" を「表全体」にすることで、スピル特性により「表の全ての項目」が表示されます。

　引数で「表示する項目を指定する必要がない」ので、逆に「表示する項目を指定すること」ができません。

　FILTER関数の引数 "配列" に指定した「表」の中から "必要な項目だけを抽出" するためには引数 "含む" に「配列リテラル」を使います。

引数 "配列" に「表全体」を指定すると、すべての項目が表示される

　　　

「横の配列リテラル」を使って、必要な項目だけを抽出する

　配列リテラルを使って、"真・偽値" を指定して引数 "含む" に使います。

横の"配列リテラル"

　　　

縦の"配列リテラル"

　※ 今回は関係ありません

　　　

３つの項目のうち「2」と「3」を"真" とする場合の「配列リテラル」

　　　

「商品名」と「金額」だけを表示

　引数 "配列" を「表全体」に、

　引数 "含む" に「配列リテラル」にすることで、指定した項目だけを抜き出すことができます。　　

　　　

条件を付けて２つの項目だけを抽出する場合は「ネスト構造」にする

　"条件をつけて 特定の項目だけ" を抜き出す場合には、"FILTER関数をネスト(入れ子) 構造"にします。

　FILTER関数で金額が "500,000以上" を抽出し、それをさらにFILTER関数で引数 "含む" に、"配列リテラルで抽出する項目を指定" します。

　　　

COUNTIF関数を使って、配列リテラルの作成を自動化する

　項目数が少ない場合は "配列リテラル" の記述は簡単ですが、項目数が多くなると面倒な作業です。

　また、"選択する項目 を変更" する際にも間違えないように記述するには神経を使います。

　COUNTIF関数を使えば簡単に "配列リテラル の作成"ができます。

FILTER関数を使って、「任意の項目だけ」を抽出する - Excelの機能を活用して、事務作業の省力化や経営分析をする

表示したい「項目名」を用意する

　COUNTIF関数を使って、あらかじめ用意しておいた「表示したい項目」と「表の全ての項目を比較」して "真・偽" 値を求めます。

あらかじめ、表示したい「項目名」を用意します

　抽出した結果を表示させたい場所に、あらかじめ「表示したい項目名」を用意しておきます。

　　　

COUNTIF関数で、"配列リテラル" を作成

　COUNTIF関数で "用意した項目" と、"元の項目" を比較することにより「配列リテラル」を作成します。

　この方法を使えば、表示したい項目を変更する際も"用意する項目名を変更"するだけで簡単に変更できます。

　　　

　このように、スピル特性をもつ "FILTER関数" を使えば、指定した条件に合うデータ(行)を抽出することができます。

k-ohmori9616.hatenablog.com