엑셀 목표값 찾기 및 해찾기 기능 사용법

목표값 찾기 vs 시나리오 분석 vs 해찾기 비교

1. 목표값 찾기

목표값 찾기는 '하나의 변수를 바꿔 목표값을 찾을 때 사용'하는 기능입니다. 예를 들어 특정 매출을 달성하기 위한 판매량이나, 영업이익을 5% 높이기 위한 판매원가를 계산할 때 활용합니다. 변수가 하나뿐이라 해찾기보다 다루기 쉽고, 엑셀에 기본 내장된 기능이므로 별도의 추가기능을 설치하지 않아도 어느 버전에서나 사용할 수 있다는 장점이 있습니다.

2. 시나리오 분석

시나리오 분석은 '여러 변수를 바꾸며 다양한 시나리오의 결과를 계산하고 예측할 때 사용'하는 기능입니다. 예를 들어 '판매량이 10% 증가하고 가격은 동일한 경우', '판매량이 5% 늘고 가격도 5% 인상된 경우', '판매량은 그대로이고 가격만 10% 증가한 경우'처럼 조건을 나누어 각 상황별 결과를 동시에 예측할 수 있습니다.

3. 해찾기 기능

해찾기 기능은 영문으로 'Solver'라 불리는 엑셀의 기본 추가기능입니다. 해찾기를 사용하면 여러 변수를 동시에 변경하여 특정 조건을 만족하는 최적 결과를 계산할 수 있습니다. 예를 들어 '매출액이 5%~10% 사이로 증가하고 인건비가 10%~15% 인상되는 상황에서 영업이익을 그대로 유지하려면 매출액과 인건비가 각각 몇 %씩 변해야 하는가'와 같은 복합 조건의 해를 구할 수 있습니다.

목표값 찾기 제한사항

목표값 찾기는 비선형 자료에는 사용할 수 없습니다. 비선형 자료란 2차 이상의 방정식처럼 x값이 증가할 때 y값이 단조 증가나 감소를 보이지 않고, 증가와 감소를 반복하는 형태의 데이터를 의미합니다.

예를 들어 아래와 같은 상황을 가정해 보겠습니다. 시작값이 0이고 목표 y값이 4000인 경우를 계산할 때, 목표값 찾기는 기준값 0에서 값이 증가하는 방향, 즉 음의 방향으로 탐색을 시작합니다.

음의 방향으로 계속 탐색할 경우 x축이 -9.51일 때 극대값 1041에 도달한 뒤 음의 무한대로 값이 계속 감소합니다. 결과적으로 목표값 찾기는 변수가 -9.51일 때 1041.914649를 반환하며, 우리가 원하는 4000은 찾지 못한 채 계산을 종료합니다.

따라서 자료가 비선형 형태일 때에는 해찾기 기능의 Evolutionary 해법을 사용하여 문제를 해결합니다.

해찾기 추가기능 설치방법

1. [파일] - [옵션] - [추가기능] 메뉴에서 관리 항목을 [Excel 추가 기능]으로 선택한 뒤, [이동] 버튼을 클릭합니다.
2. [해 찾기 추가 기능] 항목을 체크한 뒤 [확인] 버튼을 눌러 추가기능 설치를 마무리합니다.
   
3. [데이터] 탭으로 이동하면 우측에 [해 찾기] 추가기능이 정상적으로 설치된 것을 확인할 수 있습니다.

목표값 찾기 실전 활용 예제

목표값 찾기는 실무의 다양한 분야에 적용할 수 있는 활용도 높은 기능입니다. 간단한 예제를 통해 사용법을 차례대로 살펴봅니다.

아래 일일 손익예상표는 판매갯수와 평균원가, 그리고 인건비·임대료·기타잡비 등 고정지출을 반영하여 영업이익을 계산합니다. 여기서 물가 상승으로 제품 원가가 1,450원에서 1,550원으로 인상된다고 가정해 보겠습니다. 이때 현재 영업이익인 138,000원을 그대로 유지하려면 하루에 몇 잔을 판매해야 할까요?

직접 계산하면 변수가 얽혀 풀이 과정이 복잡해 보이지만, 목표값 찾기를 활용하면 단계별로 간단하게 해결할 수 있습니다.

1. 예제파일의 [1] 시트로 이동한 뒤, 평균원가 셀을 1,550으로 변경합니다.
2. [데이터] - [가상분석] - [목표값 찾기] 메뉴를 선택한 뒤, 목표값 찾기 대화상자에 아래와 같이 값을 입력합니다.
   수식셀 : $D$20 (=영업이익)
   찾는값 : 138,000
   값을 바꿀 셀 : $D$6 (=판매갯수)
3. [확인] 버튼을 누르면 계산이 시작되며, 판매갯수가 약 262잔일 때 영업이익이 138,000원으로 맞춰지는 것을 확인할 수 있습니다. [확인]을 누르면 시트가 계산 결과로 업데이트되고, [취소]를 누르면 기존 값으로 복구됩니다.

시나리오 분석 활용 예제

시나리오 분석은 특정 목표값을 역산하는 대신 여러 가지 상황별로 결과를 동시에 계산하고 비교하는 작업에 사용됩니다. 아래 실전 예제를 통해 시나리오 분석의 사용법을 살펴봅니다.

