平均から0の記録だけを除外するには？
0の行だけを除外してから、残った測定値を平均します。負の値は残せますが、実際に測定した0も除いてよいかは事前に確認してください。
未測定の欄を0で埋めた表でそのまま平均すると、測定していない記録まで計算に入ります。JOIN SHEETで測定値が0の行を除いてから平均すれば、残った測定値だけで計算できます。ただし、実際に測定した0も除いてよいかは事前に決めてください。
負の測定値は残す
この架空の表では、Aの0は未測定、Bの-6は有効な測定値です。測定値の列は数値型にします。絞り込むと行全体が除外されるため、その後に追加する集計はすべて残った行を対象にします。
| チーム | 測定値 |
|---|---|
| A | 10 |
| A | 0 |
| A | 20 |
| B | -6 |
| B | 0 |
| B | 0 |
| C | 0 |
0の除外を平均の前に置く
- シートを結果の基準に選び、「結果を整理」から「絞り込み」を追加します。
- 測定値の条件を「等しくない」、値を0にします。「より大きい」0では負の数まで消えます。
- その下に「集計」を追加し、「まとめる項目」でチームを選びます。最初の集計の「集計方法」を「平均」に変え、「項目」に測定値を選び、「結果の項目名」にゼロを除く平均と入力します。
- 「集計を追加」を押します。追加した集計は「件数」のままにし、「結果の項目名」を明細数に変えます。絞り込みが集計より上にあることを確認して「適用」を押します。
平均と件数を照合する
Aは10と20の2件で平均15、Bは-6の1件で平均-6です。Aが10になる場合は、0が計算対象に残っていないか確認します。
| チーム | ゼロを除く平均 | 明細数 |
|---|---|---|
| A | 15 | 2 |
| B | -6 | 1 |
Cは全件が0なので、絞り込み後はグループが残りません。平均0という意味ではありません。実測の0と未測定を区別したい場合は、元データで未測定を空欄にします。特定の集計だけ0を除きたい場合は、全行が必要な集計と計算対象を分けて準備してください。
自分のファイルで始めましょう
JOIN SHEETを使ってみる ↗Excel・CSVの作業はPCでご利用ください。
新しいタブで開くため、作業中のデータは置き換わりません。
サンプルの検証日:
出典
How to let Pivot Table ignore ZEROs from the data while calculating Average and Average of Average?の質問を参考に、独自の架空データで手順を作成しました。
