문제 해결을 위한 Excel Solver 사용에 대한 완전한 가이드

Microsoft Excel에는 계산을 수행하고 방정식을 푸는 과정을 간소화하여 생산성을 향상시키는 다양한 기능이 탑재되어 있습니다. 이러한 기능 중 하나는 Goal Seek 기능과 유사한 Solver 도구입니다.

일반적으로 ‘What-if’ 분석에 사용되는 Solver는 사용자가 여러 제약 하에서 특정 셀의 값을 확인할 수 있도록 합니다. 숫자에 대한 특정 값, 최소 임계값 또는 최대 한도를 결정하는 데 사용할 수 있습니다. 모든 문제를 해결하지는 못하지만 Solver는 최상의 결정을 찾는 것이 중요한 최적화 시나리오에 귀중한 리소스입니다.

이 도구는 스프레드시트 내의 결정 변수로 알려진 특정 셀의 값을 조정하여 다른 셀의 최대값 또는 최소값을 식별하는 방식으로 작동합니다. Solver는 선형 및 비선형 프로그래밍, 정수 프로그래밍, 목표 추구 작업을 포함한 다양한 프로그래밍 유형에 적용 가능합니다.

Solver의 일반적인 활용 분야로는 운송비 최소화, 최적의 업무 일정 수립, 광고 이니셔티브를 위한 최적의 예산 수립, 투자 수익 극대화 등이 있습니다.

Excel에서 솔버 활성화

Solver를 사용하려면 먼저 이 애드인을 활성화해야 합니다. Goal Seek 기능처럼 기본적으로 활성화되어 있지 않기 때문입니다. 다행히도 프로세스는 매우 간단합니다.

화면 상단의 파일 메뉴를 선택한 다음 ‘옵션’을 클릭하세요.

다음으로, 옵션 창의 왼쪽에 있는 ‘추가 기능’을 클릭합니다.

이제 하단의 ‘관리’ 드롭다운 메뉴에서 ‘Excel 추가 기능’을 선택하고 ‘이동’을 클릭합니다.

이어지는 대화 상자에서 ‘Solver 추가 기능’ 옆의 상자를 체크해서 활성화한 뒤, ‘확인’을 누릅니다.

이제 Excel에서 ‘데이터’ 탭을 클릭하면 Solver를 사용할 수 있습니다.

Solver의 핵심 구성 요소

Solver가 문제에 대한 최적의 값을 식별하기 전에 세 가지 주요 구성 요소를 설정해야 합니다.

목표 셀: 이 셀에는 문제의 목표나 대상을 나타내는 공식이 들어 있으며, 최소화, 최대화 또는 특정 값 달성 여부가 포함됩니다.
변수 셀: 이 셀은 Solver가 목표를 달성하기 위해 조정할 변수를 보관합니다. Solver에서 최대 200개의 변수 셀을 지정할 수 있습니다.
제약 조건: 제약 조건은 Solver가 원하는 결과를 달성하기 위해 작동해야 하는 매개변수입니다. 필요한 값을 결정하는 동안 충족해야 하는 조건을 정의합니다.

솔버 적용하기

Solver가 Excel에 추가되면 Solver를 활용할 수 있습니다. 이 예에서 Solver를 활용하여 팔레트당 필요한 리소스와 다양한 팔레트 유형의 가용성과 같은 알려진 리소스 값을 기반으로 팔레트 제조 사업의 이익을 계산합니다.

셀 B3에서 E3까지는 회사가 생산해야 하는 다양한 유형의 팔레트를 나열합니다. 바로 아래 행은 각 유형에 대해 제조해야 하는 팔레트 수를 나타내며, 0으로 초기화됩니다. 그 다음 행은 각 팔레트 유형과 관련된 이익을 자세히 설명합니다. 우리의 목표는 각 종류에 대해 제조해야 하는 팔레트 수를 결정하는 것이며, 총 이익은 셀 F5에 표시됩니다. 여기의 제약 조건은 사용 가능한 리소스로, 회사가 실현 가능하게 생산할 수 있는 팔레트 수를 지시합니다.

