タグ別アーカイブ: ピボット解除

(続編)2つの表にてどの行が追加、削除されている? ⇒どの列が変更になっている?

前回の続きで、パワークエリを使用してだけでなくについても、の変更もあわせて変更箇所を特定できるようにしたいと思います

ポイント

2つの表を結合するのは前回と一緒です

今回はピボット解除を行い、複合キーを作ったうえで結合を行います

使うデータ

Before、Afterの2つの表を用意するのですが、キー(りんご、みかん・・)を行単位で作成し、列は月単位で作成します

最後は2つの表でどこが変わったかを明確にできるようにクエリを作成します

ピボット解除&複合キー作成

BefoerとAfter、それぞれのクエリはピボット解除を行い、縦横並びを縦縦並びに変えます

その後、「列のマージ」を使用して、複合キーを作成します

この複合キーを使用して、BeforeとAfterの2つの表を結合(マージ)します

マージ処理

ポイントでも前述したように、2つの表を複合キーを使用してマージ処理します

マージ種類は通常でいけば、完全結合がよいです

「完全外部」の結合であればAfterの表にて追加・削除されたものが全て網羅できます

但し、こちらは用途にあわせて6つの結合種類を使いわければよいかと思います

各種調整

判定列の追加

マージ後は、条件列を使用して〇×を判定する列を追加することができます

複合キーの分解

マージ後は複合キーを分解して必要な情報だけ抜きだすこともできます

複合キーの分解は「抽出」の「区切り記号の後のテキスト」を使用し、複合キーから「月」を抜き出します

そうすると、BeforeとAfterの比較が行いやすくなります

<まとめ>

今回はマージを更に有効活用して、行だけでなく、変更があった列と値を特定できるようにしました

ポイントはピボット解除と複合キーの作成により、2つの表を比較しやすい形で結合することです

ぜひ有効活用してみてください

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

【パワークエリ・チャレンジ】セル結合が行列にある表をテーブル形式に変換

読者の皆さんも見やすくするためにセル結合を組み合わせた、下の画像のような表をみたことがありますよね!こちらの表をPower Queryの基礎技術を組み合わせてテーブル形式にしましょう!

この手の表は見た目がよさそうな気がしますが、データを追加したり、編集するのにとても不便です

「この表はもう加工・編集のしようがない・・」

そんなことはありません、あくまで「ピボット解除、行列入れ替え」「フィル」「列のマージ」「列の分割」などの技術の組み合わせで簡単にテーブル形式に直せます

今回は行列ともにセル結合がある表を扱いますが、行方向のみの場合は過去にも記事を書いてますので、ぜひそちらもご参照ください ⇒過去の記事

今回のポイントは列のマージと列の分割の組み合わせです!

行列を入れ替えるのと、フィルで空欄を埋めるのは行方向のみセル結合がある場合と一緒です!

小計や合計の行列を消去

解説はPower Queryエディタの状態からはじめさせて頂きます

小計の行や合計の列は必要ないので、フィルターしたり列の削除を行い、消去してしまいましょう

下方向のフィル

一つ目のセル結合はフィルで埋めます

フィルは変換タブから行います

下の方向でフィルを行えば、下の画像のようにnull/空欄が埋まります

列の結合

後でデータを扱いやすくするために、部門と四半期の列は「列のマージ」により1列にまとめてしまいます

列のマージは変換タブで行います

列のマージ時には「-」などの記号により、で分割しやすくしやすくしておくのがポイントです

列のマージを行ったことで、表がシンプルに整理されてきました

行列の入れ替え

この後、行列の入れ替えを行いますが、1つ準備が必要です

見出しをデータに降ろす必要があります

変換タブにで「ヘッダーを1行目として使用」をクリックします

下の画像が見出しをデータに降ろした状態です

ここから「関東・・・の行」も含めた形で行列の入れ替えを行います

