Note: The other languages of the website are Google-translated. Back to English
ログイン 申し込み

ExcelのXNUMXつのセルに複数の値を返すようにvlookupする方法は？

通常、Excelでは、VLOOKUP関数を使用するときに、条件に一致する値が複数ある場合は、最初の値を取得できます。 しかし、次のスクリーンショットのように、基準を満たすすべての対応する値をXNUMXつのセルに返したい場合があります。どうすれば解決できますか？

TEXTJOIN関数を使用して複数の値を2019つのセルに返すVlookup（Excel365およびOfficeXNUMX）

ユーザー定義関数を使用して複数の値をXNUMXつのセルに返すVlookup

便利な機能を備えたXNUMXつのセルに複数の値を返すVlookup

TEXTJOIN関数を使用して複数の値を2019つのセルに返すVlookup（Excel365およびOfficeXNUMX）

Excel2019やOffice365などの上位バージョンのExcelを使用している場合は、新しい機能があります- テキスト結合、この強力な関数を使用すると、一致するすべての値をすばやくvlookupしてXNUMXつのセルに返すことができます。

一致するすべての値をXNUMXつのセルに返すVlookup

結果を入力する空白のセルに次の数式を適用して、を押してください Ctrl + Shift + Enter キーを一緒に押して最初の結果を取得し、塗りつぶしハンドルをこの数式を使用するセルまでドラッグすると、以下のスクリーンショットに示すように、対応するすべての値が取得されます。

=TEXTJOIN(",",TRUE,IF($A$2:$A$11=E2,$C$2:$C$11,""))

注意： 上記の式では、 A2：A11 ルックアップ範囲にはルックアップデータが含まれています。 E2 ルックアップ値です。 C2：C11 一致する値を返すデータ範囲です。 ",「」は、複数のレコードを区切るための区切り文字です。
重複することなくすべての一致する値をXNUMXつのセルに返すVlookup

重複することなくルックアップデータに基づいて一致するすべての値を返したい場合は、次の式が役立つ場合があります。

次の数式をコピーして空白のセルに貼り付け、を押してください Ctrl + Shift + Enter キーを合わせて最初の結果を取得し、この数式をコピーして他のセルに入力すると、以下のスクリーンショットに示すように、重複する値なしで対応するすべての値が取得されます。

=TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$11, IF(E2=$A$2:$A$11, $C$2:$C$11, ""), 0),"")=MATCH(ROW($C$2:$C$11), ROW($C$2:$C$11)), $C$2:$C$11, ""))

注意： 上記の式では、 A2：A11 ルックアップ範囲にはルックアップデータが含まれています。 E2 ルックアップ値です。 C2：C11 一致する値を返すデータ範囲です。 ",「」は、複数のレコードを区切るための区切り文字です。

ユーザー定義関数を使用して複数の値をXNUMXつのセルに返すVlookup

上記のTEXTJOIN関数は、Excel2019およびOffice365でのみ使用できます。他の下位バージョンのExcelを使用している場合は、このタスクを完了するためにいくつかのコードを使用する必要があります。

一致するすべての値をXNUMXつのセルに返すVlookup

1。 を押し続けます Alt + F11 キー、そしてそれは開きます アプリケーション向け Microsoft Visual Basic 窓。

2に設定します。 OK をクリックします。 インセット > モジュール、次のコードをに貼り付けます モジュールウィンドウ.

VBAコード：XNUMXつのセルに複数の値を返すVlookup

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

3。 次に、このコードを保存して閉じ、ワークシートに戻って、次の数式を入力します。 =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") 結果を配置する特定の空白のセルに移動し、塗りつぶしハンドルを下にドラッグして、必要なXNUMXつのセルに対応するすべての値を取得します。スクリーンショットを参照してください。

注意： 上記の式では、 A2：A11 ルックアップ範囲にはルックアップデータが含まれています。 E2 ルックアップ値です。 C2：C11 一致する値を返すデータ範囲です。 ",「」は、複数のレコードを区切るための区切り文字です。
重複することなくすべての一致する値をXNUMXつのセルに返すVlookup

