[엑셀기초강좌] 14강 – 엑셀에서 셀참조하기(상대참조,절대참조,혼합참조) [XLWorks]

공개됨 글쓴이 Admin댓글 남기기

14강에서는 엑셀에서 셀을 참조하는 방법을 알아보고자 한다.

셀을 참조하는 방법을 알아보기 전에 복습하는 의미에서 엑셀에서 셀의 위치가 무엇인지 다시 한번 살펴보자. 아래 그림에서
  • 수량 250은 C3라는 셀에 위치해 있다. C열의 3행에 있다는 의미이다.
  • 단가 100은 D3라는 셀에 위치해 있다.
  • 합계 25,000은 E3라는 셀에 위치해 있는데 셀 C3와 셀 D3의 값을 곱해서 구한 값이다.

엑셀에서 수식을 입력하면 이 셀의 위치를 참조하여 계산하는데 참조하는 방식에 따라서 상대참조,절대참조가 있고 두가지 방식을 섞은 혼합참조가 있다.

 

1) 상대참조

상대참조는 셀을 참조하는 위치가 상대적으로 바뀔 수 있는 방식을 말한다.
백견이 불여일타다. 아래와 같이 따라해 보자.

  1. E3셀(합계)에 수식  =C3*D3를 입력한다.
  2. E3셀의 수식을 복사해서(자동채우기도 된다) 아래행에 붙여 넣으면(E4부터 E7셀까지) 수식이 각 행에 맞게 복사된다.

E3셀의 수식은 =C3*D3 이었으므로 바로 밑에 복사된 수식을 확인해 보면 원래 값과 동일하게 =C3*D3가 되어야 하는데 =C4*D5로 입력되어 있다. 참조하는 열은 C열과 D열로 동일한데 행만 아래로 이동했으므로 행의 참조값인 3이 4로 자동으로 변경되어서 =C4*D4가 된 것이다.

수식을 한번만 입력하고 나머지는 그냥 복사만 하면 열과 행의 상대적 위치를 참조하여 수식이 자동으로 각 셀에 맞도록 입력되는 정말 편리한 기능이다. 

이렇게 상대적 참조가 가능하도록 입력되어 있는 셀을 “상대참조”를 사용한다고 부른다.

상대참조는 엑셀에서 정말 유용한 기능이다. 만약에 엑셀에 상대참조 기능이 없다면 우리는 위의 수식을 각 행마다 일일이 손으로 입력해야 할 것이다.

 

2) 절대참조

상대참조는 수식을 입력하는 위치가 바뀌면 행과 열이 이동한 만큼을 반영하여 수식이 바뀌므로 여러모로 편리하지만, 가끔은 참조하는 값의 위치가 바뀌면 안되는 경우도 있다.
아래와 같이 영업사원별 판매실적에 따라 인센티브를 지급하는 경우를 가정해 보자.  각 판매금액에 5%를 곱해서 인센티브 금액을 구하는 예인데…
아래와 같이 따라 해 보자
  1.  D11셀에 “=C11*C15” 를 입력한다.
    판매금액 250만원의 5%인 인센티브 금액 125,000원이 정상적으로 계산된다.
  2. 이제 D11셀에 입력된 수식을 복사하여 D12셀에 붙여 넣어보자.
    인센티브금액이 0원이다(표시하는 방식에 따라 0원이 하이픈으로 표시될 수도 있다).
    이지은씨와 전현무씨가 인센티브를 못받는 불상사가 생길 수 있다.

 

어떻게 된 일인지 아래 그림을 살펴보자.

  1. 판매금액 셀인 C12는 맞게  변경되어 있는데
  2. 인센티브율은 제대로 참조를 못하고 한칸 아래로 이동이 되어 있다.

이렇게 상대참조로 입력된 셀을 복사하면 셀 이동에 따라 위치가 자동으로 변경된다(복사하는 위치가 아래로 한칸 이동되었으므로 인센티브율도 한칸 아래에 있다고 가정하고 참조하는 위치가 한칸 아래로 이동된 것이다). 그래서 인센티브 금액이 0원이 된 것이다.

이 때 필요한 것이 참조위치가 바뀌지 않는 절대참조 방식인데 셀 참조값 앞에 $(달러)기호를 붙이면 된다. 아래의 순서대로 따라해 보자.

  1. 인센티브율 필드는 참조 위치가 바뀌면 안되므로 아래와 같이 “=C11*$C$15″라고 입력한다.
    인센티브율의 위치인 C15를 $C$15라고 입력하면 참조하는 위치가 변하지 않는 절대참조 방식으로 참조하는 것이다.
  2. 이제 D11셀의 수식을 복사해서 아래로 붙여 넣으면 정상적으로 인센티브금액을 계산한다.
  3. D12셀의 수식을 확인해 보면 =C12*$C$15로 입력되어 있다. 상대참조와 달리 인센티브율 참조위치가 바뀌지 않고 그대로 있다.

열과 행 참조 값 앞에 $를 붙이면 셀참조 방식이 절대참조 방식으로 변경되는 것이다.

.

3) 혼합참조

혼합참조는 상대참조와 절대참조 방식이 섞여 있는 참조이다.
아래 그림에서 매월 판매금액 합계를 구해야 하는데 단가의 경우 항상 C열에 고정이 되고 판매금액은 1월 2월… 이렇게 늘어나면서 열의 위치가 바뀐다.  이렇게 열은 고정이되고 행이 바뀌거나 하는 경우에 혼합참조 방식을 이용한다.

아래와 같이 따라해 보자.

  1. E19셀에  “=$C19*D19″를 입력한다.
  2. E19셀의 수식을 복사해서 G19에 붙여 넣는다. G19셀의 수식을 확인해 보면 단가를 참조하는 위치가 바뀌지 않고 그대로 유지되어 있다.

  3. 이번에는 G20셀의 수식을 확인해보자. 단가는 20행의 단가를 참조해야 하므로 행의 위치가 20으로 변경되었고 열의 위치는 C열로 고정되어야 하므로 변경되지 않았다. 정상적으로 참조하고 계산도 정확하게 된 것이다.
절대참조방식은 열과 행 앞에 모두 $를 붙여서 $C$19(C열19행 각각 절대참조)의 형태가 되는데 혼합참조는 $C19의 형태로 구성된다. 열과 행참조 값 중에 하나에만 $가 붙어 있으므로 절대참조와 상대참조가 같이 있는 형태이다. 그래서 이 참조방식을 혼합참조라고 부른다.

이것으로 14강을 마친다. 첨부의 실습용 엑셀파일을 다운로드 받아서 직접 실습해 보자.

첨부(실습용 엑셀파일) : [엑셀기초강좌] 14강 – 엑셀에서 셀참조하기(상대참조_절대참조_혼합참조).xlsx

 

[ 엑셀기초강좌 목록 ]

댓글 남기기

이메일은 공개되지 않습니다.