엑셀 상대참조, 절대참조, 혼합참조 예제

엑셀 상대참조, 절대참조, 혼합참조 예제

 

엑셀에서 수식을 제대로 활용하려면 상대참조, 절대참조, 혼합참조 개념을 자유롭게 사용할 수 있어야 합니다.

엑셀에서 수식은 맨 앞에 = 표시를 시작으로 함수나 셀주소, 시트명, 연산기호 등을 조합해 작성합니다.

간단한 수식으로 A1 셀과 B1 셀을 더하는 수식의 경우 =A1+B1 처럼 작성을 합니다.

이때 A1 이나 B1 처럼 셀주소가 들어가는데 셀주소가 상대참조인지, 절대참조인지, 혼합참조인지에 따라 계산이 달라집니다.

 

 

위 그림의 경우 C1 셀에 커서가 위치한 상태이므로 이름상자에는 C1 이라고 표시되고, C1 셀에 입력된 수식은 수식입력줄에 =A1+B1 표시된 것처럼 A1 셀과 B1 셀을 더하는 수식이며, =5+3 수식에 의해 8 이라는 숫자가 표시되는 것입니다.

 

수식내의 셀주소를 상대참조, 절대참조, 혼합참조로 변경하기 위해서는 수식입력줄의 A1 이라고 표시된 부분의 중간에 커서를 둔 상태에서 F4키(참조방식 변경키)를 눌러서 참조방법을 변경할 수 있습니다.

물론 F4키를 눌러서 변환하지 않고, 직접 $를 셀주소에 입력해도 무관하지만 F4키를 눌러서 변환하는 것이 오류를 예방할 수 있으므로 더 편리하다고 할 수 있습니다.

 

 

수식입력줄에서 A1 이라는 셀주소 중간에 커서를 두거나 A1 이라고 된 셀주소 전체를 범위 지정 후 F4 키를 누르면 누를 때마다 위의 그림처럼 참조방법이 변경됩니다.

 

상대참조, 절대참조, 혼합참조(행절대참조, 열절대참조)의 의미가 무엇이고 어떤 경우에 해당 참조방법을 사용하는지는 아래쪽에서 설명하기로 하고, 우선 참조 순서가 변경되는 과정부터 설명드리겠습니다.

 

수식입력줄에서 A1 이라는 셀주소 중간에 커서를 둔 상태에서 F4키를 누르면 A1 이라고 표시된 셀주소가 $A$1 로 변경이 됩니다.

이 상태에서 F4 키를 또 누르면 A$1 로 변경이 되고, F4키를 또 누르면 $A1 로 변경이 되며, F4키를 또 누르면 원래 상태인 A1 으로 변경이 됩니다.

 

위의 그림에서 변환되는 과정을 살펴보면,

처음 상태는 상대참조로 A1 이라고 표시되며,

F4 키를 눌러서 절대참조로 변환된 경우 A 앞에 $가 표시되고, 1 앞에도 $가 표시되어서 $A$1 이 되는 것입니다.

절대참조 상태에서 F4 키를 누르면 A 앞의 $ 표시가 없어져서 A$1로 변경되어 혼합참조(행절대참조) 상태가 됩니다.

혼합참조(행절대참조) 상태에서 F4 키를 누르면 A 앞에 $ 표시가 붙으면서 1 앞의 $ 표시는 없어져서 혼합참조(열절대참조) 상태가 됩니다.

혼합참조(열절대참조) 상태에서 F4 키를 누르면 A 앞의 $ 표시가 없어지면서 A1 으로 표시되어 상대참조 상태가 되는 것입니다.

 

셀주소는 위의 그림처럼 A1, $A$1, A$1, $A1 으로 4가지 방식이 순서적으로 변환되며, 열번호를 뜻하는 A 앞과 행번호를 뜻하는 1 앞에만 $가 올 수 있습니다.

 

셀주소에 F4키를 누르지 않고 직접 $를 입력해도 되지만 A1$, A$1$ 의 경우처럼 열번호를 뜻하는 A 앞과 행번호를 뜻하는 1 앞이 아닌 곳에는 $가 올 수 없습니다.

 

