들어가기 전에
데이터가 완벽하다면 Excel은 엄청나게 강력합니다. 하지만 알려지지 않은 변수를 해결해야 한다면 어떻게 해야 할까요? Excel의 [목표값 찾기]와 [해 찾기] 추가 기능을 사용하면 Excel이 알려지지 않은 변수를 찾는 데 필요한 힘든 작업을 대신 수행할 수 있습니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: How to Use Excel's Goal Seek and Solver to Solve for Unknown Variables
- URL: https://www.makeuseof.com/tag/excel-goal-seek-solver-unknown-variables/
※ 본문 내용과 함께 다음 영상도 참고하세요.
Excel의 "목표값 찾기"는 어떤 기능을 하나요?
Excel에 수식이 있지만 특정 결과를 얻을 수 있는 한 변수의 값을 찾아야 할 때 Goal Seek는 유용한 도구입니다. 원하는 결과를 얻기 위해 단일 변수를 조정할 수 있으므로 빠른 문제 해결 시나리오에 이상적입니다.
판매 목표 달성, 대출금 상환액 계산, 생산 수준 조정 등 다양한 시나리오에서 Goal Seek를 유용하게 사용할 수 있습니다. 목표 찾기는 모든 재무 분석에서 알려지지 않은 값을 찾는 데 도움이 됩니다.
Excel에서 목표값 찾기 사용하는 방법
[목표값 찾기]는 Excel에 내장되어 있습니다. [데이터] 탭 - [가상 분석] 메뉴에 있습니다. 간단한 예제를 통해 살펴보겠습니다. 3분기의 판매 수치와 연간 목표가 있습니다. Goal Seek를 사용하여 목표를 달성하기 위해 4분기에 어떤 수치가 필요한지 알아낼 수 있습니다.
현재 판매 총량은 114,706대입니다. 연말까지 250,000대를 판매하려면 4분기에 몇 대를 판매해야 할까요? Excel의 [목표값 찾기]가 알려줄 겁니다. 목표 찾기를 단계별로 사용하는 방법은 다음과 같습니다.
(1) [데이터] 탭 - [예측] 그룹 - [가상 분석] - [목표값 찾기]를 클릭합니다.
(2) 수식 셀: 총 매출이 포함된 A5 셀을 선택합니다.
(3) 찾는 값: 목표 값을 입력합니다. 이 경우에는 250,000입니다.
(4) 값을 바꿀 셀: 4분기 매출 값이 들어갈 셀을 선택합니다. 여기서는 D2 셀을 선택합니다.
(5) [확인] 버튼을 클릭합니다.
이 예제에서 결과는 135,294입니다. 물론, 연간 목표에서 누적 합계를 빼서 그냥 찾을 수도 있습니다. 하지만 [목표값 찾기]는 이미 데이터가 있는 셀에서도 사용할 수 있습니다. 그리고 그게 더 유용합니다.
참고로, [목표값 찾기]를 사용하면 Excel은 이전 데이터를 덮어쓰므로 데이터 사본에서 실행하는 것이 좋습니다. 또한 [목표값 찾기]를 사용하여 생성되었다는 것을 복사한 데이터에 메모하는 것이 좋습니다. 최신의 정확한 데이터와 혼동하지 않도록 해야 합니다.
[목표값 찾기]는 가장 유용한 Excel 함수 중 하나이지만, 그렇게 인상적이지 않습니다. 한 번에 하나의 셀에서만 사용할 수 있기 때문입니다. 여러 셀에서 동시에 사용하려면 훨씬 더 강력한 도구가 필요합니다. Excel에는 그러한 도구가 있습니다. [해 찾기]가 그것입니다.
Excel의 "해 찾기"는 어떤 기능을 하나요?
간단히 말해서 [해 찾기](Solver)는 스테로이드를 넣은 [목표값 찾기]와 같습니다. Excel에서 목표값 찾기를 여러 셀에 동시에 사용하는 방법이 필요하다면 바로 이겁니다. 하나의 목표 변수를 가져와 원하는 답을 얻을 때까지 여러 다른 변수를 조정합니다.
숫자의 최대값, 숫자의 최소값 또는 정확한 숫자를 풀 수 있습니다. 그리고 제약 조건 내에서 작동하므로 한 변수를 변경할 수 없거나 지정된 범위 내에서만 변할 수 있는 경우 해 찾기가 이를 고려합니다.
Excel에는 기본적으로 [해 찾기]가 없습니다. 추가 기능이기 때문에 먼저 로드해야 합니다.
(1) [파일] - [옵션] - [추가 기능]으로 이동합니다.
(2) 대화상자에서 [Excel 추가 기능]을 선택하고 [이동]을 클릭합니다.
(3) [추가 기능] 대화상자에서 [해 찾기 추가 기능]을 선택하고 [확인] 버튼을 누릅니다.
(4) [데이터] 탭 - [분석] 그룹에 [해 찾기] 명령이 표시됩니다.
Excel에서 "해 찾기" 사용하는 방법
솔버 액션에는 목적, 변수 셀, 제약 조건의 세 가지 부분이 있습니다.
- 목표: 달성하고자 하는 목표입니다. 목표는 관심 있는 최종 결과를 계산하는 단일 셀에 설정됩니다.
- 가변 셀: 솔버가 목표에 도달하기 위해 조정할 수 있는 셀입니다. 방정식에서 알 수 없는 값을 나타냅니다.
- 제약 조건: 가변 셀이 변경할 수 있는 범위를 제한하는 규칙입니다. Excel에서 논리 연산자를 사용하여 설정할 수 있습니다.
예를 들어, 임금과 근무 시간이 다른 5개의 파트타임 일자리가 있다고 가정해 보겠습니다. 특정 시간당 제약을 준수하면서 총 급여를 극대화하고 싶을 것입니다. 제약이 될 몇 가지 규칙도 있습니다.
- 어떤 일자리도 4시간 미만으로 줄어들 수 없습니다.
- 작업 4는 12시간 이상이어야 합니다.
- 5번째 작업은 11시간 이내여야 합니다.
- 총 근무 시간은 40시간이어야 합니다.
[참고] "목표값 찾기"와 마찬가지로 "해 찾기"를 사용할 때에도 데이터 사본에 사용하는 것이 좋습니다. 해 찾기는 여러 셀을 수정할 수 있으므로 더욱 그렇습니다.
이제 [해 찾기]를 열고 다음 단계를 따릅니다.
- 목표 설정: 총 급여를 계산하는 셀을 선택하여 목표를 설정합니다. 최대화, 최소화 또는 특정 값을 설정할지 선택합니다. 이 예에서는 [최대값]을 선택합니다.
- 변수 셀 변경: 해 찾기가 조정할 수 있는 변수 셀(예: 각 직업에서 일한 시간)을 선택합니다.
- 제한 조건에 종속: 작업이 4시간 미만으로 떨어지지 않도록 하거나 총 근무 시간이 40시간이 되도록 하는 것과 같은 제약 조건을 추가합니다. [추가] 버튼을 사용하여 이러한 조건을 정의합니다.
- [해 찾기] 버튼을 클릭합니다.
해결책을 찾았습니다. 이 예에서 가상 수입은 $152 증가했습니다. 그리고 모든 제약 조건이 충족되었습니다.
마치며
이제 Excel에서 알 수 없는 변수를 해결하는 기본 사항에 익숙해졌으므로 완전히 새로운 스프레드시트 계산의 세계가 열립니다. "목표값 찾기"는 일부 계산을 더 빠르게 수행하여 시간을 절약하는 데 도움이 될 수 있으며 "해 찾기"는 Excel의 계산 능력에 엄청난 힘을 더합니다.
'Excel' 카테고리의 다른 글
| Excel에서 할 일 목록 만드는 방법 (26) | 2024.11.20 |
|---|---|
| 간단한 Excel 수식으로 난수 생성하는 법 (27) | 2024.11.19 |
| 대부분 잘 모르는 재미있는 메모장 활용법 4가지 (23) | 2024.11.17 |
| 범하지 말아야 할 초보 Excel 실수 7가지 (19) | 2024.11.16 |
| Excel "함수"와 "수식"은 어떻게 다를까? (16) | 2024.11.15 |
