Note: The other languages of the website are Google-translated. Back to English

ExcelでXNUMXつまたは複数の基準に基づいて複数の一致する値を返す方法は?

通常、VLOOKUP関数を使用すると、特定の値を検索して一致するアイテムを返すのは簡単です。 しかし、次のスクリーンショットに示すように、XNUMXつ以上の基準に基づいて複数の一致する値を返そうとしたことがありますか? この記事では、Excelでこの複雑なタスクを解決するためのいくつかの式を紹介します。

配列数式を使用したXNUMXつまたは複数の基準に基づいて複数の一致する値を返します


配列数式を使用したXNUMXつまたは複数の基準に基づいて複数の一致する値を返します

たとえば、28歳で米国出身の名前をすべて抽出したい場合は、次の式を適用してください。

1。 結果を検索する空白のセルに、次の数式をコピーまたは入力します。

=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

注意:上記の式では、 B2:B11 一致する値が返される列です。 F2, C2:C11 最初の条件と、最初の条件を含む列データです。 G2, D2:D11 XNUMX番目の条件とこの条件を含む列データです。必要に応じて変更してください。

2。 次に、 Ctrl + Shift + Enter キーを押して最初の一致する結果を取得し、最初の数式セルを選択して、エラー値が表示されるまで塗りつぶしハンドルをセルまでドラッグします。これで、一致するすべての値が次のスクリーンショットのように返されます。

ヒント:XNUMXつの条件に基づいて一致するすべての値を返す必要がある場合は、次の配列数式を適用してください。

=IFERROR(INDEX($B$2:$B$11, SMALL(IF($F$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+1), ROW(1:1))),"" )


より相対的な記事:

  • XNUMXつのカンマ区切りセルで複数のルックアップ値を返す
  • Excelでは、VLOOKUP関数を適用して、テーブルセルから最初に一致した値を返すことができますが、場合によっては、一致するすべての値を抽出してから、コンマ、ダッシュなどの特定の区切り文字で区切ってXNUMXつにする必要があります。次のスクリーンショットのようなセル。 ExcelのXNUMXつのカンマ区切りセルで複数のルックアップ値を取得して返すにはどうすればよいですか?
  • VlookupとGoogleスプレッドシートで一度に複数の一致する値を返す
  • Googleスプレッドシートの通常のVlookup関数は、特定のデータに基づいて最初に一致する値を見つけて返すのに役立ちます。 ただし、次のスクリーンショットに示すように、vlookupして一致するすべての値を返す必要がある場合があります。 Googleスプレッドシートでこのタスクを解決するための良い簡単な方法はありますか?
  • Vlookupとドロップダウンリストから複数の値を返す
  • Excelで、ドロップダウンリストから複数の対応する値をvlookupして返すにはどうすればよいですか。つまり、ドロップダウンリストからXNUMXつの項目を選択すると、次のスクリーンショットのようにすべての相対値が一度に表示されます。 この記事では、ソリューションを段階的に紹介します。
  • VlookupとExcelで垂直方向に複数の値を返す
  • 通常、Vlookup関数を使用して最初の対応する値を取得できますが、特定の基準に基づいて一致するすべてのレコードを返したい場合もあります。 この記事では、vlookupして、一致するすべての値を垂直方向、水平方向、またはXNUMXつのセルに返す方法について説明します。
  • VlookupとExcelのXNUMXつの値の間の一致するデータを返す
  • Excelでは、通常のVlookup関数を適用して、特定のデータに基づいて対応する値を取得できます。 ただし、次のスクリーンショットに示すように、vlookupして、XNUMXつの値の一致する値を返したい場合があります。Excelでこのタスクをどのように処理できますか?

 


  • スーパーフォーミュラバー (複数行のテキストと数式を簡単に編集できます); 読書レイアウト (多数のセルを簡単に読み取って編集する); フィルター範囲に貼り付け...
  • セル/行/列をマージする およびデータの保持。 分割セルコンテンツ; 重複する行と合計/平均を組み合わせる...重複セルを防止します。 範囲を比較する...
  • [複製]または[一意]を選択します 行; 空白行を選択 (すべてのセルは空です); スーパーファインドとファジーファインド 多くのワークブックで; ランダム選択...
  • 正確なコピー 数式参照を変更せずに複数のセル。 参照の自動作成 複数のシートに; 箇条書きを挿入、チェックボックスなど...
  • お気に入りの数式をすばやく挿入する、範囲、チャート、写真; セルを暗号化する パスワード付き。 メーリングリストを作成する そしてメールを送る...
  • テキストを抽出、テキストの追加、位置による削除、 スペースを削除する; ページング小計の作成と印刷。 セルの内容とコメントを変換する...
  • スーパーフィルター (フィルタースキームを保存して他のシートに適用します); 高度な並べ替え 月/週/日、頻度など。 特殊フィルター 太字、斜体...
  • ワークブックとワークシートを組み合わせる; キー列に基づいてテーブルをマージします。 データを複数のシートに分割; xls、xlsx、PDFをバッチ変換...
  • ピボットテーブルのグループ化 週番号、曜日など... ロック解除された、ロックされたセルを表示する さまざまな色で; 式/名前を持つセルを強調表示する...