엑셀 상대참조 예제

엑셀 상대참조를 설명하기 위해 제품의 수량과 단가를 곱해서 금액을 계산하는 예제로 설명드리겠습니다.

 

 

위의 그림처럼 데이터를 입력하고 수량과 단가를 곱해서 금액을 구하는 예를 보이겠습니다.

 

 

위의 그림처럼 D4 셀에서 수식 =B4*C4 를 입력합니다.

쉽게 입력하는 방법은 이 글을 읽는 분들이라면 잘 아시겠지만 간단하게 설명드리면 다음과 같습니다.

D4 셀에서 = 을 입력하고, 키보드의 왼쪽 화살표를 2회 이동하면 =B4라고 표시됩니다.

* 를 입력하고, 키보드의 왼쪽 화살표를 1회 이동하면 =B4*C4 라고 표시됩니다.

 

이렇게 입력하면 위의 그림처럼 수식은 =B4*C4 라고 입력이 되어 있고, 결과는 1,000,000 이라고 표시됩니다.

위 그림의 풍선도움말이 가리키는 부분을 마우스 왼쪽버튼으로 더블클릭하거나, 그 부분을 마우스 왼쪽버튼을 누른 상태에서 D8 셀까지 끌어줍니다. 이런 방식으로 셀에 입력된 내용을 상하좌우에 있는 셀에 붙여넣기 하는 방식을 "채우기핸들"이라고 합니다.

 

 

 

위의 설명처럼 채우기핸들을 마친 상태의 경우를 보겠습니다.

위 그림의 D4 셀에는 =B4*C4라는 수식이 입력되어 있고, 2*500,000 의 결과인 1,000,000 이 표시되어 있습니다.

그리고 D5 셀에는 =B5*C5라는 수식이 입력되어 있고, 3*400,000 의 결과인 1,200,000 이 표시되어 있습니다.

D4 셀에 수식 =B4*C4 를 입력 후 채우기핸들을 했을 뿐인데 D5셀부터 D8셀까지 모두 계산결과가 정확히 나오는 것을 볼 수 있습니다.

 

여기에서 상대참조라는 개념을 살펴보겠습니다.

상대참조란 현재의 위치를 기준하여 어느 방향으로 어느만큼 이동되는가를 표시하는 것이 상대참조입니다.

 

학교 다닐때 교실에 앉아 있는 내 모습을 상상해 보겠습니다.

저는 앞에서 3번째, 왼쪽에서 2번째에 앉아 있다고 예를 들겠습니다.

앞의 선생님께서 질문을 합니다.

"본인을 기준으로 오른쪽으로 2칸, 뒤로 1칸에는 누가 있습니까?"

이 질문을 한 경우 교실내의 학생들은 모두가 다른 대답을 할 것입니다.

그 이유는 본인 위치에 따라 위의 질문에 대한 답이 달라지기 때문입니다.

 

위의 엑셀 예제에서 D4 셀의 수식 =B4*C4 가 위의 경우와 비슷한 것입니다.

수식에는 =B4*C4 라고 되어 있지만 이 의미는 D4 셀의 위치에서 봤을 때 B4는 왼쪽으로 2칸이고, C4는 왼쪽으로 1칸입니다.

그러므로 =B4*C4 라는 수식을 채우기핸들(복사의 의미와 비슷함)을 이용해 아래쪽 셀에도 붙여넣었지만 원하는 결과가 나온 것입니다.

D4 셀에서 봤을 때는 B4는 왼쪽으로 2칸이고, C4는 왼쪽으로 1칸이므로, "왼쪽으로 2칸에 위치한 값" * "왼쪽으로 1칸에 위치한 값" 이 되는 것입니다.

이 개념을 D5 셀에 채우기핸들을 통해 붙여넣기 하면 D5 셀의 위치를 기준으로 왼쪽으로 2칸에는 B5 셀이 있고, 왼쪽으로 1칸에는 C5 셀이 있기 때문에 =B4*C4 이 =B5*C5 로 붙여넣기 되는 것입니다.

 

