☰

2つの列を結合してデータのリストを作成する

tag-icon • tag-icon microsoft-excel vba
2つの列を結合してデータのリストを作成する

現在、比較する必要がある 2 つの列があります。シート 1 の列 A とシート 2 の列 A です。

シート 1 には次の内容が含まれます。

A      B       C
5000   Apples  WI
6182   Oranges NY
7271   Grapes  MN
2293   Peanuts FL

シート 2 には次の内容が含まれます。

A
4032
5233
7271
2293

次のような結果が得られるはずです。

7271   Grapes  MN
2293   Peanuts FL

シート 2 と同じ番号を含む結果のみを表示する必要があります。シート 2 を配列にロードし、シート 1 の各セルと比較するよりも良い方法はありますか?

    'For i = 1 to Sheet1LastRow

    Sheet2Row = Range("A" & Rows.Count).End(xlUp).Row
    Sheet2Array = Range("A2:A" & Sheet2LastRow).Value
            For i = LBound(Sheet2Array, 1) To UBound(Sheet2Array, 1)

    'if cell = Sheet2Array(i, 1)
     '....
    'End if

    Next i

   'Next Cell sheet 1

答え1

これが のコア機能です=VLOOKUP()。

構文は次のとおりです。

=VLOOKUP(
         compare this cell, 
         to the cells in the leftmost column of this range,
         returning the corresponding value from this column index,
         true/false for range lookup (just leave this false if you aren't sure)
 )

シート2のセルB1に以下を配置し=VLOOKUP(A1,Sheet1!A:B,2,False)、C1に以下を配置します。=VLOOKUP(A1,Sheet1!A:C,3,False)

この関数は、ワークブック間だけでなくワークシート間でも機能します (ただし、リンクを有効にし、両方のワークブックの変更に注意する必要があります)。VBA は必要ありません。これは単純なインライン関数です。

これを拡張すると、#N/Aソース テーブルに存在しない値に対してエラーが発生する可能性があります。=IFERROR( your vlookup() function , "" )エラーを空白セルに置き換えるには、数式を囲みます。

答え2

ご質問とサンプル データから、出力として探しているのは、間に空白やエラーがなく、連続したセル ブロック内に一致するデータがある列であることは明らかです。

数式アプローチを使用する場合は、配列数式で少し複雑な INDEX 関数と MATCH 関数を使用して実現できます。列内の連続したセルに一致するデータを取得したら、VLOOKUP を適用して、Sheet1 のマスター テーブルから残りの 2 つの列を取得します。

方法は次のとおりです。以下は、Sheet1 と Sheet2 の 2 つのスクリーンショットです。

ここに画像の説明を入力してください

ここに画像の説明を入力してください

Sheet1には列A、B、Cにマスターテーブルがあり、Sheet2にはSheet1の列Aと一致するリストがあります。

次にSheet2のC1セルに次の配列数式を入力します。

{=INDEX(Sheet1!$A$1:$A$4,SMALL(IF(ISERROR(MATCH(Sheet2!$A$1:$A$4,Sheet1!$A$1:$A$4,0)),"",MATCH(Sheet2!$A$1:$A$4,Sheet1!$A$1:$A$4,0)),ROW(1:1)))}

この数式を中括弧なしで入力し、数式バー内で CTRL+SHIFT+ENTER を押して配列数式を作成し、セル内のエラーまたは一致するリストの長さになるまで下にドラッグします。これで、エラー セルより上のすべての行に、Sheet1 の列 A からの一致するセルが含まれるようになります。

エラー セルをクリアするだけで、連続したセル ブロックにリストが表示されます。

次に、D1に比較的単純なVLOOKUP式を入力して、Sheet1から次の一致する列を取得します。

=VLOOKUP(C1,Sheet1!$A$1:$C$4,2,FALSE)

E1に次の式を入れます

=VLOOKUP(C1,Sheet1!$A$1:$C$4,3,FALSE)

両方を下にドラッグします。希望どおりのリストが完成します。

Excel に組み込まれている数式の評価オプションを使用して INDEX MATCH の組み合わせを評価し、これがどのように機能するかを確かめることができます。一致する行番号の配列が作成され、数式を下にドラッグすると、最初に小さい数値、次に 2 番目、3 番目に小さい数値が選択されます。

ここでの 2 つの制限は、データがマスター テーブル内に存在する順序で返され、「一致する」テーブル内の順序では返されないこと、および一致するセルが複数ある場合は最初のセルが返されることです。

関連情報