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

XNUMXつのコンマ区切りセルで複数のルックアップ値を返す方法は?

Excelでは、VLOOKUP関数を適用して、テーブルセルから最初に一致した値を返すことができますが、場合によっては、一致するすべての値を抽出してから、コンマ、ダッシュなどの特定の区切り文字で区切ってXNUMXつにする必要があります。次のスクリーンショットのようなセル。 ExcelのXNUMXつのカンマ区切りセルで複数のルックアップ値を取得して返すにはどうすればよいですか?

docは複数の値をコンマ区切りで返します1

ユーザー定義関数を使用して、XNUMXつのコンマ区切りセルに複数のルックアップ値を返します

Kutools for Excelを使用して、XNUMXつのコンマ区切りセルで複数のルックアップ値を返します


ユーザー定義関数を使用して、XNUMXつのコンマ区切りセルに複数のルックアップ値を返します

通常、一致する複数の値を抽出してXNUMXつのセルにコンマで区切って返す直接的な方法はありません。ここでは、このジョブを解決するためのユーザー定義関数を作成できます。次のようにしてください。

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

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

VBAコード:複数のルックアップ値をXNUMXつのコンマ区切りセルに返します

Function SingleCellExtract(LookupValue As String, LookupRange As Range, ColumnNumber As Integer, Char As String)
'Updateby Extendoffice
    Dim I As Long
    Dim xRet As String
    For I = 1 To LookupRange.Columns(1).Cells.Count
        If LookupRange.Cells(I, 1) = LookupValue Then
            If xRet = "" Then
                xRet = LookupRange.Cells(I, ColumnNumber) & Char
            Else
                xRet = xRet & "" & LookupRange.Cells(I, ColumnNumber) & Char
            End If
        End If
    Next
    SingleCellExtract = Left(xRet, Len(xRet) - 1)
End Function

3。 次に、このコードを保存して[モジュール]ウィンドウを閉じ、ワークシートに戻って、次の式を入力します。 = SingleCellExtract(D2、A2:B15,2、 "、") 結果を返したい空白のセルに入れます。 そしてを押します 入力します 結果を取得するためのキー。スクリーンショットを参照してください。

docは複数の値をコンマ区切りで返します2

注意:上記の式では:

D2: 検索するセル値を示します。

A2:B15: データをフェッチするデータ範囲です。

2:番号2は、一致する値が返される列番号です。

,:コンマは、複数の値を区切る区切り文字です。

必要に応じて変更できます。


Kutools for Excelを使用して、XNUMXつのコンマ区切りセルで複数のルックアップ値を返します

あなたが持っている場合 Kutools for Excel、このタスクはもはや問題ではありません。 ザ・ 高度な結合行 ユーティリティは、列に基づいてすべての相対値を組み合わせるのに役立つ場合があります。

Kutools for Excel : 300以上の便利なExcelアドインがあり、30日以内に制限なしで無料で試すことができます

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

1。 列に基づいて、一致するすべての値を組み合わせるデータ範囲を選択します。

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

3。 の中に 列に基づいて行を組み合わせる ダイアログボックスで、結合する列名をクリックしてから、 主キー ボタン、スクリーンショットを参照してください:

docは複数の値をコンマ区切りで返します4

4。 次に、一致した値を組み合わせる他の列名をクリックして、 組み合わせる 結合された値を区切るためにXNUMXつの区切り文字を選択するには、スクリーンショットを参照してください。

docは複数の値をコンマ区切りで返します5

5。 次に、をクリックします OK ボタンをクリックすると、同じ値を持つすべての対応するセルがXNUMXつのセルに結合され、コンマで区切られます。スクリーンショットを参照してください。

docは複数の値をコンマ区切りで返します6 2 docは複数の値をコンマ区切りで返します7

このAdvancedCombineRowsユーティリティの詳細については、ここをクリックしてください…

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