行列を入れ替えると、再びフィルでセル結合を埋めることができます

条件列&フィル

ここからフィルで一気に空欄を埋めたいところですが、よくみるとnullでなく「列・・・」で埋まってしまっています

ここは条件列を使用して、列から始まる箇所をnullに変換しておきます

これでフィルで空欄を埋めることができるようになりました

フィルで空欄を埋めると、いよいよ最終形が見えてきます

ここからは①行をヘッダーに昇格②ピポット解除を行います

そして最後に列をマージした列を再度分割します

ピボット解除

ピボット解除を行う前に1行目をヘッダーにします

こちらも前回同様に変換タブから行います

これでピボット解除を行う体制になりました

ピボット解除は左2列を軸にして行います

「その他のピボット解除」を行うと最終形の一歩手前です

ここから、前述の通り、列の分割を行います

列の分割は、列のマージを行ったときに「列の間に入れた記号」を指定して行います

列の分割を行えば、これで最終形となります

後は列名などを調整しましょう!

<まとめ>

今回は長文にて行列双方向にセル結合がある複雑な表をテーブル形式にしました

長文ではありますが、使用している技術は基本的な内容ばかりです

ただ、一つ抜けるとすべてが狂ってしまうので、その点だけは注意頂きたいと思います

今回、記事を作成してみて感じたのは「見出し」が無い表は結局、あまり見栄えがよくはなりません

その点は日常的に意識していきたいものです

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

【パワークエリ・チャレンジ】パワークエリを使用して月間スケジュール表を作成する

 今回は、開始時間・終了時間・担当者を可変で指定できる月間スケジュール表(記入用)を、Power QueryM言語の技術をフル活用して作成します

4月1日であれば、13時から15時まで1時間刻みで行が自動で用意されます

担当者も可変にできます

一番のポイントは「開始」「終了」時間の判定列を、日付(1~31)と時刻(0:00~24:00)を網羅した表に作成することです

しかも判定用の列は2列用意します

判定列を作るためには、複数のクエリを用意し、クエリ内にキー列を作成しておくこともとても重要です

キー列:yyyy/MM/dd hh:mm

内容的に大きめの内容なので、今回はポイントの羅列で失礼させて頂きますので予めご了承ください

Power Queryでここまでできるのか?という点を感じて頂けたら幸いです

今回の内容の参考記事についても事前に紹介させて頂きます

複数データの組み合わせ/クエリのマージ~中級編1回目~

見出しがセル結合により2行になってしまっている表をデータ活用1~上級編2回目~

IF式を組み合わせて列作成~上級編10回目

M言語に慣れる_4回目~特殊テンプレート作成~

【パワークエリ・チャレンジ】1列のデータから複数列のテーブルデータを作成する

変換した列の追加~中級編10回目~

ポイント

各種データの用意

今回、想定する作業は以下のスケジュール表から、必要な時間だけ記載され、担当者も記載された完成スケジュール表を作成することです

スケジュール表

下の画像が完成スケジュール表です

完成スケジュール表

実際にPower Queryのクエリを作成する前に、3つの表(日付データ、時刻データ、担当者)を用意します

日付データと時刻データについては、あとで組み合わせて、次のようなクエリ(日付時刻クエリ)を作成します

こちらの結合済みの列は後でスケジュール表とマージして「開始時間」「終了時間」を紐づけるためのキー:yyyy/MM/dd hh:mmになります

日付データ

4月のスケジュールであれば、1日から30日までを用意します

*ホームタブの連続データの作成を活用するとデータの用意が早いです

時刻データ

どの月かは別にして0:00~24:00までを用意しておきます

こちらは固定になります

担当者

こちらも用意します

日付時刻クエリとスケジュール表クエリのマージ

前述のように日付と時刻を組み合わせて作成した日付時刻クエリはスケジュール表クエリとマージして、開始時刻と終了時刻を紐づけます

