[엑셀 연재] 5. 최적화 수행하기

이번에는 엑셀을 이용한 최적화를 수행해볼려고 합니다. 사실 연재의 진도상 좀 빨리 소개하는 면도 있긴합니다만, 그래도 어려운 것이 아닙니다. 사실 서식 어쩌고 다른 분들이 다 아는 이야기할려니 좀 지루하기도 하구요. ^^... 최적화에 대한 이야기는 '공학입문설계'를 연재하던 당시에도 한 번 다루었는데요[관련글] 당시엔 엑셀 쪽은 너무 대충 다루었기 때문에 좀더 자세히 설명할려고 합니다.

위의 머그컵을 만들던 문제가 당시 다루었던 것인데요. 다시 설명하면, 이번 엑셀 연재의 내용은 저 머그컵의 재료비를 어떻게 하면 최소로 할 것인가를 엑셀로 풀어보자는 것입니다.

단위 면적당 재료의 가격(Z)이 나타나 있습니다. 이때, 변수는 직경(d)과 높이(h)입니다. 그러니 가격 계수를 의미하는 A는 상수구요... 그리고 제한조건은 머그컵의 용량은 500cc이상이기를 바라고, 직경은 5-9사이기를... 높이는 10에서 15사이기를 바랍니다.

즉, 머그컵의 최소 재료를 설정하는 직경과 높이를 구하는 것이 목적이고 위의 수식을 만족하면 되겠습니다.^^

먼저 위와 같이 엑셀을 꾸미구요.^^ 의미는 차차 설명하겠습니다.

먼저 E3셀에 표시된데로 수식을 입력합니다. "PI()"는 3.14 어쩌고하는 원주율이구요. 최소화 해야할 재료비를 의미합니다. 이때, 엑셀이 구해주기를 바라는 높이(C2)와 직경(D2)을 사용해서 표현해야합니다. 물론 지금은 '0'이라고 결과가 나오겠지만요^^

그리고, E4셀에는 부피- 그러니까 용량에 관한 제한 조건을 넣어둡니다. 응? PI()앞에 괄호가 하나 쓸데없이 들어가 버렸네요. 딱 보면 아실테니 '('는 빼주세요.

이제 높이에 대한 제한조건을 입력할 건데 10<=h<=15를 한번에 넣어 둘기가 어렵습니다. 그래서 h>=10를 먼저 표현한 것으로, C5가 '1'이니까... 변수로 설정할(나중에 설정할 겁니다.) C2를 표현한 것과 같습니다. C5는 값이 '1'이니까요

그리고, 작다를 하고 싶은데.. 그게 좀 애매하거든요... 그래서 -h>=-15로 표현해버리는 겁니다. 그러면 크거나같다로 표현할 수 있지요. 그것때문에 C6에 -1을 넣어둔것입니다. 일괄적으로 적용할려다보니 이렇게 표현한 것입니다.

마찬가지로, 직경(d)에 대해서도

이렇게 해둡니다. 

이제

엑셀의 메인버튼을 누르고, Excel옵션을 선택한 후,

추가기능에 해찾기추가기능을 선택하고, '이동'버튼을 누릅니다. 저는 자료를 만들면서 이미 등록해서 그렇구요. 아마 처음하시는 분들은 비활성 응용 프로그램 부분에 들어있을 겁니다.

그러고 나면 데이터 탭 제일 끝쪽에 '해찾기'라는 메뉴가 나타날 것입니다.

그걸 선택하면 저런 창이 하나 뜨지요

목표셀에는 $E$3라고 절대참조로 하시고, 해의 조건은 최소값으로 설정하세요

그리고 변수.. 즉, 값을바꿀셀에는 마우스로 C2부터 D2를 긁어주시고,

제한조건에 추가를 누르셔서

참조영역에 E4부터 E8을 쭈욱 긁고

가운데 조건을 위와 같이 바꾸시고

제한조건에는 G4부터 G8을 또 선택하시면 됩니다.

그리고 실행을 누르시면....^^

보고서는 뭐... 원하시는 데로 선택하시고.. 확인^^

요렇게 결과가 나타나는 거죠... 높이는 대략 10으로 직경은 대략 7.98정도??로 해주면 제한조건을 모두 만족하면서 목적함수.. 그러니까.. 재료비를 최소로할 수 있다는 것이죠...^^

참고자료. 

05. 최적화설계.pdf