デモ:Kutools for Excelを使用して、XNUMXつのコンマ区切りセルで複数のルックアップ値を返します

Kutools for Excel:300以上の便利なExcelアドインがあり、30日以内に制限なしで無料で試すことができます。 今すぐダウンロードして無料トライアル!

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

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下部
コメントを並べ替える
コメント (17)
5の5を評価 · 2の評価
このコメントは、サイトのモデレーターによって最小化されました
iamがモジュールを貼り付けて保存しようとしているときに、機能の互換性チェッカーが大幅に失われたというポップアップメッセージが表示されます
このコメントは、サイトのモデレーターによって最小化されました
この投稿をありがとう。 これが作成している1つの別々の整数を操作する方法を知っていますか。 たとえば、'= SingleCellExtract'関数が(2、1)を生成するとします。 (5 + .2、5 + .XNUMX)を実行するセルを隣に配置する方法はありますか?
このコメントは、サイトのモデレーターによって最小化されました
これは機能しますが、私のExcelの速度が大幅に低下します。 スピードアップに役立つヒントはありますか?
このコメントは、サイトのモデレーターによって最小化されました
これは単に機能しません。 自分のアプリケーションで動作させることができなかったので、vbaと数式をコピーして貼り付けたところ、毎回エラーが返されました
このコメントは、サイトのモデレーターによって最小化されました
ありがとう、最初に私はパフォーマンスを低下させることなくこれを機能させることができました。 私はテキストではなく値を使用しているので、私の質問は、リストに19ポイント未満の値をすべて戻したいということです。 単一セルの抽出はそのために機能しますか、それとも特定の値である必要がありますか?
このコメントは、サイトのモデレーターによって最小化されました
範囲が154行より長い場合(つまり:B154)、VBコマンドは中断します。
このコメントは、サイトのモデレーターによって最小化されました
配列サイズを大きくするとエラーが表示されます
このコメントは、サイトのモデレーターによって最小化されました
2つの基準が一致すると、XNUMXつのカンマ区切りセルで複数のルックアップ値が返されます
A2 = B2次に、「SingleCellExtract」による範囲からの結果-お願いします......。
このコメントは、サイトのモデレーターによって最小化されました
、おはよう

VBAコードは私のワークシートで完全に機能し、非常に明確で単純でしたが、Excelに一意の値のみを返すように指示する方法を見つけようとしていました。 これと同じコードを使用してそれは可能でしょうか?
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、

各値のコンマとスペースで区切られた複数の値の正確な検証コードが必要です。

例:
ルーシー、トム、ニコル、アカッシュ、アップル

提案があれば再生してください。
4.5の5を評価
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、マニカンタ
複数の値をコンマとスペースで区切るには、コンマの後ろにスペースを追加するだけで、数式を次のように変更できます。 =SingleCellExtract(D2,A2:B15,2,",").
ぜひお試しください。お役に立てば幸いです。
このコメントは、サイトのモデレーターによって最小化されました
こんにちはスカイヤン、

リプレイありがとうございます!

私はすでに同じ方法を試しましたが、セル値の最後のXNUMXつの余分なコンマ(、)が下に来る例です。

ルーシー、トム、ニコル、アカッシュ、アップル、

これはJsonファイルでは機能しないため、以下のようにカンマとスペースで区切られた値が必要です。

ルーシー、トム、ニコル、アカッシュ、アップル

ありがとうございました!
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、マニカンタ
この場合、以下のユーザー定義関数を適用できます。

Function MultipleValues(work_range As Range, criteria As Variant, merge_range As Range, Optional Separator As String = ",") As Variant
Dim outcome As String
On Error Resume Next
If work_range.Count <> merge_range.Count Then
MultipleValues = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To work_range.Count
If work_range.Cells(i).Value = criteria Then
outcome = outcome & Separator & merge_range.Cells(i).Value
End If
Next i
If outcome <> "" Then
outcome = VBA.Mid(outcome, VBA.Len(Separator) + 1)
End If
MultipleValues = outcome
Exit Function
End Function


