[Excel] (생!기초) 엑셀 구성비 사용법 / 상대참조와 절대참조

시나리오 1)

 

다음과 같은 표에서 각 제품이 차지하고 있는 구성비를 계산해보자. 구성비는 총량의 100% 단위를 기준으로 얼마만큼의 양을 차지하고 있는지 퍼센티지(%)를 나타내는 단위이다.

그러므로 다음 표에서는 총량을 나타내는 셀인 F14를 기준으로 각 제품의 총액을 나눠주기만 하면 각각의 상품들의 구성비를 계산할 수 있다.

 

 

일단 G7번 셀에 구성비를 구한다. 수식은 다음과 같을 것이다. " =F7/F14 "

 

 

" =F7/F14 "를 입력하면 결과 값이 소숫점으로 나오게 된다. 현재 값도 맞는 값이지만, 우리는 퍼센티지(%)로 표기하려고 하기 때문에 해당 셀을 클릭하고 우클릭을 눌러 위쪽에 백분율 스타일을 선택하자.

 

그럼 전체 100%중 소수점으로 나온 결괏값이 몇 퍼센트를 차지하는지 퍼센티지 (%)로 변경되는 것을 확인할 수 있다. 단축키로 변경하려면 Ctrl + Shift + %(숫자 5번키) 이다. (또한, 상단의 메뉴의 백 불율 스타일을 선택해도 똑같이 결과값이 나온다)

 

 

결과값을 구했으면 자동 채우기를 통해 F8~F12까지 구성비도 구할 수 있을 것으로 생각할 수 있다.

 

 

시나리오 2)

 

하지만 자동 채우기를 통해 결과값을 구하면 #Div/0! 라는 표시와 함께 제대로 된 값이 출력이 안된다. 왜 이런 결과가 나타나는지는 각 셀을 선택해보자.

 

 

자동 채우기를 이용한 수식을 보면 분자와 분모에 해당하는 참조하는 셀들이 모두 변하는 걸 알 수 있다. 즉, 분자는 기자재별 총액이 다르기 때문에 변하는 게 맞지만, 분모에 해당하는 전체 합계는 변해서는 안되는데, 자동 채우기의 기본적인 기능 때문에 분모의 값 역시 F14 이후 값이 없는 F15, F16등의 값을 참조한 것이다.

 

---

상대 참조와 절대 참조

위 시나리오 1과 2처럼 자동채우기등을 통해 셀을 복사하는 과정에서 첫 셀에서 부터 이동한 양만큼 각각 참조하는 셀의 위치가 변하는 것을 상대 참조라고 하며, 이와 반대로 변하지 않는 고정된 셀 하나만 가리키는 것을 절대 참조라고 한다.

 

즉, 상대 참조는 변하는 주소 값, 절대참조는 변하지 않는 주소 값이 라고 생각하면 될 것이다.

---

 

해결책)

 

이제 시나리오 2에서 문제가 되었던 #DIV/0! 를 해결해보자. 앞서 설명한 상대 참조 주소 값인 기자재별 총액은 변하는 것이 맞으니 그대로 두고 절대 참조에 해당하는 고정된 주소 값인 분모를 F14셀로 고정하면 제대로 된 구성비가 출력될 것이다.

 

단! 이때 일일이 분모의 값을 수기로 작성하면 시간이 많이 들기 때문에 절대 참조를 나타내는 수식을 사용해 자동 채우기를 이용하는 방법으로 분모를 고정시키도록 해야 한다.

 

절대 참조를 나타내는 수식은 다음과 같다. " =F7 / $F$14 "

 

 

G7을 클릭해 분자는 상대 참조를, 분모는 절대 참조를 나타내는 수식을 사용해서 구성비의 결과 값을 구한다. 그러면 수식은 시나리오 1과는 다르겠지만 결과값은 동일하게 나오는 걸 알 수 있다.

 

G7의 결과 값이 제대로 나왔으면, 다시 자동 채우기를 이용해 G8~G11까지의 결과값을 구하도록 하자. 

 

 

마지막으로 전체 구성비에 해당하는 100%가 소수점 올림으로 인해 100%를 넘어 버리니, 자릿수 늘림을 이용해 소수점 한 자리까지 표기를 해서 정확하게 100%로 맞춰버리자.

 

(구성비를 구할 때 백분율을 표기하면서 미리 자릿수 늘림을 하고 자동 채우기를 적용해도 된다.)

 

 

Bonus!

엑셀을 작성하다 보면 각 셀마다 수많은 수식을 사용하게 될 텐데, 각 셀의 데이터를 결과 값이 아닌 수식을 보고 싶을 때가 종종 있다. 이때는 Ctrl 버튼과 ' ~ '를 눌러보자.

 

그러면 수식을 사용한 셀에 결과 값이 아닌 사용자가 사용한 수식의 내용이 표기되는 걸 볼 수 있다.