"クロス集計" すると、「表」は扱いづらくなります
「取得と変換」を使えば、"複合集計" の表を"リスト形式"に変換できます
"リスト形式" で作成された「表」は、ピボットテーブルを使えば 簡単に"クロス集計" や "複合集計" の表に変換することができます。
"クロス集計" で作成された「表」は、「取得と変換」の機能を使って "リスト形式の表に変換" することができます。
縦方向の「商品名」と横方向の「月」でクロス集計した表
縦方向と横方向の2つの「項目」で集計する "クロス集計の表" は情報をすばやく確認できる便利な形態の表です。
しかし、"クロス集計" で作成された表は「データの追加・修正」が難しく、他の集計方法に変更するのは大変な作業です。
"リスト形式"の表は、さまざまな形態に変更できる
縦方向だけに "集計" された「リスト形式」の表は、色々な形態の"集計表の データ" にすることができます。
ピボットテーブルを使って「複合集計」の資料を作成
ピボットテーブルを使えば、簡単な操作で様々な形態の「集計表」を作成することができます。
しかし、ピボットテーブルを使うためには「データ」が "リスト形式" になっている必要があります。
「商品名」➡「月」の複合集計の資料
「商品名」と「月」の2つの項目を「行方向」に2つ重ねて集計する「複合集計」の表は「商品名ごとの月別の売上」が一目でわかる資料です。
「月」➡「商品名」の複合集計の資料
「商品名」と「月」の順序を入れ替えることで「月ごとの商品別の売上」の資料にすることができます。
※ 「表」が "リスト形式" でない場合、ピボットテーブルは使えません。
「取得と変換」を使って、"クロス集計"を"リスト形式の集計"にする
「取得と変換」はExcel2016から標準機能になり、リボンから選択して実行できるようになりました。
実際の動作は、外部プログラム(アドイン)としてPower Query上で操作します。
通常の機能(コマンド)とは操作方法が異なるので注意が必要です。
「テーブルまたは範囲から」のコマンドでPowerQueryエディターを立ち上げる
データタブのリボンから「テーブルまたは範囲から」をクリックすることで、PoweQueryエディターが立ち上がりExcelとは別のウィンド(連携している)が立ち上がります。(※ PoweQueryエディターを終了しないと、Excelには戻れない)
変換したい「表」のどこか1カ所のセルを選択状態にして、「テーブルまたは範囲から」のコマンドを実行すると、自動で「表」が "テーブル化" されます。
- 変換する「表」のどこか1カ所のセルを選択状態にする
- 「データ」タブを選択
- 「テーブルまたは範囲から」をクリック
- テーブルの作成ダイアログで、「表」の範囲が正しく選択されているのを確認
- 「先頭行をテーブルの見出しとして・・・」にチェックを入れる
- 「OK」ボタンをクリック
表を「テーブル化」して作業効率を上げる - Excelの機能を活用して、事務作業の省力化や経営分析をする
セキュリティに関する通知が出る場合があります
Power Queryエディターは外部プログラム(アドイン)なので、"セキュリティの通知"が出ますが、問題ありません。
1度、Power Queryエディターを立ち上げた後は表示されなくなります。
- 「OK」ボタンをクリック
"PowerQueryエディター"が立ち上がります
PoweQueryエディターが立ち上がるので、「横方向の項目すべて(4月~7月)を選択状態」にして、「列のピボット解除」のコマンドでリスト形式に変換します。
- 「変換」タブを選択
- 変換する表の最初の項目「4月」のセルをクリックして選択
- "SHIFTキー" を押しながら(※ 範囲選択のコマンド)、最後の項目「7月」のセルをクリック
- 「列のピボットを解除」をクリック
"列のピボットが解除"され、「リスト形式の表」になりました
PoweQueryエディターを終了して、変換した表をワークシートに書き出す
「閉じて読み込む」のコマンドで、PoweQueryエディターを終了し、変換結果を書き込んだ新しいワークシートが作成されます。
- 「閉じて読み込む」をクリック
新しいワークシートが作成されました
新しいワークシートが作成され、"リスト形式" に変換した結果が書き込まれました。
「取得と変換」を使えば簡単に「クロス集計」された表を「リスト形式」の表に変換することができます。
表を作成する際は、「リスト形式の表にしておく」ことで「ピボットテーブル」などを使って様々な形式の表(クロス集計、複合集計)にすることができます。
「クロス集計」で作成された「表」は "データの追加が難しい" ので、いったん「リスト形式」に変換して、"ピボットテーブル" を使って「クロス集計」に戻してやることで「リスト」のデータを追加・修正しても自動更新されるようになるので便利です。
「ピボットテーブル」を作成する - Excelの機能を活用して、事務作業の省力化や経営分析をする