VLOOKUPを使わずに2つのExcelファイルを結合する方法（ステップ・バイ・ステップ）
VLOOKUPを使わずに2つのExcelファイルを結合するには、3つの方法があります。XLOOKUPは、VLOOKUPの検索方向と一致に関する問題を解決します。Power Queryのマージは、真の結合を実行し、ファイルが変更されたときにデータを更新します。AIデータエージェントを使用すれば、自然言語で結合方法を指示するだけで、数式を完全に省略できます。どの方法が適しているかは、結合されたテーブル自体が必要なのか、それともその先にある答えが必要なのかによって異なります。
このタスク自体は、いたるところで発生します。一方のファイルに顧客リストがあり、もう一方のファイルにエクスポートした注文データがあり、それらを繋ぐ唯一の手がかりがメールアドレスやアカウントIDである場合などです。何か有益な答えを導き出すには、まずこれらを1つのビューにまとめる必要があります。
VLOOKUPは誰もが最初に使う数式ですが、最終的に誰もが痛い目を見る数式でもあります。ここでは、Excelの操作にどれだけ頭を使いたいかに応じて、いくつかの代替案を紹介します。
2つのファイルを結合するとは、実際にはどういうことか
結合とは、共通のキーを使用して2つのテーブルの行を一致させ、一方の列をもう一方のテーブルに取り込む処理です。結合の成否は3つの決定によって決まります。そのうちの1つでも誤ると、一見正しそうに見えて実は間違っているという結果を招くことになります。
どの列をキーにするか？ メールアドレス、注文ID、SKU、アカウント番号など。両方のテーブルで同じ意味を持つデータである必要があります。
一致しない行はどうするか？ 注文がない顧客も含めてすべての顧客を維持するのか、それとも注文した顧客のみを維持するのか？これらは異なる問いであり、答えも異なります。Excelは何も確認することなく、どちらの結果も平然と出力してしまいます。
キーは重複してもよいか？ 1人の顧客が5つの注文を持っている場合、左側には1行、右側には5行存在することになります。5行のままにするのか、1行に集約するのかによって、結果全体が大きく変わります。
何かを書き始める前に、この3つの問いに答えてください。結合がうまくいかない原因のほとんどは、数式の誤りではなく、前提条件の曖昧さにあります。
Excelの標準機能で2つのファイルを結合する方法
オプション1：VLOOKUPと、それが壊れやすい理由
VLOOKUPは、範囲の最も左側にある列を検索し、位置番号で指定された右側の列から値を返します。この設計により、4つのよく知られた罠が生じます。これらはすべてMicrosoftのVLOOKUP 関数リファレンスに記載されています。
- 左側を検索できない。 取得したい値よりも右側にキーがある場合、あらかじめ参照元ファイルの列を並べ替える必要があります。
- 列番号が固定値（ハードコード）である。 検索範囲内に列を挿入しても、数式は「4番目の列」を指し続けます。しかし、その位置はすでに別のフィールドになっています。エラーは表示されず、数値だけが静かに変わってしまいます。
- 検索方法のデフォルトが「近似一致」である。 最後の引数を省略すると、VLOOKUPはデータが並べ替えられている前提で最も近い値を検索します。並べ替えられていないデータに対しては、堂々と間違った値を返します。
- 最初の一致しか返さない。 キーが重複している場合、最初の1行目だけが返され、2行目から5行目が存在することに対する警告は一切表示されません。
VLOOKUPが悪いわけではありません。1980年代に設計された機能にデータベースの役割を求めているのが原因です。しかも、エラーを吐かずに静かに間違えるという、最悪の形で失敗します。
オプション2：XLOOKUP
XLOOKUPは現代的な代替機能であり、これら4つの罠のうち3つを解消します。どの方向でも検索でき、デフォルトで完全一致になります。シートに#N/Aを残す代わりに、適切なif_not_found（見つからない場合）の引数を指定できます。また、位置番号ではなく列範囲を参照するため、列を挿入しても数式が静かに壊れることはありません。構文については、MicrosoftのXLOOKUP リファレンスを参照してください。
残る制限はVLOOKUPと同じです。これはあくまで「検索（ルックアップ）」であり、「結合（ジョイン）」ではありません。1行につき1つの値しか取得できません。重複するキーは依然として最初の一致しか返さず、次の四半期に他の誰かが開くであろうファイルの何千行もの数式を、あなたが管理し続けなければならないことに変わりはありません。
オプション3：Power Queryのマージ（真の標準機能の答え）
Excelで実際の結合を行いたい場合、Power Queryのマージが最適です。両方のファイルをクエリとして読み込み、[クエリのマージ]を選択して、それぞれのキー列を指定します。次に、結合の種類を選択します。左外部結合は左側のすべてを保持し、内部結合は一致するもののみを保持し、完全外部結合は両方を保持し、反（アンチ）結合は一致しなかった行だけを抽出します。
この反結合は、過小評価されがちですが非常に強力です。「リスト内のどの顧客が一度も注文していないか」という問いに1ステップで答えることができます。これをルックアップ関数でやろうとすると非常に面倒です。また、マージは更新可能であるため、翌月のファイルも再構築することなく、同じ結合処理を適用できます。
難点は学習コストです。クエリのステップ、展開されたテーブル列、結合の種類などは、すべて知っておく価値のある概念です。しかし、1つの文章で質問できるはずの問いに対して、4つも5つもの概念を理解しなければならないという壁があります。
3つの方法すべてが限界を迎えるポイント
Excelの標準機能を使う方法は、すべて同じ3つの限界に突き当たります。
キーがきれいな状態であることは稀である。 john@acme.com と John@Acme.com は同じ顧客ですが、完全一致では別物と判定されます。実際のキーには、末尾のスペース、大文字小文字の不一致、文字列として保存された数値、古いエクスポートデータから紛れ込んだアポストロフィなどが含まれています。標準機能を使う方法では、まずキーを正規化する必要がありますが、マッチ率が60%にとどまっている原因がそこにあることを教えてくれる機能はありません。
結合されたテーブル自体は答えではない。 誰もマージされたシートそのものを欲しがっているわけではありません。本当に知りたいのは、どのセグメントが成長しているか、どのアカウントが解約されたか、あるいはどのSKUが利益をもたらしているかです。結合は配管作業のようなものであり、その配管作業にほとんどの時間が費やされています。
次の人があなたの数式を引き継ぐことになる。 ネストされたルックアップ関数だらけのワークブックは、メンテナンスの負債になります。列が1つ移動しただけで、すべてが機能しなくなります。
Powerdrill Bloomで2つのExcelファイルを結合する方法
Powerdrill Bloomは、結合を「最初に完了すべきステップ」としてではなく、「質問の一部」として扱います。両方のファイルをアップロードし、何がそれらを繋ぐかを伝えるだけで、行を一致させ、マッチ率を報告し、そのまま分析へと進みます。
ステップ 1：両方のファイルをアップロードする
両方のワークブックを1つのワークスペースにドラッグ＆ドロップします。BloomはExcel、CSV、TSV、PDFを読み込み、取り込み時に自動でクレンジングを行うため、末尾のスペースや大文字小文字の混在するキーも、静かに無視されることなく適切に処理されます。
キーが左側にくるように列を並べ替える必要はありません。また、2つのファイルのレイアウトを統一する必要もありません。
ステップ 2：自然言語で結合方法を指示する
何がそれらを繋いでいるのか、そして何を出力したいのかを伝えます。「注文ファイルと顧客ファイルをメールアドレスで一致させ、注文がない顧客も含めてすべての顧客を維持し、一致しなかった件数を教えてください」という指示だけで十分です。
さらに、ルックアップ関数では不可能な指示を続けて行うことができます。「次に、顧客セグメント別の売上を表示し、前四半期と比較して最も減少幅の大きい10個のアカウントをリストアップしてください。」結合と分析が1回で同時に実行されます。
これが毎月のルーティン作業であれば、エージェントのスキルとして保存しておくことで、毎回入力し直すことなく、翌月のファイルに対して再実行できます。
ステップ 3：結合された結果、チャート、またはスライドをエクスポートする
結合されたテーブルをファイルとして保存したり、チャートを書き出したり、ワンクリックでキャンバス全体をスライド（Professional、Business、Fancyなどのスタイル）に変換して、PowerPointやNotionにエクスポートしたりできます。
この最後のオプションこそが、午後の作業時間を大幅に節約してくれます。結合すること自体が最終成果物ではなかったはずだからです。
数式を省略すること以上に重要な理由
本当に重要な比較は、「数式を使うか使わないか」ではありません。データに不備があったときに、それぞれの方法がどのように振る舞うかです。
| VLOOKUP | XLOOKUP | Power Query Merge | Powerdrill Bloom | |
|---|---|---|---|---|
| キーの位置は不問 | いいえ | はい | はい | はい |
| 列の挿入に耐えられる | いいえ | はい | はい | はい |
| 重複するキーを適切に処理できる | いいえ | いいえ | はい | はい |
| 一致しない行を抽出できる | 手動 | 手動 | はい（反結合） | はい |
| 不備のあるキーを自動クレンジング | いいえ | いいえ | 手動ステップ | はい |
| マッチ率を報告する | いいえ | いいえ | いいえ | はい |
| そのまま問いの答えを導き出せる | いいえ | いいえ | いいえ | はい |
| 必要なスキル | 数式 | 数式 | クエリエディター | 自然言語 |
この表を客観的に見れば、「Excelはもう古い」という結論には至りません。そうではなく、Excelのツールは「結合されたテーブルを作成する」ために作られており、テーブルを作成することは業務全体のほんの一部にすぎないということです。
スプレッドシートを結合する際のベストプラクティス
結合する前にキーを正規化する
空白を削除し、大文字小文字を統一し、両方のテーブルでIDが同じデータ型で保存されていることを確認します。不備のあるキーで結合を行ってもエラーは発生しません。ただ静かにマッチ率が下がるだけです。そして、60%というマッチ率は、データの問題ではなく、ビジネス上の事実であるかのように見えてしまいます。
一致しなかった行数を必ずカウントする
一致しなかったデータ群こそ、最も興味深い情報であることがよくあります。注文のない顧客、顧客レコードのない注文、一方のシステムにしか存在しないSKUなど、業務上の問題はそこに潜んでいます。データファイルの結合に関する詳細については、弊社のデータファイルの結合方法のガイドで詳しく解説しています。
結合前ではなく、結合後に行数を確認する
左側のファイルが4,000行で、結合後の結果が11,000行になっている場合、キーが重複しており、データが意図せず増殖している可能性があります。意図したものであれば問題ありませんが、そうでない場合は深刻な問題です。特に、売上列を合計する前には必ず確認してください。
集計する前に「一対多」の関係をどう扱うか決める
1人の顧客が5つの注文を持っている場合、5行のままにするか、1行に集約するかのどちらかを選択する必要があります。データが増殖した状態で売上を合計すると、重複してカウントされてしまいます。この単純なミスは、数式の誤りよりも多くの誤ったダッシュボードを生み出す原因となっています。
避けるべきよくある間違い
- IDではなく名前で結合する。 完全一致の観点からは、「Acme Corp」、「Acme Corp.」、「ACME Corporation」はすべて異なる3つの会社として扱われます。
- VLOOKUPの第4引数を省略する。 デフォルトは近似一致となり、並べ替えられていないデータに対してエラーを出さずに誤った値を返します。
#N/Aをゼロと解釈する。 「一致なし」と「実際のゼロ」は全く異なる意味を持ちます。すべてをIFERROR(...,0)で囲んでしまうと、その違いが見えなくなります。- 重複を排除する前に結合する。 どちらかのテーブルに重複するキーがある場合、結合によってデータが倍増します。まずクレンジングを行い、それから結合してください。
- 一対多の結合後に合計を算出する。 典型的な二重カウントのミスです。合計値を信用する前に、必ず行数を確認してください。
結論
キーがきれいで、1回限りの簡単なデータ抽出であれば、XLOOKUPが最適なツールであり、30秒で完了します。安定したファイルで繰り返し結合を行う場合は、Power Queryのマージを構築し、反結合を使用して一致しないデータを検出します。キーに不備がある場合、キーが重複している場合、あるいは本当に必要なのがマージされたシートではなくチャートやスライドである場合は、数式を書く代わりに結合方法を言葉で指示してください。
ご自身の2つのファイルを使って、無料でテストすることができます。Powerdrill Bloomの無料プランには、毎日更新される1,000クレジットが含まれています。Excel AI アシスタントやCSVファイルの結合のページでも同様のワークフローを紹介しており、AIによるExcel分析では単一ファイルを扱う場合について解説しています。
よくある質問
2つのExcelファイルを結合するために、VLOOKUPの代わりに何が使えますか？
XLOOKUPは直接の代替機能であり、VLOOKUPの最大の弱点を解決します。どの方向でも検索でき、デフォルトで完全一致になり、列が挿入されても壊れません。2つのテーブルを真に結合する場合は、重複するキーを処理でき、一致しない行を抽出できるPower Queryのマージが、標準機能としてより優れたツールです。
ファイルの結合において、Power QueryはVLOOKUPよりも優れていますか？
繰り返し行う作業であれば、間違いなく優れています。Power Queryは、選択可能な結合の種類を用いて真の結合を実行し、参照元ファイルが変更されたときにデータを更新します。また、ワークブックに何千もの数式を残すこともありません。ただし、きれいな1つの列に対して1回限りのアドホックな抽出を行うだけであれば、依然としてVLOOKUPの方が素早く処理できます。
列名が異なる2つのExcelファイルを結合するにはどうすればよいですか？
Power Queryでは、それぞれのテーブルで異なるキー列を選択できるため、列名が一致している必要はありません（値さえ一致していれば問題ありません）。AIデータエージェントを使用すれば、ファイルを読み込む際に自動的に列をマッチングし、両者で不一致がある箇所を報告してくれます。
VLOOKUPがエラーではなく間違った値を返すのはなぜですか？
ほとんどの場合、第4引数が省略されていることが原因です。引数を省略すると、VLOOKUPは近似一致を実行します。これはデータが並べ替えられていることを前提としており、そうでない場合は見つかった最も近い小さな値を返します。完全一致を強制するには、最後の引数をFALSEに設定してください。
数式を一切使わずに2つのExcelファイルを結合することはできますか？
はい、可能です。Excelの内部機能であるPower Queryのマージは、クエリエディターを使用しますが、数式を使わない方法です。AIデータエージェントを使用すれば、両方のファイルをアップロードして結合方法を1つの文章で説明するだけでよく、数式もクエリのステップも不要になります。