데이터 베이스를 구축하는 많은 이유중의 하나가 체계화된 시스템에서는 여러 가지 조건으로 미래를 예측하기 쉽다는 것입니다. 목표값과 해찾기는 엑셀 데이터베이스 기능중 분석도구의 대표적인 것으로 특정 값을 찾기 위해 조건에 맞는 범위에서 다른 데이터를 변경시키는 역할을 합니다.

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

① 다음 데이터를 입력합니다. B5 셀에는 =AVERAGE(B2:B4) 수식이 들어갑니다.

② 목표값은 평균 80점이 되는 것입니다. 평균 80점을 달성하기 위해, 영어 점수를 몇 점이나 향상시켜야 할까요? 이런 경우 사용되는 것이 목표값입니다. 도구메뉴에서 [목표값 찾기]를 선택합니다.

③ 목표값 찾기 대화상자가 나타납니다. 수식 셀은 평균 점수의 수식이 들어 있는 B5를 선택합니다. 찾는 값은 80을 입력합니다. 값을 바꿀 셀은 영어 점수인 B3를 선택합니다.

④ 목표값 찾기 상태가 나타납니다. 찾은 값이 정확하면 [확인]을 클릭합니다. [취소]를 클릭하면 값이 바뀌지 않습니다.

⑤ 셀의 값이 변경되면서 목표값 80점으로 수정되었습니다.

이해가 되죠? 원하는 목표값을 찾기 위해 다른 셀의 값을 변경하는 것입니다. 따라서 목표값의 대상이 되는 셀은 반드시 수식이 입력된 셀이어야 합니다.

34-2. 해찾기
해 찾기는 워크시트에서 목표 셀의 수식에 대한 최적 값을 찾을 때 사용합니다. 앞에서 살펴본 목표값은 한 셀의 값만 변경할 수 있었습니다. 그러나 해찾기는 여러셀의 값을 비교, 변경하면서 최적의 값을 찾아줍니다.

① 다음 데이터를 입력합니다.

② 해 찾기는 엑셀의 추가기능입니다. 따라서 [도구-추가기능]을 선택해서 해찾기를 추가해 줍니다.

③ 회사내에서 2000년도 총 예산은 ￦7,000,000을 넘어서지 않는 선에서 재조정 하라는 지시가 내려왔습니다. 따라서 현재 총 예산인 ￦6,450,000은 ￦7,000,000으로 재조정되어야 합니다. 이때 재 조정되는 ￦7,000,000이 찾으려는 해입니다. 해를 찾으려는 E9 셀을 선택한 후 [도구 - 해찾기]를 실행합니다.

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

⑤ 해찾기가 진행되고, 결과 상자가 나타납니다. 구한 해로 변경하려면 “확인”을 클릭합니다. “초기값 유지”를 선택하면 값이 변경되지 않습니다.

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

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

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

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

※ Tip – 해찾기 예제 파일
엑셀이 설치된 폴더의 \Samples 폴더에는 해 찾기 예제가 들어 있는 통합 문서인
Solvsamp.xls가 있습니다. Solvsamp.xls에 있는 예제 워크시트를 생산 관리, 운송 경로, 인사 관리, 재무 관리, 투자 관리, 공학 설계 등의 용도에 맞게 사용할 수 있습니다.

⑩ 다시 해찾기 대화상자가 나타납니다. 추가된 제한 조건을 확인한 뒤 [실행]을 클릭해서 해찾기를 실행합니다.

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

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

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

This image has been resized. Click this bar to view the full image. The original image is sized 600x400px and weights 23KB.

해찾기는 상당히 복잡한 계산을 내부적으로 진행한 후 만족할만한 결과를 보여주는 고급 기능입니다. 잘 활용하면 업무 분석 및 예측에 많은 도움이 될 것입니다.