아무개의 취미생활
1. 목표값 찾기 사용하기
목표값 찾기는 셀에 입력한 식에 의해 계산되는 값을 사용자가 원하는 값으로 고정시키고 식에 있는 변수들이 어떻게 변하는지 확인할 수 있는 기능입니다.
가령 y=10x+5라는 일반적인 방정식에서 y값이 50일 때 x값은 얼마인지 구하려면 방정식을 풀어 계산하면 되지만 엑셀에서는 목표값 찾기 기능을 사용하여 x값을 구할 수 있습니다.
그럼 y=10x+5를 [그림1]과 같이 엑셀 시트에 입력했습니다.
C3 셀에는 x값을 입력하는 셀이고 C4 셀에는 y값을 구하는 식을 입력하였습니다.
C4 셀에 입력한 식은 '=(10*C3)+5' 입니다.
C3 셀에 값으로 2를 입력하면 C4 셀에는 25가 계산되어 나오겠지요.
그럼, y값이 100이 되려면 x값은 얼마가 되는지 알아보기 위해 목표값 찾기를 이용해 보겠습니다.
우선, 마우스로 C4셀을 클릭합니다. 그리고, 데이터 메뉴의 예측 그룹에서 가상분석 아이콘을 클릭합니다.
[그림2]를 보시면 빨간색 사각형으로 표시된 부분이 가상분석 아이콘입니다. 그러면 메뉴가 뜨는데 거기서 목표값 찾기를 선택합니다.
[그림3]과 같은 대화 상자가 출력됩니다. 목표값 찾기 대화 상자에서 수식 셀 입력란은 가상분석 아이콘을 클릭하기 전에 이미 C4셀을 마우스 클릭한 상태였으므로 C4값 입력되어 있는 것을 보실 수 있습니다. 만약 클릭이 안되어 있었다면 지금 입력하셔도 됩니다. 그리고, 찾는 값 입력란에는 y값이 100 되었을 때 x값을 구하는 것이므로 100을 입력합니다.
그리고, 값을 바꿀 셀 입력란에는 x값이 어떻게 변경되는지 알고 싶은 것이기 때문에 x값이 입력되어 있는 셀인 C3를 입력합니다. 입력을 완료한 후에 확인 버튼을 클릭합니다.
그럼 [그림4]와 같이 목표값 찾기 상태라는 대화상자가 출력되면서 C3셀의 값이 9.5로 바뀐것을 보실 수 있습니다. 즉, y값이 100이 되려면 x값은 9.5가 되어야 한다는 것입니다.
목표값 찾기 상태 대화상자에서 확인 버튼을 클릭하면 구한 x값이 C3 셀에 입력된 상태로 종료됩니다. 하지만 취소 버튼을 클릭하면 C3 셀과 C4 셀 값은 목표값 찾기 실행 이전에 입력된 값으로 돌아갑니다.
한 가지 예를 더 보겠습니다. 아래 [그림5]과 같은 매입매출장부 시트가 있을 때 1월 5일 배추 판매 수익이 50,000원이라고 되어 있는데 수익이 100,000원이 되려면 배추를 몇 개나 팔아야 할까요? 이를 목표값 찾기로 알아보겠습니다.
우선, G3 셀에 마우스 클릭을 한 후 데이터 메뉴의 예측 그룹에서 가상분석 아이콘을 클릭하고 목표값 찾기를 선택합니다.
그리고, 목표값 찾기 대화 상자가 출력되면 [그림6]과 같이 입력합니다.
입력한 후 확인 버튼을 클릭하면 [그림7]과 같은 결과를 얻을 수 있습니다. F3 셀을 보시면 원래 값인 250에서 500으로 변경된 것을 확인하실 수 있습니다.
이와 같이 목표값 찾기 기능을 이용하면 계산식의 결과값을 알고 있을 때 그 변수의 값을 쉽게 구할 수 있습니다.
2. 시나리오 사용하기
이번에는 가상분석에서 시나리오 기능을 알아보도록 하겠습니다. 시나리오는 변수 값이 일정하게 변했을 때 결과가 어떻게 나오는지를 보여주는 기능입니다. 이는 어떤 가상 상황을 예상하고 그 결과가 어떻게 변할지를 예측할 때 유용합니다.
이번에는 [그림8]과 같이 품목별로 매입매출을 작성한 시트를 예로 시나리오에 대해 알아보겠습니다.
현재 장부에 입력된 매입 단가가 200원씩 올랐을 때와 100원씩 떨어졌을 때의 시나리오를 작성해보겠습니다.
데이터 메뉴의 예측 그룹에서 가상분석 아이콘을 클릭하고 시나리오 관리자를 선택합니다.
그럼 [그림9]와 같은 대화 상자가 출력됩니다. 출력된 대화 상자에서 추가 버튼을 클릭합니다.
그러면, [그림10]과 같은 시나리오 추가 대화 상자가 출력됩니다. 시나리오 이름 입력란에 '매입액 증가'라 입력하고 변경 셀 입력란에는 [그림8] 장부에서 매입 단가가 입력되어 있는 'C3:C7'을 입력합니다. 입력한 후 확인 버튼을 클릭합니다.
[그림11]과 같이 시나리오 값 대화 상자가 출력됩니다. 시나리오 값 대화 상자는 변경 셀로 입력했던 각 셀의 값들이 출력되어 있습니다. 우리가 알고 싶은 것은 200원씩 매입 가격이 올랐을 때이므로 각 셀의 값을 200씩 증가시켜 입력합니다.
1번은 1200, 2번은 700, 3번은 1000, 4번은 800, 5번은 800을 입력하고 확인 버튼을 클릭합니다.
[그림12]와 같이 '매입액 증가'라는 시나리오가 추가됩니다. 추가 버튼을 클릭하고 시나리오 이름은 '매입액 감소'로 하여 같은 방식으로 데이터를 입력하여 시나리오를 추가합니다.
[그림12]와 같은 결과를 얻게 됩니다. 그러면, 시나리오 관리자 대화 상자에서 요약 버튼을 클릭합니다.
[그림13]과 같이 시나리오 요약 대화 상자가 출력됩니다. 처음에는 결과 셀 입력란에 '=F3'가 입력되어 있는데 이를 '=F3:F7'로 변경해줍니다. 참고로 [그림13]의 결과 셀 입력란에 입력된 값에 붙은 $는 셀 주소를 고정시키기 위해 입력된 것으로 쓰지 않으셔도 됩니다. $가 입력된 이유는 입력란 오른쪽 화살표를 클릭하고 F3셀부터 F7셀까지 드래그해서 입력했기 때문입니다.
이제 확인 버튼을 클릭하시면 작성한 시나리오에 대한 요약 보고서가 새로운 시트로 출력됩니다.
'엑셀 > 엑셀 사용하기' 카테고리의 다른 글
| 엑셀 빠른 표 편집 방법 (0) | 2021.06.11 |
|---|---|
| 엑셀에서 빈 셀을 쉽게 채우는 방법 알아보기 (0) | 2021.06.08 |
| 엑셀로 데이터 베이스 다루기 II (0) | 2021.05.24 |
| 엑셀로 데이터 베이스 다루기 I (0) | 2021.05.22 |
| 조건부 서식을 이용하여 표 꾸미기 II (0) | 2021.05.17 |
