のぼゆエンジニアリング

ゼロから機械設計を勉強してみたりするブログ

エクセルで複数のブックを結合して、検索・抽出する方法。Power Queryで簡単にできたよ



スポンサーリンク

のぼゆエンジニアリング

2017/4/12追記
複数のエクセルブックのデータを結合する方法を、紹介します。
一つのエクセルファイルになるので、オートフィルで抽出したり、並べ替えたり。
エクセル上でいつも通りにデータを扱えます。

やりたいこと

今回この記事でやりたいことは、

「複数のエクセルブックのデータを結合して、検索、抽出したい」

ということです。

例えば、複数の装置の部品リスト(エクセルファイル)がそれぞれあったとします。
それらから、共通して使われている部品を抽出したい。

そんなときにこの記事の方法が役立ちます。

方法のフロー

まずは全体的な方法のフローです。

  1. アドインを公式サイトから、エクセルに導入する。
  2. アドインでデータを処理して結合する。
  3. エクセルに結合後のデータを戻す

サンプル課題

方法の説明に当たって、下記のような課題を設定しました。

「装置A、装置B、装置Cの部品リスト」のエクセルファイルがそれぞれあるとする。
装置A~Cに共通して使われている部品を検索、抽出したい


装置A~Cの部品リストはこんな感じ。

複数のブックの結合課題

そこで、それぞれの部品表を結合し、一つの部品リストにしていきます
結合さえできれば、オートフィルで簡単に抽出したり並べ替えたり、自由に処理が出来ますよね。

さて、具体的に書いていきます。

1.公式アドイン「Power Query」をインストールする

下記の公式ページからダウンロードし、インストールします。


システム要件に注意してください。
対応するエクセルは、
Microsoft Office 2010 Professional Plus
Microsoft Office 2013   とあります。

2.「Power Query」でデータを処理して結合する。(最新バージョンの場合)

この方法は、Power Queryのバージョンが最新版(2017/04/04公開のVer.2.43.4647.201)の方法です。
旧バージョンなら、次の2’の方法を試してください。

2017/4/4に公開されたバージョンから、結合だけなら更に楽になりました。

手順は下記の通りです。

  • Power Queryタブを選択し、ファイルから→フォルダーからをクリック。
    Power Queryで複数のブックの結合

  • ブック(エクセルファイル)が格納されたフォルダを指定します。
    Power Queryで複数のブックの結合

  • 「結合」というボタンがあります。▼からメニューを出して、「結合および読み込み」をクリック。
    Power Query(パワークエリ)で複数のブックの結合

  • リストを書いているシート名をクリック。シート1ならシート1を。
    Power Query(パワークエリ)で複数のブックの結合

  • はい、完成です。
    Power Query(パワークエリ)で複数のブックの結合

2’.「Power Query」でデータを処理して結合する。(旧バージョンの場合)

この方法は、Power Queryのバージョンが古い場合の方法です。
最新バージョン(2017/04/04公開のVer.2.43.4647.201)なら、上述の2.で完結します。

2-1.複数のエクセルブックをひとまず読み込む

  • Power Queryタブを選択し、ファイルから→フォルダーからをクリック。
    Power Queryで複数のブックの結合

  • ブック(エクセルファイル)が格納されたフォルダを指定します。
    Power Queryで複数のブックの結合

  • 読み込みをクリック。
    Power Queryで複数のブックの結合

  • ブックが読み込まれました。
    でも、単なるブックの一覧になっていて表(テーブル)の中身が無いですね? Power Queryで複数のブックの結合

2-2.テーブルの中身を取り込む

  • クエリタブ→クエリの編集をクリック。「クエリエディタ」を立ち上げます。
    Power Queryで複数のブックの結合

  • Content列見出しを右クリック。他の列を削除します。
    Power Queryで複数のブックの結合

  • 列の追加タブ→「カスタム列の追加」をクリック。
    でてきたウィンドウに下記数式を書き込みます。

=Excel.Workbook([content])

ちなみに、[content]は手打ちしなくても、右に出ている項目をダブルクリックすると書き込まれます。
Power Queryで複数のブックの結合

  • Content列見出しを右クリック。Content列を削除します。 Power Queryで複数のブックの結合

  • カスタム列見出しの右上マークをクリック。OKを押して展開します。 Power Queryで複数のブックの結合

  • 列の追加タブ→「カスタム列の追加」をクリック。
    でてきたウィンドウに下記数式を書き込みます。

=Table.PromoteHeaders([カスタム.Data])

  • 「新しい列名」には、適当な名前を付けておいて下さい。
    (この例ではカスタム2と付けた)
    ちなみに、[カスタム.Data]は手打ちしなくても、右に出ている項目をダブルクリックすると書き込まれます。
    Power Queryで複数のブックの結合

  • 今作った「カスタム2」列見出しを右クリック。他の列を削除します。 Power Queryで複数のブックの結合

  • 作った「カスタム2」列見出しの右上マークをクリック。OKを押して展開します。
    下のチェックボックスはオフにしておいてください。 Power Queryで複数のブックの結合

  • はい、結合されました。
    Power Queryで複数のブックの結合

3.【完成】エクセルに結合後のデータを戻す

  • ホームタブ→「閉じて読み込む」をクリック。 Power Queryで複数のブックの結合

  • 結合データがエクセルに移りました。
    あとは並べ替えたり、いつも通りエクセルで編集ができます。
    例えば、購入先がXX社の部材を抽出する、とかもできますね。便利です。 Power Queryで複数のブックの結合

この方法の良いところ

素人でも付け焼刃で簡単にできることです。

私は当初VBA、マクロ、Accessにデータを渡して・・など色々試したのですが時間と手間ばっかりかかって挫折しました。
腰を据えて勉強が必要っぽいですが、根気と時間が無くて断念・・・

公式アドインとして用意されたのは本当にありがたいです。

© 2017 のぼゆエンジニアリング
About記事一覧お問合せ