ピボットテーブルなどのデータ集計や分析の研修をやっていると、そもそも集計したいデータがテーブル形式の表ではなく、クロス集計表である、というお話を聞いたりします。

ここでいう「クロス集計表」は図 A のようなタイプで、「テーブル (形式の表)」は図 B のようなタイプのことです。

図A
R01.png

図B
R02.png

コピー＆ペーストを繰り返して A を B にするもよし、頻度が高いのなら専用のツールを購入して対応するもよし、いろいろ考えられると思いますが、今回は Excel 2016 (正確には Office 365 ProPlus の 2018年6月時点の最新の Excel) で行う手順をご紹介します。

下図のクロス集計表のデータを取り込んで、新しいブックにテーブルとして出力 (表示) してみます。

ファイル名は「クロス集計表.xlsx」、ワークシートは「2015年度」です。
R_03.png

画像が多いので少々長くなりますが、大まかには下記の手順です。

  1. クロス集計表のファイルがどこに保存されているのか、どのシートなのかを確認しておく。
  2. 編集する (クロス集計表) のシートを指定して特定する。
  3. 必要なデータを残すための編集作業をする。
  4. クロス集計表をテーブル形式に変換する。
  5. ワークシートにテーブルを読み込む。
     

編集するデータの特定

  1. 新しいブックを用意し、ワークシートの任意の位置にアクティブ セルをおいて、リボンの [データ] タブの [データの取得と変換] グループの [データの取得] をクリックし、[ファイルから] の [ブックから] をクリックします。
    R_4.png
     
  2. [データの取り込み] ダイアログ ボックスでクロス集計表が含まれているブックを選択して [インポート] をクリックします。
    R_05.png
     
  3. [ナビゲーター] ウィンドウでテーブルにしたい表が含まれるシートを選択し、[編集] をクリックします。
    R_06.png
     
  4. Power Query エディター (という Excel とは別のプログラム) が起動し、選択したシートのデータが表示されます。 

    ここで不要なデータの削除やクロス形式からテーブル形式への変換などの作業を行います。
    作業の履歴は [クエリの設定] ウィンドウの [適用したステップ] に追加されていきます。
    R_07.png
     

必要なデータだけを残すための編集

  1. ワークシート上部の「2015年度 上半期」と記載されている行など、不要な上から 2 行を削除します。

    リボンの [ホーム] タブの [行の削除] グループの [行の削除] をクリックし、[上位の行の削除] をクリックします。
    R_08.png
     
  2. [上位の行の削除] ウィンドウの [行数] に削除する行数を入力して [OK] をクリックします。
    R_09.png
     
  3. 指定した数の行が削除されます。
    R_10.png
     
  4. 「4月」「5月」「6月」などと記載されている現在の 1 行目を列名とします。
    リボンの [ホーム] タブの [変換] グループの [1 行目をヘッダーとして使用] をクリックします。

    1 行目の内容が列の名前に変換されます。
    R_11.png
     
  5. クロス集計表の中にある「Aグループ計」や「総計」といった小計行を削除するためにフィルターを実行します。

    フィルター条件を選択する列 ([Column1]) のフィルター ボタンをクリックし、除外する行のチェック ボックスをオフにして [OK] をクリックします。

    フィルターが実行され、チェック ボックスがオンになっていた行だけが抽出されます (不要な行が除外されます)。
    R_12.png
     
  6. 「エリアA」の 1 つ上のセルは、もともと空白セルだったので 1 行目をヘッダーにしたときにも列名は変わらず、[Column1] となっています。

    わかりやすくするため、この列名を変更します。(フィルター実行前にやってもよかった。別にこのタイミングでなくてもよい。何なら必須でもない。)

    名前を変更する列を選択し、リボンの [変換] タブの [任意の列] グループの [名前の変更] をクリックし、編集できる状態になったら変更後の名前を入力して [Enter] キーを押します。
    R_13.png
     
  7. クロス集計表の中にある「Q1計」と「Q2計」といった小計列を削除します。

    削除する列を選択し ([Ctrl] キーを使って 2 つ同時に選択しても OK)、リボンの [ホーム] タブの [列の管理] グループの [列の削除] をクリックします。

    選択していた列が削除されます。
    R_14.png
     

