Skip to main content

Menghitung Regresi Linear menggunakan Excel

Data Science — panduan praktis

Penulis

Anggit Wirasto, S.Si., M.Eng.

Modul ini berdiri sendiri (tanpa nomor topik DS) sebagai panduan menghitung regresi linear sederhana (satu variabel prediktor) di Microsoft Excel, termasuk metrik evaluasi MAE, RMSE, dan \(R^2\). Data contoh: 100 observasi sintetis — hubungan antara luas bangunan (luas_m2) dan harga jual (harga_juta, satuan juta Rp), dengan variasi lebih lebar di sekitar garis tren dan beberapa outlier agar scatter tidak terlalu rapat dan perbedaan MAE vs RMSE terlihat jelas.

Prasyarat: rumus dasar Excel, scatter plot, dan konsep regresi linear dari materi DS-09 / DS-11.

Data contoh (100 baris)

Kolom Makna
id nomor urut 1–100
luas_m2 luas bangunan (m²) — variabel \(X\)
harga_juta harga jual (juta Rp) — variabel \(Y\)

Unduh berkas CSV:

import pandas as pd

path = "data/regresi_linear_100.csv"
df = pd.read_csv(path)
print(f"Jumlah baris: {len(df)}")
df.head(10)
Jumlah baris: 100
Cuplikan 10 baris pertama dari 100 observasi.
id luas_m2 harga_juta
0 1 182.9 1299.2
1 2 134.6 932.7
2 3 118.5 901.3
3 4 59.6 548.4
4 5 71.2 573.5
5 6 84.8 624.3
6 7 81.1 656.8
7 8 136.8 1118.4
8 9 132.4 953.4
9 10 65.0 573.7
df[["luas_m2", "harga_juta"]].describe().round(2)
luas_m2 harga_juta
count 100.00 100.00
mean 118.41 921.08
std 44.76 308.93
min 50.50 406.70
25% 81.40 679.25
50% 117.60 926.10
75% 144.38 1119.32
max 209.10 1850.00

Catatan: data dibuat untuk latihan — tren linear kasar luas → harga, tetapi noise besar dan lima outlier (id 12, 34, 56, 71, 93) sehingga titik di scatter lebih menyebar dan RMSE / MAE bisa dibandingkan (rasio RMSE/MAE ≈ 1,8–2).

outlier_ids = [12, 34, 56, 71, 93]
df.loc[df["id"].isin(outlier_ids), ["id", "luas_m2", "harga_juta"]]
Tabel 1: Lima baris outlier — jauh dari pola mayoritas (untuk latihan MAE vs RMSE).
id luas_m2 harga_juta
11 12 198.5 520.0
33 34 51.2 1050.0
55 56 175.0 680.0
70 71 118.0 1850.0
92 93 62.0 1180.0

Menyiapkan lembar kerja Excel

  1. Buka Excel → Data → Dari Teks/CSV → pilih regresi_linear_100.csv → muat ke Lembar1.
  2. Pastikan baris 1 berisi header; data numerik di baris 2–101 (100 baris).
  3. Susunan kolom disarankan:
Kolom Header Isi
A id 1 … 100
B luas_m2 prediktor \(X\)
C harga_juta target \(Y\)
D prediksi \(\hat{y}\) dari model
E residual \(y - \hat{y}\)
F abs_error \(\|y - \hat{y}\|\)
G squared_error \((y - \hat{y})^2\)
  1. (Opsional) Sisipkan scatter plot dengan sumbu-X = luas_m2, sumbu-Y = harga_juta untuk melihat hubungan linear.

Excel Indonesia: pemisah argumen fungsi memakai titik koma (;), bukan koma. Contoh: =INTERCEPT(C2:C101;B2:B101). Di bawah ini, rumus ditulis format internasional (/ koma); sesuaikan jika lingkungan Anda memakai locale Indonesia.

Model regresi linear: \(\hat{y} = b_0 + b_1 x\)

Model ordinary least squares (OLS) untuk satu prediktor:

\[ \hat{y}_i = b_0 + b_1 x_i \]

  • \(b_0\) — intercept (titik potong sumbu-\(y\))
  • \(b_1\) — slope (kemiringan: perubahan \(\hat{y}\) per satuan \(x\))

