직장인을 위한 "엑셀 동적 참조" 완벽 가이드
엑셀 동적 참조 가이드 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [기초입문] 엑셀 동적 참조 기초부터 활용까지, 완벽 가이드예제파일
알아두면 편리한 '이름 범위' 사용법
엑셀로 보고서를 작성할 때 함수를 자주 사용하다보면, 일일히 셀 주소를 직접 입력하거나 범위를 선택하기 어려울 때가 있습니다. 그럴 때, 자주 사용하는 범위를 '이름 범위'로 등록하면 수식 작성도 편해지고 훨씬 직관적으로 만들 수 있습니다.
- 이름 범위 기초 사용법 : 예제파일에서 [이름범위] 시트로 이동한 후, 표의 첫번째 셀인 A1셀을 선택하고 이름 상자에 '기존'으로 이름 범위를 등록합니다.
머리글을 선택 후, '기존' 으로 이름 범위를 등록합니다. - 이제 다른 곳에서 작업을 하다가 첫번째 시트의 머리글로 이동해야 할 때, 이름 상자에서 [기존]을 선택해서 편리하게 이동할 수 있습니다.
이름 상자에서 기존을 선택해서 빠르게 이동할 수 있습니다. - 이렇게 등록한 이름 범위는 [수식] 탭 - [이름 관리자] 를 클릭하거나 Ctrl + F3 을 동시에 눌러서 이름 관리자를 실행하여 편집 또는 삭제할 수 있습니다.
등록한 이름 범위는 이름 관리자에서 편집할 수 있습니다. 오빠두Tip : 첫번째 항목을 선택한 후, Shift 키를 누른 상태로 마지막 항목을 클릭해서 모든 항목을 한 번에 삭제할 수 있습니다. - 이름 범위 한 번에 등록하기 : [선택 영역에서 만들기] 를 사용하면 여러 개의 이름 범위를 한 번에 등록할 수 있습니다. '이름범위' 시트에서 전체 데이터를 선택한 후, [수식] 탭 - [선택 영역에서 만들기]를 클릭합니다.
데이터를 모두 선택한 후, 수식 - 선택 영역에서 만들기를 클릭합니다. - 선택 영역에서 만들기 대화상자가 실행되면, '첫 행'만 선택한 후 [확인] 버튼을 클릭합니다.
첫 행을 선택한 후 [확인] 버튼을 클릭합니다. - 이제 수식 탭 - [이름 관리자]로 이동하면 머리글을 이름으로 한 범위가 한 번에 등록 된 것을 확인할 수 있고, 앞으로 수식을 작성할 때 머리글을 입력해서 범위를 편리하게 참조할 수 있습니다.
머리글을 이름으로 한 범위가 한 번에 등록됩니다.
엑셀 동적 참조 만들기 : 기초 예제
자동차의 내비게이션처럼, 엑셀에도 똑똑한 길찾기 기능이 있습니다. 바로 '동적 참조'인데요. 이 동적 참조를 사용하면 수식을 작성하는 셀 위치에 따라, 상대적으로 범위를 참조하여 수식을 더욱 편리하게 작성할 수 있습니다.
- 동적 참조 기초 사용법 : 예제파일에서 [동적 참조 기초] 시트로 이동합니다. 이번에는 동적 참조를 활용하여 각 클릭률마다 서로 다른 커미션 비율로 편리하게 계산해 보겠습니다. 수식을 작성할 시작셀을 선택한 후, [수식] 탭 - [이름 관리자] - [새로 만들기]를 클릭합니다.
수식을 작성할 시작셀을 선택한 후, 이름관리자 - 새로만들기를 클릭합니다. - 이름으로는 "클릭률"을 입력하고, 참조 대상으로 현재 선택된 셀인 B2셀 기준으로 왼쪽에 있는 클릭률 셀을 선택합니다.
기준 셀 좌측의 클릭률을 선택합니다. - 클릭률 셀은 수식을 작성하는 위치에 따라 '아래쪽(=행)' 으로만 이동하고, '오른쪽(=열)' 로는 이동하면 안 됩니다. 따라서, 숫자 앞에 있는 $ 기호는 지워서 참조 대상을 작성합니다.
행방향 이동을 위해 숫자 앞 $ 기호를 제거합니다. 오빠두Tip : 수식 입력창에서 방향키 입력 시, 커서가 아닌 셀 참조가 이동하여 불편할 경우 F2키를 누른 후 수식을 편집해보세요! 🙂
- 이제 이름관리자를 종료한 후, 표에서 임의에 셀을 선택하고 "=클릭률"을 입력하면 셀의 상대적 위치에 따라 클릭률 셀이 참조됩니다.
=클릭률 을 입력하면 셀 위치에 따라 상대적으로 클릭률 셀이 참조됩니다. - 동일한 과정으로 "회원수"에 대한 동적 참조를 이름 관리자에서 다음와 같이 등록합니다.
이름 : 회원수
참조 대상 : =동적참조기초!B$1 (B앞에 달러기호를 지우는 것을 주의하세요!)
머리글인 회원수도 동일한 방법으로 동적 참조를 등록합니다. - 이제 표에서 임의에 셀을 선택하거나, 새로운 데이터를 추가한 후 "=클릭률*회원수"로 수식을 작성해서 편리하게 집계 보고서를 만들 수 있습니다.
동적 참조로 수식을 편리하게 작성할 수 있습니다.
엑셀 동적 참조 실전 활용 : 집계 보고서 자동화 공식 만들기
동적참조는 실무에서 한 화면에 담기지 않는 긴 문서를 관리할 때 더욱 효과적으로 사용할 수 있습니다. 특히, 이름 범위 안에는 기본적인 집계 함수(예: SUM, SUMIF, VLOOKUP 등)을 사용할 수 있어, 간단한 집계 보고서는 동적 참조로 자동화 수식을 구현할 수 있습니다.
- 동적 참조로 데이터 집계하기 : 예제파일에서 [동적 참조 응용] 시트로 이동한 후, 합계를 구할 시작셀인 D9셀을 선택합합니다. 그런 다음, 이름관리자에서 새 이름을 등록합니다. 이름은 "매출 범위", 참조 대상은 현재 선택된 셀인 D9셀을 기준으로 위에 있는 매출 범위의 합계를 구하도록 SUM 함수로 작성합니다.
수식을 작성할 기준 셀 위의 매출 범위 합계를 구합니다. - 합계를 구할 범위는 열 방향으로만 이동해야 합니다. 따라서 참조 범위에서 알파벳 앞에 있는 $ 기호는 지워서 참조 대상을 작성 후 이름 범위를 등록합니다.
열 방향으로 이동하므로, 알파벳 앞의 $ 기호를 제거한 참조 대상을 작성합니다. - 이제 합계를 구할 셀에 "=매출합계"를 입력하면, 함수를 사용하지 않고 동적 참조로 각 지점의 매출 합계가 편리하게 계산할 수 있습니다.
=매출합계 를 입력하여 매장 합계를 편리하게 구할 수 있습니다. - 보고서 아래쪽의 지출 항목에도 "=매출합계*비율"로 수식을 입력해서, 범위를 동적으로 참조하여 보고서를 편리하게 작성할 수 있습니다.
동적 참조는 화면에 벗어난 범위를 참조할 때 매우 유용합니다. - 동적 참조 집계 보고서 실전 예제 : 동적 참조를 사용하면 실무에서 주로 작성하는 피벗테이블 형식의 보고서도 편리하게 작성할 수 있습니다. [동적참조실전] 시트로 이동한 후, 다음 단계에 따라 이름 범위를 차례대로 등록합니다.
· 머리글 범위 : 좌측 표 범위 선택 → [선택 영역에서 만들기] → [첫 행] 기준 이름 등록
· 동적 참조 : 우측 집계 보고서의 시작셀(I4) 선택 → 이름 관리자에서 "날짜(I2셀)"와 "ID(G4셀)" 동적 참조 등록
· 날짜는 열 방향 이동이므로 알파벳의 $기호 제거, ID는 행 방향 이동이므로 숫자 앞 $ 기호 제거 - 이제 보고서의 시작셀에 다음과 같이 동적 참조를 사용하여 SUMIFS 함수를 작성 후 자동채우기 하면 집계 보고서가 완성됩니다.
=SUMIFS(수량,판매일,날짜,품번,ID)
동적 참조를 활용하면 수식이 직관적이고, 작성하기 편해집니다. 오빠두Tip : 집계 보고서에 적용된 주말을 자동으로 강조하는 조건부 서식 사용법은 이전 영상 강의에서 알기 쉽게 정리했습니다.👇
- 이름 범위 기초 사용법 : 예제파일에서 [이름범위] 시트로 이동한 후, 표의 첫번째 셀인 A1셀을 선택하고 이름 상자에 '기존'으로 이름 범위를 등록합니다.
