FILTER関数でデータを抽出する:Excel関数


スポンサードリンク


作成:2020/2/3,更新更新:2023/3/15

FILTER関数で1列のデータを抽出する FILTER関数で配列のデータを抽出する
FILTER関数で複数条件でデータを抽出する
FILTER関数で日付データを抽出する COUNT関数とFILTER関数で曜日を数える
FILTER関数で指定した文字列データを抽出する FILTER関数で指定した列だけを表示する

FILTER関数が利用できるExcelの種類(バージョンについて)

FILTER関数がExcel for Microsoft 365 (サブスクリプション型)で使えるようになりました。
(Office 365は2020/4/22からは Microsoft 365となりました)
ちなみに、Excel2016(永続ライセンス版),Excel2019(永続ライセンス版)では使用できませんので、ご注意ください。
なお、2021/10/5頃から市販されている Excel2021(永続ライセンス版)では使用できるようです。

Spill(スピル) FILTER関数
Excel2016 × ×
Excel2019 × ×
Excel2021
Excel for Microsoft365

Filter関数が使えない環境では、作業列を使ったり、配列数式で代用ができます。
関数で複数データを取り出す:Excel関数の技 をご覧ください。

Filter関数はいろいろな使い方ができます、このページだけでは説明できないので以下のページでも説明していますので、ご覧ください。
FILTER関数で指定した列だけを表示する
条件を満たす値のカウントや合計をFILTER関数で計算する

定義した条件に基づいてデータの範囲をフィルター処理できます。   topへ
 フィルター
=FILTER(配列,含む,[空の場合])

FILTER関数の引数