Menghitung \(b_0\) dan \(b_1\) dengan fungsi Excel

Di sel kosong (misalnya kolom H), hitung koefisien dari seluruh data:

Sel Rumus (format internasional) Rumus (Excel Indonesia)
H2 (label intercept) =INTERCEPT(C2:C101,B2:B101) =INTERCEPT(C2:C101;B2:B101)
H3 (label slope) =SLOPE(C2:C101,B2:B101) =SLOPE(C2:C101;B2:B101)

Interpretasi (nilai acuan dari data contoh ini):

  • Intercept \(b_0 \approx 250,9\) → titik potong sumbu-\(y\) pada regresi OLS (ekstrapolasi ke luas nol hanya ilustrasi rumus).
  • Slope \(b_1 \approx 5,7\) → setiap +1 m² luas, prediksi harga naik sekitar 5,7 juta Rp pada rentang luas ±50–210 m².

Kolom prediksi \(\hat{y}\)

Di D2, lalu salin ke bawah sampai D101:

=$H$2+$H$3*B2

Artinya: \(\hat{y}_i = b_0 + b_1 \cdot \text{luas\_m2}_i\) dengan \(b_0\), \(b_1\) di H2, H3 (referensi absolut $).

Kolom residual dan error

Sel Rumus baris 2 Makna
E2 =C2-D2 residual \(e_i = y_i - \hat{y}_i\)
F2 =ABS(E2) kesalahan absolut
G2 =E2^2 kuadrat residual

Salin E2:G2 ke baris 3–101.

Metrik evaluasi di Excel

Hitung pada seluruh 100 observasi (pada latihan ini model dilatih dan dievaluasi pada data yang sama — fokusnya memahami rumus, bukan protokol train/test terpisah).

MAE (Mean Absolute Error)

\[ \mathrm{MAE} = \frac{1}{n}\sum_{i=1}^{n}|y_i - \hat{y}_i| \]

Sel Rumus
H5 =AVERAGE(F2:F101)

Interpretasi: MAE ≈ 95,2 juta Rp (acuan data contoh) → rata-rata meleset sekitar 95 juta per unit, termasuk dampak outlier yang dihitung linear. Mayoritas unit non-outlier meleset jauh lebih kecil — sortir kolom abs_error di Excel untuk melihat distribusi.

RMSE (Root Mean Squared Error)

\[ \mathrm{RMSE} = \sqrt{\frac{1}{n}\sum_{i=1}^{n}(y_i - \hat{y}_i)^2} \]

Sel Rumus
H6 =SQRT(AVERAGE(G2:G101))

Alternatif setara: =SQRT(SUM(G2:G101)/COUNT(G2:G101)).

Interpretasi: RMSE ≈ 175,9 juta Rp — jauh di atas MAE karena residual outlier (mis. id 12, 56) dihukum kuadrat. Rasio RMSE/MAE ≈ 1,85 mengindikasikan beberapa prediksi sangat meleset; cocokkan dengan baris outlier di tabel atas.

\(R^2\) (koefisien determinasi)

\[ R^2 = 1 - \frac{\sum_i (y_i - \hat{y}_i)^2}{\sum_i (y_i - \bar{y})^2} \]

Cara 1 — fungsi bawaan (paling singkat):

Sel Rumus
H7 =RSQ(C2:C101,B2:B101)

RSQ = korelasi kuadrat antara \(Y\) aktual dan nilai yang diprediksi regresi linear — setara \(R^2\) model OLS satu prediktor.

Cara 2 — dari jumlah kuadrat (memahami definisi):

Sel Rumus Makna
H8 =SUMSQ(E2:E101) \(\mathrm{SS}_{\mathrm{res}}\)
H9 =DEVSQ(C2:C101) \(\mathrm{SS}_{\mathrm{tot}}\)
H10 =1-H8/H9 \(R^2 = 1 - \mathrm{SS}_{\mathrm{res}}/\mathrm{SS}_{\mathrm{tot}}\)

Interpretasi: \(R^2 \approx 0{,}67\) → model linear menjelaskan ~67% variasi harga; sisanya noise dan outlier. Ini realistis untuk latihan: scatter tidak rapat, dan luas saja tidak “menjelaskan” hampir semua harga.

