連載
» 2016年04月26日 05時00分 UPDATE

新社会人の必須知識 「Excel ピボットテーブル」超入門(終):「リレーションシップ」機能を使いこなして挑む、“即戦力”になるピボットテーブルの作り方 (1/4)

Excelを通じて、「ピボットテーブル」の基礎を学び、データ分析を実践するまでを習得する本連載。最終回は、「リレーションシップ」機能を駆使して、実務への応用を想定した高度なピボットテーブルを作成する方法を実践する。

[薬師寺国安,PROJECT KySS]
backn2.gif

連載バックナンバー

 本連載は、今後のビジネスに必須とされる「データ分析」の第一歩を踏み出してもらうために、Microsoft Excel 2013(以下、Excel)を通じて「ピボットテーブル」を実務で応用していくためのノウハウを紹介していく。

 前回は、ピボットテーブルのデータを素早くフィルタリングし、抽出する「スライサー」と「タイムライン」の使い方を解説した。

 最終回は、ピボットテーブル基礎編の総括版として、“より高度なデータ分析の実践”に向けたテクニックをお届けする。複数のテーブルにあるデータ項目を連携させる「リレーションシップ」機能を用い、複数のテーブルから必要な項目を抽出してまとめたピボットテーブルを作成していく方法を実践しよう。

 今回も第4回「ピボットテーブルで、“さまざまな視点でのデータ分析”を簡単に実践する(応用編)」などで用意した「テーブル型式に変換した売上一覧のリストデータ」を基礎データに使うので、事前に用意しておいてほしい(図1)。

photo 図1 事前準備する、売上一覧を「テーブル型式」に変換したリストデータの見本(画像クリックでリスト全体の見本を表示)

 リストデータの見本に使用したサンプルは以下の通り。こちらをコピーして図1のように仕上げてほしい。

NO	日付	商品名	単価	数量	金額	担当
1	2014/5/1	ノートPC	145800	3	437400	夏目
2	2014/5/1	デスクトップパソコン	212800	2	425600	夏目
3	2014/5/1	デジカメ	45800	3	137400	夏目
4	2014/5/2	KINECT	24800	5	124000	久利
5	2014/5/2	Leap Motion	12600	8	100800	久利
6	2014/5/2	ノートPC	145800	2	291600	久利
7	2014/5/3	デスクトップパソコン	212800	4	851200	阪神
8	2014/5/3	プリンター	34800	5	174000	阪神
9	2014/5/4	ノートPC	145800	8	1166400	正岡
10	2014/5/5	KINECT	24800	10	248000	愛媛
11	2014/5/5	マウス	3500	20	70000	愛媛
12	2014/5/5	ノートPC	145800	5	729000	愛媛
13	2014/5/5	スキャナー	65800	2	131600	愛媛
14	2014/5/6	デスクトップパソコン	212800	3	638400	内田
15	2014/5/6	デジカメ	45800	4	183200	内田
16	2014/5/6	プリンター	34800	5	174000	内田
17	2014/5/6	ディスプレイ	39800	2	79600	内田
18	2014/5/7	ディスプレイ	39800	4	159200	薬師寺
19	2014/5/7	ノートPC	145800	6	874800	薬師寺
20	2014/5/7	KINECT	24800	5	124000	薬師寺
21	2014/5/10	タブレットPC	98000	8	784000	広瀬
22	2014/6/1	ノートPC	145800	6	874800	夏目
23	2014/6/1	デスクトップパソコン	212800	4	851200	夏目
24	2014/6/1	デジカメ	45800	3	137400	夏目
25	2014/6/2	KINECT	24800	7	173600	久利
26	2014/6/2	Leap Motion	12600	5	63000	久利
27	2014/6/2	ノートPC	145800	4	583200	久利
28	2014/6/3	デスクトップパソコン	212800	7	1489600	阪神
29	2014/6/3	プリンター	34800	3	104400	阪神
30	2014/6/4	ノートPC	145800	4	583200	正岡
31	2014/6/5	KINECT	24800	8	198400	愛媛
32	2014/6/5	マウス	3500	25	87500	愛媛
33	2014/6/5	ノートPC	145800	3	437400	愛媛
34	2014/6/5	スキャナー	65800	4	263200	愛媛
35	2014/6/6	デスクトップパソコン	212800	6	1276800	内田
36	2014/6/6	デジカメ	45800	7	320600	内田
37	2014/6/6	プリンター	34800	6	208800	内田
38	2014/6/6	ディスプレイ	39800	4	159200	内田
39	2014/6/7	ディスプレイ	39800	6	238800	薬師寺
40	2014/6/7	ノートPC	145800	8	1166400	薬師寺
41	2014/6/7	KINECT	24800	7	173600	薬師寺
42	2014/6/10	タブレットPC	98000	6	588000	広瀬
43	2014/7/1	ノートPC	145800	5	729000	夏目
44	2014/7/1	デスクトップパソコン	212800	6	1276800	夏目
45	2014/7/1	デジカメ	45800	7	320600	夏目
46	2014/7/2	KINECT	24800	3	74400	久利
47	2014/7/2	Leap Motion	12600	7	88200	久利
48	2014/7/2	ノートPC	145800	5	729000	久利
49	2014/7/3	デスクトップパソコン	212800	3	638400	阪神
50	2014/7/3	プリンター	34800	4	139200	阪神
51	2014/7/4	ノートPC	145800	9	1312200	正岡
52	2014/7/5	KINECT	24800	5	124000	愛媛
53	2014/7/5	マウス	3500	35	122500	愛媛
54	2014/7/5	ノートPC	145800	5	729000	愛媛
55	2014/7/5	スキャナー	65800	3	197400	愛媛
56	2014/7/6	デスクトップパソコン	212800	4	851200	内田
57	2014/7/6	デジカメ	45800	7	320600	内田
58	2014/7/6	プリンター	34800	8	278400	内田
59	2014/7/6	ディスプレイ	39800	6	238800	内田
60	2014/7/7	ディスプレイ	39800	5	199000	薬師寺
61	2014/7/7	ノートPC	145800	4	583200	薬師寺
62	2014/7/7	KINECT	24800	3	74400	薬師寺
63	2014/7/10	タブレットPC	98000	7	686000	広瀬
リスト1:見本のサンプルデータ。この文字列を丸ごとコピーし、Excelにペーストして図1のように仕上げてほしい

