Excel 2019 솔버를 사용하는 방법

Excel의 데이터 테이블 및 목표 찾기 명령 은 수식에서 입력과 결과 간의 직접적인 관계를 결정해야 하는 간단한 문제에 대해 잘 작동하지만 더 복잡한 문제를 처리할 때는 해 찾기 추가 기능을 사용해야 합니다. 예를 들어, Excel 모델에서 여러 입력 값을 변경해야 하고 이러한 값 및/또는 출력 값에 제약 조건을 적용해야 하는 경우 Solver를 사용하여 최상의 솔루션을 찾습니다.

Solver 추가 기능은 입력, 원하는 솔루션 및 사용자가 부과하는 제약 조건이 주어지면 "최상의" 솔루션을 찾기 위해 반복적인 방법을 적용하여 작동합니다. 각 반복에서 프로그램은 최적의 솔루션에 더 가까워지도록 시도하는 시행착오 방법(선형 또는 비선형 방정식 및 부등식 사용을 기반으로 함)을 적용합니다.

해 찾기 추가 기능을 사용할 때 많은 문제, 특히 더 복잡한 문제에는 많은 솔루션이 있음을 명심하십시오. Solver는 시작 값, 변경할 수 있는 변수 및 정의한 제약 조건이 주어지면 최적의 솔루션을 반환하지만 이 솔루션이 가능한 유일한 솔루션은 아니며 실제로 최상의 솔루션이 아닐 수도 있습니다. 최상의 솔루션을 찾기 위해 문제를 해결할 때마다 초기 값을 조정하면서 솔버를 두 번 이상 실행할 수 있습니다.

Excel 워크시트에서 해 찾기 추가 기능에 대한 문제를 설정할 때 다음 항목을 정의하십시오.

• 목표 셀: 해당 값이 최대화, 최소화 또는 특정 값에 도달하도록 하는 워크시트의 대상 셀입니다. 이 셀에는 수식이 포함되어야 합니다.
• 가변 셀: 답을 찾을 때까지 값을 조정해야 하는 워크시트의 변경 셀입니다.
• 제약 조건 셀: 변수 셀 및/또는 목표 셀의 대상 셀에서 값 변경에 부과하는 제한이 포함된 셀입니다.

이러한 매개변수로 문제 정의를 완료하고 해 찾기 추가 기능이 문제를 해결하도록 하면 프로그램이 워크시트의 값을 수정하여 최적의 솔루션을 반환합니다. 이 시점에서 워크시트의 변경 사항을 유지하거나 워크시트의 원래 값을 복원하도록 선택할 수 있습니다. 원래 값을 복원하기 전에 나중에 볼 수 있도록 솔루션을 시나리오로 저장할 수도 있습니다.

Scenario Manager 와 함께 Solver 추가 기능을 사용하여 해결할 문제를 설정하거나 나중에 볼 수 있도록 솔루션을 저장할 수 있습니다. 시나리오 관리자에 대해 정의한 변경 셀은 이 명령을 선택하면 자동으로 선택되어 솔버에서 사용되며 그 반대의 경우도 마찬가지입니다. 또한 문제에 대한 솔버의 솔루션을 시나리오로 저장할 수 있습니다(Solver 대화 상자에서 Save Scenario 버튼 클릭). 그런 다음 시나리오 관리자로 볼 수 있습니다.

Excel 2019에서 문제 설정 및 정의

솔버가 작업할 문제를 설정하는 첫 번째 단계는 목표 셀, 변수 셀 및 제약 조건 셀을 정의할 워크시트 모델을 만드는 것입니다.

해 찾기는 추가 기능 유틸리티라는 점에 유의하십시오. 즉, 리본의 데이터 탭 끝에 있는 분석 그룹의 솔버 버튼 모양으로 표시된 대로 솔버 추가 기능 프로그램이 여전히 로드되어 있는지 확인해야 사용 가능합니다. 이 단추가 없으면 Excel 옵션 대화 상자(Alt+FTAA)의 추가 기능 탭을 열고 Excel 추가 기능이 관리 드롭다운에 표시되는지 확인한 후 이동 단추를 클릭하여 해 찾기를 로드할 수 있습니다. 바로 왼쪽에 있는 목록 상자. 그런 다음 확인을 클릭하여 대화 상자를 닫고 추가 기능을 다시 로드하기 전에 추가 기능 대화 상자에서 해 찾기 추가 기능 확인란을 선택하여 확인 표시를 합니다.