이처럼 수식에 $ 표시가 없는 상대참조의 경우 눈에 보이기에는 B4, C4 셀을 가리키는 것처럼 보이지만 엄밀히 말하면 현재의 셀인 D4 셀을 기준했을 때 B4라고 표시된 것은 왼쪽으로 2칸이라는 뜻이고, C4라고 표시된 것은 왼쪽으로 1칸이라는 뜻이 되는 것입니다.

 

엑셀 절대참조 예제

 

이번에는 엑셀 절대참조 예제를 살펴보겠습니다.

 

 

위의 예제는 위쪽에서 사용한 예제에서 판매액을 기준으로 순위를 구하는 예제입니다.

 

 

엑셀에서 순위를 구하는 함수는 RANK입니다.

너무나 당연한 얘기이지만,

D4셀의 판매액 1,000,000원은 D4셀부터 D8셀의 판매액 중에서 몇위인가를 E4셀에 표시하는 것이고,

D5셀의 판매액 1,200,000원도 D4셀부터 D8셀의 판매액 중에서 몇위인가를 E5셀에 표시하는 것입니다.

 

순위를 구하는 RANK 함수를 사용해서 수식을 작성해 보겠습니다.

E4셀에 커서를 위치시킨 후 함수마법사 아이콘을 클릭하거나 함수마법사 단축키인 SHIFT+F3 키를 눌러줍니다.

함수마법사에서 RANK 함수를 선택합니다.

 

순위를 구하기에 앞서 순위를 구하는 RANK 함수의 인수를 우선 살펴보겠습니다.

1번째 인수인 Number 는 순위를 구하고자 하는 값이 들어있는 셀을 지정합니다.

2번째 인수인 Ref(Reference : 참고라는 의미)는 순위를 구할 때 참고할 영역입니다.

3번째 인수인 Order(순서라는 의미)는 오름차순인지, 내림차순인지를 정하는 것으로 내림차순일 경우 0을 입력하거나 생략하면 되고, 오름차순일 경우 0 이 아닌 값을 입력하는데 주로 1 을 입력합니다.

 

위의 그림처럼 1번째 인수에는 순위를 구하고자 하는 D4 셀을 선택하고,

2번째 인수에는 순위를 구할 참고영역으로 D4셀부터 D8셀까지를 범위지정하면 D4:D8 이라고 입력됩니다.

3번째 인수에는 내림차순으로 정렬하기 위해 0 을 입력해 줍니다.

 

 

위의 설명처럼 입력하면 E4 셀에는 수식 =RANK(D4,D4:D8,0) 이 입력되고, 결과는 2위가 표시됩니다.

위쪽에서 설명했던 것처럼 E4셀의 우측 하단에서 마우스 왼쪽버튼을 더블클릭하여 채우기핸들을 이용해 E4셀부터 E8셀까지를 붙여넣기합니다. 또는 E4셀을 선택 후 CTRL+C(복사) 누른 후, E5셀부터 E8셀까지를 범위 지정 후 CTRL+V(붙여넣기)를 눌러서 붙여넣기해도 됩니다.

 

 

위의 설명대로 채우기핸들을 진행하면 순위가 계산되어 나옵니다.

그런데 조금 이상한 것을 알 수 있습니다.

컴퓨터 판매액인 1,000,000원은 모니터 판매액 1,200,000원에 이어서 2위로 정확하게 계산되었지만,

3번째 줄의 마우스 판매액 60,000원은 5위인데도 3위로 계산되어 나왔기 때문에 순위 계산이 잘못된 것을 알 수 있습니다.

 

위의 그림 우측에 정리한 수식을 참고로 설명하겠습니다.

마우스 순위 계산식을 보니 순위를 구하고자 하는 판매액은 D6셀의 60,000원으로 맞지만,

참고하는 범위는 D6셀부터 D10셀로 지정되어 잘못된 것을 알 수 있습니다.

참고하고자 하는 범위는 컴퓨터의 판매액처럼 D4셀부터 D8셀이 되어야 하는데 D6셀부터 D10셀이 된 것입니다.

 

