[엑셀, 어렵지 않다 ⅩⅨ] 목표 값 찾기와 해 찾기

[호영]

목표와 해를 찾다. 목표는 알겠는데 해는 무슨 뜻일까? 여기서 말하는 해란 한자로는 풀다 해(解) 영어로는 solver 즉, 해결자를 뜻한다. 목표 값 찾기와 해 찾기 기능은 엑셀의 조건 분석의 도구 중 하나이다. 지난 강좌에서 조건 분석의 도구 중 데이터 테이블과 시나리오를 살펴보았다. 이어서 이번 시간에는 목표 값 찾기와 해 찾기를 경험해보자.
 
데이터 베이스를 구축하면 좋은 점 중 하나는 체계화된 시스템에서는 여러 가지 조건으로 미래를 예측하기 편하다는 것이다. 목표 값과 해 찾기는 엑셀 데이터베이스 기능 중 분석도구의 대표적인 것으로 특정 값을 찾기 위해 조건에 맞는 범위에서 다른 데이터를 변경시키는 역할을 한다.

1. 목표 값 찾기
목표 값이란 수식에서 주어진 결과를 바탕으로 입력 값을 찾는 방법이다. 엑셀은 목표 값을 찾을 때 수식이 원하는 결과를 구할 때가지 지정한 셀에서 값을 바꿔가며 찾는다. 정해진 목표 값을 찾기 위해 다른 셀의 값을 변경하는 것이다.

1) 다음 데이터를 입력하자. B6셀에는 =AVERAGE(B2:B5) 수식이 입력되어 있다. 
 

2) 강호동의 목표 값이 평균 90점이라고 가정하자. 평균 90점의 목표를 달성하기 위해, 야심만만의 점수를 몇 점 향상시켜야 하는가가 바로 목표 값을 찾는 것이다. 물론 이 점수 채점을 다시 해서 더 높은 점수가 나오면 바꿀 수 있다고 가정을 해야 한다. 이런 경우 사용되는 것이 목표 값이다.

3) 데이터 도구 메뉴 중 [가상분석]의 [목표값 찾기]를 선택한다. 


4) 목표 값 찾기 대화상자가 나타납니다. 수식 셀은 평균 점수의 수식이 들어있는 B6를 선택한다. 찾는 값은 90을 입력한다. 값을 바꿀 셀은 야심만만 점수인 B3를 선택한다.


5) [확인]을 누르면 목표 값 찾기 상태가 나타난다. 찾은 값이 정확하면 [확인]을 클릭한다. [취소]를 클릭하면 값이 바뀌지 않는다.


6) 셀의 값이 변경되면서 목표 값 90점으로 수정되는 것을 확인할 수 있다.
 

그렇다. 위 데이터가 보여주는 대로 야심만만 점수를 87점으로 올리면 평균을 90점으로 높일 수가 있다. 이처럼 목표 값 찾기는 원하는 목표 값을 찾기 위해 다른 셀의 값을 변경하는 것이다. 따라서 목표 값의 대상이 되는 셀에는 반드시 수식이 입력되어 있어야 한다.

2. 해 찾기
해 찾기는 워크시트에서 목표 셀의 수식에 대한 최적 값을 찾을 때 사용한다. 앞에서 살펴본 목표 값은 한 셀의 값만 변경할 수 있었다. 그러나 해 찾기는 여러 셀의 값을 비교, 변경하면서 최적의 값을 찾아준다. 대단히 놀라운 고급기능으로 사용자들에게 엑셀의 역작이라 칭송을 받는 기능이다. 

1) 다음 데이터를 입력하자.


2) 해 찾기는 엑셀의 추가기능이다. 따라서 Microsoft Office 단추를 클릭한 다음 Excel 옵션을 클릭한다. 추가 기능을 클릭하고 관리 상자에서 Excel 추가 기능을 선택한다. 이동을 클릭한 후에 사용 가능한 추가 기능 상자에서 해 찾기 추가 기능 확인란을 선택하고 확인을 클릭한다. 
 
[확인]을 누르면 상단 메뉴 상자에 [해 찾기]가 생긴다. 

3) 회사 내에서 2009년도 총 예산은 \7,000,000을 넘어서지 않는 선에서 재조정하라는 지시가 내려왔다. 따라서 현재 총 예산안 \6,450,000은 \7,000,000으로 재조정되어야 한다. 이때 재조정되는 \7,000,000이 찾으려는 해다. 해를 찾으려는 E9셀을 선택한 후 [분석 – 해 찾기]를 실행한다.


4) 해 찾기 대화상자가 나타난다. 목표 셀은 해를 찾을 셀이므로 E9셀을 선택한다. 해의 조건은 지정 값을 선택한 후 7000000을 입력한다. 변경할 셀은 각 부처별 예산이 들어 있는 셀로 B3:D6를 선택한다. 설정이 끝났으면 [실행]버튼을 클릭한다.
 

5) 해 찾기가 진행되고, 결과 상자가 나타난다. 구한 해로 변경하려면 [확인]을 클릭한다. “초기값 유지”를 선택하면 값이 변경되지 않는다.


6) 찾는 해가 시트에 반영되고, 참조 셀들의 값이 자동으로 변경되었다.
 

7) 이번에는 해 찾기의 조건을 추가해보도록 하자. 해 찾기 조건을 설정하지 않으면 모든 셀의 값을 일괄적으로 변경해서 계산하게 된다. 예를 들어, 2사분기의 예산은 \900,000을 넘지 못하게 설정하고, 영업부의 예산은 \1,000,000을 넘지 못하게 하는 제한 조건을 주는 것이다. [분석 – 해 찾기]를 선택해서 다시 해 찾기 대화상자를 나타낸다.


8) 제한 조건 추가 상자가 나타난다. 첫 번째 제한 조건은 2사분기의 예산은 \900,000을 넘지 못한다는 것이다. 2사분기 예산의 합계인 E4셀을 선택한 후 제한 조건에 900000을 입력한다. 비교연산자는 <= 를 선택해 준다. 설정이 끝났으면 [추가]를 클릭해 계속해서 제한 조건을 입력한다.


9) 제한 조건 추가 상자가 다시 나타난다. 계속해서 영업부의 총 예산이 \1,000,000을 넘지 못한다는 제한 조건을 추가한다. 제한 조건을 모두 추가했으므로 [확인]을 클릭한다.
 

10) 다시 해 찾기 대화상자가 나타난다. 추가된 제한 조건을 확인한 뒤 [실행]을 클릭한다.
 

11) 제한 조건을 모두 만족하는 해를 찾았음을 알 수 있다. 해 찾기 결과 대화상자가 나타난다. [확인]을 클릭하면 시트에 해 찾기 결과가 반영된다.
 

 

12) 해 찾기 결과 상자에서 보고서의 종류를 선택하면 해 찾기 결과에 대한 보고서를 자세하게 만들 수도 있다. 보고서란에서 “해답”을 선택한 후 [확인]을 클릭한다. 
 

13) “해답보고서”라는 시트가 새로 삽입되고 해 찾기에 대한 보고서가 만들어진다.
 

굉장한 기능이다. 이런 복잡한 계산을 손수 해야 한다는 생각만으로도 식은 땀이 날 정도다. 그런데 엑셀의 해 찾기 기능을 이용하면 클릭 몇 번만으로 뚝딱 해낼 수가 있다. 해 찾기는 상당히 복잡한 계산을 내부적으로 진행한 후 만족할만한 결과를 보여주는 고급 기능이다. 잘 활용하면 업무 분석 및 예측에 많은 도움이 될 것이다.@