【Microsoft Query】を使って統合データを作成

Microsoft officeのExcel(エクセル)で、各シートのデータを共通の値でまとめたい時に、昇順or降順で並び替え→目視で列の値を見比べ→統合シートに値を貼り付け。でやっていて、まぁそれでもそんなに時間がかかっている訳では無かったのですが、これがルーチン業務だった為、年間するとまぁそこそこの時間数になっていました。

私は無駄なコスト&リソースカットがモットーの為、色々調べて『【Microsoft Query】を使って統合データを作成』の方法に落ち着きました。やり方はこんな感じです。

1、1つのエクセルファイルのシート毎別に統合前のデータを貼り付ける

2、各シートの見出しを含むデータ範囲を名前の管理で『定義された名前』として登録
・名前の管理は、『メニュー>名前の管理>新規作成>名前/参照範囲を入力』もしくは、範囲をセル指定>左のセル範囲がしていされている個所に『定義された名前』を直接入力でもOKです。

thum01

3、全シートのデータ範囲の『定義された名前』登録完了後、『メニュー>その他のデータソース>Microsoft Query』選択後、『Excel Files*>OK』。データベースとして、当該エクセルファイルを選択後、OKをクリック。

thum02

thum03

4、各シートの見出しが左側に表示されるので、右側のクエリ列に全てor指定して追加後、次へをクリック。「クエリでテーブルを結合できないため、~結合してください。」と表示されるが、気にせずOKをクリック

thum04

5、上部に各シートの見出し名、その下に全シートの値が表示されます。

6、上部の各シートの見出しの軸となる共通の値同士をドラッグで繋ぎあわせます(線で繋がれた状態になります)。メニュー>ファイル>Microsoft Excelにデータを返す>新規ワークシート>OK

thum05

7、なんと!軸となる共通の値を基準に統合データの完成です!

※このMicrosoft Queryの一番のメリットは、作成後、1、の統合前データの見出しが同じ場合、統合前データの値のみ更新後、メニュー>データ>すべて更新>すべて更新を実行するだけで、統合データのみが更新されるって所です!作成したデータを過去データとして、別シートに貼り付けておけば、以降は、統合前の元データの値を更新するだけっていう優れものです!しかも、商品管理等毎日行う複数のリストデータも応用次第では、毎日更新できるという所もイケてる所です。

皆さんも機会があればぜひご活用ください!

シェアする

  • このエントリーをはてなブックマークに追加

フォローする