《Asu》Excelのピボットテーブルで集計が「データの個数」になってしまうときの対処法

仕事で役立つ！Excel活用法シリーズ Excelの使い方を解説したサイトはたくさんありますが、意外と言及されていないのが、『ピボットテーブル』。私のところでは、ピボットテーブルを活用した、業務で活用するExcelツールを作っていますので、そこで出てきた、意外と知らなかったノウハウを紹介していこうと思います。ピボットテーブルの基本や使い方については、こちらのサイトが詳しく説明されています。 Excel豆知識：ピボットテーブル

ピボットテーブルで「データの個数」で集計されてしまう！

さて、今回のテーマは、ちょっとマニアックかもしれません。数値データをピボットテーブルに取り込んだときに、通常は合計値が表示されますが、ときたま、数値の合計ではなく、データの個数になってしまうことがあります。この原因について考察し、対処方法を考えてみました。画面のサンプルはExcel2010ですが、Excel2003でも同様となります。

サンプルとして、以下のようなデータを使ってピボットテーブルを作成します。

会社名と年月を集計キーとして、数量データを追加していきます。すると、数量Ａは正しく数値の合計が表示されましたが、数量Ｂは合計が表示されず、「データの個数」で集計されてしまいました。

同様に、ＡからＤまでの４つのデータを集計表にしたところ、BとDが「データの個数」で集計されてしまいます。

もちろん、右クリック→フィールドの設定から「集計方法」を『合計』に変えることで、数値の合計で集計させることは可能です。この現象の原因は、元データの作り方にあります。もう一度元データをながめてみると、数量Ｂと数量Ｄには、数値データが空白のところがあります。

ここで今回使ったデータの種明かしをします。 Excelがピボットテーブルの集計方法を自動で判別するときに、そのデータが数値なのか、文字列なのかを判断しています。これを検証するために、事前にセルの書式を変更して、数量ＡとＢは通常通り数値データとして、数量Ｃと数量Ｄは文字列に変更しておきました。さらに、数量Ｂと数量Ｄに空白セルを入れました。

データ	セルの書式	空白セル
数量Ａ	数量	なし
数量Ｂ	数量	あり
数量Ｃ	文字列	なし
数量Ｄ	文字列	あり

これで考えてみると、セルの書式が数量か文字列かは、この判断には関係なさそうです。データの中に空白セルがあった場合は、自動でこれを文字列として判定してしまうようです。

検証１：空白セルの有無

空白セルがあると文字列として認識されるのであれば、空白セルにゼロを入力してみます。

データを変更し、一旦ピボットのデータエリアから数量データを外し、ピボットテーブルを更新します。そして、再度数量データをピボットに追加してみます。

すると、書式が数値で、空白セルが入っていた数量Ｂについては、無事に数値の合計で集計されました。ところが、数量Ｄはまだ「データの個数」のままです。データの書式が「文字列」の場合でも、全てのセルに数値が入っていると、Excelは「数値」だと判定するようです。書式が文字列でも数値でも、空白セルがある場合は「文字列」と判定する、ということのようです。

検証２：セルの書式

それでは、数量Ｄのセルの書式を、文字列から数値に変更します。

そして、同じように、一旦数値データをピボットから外して、更新、再度データを追加してみます。

今度はきちんと、数量の合計が集計されました。

まとめ

ピボットテーブルは、集計したいデータの書式を自動で判別し、それにあった集計を自動で行なってくれる便利ツールですが、元データの作り方によっては、思うような動きをしてくれないことがあります。手作業で元データを作るときはもちろんですが、他のシステムからデータを抽出して、それをピボットを使って集計するような場合でも、元データの作成方法には気をつける必要があります。各種のデータを合わせて集計の元データとする場合には、データが無い場合は空白として扱うのではなく、数値のゼロが入るようにしておくと、ピボットテーブルで集計したときの手間を少なくすることが可能になります。ちょっとしたコツを知っておくだけで、利用者（自分の場合もありますが）の作業の手間を減らしてあげることができます。

Excelピボットテーブル関連のこちらの記事もどうぞ：

《Asu》カンタンExcelツール化①〜CSV等の外部ファイルのデータをExcelシートに読み込む方法 | アスノート　AsuNote.jp