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.
import pandas as pdpath ="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).
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
Buka Excel → Data → Dari Teks/CSV → pilih regresi_linear_100.csv → muat ke Lembar1.
Pastikan baris 1 berisi header; data numerik di baris 2–101 (100 baris).
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\)
(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).
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.
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.
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.
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):
Data → Data Analysis → Regression.
Input Y Range:C1:C101 (dengan header).
Input X Range:B1:B101.
Centang Labels; pilih sel output.
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)
Hapus atau koreksi satu outlier (id 12, 34, 56, 71, atau 93) — hitung ulang MAE, RMSE, dan \(R^2\); bandingkan dengan nilai acuan.
Tambahkan garis tren pada scatter (klik kanan seri data → Add Trendline → Linear, tampilkan persamaan).
Salin 80 baris sebagai “latih” dan 20 baris sebagai “uji”; hitung MAE/RMSE/\(R^2\)hanya pada uji (mengulas DS-11).