ピボットテーブルを活用して会議資料作成

ピボットテーブルを活用して会議資料作成

前回「経理業務に使えるピボットテーブルの活用方法」の記事ではピボットテーブルについて紹介し、経費精算システムから出力した伝票データを利用して、ピボットテーブルを作成し、仕組みと大まかな機能の説明をしました。

今回はピボットテーブルからグラフを作成する方法について紹介したいと思います。
※この記事で紹介するグラフの作成方法は「Excel2013」によるものです。
 

ピボットグラフの作成

ピボットテーブルからグラフを作成できる「ピボットグラフ」という機能があります。
Excelでグラフを作成するのは簡単です。
グラフを作成するのに大変なのは、必要な表を用意することです。
ピボットテーブルとはデータから簡単に「集計表」を作成できる機能です。
グラフのために表を作成するのではなく、データからピボットテーブルを作成しグラフを作成する方が、データとグラフとの数値の整合性が担保されます。

サンプルデータをこちらからダウンロードしてシート「ピボットテーブル」を選択してください。

1.グラフを作成するため「挿入」→「ピボットグラフ」を選択します。

グラフを作成するため「挿入」→「ピボットグラフ」を選択します。

2.集合縦棒を選択して「OK」を選択します。

集合縦棒を選択して「OK」を選択します。

3. 「01売上」だけ表示されるようにグラフ下部にある「小計区分」横の▼をクリックします。

「01売上」だけ表示されるようにグラフ下部にある「小計区分」横の▼をクリックします。

フィルターメニューが表示されます。

4.「01売上」だけにチェックを入れて「OK」を選択します。

「01売上」だけにチェックを入れて「OK」を選択します。

「01売上」だけ表示されました。

「01売上」だけ表示されました。

ピボットテーブルも「01売上」だけ抽出された形になっています。

ピボットテーブルも「01売上」だけ抽出された形になっています。

他のグラフ要素を追加するには、グラフを選択する表示される右上に「+」のアイコンをクリックするとメニューが表示されますので、チェックを入れるとそれぞれが表示されます。

チェックを入れるとそれぞれが表示されます。

5.月別推移表に変更するため、「デザイン」→「行/列の切り替え」を選択します。

月別推移表に変更するため、「デザイン」「行・列の切り替え」を選択します。

部門と月度が切り替わり、月別推移表になりました。

部門と月度が切り替わり、月別推移表になりました。

グラフの元となるピボットテーブルの行と列も切り替わっています。

グラフの元となるピボットテーブルの行と列も切り替わっています。

6.折れ線グラフに変更するため、「デザイン」→「グラフの種類の変更」を選択します。

折れ線グラフに変更するため、「デザイン」→「グラフの種類の変更」を選択します。

「グラフの種類の変更」ダイアログボックスが表示されます。

7.折れ線グラフを選択します。

折れ線グラフを選択します。

折れ線グラフが作成されました。

折れ線グラフが作成されました。

簡単に別のグラフに変更することが可能です。

 

交通費・経費精算システム「楽楽精算」 交通費・経費精算システム「楽楽精算」

 

グラフの作成

「ピボットブラフ」はとても便利な機能ですが、集計値をグラフに反映できないなど不便な部分もあります。
そこでピボットテーブルを使って別の方法でグラフを作成する方法を紹介します。
部門売上と集計値である部門利益を月別推移で表した複合グラフを作成します。

サンプルデータをダウンロードしてシート「ピボットテーブル (2)」を選択してください。

1.「デザイン」→「レポートのレイアウト」→「表形式で表示」を選択します。

「デザイン」→「レポートのレイアウト」→「表形式で表示」を選択します。

2.「集計」セルを「F2」で編集状態にして、「集計」を削除します。

「集計」セルを「F2」で編集状態にして、「集計」を削除します。

※一か所削除すればすべての「集計」が消えます。

※一か所削除すればすべての「集計」が消えます。

3.行ラベルすべてに部門名表示させるために「ラベルの繰り返し」設定を行います。
※赤枠の場所に部門名を表示させます。

※赤枠の場所に部門名を表示させます。

部門名を選択し右クリックでメニューを開き、「値フィールドの設定」を選択します。

部門名を選択し右クリックでメニューを開き、「値フィールドの設定」を選択します。

フィールド設定のダイアログボックスが開きますので、タブ「レイアウトと印刷」→「アイテムのラベルを繰り返す」にチェックを入れます。

タブ「レイアウトと印刷」→「アイテムのラベルを繰り返す」にチェックを入れます。

行ラベルに部門名が埋まりました。
行ラベルに部門名が埋まりました。

4.ピボットテーブル全体ではなく、一部のセルを選択して、別のエリアに貼り付けます。

ピボットテーブル全体ではなく、一部のセルを選択して、別のエリアに貼り付けます。

ピボットテーブルの一部をコピーして貼り付けると、表はピボットテーブルではなくなります。

5.貼り付けた表の「小計区分」の空白部分に「部門利益」と入力します。

