- 최초 작성일: 2002-08-27
- 최종 수정일: 2026-09-29
- 조회수: 26 회
- 작성자: 권현욱 (엑셀러)
- 강의 제목: 해 찾기(Solver)로 목표값에 맞는 숫자 조합 찾기
들어가기 전에
오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.
늦은 휴가를 다녀왔습니다. 예년의 휴가에 비해 이번 휴가는 아주 靜的으로 보냈습니다. 그렇다고 더운 날 집 안에만 있었던 것은 아니고, 많은 생각들을 하면서 보냈습니다. 어떤 좋은 생각들이었는지는 차차 알려드리도록 하고…
- 최신 글을 불러오는 중...
해 찾기(Solver)로 목표값에 맞는 숫자 조합 찾기
핵심 요약
여러 숫자 중 일부를 더해서 특정 목표값을 만드는 조합을 찾아야 할 때, 하나하나 계산해 보는 대신 엑셀의 '해 찾기(Solver)' 기능을 사용하면 자동으로 답을 구할 수 있습니다.
- SUMPRODUCT로 '선택 여부(0/1)'와 '숫자'를 곱한 합을 목표 셀로 설정합니다.
- '값을 바꿀 셀'을 선택 여부(0/1) 셀로 지정하고 목표값을 조건으로 겁니다.
- '해 찾기'를 실행하면 목표값을 만족하는 조합이 자동으로 표시됩니다.
질문: 조건값을 입력하면 해당 조합의 셀 색깔이 바뀌게 할 수 있을까요?
자신이 조건값을 입력하면 숫자들의 목록표에서 해당 값이 있는 셀들만 색깔이 바뀌는 방법이 있을까요? 예를 들어 A1 셀~F1 셀까지 각각 100, 50, 40, 60, 70, 30이 있을 때, 조건값을 B1 셀에 210이라고 입력했을 때 210이 만들어질 수 있는 100, 50, 60이 입력되어 있는 해당 셀의 색깔이 반전되거나 다른 색깔로 바뀌는 방법 말입니다. 업무상 꼭 필요한데 부탁드립니다.
아래와 같은 데이터가 있다고 할 때, 사용자가 210이라고 입력을 하면 어떤 셀의 값이 조합되면 구해지는지를 표시하도록 하고 싶다는 질문입니다.
| 100 | 50 | 40 | 60 | 70 | 30 |
="검색할 조건값이 "&C34&"인 경우"| 100 | 50 | 40 | 60 | 70 | 30 |
|---|---|---|---|---|---|
| 1(선택) | 1(선택) | 0 | 1(선택) | 0 | 0 |
| 조건값 | =SUMPRODUCT(B31:G31,B32:G32) |
어떻게 하면 될까요? 이런 경우 '해 찾기' 기능을 사용하면 손쉽게 해결할 수 있습니다.
(1) 데이터와 수식 준비하기
아래와 같이 데이터를 만듭니다.
| 100 | 50 | 40 | 60 | 70 | 30 |
| 조건값 | =SUMPRODUCT(B41:G41,B42:G42) |
(2) 해 찾기 메뉴 실행하기
'도구 - 해 찾기' 메뉴를 선택합니다. 만약 이 메뉴가 없다면 '도구 - 추가 기능' 메뉴에서 '해 찾기 추가 기능' 항목을 선택하여 추가 설치를 하셔야 하며, 이 경우 원본 CD가 필요할 수도 있습니다.
(3) 목표 셀과 제한 조건 지정하기
아래의 그림을 참고하여 목표 셀, 해의 조건, 값을 바꿀 셀, 제한 조건 등의 항목을 추가합니다.
(4) 해 찾기 실행하기
지정이 끝났으면 '실행' 버튼을 누릅니다. '해 찾기 결과' 대화상자가 나타나면 '구한 해로 바꾸기' 항목을 선택하고 '확인' 버튼을 클릭합니다.
(5) 결과 확인하기
아래와 같이 최적의 조합이 구해집니다.
참으로 기특하기 짝이 없는 엑셀이지요? ^^
정리 — 해 찾기로 목표값 조합 구하기
| 구성요소 | 역할 |
|---|---|
| 선택 셀(0/1) | 각 숫자를 포함(1)할지 제외(0)할지 나타내는 셀 |
SUMPRODUCT(숫자범위,선택범위) | 선택된 숫자들만 곱해서 더한 합계(목표 셀) |
| 목표 셀 | SUMPRODUCT 결과 셀을 원하는 목표값과 같아지도록 지정 |
| 값을 바꿀 셀 | 선택 셀(0/1) 범위를 지정, 제한 조건으로 0 또는 1만 허용 |
자주 묻는 질문 (FAQ)
Q1. '해 찾기' 메뉴가 보이지 않으면 어떻게 하나요?
'도구 - 추가 기능' 메뉴에서 '해 찾기 추가 기능' 항목을 체크해 설치하면 됩니다. 이 경우 엑셀 설치 원본 CD(또는 설치 파일)가 필요할 수도 있습니다.
Q2. 조합 가능한 해가 여러 개일 때 해 찾기는 어떤 해를 보여주나요?
해 찾기는 설정된 제한 조건(0 또는 1)과 목표 셀 값을 만족하는 해 하나를 찾아 보여줍니다. 여러 조합이 가능한 경우 어떤 해가 나올지는 초기값과 알고리즘에 따라 달라질 수 있습니다.
Q3. 값을 바꿀 셀의 제한 조건은 왜 0과 1로 지정하나요?
각 숫자를 '포함(1)'하거나 '제외(0)'하는 이진 선택 문제로 만들기 위해서입니다. SUMPRODUCT로 선택된(1인) 항목들의 값만 더해지도록 설계되어 있기 때문에, 값을 바꿀 셀은 0 또는 1만 가질 수 있도록 제한해야 합니다.
마치며
일일이 손으로 조합을 따져보는 문제도, SUMPRODUCT와 '해 찾기'를 조합하면 순식간에 해결됩니다. 목표값을 만족하는 조합 찾기 외에도 예산 배분, 자원 할당 등 다양한 최적화 문제에 같은 원리를 응용할 수 있습니다.
