엑셀 해 찾기, 예측 시트, 통계 도구 사용법

스프레드시트는 대부분 "무슨 일이 있었나?"에 답해요. 그런데 다른 종류의 질문도 있어요. "지금 고를 수 있는 최선은 무엇인가?", "다음에는 어떻게 될 가능성이 높은가?" 같은 질문이죠. 해 찾기(Solver)는 정해 둔 한계 안에서 가장 좋은 입력 조합을 찾아 주고, 예측 시트(Forecast Sheet)는 시계열 데이터를 앞으로 이어서 예측해 줘요. 둘 다 여러분이 밝힌 한계와 가정만큼만 믿을 수 있어요. 그래서 이 글에서는 답을 구하는 방법과 함께, 그 답을 점검하고 설명하는 방법도 다뤄요.

해 찾기가 풀 수 있는 모델 만들기

작은 공방에서 제품 두 가지를 만들어요. 제품 A는 개당 마진이 12이고 작업 시간이 2시간 들어요. 제품 B는 개당 마진이 20이고 3시간이 들어요. 쓸 수 있는 작업 시간은 60시간이고, 제품 B는 최대 15개까지만 팔려요. 각각 몇 개씩 만들어야 할까요?

해 찾기 모델에는 세 부분이 있고, 부분마다 셀이 필요해요.

• 결정 변수: 해 찾기가 바꿀 수 있는 셀이에요. 여기서는 B2:C2의 A와 B 생산 수량이에요.
• 목표: 최대화하거나 최소화하거나 특정 값에 맞출 수식 셀 하나예요. 여기서는 D6의 총이익이에요.
• 제약 조건: 답이 지켜야 하는 한계예요. 여기서는 사용한 작업 시간(D5)이 60(E5)을 넘으면 안 되고, B의 생산 수량이 수요 한도(C7)를 넘으면 안 돼요.

fx

	A	B	C	D	E
1	항목	제품 A	제품 B	합계	한도
2	생산 수량	10	10
3	개당 마진	12	20
4	개당 작업 시간	2	3
5	작업 시간				60
6	이익
7	수요 한도		15

목표와 제약 조건은 변수 셀을 참조하는 수식이어야 해요. 사용한 작업 시간과 이익은 둘 다 "수량 곱하기 비율을 모두 더한 값"이고, 이게 바로 SUMPRODUCT가 하는 일이에요.

=SUMPRODUCT(B2:C2,B4:C4)복사

=SUMPRODUCT(B2:C2,B3:C3)복사

비율과 한도는 수식 안에 직접 쓰지 말고, 이름표를 붙인 별도의 셀에 두세요. 해 찾기도, 검토하는 사람도, 내년의 나도 그 값을 볼 수 있어야 하거든요.

해 찾기 실행하기

1. 해 찾기 추가 기능을 켜세요

   해 찾기는 엑셀에 들어 있지만 기본으로 꺼져 있어요. 파일 > 옵션 > 추가 기능으로 가서 관리 상자에서 Excel 추가 기능을 고르고 이동을 클릭하세요. 해 찾기 추가 기능(Solver Add-in) 을 체크하고 확인을 클릭하면 데이터› then 분석› then 해 찾기가 생겨요.

2. 목표를 정하세요

   데이터› then 분석› then 해 찾기를 클릭하세요. 해 찾기 매개 변수(Solver Parameters) 대화 상자에서 목표 설정을 $D$6으로 하고 최대값을 고르세요.

3. 변수 셀을 고르세요

   변수 셀 변경(By Changing Variable Cells) 에 $B$2:$C$2를 선택하세요.

4. 제약 조건을 추가하세요

   추가를 클릭하세요. 제한 조건 추가(Add Constraint) 대화 상자에서 $D$5를 입력하고 <=를 고른 다음 $E$5를 입력하고, 다음 조건을 넣기 위해 추가를 클릭하세요. $C$2 <= $C$7을 입력하고 추가를 클릭하세요. 마지막으로 $B$2:$C$2를 입력하고 목록에서 int(정수)를 고른 다음 확인을 클릭하세요. 제품을 반 개만 만들 수는 없으니까요.

