事務作業の省力化や資料作成に役立つ、Excelの使い方を紹介

Excelの機能を最大限に生かして、業務の省力化や経営状況を分析できる資料の作成方法を解説します。

「取得と変換」で"クロス集計"の表を"リスト集計"の表に変換する

"クロス集計" すると、「表」は扱いづらくなります

 

「取得と変換」を使えば、"複合集計" の表を"リスト形式"に変換できます

 "リスト形式" で作成された「表」は、ピボットテーブルを使えば 簡単に"クロス集計""複合集計" の表に変換することができます。

  "クロス集計" で作成された「表」は、「取得と変換」の機能を使って "リスト形式の表に変換" することができます。

 

縦方向の「商品名」横方向の「月」クロス集計した表

 縦方向と横方向の2つの「項目」で集計する "クロス集計の表" は情報をすばやく確認できる便利な形態の表です。

 しかし、"クロス集計" で作成された表は「データの追加・修正」が難しく、他の集計方法に変更するのは大変な作業です。  

   f:id:k-ohmori9616:20201029101248p:plain

 

"リスト形式"の表は、さまざまな形態に変更できる

 縦方向だけに "集計" された「リスト形式」の表は、色々な形態の"集計表の データ" にすることができます。

   f:id:k-ohmori9616:20201029101409p:plain

   

ピボットテーブルを使って「複合集計」の資料を作成

 ピボットテーブルを使えば、簡単な操作で様々な形態の「集計表」を作成することができます。

 しかし、ピボットテーブルを使うためには「データ」が "リスト形式" になっている必要があります。

k-ohmori9616.hatenablog.com

 

「商品名」➡「月」の複合集計の資料

 「商品名」と「月」の2つの項目を「行方向」に2つ重ねて集計する「複合集計」の表は「商品名ごとの月別の売上」が一目でわかる資料です。

   f:id:k-ohmori9616:20201029095601p:plain

 

「月」➡「商品名」の複合集計の資料

「商品名」と「月」の順序を入れ替えることで「月ごとの商品別の売上」の資料にすることができます。

   f:id:k-ohmori9616:20201029095438p:plain

 ※ 「表」が "リスト形式" でない場合、ピボットテーブルは使えません。

 

「取得と変換」を使って、"クロス集計""リスト形式の集計"にする

 「取得と変換」はExcel2016から標準機能になり、リボンから選択して実行できるようになりました。

 実際の動作は、外部プログラム(アドイン)としてPower Query上で操作します。

 通常の機能(コマンド)とは操作方法が異なるので注意が必要です。

 

「テーブルまたは範囲から」のコマンドでPowerQueryエディターを立ち上げる

 データタブのリボンから「テーブルまたは範囲から」をクリックすることで、PoweQueryエディターが立ち上がりExcelとは別のウィンド(連携している)が立ち上がります。(※ PoweQueryエディターを終了しないと、Excelには戻れない)

 変換したい「表」のどこか1カ所のセルを選択状態にして、「テーブルまたは範囲から」のコマンドを実行すると、自動で「表」が "テーブル化" されます。

  1. 変換する「表」のどこか1カ所のセルを選択状態にする
  2. 「データ」タブを選択
  3. 「テーブルまたは範囲から」をクリック
  4. テーブルの作成ダイアログで、「表」の範囲が正しく選択されているのを確認
  5. 「先頭行をテーブルの見出しとして・・・」にチェックを入れる
  6. 「OK」ボタンをクリック

   f:id:k-ohmori9616:20201028113741p:plain

表を「テーブル化」して作業効率を上げる - Excelの機能を活用して、事務作業の省力化や経営分析をする

 

セキュリティに関する通知が出る場合があります

 Power Queryエディター外部プログラム(アドイン)なので、"セキュリティの通知"が出ますが、問題ありません。

 1度、Power Queryエディターを立ち上げた後は表示されなくなります。

  1. 「OK」ボタンをクリック

   f:id:k-ohmori9616:20201028114404p:plain

 

"PowerQueryエディター"が立ち上がります 

 PoweQueryエディターが立ち上がるので、「横方向の項目すべて(4月~7月)を選択状態」にして、「列のピボット解除」のコマンドでリスト形式に変換します。

  1. 「変換」タブを選択
  2. 変換する表の最初の項目「4月」のセルをクリックして選択
  3. "SHIFTキー" を押しながら(※ 範囲選択のコマンド)、最後の項目「7月」のセルをクリック
  4. 「列のピボットを解除」をクリック

   f:id:k-ohmori9616:20201029082922p:plain

 

"列のピボットが解除"され、「リスト形式の表」になりました

   f:id:k-ohmori9616:20201029083538p:plain

 

PoweQueryエディターを終了して、変換した表をワークシートに書き出す

 「閉じて読み込む」のコマンドで、PoweQueryエディターを終了し、変換結果を書き込んだ新しいワークシートが作成されます。

  1. 「閉じて読み込む」をクリック

   f:id:k-ohmori9616:20201029083919p:plain

 

新しいワークシートが作成されました

 新しいワークシートが作成され、"リスト形式" に変換した結果が書き込まれました。

   f:id:k-ohmori9616:20201029084130p:plain

 

 「取得と変換」を使えば簡単に「クロス集計」された表を「リスト形式」の表に変換することができます。

 表を作成する際は、「リスト形式の表にしておく」ことで「ピボットテーブル」などを使って様々な形式の表(クロス集計、複合集計)にすることができます。

 「クロス集計」で作成された「表」は "データの追加が難しい" ので、いったん「リスト形式」に変換して、"ピボットテーブル" を使って「クロス集計」に戻してやることで「リスト」のデータを追加・修正しても自動更新されるようになるので便利です。

 

「ピボットテーブル」を作成する - Excelの機能を活用して、事務作業の省力化や経営分析をする

 

 

k-ohmori9616.hatenablog.com