日々の業務やデータ整理でエクセルの検索機能を使うとき、複数のワードを同時に探したり、複数条件に合うデータを抽出したりしたくて困ったことはありませんか。インターネットの検索エンジンのようにCtrlとFの検索窓へスペース区切りで単語を入れても、うまくヒットしなくて戸惑ってしまうことはよくありますね。
また、VLOOKUP関数やXLOOKUP関数、INDEX関数とMATCH関数の組み合わせなどを使って複数条件から1件のデータを取得したい場面や、FILTER関数などで複数該当する2番目以降のデータをすべて抽出して一覧にしたいケースもあるかなと思います。さらに、条件付き書式とCOUNTIF関数で複数の単語をハイライトして色付けしたい、ワイルドカードであいまい検索をしたい、フィルターの詳細設定でANDやORの絞り込みをしたい、別シートや複数シートからブック全体を一括で探したいなど、やりたいことによって解決策がまったく違ってくるのが難しいところです。

私自身も最初はどの機能や関数を使えばいいのか迷ってしまい、#N/Aや#SPILL!といったエラーが出たり、フィルターが途中の行までしか効かなかったりして悩んだ経験があります。でも、目的とエクセルのバージョンに合わせて手順を整理しておけば、作業列なしのスッキリした表作りや、マクロやPower Queryを使った大量データの処理まで驚くほどスムーズにできるようになりますよ。この記事では、皆さんが抱えている疑問を一つずつ解消できるよう、具体的でわかりやすい手順をまとめましたので、ぜひ参考にしてみてくださいね。
- CtrlとFの裏技やワイルドカード、条件付き書式で複数ワードを同時検索・色付けする手順
- XLOOKUPやVLOOKUP、INDEX+MATCH関数で複数条件に一致する1件を取得する方法
- FILTER関数や連番の作業列を使って、複数該当する2件目以降のデータをすべて抽出するテクニック
- フィルターの詳細設定によるAND・OR抽出や複数シートの一括検索、エラーが出る原因と対処法
【やりたいこと×Excelバージョン別 早見表】
| やりたいこと(目的) | 求める出力結果 | Microsoft 365 / Excel 2021以降の最適解 | Excel 2019 / 2016以前の最適解 |
|---|---|---|---|
| 複数ワードを同時に探して色付けしたい | 該当セルをハイライト | 条件付き書式 + COUNTIF関数 | 条件付き書式 + COUNTIF関数 |
| 1セル内に複数ワードが両方含まれるか探したい | 該当セルの特定 / 判定 | Ctrl+Fの2段階検索 / SUM(COUNTIF(…)) | Ctrl+Fの2段階検索 / {=SUM(COUNTIF(…))} |
| 複数条件に合う「1件の値」を取得(作業列なし) | 単一セルへの値返却 | XLOOKUP関数(掛け算 * または &) | INDEX+MATCH関数(配列数式)またはDGET関数 |
| 複数条件に合う「1件の値」を取得(手軽さ重視) | 単一セルへの値返却 | XLOOKUP関数 | VLOOKUP関数 + 作業列(&結合) |
| 条件に合う「複数行すべて」を別表に自動抽出 | 複数行・複数列のリスト | FILTER関数(ANDは *、ORは +) | 作業列(COUNTIF連番)+ INDEX/MATCH |
| 関数を使わずに複雑なAND/OR条件で抽出したい | 表の絞り込み / 別表コピー | フィルターの詳細設定 または FILTER関数 | フィルターの詳細設定(フィルターオプション) |
- エクセルの検索で複数の条件や単語を探す基本方法
- エクセルの検索で複数の該当データやシートを扱う応用
エクセルの検索で複数の条件や単語を探す基本方法
まずは、エクセルのシート内から複数の単語(キーワード)を同時に見つけ出す方法や、複数の条件をすべて満たす「1件のデータ」をピンポイントで取得する基本的なテクニックから見ていきましょう。標準の検索ダイアログのちょっとした工夫から、条件付き書式を使った視覚的な色付け、そして実務で大活躍する検索関数の使い分けまで、順番にわかりやすく解説していきますね。
CtrlとFで複数ワードを同時検索する手順
Webブラウザで調べものをするときは、検索窓に「東京 営業部」のようにスペース(空白)を入れて複数の単語を入力すると、両方の言葉を含むページを勝手に探してくれますよね。そのため、エクセルでもキーボードのCtrl + F([ホーム]タブの[検索と選択]→[検索])を押して開く「検索と置換」ダイアログに、スペース区切りで複数ワードを入力してみる方が多いかなと思います。
ですが、エクセルの標準検索機能には、スペースで区切って入力しても「スペースを含めた1つの連続した文字列」として認識されてしまい、複数ワードの同時検索(AND検索やOR検索)ができないという仕様上の限界があるんですね。たとえば「123 abc」と入力すると、セルの中に「123 abc」とスペース込みで全く同じ並びで書かれているデータしかヒットしません。
「すべて検索(Alt+I)」の2段階絞り込みで疑似AND検索を行う手順
関数やマクロを一切使わずに、1つのセルの中に「キーワードA(例:123)」と「キーワードB(例:abc)」の両方が含まれているセル(AND検索)を標準機能だけで探したいときは、「すべて検索」を2回に分けて行う2段階絞り込みという手法がとても便利です。
- まずキーボードのCtrl + Fを押して「検索と置換」ダイアログボックスを開きます。
- 「検索する文字列」の欄に1つ目のキーワード(例:123)を入力し、左下にある[すべて検索]ボタンをクリックします(ダイアログが開いている状態でAlt + Iを押しても実行できます)。
- ダイアログの下半分に、「123」を含んでいるすべてのセルの一覧リストがズラッと表示されます。
- このリスト内のどこか1行をクリックしてから、キーボードのCtrl + Aを押します。すると、シート上で1回目の検索にヒットしたすべてのセルが一括で選択された状態になります。
- シート上の該当セルが選択された状態のまま、検索窓の文字を2つ目のキーワード(例:abc)に書き換えます。
- ここで[オプション]を開き、検索場所が「シート」になっていることを確認して[次を検索]または再度[すべて検索]を行うか、選択範囲内を確認することで、両方のワードを含む目的のセルを段階的に特定することができます。
語順が決まっているならワイルドカードで一発検索も可能!
もし同じセルの中で「必ず123が先にあって、その後ろのどこかにabcが続く」というように単語の順番が決まっている場合は、検索窓に*123*abc*と半角アスタリスクを挟んで入力し、[すべて検索]を押すだけで一発でリストアップできますよ。
ワイルドカードで複数パターンをあいまい検索
顧客名や住所の表記揺れがあったり、共通の記号から始まる複数の商品コードをまとめて探したりしたいときは、エクセルが標準でサポートしている3種類のワイルドカード文字(すべて半角で入力)を使うのが一番の近道です。
このワイルドカードは、Ctrl + Fの検索画面だけでなく、オートフィルターの検索ボックスや、後ほど紹介するCOUNTIF関数、VLOOKUP関数、XLOOKUP関数(一致モードに2を指定した場合)など、エクセルのあらゆる場所で共通して使える便利な記号です。
| 記号 | 名称(すべて半角) | 役割と機能定義 | 具体的な検索パターンとヒットする例 |
|---|---|---|---|
| * | アスタリスク | 任意の0文字以上の文字列を代替する | *東京*(部分一致:「東京」をどこかに含む文字すべて) book*(前方一致:Bookmark、Bookcaseなど「book」で始まる文字) *部(後方一致:営業部、企画部など「部」で終わる文字) |
| ? | クエスチョンマーク | 任意の「1文字」のみを代替する(文字数固定に便利) | b?g(最初がb、最後がgの3文字:Bag、Big、Bugなど) ?山?(3文字で2文字目が「山」:富山県、岡山県など) |
| ~ | チルダ(Shift+へ) | 直後の * や ? や ~ を「記号そのもの」として検索させる | ~*(データ内に文字として混入した「*」を検索) *~?(末尾が文字としての「?」で終わるセルを検索) ~~(チルダ「~」自体を検索) |
| * と ? | 複合ワイルドカード | 文字数制限と任意の文字列を組み合わせて指定する | ?島*(2文字目から「島」が始まる文字列すべて) A??*(Aで始まり、合計3文字以上の文字列:A123、Appleなど) |
完全一致オプションとの組み合わせに注意
Ctrl + Fの検索ダイアログでワイルドカードを使うときに少し気をつけたいのが、[オプション]の中にある「セル内容が完全に同一であるものを検索する」のチェック状態です。
たとえば「?山?」と入力して「富山県」のような3文字ぴったりのデータだけを探したいのに、このチェックが外れていると、「和歌山県(4文字)」のように一部に「?山?」の並びを含むデータまで全部ヒットしてしまいます。文字数や先頭・末尾の位置を厳密に指定したいときは、必ず「セル内容が完全に同一であるものを検索する」にチェックを入れて検索するようにしてくださいね。なお、基本操作についてはExcelでセル内容が完全に同一であるものを検索する方法でも詳しく紹介していますので、あわせてチェックしてみてください。
条件付き書式とCOUNTIFで複数ワードに色付け
「シートの中に散らばっている10個〜20個の特定の型番や取引先名を同時に探して、該当するセルすべてに自動で色を塗りたい(ハイライトしたい)」という場面は、実務でとても多いですよね。Ctrl + Fでは1つの単語ずつしか画面上で追えませんが、「条件付き書式」と「COUNTIF関数」を組み合わせれば、複数の検索ワードをまとめて一括で色付けすることができます。
複数ワードを一括でハイライトする設定ステップ
たとえば、表のデータ範囲が「A2:F20」にあり、同時に探したい複数のキーワード(「白猫商事」「製造部」「企画部」など)を、同じシートの空いている列「H2:H4」に縦に並べて入力してあるとします。
- まず、色を付けたい表のデータ範囲全体(例:A2:F20)を左上から右下へドラッグして選択します(見出し行の1行目は除きます)。
- リボンの[ホーム]タブ →[条件付き書式]→[新しいルール]をクリックします。
- ルールの種類の一覧から、一番下にある[数式を使用して、書式設定するセルを決定]を選択します。
- 数式の入力ボックスに、直接キーボードから
=COUNTIF($H$2:$H$4, A2)>=1(または=COUNTIF($H$2:$H$4, A2))と入力します。 - 右下の[書式]ボタンをクリックし、[塗りつぶし]タブから黄色などの目立たせたい色を選んで[OK]を押し、元の画面でも[OK]を押して完了です。
【最重要】絶対参照($)と相対参照の使い分けルール
この数式で一番つまずきやすいのが「$」マークの付け方です。COUNTIF関数の第1引数である「検索ワードリストの範囲($H$2:$H$4)」は、範囲がズレないように必ず「$」を付けた絶対参照にします。
一方で、第2引数に指定する「最初に選択したデータ範囲の左上のセル(A2)」には、絶対に「$」を付けず相対参照のままにしてください。ここに「$A$2」と付けてしまうと、表全体がA2セルの結果だけで判定されてしまい、正しく色がつきません。
行全体への色付けや「部分一致」でのハイライト応用
もし一致したセル単体ではなく、「一致したワードが含まれる行全体」をまとめて塗りつぶしたい場合は、上記の第2引数を $A2 のように列だけ固定する複合参照にするか、数式を =SUM(COUNTIF($A2:$F2, $H$2:$H$4))>0 と記述することで行単位のハイライトが可能になります。
また、「確認」と「確認中」のように特定の文字を含む部分一致(あいまい検索)で色付けしたいときは、=COUNTIF($D3, "確認*")=1 や、検索セルJ1を参照して =COUNTIF($A4, "*"&$J$1&"*") のように記述します。条件付き書式の数式で =$D3="確認*" と直接イコールで結んでも、エクセルは「*」をただの記号として扱ってしまいますが、COUNTIF関数を通すことで初めて「*」がワイルドカードとして機能するというルールを覚えておくと非常に役立ちますよ。
セル内に複数の特定文字が含まれるかを関数で判定する方法
色付けではなく、隣のセルに「該当 / 非該当」や「TRUE / FALSE」と文字で判定結果を出したい場合は、以下の数式をセルに直接入力します。
- 複数ワードのいずれかを含むか(OR判定):
=SUM(COUNTIF(A3, "*"&$E$3:$E$5&"*"))>0
E3:E5に入力した複数の単語のうち、どれか1つでもA3セルに含まれていればTRUEになります(Excel 2019以前では入力後に Ctrl + Shift + Enter で確定します)。 - 複数ワードのすべてを含むか(AND判定):
=SUM(COUNTIF(A3, "*"&$E$3:$E$5&"*"))=COUNTA($E$3:$E$5)
ヒットした単語の合計数が、リストの単語総数(COUNTA)と一致するかを調べることで、すべてのキーワードが含まれている場合のみTRUEを返します。 - 大文字・小文字を区別して判定したい場合(SEARCH / FIND+ISNUMBER):
COUNTIF関数は大文字と小文字を区別しません。厳密に区別したいときはFIND関数、区別しないときはSEARCH関数を使い、=IF(ISNUMBER(SEARCH("book", A1)), "含まれている", "含まれていない")のように記述するとスマートに判定できます。
XLOOKUP関数で複数条件のデータを1件取得
ここからは、「支店名が『東京』かつ 商品名が『シャンプー』の単価を取り出したい」というように、複数の列にまたがる条件(AND条件)をすべて満たす1件のデータを別のセルに引き当てる関数の使い方を見ていきましょう。
まず、現在のエクセルで主流となっている4つの検索関数の違いを整理しておきますね。
| 比較項目 | XLOOKUP関数 | VLOOKUP関数(+作業列) | INDEX + MATCH関数 | DGET関数 |
|---|---|---|---|---|
| 対応バージョン | Microsoft 365 / Excel 2021以降 | 全バージョン対応 | 全バージョン対応 | 全バージョン対応 |
| 作業列・条件表 | 不要(数式内で直接計算可) | 作業列が必須(左端に&結合列) | 不要(単一の数式で完結) | 条件表が必須(見出し+条件) |
| 検索列の配置制約 | 制約なし(左側の列も取得可) | 検索範囲の左端列であることが必須 | 制約なし(左側の列もクロス抽出も可) | 制約なし(見出し名等で指定) |
| エラー処理 | 第4引数で直接指定可(IFNA不要) | IFNA関数などでのネストが必要 | IFNA関数などでのネストが必要 | IFERROR等が必要(複数該当でエラー) |
もしお使いの環境が Microsoft 365 または Excel 2021 / 2024 以降であれば、迷わずXLOOKUP関数を使うのが一番おすすめです。元の表に作業列を追加する必要がなく、検索列より左側にあるデータでも自由に取り出すことができます。
方法1:論理積(* 掛け算)を使って「1」を検索する書き方
XLOOKUP関数で複数条件を指定する最もスマートな方法が、条件式同士を掛け算(*)でつないで「1」を探す書き方です。
【数式の構文例】=XLOOKUP(1, (B2:B10="食費") * (C2:C10="外食"), A2:A10, "見つかりません")
この数式が動く仕組みはとても面白いのですが、エクセルの内部では (B2:B10="食費") という条件が成り立つ(TRUE)行は「1」、成り立たない(FALSE)行は「0」に変換されます。同じように (C2:C10="外食") も「1」と「0」の並びに変換されます。
この2つを掛け算(*)すると、両方の条件を同時に満たす行だけが「1 × 1 = 1」となり、どちらか片方でも外れている行は「1 × 0 = 0」になります。そのため、XLOOKUPの第1引数(検索値)に「1」を指定しておくことで、両方の条件が成立している行のA列(日付など)をピタッと引き当てられるわけですね。条件が3つ、4つと増えた場合も、*(D2:D10="カード決済") のように後ろへ掛け算を足していくだけで簡単に拡張できます。
方法2:&(アンパサンド)で条件と範囲を直接結合する書き方
もう一つの直感的な書き方が、&(アンパサンド)を使って検索値と検索範囲をそれぞれ数式の中でくっつけてしまう方法です。
【数式の構文例】=XLOOKUP(E2&F2, A2:A7&B2:B7, C2:C7, "該当なし")
たとえばE2セルに「東京支店」、F2セルに「営業部」と入力されているとき、第1引数で E2&F2 と結合し、第2引数の検索範囲でも A2:A7&B2:B7 と列同士を直接結合してしまいます。従来のVLOOKUP関数では範囲同士を数式内で直接結合することができませんでしたが、XLOOKUP関数なら作業列を作らずにこの記述がそのまま通るのでとても手軽です。
XLOOKUPならではの便利な機能
XLOOKUP関数は、第4引数に "該当なし" や ""(空白)を入れておくだけで、データが見つからないときのエラー処理を完結できます。さらに、第6引数の[検索モード]に -1 を指定すると、同じ条件のデータが複数あった場合に「下から探して最後(最新)に見つかった1件」を取得できるという独自の強みもあります。
VLOOKUP関数と作業列で複数条件を検索する
取引先とファイルをやり取りする場合や、職場のパソコンが Excel 2019 / 2016 以前である場合は、XLOOKUP関数が使えないため、すべてのバージョンで動くVLOOKUP関数を使うことになります。
ただ、VLOOKUP関数(=VLOOKUP(検索値, 検索範囲, 列番号, FALSE))には、「検索値を1つしか指定できない」「検索範囲の一番左端の列しか探せない」という2つの絶対的なルールがあります。そこで、元の表に「作業列」を1列追加して、複数の条件を1つの文字列に合体させてしまうのが王道のテクニックです。
作業列を使ったVLOOKUP複数条件検索の2ステップ
- 【ステップ1:元の表の左端に作業列を作る】
元のデータ表の一番左側(または取り出したいデータの列よりも左側、たとえばD列)に新しい列を1列挿入して「作業列」とします。その先頭行(D3セル)に=E3&F3(例:店舗名セル & 商品名セル)と入力し、一番下の行までオートフィルでコピーします。これで「B店シャンプー」のように2つの条件が合体した検索キーの列ができあがります。 - 【ステップ2:VLOOKUP側の検索値も「&」で結合する】
結果を表示したいセルに、=IFNA(VLOOKUP(J2&J3, $D$3:$G$11, 4, FALSE), "")と入力します。第1引数の検索値もJ2&J3のように「&」でつなぎ、第2引数の検索範囲には先ほど作った作業列(D列)が必ず左端(1列目)になるように絶対参照($付き)で指定します。あとは作業列を1列目と数えて何列目のデータを取り出すか(例:4)を指定し、完全一致のFALSEを入れれば完了です。
作業列を作れない環境なら「INDEX+MATCH関数」または「DGET関数」
「共有ファイルだから元の表に作業列を勝手に追加できない!」「でもExcel 2019以前だからXLOOKUPも使えない!」という場面もありますよね。そんなときは、以下の2つの方法のどちらかを使うと作業列なしで解決できます。
- INDEX関数+MATCH関数の組み合わせ(左側の列もクロス抽出も可能):
=INDEX($G$3:$G$8, MATCH(1, ($E$3:$E$8=A4)*($F$3:$F$8=B4), 0))
または=INDEX($G$3:$G$8, MATCH(A4&B4, $E$3:$E$8&$F$3:$F$8, 0))と記述します。MATCH関数で複数条件に一致する「上から何行目か」を特定し、INDEX関数でその行の値を取り出す仕組みです。Excel 2019以前のバージョンでは、数式を入力した後に通常のEnterではなく Ctrl + Shift + Enter を同時に押して「配列数式(数式の両端に { } がつく状態)」として確定してください(忘れると #VALUE! エラーになります)。また、縦の見出しと横の見出しが交差する場所を抜くクロス検索なら、INDEXの行番号と列番号の両方にMATCHを入れることでCtrl+Shift+Enterなしでスマートに取得できます。 - DGET関数(条件表を使ってデータベースから1件抽出):
配列数式が苦手な方にとてもおすすめなのが、=DGET(データベース, フィールド, 検索条件)(例:=DGET($C$6:$F$12, E6, $C$15:$D$16))というデータベース関数です。表の外の空いている場所(C15:D16など)に、元の表と全く同じ見出し名をコピーして並べ、そのすぐ下の行に検索したい条件を入力します。構造がシンプルでわかりやすいですが、条件に合うデータが2件以上あると#NUM!エラーになるため、社員番号や重複のない組み合わせから1件を正確に抜きたいときに使いましょう。
エクセルの検索で複数の該当データやシートを扱う応用

ここまでは「複数の条件から1つの値を取り出す方法」を見てきましたが、実務では「条件に一致するデータが何件もあって、2件目以降の該当データもすべて漏れなく抜き出して一覧表にしたい」というケースや、「複数のシートにまたがって検索したい」という場面もよくありますよね。ここからは、複数該当データの全件抽出テクニックやフィルターの詳細設定、シート横断検索、そしてトラブル解決法まで応用的な内容を掘り下げていきます。
FILTER関数で複数該当データをすべて抽出する
VLOOKUP関数やXLOOKUP関数は、仕様上「上から順番に探して、最初に見つかった1件目」しか返してくれません。そのため、同じ担当者や同じ商品のデータが表の中に何行もあった場合、2件目以降のデータは無視されてしまいます。
もし Microsoft 365 や Excel 2021 / 2024 以降をお使いなら、この「複数該当データの全件抽出」はFILTER関数を使うことで驚くほど簡単に解決できます。先頭の1つのセルに数式を入れるだけで、該当するすべての行と列が自動的に下や右のセルへ溢れ出すように展開(スピル)され、元データが書き換われば抽出リストもリアルタイムで自動更新されます。
【FILTER関数の基本構文】=FILTER(配列, 含む, [空の場合])
- 第1引数(配列):抽出したい元のデータ範囲(見出し行を除いたデータ部分、例:
A2:E100)を指定します。 - 第2引数(含む):どの行を抜き出すかを決める条件式を指定します。
- 第3引数(空の場合):条件に合うデータが1件もなかったときに表示する文字(例:
"該当なし"や"")を指定します。ここを省略すると0件のときに#CALC!エラーが出るため、必ず指定しておくのがコツです。
FILTER関数での複数条件(AND・OR・複合・部分一致)の書き方パターン
FILTER関数で複数の条件を組み合わせるときは、AND条件(かつ)には掛け算「*」を使い、OR条件(または)には足し算「+」を使います。それぞれの具体的な記述パターンを表にまとめました。
| 条件の種類 | 使う記号・関数 | 具体的な数式の記述例 | 作成時のポイントと注意点 |
|---|---|---|---|
| AND条件 (すべて満たす) | *(アスタリスク) | =FILTER(A2:E100, (B2:B100="営業部")*(C2:C100="東京"), "該当なし") | 各条件式を必ずカッコ () で囲んでから * でつなぎます。日付が抽出されて数値(シリアル値)になった場合は、表示形式を「日付」に変えます。 |
| OR条件 (いずれかを満たす) | +(プラス) | =FILTER(A2:E100, (B2:B100="営業部")+(B2:B100="企画部"), "該当なし") | 条件をカッコで囲んで + で足します。両方満たすと計算結果が2になりますが、FILTER関数は0以外をすべてTRUE(抽出対象)とみなすため正常に動きます。 |
| AND + OR (複合条件) | OR全体を (()) で囲んでから * | =FILTER(A2:E100, ((B2:B100="営業部")+(B2:B100="企画部"))*(D2:D100>=100000), "該当なし") | 算数と同じで足し算より掛け算(*)が優先されるため、OR条件の外側を必ず二重カッコ ((条件1)+(条件2)) で括るのが絶対ルールです。 |
| 部分一致 (特定文字を含む) | ISNUMBER + SEARCH | =FILTER(A2:D100, ISNUMBER(SEARCH("book", B2:B100)), "該当なし") | FILTER関数の条件式では B2:B100="*book*" のようなワイルドカードが効かないため、SEARCH関数とISNUMBER関数を組み合わせて判定します。 |
| 抽出+並べ替え | 外側を SORT関数 で包む | =SORT(FILTER(B3:D12, C3:C12="バナナ")) | 抽出した複数行の結果を、そのまま日付順や金額順で自動的に並べ替えて表示できます。 |
INDEXとMATCH関数で2番目以降も一覧抽出
「Excel 2019 や 2016 以前を使っていてFILTER関数がないけれど、条件に合う2番目以降の複数データもすべて別表に自動抽出したい!」という場合は、元の表に「COUNTIF関数で連番を振る作業列」を1つ作り、それをINDEX+MATCH関数(またはVLOOKUP関数)で順番に拾い上げる方法が一番おすすめです。配列数式のように動作が重くならず、初心者の方でも仕組みが理解しやすいのがメリットです。
手順1:作業列で条件に一致する行だけに「1, 2, 3…」と連番を振る
たとえば、元の表が3行目〜12行目(B列が担当者、C列が商品名、D列が売上)にあり、G2セルに入力した担当者名と一致する行をすべて抜き出したいとします。
まず、元の表の空き列(例:E列、もしVLOOKUPを使うなら表の左端であるA列)を作業列にして、先頭のE3セルに次の数式を入力し、E12まで下へコピーします。
【作業列(E3セル)の数式】=IF(B3=$G$2, COUNTIF($B$3:B3, $G$2), "")
この数式のポイントは、COUNTIF($B$3:B3, $G$2) の範囲指定で始点の $B$3 だけを絶対参照にし、終点の B3 を相対参照のままにしているところです。これを下へコピーすると、4行目では $B$3:B4、5行目では $B$3:B5 と範囲がゴムのように伸びていくため、条件に合う行が出てくるたびに「1、2、3…」と重複しない連番が自動で振られます(条件に合わない行は空白 "" になります)。
もし複数条件で連番を振りたい場合は、COUNTIFの代わりにCOUNTIFS関数を使って =IF(AND(B3=$G$2, C3=$H$2), COUNTIFS($B$3:B3, $G$2, $C$3:C3, $H$2), "") とすればバッチリ対応できます。
手順2:振られた連番(1, 2, 3…)をROW関数で順番に検索して抜き出す
次に、抽出先の表の先頭セル(例:G5セル)に以下のいずれかの数式を入れて、必要な列数分だけ右へコピーし、想定される最大件数分だけ下へオートフィルでコピーします。
- INDEX+MATCH+ROW方式(作業列がどこにあってもOKな万能型):
=IF(MAX($E$3:$E$12)<ROW(A1), "", INDEX(B$3:B$12, MATCH(ROW(A1), $E$3:$E$12, 0)))ROW(A1)は「1」を返し、下の行へコピーすると自動的にROW(A2)で「2」、ROW(A3)で「3」と増えていきます。これを使って作業列($E$3:$E$12)の連番1、2、3…がある行位置をMATCH関数で探し、INDEX関数で値を取り出します。先頭のIF(MAX(...)<ROW(A1), "", ...)を入れておくことで、該当件数を超えた行のエラーをきれいに空白にできます。 - VLOOKUP+ROW+COLUMN方式(作業列を表の左端A列に作った場合):
=IFERROR(VLOOKUP(ROW(A1), $A$3:$D$12, COLUMN(B1), FALSE), "")
表の一番左端に連番の作業列を作れば、おなじみのVLOOKUP関数でも2件目以降の全件抽出ができます。右へコピーしたときに列番号が2、3、4…と自動で増えるよう、第3引数にCOLUMN(B1)を使うのが時短テクニックです。
作業列をどうしても作れない場合の配列数式(INDEX+SMALL+IF+ROW)
Excel 2019以前で作業列も追加できない場合は、抽出先の先頭セルに =IF(COUNTIF($B$3:$B$12, $G$2)<ROW(A1), "", INDEX(B$3:B$12, SMALL(IF($B$3:$B$12=$G$2, ROW($A$1:$A$10)), ROW(A1)))) と入力し、Ctrl + Shift + Enter で確定してから右と下へコピーする方法もあります。複数条件にする場合は IF(($B$3:$B$12=$G$2)*($C$3:$C$12=$H$2), ROW($A$1:$A$10)) のように条件を掛け算します。また、Microsoft 365以降で件数を5件までに絞って抜きたい場合などは、作業列と組み合わせて =XLOOKUP(SEQUENCE(5), $E$3:$E$12, B3:B12, "") と記述する方法も便利です。
フィルターの詳細設定で複数のANDやOR抽出
「複雑な関数を組むのはちょっとハードルが高いから、エクセルのメニュー操作だけで複数条件の絞り込みや別シートへの抽出をやりたい」という方も多いですよね。関数を使わないフィルター機能には、主に「オートフィルター」「フィルターの詳細設定(フィルターオプション)」「テーブル+スライサー」の3つがあります。
オートフィルターでの複数選択と「複数列OR条件」の裏技
表の中で Ctrl + Shift + L([データ]タブ →[フィルター])を押すと使えるオートフィルターは、同じ列の中で複数の項目にチェックを入れれば「OR条件(営業部 または 企画部)」になり、複数の列で順番にフィルターをかければ自動的に「AND条件(営業部 かつ 東京)」になります。また、フィルターの検索ボックスで2つ目以降の単語を追加したいときは、検索ワードを入力したあとに「現在の選択範囲をフィルターに追加」にチェックを入れてOKを押せば、前の選択を残したまま複数ワードをOR追加できます。
ただし、オートフィルターには「国語・数学・英語のどれか1つでも90点以上」といった複数列をまたぐOR条件が設定できないという弱点があります。これを簡単に突破するには、表の右端に作業列を1つ作り、=(E2>=90)+(F2>=90)+(G2>=90) という足し算の数式を入れます。どれか1教科でも90点以上なら計算結果が「1以上(1〜3)」になるので、その作業列を数値フィルターで「1以上」に絞り込めば、複数列のOR抽出があっという間に完了します。なお、表を Ctrl + T で「テーブル」に変換し、[テーブルデザイン]タブから「スライサーの挿入」を使えば、画面上のパネルのボタンをポチポチとクリックするだけで複数条件を視覚的に切り替えることもできますよ。
フィルターの「詳細設定(オプション)」で複雑な条件表を作るルール
同じ列で3つ以上の条件を細かく指定したいときや、「(販売員が西脇 かつ 売上が3000超)または(販売員が吉田 かつ 売上が1500超)」のような複雑なAND・OR条件を一発で抽出したいときは、[データ]タブ →[並べ替えとフィルター]グループにある[詳細設定]を使います。
詳細設定を使うときは、表の外の空いている場所(元の表から1列以上空けた右側や別シート)に「検索条件範囲(条件表)」を事前に作っておきます。この条件表の作り方のルールは以下の通りです。
- 見出しは必ずコピペする:条件表の1行目には、元の表の見出しと一字一句同じ文字を並べます。手打ちすると全角・半角やスペースの違いでエラーになるため、必ず元の表の見出しをコピー&ペーストしてください。
- 横並びは「AND」、縦並びは「OR」:条件表の2行目以降で、同じ行の横に並べて書いた条件はすべて「AND(かつ)」になり、行を変えて縦に並べて書いた条件は「OR(または)」になります。
- 同じ列の数値や日付を「〇〇以上 かつ 〇〇以下」にする場合:たとえば売上を「3000より大きく、かつ6500未満」にしたいときは、条件表の見出し行に「売上」という列を横に2つ並べてコピーし、同じ行に
>3000と<6500を並べて書きます。 - 完全一致で抽出したいときは
="=文字"と書く:詳細設定の条件表にただ「A」や「農産物」と入力すると、仕様上「その文字で始まるデータ(前方一致)」がすべて抽出されてしまいます(Aと書くとAAやABCまで出てしまいます)。文字数がぴったり一致するものだけを抜きたいときは、セルに="=農産物"のように入力するのが重要なコツです。
| 条件表の行 | 種別 | 販売員 | 売上 | 売上 | この行が意味する抽出条件 |
|---|---|---|---|---|---|
| 条件行1(2行目) | ="=農産物" | ="=西脇" | >3000 | <6500 | 種別が「農産物」かつ 販売員が「西脇」かつ 売上が3000超〜6500未満 |
| 条件行2(3行目) | (空欄) | ="=吉田" | >1500 | (空欄) | または、販売員が「吉田」かつ 売上が1500超(種別や上限は問わない) |
【超重要】詳細設定で「別シート」へ抽出するときのエラー回避手順!
詳細設定のダイアログで[指定した範囲]を選び、抽出先に別のシートを指定すると、「抽出データを作業中のシート以外へコピーすることはできません。」というエラーが出て止まってしまうことがあります。
これを防ぐ鉄則は、「必ずデータを出力したい先の白紙シート(抽出先シート)を先に開いてアクティブにしてから、[詳細設定]ボタンをクリックする」ことです!出力先シートの上で詳細設定を開き、[リスト範囲]の欄をクリックしてから元データのシートへ移動して範囲を選択すれば、エラーにならず綺麗に別シートへ抽出できます。
また、出力先シートにあらかじめ「欲しい列の見出しだけ(例:日付と金額だけ)」をコピーして並べておき、[抽出範囲]にその並べた見出しを選択してOKを押せば、何十列もある表から必要な列だけを抜き出して別シートに作成することも可能です。
検索場所をブックにして複数シートを一括検索する
月別の売上シートや支店別の管理シートなど、1つのエクセルファイルの中にたくさんのシートがあるとき、1シートずつ順番に検索していくのは大変ですよね。ここからは、複数シートや複数ファイルをまたいで横断検索する方法を整理しておきましょう。
Ctrl+Fの検索場所を「ブック」に変えて全シートを一括検索
Ctrl + Fの標準検索は、初期状態では「いま開いているシート(アクティブシート)」しか探してくれませんが、設定を1箇所変えるだけでファイル内の全シートを一括検索できます。
- キーボードのCtrl + Fを押して「検索と置換」を開きます。
- 右側にある[オプション]ボタンをクリックして詳細メニューを広げます。
- 左側にある[検索場所]のプルダウンを、初期値の「シート」から「ブック」に変更します。
- 検索したいワードを入力して[すべて検索](Alt + I)をクリックすると、全シートの該当箇所が一覧表示され、クリックするだけでそのシートの該当セルへ一瞬でジャンプできます。
もし「全シートではなく、特定の3つのシートだけを対象に検索したい」という場合は、キーボードの Ctrl キー(連続するシートなら Shift キー)を押しながら画面下のシート見出しをクリックして複数シートを選択(作業グループ化)した状態で Ctrl + F を行えば、選択したシートだけをまとめて検索できます。終わったあとは誤操作を防ぐため、必ずシート見出しを右クリックして「作業グループ解除」をしておきましょう。
別シートの表から関数でデータを参照する書き方
別シート(例:「請求データ」シート)にある表から、集計用シートへVLOOKUP関数やFILTER関数で複数条件のデータを引っ張ってきたいときは、範囲の前に「シート名!(半角の感嘆符)」を付けます。
たとえば =FILTER(請求データ!A2:E100, (請求データ!D2:D100="未入金")*(請求データ!C2:C100>=30000), "該当なし") のように記述します。手でシート名を打つとタイポしやすいので、数式のカッコやカンマを入力した状態で、そのままマウスで別シートのタブをクリックして範囲をドラッグ選択し、F4キーを押すようにすると入力ミスがなくなりますよ。シート間の自動反映については、エクセルで別シートへ自動反映させる究極のガイドでもさらに詳しく解説しています。
大量の検索ワードや複数ファイルの一括処理なら「Power Query」や「VBA」
さらに一歩進んだ業務効率化として、「検索したい型番リストが50個あって、毎週リストを入れ替えて抽出したい」とか「フォルダに入っている数十個のエクセルファイルをまとめて検索したい」という場合は、Excel 2016以降に標準搭載されているPower Query(パワークエリ)やVBAマクロの出番です。
- Power Query(List.Contains / Text.Contains)の活用:
[データ]タブの[テーブルまたは範囲から]を使って、元データの表と検索ワードリスト(列を右クリックして「ドリルダウン」し、単一リスト化しておく。例:FilterList)をPower Queryに読み込みます。クエリのフィルター式(Table.SelectRows)の条件部分を、完全一致ならeach List.Contains(FilterList, [対象列名])、部分一致ならeach List.AnyTrue(List.Transform(FilterList, (kw) => Text.Contains([対象列名], kw)))と書き換えておけば、次回からはエクセルシート上の検索ワードリストを書き換えて「すべて更新」を押すだけで、何十個ものキーワードによる抽出が自動で完了します(Text.Containsを使う際は事前に対象列のデータ型をテキストに変換しておきます)。 - VBAマクロ(Find / FindNext や2次元配列)の活用:
ブック内の全シートやフォルダ内の全ファイルを横断して、複数ワードを一括で色分けハイライトしたりリンク一覧を作ったりしたい場合は、VBAでArray("りんご", "みかん")のように検索語を配列に入れ、Range.FindとRange.FindNextでループ処理を行うか、UsedRangeを2次元配列に格納してInStr関数やLike演算子で高速照合するマクロを組むことで、数万行のデータでも一瞬で処理できるようになります。
複数検索で出るエラーやフィルター不具合の対処法
せっかく関数を組んだりフィルターを設定したりしても、エラー表示が出たり途中までしか抽出されなかったりすると焦ってしまいますよね。ここでは、複数検索・抽出でよくある4大エラーと、フィルターが効かない5大原因の解決策をチェックリスト形式でまとめました。
検索・抽出関数の4大エラー原因と解決プロトコル
| エラー表示 | 主な発生関数 | よくある原因 | 確実な解決策・対処法 |
|---|---|---|---|
| #N/A | VLOOKUP XLOOKUP MATCH | ・検索値が元表に存在しない ・数値と文字列の型が不一致 ・末尾などに余分なスペースがある ・コピー時に絶対参照($)がズレた | ・対象列を選択し[データ]→[区切り位置]→[完了]でデータ型を統一する ・ TRIM関数で余分な空白を消す・エラー非表示には IFNA関数 を使う |
| #SPILL! | FILTER XLOOKUP | ・結果が展開される先のセルにすでに別の文字が入っている ・展開先に結合セルがある ・テーブル(Ctrl+T)の中に数式を入れた | ・点線枠の中にある邪魔なデータを消すか移動し、結合セルを解除する ・テーブル内ではスピルしないため、表の外に数式を書くか[範囲に変換]する |
| #CALC! | FILTER | ・複数条件に合うデータが0件で、かつ第3引数[空の場合]を省略している | ・FILTER関数の第3引数に必ず "該当なし" または "" を指定する |
| #VALUE! | XLOOKUP FILTER INDEX+MATCH | ・複数条件で比較する各範囲の行数がズレている(例:A2:A10とB2:B15) ・2019以前で配列数式を通常Enterした | ・複数条件で指定するすべての範囲の開始行と終了行をぴったり揃える ・2019以前は Ctrl + Shift + Enter で確定する |
エラー処理には「IFERROR」よりも「IFNA」を使うのが安全!
VLOOKUPなどで「該当がないときに空白にする」処理として IFERROR(数式, "") がよく紹介されますが、IFERRORは #REF!(参照先が消えた)や #NAME?(関数名のスペルミス)、#VALUE!(範囲の行数ズレ)といった数式自体の重大なミスまですべて隠して空白にしてしまうという怖さがあります。
一方で IFNA(数式, "") を使えば、「データが見つからない(#N/A)」ときだけを空白にし、数式のバグはちゃんとエラーとして知らせてくれるため、実務でのミス防止にとても役立ちますよ。
フィルターが途中までしか効かない・押せないときの5大原因
- 途中の行までしか抽出されない(下の行が無視される):
表の途中に「空白行」や「空白列」があると、エクセルが「ここで表が終わった」と判断してしまいます(非表示行が空白になっているケースも多いです)。空白行を削除するか、一度フィルターを解除(Ctrl + Shift + L)してから、手動で表の左上から一番右下まで全体をドラッグ選択した状態で再度[フィルター]をクリックすると、空白行を飛び越えて最後まで認識されます。 - 該当するはずのデータが一部しかヒットしない:
表の中に「結合されたセル」があるのが原因です。たとえば3行分を縦に結合して「東京支店」と入れると、エクセルの内部では一番上の1行目にしか「東京支店」というデータが入っておらず、2行目と3行目は「空欄」として扱われるため検索から漏れてしまいます。フィルターを使う表では結合セルをすべて解除し、空欄になったセルにもデータを埋めるようにしましょう。もしシート内のどこに結合セルがあるか探したいときは、Excelで結合しているセルを検索する方法の手順を使うと一発で見つけられますよ。 - [フィルター]ボタンがグレーアウトして押せない①(複数シート選択):
画面下のシート見出しが複数白くなって同時に選択(作業グループ化)されていると、フィルター機能はロックされて使えません。違うシートのタブをクリックするか、右クリックから「作業グループ解除」を行ってください。 - [フィルター]ボタンがグレーアウトして押せない②(シートの保護):
シートが1つしか選択されていないのに押せない場合は、[校閲]タブの[シート保護の解除]をクリックしてロックを解除します。 - 「選んだ範囲にこの操作を適用できません」と出る:
データが何も入っていない表の外の空白セルを選んだ状態でボタンを押しています。必ず表の内部のセルをクリックしてからフィルターを実行してください。
なお、この記事でご紹介しているショートカットキーや関数の仕様、エラーの挙動などは、お使いのOS(Windows / Mac)やExcelのバージョン、アップデート状況によって画面表示や動作が一部異なる場合があります。業務の重要なデータを扱う際は、事前にバックアップを取ったうえで試してみてくださいね。正確な仕様や最新情報についてはMicrosoftの公式サイトをご確認いただき、システム構築や業務上の最終的な判断は社内の管理者や専門家にご相談ください。
エクセルの検索で複数の条件や項目を扱う方法まとめ
今回は、エクセルの検索で複数のワードを同時に探す方法から、複数条件に合うデータの関数取得、2件目以降の全件抽出、そして複数シートの横断検索まで幅広く解説してきました。最後に、目的別の最適なやり方をもう一度コンパクトに振り返っておきましょう。
- 複数ワードの同時検索・色付け:Ctrl+Fの「すべて検索(Alt+I)」の2段階絞り込みやワイルドカード(
*,?,~)を活用する。複数ワードを一括でハイライトしたいときは「条件付き書式 +=COUNTIF($H$2:$H$4, A2)>=1」を設定する。 - 複数条件から1件のデータを取得:Excel 2021 / 365以降なら作業列がいらないXLOOKUP関数(
(条件1)*(条件2)で1を検索、または&結合)がベスト。旧バージョンとの互換性が必要ならVLOOKUP関数+作業列、作業列が作れないならINDEX+MATCH関数やDGET関数を使い分ける。 - 複数該当データ(2件目以降のすべて)を全件抽出:Excel 2021 / 365以降ならFILTER関数(ANDは
*、ORは+)で一発抽出。2019以前なら作業列でCOUNTIF($B$3:B3, 条件)の連番を振り、INDEX+MATCHやVLOOKUPとROW(A1)を組み合わせて順番に抜き出す。 - 関数なしの絞り込みと別シート抽出:複雑なAND/OR条件はフィルターの「詳細設定(オプション)」が便利。別シートへ抽出するときは、必ず出力先のシートをアクティブにしてから詳細設定を開くのがエラーを防ぐ鉄則。
- 複数シート検索とエラー対策:全シート検索はCtrl+Fの検索場所を「ブック」に変更する。関数のエラー非表示には数式のバグを隠さないIFNA関数を使い、フィルターの不調時は「空白行」と「結合セル」をチェックする。
「複数の検索」と一言で言っても、単語を探したいのか、複数条件を掛け合わせたいのか、それとも複数行をすべて抜き出したいのかによって選ぶべき道具が変わってきます。まずはご自身のエクセルのバージョンと目の前の表の形に合わせて、一番やりやすそうな方法から1つずつ試してみてくださいね。日々のエクセル作業が少しでもラクに、楽しくなるきっかけになれば嬉しいです!
