Excel Solver를 사용한 최적화

Solver What-if 분석에서 최적화를 위해 사용할 수있는 Microsoft Excel 추가 기능 프로그램입니다.

오브라이언과 마라카스에 따르면 optimization analysis목표 추구 분석의 더 복잡한 확장입니다. 변수에 대한 특정 목표 값을 설정하는 대신 목표는 특정 제약 조건에서 하나 이상의 목표 변수에 대한 최적 값을 찾는 것입니다. 그런 다음 대상 변수에 대한 최상의 값을 찾을 때까지 지정된 제약 조건에 따라 하나 이상의 다른 변수가 반복적으로 변경됩니다.

Excel에서는 다음을 사용할 수 있습니다. Solver 찾기 위해 optimal value 워크 시트의 다른 수식 셀 값에 대한 특정 제약 조건이나 제한에 따라 목표 셀이라고하는 한 셀의 수식에 대한 (최대 또는 최소값 또는 특정 값).

즉, Solver는 목표 및 제약 조건 셀의 공식을 계산하는 데 사용되는 결정 변수라는 셀 그룹과 함께 작동합니다. Solver는 제약 조건 셀의 한계를 충족하고 목표 셀에 대해 원하는 결과를 생성하도록 결정 변수 셀의 값을 조정합니다.

Solver를 사용하여 다음과 같은 다양한 문제에 대한 최적의 솔루션을 찾을 수 있습니다.

수익성을 극대화하는 의약품 제조 단위의 월간 제품 믹스를 결정합니다.
조직의 인력 일정.
운송 문제 해결.
재정 계획 및 예산.

솔버 애드 인 활성화

솔버 문제에 대한 솔루션 찾기를 진행하기 전에 Solver Add-in 다음과 같이 Excel에서 활성화됩니다-

리본에서 데이터 탭을 클릭합니다. 그만큼Solver 명령은 아래와 같이 분석 그룹에 나타납니다.

Solver 명령을 찾지 못한 경우 다음과 같이 활성화하십시오.

파일 탭을 클릭하십시오.
왼쪽 창에서 옵션을 클릭합니다. Excel 옵션 대화 상자가 나타납니다.
왼쪽 창에서 추가 기능을 클릭합니다.
관리 상자에서 Excel 추가 기능을 선택하고 이동을 클릭합니다.

추가 기능 대화 상자가 나타납니다. 검사Solver Add-in확인을 클릭합니다. 이제 데이터 탭의 리본에서 Solver 명령을 찾을 수 있습니다.

Solver에서 사용하는 해결 방법

문제 유형에 따라 Excel Solver가 지원하는 다음 세 가지 해결 방법 중 하나를 선택할 수 있습니다.

LP 단면

선형 문제에 사용됩니다. ㅏSolver 모델은 다음 조건에서 선형입니다-

대상 세포는 (변화하는 세포) * (상수) 형태의 항을 더하여 계산됩니다.
각 제약 조건은 선형 모델 요구 사항을 충족합니다. 즉, 각 제약 조건은 (변경 셀) * (상수) 형식의 항을 더하고 합계를 상수와 비교하여 평가됩니다.

일반화 감소 기울기 (GRG) 비선형

부드러운 비선형 문제에 사용됩니다. 대상 셀, 제약 조건 중 하나 또는 둘 다 (변경 셀) * (상수) 형식이 아닌 변경 셀에 대한 참조를 포함하는 경우 비선형 모델이 있습니다.

진화

솔버 평가 이해

솔버에는 다음 매개 변수가 필요합니다.

결정 변수 셀
제약 셀
목표 세포
해결 방법

솔버 평가는 다음을 기반으로합니다.

결정 변수 셀의 값은 제약 조건 셀의 값으로 제한됩니다.
목표 셀의 값 계산에는 결정 변수 셀의 값이 포함됩니다.
Solver는 선택한 Solving Method를 사용하여 목표 셀에서 최적의 값을 산출합니다.

문제 정의

특정 제품을 제조 및 판매하는 회사의 수익을 분석한다고 가정 해보십시오. 최대 20,000을 조건으로 다음 2 분기 동안 광고에 사용할 수있는 금액을 찾아야합니다. 각 분기의 광고 수준은 다음에 영향을 미칩니다.

판매 된 단위 수로, 판매 수익 금액을 간접적으로 결정합니다.
관련 비용 및
이익.

문제를 다음과 같이 정의 할 수 있습니다.

단가를 찾으십시오.
단위당 광고 비용을 찾으십시오.
단가를 찾으십시오.

다음으로 필요한 계산을위한 셀을 아래와 같이 설정합니다.

관찰 할 수 있듯이 고려중인 Quarter1 및 Quarter2에 대한 계산은 다음과 같습니다.

1 분기에 판매 가능한 단위 수는 400 개이고 2 분기에는 600 개 (셀 – C7 및 D7)입니다.
광고 예산의 초기 값은 분기당 10000으로 설정됩니다 (셀 – C8 및 D8).
판매 된 단위 수는 단위당 광고 비용에 따라 달라 지므로 분기 별 예산입니다. 단위당 비용. Min 함수를 사용하여 아니요를 확인했습니다. 판매 단위 수 <= no. 사용 가능한 단위의. (셀 – C9 및 D9).
수익은 단가 * 판매 단위 수 (셀 – C10 및 D10)로 계산됩니다.
비용은 단위 비용 * 사용 가능한 단위 수 + 고급으로 계산됩니다. 해당 분기의 비용 (셀 – C11 및 D12).
이익은 수익 – 비용입니다 (셀 C12 및 D12).
총 이익은 1 분기 이익 + 2 분기 이익 (셀 – D3)입니다.

