データ形式には、人間が見たり入力しやすい形になっている「クロス集計表」と集計がしやすい「データーベース形式」の大きく分けて二つの形式があります。ケースバイケースで二つの形式を使い分ける必要があるため、瞬時に二つの形式を変換するテクニックが重要となります。今回の講座では、パワークエリの「列のピボット解除」と「列のピボット」という機能を使って二つの形式を作成する方法を学習します。
「クロス集計表」と「データーベース形式」
さてまずは、「クロス集計表」と「データーベース形式」をビジュアルで確認してみましょう。
左が「データーベース形式」で集計がしやすい形です。個々の列が一つの項目になっているのが特徴です。右側が「クロス集計表」で人間がみやすい形式です。そして、パワークエリでは「列のピボット」という機能で「データーベース形式」から「クロス集計表」に変換して、「列のピボット解除」という機能でその逆変換を行うことができます。

ピボット解除して集計しやすいデータに変換
人間が見やすいクロス集計表から集計しやすいデーターベース形式へ変換する方法を学習します。
選択した列のみをピボット解除
パワークエリエディタを開きます。
- テーブル内のセルを選択して[データ]タブを選択します。
- [テーブルまたは範囲から]を選択します。

- ピボット解除したい列を選択します。
- [変換]タブを選択します。
- [選択した列のみをピボット解除]を選択しま

以下のようにピボットが解除されます。

関数の確認しておきましょう。
Table.Unpivot関数を使ってピボット解除をしています。。
1 2 |
= Table.Unpivot(変更された型, {"1Q", "2Q", "3Q", "4Q", "総計(品目)"}, "属性", "値") |
クロス集計表をピボット解除すると構造上必ず不要な行が存在しています。今回の演習の場合では、以下の集計行が不要となっているので削除していきます。

[品目]列の総計(四半期)行を削除します。[品目]列の右上の▽をクリックして、総計(四半期)からチェックを外します。[品目]列から削除されました。

同様に、[属性]列の総計(品目)行を削除します。

最後に列名の変更をして完了です。

その他の列のピボット解除
今回の演習では、「選択した列のみをピボット解除」としましたが、「その他の列のピボット解除」を選択することで選択した列以外をピボット解除することもできます。
以下の例では、品目を選択して「その他の列のピボット解除」を選択していますが、これは品目列以外をピボット解除するという意味で、上記の演習の結果と同じになります。

使われている関数が異なりますので確認しておきましょう。
コード量が少ないのでこちらの関数を利用した方がスッキリします。
Table.UnpivotOtherColumns関数を使ってピボット解除をしています。
1 |
= Table.UnpivotOtherColumns(変更された型, {"品目"}, "属性", "値") |
ピボット化して見やすいクロス集計表にする
続いては、先ほどの演習とは逆で集計しやすいデーターベース形式から人間が見やすいクロス集計表へ変換する方法を学習します。
パワークエリエディタを開きます。
- テーブル内のセルを選択して[データ]タブを選択します。
- [テーブルまたは範囲から]を選択します。

- ヘッダー項目にしたい列を選択します。(以下の例では四半期列を指定)
- [変換]タブを選択します。
- [列のピボット]を選択しま

- [値列]には合計にしたい[費用]列を選択します。
- [値の集計関数]は合計を選択します。(デフォルトが合計)

関数の確認しておきましょう。
Table.Pivot関数を使ってピボット化をしています。。
1 2 |
= Table.Pivot(変更された型, List.Distinct(変更された型[四半期]), "四半期", "費用", List.Sum) |
四半期がヘッダー項目となっている集計表が完成しました。総計は、Excelに読み込ませてExcel関数で行ってください

パワークエリ学習コンテンツ
-
第1回PowerQuery講座|パワークエリとは?何ができる?どう起動する?
-
第2回PowerQuery講座|パワークエリのデータソースの取り込み方法をデータ形式毎に解説
-
第3回PowerQuery講座|パワークエリの『列』操作
-
第4回PowerQuery講座|パワークエリの『行』操作
-
第5回PowerQuery講座|文字を整えて(テキストクレンジング)パワークエリで扱いやすいようにする。
-
第6回PowerQuery講座|パワークエリの数字計算(四則演算、四捨五入、切り上げ、切り捨て)
-
第7回PowerQuery講座|パワークエリの統計関数やグループ化機能を使った列の集計方法
-
第8回PowerQuery講座|パワークエリの時間を表すデータ型と時間を使った計算
-
第9回PowerQuery講座|パワークエリの様々な条件判断
-
第10回PowerQuery講座|パワークエリで行と列を入れ替える(列のピボット解除、列のピボット)
-
第11回PowerQuery講座|パワークエリにおけるクエリの管理方法
コメント