ピボットテーブルを使える形式に表を変換する

「表の形式」と「ピボットテーブル」

Excelには「ピボットテーブル」と呼ばれる機能が用意されている。データをさまざまな角度から分析でき、とても便利な機能であるが、実際に活用されているケースは意外と少ないようである。

その理由は、ピボットテーブルが少し上級者向けの機能であることが一つの原因と考えられる。しかし、それ以上にピボットテーブルの活用を妨げているのが「基となるデータ（表）の形式」である。特に個人レベルで扱うデータの場合、「そもそもピボットテーブルを活用できる表になっていない・・・」というケースが大半を占めるかもしれない。

具体的な例で示していこう。Excelで表を作成するときは、以下に示した表のように「行」と「列」に項目を並べて、2次元的なデータ配置の表を作成するケースが多いと思われる。このような表は、一般的に「クロス集計表」と呼ばれている。

• 

  クロス集計表

一方、ピボットテーブルの基データとなる表は、「リスト形式の表」にしておくのが基本である。つまり、項目が横一列に並ぶ、いわゆる「データベース形式の表」にしておく必要がある。

• 

  リスト形式の表（データベース形式の表）

このため、手元にあるデータが「クロス集計表」であった場合、ピボットテーブルを活用することはできない。「絶対に使えない」という訳ではないが、効果的な活用方法にならないのが一般的だ。

かといって、既存の「クロス集計表」を「リスト形式の表」に書き換えるのは、相当に面倒な作業となる。現実問題として考えると、人の手で行うべき作業ではないだろう。そこで、ツールを使って表の形式を変換する方法を覚えておくとよい。

表をリスト形式に変換する方法

ここでは、以下に示した表を使って、「リスト形式の表」に変換する方法を紹介していこう。この表は、それぞれの実店舗とWebサイトについて、Tシャツの販売実績をまとめた表となる。

• 

  Tシャツの販売実績をまとめた表

項目数が多いため、一見すると「リスト形式の表」のようにも見えるが、「Tシャツの種類」と「店舗」についてデータが2次元的に配置されているため、見方によっては「クロス集計表」と考えることもできる。また、「計」や「売上」などの集計列があることも、ピボットテーブルを使いにくくする要因となる。単純にデータだけを並べた表にしておくのが理想的だ。

このような表を完全な「リスト形式の表」にするときに活用できるのが、第44回の連載でも紹介した「クエリ」（Power Query エディター）である。その手順を順番に解説していこう。

まずは、先ほどのExcelファイルを閉じて、新たにExcelを起動する。続いて、「データ」タブにある「データの取得」をクリックし、「ファイルから」→「ブックから」を選択する。

• 

  データの取得

ファイルを選択する画面が表示されるので、先ほどの表（クロス集計表）が保存されているExcelファイルを選択する。

• 

  Excelファイルの選択

Excelファイルが読み込まれ、ワークシートの一覧が表示される。データを取り込むワークシートを選択し、「データの変換」ボタンをクリックする。

• 

  シートの選択と「Power Query エディター」の起動

「Power Query エディター」が起動する。まずは、不要な行を削除していこう。行の削除は、「上から▲行」または「下から▲行」といった形で指定する。この機能を使って、空白行や集計行を削除しておくとよい。今回の例では、1行目が空白行になっているので、「上から1行」を削除する。

• 

  行の削除方法を選択

• 

  削除する行数の指定

続いて、各列のヘッダー（見出し）を指定する。今回の例では、「見出し」が「ColumnX」などと表示されており、本来の「見出し」はデータ行の1行目に配置されている。このような場合は、「1行目をヘッダーとして使用」をクリックすると、正しく「見出し」を配置できる。

• 

  1行目をヘッダーとして使用

表に集計列が含まれている場合は、この時点で「列の削除」も行っておこう。この操作は、列の「見出し」をクリックして選択し、「列の削除」のアイコンをクリックすると実行できる。

• 

  集計列の削除

念のため、各列の「データ型」も確認しておこう。「見出し」の左側に「ABC」のアイコンが表示されている列は「テキスト型」のデータとして扱われている。同様に、「1.2」のアイコンが表示されている列は「数値型」（10進数）のデータ、「123」のアイコンが表示されている列は「整数型」のデータとなる。

もしも「データ型」が間違って指定されていた場合は、各列のアイコンをクリックして「データ型」を正しく指定しなおしておくとよい。

• 

  データ型の指定

次は、「クロス集計表」になっている部分を「リスト形式」に変換する作業を行う。まずは「現在の形のまま維持する列」を選択する。複数の列の選択は、「Ctrl」キーを押しながら「見出し」をクリックしていくか、もしくは「Shift」キーを押しながら範囲の先頭と最後をクリックすればよい。

• 

  形式をそのまま維持する列の選択

この状態で「列のピボット解除」→「その他の列のピボット解除」を選択すると、選択しなかった列を「リスト形式」に変換することができる。

• 

  選択していない列のピボットを解除

今回の例では、以下の図のような変換結果になった。「本店」や「品川店」などが「見出し」ではなく「データ」として扱われていることを確認できるはずだ。

• 

  リスト形式に変換された表

変換された列の「見出し」には、「属性」や「値」などの文字が表示されている。このままでは内容を把握しにくいので、「見出し」の部分をダブルクリックし、適切な「見出し」に修正しておこう。

• 

  見出しの修正

以上で、「Power Query エディター」での作業は終了。「ホーム」タブを選択し、「閉じて読み込む」のアイコンをクリックすると、「リスト形式」に変換された表をExcelに取り込むことができる。

• 

  「Power Query エディター」を終了し、データを読み込む

• 

  リスト形式に変換された表

あとは、新たに作成された表をExceファイルとして保存しておくだけだ。これで、ピボットテーブルを存分に活用できるようになる。

テーブルを解除するには

ちなみに、「Power Query エディター」を使って取り込んだ表は、「テーブル形式の表」が自動指定される仕組みになっている。「テーブル形式の表」に慣れていない場合は、「通常の表」に変更しておくとよいだろう。

この作業は、表内のセルをクリックして選択し、テーブルツールの「デザイン」タブにある「範囲に変換」をクリックすると実行できる。

• 

  「e-Stat」のトップページ

「e-Stat」からデータをダウンロードする場合

参考までに、前回の連載で紹介した「e-Stat」からデータをダウンロードする場合についても補足しておこう。

「e-Stat」では、データをダウンロードする際に表の形式を指定することが可能となっている。ここで「CSV形式（列指向形式）」を選択すると、「リスト形式」に変換した表をダウンロードできるようになる。

• 

  「e-Stat」のトップページ

こちらも、あわせて覚えておくと、いつか役に立つだろう。