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

LINEで送る
Pocket

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

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

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

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

pivot001

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

pivot002

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

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

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

データ セルの書式 空白セル
数量A 数量 なし
数量B 数量 あり
数量C 文字列 なし
数量D 文字列 あり

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

 

検証1:空白セルの有無

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

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

pivot007

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

 

検証2:セルの書式

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

pivot0009

  pivot008

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

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

 

 まとめ

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

 

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

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

 

 

 

 

《Asu》カンタンExcelツール化②〜ピボットテーブルのデータ更新を自動化する | アスノート AsuNote.jp

 

LINEで送る
Pocket