함수를 쓰는 직장인이라면 꼭 알아야 할, "엑셀 동적 참조" 완벽 가이드

알아두면 편리한 '이름 범위' 사용법

엑셀에서 함수를 자주 사용하다 보면, 셀 주소를 일일이 입력하거나 넓은 범위를 매번 다시 선택해야 하는 번거로움이 생깁니다. 이때 자주 사용하는 범위를 '이름 범위'로 등록하면 수식 작성이 간결해지고, 어떤 데이터를 참조하는지 한눈에 알아볼 수 있어 보고서의 가독성도 높아집니다.

1. 이름 범위 기초 사용법 : 예제파일에서 [이름범위] 시트로 이동한 뒤, 표의 첫 번째 셀인 A1셀을 선택하고 이름 상자에 '기존'으로 이름 범위를 등록합니다.

   

2. 이렇게 등록해 두면 다른 시트에서 작업하던 중 첫 번째 시트의 머리글로 돌아가야 할 때, 이름 상자에서 [기존]을 선택하는 것만으로 바로 해당 위치로 이동할 수 있습니다.

   

3. 이미 등록한 이름 범위는 [수식] 탭 - [이름 관리자]를 클릭하거나 Ctrl + F3 단축키를 눌러 이름 관리자에서 편집하거나 삭제할 수 있습니다.

   

   오빠두Tip : 첫 번째 항목을 선택한 뒤 Shift 키를 누른 상태로 마지막 항목을 클릭하면 모든 항목을 한 번에 선택해 일괄 삭제할 수 있습니다.
4. 이름 범위 한 번에 등록하기 : [선택 영역에서 만들기] 기능을 사용하면 여러 개의 이름 범위를 한 번에 등록할 수 있습니다. [이름범위] 시트에서 전체 데이터를 선택한 후, [수식] 탭 - [선택 영역에서 만들기]를 클릭합니다.

   

5. 선택 영역에서 만들기 대화상자가 열리면, '첫 행'만 체크한 뒤 [확인] 버튼을 클릭합니다.
   
6. [수식] 탭 - [이름 관리자]를 다시 열어보면 각 머리글이 그대로 이름이 되어 범위가 일괄 등록된 것을 확인할 수 있습니다. 이후로는 수식을 작성할 때 머리글 이름만 입력해도 해당 범위를 손쉽게 참조할 수 있습니다.

   

엑셀 동적 참조 만들기 : 기초 예제

자동차 내비게이션이 현재 위치에 따라 경로를 다시 계산하듯, 엑셀에도 셀 위치에 따라 참조 범위를 자동으로 바꿔 주는 '동적 참조' 기능이 있습니다. 동적 참조를 활용하면 수식이 작성된 셀의 위치에 맞춰 참조 범위가 상대적으로 이동하므로, 같은 수식을 여러 셀에 반복해서 작성할 필요 없이 한 번의 정의로 표 전체의 계산을 처리할 수 있습니다.

1. 동적 참조 기초 사용법 : 예제파일에서 [동적 참조 기초] 시트로 이동합니다. 이번 예제에서는 동적 참조를 활용해 각 클릭률에 따라 서로 다른 커미션 비율을 계산해 보겠습니다. 수식을 작성할 시작셀을 선택한 뒤, [수식] 탭 - [이름 관리자] - [새로 만들기]를 클릭합니다.

   

2. 이름 칸에는 "클릭률"을 입력하고, 참조 대상으로는 현재 선택된 셀인 B2셀을 기준으로 왼쪽에 위치한 클릭률 셀을 지정합니다.

   

3. 클릭률 셀은 수식이 작성되는 위치에 따라 '아래쪽(=행)' 으로만 이동해야 하고, '오른쪽(=열)' 으로는 이동하면 안 됩니다. 따라서 숫자 앞에 있는 $ 기호만 지운 혼합 참조 형태로 참조 대상을 작성합니다.

   

   오빠두Tip : 수식 입력창에서 방향키를 누를 때 커서 대신 셀 참조가 이동하여 불편하다면, F2키를 눌러 편집 모드로 전환한 뒤 수식을 다듬어보세요. 🙂
   
