Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
14
Help us understand the problem. What is going on with this article?
@y_ohira

Power Queryで複数のExcelブックの共通しない名称のシートを読み込む

More than 1 year has passed since last update.

Power Queryとは

モダンExcelやPower BIに欠かせないETLツールである「Power Query」、Excel VBAのコードを覚えることなく、VBAで出来るようなことや出来ないようなこともがポイント&クリックで可能となるツールである。

この記事でやること

  • 複数のExcelブックが存在する。
  • 各Excelブックに含まれるシートの名称は統一されていない。
  • 各Excelブックに含まれるシートの数も統一されていない。
  • 全ブックおよび全シートのデータを1つのテーブルに統合する
  • 操作にコードを使わない(重要)

ビジネス的な与件

何かのイベントの参加者だとか、会の登録だとかをExcelで集約すると考えてほしい。
そもそもExcel使うなPowerApps使えとかいう指摘はご尤もだが、話が進まないのでここでは無視する

こんな感じのExcelと考えてほしい。IDと氏名の列があり、人数に応じて参加者が追加されていく。

シートを増やすな、シート名を変えるなといくら連絡をしたとしても、守らない人は絶対にいる
悲しいが、人はルールを守れない生き物なのである

以下のような感じでシート名が一部の人に編集されていたり、増やされているとしよう。Excelを開いたとたん、対処法を持たない事務局は絶望するであろう。

と、ばらばらのシート名、シート数であっても、すべてをノーコーディングで一つのテーブルとする手順について解説する。

手順1/2 複数のExcelブック内の同一名称のシートを統合する。

手順の前半は、「同一名称のシートを統合する」のと同様の手順となる。
まず、ExcelもしくはPower BIでPower Queryを起動し、「フォルダー」からデータを取得する。

※画面はフォルダだが、SharePointフォルダでも可能。
※ただしExcelのPower Queryだと、UIにSharePointフォルダが無いかもしれない。Power BI Desktopをダウンロードすべし。

パスを指定して読み込むと、該当のフォルダに含まれるファイルの一覧が表示される。

いずれかのブックのシートを読み込みサンプルをするか聞かれるので、任意のファイルを選択する。
(選択しなくても、勝手にいずれかのブックとシートが選択される)

ここでサンプルとして指定したシートの名称が存在しないブックがフォルダに入っていると、以下のエラーが発生する。
※上記の画面で「エラーのあるファイルをスキップする」にチェックを入れているとエラーが発生せず、サンプルシートと同一名称のシートがすべて結合される。
07.PNG

手順2/2 シート名が異なるシートも読み込むように設定する。

上記1/2の手順を完了すると、「サンプルクエリ」というグループと、4つのクエリ・関数・パラメータが自動的に生成されている。
複数のブックから同一名称のシートを読み込むために必要なものである。

その中で、「〇〇からサンプルファイルを変換する」というクエリを開く。
※〇〇には、参照元として指定したフォルダ名称などが通常入る。

このクエリは、サンプルファイルからある名称のシートだけを開くように指示しているものであり、このクエリのステップをシート名を問わずに開くように変更することで、最終的に読み込まれるクエリも連動して変更される。

右側の「ステップ」の「ナビゲーション」は、特定の名称のシートのみを開くよう指示しているものなので、「ナビゲーション」以降のステップを、右クリックして「最後まで削除」する。

続けて、「Data」列を展開し、すべてのシートを残す…のだが、その前に邪魔になるほかの列を右クリックして削除する。

さらに「Data」列の「展開」ボタン(90度曲がった矢印が二つくっついているボタン。某氏によると「調子に乗っちゃって」ボタンだそう。)を押し、OKを押す。
(元の列名をプレフィックス…は、つけておいてもよいが、列名が長すぎてうっとおしくなるので、外したほうが良い。)

シートの中身が展開されるが、列名が1行目のデータとして入ってしまっているので、「1行目をヘッダーとして使用」をクリックする。

すると、なんと言うことでしょう…。読み込み後のクエリが異なるシート名を含めて、すべてのExcelブックとシートを読み込んでいるではありませんか!
15_1.PNG

複数のシートを含むテーブルを読み込むと、上の画像のようにエラーが生じたり、列名がデータに入ってしまったりするが、適当にエラーを消したりフィルターかけて取り除けばOK。

18.PNG

Excelをやめられない事務局に救いを

Excelを親の仇ほど憎んでいる人はたくさんいると思うが、実務上、まだまだ事務局作業にExcelを使わざるを得ないケースもある

Microsoft Flowを組み合わせてSharePointやオンプレミスに自動的に格納したり、格納されたデータをPower Queryで読み取ったりといった形で自動化を進めることで、そのような人たちが救われることを願う。

14
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
y_ohira
Data Scientist / Digital Transformation / Human Development

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
14
Help us understand the problem. What is going on with this article?