추가 기능을 로드하고 워크시트 모델을 만든 후 해 찾기 추가 기능의 문제를 정의하고 해결하려면 다음 단계를 수행합니다.

리본의 데이터 탭 끝에 있는 분석 그룹에서 솔버 명령 버튼을 클릭합니다.

Excel에서 해 찾기 매개 변수 대화 상자가 열립니다.

솔버 매개변수 대화 상자에서 모델에 적용할 매개변수를 지정합니다.

워크시트에서 대상 셀을 클릭하거나 목표 설정 텍스트 상자에 해당 셀 참조 또는 범위 이름을 입력합니다.

다음으로 To 설정을 선택해야 합니다. 대상 셀의 값을 최대한 크게 하려면 최대 옵션 버튼을 클릭합니다. 대상 셀의 값을 가능한 한 작게 하려면 최소 옵션 버튼을 클릭합니다. 대상 셀의 값이 특정 값에 도달하도록 하려면 값 옵션 버튼을 클릭한 다음 연결된 텍스트 상자에 값을 입력합니다.

대화 상자의 대상 섹션에서 적절한 옵션 버튼 옵션을 클릭합니다. 값 옵션 버튼을 선택한 경우 연결된 텍스트 상자에 일치시킬 값을 입력합니다.

다음으로 변수 셀을 지정합니다. 즉, Solver가 Equal To 목표에 도달하기 위해 변경할 수 있는 셀을 지정합니다.

변수 셀을 변경하여 텍스트 상자를 클릭한 다음 워크시트에서 변경할 셀을 선택하거나 텍스트 상자에 셀 참조 또는 범위 이름을 입력합니다.

워크시트에서 인접하지 않은 셀을 선택하려면 Ctrl 키를 누른 상태에서 선택 영역의 각 셀을 클릭해야 합니다. Excel에서 선택한 대상 셀을 기반으로 변경 셀을 선택하도록 하려면 이 텍스트 상자 오른쪽에 있는 추측 단추를 클릭하십시오.

Solver가 모델을 조정하도록 하기 전에 대상 셀 또는 값을 조정할 때 한계를 결정하는 변경 셀에 대한 제약 조건을 추가할 수 있습니다.

(선택 사항) Solver Parameters 대화 상자에서 Subject to the Constraints 목록 상자 오른쪽에 있는 Add 버튼을 클릭합니다.

이 작업은 제약 조건 추가 대화 상자를 엽니다. 제약 조건을 정의할 때 값을 제약할 셀을 선택하거나 워크시트에서 셀을 선택하거나 셀 참조 텍스트 상자에 셀 참조를 입력합니다. 그런 관계를 선택 (=, <=,> =, 또는 INT 정수 나에 대한 빈 바이너리에 대한) 오른쪽에있는 드롭 다운 목록 상자에서와 (당신이 선택하지 않는 INT 또는 빈 에 적절한 값 또는 셀 참조를 입력합니다) 제약 조건 텍스트 상자.

솔버에서 사용하는 다른 셀에 대한 제약 조건을 계속 추가하려면 추가 버튼을 클릭하여 제약 조건을 추가하고 제약 조건 추가 대화 상자에서 텍스트 상자를 지웁니다. 그런 다음 5단계를 반복하여 새 제약 조건을 추가합니다. 대상 셀에 대한 제약 조건 정의 및 모델의 값 변경을 완료한 후 확인을 클릭하여 제약 조건 추가 대화 상자를 닫고 솔버 매개 변수 대화 상자(이제 제약 조건 목록 상자에 제약 조건이 나열됨)로 돌아갑니다.

(선택 사항) 변수 셀에 제약 조건이 적용되지 않을 때 음수 값을 허용하려면 제약 없는 변수를 음수가 아닌 것으로 만들기 확인란을 선택 취소합니다.
기본적으로 Solver Add-in은 부드러운 비선형 문제를 해결하는 매우 효율적인 방법으로 알려진 매개변수를 설정하는 모델을 풀 때 GRG(Generalized Reduced Gradient) 비선형 방법을 사용합니다. LP Simplex 방법(심플렉스 알고리즘을 따르는 선형 계획법) 또는 매끄럽지 않은 문제를 해결하기 위한 Evolutionary 엔진을 사용하려면 7단계를 따라야 합니다.

