엑셀 명단이 늘어나면 저절로 넓어지는 범위 만들기

고객 명단을 매주 아래로 한 줄씩 늘려 가는 파일을 생각해 보십시오. 합계 칸에는 =SUM(B2:B7), 드롭다운의 원본에는 A2:A7을 적어 두었는데, 8행에 새 고객을 적으면 그분은 합계에도 드롭다운에도 들어가지 않습니다. 범위를 고치는 걸 한 번 잊으면 숫자가 틀린 채로 보고서가 나가기도 합니다. 범위가 명단 길이를 스스로 따라가게 만들어 두면 이런 실수를 줄일 수 있습니다.

생활에서는 날마다 한 줄씩 적는 가계부나 운동 기록의 합계·평균을 낼 때 같은 방법을 씁니다. 이 글은 엑셀 "목적별 함수 조합" 시리즈의 한 편입니다. OFFSET은 컴활 1급 준비 자료에서도 자주 보이는 함수라, 인수 다섯 개의 뜻을 익혀 두면 시험 공부에도 도움이 됩니다.

예제 표 — 계속 늘어나는 고객 명단

시트 이름은 명단입니다. 맨 위 줄은 열 문자, 맨 왼쪽 칸은 행 번호입니다. 이름은 모두 가상입니다. 처음에는 7행까지 있고, 8행은 나중에 추가할 줄입니다.

	A	B
1	고객명	상담 횟수
2	김하늘	3
3	이도윤	1
4	박서연	2
5	최민준	4
6	정수아	1
7	한지호	2
8	(나중에 추가) 윤서준	3

이 방식이 제대로 동작하려면 두 가지가 지켜져야 합니다. A열에는 1행 머리글과 고객 이름만 있어야 하고, 이름 사이에 빈 줄이 없어야 합니다. 이유는 아래 "자주 막히는 곳"에서 실제 계산으로 보여 드립니다.

완성 수식 — 이름으로 정의하기

수식을 칸에 바로 쓰지 않고 이름(범위에 붙이는 별명)으로 정의해 둡니다. 그래야 합계·드롭다운 어디서든 같은 범위를 이름 하나로 부를 수 있습니다.

1. [수식] 탭 → [정의된 이름] 묶음 → [이름 정의]를 누릅니다.
2. "새 이름" 창의 이름 상자에 고객목록을 적습니다. 범위는 "통합 문서" 그대로 둡니다.
3. 참조 대상 상자의 내용을 지우고 아래 수식을 넣은 뒤 [확인]을 누릅니다.

=OFFSET(명단!$A$2,0,0,COUNTA(명단!$A:$A)-1,1)

같은 방법으로 이름 상담횟수를 하나 더 만듭니다. 시작 칸만 B2로 바뀌고, 줄 수는 똑같이 A열로 셉니다.

=OFFSET(명단!$B$2,0,0,COUNTA(명단!$A:$A)-1,1)

이제 이름을 이렇게 씁니다.

쓰는 곳	넣는 것	7행까지일 때	8행을 추가한 뒤
상담 횟수 합계	=SUM(상담횟수)	13	16
고객 수	=COUNTA(고객목록)	6	7
평균 상담 횟수	=AVERAGE(상담횟수)	2.166…	2.285…
고객 이름 드롭다운	데이터 유효성 검사 → 목록 → 원본 =고객목록	6명	7명

8행에 윤서준 님을 적는 것만으로 합계·개수·드롭다운이 함께 바뀝니다. 수식은 하나도 고치지 않았습니다.

수식 풀어 보기 — 안쪽부터

OFFSET(명단!$A$2,0,0,COUNTA(명단!$A:$A)-1,1)을 7행까지 있을 때 기준으로 봅니다.

단계	식	중간 결과
① A열에서 비어 있지 않은 칸 세기	COUNTA(명단!$A:$A)	7 (머리글 1 + 고객 6)
② 머리글 빼기	①-1	6
③ 범위 잡기	OFFSET($A$2,0,0,6,1)	A2에서 6줄 × 1칸 = A2:A7
④ 합계에 쓰면	SUM(상담횟수) = SUM(B2:B7)	3+1+2+4+1+2 = 13

8행을 추가하면 ①이 8, ②가 7이 되어 범위가 A2:A8로 늘어나고 합계는 16이 됩니다.

