Excelで一覧表を扱っていると、膨大なデータの中から必要な項目だけを抜き出したい場面があります。
たとえば、売上一覧から特定の担当者の行だけを表示したいとき、顧客台帳から必要な列だけを別シートへまとめたいとき、空白を除いた商品名だけを抽出したいときなどです。
抽出の目的に合う機能を選べば、元データを書き換えずに一覧を整理でき、集計や報告書の作成も進めやすくなります。
項目を抽出する代表的な方法
・FILTER関数で条件に一致する行を表示する方法
・XLOOKUP関数やINDEX関数で必要な項目を取得する方法
・フィルター機能で表示対象を絞り込む方法
・別シートへ抽出結果を自動表示する方法
この記事では、Excelで必要な項目を抽出する方法を、関数、別シート、条件指定という視点から順番に解説します。
サンプルデータはすべて1行目に見出しがある表を前提とします。
エクセルで必要な項目を抽出する方法【FILTER関数】
| A列 | B列 | C列 | D列 |
|---|---|---|---|
| 商品名 | 担当者 | 地域 | 売上 |
| ノートPC | 田中 | 東京 | 120000 |
| モニター | 佐藤 | 大阪 | 80000 |
| キーボード | 田中 | 東京 | 15000 |
それではまず、条件に合う複数の項目をまとめて取り出せるFILTER関数について解説していきます。
Microsoft 365版やExcel 2021以降では、FILTER関数を使うことで、該当する行を数式だけで自動抽出できます。
担当者を条件にした行データの抽出
担当者が田中さんの行だけを別の場所に表示したい場合は、抽出先のA2セルにFILTER関数を入力します。
=FILTER(A2:D100,B2:B100=”田中”,”該当なし”)
第1引数のA2:D100は、抽出して表示したい元データの範囲です。
第2引数のB2:B100=”田中”は、B列が田中である行だけを残す条件です。
第3引数の該当なしは、条件に一致するデータがない場合に表示する文字列になります。
数式を入力すると、条件に一致したレコードが下方向と右方向へ自動的に展開されます。
この展開範囲はスピルと呼ばれ、抽出件数が増減しても基本的には数式を書き換える必要がありません。
抽出先の周囲に値が入力されていると、スピル範囲に重なってエラーになるため、出力先は十分に空けておくことが大切です。
複数条件による必要項目の絞り込み
担当者が田中で、かつ売上が50000以上のデータだけを抽出する場合は、複数の条件を掛け合わせます。
=FILTER(A2:D100,(B2:B100=”田中”)*(D2:D100>=50000),”該当なし”)
条件式の間に掛け算記号を入れると、両方の条件を満たす行だけを抽出するAND条件として扱えます。
反対に、東京または大阪のように、どちらかを満たせばよい場合は、条件式の間をプラス記号でつなぎます。
=FILTER(A2:D100,(C2:C100=”東京”)+(C2:C100=”大阪”),”該当なし”)
売上のような数値条件では、セルの表示形式が文字列になっていないかも確認しましょう。
見た目が数字でも文字列として保存されていると、以上や以下の比較が期待通りに動かないことがあります。
列を限定した抽出結果の作成
元表の全列ではなく、商品名と売上だけを取り出したいケースもあります。
FILTER関数は行を条件で絞り込む関数なので、表示する列を限定するにはCHOOSECOLS関数との組み合わせが便利です。
=CHOOSECOLS(FILTER(A2:D100,B2:B100=”田中”,”該当なし”),1,4)
この数式では、まず田中さんの行をFILTER関数で取得し、その結果から1列目と4列目だけを選びます。
元の範囲A2:D100における1列目は商品名、4列目は売上です。
抽出する順番を変えたい場合も、CHOOSECOLS関数の列番号を並べ替えるだけで対応できます。
たとえば4,1と指定すれば、売上、商品名の順番で結果を表示できます。
【操作のポイント】FILTER関数は元データの行数が増える可能性を考え、範囲を必要以上に狭く設定しないことが重要です。
別シートへ項目を抽出する数式の設定
| 元データシート | 抽出シート | 抽出条件 |
|---|---|---|
| 売上一覧 | 東京支店一覧 | 地域が東京 |
| 顧客台帳 | 対象顧客 | 契約状況が継続 |
続いては、別シートに抽出結果を表示する設定を確認していきます。
抽出用のシートを作っておくと、元データの表を見やすい状態で保ちながら、報告用の一覧を自動更新できます。
シート名を含めたFILTER関数の指定
元データが売上一覧シートにあり、抽出結果を抽出結果シートへ表示する例を考えます。
抽出結果シートのA2セルへ、次のように入力してください。
=FILTER(売上一覧!A2:D100,売上一覧!C2:C100=”東京”,”該当なし”)
シート名の後に感嘆符を付けることで、別シートにあるセル範囲を参照できます。
シート名に空白や記号が含まれる場合は、シート名をシングルクォーテーションで囲みます。
たとえば月別 売上というシート名なら、’月別 売上’!A2:D100のように指定します。
抽出先の1行目には、元データと同じ見出しをあらかじめ入力しておくと、一覧の意味を確認しやすくなります。
条件セルを使った抽出の自動切り替え
条件を数式内へ直接入力すると、担当者や地域を変えるたびに数式の編集が必要になります。
抽出結果シートのF1セルなどに条件を入力し、数式からそのセルを参照すれば、入力値だけで抽出結果を切り替えられます。
=FILTER(売上一覧!A2:D100,売上一覧!C2:C100=F1,”該当なし”)
F1セルへ東京と入力すれば東京のデータが表示され、大阪と入力すれば大阪のデータへ切り替わります。
条件セルにはデータの入力規則を設定し、地域名のリストから選べるようにしておくと、入力ミスの予防にも役立ちます。
条件が空欄のときに全件を表示したい場合は、IF関数を組み合わせて分岐させる方法があります。
=IF(F1=””,売上一覧!A2:D100,FILTER(売上一覧!A2:D100,売上一覧!C2:C100=F1,”該当なし”))
抽出後に並べ替えるSORT関数の利用
抽出した結果を売上の大きい順や商品名順に表示したいときは、SORT関数を外側に重ねます。
売上一覧の4列目を降順で並べる数式は次の通りです。
=SORT(FILTER(売上一覧!A2:D100,売上一覧!C2:C100=F1,”該当なし”),4,-1)
SORT関数の第2引数は並べ替えに使う列番号で、第3引数のマイナス1は降順を示します。
昇順にしたい場合は1を指定しましょう。
元データを直接並べ替えずに抽出シートで並べ替えれば、元表の入力順や管理順を崩さずに済みます。
【操作のポイント】別シート参照ではシート名の変更による参照エラーに注意し、抽出用シートと元データ用シートの役割を分けて管理しましょう。
オートフィルターを使った項目抽出
| 商品名 | 担当者 | 地域 | 売上 |
|---|---|---|---|
| ノートPC | 田中 | 東京 | 120000 |
| モニター | 佐藤 | 大阪 | 80000 |
| キーボード | 田中 | 東京 | 15000 |
続いては、関数を使わずに画面操作で表示する項目を絞り込めるオートフィルターを確認していきます。
一時的に必要なデータを探すだけなら、フィルター機能が最も手早い方法になるでしょう。
フィルターボタンの設定
最初に、見出しを含めた表内の任意のセルを選択します。
次に、リボンのデータタブからフィルターをクリックします。
1行目の各見出しに下向き矢印が表示されれば、フィルター設定は完了です。
フィルターは表の1行目を見出しとして認識するため、データの途中に空白行を入れないことが基本になります。
文字列項目から特定のデータを表示
地域列の矢印をクリックすると、地域名の一覧と選択用のチェックボックスが表示されます。
すべて選択のチェックを外して東京だけにチェックを入れ、OKをクリックすると、東京の行だけが表示されます。
文字列フィルターを使えば、指定の値に等しいデータだけでなく、指定の文字列を含むデータや、特定の文字列から始まるデータも抽出できます。
商品名にPCという文字を含む行を探す場合は、商品名列の矢印から文字列フィルターを選び、指定の値を含むを使います。
抽出されなかった行は削除されるのではなく非表示になるだけなので、フィルターを解除すれば元の一覧へ戻せます。
数値項目と日付項目の条件指定
売上のような数値列では、数値フィルターから指定の値以上、指定の値以下、指定の範囲内などを選べます。
売上が50000以上のデータを表示したい場合は、売上列の矢印をクリックし、数値フィルターから指定の値以上を選択します。
条件欄に50000を入力すれば、基準を満たす行だけが残ります。
日付列の場合は、日付フィルターから今月、先月、指定の期間などの条件を選択できます。
ただし、日付が文字列として入力されていると日付フィルターが正しく表示されないことがあります。
日付の並びや絞り込みがおかしいと感じたら、セルの表示形式だけでなく、実際のデータ型も確認しましょう。
【操作のポイント】オートフィルターは共有用の抽出結果を作るより、元表を確認しながら条件を試したい場面に向いています。
特定項目を取り出すXLOOKUP関数
| 商品コード | 商品名 | 単価 |
|---|---|---|
| A001 | ノートPC | 120000 |
| A002 | モニター | 80000 |
| A003 | キーボード | 15000 |
続いては、商品コードなどを手がかりにして、対応する項目を1件ずつ取得するXLOOKUP関数を確認していきます。
複数行を絞り込むFILTER関数とは異なり、検索値に対応する項目を別のセルへ表示したいときに役立つ関数です。
商品コードから商品名を取得する数式
F2セルに商品コードを入力し、G2セルに商品名を表示する場合を考えます。
=XLOOKUP(F2,A2:A100,B2:B100,”該当なし”)
F2が検索値、A2:A100が検索する列、B2:B100が返す列です。
つまり、A列からF2と同じ商品コードを探し、見つかった行のB列にある商品名を表示します。
XLOOKUP関数は検索列より左側にある項目も返せるため、VLOOKUP関数より柔軟に使える場面があります。
複数項目を横方向へ表示する方法
商品名と単価を同時に表示したい場合は、返す範囲をB列からC列までに広げます。
=XLOOKUP(F2,A2:A100,B2:C100,”該当なし”)
この数式をG2セルに入力すると、商品名がG2セル、単価がH2セルへ自動的に表示されます。
返す範囲が複数列の場合も、数式を入力するセルの右側を空けておきましょう。
コードを入力して見積書の品名や単価を自動表示するような仕組みでは、この使い方が便利です。
見つからない場合の表示と入力ミス対策
検索値が表に存在しない場合、XLOOKUP関数は通常ならエラーを返します。
第4引数に該当なしなどの文字列を指定しておけば、見つからないときも内容を把握しやすくなります。
検索値に余分な空白が入ると一致しないため、入力規則によるリスト選択や、TRIM関数を使った空白除去も検討しましょう。
商品コードは先頭のゼロが消えやすいため、A001のようなコードは文字列として統一して管理することが重要です。
【操作のポイント】XLOOKUP関数は検索する列と返す列の開始行、終了行を同じ高さにそろえるとエラーを防ぎやすくなります。
重複を除く一覧の抽出と空白の処理
| 担当者 | 抽出後の担当者一覧 |
|---|---|
| 田中 | 田中 |
| 佐藤 | 佐藤 |
| 田中 | 鈴木 |
| 鈴木 |
続いては、重複した項目を1つにまとめ、空白セルを除外した一覧を作る方法を確認していきます。
担当者名、顧客名、商品カテゴリーなどの候補リストを作成するときに活用できます。
UNIQUE関数による重複しない項目の取得
A2:A100に担当者名が入力されている場合、重複しない名前だけを抽出する数式は次の通りです。
=UNIQUE(A2:A100)
同じ名前が何度登場しても、抽出結果には1回だけ表示されます。
担当者別の集計表や、プルダウン用の候補を作る前段階として便利な方法です。
UNIQUE関数の結果は元データが追加されると自動で変わるため、手作業で重複を削除するより更新漏れを防げます。
空白セルを除外した項目一覧
UNIQUE関数だけでは、元の範囲に空白が含まれていると、抽出結果にも空白が表示されることがあります。
空白を除外するには、FILTER関数で空欄でないセルだけを取り出してからUNIQUE関数へ渡します。
=UNIQUE(FILTER(A2:A100,A2:A100<>””))
不等号記号と二重引用符を組み合わせた条件は、セルが空白ではないことを意味します。
候補一覧に不要な空行が出る場合は、この形で数式を組むと整いやすくなります。
並べ替えを含めた候補リストの作成
名前を五十音順などで並べたい場合は、SORT関数をさらに組み合わせます。
=SORT(UNIQUE(FILTER(A2:A100,A2:A100<>””)))
この数式は、空白を除外し、重複を消し、その結果を昇順で並べ替える流れです。
候補一覧をデータの入力規則で参照するときは、抽出結果のスピル範囲を利用する設定にすると、候補の増減にも対応しやすくなります。
【操作のポイント】空白に見えるセルでも数式が入っている場合は完全な空欄ではないため、必要に応じてLEN関数で文字数を確認しましょう。
まとめ 必要な項目をエクセルで抽出する方法
| 目的 | 適した方法 |
|---|---|
| 条件に合う複数行を表示 | FILTER関数 |
| 別シートに一覧を作成 | FILTER関数と別シート参照 |
| 対応する1項目を取得 | XLOOKUP関数 |
| 一時的に表示を絞る | オートフィルター |
Excelで項目を抽出する方法は、何を取り出したいか、結果をどこで使いたいかによって選ぶことが大切です。
条件に一致する複数の行を自動表示したい場合は、FILTER関数が中心となる便利な方法です。
元データと抽出結果を分けて管理したいときは、別シート参照を組み合わせると、報告用の一覧を更新しやすくなります。
商品コードから商品名や単価を表示するような1件検索には、XLOOKUP関数が適しています。
一方で、数式を作らずに今すぐ特定のデータを確認したい場合は、オートフィルターを使うとよいでしょう。
重複を除いた候補一覧にはUNIQUE関数、空白を除外したい場合にはFILTER関数との組み合わせが役立ちます。
抽出用の数式では、元データの見出しが1行目にあること、参照範囲の行数をそろえること、出力先を空けておくことが基本です。
必要な項目をすばやく取り出せる表へ整え、日々の確認作業や集計作業を効率化していきましょう。
