평균에서 0으로 적힌 기록만 빼려면?
측정값이 0인 기록을 제외하고 팀별 평균을 구하세요. 음수는 남기되, 0이 실제 측정값인지 아직 측정하지 않았다는 표시인지 먼저 확인합니다.
측정하지 않은 값을 0으로 채운 표에서 그대로 평균을 내면 미측정 기록까지 계산에 포함됩니다. 이런 0을 제외하려면 JOIN SHEET의 결과 필터로 0인 행을 먼저 빼고 평균을 내세요. 실제로 측정한 0도 빼야 하는지는 계산 전에 정해야 합니다.
0을 빼도 음수는 남기기
아래 가상 표에서 A의 0은 아직 측정하지 않았다는 표시이고, B의 -6은 실제 측정값입니다. 측정값 칼럼의 자료형은 숫자로 맞추세요. 필터로 제외한 행은 뒤에 추가하는 모든 집계에서 빠진다는 점도 기억해야 합니다.
| 팀 | 측정값 |
|---|---|
| A | 10 |
| A | 0 |
| A | 20 |
| B | -6 |
| B | 0 |
| B | 0 |
| C | 0 |
0 제외 조건을 평균보다 앞에 놓기
- 불러온 시트를 클릭하고 ‘결과 정리’에서 ‘필터’를 누릅니다.
- 측정값 칼럼의 조건을 ‘같지 않음’, 값을 0으로 정합니다. ‘보다 큼’ 0을 사용하면 음수도 사라집니다.
- 필터 아래에 ‘요약’을 추가하고 ‘그룹화 기준’에서 팀을 선택합니다. 기본 집계의 ‘집계 방식’을 ‘평균’, ‘항목’을 측정값으로 선택하세요. ‘결과 항목 이름’에는 ‘0 제외 평균’을 입력합니다.
- 같은 요약에서 ‘집계 추가’를 누릅니다. ‘집계 방식’의 기본값인 ‘개수’는 유지하고 ‘결과 항목 이름’을 ‘내역 수’로 바꾸세요. 필터가 요약 위에 있는지 확인한 뒤 ‘적용’을 누릅니다.
평균과 건수를 함께 읽기
A는 10과 20의 평균 15이며 내역 수는 2입니다. B는 -6만 남아 평균 -6, 내역 수 1입니다. A가 10으로 나오면 0이 계산에 포함됐는지 확인하세요.
| 팀 | 0 제외 평균 | 내역 수 |
|---|---|---|
| A | 15 | 2 |
| B | -6 | 1 |
C는 모든 측정값이 0이어서 필터를 거친 뒤에는 기록이 남지 않습니다. C가 결과에서 빠졌다고 평균이 0이라는 뜻은 아닙니다. 원본에서 아직 측정하지 않은 값을 빈 셀로 관리하면 실제 측정값 0과 구별할 수 있습니다. 여러 칼럼 중 하나의 평균에서만 특정 기록을 빼려는 경우에는 계산 대상을 따로 준비하세요. 이 필터는 해당 행 전체를 제외하기 때문입니다.
내 파일로 시작해 보세요
JOIN SHEET에서 시작하기 ↗Excel·CSV 작업은 PC에서 이용해 주세요.
새 창에서 열리며 작업 중인 데이터를 바꾸지 않습니다.
예제 확인일:
출처
How to let Pivot Table ignore ZEROs from the data while calculating Average and Average of Average?의 질문을 참고해 별도의 가상 데이터로 구성했습니다.
