【Excel】ピボットテーブル 応用編

Excel 基本動作

前回はピボットテーブルの入門的な内容を紹介しました。前回の内容は以下からご覧ください。

今回は、ピボットテーブルを何とか使えるようになった人向けに、ピボットテーブルの応用編として、オススメ機能を紹介します。

スポンサーリンク

更新

まずは更新機能です。

ピボットテーブルに使用している元データが変更されたときに、稀に一度ピボットテーブルのシートを削除して再度ピボットテーブルを挿入している人がいます。それでも間違ってはいないですが、もっと簡単に作業することができます。

今回も入門編と同じデータを使いましょう。以下のデータに対してピボットテーブルを挿入し、

販 売 単 販 売 担 山 山 山 山 山 山 山 山 山 田 新 田 田 田 田 新 田 田 田 田 爺 爺 鈴 鈴 鈴 爺 爺 鈴 鈴 希 爺 250 250 420 420 250 420 250 250 420 420 250 発 20 250 250 0 420 250 420 100 270 250

以下のようなレポートを作成しました。

合 計 / 販 売 価 格 ( 合 価 ) 列 ラ へ ル 行 ラ へ ル , 山 田 い ち ご 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

この時、元データに対して、以下のように受注番号の列を加えたとします。この時、ピボットテーブルのレポートにも受注番号の情報を加えるにはどうしたらいいでしょうか？

山 田 250 500 山 田 420 420 山 田 90 350 山 田 250 1250 山 田 250 750 山 田 90 540 山 田 420 2940 山 田 90 270 山 田 420 1580 250 500 田 中 S- 11 420 2940 90 720 250 500 250 1000 90 270 田 中 S -15 420 1580 90 900 田 中 S -18 420 1250 250 1000 田 中 S -20 420 90 530 250 2000 250 500 90 350 鈴 木 S -25 420 4200 90 180 鈴 木 S -27 420 420 250 1000 鈴 木 S -29 420 2100 鈴 木 C -30 90 270 250 3750

正解は、”更新“機能を使うことです。以下のように、ピボットテーブルの”分析”タブの”更新”を押してください。このピボットテーブルのタブは、ピボットテーブルがアクティブになっていないと表示されません。表示されていない時は、まずはピボットテーブルのレポート部分をクリックしてみましょう。

目 動 保 存 ・ 三 う Bookl ー ( フ ァ イ ル ホ ー ム 入 ビ ボ ッ ト テ ー プ ル 名 : ア 行 イ プ な フ ィ ー ル ド : 合 計 / 販 売 価 格 ( 合 価 ) ペ ー ジ レ イ ア ウ ト 数 式 デ ー タ 校 問 表 示 問 発 グ ル ー プ D ヘ ル プ ビ ボ ッ ト テ ー ブ ル ッ 分 析 ー ル ど ル ト テ ー プ 品 わ シ ョ ン , ビ 和 ト テ ー プ ル D3 心 1 行 ラ ベ ル い ち ご チ ョ コ レ - ト ア ク テ イ プ な フ ィ ー ル ド A ス ラ イ サ ー タ イ ム ラ イ ン の 入 0 入 フ ィ 】 み ー E 総 計 1848 切 4500 1275 び 35730 F 実 行 し た し 胙 業 を 入 力 し て く た 港 い い 第 を 要 選 択 ビ ボ ッ ト テ ー ブ ル フ ィ づ 更 新 一 デ - タ ソ - ス グ 〃 G ア ク シ ョ ン H B 列 ラ ベ ル 0 匚 山 田 5040 1170 2500 8710 C I 田 中 鈴 木 6720 1890 3000 11610 6720 1440 7250 15410

すると、以下のように、フィールドの選択肢に”受注番号”が新たに追加されました。これを”行”フィールドに追加すると、

ビ ボ ッ ト テ - ブ ル の フ ィ - ル ド レ ポ - ト に 造 加 す る フ ィ - ル ド を 選 0 択 し く だ さ い : Q] 商 品 名 ロ 受 注 番 号 担 当 ロ 販 売 数 邑 ロ 販 売 価 格 ( 単 価 ) 団 販 売 価 格 ( 合 価 ) そ の 他 の 丁 - プ ル ..

