엑셀 상대참조/절대참조/혼합참조 완벽 정리

엑셀의 상대참조/절대참조/혼합참조를 알아보기 전에 '참조'의 개념을 간단히 살펴보겠습니다.

엑셀에서 '참조'란 값을 직접 입력하는 것이 아닌 '어딘가를 가리키는 것'을 말합니다.

아래 그림과 같이 [A1] 셀에 숫자 1000이 입력되어 있을 때

[A3]셀에 수식 '=A1'을 입력하면
[A3]셀은 [A1] 셀의 숫자 값 1000을 표시해 줍니다.

이 때 '[A3] 셀은 [A1]셀을 참조한다' 고 말하며 '수식 입력줄'에서 참조상태를 확인할 수 있습니다.

위에 살펴본대로 엑셀에서 수식을 입력하면 셀의 위치를 참조하여 계산하는데 참조하는 방식에 따라서 상대참조,절대참조가 있고 두가지 방식을 섞은 혼합참조가 있습니다.

만약 A1셀을 어떤 수식에서 참조할 때 다음 4가지 방법으로 참조할 수 있습니다.

엑셀의 참조 방식이 사실 그리 어려운 개념은 아니지만 수식을 복사해서 붙여넣는 방향에 따라 참조의 위치가 변하므로 복잡하고 어렵게 느껴집니다. 아래의 실습용 엑셀파일을 다운로드해서 연습해 보면 그리 어렵지 않게 참조 방식을 이해할 수 있을 겁니다.

실습용 엑셀파일 다운로드 : 엑셀참조-상대참조-절대참조-혼합참조.xlsx

1. 상대참조

1) 상대참조가 포함된 수식을 행 방향으로 입력하기

먼저 상대참조부터 알아 보겠습니다. 상대참조는 셀을 참조하는 위치가 상대적으로 바뀔 수 있는 방식을 말합니다.

쉬운 예이지만 정확한 이해를 위해 아래와 같이 따라해 보겠습니다.

[E3] 셀에 '판매수량과 단가를 곱한 판매금액'을 계산하기 위한 수식 =C5*D5를 입력합니다.

이제 수식이 입력되어 판매금액이 구해진 [E5] 셀을 복사하여 셀 범위 [E6:E9]에 붙여 넣습니다.

수식을 복사해서 붙여 넣는 대신 다음과 같이 '자동채우기'를 해도 됩니다.

'붙여넣기'한 결과 다음과 같이 상품별 판매금액이 구해졌습니다.

붙여넣어진 셀 범위 [E6:E9]의 수식을 살펴보면 다음과 같습니다.

[E5]셀의 수식은 =C5*D5 이었으므로 아래의 범위에 복사된 수식은 원래 수식과 동일하게 =C5*D5 가 되어야 할 것 같지만 실제로는 =C6*D6, =C7*D7, =C8*D8, =C9*D9로 입력되어 있습니다.

수식을 복사해서 붙여넣을 때 E열의 위치는 변하지 않았고 행만 아래(6,7,8,9행)로 이동했으므로 [E5]셀의 행번호인 5가 자동으로 차례로 변경되어서 6,7,8,9로 변경된 것입니다.

상대참조는 수식을 한번만 입력하고 나머지는 그냥 복사만 하면 열과 행의 상대적 위치를 참조하여 수식이 자동으로 각 셀에 맞도록 입력되는 편리한 기능입니다.

이렇게 상대적 참조가 가능하도록 입력되어 있는 셀을 "상대참조"를 사용한다고 부릅니다. 상대참조는 엑셀에서 정말 유용한 기능입니다. 만약에 엑셀에 상대참조 기능이 없다면 우리는 위의 수식을 각 행마다 일일이 손으로 입력했을 겁니다.

2) 상대참조가 포함된 수식을 열 방향으로 입력하기

이번에는 합계를 구하는 표가 다음과 같이 가로로 되어 있을 경우에 상대참조 수식을 입력하는 방법을 알아보겠습니다.

[C18] 셀에 1월 판매금액 합계를 구하기 위한 수식 =SUM(C13:C17)을 입력합니다.