여기에서 상대참조와 절대참조라는 개념이 필요한 것입니다.

E4셀의 수식 중 참고영역 D4:D8이라는 의미는 E4셀에서 봤을 때는 왼쪽으로 1칸인 D4셀부터 왼쪽으로 1칸이면서 아래쪽으로 4칸인 D8셀을 가리키는 것입니다.

간략하게 정리하면 E4셀에서 D4:D8이라는 의미는 왼쪽으로 1칸부터 왼쪽으로 1칸이면서 아래쪽으로 4칸의 영역을 가리키는 것입니다.

위의 개념을 아래쪽 셀에도 붙여넣기 하면 마우스의 순위는 왼쪽으로 1칸인 D6셀을 가리키게 되고, 왼쪽으로 1칸이면서 아래쪽으로 4칸인 D10셀을 가리키게 되는 것입니다.

 

위의 오류가 무엇인지를 살펴보면,

2번째 인수인 순위를 구할 때의 참고영역은 고정된 위치여야 한다는 것입니다.

컴퓨터 판매액의 순위를 구할 때나 마지막의 스피커 판매액의 순위를 구할 때나 모두가 동일한 범위인 D4셀부터 D8셀까지를 가리키도록 해야 하는 것입니다.

 

 

순위 계산식이 잘못된 부분을 수정하기 위해 E4셀을 선택 후 함수마법사 아이콘을 클릭하거나 함수마법사 단축키인 SHIFT+F3키를 누릅니다.

그러면 위의 그림처럼 함수마법사가 나타나며, 그 중 2번째 인수로 입력된 D4:D8 이라고 입력된 부분 전체를 범위지정합니다.

이 때 D4:D8 의 영역 전체를 범위지정하지 않으면 다른 결과가 나오므로 2번째 인수 입력칸에서 D4:D8 전체를 범위지정하시기 바랍니다.

 

참조변경키인 F4키를 1회 눌러서 현재의 상대참조 방식을 절대참조 방식으로 변경해 줍니다.

그러면 2번째 인수 입력칸은 D4:D8에서 $D$4:$D$8 로 변경됩니다.

확인 버튼을 클릭하여 함수마법사를 마친 후 E4셀의 내용을 복사하여 E5셀부터 E8셀까지 붙여넣기 하거나, E4셀의 우측하단 부분에 마우스를 위치시켜서 진한 + 표시가 나올 때 마우스 왼쪽 버튼을 더블클릭하여 채우기핸들 기능으로 나머지 셀들을 채워줍니다.

 

 

 

이 과정을 진행하고 나면 E4셀부터 E8셀까지의 수식이 변경된 것을 알 수 있으며, 순위 결과도 정확하게 나오는 것을 알 수 있습니다.

컴퓨터 판매액의 순위를 구할 때나 모니터, 마우스, 키보드, 스피커의 판매액을 구할 때 모두 참고해야 하는 영역이 모두 동일할 경우 F4키를 눌러서 절대참조로 변경해 주시면 원하는 결과를 얻으실 수 있습니다.

 

 

여기에서 절대참조라는 개념을 좀 더 정리해 보겠습니다.

 

상대참조의 경우 현재의 위치를 기준으로 상하좌우 몇 칸의 위치라는 의미이지만,

절대참조의 경우 현재의 위치와는 무관하게 특정위치를 가리키는 것입니다.

 

위에서 예로 들었던 교실의 좌석을 예로 들어보겠습니다.

선생님께서 "앞에서 3번째 줄, 왼쪽에서 2번째 위치에 누가 있습니까?"라고 질문하신다면 학생들의 대답은 모두가 동일할 것입니다.

그 이유는 학생들의 각자 위치와는 무관하게 특정위치를 가리키는 질문이기 때문에 대답이 동일한 것입니다.

 

이처럼 절대참조는 현재의 위치와는 무관하게 고정된 위치를 가리키는 의미를 가지고 있습니다.

$의 의미가 바로 고정이라는 의미를 가지고 있습니다.