マージするためのキーはyyyy/MM/dd hh:mmになります

スケジュール表をマージするためにも、事前にスケジュール表はピボット解除を行っておきます

そして日付と時刻の列はマージして「yyyy/MM/dd hh:mm」のキー列を作成しておきます

フィル機能の活用

キー列をもとにして、2つのクエリをマージするとキー列に「開始時間」「終了時間」を紐づけることができます

但し、開始時間と終了時間を判定するには、判定列が1列だけでは不十分です

1列だけだと、終了⇒開始(上の画像だと16時以降)までの時間帯が判定できないからです

フィル機能を使って、下に埋めた列を作成します

2列あれば、残すべき行を特定できます

IF式

判定列を利用して、残すべき行を判定します

判定2が開始の場合には行を残します。判定1と2が終了の場合にも残します

*1の場合は行を残す、0の場合は行を削除する

逆にそれ以外は行を残しません

担当者の紐づけ

担当者はカスタム列を作成してリストで紐づけを行います

*担当者はクエリ名です

リストで紐づけを行い、展開した状態が下の画像の状態です

ピボット処理

担当者の列の内容は、下の画像のように見出しにします

この表の値欄は空欄になるので、ダミーの列を事前に追加しておくのも大きなポイントです

その後にピボット処理を行います

最後に

以上が今回のポイントなのですが、作成していくと、ところどころで並び順が変わってしまうと思います

その際には、インデックス列を追加して調整を図ったください

今回は長文に最後までお付き合い頂き誠にありがとうございました

参考までに今回のサンプルファイルを添付します

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

ピボット解除を複数列を軸にして実行する

パワークエリのピボット解除はとても便利ですね

 1つ列を軸として選択 ⇒ その他の列のピボット解除、これだけでデータの縦横/↓→並びを縦縦/↓↓に変更できます

でも、複数列を軸にしてピボット解除をする場合にはどうするんだろう?

実はこちらについてもとても簡単にできます

ピボット解除の前に軸にする列を指定するだけです

今回の内容

ピボット解除を行うデータは下の画像です

上の画像の黄色い箇所、カテゴリー地区を軸にして「4月~6月」を横並びから縦並びにかえます

複数列を軸にしたピボット解除

解説はPower Queryエディターからはじめさせて頂きます

ピボット解除を行う前に、軸にする列を下の画像のように選択しておいてください

複数列を同時に選択する場合には、Ctrlキーを押しながら選択してください

このまま「その他の列のピボット解除」をクリックしましょう

そうすれば、選択した列以外の並びが変わります

<まとめ>

ピボット解除はとても便利です

データの並べ替えによるデータのクリーニングはとても重要な作業です。ミスが起こっては困ります

このピボット解除を適切に実行すれば、データクリーニングの質は格段に向上します

今回の内容はぜひ有効活用して頂きたいと思います


にほんブログ村

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

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

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

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

ポイント

詳細エディタ

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

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

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

列➡一括データ取得

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

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

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

カスタム関数を作成

データの取得

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

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

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

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

行の調整

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

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

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

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

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

詳細エディタで関数作成

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

<まとめ>

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

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

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

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

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


にほんブログ村

列名をダイナミックにしたピボット解除を行う

ピボット解除はパワークエリの協力な武器です。縦横/↓→並びを縦縦/↓↓並びに変えてくれます

但し、新たに列を加えた場合には、並び替えの対象列が狂ってしまいます

 上のGIF画像では「評価」という列を加えると、当然、「評価」列は軸として固定されずに並べ替えの対象となってしまいます

本来であれば、下の画像のようにしたいところでした

他の言い方に変えると、列の追加を行ったとしても「日付」の列のみ、並び替えを行って欲しいのです

これにはいい方法があります

ポイント

ピボット解除のM関数

下の画像は「その他の列のピボット解除」を行ったステップの数式バーです