返される一致する値の重複を無視するには、以下のコードを使用してください。

1。 を押し続けます Altキー+ F11 キーを押して アプリケーション向け Microsoft Visual Basic 窓。

2に設定します。 OK をクリックします。 インセット > モジュール、次のコードをに貼り付けます モジュールウィンドウ.

VBAコード：Vlookupを実行し、複数の一意の一致値をXNUMXつのセルに返します

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
'Updateby Extendoffice
    Dim xDic As New Dictionary
    Dim xRows As Long
    Dim xStr As String
    Dim i As Long
    On Error Resume Next
    xRows = LookupRange.Rows.Count
    For i = 1 To xRows
        If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
            xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
        End If
    Next
    xStr = ""
    MultipleLookupNoRept = xStr
    If xDic.Count > 0 Then
        For i = 0 To xDic.Count - 1
            xStr = xStr & xDic.Keys(i) & ","
        Next
        MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
    End If
End Function

3。 コードを挿入したら、をクリックします 工具 > リファレンス オープンで アプリケーション向け Microsoft Visual Basic ウィンドウ、そして、ポップアウトで 参照– VBAProject ダイアログボックス、チェック Microsoftスクリプトランタイム 内のオプション 利用可能な参考文献 リストボックス、スクリーンショットを参照してください：

4。 次に、をクリックします OK ダイアログボックスを閉じるには、コードウィンドウを保存して閉じ、ワークシートに戻り、次の式を入力します。 =MultipleLookupNoRept(E2,$A$2:$C$11,3) into a blank cell where you want to output the result, and then drag the fill hanlde down to get all matching values, see screenshot:

注意： 上記の式では、 A2：C11 使用するデータ範囲は、 E2 ルックアップ値、数値です 3 戻り値を含む列番号です。

便利な機能を備えたXNUMXつのセルに複数の値を返すVlookup

 あなたが私たちを持っているなら Kutools for Excelそのと 高度な結合行 この機能を使用すると、同じ値に基づいて行をすばやくマージまたは結合し、必要に応じていくつかの計算を実行できます。

注意：これを適用する 高度な結合行、まず、ダウンロードする必要があります Kutools for Excel、次に機能をすばやく簡単に適用します。

インストールした後 Kutools for Excel、次のようにしてください。

1。 別の列に基づいてXNUMXつの列データを結合するデータ範囲を選択します。

2に設定します。 OK をクリックします。 クツール > マージ＆スプリット > 高度な結合行、スクリーンショットを参照してください：

3。 飛び出した 高度な結合行 ダイアログボックス：

  • 結合するキー列名をクリックしてから、 主キー
  • 次に、キー列に基づいてデータを結合する別の列をクリックし、をクリックします。 組み合わせる 結合されたデータを分離するためにXNUMXつのセパレータを選択します。

4. 次に、をクリックします。 OK ボタンをクリックすると、次の結果が得られます。

今すぐExcel用のKutoolsをダウンロードして無料トライアル！

より相対的な記事：

  • いくつかの基本的な例と高度な例を含むVLOOKUP関数
  • Excelでは、VLOOKUP関数はほとんどのExcelユーザーにとって強力な関数であり、データ範囲の左端の値を検索し、指定した列から同じ行の一致する値を返すために使用されます。 このチュートリアルでは、Excelのいくつかの基本的な例と高度な例でVLOOKUP関数を使用する方法について説明します。
  • XNUMXつまたは複数の基準に基づいて複数の一致する値を返します
  • 通常、VLOOKUP関数を使用すると、特定の値を検索して一致するアイテムを返すのは簡単です。 しかし、XNUMXつ以上の基準に基づいて複数の一致する値を返そうとしたことがありますか？ この記事では、Excelでこの複雑なタスクを解決するためのいくつかの式を紹介します。
  • Vlookupと複数の値を垂直方向に返す
  • 通常、Vlookup関数を使用して最初の対応する値を取得できますが、特定の基準に基づいて一致するすべてのレコードを返したい場合もあります。 この記事では、vlookupして、一致するすべての値を垂直方向、水平方向、またはXNUMXつのセルに返す方法について説明します。
  • Vlookupとドロップダウンリストから複数の値を返す
  • Excelで、ドロップダウンリストから複数の対応する値をvlookupして返すにはどうすればよいですか。つまり、ドロップダウンリストからXNUMXつの項目を選択すると、そのすべての相対値が一度に表示されます。 この記事では、ソリューションを段階的に紹介します。