$D$4 의 의미는 D열을 고정하기 위해 D 앞에 $를 넣은 것이고, 4행을 고정하기 위해 4 앞에 $를 넣은 것입니다.

그러므로 $D$4는 D열도 고정, 4행도 고정하라는 의미가 되는 것입니다.

 

엑셀 혼합참조 예제

 

엑셀 혼합참조 예를 들어보겠습니다.

 

 

위의 절대참조 설명 마지막 부분을 자세히 읽어보시면 아시겠지만 위 그림의 구구단 예제를 사용하기 위해서는 셀주소에 $ 표시를 적절히 사용해야 한다는 것을 눈치채셨을 것입니다.

구구단을 구해보기 위해 위의 그림처럼 입력합니다.

 

 

3행은 단수를 의미하며, A열은 단에 곱하는 숫자를 의미합니다.

B4셀을 클릭한 상태에서 =키를 누르고 위로 1칸 이동하여 =B3 이 표시되도록 한 후 * 키를 누르고 왼쪽으로 1칸 이동하면 위의 그림처럼 =B3*A4 라고 입력됩니다. 이 상태에서 엔터를 쳐서 수식을 마무리하겠습니다.

 

 

위의 그림에 표시해 놓은 B4셀의 우측하단 부분을 더블클릭하여 B4셀부터 B12셀까지를 채우기핸들 기능을 이용하여 채웁니다.

그러면 채우기핸들이 진행되어 B4셀부터 B12셀까지가 채워진 상태로 범위지정이 되어 있는데 B12셀의 우측 하단에 마우스를 위치시켜서 진한 + 표시가 나오면 마우스 왼쪽버튼을 누른채로 드래그하여 I 열까지 끌어줍니다.

그러면 B4셀부터 I12셀까지 수식이 모두 채워지게 됩니다.

 

 

위의 그림은 B4셀부터 I12셀까지 모두 채워진 상태입니다.

그런데 구구단의 결과가 이상하네요.

당연히 참조 방식 때문에 문제가 된 것입니다.

 

B4셀의 경우 B3*A4셀을 가리켜서 2*1의 결과인 2가 나온게 맞지만,

B5셀의 경우 B4*A5셀을 가리켜서 2*2의 결과인 4가 나와 결과는 맞지만 참조하는 위치는 잘못되었습니다.

B6셀의 경우 B5*A6셀을 가리켜서 4*3의 결과인 12가 나와 원하는 계산식 2*3의 결과인 6 이 나오지 않는 것을 볼 수 있습니다.

 

 

위 그림의 수식을 참고해서 수정해 보겠습니다.

B4셀을 클릭 후 수식입력줄에서 B3의 중간에 커서를 위치 시킨 후 F4키를 2회 눌러서 B$3 이 되도록 하고, 수식입력줄에서 A4의 중간에 커서를 위치 시킨 후 F4키를 3회 눌러서 $A4 가 되도록 해 줍니다.

 

 

위의 설명대로 진행하게 되면 B4셀에는 =B$3*$A4 라는 수식이 입력됩니다.

B4셀의 우측하단을 더블클릭하여 채우기핸들 기능으로 B12셀까지 채워준 후, B12 셀의 우측하단에 진한 + 표시가 나오도록 마우스를 위치 시킨 후 드래그 하여 I12셀까지 채워줍니다.

위와 같이 진행하면 구구단의 결과가 원하는대로 나온 것을 알 수 있습니다.

 

여기에서 혼합참조라는 개념을 좀 더 살펴보겠습니다.

혼합참조에는 행절대참조 방식과 열절대참조 방식이 있습니다.

행절대참조라는 말의 의미는 행은 절대참조이고, 열은 상대참조라는 뜻이며,

열절대참조하는 말의 의미는 열은 절대참조이고, 행은 상대참조라는 뜻입니다.

행절대참조, 열절대참조라는 용어가 중요한 것은 아닙니다.

다만 $ 표시가 열 앞에 있는지, 행 앞에 있는지를 구분해야 한다는 뜻입니다.

 