Table.UnpivotOtherColumnsというM関数が使用されており、カテゴリー列*以外の列が並び替えられているのがよく分かります 

逆に言えば「並び替えない列」を上のM関数内でリストにて指定してしまえば、それ以外の列が並び替えの対象となります

エラーを敢えて発生させる

今回は、日付のみを並び替えの対象とします

エラーと絡めて言うと、日付形式にした場合にエラーになるものは並び替えの対象としないようにすればよいのです

更に前述のM関数との絡みで言うと、M関数内の「ピボット解除を行わない列名」を「日付形式にした場合にエラーになる列名」のみにしてしまえばいいのです

並び替えない列名リストの作成

まずは、ピボット解除を行ったクエリを複製します

そして、ソース以外のステップを削除した後にM関数/Table.ColumnNamesを使用して「列名」をリスト化します

そして、テーブル化した後に、以下の画像のようにカスタム列の作成画面で列を複製します

ここから、ポイントで前述した敢えてエラーを発生させる話になります

カスタム列を日付形式に変えます

すると、日付以外はエラーになります

次に、ホームタブで「エラーの保持」を行います

すると、日付以外の列名だけが残ります

ここから2つ作業を行います①カスタム列の削除、②リスト化

リスト化は変換タブの「リストに変換」で行います

リストに変換したら、リスト名を覚えやすいようにつけておきます

今回は列名リストとしました

M関数にリストを組み込み

並び替えない列名のリストができたので、M関数/Table.UnpivotOtherColumnsの中に組み入れます

こうしておけば、列を追加しても日付のみが並び替えされます

<まとめ>

今回は、ピボット解除において列を追加した場合でも日付のみが並び替えられるようにしました

ポイントは、並び替えを行わない列名のリストを作成して、ピボット解除を行うM関数の中に組み入れることです

リストを作成する際には、敢えてエラーを発生させるのもポイントです

でも、日付以外の月などの場合にはどうするのか?

その場合には、条件列を使います

上の画像の場合には、月で終わらないものはnullになります

そして、nullだけフィルタで残す、という風にすれば対応できます


にほんブログ村

【豆知識】ピポット解除時の空欄の扱い

 パワークエリのピボット解除は便利ですよね。一括で縦横並びを縦縦並びに変えられます。ところが、元データに空欄が混じると2つ問題が出てきます。1つ目は空欄を含むデータが非表示になることです。

 こちらは「値置換」で対応できます。ところが元データに列を追加した時、追加した「値置換」が追加列に対して行われなくなります。これが2つ目の問題です。

 原因は値置換を行う時に、対象列名が記録されてしまっていて、追加列に対しては「値置換」が行われないのです。こちらはコードを直接置き換えることで対応できます。

今回の課題は、実際の業務ではあまり遭遇する機会が少ないかもしれませんが、Power Queryの「ハードコード」という特性を掴むにはとてもいい課題です

ぜひ、手を動かしてPower Queryの特性を掴んでみてください!

では、上記の①/1つ目の問題から②/2つ目の問題の順番で解説を行って行きます!

①空欄の置換

下の図は、上の表がピボット解除前の元データです

下の表は、元のデータをピボット解除したデータです

ピボット解除時は元データに空欄が含まれていると、空欄のデータは出力されません

例えば、図の下の表では「かつ丼の2020/01/02」の空欄データは抜けてしまっています

空欄データを「0」で表示したい時には、値置換で対応します

ピボット解除を行うクエリをPower Queryエディタ(以降、エディタ)で開くと、上の画像の図のように空欄は「null」で表示されます

こちらの「null」を「0」で置き換えます

置換処理を行う前は、Ctrl+Aで全データを選択しておいてください

値置換を行えば、下の画像のように空欄が0で表示されます

②コード変更

①で置換を行いましたが、次の画像のように元データに列を追加すると置換に関する問題が発生します

下の画像のように、追加された2020/01/08のかつ丼の空欄が0で反映されません