最高のオフィス生産性ツール

Kutools for Excelはほとんどの問題を解決し、生産性を80％向上させます

  • 再利用: すばやく挿入 複雑な数式、チャート および以前に使用したものすべて。 セルを暗号化する パスワード付き。 メーリングリストを作成する そしてメールを送る...
  • スーパーフォーミュラバー （複数行のテキストと数式を簡単に編集できます）; 読書レイアウト （多数のセルを簡単に読み取って編集する）; フィルター範囲に貼り付け...
  • セル/行/列をマージする データを失うことなく; 分割セルコンテンツ; 重複する行/列を組み合わせる...重複セルを防止します。 範囲を比較する...
  • [複製]または[一意]を選択します 行; 空白行を選択 （すべてのセルは空です）; スーパーファインドとファジーファインド 多くのワークブックで; ランダム選択...
  • 正確なコピー 数式参照を変更せずに複数のセル。 参照の自動作成 複数のシートに; 箇条書きを挿入、チェックボックスなど...
  • テキストを抽出、テキストの追加、位置による削除、 スペースを削除する; ページング小計の作成と印刷。 セルの内容とコメントを変換する...
  • スーパーフィルター （フィルタースキームを保存して他のシートに適用します）; 高度な並べ替え 月/週/日、頻度など。 特殊フィルター 太字、斜体...
  • ワークブックとワークシートを組み合わせる; キー列に基づいてテーブルをマージします。 データを複数のシートに分割; xls、xlsx、PDFをバッチ変換...
  • 300 以上の強力な機能. Office / Excel 2007-2021 および 365 をサポートします。すべての言語をサポートします。 企業や組織に簡単に導入できます。 フル機能の 30 日間無料トライアル。 60日間の返金保証。
kteタブ201905

Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作​​業をはるかに簡単にします

  • Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性を 50% 向上させ、毎日何百回もマウス クリックを減らすことができます!
officetab下部
コメントを並べ替える
古い順
最新の最初の
コメント (43)
まだ評価はありません。 最初に評価してください！
デビッドアトウェル
デビッドアトウェル
このコメントは、サイトのモデレーターによって最小化されました
この数式を調整して、各戻り値を分離し、「、」だけでなく、一意の値のみを返すにはどうすればよいですか？
返信
0
0
Michael Liebreich
Michael Liebreich     デビッドアトウェル
このコメントは、サイトのモデレーターによって最小化されました
コードをありがとう！

ワイルドカードに関しては、回避策はINSTRを使用することです

