
2 つの異なる Excel スプレッドシートのデータを 3 番目の Excel スプレッドシートに結合する最も簡単で最適な方法は何ですか? スプレッドシート 1 にはユーザー名のみが含まれます。スプレッドシート 2 には、名、姓、部門、ユーザー名など、多くの情報が含まれます。必要なのは、3 番目のスプレッドシートに、スプレッドシート 1 のユーザー名と、スプレッドシート 2 の名、姓、部門を表示することです。
答え1
使用したい関数は ですVLOOKUP
。これを行う方法はシートの配置方法によって若干異なりますが、すべて同じ構文に従います。
=VLOOKUP( lookup value
, table array
, column index number
, range lookup
)
lookup value
検索したいデータです。table array
検索対象を含む列を含む、データを取得するセルを定義します。column index number
は、情報を取得する列のインデックスですtable array
。(例: A:E の配列の場合、列 D は になります4
。)range lookup
は、近似一致が許容されるか、または完全一致が必要であるかを指定する TRUE/FALSE オプションです。 簡単にするために、私は常にこれを に設定しますFALSE
。 詳細が必要な場合は、Excel で F1 キーを押してください。
データがすべて同じブック内にあるかどうかによって、数式の形式が少し異なります。以下にそれぞれの例を示します。
重要な注意点:あなたが検索に使用しているキーワードはlookup value
しなければならないtable array
VLOOKUP が機能するには、最初の列で検索可能である必要があります。
最初の例: すべてのデータは同じ Excel ブック内にありますが、シートは異なります。最初のシートは「ユーザー名」というラベルが付いており、ユーザー名のみが含まれています。2 番目のシートは「ユーザー データ」と呼ばれ、すべてのユーザーの詳細が含まれています。3 番目のシートは「検索結果」と呼びます。「ユーザー データ」シートには 5 つの列が含まれていますA:E
。
- 「ユーザーデータ」スプレッドシートの列 A にすべてのユーザー名が含まれていることを確認します。
- 「ユーザー名」からすべてのユーザー名を「検索結果」にコピーします。
- ヘッダー行を使用していると仮定すると、「検索結果」の最初のユーザー名は A2 に配置されます。
- 「検索結果」の B2 の式は次のようになります。
=VLOOKUP(A2,'User Data'!A:B,2,FALSE)
- 「検索結果」の B3 の式は次のようになります。
=VLOOKUP(A3,'User Data'!A:B,2,FALSE)
- 「検索結果」の C2 の式は次のようになります。
=VLOOKUP(A2,'User Data'!A:C,3,FALSE)
ここまででパターンがおわかりいただけたかと思います。各列の最初のセル(例:B2)にVLOOKUP式を記述し、シートの残りの部分にその式を入力するだけでよいはずです。ただし、式を切り取って貼り付けることはできません。横切ってtable array
列の更新はそれほど簡単ではありません。および の値を更新する必要がありますcolumn index number
。
2番目の例: 各データ セットは、独自の Excel ワークブックに保存されます。ワークブック内のシート名は既定値です (つまり、最初のシートは「Sheet1」です)。ワークブックのファイル名は、「Usernames.xlsx」、「User Data.xlsx」、および「Lookup Results.xlsx」です。これらはすべて、「Me」というユーザーのデスクトップにある「My Spreadsheets」というフォルダーにあります。
- 「User Data.xlsx」シートの列 A にすべてのユーザー名が含まれていることを確認します。
- 「Usernames.xlsx」からすべてのユーザー名を「Lookup Results.xlsx」にコピーします。
- 繰り返しますが、ヘッダー行を使用すると仮定すると、これは A2 から始まります。
- 「Lookup Result.xlsx」のB2の式は次のようになります。
=VLOOKUP(A2,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:B,2,FALSE)
- 「Lookup Result.xlsx」のB3の式は次のようになります。
=VLOOKUP(A3,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:B,2,FALSE)
- 「Lookup Result.xlsx」のC2の式は次のようになります。
=VLOOKUP(A2,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:C,3,FALSE)
もう一度言いますが、ここまででパターンがわかるはずです。必要に応じて行と列を切り取り、貼り付け、微調整すれば、準備完了です。
ここで留意すべきもう 1 つの点は、このシートは「ユーザー名」データの変更に対して自動的に更新されないということです。この方法で「ユーザー データ」シートの変更を取得できますが、「ユーザー名」の変更も追跡する場合は、より高度なテクニックが必要になります。
答え2
Vlookupなどの機能は、両方のシートのデータが同一である場合にのみ機能します。
必要なのはExcelのファジーロジックアドオンだと思います。これにより、いくつかの異なるパラメータに基づいて類似の結果を検索できます。ダウンロードページ。
答え3
HLOOKUP/VLOOKUP - 数式を使用してスプレッドシート 1 からユーザー名を取得し、ユーザー名をキーとして使用し、スプレッドシート 2 を HLOOKUP/VLOOKUP の 1 つのインスタンスのルックアップ マトリックスとして使用します (英語以外の Excel バージョンを使用しているため、どちらがどちらかわかりません)。
答え4
Excel ファイルからのクエリを使用できます:
- スプレッドシート 1 のデータセットの名前を定義します (数式タブ -> 名前の定義)
- スプレッドシート2でデータセットの名前を定義する
- スプレッドシート 1 で、[データ] タブに移動し、[その他のソースから] を選択して、ドロップダウンから [Microsoft Query から] を選択します。
- 他のスプレッドシートファイルを選択し、列を手動で結合することを確認します
- 次のウィンドウ「Excelファイルからのクエリ」で、最初のデータセットの「ユーザー名」列を2番目のデータセットの「ユーザー名」列にドラッグアンドドロップします。これらの列の間にリンクが作成されます。
- ファイルメニューに移動し、「データをMS Office Excelに戻す」をクリックすると、データのインポートダイアログがポップアップ表示されます。
- 一致したデータをインポートするシートを選択します
- [OK]をクリックすると、両方のスプレッドシートの列に一致するデータが表示されます。