Jebakan: \(R^2\) tinggi di data latih tidak menjamin performa sama di data baru — untuk proyek nyata pisahkan train/test (lihat DS-11).

Tabel ringkasan hasil (contoh tata letak)

Metrik / koefisien Sel contoh Nilai acuan (data contoh)
Intercept \(b_0\) H2 250,92
Slope \(b_1\) H3 5,66
MAE H5 95,17
RMSE H6 175,89
RMSE / MAE — 1,85
\(R^2\) (RSQ) H7 0,673

Angka acuan dihitung ulang dari CSV; toleransi ±0,05 jika pembulatan sel Excel berbeda.

import numpy as np
import pandas as pd

df = pd.read_csv("data/regresi_linear_100.csv")
x = df["luas_m2"].to_numpy()
y = df["harga_juta"].to_numpy()
n = len(y)
xbar, ybar = x.mean(), y.mean()
b1 = np.sum((x - xbar) * (y - ybar)) / np.sum((x - xbar) ** 2)
b0 = ybar - b1 * xbar
pred = b0 + b1 * x
res = y - pred
mae = np.mean(np.abs(res))
rmse = np.sqrt(np.mean(res**2))
r2 = 1 - np.sum(res**2) / np.sum((y - ybar) ** 2)
pd.Series(
    {
        "intercept": round(b0, 2),
        "slope": round(b1, 2),
        "MAE": round(mae, 2),
        "RMSE": round(rmse, 2),
        "RMSE/MAE": round(rmse / mae, 2),
        "R2": round(r2, 4),
    },
    name="Nilai acuan",
)
intercept    250.9200
slope          5.6600
MAE           95.1700
RMSE         175.8900
RMSE/MAE       1.8500
R2             0.6725
Name: Nilai acuan, dtype: float64

Alternatif: Analysis ToolPak (Regresi)

Jika Analysis ToolPak aktif (File → Opsi → Add-ins → Analysis ToolPak):

  1. Data → Data Analysis → Regression.
  2. Input Y Range: C1:C101 (dengan header).
  3. Input X Range: B1:B101.
  4. Centang Labels; pilih sel output.
  5. Laporan ToolPak menampilkan Intercept, X Variable (slope), R Square, dan Standard Error — cocokkan dengan perhitungan manual di atas.

Gunakan salah satu jalur (rumus sel demi sel atau ToolPak), jangan mencampur sumber koefisien yang berbeda tanpa sadar.

Interpretasi bisnis singkat

Dengan data contoh:

  • Slope ≈ 5,7 — penambahan 10 m² terkait kenaikan prediksi ~57 juta Rp (garis tren, bukan untuk outlier).
  • MAE ≈ 95 juta vs RMSE ≈ 176 juta — outlier menaikkan RMSE lebih keras; di Excel, filter baris dengan abs_error > 200 untuk melihat kontributor terbesar.
  • \(R^2\) ≈ 0,67 — luas membantu, tetapi lokasi, kondisi, dan transaksi anomali (id outlier) belum masuk model; scatter di Excel sebaiknya tampak berawan, bukan rapat di garis.

Ringkasan rumus Excel

Tujuan Fungsi Excel
Intercept \(b_0\) INTERCEPT(Y_range, X_range)
Slope \(b_1\) SLOPE(Y_range, X_range)
Prediksi \(\hat{y}\) =$b_0$ + $b_1$ * X
Residual =Y - prediksi
MAE AVERAGE(ABS(residual))
RMSE SQRT(AVERAGE(residual^2))
\(R^2\) RSQ(Y_range, X_range) atau 1 - SUMSQ(res)/DEVSQ(Y)

Latihan lanjutan (opsional)

  1. Hapus atau koreksi satu outlier (id 12, 34, 56, 71, atau 93) — hitung ulang MAE, RMSE, dan \(R^2\); bandingkan dengan nilai acuan.
  2. Tambahkan garis tren pada scatter (klik kanan seri data → Add Trendline → Linear, tampilkan persamaan).
  3. Salin 80 baris sebagai “latih” dan 20 baris sebagai “uji”; hitung MAE/RMSE/\(R^2\) hanya pada uji (mengulas DS-11).

Referensi