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

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

統計資料(グラフ)をピボットテーブルで作成する(その1)

統計資料(グラフ)の作成は「ピボットテーブル」を使えば簡単にできます

 

グラフを使えば情報が伝わりやすくなる

 日々更新される情報の変化を分かりやすく伝えるためには「グラフ」が効果的なツールになります。

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

 

 しかし、グラフの作成は設定が複雑なことに加え、頻繁に情報が更新されるような場合には、その都度再設定が必要になり大変な作業になります。

関数を使った集計表では、グラフは自動作成されない

 通常グラフの作成は、集計表に合わせて設定する必要があります。

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

 

ピボットテーブルでは、グラフが自動的に作成される

 ピボットテーブルでは、グラフの種類を選ぶだけで集計表から自動的にグラフが作成されます。

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

 

複雑な集計方法のグラフも簡単に作成できます

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

 

情報をビジュアルで伝えることができます

 グラフの種類を簡単な操作で変えることができます。

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

 

グラフの欠点は、情報のすべてを表示できない

 グラフは元になる集計表の「一定の断面」を切り取って表示するので、情報のすべてを表示できません。

 そのため、様々な角度から見る必要がある分析資料にグラフを使う場合にはので何種類ものグラフを作成する必要があります。

 複数のグラフを作成するのは大変ですが、ピボットテーブルを使えば日々作成する「リスト」に連動したグラフが作成されるので、集計作業やグラフの作成作業が不要になります。

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

 

ピボットテーブルで作成した「分析資料」

 ピボットテーブルを使えば、リスト形式の「入力表」から「集計表」を作成し高度な分析資料にすることができます。

 

リスト形式の「入力表」が、複雑な集計をした「集計表」になる   

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

 

関数を使って集計表を作るのは大変

 関数(数式)を使って集計表を作業はとても大変です。

 今回の例のように「発生内容」を集計する場合は、COUNTIF関数(COUNTIFS関数)を使います。

 例えば、発生場所別に発生件数をカウントする場合は下のようになります。

 関数(数式)を使う方法は集計方法を変更したり、表示形式を変えたい時などには修正が必要になり、とても大変な作業になってしまいます。

 その点、ピボットテーブルを使う方法では簡単なドラッグ操作だけで集計表が作成でき、集計方法の変更もドラッグ操作だけで簡単にできます。

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


 集計表を「グラフ」にすることで情報が伝わりやすくなります

 ピボットテーブルを使えば、クロス集計複合集計も簡単にできます。

 

「発生日時」「発生場所」複合集計し、「原因」クロス集計

 「日付」ごとに「発生場所」別の発生件数を表示し、さらに「原因」積み上げ縦棒で表示。

 1つのグラフで、3つの情報を伝えることができます。

  1. 発生日時
  2. 発生場所
  3. 原因

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

 

「2軸」の設定をして、発生件数(累計)を追加

 発生件数(累計)「2軸」に設定しすることで、日々の発生件数それまでの累計数を表示。  

 グラフを「2軸」にすることで、伝える情報が4つになります。

  1. 発生日時
  2. 発生場所
  3. 原因
  4. 発生数の「累積」

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

     ※ 縦軸の目盛が左と右で「最大値」が異なる。 

 

「タイムバー」「スライサー」で情報を絞り込む

 ピボットテーブルの「タイムライン」「スライサー」の機能を使えば、ドラッグクリックの操作だけで情報を絞り込むことができます。

 

タイムライン「2月3~4日」スライサー「北海道」のみを集計

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

 

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

 

集計結果の「フィールド(項目)」を追加する

 元の「表」に手を加えることなく、新しいフィールド(項目)」を追加することができます。

 

前日の発生件数に当日を加えて「発生件数(累計)」のフィールド(項目)を作成

 ピボットテーブルには、前日の件数に当日の件数を加えて「累計」をする計算方法も用意されています。

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

 

 「その2」では、ピボットテーブルの具体的な操作方法を説明します。

 

 

 

k-ohmori9616.hatenablog.com