5. 해법을 고르고 실행하세요

   제한되지 않는 변수를 음이 아닌 수로 설정(Make Unconstrained Variables Non-Negative) 이 체크되어 있는지 확인하세요. 그래야 해 찾기가 -5개를 만들라고 하지 않아요. 해법 선택에서 단순 LP(Simplex LP) 를 고르세요. 이 예제처럼 모든 수식이 "변수 곱하기 고정된 비율"의 합일 때 맞는 해법이에요. 해 찾기를 클릭하세요.

6. 해를 보존하세요

   해 찾기 결과(Solver Results) 대화 상자에서 해 찾기 해 보존을 고르세요. 해답 보고서를 선택하면 원래 값과 최종 값, 제약 조건마다의 상태를 정리한 시트가 추가돼요. 확인을 클릭하세요.

해 찾기가 B2를 9, C2를 14로 정해요. 사용한 작업 시간은 정확히 60이고 이익은 388이에요.

fx

	A	B	C	D	E
1	항목	제품 A	제품 B	합계	한도
2	생산 수량	9	14
3	개당 마진	12	20
4	개당 작업 시간	2	3
5	작업 시간			60	60
6	이익			388
7	수요 한도		15

B가 시간당 더 많이 벌어 주는데(20 / 3 = 6.67, A는 12 / 2 = 6) 왜 B를 15개 만들지 않을까요? B를 15개 만들면 45시간을 쓰고 15시간이 남는데, 그 시간으로는 A를 7개밖에 못 만들고 1시간이 남아요. 이익은 384예요. B를 하나 줄이면 3시간이 생기고, 그 시간으로 A를 2개 더 만들 수 있어서 정확히 60시간에 이익 388이 돼요. 정수 제약 조건을 빼면 해 찾기는 A 7.5개, B 15개로 390을 돌려주지만, 실제로는 만들 수 없는 답이에요.

답이 얼마나 민감한지 점검하기

최적의 답은 무엇이 그 답을 움직이는지 알 때 비로소 쓸모가 있어요. 던져야 할 질문은 "어떤 한계를 늘리는 게 가장 값어치가 있을까?"예요. 입력을 한 번에 하나씩 바꿔서 해 찾기를 다시 실행하고, 결과를 작은 표에 적어 두세요.

작업 시간이 10% 늘면 이익이 36 늘고, B의 수요가 10% 늘면 4밖에 늘지 않아요. 초과 근무를 늘릴지 고객을 더 찾을지 고민 중이라면, 이 표는 초과 근무가 먼저라고 말해 줘요.

이 일은 데이터 표(Data Table)로 대신할 수 없어요. 데이터 표는 수식을 다시 계산할 뿐, 해 찾기를 다시 실행하지는 않거든요. 대신 이런 기능이 도움이 돼요.

• 해 찾기 결과 대화 상자의 시나리오 저장은 지금의 변수 값을 시나리오 관리자(Scenario Manager)(데이터› then 예측› then 가상 분석› then 시나리오 관리자)에 저장해요. 나중에 기본안과 대안을 오가며 볼 수 있어요.
• 정수 제약 조건이 없는 모델이라면 민감도(Sensitivity) 보고서가 제약 조건마다 잠재 가격(Shadow Price) 을 보여 줘요. 한계를 한 단위 늘릴 때 목표가 얼마나 좋아지는지를 나타내는 값이에요. 정수 제약 조건이 있으면 엑셀이 이 보고서를 제공하지 않으니, 먼저 옵션에서 정수 제한 조건 무시(Ignore Integer Constraints) 를 체크하세요. 이 모델에서는 작업 시간 제약 조건의 잠재 가격이 6으로 나와요. 한 시간이 늘 때마다 이익이 6, 즉 A 반 개만큼의 마진이 늘어난다는 뜻이에요. 이 값은 소수 답을 기준으로 한 것이니 참고만 하고, 위의 표처럼 다시 실행해서 확인하세요.

예측 시트로 시계열 예측하기