4. 이름 관리자를 닫은 뒤, 표에서 임의의 셀을 선택하고 "=클릭률"을 입력하면 셀의 상대적 위치에 따라 알맞은 클릭률 셀이 자동으로 참조됩니다.

   

5. 같은 방식으로 "회원수"에 대한 동적 참조도 이름 관리자에서 다음과 같이 등록합니다.
   이름 : 회원수
   참조 대상 : =동적참조기초!B$1 (B 앞의 달러 기호를 지우는 것에 주의하세요!)

   

6. 이제 표에서 임의의 셀을 선택하거나 새로운 데이터를 추가한 뒤 "=클릭률*회원수"로 수식을 작성하면, 위치에 따라 자동으로 참조 범위가 바뀌면서 집계 보고서를 한 번에 완성할 수 있습니다.

   

엑셀 동적 참조 실전 활용 : 집계 보고서 자동화 공식 만들기

동적 참조는 실무에서 한 화면에 담기지 않을 만큼 긴 보고서를 다룰 때 진가를 발휘합니다. 또한 이름 범위 안에는 SUM, SUMIF, VLOOKUP 같은 기본 집계 함수를 함께 정의할 수 있어, 단순한 형태의 집계 보고서라면 별도의 수식 없이 동적 참조만으로도 자동화된 계산 구조를 구현할 수 있습니다.

1. 동적 참조로 데이터 집계하기 : 예제파일에서 [동적 참조 응용] 시트로 이동한 뒤, 합계를 구할 시작셀인 D9셀을 선택합니다. 그런 다음 이름 관리자에서 새 이름을 등록합니다. 이름은 "매출 범위"로 지정하고, 참조 대상은 현재 선택된 D9셀을 기준으로 위쪽에 있는 매출 범위의 합계를 구하도록 SUM 함수를 사용해 작성합니다.

   

2. 합계를 구할 범위는 열 방향으로만 이동해야 하므로, 참조 대상에서 알파벳 앞에 있는 $ 기호만 지운 혼합 참조로 작성한 뒤 이름 범위로 등록합니다.

   

3. 이제 합계를 구할 셀에 "=매출합계"만 입력하면 별도의 수식 없이도 동적 참조만으로 각 지점의 매출 합계가 자동으로 계산됩니다.

   

4. 보고서 아래쪽의 지출 항목에도 같은 원리로 "=매출합계*비율" 수식을 입력하면, 동적 참조가 위치에 따라 다른 범위를 알아서 잡아 주므로 보고서 전체를 빠르게 완성할 수 있습니다.

   

5. 동적 참조 집계 보고서 실전 예제 : 동적 참조를 사용하면 실무에서 자주 작성하는 피벗테이블 형태의 집계 보고서도 함수만으로 깔끔하게 구성할 수 있습니다. [동적참조실전] 시트로 이동한 뒤, 다음 단계에 따라 이름 범위를 차례대로 등록합니다.
   · 머리글 범위 : 좌측 표 범위 선택 → [선택 영역에서 만들기] → [첫 행] 기준 이름 등록
   · 동적 참조 : 우측 집계 보고서의 시작셀(I4) 선택 → 이름 관리자에서 "날짜(I2셀)"와 "ID(G4셀)" 동적 참조 등록
   · 날짜는 열 방향 이동이므로 알파벳 앞 $ 기호 제거, ID는 행 방향 이동이므로 숫자 앞 $ 기호 제거
6. 마지막으로 보고서의 시작셀에 다음과 같이 동적 참조를 사용해 SUMIFS 함수를 작성한 뒤 자동 채우기로 끌어 내리면, 조건에 맞는 값이 셀 위치에 따라 자동으로 합산되며 집계 보고서가 완성됩니다.
   =SUMIFS(수량,판매일,날짜,품번,ID)

   

   오빠두Tip : 집계 보고서에 적용된 주말을 자동으로 강조하는 조건부 서식 사용법은 이전 영상 강의에서 자세히 정리했습니다.👇