[If rng = pValue Then]を[InStr（1、rng.Value、pValue）Then]に置き換えることができます。大文字と小文字を区別したくない場合は、[InStr（1、rng.Value、pValue、 vbTextCompare）次に]
返信
0
0
デビットV
デビットV
このコメントは、サイトのモデレーターによって最小化されました
上記のVBAコードをありがとう。 結果をセルの新しい行に入力する方法を教えてください。つまり、Alt-Enter 300 400のようになります。
返信
0
0
コリーン
コリーン
このコメントは、サイトのモデレーターによって最小化されました
上記のコードを共有していただきありがとうございます。 私はこれを数ヶ月使用していますが、今日は機能していないようです。 返されるデータがある場合、通常のエラーではなく空白のセルが表示されます。 何かご意見は？
返信
0
0
アジャイ
アジャイ
このコメントは、サイトのモデレーターによって最小化されました
素晴らしい仕事..私が欲しいものを正確に手に入れました!!! 大好きです ！！
返信
0
0
アンキットジャイン
アンキットジャイン
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、私はこの作業に本当に感銘を受けました。この関数を使用するための作業はとても簡単です。 しかし、私はさらなるサポートが必要です。 私の ？ vlookup配列に複数のセルがあるセルから数値を選択するにはどうすればよいですか。 つまり、セルA1 = 100、A2 = 350、A3 = 69 C1=100の場合。 1222; 12133 C2 = 69; 222 D1 = Apple D2 = bananaでは、テーブル配列の列Cから100を選択して、対応するD1 = appleを導出するにはどうすればよいですか。ルックアップ値とテーブル配列には、「;」で区切られた7桁の数値があることに注意してください。 これを解決して、多くの時間を節約するのを手伝っていただければ幸いです。
返信
0
0
アリ
アリ
このコメントは、サイトのモデレーターによって最小化されました
VBAコードをありがとうございます。 私はまさに私が欲しいものを手に入れました！ コード「rng.Offset（0、pIndex-1）」のみを「rng.Offset（0、pIndex-2）」に変更しました。 したがって、MYVLOOKUPは右から左に検索できます。
返信
0
0
ジェイ
ジェイ
このコメントは、サイトのモデレーターによって最小化されました
これはまさに私が探していたものであり、自分でUDFを作成することだけを考えていませんでした。 ただし、VLOOKUPとまったく同じようには機能しません。 探している文字列が最初の列だけにない場合は、渡された元の範囲外のデータが得られる可能性があります。 名前番号その他の名前合格した範囲外の列Jay1Jay 1 Jay 2 Jay 2 Chris 3 Chris 3 Jorge 4 Jorge 4 Jay 5 Jay 5 Jorge 6 Jorge 6上記の表がセルA1：D7の場合、A1：C7のみを合格した場合「MYVLOOKUP」関数は、1 1 2を返すと予想される場合に、2 5 5 1 2 5を返します。以下の変更により、問題が修正されます。Function MYVLOOKUP（pValue As String、pWorkRng As Range、pIndex As Long）'Update 20150310' Updated 6 / 9/16 Jay Coltrain'Dim rng As Range Dim xResult As String xResult = "" Dim Rows As Long、i As Long Rows = pWorkRng.Rows.Count For i = 1 To Rows If pWorkRng.Cells（i、1）。 Value = pValue Then xResult = xResult＆ ""＆pWorkRng.Cells（i、1）.Offset（0、pIndex-1）End If Next i'For Each rng In pWorkRng' If rng = pValue Then'xResult = xResult＆ " "＆rng.Offset（0、pIndex-1）'End If' Next MYVLOOKUP = xResult End Function
返信
0
0
コー​​リー
コー​​リー
このコメントは、サイトのモデレーターによって最小化されました
これはうまく機能しますが、結果から重複を削除するコマンドのヘルプが必要です。 真剣に、しかし、素晴らしい仕事。
返信
0
0
コー​​リー
コー​​リー
このコメントは、サイトのモデレーターによって最小化されました
これはうまく機能しますが、結果から重複を削除するためのコマンド関数のヘルプが必要です。
返信
0
0
ロブ
ロブ
このコメントは、サイトのモデレーターによって最小化されました
フォローアップコメントを通知する
返信
0
0
アジャイ
アジャイ
このコメントは、サイトのモデレーターによって最小化されました
何も返さない！ MYLOOKUPを適用した後、結果は表示されませんが空白になります。
返信
0
0
スピロス・ツォルツィス
スピロス・ツォルツィス
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、うまくいきます。 私がやりたいのは、「///」またはその他のマーカーで値の結果を区切るようにコードを調整することです（技術的な理由から、6文字の区切り文字だけは必要ありません）。 また、この式はワイルドカードでは機能しないことに気づきました。 質問が多すぎることはわかっていますが、= myvlookup（ "*"＆E2＆ "*"、$ A $ 15,2：$ C $ XNUMX）を検索すると、vlookupが機能するため、機能しません。 何か助けはありますか？
返信
0
0
スピロス・ツォルツィス
スピロス・ツォルツィス
このコメントは、サイトのモデレーターによって最小化されました
注意喚起。 その出力でセパレータを取得する方法を理解しました。 その初歩。 しかし、私はそれを理解しました。 xResult = xResult＆ "///"＆rng.Offset（0、pIndex-1）ただし、検索条件でワイルドカードを使用できるようにすることで、最後に最も望まれることです。 この美しく素晴らしいソリューションをありがとうございました。 非常に役に立ちました。 今度は、マクロを実行して、何をしていてもExcelに永続的にインストールして、必要なときに使用できるようにします。 そしてワイルドカード！ どうもありがとう。 ワイルドカードだけが残されています。
返信
0
0
アレックス・チョウ
アレックス・チョウ
このコメントは、サイトのモデレーターによって最小化されました
一意のレコードを取得するには、以下を使用できます:(他のユーザーコードを参照して変更）Function MYVLOOKUP（pValue As String、pWorkRng As Range、pIndex As Long）'Update 20150310' Updated 6/9/16 Jay Coltrain'Dim rng As Range Dim xResult As String xResult = "" Dim Rows As Long、i As Long Rows = pWorkRng.Rows.Count For i = 1 To Rows If pWorkRng.Cells（i、1）.Value = pValue Then xResult = xResult＆ "、" ＆pWorkRng.Cells（i、1）.Offset（0、pIndex-1）End If Next i Dim varSection As Variant Dim sTemp As String Dim sDelimiter As String sDelimiter = "、" For Each varSection In Split（xResult、sDelimiter）If InStr（1、sDelimiter＆sTemp＆sDelimiter、sDelimiter＆varSection＆sDelimiter、vbTextCompare）= 0 Then sTemp = sTemp＆sDelimiter＆varSection End If Next varSection MYVLOOKUP = Mid（sTemp、Len（sDelimiter）+ 1）End Function
返信
0
0
カーティス
カーティス
このコメントは、サイトのモデレーターによって最小化されました
これは完全に機能しましたが、20タブ、50万行以上のスプレッドシート内で関数が正しく機能するようになるまでに少し時間がかかりました。 ここで大きな問題は、その区切られた文字列を取得し、各エントリを別のデータセットのインデックス/マッチ（インデックス/マッチとは結婚していませんが、より高速に見える）ルックアップ値として使用し、すべてのリターンのSUM値をXNUMXつのセルに返す方法です。 。 私のシナリオは、複数の請求書を持つ単一の注文があるというものです。 MYVLOOKUP関数は、すべての請求書をXNUMXつのセルに報告するのに最適です。 私が今やりたいのは、報告されたセルと連結された各リターンを取得し、その配列をスピンして、各請求書の支払い額を合計して数式セルに戻すことです。 これに関してあなたが提供できるどんな助けにも感謝し、MYVLOOKUP関数に感謝します！
返信
0
0
ジェイ
ジェイ
このコメントは、サイトのモデレーターによって最小化されました
私が何をしても、私は常に#valueを取得します！ 結果の代わりに返されます。 vlookupは問題なく機能するため、データは機能します。 マクロを有効にするプロセスをすでに実行しています。 すべてをXNUMX枚にまとめました。 何か案は？？
返信
0
0
カビランクマール
カビランクマール
このコメントは、サイトのモデレーターによって最小化されました
素晴らしいマクロ、便利。 ただし、2つの基準をチェックするように変更できるかどうかを知る必要があります。とにかく、ワイルドカードを使用できるようにするために誰かが見つかりましたか。 何か助けはありますか？
返信
0
0
ウィル
ウィル
このコメントは、サイトのモデレーターによって最小化されました
結果を修正して、1000 1000 -1000を表示する代わりに、たとえば1,000 / 1,000 /（1,000）を表示する方法はありますか？
返信
0
0
Mark Hodder
Mark Hodder
このコメントは、サイトのモデレーターによって最小化されました
優れた機能ですが、100,000レコードをチャンク化することは、私の貧弱なラップトップにとっては少し多くのことを証明します。一晩実行する必要があります。
返信
0
0
トムT
トムT
このコメントは、サイトのモデレーターによって最小化されました
これはすごい、ありがとう！
返信
0
0
ここにはまだコメントが投稿されていません
もっと読む
あなたのコメントを残す
ゲスト
ゲストとして投稿
ログイン
×
この投稿を評価：
リセット
0   文字
推奨される場所

フォローする

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