다음으로 Solver에 대한 매개 변수를 아래와 같이 설정할 수 있습니다.

보시다시피 Solver의 매개 변수는 다음과 같습니다.

목표 셀은 최대화하려는 총 이익을 포함하는 D3입니다.
의사 결정 변수 셀은 분기 1 및 분기 2의 두 분기에 대한 예산을 포함하는 C8 및 D8입니다.
세 가지 제약 조건 셀 (C14, C15 및 C16)이 있습니다.
- 총 예산을 포함하는 셀 C14는 제약 조건을 20000 (셀 D14)으로 설정합니다.
- 번호를 포함하는 셀 C15. 1 분기에 판매 된 단위 수는 <= 아니오의 제약 조건을 설정하는 것입니다. Quarter1 (셀 D15)에서 사용 가능한 장치 수.
- 번호가 포함 된 셀 C16. Quarter2에 판매 된 단위의 제한은 <= no. Quarter2 (D16 셀)에서 사용 가능한 장치 수

문제 해결

다음 단계는 Solver를 사용하여 다음과 같이 솔루션을 찾는 것입니다.

Step 1− 리본에서 데이터> 분석> 솔버로 이동합니다. Solver Parameters 대화 상자가 나타납니다.

Step 2 − 목표 설정 상자에서 D3 셀을 선택합니다.

Step 3 − 최대를 선택합니다.

Step 4 − C8 : D8 범위를 By Changing Variable Cells 상자.

Step 5 − 다음으로 추가 버튼을 클릭하여 식별 한 세 가지 제약 조건을 추가합니다.

Step 6− 제약 추가 대화 상자가 나타납니다. 총 예산에 대한 제한을 아래와 같이 설정하고 추가를 클릭합니다.

Step 7− 총 개수 제한을 설정합니다. 다음과 같이 Quarter1에 판매 된 단위 수를 선택하고 추가를 클릭합니다.

Step 8− 총 개수에 대한 제약을 설정합니다. 다음과 같이 Quarter2에 판매 된 단위 수를 선택하고 확인을 클릭합니다.

Solver Parameters 대화 상자가 상자 –Subject to the Constraints에 추가 된 세 가지 제약 조건과 함께 나타납니다.

Step 9 −에서 Select a Solving Method 상자에서 Simplex LP를 선택합니다.

Step 10− 해결 버튼을 클릭합니다. 솔버 결과 대화 상자가 나타납니다. 고르다Keep Solver Solution 확인을 클릭합니다.

결과가 워크 시트에 나타납니다.

보시다시피 주어진 제약 조건에 따라 최대 총 이익을 생성하는 최적의 솔루션은 다음과 같습니다.

총 이익 – 30000.
Adv. 1/4 분기 예산 – 8000.
Adv. 2 분기 – 12000 년 예산.

솔버 평가판 솔루션 단계별 실행

반복 결과를 확인하면서 솔버 시험 솔루션을 단계별로 살펴볼 수 있습니다.

Step 1 − Solver Parameters 대화 상자에서 Options 버튼을 클릭합니다.

그만큼 Options 대화 상자가 나타납니다.

Step 2 − 반복 결과 표시 상자를 선택하고 확인을 클릭합니다.

Step 3 − Solver Parameters대화 상자가 나타납니다. 딸깍 하는 소리Solve.

Step 4 − Show Trial Solution 메시지를 표시하는 대화 상자가 나타납니다. Solver paused, current solution values displayed on worksheet.

보시다시피 현재 반복 값이 작업 셀에 표시됩니다. 솔버가 현재 결과를 받아들이는 것을 중지하거나 솔버가 추가 단계에서 솔루션을 찾지 못하도록 계속할 수 있습니다.

Step 5 − 계속을 클릭합니다.

그만큼 Show Trial Solution모든 단계에서 대화 상자가 나타나고 마지막으로 최적의 솔루션을 찾은 후 Solver Results 대화 상자가 나타납니다. 워크 시트는 모든 단계에서 업데이트되고 마지막으로 결과 값이 표시됩니다.

솔버 선택 저장

Solver로 해결하는 문제에 대해 다음과 같은 저장 옵션이 있습니다.

통합 문서를 저장하여 워크 시트와 함께 Solver Parameters 대화 상자에서 마지막 선택 사항을 저장할 수 있습니다.
통합 문서의 각 워크 시트에는 고유 한 솔버 선택 항목이있을 수 있으며 통합 문서를 저장할 때 모두 저장됩니다.
워크 시트에서 각각 고유 한 솔버 선택 항목이있는 둘 이상의 문제를 정의 할 수도 있습니다. 이러한 경우 솔버 매개 변수 대화 상자의로드 / 저장을 사용하여 개별적으로 문제를로드하고 저장할 수 있습니다.
- 클릭 Load/Save단추. 로드 / 저장 대화 상자가 나타납니다.
- 문제 모델을 저장하려면 문제 모델을 배치 할 빈 셀의 세로 범위의 첫 번째 셀에 대한 참조를 입력합니다. 저장을 클릭하십시오.

문제 모델 (솔버 매개 변수 세트)은 참조로 제공 한 셀에서 시작됩니다.

문제 모델을로드하려면 문제 모델을 포함하는 전체 셀 범위에 대한 참조를 입력하십시오. 그런 다음로드 버튼을 클릭합니다.