(선택 사항) 해결 방법 선택 드롭다운 목록에서 LP Simplex 또는 Evolutionary를 선택하여 평활하지 않은 문제를 푸는 이러한 방법 중 하나를 사용합니다.

솔버 매개변수 대화 상자에서 정의한 대로 솔버가 문제를 해결하도록 하려면 해결 버튼을 클릭합니다.

Excel의 Solver로 문제 해결

풀기 버튼을 클릭하면 솔버 매개변수 대화 상자가 사라지고 상태 표시줄에 솔버가 문제를 설정하고 있음을 표시한 다음 중간(또는 시도) 번호를 표시하여 문제 해결 진행 상황을 계속 알려줍니다. 시도한 대로 솔루션을 제공합니다. Excel에서 마지막 반복을 계산하기 전에 언제든지 솔루션 프로세스를 중단하려면 Esc 키를 누릅니다. 그런 다음 Excel은 솔루션 프로세스가 일시 중지되었음을 알리는 시험 솔루션 표시 대화 상자를 표시합니다. 솔루션 프로세스를 계속하려면 계속 버튼을 클릭하십시오. 솔루션 프로세스를 중단하려면 중지 버튼을 클릭합니다.

Excel에서 솔루션 프로세스가 완료되면 해 찾기 결과 대화 상자가 나타납니다. 이 대화 상자는 대상 셀, 셀 변경 및 문제에 대해 정의된 제약 조건이 주어졌을 때 솔버가 솔루션을 찾을 수 있었는지 여부를 알려줍니다. 솔버가 워크시트 모델에서 수행한 변경 사항을 유지하려면 솔버 솔루션 유지 옵션 버튼을 선택한 상태로 두고 확인을 클릭하여 솔버 결과 대화 상자를 닫습니다. 원래 값을 워크시트로 되돌리려면 대신 원래 값 복원 옵션 단추를 클릭하십시오. 원래 값을 복원하기 전에 변경 사항을 시나리오로 저장하려면 원래 값 복원 옵션과 확인 단추를 클릭하기 전에 시나리오 저장 버튼을 클릭하고 현재 시나리오에 이름을 지정하십시오.

솔버가 문제에 대한 솔루션을 찾았음을 보여주는 솔버 결과 대화 상자.

목표 찾기 명령을 사용할 때와 달리 솔버 결과 대화 상자에서 솔버 솔루션 유지 옵션 버튼을 클릭한 후에는 빠른 실행 도구 모음에서 실행 취소 명령 버튼을 사용하여 워크시트에 원래 값을 복원할 수 없습니다. 워크시트의 "이전"과 "이후" 보기 간에 전환할 수 있도록 하려면 시나리오 저장 버튼을 사용하여 변경 사항을 저장한 다음 원래 값 복원 옵션 버튼을 선택해야 합니다. 이렇게 하면 원본 워크시트의 "이전" 보기를 유지하고 시나리오 관리자를 사용하여 솔버에서 만든 "이후" 보기를 표시할 수 있습니다.

Excel의 해 찾기 옵션 변경

For most of the problems, the default options used by the Solver are adequate. In some situations, however, you may want to change some of the Solver options before you begin the solution process. To change the solution options, click the Options button in the Solver Parameters dialog box. Excel then opens the Options dialog box with the All Methods tab selected where you can make all necessary changes.

Modifying the solution options in the Options dialog box.

Excel 2019 Solver Option Settings

옵션을 변경한 후 확인을 클릭하여 솔버 매개변수 대화 상자로 돌아갑니다. 여기에서 해결 버튼을 클릭하여 방금 변경한 새 솔루션 설정으로 솔루션 프로세스를 시작할 수 있습니다.

기본 GRG(Generalized Reduced Gradient) 비선형 또는 진화 방법을 사용하는 경우 옵션 대화 상자의 GRG 비선형 및 진화 탭에 있는 옵션을 사용하여 추가 솔버 설정을 지정할 수 있습니다. 이러한 옵션에는 이러한 특정 방법 중 하나에 대한 수렴, 인구 크기 및 임의 시드 설정 변경이 포함됩니다.

Excel 2019에서 모델 문제 저장 및 로드

가장 최근에 사용한 목표 셀, 변수 셀, 제약 조건 셀 및 솔버 옵션은 빠른 실행 도구 모음(Ctrl+S)에서 저장 버튼을 클릭하면 Excel 워크시트의 일부로 저장됩니다. 저장하려는 동일한 워크시트에 대해 다른 문제를 정의할 때 솔버 옵션 대화 상자에서 모델 저장 버튼을 클릭 하고 문제의 매개변수를 저장할 활성 워크시트 의 범위 이름 이나 셀 참조를 지정 해야 합니다. 삽입.