以下のように、それぞれの商品に該当する受注番号が見れるようになりました。

10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 38 39 S-11 s 16 s 18 s 20 s 25 S 27 s 29 c 12 c 15 C 17 c 21 c 24 c 26 c 30 BI B 10 B 13 B 14 B 19 B 22 B 23 B 28 B 31 5040 420 2940 1680 1170 360 540 270 2500 500 1250 750 8710 6720 2940 1680 1260 1890 720 270 900 3000 500 500 1000 1000 11610 6720 4200 420 2100 1440 630 360 180 270 7250 2000 500 1000 3750 15410 18480 2940 1680 1260 420 840 4200 420 2100 2940 1680 4500 720 270 900 630 360 180 360 270 540 270 12750 500 500 500 1000 1000 2000 500 1000 3750 1250 750 35730

ここまでが、更新機能でした。

スポンサーリンク

フィルター

次はフィルター機能です。様々な方法で、レポートに表示される内容を絞ってみましょう。

5,040 420 2,940 1,680 1,170 360 540 270 2,500 500 1,250 750 8,710 6,720 2,940 1,680 1,260 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 38 39 40 fi-7XJb S 11 s 16 s 18 s 20 s 25 S 27 s 29 c 12 c 15 C 17 c 21 c 24 c 26 c 30 B 10 B 13 B 14 B 19 B 22 B 23 B 28 B 31 ı, 840 890 720 270 900 3,000 500 500 1,000 1,000 11,610 6,720 4,200 420 2,100 1,440 630 360 180 270 7,250 2,000 500 1,000 3,750 15,410 18,480 2,940 1,680 1,260 420 840 4,200 420 2,100 2,940 1,680 4,500 720 270 900 630 360 180 360 270 540 270 12,750 500 500 500 1,000 1,000 2,000 500 1,000 3,750 1,250 750 35,730

上のレポートでは、行で商品名と受注番号が見えますが、長すぎて見にくいので、一時的にいちごとバナナの情報だけを見たいとします。全体の金額は保持した上で、チョコレートだけを折りたたみましょう。以下のチョコレートの左横にある”-“をクリックすると折りたため、その後”+”を押すと、再び広げることができます。

14 15 16 17 18 19 2,940 1,680 1,170 5-Ξ2Ιι—Η c-12 c-15 1,890 720 270 , Ll-.ll-J 2,940 1,680 4,500 720 270
折り畳み前
10 11 12 13 15 16 17 18 19 20 21 22 23 24 25 26 28 29 fi-7XJb S 11 s 16 s 18 s 20 s 25 S 27 s 29 B 10 B 13 B 14 B 19 B 22 B 23 B 28 B 31 5,040 420 2,940 1,680 1,170 2,500 500 1,250 750 8,710 6,720 2,940 1,680 1,260 1,890 3,000 500 500 1,000 1,000 11,610 6,720 4,200 420 2,100 1,440 7,250 2,000 500 1,000 3,750 15,410 18,480 2,940 1,680 1,260 420 840 4,200 420 2,100 2,940 1,680 4,500 12,750 500 500 500 1,000 1,000 2,000 500 1,000 3,750 1,250 750 35,730
折り畳み後

ちなみに、まとめて折り畳むこともできます。以下の”分析”タブから”フィールドの折りたたみ”を押しましょう。

