같은 그룹의 평균보다 값이 큰 기록 찾기
그룹별 평균을 원본에 붙여 각 기록과 비교하세요. 값에서 해당 그룹의 평균을 뺀 뒤 차이가 0보다 큰 행만 남기면 됩니다.
각 기록이 자기 그룹의 평균보다 높은지 알고 싶다면 그룹별로 다른 기준을 적용해야 합니다. 전체 평균 하나로 필터를 걸면 그룹 안에서는 평균보다 높은 기록을 놓칠 수 있습니다. JOIN SHEET에서 그룹별 평균표를 만든 뒤 원본에 붙여, 각 기록의 값과 그 그룹의 평균을 비교해 보겠습니다.
그룹마다 비교 기준이 다릅니다
아래 가상 Records의 전체 평균은 10.4입니다. 하지만 이번에 비교할 기준은 그룹별 평균인 A=15, B=6, C=10입니다. Means는 첫 단계에서 계산해 저장할 평균표입니다. 각 기록을 평균 계산에 포함하며, 평균과 같은 값은 빼고 평균보다 큰 기록만 남깁니다.
Records
| Group | Record | Value |
|---|---|---|
| A | R1 | 12 |
| A | R2 | 18 |
| B | R3 | 4 |
| B | R4 | 8 |
| C | R5 | 10 |
첫 단계에서 만들 평균표 — Means
| Group | Mean |
|---|---|
| A | 15 |
| B | 6 |
| C | 10 |
평균표를 저장한 뒤 원본에 붙여 비교하기
- Records만 불러와 클릭한 뒤 ‘결과 정리’에서 ‘요약’을 누릅니다. ‘그룹화 기준’은 Group으로 선택하세요. 기본 집계의 ‘집계 방식’을 ‘평균’으로 바꾸고 ‘항목’은 Value, ‘결과 항목 이름’은 Mean으로 설정합니다. ‘적용’을 누른 뒤 ‘결과 저장’으로 Excel 파일을 저장합니다. 이 평균표가 Means입니다.
- 새 작업에 원본 Records와 저장한 Means를 불러옵니다. Group끼리 연결하고 ‘일치하는 데이터만’을 선택합니다. 같은 기록에 평균이 여러 번 붙지 않도록 Means에는 그룹마다 한 행만 있어야 합니다.
- Means 쪽 Group만 숨기고 Mean은 남깁니다. 새 ‘결과 정리’ → ‘계산’에서 ‘빼기 · A − B’를 선택하고 A=Value, B=Mean, 결과 이름=Excess로 지정합니다.
- 계산 아래에 ‘필터’를 추가하고 Excess의 조건을 ‘보다 큼’, 값을 0으로 지정한 뒤 ‘적용’을 누릅니다. 차이를 먼저 계산해야 그 값으로 행을 고를 수 있습니다.
- R2와 R4만 남는지 확인합니다. C의 유일한 행 R5는 자기 평균과 같으므로 제외됩니다.
전체 평균으로는 놓치는 행
R4의 값 8은 전체 평균 10.4보다 작지만 B그룹 평균 6보다 크므로 결과에 남습니다. 결과표에는 조건에 맞는 원본 기록과 해당 그룹의 평균, 두 값의 차이가 나란히 표시됩니다. 그룹별로 한 행을 남긴 평균표와 구분하세요.
| Group | Record | Value | Mean | Excess |
|---|---|---|---|---|
| A | R2 | 18 | 15 | 3 |
| B | R4 | 8 | 6 | 2 |
어떤 기록으로 평균을 구할지 먼저 정하세요
원본 일부를 필터로 먼저 제외하면 평균 계산에 포함되는 기록도 달라집니다. 비교할 기록과 평균을 구할 기록의 범위가 다르다면 두 작업의 대상을 따로 정하세요. 빈 값과 숫자로 바꿀 수 없는 값은 숫자 평균과 차이 계산에서 제외되지만, 실제 숫자 0은 평균에 포함됩니다. 이 방법은 저장과 재불러오기를 거치는 작업이며 SQL 윈도 함수나 자동 새로고침을 실행하지 않습니다.
내 파일로 시작해 보세요
JOIN SHEET에서 시작하기 ↗Excel·CSV 작업은 PC에서 이용해 주세요.
새 창에서 열리며 작업 중인 데이터를 바꾸지 않습니다.
예제 확인일:
출처
How to select students who got above average? (Stack Overflow)
그룹별 평균 점수를 넘는 학생을 찾는 SQL 질문을 참고했습니다. 이 글에서는 평균표를 저장하고 다시 불러와 연결하는 방법으로 같은 문제를 다룹니다. 위 표와 작업 순서는 별도로 만든 가상 예제이며, 질문자의 JOIN SHEET 이용 후기가 아닙니다.
