엑셀 해 찾기(Solver) 기능이란?
해 찾기는 주어진 목표(특정 셀의 값을 최대화, 최소화, 또는 특정 값으로 만들기)를 달성하기 위해, 여러 개의 입력 값(변수 셀)을 동시에 변경하면서 다양한 제약 조건을 만족하는 최적의 해를 찾는 기능입니다.
딱 하나의 변수만 변경할 수 있는 목표값 찾기와 달리, 여러 개의 변수를 동시에 조정할 수 있습니다.
해 찾기의 핵심 기능 요약 및 추가 설치
| 항목 | 설명 |
| 기능 목적 | 수식의 결과값을 최대화, 최소화, 또는 특정값으로 만들기 위해 입력 셀들을 자동 조정 |
| 조정 가능한 셀 | 여러 개 가능 |
| 제약 조건 | 숫자 범위, 정수 여부, 논리 조건 등 설정 가능 |
| 목표 셀 설정 | 최대/최소/지정값 중 하나 선택 가능 |
| 활용 사례 | 수익 극대화, 비용 최소화, 자원 배분 최적화 등 |
| 설치 여부 | Solver 추가기능 설치 필요 (한 번만 설정하면 됨) |
▶ 해 찾기 기능 활성화
파일 탭 > 옵션 > 왼쪽 메뉴 아래의 "추가 기능" > 아래쪽에 있는 "Excel 추가 기능" 선택된 상태에서 이동(G) 버튼
> 추가 기능 창 > "해 찾기 추가 기능" 선택 후 확인 > 엑셀 리본 메뉴 데이터 탭 > 오른쪽에 분석 탭 생성 > 해 찾기 메뉴
엑셀 해 찾기 구성 요소
● 목표 설정(Set Objective) : 목표 값이 될 셀을 지정합니다. 이 셀은 보통 수식이 입력되어 있어야 해요.
이 값을 최대화(Max), 최소화(Min), 또는 특정 값(Value Of)으로 만들 것인지 선택합니다.
● 변수 셀 변경(By Changing Variable Cells) : 목표를 달성하기 위해 해 찾기가 값을 변경할 하나 이상의 셀 범위를 지정합니다.
이 셀들은 수식이 아니라 직접 입력된 값이어야 합니다.
● 제약 조건(Subject to the Constraints) : 변수 셀의 값이 만족해야 하는 조건들을 추가합니다.
예를 들어, 특정 값보다 크거나 같아야 한다거나, 정수여야 한다거나 하는 조건을 설정할 수 있습니다.
● 해법 선택 : GRG, 선형, Evolutionary 등 문제 유형에 따라 선택합니다.
엑셀 해 찾기 활용 예시
예시 : 생산 계획 최적화
가구 공장에서 책상과 의자 두 가지 제품을 생산한다
각 제품 생산에는 노동시간과 목재가 필요하며, 사용할 수 있는 총 노동 시간과 목재 양은 제한되어 있다
공장의 목표는 사용 가능한 자원을 가지고 총이익을 극대화하는 것이다.
데이터 탭 > 해 찾기 >
"해 찾기" 확인
변수 셀에 해 값이 입력됩니다
"해 찾기 해 보존" 은 원자료를 구한 해로 바꾸는 것이고
"원래 값 복원" 은 초기값을 유지하고 구한 결과만 잠시 보여주고 "확인"하면 결과가 사라진다
"원래 값 복원 / 보고서 (해답) / 개요 보고서 선택 후 확인 → 새 시트 "해답 보고서1"이 생성된다
엑셀 해 찾기 유의사항
해 찾기를 효과적으로 사용하고 오류를 줄이기 위해 다음과 같은 점들을 고려하시면 좋습니다.
1. 목표 셀은 반드시 '수식'이어야 합니다.
해 찾기는 목표 셀의 수식 결과를 보고 변수 셀 값을 조정해 나갑니다.
따라서 목표 설정 셀에는 변수 셀의 값이 변경될 때 함께 값이 바뀌는 수식이 들어가 있어야 합니다.
수식이 아닌 고정된 값이면 해 찾기가 작동하지 않아요.
2. 변수 셀과 목표 셀 간의 연결 확인이 중요합니다.
해 찾기가 값을 변경할 변수 셀과, 목표로 삼는 목표 셀이 수식을 통해 제대로 연결되어 있는지 확인해야 합니다.
변수 셀의 변화가 목표 셀의 값에 영향을 주지 않으면 해 찾기가 목표를 달성할 수 없습니다.
3. 제약 조건 설정에 신중해야 합니다.
● 실현 불가능한 제약 조건: 모든 제약 조건을 동시에 만족하는 해가 존재하지 않을 수 있습니다.
예를 들어 'A셀은 10 이상'이면서 동시에 'A셀은 5 이하'라는 제약 조건을 걸면 해를 찾을 수 없겠죠.
제약 조건들이 서로 모순되지는 않는지 확인해야 합니다.
● 정확한 제약 조건 입력: '크거나 같음(>=)', '작거나 같음(<=)', '같음(=)', '정수(int)', '이진(bin: 0 또는 1)' 등의 조건을 정확하게 설정해야 합니다.
특히 수량이 정수여야 하는 경우 'int' 조건을 빠뜨리지 않도록 주의해야 합니다.
● 제약 조건 셀도 수식으로 연결: 제약 조건에 사용되는 셀(예: 총 노동 시간 사용량) 역시 변수 셀에 따라 값이 바뀌는 수식으로 연결되어 있어야 합니다.
4. 초기 변수 값 설정이 결과에 영향을 줄 수 있습니다.
특히 비선형 문제의 경우, 변수 셀에 처음에 어떤 값을 입력해 두느냐에 따라 해 찾기가 찾는 해(로컬 최적해 vs 전역 최적해)가 달라질 수 있습니다. 가능한 현실적이거나 의미 있는 초기 값을 설정해 보는 것이 좋습니다.
5. 알맞은 '해법'(Solving Method) 선택이 필요합니다.
해 찾기는 문제의 종류에 따라 세 가지 해법을 제공합니다.
● 심플렉스 LP (Simplex LP): 선형 문제에 사용합니다. 가장 빠르고 정확하게 최적해를 찾습니다.
● GRG 비선형 (GRG Nonlinear): 비선형 문제에 사용합니다. 로컬 최적해를 찾기 쉽습니다.
● 진화 (Evolutionary): 복잡한 비선형 문제나 전역 최적해를 찾고 싶을 때 사용합니다.
다른 방법보다 시간이 오래 걸릴 수 있습니다.
문제가 어떤 성격을 가지는지 파악하고 적절한 해법을 선택해야 정확한 결과를 얻을 수 있습니다.
6. '해를 찾을 수 없음' 결과가 나올 수 있습니다.
위에 언급된 것처럼 제약 조건이 너무 많거나 모순되거나, 또는 문제가 복잡하여 해 찾기 알고리즘이 제한된 시간 안에 해를 찾지 못하는 경우 '해를 찾을 수 없음'과 같은 메시지가 나타날 수 있습니다.
이때는 제약 조건을 검토하거나 해법을 변경해 보는 등의 시도가 필요합니다.
7. 민감도 및 한계 보고서 활용을 고려해 보세요.
해 찾기가 완료된 후 나타나는 결과 창에서 민감도 보고서나 한계 보고서 등을 요청할 수 있습니다.
이 보고서들은 최적해가 제약 조건의 변화에 얼마나 민감한지 등을 알려주어 결과를 더 깊이 이해하는 데 도움을 줍니다.