Excelの表から必要な行だけを取り出したい場面では、オートフィルター、FILTER関数、詳細設定フィルター、検索関数などを目的に応じて使い分けることが大切です。
売上一覧から特定の担当者の実績を確認したり、条件に合う商品だけを別シートへ表示したりすると、集計や報告にかかる時間を減らせます。
この記事では、1行目に見出しがあるサンプル表を使い、初心者でも実践しやすいデータ抽出の手順を解説します。
画面上で絞り込むならフィルター、抽出結果を別の場所へ自動表示するならFILTER関数、重複を除いた一覧を作るならUNIQUE関数が便利です。
エクセルでデータを抽出する方法1【オートフィルターによる絞り込み】
それではまず、表をその場で確認したいときに便利なオートフィルターについて解説していきます。
| 日付 | 担当者 | 商品 | 売上 |
|---|---|---|---|
| 4月1日 | 田中 | ノートPC | 120000 |
| 4月2日 | 佐藤 | モニター | 45000 |
| 4月3日 | 田中 | キーボード | 8000 |
フィルターボタンの設定手順
表内の任意のセルを選択してから、リボンのデータタブにあるフィルターをクリックします。
見出し行の右側に下向きの矢印が表示されれば設定完了です。
表全体を選択しなくても、連続したデータ範囲内のセルを選んでいればExcelが範囲を判断します。
ただし、表の途中に空白行や空白列があると、意図しない範囲でフィルターが設定されることがあります。
データの一覧は、途中で区切らず、1つのまとまった表として整えておきましょう。
ショートカットキーを使う場合は、表内を選択してCtrlキーとShiftキーとLキーを同時に押します。
同じ操作をもう一度行うと、フィルター表示を解除できます。
文字列と数値による条件指定
担当者列の矢印をクリックすると、表内にある担当者名の一覧が表示されます。
すべて選択のチェックを外し、田中だけにチェックを入れてOKを選ぶと、田中が担当した行だけが表示されます。
売上列では、数値フィルターから指定の値以上、指定の値以下、指定の範囲内などを選べます。
たとえば50000円以上の売上を確認したい場合は、数値フィルターで指定の値以上を選び、50000と入力します。
非表示になった行は削除されたわけではなく、一時的に隠れている状態です。
条件を解除すれば、元の一覧をすぐに戻せます。
複数条件と解除操作
担当者を田中に絞ったうえで、さらに売上を50000円以上に設定すると、両方の条件を満たす行だけが残ります。
通常のフィルターでは、異なる列に指定した条件は両方を満たす条件として扱われます。
同じ列で複数の項目を選んだ場合は、選択したいずれかに一致する行が表示されます。
条件を外すときは、対象列の矢印からフィルターをクリアを選びます。
【操作のポイント】フィルターの結果をコピーするときは、表示されているセルだけを選択する操作を使うと、隠れた行までコピーしてしまう失敗を防げます。
FILTER関数による条件付き抽出
続いては、抽出結果を別のセルへ自動表示したいときに使うFILTER関数を確認していきます。
| A列 | B列 | C列 | D列 |
|---|---|---|---|
| 日付 | 担当者 | 商品 | 売上 |
| 4月1日 | 田中 | ノートPC | 120000 |
基本構文と抽出範囲
FILTER関数は、指定した配列から条件に一致する行や列を取り出す関数です。
=FILTER(A2:D100,B2:B100=”田中”,”該当データなし”)
この式では、A2からD100までを抽出対象にし、B列が田中である行だけを返します。
3つ目の引数は、該当するデータが1件もない場合に表示する文字列です。
元データに見出しが1行目にある場合、通常は抽出範囲を2行目から指定します。
見出しを含めてしまうと、条件範囲との行数が一致しなくなり、エラーになることがあります。
複数条件の組み合わせ
担当者が田中で、売上が50000以上という2つの条件を同時に指定することも可能です。
=FILTER(A2:D100,(B2:B100=”田中”)*(D2:D100>=50000),”該当データなし”)
条件式の間にアスタリスクではなく、数式内の乗算記号としての*を使うと、両方の条件を満たす行を抽出できます。
各条件の判定結果は、TRUEとFALSEに相当する値として処理されます。
両方がTRUEの行だけが1となるため、AND条件として機能する仕組みです。
どちらかの条件に合う行を抽出したいときは、乗算記号の代わりにプラス記号を使います。
式を入力するセルの右側や下側には、抽出結果が広がるための空白が必要です。
スピルとエラーの対処
FILTER関数の結果は、最初のセルから必要な行数と列数へ自動的に展開されます。
この動きをスピルと呼びます。
展開先に文字や数値が入力されていると、スピルの範囲内にデータがありますというエラーが表示されます。
エラーが出た場合は、式を入れたセルを選択し、点線で示された展開予定範囲を確認しましょう。
その範囲内の不要な値を移動または削除すると、結果が表示されます。
【操作のポイント】FILTER関数の抽出元はテーブル化しておくと、行の追加時に参照範囲を修正する手間を抑えられます。
条件指定と検索関数によるデータ取得
続いては、検索値に応じて関連情報を取り出す検索関数の使い方を確認していきます。
| 商品コード | 商品名 | 単価 |
|---|---|---|
| A001 | ノートPC | 120000 |
| A002 | モニター | 45000 |
XLOOKUP関数による一致データ
XLOOKUP関数は、指定した値を検索し、同じ行にある別の項目を返す関数です。
=XLOOKUP(F2,A2:A100,C2:C100,”見つかりません”)
F2に商品コードを入力すると、A列からコードを探し、対応するC列の単価を表示します。
XLOOKUP関数は検索列が左端にない表でも利用でき、戻り値の列を自由に指定できます。
コードから商品名や担当者を取得するようなマスタ参照に向いています。
Microsoft 365などの対応バージョンで利用できる点は確認しておきましょう。
INDEX関数とMATCH関数の組み合わせ
古いバージョンのExcelを含めて利用するなら、INDEX関数とMATCH関数の組み合わせも有効です。
=INDEX(C2:C100,MATCH(F2,A2:A100,0))
MATCH関数は、F2の値がA2からA100の何番目にあるかを調べます。
その位置番号をINDEX関数に渡すことで、C列の対応する単価を返します。
第3引数の0は完全一致の検索を意味します。
商品コードや社員番号のように、完全に一致すべき項目には完全一致検索を選ぶのが基本です。
検索値の入力規則とエラー表示
検索値を手入力すると、全角半角の違いや余分な空白によって一致しないことがあります。
入力規則のリストを使い、商品コードを選択式にすると入力ミスを減らせます。
また、IFERROR関数で検索式を包むと、未登録コードを入力したときのエラー表示を読みやすくできます。
=IFERROR(XLOOKUP(F2,A2:A100,C2:C100),”コードを確認してください”)
利用者が見るシートでは、数式エラーよりも状況が分かる案内文のほうが扱いやすいでしょう。
【操作のポイント】検索用のコードは文字列として統一し、先頭のゼロがある番号は表示形式ではなく文字列として管理します。
詳細設定フィルターと重複のない一覧
続いては、条件に一致したレコードを別の場所へコピーしたい場合や、重複しない項目一覧を作りたい場合を確認していきます。
| 担当者 | 商品 | 売上 |
|---|---|---|
| 田中 | ノートPC | 120000 |
| 田中 | キーボード | 8000 |
詳細設定フィルターの抽出準備
詳細設定フィルターは、条件範囲を別途用意し、抽出結果を指定した場所へコピーできる機能です。
まず、元の表と同じ見出しを使って条件範囲を作成します。
たとえばF1に担当者、F2に田中と入力すると、担当者が田中のレコードを抽出する条件になります。
条件範囲の見出しは、元データの見出しと完全に同じ文字列にする必要があります。
見出しに余分な空白があるだけでも、期待どおりに抽出されないことがあります。
同じ行に入力した条件はAND条件になり、別の行に入力した条件はOR条件になります。
別の場所への抽出操作
元の表内を選択してから、データタブの詳細設定をクリックします。
リスト範囲には見出しを含む元表の範囲を指定し、条件範囲には作成した条件表を指定します。
指定した場所にコピーするを選び、コピー先には結果を表示したい左上のセルを入力します。
OKを選ぶと、条件に合う行がコピー先へ表示されます。
| A | B | C | D | F | |
|---|---|---|---|---|---|
| 1 | 日付 | 担当者 | 商品 | 売上 | 担当者 |
| 2 | 4月1日 | 田中 | ノートPC | 120000 | 田中 |
UNIQUE関数による項目一覧
Microsoft 365では、UNIQUE関数を使うと重複しない担当者名や商品名の一覧を簡単に作成できます。
=UNIQUE(B2:B100)
この式を入力すると、B列にある担当者名から重複を除いた一覧が自動展開されます。
さらにSORT関数を組み合わせれば、五十音順や昇順に並べた一覧も作れます。
=SORT(UNIQUE(B2:B100))
入力規則のリスト元に重複なしの一覧を利用すると、検索条件の選択画面を見やすく整えられます。
【操作のポイント】詳細設定フィルターは結果を固定してコピーしたい場面に、UNIQUE関数は元データの更新を反映したい場面に向いています。
抽出できない原因とデータ整形
続いては、フィルターや関数で期待した結果が出ないときに確認したいデータ整形のポイントを解説していきます。
| 確認項目 | よくある状態 | 対処 |
|---|---|---|
| 空白 | 末尾にスペース | TRIM関数 |
| 数値 | 文字列の数字 | VALUE関数 |
空白文字と表記ゆれ
見た目が同じ田中という文字でも、セルの前後にスペースが含まれていると、FILTER関数や検索関数では別の値として扱われます。
Webページや別システムから貼り付けたデータには、通常の半角スペースとは異なる空白文字が混ざることもあります。
TRIM関数を使うと、文字列の前後にある不要な半角スペースを整理できます。
=TRIM(B2)
抽出用の元データは、表記ゆれを直してから利用するほど、条件設定が安定します。
数値と日付のデータ型
売上金額が数字に見えても、文字列として保存されている場合は、数値フィルターや大小比較が正しく働かないことがあります。
セルの左上に緑の三角が表示される場合は、数値が文字列として保存されている可能性があります。
VALUE関数で数値へ変換するか、警告アイコンから数値に変換を選びます。
日付も同様に、日付形式として認識されていないと、今月分や指定期間の抽出が難しくなります。
日付は文字列ではなく、Excelが計算できる日付シリアル値として統一することが重要です。
テーブル化と参照範囲の管理
データ範囲を選択してCtrlキーとTキーを押すと、表をテーブルとして管理できます。
テーブルでは行を追加したときにフィルター範囲や数式の参照範囲が自動的に広がります。
毎月データが増える一覧では、A2からA100のように固定範囲を指定するより管理しやすいでしょう。
ただし、テーブル名や列名を変更した場合は、既存の数式参照も確認する必要があります。
【操作のポイント】抽出前に空白行、結合セル、表記ゆれ、文字列化された数値を見直すと、関数のエラーやフィルター漏れを防げます。
まとめ エクセルでデータを抽出する方法
Excelでデータを抽出する方法は、確認したい目的と結果の使い道によって選ぶのが基本です。
一覧を一時的に絞り込むならオートフィルター、別の場所へ条件に合うデータを自動表示するならFILTER関数が役立ちます。
商品コードなどをもとに関連項目を取得する場合は、XLOOKUP関数やINDEX関数とMATCH関数の組み合わせを使います。
固定した抽出結果を別の場所へコピーするなら詳細設定フィルター、重複のない候補一覧を作るならUNIQUE関数が便利です。
抽出がうまくいかないときは、余分な空白、表記ゆれ、文字列になった数値、日付形式を最初に確認しましょう。
データをテーブル化し、見出しを1行目にそろえた状態で管理すれば、フィルターや関数をより正確に活用できます。