目 動 保 存 ビ ホ 針 、 テ - プ ル 名 目 グ ル - プ 解 ど ホ ッ ト テ - プ ル 4 品 オ プ シ ョ ン ・ ビ ホ 針 、 テ - プ ル A5 S- 11 S -15 S -18 S 25 方 イ ル ホ - ム 挿 入 ペ - ジ レ イ ア ウ ト ア ク テ イ プ な フ ィ - ル ド : 商 名 フ ィ - ル ド の 設 定 数 式 デ - タ 校 閂 フ ィ - ル 切 展 問 フ ィ - ル ド の 折 り た た み A ア ク テ イ プ な フ ィ - ル ド B C 表 示 問 発 へ ル プ → ク ル - プ の 選 択 グ ル - プ 18 ′ 480 2 ′ 940 1 ′ 580 1 ′ 250 420 4 ′ 200 ビ ホ ッ ト テ - プ ル ツ - ル テ ザ イ ン 分 析 ス ラ イ サ - タ イ ム ラ イ ン の 挿 入 の 挿 入 フ ィ ル タ - G 合 計 / 販 売 価 格 ( 合 価 ) 列 ラ へ ル 行 ラ へ ル 三 い ち ご 10 を 山 田 5 ′ 040 420 6 ′ 720 2 ′ 940 1 ′ 580 1 ′ 250 840 D 6 ′ 720 4 ′ 200
折り畳み前
方 イ ル ホ - ム 挿 入 ペ - ジ レ イ ア ウ ト 数 式 デ - タ 校 閂 表 示 問 発 へ ル プ ど ホ 針 、 テ - プ ル 名 ア ク テ イ プ な フ ィ - ル ド : → グ ル - プ の 選 択 フ ィ - ル 切 展 問 目 グ ル プ 解 ど ホ ッ ト テ - プ ル 4 商 名 フ ィ - ル ド の 折 り た た み 品 オ プ シ ョ ン ・ フ ィ - ル ド の 設 定 ビ ホ 針 、 テ - プ ル ア ク テ イ プ な フ ィ - ル ド グ ル - プ A5 A C D E 合 計 / 販 売 価 格 ( 合 価 ) 列 ラ へ ル を 山 田 行 ラ へ ル + い ち ご 5 ′ 040 6 ′ 720 6 ′ 720 18 ′ 480 - ト 1 ′ 170 1 ′ 890 1 ′ 440 4 ′ 500 2 ′ 500 3 ′ 000 乙 250 12 ′ 750 8 ′ 710 11 ′ 610 15 ′ 410 35 ′ 730
折り畳み後

次は、そもそもチョコレートをレポートから外したい時です。

そういった場合は、以下のプルダウンから見たい情報だけを選択します。(チョコのチェックを外す)

