14강 - 엑셀 상대참조,절대참조,혼합참조(셀참조 방식)

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*D4로 입력되어 있습니다.

참조하는 열은 C열과 D열로 동일한데 행만 아래로 이동했으므로 행의 참조값인 3이 4로 자동으로 변경되어서 =C4*D4가 된 것입니다.

상대참조는 수식을 한번만 입력하고 복사하여 붙여넣기만 하면 열과 행의 상대적 위치를 참조하여 수식이 자동으로 각 셀에 맞도록 입력되는 정말 편리한 기능입니다. 상대적 참조가 가능하도록 입력되어 있는 셀을 '상대참조'를 사용한다고 부릅니다.

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

 

2. 절대참조

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열로 고정되어야 하므로 변경되지 않았습니다. 정상적으로 참조하고 계산도 정확하게 되었습니다.

 

[엑셀 참조 더 알아보기]

엑셀 상대참조/절대참조/혼합참조 완벽 정리

 

[ 엑셀기초강좌 목록 ]

• 16강 - 엑셀에서 틀 고정, 셀숨기기
• 15강 - 엑셀 시트 다루기(추가,삭제,이름바꾸기,숨기기 등)
• 14강 - 엑셀 상대참조,절대참조,혼합참조(셀참조 방식)
• 13강 - 엑셀에서 찾기 및 바꾸기
• 12강 - 엑셀에서 필터로 원하는 데이터만 보기
• 11강 - 엑셀에서 데이터 정렬하기
• 10강 - 엑셀에서 값,수식 복사하고 붙여넣기
• 9강 - 엑셀에서 채우기핸들로 값 입력하기(자동 채우기)
• 8강 - 엑셀에서 인쇄 설정하기(페이지,여백,용지 등)
• 7강 - 엑셀에서 차트만들고 꾸미기
• 6강 - 엑셀 시트 꾸미기(자료의 표시형식 바꾸기)
• 5강 - 엑셀 시트 꾸미기(선그리기,글자 색상/크기 바꾸기 등)
• 4강 - 엑셀 시트 편집하기(행과 열을 삽입/복사/삭제 하기)
• 3강 - 엑셀 함수 입력하기
• 2강 - 엑셀에서 값, 수식 입력하기
• 1강 - 엑셀 시작하기