☰

Excel で、同じ参照値 (主キー) を持つ複数行のデータに対して Vlookup を実行する

tag-icon • tag-icon microsoft-excel
Excel で、同じ参照値 (主キー) を持つ複数行のデータに対して Vlookup を実行する

同じ主キーに対して複数の行を持つ名前に対して Vlookup を実行したい:

Deal ID (Primary Key) | Name
437                   | Tom Jones
437                   | Frank Thomas
437                   | Mary Smith

Vlookup は、レコードの最初の名前 (Tom Jones) のみを返します。Tom Jones、Frank Thomas、Mary Smith がすべて同じ行に表示されるようにしたいです。

答え1

Excel をリレーショナル データベースとして使用しようとしているようですが、Excel はそれほど優れたツールではありません。さらに、近年変更がない限り (私は Excel 2010 より後のバージョンを使用したことはありません)、範囲または配列を区切り文字列に変換するワークシート関数はありません。それを実行する VBA ワークシート関数を考案する必要があります。

配列数式と VBA を組み合わせて使用​​すれば、必要な操作を実行できます。先に進む前に、このような作業を定期的に行う場合は、実際のデータベース プログラムを使用することを強くお勧めします。私はこれまで、単純なリレーショナル データベースのようなタスクを実行するために Excel を悪用したことがありますが、単純なものだけです。もっと複雑な操作を実行しなければならない場合は、非常に困難だったでしょう。

検索するIDがセルにありF1、テーブルが と呼ばれていると仮定するとTable1、次のように入力します。配列数式(Enter の代わりに、Ctrl + Shift + Enter を使用します)。SimpleCatこれは、VBA 連結ワークシート関数です。

=SimpleCat(IF(Table1[Deal ID (Primary Key)]=$F$1, Table1[Name], ""))

SimpleCatこの特定のインスタンスに機能する は次のとおりです。

Function SimpleCat(Args() As Variant) As Variant
    Dim a As Variant
    SimpleCat = ""
    For Each a In Args
        If a <> "" Then SimpleCat = SimpleCat & a & ", "
    Next
    If Len(SimpleCat) > 0 Then SimpleCat = Left$(SimpleCat, Len(SimpleCat) - 2)
End Function

SimpleCatより完全な機能を備えた汎用連結関数は誰のツールボックスにも役立つため、これを拡張してより一般的なものにするための演習として残しておきます。

説明：

配列数式として評価される場合、テーブル列全体を単一の値と比較すると、 および の配列が生成されますTRUE。FALSEを使用するとIF、 の true の場合の値側で使用している配列の正しい位置から値が取得され、 のIFfalse の場合の値側で使用している空の文字列が入力され、新しい配列が構築されますIF。 関数SimpleCatは、この新しい配列に、空でないすべての値をコンマで区切って配置します。

答え2

問題は 2 つあります。まず、これは主キーではありません。次に、Excel には、必要な結果を提供する組み込み機能がありません。

主キーは、単一のレコードに関連付けられ、決して繰り返されない一意の識別子である必要があります。Excel のほとんどのルックアップ関数は、この目的で設計されており、一致が見つかると停止します。一致がすでに見つかっている場合は、毎回範囲全体を検索する必要がないため、リソースを節約できます。

ここで求めているような統合、つまり複数の値を 1 つの文字列にまとめる唯一の方法は、マクロまたはカスタム関数を使用することです。必要なコードは正確には、求めている結果によって異なり、残念ながらこの回答の範囲外です。

答え3

私はこの機能を使います...

Function ConcatRange(inputRange As Range, Optional delimiter As String) As String
    Dim oneCell As Range
    Dim usedRange As Range

    Set usedRange = Application.Intersect(inputRange.Parent.usedRange, inputRange.Cells)
    If Not (usedRange Is Nothing) Then
        For Each oneCell In usedRange
            If oneCell.Text <> vbNullString Then
                ConcatRange = ConcatRange & delimiter & Trim(oneCell.Text)
            End If
        Next oneCell
        ConcatRange = Mid(ConcatRange, Len(delimiter) + 1)
    End If
End Function

これを修正して、次のような新しい関数を作成します。

Function ConcatIf(KeyRange as Range, KeyValue as Variant, DataColumnOffset As Integer)

ここでは、KeyRange内の各セルをループし、その値がKeyValueと等しい場合にのみ、oneCell.Offset(0, DataColumnOffset)

関連情報