앞선 강좌와 마찬가지로 이번에는 엑셀 기초 강좌입니다.
할인율이 적용된 금액을 구해보면서 상대 참조 원리를 소개하겠습니다.
이전에도 다른 데이터로 상대 참조와 수식에 관련된 설명을 했지만 이번에 새로운 교육 교재를 만들면서 예제를 추가하게 되었습니다.
그럼 수식에 기본 내용을 짚고 넘어 가겠습니다.
수식은 결과를 구할 셀을 먼저 선택하고 =을 입력한 다음 계산할 숫자나 계산할 값이 들어 있는 셀 주소를 입력합니다.
일반적으로 수식은 실제 값보다 셀 주소를 사용하여 수식을 만드는데 셀 주소를 사용하여 수식을 작성하면 수식에 사용된 셀 값이 변경되더라도 수식을 고쳐 쓸 필요가 없기 때문입니다.
셀 주소의 값이 변하면서 자동으로 수식의 결과 값은 바뀝니다.
실습 파일 다운로드
정보화 교육 교재 입고 내역에서 금액을 구해보겠습니다.
금액을 구하는 식은 단가 × 수량 × (1-할인율)입니다.
1. 금액을 구할 셀 [G5]를 선택하고 =D5*E5*(1-F5)이라고 수식을 작성하고 <Enter>를 누릅니다.
단가*수량*(1-할인율) 식에서 1은 100%를 의미합니다.
결과적으로 단가*수량*95%가 정확한 결과를 얻는 수식이 됩니다.
2. 금액을 구한 다음 [홈]-[표시 형식]-[쉼표 스타일]을 선택하고 천 단위마다 구분기호를 표시합니다.
3. 채우기 핸들에 마우스를 맞추고 나머지 금액도 구해지도록 수식을 복사합니다.
‘정보화 교육 교재 입고 내역’에서 ‘컴퓨터 입문’의 할인율은 5%입니다.
이때 구할 금액은 단가에 수량을 곱한 결과에서 5%가 할인된 95%를 구하는 겁니다.
만약 금액을 구할 때 ‘단가*수량*할인율’을 하면 5%에 해당하는 할인금액을 구하게 됩니다.
그럼 잘못된 결과를 얻게 되는 거죠.
엑셀에서 수식과 함수식에 사용되는 산술 연산자를 정리한 표입니다.
상대 참조
아래 표에서 ‘컴퓨터 입문’ 교재 입고 금액을 구하는 수식을 봅니다.
수식에서 사용된 [D5], [E5], [F5] 셀을 참조 셀이라고 합니다.
수식에 사용된 주소와 실제 셀이 동일한 색상으로 쉽게 확인할 수 있습니다.
위의 표에서 ‘컴퓨터 입문’ 금액을 구하는 수식을 작성하고 나머지 한글에서 포토샵 교재의 금액 구하는 수식은 다시 작성하지 않습니다.
‘컴퓨터 입문’의 ‘금액’ 수식을 복사하면 나머지 교재의 금액은 자동으로 구해집니다.
이때 수식을 복사하면서 수식에서 참조하는 셀 주소도 복사한 위치에 맞게 변하는데 이렇게 주소가 변하는 참조 방식을 상대 참조라고 합니다.
[G6] 셀을 더블클릭하고 수식을 확인해 보겠습니다.
‘컴퓨터 입문’ 금액을 구하는 수식은 =D5*E5*(1-F5)이었습니다.
하지만 복사된 수식의 참조 셀이 변해서 한글 금액을 구하는 수식은 =D6*E6*(1-F6)이 되었습니다.
[G7] 셀도 더블클릭해서 확인해 보겠습니다.
[G5] 셀 수식을 [G7] 셀로 복사했더니 참조 셀이 변해서 수식이 =D7*E7*(1-F7)이 되었습니다.
상대 참조 원리에 의해 반복되는 수식을 여러 번 작성하지 않고도 수식 복사만으로 쉽고 빠르게 결과를 구할 수 있습니다.
구성비 구하기
이번에는 각 입고된 교재가 전체 입고 금액에 몇 %를 차지하는지 구성비를 구해보겠습니다.
바로 아래 그래프처럼 말이죠.
구성비를 구하는 식은 금액÷금액합계입니다.
2. 구성비 결과를 백분율 스타일로 변경하기 위해 [H5] 셀을 선택하고 [홈]-[표시 형식]-[백분율 스타일]을 선택합니다.
3. 그런 다음 [소수 자릿수 늘림]을 눌러 소수 첫 째 자리까지 나타냅니다.
구성비 수식을 채우기 하여 수식을 복사했더니 구성비가 제대로 구해지지 않고 #DIV/0!라는 오류가 생깁니다.
4. 오류의 원인을 확인하기 위해 [H6] 셀을 더블클릭해서 수식을 확인해 보겠습니다.
‘컴퓨터 입문’ 교재의 구성비 구하는 식은 =G5/G12입니다.
수식을 아래로 복사했더니 ‘한글’ 교재의 구성비 식이 =G6/G13으로 수식을 복사한 위치만큼 참조 셀도 변한 것(상대 참조)을 확인할 수 있습니다.
하지만 ‘컴퓨터 입문’의 금액 셀 [G5]는 상대 참조되는 것이 맞지만 ‘금액 합계’ 셀 [G12]는 참조 셀이 변하면 안 됩니다. 상대 참조한 [G13] 셀에 값이 비어있기 때문에 오류가 생길 수밖에 없습니다.
수식을 복사했을 때 ‘합계 금액’은 변하지 않도록 절대 참조해야 합니다.
절대 참조
그럼 절대 참조 방식으로 수식을 제대로 작성해 보겠습니다.
1. 수식을 수정하기 위해 [H5] 셀을 더블클릭합니다.
2. [G12] 셀에 커서를 두고 <F4>를 눌러 절대 참조 합니다.
다시 수식을 복사해 보세요.
정상적으로 구성비가 구해집니다.
[G6] 셀을 더블 클릭하고 수식을 확인해 보면 절대 참조한 $G$12 셀은 수식을 복사해도 변하지 않은 것을 확인할 수 있습니다.
영상 강좌도 준비했습니다.
학습에 도움이 되길 바랍니다.
'기초에서 실무까지 > 엑셀' 카테고리의 다른 글
| 엑셀 107강] 업무에 필요한 양식 만들기1 - 지출기안서 (3) | 2019.04.20 |
|---|---|
| 엑셀 실무 106강] IF, LEN, REPLACE, REPT 함수를 활용한 이름 가운데 글자 * 처리하기 (2) | 2019.01.16 |
| 엑셀 기초 104강] 워크시트 다루기 (1) | 2019.01.10 |
| 엑셀 기초 103강] 메모 삽입, 편집, 삭제 (2) | 2019.01.10 |
| 엑셀 102강] IF 함수를 사용하여 두 시트를 비교하여 상승 여부 표시하기 (0) | 2019.01.09 |