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

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

 パワークエリのピボット解除は便利ですよね。一括で縦横並びを縦縦並びに変えられます。ところが、元データに空欄が混じると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へ

にほんブログ村

セル分割他・変換処理~中級編7回目

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

読者の皆さんは、他の方が作成した表を見て「使いにくい」と思ったことはありませんか?

例えば、下の図のような表です

1セルの中に複数のデータがカンマ” , ”を区切りにして混在しています

しかも、担当者が縦への繰返しで並んでいるのに、担当企業の欄は横に並んでいます

この表を他の目的に活用しようと思ったら、下の図のような「縦並びが複数列で繰り返された表」に変換するしかありません

この変換処理は手で行ったとしたら莫大な時間がかかります

ところが、Power Queryならば一括で変換できてしまうのです!

今回は上の表の変換を事例として、Power Queryを活用したデータクリーニング技術について、2回に分けて解説します

<今回のポイント>

A.セル分割

このセル分割によるデータ変換が今回のメインの解説になります

区切り文字を指定し、分割の仕方を指定するのがポイントです

B.ピボット解除

ピボット解除については前回も解説しました

この機能はとても便利です

今回も横並びを縦並びにするのに活用します

では、演習ファイルをダウンロードして頂いたら解説をはじめます!

今回は、セルの分割までです

アイコン

中級編7回目_演習 17.28 KB 42 downloads

...

1.セル分割

①Power Query Editorを開く

問題となる表の上にカーソルを置き、”テーブルまたは範囲から”をクリックし、Power Query Editorを開く

➁列の分割をクリック

Power Query Editorが開いたら、担当企業を選択した上で、変換タブから”列の分割”の▼マークをクリックします

③区切り記号と分割方法を指定

列の分割の▼マークをクリックすると、”区切り記号による分割”が選択できるようになりますので、またこの文字をクリックします

すると次のような画面が開きます

さて、今回のキーポイントに入る前に、元の表を再度確認しましょう!

カンマ” , ”が不規則に出現し、複数のデータが1セル内に混在しています

このセルを、区切り文字のカンマが出現するごとにデータ分割すればいいのです

ですから、区切り記号は”カンマ”を指定し、分割方法はデフォルトの「区切り記号の出現ごと」のままにし、右画面下のOKボタンを押します

すると、1セルの内容がカンマ” , ”ごとに複数のセルに分割されます!

<まとめ>

今回は、カンマ” , ”で区切られた使いにくい表をPower Query Editor上で分割まで行いました

今までのエクセル上でも同じようなことはできましたが、Power Queryではもっとシンプルに行えるようになっています

次回は使える表への変換をガッツで完成しましょう!

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

にほんブログ村

セル分割他・変換処理2~中級編8回目

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

前回はカンマ” , ”で区切られた「使いにくい表」をPower Query Editor上で「使いやすい形」になるように行方向に分割しました

このままだと、行別にみると2列の行があったり、5列以上の行あったり不規則で使いにくい表のままです

今回はPower Query Editor上で行方向に広がった列の並びを、縦の並びに変換して1列にし「使いやすい表」に変換します!

そして、前回使用したセル分割のテクニックを再び使い、連番までつけてしまいます!

前回の冒頭で1.セル分割2.ピボット解除を解説すると案内しました

前回1.セル分割を行ったので2.以降から解説を開始します

2.ピボット解除

コンマ” , ”で区切られたデータが混在したセルを分割した後は、データは横並びになっています

この並びを縦並びにして1列にします

この並び替えにはピボット解除の技術を使います

①担当者の列を選択

担当者の列をカーソルで選択し、右クリックします

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

担当者以外の列が並び替えの対象です

その他の列のピボット解除をクリックします!

すると、並び順が縦の並びの繰返しに変わります

3.読込

いよいよ2.で変換した内容をシートに読込みます

その前に若干、修正を加えましょう!

①再分割

ピボット解除をした時、新たに属性の列ができています

この列を更に2列に分割し、

⇒属性1の列は削除

⇒属性2の列は”NO”として残しましょう

今回の分割処理はピリオド”.”を区切り記号に指定して行います

2列に分割された属性1の列は削除し、

属性2の列は列の名前を変えましょう!

➁クエリ名前の変更

クエリの名前は担当企業としましょう!

③既存シートへの読込

①②が終わったところで読込を行います

今回は読込先を指定するので「閉じて読み込む」の右下▼から「閉じて次に読み込む」をクリックします

更にデータのインポート画面で

既存のワークシートを選択し、読み込むセル位置をE2セルに指定します

これで使いにくい表を使いやすくする表にする処理は完成です!

<まとめ>

今回は”,”や”.”で区切られたセルを分割することで表を使いやすくする技術を学びました

従来のエクセル手法であれば、この分割処理を行うのにワークシート上で大量の手作業が発生していました

Power Queryであれば一括で行えるのでとても便利です!

参考までに今回の解説に使用したサンプルデータを参考までに添付します


では次回はもっとPower Queryを実践的に使いこなせるようになるため、「やり直し」の方法についてガッツで解説します

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

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

にほんブログ村