엑셀 합계 · 평균 — 자동 합계가 일부만 잡을 때, 빈칸과 0, 글자로 된 숫자, 필터 뒤 합계

칸만 골라도 보인다 — 상태 표시줄의 평균 · 개수 · 합계

합계를 칸에 적어 둘 필요가 없을 때는 더할 칸들을 끌어 고르기만 하면 됩니다. 엑셀 창 맨 아래 상태 표시줄에 고른 칸의 평균 · 개수 · 합계가 바로 나옵니다. 실습 파일에서 B3:B9(월~일 판매 수량)를 고르자 평균: 14.4 · 개수: 6 · 합계: 72 였습니다.

그런데 판매 수량을 손으로 더하면 12 + 15 + 18 + 20 + 25 + 0 = 90 입니다. 상태 표시줄 합계는 72 — 목요일 18 이 빠졌습니다. 화면을 보면 목요일 18 만 칸 왼쪽에 붙어 있고 왼쪽 위에 초록 삼각형이 있습니다. 숫자처럼 보이지만 엑셀이 글자로 갖고 있다는 표시입니다(이 PC 엑셀 오류 검사도 B6 을 '텍스트 형식으로 저장된 숫자'로 잡음). '개수 6'은 글자 칸까지 센 빈칸 아닌 칸의 수라, 숫자 칸 수(=COUNT 5)와도 다릅니다.

함정 ① — 자동 합계가 범위를 일부만 잡는다

합계를 칸에 적을 때 가장 빠른 방법은 더할 숫자 바로 아래 칸을 누르고 홈 → 자동 합계(Σ) 또는 Alt+= 입니다. 엑셀이 위쪽 숫자 칸을 찾아 =SUM(…) 범위를 점선으로 제안하고, Enter 를 누르면 들어갑니다.

실습 파일에서 표 바로 아래 B10 을 누르고 자동 합계를 누르자, 엑셀이 제안한 범위는 =SUM(B7:B9) — 금 · 토 · 일 세 칸뿐이었습니다. 위로 숫자 칸을 찾아 올라가다가 글자 칸(목요일)에서 멈춘 것입니다. 그대로 Enter 를 누르면 45, 오류 표시는 없습니다.

그래서 자동 합계를 누른 뒤에는 점선이 어디까지 감쌌는지 꼭 보고 Enter 를 누르세요. 범위가 짧으면 그 상태에서 마우스로 B3:B9 를 다시 끌어 고르면 됩니다. 더할 칸과 합계 칸을 처음부터 같이 고르고 자동 합계를 누르면 고른 범위 그대로 들어갔습니다(머리글이 1행인 시험 시트에서 B2:B9 를 고르자 =SUM(B2:B8)) — 다만 목요일이 글자인 동안은 이것도 72 입니다.

함정 ② — 글자로 된 숫자는 SUM 에서 빠진다

같은 일곱 칸을 여러 방법으로 더해 봤습니다(머리글이 1행인 시험 시트 B2:B8 에서 잰 값 — 실습 파일 주소로 바꿔 적음). 함수(SUM · AVERAGE · COUNT)는 범위 안의 글자를 건너뛰고, 칸을 + 로 하나씩 이으면 글자 '18' 을 숫자로 바꿔 더합니다. 그래서 같은 표에서 합계가 둘로 갈립니다.

고치는 방법은 목요일 칸을 숫자로 바꾸는 것입니다. 칸을 누르면 옆에 생기는 ⚠ 단추 → 숫자로 변환을 누르면 칸 오른쪽으로 붙고 초록 삼각형이 사라집니다. 이렇게 고치자 =SUM(B3:B9) 도 90, =AVERAGE(B3:B9) 는 15 가 됐습니다. 글자 칸이 여럿이면 그 칸들을 모두 고른 뒤 ⚠ → 숫자로 변환을 한 번에 하면 됩니다.

평균 — 빈칸은 빠지고 0 은 들어간다

=AVERAGE 는 숫자가 든 칸만 더해서 그 칸 수로 나눕니다. 수요일(쉬는 날)처럼 비어 있는 칸은 아예 빠지고, 일요일처럼 0 이 적힌 칸은 하루로 셉니다. 고친 표에서 =AVERAGE(B3:B9) 는 90 ÷ 6 = 15 였습니다.

어느 평균이 맞는지는 표가 뜻하는 바에 달렸습니다. 문을 열었지만 못 판 날은 0 으로 적고, 쉬는 날은 비워 두면 =AVERAGE 가 '문 연 날의 평균'이 됩니다. 쉬는 날에 0 을 적어 두면 평균이 내려가니 주의하세요(맨 아래 줄 7일 평균은 계산으로 구한 값입니다).

숨긴 행 · 필터 — SUM 은 다 더하고, SUBTOTAL 은 보이는 것만

토요일(25) 행을 숨겨도 =SUM 은 90 그대로였습니다(숨긴 행은 같은 자료의 시험 시트에서 잼). SUM 은 숨긴 줄도 더합니다. 보이는 줄만 더하는 함수는 SUBTOTAL 입니다 — 첫 번째 인수 109 는 '합계 · 숨긴 행 빼고', 9 는 '합계 · 손으로 숨긴 행은 포함'입니다.

필터로 걸러진 줄은 9 · 109 모두 뺍니다. 판매 수량 15 이상만 남기자 화 · 목 · 금 · 토 네 줄(상태 표시줄 '7개 중 4개의 레코드가 있습니다')이 보였고, SUBTOTAL 은 78, SUM 은 그대로 90 이었습니다.

자주 막히는 곳

• 합계가 손으로 더한 값보다 작다: 글자로 된 숫자(왼쪽 정렬 · 초록 삼각형) — ⚠ → 숫자로 변환
• 자동 합계가 일부만 더했다: 위쪽에 빈칸이나 글자 칸이 있으면 거기서 멈춤 — Enter 전에 점선 범위 확인, 다시 끌어 고르기
• 평균이 낮게 나온다: 쉬는 날에 0 을 적어 둠 — 비워 두거나 =AVERAGEIF(범위,">0")
• 숨긴 줄까지 더해진다: =SUM 은 다 더함 — =SUBTOTAL(109, 범위)
• 상태 표시줄 개수와 COUNT 가 다르다: 상태 표시줄 '개수'는 빈칸 아닌 칸(글자 포함), COUNT 는 숫자 칸만

연습 문제

실습 파일(가상 일주일 판매 수량)을 열고 아래 순서대로 해 보세요.

1. 1B3:B9 를 골라 상태 표시줄의 평균 · 개수 · 합계를 읽습니다.(14.4 · 6 · 72)
2. 2B10 을 누르고 자동 합계 — 점선 범위가 어디까지인지 보고 Esc 로 취소합니다.
3. 3목요일 B6 을 ⚠ → 숫자로 변환하고 B11 에 =SUM(B3:B9), B12 에 =AVERAGE(B3:B9), B13 에 =AVERAGEIF(B3:B9,">0") 를 넣습니다.
4. 4B14 에 =SUBTOTAL(109,B3:B9) 를 넣고 판매 수량에 '15 이상' 필터를 걸어 B11 과 B14 를 비교합니다.

정답 파일은 목요일을 숫자로 고친 뒤 합계 90 · 평균 15 · 판 날만 평균 18 · 보이는 행 합계 · 숫자 칸 수 6 · 빈칸 아닌 칸 수 6 을 넣고 필터를 켠 표입니다.

자주 묻는 질문