데이터 분석의 궁극적인 목표 중 하나는 바로 미래를 예측하고, 과거 데이터에서 추세(Trend)를 찾아내어 정보에 기반한 의사결정을 내리는 것입니다. 데이터를 요약하고, 조건을 부여하며, 분포와 관계를 분석하는 방법을 익혔다면, 이제는 그 지식을 바탕으로 미래를 엿보는 통계 예측의 세계로 들어갈 차례입니다.
추세 및 예측 분석에 사용되는 엑셀 함수들을 집중적으로 다룰 예정입니다.
왜 추세 및 예측 함수를 알아야 할까요?
비즈니스에서 미래를 예측하는 능력은 매우 중요합니다. "다음 분기 판매량은 어떻게 될까?", "내년 예산을 어떻게 수립해야 할까?", "새로운 마케팅 전략이 어떤 성과를 가져올까?"와 같은 질문들은 모두 예측을 필요로 합니다.
엑셀의 추세 및 예측 함수들은 이러한 질문에 답하기 위한 강력한 도구를 제공합니다.
과거 데이터를 분석하여 숨겨진 패턴을 발견하고, 이를 통해 합리적인 미래 전망을 가능하게 합니다.
이는 재고 관리, 예산 책정, 마케팅 전략 수립 등 다양한 비즈니스 영역에서 필수적인 역량입니다.
주요 함수들
다음 함수들을 실무 예제와 함께 자세히 살펴보겠습니다.
- FORECAST.LINEAR, FORECAST.ETS: 선형 추세 및 계절성 포함 예측 (최신 버전 함수)
- TREND: 선형 추세선을 기반으로 미래 값 배열 예측
- GROWTH: 지수 성장 추세를 기반으로 미래 값 예측
- SLOPE, INTERCEPT: 선형 회귀선의 기울기와 Y절편 계산
- RSQ: 선형 회귀 분석의 결정 계수 계산
실무 예제로 배우는 엑셀 함수 마스터하기
각 함수를 실제 비즈니스 시나리오에 적용하여 데이터 기반의 의사결정을 지원하는 방법을 보여드리겠습니다.
1. FORECAST.LINEAR, FORECAST.ETS: 미래 예측의 최전선
FORECAST.LINEAR는 가장 기본적인 선형 예측 함수로, 기존 X 값과 Y 값을 기반으로 새로운 X 값에 대한 Y 값을 예측합니다. FORECAST.ETS는 엑셀 2016부터 도입된 강력한 예측 함수로, 계절성(Seasonality) 및 데이터 불규칙성(Irregularity)을 고려하여 예측 정확도를 높인 시계열 예측 기능입니다.
- FORECAST.LINEAR 기본 형식: =FORECAST.LINEAR(예측할_X, 알려진_Y, 알려진_X)
- 예측할_X: 예측하려는 특정 X 값
- 알려진_Y: 기존 데이터의 Y 값 (종속 변수) 범위
- 알려진_X: 기존 데이터의 X 값 (독립 변수) 범위
- FORECAST.ETS 기본 형식: =FORECAST.ETS(예측할_날짜, 값, 시간표, [계절성], [데이터_완성], [집계])
- 예측할_날짜: 예측하려는 미래 날짜
- 값: 과거 값 데이터 범위
- 시간표: 과거 값에 해당하는 날짜/시간 데이터 범위
- 계절성 (선택): 1(자동 감지), 0(계절성 없음), 또는 계절성 길이 (예: 월별 데이터의 1년 주기는 12)
- 나머지 인수는 복잡하므로 여기서는 생략하고 기본 사용법에 집중합니다.
실무 예제: 우리 회사의 월별 온라인 광고 지출액(B2:B7)과 웹사이트 방문자 수(C2:C7) 데이터가 있다고 가정해봅시다.
| 월 | 광고비(만원) | 방문자수(명) |
| 1월 | 500 | 50,000 |
| 2월 | 550 | 53,000 |
| 3월 | 600 | 58,000 |
| 4월 | 650 | 61,000 |
| 5월 | 700 | 65,000 |
| 6월 | 750 | 68,000 |
예제 목표:
- 광고비를 800만 원으로 늘릴 경우 예상 방문자 수를 선형적으로 예측하세요 (FORECAST.LINEAR).
- (별도 예시) 월별 판매량 데이터에 계절성이 있을 경우, 특정 미래 시점의 판매량을 예측하세요 (FORECAST.ETS).
해결 방법:
- 광고비 800만원일 때 방문자 수 예측 (E2 셀): =FORECAST.LINEAR(800, C2:C7, B2:B7) 결과: 약 73,333명
- FORECAST.ETS 예시 (새로운 데이터셋 가정: 1월부터 12월까지의 월별 아이스크림 판매량, 계절성 12개월 주기)
- 알려진 판매량: Sales_Data 범위
- 알려진 날짜: Date_Data 범위
- 예측할 날짜: 2025-01-01 =FORECAST.ETS("2025-01-01", Sales_Data, Date_Data, 12) 결과: (특정 예측값)
최상급 실무 활용: FORECAST.LINEAR는 간단한 선형 관계 예측에 유용합니다.
예를 들어, 투자 비용 증가에 따른 예상 수익 예측, 생산량 증가에 따른 원자재 소비 예측 등입니다. FORECAST.ETS는 시간 기반 데이터(시계열 데이터)에서 복잡한 패턴(계절성, 추세, 불규idity)을 반영하여 예측 정확도를 높일 때 사용됩니다.
월별 매출, 주간 방문자 수, 일별 생산량 등 미래 수요 예측, 재고 관리, 인력 계획 등에 매우 강력한 도구입니다.
2. TREND: 선형 추세에 따른 값 배열 예측
TREND 함수는 선형 회귀를 사용하여 알려진 데이터 점에 가장 적합한 직선(추세선)을 찾고, 이 추세선을 기반으로 새로운 X 값에 대한 Y 값(또는 알려진 X 값에 대한 예측 Y 값)을 배열로 반환합니다.
- 기본 형식: =TREND(알려진_Y, [알려진_X], [새_X], [상수])
- 알려진_Y: 기존 데이터의 Y 값 (종속 변수) 범위
- 알려진_X (선택): 기존 데이터의 X 값 (독립 변수) 범위. 생략 시 {1, 2, 3, ...}으로 자동 생성.
- 새_X (선택): 예측하려는 새로운 X 값. 이 값이 없으면 알려진_X와 동일한 범위의 예측값을 반환.
- 상수 (선택): TRUE(상수 계산), FALSE(상수를 0으로 설정). 생략 시 TRUE.
실무 예제: 위 월별 광고비 지출과 웹사이트 방문자 수 데이터(B2:C7)를 사용하여:
예제 목표:
- 기존 광고비 데이터(B2:B7)에 대해 추세선에 따른 예상 방문자 수를 계산하세요.
- 추가적으로 광고비가 800, 850, 900만 원일 경우 예상 방문자 수를 예측하세요.
해결 방법:
- 기존 광고비에 대한 추세 예측 (D2:D7 셀을 선택 후 배열 수식으로 입력): =TREND(C2:C7, B2:B7) 결과: 50000, 54000, 58000, 62000, 66000, 70000 (근사치)
- 새로운 광고비에 대한 추세 예측 (E8:E10 셀에 새로운 광고비 입력 후, F8:F10 셀 선택 후 배열 수식 입력):
- E8:E10에 800, 850, 900 입력
- F8 셀에 =TREND(C2:C7, B2:B7, E8:E10) 입력 후 Ctrl + Shift + Enter 결과: 약 72000, 76000, 80000
최상급 실무 활용: TREND 함수는 특히 예측 시나리오를 만들 때 유용합니다. 여러 개의 미래 X 값에 대한 Y 값을 한 번에 계산할 수 있어, 다양한 마케팅 투자 수준에 따른 예상 매출액을 시뮬레이션하거나, 시간 경과에 따른 자산 가치 변화를 예측하는 데 활용됩니다. 예산 수립, 투자 계획, 성과 목표 설정 등에 중요한 정보를 제공합니다.
3. GROWTH: 지수 성장 추세에 따른 값 배열 예측
GROWTH 함수는 TREND 함수와 유사하지만, 선형 추세가 아닌 지수 성장 추세를 기반으로 예측합니다.
데이터가 일정한 비율로 증가하거나 감소하는 경우(예: 인구 증가, 바이러스 확산, 복리 이자)에 적합합니다.
- 기본 형식: =GROWTH(알려진_Y, [알려진_X], [새_X], [상수])
- 인수는 TREND 함수와 동일합니다.
실무 예제: 벤처
| 연도 | 사용자수 |
| 2021 | 1,000 |
| 2022 | 1,500 |
| 2023 | 2,250 |
| 2024 | 3,375 |
예제 목표: 2025년과 2026년의 예상 사용자 수를 지수 추세에 따라 예측하세요.
해결 방법:
- C2:C5에 알려진_X (2021, 2022, 2023, 2024) 입력
- B7:B8에 새_X (2025, 2026) 입력
- C7:C8 셀을 선택 후 배열 수식으로 입력: =GROWTH(B2:B5, C2:C5, B7:B8) 결과: 5,062.5, 7,593.75 (2025, 2026년 예상 사용자 수)
최상급 실무 활용: 시장 성장률 예측, 인구 동향 분석, 바이오 기술 성장 추세 예측 등 데이터가 기하급수적으로 증가하거나 감소하는 시나리오에 특히 유용합니다. 스타트업의 사용자 성장 예측이나 복리 투자 수익률 계산에 활용하여 장기적인 계획을 수립하는 데 도움을 줍니다.
4. SLOPE, INTERCEPT: 선형 회귀 분석의 구성 요소
SLOPE 함수는 두 변수 간의 선형 회귀선 기울기를 계산하고, INTERCEPT 함수는 Y절편(X가 0일 때 Y 값)을 계산합니다.
이 두 값은 선형 회귀 모델의 핵심 구성 요소입니다.
- SLOPE 기본 형식: =SLOPE(알려진_Y, 알려진_X)
- INTERCEPT 기본 형식: =INTERCEPT(알려진_Y, 알려진_X)
실무 예제: 위 광고비 지출과 웹사이트 방문자 수 데이터(B2:C7)를 사용하여 선형 회귀선의 기울기와 Y절편을 계산하세요.
예제 목표: 광고비와 방문자 수 간의 선형 관계를 나타내는 방정식 ()의 (기울기)와 (Y절편) 값을 찾으세요.
해결 방법:
- 기울기 (SLOPE): =SLOPE(C2:C7, B2:B7) → 약 40 (광고비 1만원 증가 시 방문자 수 약 40명 증가)
- Y절편 (INTERCEPT): =INTERCEPT(C2:C7, B2:B7) → 약 30000 (광고비가 0일 때 예상 방문자 수 약 30,000명)
최상급 실무 활용: 선형 회귀 분석의 기본을 이해하고 변수 간의 양적 관계를 모델링하는 데 사용됩니다.
기울기는 독립 변수(X)가 한 단위 변할 때 종속 변수(Y)가 얼마나 변하는지 보여주는 핵심 지표입니다. Y절편은 독립 변수가 0일 때의 종속 변수 값을 나타내며, 모델의 시작점을 파악하는 데 도움을 줍니다. 비용-수익 분석, 효율성 측정 등에 유용합니다.
5. RSQ: 선형 회귀 모델의 설명력 (결정 계수)
RSQ 함수는 선형 회귀 모델의 **결정 계수()**를 계산합니다. 결정 계수는 모델이 종속 변수(Y)의 변동성을 독립 변수(X)로 얼마나 잘 설명하는지 나타내는 지표로, 0과 1 사이의 값을 가집니다. 1에 가까울수록 모델의 설명력이 높다는 것을 의미합니다.
- 기본 형식: =RSQ(알려진_Y, 알려진_X)
실무 예제: 위 광고비 지출과 웹사이트 방문자 수 데이터(B2:C7)를 사용하여 결정 계수를 계산하세요.
예제 목표: 광고비 지출이 웹사이트 방문자 수의 변동성을 얼마나 잘 설명하는지 평가하세요.
해결 방법: 결정 계수를 계산하려면 D5 셀에 다음 수식을 입력합니다. =RSQ(C2:C7, B2:B7)
이 결과로 결정 계수()는 약 0.992가 됩니다.
최상급 실무 활용: 이 매우 높은 결정 계수는 광고비 지출이 웹사이트 방문자 수의 변동성을 거의 완벽하게 설명한다는 것을 의미합니다. 즉, 광고비 지출이 웹사이트 방문자 수에 강력한 선형적 영향을 미친다고 해석할 수 있습니다.
RSQ는 회귀 모델의 유효성을 평가하고, 어떤 변수들이 목표 변수에 가장 큰 영향을 미치는지 파악하여 최적의 비즈니스 전략을 수립하는 데 필수적인 지표입니다.
추세 및 예측 분석에 활용되는 강력한 함수들(FORECAST.LINEAR/ETS, TREND, GROWTH, SLOPE, INTERCEPT, RSQ)을 살펴보았습니다.
이 함수들을 통해 여러분은 과거 데이터의 패턴을 기반으로 미래를 합리적으로 예측하고, 데이터 간의 선형적/지수적 관계를 정량적으로 분석하여 비즈니스 의사결정의 정확도를 높일 수 있게 될 것입니다.