Excel使いこなしの基礎と応用

ピボットテーブル|クロス集計表の列・行に集計アイテムを追加する

列と行の2軸に配置したフィールドそれぞれに集計アイテムを追加して、一つのピボットテーブルで2方向からの分析を可能にする提案例です。企画書などで少し小洒落たコンパクトな見せ方をしたい時に参考にして下さい。

目次


「行」「列」ごとに集計アイテムを作成

▶集計アイテムを挿入する基本手順を実例で解説 からの続編です。

スポンサーリンク

前記事で、行ラベルに配置した「商品」フィールドの集計アイテムを12作りました。構成比と基になる総計が、同種の商品ごとに表示されています(下図の赤い枠内)。
行エリアに追加された集計アイテム
ピボットテーブルのフィールド」作業ウィンドウで、列エリアに「担当」フィールドを配置します。
列エリアに「担当」フィールドを配置
「担当」フィールドか項目の一つを選択して、「ピボットテーブル ツール」ー「分析」の「計算方法」グループにある「フィールド/アイテム/セット▼」を押して「集計アイテム」をクリック。
フィールド/アイテム/セットの「集計アイテム」をクリック
”担当”への集計アイテムの挿入」ダイアログボックスが開いたら、担当者全員の売上総計の「名前」と「数式」を入力します。この手順は「商品」に追加した集計アイテムと同じです。名前:担当の総計数式:=担当者A +担当者B +担当者C
入力が完了したら、「追加」ボタンをクリック。
担当の総計を表示する集計アイテムを構築
そのままダイアログボックスを閉じずに、続けて「構成比」の名前と数式を入力。
名前:A構成比数式:=担当者A/担当の総計
「担当者A」の構成比を表示させる集計アイテムを構築
「追加」を押したら、続けて「B構成比」入力→「追加」、「C構成比」→入力「OK」。
これで列ラベルの集計アイテムも全て表示できました。
ピボットテーブルの行と列に集計アイテムを追加
この時点では何が何だかわからない集計表になってます。意味のない集計値も目につきますね。この見栄えを最小限の書式で整えていきましょう。

書式でメリハリをつけて見やすく整える

「総計」の範囲を色分けして目立たせるとともに、項目別の売上金額と構成比を隔てる仕切り線の役割を持たせます。罫線も適用してみましたが、この辺りは好みの範疇で。
▶罫線の引き方いろいろ基本
▼クリックで拡大表示
総計のセル範囲を背景色で塗り潰し、罫線を適用
ピボットテーブル作成時点で「ピボットテーブル ツール」ー「デザイン」の「ピボットテーブルスタイル」から「薄い青、ピボットスタイル(淡色)9」を選んであります。
背景色の指定と罫線の適用は「ホーム」タブか「セルの書式設定」ダイアログで作業します。上図の例では「商品」と「担当」の総計欄をそれぞれ別色にし、交差した位置にある全体の総数のセルの色も違えてありますが、単色でも十分です。
▼クリックで拡大表示
総計のセル範囲の背景色を単色にした例
後は、不要な集計値の始末だけです。

不要な集計値を隠す

構成比同士を足し算させてるような邪魔な集計値を隠してしまいましょう。

スポンサーリンク

不要な集計セルは表の右下の一角に並んでいます。
不要な集計値の範囲
これらを全て選択(Ctrlキーを押しながらドラッグ)して、右クリック。
カラーパレットで文字色を白(背景色と同色)にします。
不要な集計値を選択して文字色を背景色と同じにする
これで完成です。
集計アイテムを追加したクロス集計表が完成
クロス集計表に集計アイテムを追加する場合の一例としてご参照下さい。


高機能の表計算ソフトExcelを活用すれば、仕事や私生活で、今あなたが必要とするいろいろなモノが作れます。その作成手順とデザイン、アイデアのヒントまでを可能な限り詳細にわかりやすくナビします。基本操作や関数、グラフに関する解説記事とともにご利用下さい。