合 計 / 販 売 価 格 ( 合 行 ラ へ ル フ ィ - ル ド の 選 択 : 昇 願 に ) 願 ( 0 ) そ の 他 の 並 べ 替 え オ プ シ ョ ン 但 ) ・ ・ ・ 倍 品 名 か ら フ ィ ル タ ー を ク リ ア ( 0 ラ ベ ル フ ィ ル タ ー ( L ) 値 フ ィ ル タ ー ( Y ) マ ( す べ て 選 択 ) ー マ い ち ご ー マ バ ナ ナ OK 田 40 20 80 70 50 キ 11 ン セ ル 40 田 中 6 ′ 720 2 ′ 940 1 ′ 580 1 ′ 260 840 1 ′ 890 720 270 900

そうすると、以下のようにチョコのデータを外すことができます。

65-'\Jb s-11 S-16 s-18 S-20 S-25 S-27 S-29 8-10 8-13 8-14 8-19 8-22 8-23 8-28 8-31 5,040 420 2,500 1,250 7,540 6,720 1,260 3,000 9,720 6,720 4, 200 420 2,100 7,250 2,000 1,000 3,750 13,970 18,480 1,260 420 4,200 420 2,100 12,750 2,ооо 1,ппп 3,750 1,250 31,230

ドリルダウン

次はドリルダウンです。これは、レポートに表示されているデータの詳細を知りたい時に便利な機能です。

例えば以下のレポートのいちごの合計金額である”18,480″の詳細が知りたい時は、そのセルをダブルクリックします。

合 計 / 販 売 価 格 ( 合 価 ) 列 ラ ベ ル を 行 ラ へ ル 凵 チ ョ コ レ ー ト を 山 田 田 中 鈴 木 総 計 5 ′ 040 1 ′ 170 2 ′ 500 8 ′ 710 6 ′ 720 1 ′ 890 3 ′ 000 11 ′ 610 6 ′ 720 1 440 7 ′ 250 15 ′ 410 18 ′ 480 4 ′ 500 12 ′ 750 35 ′ 730

そうすると、新しいシートが追加され、そこに”18,480″の中身が表示されます。

■ 受 注 番 号 ■ 担 当 者 ■ 販 売 数 量 ■ 販 売 価 格 ( 単 価 ) ■ 販 売 価 格 ( 合 価 ) 品 名 い ち ご 中 中 中 田 中 木 木 木 田 田 田 田 田 山 田 鈴 鈴 鈴 山 山 S- 11 420 2940 い ち ご S -15 420 1580 い ち ご S -18 420 1250 い ち ご 420 420 420 い ち ご S -25 420 4200 S -27 420 420 い ち ご 420 2100 420 2940 い ち ご 420 1580

レポートのレイアウトをカスタマイズ

ピボットテーブルというと、特定のレイアウトでしか作れないと思っている人がいますが、実は様々なレイアウトを選べるようになっています。レイアウトは、”デザイン”タブの”レイアウト”から変更可能です。

ビ ホ ッ ト テ - プ ル ツ - ル 目 動 保 存 方 イ ル ホ - ム 挿 入 ペ - ジ レ イ ア ウ ト 数 式 デ - タ 校 閂 表 示 問 発 ヘ ル プ デ ザ イ ン 分 析 ■ 国 題 ■ Q] 行 見 出 し ロ 縞 模 様 ( 行 ) 小 計 卞 レ ポ - レ 空 白 行 Q] 列 見 出 し ロ 縞 模 様 ( 列 ) ビ ホ 針 、 テ - プ ル ス タ イ ル の オ プ シ ョ ン レ イ ア ウ ト A B C D 合 計 / 販 売 価 格 ( 合 価 ) 列 ラ へ ル を 山 田 行 ラ へ ル 三 い ち ご 5 ′ 040 6 ′ 720 6 ′ 720 18 ′ 480 S- 11 2 ′ 940 2 ′ 940 S -15 1 ′ 580 1 ′ 580 S -18 1 ′ 250 1 ′ 250 420 420

今日紹介するのは３つで、”小計”から選択できる①”小計を表示しない“レイアウトと、”レポートのレイアウト”から選択できる②”表形式で表示“、それから③”アイテムのラベルをすべて繰り返す“です。

動 保 存 ( 三 ) 方 イ ル ホ ー ム 挿 入 ペ ー ジ レ イ ア ウ ト ■ 劇 ■ マ 行 見 出 し ロ 小 計 総 計 レ ポ ー レ 空 白 行 列 見 出 し ロ イ ア ウ ト ャ 小 計 を 表 示 し な い ( P ) ■ す へ て の 小 計 を グ ル ー プ の 末 尾 に 表 示 す る 但 ) 日 す へ て の 小 計 を グ ル ー プ の 先 頭 に 表 示 す る 仼 ) , フ ィ ル タ ー さ れ た ア イ テ ム を 合 計 に 含 め る ( )
自 動 保 存 ( 三 ) 方 イ ル ホ ー ム 挿 入 ペ ー ジ レ イ ア ウ ト 数 式 デ ー タ ■ 住 行 見 出 し ロ 縞 模 様 ( 行 ) 小 計 総 計 レ ポ ー レ 空 白 行 ⅵ 列 見 出 し ロ 様 ( 列 ) イ ア ウ ト , 目 コ ン バ ク ト 形 式 で 表 示 ( ( ) ア ウ ト ラ イ ン 形 式 で 表 示 ( 0 ) 2 3 4 5 6 7 8 表 形 式 で 表 示 仼 ) 合 計 / 行 ラ ベ ル ■ ア イ テ ム の ラ ベ ) 陸 す べ て 縵 り 返 す 但 ) ョ い ち こ ア イ テ ム の ラ ベ ル を 縵 り 返 さ な い 但 )

