엑셀은 기본적인 머신러닝 작업을 수행하기에 의외로 강력한 도구입니다. 전문 머신러닝 플랫폼은 아니지만, 내장 함수와 솔버(Solver)를 활용하면 선형 회귀, 로지스틱 회귀 같은 핵심 ML 개념을 효과적으로 구현하고 시연할 수 있습니다.
이 튜토리얼에서는 엑셀의 솔버와 수식을 이용해 경량 머신러닝 모델을 직접 구축하는 방법을 단계별로 소개합니다.
- 선형 회귀(Linear Regression): 연속형 값을 예측합니다. (매출액, 주택 가격, 시험 점수 등)
- 로지스틱 회귀(Logistic Regression): 예/아니오 형태의 결과를 예측합니다. (고객 구매 여부, 대출 연체, 의료 진단, 합격/불합격 등)
시작하기 전 준비 사항
- Microsoft Excel (2016 이상 권장)
- 솔버 추가 기능(Solver Add-in) 활성화
- 파일 탭 >> 옵션 선택 >> 추가 기능 선택 >> Excel 추가 기능 선택
- 이동(Go) 클릭
- Solver Add-in 체크
- 확인 클릭
- 회귀 분석에 대한 기본적인 개념 이해
1부: 선형 회귀 모델
선형 회귀는 데이터 포인트를 관통하는 최적의 직선을 찾아 연속형 수치를 예측하는 기법입니다. 여기서는 간단한 비즈니스 시나리오, 즉 광고비(X)가 매출(Y)을 예측하는 모델을 만들어 보겠습니다. 각 데이터 포인트는 한 달간의 사업 데이터를 나타냅니다.
1단계: 샘플 데이터 준비
입력값(광고비, 천 달러 단위)과 출력값(매출액, 천 달러 단위) 사이에 뚜렷한 선형 관계가 나타나는 현실적인 데이터셋을 만듭니다.
각 행은 한 달의 사업 데이터입니다. 광고비가 증가하면 매출도 증가하지만 완벽하게 비례하지는 않습니다. 약간의 무작위성이 존재하는데, 이것이야말로 실제 데이터의 모습입니다.
2단계: 예측 수식 만들기
모델이 최적의 직선을 찾기 위해 조절할 수학적 '노브'를 설정합니다. 선형 회귀에는 두 개의 파라미터가 필요합니다.
- 절편(b0): 직선이 Y축과 만나는 지점입니다. (광고비가 0일 때의 기본 매출)
- 기울기(b1): 광고비가 1,000달러 증가할 때 매출이 얼마나 증가하는지를 나타냅니다.
모델 파라미터를 별도의 셀에 설정합니다.
모델 파라미터:
Predicted Y = b0 + b1 * X
- 절편(b0): 초기값 0
- 기울기(b1): 초기값 1
이 선형 방정식을 사용해 광고비를 기반으로 매출을 예측합니다. 이것이 모델의 핵심으로, 광고 금액을 입력받아 예상 매출을 산출합니다.
수학적 의미:
- b0 = 0.5, b1 = 2라면, 광고비 3천 달러 투입 시 예측 매출은 0.5 + 2×3 = 6.5천 달러입니다.
- 모델은 데이터로부터 b0와 b1의 최적값을 학습합니다.
예측 수식:
- 셀을 선택하고 아래 수식을 입력합니다.
- 이 수식을 F11까지 드래그하여 채웁니다.
3단계: 잔차와 오차 계산
예측이 실제 값과 얼마나 다른지 측정합니다. 모델은 바로 이 오차를 최소화하는 방향으로 학습하기 때문에 매우 중요한 단계입니다.
- 잔차(Residuals): 각 월의 실제 매출과 예측 매출의 차이입니다.
- 제곱 오차(Squared Errors): 잔차를 제곱한 값입니다. (모든 오차를 양수로 만들고, 큰 오차에 더 큰 페널티를 부여)
잔차:
- 수식을 G11까지 드래그합니다.
제곱 오차:
- 수식을 H11까지 드래그합니다.
4단계: 오차 지표 계산
모델 성능을 비즈니스 관점에서 해석할 수 있는 지표를 만듭니다. 이 지표들은 모델이 실무에 사용할 만큼 충분히 좋은지 판단하는 데 도움이 됩니다.
지정된 영역에 핵심 지표를 설정합니다.
오차 지표:
- 오차 제곱합(SSE): 모든 예측의 총 오차 — 낮을수록 좋습니다.
- 평균 제곱근 오차(RMSE): 원래 단위(천 달러) 기준의 평균 오차 — 해석이 쉽습니다.
- 결정계수(R²): 매출 변동 중 광고비로 설명되는 비율 (0~100%, 높을수록 좋습니다).
=1-(K2/SUMPRODUCT((B2:B11-AVERAGE(B2:B11))^2))
- 평균 절대 오차(MAE): 절대 오차의 평균 — RMSE보다 이상치에 덜 민감합니다.
5단계: 솔버로 파라미터 최적화
엑셀이 예측 오차를 최소화하는 최적의 절편과 기울기를 자동으로 찾도록 합니다.
- 데이터 탭 >> 솔버 선택
- 목표 설정: K2 (SSE 셀)
- 목표: 최소화(Min)
- 변수 셀 변경: E3, E5 (파라미터 셀)
- 풀기(Solve) 클릭
- 확인 클릭
솔버의 작동 원리는 다음과 같습니다.
- b0와 b1의 수백만 가지 조합을 시도합니다.
- 각 조합의 총 오차를 계산합니다.
- 오차가 가장 낮은 조합을 찾을 때까지 조정을 반복합니다.
- 직접 추측하는 것보다 훨씬 빠르고 정확합니다.
6단계: 시각화로 검증하기
모델을 시각적으로 확인하는 것이 중요합니다. 예측선이 대부분의 데이터 포인트 근처를 지나가는지 살펴봅시다.
- 광고비와 매출 열을 선택합니다.
- 삽입 탭 >> 차트에서 분산형 차트(Scatter Plot) 선택
- 차트 우클릭 >> 데이터 선택 >> 추가
- 계열 이름: F1 셀 선택
- X 값: X값 범위 선택 (예: B2:B11)
- Y 값: 예측값 범위 F2:F11 선택
- 예측 계열을 꺾은선 형태로 서식 지정
잘 만들어진 모델이라면 다음과 같은 특징이 보입니다.
- 예측선이 데이터 포인트의 전반적인 추세를 따라갑니다.
- 포인트들이 선 주변에 고르게 분포합니다. (한쪽으로 치우치지 않음)
- 잔차에 뚜렷한 패턴이 없습니다.
2부: 로지스틱 회귀 모델
로지스틱 회귀는 예/아니오 판단에 대한 확률을 예측합니다. 정확한 숫자를 예측하는 선형 회귀와 달리, 로지스틱 회귀는 어떤 일이 발생할 가능성(0~100%)을 예측합니다.
1단계: 이진 분류 데이터 준비
고객의 구매 행동을 모델링해 보겠습니다. 고객의 소득 수준(X)을 기반으로 프리미엄 제품을 구매할지(1) 아닌지(0)를 예측하는 것입니다. 마케팅 타겟팅, 의료 진단 등 모든 이진 판단 상황에서 활용되는 전형적인 사례입니다.
로지스틱 회귀용 데이터를 설정합니다.
고객 소득 수준(만 달러 단위)과 구매 여부 데이터입니다. 저소득 고객(1~5)은 구매하지 않는 경향(0)이 있고, 고소득 고객(6~10)은 구매하는 경향(1)이 있습니다. 현실적인 구매 패턴을 반영한 데이터입니다.
2단계: 로지스틱 예측 수식 만들기
로지스틱 모델 파라미터 초기화:
로지스틱 함수에 필요한 파라미터를 설정합니다. 선형 회귀와 달리, 이 파라미터들은 더 복잡한 수학적 변환(시그모이드 함수)을 거칩니다.
- 절편(b0): 임계점(50% 확률이 되는 지점)을 좌우로 이동시킵니다.
- 기울기(b1): '구매 안 함'에서 '구매함'으로 전환되는 곡선의 기울기를 조절합니다.
- 초기값: 합리적인 추측값으로 시작하며, 솔버가 이를 최적화합니다.
로지스틱 파라미터:
Probability = 1 / (1 + e^(-(b0 + b1×X)))
- 절편(b0): -2 (초기값)
- 기울기(b1): 0.5 (초기값)
로지스틱 예측 수식 작성:
시그모이드 함수를 사용해 선형 조합을 확률로 변환합니다. 예측값이 항상 0과 1 사이에 머물게 하는 수학적 장치입니다.
- 선형 조합: b0 + b1*X (선형 회귀와 동일)
- 시그모이드 변환: 1/(1+e^(-(선형조합))) — 어떤 숫자든 0~1 범위로 변환
- 결과: 확률을 나타내는 부드러운 S자 곡선. 확률이 0.7이라면 해당 고객이 구매할 확률이 70%라는 의미입니다.
확률 예측:
- 선형 조합 계산:
- 확률 예측 계산:
- 해당 셀들의 서식을 백분율(%)로 지정합니다.
3단계: 로그 가능도(Log-Likelihood) 계산
확률 예측이 실제 결과와 얼마나 잘 맞는지 측정합니다. 정확한 값이 아닌 확률을 다루기 때문에 단순 오차 계산보다 복잡합니다.
로그 가능도 구성 요소:
=IF(B2=1,LN(MAX(G2,0.0001)),LN(MAX(1-G2,0.0001)))
핵심 개념을 정리하면 다음과 같습니다.
- 이진 결과에서는 단순 뺄셈(실제값 – 예측값)을 사용할 수 없습니다.
- 대신, 예측을 기준으로 실제 결과가 얼마나 '놀라운지'를 측정합니다.
- 구매 확률 90%를 예측했는데 고객이 실제로 구매했다면 놀랍지 않습니다. (좋은 모델)
- 구매 확률 10%를 예측했는데 고객이 구매했다면 매우 놀랍습니다. (나쁜 모델)
4단계: 로지스틱 모델 평가 지표 설정
분류 성능을 비즈니스 관점에서 측정하는 지표를 만듭니다. 이 지표들은 모델이 실제 비즈니스 의사결정에 사용할 만큼 신뢰할 수 있는지 판단하는 데 필수적입니다.
정밀도가 높으면 낭비되는 마케팅 비용이 줄어들고(거짓 양성 감소), 재현율이 높으면 잠재 고객을 놓치지 않습니다(거짓 음성 감소).
로지스틱 평가 지표:
- 모델 적합도/음의 로그 가능도: 값이 낮을수록 확률 예측이 정확합니다.
- 정확도(Accuracy): 50%를 기준으로 분류했을 때 올바르게 분류된 고객의 비율입니다.
=SUMPRODUCT((G2:G11>0.5)*(B2:B11=1)+(G2:G11<=0.5)*(B2:B11=0))/10
- 정밀도(Precision): 구매할 것으로 예측한 고객 중 실제로 구매한 고객의 비율입니다.
=IF(SUMPRODUCT((G2:G11>0.5))=0,"No Predictions",SUMPRODUCT((G2:G11>0.5)*(B2:B11=1))/SUMPRODUCT((G2:G11>0.5)))
- 재현율(Recall): 실제 구매한 고객 중 우리가 식별해낸 고객의 비율입니다.
=SUMPRODUCT((G2:G11>0.5)*(B2:B11=1))/SUMPRODUCT(B2:B11)
5단계: 솔버로 로지스틱 모델 최적화
데이터의 확률 패턴에 가장 잘 맞는 파라미터 값을 찾습니다. 솔버는 음의 로그 가능도를 최소화하는데, 이는 실제 데이터가 관찰될 확률을 최대화하는 것과 같습니다.
- 데이터 탭 >> 솔버 선택
- 목표 설정: K2 (음의 로그 가능도)
- 목표: 최소화(Min)
- 변수 셀 변경: E3, E5
- 풀기(Solve) 클릭
자주 발생하는 문제 해결법
- 솔버가 수렴하지 않을 때: 다른 초기값을 시도하거나 반복 횟수를 늘려보세요.
- R²(결정계수)가 음수일 때: 데이터 입력 오류나 모델 설정 문제를 확인하세요.
- 로지스틱 회귀에서 완전 분리가 발생할 때: 특성 값을 줄이거나 정규화를 추가하세요.
마무리
이 튜토리얼에서는 엑셀만으로 머신러닝 모델을 구축하는 전 과정을 단계별로 살펴보았습니다. 전문 ML 도구에 비해 엑셀에는 한계가 있지만, 모델의 작동 원리를 투명하고 쉽게 이해할 수 있다는 큰 장점이 있습니다. 엑셀의 솔버와 기본 수식을 활용하면 경량 머신러닝 모델을 빠르게 구현하고, 예측 결과를 시각화하며, 간단하면서도 통찰력 있는 방법으로 모델의 정확도를 평가할 수 있습니다. 여기서 소개한 기법은 더 복잡한 시나리오로 확장할 수 있으며, 회귀 개념을 학습하는 교육 도구로도 훌륭하게 활용됩니다.