コードを貼り付けた後、次の式を使用してください。 =MultipleValues($A$2:$A$15,D2,$B$2:$B$15,", ")

試してみてください。これが役立つことを願っています!
それでも問題が解決しない場合は、ここにコメントしてください。
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-return-multiple-matching-1.png
このコメントは、サイトのモデレーターによって最小化されました
こんにちはスカイヤン、

これは現在機能しています。迅速な対応ありがとうございます。

改めて大変お世話になりました。

よろしく、
マニカンタ。
5の5を評価
このコメントは、サイトのモデレーターによって最小化されました
No soy programador pero necesito ayuda para que la función de abajo en lugar de devolverme esto

S-01-08-0017->マイクロポスト1R、黒、ステンレス鋼-エンド、->4;S-01-08-0057->マイクロポスト2R、黒、ステンレス鋼-エンド、->2

me devuelva los valores en líneas diferentes.
S-01-08-0017->マイクロポスト1R、黒、ステンレス-エンド、->4
S-01-08-0057->マイクロポスト2R、黒、ステンレス-エンド、->2

機能:
関数 SingleCellExtract (文字列としての LookupValue、範囲としての LookupRange、整数としての ColumnNumber、文字列としての Char)
'Updateby Extendoffice
薄暗い私は長く
Dim xRet As String
I = 1 の場合 LookupRange.Columns(1).Cells.Count へ
If LookupRange.Cells(I, 1) = LookupValue Then
xRet = "" の場合
xRet = LookupRange.Cells(I, ColumnNumber) & Char

xRet = xRet & "" & LookupRange.Cells(I, ColumnNumber) & Char
終了する場合
終了する場合
次へ
SingleCellExtract = Left(xRet, Len(xRet) - 1)
エンド機能
このコメントは、サイトのモデレーターによって最小化されました
こんにちは。
セミコロン文字に基づいてセルを複数の行に分割するつもりでしたか?
その場合は、次の VBA コードが役に立ちます。
Sub SplitAll()
    Dim xRg As Range
    Dim xRg1 As Range
    Dim xCell As Range
    Dim I As Long
    Dim xAddress As String
    Dim xUpdate As Boolean
    Dim xRet As Variant
    On Error Resume Next
    xAddress = Application.ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select a range", "Kutools for Excel", xAddress, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
    If xRg Is Nothing Then Exit Sub
        If xRg.Columns.Count > 1 Then
            MsgBox "You can't select multiple columns", , "Kutools for Excel"
            Exit Sub
            End If
            Set xRg1 = Application.InputBox("Split to (single cell):", "Kutools for Excel", , , , , , 8)
            Set xRg1 = xRg1.Range("A1")
            If xRg1 Is Nothing Then Exit Sub
                xUpdate = Application.ScreenUpdating
                Application.ScreenUpdating = False
                For Each xCell In xRg
                    xRet = Split(xCell.Value, ";")
                    xRg1.Worksheet.Range(xRg1.Offset(I, 0), xRg1.Offset(I + UBound(xRet, 1), 0)) = Application.WorksheetFunction.Transpose(xRet)
                    I = I + UBound(xRet, 1) + 1
                Next
                Application.ScreenUpdating = xUpdate
            End Sub

試してみてください。お役に立てば幸いです。
このコメントは、サイトのモデレーターによって最小化されました
一致が見つからない場合に「テキスト」を生成するように VBA コードを変更する場所/方法 - 上記のコードでは、値が見つからない場合は「#VALUE!」 セルに表示されます。 これは、ブックの最適な外観ではありません。 ありがとう。
ここにはまだコメントが投稿されていません
あなたのコメントを残す
ゲストとして投稿
×
この投稿を評価:
0   文字
推奨される場所

フォローする

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