FILTER（フィルター）とSPILL（スピル）

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

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

・関数	FILTER（フィルター）
・書式	=FILTER(配列,検索範囲,空の場合)
・内容	指定された範囲の列で特定の値を検索し、一致した行の範囲の対応するセルの値を返します。

エクセル表サンプル（ Sheet1

	A	B	C	D	E	F
1
2		日時	担当	内線	受付
3		4月21日	鈴木	1234	9:00～18:00
4		4月22日	佐藤	5678	10:00～19:00
5		4月23日	高橋	6789	10:00～19:00
6		4月24日	鈴木	1234	9:00～18:00
7		4月25日	鈴木	1234	9:00～18:00
8		4月26日	高橋	6789	9:00～18:00
9		4月27日	鈴木	1234	9:00～18:00
10		4月28日	鈴木	1234	9:00～18:00
11		4月29日	佐藤	5678	10:00～19:00
12		4月30日	佐藤	5678	10:00～19:00
13		5月1日	高橋	6789	9:00～18:00
14		5月2日	小林	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		4月23日	高橋	6789	10:00～19:00
6		4月26日	高橋	6789	9:00～18:00
7		5月1日	高橋	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も表示を日付にすれば「4月23日」ですが
シリアル値が違うので一致しません
=NOW()　　45405.783113426
=TODAY()　45405
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	表の範囲を列から検索して呼び出す