FILTER(フィルター)エクセル(Excel)関数

FILTER(フィルター)とSPILL(スピル)

FILTER(フィルター)は、指定した列から検索して一致する行にある値を呼び出します
またSPILL(スピル)で複数の行の範囲を取得することができます

XLOOKUP関数は一致する行、または列を1行呼び出しますが
FILTER関数は一致するすべての行を呼び出します

・関数FILTER(フィルター)
・書式=FILTER(配列,検索範囲,空の場合)
・内容指定された範囲の列で特定の値を検索し、一致した行の範囲の対応するセルの値を返します。
エクセル表サンプル( Sheet1
  A B C D E F
1            
2   日時 担当 内線 受付  
3   9月27日 鈴木 1234 9:00~18:00  
4   9月28日 佐藤 5678 10:00~19:00  
5   9月29日 高橋 6789 10:00~19:00  
6   9月30日 鈴木 1234 9:00~18:00  
7   10月1日 鈴木 1234 9:00~18:00  
8   10月2日 高橋 6789 9:00~18:00  
9   10月3日 鈴木 1234 9:00~18:00  
10   10月4日 鈴木 1234 9:00~18:00  
11   10月5日 佐藤 5678 10:00~19:00  
12   10月6日 佐藤 5678 10:00~19:00  
13   10月7日 高橋 6789 9:00~18:00  
14   10月8日 小林 3456 9:00~18:00  
15            

上記の表から担当者が「高橋」の行を呼び出します
=FILTER(配列,検索範囲,空の場合)

セルB5
=FILTER(Sheet1!B3:E14,Sheet1!C3:C14=C2,"")

  A B C D E F
1            
2   担当者 高橋      
3            
4   日時 担当 内線 受付  
5   9月29日 高橋 6789 10:00~19:00  
6   10月2日 高橋 6789 9:00~18:00  
7   10月7日 高橋 6789 9:00~18:00  
8            

担当者からを検索する場合
=FILTER(Sheet1!B3:E14,Sheet1!C3:C14=C2)

見つからない場合「#CALC!」エラーになるのでエラー時に「空白」にする場合
=FILTER(Sheet1!B3:E14,Sheet1!C3:C14=C2,"")

見つからない場合「#CALC!」エラーになるのでエラー時に「見つかりません」にする場合
=FILTER(Sheet1!B3:E14,Sheet1!C3:C14=C2,"見つかりません")
(空白にする場合は""、指定しないと見つからないときは#CALC!エラーになります)


抽出した結果を並び替える場合

SORT関数を使って並び替えます
=SORT(配列,基準値,順序)
配列:FILTERで抽出した関数
基準値:何列目を基準にするか
順序:(1:昇順 -1:降順)

担当者で抽出した後に日時小さい順に並び替え
=SORT(FILTER(Sheet1!B3:E14,Sheet1!C3:C14=C2),1,-1)


複数の条件で抽出する場合

=FILTER(Sheet1!B3:E14,(Sheet1!B3:B14=B2)*(Sheet1!C3:C14=C2),"")

1つ目の条件:セルB2の日時
Sheet1!B3:B14=B2

2つ目の条件:セルC2の担当者
Sheet1!C3:C14=C2

2つの条件を「*」で掛けた場合が「&」
(Sheet1!B3:B14=B2)*(Sheet1!C3:C14=C2)
これで「日時」「担当者」が一致した行が抽出されます

また2つの条件を「+」で足した場合が「または」になります
(Sheet1!B3:B14=B2)+(Sheet1!C3:C14=C2)
これで「日時」「担当者」どちらかが一致した行が抽出されます


結果がエラーになる、日時を呼び出せない場合

3通りが考えられます
・指定日時の担当を呼び出す場合
=FILTER(配列,検索範囲,空の場合)
=FILTER(B3:E14,B3:B14=C2,"")

1つ目:検索範囲の日時が文字列で入力されている場合
配列は「"8月18日"」と直接指定した場合「8月18日」という数字と漢字の文字列を探します 検索範囲の日時も文字列で入力されていれば呼び出せますが、通常セルにある日時はシリアル値として認識されます

検索範囲の日時:「8月18日」シリアル値「43695」
配列:「"8月18日"」文字列
文字列の「8月18日」を探してもシリアル値「43695」とは一致しません
対応策:検索値の8月18日をシリアル値に変換します
=FILTER(B3:E14,B3:B14=VALUE(C2),"")
※(検索値の年号を省略すると今年になります)

2つ目:日時をセルで指定した場合はシリアル値になりますが
何かの表や去年から使いまわして日付をコピーした場合
「8月18日」が実は「2017年8月18日」など今年ではない時は見た目は一緒でも中身は「2017年8月18日」と「2018年8月18日」なので一致しません
対応策:セルをクリックして見ると年号も表示されるので今年に直します

また検索している関数がTODAYやNOWの場合
TODAYもNOWも表示を日付にすれば「9月29日」ですが
シリアル値が違うので一致しません
=NOW()  44103.39681713
=TODAY() 44103
NOW()は小数点以下に時間を表示しています
今日を検索するにはNOW()ではなくTODAY()を使います

3つ目:スピルの結果範囲内に何かが入力されている
この場合セルに「#SPILL!」と表示されます
上記のサンプルで
セルB2
=FILTER(B3:E14,B3:B14=VALUE(C2),"")
とした場合
戻り値の範囲が「B3:E14」とC列と一致してる行が自動で数式が入ります
「#SPILL!」と表示されている場合は戻り範囲に何か入力されていないか、入力されていれば削除するかそのセルに含まないように戻り範囲を変更します


FILTER関数とよく使うエクセル関数
 AVERAGE  空白や「0」のセルを無視した平均値
 AVERAGEA  空白や「0」のセルを含む平均値
 LARGE  範囲内の最大値
 SMALL  範囲内の最小値
 ROUNDDOWN   指定した桁数で切り捨て
 XLOOKUP   表の範囲を列から検索して呼び出す
HOME

エクセル(Excel)関数

(C) 2001-2020 Digital World