두 번째 도구는 앞을 내다봐요. 아래 시트에는 열 달 치 매출이 있어요. 7번째 달에는 일회성 대량 주문이 들어 있어요.

fx

	A	B	C	D	E
1	월	매출	포함	지표	결과
2	1	120	예	평균(전체)
3	2	126	예	중앙값(전체)
4	3	131	예	평균(포함한 달만)
5	4	136	예
6	5	143	예
7	6	148	예
8	7	385	아니요
9	8	158	예
10	9	166	예
11	10	177	예

1. 시간 축과 값을 선택하세요

   A1:B11을 선택하세요. 시간 축(A열)은 날짜나 숫자여야 하고, 값 사이의 간격이 일정해야 해요. 매일, 매달, 또는 여기처럼 1, 2, 3이요.

2. 예측 시트를 여세요

   데이터› then 예측› then 예측 시트를 선택하세요. 예측 워크시트 만들기(Create Forecast Worksheet) 대화 상자에 미리 보기 차트가 나와요.

3. 예측 종료와 옵션을 정하세요

   세 달 뒤까지 예측하도록 예측 종료를 13으로 정하세요. 옵션을 클릭하면 나머지 설정이 보여요. 신뢰 구간(기본값 95%), 계절성(직접 정하지 않으면 자동으로 찾아요), 빠진 값을 채우는 방법과 중복 값을 집계하는 방법이에요.

4. 예측을 만드세요

   만들기를 클릭하세요. 엑셀이 표와 차트가 들어 있는 새 워크시트를 추가해요. 표에는 원래 데이터와 함께 예측값, 신뢰 하한, 신뢰 상한 열이 있고, 모두 FORECAST.ETS와 FORECAST.ETS.CONFINT 수식으로 계산되어 있어서 직접 열어 볼 수 있어요.

선 하나만 보지 말고 띠 전체를 읽으세요. 95% 신뢰 구간은 과거의 패턴이 이어진다면 실제 값이 100번 중 약 95번은 상한과 하한 사이에 들어온다는 뜻이에요. 띠가 넓으면 과거 데이터가 들쭉날쭉하다는 뜻이니 예측값 하나에 큰 무게를 두면 안 돼요. 7번째 달 같은 이상값은 측정된 변동을 키우기 때문에, 나머지 달만 있을 때보다 띠가 넓어진다고 보면 돼요.

예측 시트는 지수 평활법을 써서 계절 패턴도 따라갈 수 있어요. 다만 미래가 과거처럼 움직인다고 가정해요. 가격 변경, 새 경쟁자, 큰 고객의 이탈 같은 일은 전혀 알지 못해요.

선형 추세를 구하고 신중하게 읽기

직선 추세를 원한다면 회귀 함수를 직접 쓰세요. 함수마다 값(known_y's)을 먼저, 시간 축(known_x's)을 두 번째로 받아요.

=FORECAST.LINEAR(11,B2:B11,A2:A11)복사

=SLOPE(B2:B11,A2:A11)은 약 10.18(한 달에 늘어나는 양), =INTERCEPT(B2:B11,A2:A11)은 113, =RSQ(B2:B11,A2:A11)은 약 0.16을 돌려줘요. R-제곱은 0부터 1 사이 값이고, 직선이 변동을 얼마나 설명하는지 보여 줘요. 0.16이면 직선이 설명하는 부분이 아주 적다는 뜻이에요. 7번째 달의 급등이 직선을 위로 끌어올리고 있어요.

같은 직선을 차트에서도 볼 수 있어요. 삽입› then 차트에서 A1:B11로 분산형 차트를 만들고, 차트를 클릭한 다음 차트 요소 단추(+)에서 추세선(Trendline) 을 가리키고 기타 옵션을 고르세요. 추세선 서식 창에서 선형을 고르고 수식을 차트에 표시와 R-제곱 값을 차트에 표시를 체크하세요. 숫자가 SLOPE, INTERCEPT, RSQ와 같아요.

추세에는 늘 두 가지 한계가 있어요.

