第10回PowerQuery講座|パワークエリで行と列を入れ替える(列のピボット解除、列のピボット)

データ形式には、人間が見たり入力しやすい形になっている「クロス集計表」と集計がしやすい「データーベース形式」の大きく分けて二つの形式があります。ケースバイケースで二つの形式を使い分ける必要があるため、瞬時に二つの形式を変換するテクニックが重要となります。今回の講座では、パワークエリの「列のピボット解除」と「列のピボット」という機能を使って二つの形式を作成する方法を学習します。

目次

「クロス集計表」と「データーベース形式」

さてまずは、「クロス集計表」と「データーベース形式」をビジュアルで確認してみましょう。

左が「データーベース形式」で集計がしやすい形です。個々の列が一つの項目になっているのが特徴です。右側が「クロス集計表」で人間がみやすい形式です。そして、パワークエリでは「列のピボット」という機能で「データーベース形式」から「クロス集計表」に変換して、「列のピボット解除」という機能でその逆変換を行うことができます。

ピボット解除して集計しやすいデータに変換

人間が見やすいクロス集計表から集計しやすいデーターベース形式へ変換する方法を学習します。

練習用Excelファイルのダウンロード

説明で利用するExcelファイルをダウンロードして実際に操作をしてみましょう。

選択した列のみをピボット解除

STEP
パワークエリエディタを開く

パワークエリエディタを開きます。

  1. テーブル内のセルを選択して[データ]タブを選択します。
  2. [テーブルまたは範囲から]を選択します。
STEP
ピボット解除
  1. ピボット解除したい列を選択します。
  2. [変換]タブを選択します。
  3. [選択した列のみをピボット解除]を選択しま

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

関数の確認しておきましょう。

利用している関数

Table.Unpivot関数を使ってピボット解除をしています。。

STEP
不要行の削除

クロス集計表をピボット解除すると構造上必ず不要な行が存在しています。今回の演習の場合では、以下の集計行が不要となっているので削除していきます。

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

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

STEP
列名の変更

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

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

今回の演習では、「選択した列のみをピボット解除」としましたが、「その他の列のピボット解除」を選択することで選択した列以外をピボット解除することもできます。

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

使われている関数が異なりますので確認しておきましょう。

コード量が少ないのでこちらの関数を利用した方がスッキリします。

利用している関数

Table.UnpivotOtherColumns関数を使ってピボット解除をしています。

ピボット化して見やすいクロス集計表にする

続いては、先ほどの演習とは逆で集計しやすいデーターベース形式から人間が見やすいクロス集計表へ変換する方法を学習します。

練習用Excelファイルのダウンロード

説明で利用するExcelファイルをダウンロードして実際に操作をしてみましょう。

STEP
パワークエリエディタを開く

パワークエリエディタを開きます。

  1. テーブル内のセルを選択して[データ]タブを選択します。
  2. [テーブルまたは範囲から]を選択します。
STEP
ピボット化
  1. ヘッダー項目にしたい列を選択します。(以下の例では四半期列を指定)
  2. [変換]タブを選択します。
  3. [列のピボット]を選択しま
  1. [値列]には合計にしたい[費用]列を選択します。
  2. [値の集計関数]は合計を選択します。(デフォルトが合計)

関数の確認しておきましょう。

利用している関数

Table.Pivot関数を使ってピボット化をしています。。

STEP
ピボット表示の確認

四半期がヘッダー項目となっている集計表が完成しました。総計は、Excelに読み込ませてExcel関数で行ってください

パワークエリ学習コンテンツ

全11回の学習コンテンツでパワークエリを習得することができます。

コメント

コメントする

目次