엑셀 혼합참조 및 확장범위 사용법 총정리 :: 엑셀 위키

엑셀 혼합참조 및 확장범위의 동작원리와 실전 사용 예제를 알아봅니다.

홈페이지 » 엑셀 혼합참조 및 확장범위 사용법 총정리 :: 엑셀 위키

엑셀 혼합참조 및 확장범위 사용법 총정리

엑셀 혼합참조 확장범위 목차 바로가기


예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [엑셀사전] 혼합참조 및 확장범위 알아보기
    예제파일

엑셀 혼합참조란 무엇인가요?

엑셀은 셀 참조 시 참조하는 셀을 고정하기 위해 달러표시($)를 사용합니다. 엑셀의 셀 참조방식인 상대참조, 절대참조, 혼합참조에 대한 기초이론 및 보다 자세한 설명은 아래 관련포스트를 확인하세요.

셀 참조방식 중 절대참조방식에는 총 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)
엑셀 혼합참조 예제 1 (잘못 사용한 경우)
혼합참조 없이 자동채우기를 하면 참조값의 위치가 변합니다.

찾을값을 혼합참조로 입력할 경우

찾을값을 혼합참조로 입력하면 수식을 오른쪽으로 자동채우기해도 열($B)은 항상 고정되어 $B3 셀을 참조합니다. 그리고 수식을 아래로 자동채우기 하면 $B3 → $B4 → $B5... 순으로 기업명이 차례대로 찾을값에 입력됩니다.

=VLOOKUP($B3,$H$3:$L$19,COLUMNS($A$1:B1),0)
엑셀 혼합참조 사용예제
혼합참조를 사용하면 VLOOKUP함수를 한번에 자동채우기 할 수 있습니다.

엑셀 혼합참조 응용 - 확장범위 만들기

엑셀 자동화 양식을 만들려면 반드시 알아두어야 할 필수 스킬이 있습니다. 바로 동적범위입니다. 동적범위에 대한 내용은 아래 OFFSET 동적범위 기초 영상강의에서 자세히 다뤄드렸으니 궁금하신 분은 꼭 참고해보시길 바랍니다.

그렇다면, 확장범위는 무엇일까요?

확장범위란, 자동채우기를 할 때 확장된 범위만큼 동시에 늘어나는 범위를 이야기합니다.

좀 더 쉬운 이해를 위해 아래 예제를 보겠습니다.

=$A$1:A1

범위의 첫번째 셀을 절대참조로 입력했습니다. 따라서 범위를 자동채우기 하면, 첫번째 셀이 고정된 상태로 한칸씩 확장되는 범위가 만들어집니다.

엑셀 혼합참조 동적 확장범위 예제
동적확장범위는 첫셀은 고정된 채 참조하는 범위가 확장되는 범위입니다.

확장범위 응용 - 누적합계 구하기

확장범위를 응용하면 아래와같이 누적합계를 아주 간단하게 계산할 수 있습니다.

=SUM($매출액:매출액)

수식을 아래방향으로 자동채우기 하면, 첫번째 매출액을 시작으로 일자별 매출액 누적합계가 계산됩니다.

엑셀 혼합참조 누적합계 구하기 예제
혼합참조 확장범위를 이용하면 누적합계를 매우 쉽게 구할 수 있습니다.

관련링크 : MS 홈페이지 셀 참조방식 상세설명 바로가기

4.5 2 투표
게시글평점
guest
2 댓글
Inline Feedbacks
모든 댓글 보기
오리온
오리온
2020년 4월 14일 9:50 오전
게시글평점 :
     

너무 감사합니다. 볼때는 아하 그렇구나 하는데, 쓸려면 늘 헷갈리는 경우가 아직은 많네요, 배열함수와 함께, 저의 난제인데 도움을 주셔서 감사합니다.

홍반장
홍반장
2021년 5월 2일 9:50 오전
게시글평점 :
     

도움을 많이 받습니다. 감사합니다.

2
0
여러분의 생각을 댓글로 남겨주세요.x