• COUNTA는 비어 있지 않은 칸의 개수를 셉니다. 글자든 숫자든 셉니다. 머리글 "고객명"도 한 칸으로 세기 때문에 1을 뺍니다.
• OFFSET(기준, 행 이동, 열 이동, 높이, 너비)은 기준 칸에서 몇 줄·몇 칸 옮긴 자리부터 정한 크기만큼의 범위를 돌려줍니다. 여기서는 옮기지 않고(0, 0) 기준 A2에서 시작해 높이만 COUNTA로 정했습니다. Microsoft 문서 설명대로 OFFSET은 칸을 실제로 옮기거나 선택을 바꾸지 않고 "참조를 구할 뿐"이라, SUM처럼 범위를 받는 함수 안에 넣어 쓸 수 있습니다.

검증 방법도 밝혀 둡니다. 계산에 쓴 파이썬 formulas 라이브러리는 OFFSET을 지원하지 않습니다. 그래서 OFFSET이 돌려줄 범위는 Microsoft 문서의 정의대로 계산했고(문서 예제 세 개와 결과가 같은지 먼저 확인), COUNTA 값과 그 범위를 넣은 SUM·COUNTA·AVERAGE 결과는 formulas로 계산해 확인했습니다. 즉 OFFSET 자체의 동작은 Microsoft 문서 기준입니다.

자주 막히는 곳

• 같은 열 아래쪽에 메모를 적으면 범위가 늘어납니다. A20에 "메모: 9월 명단"을 적고 계산하니 COUNTA가 8이 되어 범위가 A2:A8로, 빈 칸 하나를 더 품었습니다. 합계는 그대로였지만 드롭다운에는 빈 항목이 낄 수 있습니다. 메모는 다른 열이나 다른 시트에 적습니다.
• 빈 글자("")를 돌려주는 수식도 셉니다. Microsoft 문서에 따르면 COUNTA는 오류값과 빈 텍스트("")가 든 칸도 셉니다. A열 아래쪽에 =IF(…,"",…) 같은 수식을 미리 채워 두면 보이지 않아도 개수에 들어갑니다.
• 명단이 비면 #REF! — 머리글만 있으면 높이가 0이 됩니다. Microsoft 문서는 OFFSET의 높이와 너비가 양수여야 한다고 설명합니다. 명단에 최소 한 명은 있어야 합니다.
• 참조 대상에는 시트 이름과 $를 붙입니다. 명단!$A$2처럼 써야 다른 시트에서 이름을 써도 같은 범위를 가리킵니다. $가 없으면 이름을 쓰는 칸 위치에 따라 범위가 달라질 수 있습니다.

다른 방법 — 표 기능

수식이 부담스럽다면 명단을 엑셀 표로 바꾸는 방법이 있습니다. Microsoft 문서 기준으로 명단 안의 칸 하나를 누르고 [홈] 탭 → [표 서식]에서 스타일을 고른 뒤, 범위와 머리글 포함 여부를 확인하고 [확인]을 누르면 됩니다. Microsoft 문서는 드롭다운 목록을 표에서 만들어 두면 항목을 추가하거나 지울 때 드롭다운이 자동으로 바뀐다고 안내합니다. 표의 자세한 사용법은 아래 "함께 보면 좋은 글"의 표 기능 글에 있습니다.

	OFFSET + COUNTA 이름	표 기능
준비	이름 정의 창에 수식 입력	메뉴 한 번
주의할 점	빈 줄·같은 열 메모에 약함 (위 "자주 막히는 곳")	표 범위 안에 적어야 함
보기 모양	바뀌지 않음	고른 표 스타일이 입혀짐
원리 이해·시험 대비	OFFSET 인수를 익히기 좋음	메뉴 기능

실무에서 새로 만드는 명단이라면 표 기능이 간단하고, 이미 모양이 정해진 양식이나 표로 바꾸기 곤란한 시트라면 OFFSET 방식이 쓸모 있습니다.

자주 묻는 질문

함께 보면 좋은 글

• 엑셀 COUNT·COUNTA 차이와 사용법 — COUNTA 자세히
• 엑셀 표 기능으로 범위를 표로 바꾸기 — 수식 없이 늘어나는 범위
• 엑셀 절대참조·상대참조와 $ 쓰는 법 — $A$2의 뜻
• 엑셀 글 전체 목차 — 드롭다운 만들기 등 목적별 함수 조합 시리즈 모아 보기

참고: OFFSET 함수 (Microsoft 지원) · COUNTA 함수 (Microsoft 지원) · Excel에서 이름 관리자 사용 (Microsoft 지원)