Excel Solver: 결과 최적화, 제약 조건 추가 및 솔루션을 시나리오로 저장

솔버 매개변수 대화 상자를 사용하여 Excel 솔버 모델을 설정합니다 . 목표 설정 상자를 사용하여 목표 셀을 지정하고 대상 그룹을 사용하여 Excel Solver에 목표 셀에서 원하는 것을 알립니다. 가능한 최소값; 또는 특정 값. 마지막으로, 변수 셀 변경 상자를 사용하여 결과를 최적화하기 위해 값을 연결하는 데 솔버가 사용할 수 있는 셀을 지정합니다.

Excel Solver 결과 최적화

해 찾기가 솔루션을 찾으면 해 찾기 솔루션 유지 또는 원래 값 복원을 선택할 수 있습니다. 해 찾기 솔루션 유지를 선택하면 Excel에서 워크시트를 영구적으로 변경합니다. 변경 사항은 실행 취소할 수 없습니다.

Solver-ready 워크시트 모델이 준비된 상태에서 Solver를 사용하여 모델에 대한 최적의 결과를 찾기 위해 따라야 할 단계는 다음과 같습니다.

데이터 → 솔버를 선택합니다.
Excel에서 해 찾기 매개 변수 대화 상자가 열립니다.

목표 설정 상자에 모델의 목표 셀 주소를 입력합니다.
셀을 클릭하여 입력하면 Solver가 자동으로 절대 셀 주소를 입력합니다(예: B14 대신 $B$14). 솔버는 어느 쪽이든 잘 작동합니다.

받는 사람 그룹에서 다음 옵션을 선택합니다.

• 최대: 가능한 최대 값을 반환합니다.
• 최소값: 가능한 최소값을 반환합니다.
• 값: 목표 셀을 해당 숫자로 설정할 숫자를 입력합니다.

  예제 모델의 경우 Value Of가 선택되었고 텍스트 상자에 0이 입력되었습니다.

변수 셀 변경 상자에 Solver가 솔루션을 찾는 동안 변경할 셀의 주소를 입력합니다.
이 예에서 변경 셀은 B4 및 C4입니다. 다음 이미지는 완료된 솔버 매개변수 대화 상자를 보여줍니다.

완성된 솔버 매개변수 대화 상자.

해결을 클릭합니다.
해결사가 업무에 착수합니다. Solver가 문제를 해결할 때 Show Trial Solution 대화 상자가 한 번 이상 표시되는 것을 볼 수 있습니다.

표시되는 평가판 표시 대화 상자에서 계속을 클릭하여 계속 진행합니다.
최적화가 완료되면 Excel에 해 찾기 결과 대화 상자가 표시됩니다.

솔버 결과 대화 상자 및 손익분기점 문제에 대한 솔루션입니다.

솔버 솔루션 유지 옵션을 선택합니다.
결과를 수락하지 않으려면 대신 원래 값 복원 옵션을 선택하십시오.

확인을 클릭합니다.

결과에 대한 추가 정보를 제공하는 하나 이상의 보고서를 표시하도록 Solver에 요청할 수 있습니다. 해 찾기 결과 대화 상자에서 보고서 목록을 사용하여 보려는 각 보고서를 선택합니다.

• 답변: 모델의 목표 셀, 변수 셀 및 제약 조건에 대한 정보를 표시합니다. 목표 셀 및 변수 셀의 경우 Solver는 원래 값과 최종 값을 표시합니다.
• 민감도: 솔루션이 모델 공식의 변화에 ​​얼마나 민감한지를 보여주려고 시도합니다. 민감도 보고서의 레이아웃은 사용 중인 모델 유형에 따라 다릅니다.
• 제한: 목표 셀과 해당 값, 변수 셀과 해당 주소, 이름 및 값을 표시합니다.

Excel Solver는 여러 해결 방법 중 하나를 사용할 수 있습니다. 솔버 매개변수 대화 상자에서 해결 방법 선택 목록을 사용하여 다음 중 하나를 선택합니다.

• Simplex LP: 워크시트 모델이 선형인 경우 사용합니다. 가능한 가장 간단한 용어로 선형 모델은 변수가 어떤 거듭제곱으로도 증가하지 않고 SIN 및 COS와 같은 소위 초월 함수가 사용되지 않는 모델입니다.
• GRG 비선형: 워크시트 모델이 비선형이고 부드러운 경우 사용합니다. 일반적으로 부드러운 모델은 사용된 방정식의 그래프가 날카로운 모서리나 끊김을 나타내지 않는 모델입니다.
• 진화형: 워크시트 모델이 비선형이고 평활하지 않은 경우 사용합니다.

이것에 대해 걱정할 필요가 있습니까? 거의 확실하지 않습니다. Excel Solver는 기본적으로 GRG Nonlinear를 사용하며 이는 Solver로 수행하는 거의 모든 작업에서 작동합니다.

Excel 솔버에 제약 조건 추가

현실 세계는 공식에 제한과 조건을 둡니다. 공장의 하루 최대 생산 능력은 10,000단위이고 회사의 직원 수는 음수가 될 수 없으며 광고 비용은 총 비용의 10%로 제한될 수 있습니다.

마찬가지로 두 제품에 대해 손익분기점 분석을 실행하고 있다고 가정합니다. 제한 없이 최적화를 실행하면 한 제품을 약간의 손실로 설정하고 다른 제품을 약간의 이익으로 설정하여 손실과 이익이 서로 상쇄되어 Solver가 총 이익 0에 도달할 수 있습니다. 사실 이전 이미지를 자세히 보면 이것이 바로 Solver가 한 일입니다. 진정한 손익분기점 솔루션을 얻으려면 두 제품 이익 값을 모두 0으로 보는 것이 좋습니다.

