前回のパワークエリの記事は、複数ファイルの集計でしたが、今回は複数シートの集計についてです。

今回紹介する方法も、エクセル作業、経理業務でよくあるパターンかと思いますので、ぜひ作業の効率化にご活用ください。

(画像が小さくて見にくい場合はクリックすると拡大します)

 

月日ごと地域ごとなど、同じ形式の表の複数シートの例

もし日計表をエクセルで1日1シートで作られている場合、ひと月分で約30シートになります。月ごとだとしても年間で12シート。関数は難しいし、コピペで集計していくのはひと手間ですね。

本来なら、ひとつのシート、ひとつのリストにまとめるほうが、管理も集計も楽ですので、可能な限り最初の表作成時に、その後の利用も考慮して作成することです。

しかし、これまでのフォーマットを変えられない、データが多すぎてどうしてもシートを分ける必要がある等、様々な事情があって複数シートの集計作業が必要になる場合もあると思います。

そのようなデータのなかで、クエリで結合可能なのは、前回のファイル結合と同様に、項目や内容の揃ったデータです。

たとえば今回例として使用する以下のような売上・売掛管理表。1シートひと月分の売上を記録してあります。

これらのデータから、以下の状況が一目でわかるようなデータに変換してみましょう。

• • • 月ごとの売上
    • 任意の日付時点の売掛残
    • 入金予定

元のデータさえ揃っていれば、以上のような、資金繰りなど経営上重要な情報をすぐに取り出せます。

 

結合したいシートのあるファイルを指定する

 まず、前回同様、データ→データの取得と進みます。

前回はフォルダを指定しましたが、今回は　ファイルから→Excelブックから　と進み、結合したいシートのあるファイルを指定します。

 

 

すると、ナビゲーターが表示されるので、今指定したファイル名をクリック→【データの変換】と続きます。

 

クエリを操作して必要な情報を取り出す

クエリ画面が開きます。

左端のName列にシート名が並んでいるので、結合に使用したいものだけをフィルターで選びます。

左端Name列に、結合したいシート名だけが表示されましたら、次は、隣のDate列からシートの内容を開いていきます。

 

フィルターの位置にある両端に開く形の矢印をクリックすると、開きたい列を選択するように問われますが、ひとまずOKで進みましょう。

表示が変わったら、下へスクロールして、シート内のセルにデータのある行のすべてが縦に繋がっていることを確認します。

するといくつか余計なものがありますね。

 

 

元のデータでは最下行に合計がありましたが、周辺は空のセルでした。それがnullと表示されています。

また、各月シートの項目も入り込んでいますので、これら不要な行を除いて、集計に必要な数値のみを残していきます。

 

が、その前に、「一行目をヘッダーとして使用」をクリックして、項目名を変更しましょう。

 

そして、さきほどのnullや行途中の項目名などは、フィルターでチェックを外すことで削除可能です。

たとえば「売上本体」のフィルター(右隣の▼)で「null」「売上本体」からチェックを外すと、全ての列から該当の行が消えるはずです。

余計な行がなくなりましたら、次は列を集計に必要なデータに合わせていきます。

 

ピボット集計に便利なデータにクエリで加工

Excelでも関数などを使用して可能な作業なのですが、今回はクエリで操作してみましょう。難しいことはやりませんのでご安心ください！

まず、お気づきでしょうか、「一行目をヘッダーとして使用」とした際に、左端の列(シート名)が勝手に日付形式に変わってしまいました。エクセルが作業年を判断して勝手に表示されてしまいます。便利なときもありますが、間違っていることもあり、この列は使用しないことにします。

修正削除せず、ただ無視することにして、必要なデータのみ抽出した列を追加していきます。この場合の必要なデータとは、シート名、売上月です。

ちなみに、項目の左側(図の黄色の丸印)がその列の表示形式を示しています。

月の列を追加する手順は画像の通り、

1. 1. 1. 1. 該当の列をクリック
         2. メニューから「列の追加」
         3. 日付
         4. 月
         5. 月

すると、一番右端に列が追加されているはずですので、判りやすい場所にドラッグで移動しておきましょう。

また、項目名も変更しておきます。

エクセルでも表示形式や関数などで、このような日付から年・月・日それぞれの表示・抽出は可能ですが、関数が難しい方にもこの方法なら簡単にできるのではないでしょうか。また、クエリで追加しておくことで、クエリ→ピボット集計の流れがスムーズになります。

さて、同様に、入金日についても、年月を抽出して、ピボットで集計しやすくしておきます。

 

一旦ここで「閉じて読み込む」をして、クエリを終えます。

 

クエリで加工して戻るとテーブル形式になっています。

このテーブルを元に、ピボットで集計しましょう。

ピボットの基本的な使い方については、以前の記事も参考にしてみてくださいね。

ピボットでの集計例

たとえば、2022年12月末時点の売掛残を求めるには、図のようにフィールドを配置します。今回のデータは入金月と売上月が紛らわしいので、そこに注意もです。

例) 2022年12月末時点の売掛残

 

 

 

 

 

例)2022年1月の入金予定は…

と思ったら、入金予定がシリアル値になっています。

クエリへ戻って確認してみましょう。

 

クエリの編集⇔エクセルで効率化！

クエリと接続しているテーブルも入金予定の列がシリアル値になっています。

 

 

エクセルで表示形式を変更してもよいですが、今後のことも考えて、元のクエリを修正しましょう。

 

エクセルから接続しているクエリには「編集」で戻ることができます。

 

クエリを確認すると、シート名は自動で日付に変更されましたが、入金予定の列の表示形式が日付になっていません。

 

 

 

この列から「年」や「月」は正しく抽出できたので、うっかりしていました。

日付に変更して、再度読み込み、Excelへ戻ります。

 

ピポットを更新して、フィールドを配置して「2022年1月の入金予定」を表示することができました(日付をシンプルな表示形式に設定しています)。

前回のフォルダ結合の場合と同様に、クエリは指定したファイルと接続(リンク)しています。

今回のデータの修正があった場合、元ファイルを修正し、クエリを更新するだけで、正しい集計が可能です。シートが追加されたら、クエリを編集すればよいわけです。

---

お疲れさまでした。この記事の内容をすべて覚えなくても、クエリでこういうことが可能であるということだけでも頭の片隅においておけば大丈夫です。具体的な方法は都度調べることも可能ですし、様々に応用できるかと思います。

ですが、今回例に使用した程度の行数であれば、最初から12シートを1つのシートにまとめて入力しておく方がお勧めです。つまり、最初に表を作成する際に、クエリで出力されるテーブルの形式にして、年間分を1シートで記録していけば、クエリを介する必要がありません。

Excelは最初の表作成が重要です。新たに表を作る際はもう一度、基礎的な注意点などもご確認いただくとよいかと思います。以前、関連事項を書いた記事もありますので、よろしければご覧ください。

今回の記事が何かしらお役に立てましたら幸いです！