직장인을 위한 "엑셀 동적 참조" 완벽 가이드
엑셀 동적 참조 가이드 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [기초입문] 엑셀 동적 참조 기초부터 활용까지, 완벽 가이드예제파일
알아두면 편리한 '이름 범위' 사용법
엑셀로 보고서를 작성할 때 함수를 자주 사용하다보면, 일일히 셀 주소를 직접 입력하거나 범위를 선택하기 어려울 때가 있습니다. 그럴 때, 자주 사용하는 범위를 '이름 범위'로 등록하면 수식 작성도 편해지고 훨씬 직관적으로 만들 수 있습니다.
- 이름 범위 기초 사용법 : 예제파일에서 [이름범위] 시트로 이동한 후, 표의 첫번째 셀인 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셀을 선택하고 이름 상자에 '기존'으로 이름 범위를 등록합니다.
=OFFSET(일위대가!$A$7,,,COUNTA(일위대가!$A$7:$A$1849),COUNTA(일위대가!$A$2:$N$2)) 지정하였습니다.
엑셀로 와서 이름정의한 일위대가는 262행 까지만 점선으로 표출됩니다.
혹시나 이름정의가 잘못되었는가 하여서 여러차례 이름을 바꾸고 변경하고 수정해도
똑같이 262행만 지정되는지 알수가 없어서 문의드리게 되었습니다.
분명히 원인이 있을거 같은데요
제능력으로는 해결되지 않아서 문의드립니다.
먼저 작성하신 수식에는 이상이 없습니다.
262행까지 범위가 선택된다면, COUNTA(일위대가!$A$7:$A$1849) 함수로 반환되는 행의 개수가 255개여서 그렇습니다.
범위에 보이지 않는 공백이나 빈 값을 반환하는 수식이 작성되어 있는지 한번 검토해보시길 바랍니다.
고맙습니다.