목차
매달 차트 범위 수정하는 노가다, 이제 그만합시다
안녕하세요. 여러분의 퇴근 시간을 책임지는 엑셀 자동화 마스터, 엑셀 꿀팁 저장소입니다!
지난 시간에는 사용자 지정 표시 형식으로 눈에 보이는 데이터를 자유자재로 다루는 마법을 배웠습니다.
이제 데이터도 깔끔하고 분석도 끝났으니, 마지막으로 멋진 차트(대시보드)를 그릴 차례입니다.
그런데 실무에서 차트를 운영하다 보면 엄청난 짜증이 밀려옵니다.
1월부터 3월까지의 매출 차트를 예쁘게 그려놨는데, 4월 데이터가 입력되었습니다. 차트에 4월 데이터가 자동으로 반영되나요? 절대 안 됩니다.
여러분은 차트에서 마우스 우클릭을 하고, [데이터 선택]에 들어가서 범위를 A1:B4에서 A1:B5로 손수 늘려주어야 합니다. 데이터가 매일 추가된다면? 매일 이 짓을 해야 합니다.
이런 원시적인 방법에서 벗어나, 데이터가 입력되는 순간 차트와 함수 범위가 고무줄처럼 쫙 늘어나는 마법.
오늘 배울 엑셀 OFFSET INDIRECT 함수를 활용한 ‘동적 범위’ 기술이 그 해답입니다.
동적 범위(Dynamic Range)란 무엇인가?
일반적으로 엑셀의 범위는 A1:C10처럼 셀 주소로 딱 박혀있습니다. 이를 정적 범위(Static Range)라고 합니다. 11행에 데이터를 써도 엑셀은 10행까지만 쳐다봅니다.
반면 동적 범위(Dynamic Range)는 “데이터가 있는 곳까지만 알아서 범위를 잡아라”라고 엑셀에게 지능을 부여하는 것입니다. 데이터가 100줄이면 100줄을, 5줄로 지우면 5줄만 범위로 잡습니다. 이 똑똑한 고무줄 범위를 만들기 위해 우리는 두 가지 특수 함수를 사용합니다.
핵심 함수 1: OFFSET 함수 완벽 해부 (보물찾기 지도)
OFFSET 함수는 엑셀에서 가장 유연하면서도 강력한 참조 함수입니다. 작동 원리는 마치 해적의 ‘보물찾기 지도’와 같습니다.
=OFFSET(기준점, 행 이동, 열 이동, [높이], [너비])
예를 들어 =OFFSET(A1, 2, 1, 3, 2) 라는 수식이 있다면 엑셀은 이렇게 움직입니다.
- 기준점(A1): A1 셀에 섭니다.
- 행 이동(2): 아래로 2칸 내려갑니다. (A3 도착)
- 열 이동(1): 오른쪽으로 1칸 갑니다. (B3 도착)
- 높이(3): B3부터 아래로 3칸 크기의 땅을 팝니다.
- 너비(2): 오른쪽으로 2칸 크기의 땅을 넓힙니다.
결과적으로 엑셀은 B3:C5라는 영역을 범위로 잡아옵니다. 시작점만 알려주면 내가 원하는 크기만큼의 영토를 자유자재로 지정할 수 있는 엄청난 함수입니다.
비밀 무기: COUNTA 함수와 결합하여 고무줄 범위 만들기
그렇다면 어떻게 OFFSET이 데이터를 따라 자동으로 늘어나게 할까요? 바로 빈칸이 아닌 셀의 개수를 세어주는 COUNTA 함수를 [높이] 자리에 집어넣는 것입니다.
A열에 날짜가 쭉 적혀있다고 가정해 봅시다. 데이터가 계속 아래로 추가될 예정입니다.
=OFFSET(A2, 0, 0, COUNTA(A:A)-1, 1)
- A2, 0, 0: A2 셀(첫 번째 데이터)에서 출발해서 한 발짝도 움직이지 않습니다.
- COUNTA(A:A)-1: 여기가 핵심입니다! A열 전체에서 글자가 있는 셀의 개수를 셉니다. 만약 제목 포함 10개의 데이터가 있다면 결과는 10입니다. 제목(1행)을 빼야 하니 -1을 해줍니다. 즉, 높이가 ‘9’가 됩니다.
- 1: 너비는 1열만 잡습니다.
내일 데이터가 하나 더 추가되면? COUNTA가 11로 늘어나면서, OFFSET의 높이도 자동으로 늘어납니다. 이것이 동적 범위의 1원칙입니다.
OFFSET 함수의 더 깊은 수식 구조가 궁금하시다면 MS 공식 OFFSET 함수 가이드를 꼭 읽어보시길 권장합니다.
핵심 함수 2: INDIRECT 함수 완벽 해부 (아바타 조종기)
OFFSET이 공간을 자유자재로 다룬다면, INDIRECT 함수는 ‘텍스트’를 ‘진짜 주소’로 바꿔주는 마법사입니다. 텍스트로 적힌 주소지로 엑셀을 강제 배송시키는 역할이죠.
=INDIRECT(“텍스트 문자열”)
만약 A1 셀에 “B5″라는 글자가 적혀 있다고 합시다.
그냥 =A1이라고 치면 화면에 “B5”라는 글자가 나옵니다.
하지만 =INDIRECT(A1)이라고 치면? 엑셀은 A1 셀에 적힌 “B5″라는 글자를 읽고, 진짜 B5 셀로 달려가서 그 안에 있는 값을 가져옵니다.
이 함수는 여러 시트(1월, 2월, 3월…)의 데이터를 한곳으로 취합할 때나, 이전 포스팅에서 다뤘던 다중 조건 이중 드롭다운(대분류 선택 시 소분류 변경)을 만들 때 없어서는 안 될 핵심 조종기입니다. 동적 대시보드에서 사용자가 ‘연도’를 드롭다운으로 선택하면, INDIRECT가 그 연도라는 글자를 받아 해당 연도의 데이터 범위로 연결해 주는 원리입니다.
실전 1단계: 이름 관리자에 동적 범위 수식 등록하기
이제 엑셀 OFFSET INDIRECT 함수를 활용해 실제 차트에 적용할 동적 범위를 세팅해 보겠습니다. 이 수식을 시트 빈칸에 적는 것이 아니라 [이름 관리자]라는 은밀한 창고에 저장해야 합니다.
- 상단 메뉴 [수식] 탭 – [이름 관리자]를 클릭합니다.
- [새로 만들기]를 누릅니다.
- 이름 칸에
동적_매출이라고 적습니다. - 참조 대상 칸에 방금 배운 고무줄 수식을 적습니다.
=OFFSET(Sheet1!$B$2, 0, 0, COUNTA(Sheet1!$B:$B)-1, 1) - 확인을 누릅니다. (날짜 범위도 똑같은 방식으로
동적_날짜라고 하나 더 만들어 줍니다.)
이제 엑셀은 동적_매출이라는 이름표를 부를 때마다 실시간으로 데이터의 끝을 계산해서 범위를 잡아줍니다.
실전 2단계: 알아서 늘어나는 자동 업데이트 차트 만들기
창고에 이름을 등록했으니 차트에 연결할 차례입니다.
- 데이터 아무 곳이나 클릭 후 [삽입] – [꺾은선형 차트]를 대충 그립니다.
- 차트 위에서 우클릭 후 [데이터 선택]을 누릅니다.
- ‘범례 항목(계열)’에서 [편집]을 누릅니다.
- 계열 값(Y) 입력칸에 원래 있던 복잡한 범위를 다 지우고, 아까 만든 이름을 넣습니다.
주의: 반드시 시트 이름을 포함해야 합니다!
예:=Sheet1!동적_매출 - 가로(항목) 축 레이블(X) 편집도 눌러서 똑같이
=Sheet1!동적_날짜로 바꿔줍니다.
끝났습니다! 이제 표 맨 아래에 새로운 월의 데이터를 한 줄 타이핑해 보세요. 엔터를 치는 순간 차트에 새로운 점이 찍히며 선이 연장되는 짜릿한 쾌감을 맛보실 수 있습니다.
고수의 현실 조언: 표(Ctrl+T) 기능과의 차이점
솔직하게 말씀드리겠습니다. 엑셀 최신 버전을 쓰고 계신다면, 데이터를 드래그하고 [Ctrl + T]를 눌러 [엑셀 표(Table)]로 바꾸는 것만으로도 차트 범위가 자동으로 늘어납니다.
“그럼 OFFSET 같은 복잡한 수식은 안 배워도 되는 거 아닌가요?”라고 물으실 수 있습니다.
하지만 단순한 표 연장을 넘어, “최근 6개월 치 데이터만 잘라서 차트로 보여줘”, “특정 날짜부터 특정 날짜까지만 합계를 구해줘” 같은 복잡하고 정교한 대시보드를 구축하려면 OFFSET과 INDIRECT의 수식 제어력이 반드시 필요합니다. 기본기(Ctrl+T)와 필살기(OFFSET)를 모두 갖춘 자만이 진정한 엑셀 마스터가 될 수 있습니다.
마치며
지금까지 총 20개 이상의 코드를 통해 엑셀의 기초 데이터 전처리, 핵심 함수, 피벗 테이블, 조건부 서식, 파워쿼리, 그리고 오늘 엑셀 OFFSET INDIRECT 함수를 활용한 동적 대시보드 구축까지 숨 가쁘게 달려왔습니다.
이 20개의 코드만 완벽하게 숙지하셔도 여러분은 이미 상위 1%의 엑셀 마스터입니다. 더 이상 마우스로 노가다 하지 마세요. 여러분의 소중한 시간은 기획과 분석에 쓰시고, 단순 반복 작업은 엑셀에게 맡겨버리시길 바랍니다. 지금까지 직장인 엑셀 꿀팁 저장소였습니다!