何度かこのブログで紹介していますが、Excelの新機能（と、もう言えない？）の「取得と変換」は、これまでの「外部データの取り込み」を置き換えるだけではありません。
先日、ある方から相談を受けたのが、クロス集計表と呼ばれる表をデータ分析のためにクロス集計ではないシンプルな表形式データ（テーブル）に変換するものでした。その方は手作業でその変換をしていたのですが、テーブルのサイズが大きくなったり、繰り返しの作業になると手では無理、なんとか自動化できないか？というものでした。
これ、今は取得と変換のクエリエディターの「列ピボットの解除」を使えば、VBAを使わなくても変換ができます。
取得と変換のこの「クロス集計を解除する」言い換えれば「列ピボットを解除する」機能は、まだまだ認知が低いようです。Excelのテーブル機能と合わせて、取得と変換のクエリエディターは、業務でデータ分析をする人（もっと言えば、Excelを使ってレポートを作る人）にとって知っていて損はしない機能だと思います。また、PowerBIを使いこなすためのベースとなる知識のひとつです。
クロス集計表とは質問やデータのカテゴリを縦・横に「クロスさせて」数値を集計した表です。多くの人が意識しないでレポートの表を作ると、このクロス集計表になっていることが多く、Excel初級講座などでも、まずこのクロス集計表を作らせる演習が多いのも事実です。以下の表は、支店と製品のカテゴリを、月別の数値とクロスさせた表です。
データ分析という観点からはツッコミどころが満載の表で、これを分析元のデータの表として作ってはいけないのですが、反面、数値を理解しやすい表なのです。この表はレポートとしての「最終形」と言えます。繰り返しになりますが、この表から別の分析をするといった作業には向かないのです。
分析のためのシンプルな表とは、縦・横のカテゴリでクロスしていない表で、以下のような表です。
前置きはここまでにして、クロス集計表を「取得と変換」の「クエリエディター」の「列ピボットの解除」などの機能を使って、シンプルな表形式データに変換する手順をご紹介します。
以下は一連の作業を記録したアニメーションGIFです。
以下、アニメーションGIF内の手順です。
1. クロス集計表をテーブルに変換する
リボン[挿入]のテーブルから変換できます。ショートカット Ctrl+T も使えます。
変換したいクロス集計表のセルを選択するだけで自動的に範囲が選択されます。
なお、集計行・集計列はいりません。アニメーションGIFでは、自動選択で集計行ははずれましたが、集計列を含んでテーブル変換したので、クエリエディターで集計列の削除をしています。
2. テーブルを指定してクエリエディターを立ち上げる
上記で作成したテーブルのいずれかのセルが選択されている状態（＝アクティブセルをテーブル内のセルにする）にして、[データ]タブの「取得と変換」の [テーブルから] をクリックして、クエリエディターを立ち上げます。
クエリエディターはExcelとは別のウィンドウです。クエリエディターで指定した編集・操作の結果をExcelのワークシートに反映させることができます。元のデータを書き換えたりしないので安心してください。
3. 不要な列を削除する
集計列はいらないので「小計」の列を選択して、右クリックメニューの[削除]を使って列の削除をします。
このあたりの操作感はほぼExcelと一緒です。また、慣れてきたら要らない列を削除する方法から、必要な列を指定して残す方法も試してみて下さい。実際の業務では「必要な列だけを指定して残す」ほうが使い勝手が良いです。
4. 列ピボットを解除する
クロス集計表で慣れていると「何がダメなのかわからない」「どの列が列ピボットなのかわからない」と感じる人がいるようです。
仕方ありません。最初にExcelを習うときのサンプルの表はクロス集計表になりがちで、それは「罫線」のひき方、小計セルで使うSUM関数、さらには同じ値が連続したセルの結合方法(最悪・・・）を教えるには最適な表だからです。
Accessなどのデータベース製品を勉強した人にとっては、テーブルや表のことを言っているので理解しやすいと思いますが、Excelは上記のような教え方をされるため、ここの理解が第一関門かもしれません。このあたりは別途「テーブル」や「フィールド」、「レコード」といったキーワードで勉強してみてください。テーブルにおいては、1月、2月、3月、、、は月が「横に並ぶ」、東京、大阪、名古屋など地域や都市が「横に並ぶ」、製品A、製品B、製品Cなど同じ性質で種類の違うものが「横に並ぶ」ことはありません。それらは「月」や「地域」や「製品」の列にして扱います。
そうすると同じような行が増えるから「見づらい」という人もいますが、その感覚正しいです。そこから見やすく集計するから大丈夫です。また、何度も繰り返し同じ値のセルが続くからといって「結合セル」は使わないでください。（ワークシートでテーブルに変換済みだと結合セルは使えなくなりますが。）
サンプルのアニメーションGIFでは、「支店」と「製品」の列は列ピボットではなく、「1月」「2月」「3月」の列だけが列ピボットです。これらをまとめて「月」の列にしたいので、「1月」から「3月」の列を選択して、「列ピボットの解除」を行っています。解除した列は「属性」になっていますが、後で「月」などの名前（フィールド名）に変更可能です。
5. 空欄に値を埋める
列ピボット解除によって、元の行の直後に新しい行が追加されます。その時の空欄のセルに何を入れるかを指定します。アニメーションGIFの例では「支店」の列（フィールド）に空欄ができてしまいました。このような場合、「下方向にフィルする」を使って空欄に必要なデータを入れることができます。
6. ワークシートに結果を戻す
ここまでの操作は「クエリエディター」ウィンドウでの操作です。この結果をワークシートに戻すために、クエリエディターウィンドウの[ファイル]タブの[閉じて読み込む]や[閉じて次に読み込む]を使って、ワークシートに結果を戻します。
これでクロス集計表を表形式のテーブルに変換することができます。
このクエリエディターで行った操作は、ブックに保存されます。ブックに保存されているクエリは後から編集することも可能で、なおかつデータの「更新」によって最新の状態にすることができます。取得と変換の最大の強みは、元のデータを柔軟に指定できること（Excelは元より、他のcsvファイル、SharePointやFacebook、他のデータベースなどなど）、元データを変更せずにクエリエディター内で編集・加工すること、その手順をクエリとしてブック内に保存できること、そしてデータの更新を使ってクエリの結果を最新にできること、です。
この機能は覚えていて損はしません。この取得と変換のピボット解除の機能を知らないと、強引に手作業と機能で処理するか、この機能を知らないExcel上級者からVBAでやるしかないね、といったアドバイスを受けることになります。モダンエクセルで本当に知ってほしい機能のひとつです。（しつこいですが、あとはExcelのテーブル機能です。）
https://road2cloudoffice.blogspot.jp/2014/10/vlookup.html
先日、ある方から相談を受けたのが、クロス集計表と呼ばれる表をデータ分析のためにクロス集計ではないシンプルな表形式データ（テーブル）に変換するものでした。その方は手作業でその変換をしていたのですが、テーブルのサイズが大きくなったり、繰り返しの作業になると手では無理、なんとか自動化できないか？というものでした。
これ、今は取得と変換のクエリエディターの「列ピボットの解除」を使えば、VBAを使わなくても変換ができます。
取得と変換のこの「クロス集計を解除する」言い換えれば「列ピボットを解除する」機能は、まだまだ認知が低いようです。Excelのテーブル機能と合わせて、取得と変換のクエリエディターは、業務でデータ分析をする人（もっと言えば、Excelを使ってレポートを作る人）にとって知っていて損はしない機能だと思います。また、PowerBIを使いこなすためのベースとなる知識のひとつです。
クロス集計表とは質問やデータのカテゴリを縦・横に「クロスさせて」数値を集計した表です。多くの人が意識しないでレポートの表を作ると、このクロス集計表になっていることが多く、Excel初級講座などでも、まずこのクロス集計表を作らせる演習が多いのも事実です。以下の表は、支店と製品のカテゴリを、月別の数値とクロスさせた表です。
 |
| クロス集計表の代表例 支店カテゴリ（縦）と月別の数値（横）をクロスさせた表 |
分析のためのシンプルな表とは、縦・横のカテゴリでクロスしていない表で、以下のような表です。
 |
| シンプルな集計前の表形式のデータ |
以下は一連の作業を記録したアニメーションGIFです。
 |
| クロス集計表を取得と変換クエリエディターを使ってピボット解除する |
1. クロス集計表をテーブルに変換する
リボン[挿入]のテーブルから変換できます。ショートカット Ctrl+T も使えます。
変換したいクロス集計表のセルを選択するだけで自動的に範囲が選択されます。
なお、集計行・集計列はいりません。アニメーションGIFでは、自動選択で集計行ははずれましたが、集計列を含んでテーブル変換したので、クエリエディターで集計列の削除をしています。
2. テーブルを指定してクエリエディターを立ち上げる
上記で作成したテーブルのいずれかのセルが選択されている状態（＝アクティブセルをテーブル内のセルにする）にして、[データ]タブの「取得と変換」の [テーブルから] をクリックして、クエリエディターを立ち上げます。
クエリエディターはExcelとは別のウィンドウです。クエリエディターで指定した編集・操作の結果をExcelのワークシートに反映させることができます。元のデータを書き換えたりしないので安心してください。
3. 不要な列を削除する
集計列はいらないので「小計」の列を選択して、右クリックメニューの[削除]を使って列の削除をします。
このあたりの操作感はほぼExcelと一緒です。また、慣れてきたら要らない列を削除する方法から、必要な列を指定して残す方法も試してみて下さい。実際の業務では「必要な列だけを指定して残す」ほうが使い勝手が良いです。
4. 列ピボットを解除する
クロス集計表で慣れていると「何がダメなのかわからない」「どの列が列ピボットなのかわからない」と感じる人がいるようです。
仕方ありません。最初にExcelを習うときのサンプルの表はクロス集計表になりがちで、それは「罫線」のひき方、小計セルで使うSUM関数、さらには同じ値が連続したセルの結合方法(最悪・・・）を教えるには最適な表だからです。
Accessなどのデータベース製品を勉強した人にとっては、テーブルや表のことを言っているので理解しやすいと思いますが、Excelは上記のような教え方をされるため、ここの理解が第一関門かもしれません。このあたりは別途「テーブル」や「フィールド」、「レコード」といったキーワードで勉強してみてください。テーブルにおいては、1月、2月、3月、、、は月が「横に並ぶ」、東京、大阪、名古屋など地域や都市が「横に並ぶ」、製品A、製品B、製品Cなど同じ性質で種類の違うものが「横に並ぶ」ことはありません。それらは「月」や「地域」や「製品」の列にして扱います。
そうすると同じような行が増えるから「見づらい」という人もいますが、その感覚正しいです。そこから見やすく集計するから大丈夫です。また、何度も繰り返し同じ値のセルが続くからといって「結合セル」は使わないでください。（ワークシートでテーブルに変換済みだと結合セルは使えなくなりますが。）
サンプルのアニメーションGIFでは、「支店」と「製品」の列は列ピボットではなく、「1月」「2月」「3月」の列だけが列ピボットです。これらをまとめて「月」の列にしたいので、「1月」から「3月」の列を選択して、「列ピボットの解除」を行っています。解除した列は「属性」になっていますが、後で「月」などの名前（フィールド名）に変更可能です。
5. 空欄に値を埋める
列ピボット解除によって、元の行の直後に新しい行が追加されます。その時の空欄のセルに何を入れるかを指定します。アニメーションGIFの例では「支店」の列（フィールド）に空欄ができてしまいました。このような場合、「下方向にフィルする」を使って空欄に必要なデータを入れることができます。
6. ワークシートに結果を戻す
ここまでの操作は「クエリエディター」ウィンドウでの操作です。この結果をワークシートに戻すために、クエリエディターウィンドウの[ファイル]タブの[閉じて読み込む]や[閉じて次に読み込む]を使って、ワークシートに結果を戻します。
これでクロス集計表を表形式のテーブルに変換することができます。
このクエリエディターで行った操作は、ブックに保存されます。ブックに保存されているクエリは後から編集することも可能で、なおかつデータの「更新」によって最新の状態にすることができます。取得と変換の最大の強みは、元のデータを柔軟に指定できること（Excelは元より、他のcsvファイル、SharePointやFacebook、他のデータベースなどなど）、元データを変更せずにクエリエディター内で編集・加工すること、その手順をクエリとしてブック内に保存できること、そしてデータの更新を使ってクエリの結果を最新にできること、です。
この機能は覚えていて損はしません。この取得と変換のピボット解除の機能を知らないと、強引に手作業と機能で処理するか、この機能を知らないExcel上級者からVBAでやるしかないね、といったアドバイスを受けることになります。モダンエクセルで本当に知ってほしい機能のひとつです。（しつこいですが、あとはExcelのテーブル機能です。）
https://road2cloudoffice.blogspot.jp/2014/10/vlookup.html
