Excel 해 찾기 기능 활성화시키기
엑셀의 해 찾기 기능은 개인적으로, 엑셀을 단순 계산용 도구에서, 의사결정을 도와주는 고급 도구로 바꾸어주는 엑셀의 매우 돋보이는 기능이라고 생각하는데, 대부분의 사람들이 잘 사용하지 않는 기능이기도 하다.
이러한 해 찾기 기능은, 데이터 메뉴 탭에 있기는 하지만, 기본적으로는 비활성 상태라서, 파일 메뉴를 이용하여 다음과 같이 “Excel 옵션” 메뉴에 들어가 기능을 추가시켜 주어야 하는데
위의 화면에서 “해 찾기 기능 추가” 부분을 마우스로 선택해 주면, 다음과 같은 화면이 나오며, 여기서 해 찾기 기능에 “선택” 표시를 해주고 “확인” 해주면
해당 기능이 활성 응용프로그램 목록 부분에 표시되면서,
다음과 같이 데이터 메뉴 탭에, 해 찾기 기능이 표시되어 사용이 가능하게 된다.
생산 현장에서 만나는 질문들
엑셀의 해 찾기 기능이 빛을 발하는 용도 중 하나는 생산계획 분야이다. 예를 들면 다음과 같은 경우이다.
하나의 원재료로부터 여러 가지의 세부 제품을 다음과 같은 수율의 범위에서 조정이 가능한 경우에,
단, 각 세부 제품의 생산 단위당 판매 가격과, 생산 비용은 다음과 같고,
해당 공장의 최대 생산 가능한 제품수의 합은 300,000개인 경우이다.
이러한 조건에서, 가장 궁금한 것은 다음과 같은 질문들이다.
“이익을 극대화하려면 제품별 생산 비율을 어떻게 정해야 할까?”
“이익보다는 매출액을 극대화하려면 최적 생산 비율은?”
” 특정 제품을 최대로 생산하려고 할 때, 나머지 제품의 생산 수량은 어떻게 될까?”
생산계획에 해 찾기 기능 적용하기
위와 같은 질문을 수학적으로 풀어, 생산계획을 마련하는데, 엑셀의 해 찾기 기능은 아주 강력한 도구가 된다.
위와 같은 문제를 풀려면,
먼저 주어진 문제 속에 포함된 여러 항목 간의 관계들을 다음과 같이 정리한다.
다음의 테이블에서, 내가 조절할 것은 노란색으로 표시된 각 세부 제품별 수율 값이며, 해 찾기 기능을 통해, 이 노란색 부분의 값을 결정하게 된다.
하늘색 부분에는 노란색 부분의 값에 따라, 해당되는 값들이 계산되도록, 미리 다음과 같은 수식을 넣어둔다.
그리고 이 문제에 적용되는 조건들을 다음과 같이 정리해 본다.
문제 풀이의 경게조건이 되는 사항들이다.
문제를 풀기 위해, 엑셀 메뉴의 데이터 메뉴 탭에서 “해 찾기”를 선택해 준다.
다음과 같은 화면이 된다.
이 화면의 “목표 설정” 부분에 내가 얻고자 하는 목표 (예: 이익, 생산 수량 등..)를 결정해 지정한다. 만일 “이익 극대화”를 목표로 하는 경우 다음과 같이, 이익의 합을 나타내는 셀인 J11을 지정하고, “최대값”을 선택해 준다.
그리고 해 찾기를 통해 얻고자 하는 조절 변수인 노란색 부분을 “변수 셀 변경” 부분에 지정한다.
다음으로, 문제 풀이의 경계조건들을 다음 버튼을 이용해 하나씩 모두 추가해 준다.
필요한 경계조건을 추가해 주면 다음과 같이 된다.
이때 주의할 것은 해당 문제에 적용되는 경계조건을 모두 반영해 주어야 하고, 수식으로 표현하는데 오류가 없어야 한다는 점인데,
다음과 같은 경계조건(Boundary conditions)을
해 찾기 기능에 수식의 형태로 추가한 결과는 다음과 같다.
(참고로, 해 차기 기능이 원활히 동작하지 않을 때 오류의 원인 대부분은 이 경계조건을 지정하는 부분의 오류에서 발생한다.)
이렇게 해찾기를 수행해주면 다음과 같은 화면이 나오고,
다음과 같이 시트값이 변화하는 것을 볼수가 있다.
즉, 위의 제품비로 생산하면 총 300만 개의 생산이 가능하고, 최대 35백만 불의 이익 창출이 가능하다는 계산이 된다.
참고로 본 예제의 파일은 다음에 첨부해둔다.
다양한 시나리오별 생산계획 계산하기
일단 위와 같은 기본 해 찾기 모델이 만들어지면, 다양한 시나리오별로, 목표 조건을 변경하여, 해 찾기를 시행할 수 있다.
예를 들어 위에서 “이익 극대화”를 목표로 했던 생산계획 목표를,
특정 제품(예: 제품 F)의 생산량 최대화를 목표로 전환한다면, 다음과 같이 해 찾기의 목표 설정을 변경지정해 주면 된다.
이런 변경된 조건으로 해 찾기를 수행하면,
다음과 같이 총이익은 기존의 35백만 불에서 약 28.7백만 불 수준으로 줄지만,
제품 F의 생산량은 60,000개에서, 105,000개로 증가하는 것을 볼 수가 있다.
해를 찾을 수 없는 경우들
엑셀로 해 찾기를 하다 보면 해를 찾을 수 없는 경우들이 있다.
이런한 경우는 대개 다음과 같은 문제들을 먼저 살펴볼 필요가 있다.
-
경계조건이 적절히 수식으로 반영되었는가? (부호, 셀)
2. 경계조건이 누락된 것은 없는가?
3. 기준 Data의 현실성(예 : 위에서 제품 수율 최소치 및 최대치)의 범위 값이 너무 넓거나 비정상적인 값은 아닌지 살펴보고, 범위를 좁혀준다. (참고 : 위의 예제의 수율 최소치와 최대치를 변경하면 변경된 상황에 따라 해가 있을수도 있고, 없을수도 있다)