kteタブ201905
  • Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性を 50% 向上させ、毎日何百回もマウス クリックを減らすことができます!
officetab下部

 

コメントを並べ替える
コメント (25)
まだ評価はありません。 最初に評価してください!
このコメントは、サイトのモデレーターによって最小化されました
私はこれとまったく同じ式を試しました。 100%コピーしました。 私が変更したのは、照合されて返されるデータだけでした。 この数式を使用すると、Excelは「この関数に入力した引数が多すぎます)と表示します。= INDEX( '2020ボリュームレポート'!$ B $ 3:$ B $ 100、SMALL(IF(COUNTIF($ A $ 1、'2020ボリュームレポート'!$ A $ 3:$ A $ 100)* COUNTIF($ A $ 3、' 2020ボリュームレポート'!$ D $ 3:$ D $ 100)、ROW(' 2020ボリュームレポート'!$ A $ 3:$ G $ 100)- MIN(ROW( '2020 Volume Report'!$ A $ 3:$ G $ 100))+ 1、 "0")、ROW(A1)、COLUMN(A1))
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、ここでscreesnhotとしてデータと数式のエラーを教えていただけますか?
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、水平状態でどのように使用できますか。
このコメントは、サイトのモデレーターによって最小化されました
式の+0の後の「1」は何ですか? それは例のXNUMXつではありません。
このコメントは、サイトのモデレーターによって最小化されました
こんにちは私は同じ式を試しました。 結果を得ていますが、CSEを与えると、複数の応答は提供されません
このコメントは、サイトのモデレーターによって最小化されました

このコメントは、サイトのモデレーターによって最小化されました
配列数式で1つまたは複数の基準に基づいて複数の一致する値を返すことについて:AXNUMXから始まる以外の場所にデータがある場合、数式内のすべてのセル参照を更新しても機能しないのはなぜですか?
このコメントは、サイトのモデレーターによって最小化されました
最初の例では、28歳未満のすべての人を返すために、式にどのような変更が必要でしょうか。
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、

2番目の基準を入力することは可能かと思いましたが、1番目の基準と同じ範囲から、

たとえば、上記の使用例では、アメリカとフランスの両方の人の名前を検索したいので、セルF3にはフランスが含まれ、ScarlettとAndrewも列Gのリストに入力されます。

よろしくお願いします。
このコメントは、サイトのモデレーターによって最小化されました
こんにちはニック、

喜んでお手伝いします。 アメリカとフランスの両方の人の名前を取得したい場合は、結果を得るために私たちの式を2回使用することをお勧めします。 スクリーンショットを参照してください。F2とG2では、値は「米国」と「フランス」です。 式を適用=IFERROR(INDEX($ B $ 11:$ B $ 2、SMALL(IF($ F $ 2 = $ D $ 11:$ D $ 2、ROW($ D $ 11:$ D $ 2)-ROW($ D $ 1)+1 )、ROW(1:2)))、 "")を使用して、アメリカの結果を取得します。 そして、式= IFERROR(INDEX($ B $ 11:$ B $ 2、SMALL(IF($ G $ 2 = $ D $ 11:$ D $ 2、ROW($ D $ 11:$ D $ 2)-ROW($ D $ 1)+ 1)、ROW(1:XNUMX)))、 "")フランスの結果を取得します。 それは簡単です。 ぜひお試しください。

敬具
マンディ
このコメントは、サイトのモデレーターによって最小化されました
XNUMX番目の数式を使用して下にドラッグすると、何も表示されません。 数式の結果(fx)は、何かを返す必要があることを示していますが、空白です。 これを修正するにはどうすればよいですか?
このコメントは、サイトのモデレーターによって最小化されました
こんにちはアリシア、

喜んでお手伝いします。 記事のXNUMX番目の数式を試し、数式を下にドラッグすると、残りの結果が返されました。 あなたの問題にはXNUMXつの理由があると思います。 まず、Ctrl + Shift+Enterキーを押して数式を入力するのを忘れているかもしれません。 次に、一致する結果はXNUMXつだけであるため、他の結果は返されません。 確認してください。

敬具
マンディ
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、
式を使用してみましたが、0の値または添付された画像が生成されます
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、みるくさん
あなたのスクリーンショットは MAC バージョンの WPS ソフトウェアを示していたので、私たちの式が利用可能かどうかわかりません。
こちらにエクセルファイルをアップロードしましたので、お使いの環境で正しく計算できるか試してみてください。
ありがとうございました!
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、
次の場合、最初の式を展開するために必要なものは次のとおりです。
一部の ID は空白です (たとえば、セル A5 は空白です)。ID が空白でない場合にのみ行を出力する追加の条件が必要です。 (したがって、出力は James と Abdul になります。
ありがとうございます!
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、ジョーさん
問題を解決するには、次の式を適用してください。
=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11)*($A$2:$A$11<>0), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

お役に立てれば幸いです。
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、

セル H1 に「名前」と書き、それを数式にリンクしたい場合、どのように機能しますか?
次に、セル H1 に「ID」を書き込むと、結果として自動的に AA1004; が得られます。 DD1009; PP1023(第一処方用)

事前にありがとうございます!
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、マリー
申し訳ありませんが、最初の問題の要点がわかりません。問題をより明確かつ詳細に説明していただけますか? または、ここにスクリーンショットを挿入して問題を説明することもできます。
XNUMX 番目の質問については、セル参照を次のように変更するだけです。
=INDEX($A$2:$A$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

押すことを忘れないでください Ctrl + Shift + Enter 一緒にキー。
ぜひお試しください。お役に立てば幸いです。
このコメントは、サイトのモデレーターによって最小化されました
ねえ、式をありがとう。 「固定」値/テキストを基準として機能しました。 ただし、使用しようとしている基準の 0 つは条件 (値 <>XNUMX ) ですが、説明されている数式は機能しません。 基準の XNUMX つとして条件を使用できるように、数式を適応させるには何を変更すればよいかご存知ですか?

ベスト、

ジョン
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、マーカス
問題を解決するには、次の記事をご覧ください。
https://www.extendoffice.com/documents/excel/6393-excel-vlookup-function.html#b3-2
このタスクの詳細な説明がいくつかあります。 基準を独自のものに変更するだけです。
ありがとうございました!
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、

まず、共有していただきありがとうございます!

以下の場合の解決方法を教えてください。

私は3つの列を持っています(A:参照情報を含む、B:検索する情報を含む、C:検索結果)

画像のURLは以下に記載されています

https://ibb.co/VHCd09K

A列-------------------------B列-------------C列
ファイル名---------------名前----------------ファイル名、ドキュメント名、要素名、名前
変更された要素------------------要素--------------変更された要素、要素名、要素 ID
列の位置
文書名
要素名
お名前
カテゴリー
保証
スロープ
要素 ID

必要なのは、列 A でセル B2 (名前) または B3 (要素) との部分一致を検索し、XNUMX つのセルで結果を取得することです。

ありがとう、ベザド
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、ベザド
以下のユーザー定義関数が役立つかもしれません。
Public Function ConcatPartLookUp(rngInput As Range, rngSource As Range, Optional strDelimiter As String, Optional blCaseSensitive)
Dim rng As Range
If strDelimiter = "" Then strDelimiter = ","
If IsMissing(blCaseSensitive) Then
    blCaseSensitive = False
Else
    blCaseSensitive = True
End If
For Each rng In rngSource
    If blCaseSensitive Then
        If InStr(1, rng.Value, rngInput.Value, vbBinaryCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    Else
        If InStr(1, rng.Value, rngInput.Value, vbTextCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    End If
Next
If Len(ConcatPartLookUp) > 0 Then ConcatPartLookUp = Mid(ConcatPartLookUp, 2, Len(ConcatPartLookUp))
End Function


このコードをコピーして貼り付けた後、次の式を使用します。=ConcatPartLookUp(B2,$A$2:$A$8) 必要な結果を得るために。
試してみてください。お役に立てば幸いです。
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、

これらの例を投稿していただきありがとうございます。
これを自分のシートに実装しようとしていますが、機能しません (ヨーロッパ版の Excel を使用している可能性があります)。

私が自分のシフトを行った日、またはクライアントのために「何時間か」(>0) 時間働いた日の日付を取得したいと考えています。

したがって、I3 は名前で、J3 は月です。 K3 と L3 はシフト (1 は勤務) と時間 (設定方法がわからないため、XNUMX より大きくする必要があります) です。

私の期待される結果は次のとおりです。
シフト: I7 および I8
営業時間: J7

だから私は 0 年 2 月 3 日に 10 月に「人 2022」のために XNUMX 時間以上働いた
「2 年 10 月 10 日」と 2022 年 28 月 10 日に人物 2022 のシフトがありました

'=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11)), ROW を追加すると($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))' 私のExcelシートでは、式の異なる部分の間のコンマ。
したがって、それらを「;」に変更する必要があります。
しかし、試してみると、常に「#NAME?」と表示されます。

それで、誰かがこれで私を助けることができますか?

敬具、

ロー
[img]https://drive.google.com/file/d/1iIPQKuj_PNhqWyWlwJ4IQTqGNEd6B9Hw/view?usp=share_link[/img]
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、重複する値 (たとえば 1 つのアダム) がある場合、2 ではなく XNUMX つのアダムのみを返すようにするにはどうすればよいですか?
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、ボビー。
一致する一意の値のみを抽出するには、次の式を適用する必要があります。
数式を貼り付けた後、 を押してください Ctrl + Shift + Enter 正しい結果を得るために一緒にキーを押します。
=IFERROR(INDEX($B$2:$B$5, MATCH(0, COUNTIF(H1:$H$1, $B$2:$B$5))+IF($D$2:$D$5<>$G$2, 1) , 0)+IF($C$2:$C$5<>$F$2, 1, 0), 0)), "")

試してみてください。お役に立てば幸いです。
ここにはまだコメントが投稿されていません
あなたのコメントを残す
ゲストとして投稿
×
この投稿を評価:
0   文字
推奨される場所

フォローする

著作権©の2009 - WWW。extendoffice.com。 | | 全著作権所有。 搭載 ExtendOffice。 | サイトマップ
MicrosoftおよびOfficeのロゴは、米国MicrosoftCorporationの米国およびその他の国における商標または登録商標です。
SectigoSSLで保護