動的配列・スピル入門
FILTER関数を見て理解する
指定した条件を満たす行や列だけを配列から取り出し、結果を複数セルへスピルする関数です。
3つのポイント
- 抽出元の表全体を指定する
- 残したいデータの条件を判定する
- TRUEの行だけを新しい一覧へスピルする
部署を指定して社員一覧を絞り込む
サンプル数式
- 配列
- 条件範囲
- 比較値
- 0件時の値
いまの結果：
先頭行：「田中」 ｜ 「営業」 ｜ 4
入力・条件：4項目
- 配列A2:C6（5行 × 3列）先頭行：「田中」 ｜ 「営業」 ｜ 4
- 条件範囲B2:B6（5セル）値の例：「営業」、「開発」、「人事」
- 比較値E2：「営業」
- 0件時の値F2：「該当なし」
抽出条件に一致した項目は3件です。
サンプルデータ
※ Excel と Google スプレッドシートで結果や挙動が異なる場合、Excel の仕様を基準にしています。
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 社員名 | 部署 | 在籍年数 | 抽出する部署 | 0件時の値 | 結果 | ||||
| 2 | 田中 | 営業 | 4 | 田中 | 営業 | 4 | ||||
| 3 | 佐藤 | 開発 | 2 | 鈴木 | 営業 | 6 | ||||
| 4 | 鈴木 | 営業 | 6 | 伊藤 | 営業 | 3 | ||||
| 5 | 高橋 | 人事 | 5 | |||||||
| 6 | 伊藤 | 営業 | 3 |
E2、F2に値を入力すると、結果と処理順が変わります。
3つの引数
正式引数は3つです。② include を2つの教材要素に分けて表示しています。
A2:C6表のセル: A2:C6条件に合う行を取り出す社員データです。
B2:B6表のセル: B2:B6部署が指定した条件と一致するかを1行ずつ判定する範囲です。
E2表のセル: E2抽出したい部署名です。値を変えると抽出結果も変わります。
F2表のセル: F2条件に一致する社員がいないときに表示する値です。
この数式を日本語にすると
E2の部署名とB2:B6を1行ずつ比べ、同じ部署の行だけをA2:C6から取り出します。一致する社員がいなければF2の値を返します。
この説明は、最初に表示されているサンプルデータをもとにしています。入力値を変更しても説明文は変わりません。
処理の流れ
手順1〜5を順番に確認しましょう。表内の小さな角丸数字は、対応する手順を示します。
抽出する配列を確認する
A2B2C2A3B3C3A4B4C4A5B5C5A6B6C6A2:C6を抽出元の配列として使います。
条件を判定する範囲を確認する
B2B3B4B5B6B2:B6の各値を条件判定に使います。
比較値を確認する
E2B2:B6=営業を判定し、TRUEになる行だけを残します。
0件時の値を確認する
F2条件に一致するデータがあるため、0件時の値は結果に使われません。
条件に合うデータをスピルする
H2:J4B2:B6=営業に一致する行を3件残し、3行×3列の結果をH2:J4へスピルします。
対応アプリ
- Excel
- Google スプレッドシート
関数自体が対応するアプリです。教材例の数式や再計算が、両アプリで同じように動くことを保証するものではありません。
この例の注意点
- ExcelのFILTERはarray、include、[if_empty]の3引数です。この教材では、2番目のinclude（B2:B6=E2）を「条件範囲」と「比較値」の2つに分けて可視化しています。
- 条件範囲が元配列と同じ高さの1列なら行を、同じ幅の1行なら列を抽出します。
- 比較演算子は =、<>、>、>=、<、<= を扱います。Excel 2021の比較規則に合わせ、数値・文字列・論理値の型差や空白セルの特殊な比較も反映します。
- 条件に一致するデータが0件でif_emptyを指定している場合は、その値を返します。
- 複数条件を * や + で組み合わせるAND / OR条件は今回のFoundation対象外です。
一般構文
対応：Excel / Google スプレッドシート ・ 構文：構文差あり
- Sheetsでは追加条件を繰り返し指定できます。
- Excelのif_emptyに相当する引数はSheetsにはありません。
- 一致するデータが0件の場合、Excelでif_emptyを省略すると#CALC!、Google スプレッドシートでは#N/Aになります。
Excel
=FILTER(array, include, [if_empty])[] は省略できる引数です。
省略時
if_empty- 省略して条件に合うデータが0件になると、#CALC!エラーを返します。
Google スプレッドシート
=FILTER(range, condition1, [condition2], ...)公式資料・確認範囲
公式資料
- Microsoft Excel 公式構文
- Google スプレッドシート 公式対応状況・構文
未確認
計算結果・入力の扱い・配列展開
この教材で扱わない範囲
- 複数条件を * や + で組み合わせるAND / OR条件は、この教材では扱いません。
次に学ぶ関数
UNIQUE
条件で残した行の次は、重複を除いて一覧にします。
次に学ぶ