【Excel】ピボットテーブル 入門編

Excel 基本動作

ピボットテーブルを活用できていますか?

ピボットテーブルと聞くと、”なんかパソコン強そうな人が使ってるよくわからないやつ“と思っている人も多いと思いますが、非常に便利な機能なので、ぜひ習得して使ってください。

スポンサーリンク

ピボットテーブル=クロス集計機能

ピボットテーブルはExcelのクロス集計機能です。

クロス集計というのは、単純集計(GT集計)との比較で考える方が理解しやすいので、まずは単純集計について説明します。

今回使うのは、以下のようなデータです。販売商品の一覧データです。商品はバナナ、いちご、チョコレートで、販売担当者に山田、田中、鈴木がいます。他には販売量、販売単価、合価のデータがあります。

11 
20 
22 
23 
24 
25 
26 
28 
30 
31 
32 
33 
商 品 名 
い ち ご 
チ ョ コ レ - ト 
チ ョ コ レ ー ト 
い ち ご 
チ ョ コ レ ー ト 
い ち ご 
い ち ご 
チ ョ コ レ ー ト 
チ ョ コ レ - ト 
い ち ご 
チ ョ コ レ - ト 
い ち ご 
い ち ご 
チ ョ コ レ - ト 
チ ョ コ レ ー ト 
い ち ご 
チ ョ コ レ ー ト 
い ち ご 
い ち ご 
チ ョ コ レ ー ト 
0 担 当 者 
山 田 
山 田 
山 田 
山 田 
山 田 
山 田 
山 田 
山 田 
田 中 
田 中 
田 中 
田 中 
田 中 
田 中 
田 中 
田 中 
田 中 
田 中 
田 中 
鈴 木 
鈴 木 
鈴 木 
鈴 木 
鈴 木 
鈴 木 
鈴 木 
鈴 木 
鈴 木 
0 販 売 数 量 0 販 売 価 格 ( 単 価 ) 0 販 売 価 格 ( 合 価 ) 0 
250 
500 
420 
420 
90 
360 
250 
1 ′ 250 
250 
750 
90 
540 
420 
2 ′ 940 
90 
270 
420 
1 ′ 680 
250 
500 
420 
2 ′ 940 
90 
720 
250 
500 
250 
1 ′ 000 
90 
270 
420 
1 ′ 680 
90 
900 
420 
1 ′ 260 
250 
1 ′ 000 
420 
840 
90 
630 
250 
乙 000 
250 
500 
90 
360 
420 
4 ′ 200 
90 
180 
420 
420 
250 
1 ′ 000 
420 
2 ′ 100 
90 
270 
250 
3 フ 50
スポンサーリンク

単純集計(GT集計)

単純集計というのは、1つの集計項目に対して1つの集計結果があるというような集計です。例えば上の元データの販売価格(合価)を単純集計すると、以下の通りになります。

10 
11 
12 
16 
18 
20 
22 
- ト 
24 
- ト 
26 
- ト 
28 
30 
31 
- ト 
32 
33 
34 
商 品 名 
0 担 当 者 
バ ナ ナ 
い ち ご 
チ ョ コ レ - ト 
バ ナ ナ 
バ ナ ナ 
チ ョ コ レ - ト 
い ち こ 
チ ョ コ レ - ト 
い ち ご 
バ ナ ナ 
い ち こ 
チ ョ コ レ - ト 
バ ナ ナ 
バ ナ ナ 
チ ョ コ レ - ト 
い ち ご 
チ ョ コ レ - ト 
い ち ご 
バ ナ ナ 
い ち ご 
チ ョ コ レ 
バ ナ ナ 
バ ナ ナ 
チ ョ コ レ 
い ち こ 
チ ョ コ レ 
い ち ご 
バ ナ ナ 
い ち こ 
チ ョ コ レ 
バ ナ ナ 
山 田 
山 田 
山 田 
山 田 
山 田 
山 田 
山 田 
山 田 
山 田 
田 中 
田 中 
田 中 
田 中 
田 中 
田 中 
田 中 
田 中 
田 中 
田 中 
田 中 
鈴 木 
鈴 木 
鈴 木 
鈴 木 
鈴 木 
鈴 木 
鈴 木 
鈴 木 
0 販 売 数 量 0 販 売 価 格 ( 単 価 ) 0 販 売 価 格 ( 合 価 ) 0 ・ 
250 
500 
420 
420 
90 
360 
250 
1 ′ 250 
250 
750 
90 
540 
420 
2 ′ 940 
90 
270 
420 
1 ′ 680 
250 
500 
420 
2 ′ 940 
90 
720 
250 
500 
250 
1 ′ 000 
90 
270 
420 
1 ′ 680 
90 
900 
420 
1 ′ 260 
250 
1 ′ 000 
420 
840 
90 
630 
250 
2 ′ 000 
250 
500 
90 
360 
420 
4 ′ 200 
90 
180 
420 
420 
250 
1 ′ 000 
420 
2 ′ 100 
90 
270 
250 
3 ′ 750 
35 ′ 730

