Excelで一覧表を扱っていると、特定の担当者名、商品名、ステータスなど、条件に合う行だけを別の場所へ取り出したい場面があります。
目視でコピーすると元データが更新されるたびに作業が必要になりますが、関数を使えば対象データを自動で抽出できます。
範囲内に値があるか確認してから抽出する場合は、FILTER関数を中心に、IF関数、COUNTIF関数、XLOOKUP関数を目的別に使い分けることが重要です。
この記事で確認する主な方法です。
・複数行を条件付きで抽出するならFILTER関数
・値が存在するかだけを判定するならCOUNTIF関数
・一致した1件の情報を取得するならXLOOKUP関数
サンプルデータは、1行目に見出しがあり、A列に商品名、B列に担当者、C列に売上、D列に状況が入力されているものとして説明します。
数式を入力する前に、抽出結果を表示する範囲に既存データがないことも確認しておきましょう。
それでは、範囲内の値を条件にして必要なデータを取り出す方法を順番に見ていきます。
エクセルで範囲内に値があれば抽出する方法【FILTER関数】
それではまず、条件に合う複数の行を一度に抽出できるFILTER関数について解説していきます。
| 商品名 | 担当者 | 売上 | 状況 |
|---|---|---|---|
| ノートPC | 田中 | 120000 | 受注 |
| モニター | 佐藤 | 45000 | 見積 |
| キーボード | 田中 | 9800 | 受注 |
FILTER関数による複数行の抽出
FILTER関数は、指定した範囲から条件を満たす行だけを取り出す関数です。
田中さんが担当するデータを、F2セルから表示したい場合を考えます。
=FILTER(A2:D10,B2:B10=”田中”,”該当データなし”)
第1引数のA2:D10は抽出したい表全体、第2引数のB2:B10=”田中”は担当者が田中であるかを確認する条件です。
第3引数の「該当データなし」は、条件に一致するデータがなかった場合に表示する文字列になります。
FILTER関数は結果が複数行に広がるスピル機能を使うため、数式を入れたセルの下や右に値を置かないことがポイントです。
条件に一致する行が2件あれば2行、5件あれば5行と、自動で抽出件数に応じて表示範囲が変わります。
検索条件をセル参照にする設定
抽出条件を数式に直接入力すると、担当者を変更するたびに式を書き換える必要があります。
そこで、F1セルに検索したい担当者名を入力し、F2セルには次の数式を設定します。
=FILTER(A2:D10,B2:B10=F1,”該当データなし”)
F1セルを田中から佐藤へ変更するだけで、抽出結果もすぐに切り替わります。
検索用のセルを用意してセル参照にすると、利用者が数式を編集せずに検索できる一覧表になります。
担当者名の前後に不要な空白があると一致しないため、元データの入力規則も整えておくと安心です。
空白セルを除外する抽出式
表の中に未入力行が含まれる場合は、商品名の列が空白ではない行だけを抽出する方法も便利です。
=FILTER(A2:D10,A2:A10<>””,”データがありません”)
<>””は空白ではないという条件を表します。
この式ではA列に商品名が入力されている行だけが抽出されるため、入力途中の空行を結果に出したくないときに役立ちます。
抽出条件には文字列だけでなく、空白、数値、日付、TRUEまたはFALSEの判定式も指定できます。
【操作のポイント】FILTER関数の抽出範囲と条件範囲は、必ず同じ開始行と終了行にそろえます。
複数条件に合うデータを取り出す設定
続いては、担当者と状況など、複数の条件を組み合わせて抽出する設定を確認していきます。
| 商品名 | 担当者 | 売上 | 状況 |
|---|---|---|---|
| ノートPC | 田中 | 120000 | 受注 |
| モニター | 佐藤 | 45000 | 見積 |
| キーボード | 田中 | 9800 | 受注 |
AND条件による絞り込み
田中さんが担当し、かつ状況が受注である行だけを取り出す場合は、各条件を掛け算でつなぎます。
=FILTER(A2:D10,(B2:B10=”田中”)*(D2:D10=”受注”),”該当データなし”)
ExcelではTRUEが1、FALSEが0として扱われるため、両方の条件がTRUEの行だけで1になります。
つまり、掛け算はすべての条件を満たすAND条件の役割を果たします。
条件ごとに丸括弧を付けると、数式の構造が見やすくなり、後から条件を追加するときにも便利です。
検索語をF1セル、検索する状況をG1セルに入力するなら、文字列部分をF1とG1に置き換えるだけです。
OR条件による絞り込み
田中さんまたは佐藤さんのデータを取り出したい場合は、条件を足し算で結びます。
=FILTER(A2:D10,(B2:B10=”田中”)+(B2:B10=”佐藤”),”該当データなし”)
足し算はどちらか一方でもTRUEなら正の値になるため、OR条件として働きます。
同じデータが複数の条件に一致した場合でも、FILTER関数の結果には1行として表示されます。
AND条件は掛け算、OR条件は足し算と覚えると、複数条件の数式を組み立てやすくなります。
数値条件による売上データの抽出
売上が50000以上の行だけを確認したい場合は、比較演算子を条件に使います。
=FILTER(A2:D10,C2:C10>=50000,”該当データなし”)
数値を条件にする場合、50000を二重引用符で囲む必要はありません。
日付で絞り込むときも同様ですが、日付はExcelが日付データとして認識している必要があります。
文字列として保存された数値や日付では期待どおりに抽出できないことがあるため、セルの表示形式と実際の値を確認しましょう。
【操作のポイント】条件セルを設ける場合は、検索語と数値条件を入力するセルの用途を見出しで明示します。
COUNTIF関数による値の有無判定
続いては、範囲内に指定した値が存在するかだけを確認したいときのCOUNTIF関数を確認していきます。
| 担当者 | 検索値 | 判定結果 |
|---|---|---|
| 田中 | 田中 | あり |
| 佐藤 | 鈴木 | なし |
COUNTIF関数による存在チェック
COUNTIF関数は、指定範囲の中に条件と一致するセルが何個あるかを数える関数です。
=COUNTIF(B2:B10,F1)
この式では、B2からB10の範囲でF1セルと同じ担当者名が何件あるかを返します。
結果が0より大きければ、範囲内に値があると判断できます。
抽出の前にCOUNTIF関数で存在を判定すると、該当なしのときの表示や処理を分けられます。
重複データの件数を調べる用途にも使えるため、名簿や商品リストの確認にも向いています。
IF関数と組み合わせる判定表示
件数ではなく「あり」または「なし」と表示したい場合は、IF関数を組み合わせます。
=IF(COUNTIF(B2:B10,F1)>0,”あり”,”なし”)
COUNTIF関数の結果が1以上なら「あり」、0なら「なし」と表示されます。
検索する文字列をF1セルに入れておけば、値を変更するだけで判定結果も自動更新されます。
大文字と小文字は区別されませんが、全角と半角、余分なスペースなどは別の値として扱われる場合があります。
値がある場合だけ抽出する数式
存在する場合だけFILTER関数を実行し、存在しない場合には案内文を出す式も作れます。
=IF(COUNTIF(B2:B10,F1)>0,FILTER(A2:D10,B2:B10=F1),”該当データなし”)
FILTER関数の第3引数だけでも該当なしを処理できますが、COUNTIF関数を使う形は判定の考え方を明確にしたい場合に有効です。
ただし、大きな表で同じ範囲を何度も参照すると計算負荷が増えることがあります。
通常の一覧抽出ではFILTER関数だけを使い、件数表示も必要なときにCOUNTIF関数を加えると整理しやすいでしょう。
【操作のポイント】COUNTIF関数の範囲は、見出し行を除いた実データの範囲で指定します。
このイメージでは、F1セルに検索値を入力し、数式バーでCOUNTIF関数を確認している状態です。
XLOOKUP関数による一致データの取得
続いては、検索値に一致する1件の情報を別セルへ取得するXLOOKUP関数を確認していきます。
| 商品コード | 商品名 | 単価 |
|---|---|---|
| A100 | ノートPC | 120000 |
| A101 | モニター | 45000 |
XLOOKUP関数の基本構文
XLOOKUP関数は、指定した値を検索し、同じ行にある別列の値を返す関数です。
=XLOOKUP(F1,A2:A10,C2:C10,”該当なし”)
F1セルの商品コードをA2からA10で探し、見つかった行のC列にある単価を返します。
XLOOKUP関数は検索列が左端でなくても使えるため、VLOOKUP関数より柔軟に参照できます。
第4引数には見つからなかったときの表示を指定できるので、エラー表示を避けたい場合にも便利です。
商品名と単価を別々に返す設定
商品コードから商品名と単価の両方を表示したい場合は、返す範囲を複数列にします。
=XLOOKUP(F1,A2:A10,B2:C10,”該当なし”)
この数式を入力すると、商品名と単価が横方向にスピルして表示されます。
検索用のコードを入力するだけで関連情報を呼び出せるため、見積書や入力フォームの作成にも使えます。
同じ商品コードが複数ある場合は、上から最初に見つかった1件が返る点を理解しておきましょう。
FILTER関数との使い分け
XLOOKUP関数とFILTER関数は似た目的で使われますが、取得したいデータ件数が異なります。
1つのコードから商品名や単価を返すならXLOOKUP関数、担当者が田中の行をすべて表示するならFILTER関数が適しています。
複数件を一覧で取り出す処理にはFILTER関数、1件の対応データを検索する処理にはXLOOKUP関数を選びます。
関数の役割を分けることで、数式が長くなりすぎず、ほかの人も管理しやすいファイルになります。
【操作のポイント】XLOOKUP関数の検索範囲と戻り範囲は、同じ行数で指定します。
抽出結果を見やすくする応用設定
続いては、抽出したデータを実務で使いやすくするための応用設定を確認していきます。
| 検索担当者 | 検索状況 | 抽出結果 |
|---|---|---|
| 田中 | 受注 | 2件 |
FILTER関数とSORT関数の組み合わせ
抽出結果を売上の大きい順に並べたい場合は、SORT関数でFILTER関数を囲みます。
=SORT(FILTER(A2:D10,B2:B10=F1,”該当なし”),3,-1)
第2引数の3は抽出範囲内で3列目にある売上列、第3引数のマイナス1は降順を表します。
元データの並び順を変えずに抽出結果だけを並べ替えられることが、SORT関数を組み合わせる利点です。
昇順にしたい場合はマイナス1を1に変更します。
ワイルドカードによる部分一致検索
担当者名や商品名の一部だけで検索したいときは、アスタリスクをワイルドカードとして使えます。
=FILTER(A2:D10,ISNUMBER(SEARCH(F1,A2:A10)),”該当データなし”)
この式では、F1セルに入力した文字を商品名の中から部分一致で探します。
たとえばF1にPCと入力すると、ノートPCのようにPCを含む商品名が抽出されます。
SEARCH関数は大文字と小文字を区別しないため、商品検索のような用途で扱いやすい関数です。
テーブル機能による参照範囲の自動拡張
元データをExcelのテーブルとして登録すると、行を追加したときに数式の参照範囲を調整する手間を減らせます。
一覧内のセルを選択し、挿入タブからテーブルを選び、先頭行を見出しとして使用する設定を確認します。
テーブル名を売上表とした場合は、構造化参照を用いて数式を組み立てられます。
=FILTER(売上表,売上表[担当者]=F1,”該当データなし”)
テーブル化したデータは追加行を自動で参照しやすいため、毎月更新する売上一覧や顧客リストに向いています。
【操作のポイント】抽出先は元データのテーブル外に置き、スピルするための空白範囲を確保します。
まとめ エクセルで範囲内に値があれば抽出する関数
エクセルで範囲内に値があれば抽出するには、複数行を取り出せるFILTER関数を基本に考えると分かりやすいです。
担当者や状況など、1つまたは複数の条件に一致するデータを別の場所に一覧表示したいときは、FILTER関数を使いましょう。
値があるかだけを確認したい場合はCOUNTIF関数、一致した1件の情報を返したい場合はXLOOKUP関数が適しています。
AND条件は掛け算、OR条件は足し算で指定でき、数値の比較や空白の除外も条件式に含められます。
元データをテーブル化し、検索条件を入力セルとして用意すれば、更新に強く使いやすい検索表になります。
まずは小さなサンプル表でFILTER関数を試し、抽出範囲、条件範囲、該当なしの場合の表示を確認してみましょう。