수식이 입력되어 1월 합계가 구해진 [C18] 셀을 복사하여 셀 범위 [D18:G18]에 붙여 넣습니다.

수식을 복사해서 붙여넣는 대신 다음과 같이 '자동채우기'를 해도 됩니다.

'붙여넣기'한 결과 다음과 같이 월별 합계가 구해졌습니다.

붙여넣어진 셀 범위 [D18:G18]의 수식을 살펴보면 다음과 같습니다.

[C18]셀의 수식은 =SUM(C13:C17) 이었으므로 옆으로 복사된 수식은 원래 수식과 동일하게 =SUM(C13:C17) 이 되어야 할것 같지만 실제로는 =SUM(D13:D17), =SUM(E13:E17)... 로 입력되어 있습니다.

엑셀에서 수식을 복사해서 붙여 넣을 때 기본이 상대참조이므로 위의 경우 행의 위치는 그대로이고 오른쪽으로 이동하면서 열이름만 D, E, F, G로 바뀐것입니다.

3) 상대참조 정리

지금까지 살펴본 상대참조를 다음 그림으로 정리해 보겠습니다.

수식을 복사하여 행 방향(아래 또는 위로)으로 붙여 넣으면 행의 번호만 바뀌면서 붙여 넣어지고

열 방향(왼쪽 또는 오른쪽)으로 붙여 넣으면 열이름만 바뀌면서 붙여 넣어집니다.

2. 절대참조

상대참조는 수식을 입력하는 위치가 바뀌면 행과 열이 이동한 만큼을 반영하여 수식이 바뀌므로 여러모로 편리하지만, 참조하는 위치가 바뀌면 안되는 경우도 있습니다.

1) 고정될 셀의 값을 참조하는 경우

어떤 회사의 영업사원의 판매실적에 따라 인센티브를 지급하는 경우를 가정해 보겠습니다.

각 판매금액에 5%를 곱해서 인센티브 금액을 구하는 예입니다. 아래와 같이 따라 해 보겠습니다.

[D27] 셀에 =C27*C24를 입력합니다.

김나나의 판매금액 250만원의 5%인 인센티브 금액 125,000원이 정상적으로 계산됩니다.

이제 아래와 같이 '자동채우기'로 [D27] 셀에 입력된 수식을 셀 범위 [D28:D29]에 채워 넣어보겠습니다.

이지은의 인센티브금액이 0원으로 계산됩니다(표시하는 방식에 따라 0원이 하이픈으로 표시될 수도 있습니다).

이지은씨가 인센티브를 못 받게 되었네요.

어떻게 된 일인지 수식을 살펴보겠습니다.

판매금액을 참조하는 셀은 [C28]로 정상인데, 인센티브지급율은 제대로 참조를 못하고 한칸 아래로 이동이 되어 있습니다.

이렇게 상대참조로 입력된 셀을 복사하면 셀 이동에 따라 위치가 자동으로 변경됩니다(복사하는 위치가 아래로 한칸 이동되었으므로 인센티브지급율도 한칸 아래에 있다고 가정하고 참조하는 위치가 한칸 아래로 이동된 것입이다). 그래서 인센티브 금액이 0원으로 계산되었습니다.

이 때 필요한 것이 참조위치가 바뀌지 않는 '절대참조' 방식인데 셀 참조의 행과 열의 앞에 $(달러)기호를 붙이면 됩니다. $기호는 위치가 움직이지 않도록 고정시키는 역할을 합니다.

위의 문제를 해결하기 위해 아래와 같이 ‘절대참조’ 방식을 사용해 보겠습니다.

'인센티브지급율' 필드는 참조 위치가 바뀌면 안되므로 아래와 같이 =C27*$C$24를 입력합니다.
인센티브지급율의 위치인 [C24]를 [$C$24]로 입력하면 참조하는 위치가 변하지 않는 '절대참조' 방식으로 참조하는 것입니다.

다음과 같이 결과가 구해졌습니다.

이제 아래와 같이 '자동채우기'로 [D27] 셀에 입력된 수식을 셀 범위 [D28:D29]에 채워 넣어보겠습니다.

