보통 데이터에서 어떤 결괏값을 확인하기 위해선 통계 함수를 사용합니다. SUM, AVERAGE 등으로 결과를 확인하죠. 하지만 그게 끝은 아니죠. 그 통계를 확인하는 이유는 잘된 점과 잘못된 점을 확인하고 어디서 잘못되었는지 무엇이 잘됐는지를 파악하려는 의도입니다. 그러고 나서 앞으로 무엇을 해야 할지 무엇을 하지 말아야 할지를 판단하는 것이죠. 즉 결과 분석 후에는 필연적으로 다시 새로운 목표가 생기게 됩니다. 새 목표를 세우지 않을 것이라면 굳이 데이터를 확인할 필요도 없죠.
물론 엑셀에서 그런 원인까지 파악할 수는 없습니다. 단지 통계니까요. 하지만 결과를 토대로 목표를 세울 순 있습니다. 그걸 가능하게 하는 기능이 "목표값 찾기"입니다.
이번 강좌에서는 "목표값 찾기"를 알아보겠습니다.
그런데 "목표값 찾기"를 하기 위해선 몇 가지 준비 사항이 필요합니다. 위에 말씀드린 내용을 잘 생각해 보면 됩니다. 일단 통계 데이터가 필요하겠죠. 무턱대고 목표를 세울 순 없습니다. 기존 데이터가 있어야 기준을 삼을 수 있으니까요. 그 기준 데이터 중에서도 중요한 데이터는 2가지입니다. 일단 SUM이나 AVERAGE 등으로 만든 통계 데이터일 것입니다. 이 통계 데이터값이 좀 더 낳은 결과를 얻길 원하겠죠. 즉 이 통계 데이터가 "목표값"이 되는 것입니다.
다음, 목표값을 정했으면, 목표에 도달하기 위해서 통계의 자료로 쓰였던 어떤 데이터가 변해야 합니다. 보통은 실적이 좋지 않은 데이터가 대상이 되겠죠. 물론 외부적인 다른 조건이 있다면 다른 데이터가 대상이 될 수도 있을 것입니다. 어째든 변해야 하는 데이터가 필요하다는 것입니다.
예제는 어떤 회사의 판매 팀별 1주일간 제품 판매 현황입니다. 기준 데이터죠.
1주일간 총 564개의 제품을 판매했으니, 다음 주에는 좀 더 낳은 결과인 580개를 판매하고 싶습니다. 그럼, 목표값은 G5셀 값이 580이 되는 것이겠죠.
그러기 위해선 판매1팀에서 5팀 중 어느 팀에선 분발해서 판매해야 합니다. 여기선 가장 낮은 실적인 판매1팀이 좀 더 분발하는 것이 좋겠습니다. 그럼, 판매1팀은 다음 주에 16개는 더 판매해야 한다는 결론에 도달할 수 있습니다. 총 36개를 판매해야 한다는 것이죠. 그러기 위해 회사는 판매1팀에 외부적인 요소를 개선하거나 적어도 격려 차원으로 점심 정도는 제공해 주는 것이 좋겠습니다.
위 내용을 엑셀로 만들어 보겠습니다.
- 목표값을 만들 셀(G5)을 클릭하여 선택합니다.
- "데이터 탭 > 가상 분석 > 목표값 찾기"를 클릭합니다. ⇒ 목표값 찾기 대화상자가 나옵니다.
- 이때 수식 셀에는 ❶에서 선택한 셀의 위치가 들어옵니다 - 찾는 값에 목표값(580)을 입력합니다.
- 값을 바꿀 셀을 클릭하고 B5 셀을 클릭하여 참조합니다. ⇒ 절대 참조로 들어옵니다.
- 최종 확인 버튼을 클릭합니다.
그림처럼 목표값 찾기 상태 대화상자가 나오고 데이터 표에 값이 반환됩니다.
이렇게 "목표값 찾기"는 통계 데이터에서 역산하여 필요한 데이터의 값을 추적하는 기능입니다. 그런데 한 가지 중요한 사항이 있습니다. 당연한 얘기지만 이렇게 역 추적하기 위해선 목표값을 변경한 G5 셀은 참조를 통해 수식으로 만들어져야 합니다. 지금은 SUM으로 합계를 반환했으니, 문제가 없습니다만 만약 그냥 계산해서 값(580)을 입력한 상태라면 당연히 역 추적할 수 없습니다. 물론 SUM의 범위에 역 추적 대상이 포함되지 않아도 안 되겠죠. "목표값 찾기"는 반드시 수식으로 만들어진 셀에서만 가능하다는 것도 기억해 주시길 바랍니다.
조금 더 복잡한 데이터도 확인해 보겠습니다.
지금까지 많이 사용한 데이터입니다. 합계 가격을 100,000원으로 만들기 위해서 판매 5팀은 몇 개를 판매해야 하는지 알아보겠습니다. 목표값 찾기의 방법은 동일합니다. 하지만 이번에는 변경할 금액이 아니라 수량이니 변경 대상 셀을 C11로 하면 되겠죠.
- 목표값을 만들 셀(C12)을 클릭하여 선택합니다. (병합된 셀입니다.)
- "데이터 탭 > 가상 분석 > 목표값 찾기"를 클릭합니다. ⇒ 목표값 찾기 대화상자가 나옵니다.
- 찾는 값에 목표값(100000)을 입력합니다.
- 값을 바꿀 셀을 클릭하고 C11 셀을 클릭하여 참조합니다. ⇒ 절대 참조로 들어옵니다.
- 최종 확인 버튼을 클릭합니다.
결과 판매 5팀은 12개를 판매해야 한다는 것을 도출할 수 있습니다. 하지만 금액이 조금 이상하죠? 12*1,800=21,600입니다. 그런데 22,400으로 계산되었습니다.
수식 입력줄을 확인하면 C11 셀의 값은 사실 12가 아니라 12.44... 라는 것을 알 수 있습니다. 반올림한 것이죠. 마찬가지 E11 셀 역시 반올림 했습니다. 엑셀의 숫자 표시 형식은 기본으로 소수점 이하를 반올림하기 때문이었던 것이죠. 사실은 10만 이상으로 만들고 싶다면 13개를 판매해야 한다는 얘기입니다.
이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.
이것으로 어쨌든 복잡한 데이터에서도 "목표값 찾기"가 가능하다는 것을 알 수 있습니다. 다음, 처음에 했던 방식처럼 수량 셀(C11)을 변경하는 것이 아니라 금액 셀(E11)로도 변경할 수 있을까요? 이번엔 목표값은 110,000으로 하고 변경할 셀을 E11 셀로 선택하여 만들어 보겠습니다.
같은 방법이니 설명은 생략하겠습니다. 위 그림의 순서로 작성해 주세요. 결과,
예상과는 다르게 결과가 반영되지 않고, "셀에 값이 있어야 합니다"라는 경고창이 나옵니다. 왜 이럴까요?
눈치채셨겠지만 목표값이 되기 위해 변경할 E11 셀은 사실 값이 입력된 셀이 아닙니다. 이것 역시 C11 셀 값과 D11 셀 값이 곱해진 수식입니다. 값이 아니니 당연히 변경할 수 없습니다. 즉 여기서는 변경 가능한 셀은 값으로 입력된 수량 셀(C11)이거나 단가 셀(D11)이란 것이죠.
목표값을 정할 셀 ⇒ 수식, 목표값이 되기 위해 변해야 하는 셀 ⇒ 값
그래서 "목표값 찾기"를 진행하기 위해선 목표값을 정할셀은 반드시 수식이여야하고, 목표값이 되기 위해 변해야 하는 셀은 값으로 입력돼 있어야 한다는 것을 꼭 기억해 주세요.
이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.
의견글 입니다.
()
