Excel業務カイゼンブログ

ピボットテーブルでは様々な集計がとても簡単にできることを、こちらの記事で紹介しました。

ピボットテーブルで集計をしているときに、「こんな集計はどうするのだろう？」と思うこともありますが、そのような時に使える応用テクニックを紹介します。

実際に操作するファイルはこちらからダウンロードできます。

今回のダウンロードするファイルは、こちらの記事で完成したブックを使っています。通して操作することで、より理解を深めることができます。

割合の表示

ダウンロードファイルの「ピボットテーブル１」シートをご覧ください。

すでに販売金額がピボットテーブルで集計されています。

金額は値の合計値を表示しています。

平均やデータ個数、最大値などに変更することもできますが、その集計した値を割合で表すこともできます。

売上金額のいずれかの集計値を右クリックして、［計算の種類］の中の［行集計に対する割合］をクリックします。

一列ごとの集計値に対するそれぞれの割合が求まります。

売上金額のいずれかの集計値を右クリックして、［計算の種類］の中の［計算なし］にして割合表示を元に戻します。

小計と総計の非表示

標準の設定で作成したピボットテーブルには小計が表示される場合があります。

もしも小計を非表示にしたい場合は、小計の項目名を右クリックし、その項目の小計のチェックを外します。この方法を次の手順で操作してみましょう。

まず、ピボットテーブルに［年］での小計を集計します。

ピボットテーブルをクリックして、画面右の［ピボットテーブルのフィールド］の作業ウィンドウの上の項目一覧の中の［年］を下の［行］の［販売日］の上にドラッグします。

年ごとの小計を非表示にします。

ピボットテーブルのセルA5の［2017年］を右クリックし、［"年"の小計］をクリックしてチェックを外します。

年の小計が非表示になりました。

セルE4の［総計］を右クリックし、［総計の削除］をクリックします。

総計列が消えました。

小計の削除は、セル内容が非表示になります。

対して、総計の削除はセル自体がなくなります。

ピボットテーブルの更新

普通の計算式であれば、元の値を変更すると自動で再計算し更新されます。

ピボットテーブルの最も気を付けなければならないことは、この値の自動更新をしないことです。

元の値を変えたら、更新の操作をしなければなりません。

今回のピボットテーブルは、元のデータが「売上一覧」シートを使って集計しています。

今は、2017年の1月の［ノートパソコン］の集計が「3822543」です。

元データである「売上一覧」シートのセルD3の値を「100213844」という大きな値に書き換えましょう。

シート「ピボットテーブル1」をご覧ください。

まだ、1月の［ノートパソコン］の集計は「3822543」のままです。

ピボットテーブルのいずれかのセルを右クリックして、［更新］をクリックします。

1月の［ノートパソコン］の集計が再計算され「103822543」に変更になりました。

変更になった値によっては列の順番も変わります。

ピボットテーブルの元の値を変更したら、必ず更新するようにしましょう。

また、元のデータに行や列を追加したり削除したりすると元データ範囲が変わりますが、この場合も自動で変更になりません。

データ範囲を変更するには、ピボットテーブルをクリックして［ピボットテーブル］ツールの中の［分析］の中にある［データソースの変更］をクリックします。

元データの範囲が指定できますので、指定し[OK]ボタンをクリックします。

年の項目を削除すると

日時で記録されたデータを集計した場合、年、四半期などの項目が自動的に追加される場合があります。

特に年の項目がいらないと思った時に注意しなければならないのですが、年の要素を削除すると、年ごとに仕分けされたものが解除され、同じ１月のデータであれば2017年と2018年が合計されたものが集計されてしまいます。

今の状態では、2017年の1月の［タブレット］の集計は「3900184」、2018年の1月の［タブレット］の集計は「171201」です。

ここから［年］を［行］から削除すると、2017年と2018年の判別がなくなり、合計されて1月の［タブレット］の集計が「4071385」になります。

毎年の月ごとや季節ごとの傾向を集計するなら構わないのですが、それ以外の時は、年を削除すると予想外の答えになってしまうので注意が必要です。

列や行の項目の移動

ピボットテーブルに配置される列や行はドラッグして並べ替えることができます。

B列の［ノートパソコン］とC列の［タブレット］の入れ替えは、セルB4の［ノートパソコン］をクリックして、セルの周りの線をセルC4の右にドラッグし、離します。

ピボットテーブルで求めた値

ピボットテーブルで求めた値の範囲には、以下のようなピボットテーブル特有の設定がされています。

ピボットテーブルの結果

ピボットテーブルで求めたものは、計算式ではなく、ピボットテーブルが求めた数値になっています。

もし、集計した値を書き換えようとしてもエラーが表示され、書き換えることができません。

ピボットテーブルの値の参照

ピボットテーブルの値をセル参照して計算式を作成すると、セル参照ではなく、GETPIVOTDATA関数が入ります。この場合、絶対参照のように１つのセルを固定して参照しようとします。