この理由は、Power Queryの特性であるハードコードにあります

ハードコードとは、エディタ内のステップに処理の内容がとともに記録されることです

では、0への置換を行ったクエリの中味をエディタで見てみます

上の画像は、空欄から0への置換を行ったコードです

このコードを見ると、0への置換処理は「2020/01/07」までの列に対して行うことが記録されています

 ですので、ハードコードされていない列、上記のコードに記載されていない列が追加されると「置換」は行われません

この状況をどのように改善するかというと、コード自体の置換を行います

まずは、列名が記載されたコードは削除します

その後削除したコードを、下の図のようにテーブル内の全ての「列名」を表示するTable.ColumnNamesで置き換えます

そうすれば、いくら列を追加しても置換が行われるようになります

今回の解説は以上になります

②のコード変更については、概要だけを解説しました

Power Queryの言語である「M言語」については、別途、シリーズで記事を書きますので、そちらでは詳細な解説を行いたいと思います

記事を最後まで読んで下さり、誠にありがとうございました

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

セル結合により見出しが2行の表をデータ活用2~上級編3回目~

こんにちは、Excellent仕事術ガッツ鶴岡です

セル結合された表を有効活用できようにするため、前回行列の入れ替えとフィル機能を組み合わせて、データ変換を行いました

図にすると、下の図のようなイメージになります

セル結合で欠落した箇所を、上の文字で埋めた合わせた形になっています

今回は、更にデータの並びを縦横並びから縦縦並びに変えるピボット解除を組み合わせます

そして、セル結合された「見出しが不完全なデータ」を下のGIFのように見出しが整った形でエクセルシートに読込めるようにします

つまり、欠落したデータを埋めた上で、縦(1Q/4月、5月・)横(商品A、商品B・)の並びを下の図のように縦縦並びにして、値を1列に集約します

それではまず、前回の作業がどこで終了していたかを確認しましょう!

上の画像を確認してください

左から2列のColumn1は、元はセル結合したデータでした

ですので、Power Query Editorに読込んだ際に1Q、列1・・・と不規則なデータになってしまっていました

前回は、この不規則なデータを画像・一番左の列の形にしました

Column1については、前回で既に削除してあります

では、ここから今回の詳細な解説をはじめます

1.1行目をヘッダーとして使用

前回は、行列を入れ替えるために、あえてヘッダーの見出し行をデータの1行目に変換しておきました

1番左の列の見出しは前回、手動で四半期と入力しています

今回は逆に、データの1行目をヘッダーに変換して見出し行を整えます

ちなみに、上の画像の一番左の列のデータ1行目は前回、条件列を出力した時に「Qで終わらないものは空白」で出力した時に空白になってしまっています

では、変換タブに移動し「 1行目をヘッダーとして使用 」をクリックしましょう!

はい、これで見出しが整ったはずなのですがここで問題が1つあります

データの1行目を見出しに持ってきた時に、下の画面のように2つの自動変換が起こっています

一番左の列の見出しが、前述のように元は空白だったので四半期からColumn1に変わってしまっています

そして、月の列のデータの中味も日付形式に変わってしまっています

適用ステップの欄を見てみると、型の変更が追加されているがよくわかります

こちらの2点は、以下の様に変更を行いましょう!

①Column1の名称→四半期に変更

直接、列の名称を変更しましょう

②日付形式→文字型式に変換

こちらは、変更が行われたステップを削除するか、型式の変更を行いましょう

ちなみに、型式の変更で行う場合には、こちらから行えます

2.ピボット解除

それでは前述の縦横並びを、縦縦並びにかえます

四半期と月の列は縦縦なので、こちらの2つの列をカーソルで選択しておき、右クリックしてください

ちなみに複数の列を選択する時には、Shiftキーを押したままで列の選択を行ってください

上の画像のように、右クリックをすると「その他の列のピボット解除」が選択できますので、こちらをクリックしてください

