パワークエリを使うことによる強力なメリットである「列のピボット解除」について投稿します。
名前から何ができるのか、想像しづらいわね
機能から得られるそのメリットは非常に強力！
エクセルでデータ集計を行えば、横長のデータに遭遇することがよくあります。
パワークエリの「列のピボット解除」は、「横持ちのデータから縦持ちのデータへの変換」を少ない手間で実現します。
セル上で手動操作や関数、マクロを駆使すると結構大変。
でもパワークエリならたった「数クリック」で変換処理が完了、しかも一旦仕組みを作れば更新も自動化できます。注意点も含めて使い方に触れていきます。
パワークエリ「列のピボット解除」とは？
パワークエリの変換タブにある「列のピボット解除」は、人間が視認しやすい横持ちに整形されたデータを分析で扱えるように構造化されたデータへ変換する機能になります。
一連の列ヘッダーが一つの列（属性値）として、一連の列ヘッダーに含まれていた数値データが一つの列（値）として扱われます。
「横持ちのデータ」は、日付属性を持つデータ（月別の売り上げや、日別の勤務データなど）でよくみられますよね。
だって、その方が見やすいんだもの
これらの形式って、人間には見やすいのですが、ピボットテーブルで改めて分析しようとすると、扱いづらく感じます。要するに、データ形式が分析に適していないということになります。
「列のピボット解除」によって、データ分析に適した縦持ちのデータ構造へ変換することが可能となります。
「列のピボット解除」で横持ちから縦持ちへのデータ変換
では、実際にパワークエリ・エディタを操作していきます。
「列のピボット解除」を行う手順
- ステップ1表をテーブル化する
- ステップ2パワークエリで対象データを読込む
- ステップ3「列のピボット解除」を行う
- ステップ4列名称を変更し、エクセルに読込む
- ステップ5データ更新できるかチェックする
ステップ1 表をテーブル化する
サンプルのデータは以下の通り。
▲ 行ヘッダーに商品名、列ヘッダーに日付属性として各月が定義されています。数値データは、売上となっています。
パワークエリでは、読込むデータがテーブル定義されているほうが扱いやすいので、表をテーブル化します。
▲ テーブル範囲を選択して、リボンメニューの「ホーム」タブ→「テーブルとして書式設定」で任意の書式を選ぶか、ショートカット「CTRL+T」でテーブル化します。
▲ 「テーブルの作成」ダイアログで選択範囲の確認を行い、「先頭行をテーブルの見出しとして使用する(M)」にチェックを入れます。
▲ 「テーブルデザイン」タブの「テーブル名」で任意の名前をつけます。ここでの名称がパワークエリを起動した際のクエリ名称となるので、わかりやすいものをつけておきます。
ステップ2 パワークエリで対象データを読込む
▲ テーブル上の任意セルからリボンメニューの「データ」タブ→「テーブルまたは範囲から」データを読み込み、パワークエリ・エディターを起動します。
▲ 読み込んだ状態でテーブルの１行目がヘッダーとなっています。
ヘッダーの型変換は自動で行われて、「商品名」のデータ型は「テキスト」、それ以外は「数値（10進数）」となっています。
▲ このデータには、「総計」行が含まれています。ピボットテーブルとして処理する際は不要なのでフィルターで除外します。
ステップ3「列のピボット解除」を行う
ここから本題の操作です。
▲ ピボット解除を行いたい列を選択して、リボンメニューの「変換」タブから「列のピボット解除」をクリックします。（解除を行わない列を選択する方法もあり、後述）
▲ 各月のデータが 横持ちから縦持ちのデータへ変換 されました。
操作自体は簡単でこれだけです。ただ、注意点があるので後述します。
ステップ4 列名称を変更する
列のピボット解除後に生成される列名称は、ツールが定めた仮のもの。列の目的に沿った名称に変更しましょう。
▲ 「属性」列の名称を「月」、「値」列の名称を「売上」に変更します。これでパワークエリ上の処理は完了です。
▲ エディター上の操作が完了したら、データをエクセルに読み込みます。
▲ 整形済みのデータがセル上に展開されました。
ステップ5 データ更新できるかチェックする
読込元のテーブルにデータを追加して、パワークエリが更新可能かチェックします。
▲ 読込元のテーブルに「6月」のデータを追加します。
▲ リボンメニューの「データ」タブから「クエリと接続」を開き、対象のクエリを更新します。（または、読込先のテーブルの任意セル上で右クリック「更新(R)」を行う）
読込先のテーブルにも新しいデータが表示されました。これで追加データがあった場合の更新が容易になります。
「列のピボット解除」3つのオプション
「列のピボット解除」は、リボンメニューの「変換」タブから操作する方法、列を選択した状態で右クリックから操作する方法でアプローチできます。
操作メニューを見ると「列のピボット解除」には、3つのオプションがあります。
- 列のピボット解除
- ほかの列をピボット解除
- 選択した列のみピボット解除
操作の起点 は、次のとおりです。
- ピボット解除を行いたい列を選択するか
- ピボット解除を行いたい列以外を選択するか
▲ ピボット解除を行いたい列とは、上記だと各月を選択している状態（緑・網掛け）となります。
ピボット解除を行いたい列を選択する場合は、「1. 列のピボット解除」か、「3. 選択した列のみピボット解除」を選びます。これらの操作では、ステップで生成されるM言語コードが異なります。
▲ ピボット解除を行いたい列以外とは、上記だと商品名を列を選択している状態（緑・網掛け）となります。
ピボット解除を行いたい列以外を選択している場合には、「2. ほかの列をピボット解除」を選びます。
表の属性に応じた操作を選ぶこととなります。
今回の場合、選択肢2.（ピボット解除を行いたい列以外を選択し、 ほかの列をピボット解除する）が良いかもしれません。それは操作方法と操作ステップで生成されるコードが一致しており、後から処理ステップを振り返りやすいからです。
= Table.UnpivotOtherColumns(フィルターされた行, {"商品名"}, "属性", "値")▲ 「1. 列のピボット解除」「2. ほかの列をピボット解除」は、「商品名」を参照してそれ以外の列をピボット解除していることがわかります。
= Table.Unpivot(フィルターされた行, {"1月", "2月", "3月", "4月", "5月"}, "属性", "値")▲ 「3. 選択した列のみピボット解除」ではピボット解除したい列が明示的に指定されています。
6月以降のデータが入ってきたら、不都合はでないのかしら
今回のデータ構造では更新はできました。ただ、列名称が直接書き込まれていると後々、様々な問題が引き起こされることが考えられます。
「列のピボット解除」を使う際の注意事項
少し込み入っているので別の記事にしました。
- 自動の型変換が影響するケースと解消商法
- 置換処理が影響するケースと解消方法
- 選択した列のみピボット解除が影響するケースと解消方法
まとめ
パワークエリでとても便利な「列のピボット解除」について投稿しました。一度覚えると手放せなくなる機能となります。
紹介する書籍の中で翔泳社出版のものは、紙媒体やKindleのほかに公式サイト「SEshop」でPDF書籍が購入できます。オフィスで画面に移しながら手を動かせるので使い勝手抜群！！
全国送料無料！IT書、ビジネス書、資格書が豊富なSEshop
「SEShop」だとクーポン利用可、PDF版での入手となり扱いやすいです。現在、10%オフとなるクーポンが配布されていました。アマゾンとかで買うよりもお得です。