引数の表現(日本語)がわかりにくいのでよく見てください。
配列 フィルターするデータの範囲または配列
含む フィルターする条件を書きます(フィルターで残すデータの条件を書きます)
空の場合 (省略可)フィルターした後にデータがない場合に表示するテキスト
(#CALC! エラーとなるときに表示する文字列を指定します)

FILTER関数で1列のデータを抽出する

下表の成績表を使って説明します。
B C D E F G H
2 No 氏名 国語 算数 理科 社会 合計
3 1 岡田 70 65 71 77 283
4 2 会田 55 61 66 48 230
5 3 河合 82 77 83 88 330
6 4 近藤 72 78 74 73 297
7 5 井上 61 55 52 65 233
8 6 木下 86 98 94 87 365
9 7 植田 68 78 75 66 287
10 8 佐々木 81 67 71 86 305
11 9 桑田 62 64 61 69 256

合計が300以上の合計のデータを抽出します。
J3セルに =FILTER(H3:H11,H3:H11>=300) と入力しました。
ここでは、抽出するデータを合計のセルとするので、引数の配列には H3:H11 とします。
抽出する条件は 合計が300以上なので H3:H11>=300 とします。
合計が300以上の「合計の値」が抽出されました。

データ範囲をテーブルに変換して、FILTER関数を使う例です。
テーブルの名前は 成績表 としています。
J3セルには =FILTER(成績表[合計],成績表[合計]>=300) と入力しています。

合計ではなく、氏名を抽出する場合は =FILTER(C3:C11,H3:H11>=300) とします。

テーブルの名前は 成績表 としています。
J3セルには =FILTER(成績表[氏名],成績表[合計]>=300) と入力しています。

FILTER関数を使って配列のデータを抽出する

C15セルに =FILTER(C3:H11,H3:H11>=300) と入力するだけでデータを抽出することができます。
配列にはタイトル行を除いたデータだけを指定します。C3:H11 としています。
含むの条件は 合計が300以上としたいので、 H3:H11>=300 としました。
合計が300以上のデータが抽出されました。

テーブルの場合、テーブルの名前は 成績表 としています。
J3セルには =FILTER(成績表[[氏名]:[合計]],成績表[合計]>=300) と入力します。

このデータを並べ替えたい場合はSORT関数と組み合わせます。
(合計の大きい方から順に並べ替えています)
=SORT(FILTER(C3:H11,H3:H11>=300 ),6,-1) としました。
SORT関数については SORT関数の使い方 をご覧ください。

FILTER関数を使って複数条件でデータを抽出する

[AND条件]でデータを抽出する(演算子 * を使う)

国語と算数のどちらも70を超える人を抽出します。
国語が70を超えるは D3:D11>70 と書くことができます。
算数が70を超えるは E3:E11>70 と書くことができます。
この2つの条件の AND条件で抽出したいので、論理積 *(アスタリスク) を使って (D3:D11>70 )*(E3:E11>70) と書きます。
FILTER(C3:H11,(D3:D11>70 )*(E3:E11>70)) でデータが抽出できます。
この抽出したデータを合計(6列目)の高い順(降順-1)に並べ替えるときは、SORT関数と組み合わせて、
=SORT(FILTER(C3:H11,(D3:D11>70 )*(E3:E11>70)),6,-1) と、します。

[OR条件]でデータを抽出する(演算子 + を使う)

国語と算数のどちらかが70を超える人を抽出します。
国語が70を超えるは D3:D11>70 と書くことができます。
算数が70を超えるは E3:E11>70 と書くことができます。
この2つの条件の OR条件で抽出したいので、論理和 + (プラス)を使って (D3:D11>70 )+(E3:E11>70) と書きます。
数式が =FILTER(C3:H11,(D3:D11>70 )+(E3:E11>70)) でデータが抽出できます。
この抽出したデータを合計の高い順に並べ替えるときは、SORT関数と組み合わせて、
=SORT(FILTER(C3:H11,(D3:D11>70 )+(E3:E11>70)),6,-1)
と、します。

テーブルの場合は、=SORT(FILTER(成績表[[氏名]:[合計]],(成績表[国語]>70 )+(成績表[算数]>70)),6,-1) とします。

[NOT条件]でデータを抽出する(比較演算子 <> を使う)

氏名が2文字ではない人のデータを抽出します。
条件は LEN(C3:C11)<>2 と書くことができます。
C15セルに =FILTER(C3:H11,LEN(C3:C11)<>2) と入力しています。
氏名が2文字でない、3文字の「佐々木」さんのデータを抽出できました。

[NOT条件]でデータを抽出する(NOT関数を使う)

氏名が2文字ではない人のデータを抽出します。
条件は NOT(LEN(C3:C11)=2) と書くことができます。
C15セルに =FILTER(C3:H11,NOT(LEN(C3:C11)=2),"該当なし") と入力します。
氏名が2文字でない、3文字の「佐々木」さんのデータを抽出できました。

FILTER関数で日付データを抽出する

2020/2/3 のデータを抽出したいときは =FILTER(B3:D17,B3:B17="2020/2/3") とすると、エラー #CALC! となります。

2020/2/3 のデータを抽出したいときは =FILTER(B3:D17,B3:B17=DATE(2020,2,3)) とします。
DATE関数を使って、シリアル値に変換します。 "2020/2/3"*1 としてもOKです。

月のデータを抽出する

下図のデータから、2月のデータを抽出しています。
F3セルの数式は =FILTER(B3:D17,MONTH(B3:B17)=2) としました。

SORT関数と組み合わせて、売上数の大きい順にデータを並べ替えることができます。
F3セルに =SORT(FILTER(B3:D17,MONTH(B3:B17)=2),3,-1) と入力しています。

テーブルに変換している場合、テーブル名は「売上表」としています。
数式は =SORT(FILTER(売上表,MONTH(売上表[日付])=2),3,-1) とします。

または、=FILTER(B3:D17,(B3:B17>="2020/2/1"*1)*(B3:B17<"2020/3/1"*1),"無し") とすることもできます。
または、=FILTER(B3:D17,(B3:B17>=DATE(2020,2,1))*(B3:B17<DATE(2020,3,1)),"無し") としてもOKです。

FILTER関数で指定した文字列データを抽出する

Filter関数ではワイルドカードが使えないようですので、文字列を扱う関数 LEFT,RIGHT,MID,FIND関数を利用します。

下図のデータから、氏名が 田 で終わる人のデータを抽出する場合はRIGHT関数を利用します。
C15セルに =FILTER(C3:H11,RIGHT(C3:C11)="田") と入力しています。

から始まる文字列の場合にはLEFT関数を利用します。
数式は =FILTER(C3:H11,LEFT(C3:C11)="木") とします。

を含む文字列を含む場合はFIND関数を利用します。
数式は =FILTER(C3:H11,IFERROR(FIND("河",C3:C11)>0,0)) とします。
検索条件に FIND("河",C3:C11)>0 としたら、K列のようにエラーとなるところが出てきます。
エラーを回避するために、IFERROR(関数を組み合わせて、IFERROR(FIND("河",C3:C11)>0,0) エラーの時は 0を返すようにしました。

COUNT関数とFILTER関数で曜日を数える

FILTER関数を他の関数と組み合わせて、計算に利用する例です。

下図のデータから、金曜日をカウントします。
金曜日という条件をTEXT関数を使って TEXT(B3:B11,"aaa")="金") としています。
数式は =COUNT(FILTER(B3:B11,TEXT(B3:B11,"aaa")="金")) としました。

他の関数を使う例:=SUMPRODUCT((WEEKDAY(B3:B11)=6)*1)

金曜日と土曜日をカウントします。
数式は =COUNT(FILTER(B3:B11,(TEXT(B3:B11,"aaa")="金")+(TEXT(B3:B11,"aaa")="土"))) としました。
他の関数を使う例:=SUMPRODUCT((WEEKDAY(B3:B11)=6)*1)+SUMPRODUCT((WEEKDAY(B3:B11)=7)*1)

FILTER関数で指定した列だけを表示する

条件に合致したデータをすべて(連続した列)で抽出するには
C15セルに =FILTER(C3:H11,H3:H11>=300)
と入力するだけで、下図のように氏名〜合計までの連続した列を抽出することができます。
では、合計が300以上のデータの、氏名と算数と理科と合計の不連続な列のデータを表示するにはどうすればよいのでしょう?

配列で表示する列を指定する方法

C14セルに =FILTER(FILTER(B3:H11,H3:H11>=300),{0,1,0,1,1,0,1}) と入力します。
B3:H11 の各列について、{0,1,0,1,1,0,1} と、表示する列は 1(TRUE)、表示しない列は 0(FALSE)と指定しています。

Googleスプレッドシートのヘルプに使い方の例(行の例です)として挙げてあります。
FILTER ソース範囲をフィルタ処理して、指定した条件を満たす行または列のみを返します。
https://support.google.com/docs/answer/3093197?hl=ja

COUNTIF関数で表示する列を指定する方法

タイトル行の有無をCOUNTIF関数で COUNTIF(C13:F13,B2:H2) と計算して配列 {0,1,0,1,1,0,1} を返すこともできます。
=FILTER(FILTER(B3:H11,H3:H11>=300),COUNTIF(C13:F13,B2:H2))
COUNTIF関数の使い方は 条件に一致するセルをカウントする(COUNTIF関数,COUNTIFS関数) をご覧ください。

COUNTIF関数の部分の結果を調べてみます。(デバッグしてみます)
数式の一部分 COUNTIF(C13:F13,B2:H2) を選択して、[F9]キーを押します。


配列 {0,1,0,1,1,0,1} が返されることがわかります。
確認できたら、[Esc]キーを押して数式を元に戻します。

[数式]タブの[数式の検証]を利用して確認することもできます。

COUNTIF関数を使う方法には欠点もあります。
列見出しが元のデータの列見出しと同じ順番であれば問題は無いですが、順番に違いがあると期待したデータが返されません。
下図のように、理科、算数の順番になっていても国語、算数、理科、社会の順番の時と同じ結果になっています。
理科と算数が元のデータの列見出しと順番が異なっていますが、COUNTIF関数では同じ配列が返されます。
よって、期待したデータが返されません。
このような場合は、Excel for Microsoft365でしか使えませんが、下記のCHOOSECOLS関数を利用してください。

CHOOSECOLS関数で表示する列を指定する方法

Excel for Microsoft365ではCHOOSECOLS関数が使用できるようになり、この関数を使っても列データを取り出すことができます。
FILTER関数で指定した列だけを表示する
指定された行・列の配列を返すCHOOSEROWS関数・CHOOSECOLS関数の使い方
をご覧ください。
FILTER関数で指定した列だけを取り出すのに、CHOOSECOLS関数が使用できるようになり、ちょっとスマートな感じの数式にすることができるようになりました。

C14セルの数式は =CHOOSECOLS(FILTER(B3:H11,H3:H11>=300),XMATCH(C13:F13,B2:H2)) としています。
FILTER(B3:H11,H3:H11>=300) では合計が300以上のデータを取り出しています。
この取り出したデータから、氏名、算数、理科、合計の列のデータをCHOOSECOLS関数とXMATCH関数で取り出します。
XMATCH(C13:F13,B2:H2)では氏名、算数、理科、合計の列の位置 {2,4,5,7} を調べています。
CHOOSECOLS関数でこの列の位置 {2,4,5,7} のデータを取り出しています。

理科と算数が元のデータの列見出しと順番が入違っていますが、CHOOSECOLS関数とXMATCH関数を組み合わせた場合は期待したデータを返すことができます。
XMATCH(C13:F13,B2:H2)で {2,5,4,7} が返されるからです。

スポンサードリンク



よねさんのWordとExcelの小部屋 »  エクセル関数一覧表 »  FILTER関数でデータを抽出する

PageViewCounter
Counter
Since2006/2/27