スポンサードリンク | |
作成:2020/2/3,更新:2023/3/15
FILTER関数で1列のデータを抽出する | FILTER関数で配列のデータを抽出する |
FILTER関数で複数条件でデータを抽出する | |
FILTER関数で日付データを抽出する | COUNT関数とFILTER関数で曜日を数える |
FILTER関数で指定した文字列データを抽出する | FILTER関数で指定した列だけを表示する |
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関数で計算する
配列 | フィルターするデータの範囲または配列 |
含む | フィルターする条件を書きます(フィルターで残すデータの条件を書きます) |
空の場合 | (省略可)フィルターした後にデータがない場合に表示するテキスト (#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 |
合計が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) と入力しています。
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関数の使い方 をご覧ください。
国語と算数のどちらも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) と、します。
国語と算数のどちらかが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) とします。
氏名が2文字ではない人のデータを抽出します。
条件は LEN(C3:C11)<>2 と書くことができます。
C15セルに =FILTER(C3:H11,LEN(C3:C11)<>2) と入力しています。
氏名が2文字でない、3文字の「佐々木」さんのデータを抽出できました。
氏名が2文字ではない人のデータを抽出します。
条件は NOT(LEN(C3:C11)=2) と書くことができます。
C15セルに =FILTER(C3:H11,NOT(LEN(C3:C11)=2),"該当なし") と入力します。
氏名が2文字でない、3文字の「佐々木」さんのデータを抽出できました。
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関数ではワイルドカードが使えないようですので、文字列を扱う関数 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)
条件に合致したデータをすべて(連続した列)で抽出するには
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(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関数を利用してください。
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} が返されるからです。
スポンサードリンク
PageViewCounter
Since2006/2/27