|
엑셀기초강좌 11. 참조 (상대참조,절대참조,혼합참조)
엑셀은 수식에서 다른 셀에 있는 데이터를 포함해서 계산할 때, 해당하는 셀의 주소를 이용한다.
참조란 이런 셀의 주소를 나타내며, 참조를 통해 여러 셀의 데이터를 불러와서 수식을 간단하게 구성할 수 있다.
참조에는 A1 참조 형식과 R1C1 참조 형식이 있다.
R1C1 참조 형식은 매크로를 사용할 경우 이외에는 거의 사용하지 않으므로,
여기서는 A1 참조 형식을 중점적으로 익혀보도록 한다.
또한, 참조의 가장 중요한 개념인 상대참조와 절대참조에 대해 확실히 이해하도록 한다.
1. A1 참조 형식
1) 셀 참조
엑셀은 총 256개의 열에 대해 A~IV의 열번호가, 총 65536의 행에 대해 각 숫자의 행번호가 부여된다.
A1 참조 형식은 이 열번호와 행번호의 조합으로 셀의 위치(주소)를 참조하는 것이다.
예를 들어, C5는 C열과 5행이 만나는 위치의 셀을 가리킨다.
참조는 하나의 셀 뿐만 아니라 셀 범위를 지정할 수도 있는데 이 때는 :(콜론)을 사용한다.
|
참조 대상 |
참조 형식 |
| A열의 10행에 있는 셀 |
A10 |
| A열의 10행부터 20행까지의 셀 범위 |
A10:A20 |
| 15행의 B열부터 E열까지의 셀 범위 |
B15:E15 |
| 5행에 있는 모는 셀 |
5:5 |
| 5행부터 10행까지의 모든 셀 |
5:10 |
| H열의 모든 셀 |
H:H |
| H열부터 J열까지의 모든 셀 |
H:J |
| A열부터 E열까지의 10행부터 20행까지의 셀 범위 |
A10:E20 |
2) 다른 워크시트(Sheet)에 대한 참조 (동일 통합 문서에서만 해당함)
보통, 간단한 작업은 동일한 워크시트 내에서 이루어지지만,
데이터가 많을 경우 다른 시트에 있는 데이터를 참조해야 할 경우가 있다.
가령 현재 Sheet1 에서 작업하고 있는데, Sheet2의 B1:B10 범위에 대한 평균값을 구하고자 한다면,
아래의 그림과 같이 범위 참조 앞에 워크시트의 이름과 느낌표(!)를 붙여서 수식을 구성한다.
3) 상대 참조와 절대 참조
주소를 참조하는 방법은 상대참조, 절대참조, 혼합참조 세가지가 있다.
상대참조와 절대참조는 수식 사용 시 매우 중요하기 때문에 그 차이점과 사용방법을 숙지해야 한다.
간단히 설명하면, 상대참조는 수식으로부터 참조하는 셀의 상대위치를 기준으로 하는 방식이고,
절대참조는 참조하는 셀의 절대적인 위치를 사용하는 방식이다.
두 참조는, 단순히 한번의 수식계산 작업을 할 때에는 차이가 없지만,
복사 또는 자동채우기를 사용하여 수식을 다른 셀에 복사할 때 큰 차이가 생기게 된다.
① 상대참조
상대참조는 셀이나 범위에 대한 참조영역을, 수식이 있는 셀에 대한 상대위치를 기반으로 한다.
상대참조의 표기 방식은 A1 과 같이 열번호와 행번호의 조합을 그대로 사용한다.
참조가 상대위치이므로, 수식이 들어 있는 셀을 복사해서 다른 셀에 붙여넣기 하면 참조도 자동적으로 바뀐다.
아래의 그림과 같이 C4셀이 A1 셀을 참조하도록 =A1 을 입력해보자.
A1은 상대참조 형식이므로, 엑셀은 참조할 셀을 셀 주소 차이인 2열 왼쪽, 3행 위쪽에 있는 셀로 인식한다.
이 상태에서 C4셀을 복사(Ctrl-C)하여 C9셀에 붙여넣기(Ctrl-V)해보자.
그러면 C9셀에는 6이라는 값이 표시될 것이다. 수식입력줄에는 =A6 로 바뀐 것을 볼 수 있다.
즉, 상대참조를 복사하면 복사된 셀은, 2열 왼쪽, 3행 위쪽에 있는 셀인 A6를 참조하는 것이다.
② 절대참조
절대참조는 셀이나 범위에 대한 참조영역을 절대적인 셀 주소 그대로 참조한다.
절대참조의 표기방식은 $1과 같이 열,행번호 앞에 $ 기호를 붙인다.
참조가 절대위치이므로, 다른곳에 복사해도 참조위치가 바뀌지 않는다.
다음에서 C4 셀에 =$1 을 입력하면, =A1 입력시와 동일하게 A1셀의 값을 표시한다.
C4 셀을 C9 셀에 복사하면, 이번에는 상대참조와 다르게 A1 셀의 값이 표시됨을 알 수 있다.
수식입력줄에도 C4 셀과 동일한 =$1 이 표시되고 있다.
다른 예를 들어보자. 다음은 중력가속도 g 하에서 질량 m인 물체에 작용하는 힘 F를 구하는 테이블이다.
계산될 각 F에 대해 참조할 m 셀은 변경되도록 하고, g 값은 동일한 셀을 참조하려고 한다.
먼저 B5 셀에 수식을 입력한 뒤, 나머지 B6, B7 셀에는 자동채우기를 사용하여 값을 출력해보자.
B5 셀에 단순히 =B2*A5 를 입력해보자.
채우기 핸들을 사용하여 B7 셀까지 드래그한다.
완료된 후 결과를 보면 B6, B7 셀에 이상한 결과값이 출력됨을 알 수 있다.
B7 셀의 수식을 들여다보면, g 값으로 참조할 셀 주소가 B2 가 아닌 B4 로 바뀌었음을 알 수 있다.
상대주소를 썼기 때문에 자동으로 주소가 바뀐것이다.
따라서 이 경우에 B2 셀은 절대참조의 형식을 써야 한다.
B5 셀에 =$2*A5 라고 입력한다.
채우기 핸들을 이용하여 B6, B7 셀을 자동 계산하면, 이번에는 제대로 계산되었음을 알 수 있다.
B7 셀의 수식입력줄에서, 절대참조로 입력한 $2가 변경되지 않았음을 확인한다.
즉, 상대참조를 사용한 A열의 셀은 자동으로 참조가 변경되지만,
의해 절대참조로 고정된 B2 셀은 자동채우기를 해도 다른 곳을 참조하지 않고 항상 B2 셀만 참조한다.
③ 혼합참조
혼합참조는 행 또는 열 중에서 한가지는 절대참조를 사용하고 다른 한가지는 상대참조를 사용하는 것이다.
예를 들어, 의 경우는 열은 고정되지만, 행은 변경될 수 있다.
A$1 의 경우는 행은 고정되지만, 열은 변경될 수 있다.
혼합참조의 예제로 다음과 같은 구구단 테이블을 작성해보자.
A열과 1행에는 계산에 쓰일 수를 2~9 까지 입력한다.
그 후, 혼합참조를 이용해서 한개의 셀만 잘 입력하면 나머지는 자동채우기 기능으로 간단하게 끝낼 수 있다.
B2 셀에 =*B$1 을 입력한다. 즉,
A열 값을 참조할 때, 열 자체는 바뀌면 안되기 때문에 사용하였고, 행은 상대참조를 사용한다.
1행 값을 참조할 때, 행은 사용하여 고정시키고, 열은 상대참조를 사용한다.
다음으로 채우기 핸들을 사용하여 B2 셀에서 B9 셀까지 드래그 한 후 마우스 버튼을 뗀다.
2단 결과가 완성 된다.
B2:B9 셀 범위가 지정된 상태에서 다시 채우기 핸들을 사용하여 I9 셀까지 드래그 한다.
완료 후 I9 셀의 수식을 보면 열과 $1 행 성분은 고정되고 나머지는 자동으로 변경되었음을 알 수 있다.
2. Tip : 단축키
절대참조를 만들기 위해 일일이 붙이는 것은 상당히 번거로운 작업이다.
엑셀에서는 이 작업을 간소화하기 위해 단축키 [F4]를 지원한다.
수식을 입력하면서 참조 형식을 바꾸고 싶은 곳에서 [F4] 키를 누르면,
절대참조 → 혼합참조 → 상대참조 순으로 변경된다.
|
실험실소개
국제캠퍼스
