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

スポンサードリンク

更新:2024/4/29;作成:2020/2/3

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

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

FILTER関数はExcel for Microsoft 365 (サブスクリプション型)、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

FILTER関数の使用例

合計が300以上の氏名を抽出します。
氏名を抽出する場合は
=FILTER(配列,含む,[空の場合])
配列に　C3:C11、抽出する条件は　H3:H11>=300　を入れて、
=FILTER(C3:C11,H3:H11>=300)　とします。

FILTER関数をテーブルで使用する例

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

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

C14セルに
配列にはタイトル行を除いたデータだけを指定します。C3:H11 としています。
含むの条件は合計が300以上としたいので、　H3:H11>=300　としました。
=FILTER(C3:H11,H3:H11>=300)　と入力するだけでデータを抽出することができます。
合計が300以上のデータが抽出されました。
このデータを並べ替えたい場合はSORT関数と組み合わせます。
Sort関数の引数は合計(6列目)の大きい方から順(降順-1)に並べ替えると指定しています。
=SORT(FILTER(C3:H11,H3:H11>=300 ),6,-1)　としました。
SORT関数については SORT関数の使い方　をご覧ください。

FILTER関数をテーブルで使用する例

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

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

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

国語と算数のどちらも70を超える人を抽出します。
国語が70を超えるは　D3:D11>70　と書きます。
算数が70を超えるは　E3:E11>70　と書きます。
この2つの条件の　AND条件で抽出したいので、*(アスタリスク) を使って　(D3:D11>70 )*(E3:E11>70)　と書きます。これは論理積と呼ばれます。
これを2つ目の引数に入れて、
=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)

スポンサードリンク

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

PageViewCounter
Counter
Since2006/2/27