複数の条件に基づいてExcelで複数の一致する値を返す（完全ガイド）
Excelユーザーは、複数の条件を同時に満たす複数の値を抽出し、すべての一致する結果を列、行、または単一のセルに統合して表示する必要があるシナリオによく遭遇します。このガイドでは、すべてのExcelバージョンおよびExcel 365と2021で利用可能な新しいFILTER関数の使用方法を探ります。
単一のセル内で複数の条件に基づいて複数の一致する値を返す
Excelでは、単一のセル内で複数の条件に基づいて複数の一致する値を抽出することは一般的な課題です。ここでは、2つの効率的な方法を探ります。
方法1: TEXTJOIN関数を使用（Excel365 / 2021, 2019）
すべての一致する値を区切り文字付きで1つのセルに取得するには、TEXTJOIN関数が役立ちます。
次の数式を空白のセルに入力またはコピーし、Enterキーを押します（Excel 2021およびExcel 365の場合）、またはExcel 2019ではCtrl + Shift + Enterキーを押して結果を得ます：
=TEXTJOIN(", ", TRUE, IF(($A$2:$A$18=E2)*($B$2:$B$18=F2), $C$2:$C$18, ""))
- ($A$2:$A$21=E2)*($B$2:$B$21=F2) は、各列が「販売者がE2と等しい」かつ「月がF2と等しい」という両方の条件を満たしているか確認します。両方の条件が満たされた場合、結果は1、そうでない場合は0になります。アスタリスク * は、両方の条件が真であることを意味します。
- IF(..., $C$2:$C$21, "") は、行が一致する場合、商品名を返します。それ以外の場合は空白を返します。
- TEXTJOIN(", ", TRUE, ...) は、すべての空白でない商品名を", "で区切って1つのセルに結合します。
方法2: Kutools for Excelを使用
Kutools for Excelは強力でシンプルなソリューションを提供し、複雑な数式を使わずに複数の条件に基づいて複数の一致を迅速に取得して結合することができます。
Kutools for Excelをインストール後、以下のように操作してください：
- 基準に基づいてすべての対応する値を取得したいデータ範囲を選択します。
- 次に、Kutools > マージ & 分割 > 高度な行のマージをクリックします。スクリーンショットをご覧ください：
- 高度な行のマージダイアログボックスで、以下のオプションを設定してください：
- 一致条件を含む列ヘッダー（例：販売者と月）を選択します。選択した各列について、検索条件として定義するために「主キー」をクリックします。
- 結合結果を取得したい列ヘッダー（例：商品）をクリックします。結合セクションから、好みの区切り文字（カンマ、スペース、またはカスタム区切り文字など）を選択します。
- 最後に、「OK」ボタンをクリックします。
結果：Kutoolsは、ユニークな条件の組み合わせごとにすべての一致する値を1つのセルに即座にマージします。
列内で複数の条件に基づいて複数の一致する値を返す
いくつかの条件に基づいてデータセットから複数の一致するレコードを抽出して表示し、結果を縦方向の列形式で返す必要がある場合、Excelにはいくつかの強力な解決策があります。
方法1: 配列数式を使用（すべてのバージョン対応）
次の配列数式を使用して、結果を列に垂直に返すことができます：
1. 次の数式を空白のセルにコピーまたは入力します：
=IFERROR(INDEX($C$2:$C$18, SMALL(IF(($A$2:$A$18=$E$2)*($B$2:$B$18=$F$2), ROW($C$2:$C$18)-ROW($C$2)+1), ROW(1:1))), "")2. 最初の一致結果を得るためにCtrl + Shift + Enterキーを押し、最初の数式セルを選択して、空白のセルが表示されるまで下のセルにフィルハンドルをドラッグすると、すべての一致する値が以下のように返されます：
- $A$2:$A$18=$E$2: 販売者がセルE2の値と一致するか確認します。
- $B$2:$B$18=$F$2: 月がセルF2の値と一致するか確認します。
- * は論理AND演算子です（両方の条件が真でなければなりません）。
- ROW($C$2:$C$18)-ROW($C$2)+1: 各商品に対して相対的な行番号を生成します。
- SMALL(..., ROW(1:1)): 数式を下にドラッグするにつれてn番目に小さい一致する行を取得します。
- INDEX(...): 一致する行から商品を返します。
- IFERROR(..., ""): これ以上一致がない場合、空白のセルを返します。
方法2: FILTER関数を使用（Excel365 / 2021）
Excel 365またはExcel 2021を使用している場合、FILTER関数は複数の条件に基づいて複数の結果を返すのに優れた選択肢です。そのシンプルさ、明瞭さ、そして複雑な配列数式なしで結果を動的に展開できる能力によります。
次の数式を空白のセルにコピーまたは入力し、Enterキーを押すと、複数の条件に基づいてすべての一致するレコードが返されます。
=FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match")
- FILTER(...) は、両方の条件が満たされている場合、C2:C18からすべての値を返します。
- (A2:A18=E2)*(B2:B18=F2): 販売者と月が一致しているかどうかを確認する論理配列です。
- "No match": 値が見つからない場合のオプションメッセージです。
行内で複数の条件に基づいて複数の一致する値を返す
Excelユーザーは、複数の条件を満たすデータセットから複数の値を抽出し、それを水平方向（行内）に表示する必要があります。これは、動的レポート、ダッシュボード、または要約表を作成する際に特に有用であり、縦方向のスペースが限られている場合に適しています。このセクションでは、2つの強力な方法を探ります。
方法1: 配列数式を使用（すべてのバージョン対応）
従来の配列数式を使用すると、INDEX、SMALL、IF、COLUMN関数を利用して複数の一致する値を抽出できます。垂直方向の抽出（列ベース）とは異なり、数式を調整して結果を行に返します。
1. 次の数式を空白のセルにコピーまたは入力します：
=IFERROR(INDEX($C$2:$C$18, SMALL(IF(($A$2:$A$18=$E$2)*($B$2:$B$18=$F$2), ROW($C$2:$C$18)-ROW($C$2)+1), COLUMN(A1))), "")2. 最初の一致結果を得るためにCtrl + Shift + Enterキーを押し、最初の数式セルを選択して、右側の列に数式をドラッグしてすべての結果を取得します。
- $A$2:$A$18=$E$2: 販売者が一致するか確認します。
- $B$2:$B$18=$F$2: 月が一致するか確認します。
- *: 論理AND—両方の条件が真でなければなりません。
- ROW($C$2:$C$18)-ROW($C$2)+1: 相対的な行番号を作成します。
- COLUMN(A1): 数式が右にドラッグされた距離に応じて、どの一致を返すかを調整します。
- IFERROR(...): 一致がなくなったときにエラーを防ぎます。
方法2: FILTER関数を使用（Excel365 / 2021）
次の数式を空白のセルにコピーまたは入力し、Enterキーを押すと、すべての一致する値が抽出され、行に配置されます。スクリーンショットをご覧ください：
=TRANSPOSE(FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match"))
- FILTER(...): 2つの条件に基づいて列Cから一致する値を取得します。
- (A2:A18=E2)*(B2:B18=F2): 両方の条件が真でなければなりません。
- TRANSPOSE(...): FILTERによって返される縦方向の配列を横方向の配列に変換します。
🔚 結論
Excelで複数の条件に基づいて複数の一致する値を取得するには、結果を列、行、または単一のセルに表示する方法に応じて、いくつかの方法があります。
- Excel 365またはExcel 2021のユーザーにとって、FILTER関数は現代的で動的かつエレガントなソリューションを提供し、複雑さを最小限に抑えます。
- 古いバージョンを使用している場合、配列数式は依然として強力なツールですが、少し多くの設定と注意が必要です。
- さらに、結果を単一のセルに統合したり、ノーコードのソリューションを好む場合、TEXTJOIN関数やKutools for Excelのようなサードパーティツールがプロセスを大幅に簡素化します。
お使いのExcelのバージョンと希望するレイアウトに最適な方法を選んでください。そうすれば、多条件検索を効率的かつ正確に処理する準備が整います。さらに多くのExcelのヒントやコツに興味がある場合、当社のウェブサイトには数千のチュートリアルがあり、Excelをマスターする手助けをします。
関連記事：
- 1つのカンマ区切りセルで複数の検索値を返す
- Excelでは、VLOOKUP関数を適用してテーブルセルから最初に一致する値を返すことができますが、時にはすべての一致する値を抽出し、特定の区切り文字（カンマ、ダッシュなど）で区切って1つのセルにまとめる必要があります。Excelで複数の検索値を1つのカンマ区切りセルに取得して返すにはどうすればよいですか？
- Googleスプレッドシートで一度に複数の一致する値を返すVlookup
- Googleスプレッドシートでの通常のVlookup関数は、指定されたデータに基づいて最初に一致する値を見つけて返すのに役立ちます。しかし、時にはすべての一致する値を返したい場合があります。Googleスプレッドシートでこのタスクを簡単に解決する良い方法はありますか？
- ドロップダウンリストから複数の値を返すVlookup
- Excelで、ドロップダウンリストから複数の対応する値をvlookupして返すにはどうすればよいですか？つまり、ドロップダウンリストから1つの項目を選択すると、そのすべての関連値が一度に表示されます。この記事では、ステップバイステップで解決策を紹介します。
- Excelで複数の値を垂直に返すVlookup
- 通常、Vlookup関数を使用して最初に対応する値を取得できますが、時には特定の基準に基づいてすべての一致するレコードを返したい場合があります。この記事では、複数の一致する値を垂直、水平、または1つのセルに返す方法について説明します。
- Excelで2つの値間の一致するデータを返すVlookup
- Excelでは、通常のVlookup関数を適用して、指定されたデータに基づいて対応する値を取得できます。しかし、時には2つの値の間にある一致する値を返したい場合があります。このタスクをExcelでどのように処理すればよいですか？
最高のオフィス生産性ツール
| 🤖 | Kutools AI Aide：データ分析を革新：インテリジェント実行 | コード生成 | カスタム数式の作成 | データを分析してグラフを生成 | Kutools Functions を呼び出す… |
| 人気機能：重複の検索・ハイライト・マーキング | 空白行を削除 | データを失わず列やセルを統合 | 丸める ... | |
| スーパーLOOKUP：複数条件VLOOKUP | 複数値VLOOKUP | 複数シートの検索 | ファジーマッチ .... | |
| 高度なドロップダウンリスト：すばやくドロップダウンリストを作成 | 依存型ドロップダウンリスト | 複数選択ドロップダウンリスト .... | |
| 列の管理：特定数の列を追加 | 列を移動 | 非表示列の表示状態を切り替え | 範囲と列の比較 ... | |
| 注目機能：グリッドフォーカス | デザインビュー | 強化された数式バー | ワークブック&ワークシートの管理 | オートテキスト ライブラリ (Auto Text) | 日付ピッカー | データの統合 | セルの暗号化/復号化 | リスト送信で電子メールを送信 | スーパーフィルター | 特殊フィルタ（太字/斜体/取り消し線でフィルタ...） | |
| トップ15ツールセット：12 種類のテキストツール（テキストの追加、特定の文字を削除など） | 50 種以上のグラフ タイプ（ガントチャートなど） | 40を超える実用的な 数式（誕生日に基づいて年齢を計算する、など） | 19種の 挿入ツール（QRコードの挿入、パスから画像の挿入など） | 12種類の 変換ツール（単語に変換する、通貨変換など） | 7つの 結合＆分割ツール（高度な行のマージ、セルの分割など） | ...さらに多数 |
Kutools for ExcelでExcelスキルを強化し、かつてない効率を体験しましょう。 Kutools for Excelは300以上の高度な機能で生産性向上と時間短縮を実現します。最も必要な機能を今すぐ取得...
Office TabはOfficeにタブ表示を追加し、作業効率を大幅に向上させます
- Word、Excel、PowerPointでタブ編集とタブ閲覧を有効にします
- 同じウィンドウ内の新しいタブで複数のドキュメントを開いたり作成したりできます。新しいウィンドウを開く必要はありません。
- 生産性が50%向上し、1日に何百回ものマウスクリックも削減できます！