151 엑셀 해찾기 추가와 해찾기 기능

151_엑셀 초급 강좌 – 데이터 분석 (2)

공대를 다니거나 공학용 계산기를 써 보신 분들은 Equation Solver 라는 기능을 써 보신적 있을거라 생각 됩니다. 또는 통계학 수업을 들은 적이 있는 분이라면 데이터 최적화라는 개념을 공부 하셨을 거라 생각 됩니다. 엑셀의 해찾기는 Equation Solver 와 데이터 최적화 기능을 합쳐놓은 훨씬 고급 기능이라고 생각하시면 됩니다. 기존의 목표값 찾기가 하나의 수식을 바탕으로 하나의 셀을 변경 해서 원하는 값을 얻었다면 엑셀 해찾기는 여러 개의 수식으로 얽혀있는 복잡한 엑셀 데이터 에서 원하는 다수의 셀을 변경해서 목표값을 찾아줍니다. 같은 목표값을 찾는 다는 점에서는 비슷하지만 여러 개의 목표값을 여러 개의 수식에서 동시에 찾아주는 훨씬 고급 기능입니다.

이본 포스팅은 엑셀 데이터 탭의 해 찾기에 관한 내용입니다.

151연습파일.xlsx

151연습파일_완성본.xlsx

1. 해 찾기 기능 추가

   엑셀의 해찾기 기능은 고급기능으로 엑셀에서 기능을 추가 하셔야 사용 할 수 있는 데이터 분석 기능입니다. 초기 한번만 해주시면 됩니다.

    

   백 스테이지 뷰에서 옵션을 클릭 합니다.

   

    

    

   추가 기능을 클릭하고 해 찾기 추가 기능 선택 후 이동을 클릭 합니다.

   

    

    

   추가 하시고 확인을 누릅니다.

   

    

    

   이제 데이터 탭에 와 보시면 해 찾기 기능이 추가 된 걸 확인하실 수 있습니다.

   

    

2. 해 찾기 기능 이해하기

   해찾기 기능은 사용하기는 어렵지 않지만 역시 개념이 약간 까다로울 수 있습니다. 다음의 표를 보면서 이해 하도록 하겠습니다.

    

   지금 1분기의 데이터를 빼고는 모두 수식이 들어가 있는 상태 입니다. 1분기의 이익도 10프로씩 분기마다 증가하며 지출도 같이 10프로씩 분기마다 증가 합니다. 즉 2와 3분기의 데이터는 1분기의 실적을 바탕으로 계산된 값 들 입니다.

   

    

   지금 총 순이익은 198040 원 입니다.

   총 순이익은 판매 이익, 광고 이익, 지출 등을 토대로 계산된 값입니다.

   판매 이익과 광고 이익을 증대 시키고 지출을 감소 시켜서 순이익을 3000000을 만들고 싶습니다.

   목표값 3000000, 그리고 변하는 셀 3가지 이상, 수식이 적용된 셀 다수 >>> 해 찾기 입니다.

    

   해 찾기 기능을 선택 하세요.

   

3. 해찾기 기능 적용하기

   해찾기 기능은 복잡한 수식인 만큼 정해 줘야하는 인수도 많습니다. 그러나 당연히 정해줘야 하는 인수들이며 따지고 보면 어렵지 않습니다.

    

   우리의 목표값을 먼저 정해야 합니다. 3000000 으로 정합니다.

   

    

    

   무슨 값을 바꿔서요? 저는 이익의 두 셀, 지출 두 셀 데이터를 바꾸는 셀 값으로 잡습니다.

   

    

    

   해 찾기 만의 기능입니다. 내가 제한 조건을 둘 수 있습니다. 내가 아무리 목표치를 높게 잡아도 판매 이익은 두배를 넘을수 없다고 판단하여 3000000으로 목표치를 잡았습니다.

   

    

    

   같은 개념으로 임대료는 내려 가지가 않으니 1000000 이상으로 잡습니다.

   

    

   해를 어떻게 찾느냐는 통계학 전공 분야이므로 패스 합니다. 그러나 모두 최적화 된 해를 찾는 기법이고 기본은 GRG 비선형 방법으로 해를 찾습니다.

   

    

    

   목표값 찾기가 1개의 셀만 바꿨다면 해 찾기는 거의 모든 셀의 값을 바꾸었습니다. 순이익이 3000000으로 잡히려면 판매 이익과 광고 이익의 최적 조건을 보여줍니다.

   

    

    

   엑셀의 이 기능을 모를 때는 최적화 방법을 매틀랩으로 수동 코딩을 했었습니다. 이 기능이 있다는 걸 안 후 최적화 수업 과제가 엄청 쉬워졌었던 기억이 납니다.