14강에서는 엑셀에서 셀을 참조하는 방법을 알아보고자 한다.
- 수량 250은 C3라는 셀에 위치해 있다. C열의 3행에 있다는 의미이다.
- 단가 100은 D3라는 셀에 위치해 있다.
- 합계 25,000은 E3라는 셀에 위치해 있는데 셀 C3와 셀 D3의 값을 곱해서 구한 값이다.
엑셀에서 수식을 입력하면 이 셀의 위치를 참조하여 계산하는데 참조하는 방식에 따라서 상대참조,절대참조가 있고 두가지 방식을 섞은 혼합참조가 있다.
1. 상대참조
상대참조는 셀을 참조하는 위치가 상대적으로 바뀔 수 있는 방식을 말한다.
백견이 불여일타다. 아래와 같이 따라해 보자.
E3셀의 수식은 =C3*D3 이었으므로 바로 밑에 복사된 수식을 확인해 보면 원래 값과 동일하게 =C3*D3가 되어야 하는데 =C4*D5로 입력되어 있다. 참조하는 열은 C열과 D열로 동일한데 행만 아래로 이동했으므로 행의 참조값인 3이 4로 자동으로 변경되어서 =C4*D4가 된 것이다.
수식을 한번만 입력하고 나머지는 그냥 복사만 하면 열과 행의 상대적 위치를 참조하여 수식이 자동으로 각 셀에 맞도록 입력되는 정말 편리한 기능이다.
이렇게 상대적 참조가 가능하도록 입력되어 있는 셀을 "상대참조"를 사용한다고 부른다.
상대참조는 엑셀에서 정말 유용한 기능이다. 만약에 엑셀에 상대참조 기능이 없다면 우리는 위의 수식을 각 행마다 일일이 손으로 입력해야 할 것이다.
2. 절대참조
- D11셀에 "=C11*C15" 를 입력한다.
판매금액 250만원의 5%인 인센티브 금액 125,000원이 정상적으로 계산된다. - 이제 D11셀에 입력된 수식을 복사하여 D12셀에 붙여 넣어보자.
인센티브금액이 0원이다(표시하는 방식에 따라 0원이 하이픈으로 표시될 수도 있다).
이지은씨와 전현무씨가 인센티브를 못받는 불상사가 생길 수 있다.
어떻게 된 일인지 아래 그림을 살펴보자.
이렇게 상대참조로 입력된 셀을 복사하면 셀 이동에 따라 위치가 자동으로 변경된다(복사하는 위치가 아래로 한칸 이동되었으므로 인센티브율도 한칸 아래에 있다고 가정하고 참조하는 위치가 한칸 아래로 이동된 것이다). 그래서 인센티브 금액이 0원이 된 것이다.
이 때 필요한 것이 참조위치가 바뀌지 않는 절대참조 방식인데 셀 참조값 앞에 $(달러)기호를 붙이면 된다. 아래의 순서대로 따라해 보자.
- 인센티브율 필드는 참조 위치가 바뀌면 안되므로 아래와 같이 "=C11*$C$15"라고 입력한다.
인센티브율의 위치인 C15를 $C$15라고 입력하면 참조하는 위치가 변하지 않는 절대참조 방식으로 참조하는 것이다.
- 이제 D11셀의 수식을 복사해서 아래로 붙여 넣으면 정상적으로 인센티브금액을 계산한다.
- D12셀의 수식을 확인해 보면 =C12*$C$15로 입력되어 있다. 상대참조와 달리 인센티브율 참조위치가 바뀌지 않고 그대로 있다.
열과 행 참조 값 앞에 $를 붙이면 셀참조 방식이 절대참조 방식으로 변경되는 것이다.
3. 혼합참조
아래와 같이 따라해 보자.
- E19셀에 "=$C19*D19"를 입력한다.
- E19셀의 수식을 복사해서 G19에 붙여 넣는다. G19셀의 수식을 확인해 보면 단가를 참조하는 위치가 바뀌지 않고 그대로 유지되어 있다.
- 이번에는 G20셀의 수식을 확인해보자. 단가는 20행의 단가를 참조해야 하므로 행의 위치가 20으로 변경되었고 열의 위치는 C열로 고정되어야 하므로 변경되지 않았다. 정상적으로 참조하고 계산도 정확하게 된 것이다.
이것으로 14강을 마친다. 첨부의 실습용 엑셀파일을 다운로드 받아서 직접 실습해 보자.
첨부(실습용 엑셀파일) : [엑셀기초강좌] 14강 - 엑셀에서 셀참조하기(상대참조_절대참조_혼합참조).xlsx
[엑셀 참조 더 알아보기]
[ 엑셀기초강좌 목록 ]
- 16강 - 엑셀에서 틀고정, 셀숨기기
- 15강 - 엑셀 시트 다루기(추가,삭제,이름바꾸기,숨기기 등)
- 14강 - 엑셀 상대참조,절대참조,혼합참조(셀참조 방식)
- 13강 - 엑셀에서 찾기 및 바꾸기
- 12강 - 엑셀에서 필터로 원하는 데이터만 보기
- 11강 - 엑셀에서 데이터 정렬하기
- 10강 - 엑셀에서 값,수식 복사하고 붙여넣기
- 9강 - 엑셀에서 채우기핸들로 값 입력하기(자동 채우기)
- 8강 - 엑셀에서 인쇄 설정하기(페이지,여백,용지 등)
- 7강 - 엑셀에서 차트만들고 꾸미기
- 6강 - 엑셀 시트 꾸미기(자료의 표시형식 바꾸기)
- 5강 - 엑셀 시트 꾸미기(선그리기,글자 색상/크기 바꾸기 등)
- 4강 - 엑셀 시트 편집하기(행과 열을 삽입/복사/삭제 하기)
- 3강 - 엑셀 함수 입력하기
- 2강 - 엑셀에서 값, 수식 입력하기
- 1강 - 엑셀 시작하기