이러한 제한 및 조건은 Solver가 Constraints 라고 부르는 것의 예입니다 . 제약 조건을 추가하면 이러한 조건이 위반되지 않도록 Solver에 솔루션을 찾도록 지시합니다.

다음은 최적화에 제약 조건이 추가된 솔버를 실행하는 방법입니다.

데이터 → 솔버를 선택합니다.
Excel에서 해 찾기 매개 변수 대화 상자가 열립니다.

목표 설정 상자, 대상 그룹 및 변수 셀 변경으로 상자를 사용하여 위에서 설명한 대로 솔버를 설정합니다.

추가를 클릭합니다.
Excel에 제약 조건 추가 대화 상자가 표시됩니다.

셀 참조 상자에 제한할 셀의 주소를 입력합니다.
주소를 입력하거나 워크시트에서 셀을 선택할 수 있습니다.

드롭다운 목록에서 사용할 연산자를 선택합니다.
대부분의 경우 같음(=) 또는 보다 큼(>)과 같은 비교 연산자를 사용합니다. 전체 직원과 같은 제약 조건이 실수(즉, 소수 구성 요소가 있는 숫자, 10.5명의 직원이 있을 수 없음)가 아닌 정수 값이 되어야 하는 경우 int(정수) 연산자를 사용합니다. TRUE 또는 FALSE(또는 1 또는 0)여야 하는 제약 조건이 있는 경우 bin(이진) 연산자를 사용합니다.

5단계에서 비교 연산자를 선택한 경우 제약 조건 상자에 셀을 제한할 값을 입력합니다.
이 이미지는 완료된 제약 조건 추가 대화 상자의 예를 보여줍니다. 예제 모델에서 이 제약 조건은 Inflatable Dartboard(셀 B12)의 제품 이익이 0과 같은 솔루션을 찾도록 Solver에 지시합니다.

완료된 제약 조건 추가 대화 상자.

더 많은 제약 조건을 지정하려면 추가를 클릭하고 필요에 따라 4-6단계를 반복합니다.

예를 들어 Dog Polisher 제품 이익(C12 셀)이 0이 되도록 요청하는 제약 조건을 추가합니다.

확인을 클릭합니다.

Excel이 Solver Parameters 대화 상자로 돌아가고 Subject to the Constraints 목록 상자에 제약 조건이 표시됩니다.

해결을 클릭합니다.

표시되는 평가판 표시 대화 상자에서 계속을 클릭하여 계속 진행합니다.
아래 이미지는 제약 조건이 추가된 손익분기 솔루션의 예를 보여줍니다. Total Profit 셀(B14)이 0으로 설정되어 있을 뿐만 아니라 두 개의 Product Profit 셀(B12 및 C12)도 0으로 설정되어 있습니다.

솔버 결과 대화 상자 및 손익분기점 문제에 대한 최종 솔루션.

솔버 솔루션 유지 옵션을 선택합니다.
결과를 수락하지 않으려면 대신 원래 값 복원 옵션을 선택하십시오.

확인을 클릭합니다.

최대 100개의 제약 조건을 추가할 수 있습니다. 또한 해석을 시작하기 전에 제약 조건을 변경해야 하는 경우 제약 조건 목록 상자에서 제약 조건을 선택하고 변경을 클릭한 다음 나타나는 제약 조건 변경 대화 상자에서 조정합니다. 더 이상 필요���지 않은 제약 조건을 삭제하려면 제약 조건을 선택한 다음 삭제를 클릭합니다.

Excel Solver 솔루션을 시나리오로 저장

셀 변경 이라고 하는 일관된 입력 값 집합을 사용하는 스프레드시트 모델이 있을 때마다 Excel에서 시나리오 라고 부르는 것이 있습니다. Solver를 사용하면 이러한 변화하는 셀이 변수 셀이므로 Solver 솔루션 은 Excel에서 일종의 시나리오에 해당 합니다. 그러나 Solver는 특정 솔루션을 저장하고 재실행하는 쉬운 방법을 제공하지 않습니다. 이 문제를 해결하려면 솔루션을 시나리오로 저장한 다음 나중에 Excel의 시나리오 관리자 기능을 사용하여 불러올 수 있습니다.

솔버 솔루션을 시나리오로 저장하려면 다음 단계를 따르십시오.

데이터 → 솔버를 선택합니다.
Excel에서 해 찾기 매개 변수 대화 상자가 열립니다.

목표 설정 상자, 대상 그룹, 변수 셀을 변경하여 상자 및 제약 조건 목록에 대한 주제를 사용하여 위에서 설명한 대로 솔버를 설정합니다.

해결을 클릭합니다.

Show Trial Solution 대화 상자가 나타날 때마다 계속을 선택하십시오.
최적화가 완료되면 Excel에 해 찾기 결과 대화 상자가 표시됩니다.

시나리오 저장을 클릭합니다.
Excel에 시나리오 저장 대화 상자가 표시됩니다.

시나리오 이름 대화 상자에서 시나리오 이름을 입력한 다음 확인을 클릭합니다.
Excel에서 해 찾기 결과 대화 상자로 돌아갑니다.

솔버 솔루션 유지 옵션을 선택합니다.
결과를 수락하지 않으려면 대신 원래 값 복원 옵션을 선택하십시오.

확인을 클릭합니다.