엑셀 - 상대참조,절대참조, 혼합참조

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

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

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

상대참조라? 말이 어렵지요.
 
이를 살피기 전 참조가 무엇인지를 집고 넘어가겠습니다.
엑셀에서의 참조란, 수식을 만들어 가면서 수식에서 필요로 하는 값들을 수식에 값자체를 삽입하는 것이 아니라 값이 들어 있는 셀을 수식에서 사용하는 것을 말합니다.

=10+20+30

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

=A1+A2+A3

이 수식에서는 셀을 참조하여 그 셀에 들어있는 값을 수식으로 가져오는 것이지요. A1 셀에 10이라는 수치값이 들어있고 A2 셀에 10이, 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 로 말이죠. 열방향으로 수식이 복사되지는 않기에 열이 변화할일은 없습니다. 행방향의 고정은 필요합니다. 이렇게 사용하는 것이 혼합참조입니다.

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

앞에서 살펴드려야 하는데 게시가 많이 늦었습니다. 용서해주세요.^^
궁금한점 있으면 질문해 주세요.