【Excel】ピボットが合計ではなく個数になる時|元データと集計方法を確認

金額を集計したつもりが「個数」になる時は、値フィールドの集計方法と元データの型を確認します。空欄をすべて0へ置き換える必要はありません。未入力と0円を同じ意味にしてよいかを先に判断します。

以下は通常のワークシート範囲やテーブルから作成するピボットテーブルを中心に説明します。データモデルやOLAPの集計では、使用できる設定が異なります。

1. 「個数」が何を数えているかを確認する

数値中心の値フィールドでは合計が使われますが、空白や数値以外の値を含む列を追加すると、初期設定が個数になる場合があります。個数は金額を足す処理ではなく、空でない値の件数を数える処理です。

表示された数字が小さい時は、見出しに「個数」が付いていないか確認します。合計へ変更できても、文字列として保存された金額が自動的に正しい数値へ変わるわけではありません。

2. 空欄と0を区別する

未確定の金額を空欄にしているなら、その意味を残してください。0は金額がゼロと分かっている状態です。平均や件数では、空欄を0で埋めると結果が変わります。

例えば100円、300円、空欄の3行で、入力済み金額の平均は200円です。空欄へ0を入れると、3行の平均は約133.33円になります。合計は同じ400円でも、別の指標を変えてしまいます。

3. 数字に見える文字列を必要な範囲だけ直す

補助列の=ISNUMBER(B2)などで型を確かめます。緑の三角は複数の警告に使われるため、表示があるだけで文字列と決めず、警告の内容も読みます。

金額として数値化すべきセルだけを選び、警告の「数値に変換」などを使います。先頭の0が必要な商品番号や長い識別番号は数値化しないでください。変換前の列を残し、桁や小数点が変わっていないか比較します。

4. 更新後に集計方法を合計へ変える

1. 元データを確認・修正した後、ピボットテーブルを更新します。
2. 対象の値を右クリックして「値フィールドの設定」を開きます。
3. 「値の集計方法」で「合計」を選びます。
4. 必要に応じて表示形式を通貨や桁区切りにします。

既存の集計方法が自動で変わるとは限りません。合計の設定と、元データの取り込み範囲を両方確認します。

5. 小さな範囲で検算する

1つの支店など少数の行を取り出し、元データの合計とピボットの合計を照合します。100、300、文字列の「200」がある例なら、数値化後に600になるかを確認します。400のままなら、200が計算に入っていない可能性があります。

フィルターで除外した行や集計範囲外の追加行も差の原因です。型だけでなく対象期間と対象行をそろえて比較してください。日付や時刻の列は、そもそも合計する指標かを判断します。

6. 元データの意味を保って集計する

集計値が合えば、次回取り込むデータでも同じ型が維持されるか確認します。入力済み件数と未入力件数を別に持つと、合計だけでは気付けない欠損を発見できます。

集計方法の違いはMicrosoftのピボットテーブルでの合計を参照できます。空欄を埋めることを目的にせず、求める指標に合うデータと計算方法を選びます。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】