엑셀 해 찾기 기능으로 지출계획 최적화

  오늘은 여러 부분에서 응용할 수 있는 기능임에도 불구하고, 존재 자체에 대해 잘 모르는 사람들이 많은 엑셀 해 찾기 기능에 대해서 한 번 알아보죠. 일단 오늘 할 건 해 찾기 도구를 활성화, 그 뒤에 주어진 예산내에서 최대 효용을 거둘 수 있는 지출계획의 해를 찾는 걸 해볼 거예요!! 다음에 시간이 되면 조금 변형된 예제도 알아보도록 하죠.

 

※ 사용되는 엑셀은 2013버전입니다. 단, 엑셀의 해 찾기 기능은 2007버전부터 사용 가능합니다.

 

1. 해 찾기 도구 표시

 

 - 엑셀 실행 -> 파일 -> 옵션 -> 추가 기능 -> 관리 : Excel 추가 기능 [ 이동 ] 클릭

 - 추가 기능 선택 창 -> 해 찾기 추가 기능 체크 -> 확인

 - 엑셀 -> 데이터 메뉴 -> 분석 : 해 찾기 표시 확인

  기본 설치 상태에서 해 찾기 기능이 활성화가 안 되어 있기 때문에 해 찾기 메뉴를 활성화 시켜줘야 합니다. 위의 방법대로 진행하면 약간의 설치 대기 시간 이후에 데이터 메뉴 하위에 분석 항목이 표시되고 해 찾기가 표시되는 것을 확인할 수 있습니다. 특정한 경우에 해 찾기 추가 기능 체크가 되어있는 상태인데도 해 찾기가 표시가 안 되는 경우가 있는데요. 이런 경우에는 체크 해제 후 확인 -> 다시 추가 기능에 들어가서 체크 후 확인하면 정상적으로 표시가 될 거예요.

2. 해 찾기를 사용할 기본 내용 작성

 

 - 전체 예산 금액 : 지출 가능한 전체 예산 금액 입력

 - 지출항목 / 비용 / 기대 효용 : 선택 가능한 지출계획안을 입력

 - 선택 / 지출 / 효용 : 해 찾기를 통해 얻어질 결과값 필드

  일단 지출 가능한 전체 예산 금액과 지출 계획 내용 그리고 해 찾기를 통해 선택된 항목들의 지출과 효용의 합을 표시할 셀들을 만들어줍니다. 양식은 당연히 해를 찾고자 하는 문제에 따라 변경해 주시면 되구요. 기대효용이라는 부분은 회사라면 만족도라든가 예상수익이라든가 여러 가지 항목으로 변경 가능하겠죠?

 

3. 해 찾기 결과의 지출과 효용 수식 작성

 

 - E4셀에 수식 입력 : =B4 * $D4

 - E4셀의 내용 끌어서 채우기 : 범위 [E4:F13]

 - 합계 항목 수식 작성 : E14=SUM(E4:E13) / F14=SUM(F4:F13)

  입력된 수식에 대해 부연 설명을 하자면 엑셀의 해 찾기로 채워지는 항목은 선택란인데요.해 찾기를 통해 선택란에 1 또는 0이 채워지면 선택란의 값과 비용을 곱해서 지출란에 넣어주게 되고, 선택란의 값과 기대효용을 곱해서 효용란에 채워주는 거죠.

  합계 항목은 단순히 지출과 효용 전체 항목에 대한 SUM()함수이니 따로 설명 안 할게요.

 

4. 해 찾기 적용

 

 - 데이터 -> 분석 -> 해 찾기 선택

 - 해 찾기 매개 변수

  1. 해 찾기의 결과가 될 셀을 선택 : $F$14(효용의 합계란)

  2. 변수 셀 변경 : $D$4:$D$13(선택란의 범위 입력)

  3. 제한 조건 추가 : [ 추가 ] 버튼 선택

      가. 제한 조건 1 : 셀참조($D$4:$D$13) / bin 선택 -> 추가

      나. 제한 조건 2 : 셀참조($E$14) / <= / 제한 조건(=$B$1)-> 확인

  4. 해 찾기 실행

 

  해 찾기의 목표는 최대의 효용을 거두는 거겠죠? 따라서, 목표 설정은 효용의 합계인 $F$14셀을 지정해 주구요.

  해 찾기 매개 변수는 해를 찾아서 적용할 셀들이라고 생각하면 되는데요. 여기에는 선택란의 전체 범위를 지정($D$4:$D$13)해 줍니다.

 

 

   해를 찾는 제한 조건을 지정해 줘요. 여기에서 적용할 조건 중 가.는 선택란이 선택되면 1, 선택되지 못한 경우 0이라는 뜻이구요. 나.는 지출의 합계($E$14)가 예산금액($B$1)보다는 작아야 된다는 뜻이예요.

 

  

여기까지 진행이 되었으면 해 찾기를 적용해서 우리는 선택 / 지출 / 효용란을 채웠구요. 예산 지출계획과 얻을 수 있는 효용의 합계를 구할 수 있습니다. 결과는 아래와 같아요.

 

  ===> 그런데, 열심히 따라했는데 이상한 거 못 느끼셨나요? ㅋㅋㅋ. 예산 대비 지출항목 비용들이 너무 작아서 마지막 샷시 교체만 빼고는 다 선택되었습니다. 이러면 의미가 없겠죠? 예산을 1500만원으로 줄여서 다시 해 찾기를 해보죠!

 

  ===> 예산 금액(B1)만 1500만원으로 변경 후에 해 찾기를 실행하면 이전에 입력했던 조건들은 그대로 있기 때문에 바로 결과를 구할 수 있어요. 1470만원 지출에 85의 효용을 얻는 해를 구했네요.ㅉㅉㅉ

 

  말이 복잡해서 그렇지. 해보면 별로 어렵지 않고 쓸모도 많답니다. 한 번씩 해보세요!