이지은의 인센티브금액이 150,000원으로 정상적으로 계산됩니다.

[D28] 셀에 입력된 수식을 살펴보면 다음과 같이 =C28*$C$24로 입력되어 있습니다. 상대참조와 달리 인센티브지급율 참조위치가 바뀌지 않고 그대로 되어있어서 인센티브가 정상적으로 계산되었습니다.

2) 고정된 셀범위를 참조하는 경우

절대참조가 고정된 셀만 참조하는 것은 아닙니다. 다음과 같이 상품의 판매이력 자료가 있을 때

상품별 판매금액 합계를 구할 때 상품, 판매금액의 범위는 항상 고정되어 있어야 하므로 셀 범위를 '절대참조'로 지정합니다.

먼저 상품이 '모나미 볼펜'인 것의 판매금액 합계를 구하기 위해 다음 수식을 입력합니다.

=SUMIF($C$35:$C$49,B53,$F$35:$F$49)

인수별 의미는 다음과 같습니다. 인수에 절대참조와 상대참조가 섞여 있습니다.

첫번째 인수는 조건을 적용할 범위 [$C$35:$C$49]이며 상품에 관계없이 항상 동일한 범위를 참조하므로 절대참조임
두번째 인수는 합계를 구할 조건이며 [B53] 셀의 '모나미 볼펜'을 참조함. 행이 바뀌면 참조하는 위치도 바뀌어야 하므로 상대참조임
세번째 인수는 합계 구할 범위 [$F$35:$F$49]이며 상품에 관계없이 항상 동일한 범위를 참조하므로 절대참조임

수식이 정상적으로 입력이 되었다면 다음과 같이 수량 합계가 구해집니다.

이제 '자동채우기'로 [C53] 셀에 입력된 수식을 셀 범위 [C54:C57]에 채워넣어보겠습니다.

'자동채우기'를 하면 아래와 같이 상품별 판매금액 합계가 구해집니다.
[C54] 셀의 수식을 살펴보면 절대참조로 입력된 인수는 변하지 않고 그대로인 것을 확인할 수 있습니다.

3. 혼합참조

혼합참조는 상대참조와 절대참조 방식이 섞여 있는 참조입니다.

혼합참조는 열과 행참조 값 중에 하나에만 $가 붙어 있으므로 절대참조와 상대참조가 같이 있는 형태입니다. 그래서 이 참조방식을 혼합참조라고 부르며 행을 고정하면 '행고정 혼합참조', 열을 고정하면 '열고정 혼합참조'라고 합니다.

혼합참조는 실무에서 많이 쓰이므로 잘 익혀 두면 많은 도움이 됩니다.

1) 인센티브 계산

어떤 회사에서 영업사원의 판매실적에 따라 인센티브를 지급하는 경우를 가정해 보겠습니다.
판매금액에 5%, 10%, 15%를 곱해서 인센티브 금액을 구해야 할 때 다음과 같이 표를 작성할 수 있습니다.

<인센티브지급율별 인센티브 계산>

인센티브 금액을 구하기 위해 '상대참조' 방식으로 입력한다면 다음과 같이 수식을 입력할 수 있습니다.
=C64*D63

상대참조로 입력한 [D64] 셀의 수식을 복사하여 [D64:F66] 범위에 붙여 넣으면 다음과 같은 결과가 나옵니다.

뭔가 잘못되었습니다. 금액이 너무 커서 표시가 안되는 셀도 있습니다.

금액이 너무 적게 계산된 [E64] 셀을 확인해 보면 수식이 =D64*E63로 입력되어 있습니다. 상대참조로 입력된 수식을 복사해서 붙여넣었으므로 참조범위가 오른쪽으로 이동하면서 잘못된 셀을 참조합니다.

[E65] 셀의 수식도 잘못되었습니다. 판매금액과 인센티브지급율을 참조해야 하는데 엉뚱한 셀을 참조하고 있습니다.

