참조에 관하여..

ex_file.xls

수식을 작성할 때 참조에 관한 부분을 많은 분들이 접하면서 어렵다고 느낀다.
그러한 분들을 위해서 이번에는 참조의 종류와 사용되는 방법에 대해서 글을 쓰고자한다.

★ 참조의 종류
   - 상대참조
   - 절대참조
   - 혼합참조


1. 상대참조
   엑셀 안에서 수식을 작성한다고 가정해보자. 아래 표를 가정하자.
  

상여금을 구하는 공식이 [현금의 5%]라고 가정해보자.

수식은 이러할 것이다.
=E5*0.05
(5%를 숫자로 바꾸면 0.05이다.)
이를 반복작업으로 매줄 마다 해주어야 할 것인가?

많은 사람들이 알고 있고 사용하는 방법이 있다. 바로 채우기 기능이다.

 
F5에 수식을 작성한 뒤 F5셀 오른쪽 하단부에 있는 조그만 하얀 점 위에 마우스를 오버하면
검은색 십자가 모양의 커서로 바뀌게된다. 이때 드래그&드롭(Drag&Drop)을 하면, 셀에 있는 수식이 자동으로 채워지게 된다.
단, 이 과정에서 모든 수식이 E5*0.05가 되는 것이 아니다 자동으로 E6, E7, E8로 변경이 되어

E6*0.05
E7*0.05
...
....

와 같이 수식이 입력되는 것을 확인할 수 있다.
이 점이 바로 대량의 데이터를 쉽게 수식처리 할 수 있는 엑셀의 가장 큰 장점이라고 할 수 있다.











2. 절대참조
   위에서 상대참조를 배우면서 5%의 상여율을 적용해보았다.
한번 더 생각해보자. 임의의 셀에 5%라는 데이터를 입력해두고, 그 데이터를 수식으로 연결한다고 가정해보자. (이 표에서는 I2에 작성해보겠다.)


F5셀의 수식은 이렇게 되겠다.
=E5*I2
자 이제 수식 채우기 기능을 이용해서 채워보자.
눈치를 채야한다.

각 셀의 값은 [엑셀에서 기본으로 설정된 상대참조] 방식에 의해서 셀 주소가 바뀌게 된다.
=E6*I3
=E7*I4
=E8*I5
...
....

물론 원하는 결과값이 아니다.
즉, [수식 채우기를 통하더라도 움직이지 않도록 고정]을 해야 하는 셀이 생긴다.
F5셀에
=E5*I2
를 입력한 뒤, I2를 클릭해서 키보드상의 [F4]키를 눌러보자.
수식이
=E5*$I$2
로 바뀐 것이 보인다면 엔터를 누르고 수식채우기를 해보자.

원하는 결과값이 출력된다. 즉 $를 사용하면 행(I)이나 열(2)이 고정이 된다는 것이다.
말 그대로 절.대.움직이지 않는 참.조. = 절대참조이다.

한 예를 더 들어보자.
함수가운데 Rank라는 함수가 있다. 순위를 구하는 함수인데. 그 양식은 이러하다.

Rank(number, reference, [order])
 - number : 순위를 구하고자 하는 값
 - reference : 전체 값들의 목록
 - order : 오름차순 랭킹/ 내림차순 랭킹 으로 할 것인지 선택 (0 : 내림차순, 1 : 오름차순)


먼저 G5셀의 순수 지급액을 구하자.

H5셀에 Rank함수를 적용해보자.

order 값으로 아무 값도 입력하지 않으면 내림차순으로 Default를 설정한다.
순위로 5위가 나왔다면 [수식 채우기]를 해보자.

이대로 수행했다면, 1등이 2명, 2등은 3명, 3등은 2명, 4등은 없을 것이다. H12에 있는 셀을 확인해보면(H12셀에서 F2키를 눌러보면 수식을 확인할 수 있다.) Reference 부분이 한참 잘못되있다는 것을 알 수 있다.
그렇다. 절대참조를 이용해야한다.

H5셀로 이동해서 수식을
=RANK(G5,$G$5:$G$12)
로 바꾼 뒤 [수식 채우기]를 해보라.
원하는 결과를 얻을 수 있다.



3. 혼합참조
   절대참조를 이해했다면 혼합참조는 조금의 생각만 바꿔주면 된다. 좀전에 F4를 누르면 절대참조를 쉽게 사용할 수 있었다. (굳이 $를 써주지 않아도 되므로)
그 상태에서 F4를 몇번 더 눌러보자.
F5   →   $F$5   →   F$5   →   $F5
로 바뀌는 것을 알 수 있다.
F5는 상대참조
$F$5는 절대참조
F$5와 $F5가 혼합참조이다.

수식을 복사할 때 F$5는 ABCDEFG. 즉, 열은 변경될 수 있다는 것을 의미한다.
반면에 $F5는 123456. 즉, 행은 변경이 될 수 있음을 의미한다.

좀전에 사용했던 Rank함수에서 절대참조를 사용하지 않고 혼합참조를 사용한다면
=RANK(G5,$G5:$G12) …………………… ①
=RANK(G5,G$5:G$12) …………………… ②
중 어느 것이 같은 결과를 반환할까 생각해보자.


정답은 2번이다. 수식이 채워지는 방향은 상하방향이다. 즉 행(숫자부분)을 고정한다면 절대참조와 같은 효과를 발휘 한다는 것이다.
하지만 만약에 가로방향으로도 변경되고, 세로방향으로도 변경이되는 수식을 사용한다면 상대참조만으로는 해결할 수 없다는 것도 더불어 알아두자.



참조에 대해서 조금은 길고 두서없이 설명을 했다고 생각이 되는데.
이 글로 조금이나마 도움이 되었으면 하는 바램이다.

예제로 사용된 파일은 직접 실습해볼 수 있도록 파일로 올려드립니다.^^


다음에는 또 다른 기초엑셀 수업으로 찾아오겠습니다.