[엑셀] 상대 참조, 절대 참조, 혼합 참조의 차이 배우기

이번 게시물에서는 엑셀 함수는 잠시 쉬었다 가려 합니다. 엑셀의 함수를 좀 더 활용하여 그 함수의 기능을 최대한 활용하기 위해서 함수와 엑셀의 여러 수식에서 끊임없이 사용되는 참조의 여러 기법들을 자세히 살펴봅니다.

이미 다루었던 엑셀 함수 설명에서도 절대참조와 상대참조의 차이가 나왔습니다만 그 설명이 빈약한 것 같아 페이지를 할애하여 좀 더 세부적으로 살펴볼까 합니다.

엑셀에서의 여러 참조 기법들 중 아마도 가장 쉽게 접하고 가장 자주 접하는 참조가 바로 상대참조 입니다.

상대참조 라? 말이 어렵지요. 이를 살피기 전 참조가 무엇인지를 집고 넘어가겠습니다.

엑셀에서의 참조란, 수식을 만들어 가면서 수식에서 필요로 하는 값들을 수식에 값 자체를 삽입하는 것이 아니라 값이 들어 있는 셀을 수식에서 사용하는 것을 말합니다.

=10+20+30

이란 엑셀 수식이 있습니다. 이 수식에서는 참조가 사용되지 않았습니다. 10, 20, 30 이라는 수치 값 자체를 수식에 입력하여 사용하였지요. 하지만 아래의 수식을 보시면 다릅니다.

=A1+A2+A3

이 수식에서는 셀을 참조하여 그 셀에 들어있는 값을 수식으로 가져오는 것이지요. A1 셀에 10 이라는 수치값이 들어있고 A2 셀에 20 이, A3 셀에 30 이란 수치가 들어있다면 이 수식은 위의 수식과 같아지는 것이지요.

이렇게 수식에 참조를 사용하여 얻을 수 있는 잇점은 만일 그 셀의 값이 변하게 된다면 셀을 참조하여 만들어진 수식의 계산 결과도 자동으로 변한다는 것입니다. 엑셀과 같은 스프레드시트(표계산프로그램)를 사용하는 목적이기도 합니다.

참고> 아직 엑셀에서의 셀 주소에 어려움을 겪고 계신분들을 위한 참고입니다
엑셀에서의 기본으로 사용되는 셀 주소 체계는 알파벳으로 되어있는 열 머리글과 숫자로 되어있는 행 머리글을 순서대로 나열하여 그 셀의 주소를 정합니다.
열머리글은 B 이고 행머리글이 10 인 셀의 주소는 B10 이 된다는 것이지요.
그리고 범위를 주소로 지정하기 위해서는

'시작 셀의 주소:종료 셀의 주소' 형태로 범위 주소가 만들어 집니다.
시작 셀이 A3 이고 종료 셀이 C10 이라면 이들을 범위로 지정하여 주소로 나타내려면

A3:C10

이 되는 것입니다. ':' 으로 시작 셀과 종료 셀을 나누어 줍니다.

수식의 변화없이 참조값이 변함에 따라 그 값이 갱신된다는 것은 엄청난 작업의 효율을 가져옵니다.
엑셀에는 수식 복사라는 기능이 있습니다. 이 기능을 이용 수식을 복사해 나가면 신속하게 작업을 완료할 수 있는 것이지요.

예) 한 학교의 선생님이 그 반 학생들 40명의 성적을 계산한다고 합니다. 성적이야 각기 다르기에 직접 입력할수 밖에 없지만 총점과 평균은 위의 참조를 이용하면 쉽게 작업을 끝낼 수 있습니다. (만일 한명 한명의 성적을 각각 총점을 내고 평균을 구한다면 어떨까요?)

 

E3 셀에서 번호 1번의 국어, 영어, 수학 점수를 총점 낸다고 합시다. 셀을 참조하여 총점을 구했습니다. 이렇게 한명의 총점이 구해 진다면 나머지 39명의 총점은 수식이 동일하기에 수식을 복사하면 쉽게 해결 총점을 얻을 수 있고 위의 그림에서 보시 듯 채우기핸들 을 사용하시면 엑셀에서 수식 복사는 쉽게 해결됩니다.
하지만 수작업으로 총점을 구한다면 각각의 점수를 다시 입력하여야겠지요. 중복된 작업이 필요합니다.

여러분은 어느방법을 사용하시겠습니까?

이제 참조가 무엇인지 아셨으리라 믿습니다. 바로 엑셀 상대참조를 살펴봅니다.

상대참조? 도대체 어떤 참조를 상대참조라 할까요? 바로 위의 선생님이 참조하신 방법을 엑셀에서는 상대참조 라 부릅니다. ^^
E3 셀에서 수식을 E4 셀에 복사 하였습니다. 그런데 수식이 바뀌어 있는 것이 보이십니까? E3 에서 셀의 위치가 E4 로 바뀌었습니다. 행이 하나 증가한 것이지요. 당연히 총점을 구하는데 사용되는 데이터 국어, 영어, 수학 점수도 행이 증가하여야 겠습니다.

E3 셀에서 E4 셀로 수직 방향, 곧 행이 1 증가하면 E3셀의 수식에서 사용되는 참조 셀들의 주소도 행방향(수직)으로 1증가 합니다. 결국 위의 그림과 같이 =B3+C3+D3 가 =B4+C4+D4 가 되는 것입니다.