小計を表示しない

このレイアウトが有効な例を考えてみましょう。

例えば上のレポートに、受注年の情報が追加されたとします。それを列のフィールドに投入すると、以下のようなレイアウトになります。

この時、小計の表示を無くすことで、もっとスリムなレポートにすることができます。

合 計 / 販 売 各 ( 合 価 ) 行 ラ ベ ル ラ ベ ル 三 山 出 山 出 隼 計 三 田 中 2017 2 ′ 940 720 500 4 ′ 160 田 中 隼 計 6 ′ 720 890 3 ′ 000 11 ′ 510 - 鈴 木 2018 630 630 鈴 木 隼 計 計 2017 5 ′ 040 1 ′ 170 2 ′ 500 8 ′ 710 5 ′ 040 1 ′ 170 2 ′ 500 8 ′ 710 2018 3 ′ 780 1 ′ 170 2 ′ 500 7 ′ 450 2019 4 ′ 520 540 3 ′ 500 8 ′ 650 2020 2 ′ 1 開 270 3 ′ 750 6 ′ 120 6 ′ 720 1 ′ 440 ん 250 15 ′ 410 18 ′ 480 4 ′ 500 12 ′ 750 35 ′ 730

具体的には以下のようになります。

合 計 / 販 売 各 ( 合 価 ) 行 プ 、 ル ラ ベ ル 三 山 田 2017 5 ′ 040 1 ′ 170 2 ′ 500 8 ′ 710 2017 2 ′ 940 720 500 4 ′ 160 2018 3 ′ 780 1 ′ 170 2 ′ 500 7 ′ 450 2018 630 630 2019 4 ′ 520 540 3 ′ 500 8 ′ 650 2020 2 ′ 1 開 270 3 ′ 750 6 ′ 120 18 480 4 ′ 500 12 ′ 750 35 ′ 730

表形式で表示

このレイアウトが有効な例を考えてみましょう。

上のレポートのフィールドを変更し、行フィールドに受注年=>担当者=>商品名を投入するとします。この時、赤い部分などで無駄な改行が多く、縦長で見にくいレポートだと感じませんか？(小計は非表示としています)

, 合 計 / 販 売 価 格 ( 合 価 ) 2017 5 ′ 040 王 い ち ご 1.170 ー ト チ ョ コ レ 田 ム 500 , 田 バ ナ ナ 三 田 中 2.94d 王 い ち ご 720 、 50d 田 バ ナ ナ ョ 2018 三 田 中 3 780 田 い ち ご 1 ′ 170 。 ー ト 田 チ ョ コ レ 2.500 田 バ ナ ナ 三 鈴 木 630 ー ト 田 チ ョ コ レ 2019 4 , 620 ' 王 い ち ご 540 ? ー ト チ ョ コ レ 3 50b 田 バ ナ ナ 2020 ヨ 三 鈴 木 2.10b 田 い ち ご 270 3 ′ 750 田 バ ナ ナ 35 フ 30

こういったレポートを表形式で表示すると、以下のように見やすくなります。

受 注 年 ロ 合 計 / 販 売 価 格 ( 合 価 ) を 担 当 者 三 2017 三 山 田 三 2018 三 田 中 三 鈴 木 三 2019 三 鈴 木 三 2020 を 商 品 名 を 受 注 番 号 + い ち ご 王 チ ョ コ レ - ト 王 い ち ご 王 チ ョ コ レ - ト 王 い ち ご 王 チ ョ コ レ - ト 王 チ ョ コ レ - ト 王 い ち ご 王 チ ョ コ レ - ト 王 い ち ご 王 チ ョ コ レ - ト 5 ′ 040 1 ′ 170 2 ′ 500 2 ′ 940 720 500 3 ′ 780 1 ′ 170 2 ′ 500 630 4 ′ 620 540 3 ′ 500 2 ′ 100 270 3 ′ 750 35 ′ 730