• 너무 멀리 늘려 잡지 마세요. 1~10번째 달에 맞춘 직선은 11번째 달에 대해서는 무언가를 말해 주지만, 24번째 달에 대해서는 훨씬 적게 말해 줘요. 성장이 계속된다는 근거는 데이터 어디에도 없어요.
• 관계는 이유가 아니에요. 추세는 시간이 지나며 매출이 늘었다는 것을 보여 줄 뿐, 왜 늘었는지는 알려 주지 않아요.

이상값을 어떻게 다룰지 정하기

특이한 값 하나가 평균이나 추세를 크게 끌어당길 수 있어요. 요약 통계를 비교해 보세요.

중앙값은 거의 움직이지 않아요. 극단적인 값이 나머지와 얼마나 떨어져 있는지는 따지지 않기 때문이에요. 반면 평균과 추세는 크게 움직여요. 그렇다고 7번째 달을 빼는 게 늘 옳은 건 아니에요. 대량 주문이 해마다 들어온다면 그것도 사업의 일부이고, 빼 버리면 실제 매출을 감추게 돼요.

행을 지우지 말고, 선택한 내용이 보이게 하세요. 포함 열(C)을 추가하고 수식이 그 열을 보게 하세요.

=AVERAGEIFS(B2:B11,C2:C11,"예")복사

=FORECAST.LINEAR(11,FILTER(B2:B11,C2:C11="예"),FILTER(A2:A11,C2:C11="예"))복사

모델을 읽는 사람은 어느 달이 빠졌는지 볼 수 있고, 그 달을 다시 예로 바꾸면 모든 결과가 함께 바뀌는 것도 볼 수 있어요. 표 옆에 메모로 이유를 적어 두세요.

통계를 빠르게 훑어보고 싶다면 분석 도구(Analysis ToolPak) 추가 기능을 해 찾기와 같은 방법으로 켜세요. 데이터› then 분석› then 데이터 분석이 생겨요. 여기서 기술 통계법(Descriptive Statistics) 도구를 B1:B11에 대해 첫째 행 이름표 사용과 요약 통계량을 체크하고 실행하면 평균(169), 중앙값(145.5), 표준 편차(약 78.0), 최솟값, 최댓값 등이 나와요. 회귀 분석 도구는 회귀 분석 결과 표 전체를 만들어 줘요. 분석 도구는 수식이 아니라 값만 써 넣기 때문에, 데이터가 바뀌면 다시 실행해야 해요.

모델이 할 수 없는 일을 밝히기

해 찾기의 답이나 예측을 공유하기 전에, 결과 옆에 한계를 쉬운 말로 적어 두세요.

• 무엇을 가정하나요. 생산 조합은 개당 마진과 작업 시간이 고정되어 있다고 가정하고, 예측은 다음 달이 지난 열 달과 비슷하다고 가정해요.
• 무엇이 모델을 무너뜨리나요. 가격 변경, 기계 고장, 새 계약, 또 다른 대량 주문 같은 일이에요.
• 얼마나 확실한가요. 최선의 숫자 하나만 말하지 말고 신뢰 구간이나 민감도 표도 함께 보여 주세요.

직접 말하면 한 문장이면 되지만, 결정을 내린 뒤에 다른 사람이 발견하면 훨씬 큰 대가를 치러요.

자주 하는 실수

• 목표 수식 안에 숫자를 직접 쓰기. D6이 =12*B2+20*C2여도 해 찾기는 돌아가지만, 아무도 마진을 보거나 바꿀 수 없어요. 비율은 셀에 두세요.
• 음수 불가나 정수 제약 조건 빠뜨리기. 그러면 해 찾기가 현실에서는 말이 안 되는 음수나 소수 수량을 돌려줄 수 있어요.
• 선형 모델에 GRG 비선형이나 Evolutionary 고르기. 모델이 선형이면 단순 LP가 더 빠르고 진짜 최선의 답을 찾아요. 변수 셀 두 개를 곱하는 것처럼 매끄러운 비선형 모델에는 GRG 비선형을, IF나 VLOOKUP 같은 수식 때문에 결과가 뚝뚝 끊기는 모델에는 Evolutionary를 쓰세요.
• 예측값 하나만 믿기. 예측 시트의 선은 범위의 가운데일 뿐이에요. 상한과 하한도 함께 보고하세요.
• 이상값을 말없이 지우기. 열에 표시하고, 왜 그렇게 정했는지 설명하고, 원래 데이터는 남겨 두세요.