1. 예제파일의 [1] 시트로 이동합니다. 변경해야 할 조건은 '판매갯수, 공사비, 월세' 3가지이므로, 키보드 Ctrl 키를 누른 채로 판매갯수·월세·공사비 셀을 동시에 선택합니다. 이후 [데이터] - [가상분석] - [시나리오 관리자]로 이동한 뒤, [추가] 버튼을 눌러 새 시나리오를 생성합니다.
2. 시나리오 이름으로 '250'을 입력한 뒤 [확인]을 눌러 첫 번째 시나리오를 추가합니다.
   
3. 첫 번째 시나리오는 신규 매장에서 월세 300만 원, 공사비 3억 원, 판매잔수 250잔인 경우입니다. 따라서 시나리오 값을 아래와 같이 설정합니다.
   $D$6 : 250
   $G$11 : 3000000
   $G$13 : 300000000
4. 값을 입력한 뒤 [추가] 버튼을 눌러 두 번째와 세 번째 시나리오를 차례로 추가합니다.
   두번째 시나리오 이름 : 300
   $D$6 : 300
   $G$11 : 3000000
   $G$13 : 300000000
   세번째 시나리오 이름 : 350
   $D$6 : 350
   $G$11 : 3000000
   $G$13 : 300000000
5. 모든 시나리오를 추가한 뒤 [요약] 버튼을 눌러 요약 보고서를 생성합니다.
   
6. 요약 보고서에 표시할 결과 항목은 영업이익과 영업이익률이므로 결과 셀로 D20:E20 범위를 선택한 뒤 [확인]을 눌러 마무리합니다.
7. 요약 보고서가 생성되었습니다. 불필요한 항목은 숨기고 각 셀의 머릿글을 정리하여 보고서를 완성합니다.

해찾기 사용 예제

예제파일의 [3] 시트로 이동합니다. 이번에는 여러 조건이 복잡하게 얽혀 있는 상황에서 해찾기 기능을 활용하여 최대 영업이익을 만드는 판매갯수를 계산해 봅니다. 계산 시 고려해야 할 조건은 다음과 같습니다.

위 조건처럼 판매잔수가 늘어날수록 인건비와 가맹비가 동시에 증가하는 구조이므로, 양쪽 지출을 함께 고려하여 최대 영업이익을 만드는 판매잔수를 계산해야 합니다. 변수와 제한조건만 정확히 설정하면 해찾기 기능으로 손쉽게 해를 구할 수 있습니다.

1. 예제파일의 [3] 시트로 이동한 뒤 [데이터] - [해찾기] 메뉴를 선택합니다. 빠른 실습을 위해 해찾기에 필요한 변수가 예제파일에 미리 입력되어 있으므로, 각 변수의 의미를 하나씩 살펴봅니다.
2. 목표 설정 항목으로 $D$22셀(=영업이익)을 선택합니다. 대상은 영업이익의 '최대값'으로 지정합니다.
3. 변수 셀로는 $D$6(=판매갯수)을 지정하여, 이 값을 변화시키면서 영업이익의 최대값을 탐색합니다.
4. 제한조건에 해당하는 3개의 조건을 입력합니다.
   1. $D$6 <= 350 : 판매잔수는 350잔 이하여야 합니다.
   2. $D$6 = 정수 : 판매잔수는 정수여야 합니다.
   3. $D$6 >=0 : 판매잔수는 양수여야 합니다.
5. 해법은 Evolutionary로 선택합니다. 본 예제는 판매갯수가 증가할 때 영업이익이 불규칙하게 변하는 비선형 자료이므로, 해법은 반드시 Evolutionary로 지정해야 합니다.
6. 해법의 [옵션]으로 이동한 뒤 '최대시간'을 5초로 설정합니다. Evolutionary 해법은 모든 조건을 순차적으로 계산하므로 기본 설정으로는 10분 이상 소요되는 경우가 많아, 최대시간을 5~30초 사이로 제한하는 것이 좋습니다.
   (완전한 최적해 대신 실무에서 타협 가능한 근사해를 빠르게 찾는 것을 목표로 활용하는 것을 권장합니다.)
7. 각 판매갯수별 할인율, 수수료와 가맹비용, 인건비는 어디에서 계산될까요? 예제파일의 노란색 셀을 살펴보면 판매갯수에 따라 할인율과 수수료, 마케팅비가 자동으로 계산되도록 수식이 미리 입력되어 있습니다. 각 지출 항목이 합산되어 최종 영업이익이 산출됩니다.
8. [해찾기] 버튼을 클릭하면 계산이 시작되며, 앞서 설정한 최대시간 5초가 경과하면 중간 해 표시 안내창이 나타납니다. [정지] 버튼을 눌러 계산을 중단합니다.
   
9. 계산이 중단되며 시트가 업데이트되고, 판매갯수가 199잔일 때 영업이익이 19,488원으로 최대가 되는 것을 확인할 수 있습니다. [확인] 버튼을 누르면 계산 결과가 시트에 반영되며, [취소] 버튼을 누르면 기존 값으로 복구됩니다.