ピボット解除を複数シートで一気に行う

ピボット解除は、Power Queryの中でも人気の機能だと思います

そのピボット解除を、複数シートに対して一気に行いたくなるようなシーンは多いと思います

今回はカスタム関数を使用し、複数シートの内容を「一番てっとり早く」ピボット解除を行う方法を紹介します

ポイント

詳細エディタ

下の画像は、あるシートの内容をピボット解除した時の詳細エディタの内容です

各行が繋がっているのが分かりますでしょうか?(ソース➡#”2018_Sheet”➡変更された型・・・)

*********** ブログ管理人・ガッツの期間限定レッスン ***********

売りは技術でなく、経験です!

時間は30分から!あなたの都合のいい時間に、エクセルのお困りごとや悩みに対するヒントを提供します!!平日の昼間でも可です **************************************************************

後で、この仕組みをカスタム関数の作成の際に利用します

列➡一括データ取得

下の画像は複数のシートがあるファイルから一気に全てのシートを取得した場合のソースステップです

上の画像の「Data」列を指定すれば、ファイル内の全てのデータを取得できます

こちらも、後でこちらの仕組みを利用します

カスタム関数を作成

データの取得

元データはこちらのデータになります

各シートが年別になっており、それぞれのシートに4月~翌3月のデータがあります

この元データとは別のファイルからまずはデータを取得します

データ取得の際には、1年分のみを指定します

行の調整

下の画像のように1行目と最終行は調整が必要です

1行目は、ヘッダーとして繰り上げます

最終行については、行の削除を行います

一番下位の行を指定すれば最終行が削除されます

行の調整を行ったら、最終列のTOTAL列を削除して「ピボット解除」を行います

詳細エディタで関数作成

それでは、詳細エディターを開いてみましょう!

下の画像のように、各ステップが記載されています

画像に alt 属性が指定されていません。ファイル名: image.png

上の3行はシートによって可変になっていますので、こちらをカスタム関数にまとめます

作成したカスタム関数は「myTable」と名前を変えておきましょう!

画面下にある「完了」ボタンを押すと、下の画面のようにカスタム関数が表示されているはずです

他シートのデータを呼出し

表示されているカスタム関数の下あたりで、右クリックをしましょう!

そして、再び元データがあるファイルのデータを取得します

取得するのは全てのシートにします

記事の冒頭のポイントでも解説したように、「Data」の列には全てのシートのデータが詰まっています

こちらの列を活用してカスタム列を作成しますので、カスタム列・作成画面を開きます

そして、上の画像のように前章で作成したカスタム関数/myTableの中に「Data」列を指定します

すると上の画像のように「年毎にピボット解除されたデータ」が新たに作成されていますので、こちらを展開処理します

これで、全てのシートのデータがピボット解除されました

<まとめ>

今回は、複数シートの内容を一気にピボット解除する方法を解説しました

こちらは、カスタム関数を使用するのがポイントです

詳細エディタは使い慣れない方も多いと思いますが、今回の題材は慣れるのにいい題材だと思います

各ステップが繋がっていることを理解するのがとても大事です

ぜひ、実際に手を動かして慣れを作成しましょう!


にほんブログ村

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です