Penjelasan analisis regresi ms excel 2007
-
Upload
bang-mohtar -
Category
Education
-
view
6.258 -
download
5
Transcript of Penjelasan analisis regresi ms excel 2007
PENGGUNAAN FUNGSI REGRESI EXCELPerhitungan Persamaan Regresi Ganda
Untuk mudahnya, pembahasan dilakukan dengan memberikan contoh.
Contoh yang diambil adalah penentuan Final Boiling Point (FBP) produk naphtha
pada Crude Distiller Unit (CDU). Dari pengalaman dan pengetahuan proses, FBP
produk naphtha dominannya dipengaruhi oleh dua variabel yaitu overhead
temperature dan pressure compensated fractionator temperature. Berikut adalah
data FBP produk naphtha dan kedua variabel proses yang mempengaruhinya.
Pilih (klik)
Selanjutnya dengan data ini akan dibangun model inferential dengan
menggunakan fungsi regresi di microsoft excell.
Fungsi regresi di microsoft excel dapat diakses dari menu dengan cara
sebagai berikut: Data – Data Analysis… , muncul jendela Data Analysis, pilih
Regression – Ok – muncul jendela Regression.
Pada jendela Regression: Input Y Range diambil dari data kolom
B2 s/d B29 ($B$2:$B$29) yaitu data untuk Naphtha FBP, Input X Range diambil
dari kolom C2 s/d D29 ($C$2:$D$29) yaitu data untuk Overhead temperature
dan Pressure compensate temperature, Confidence Level tetap diset 95%, Output
Range G2 ($G$32). Kemudian tekan OK. Hasil regresinya adalah sbb:
Hasil/output perhitungan regresi terdiri dari 3 komponen/tabel, yaitu: 1)
Regression Statistics; 2) ANOVA; 3) Regression Coefficients. Selanjutnya akan
dijelaskan maksud dari masing-masing tabel tersebut.
Tabel pertama adalah Regression Statistic, yang digambarkan kembali
sebagai berikut.
Tabel ini menunjukan besarnya korelasi/varian antara variabel tidak
bebas (dependent variable, dalam hal ini FBP produk naphtha) dengan variabel
bebas (independent variable, dalam hal ini Overhead temperature dan pressure
compensated fractionator temperature).
Dari semua parameter yang ada pada tabel tersebut, parameter R Square
yang biasanya digunakan untuk menentukan bagus tidaknya korelasi/variasi
model hasil regresi (walaupun ada juga yang menggunakan Multiple R atau
Adjusted R Square). Sebagai Panduan umum, R Square > 0.8 menunjukan varian
model bagus. R square sebesar 0,690 seperti yang dihasilkan pada tabel diatas
juga masih cukup baik, ia mengandung pengertian 69,03% perubahan/variasi
output (FBP produk naphtha) dipengaruhi oleh input (Overhead temperature dan
Pressure compensated fractionator temperature), sedangkan sisanya oleh variabel
lainnya. R Square, yang menunjukan total varian yang dihasilkan oleh model,
dihitung dengan rusmus: R Square = 1 – (SSres/SStotal). Mengenai apa itu SSres
dan SStotal lihat pembahasan tentang tabel ANOVA.
Parameter lainnya adalah Multiple R, disebut sebagai koefisien korelasi
antara variabel output dan input, dihitung dengan rumus : Multiple R = (R
Square)^0.5.
Adjusted R Square, merupakan nilai R Square yang di-adjusted sesuai
ukuran model, dengan menggunakan rumus : Adjusted R Square = 1 –
(SSres/dfress)/(SStotal/dftotal).
Standard Error, merupakan standard deviasi error keseluruhan model.
Observation, adalah jumlah observasi/data.
Tabel keduadalah ANOVA (analysis of variant), berisi jumlah kuadrad
(sum of square) untuk setiap komponen.
Dalam regresi, ANOVA digunakan untuk mengetes tingkat
kebenaran/signifikan model hasil regresi secara keseluruhan (overall model).
Parameter yang menentukan tingkat signifikan model regresi adalah yang berada
pada kolom paling kanan, yaitu Significance F. Parameter ini disebut juga p-
value. Tingkat signifikan model naik jika Significance F turun. Significance F
mendekati nol berarti variabel input sangat (signifikan) berpengaruh pada output.
Tabel berikut bisa digunakan sebagai panduan untuk menentukan hubungan antara
Significance F dengan tingkat signifikan model yang dihasilkan.
Untuk contoh ini, nilai Significance F adalah 0.0000000187 < 0.05 (alfa),
sehingga model yang dihasilkan signifikan.
Untuk lebih jelasnya, akan diuraikan lebih rinci tentang informasi yang
ada dalam tabel ANOVA. Secara umum tabel ANOVA berbentuk sbb:
Kolom pertama adalah source, yaitu data yang akan dicek
rentang/variasinya. Ada 3 source, yaitu Regression, Residual dan Total.
Regression digunakan untuk melihat rantang/variasi dari model yang diperoleh.
Residual digunakan untuk melihat kesalahan/error dari model yang diperoleh.
Total merupakan penjumlahan antara Regression dan Residual.
Kolom kedua adalah df, merupakan kependekan dari degree of freedom.
Ia menunjukan sejauh mana variabel bebas yang dimiliki. m adalah jumlah
variabel dan n adalah jumlah observasi.
Kolom ketiga, Sum of Square (SS) untuk masing-masing source. Untuk
Regression, Sum of Square (SSreg) merupakan penjumlahan dari kuadrat nilai
yang diprediksi/hasil perhitungan model (Yhat) dikurangi nilai rata-rata output
data observasi (Ybar). Ini bertujuan untuk mengukur sejauh mana rentang/variasi
hasil prediksi model dari rata-rata data observasi. Semakin tinggi SSreg semakin
bagus karena rentang/variasinya semakin besar (model bisa digunakan dalam
rentang yang lebar).
Untuk Residual, Sum of Square (SSres) merupakan jumlah kuadrat data
observasi (Y) dikurangi nilai prediksi (Yhat). Ini bertujuan untuk mengetahui
sejauh mana model sama/mirip dengan aktual. Jika modelnya bagus maka SSres
kecil.
Yang ketiga adalah Sum of Square untuk total (SStotal), ini merupakan
jumlah kuadrat dari masing-masing observasi (Y) dikurangi rata-rata seluruh
observasi (Ybar). Ini digunakan untuk mengukur sejauh mana rentang/variasi
observasi terhadap rata-ratanya.
Kolom keempat, Mean Square (MS), yang dihasilkan dengan membagi
masing-masing Sum of Square (SS) dengan degree of freedoms (df). MSres
disebut juga dengan variance of error, yang mengukur sebaran data observasi
terhadap prediksi.
Kolom kelima, F, yang diperoleh dengan membagi Mean Square untuk
regresi (MSreg) terhadap Mean Square untuk residual (MSres). Nilai F digunakan
untuk menentukan apakah model sesuai dengan data aktual.
Kolom terakhir adalah Significance F atau dikenal juga dengan p-value,
digunakan untuk menunjukan ukuran tingkat signifikan model regresi dalam
menjelaskan tingkat variasi output. Significance F ini diperoleh berdasarkan nila
F, df regression dan df residual, bisa diperoleh dengan menggunakan rumus excell
berikut =FINV(F,dfreg,dfres). Tingkat signifikan model regresi akan naik jika
Significance F turun. Significance F mendekati nol berarti variabel input sangat
(signifikan) berpengaruh pada output.
Tabel terakhir adalah tabel coefficients. Tabel ini berisi nilai koefisien
model hasil regresi dan beberapa parameter penting untuk setiap koefisien.
Kolom coefficients berisi nilai koefisien model hasil perhitungan
(menggunakan metode least square estimate). Kolom standard Error berisi
simpangan baku (standard error) perhitungan koefisien model. Kolom t Stat
adalah t-statistic untuk koefisen model, yang dihitung dengan membagi nilai
koefisien oleh standard error-nya, t Stat = coefficient/standard error. Kolom P-
value berisi p-value yang terkait dengan t statistic untuk masing-masing koefisien.
Parameter P-value bergantung pada t Stat dan df, yang dihitung dengan
menggunakan rumus two tailed t distribution pada excell sbb : P-value = TDIST(t
value,df,2). Parameter P-value disini sama artinya dengan pada tabel ANOVA,
yang membedakannya adalah P-value pada tabel ANOVA untuk keseluruhan
model, sedangkan disini P-value untuk masing-masing koefisien model.
Hubungan antara P value dengan tingkat signifikan koefisien model adalah sbb:
Kolom Lower 95% dan Upper 95% menunjukan nilai koefisien pada
interval 95% confidence. Batas konfiden (confidence limits) dihitung dengan
menggunakan rumus excell berikut: Coefficient ± t*standard error. Dengan
t adalah t value sesuai nilai df dan alpha tertentu (dalam contoh ini df = 21 dan
alpha = 0.05), yang dapat dihitung dengan menggunakan rumus excell :
=TINV(alpha,df). Koefisien model bagus/signifikan apabila rentang 95%
significant ini tidak berisi nol.
Dari semua parameter tersebut, yang perlu diperhatikan untuk menilai
koefisien model adalah cukup nilai koefisien dan P-value sesuai kriteria tingkat
signifikan diatas. Untuk contoh ini, karena p-value untuk ketiga koefisien < 0,05
(alpha), maka nilai ketiga koefisien tersebut signifikan.
A. Rangkuman Regresi
Pada regresi, ada 3 parameter kunci yang menentukan/ menunjukan
model hasil regresi tersebut bagus, yaitu:
Dari tabel hasil regresi untuk contoh diatas, diperoleh model inferential
untuk FBP produk naphtha sbb:
Naphtha FBP = 0,504 * (Overhead Temperature) 0,574 *
(Pressure Compensate Temperature) + 4,672.
Dengan R Square = 0,690 (mendekati 0,7), Significance F =
0.0000001344 < 0.05 (alpha), dan P value untuk ketiga koefisien < 0.05 (alpha).
Sehingga dapat disimpulkan, model hasil regresi ini cukup baik/signifikan.
Hasil ini tidak jauh berbeda dengan hasil perhitungan regresi dengan
menggunakan Profit Sensor Pro yang merupakan paket inferential model produk
Honeywell, yaitu:
Naphtha FBP = 0,504 * (Overhead Temperature) 0,574 *
(Pressure Compensate Temperature) + 4,672. Dengan R
Square = 0,690.
B. Konversi Data
Regresi yang digunakan disini adalah regresi linear, sehingga untuk
mendapatkan model yang benar, relasi antara variabel bebas dan variabel tidak
bebas harus bersifat linear. Untuk itu, sebelum digunakan dalam regresi, data
tersebut harus dicek terlebih dahulu linearitasnya dengan menggunakan grafik
scatter. Apabila trending di grafik scatter menunjukan hubungan tidak linear,
maka sebelum digunakan data tersebut harus dikonversi menjadi linear terlebih
dahulu. Beberapa tipe relasi yang mungkin terjadi adalah exponential, logarithmic
dan sigmoid. Untuk konversi exponential dan logarithmic ke linear gunakan
rumus [=LN(y)] dan [=log10(y)]. Sedangkan untuk konversi sigmoid ke linear
dapat digunakan fungsi logistic [=log10(y/(1-y))] atau probit transformation
[=NORMINV(y,5,1)].
C. Pengujian Hipotesa
Salah satu fungsi statistik adalah menguji hipotesa. Lalu hipotasa apa
yang hendak diuji dalam perhitungan regresi diatas? Berikut akan dijelaskan
secara garis besar.
Pada tabel ANOVA, hipotesa yang hendak diuji adalah: H0 – nilai
semua koefisien sama dengan nol; Ha – paling sedikit satu buah koefisien
nilainya tidak sama dengan nol. Apabila F Significance (p-value) < alpha maka
Ha diterima dan H0 ditolak, sebalinya apabila F Significance > alpha maka H0
diterima dan Ha ditolak. Pada perhitungan diatas, karena F significance =
0.0000001344 < 0.05 (alpha) maka Ha diterima dan H0 ditolak, yang berarti
paling sedikit ada 1 buah koefisien yang nilainya tidak sama dengan nol.
Pada tabel Regression Coefficient, hipotesa yang hendak diuji adalah
untuk masing-masing koefisien: H0 – nilai koefisien sama dengan nol; Ha – nilai
koefisien tidak sama dengan nol. Apabila P-value<alpha maka Ha diterima dan
H0 ditolak, sebalinya apabila P-value> alpha maka H0 diterima dan Ha ditolak.
Pada perhitungan diatas, karena P-value untuk semua koefisien < 0.05 (alpha),
maka H0 ditolak dan Ha diterima, yang berarti semua koefisien nilainya tidak
sama dengan nol.
D. Penentuan Variabel Bebas.
Sebenarnya, variabel proses yang menentukan nilai FBP produk naphtha
tidak hanya Overhead temperature dan Pressure compensated fractionator
temperature. Ada variabel proses lainnya juga berpengaruh, misalnya Naphtha
flowrate, Column overhead pressure, Pump around flowrate, Flash zone
temperature, ddan sebagainya. Akan tetapi karena pengaruhnya tidak signifikan,
maka variabel tersebut tidak diikutsertakan dalam model. Cara menentukan
variabel mana yang dominan berpengaruh dan mana yang tidak dominan adalah
dengan melakukan regresi terhadap semua variabel tersebut baik secara bersama-
sama (sekaligus) maupun kombinasi diantaranya. Model hasil regresi yang paling
signifikan-lah yang akan dipakai.