タグ別アーカイブ: 行列入れ替え

セル結合により見出しが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へ

にほんブログ村