【Excel】ピボットの合計・平均・件数を切り替える|個数と数値の個数の違い

ピボットテーブルの売上を「合計」から「平均」へ変えるには、集計結果の数値を右クリックし、「値の集計方法」で平均を選びます。「値フィールドの設定」からも変更できます。

ただし「件数」が何を数えるかは、選んだ列と集計方法によって変わります。空白を含む列で個数を数えても、必ず元表の全行数になるわけではありません。合計・平均・件数を切り替える前に、1行が何を表しているかを確認しましょう。

1. 自動で「個数」が選ばれても集計できないとは限らない

【要点】集計方法を選び直すことと、元データを直すことは分けて考えます。通常のピボットでは、数値のフィールドを値エリアに置くと合計が使われます。空白や文字列などを含む場合は、個数が初期設定になることがあります。

例えば金額列に100、200、空白があれば、空白は「まだ入力していない金額」なのか「売上0」なのかを調べます。個数になったからといって、空白をすべて0で埋める必要はありません。特に平均では、0を足すと分母が増えて結果が変わります。

「合計」を選べない場合も、すぐ元データの汚れと決めつけないでください。OLAPを元にしたピボットなどでは、通常の集計関数を選べない場合があります。データソースの種類と、選択しているフィールドが通常の値フィールドかを先に確かめます。

2. 値フィールドの設定から平均や個数へ変更する

次の手順は、主にWindowsのデスクトップ版で、ワークシート上の表から作った通常のピボットを想定しています。

1. 変更したい値フィールドの集計結果を右クリックします。
2. 「値の集計方法」から「合計」「平均」「個数」などを選びます。
3. 詳しく設定する場合は「値フィールドの設定」を開き、「集計方法」で目的の方法を選びます。
4. 必要に応じて表示名を「平均売上」「注文ID件数」などにします。
5. 表示形式を設定し、1グループの結果を元明細と照合します。

行ラベルや空白のセルを右クリックすると、別のメニューが出る場合があります。売上合計など、実際の集計値を選び直します。右側のフィールド一覧の「値」エリアから対象項目の設定を開く方法もあります。

同じ金額フィールドを値エリアに2回入れれば、片方を合計、もう片方を平均にして並べられます。元の表に平均専用の列を作らなくても比較でき、設定変更前後の数値も確かめやすくなります。

3. 合計・平均・個数が何を返すかを比べる

金額列に数値の100、200、0、未入力の空白、文字の「未確定」が1つずつある例です。通常のピボットの各集計方法では、同じ列でも次のように異なる数値になります。

「個数」は文字も数えるため、ワークシートのCOUNTAに相当する考え方です。「数値の個数」は数値を数えます。どちらも重複を自動で除くわけではありません。

この例の明細は5行ですが、金額列の個数は4です。注文の件数を数えたいなら、全注文に必ず入る注文IDなどを値フィールドに使います。1注文が複数の商品行に分かれていれば、IDの個数も注文数より多くなるため、元表の1行の単位が重要です。

4. 合計や平均が合わないときは欠損と文字列を調べる

数字に見えても文字列として保存されている値は、期待どおりに合計へ入らないことがあります。元表の少数のセルで=ISNUMBER(B2)を使うと、数値として認識されているか確認できます。先頭のアポストロフィ、単位付きの文字、取り込み時の型も調べます。

本来金額である列だけを、確認したうえで数値へ変換します。顧客番号や商品コードの先頭の0を消してまで、表全体を数値へ統一する必要はありません。元データを直したら、ピボットを更新して結果を確認します。

欠損を0へ変える判断は、業務上の意味に合わせます。100と200の2件なら平均150ですが、未入力を0にすると100になります。「空白セルに表示する値」で0を表示する設定と、元表へ実際の0を入れることも異なります。表示だけの変更で元の欠損が解決したと考えないでください。

5. 「集計方法」と「計算の種類」を使い分ける

「集計方法」は合計や平均など、明細をどうまとめるかの指定です。「計算の種類」は、まとめた結果を総計に対する比率などに変える指定です。平均へ変えたいだけなら、比率の設定を探す必要はありません。

例えば東店300万円、西店100万円の売上合計がある場合、総計400万円に対する比率は75%と25%です。金額を残したまま割合も見せるなら、金額フィールドを2回配置し、2つ目の計算の種類を総計に対する比率へ変えます。

以前に比率を設定したフィールドを平均へ切り替えると、意図しない割合表示が残る場合があります。数字が変に見えたら、集計方法、計算の種類、表示形式の3か所を点検します。前月比など基準項目が必要な方法では、比較元の月も確認してください。

6. 重複しない顧客数は通常の個数と分ける

顧客IDがA01、A01、B02なら、空白がない場合の個数は3ですが、顧客の種類は2です。「個数」を選ぶだけではA01が2回数えられます。

データモデルを利用できるWindows環境では、ピボット作成時に「このデータをデータモデルに追加する」を選ぶと、「重複しない値の数」を使用できます。通常のピボットと同じ条件でいつでも出る項目ではありません。Mac版やWeb版へWindowsの作成手順をそのまま当てはめないでください。

既存の元表から重複行を削除して顧客数を合わせる方法は、売上明細まで消す恐れがあります。顧客数を数えるための別集計を作り、取引明細は残します。また、顧客IDの欠損や表記の違いがあると人数の意味が変わるため、数えたい対象の定義も確認します。

7. 平均の総計と表示桁数を読み違えない

通常のピボットで平均を集計した総計は、表示された各グループの平均を単純に足して割るものではなく、元の数値を対象に計算します。東店が100と200、西店が900なら、店別平均は150と900ですが、全体平均は1200÷3で400です。150と900の平均525とは違います。

「店舗の平均を同じ重みで比べたい」のか「全取引の平均を知りたい」のかで求める答えは変わります。客単価も、1行が1客なのか1商品なのかを確かめてから平均を使います。

小数桁は「値フィールドの設定」の表示形式で整えます。画面上で整数に見せても、平均の内部の値を整数へ丸める計算に変えたわけではありません。金額や率の単位を見出しに含め、0件のグループの扱いも確認します。

8. まとめ：切り替えた結果を少数の明細で検算する

合計から平均や件数へ変えたら、1つの店舗や1日の明細を取り出して手計算と比較します。個数が欲しい場合は、何の列の空白以外を数えているのか、重複を含むのかまで確認します。

集計方法の定義はMicrosoftのピボットテーブルでの値の集計にあります。欠損を埋めて数字を合わせる前に、目的に合った集計列と方法を選んでください。

📊

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

🏆

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