=B3*A4 라는 수식을 =B$3*$A4 로 변경했는데, B3은 B$3 으로 변경되면서 3 앞에 $ 표시가 붙었고, A4는 $A4로 변경되면서 A 앞에 $ 표시가 붙었습니다.

3 앞에 $ 표시가 붙었다는 뜻은 3 은 변경하지 말라는 뜻이고,

A 앞에 $ 표시가 붙었다는 뜻은 A 는 변경하지 말라는 뜻입니다.

 

위의 구구단 예제를 보면 2단에서 9단까지의 단수는 3번행에만 있으므로 3 앞에 $를 붙인 것이고,

단수에 곱하는 1부터 9까지의 숫자는 A열에만 있으므로 A 앞에 $를 붙인 것입니다.

 

혼합참조를 쉽게 사용하기 위해서는 혼합참조 예제 첫 번째 그림처럼 수식을 입력한 상태에서 수식별로 어느 부분이 고정되는지 보면서 $ 표시가 고정하고자 하는 행이나 열 앞에 위치시켜 주면 되는 것입니다.

 

위의 구구단 예제의 경우 B4 셀의 수식 중 B3 이 가리키는 곳은 공통되는 부분이 단수로 3행에 있기 때문에 3 앞에 $를 붙이는 것이고,

A4가 가리키는 곳은 공통되는 부분이 곱하는 숫자가 A열에 있기 때문에 A 앞에 $를 붙이는 것입니다.

 

이처럼 혼합참조는 셀주소 중 열이나 행 중에서 1가지는 고정(절대참조)되고, 나머지 1가지는 변하는(상대참조) 경우를 말합니다.

 

 

상대참조, 절대참조, 혼합참조를 정리하면,

 

상대참조는 현재의 위치를 기준하여 어느 방향으로 어느만큼 이동되는가를 표시하는 것이며, 엑셀에서 작성하는 수식 중 90% 정도는 상대참조에 해당되기 때문에 일반적인 수식은 상대참조로 작성하시면 됩니다.

 

절대참조는 현재의 위치와는 무관하게 특정위치를 가리키는 것으로, 엑셀에서 작성하는 수식 중 RANK, VLOOKUP 함수의 사용이 대표적입니다.

 

혼합참조는 셀주소 중 열이나 행 중에서 1가지는 고정(절대참조)되고, 나머지 1가지는 변하는(상대참조) 경우를 말합니다.

혼합참조는 엑셀을 사용하면서 사용되는 경우는 많지 않습니다.

위의 구구단 예제는 혼합참조를 설명하기 위해 만든 예제일 뿐 실제 엑셀의 수식에서는 많이 사용되지는 않습니다.

다만 조건부서식에서는 자주 사용되는 편입니다.

 

 

위의 그림은 구구단 예제에서 사용된 수식이 어떻게 입력되어 있는지를 보여주는 그림입니다.

일반적인 경우는 엑셀에서 "계산 결과 대신 수식을 셀에 표시" 해 주는 기능을 사용할 일은 없지만, 엑셀 고급 사용자라면 알아두는 것도 좋을 것 같아서 간단하게 설명드리겠습니다.

 

 

엑셀의 왼쪽 상단 Offece 단추를 클릭한 후 엑셀 옵션 단추를 클릭합니다.

 

 

왼쪽의 메뉴 중 고급 메뉴를 선택 후 오른쪽 내용 중 "이 워크시트의 표시 옵션"의 항목 중 "계산 결과 대신 수식을 셀에 표시"에 체크합니다.

"계산 결과 대신 수식을 셀에 표시"에 체크할 경우 우리가 평상 시에 보던 화면과는 다른 화면이 보이게 됩니다.

우리가 평상 시 접하던 화면은 수식이 아니라 계산 결과가 셀에 표시되는 방식입니다.

그러므로 필요할 경우에만 "계산 결과 대신 수식을 셀에 표시"에 체크하여 계산 결과 대신 수식을 확인 후, "계산 결과 대신 수식을 셀에 표시"에 체크를 해제하여 계산 결과가 보이도록 변경해 두셔야 하는 것입니다.