ピボット解除

これで、縦横並びが縦縦並びに変わりました

3.エクセルシートへの読み込み

本題に入る前に、商品の列の名称が「属性」となっているので「商品」に変えておきましょう

これで、エクセルシートに読込む準備が整いましたが、読込先はテーブルにしても、ピボットテーブルにしてもOKです

テーブルで読み込んで、後でピボットテーブルにする方法もあります

では、解説としては一旦はテーブルで読込ます

そして、こちらのデータをピボットテーブルに変えます

これで、元々はセル結合されていて編集・加工が困難だったデータがピボットテーブルにて様々な形式で集計できるようなりました

<まとめ>

2回にわたり、セル結合を含むデータを有効活用する方法を解説しました

1回目では主に以下、3つのテクニックを解説しました

ⅰ)ヘッダーを1行目として使用

ⅱ)行列入れ替え

ⅲ)フィル

2回目の今回は、ピボット解除により「四半期と月が縦」に、そして、「商品が横に並んでいたデータ」を縦と縦の並びに変えました

データが縦と縦に並んだ、見出しが整ったデータすることにより、ピボットテーブルで有効にデータ活用ができる形式になりました

今回の2回の解説は「行列の入れ替え」「ピポット解除」どちらも行っています

この2つの違いを明確に区別して処理が行えるようになると、パワークエリでデータクレンジングする力が飛躍的に向上します

ぜひ、実際のデータで2回の内容にガッツで取り組んでみてください

では今回の解説は以上です

長文に最後までお付き合い頂き、誠にありがとうございました

次回は四則演算から四捨五入まで、様々な集計方法を解説します

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

Power Queryって何?~ピボット解除(縦横並び替え)と入れ替えの違い~

Power Queryの魅力の一つに、 行列の縦横の並び替えを自動で変更できること
ピボット解除) が挙げられます

 業務でデータ成型(データクレンジング)を行う人は絶対に覚えておくべき技術です

もし、手動で縦横の並びを変えるとなると、相当な手間です

ここで疑問を持つ人がいるかもしれません

「縦横の並び替え(ピボット解除)」と「行列の入れ替え」はどう違うのか?

この2つは似て異なるものです

この2つの処理が、具体的にどう違うのかを明確に理解した上で、ピボット解除を使いこなせるようになると、Power Queryをより実践的に使えるようになります

2つの処理の違いを実際の例で確かめてみましょう!

とちらもこの表からスタートします

1.ピボット解除

①テーブルまたは範囲からデータ取得

上の表をまず、「テーブルまたは範囲から」を指定し、エディター画面に呼び出します

②ピボット解除

エディター画面で一番左の列にカーソルを置き、右クリックします

すると、「その他の列のピボット解除」が選択できるようになっています

こちらをクリックしましょう!

こちらをクリックすると、一番左の列を軸にして、他の列を回転させるということになります

2.行列入れ替え

今度は、「行列の入れ替え」を行ってみましょう

明らかに、1.のピボット解除とは結果が違っていますよね

では、1.ピボット解除と2.行列の入れ替えをエクセルシート上に読込んで比較してみましょう

ピボット解除は元のデータを縦(ABC)横(123)並びから、縦(AAA)縦(123)の並びに変えています

一方、行列入れ替えは 元のデータを縦(ABC)横(123) 並びから、横(ABC)縦(123)⇒横にずれて縦(456)の並びに変えています

ピボット解除と行列の入れ替え、似ているようで異なるものです

2つの処理から出たデータを並べると、違いが明確になったと思います

今回は以上です

最後まで記事を読んで下さり、誠にありがとうございます

Power Query講座も連載していますので、こちらも参考になれば幸いです

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

ピボット解除/縦横並びの変換処理~中級編6回目

こんにちは、Excellent仕事術ガッツ鶴岡です