同じように”数量の合計”も集計できるし、”鈴木の数”も集計できます。これが単純集計です。

クロス集計

それに対してクロス集計とは、集計項目を2つ以上かけ合わせる(クロスさせる)集計方法です。

例えば上のデータを例にすると、”商品名”と”担当者”という項目で”販売金額(合価)”を集計するとしたらそうすればよいでしょうか?

ピボットテーブルが使えなければ、”まずバナナでフィルターをかけて、次に担当者で一人ずつフィルターをかけて”といった煩雑な作業が発生します。

それがピボットテーブルを使えば、以下のようなアウトプットを一瞬で出すことができます

合 計 / 販 売 価 格 ( 合 価 ) 列 ラ へ ル 
行 ラ へ ル 
, 山 田 
い ち ご 
5 ′ 040 
チ ョ コ レ - ト 
1 靆 70 
2200 
8 ′ 710 
田 中 
6 20 
1 90 
3 0 
11 ′ 610 
鈴 木 
6 20 
1 ′ 440 
7 50 
15 ′ 410 
総 計 
18 ′ 480 
4200 
12 50 
35 ′ 730

いかがでしょうか?合計金額の内、”山田さんが販売したいちごの合計金額”や、”鈴木さんが販売したバナナの数”のような、かなり細かい集計ができるようになりました。

クロス集計は、データ分析で大活躍する手法なのです。

ピボットテーブルを使ってみよう

ではここから、実際にピボットテーブルを使う手順を照会します。

上述の元データはテーブルが設定されていたので、一度解除してから進めます。

まずは、クロス集計をしたいデータの範囲を選択します。この時注意すべきなのは、”商品名”や”担当者”と書いてあるラベル行も含むことです。

範囲を選択したら、”挿入”タブの一番左にある”ピボットテーブル”をクリックします。

ホ ー ム 
0 
お す す め 
テ ー プ ル ビ ボ ッ ト テ ー プ ル 
テ ー プ ル 
ビ ホ ッ ト テ ー プ ル 
数 式 デ ー タ 校 問 
方 イ ル 
ピ ボ ッ ト 
挿 入 
テ ー プ ル 
ペ ー ジ レ イ ア ウ ト 
画 像 オ ン ラ イ ン 図 形 ア イ コ ン 
画 像 
表 示 
問 発 へ ル プ 
ア ド イ ン を 入 手 
0 
3D モ デ ル , 
■ SmartArt 
ス ー ン シ ト , 
Visio 
個 人 用 ア ド イ ン ヤ 
Visu 
人 
商 品 名 
複 雑 な デ ー タ を ピ ボ ッ ト テ ー プ ル に 簡 
単 に 配 匿 し 隻 計 し ま す 。 
参 考 : 値 を ダ カ ル ク す る と 、 ど の 
値 が 合 計 に 含 ま れ る か を 確 認 す る こ と 
が て き ま す 。 
② 詳 細 情 報 
者 販 売 数 量 
販 売 価 格 ( 単 価 ) 販 売 価 格 ( 合 価 ) 
250 
500 
420 
420

そうすると、以下のようなウィンドウが出てきますので、”OK”をクリックします。

販 売 単 販 売 
担 山 山 山 山 山 山 山 山 山 田 新 田 田 田 田 新 田 田 田 田 爺 爺 鈴 鈴 鈴 爺 爺 鈴 鈴 希 爺 
250 
ビ ホ ッ ト テ - プ ル の 作 成 
分 析 す る テ - タ を 選 択 し て く だ さ い を 
250 
◎ テ ー プ ル ま た は 範 囲 を 択 ( 印 
420 
テ - プ ル / 範 回 に ) 新 1 ! 田 $ 2 : $ F$ お 
0 外 部 デ ー タ ソ ー ス を 使 用 但 ) 
420 
250 
接 続 の 択 ( C ) … 
420 
0 こ の 秀 ク の デ ー タ モ テ ル を 使 用 す る ( D ) 
250 
250 
ど ホ ッ ラ ー プ ル レ ポ ー ト を 配 ま す る を 所 を 澱 択 し て く た ぞ い . 
◎ 新 規 ワ - ク シ - ト 間 
420 
〇 既 存 の ワ - ル - t'(k) 
420 
場 所 ( い 
250 
複 数 の テ - プ ル を 分 析 す る か ど う か を 択 
発 20 
ロ こ の デ ー タ を デ ー タ モ デ ル こ 這 加 す る 住 ) 
250 
250 
キ ャ ン セ ル 
0 
420 
250 
420 
250

そうすると、以下のように新たなシートが追加されます。これでピボットテーブルを使う準備は完了です。

この画面は大きく2つのパートから構成されています。

下記の①はフィールドで、②はレポートです。