이렇게 수식에서 참조를 사용하고 이 수식이 다른 셀로 수식 복사될 때 그 수식에서 참조하던 셀들의 주소도 같은 방향 같은 크기로 변환 하는 것을 엑셀에서 상대 참조라 합니다.

상대참조, 말 그대로 상대적인 참조 라는 뜻이고 참조하던 수식 위치가 변하면 그 방향 그 크기로 참조 셀 주소도 변하는 참조입니다. 엑셀에서 기본으로 상대 참조로 지정되어 있습니다. 가장 많이 쓰이는 참조 기법이기도 하구요.

절대참조로 들어가 봅니다.

절대적인 참조라는 말이겠죠^^. 엑셀에서 그것도 셀 참조에서 절대적이란 말의 의미는 무엇일까요?  위의 엑셀 상대참조가 수식 복사 방향에 따라 셀 주소가 변한다면 이 절대 참조는 셀을 참조하는 수식을 어디로 복사하더라도 그 참조 주소가 변하지 않는 참조 입니다.

한마디로 참조하는 셀의 주소를 고정시키는 것이지요. 그런다면 이 참조는 절대참조라고 엑셀에 전달하려면 어떻게 해야 할까요?

네, 셀 주소의 앞에 $를 추가 합니다. 위의 그림에서 어느 셀로 수식 복사를 하더라도 E3 셀에서의 주소를 그대로 표현하려면
=$B$3+$C$3+$D$3 라고 각 셀 주소의 열 머리글과 행 머리글의 앞에 $를 추가 합니다.

이런 엑셀 절대 참조를 사용하는 경우는 이렇습니다.

예) 달러를 환율에 근거하여 원화로 변환한다면 달러값에 각각 환율을 직접 넣어야 할까요? 환율표를 작성해 두고 이를 이용하면 되겠지요.

[엑셀 절대참조 예제 그림]

위의 그림을 살펴봅니다. C3 셀에 한 번 수식을 입력하고 이를 아래로 수식 복사합니다. 수식 복사 방향에 따라 달러의 값은 변화해야겠지요. 하여 엑셀 상대 참조를 사용합니다. 하지만 중요한 것은 달러당 원화의 값이 입력된 셀 주소 E3 를 어떻게 참조하여야 할까요?

C3 에서의 수식을 아래로 복사하지요? 하여 달러값은 상대 참조 하였습니다.  이 달러값에 곱해지는 달러당 원화값 E3도 상대 참조를 한다면 어떻게 될까요? C3를 C4에 수식 복사하면 B3 값은 B4 로 변합니다. E3 도 E4 로 변하겠지요. 하지만 E4 셀은 비어있습니다. 결국은 E3셀은 변하면 안되는 것이지요.

하여 E3 를 수식복사시 고정하기 위해 엑셀 절대 참조를 사용합니다. 달러는 상대참조하고, 달러당 원화는 절대참조 하여야 완벽한 수식이 되는 것이죠.

이럴때 엑셀 절대 참조가 필요합니다.

이것이 엑셀 절대 참조의 힘이죠^^. 환율이 변하여 E3 값을 변경하면 이 셀을 참조하던 모든 셀들의 값, 원화 부분이 되겠네요. 이 부분이 변화하지요.

이것이 엑셀에서 말하는 절대참조 입니다.

마지막으로 상대참조와 절대참조가 뒤섞인 혼합참조란 것도 있습니다.

엑셀 상대 참조, 참조 셀 주소가 수식의 위치에 따라 변화하는 참조 유형.
엑셀 절대 참조, 참조 셀 주소가 고정되어 수식의 위치가 변해도 셀 주소가 고정되는 참조 유형.

이 둘을 섞는다? 그 말이 뭘 말할까요?

=$B5+$C5

위의 수식을 보시면 달러가 셀 주소 앞에 추가 되어 있습니다. 하지만 이는 절대참조가 아닙니다. $ 가 열머리글에는 붙어있는데 행머리글(숫자)에는 없지요. 열만은 고정시켜 절대 참조하고 행은 상대 참조하여 변하게 하겠다는 것입니다.

반대의 경우도 있습니다. 열은 상대참조하고 행을 절대참조 하는 것이지요. 이렇게 절대참조와 상대참조가 함께 사용되는 셀주소의 참조유형을 엑셀 혼합 참조 라고 합니다.

위의 절대 참조 예제 그림에서 달러당 원화를 절대 참조 하였습니다. 왜 절대 참조 하였습니까? C열에서 수식이 아래 방향(행방향)으로 수식복사 되기 때문이지요. 하여 E3 셀의 주소를 보호하기 위해서 절대 참조를 하였습니다. $E$3 로.

하지만 이 참조를 혼합 참조로 사용하셔도 좋습니다. 행 방향으로 수식 복사되기에 행을 고정시키는 것이지요.

E$3 로 말이죠. 열 방향으로 수식이 복사되지는 않기에 열이 변화할 일은 없습니다. 행방향의 고정은 필요합니다. 이렇게 사용하는 것이 엑셀 혼합 참조입니다.

@ 이상으로 엑셀 상대 참조, 절대 참조, 혼합 참조 를 살펴보았습니다. 엑셀 혼합참조에 대해서는 제대로는 살펴드리지 못했지만 상대참조와 절대참조를 이해하신다면 혼합 참조도 이해가 가실 것입니다.

앞에서 살펴드려야 하는데 게시가 많이 늦었습니다.

질문있으시면 댓글 달아주세요.

이상 마늘빵 이었습니다.