【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をコピーしました