前回までは複数のクエリ(データ)を組み合わせる技術を解説しました

今回からは、Power Query Editor上でデータを変換する技術を解説していきます

ところで、題名にあるPivotはどう意 味かご存知でしょうか?

ちなみに辞書には軸とか、旋回と書いてありました

この回を終了した時には、”Pivot”の意味が納得できるようになっていると思います

<今回のポイント>

A.集計の目的とデータの並べ方の関係

下の2つのデータを見比べてください

①は縦に繰り返し並んでいます。一方、②は、①の2つの目の列が横に“旋回”して並んでいます

どちらが集計しやすいと思いますでしょうか?

全体の合計値を出す場合、①は1列を範囲にしてSUM関数を入力すれば算出できます

一方、②は見出し以外の全ての行列を範囲に含めてSUM関数を入力するしかありません

①と②のデータで「アルファベット」と「ひらがな」別、つまり2つの切り口を組み合わせて合計値を集計する場合も考えてみましょう

①はフィルターをかけて、ひらがなの列だけみれば合計値を集計できます

②の場合には、目で列を追いかけながら集計するしかありません

ですので、「データはあらゆる切り口でデータ集計が行えるようにする」、ということであれば、縦方向に繰り返し並べていくべきなのです

B.Power Query上での操作

Power Queryでは、Power Query Editor上で、横に旋回しているデータを縦の並びに切り替え、集計を行い易い形にてデータを出力し直すことができます

その際、旋回させる軸を決めるのと同時に、以下のサンプルデータの合計の行列(Total)を削除します

この合計の値を消して、その後はどのようにに合計値を算出していくかについては後述します

では、実際のサンプルデータを基にして解説をはじめます

1.Power Query Editorを立ち上げる

データタブの「テーブルまたは範囲から」をクリックし、Power Query Editorを立ち上げます

この際、8行目のTotalの範囲が入るように気を付けましょう!

こちらは前述のように、Power Query Editor上で削除を行います

2.合計行列を消去する

Power Query Editorが開いたら次の①②の処理を行い、「 B.Power Query上での操作」で前述したようにTotalの行列を削除します

①フィルターからTotal行を消去する

-フィルターをクリックします

-チェックを外して一覧からTotalを消します

➁Total列を消去する

Total列を指定し、削除をクリックします

3.Pivot解除

ではPivot解除機能により、に旋回しているデータをの並びに切り替えます

①軸を指定します

今回は、に展開している日付を並びにします

ですので、一番左にあるカテゴリー列を左クリックして指定します

その後、右クリックすると可能な処理の一覧がでてきます

➁その他の列のピボット解除

①の一覧から「その他の列のピボット解除」をクリックします

これで、横に展開していたデータが縦並びになりました

4.読込先をピボットテーブルに指定する

Power Query Editorで縦並びにしたデータを「ピボットテーブル」としてエクセルシート上に読込みます

①閉じて次に読みこむを指定

ホームタブにて「閉じて読み込む」の右横▼マークから「閉じて次に読み込む」を指定します

➁ピボットテーブルの読込先を指定

ー「既存のワークシート」内の「元のデータの下」A11セルを指定しましょう!

-ピボットテーブルのフィールドを以下のように指定しましょう!

Power Query Editor上でTotalの行列は削除しましたが、ピボットテーブルでTotal(合計)が集計できるようになりました

逆に、もしPower Query Editor上でTotal行列を削除しなかったら、ピボットテーブル上で合計値が本来の値より2倍になってしまいます

<まとめ>

 このPivot解除を知っておけば、他の方から入手したデータが、横並びになっていなかったとしても、縦の並びに簡単に変えることができます

 データを縦の並びに変えておけば、様々な切り口でデータ集計がおこなえるようになりますので、ぜひ有効活用していきましょう!

 次回はセルの中のデータを複数列に分解する方法と今回解説したピボット解除を組み合わせた変換処理を行います

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村