方 イ ル ホ ー ム 捧 入 ペ ー ジ レ イ ア ウ ト 数 式 
デ ー タ 
校 問 
表 示 
問 発 
し プ 分 析 
デ ザ イ ン 
第 要 第 
ビ ボ ッ ト テ ー プ ル 名 : ア ク テ イ プ な フ ィ ー ル ド : 
「 ビ ボ ッ ト テ ー カ 
選 択 ビ ボ ッ ト テ ー プ ル フ ィ ー ル ド / ア イ テ 
ビ ボ ッ ト グ ラ フ お す す め フ ィ ー ル + / - フ ィ ー ル ド 
ス ラ イ サ ー タ イ ム ラ イ ン 
更 新 デ ー タ ソ ー ス 
品 オ プ ン , 
ビ ボ ッ ト テ - プ ル ド リ ス ト ボ タ ン の 見 出 し 
の 入 
の 入 
ビ ボ ッ け ー プ ル 
ア ク テ イ プ な フ ィ ー ル ド 
グ ル ー プ 
フ 有 の 一 
ア ク シ ョ ン 
ツ ー ル 
ビ ボ ッ ト テ ー ブ ル の フ ィ ー ル ド 
1 2 3 4 5 6 7 8 9 
レ ポ ー ト に 追 加 す る フ ィ ー ル ド を 選 択 し て く た 港 い : 
ロ 商 品 名 
ロ 担 当 者 
ビ ボ ッ ト テ ー フ 、 ル 4 
ロ 販 売 数 星 
ロ 販 売 価 格 ( 単 価 ) 
ロ 販 売 価 格 ( 合 価 ) 
レ ポ ー ト を 作 成 す る に は 、 に ボ ッ ト テ ー プ ル 
そ の 他 の テ ー プ ル .. 
の フ ィ ー ル ド リ ス ト ] か ら フ ィ ー ル ド を 選 択 し 
て く た さ い 
0 1- フ 」 「 0 4 ・ 5 ) 一 / 8 0 1- フ 」 「 0 
次 の ボ ッ ク ス 問 で フ ィ ー ル ド を ド カ グ し て く た 港 い : 
フ ィ ル タ ー 
行 
ロ レ イ ア ウ ト の 更 新 を 保 す る 
Sheet3 
Sheetl

①のフィールドでは、元のデータをどのように集計するかを決めます。その結果として②にレポートが作成されます。

例えばフィールドのうちの行に”商品名”、列に”担当者”、Σ値に”販売価格(合価)”を投入すると、以下のようなレポートを得られます。

ビ ボ ッ ト テ - ブ ル の フ ィ - ル ド 
レ ポ - ト に 這 加 す る フ ィ - ル ド を 選 択 し く だ さ い : 
合 計 / 販 売 価 格 ( 合 価 ) 列 ラ へ ル 
Q] 商 品 名 
行 ラ へ ル 
田 中 鈴 木 総 計 
山 田 
Q] 担 当 
ロ 販 売 数 星 
い ち ご 
5040 
6720 
6720 18480 
ロ 販 売 価 格 ( 単 価 ) 
Q] 販 売 価 格 ( 合 価 ) 
チ ョ コ レ ー ト 
1170 
1890 
1440 4500 
そ の 他 の 丁 - プ ル .. 
バ ナ ナ 
2500 
3000 
7250 12750 
8710 11610 15410 35730 
0 一 1 2 一 3 一 4 一 5 一 6 一 7 一 8 一 9 一 0 一 1 
次 の ホ ッ ク ス 誾 で フ ィ - ル ド を ド プ グ し く だ さ い : 
フ ィ ル タ 
担 当 者 
行 
合 計 / 販 売 価 格 ( 合 価 )

この時の注意しないといけないのは、”値のフィールド設定”です。Σ値は、必ずしもデータに含まれる数値を合計したものが表示されるわけではありません。

例えば以下のように”個数“と表記があるときは、データの数を合計します。

上の例でいえば、金額の合計は35,730ですが、データの数としては31です。集計結果が大きく異なるので、気を付けてください。

値のフィールド設定を変更したい時は、Σ値のフィールドに入っているラベル(以下で言うと”個数 / 販売価格(合価)”)をクリックし、”値のフィールド設定”をクリックします。

X 
値 に 移 動 
フ ィ ー ル ド の 削 除 
値 フ ィ ー ル ド の 設 定 ) .. 
個 数 / 販 売 価 格 ( 合 価 )

そうすると以下のウィンドウが出てきますので、ここから必要な計算の種類を選択して、”OK”を押してください。

値 フ ィ - ル ド の 定 
ソ - ス 名 販 売 価 格 ( 合 価 ) 
名 前 の 指 定 ( 0 : 合 計 / 販 売 価 格 ( 合 価 ) 
計 方 法 計 算 の 類 
値 フ ィ - ル の 計 に ) 
計 に 用 す る 計 算 の 類 を 選 択 し て く だ さ い 
選 択 し た ア - ル の デ - タ 
個 数 
表 示 形 式 住 ) 
キ 11 ン セ ル

なお、この値のフィールド設定は、元データに空白がなければデフォルトで”合計”が設定されますが、空白があれば”個数”が設定されます。

コメント

タイトルとURLをコピーしました