사람들이 잘 모르는 엑셀 팁(Tip) 알아보기 : 참조에 대해 알아보기

 

 

 

 

참조는 상대참조, 혼합참조, 절대참조로 나뉠 수 있다. 참조를 위해서는 셀에 입력된 수식은 필요에 다라서 형태를 변경할 수 있다. 참조 형태를 설정하는 방법을 '참조방식'이라고 하며, 엑셀에서는 상대참조, 혼합참조, 절대참조 방법을 활용하여 다른 셀의 값을 참조할 수 있다. 참조의 형태에 따라서 참조 위치가 고정되거나 변경된다.

 

 

참조방식의 이해

앞에서 언급했듯이 참조는 방식에 따라서 상대참조, 절대참조, 혼합참조의 세 가지로 나뉜다.

 

▶ 상대참조

상대 참조는 절대참조 기호($)를 사용하지 않으며, 수식이 복사된 방향의 주소가 변경된다. 예를 들어 [A1] 셀을 참조하는 수식을 행방향으로 복사하면 [A2], [A3], [A4], ... 과 같이 수식의 행 주소가 변경된다. 열 방향으로 복사하면 [B1], [C1], [D1], ... 과 같이 열 주소가 변경된다.

 

▶ 절대참조

절대참조는 열과 행 주소 앞에 절대참조 기호($)를 사용하며, 참조 위치가 변경되지 않는다. 예를 들어 [A1]셀을 참조할 때 [$A$1]과 같이 입력되면 수식을 복사해도 항상 [A1]셀만 참조하게 된다.

 

▶ 혼합참조

혼합참조는 열 주소 또는 행 주소에만 절대참조 기호($)를 사용하며, '$' 기호가 사용된 주소는 변경되지 않는다. 예를 들어 [A1]셀을 참조할 때 [A$1]이라고 입력하게 되면 행 방향으로 복사할 때는 셀 주소가 변경되지 않지만, 열방향으로 복사하면 [B$1], [C$1], [D$1], ... 과 같이 변경된다.

 

 

영업팀 실적현황.xlsx

0.01MB

 

 

(1) 첨부파일을 다운로드하여 불러오기를 한다.

 

 

 

 

(2) 먼저 [H6:H16]까지의 값을 합계를 넣는 작업을 진행해 본다.

 

 

 

 

(3) [H6] 셀을 선택하고 [=C6+D6+E6+F6+G6]라고 입력하고 [ENTER KEY]를 누른다. [H6] 셀의 채우기 핸들을 [H17]셀까지 드래그해서 수식을 복사한다. [H17]셀을 클릭해 보면 수식이 [=C17+D17+E17+F17+G17]로 변경되어 있음을 확인할 수 있다. [H6] 셀에서 작성한 수시과 비교해보면, 행 주소가 모두 변경되었다. 이런 참조 방식은 복사 방향에 따라 참조 주소가 변경된다고 해서 [상대참조]라고 한다.

 

 

 

 

(4) 이번에는 절대참조를 이용하여 수정합계열을 작성해 보자. 먼저 [I6]셀을 클릭하고, [=H6*(1-C20)]의 수식을 입력하고 ENTER KEY를 누른다. [I6] 셀의 채우기 핸들을 [I17] 셀가지 드래그해서 수식을 복사한다. [H6]과 [I6]를 서로 비교해보면 수정합계가 변경되어 있는 것을 확인할 수 있지만, [H7]과 [I7]을 서로 비교해보면 두 개의 수정합계가 동일함을 확인할 수 있다. 마지막 [I17]셀의 수식값을 확인해 보면 [=H17*(1-C31)]로 변경되어 있는데 [C31] 셀의 값이 빈 공란이라서 결국 [H17] 셀의 값이 그대로 입력된 것임을 확인할 수 있다.

 

 

 

 

(5) [I6:I17]까지 범위를 지정하여 [DELETE KEY]를 눌러서 셀의 내용을 삭제한다. 다시 [I6]셀을 클릭하고, [=H6*(1-$C$20)]의 수식을 입력하고 ENTER KEY를 누른다. [I6] 셀의 채우기 핸들을 [I17] 셀가지 드래그해서 수식을 복사한다. (4)번에서 나타났던 값과는 다르게 [H]열과 [I]열이 다르게 나타난 것을 확인할 수 있다.

 

 

절대참조 기호($) 입력하기

셀을 참조할 때 주소를 고정하기 위해서 사용하는 절대참조 기호($)는 직접 입력할 수도 있고 [F4 KEY]를 눌러서 참조 방식을 변경할 수도 있다. [F4 KEY]는 토글키로 참조 주소를 입력한 다음 누를 때마다 다음과 같이 참조 방식을 순환하면서 변경한다.

 

 

상대참조 → 절대참조  → 혼합참조(행)  → 혼합참조(열)  → 상대참조

 

 

 

 

(6) 다음으로 [I6:I17] 범위의 값을 각각 110%, 120%, 113% 증액한 값을 [J6:L17] 범위에 입력해 본다.

 

 

 

 

(7) [J6] 셀을 선택하고 [=I6*(1+$J$5)]을 입력한다. [J6] 셀의 채우기 핸들을 [J17]까지 드래그해서 수식을 복사한다.

 

 

 

 

(8) 120%, 130% 증액하는 것도 같은 계산 방법으로 구해본다. [J6:J17] 범위가 지정되어 있는 상태에서 채우기 핸들을 이용해서 [L]열까지 드래그한다. 그러나 [K]열과 [L]열의 계산 결과 값의 크기가 너무 크게 나오는 것을 알 수 있다.

 

 

 

 

(9) 수식의 참조 위치를 확인하기 위해서 [L17]열을 클릭한다. 변경된 수식을 확인해 보면 [=K17*(1+$J$5)]라고 입력되어 있다. 계산이 정확이 입력이 되기 위해서 [I17]셀과 [L5] 셀이 참조가 되어야 하지만 [K17] 셀과 [J5] 셀이 참조되었음을 확인할 수 있다.

 

 

 

 

(10) 수식을 수정하기 위해서는 다시 [J6]를 클릭한다. 그리고 셀에 [=$I6*(1+J$5)]를 입력하고 [ENTER KEY]를 누른다. [J6] 셀의 채우기 핸들을 [J17]까지 드래그해서 수식을 복사한다. [J6:J17] 범위가 지정되어 있는 상태에서 채우기 핸들을 이용해서 [L]열까지 드래그한다. 이번에는 계산된 값이 정확하게 입력된 것을 확인할 수 있다.