項目別の基本情報をまとめた「テーブル」を個別に作る

 今回は、事前準備したリストデータとは別に、別のワークシートを作成して項目別の基本情報をまとめた「テーブル」を個別に作っていく。

 項目別の基本情報となるテーブルを個別に用意する理由は、基本データを一元管理し、データの整合性を保つためだ。後で解説する「リレーションシップ」機能で各テーブルの同じ項目同士を連携させておくと、基本データに修正があったとしても、同じ項目を通じてデータを参照・反映できる。この準備は数千、数万、数十万行と、データ量が増えるほど有効なので、今後データ分析を行う上でのテクニックとして覚えておいてほしい。

 まず、1つ目の「商品情報のテーブル」から作成する。「商品」であるノートPCは、商品番号「ABC-1」で、単価は「145800」円。同じくデスクトップパソコンは、商品番号「ABC-2」で、単価は「212800」円……といった基本情報を、「商品」というワークシートを新規作成してデータを追加し、テーブル型式に変換する。基本情報テーブルのための見本データは、リスト2に用意した。

商品番号	商品名	単価
ABC-1	ノートPC	145800
ABC-2	デスクトップパソコン	212800
ABC-3	デジカメ	45800
ABC-4	KINECT	24800
ABC-5	Leap Motion	12600
ABC-6	プリンター	34800
ABC-7	マウス	3500
ABC-8	スキャナー	65800
ABC-9	デジカメ	45800
ABC-10	ディスプレイ	39800
ABC-11	タブレットPC	98000
リスト2:「商品情報のテーブル」用の見本データ

 続いて、テーブルとして識別されているセル(A5など)にカーソルを合わせ、Excelメニュー「テーブルツール」→「デザイン」より、テーブル名を「商品情報」と指定する(図2)。

photo 図2 「商品」ワークシートを追加して商品の基本情報をまとめ、「商品情報」というテーブルを作成する

 なお、テーブル名はワークシート別に「テーブル*」などと自動命名されるが、テーブル名は文字列そのものが重要な役割を果たすので、瞬時に何かを識別できるほどに明解な名称を付けておくことが望ましい。後ほど解説する「複数のテーブルをまとめて集計する」など、大量の項目が羅列されるシーンで「何の項目かが瞬時に分かる」と、後の作業効率に大きな差が出てくる。

       1|2|3|4 次のページへ

Copyright© 2017 ITmedia, Inc. All Rights Reserved.

@IT Special

- PR -

TechTargetジャパン

この記事に関連するホワイトペーパー

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。