貼り付けた表の「小計区分」の空白部分に「部門利益」と入力します。

6.「挿入」→「複合グラフ」を選択します。

「挿入」→「複合グラフ」を選択します。

「挿入」→「複合グラフ」を選択します。2

7.「売上」と「部門利益」のみ表示させるためにフィルターで絞り込みます。

「売上」と「部門利益」のみ表示させるためにフィルターで絞り込みます。

グラフを選択すると右上に表示される「フィルター」をクリックとメニューが表示されます。

グラフを選択すると右上に表示される「フィルター」をクリックとメニューが表示されます。

8. 月別推移表にするため「デザイン」→「行/列の切り替え」を実行します。

月別推移表にするため「デザイン」→「行列の切り替え」を実行します。

「デザイン」→「グラフの種類の変更」で複合グラフの設定を行います。

9.「デザイン」→「グラフの種類の変更」で複合グラフの設定を行います。

「デザイン」→「グラフの種類の変更」で複合グラフの設定を行います2

「グラフの種類の変更」ダイアログボックスが開くので、下部の設定を変更します。

「グラフの種類の変更」ダイアログボックスが開くので、下部の設定を変更します。

「01売上」項目は「積み上げ縦棒」
「部門利益」項目は「マーカー付き折れ線」を選択し、第2軸にチェックを入れます。

「部門利益」項目は「マーカー付き折れ線」を選択し、第2軸にチェックを入れます。

「部門利益」項目は「マーカー付き折れ線」を選択し、第2軸にチェックを入れます。2

※凡例の順番を変更したい場合

「デザイン」→「データの選択」を選択すると「データソースの変更」のダイアログボックスが開きます。

「デザイン」→「データの選択」を選択すると「データソースの変更」のダイアログボックスが開きます。

凡例項目の右上にある「▲▼」で順番に並べ替えます。

凡例項目の右上にある「▲▼」で順番に並べ替えます。

10.グラフ内に数字を表示させるため、グラフ右上「+」をクリックし「データラベル」にチェックを入れます。

グラフ内に数字を表示させるため、グラフ右上「+」をクリックし「データラベル」にチェックを入れます。

グラフ内に数字を表示させるため、グラフ右上「+」をクリックし「データラベル」にチェックを入れます。2

11.部門ごとにカラーを統一します。

棒グラフ、折れ線グラフそれぞれのパーツを選択しながら、右クリックで「塗りつぶし」「枠線」で変更しましょう。

部門ごとにカラーを統一します。

上記のグラフの設定
A部門
棒グラフ:塗りつぶし「薄い青」枠線「白の太さ2.25」
折れ線:塗りつぶし・枠線「濃い青」

B部門
棒グラフ:塗りつぶし「薄い赤」枠線「白の太さ2.25」
折れ線:塗りつぶし・枠線「濃い赤」

C部門
棒グラフ:塗りつぶし「薄い緑」枠線「白の太さ2.25」
折れ線:塗りつぶし・枠線「濃い緑」

12.軸ラベルを表示させます。

軸ラベルを表示させます。

表の左右下に「軸ラベル」が表示されますので、左軸に「売上高」右軸に「部門利益」と入力し、下軸は削除します。

横向きの文字を縦書きに変更する
文字を選択した状態で、「ホーム」→「文字方向」→「縦書き」にします。

文字を選択した状態で、「ホーム」→「文字方向」→「縦書き」にします。

横向きの文字を縦書きに変更する

13.グラフタイトルを変更します。

「グラフタイトル」をクリックすると編集が可能になります。

グラフタイトルを変更します。
 

まとめ

各種資料を作成する際に注意しなければならないのは、それぞれの資料間での数値が合っているかです。
基本的なことですが、意外に生じるミスです。
原因として考えられるのが、参考元のデータから異なる手順でそれぞれの資料を作成した場合や、資料作成後に訂正があり1つの資料は訂正したが、他の資料は訂正し忘れていた場合などがあります。

資料を作成する場合、別々の手順で作成するのではなく、同じ手順上で資料をそれぞれ作成するようにすれば、数値が合わないというミスは生じにくいです。

グラフも「データ」→「ピボットテーブル」→「グラフ」の手順で作成することを意識しましょう。

 

「経理プラス」メルマガでは、定期的に記事のランキングやおすすめ情報などをお届けしています。読み逃しがないよう是非ご登録ください!

「経理プラス」メルマガ登録は・・・ こちらから

 

この記事は更新日時点の情報となります。掲載の情報は法改正などにより変更になっている可能性があります。

経費精算システム「楽楽精算」

URLをクリップボードにコピーしました

● 著者

小栗勇人

小栗勇人

1980年生まれ。上場企業と上場企業子会社で経理を10年経験。ExcelやAccessの活用、クラウドサービスの導入、社内基幹システムの構築など、経理業務だけでなく、会社全体を効率化させることを日々実践中。運営ブログ「経理と事務の効率化」をきっかけにExcelの本『経理の仕事がサクサク進むExcel超活用術』を出版。

経理と事務の効率化