手入力で、そのセルの場所を「B5」のように手入力すれば、相対参照のセル参照として参照します。

対象データの検証

ピボットテーブルで集計した値に含まれる元データは、その値を右クリックし、［詳細の表示］をクリックすることで、別シートに一覧で表示することができます。

値がおかしいときなどはこれで確認することができます。

この機能は、その値だけを抽出するようなフィルターのような目的でも使えます。

セルD5の［1月］の［デスクトップパソコン］の集計値［2870040］を右クリックし、［詳細の表示］をクリックしてみましょう。

別シートに［1月］の［デスクトップパソコン］の元データのみの一覧が作成されました。

詳細を見たい値をダブルクリックするだけでも、同じ動作ができます。

集計値の追加

商品の販売個数も同時に集計したくなるかも知れません。

合計額や平均値などは、実際にその数値の項目を指定しなければならないのですが、個数を集計する場合は、抜けのない項目であればどの項目でも構わないので［値］にドラッグします。

今回は商品の個数を集計するので、［商品］を［値］にドラッグするのがわかりやすいでしょう。［値］にすでにある［金額］の下にドラッグして配置しましょう。

それぞれの商品、部署で売上金額と同時に商品の販売個数も集計しました。

［商品］は文字データなので、個数をカウントしています。

また、金額の合計の他に金額の平均を同時に集計したい場合もあるでしょう。

その場合は、［金額］も［値］の［商品］の下にドラッグすれば２つ目の合計金額が集計されますので、その後、値の集計方法を平均に変更します。

［値］には複数項目が設定されてもかまいません。

また、同じ項目が［値］に入っても良いのです。［値］に入る項目が多くなると大きな集計表になります。実際に集計する際には見やすくなるように配置するとよいでしょう。

ピボットテーブルのコピー

ピボットテーブルをコピーして、集計表を再利用することもできます。

ピボットテーブルの設定をそのままコピーする場合と、集計された値だけを利用したいときで、方法が違います。

もし、ピボットテーブルの設定をそのままコピーしたいときは、ピボットテーブルの色のついている部分すべてを選択すればコピーができます。

A3からJ18までを範囲選択してコピーし、セルA22に貼り付けてみましょう。

ピボットテーブルがそのまま貼り付きました。

貼り付け先でも上のピボットテーブルとは別のピボットテーブルとして集計することができます。

ピボットテーブルが設定されていると参照した式がGETPIVOTDATA関数になったり、思ったように入力できなかったり、削除などの編集ができないので、扱いにくい場合もあります。そのような時は集計値だけを利用します。

ピボットテーブル全体を選択せず、総計などを選択しない状態でコピーします。貼り付けたものは普通のセルになります。

元のピボットテーブルの総計を除いたA5からJ17の範囲を選択し、セルA41に貼り付けてみましょう。

今度はピボットテーブルではなく普通のセルになりました。色などの書式も貼り付きます。

ピボットテーブルに設定されている並べ替えやフィルターがなくなりましたが、参照時にGETPIVOTDATA関数になったり、入力、編集に制限がかかったりするようなことはありません。

また、ピボットテーブルの全体をピボットテーブルではなく、普通のセルとしてコピーしたいときは、貼り付けるときに［値］で貼り付けをします。

元のピボットテーブル全体A3からJ18までを範囲選択してコピーし、セルA57に［値］で貼り付けてみましょう。

この場合も普通のセルになりました。この場合では、書式は貼り付きません。

次の作業のために、「ピボットテーブル」シートのA3からJ18までを範囲選択してコピーし、新規に「価格ごとの個数」という名前のシートを挿入し、セルA1に貼り付けましょう。

ピボットテーブルのグループ化

ピボットテーブルで集計した項目はグループ化できます。

金額ごとのグループ化することで集計したデータが見やすくなります。

一旦、「価格ごとの個数」シートの［列］［行］［値］の各項目を、「ピボットテーブルのフィールド」からドラッグしてシート上にドロップすることで、すべて削除しましょう。

［行］に［金額］、［値］に［商品］を設定します。

金額ごとの商品個数が集計されます。同じ金額はほとんどないため、ほとんどの個数は１個になっています。

ここで、［金額］のどこかの値を右クリックして、［グループ化］をクリックしましょう。

先ほど大きな値に書き換えた金額以外は150000から300000円の範囲にあります。それを30000円ごとに集計してみましょう。

先頭の値は「150000」、末尾の値は「300000」、単位は「30000」とし、[OK]ボタンをクリックします。

150000円から30000円ごとに販売個数が集計されました。

ピボットテーブルの解除

ピボットテーブルが作成されているエリアは、ピボットテーブルの専用の範囲となります。

一部だけをクリアすることはできず、一部のみ選んでクリアすると次のようなメッセージが表示されます。

ピボットテーブル全体を選択して、［ホーム］タブの［クリア］［すべてクリア］か、Deleteキーでクリアします。