로드/저장 버튼을 클릭하면 Excel에서 모델 영역 선택 텍스트 상자가 포함된 모델 로드/저장 대화 상자가 열립니다. 이 텍스트 상자에는 활성 셀부터 시작하여 문제의 모든 매개변수를 포함할 수 있을 만큼 충분히 큰 범위에 대한 셀 참조가 포함됩니다. 문제의 매개변수를 이 범위에 저장하려면 확인을 클릭하십시오. 이 범위에 기존 데이터가 있는 셀이 포함된 경우 확인을 클릭하기 전에 이 텍스트 상자에서 셀 참조를 수정하여 Excel에서 기존 데이터를 바꾸지 못하도록 해야 합니다.

확인을 클릭하면 Excel이 문제의 매개변수를 지정된 범위에 복사합니다. 이러한 값은 다음에 통합 문서를 저장할 때 워크시트의 일부로 저장됩니다. 문제를 해결할 때 이러한 문제 매개변수를 재사용하려면 Solver Options 대화 상자를 열고 Load/Save 버튼을 클릭하여 Load/Save Model 대화 상자를 열고 Load 버튼을 클릭한 다음 저장된 범위를 선택하기만 하면 됩니다. 문제 매개변수. 모델 로드 대화 상자에서 확인을 클릭하면 Excel에서 이 셀 범위의 매개 변수를 해 찾기 매개 변수 대화 상자의 해당 텍스트 상자에 로드합니다. 그런 다음 확인을 클릭하여 해 찾기 옵션 대화 상자를 닫고 해결 명령 단추를 클릭하여 이러한 매개 변수를 사용하여 문제를 해결할 수 있습니다.

이전 문제에 대해 정의된 모든 매개변수를 지우고 솔버 옵션을 기본값으로 되돌리고 싶을 때마다 모두 재설정 버튼을 사용할 수 있음을 기억하십시오.

Excel 2019에서 해 찾기 보고서 만들기

솔버를 사용하여 세 가지 유형의 보고서를 생성할 수 있습니다.

• 답변 보고서: 문제 해결에 사용된 제약 조건과 함께 대상 셀과 변경 셀을 원래 및 최종 값과 함께 나열합니다.
• 민감도 보고서: 최적 솔루션이 대상 셀 및 제약 조건을 계산하는 공식의 변경 사항에 얼마나 민감한지를 나타냅니다. 보고서는 최종 값과 각 셀에 대한 감소된 기울기 와 함께 변화하는 셀을 보여줍니다 . (감소된 기울기는 변화하는 셀에서 단위 증가당 목표를 측정합니다.) 제약 조건을 정의한 경우 민감도 보고서는 각 제약 조건에 대한 최종 값 및 라그랑주 승수 와 함께 제약 조건을 나열합니다 . (라그랑주 승수는 제약 방정식의 오른쪽에 나타나는 단위 증가당 목표를 측정합니다.)
• 제한 보고서: 대상 셀 및 해당 값, 하한 및 상한, 대상 결과와 함께 변경되는 셀을 표시합니다. 하한은 다른 모든 셀의 값을 고정하고 여전히 제약 조건을 충족하면서 변경 셀이 가질 수 있는 가장 낮은 값을 나타냅니다. 상한은 이를 수행할 가장 높은 값을 나타냅니다.

Excel은 해결자 문제에 대해 생성한 각 보고서를 통합 문서의 별도 워크시트에 배치합니다. 이러한 보고서 중 하나(또는 모두)를 생성하려면 Solver Results 대화 상자의 Reports 목록 상자에서 보고서 유형(Answer, Sensitivity 또는 Limits)을 선택하십시오. 보고서를 두 개 이상 선택하려면 보고서 이름을 클릭하기만 하면 됩니다.

확인을 클릭하여 해 찾기 결과 대화 상자를 닫으면(해 찾기 솔루션 유지 및 원래 값 복원 옵션 중에서 선택한 후) Excel은 통합 문서의 시작 부분에 추가하는 새 워크시트에서 선택한 보고서를 생성합니다. . (보고서 시트 탭의 이름은 답변 보고서 1, 민감도 보고서 1 및 제한 보고서 1에서 와 같이 보고서 유형별로 지정됩니다 . )