연습

연습

최적화하고, 흔들어 보고, 예측하기

1. 새 통합 문서에 위의 공방 모델을 만들고 D5와 D6에 수식을 넣으세요.
2. 세 가지 제약 조건으로 이익을 최대화하도록 해 찾기를 실행하세요. A 9개, B 14개, 이익 388이 나오는지 확인하세요.
3. 작업 시간 한도를 66으로 바꿔 다시 실행하고 결과를 적어 두세요. 그다음 60으로 되돌리고 B 수요 한도를 16.5로 바꿔 실행하세요. 어느 한계를 늘리는 게 더 값어치 있나요?
4. 열 달 치 매출을 입력하고 13번째 달까지 예측 시트를 만든 다음, 신뢰 구간이 얼마나 넓은지 적어 두세요.
5. 포함 열을 추가하고, 7번째 달을 넣었을 때와 뺐을 때의 11번째 달 FORECAST.LINEAR 값을 비교하세요.
6. 그다음 내 업무에서 해 찾기 문제를 하나 세워 보세요. 결정 변수 두 개 이상과 실제 제약 조건 두 개 이상이 필요해요. 제약 조건 하나가 10% 느슨해지면 어떻게 되는지 한 줄짜리 점검도 추가하세요.

여기서 수식을 직접 입력해 보세요. 먼저 모델의 작업 시간과 이익 셀이에요.

직접 해 보기2개 중 0개 완료

1. 1.D5에 사용한 작업 시간을 계산해 보세요. 제품마다 생산 수량에 개당 작업 시간을 곱해서 더하면 돼요.힌트 보기
2. 2.D6에 총이익을 계산해 보세요. 두 제품의 생산 수량에 개당 마진을 곱해서 더해요. 해 찾기가 최대로 만들 셀이에요.힌트 보기

D5fx수식 입력 셀 D5Enter ↵

점선으로 표시된 셀을 클릭하고, 입력줄에 수식을 쓴 다음 Enter를 누르세요.

	A	B	C	D	E
1	항목	제품 A	제품 B	합계	한도
2	생산 수량	10	10
3	개당 마진	12	20
4	개당 작업 시간	2	3
5	작업 시간				60
6	이익
7	수요 한도		15

다음은 이상값 비교예요.

직접 해 보기3개 중 0개 완료

1. 1.E2에 열 달 전체의 월평균 매출을 계산해 보세요.힌트 보기
2. 2.E3에 같은 열 달의 중앙값을 구해 보세요. 평균과 비교해 보세요.힌트 보기
3. 3.E4에 C열이 예인 달만 평균을 내 보세요. 7번째 달의 일회성 주문은 빠져요.힌트 보기

E2fx수식 입력 셀 E2Enter ↵

점선으로 표시된 셀을 클릭하고, 입력줄에 수식을 쓴 다음 Enter를 누르세요.

	A	B	C	D	E
1	월	매출	포함	지표	결과
2	1	120	예	평균(전체)
3	2	126	예	중앙값(전체)
4	3	131	예	평균(포함한 달만)
5	4	136	예
6	5	143	예
7	6	148	예
8	7	385	아니요
9	8	158	예
10	9	166	예
11	10	177	예

실력 점검

• 해 찾기에 쓸 결정 변수, 제약 조건, 목표를 설정할 수 있나요?
• 한계 하나가 느슨해질 때 답이 어떻게 바뀌는지 보여 주는 작은 민감도 표를 만들 수 있나요?
• 예측 시트를 만들고 신뢰 구간이 무슨 뜻인지 설명할 수 있나요?
• 이상값이 평균과 추세를 어떻게 바꾸는지 보여 주고, 내린 선택을 모델에 기록할 수 있나요?
• 다른 사람이 찾아내기 전에 내 모델의 한계 하나를 먼저 말할 수 있나요?