티스토리 뷰
엑셀 절대참조, 상대참조, 혼합참조 사용 예제
커피 한 잔의 여유 2013. 3. 27. 06:03
[링크]메모리스트 엑셀 함수 정리 목록 및 엑셀 팁 정리 목록
엑셀에서 수식이나 함수를 쓰다보면 상대참조, 절대참조, 혼합참조(열절대참조, 행절대참조)를 사용하게 된다. 사용법을 제대로 익히면 어렵지 않게 사용할 수 있지만, 개념을 정확히 파악하지 못하면 사용하는데 많은 어려움이 따른다.
엑셀에서 사용하는 수식은 기본적으로 상대참조를 기준해서 작성이 된다. 이는 대부분의 수식이 상대참조를 사용하기 때문이다. 상대참조의 의미는 아래에 설명한다.
참조방식을 바꾸는 단축키는 F4키이다. 수식입력줄에 계산식을 입력하고, F4키를 눌러서 변경하면 된다.
F4키를 누르면 아래 그림처럼 상대참조 ⇒ 절대참조 ⇒ 혼합참조(행절대참조) ⇒ 혼합참조(열절대참조) ⇒ 상대참조의 순으로 순환 반복된다.
상대참조를 활용한 합계 구하기
참조방식의 이해를 돕기 위해 합계를 구하는 예제로 설명한다.
아래 그림에서 총점 계산식은 E2셀에 자동합계 기능을 이용하거나, 함수마법사를 이용해서 =SUM(B2:D2)라고 입력한 후 채우기핸들을 이용해서 E3셀부터 E8셀까지를 채우게 된다.
채우기핸들이라는 의미는 쉽게 말하면 "복사 + 붙여넣기" 기능이다. 그러므로 E2셀에 =SUM(B2:D2)라고 입력된 셀을 복사하여 E3셀부터 E8셀까지를 붙여넣기한 것이라고 할 수 있다.
여기서 의문이 들게 된다. E2 셀에 =SUM(B2:D2) 라고 입력을 했고, 결과는 233 이 나왔다. 이 값을 복사해서 붙여넣기 하면 그 아래에도 모두 233 이라는 결과가 나와야 하는데 너무나도 당연하게 각 행에 대한 합계가 계산되어 나온다.
왜일까? 그건 바로 계산식이 상대참조이기 때문이다.
엑셀에서의 주소값은 기본적으로 상대참조로 계산되기 때문이다.
여기서 상대참조란 셀이 위치한 곳을 기준해서 상하좌우로 몇 칸째인가라는 것을 가리킨다는 것이다.
E2셀의 233 이라는 결과의 계산식은 =SUM(B2:D2) 이다.
여기서 B2 셀은 E2셀로부터 왼쪽으로 3칸 위치에 있고, D2는 왼쪽으로 1칸 위치에 있다.
즉 =SUM(B2:D2) 이라는 뜻은 E2셀을 기준하면 왼쪽으로 3칸부터 왼쪽으로 1칸의 위치까지의 범위에 대한 값에 대해 합계를 내라는 뜻이다.
이 의미를 아래로 복사하게 되므로 E3셀에서는 2행에 대한 합계를 내는 것이 아니라 3행에 대한 합계를 계산하는 것이다.
설명이 너무 길어진 느낌이 든다.
정리하면,
상대참조란 셀의 현재 위치를 기준한 좌표라는 뜻이다.
절대참조란 셀의 현재 위치와 무관하게 무조건 해당셀을 가리킨다는 뜻이다.
혼합참조는 상대참조와 절대참조가 섞여 있으며, 행절대참조와 열절대참조로 나뉜다.
행절대참조는 행은 절대참조이며, 열은 상대참조라는 뜻이고,
열절대참조는 열은 절대참조이며, 행은 상대참조라는 뜻이다.
혼합참조는 아래의 아래쪽에서 구구단 예제로 설명한다.
절대참조를 활용한 순위 구하기
절대참조의 예는 순위를 구하면서 설명한다.
순위는 RANK 함수를 이용해서 구한다.
먼저 RANK 함수의 인수를 설명하면,
첫 번째 인수는 순위를 구하고자 하는 셀의 위치이고,
두 번재 인수는 순위를 구하고자 하는 셀들의 범위이며,
세 번째 인수는 정렬 순서로 아래의 예에서는 생략해서 내림차순으로 정렬하였다.
순위를 구하기 위해 F2셀에 =RANK(E2,E2:E8)이라고 입력하였다.
이는 E2셀에 있는 총점 233점이 총점 범위(E2:E8)에서 몇 등인가를 나타낸다는 수식이다.
233 점은 아래의 총점 영역에서 4등에 해당한다.
그래서 채우기핸들을 이용해서 아래의 순위 범위도 채워보자.
그랬더니 233점과 231점이 동일하게 4위로 나오는가 하면 점수가 다른데도 1위가 4명이나 나오게 되는 오류가 발생한다.
이는 순위를 구하고자 하는 각각의 총점은 상대적으로 한 칸씩 아래로 내려가도 되지만, 범위는 바껴서는 안되기 때문이다. 아래 그림의 수식을 유심히 보면 범위 또한 한 칸씩 따라서 내려간 것이 보인다.
이 문제를 해결하기 위해서는 범위에 해당하는 E2:E8 영역을 지정할 때 함수마법사에서 단축키 F4키를 눌러서 절대참조로 변환해 주어야 한다.
아래 그림처럼 절대참조를 지정하게 되면 순위가 원하는대로 나오게 된다.
수식을 보면 각각의 총점 위치는 E2에서 E3, E4... E8로 변경되지만, 총점 영역(E2:E8)은 절대참조 형식($E$2:$E$8)을 그대로 유지하는 것을 볼 수 있다.
이처럼 범위가 변경되지 않는 경우는 F4키를 눌러서 절대참조를 눌러주면 해결된다.
엑셀에서 함수 사용 시 F4키를 누르는 경우의 95% 이상이 절대참조로 바꾸는 경우에 해당한다.
혼합참조를 활용한 구구단 예제
혼합참조의 이해를 돕기 위해 자주 사용되는 구구단을 예로 들어 설명한다.
먼저 혼합참조란 열이나 행 중에서 하나만 절대참조이고, 나머지 하나는 상대참조라는 뜻이다.
이론적인 것은 너무 복잡하니 실제 예제에서 어떻게 사용되는지를 살펴보자.
구구단을 계산하기 위해서는 B2셀에 계산식을 넣고 채우기핸들을 이용해서 B2부터 I10셀까지를 채우면 된다.
아래의 예제는 B2셀에 수식 =B1*A2 를 넣고 채우기 핸들을 적용한 예이다.
아래 그림처럼 상대참조를 이용한 계산에서는 원하는 결과가 나오지 않는다.
구구단의 계산이 아니라 위칸과 왼쪽칸을 곱한 결과가 나오게 되는 오류를 볼 수 있다.
이 문제를 해결하기 위해서는 상대참조도 아닌, 절대참조도 아닌 혼합참조를 사용해야 한다.
아래의 그림처럼 결과가 나오도록 하기 위해서는 수식을 =B$1*$A2라고 입력해야 한다.
여기서 B$1이라는 것은 B는 상대참조이고, 1은 1 앞쪽에 $가 붙어 있으므로 절대참조라는 뜻이다.
상대참조는 행이나 열을 나타내는 영문자나 숫자만 오게 된다. 즉 B만 나오거나 1만 나오는 경우이다.
절대참조는 행이나 열을 나타내는 영문자나 숫자 앞에 $를 붙이는 것이다. 즉 $B , $1등의 경우이다.
여기서 $를 어디에 붙여야 하는가가 문제이다.
하지만 이 방법만 알면 쉽게 혼합참조를 사용할 수 있다.
아래의 예제에서 B1의 위치에는 구구단의 2라는 숫자가 있고, 이 값은 2단이라는 것을 의미하며, 3단이나 4단, 5단 등은 모두 1번행에 위치하고 있다. 그러므로 1번행에서 벗어나면 안되므로 1 앞에 $가 오면 된다.
A2의 위치에는 곱하는 수 1부터 9까지가 해당되며, 이 값들은 모두 A열에만 위치하고 있다. 그러므로 A열을 벗어나면 안되므로 A앞에 $가 오면 된다.
정리하면 계산식에서 참조하고 있는 좌표값이 어느 열에 위치하는가, 어느 행에 위치하는가를 판단해서, 고정된 열이나 행을 나타내는 문자앞에 $를 붙여주면 되는 것이다.
여기서는 1행(2~9)과 A열(1~9)을 참조하므로 1과 A앞에 $를 붙이면 된다.
아래는 위의 수식을 적용한 결과화면이다.
혼합참조는 조건부서식을 적용할 때 자주 사용하게 되는데, 특정열이나 특정행의 값을 참조해서 해당셀의 배경색을 바꾸는 경우가 그 대표적 예다.
저는 위 상품을 소개하면서 포인트를 받았습니다.