Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@_ina_

PowerQuery でExcel名が変わる/シート名が変わる/列名が変わる場合の読込や置換、累計について

Last updated at Posted at 2023-10-20

前書き

「マクロは属人化するからこれからはローコード/ノーコードの時代、
PowerQueryにMicrosoft Power AutomateやMicrosoft Power Appsだ!」なんていうもののマクロより情報が散らばるからもっとひどいんじゃないかと思う今日この頃

良し悪しは別にしてExcel関数やマクロを使わずPowerQueryやらを使えと言われたら、それはそれで縛りプレイとしてやる必要があるんですということで今回はPowerQueryの話
実際に作るときにQiitaにはお世話になったのでQiitaで投稿

前提

結合前1, 結合前2は誰かが作って毎月送り込んでくるもので、Excel自体は加工しないものとします。
月が変わると今は10月となっている部分が変わるとします。データの位置などは変わりません。
加工しないということで、PowerQueryを組み込むためのExcelが必要になるため、空の結合用.xlsxを用意しました。
image.png
最終的な形は以下の通りとします。
結合前1をベースに、結合前2に書かれた情報を集計してくっつけるようなイメージです。
日付は不要とします。
image.png

Excel直接指定(基本形)

Excelを直接指定する場合はデータタブのファイルからを選び、Excelブックからを選びます。
image.png
結合前1を選択
image.png
10月シートに情報があるので、10月シートを選択して読み込みを選びます。
image.png
すると10月というシートができて、データが読み込まれます。
image.png

結合2も同様に読み込むと、
image.png
日付は整数で出ていますが、今回は関係ないのでスルー

読み込んだ2シートを結合するにはクエリの結合を行います。データタブのデータの取得→クエリの結合→マージを選び、
image.png
IDで結合することから、10月のIDと10月売上のIDを選んでOKとします。
image.png

すると、PowerQueryエディターで何か出てくるので、
image.png
10月売上の右のやれやれみたいな記号を選んでΣ10月金額の合計のみにチェックを入れてOKを押すと、
image.png
いい感じになってきます。
image.png
名前が変なので新しい列で右クリック、名前の変更で
image.png
金額にします
image.png

ホームの閉じて読み込むをクリックすると
image.png
マージ1というシートができてデータが出てきます。
image.png
右の目標と比べてみると、確かに内容は完全に一致しています。めでたしめでたし

めでたくない

10月はこれでいいでしょうが、来月以降に変わる雰囲気を醸し出している要素がたくさんあります。Excelの名前も10月になってるし、シート名も10月っぽいし、10月金額とか書いてある列もあります。もしこれで来月全て11月に変わっていたらどうなるでしょうか
全ての情報を11月に変えてデータタブのすべて更新を押すと
image.png
当然エラーが出ます
image.png
10月のExcelを指定したので、名前が11月になれば読み込めません

Excel名が変わる場合の対処

Excel名が毎回変わる場合はどうしたらよいかをまず解説
当然毎月名前を変えて送り付けてくるバカを殴るのが一番なのですが、どうにもならない場合は以下のようにします。
image.png
今までは1つのフォルダにExcelが3つ並んでいましたが、フォルダの中に、毎月名前の変わるExcelを入れる、名前の変わらないフォルダを作ります。

そしてデータタブからデータの取得を選び、ファイルから、Excelブックからとしていたのを、フォルダーからに切り替えます。
image.png
結合前1のExcelを選んでいた代わりに結合前1の入ったフォルダを選択します
image.png
すると何か出てきますが、結合のボタンを押して、データの結合と変換とすると、
image.png
さっきも出てきたものが出てくるので、11月を選ぶ
image.png
何のExcelから読みこんだのかという情報は今回はいらないので削除
image.png
閉じて読み込むをクリックするとちゃんと読めます。
image.png
これを結合用2でもやっていくと完成です。

シート名が変わるときの対処

Excel名が変わる場合にはフォルダを作ってExcelを直接指定しないことで解決しました。
しかし、11月シートは直接指定していました。じゃあシート名も変わる場合はどうするか。

同様にデータタブのデータの取得、ファイルから、フォルダーからで結合前1を選んで、データの結合と変換を選んで出てきた画面で、シート名ではなく、「パラメーター」と書かれたものを選んでOKとします。
image.png
何か不思議な文字がたくさん書かれたPowerQueryエディターが開きますが、Dataの横のマークを選んで、OKとすると
image.png
何か多いですが、ちゃんと出てきます。
image.png
いらない列を消して、
image.png
ホームタブの1行目をヘッダーとして使用を押して保存して読み込むと、何か手数が増えましたが、無事に読み込めます。
image.png
結合前2も同様です。

Tips

シートが複数あり、何シート目に情報があるかがわかっている場合は、余計な行を消すとか、
image.png
指定のColumnだけを選んでOKなどの方法があります。まあどちらも大した話ではありません。
image.png

列名が変わる場合の対処

こんな感じで列の名前も変わる場合にどうするか。
image.png
列名が変わると結合前2の型変換とマージで
image.png
image.png
「1月金額」と書かれているのが問題になります。
image.png
なお、後ろの「結合前.1月金額 の合計」は累計を表示する行の名前を書いているだけなので適当に変えてもOKです。
image.png

さて、結合前1のフォルダを読み込んだ後に
image.png
結合前2の1月金額という名前を変えればいいのではと思いもしますが、直接変えると
image.png
1月金額という毎月変わる名前が式に入っています。
Table.AggregateTableColumnの「1月金額」をFirst.Nとかで何とかならないか色々試してみたののの、うまくいかなかったので別方向から解決させた結果が以下の通り。

結合前2をなんやかんやで読み込んで、
image.png
「1行目をヘッダーとして使用」をすると、変更された型1も出てきて式に1月金額が出てくるので、
image.png
これを消す。
image.png
そして、列名を指定せずに名前を変える方法を使います。
image.png
〇月金額を普通改名すると↓になるところを
image.png
↓に書き換えます。

= Table.RenameColumns(昇格されたヘッダー数,{{Table.ColumnNames(昇格されたヘッダー数){3}, "金額"}})

4列目(PowerQuery的には3列目)の名前を固定の「金額」という名前に変えました。

そして先ほど消した金額列の型変換を行います。これをやらないと累計できません。
image.png

この後は普通にマージしてOKです。
image.png
これがやりたくてこの記事にたどり着いたんだろ?というレベルのお話。

結果
image.png

Tips 展開するだけの場合

結合前1のフォルダを読み込むところまで進めておきます。
image.png
ただし、後のために結合前2のIDはID2に製作物は製作物2に改名しておきます。
image.png
結合して、展開すると以下の通りですが、
image.png
この式を

=Table.ExpandTableColumn(ソース, "結合前2", List.Union(List.Transform(ソース[結合前2],each Table.ColumnNames(_))))

に書き換えると列名に依存せず展開できます。
image.png
この時に、IDと製作物の名前を変えておかないと重複エラーになります。
image.png

まとめ

名前が変わる場合の基本はその上位(Excel名ならフォルダ名、シート名ならパラメーター)を指定する
列名が変わる場合は列番号から固定列名に変更する

後書き

で、これならローコードだからマクロと違ってユーザーだけでなんとかなるんですよね?

0
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?