クロス集計表をテーブル形式にするための操作

ここまでの手順で、不要な行や列の削除が終わり、テーブルに必要なデータだけが残っている状態になりました。

現在残っているデータを [エリア]、[属性] (4月とか5月とか)、[値] (100 とか 300 とか) という組み合わせで 1 レコードになるように、“ピボット解除” という処理を行います。
 

  1. [エリア] 列を選択し、リボンの [ホーム] タブの [列のピボット解除] の▼をクリックし、[その他の列のピボット解除] をクリックします。
    R_15.png
     
  2. ピボットの解除が実行され、[エリア]、[属性]、[値] の 3 つのフィールドによって、クロスした位置の値を含む各レコードが表示されます。
    R_16.png
     
  3. 加工したデータを Excel ブックのワークシートに読み込みます。

    (Power Query エディターで編集しているクエリを) 閉じて (ワークシートにテーブルとしてデータを) 読み込みたいので、リボンの [ホーム] タブの [閉じる] グループの [閉じて読み込む] をクリックします。
    R_17.png
     
  4. ブックに新しいシートが追加され、Power Query エディターで加工したデータが読み込まれて表示されます。

    [クエリと接続] ウィンドウに、どのクエリをもとにいくつのレコード (行) 読み込まれたのかが表示されています。
    R_18.png

ここまでくればあとはお好きにどうぞ。という感じでしょうか。
クロス集計表をテーブル形式にしたいその理由はそれぞれなので。

テーブルのスタイルを変えてもいいし、テーブル名を変えてもいいし、フィルターしてもピボットテーブルを作ってもなんでも。
R_19.png

ただし、このクエリを読み込むことによって表示されているテーブルは、元のブックとつながっているという意識は持っておくほうがよいです。(もちろんよいことだけれど、何も言わないとコピーだと理解される方もいるかもしれないので)

たとえば、クロス集計表の値を変更して更新を実行すると、テーブルに表示される値も更新されます。
これがコピー＆ペーストによって “そのときの値” を手作業でテーブル形式にすることと、クエリという処理によって行うことの違いですね。
R_20.png

R_21.png
 

おまけ 1

読み込まれたデータはテーブルであるため、テーブルを選択するとリボンに [テーブル] ツールが表示されます。
テーブルとは？に関しては、今までもこのサイトで説明しているので省きます。

R_22.png

もう 1 つ。
このテーブルに表示されているのはクエリによる処理の結果なので、リボンに [クエリ] ツールも表示されます。
[クエリ] ツールの [クエリ] タブの [編集] グループの [編集] をクリックすると、Power Query エディターが起動します。
R_23.png
 

おまけ 2

Power Query エディターで行った編集作業は、クエリとして定義されています。
クエリには、クロス集計表のあった元のワークシートのシート名をもとにした「2015年度」というクエリ名が設定されています。

“クエリ” というのがよくわからない、という場合は、指定したデータ ソースから必要なデータを取り出したり加工したりするための処理だと思ってもらえたらよいです。

今回の場合は、「クロス集計表.xlsx の 2015年度 シート」というデータ ソースから、不要な行や列を削除したり、ピボットを解除するといった加工をしたりしました。

この作業の流れがステップとして「2015年度」というクエリに定義されていると考えるとよいと思います。
R_24.png


 

今回は、Power Query とはなんぞやとか、Excel 2010 や 2013 だったらアドインでどうとか、この機能が Excel で標準的に使えるようになるまでの歴史があるのは省きました。

正しく理解するためにはそういった歴史や経過も知っていたほうがよいかもしれないけれど、シンプルに、最新の Excel だとこんなことができるんだ、手作業することとの違いはなんだ、というところを知ってもらいたい、気をつけて？使ってもらいたい、ということでご紹介しました。

石田 かのこ