이 문제를 해결하기 위해서는 행과 열이 바뀌더라도 '판매금액'과 '인센티브지급율'을 항상 참조하도록 '혼합참조' 수식을 입력해야 합니다. 그래야 수식을 한번만 입력하고 나머지 범위에는 붙여넣기만 해도 수식이 제대로 적용됩니다.

다음과 같이 [D64] 셀에 수식을 입력합니다.

=$C64*D$63 이 수식에서

[$C64]는 '$'를 붙여서 C열은 고정한다는 뜻이므로 수식을 옆으로 붙여넣어도 판매금액이 입력된 C열을 항상 참조합니다.
[D$63]은 '$'를 붙여서 63행은 고정한다는 뜻이므로 수식을 아래로 붙여넣어도 인센티브지급율이 입력된 63행을 항상 참조합니다.

이제 [D64] 셀을 복사하여 [D64:F66] 범위에 붙여넣으면 다음과 같은 결과가 나옵니다.

정상적으로 계산이 되었습니다. [D65] 셀의 수식을 확인해 보면 =$C65*D$63로 입력되어 있고 판매금액과 인센티브지급율을 정상적으로 참조하고 있습니다.

[E65] 셀의 수식도 확인해 보면 =$C65*E$63로 입력되어 있고 판매금액과 인센티브지급율을 정상적으로 참조하고 있습니다.

2) 판매일자별, 상품별 판매금액 집계

이번에는 실무에서 아주 빈번하게 사용되는 집계표를 만들어 보겠습니다.

다음 그림과 같이 '판매이력'자료가 있을 때 '판매일자별, 상품별 판매금액' 집계표를 만드는 예입니다.

[C70]셀에 다음 수식을 입력합니다. SUMIFS함수입니다. 여러 조건에 맞으면 값을 더해주는 함수입니다.

=SUMIFS($F$35:$F$49,$B$35:$B$49,$B70,$C$35:$C$49,C$69)

이 수식의 세번째 인수 '$B70'은 B열을 고정한다는 뜻이므로 수식을 다른 셀에 붙여 넣어도 판매일자가 있는 B열을 항상 참조합니다.
네번째 인수 'C$69'는 69행을 고정한다는 뜻이므로 수식을 다른 셀에 붙여 넣어도 상품명이 입력되어 있는 69행을 항상 참조합니다.

이제 [C70] 셀을 복사하여 [C70:G74] 범위에 붙여넣으면 다음과 같은 결과가 나옵니다.

정상적으로 계산이 되었습니다. [D70] 셀의 수식을 확인해 보면 =SUMIFS($F$35:$F$49,$B$35:$B$49,$B70,$C$35:$C$49,D$69)로 입력되어 있고 판매일자, 상품명을 정상적으로 참조하고 있습니다.

Tip) 상대참조,절대참조,혼합참조를 손쉽게 입력하기

상대참조, 절대참조, 혼합참조를 입력시 $를 입력하는 것은 생각보다 번거롭고 잘못 입력하는 경우도 많습니다.

다음과 같이 [A1] 셀을 참조한다고 할 때 마우스 커서를 'A1'으로 입력된 곳에 놓고 [F4] 키를 클릭하면, 번갈아 가면서 참조모드가 바뀌므로 손으로 입력하는 것보다 훨씬 수월하고 잘못 입력할 가능성도 줄어듭니다.

다음과 같이 여러 셀을 선택하고 [F4] 키를 클릭하면 마찬가지로 참조모드가 바뀝니다.

엑셀 수식 강좌

[ 수식 다루기 ]

[ 엑셀에서 입력과 표시방법 ]

[ 수식에서 셀과 범위 참조 방식 ]

[ 엑셀을 제대로 쓰는 데이터처리 ]

[ 엑셀 이름정의와 표기능 ]

[ 수식을 분석하고 오류 해결하기 ]

엑셀웍스 책 출간 안내

그동안 엑셀웍스에 올린 '엑셀 강좌', '함수 사용법', 여러 '실무 예제'에서 핵심만 뽑아 '된다! 엑셀 수식 & 함수' 책으로 출간하였습니다.

자세히 보기

온라인 구매처: 예스24 교보문고 알라딘