- 최초 작성일: 2002-09-27
- 최종 수정일: 2026-09-29
- 조회수: 23 회
- 작성자: 권현욱 (엑셀러)
- 강의 제목: OFFSET과 COUNTA로 범위가 자동으로 늘어나는 피벗 테이블 만들기
들어가기 전에
오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.
Never too old to learn. No man is too old to learn.
배울 수 없을 정도로 늙은 경우는 없습니다. 배우는 데 너무 늙었다는 법은 없습니다. 배움에 나이는 제약 조건이 될 수 없는 법이겠지요.
- 최신 글을 불러오는 중...
OFFSET과 COUNTA로 범위가 자동으로 늘어나는 피벗 테이블 만들기
핵심 요약
OFFSET과 COUNTA 함수로 동적 이름을 정의해 두면, 원본 데이터가 늘어나도 피벗 테이블 범위를 매번 다시 지정할 필요 없이 '새로 고침'만으로 최신 데이터가 반영됩니다.
- '삽입 - 이름 - 정의' 메뉴에서 OFFSET+COUNTA 수식으로 동적 범위 이름을 만듭니다.
- 피벗 테이블 마법사에서 셀 범위 대신 이 이름을 입력합니다.
- 데이터가 추가된 뒤에는 '데이터 새로 고침' 아이콘만 클릭하면 됩니다.
질문: 원본 데이터가 늘어나도 피벗 테이블에 자동으로 반영되게 할 수 없을까요?
우연히 이 사이트를 알게 되었는데 정말 대단하십니다. Excel 강좌를 보고 많이 배워갑니다. 한 가지 질문이 있는데, 피벗 테이블을 만들고 나서 나중에 원본 데이터가 추가되는 경우 기존 피벗 테이블에 추가할 수는 없나요? 즉, 1월부터 5월까지의 데이터로 피벗 테이블을 만들어 보고서를 작성했는데 6월이 되면 6월 데이터가 생기잖아요. 이것을 원본 데이터에 입력하고 나서 기존 피벗 테이블 보고서에 6월이 나오도록 하고 싶은데 잘 안 되네요. 새로 피벗 테이블 보고서를 만들면 글자체며 색이며 틀을 다시 다 조정해야 하는 어려움이 있어서요.
동적 이름 정의(Dynamic Naming)에 대해 강좌 시간에 수차례 설명드린 기억이 납니다. 따라서 응용력을 조금만 발휘해서 피벗 테이블의 원본 범위를 정할 때 동적으로 지정해 주시면 됩니다.
'삽입 - 이름 - 정의' 메뉴를 선택하고 '이름 정의' 대화상자를 보시면 MyRange라는 이름이 하나 있을 것입니다.
이 범위는 OFFSET 함수와 COUNTA 함수를 사용하여 범위가 동적으로 변하도록 설정해 준 것입니다.
=OFFSET(Source!$A$1,0,0,COUNTA(Source!$A:$A),7)OFFSET 함수의 성질을 조금만 떠올려 보시면 수식을 이해하기란 전혀 어려운 일이 아닙니다.
이렇게 동적 이름 정의를 하신 다음, 피벗 테이블 마법사 2단계에서 범위를 지정할 때 범위를 직접 지정하는 대신 범위 이름을 입력해 주시면 됩니다.
이렇게 동적 이름을 피벗 테이블의 데이터 영역으로 설정해 준 다음에는, 필요할 때마다 '데이터 새로 고침' 아이콘만 클릭하면 자동으로 피벗 테이블이 갱신됩니다.
정리 — OFFSET과 COUNTA로 동적 범위 만들기
| 구성요소 | 역할 |
|---|---|
OFFSET(Source!$A$1,0,0,행수,7) | 시작 셀(A1)에서 지정한 행수·열수(7열)만큼의 범위를 반환 |
COUNTA(Source!$A:$A) | A열에 값이 입력된 셀 개수를 세어 행수를 자동 계산 |
| 이름 정의 | '삽입 - 이름 - 정의'에서 이 수식을 'MyRange' 같은 이름으로 등록 |
| 피벗 테이블 범위 | 마법사에서 셀 범위 대신 정의한 이름을 입력 |
자주 묻는 질문 (FAQ)
Q1. OFFSET/COUNTA 대신 표(테이블) 기능을 사용해도 되나요?
네, 엑셀 2007 이상에서는 데이터를 '표'로 등록하면 자동으로 범위가 확장되어 더 간단합니다. 다만 레거시 버전 호환이 필요할 때는 OFFSET+COUNTA 방식이 유용합니다.
Q2. COUNTA 대신 COUNT를 쓰면 안 되나요?
COUNT는 숫자가 입력된 셀만 세므로 텍스트가 섞인 열에는 COUNTA를 써야 정확한 행수를 구할 수 있습니다.
Q3. 데이터를 추가한 뒤에도 피벗 테이블이 갱신되지 않으면 어떻게 하나요?
동적 이름이 실제로 피벗 테이블 원본으로 지정되어 있는지 확인하고, '데이터 - 새로 고침'을 실행해야 합니다. 자동으로는 갱신되지 않습니다.
마치며
OFFSET과 COUNTA를 조합한 동적 이름 정의는 피벗 테이블뿐 아니라 차트, 드롭다운 목록 등 데이터가 계속 늘어나는 모든 상황에 똑같이 활용할 수 있습니다. 데이터가 추가될 때마다 범위를 다시 지정하는 게 번거로우셨다면, 오늘 소개한 방법으로 한 번만 설정해 두고 편하게 사용해 보세요!
