엑셀 전문가 · 대화형 레슨 12개 중 7번째
최적화, 예측과 통계 도구
어떤 질문은 "무슨 일이 있었나"가 아니라 "지금 고를 수 있는 최선은 무엇인가"예요. 해 찾기(Solver)는 실제 제약 조건 안에서 가장 좋은 조합을 찾아 주고, 예측 시트(Forecast Sheet)는 앞으로 일어날 가능성이 높은 흐름을 보여 줘요. 둘 다 여러분이 한계를 얼마나 솔직하게 밝히느냐에 따라 신뢰도가 달라져요.
화면 기준: Windows용 Microsoft 365 Excel. 연습은 어느 브라우저에서나 할 수 있어요.
글로 보는 튜토리얼엑셀 해 찾기, 예측 시트, 통계 도구 사용법실력 점검
먼저 해 보세요:
- 해 찾기에 사용할 결정 변수, 제약 조건, 목표를 설정해요.
- 핵심 가정 하나에 대한 시나리오 표나 민감도 표를 만들어요.
- 예측 시트를 만들고 그것이 실제로 무엇을 예측하는지 설명해요.
- 직접 만든 모델의 한계 하나를, 다른 사람이 찾아내기 전에 먼저 소리 내어 설명해요.
모두 익숙한가요? E8 — 대시보드 애플리케이션 설계 레슨으로 건너뛰세요.
개념 익히기
이번 레슨에서 배울 내용
- 해 찾기 설정
- 데이터 > 해 찾기: 목표 셀, 해 찾기가 바꿀 수 있는 결정 변수 셀, 그리고 최대값·최소값·지정값 중 하나를 고르세요.
- 결정 변수와 제약 조건
- 변수는 해 찾기가 자유롭게 정할 수 있는 값(생산할 수량)이고, 제약 조건은 넘을 수 없는 한계(생산 능력, 수요, 음수 불가)예요.
- 시나리오와 민감도 설계
- 시나리오 관리자(Scenario Manager)는 이름을 붙인 "가상" 가정 묶음을 저장해 두고 바꿔 가며 볼 수 있게 해 줘요. 민감도 표는 가정 하나가 일정 범위에서 움직일 때 결과가 어떻게 달라지는지 보여 줘요.
- 예측 시트
- 데이터 > 예측 시트는 시계열 데이터에서 미래 값을 자동으로 예측하고 신뢰 구간까지 함께 보여 줘요. 숫자 하나만 보지 말고 이 구간도 꼭 읽어 보세요.
- 회귀 분석 감각
- 추세선(trendline)이나
TREND()는 과거 데이터에 직선을 맞춰요. 관계를 찾아내는 데는 유용하지만, 데이터가 실제로 다루는 범위를 넘어서까지 믿으면 위험해요. - 이상값 처리
- 튀는 데이터 하나가 평균이나 추세선을 엉뚱한 방향으로 끌고 갈 수 있어요. 조용히 넣거나 빼지 말고, 제외할지 말지 신중하게 정한 뒤 그 사실을 밝히세요.
- 모델의 한계 설명하기
- 모든 예측과 최적화는 앞으로도 상황이 과거와 크게 다르지 않다고 가정해요. 다른 사람이 호되게 겪고 나서 알게 되기 전에, 무엇이 모델을 무너뜨릴 수 있는지 먼저 말해 두세요.
엑셀에서 찾는 위치
데이터›분석›해 찾기
자동 저장 Book1.xlsx · 저장됨 검색–□✕
파일홈삽입페이지 레이아웃수식데이터검토보기자동화도움말
정렬
필터
지우기
다시 적용
고급
정렬 및 필터
가상 분석
예측 시트
예측
그룹
그룹 해제
부분합
개요
해 찾기
데이터 분석
분석
| A | B | C | D | E | F | G | H | ||
|---|---|---|---|---|---|---|---|---|---|
| 1 | 제품 | 수량 | 단위 이익 | 작업 시간 | |||||
| 2 | 제품 A | 120 | 14 | 2 | |||||
| 3 | 제품 B | 80 | 22 | 3 | |||||
| 4 | |||||||||
| 5 | 사용 시간 | 480 | |||||||
| 6 | 작업 한도 | 480 | |||||||
| 7 | |||||||||
| 8 | 총이익 | 3,440 | |||||||
| 9 | |||||||||
| 10 | |||||||||
| 11 | |||||||||
| 12 | |||||||||
| 13 |
해 찾기 매개 변수? ✕
$D$8
대상: ● 최대값 ○ 최소값 ○ 지정값
$B$2:$B$3
$D$5 <= $D$6
단순 LP▾
해 찾기닫기
◀ ▶Sheet1+
준비접근성: 계속 진행 가능▦ ▤ ▥ — + 100%
옆으로 밀어 창 전체를 볼 수 있어요.
- 1데이터 › 분석 › 해 찾기. 없으면 파일 › 옵션 › 추가 기능 › Excel 추가 기능 › 해 찾기 추가 기능.
- 2목표 셀은 최대화하거나 최소화할 수식이에요.
- 3변수 셀은 해 찾기가 바꿀 수 있는 결정값이에요.
- 4제한 조건이 답을 현실적으로 만들어요.
- 5예측 시트는 신뢰 구간과 함께 시계열을 예측해요.
Microsoft 365용 Excel(Windows) 화면을 재현한 그림입니다. Mac, 웹용 Excel 또는 좁은 창에서는 리본 배치가 조금 다를 수 있습니다.
따라 만들기
제품은 두 가지, 작업 시간도 수요도 한정되어 있어요. 직접 조합을 정해 본 다음, 해 찾기가 이익을 최대로 만드는 조합을 찾게 해 보세요.
혼자 응용하기
실제 제약 조건 최적화하기
실제 해 찾기 문제를 세워 보세요. 최대화하거나 최소화할 목표 하나, 결정 변수 두 개 이상, 실제 제약 조건 두 개 이상이 필요해요. 문제를 푼 다음, 제약 조건 하나가 10% 느슨해지면 답이 어떻게 바뀌는지 보여 주는 한 줄짜리 민감도 점검을 만드세요.
이 레슨에서 틀린 부분을 찾았거나 제안이 있나요? 의견 보내기