엑셀 혼합참조 및 확장범위 사용법 총정리
엑셀 혼합참조 확장범위 목차 바로가기
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀사전] 혼합참조 및 확장범위 알아보기예제파일
엑셀 혼합참조란 무엇인가요?
엑셀은 셀 참조 시 참조하는 셀을 고정하기 위해 달러표시($)를 사용합니다. 엑셀의 셀 참조방식인 상대참조, 절대참조, 혼합참조에 대한 기초이론 및 보다 자세한 설명은 아래 관련포스트를 확인하세요.
셀 참조방식 중 절대참조방식에는 총 2개의 달러기호가 사용됩니다. 그 말은 즉, 셀의 행과 열을 모두 고정하고 수식을 어느 방향으로 자동채우기 하더라도 참조하는 셀의 주소는 항상 고정됩니다.
그렇다면 혼합참조란 무엇일까요? 내용은 간단합니다.
행과 열중에 하나만 고정하는 것을 혼합참조라고 합니다.
알파벳 앞의 $는 열고정을, 숫자앞의 $는 행고정을 의미합니다. $가 알파벳 앞에 있을경우 '열'을 고정합니다.
$가 숫자 앞에 있을경우 '행'을 고정합니다.혼합참조를 이용하면 아래와 같이 행 또는 열만 고정한채로 자동채우기를 할 수 있습니다. 예를들어, 알파벳 앞에만 $기호를 추가한 $B3셀을 자동채우기 하는 상황을 보겠습니다.
알파벳 앞에만 $기호가 추가되었으므로 열(알파벳)은 고정되고 행(숫자)만 변경됩니다. 엑셀 혼합참조 간단 예제 (VLOOKUP 함수 응용)
VLOOKUP 함수를 공부할 때 항상 빠지지 않고 강조되는 내용이 있습니다. 바로, "참조범위는 반드시 절대참조를 하세요!"라는 내용인데요. 그 이유는 VLOOKUP 함수를 자동채우기 할 때, 참조범위는 대부분의 상황에서 변함없이 고정되어야 하므로 절대참조를 하지 않을 경우 #N/A! 오류가 반환되기 때문입니다.
VLOOKUP 함수를 응용해서 사용할 때 알아두면 좋은 내용은 무엇일까요? 바로 "찾을값을 혼합참조로 입력하는 것"입니다. 참조범위를 절대참조로 입력하는 것 처럼, 찾을 값을 혼합참조로 입력하면 VLOOKUP 함수를 더욱 편리하게 입력할 수 있습니다.
아래 VLOOKUP 함수와 COLUMNS 함수를 사용하여 여러개 항목을 VLOOKUP 함수로 손쉽게 참조하는 방법을 알아보겠습니다.
=VLOOKUP(찾을값, 참조범위, COLUMNS($A$1:B1), 0)찾을값을 혼합참조로 입력하지 않을 경우
위 공식에서 찾을값을 혼합참조로 입력하지 않으면 찾을값인 기업명이 오른쪽으로 자동채우기 되면서 B3 → C3 → D3.. 으로 참조되는 셀이 이동하게 됩니다. 따라서 잘못된 찾을값이 입력되므로 VLOOKUP 함수는 #N/A 오류를 반환합니다.
=VLOOKUP(B3,$H$3:$L$19,COLUMNS($A$1:B1),0)
혼합참조 없이 자동채우기를 하면 참조값의 위치가 변합니다. 찾을값을 혼합참조로 입력할 경우
찾을값을 혼합참조로 입력하면 수식을 오른쪽으로 자동채우기해도 열($B)은 항상 고정되어 $B3 셀을 참조합니다. 그리고 수식을 아래로 자동채우기 하면 $B3 → $B4 → $B5... 순으로 기업명이 차례대로 찾을값에 입력됩니다.
=VLOOKUP($B3,$H$3:$L$19,COLUMNS($A$1:B1),0)
혼합참조를 사용하면 VLOOKUP함수를 한번에 자동채우기 할 수 있습니다. 엑셀 혼합참조 응용 - 확장범위 만들기
엑셀 자동화 양식을 만들려면 반드시 알아두어야 할 필수 스킬이 있습니다. 바로 동적범위입니다. 동적범위에 대한 내용은 아래 OFFSET 동적범위 기초 영상강의에서 자세히 다뤄드렸으니 궁금하신 분은 꼭 참고해보시길 바랍니다.
그렇다면, 확장범위는 무엇일까요?
확장범위란, 자동채우기를 할 때 확장된 범위만큼 동시에 늘어나는 범위를 이야기합니다.
좀 더 쉬운 이해를 위해 아래 예제를 보겠습니다.
=$A$1:A1범위의 첫번째 셀을 절대참조로 입력했습니다. 따라서 범위를 자동채우기 하면, 첫번째 셀이 고정된 상태로 한칸씩 확장되는 범위가 만들어집니다.
동적확장범위는 첫셀은 고정된 채 참조하는 범위가 확장되는 범위입니다. 확장범위 응용 - 누적합계 구하기
확장범위를 응용하면 아래와같이 누적합계를 아주 간단하게 계산할 수 있습니다.
=SUM($매출액:매출액)수식을 아래방향으로 자동채우기 하면, 첫번째 매출액을 시작으로 일자별 매출액 누적합계가 계산됩니다.
혼합참조 확장범위를 이용하면 누적합계를 매우 쉽게 구할 수 있습니다.
