-
Home »
-
エクセル関数一覧表 »
-
Excel関数の目次 »
-
FILTER関数でデータを抽出する
更新:2024/12/03;作成:2020/2/3
FILTER関数が利用できるExcelの種類(バージョンについて)
FILTER関数の引数
- 引数の表現(日本語)がわかりにくいのでよく見てください。
| 配列 |
フィルターするデータの範囲または配列 |
| 含む |
フィルターする条件を書きます(フィルターで残すデータの条件を書きます) |
| 空の場合 |
(省略可)フィルターした後にデータがない場合に表示するテキスト
(#CALC! エラーとなるときに表示する文字列を指定します) |
- このページでは下表データ(成績表)を使って説明します。
|
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) と入力します。
- B15セルに
配列にはタイトル行を除いたデータだけを指定します。C3:H11 としています。
含むの条件は 合計が300以上としたいので、 H3:H11>=300 としました。
=FILTER(B3:H11,H3:H11>=300) と入力するだけでデータを抽出することができます。
合計が300以上のデータが抽出されました。
- このデータを並べ替えたい場合はSORT関数と組み合わせます。
Sort関数の引数は合計(6列目)の大きい方から順(降順-1)に並べ替えると指定しています。
=SORT(FILTER(B3:H11,H3:H11>=300 ),7,-1) としました。
SORT関数については SORT関数の使い方 をご覧ください。
FILTER関数をテーブルで使用する例
- テーブルの場合、テーブルの名前は 成績表 としています。
B15セルには =SORT(FILTER(成績表,成績表[合計]>=300),7,-1) と入力します。
- マウスでテーブルの指定方法
テーブルの左上をポイントすると、斜め矢印となり、クリックするとテーブル名が入力されます。
タイトル行の上をポイントすると、下矢印が表示されます。クリックすると、テーブルの列が入力されます。
- 国語と算数のどちらも70を超える人を抽出します。
国語が70を超えるは D3:D11>70 と書きます。
算数が70を超えるは E3:E11>70 と書きます。
この2つの条件の AND条件で抽出したいので、*(アスタリスク) を使って (D3:D11>70 )*(E3:E11>70) と書きます。
これは論理積と呼ばれます。
これを2つ目の引数に入れて、
=FILTER(B3:H11,(D3:D11>70 )*(E3:E11>70))
として、データが抽出できます。
この抽出したデータを合計(6列目)の高い順(降順-1)に並べ替えるときは、SORT関数と組み合わせて、
=SORT(FILTER(B3:H11,(D3:D11>70 )*(E3:E11>70)),7,-1) と、します。
- テーブルの場合は、=SORT(FILTER(成績表,(成績表[国語]>70 )*(成績表[算数]>70)),7,-1) とします。
- 国語と算数のどちらかが70を超える人を抽出します。
国語が70を超えるは D3:D11>70 と書くことができます。
算数が70を超えるは E3:E11>70 と書くことができます。
この2つの条件の OR条件で抽出したいので、論理和 + (プラス)を使って (D3:D11>70 )+(E3:E11>70) と書きます。
数式が =FILTER(B3:H11,(D3:D11>70 )+(E3:E11>70)) でデータが抽出できます。
この抽出したデータを合計の高い順に並べ替えるときは、SORT関数と組み合わせて、
=SORT(FILTER(B3:H11,(D3:D11>70 )+(E3:E11>70)),7,-1)
と、します。
- テーブルの場合は、=SORT(FILTER(成績表,(成績表[国語]>70 )+(成績表[算数]>70)),7,-1) とします。
- 氏名が2文字ではない人のデータを抽出します。
条件は LEN(C3:C11)<>2 と書くことができます。
B15セルに =FILTER(B3:H11,LEN(C3:C11)<>2) と入力しています。
氏名が2文字でない、3文字の「佐々木」さんのデータを抽出できました。
- 氏名が2文字ではない人のデータを抽出します。
条件は NOT(LEN(C3:C11)=2) と書くことができます。
B15セルに =FILTER(B3:H11,NOT(LEN(C3:C11)=2),"該当なし") と入力します。
氏名が2文字でない、3文字の「佐々木」さんのデータを抽出できました。
- 2025/2/3 のデータを抽出したいときは
=FILTER(B3:D17,B3:B17="2025/2/3")
とすると、エラー #CALC! となります。
- 2025/2/3 のデータを抽出したいときは
=FILTER(B3:D17,B3:B17=DATE(2025,2,3))
とします。
DATE関数を使って、シリアル値に変換します。 "2025/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関数ではワイルドカードが使えないようですので、文字列を扱う関数 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を返すようにしました。
- 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)
スポンサードリンク
Home »
エクセル関数一覧表 » FILTER関数でデータを抽出する
PageViewCounter
Since2006/2/27