엑셀[Excel] 상대 참조, 절대 참조, 혼합 참조 사용하는 법

엑셀을 셀로 인해서 많은 데이터 값들을 표현하고 연산합니다. 따라서, 많은 부분에서 편리한 기능을 제공합니다. 그래서 때론 셀에 입력된 값들을 토대로 연산이 이루어집니다. 이럴 경우 셀에 입력된 값이 아니라, 셀 주소를 바탕으로 연산이 이루어집니다.

 

그럼 다음 예시를 한번 보겠습니다.

Sample로 사용할 예시

이를 바탕으로 작업을 진행해보겠습니다. 

 

상대참조

엑셀에서는 상대참조를 가장 많이 사용합니다. 별다른 것 없이 복사만해도 사용이 가능하기 때문입니다. 그럼, 앞서서 포스팅한 내용에 달성률을 구하는 공식을 입력해보겠습니다.

달성률 구하는 공식입력

여기에서 최초 셀의 우측 하단에 네모 부분을 마우스 왼쪽 버튼을 눌려서 그림과 같이 드래그 해주세요. 그러면 자연스럽게 상대참조가 적용이 됩니다. 

달성률을 바탕으로 상대참조 적용하는 방법

그럼 위에서 상대참조를 적용한 부분을 토대로 보면, 다음과 같은 결과가 나옵니다. 즉, 각각 셀에 맞는 데이터을 바탕으로 연산을 진행하여 표현이 됩니다. 

달성률 연산의 상대참조를 적용한 결과 

그럼 위의 결과가 어떻게 셀에 적용이 되었는지 보기 위해서 다음과 같이 F6열을 눌려 확인해보겠습니다. 위에서는 분명 5열 기반의 데이터를 입력했는데 이번에는 6열 기반의 데이터가 적용됨을 알수 있습니다. 

상대 참조가 적용된 수식의 확인 

위와 같은 부분을 열에 대해서만 아니라, 행에 대해서도 정상 적용이 됩니다. 위의 방식과 동일하게 적용이 됩니다. 이를 한번 살펴보면 합계를 토대로 알 수 있습니다. 

행 기반의 상대 참조 결과 

 

절대참조

위에서는 상대 참조에 대해서 살펴봤습니다. 그럼 이번에는 절대 참조에 대해서 한번 살펴보고자 합니다. 먼저 위의 했던 방식대로 값을 입력하여 적용했습니다. 단 매출에서 판관비 및 제품원가 비율을 빼서, 영업이익을 구하는 공식을 한번 작성해 봤습니다. 

영업이익 입력

위의 공식을 토대로 셀을 복사해서 보니, 다음 그림과 같이 이상이 있습니다. 즉, 자연스럽게 상대 참조가 되다보니 H3셀에 있던 판관비 및 제품 원가 비율이 한단계씩 아래로 내려가다 보니, 다음 그림과 같이 문제가 생기는 부분과 연산이 정상적으로 이루어 지지 않습니다. 

 

문제가 발생한 수식

이를 바탕으로 판관비 및 제품원가 비율은 절대참조, 즉 셀이 복사되어도 셀의 위치가 변경되어서는 안됩니다. 이 부분에 대해서 이제 한번 살펴보겠습니다. 

 

생각보다는 간단합니다. 절대 참조를 하기 위한 부분에 '$'를 입력해주셔야 합니다. 그럼 공식은 다음과 같이 입력하셔야 합니다. 

=E5-(E5*$H$3)

절대 참조가 포함된 공식 입력

그럼 위의 절대 참조가 포함된 공식을 입력을 해보니 다음과 같이 정상적으로 입력이 됩니다. 한번 식을 봐보니, 9번째 열에 H3부분이 절대 참조가 적용됨을 알 수 있습니다. 

절대 참조가 포함된 공식 입력의 결과 

혼합 참조 

혼합 참조는 상대 참조와 절대 참조가 섞여 있는 것입니다. 쉽게 행 또는 열을 둘중에 하나를 고정해놓고 상대참조를 하는 방식이라고 생각하시면 됩니다. 물론, 위에서 절대참조 할때 고정하는 방법을 이미 배워서 알고 있을 것입니다. 그럼 바로 예시를 보면서 이야기 하겠습니다. 

 

혼합 참조를 이야기 할때 가장 쉽게 표현할 수 있는 것이 할인 가격을 알아보는 것입니다. 즉 제품 가격은 행은 고정되어야 하고, 각 할인가격의 열은 고정이 되어야 합니다. 그래서 다음과 같이 $C6 - ($C6-D$5)를 해서 위에서 와 같이 채우기를 하면됩니다. 

혼함 참조를 사용한 공식 입력

위의 공식을 입력하여 채우기를 하니 다음과 같은 결과가 나옵니다. 

혼함 참조를 사용한 공식 입력 결과 

그럼 간단한 팁을 드리겠습니다. =를 누르고 선택하고자 하는 셀을 클릭하시면 최초에는 상대 참조를 볼 수 있습니다. 여기에서 [F4]키를 누르면 참조 방식이 변경이 됩니다. 그 순서는 다음과 같습니다. 

 

상대 참조 - 절대 참조 - 행기반의 혼합 참조 -> 열 기반의 혼합 참조  

 

예시) 

F8 -> $F$8 -> F$8 ->$F8 -> F8

 

다음 순서로 진행이 됩니다. 이 부분을 쉽게 사용하시면 도움이 될 것이라고 생각합니다. 

 

오늘 포스팅에서는 상대 참조, 절대 참조, 혼합 참조에 대해서 살펴봤습니다. 그리고 F4를 이용해서 이 부분을 순차적으로 변경하는 방법에 대해서도 살펴봤습니다. 도움이 되시길 바랍니다. 혹시 궁금하시거나, 문의 사항이 있으시면 언제든지 댓글 및 방명록에 글 남겨주시길 바랍니다.