시작하려면 오른쪽 상단 모서리에 있는 ‘Solver’를 클릭하면 Solver 대화 상자가 나타납니다. 목표 셀에 대한 이름이나 셀 참조를 입력하고 수식이 포함되어 있는지 확인합니다. 이 시나리오에서 셀 F5는 목표 함수 역할을 하며, 사용 가능한 리소스와 생산할 팔레트를 모두 고려하여 모든 팔레트 유형을 합친 총 이익을 산출합니다.

‘변수 셀 변경’ 필드에서 마우스를 끌거나 셀 이름을 직접 입력하여 B4:E4 범위를 선택합니다. 이 셀은 유형당 팔레트 수를 나타내며 현재 0으로 설정되어 있습니다. Solver는 실행 중에 이 값을 조정합니다.

다음으로, ‘추가’ 버튼을 클릭하여 제약 조건을 도입합니다. Solver는 접착제, 압착, 소나무 칩, 참나무 칩과 같은 재료 가용성을 기반으로 회사에서 제조할 수 있는 팔레트 수를 계산합니다. Solver를 실행하면 현재 0인 ‘사용됨’ 열의 값이 변경되는 것을 볼 수 있습니다.

‘사용됨’ 열에 해당하는 ‘셀 참조’에 F8:F11을 입력하고, 제약 조건 필드에서 ‘사용 가능’ 열에 G8:G11을 입력합니다. 관계가 <=기본적으로 설정되어 사용됨 열의 값이 사용 가능 열의 값보다 작거나 같아야 함을 나타냅니다.

모든 변수와 제약 조건을 입력한 후 ‘제약 조건 추가’ 대화 상자에서 ‘추가’를 다시 클릭한 다음 닫습니다. 또한 Solver Parameters 대화 상자에서 ‘제약 조건이 없는 변수를 음수가 아닌 값으로 만들기’ 옵션이 기본적으로 활성화되어 특정 제약 조건이 설정되지 않았더라도 모든 변수가 음수가 아닌 값으로 유지됩니다.

솔버 매개변수 대화 상자에서 입력을 완료한 후 ‘해결’ 버튼을 클릭하고 Excel에서 결과가 나올 때까지 기다립니다.

결과가 생성되면 Solver Results 대화 상자가 나타나 B4~E4 셀에 새 값이 표시됩니다. Solver가 데이터를 변경한다는 점을 명심하세요. 원래 값으로 되돌리려면 ‘원래 값 복원’ 옵션을 선택하면 됩니다. 솔루션을 유지할지 아니면 원래 데이터로 되돌릴지 결정한 후 오른쪽에 ‘답변’이 선택되어 있는지 확인한 다음 ‘확인’을 클릭하여 대화 상자를 종료합니다.

새 솔루션을 유지하기로 선택하면 Solver 대화 상자를 닫을 때 스프레드시트에 반영됩니다. 회사의 생산에는 Tahoe 팔레트 23개, Pacific 팔레트 15개, Savannah 팔레트 39개가 포함되고 Aspen 팔레트는 없으며, 이는 B4에서 D4까지의 팔레트 행에 표시됩니다. 또한 총 이익 셀은 0에서 $58,800으로 업데이트됩니다.

중요한 고려 사항

Excel의 목표값 찾기 기능과 마찬가지로 Solver도 제대로 작동하려면 필요한 수식을 미리 설정해야 합니다.
솔버 매개변수 대화 상자에서 ‘옵션’ 버튼을 선택하여 문제 해결 방법에 영향을 줄 수 있습니다. 이 대화 상자에서 ‘모든 방법’, ‘GRG 비선형’ 및 ‘진화’에 대한 값을 지정할 수 있습니다.
또한 Solver를 사용하면 나중에 사용할 수 있도록 모델을 저장하고 로드할 수 있습니다. 기존 모델을 로드할 때는 해당 문제와 관련된 전체 셀 범위에 대한 참조를 입력해야 합니다.
Solver를 사용할 때는 데이터 사본을 가지고 작업하는 것이 좋습니다. Solver를 실행하면 원본 데이터가 수정되고, 변경 사항이 적용된 후에는 해당 데이터를 복구하는 것이 불가능할 수 있기 때문입니다.

원천