【エクセル】複数ブックのデータを統合・集計する方法【パワークエリ】

ecoslymeです。

 

同じ書式(構成)の複数のエクセルブックのデータを取ってきて、一つのエクセルにコピペ(最下行へ追加)してから統合作業集計作業を行ったことのある人は多いのではないでしょうか?

 

エクセルブックが2,3個であれば問題ないのですが、そのブック数が10を超えると正直しんどくなってきます。

 

困っている人
同じ構成のエクセルをグループ会社や全部に展開して、回収してから集計する作業が本当に面倒。。。
困っている人
全国の営業所のデータを回収してから、エクセルで集計する単純作業が苦痛。。。

 

エクセルのブックが分かれているので、違うブックにあるエクセルは簡単に集計できない、と思い込んでいる人も中に入るのではないでしょうか。

 

そこでこの記事では、エクセルのPower Query(パワークエリ)という機能を利用して、複数のエクセルブックからデータを取得して一つのデータに集計する方法をご紹介します。

 

めちゃくちゃ便利な機能なので、是非覚えて下さい。

 

ベクター 航海

元データと結果

(同じ書式のエクセル ※1つのフォルダに複数ブックがある状態)

(エクセルブックの中身 ※全ブック同じ項目)

(パワークエリ実行後のエクセル)

 

Power Query(パワークエリ)で複数のエクセルブックを統合・集計する前提条件

ベクター 講習

まずは、このPower Query(パワークエリ)で複数のエクセルブックを統合・集計できる前提条件についてご紹介します。

 

以下の条件でない場合は、利用できない可能性があります。

 

  • パワークエリで複数のエクセルブックを統合・集計できる環境

1つのフォルダに全てのエクセルブックが収納されている

シートの名前が全て同じ

項目名(列)が全て同じ

 

ファイル名はもちろん一致していなくて良いです。(同じファイル名だと同じフォルダの中に置けません)

 

パワークエリ実行後に、エクセルの1列にブック名を表記することが出来るので、統一感のあるブック名にしておいた方が良いです。

 

複数のエクセルブックを統合・集計・結合する方法

それでは、複数のエクセルブックを統合・集計・結合する方法についてご説明します。

 

例として、株価のデータを利用してみます。

 

とある時点の株価のデータをエクセルブックにしています。

 

・ある一時点の株価のデータ

・銘柄番号:1300番台~1700番台が、100番台ずつ5つのエクセルブックに記載されている

 

<例:1300番台のエクセルブックの中身>

 

同じようなデータが5つあり、この5つに分かれたエクセルブックを1つのエクセルブックに結合(統合・集計)します。

 

データの取得(ファイルから ⇒ フォルダから)

エクセルで、以下の要領で「フォルダから」を選択して下さい。

 

・データ ⇒ データの取得 ⇒ ファイルから ⇒ フォルダから

 

 

URL入力

URLの欄に、結合したいデータ(表)があるフォルダのURLを入力します。

 

その後、OKをクリックします。

フォルダ内の複数エクセルブックからデータの変換

フォルダ内の複数エクセルブックが表示されている画面に行きますので、以下のように操作して下さい。

 

・「結合」⇒「データの結合と変換」

 

Fileの結合

Fileの結合の画面に行きますので、結合させたい全エクセルブックに共通するシート名をクリックします。

 

 

データ修正後、データを閉じて読み込む

データを修正できるエディターが表示されます。

 

このエディターでデータの修正が出来ます。

 

イメージとしては、ACCESSでの処理と似たようなものです。

 

データを扱うスキルの差が生じてくる部分かと想定しています。

 

今回は左上の「閉じて読み込む」をいきなりクリックして、編集を終了してもOKです。

 

名前の修正

クエリで名前の修正を行いたい場合は、該当する項目名をダブルクリックして名前を編集しましょう。

 

「Source Name」には、ファイル名が表示されますので、「.xlsx」という無駄な文字を削除しておきましょう。

 

・値の変換(データ置換と同じ)で、「.xlsx」⇒「」(空白の意味)へ変換させましょう。

⇒(Source Nameで、「.xlsx」を削除)

 

データ結合したデータのエクセルへの表示

データ結合してきたデータが、エクセルに表示されました。

 

 

クエリの編集を行いたい場合

クエリの編集を行いたい場合、下図のようにエクセル画面上部の「クエリ」⇒「編集」をクリックすれば、エディター画面が再度表示されます。

 

 

表示形式の微修正

表示形式の微修正はわざわざクエリの方で行わずとも、エクセルの方で微修正行うことが出来ます。

 

表示形式について詳しく知りたい方は、以下の記事をご覧ください。

関連記事

ecoslymeです。日々の業務の中で、スケジュール表を毎月更新もしくは毎年更新することってありませんか?昨年度のエクセル(Excel)のスケジュール表を使い回したはいいけれども、年度が違えば曜日が変わっていて、その曜日を修[…]

 

クエリを更新する方法

パワークエリは更新しなければ最新のデータにはなりません。

 

パワークエリを手動で更新する方法をご紹介します。

 

・クエリ ⇒ 更新 をクリック

 

これでデータが更新されます。

 

最後に

ベクター 散歩

いかがでしたでしょうか。

 

もしかしたら、エクセルではエクセル間もしくはそのエクセル内にあるデータからしかデータの編集ができない、と思っていた人も多いのではないでしょうか。

 

「同じ書式のエクセルを複数名に渡して、何かデータを記載してしてもらった後に回収する」という作業は結構多い作業だと思います。

 

その後集計する時にこのパワークエリの機能を知っているだけで、業務効率が格段に上がります。

 

特にRPA(WinActorなど)を利用している人にとっては、相性が良ければ複数エクセルからのデータ取得がパワークエリで行える分安定するため、更にロボット稼働時間も短縮するかもしれません。

 

マクロを作らなければ出来ないと思っていたことが、このようにパワークエリで実現できてしまうことがあります。

 

属人業務になりにくいレベルのスキルなので、再現性の難易度は低いと思うので引継ぎ作業も容易かと思っています。

世界最大級のオンライン学習サイトUdemy

Udemyは受講者数3,000万人の世界最大級オンライン学習プラットフォームです。

 

✔︎ みんなに内緒でエクセル技術力を大幅アップできる! 社内で人気者間違いなし!!

✔︎ E-Learningでいつでも、どこでも学べる! 行動格差を広げよう!!

✔︎ 人気コース、評価が一目で分かる! 気に入ったコースがすぐ選べる!!

 

※「検索」で学びたいコースを入力してくだい

スキルのフリマ【ココナラ】

ココナラはクリエイターへの制作依頼から個人のお悩み解決までできる、スキルのオンラインマーケットです。

 

✔︎ 自分の業務マクロ自動化を依頼できる! 内緒でプロに仕事を丸投げしよう!!

✔︎ 依頼から納品まで、全てネットで完結! 誰にもばれずに、あなたの評価アップ!!

✔︎ 制作サンプルやレビューで比較が簡単! ネットなのに信頼ある人に頼める!!

 

※「検索」で依頼したい業務を入力して下さい