Excel 条件に一致する行を全て取得(FILTER関数)
FILTER関数は2019年に実装されたスピルの関連関数です。
フィルターの関数版で特定の条件に一致するデータを取得します。
ここでは、FILTER関数の使い方や既存機能（フィルターとDGET関数）との比較を紹介します。
仕様
=FILTER(配列,含む,空の場合)
| 配列 | 省略時の値 | 説明 |
|---|---|---|
| 配列 | 省略不可 | フィルターするセル範囲 |
| 含む | 省略不可 | 検索条件 |
| 空の場合 | #CALC!エラー | 0件の場合の結果 |
使用例
基本的な使い方
配列・含む
第1引数に元データの範囲を指定し、第2引数に検索条件を指定します。
下の例ではエリアが関東である行を抽出します。
一つのセルにFILTER関数を設定すると条件の結果の数に合わせて数式が自動拡大されます。
最初に関数を設定したオリジナルのセル以外は灰色のゴーストという状態で編集できません。
ただし自動拡大先のセルに何らかの値があると#SPILLエラーになります。
これにより数式のコピー・ペーストの必要がなくなり、
数式壊したことに気付かずに使い続けるリスクがなくなります。
更に数式の本体は一つだけのため、料率などを計算に含める場合に絶対参照が不要です。
また類似の既存機能では最大検索結果数を想定して、
数式をセルに事前準備しておく必要がありましたが、（静的）
FILTER関数では一つのセルに設定すれば必要な数だけ
自動的に拡充されます（動的）。これが新機能のスピルです。
空の場合
第3引数には見つからない場合の結果を指定します。
未指定の場合は#CALC!エラーになります。
指定した場合はその値が結果となります。
特定の列のみ取得したい場合
引数2の範囲が引数1に含まれている必要はないため、
引数1に表示したい列のみを指定すれば
特定列のみを取得することが可能です。
大小条件（比較演算子）
検索値でなく条件であるため○○以上・□□以下のような比較演算子による指定が可能です。
下の例では人口が500万以上のデータを抽出します。
複数条件
○○以上かつ□□以下のような条件も指定可能です。
| 条件 | 演算子 |
|---|---|
| かつ | * |
| または | + |
+演算子でOR(または)
+(または)を利用し100万未満かまたは、1000千万より多いデータを抽出する例です。
*演算子でAND(かつ)
*(かつ)を利用し100万以上でかつ、1000千万以下のデータを抽出する例です。
順序が狂うと条件でなく、足し算と掛け算として扱われるため
1つの条件ごとに()で囲む必要があります。
このパターンはFILTER関数を入れ子にしても実現できます。
文字列の部分一致条件
ワイルドカードは利用できませんが、他の関数との併用で部分一致検索を実現可能です。
部分一致：○○を含む(FIND関数)
FIND関数で部分一致検索が可能です。
IFERROR関数も併用しないと、一致しないデータの処理で
エラーしてしまい結果を取得できません。
=FILTER(元データの範囲,IFERROR(FIND(含む文字列,検索条件の範囲)>0,0))
前方一致：○○から始まる(LEFT関数)
LEFT関数で前方一致条件が可能です。LEN関数も併用します。
=FILTER(元データの範囲,LEFT(検索条件の範囲,LEN(始まる文字列))=始まる文字列)
後方一致：○○で終わる(RIGHT関数)
RIGHT関数で後方一致条件が可能です。LEN関数も併用します。
=FILTER(元データの範囲,RIGHT(検索条件の範囲,LEN(終わる文字列))=終わる文字列)
日時の条件(日付/時刻関数)
シリアル値そのものを条件にすることもできますが、
扱いづらく数式を読んでも意味不明になります。
よってセルに入力した日付を条件にするか日付／時刻関数を併用しましょう。
例えばDATE関数を使用すればシリアル値を使わずに日付比較を行うことが可能です。
（セル指定でもよいですが）
DAY関数を併用すれば毎月の特定日の指定が可能です。
併用する日付関数の一覧と想定される利用ケースは以下の通りです。
| 関数 | 利用ケース |
|---|---|
| DATE | 日付を指定（年月日の数値からシリアル値を取得） |
| DATEVALUE | 日付を指定（年月日の文字列からシリアル値を取得） |
| TIME | 時刻を指定（時分秒の数値からシリアル値を取得） |
| TIMEVALUE | 時刻を指定（時分秒の文字列からシリアル値を取得） |
| TODAY | 現在日付を指定 |
| Now | 現在日時を指定 |
| YEAR MONTH DAY HOUR MINUTE SECOND | 特定の年、月、日、時、分、秒を指定 |
| WEEKDAY | 特定の曜日を指定 |
| WORKDAY | 営業日・稼働日を指定 |
| EOMONTH | 月末日付を指定 |
結果を他の関数に利用
SORT関数・SORTBY関数で並び替え
FILTER関数の結果はセル範囲であるため
セル範囲を引数とする他の関数に結果を受け渡すことが可能です。
例えばFILTER関数の結果をSORT関数で並び替えるといったことが可能です。
例ではSORT関数ですが、SORTBY関数でも同じことが可能です。
ROWS関数で結果の件数を取得
FILTER関数の結果をROWS関数に指定すると条件に一致する件数になります。
COUNT系の関数では列まで数えてしまうので結果件数とは一致しない場合があります。
（COUNT関数では数値のみなため、たまたま3件と一致するがCOUNTA関数だと全セルを数えて9件）
複数のセル範囲を対象にする
VSTACK関数・HSTACK関数で複数の範囲を対象にすることが可能です。
既存機能との比較
フィルタリングや検索を行う類似機能と比べて、どのような違いがあるかを紹介します。
フィルター
従来、データのフィルタリングを行う場合は
フィルターが選択肢でしたが、そこにFILTER関数が追加されました。
フィルターはデータを更新した場合は毎回、条件を設定する必要があるため
日次などのルーチンワークには煩わしさがありましたが、
FILTER関数によって解消が見込めます。
逆に非定型作業ではこれまで通り、フィルターを使う方が取り回しが良いでしょう。
ただしOffice365や2019以降でしかFILTER関数は使えません。
| 項目 | フィルター | FILTER関数 |
|---|---|---|
| 使いやすさ | 〇 画面操作が優秀で日付も扱える。 | × 数式が複雑で日付もシリアル値を意識する必要がある。 |
| 柔軟性 | × フィルター条件が画面操作の範囲内に限られる。 | 〇 数式次第でどのような条件も設定可能。 |
| ルーチンワーク | × 毎回、手作業で設定が必要。 | 〇 数式を作成すれば流用可能。 |
| 非定型業務 | 〇 調査業務など様々な条件でフィルタリングを繰り返す場合に向く。 | × 数式を作り変える必要があるため、様々な条件でフィルタリングしたい場合には劣る。 |
| Excelのバージョン | 〇 古いバージョンでも可能。 | × Office365や2019以降の新しいバージョンのみ。 |
DGET関数
DGET関数は似たような機能の関数ですが、こちらは検索結果が一つのみです。
FILTER関数の方が理解しやすい仕様になっているので
バージョンなどの理由がなければFILTER関数の方が優秀です。
Excel完全マスター: 関数・テクニック・新機能を網羅した決定版ガイド
本書は、Excelの基礎から応用まで、包括的に学べるガイドブックです。当サイトの豊富な情報を、電子書籍向けに最適化し、読みやすく再構成しました。
【本書の特徴】
- 情報量：Word-A4サイズ換算で1,400ページ相当
- 基本操作から高度なテクニックまで、段階的に学習可能
- 140種類の関数を詳細に解説
- 109種類の実用的なテクニックと具体例を紹介
- 最新機能（XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピル等）を徹底解説
【対象読者】
- Excel初心者からプロフェッショナルまで
- 体系的にExcelスキルを向上させたい方
- 業務効率化を目指すビジネスパーソン
- データ分析や可視化のスキルを磨きたい方
【本書の強み】
- 実務に即した例題と解説
- 視覚的な図表やスクリーンショットで理解を促進
- 最新のExcelバージョンに対応した内容
- 著者の長年の経験に基づく、実践的なTipsを多数収録
【更新とサポート】
- 常に最新の情報を反映するため、定期的に内容を更新
- 最新版の入手方法：Amazonカスタマーサポートへお問い合わせください
【入手方法】
- 買い切り：950円
- Kindle Unlimited：読み放題プランで利用可能
Excelマスターを目指す方、業務効率を劇的に向上させたい方に、自信を持っておすすめできる一冊です。この1冊で、Excelの可能性を最大限に引き出す力が身につきます。