데이터 베이스를 구축하는 많은 이유 중의 하나가 체계화된 시스템에서는 여러 조건으로 미래를 예측하기 쉽다는 것입니다. 목표값과 해찾기는 특정 값(목표값)을 찾기 위해 조건에 맞는 범위에서 다른 데이터를 변경시키는 역할을 합니다. 이를 통해 목표를 조정하는 등의 추가적인 판단 근거로서 활용할 수 있겠죠.
목표값 찾기
목표값이란 수식에서 주어진 결과를 바탕으로 입력 값을 찾는 방법입니다. 엑셀은 목표값을 찾을 때 수식이 원하는 결과를 구할 때까지 지정한 셀에서 값을 바꿔가며 찾습니다. 정해진 목표값을 찾기 위해 다른 셀의 값을 변경하는 것입니다.
다음 데이터를 입력합니다. B5 셀에는 =AVERAGE(B2:B4) 수식이 들어갑니다.
목표값은 평균 80점이 되는 것입니다. 평균 80점을 달성하기 위해, 영어 점수를 몇 점이나 향상시켜야 할까요? 이런 경우 사용되는 것이 목표값입니다. [데이터-가상분석-목표값찾기]를 선택합니다. 목표값 찾기 대화상자가 나타납니다. 수식 셀은 평균 점수의 수식이 들어 있는 B5를 선택합니다. 찾는 값은 80을 입력합니다. 값을 바꿀 셀은 영어 점수인 B3를 선택합니다.
목표값 찾기 상태가 나타납니다. 찾은 값이 정확하면 [확인]을 클릭합니다. [취소]를 클릭하면 값이 바뀌지 않습니다.
셀의 값이 변경되면서 목표값 80점으로 수정되었습니다.
이해가 되시죠? 원하는 목표값을 찾기 위해 다른 셀의 값을 변경하는 것입니다. 따라서 목표값의 대상이 되는 셀은 반드시 수식이 입력된 셀이어야 합니다.
해찾기
해찾기는 워크시트에서 목표 셀의 수식에 대한 최적 값을 찾을 때 사용합니다. 앞에서 살펴본 목표값은 한 셀의 값만 변경할 수 있었습니다. 그러나 해찾기는 여러셀의 값을 비교, 변경하면서 최적의 값을 찾아줍니다.
다음 데이터를 입력합니다.
해 찾기는 엑셀의 추가(Add-in)기능입니다. 따라서 [파일-옵션-추가기능]을 선택해서 해찾기를 추가해야 사용할 수 있습니다.
회사 내에서 2012년도 총 예산은 ￦3,000,000을 넘어서지 않는 선에서 재조정 하라는 지시가 내려왔다고 가정을 해보겠습니다. 따라서 현재 총 예산인 ￦3,225,000은 ￦3,000,000으로 재조정되어야 합니다. 이때 재 조정되는 ￦3,000,000이 찾으려는 해입니다. 해를 찾으려는 E9 셀을 선택한 후 [데이터 – 해찾기]를 클릭합니다.
해 찾기 대화상자가 나타납니다. 목표 셀은 해를 찾을 셀이므로 E9 셀을 선택합니다. 해의 조건은 지정값을 선택한 후37000000을 입력합니다. 변경할 셀은 각 부처별 예산이 들어 있는 셀로 B3:D6를 선택합니다. 설정이 끝났으면 [해 찾기] 버튼을 클릭합니다.
해찾기가 진행되고, 결과 상자가 나타납니다. 구한 해로 변경하려면 “확인”을 클릭합니다. “원래 값 복원”을 선택하면 값이 변경되지 않습니다.
찾는 해가 시트에 반영되고, 참조 셀들의 값이 자동으로 변경되었습니다.
이번에는 해찾기의 조건을 추가해보도록 하겠습니다. 해찾기 조건을 설정하지 않으면 모든 셀의 값을 일괄적으로 변경해서 계산하게 됩니다. 예를 들어, 2사분기의 예산은 ￦900,000을 넘지 못하게 설정하고, 영업부의 예산은 ￦1,000,000을 넘지 못하게 하는 제한 조건을 주는 것입니다. [데이터-해찾기]를 선택해서 다시 해찾기 대화상자를 표시합니다.
제한 조건 추가 상자가 나타납니다. 첫 번째 제한 조건은 2사분기의 예산은 ￦800,000을 넘지 못한다는 것입니다. 2사분기 예산의 합계인 E4 셀을 선택한 후 제한조건에 800000을 입력합니다. 비교연산자는 <=를 선택해 줍니다. 설정이 끝났으면 [추가]를 클릭해 계속해서 제한조건을 입력합니다.
제한 조건 추가 상자가 다시 나타납니다. 계속해서 영업부의 총 예산이 ￦900,000을 넘지 못한다는 제한조건을 추가합니다. 제한 조건을 모두 추가 했으므로 [확인]을 클릭합니다.
다시 해찾기 대화상자가 나타납니다. 추가된 제한 조건을 확인한 뒤 [해 찾기]를 클릭해서 해찾기를 실행합니다.
제한 조건을 최대한 만족시키는 값으로 해가 찾아지고 데이터가 변경되었습니다. (데이터 구성에 따라 조건이 100% 적용되지 않을 수 있습니다.)
해찾기는 상당히 복잡한 계산을 내부적으로 진행한 후 만족할만 한 결과를 보여주는 고급 기능입니다. 잘 활용하면 업무 분석 및 예측에 많은 도움이 될 것입니다.