こういったレイアウトのレポートを、Excelのようなデータテーブルにしたい時に有効なのが、次に紹介する”アイテムのラベルをすべて繰り返す”レイアウトです。

アイテムのラベルをすべて繰り返す

このレイアウトは、全項目の”表形式で表示”と組み合わせて使うことで便利になりますので、全項目の続きとして書いていきます。

表形式で表示した状態で”アイテムのラベルをすべて繰り返す”を適用すると、以下のようになります。

11 フ 一 つ 0 ー 5 6 7 ・ 9 受 注 年 を 担 当 者 を 商 品 名 を 受 注 番 号 ロ 合 計 / 販 売 価 格 ( 合 価 ) + い ち ご 三 山 田 三 2017 5 ′ 040 山 田 王 チ ョ コ レ - ト 2017 1 ′ 170 山 田 2017 2 500 王 い ち ご 三 田 中 2017 2 ′ 940 田 中 王 チ ョ コ レ - ト 2017 720 田 中 2017 500 王 い ち ご 三 2018 三 田 中 0 11 フ 一 つ 0 ー 5 6 7 ・ 9 0 3 ′ 780 2018 田 中 王 チ ョ コ レ - ト 1 ′ 170 2018 田 中 2 500 三 鈴 木 王 チ ョ コ レ - ト 2018 630 三 鈴 木 王 い ち ご 三 201 4 ′ 620 鈴 木 王 チ ョ コ レ - ト 2019 540 鈴 木 2019 3 500 三 202b 三 鈴 木 王 い ち ご 2 ′ 100 鈴 木 王 チ ョ コ レ - ト 2020 270 鈴 木 2020 3 ′ 750 35 ′ 730

今は小計を非表示にしていますが、小計を再度表示すると以下のようになります。

受 注 年 を 担 当 者 を 商 品 名 を 受 注 番 号 ロ 合 計 / 販 売 価 格 ( 合 価 ) + い ち ご 三 2017 三 山 田 5 ′ 040 2017 山 田 王 チ ョ コ レ - ト 1 ′ 170 2017 山 田 2 50b 2017 山 田 集 計 8 ′ 710 王 い ち ご 2017 三 田 中 2 ′ 94b 2017 田 中 王 チ ョ コ レ - ト 72b 2017 田 中 0 。 1 フ 一 つ 0 ・ 5 6 「 / 一 0 ノ 0 。 1 フ 一 つ 0 ・ 5 6 「 / 一 0 ノ 一 0 500 2017 田 中 集 計 4 ′ 160 2017 集 計 12 ′ 870 + い ち ご 三 2018 三 田 中 3 ′ 780 2018 田 中 王 チ ョ コ レ - ト 1 ′ 170 2018 田 中 2 50b 2018 田 中 集 計 7 ′ 450 2018 三 鈴 木 王 チ ョ コ レ - ト 630 2018 鈴 木 集 計 630 2018 集 計 8 ′ 080 三 2019 三 鈴 木 + い ち ご 4 ′ 620 2019 鈴 木 王 チ ョ コ レ - ト 540 2019 鈴 木 3 50b 2019 鈴 木 集 計 8 ′ 660 2019 集 計 8 ′ 660 三 2020 三 鈴 木 + い ち ご 2 ′ 100 2020 鈴 木 王 チ ョ コ レ - ト 27b 2020 鈴 木 3 ′ 75b 2020 鈴 木 集 計 6 ′ 120 2020 集 計 6 ′ 120 35 ′ 730

以上見てきたように、ピボットテーブルのレポートはレイアウトを様々変更できます。TPOにあった形式を自分で選択することで、見やすく、わかりやすい表を作成しましょう。

コメント

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