analisis data dengan excell
TRANSCRIPT
i
KARYA PENGABDIAN PADA MASYARAKAT
Pemanfaatan Excel untuk Analisis Data
Haryadi
NIDN 0003116401
LEMBAGA PENELITIAN DAN PENGABDIAN PADA MASYARAKAT
UNIVERSITAS MUHAMMADIYAH PALANGKARAYA
PALANGKA RAYA, 2012
ii
iii
Daftar Isi KATA PENGANTAR........................................................................................................................................ iv
Mengaktifkan paket Analysis ToolPak .......................................................................................................... 1
Membuat Ringkasan Data ............................................................................................................................. 2
Frekuensi Distribusi ....................................................................................................................................... 4
Uji Hipotesis .................................................................................................................................................. 8
Uji kesamaan dua mean populasi dengan ukuran sampel sama .............................................................. 8
Uji kesamaan dua mean populasi dengan varian sama dan tidak diketahui .......................................... 10
Uji kesamaan dua mean populasi dengan varian berbeda dan tidak diketahui ..................................... 12
Uji kesamaan dua mean populasi dengan varian diketahui ................................................................... 12
Regresi Linear .............................................................................................................................................. 15
Regresi Linear Sederhana........................................................................................................................ 15
Regresi Linear Ganda .............................................................................................................................. 17
Analisis Varian ............................................................................................................................................. 19
Analisis Varian Satu Faktor...................................................................................................................... 19
Analisis Varian Dua Faktor ...................................................................................................................... 20
iv
KATA PENGANTAR
Bismillahirrahmaanirrahiim,
Perangkat lunak Excel merupakan perangkat lunak yang telah secara luas digunakan oleh masyarakat
untuk membantu pekerjaan yang banyak komputasinya. Meskipun demikian masih sangat banyak
fasilitas yang dimiliki program ini yang belum dimanfaatkan untuk membantu pekerjaan sehari-hari
maupun pekerjaan ilmiah.
Dalam tulisan ini akan disajikan penggunaan Excel untuk analis data, baik untuk statistiK deskkriptif
maupun inferensial. Selain digunakan oleh peneliti dan mahasiswa, analisis data statistiK juga banyak
digunakan oleh masyarakat luas, baik di pemerintaah maupun swasta. Agar para pengguna dapat
mempelajari dengan mudah, pembahasan disajikan secara sederhana dengan disertai gambar dan
contoh. Prasyarat untuk menggunakan tulisan ini adalah memiliki pengetahuan statistika dasar, sebab
semua konsep dan istilah mengacu pada pengetahuan tersebut.
Demikian, semoga tulisan ini bermanfaat bagi mansyarakat luas.
Penulis, Haryadi NIDN 0003116401
1
Mengaktifkan paket Analysis ToolPak Untuk bisa melakukan analisis statistic dengan Excel, kita bisa menggunakan paket Analysis ToolPak.
Secara normal, program Excel yang telah diintall di computer paket Analisys ToolPak belum aktif. Untuk
mengaktifkan paket ini dilakukan sebagai berikut:
1. Klik Office Buttom , kemudin klik
2. Pada menu Excel Option Klik Add-Ins lalu Analysis ToolPak lalu Go lalu beri tanda cek pada
Analysis ToolPak
3. lalu klik OK. Sekarang pada toolbar Data terdapat sub menu Data Analysis.
2
Membuat Ringkasan Data
Dari data mentah biasanya kita menginginkan ringkasan data seperti mean, standar deviasi, dan
sebagainya. Untuk membuat ringkasan data, pertama data mentah harus sudah dientry pada worksheet.
Misalkan kita telah memiliki data berikut
Untuk membuat ringkasan data dilakukan sebagai berikut:
1. klik Data kemudian klik Data Analysis dan tampil menu berikut
2. Pilih Descriptive Statistics kemudian klik OK, dan tampil menu berikut
3
Pada pilihan di samping, jika data tidak memiliki nama kolom, maka Label in First Row jangan diberi tanda cek. Pada Output options ada tiga pilihan, yaitu Output Range jika hasil analisis akan diletakan pada lembar kerja yang sama dengan lembar kerja data, New Worksheet Ply jika hasil analisis akan diletakan pada lembar kerja baru, dan New Workbook jika hasil analisis akan diletakan pada file terpisah dengan dile data.
3. Klik Input Range, lalu pilih sel A1 sampai dengan A11, yaitu sel yang berisi data.
4. Beri tanda cek pada Label in First Row dengan maksud baris pertama pada tabel adalah nama
kolom.
5. Beri tanda cek pada Summary statistics, dan akhirnya klik OK. Sekarang ada lembar kerja baru
yang berisi ringkasan data seperti berikut
4
Frekuensi Distribusi
Excel dilengkapi dengan fasilitas untuk membuat table frekensi distribusi, hitstogram frekensi dan
histogram frekuensi kumulatif. Perlu dicatat bahwa Excel menggunakan aturan nilai data yang nilainya
sama dengan batas bawah interval tidak termasuk dalam interval tersebut, dan nilai data yang nilainya
sama dengan batas atas interval termasuk kedalam kelas interval tersebut.
Misalkan kita telah memiliki data berat badan dan ingin dibuat table frekuensi dengan 5 interval kelas
sebagai berikut
Berat badan < 50
50 < berat badan < 60
60 < berat badan < 70
70 < berat badan < 80
berat badan > 80
Setelah data berat badan dientri, pada sel lain di lembar tersebut dibuat interval kelas (bin range)
5
Sekarang kita siap untuk membuat table frekuensi:
1. Klik Data kemudian klik Data Analysis lalu pilih Histogram
6
2. Pada Input Range, pilih sel yang berisi data dan nama kolomnya (sel A1 s/d sel A25)
3. Pada Bin Range, pilih sel yang memuat kelas interval dan nama kolomnya (sel D6 s/d sel D10)
4. Pada Labels beri tanda cek
5. Pada Chart Output beri tanda cek (untuk menampilkan histogram)
6. Klik OK, kemudian akan tampil lembaran baru, yaitu tabel frekuensi distribusi dan histogramnya,
sebagai berikut
Jika kita menginginkan pilihan output yang lain, maka kita bisa memberi tada cek pada pilihan tersebut.
Jika pada Pareto (sorted histogram) dan Chart Outpu diberi tanda cek, aka akan dihasilkan table
frekuensi distribusi pareto dan histogramnya sebagai berikut
7
Jika pada Cumulative Percentage dan Chart Outpu diberi tanda cek, aka akan dihasilkan tabel frekensi
kumulatif dan histogramnya seperti gambar berikut.
8
Uji Hipotesis
Pengambilan kesimpulan secara statistic pada dasarnya adalah pengujian suatu hipotesis.
Dalam bagian ini kita akan menguji hipotesis kesamaan mean dua populasi
π»0: π1 = π2
π»1: π1 β π2
Hipotesi dapat dapat ditulis kembali sebagai
π»0: π1 β π2 = 0
π»1: π1 β π2 β 0
Kita akan meninjuau kasus-kasus:
Kedua sampel memiliki ukuran sama
Kedua populasi memiliki varian sama
Kedua populasi memiliki varian berbeda
Kedua populasi meliki varian sama dan diketahui.
Uji kesamaan dua mean populasi dengan ukuran sampel sama
Misalkan kita ingin mengetahui apakah dua jenis neraca, neraca A dan neraca B, memberikan hasil
penimbangan yang sama. Misalkan hasil penimbangan delapan objek dengan kedua neraca memberikan
hasil sebagai berikut
Sekarang kita akan menguji apakah hasil penimbangan kedua neraca berbada:
9
1. Klik Data kemudian klik Data Analysis lalu pilih t-Test: Paired Two Sample for Means
2. Pada Variable 1 Range pilih kolom nilai data Neraca A dan judul kolomnya (sel B1 s/d sel B9)
Pada Variable 2 Range pilih kolom nilai data Neraca B dan judul kolomnya (sel C1 s/d sel C9)
Pada Hypothesized Mean Difference ketik 0 (mengindikasikan beda kedua mean adalah 0)
Pada Label, beri tanca cek, lalu klik OK, akan tampil lembaran baru sebagai berikut
Pada keluaran di samping, t Stat menyatakan t-hitung atau statistik t, t Critical two-tail menyatakan nilai kritis t untuk hipotsis yang sedang kita bahas, sering disebut pula dengan istilah statistic penguji atau t table. Berdasarkan hasil ini, nilai t Stat < t Critical two-tail, yang berarti bahwa tidak terdapat berbedaan hasil pengukuran kedua jenis neraca pada tingkat signifikansi 0.05. Kesimpulan bisa pula diperoleh dari nilai P(T<=t) two-tail yang menyatakan nilai maksimum kesalahan jenis pertama. Berdasarkan table di samping, ternyata nilai P(T<=t) two-tail lebih besar dari 0.05, yang berarti tidak ada berbedaan tingkat signifikansi 0.05.
10
Uji kesamaan dua mean populasi dengan varian sama dan tidak diketahui
Misalkan akan diuji apakah tekanan darah sistolik antara perokok dan bukan berokok berbeda. Sampel
pertama berukuran 11 berasal dari populasi perokok dan sampel kedua berukuran 15 berasal dari
populasi bukan perokok. Hasil pengukuran tekanan darah sistolik kedua sampel adalah sebagai berikut
Sekarang kita akan menguji hipotesis
π»0: π1 β π2 = 0
π»1: π1 β π2 β 0
dengan tingkat signifikansi 1 persen:
1. Klik Data kemudian klik Data Analysis lalu pilih t-Test: Two-Sample Assuming Equal Variances,
lalu klik OK,
11
2. Pada Variable 1 Range pilih kolom nilai data Prokok dan judul kolomnya (sel A1 s/d sel A12)
Pada Variable 2 Range pilih kolom nilai data Bukan Perokok dan judul kolomnya (sel B1 s/d sel
B16)
Pada Hypothesized Mean Difference ketik 0 (mengindikasikan beda kedua mean adalah 0)
Pada Label, beri tanca cek, lalu klik OK, akan tampil lembaran baru sebagai berikut
Pada Alpha ketik 0.01 (tingkat signifikansi 1 persen), lalu klik OK dan akan dihasilkan
Karena hipotesisnya adalah dua arah (two sides), maka kita menggukana t Stat (statistic t atau t hitung)
dan t Critical two-tail (nilai kiris t atau t table) untuk menyimpulkan bahwa tidak ada berbedaan
12
tekanan darah sistolik antara perokok dan bukan perokok pada tingkat signifikansi 1 persen. Kesimpulan
yang sama dapat diperoleh dari nilai P(T<=t) two-tail yang lebih besar dari 0.01.
Uji kesamaan dua mean populasi dengan varian berbeda dan tidak diketahui
Untuk ini kita gunakan data tekanan sistolik pada perokok dan bukan perokok di atas. Jika diasumsikan
varian kedua populasi tidak diketahui dan tidak sama, maka kita menggunakan langkah
Klik Data kemudian klik Data Analysis lalu pilih t-Test: Two-Sample Assuming Unequal Variances, lalu
klik OK, kemudian diteruskan dengan langkah seperti pada kasus varian sama. Hasil analisis ditunjukan
pada table berikut
Uji kesamaan dua mean populasi dengan varian diketahui
Misalkan ingin diketahui apakah ada perbedaan kadar PCB pada ikan yang berasal dari danau A dan
danau B pada tingkat signifikansi 5 persen. Dari danau A ditangkap 10 ikan dan dari danau B ditangkap 8
ikan kemudian dilakukan pengukuran kadar PCBnya. Karena perbedaan teknik pengukuran, misalnya
varian PCB ikan dari danau A adalah 0.09 dan dari danau B adalah 0.16. Data hasil pengukuran PCB
misalnya adalah sebagai berikut:
13
Karena varian populasi diketahui maka kita menggunakan langkah berikut untuk menguji hipotesis:
1. Klik Data kemudian klik Data Analysis lalu pilih t-Test: Two-Sample Assuming Unequal
Variances kemudian pilih z-Test: Two Sample for Means lalu klik OK,
2. Kemudian dilanjutkan dengan cara serupa untuk Variabel 1 Range, Variabel 2 Range dan
Hypothesized Mean Difference.
Pada Variable 1 Variance (known) diisi 0.09 (varian PCB ikan dari danau A)
14
Pada Variable 2 Variance (known) diisi 0.16 (varian PCB ikan dari danau B)
Label dan Alpha diisi seperti pada cara sebelumnya. Setelah diklik OK akan dihasilkan
Untuk pengambilan kesimpulan, dibandingkan nilai z (z hitung) dan z Critical two-tail, atau
menggunakan nilai P(Z<=z) two-tail. Berdasarkan tabel tersebut disimpulkan terdapat berbedaan PCB
ikan dari kedua danau pada tingkat signifikansi 5 persen.
15
Regresi Linear Paket Data Analysis pada Excel dilengkapi dengan fasilitas regresi linear satu dan banyak variable. Secara
umum hubungan linear antara variable independend π¦ dan variable bebas/ explanatory π₯1 ,π₯2, β― , π₯π
dapat dinyatakan dengan
π¦ = π + π1π₯1 + π2π₯2 + β―+ πππ₯π + πππ πππβππ ππππππ
Dengan π ,π1 ,π2 ,β― , ππ adalah konstanta-konstanta yang akani kita mencari dengan Excel.
Regresi Linear Sederhana
Regresi linear sederhana merupakan hubungan antara dua variable dengan satu variable bebas. Dengan
demikian persamaan regresinya dapat dinyatakan sebagai
π¦ = π + π π₯ + πππ πππβππ ππππππ
Dimana dalam hal ini π dinamakan intersep dan π dinamakan slope.
Untuk memberikan gambaran bagaimana regresei demikian dicari dengan Excel, misalkan kita ingin
menduga hubungan antara tekanan dan temperature. Musalkan variable bebasnya adalah temperature
dan data hasil pengamatan adalah sebagai berikut
Untuk membuat regresi dengan temperature sebagai variable independen dan tekanansebagai variable
dependen, dilakukan dengan langkah-langkah berikut:
1. Klik: Data lalu Data Analysis lalu Regression
2. Setelah diklik OK akan muncul menu berikut:
16
Pada Input Y Range pilih nilai data Tekanan dan judul kolomnya (sel C1 s/d sel C11)
Pada Input X Range pilih nilai data Temperatur dan judul komponya (sel B1 a/d sel B11)
Pada Label beri tanda cek
Pada Confidence Level ketik 95 (interval kepercayaan 95 persen)
Setelah klik OK akan muncul hasil berikut
17
Pada table di atas, R square menyatakan nilai koefisien determinasi (πΉπ)
Pada ANOVA, nilai F menyatakan nilai statistic F atau sering sebut F hitung. Selain dengan nilai F untuk
menentukan signifikan tidaknya persamaan regresi, dapat pula digunakan significance F pada kolom
terakhir table ANOVA.
Dari table paling bawah, kita bisa memperoleh nilai intersep 0.742638 dan nilai slope 0.024197, yang
berarti persamaan regresi antara tekanan dan temperature dapat diprediksi dengan
πππππππ = 0.742638 + 0.024197 Γ ππππππππ‘π’π
Tabel tersebut juga menyajikan interval kerpcayaan 95 persen untuk intersep dan untuk slope.
Regresi Linear Ganda
Oleh karena itu model regresi ganda sering digunakan dalam aplikasi, sebab dalam penerapan biasanya
variable respon dipengaruhi oleh lebih dari satu variable independan. Sebagai contoh, dalam proses
produksi, ouput dipengaruhi leh variable modal dan tenaga kerja. Misalkan kita memiliki data suatu
proses produksi berikut
18
Kita bisa membuat regresi antara variable tak bebas produksi dengan dua variable bebas modal dan
tenaga kerja dengan langkah-langkah yang serupa dengan regresi linear sederhana, perbedaaanya
hanya pada
Input X Range dipilih nilai data modal dan tenaga kerja beserta judul komponya (sel B1 a/d sel C9)
Ouput untuk data produksi di atas adalah sebagai berikut
Interpretasi dari table-tabel di atas sama dengan pada regresi linier sederhana. Dari table terakhir,
prediksi model regresinya adalah
πππππ’ππ π = β5.47199 + 0.635901 Γ πππππ + 0.106369 Γ ππππππ πΎππππ
19
Analisis Varian
Analisi varian sering digunakan untuk mengetahui apakah suatu variable respon dipengaruhi oleh
sejumlah perlakuan. Objek yang dikenai perlakuan dinamakan satuan percobaan. Prinsip dasar pada
analisis varian adalah ulangan, randomisasi dan pengelompokan. Dengan ulangan maksudnya adalah
setiap level perlakuan dikenakan beberapa kali pada satuan percobaan.
Analisis Varian Satu Faktor Sebagai contoh, misalnya suatu eksperimen ingin mengetahui pengaruh dosis pupuk terhadap
pertumbuhan tanaman dengan mengenakan 5 dosis pupuk P1, P2, P3, P4 dan P5, dan diulang 3 kali.
Misalkan hasil pengamatan terhadap tinggi tanaman adalah sebagai berikut:
Langkah-langkah melakukan analisis varian satu factor:
1. Klik: Data lalu Data Analysis lalu Anova: Single Factor
2. Setelah diklik OK akan muncul menu berikut:
3. Pada Input Range pilih sel yang memuat data (sel A1 s/d sel E5)
4. Pada Grouped By: klik Columns
5. Pada Labels in First Row, beri tanda cek. Setelah dilklik OK maka akah dihasilkan:
20
Kesimpulan tentang pengaruh perlakuan dapat dilihat dari table ANOVA pada kolom F atau P-value;
dalam contoh ini nilai F lebih besar dari nilai kritis F (F crit) yang berarti perlakuan pupuk berpengaruh
terhadap pertumbuhan tinggi tanaman pada tingkat signifikansi 0.05. Kesimpulan yang sama dapat
dilihat dari P-value dimana dalam table ini lebih kecil dari 0.05.
Analisis Varian Dua Faktor Misalkan data berikut adalah hasil pengamatan tinggi tanaman yang diberi dua factor, pupuk P dan
pupuk N. Kedua jenis pupuk masing-masing terdiri dari 3 dosis (level) dan percobaan dulang 4 kali.
21
Langkah-langkah melakukan analisis varian dua factor:
1. Klik: Data lalu Data Analysis lalu Anova: Two-Factor With Replication
2. Setelah diklik OK akan muncul menu berikut:
3. Pada Input Range pilih sel yang memuat data (sel A1 s/d sel D13)
4. Pada Grouped By: klik Columns
5. Pada Rows per sample, ketik 4 (banyaknya ulangan)
6. Setelah dilklik OK maka akah dihasilkan:
22
Kesimpulan tentang pengaruh setiap factor perlakuan didasarkan pada kolom F atau P-value untuk
setiap factor; pengaruh factor pupuk P ditunjukan oleh nilai F atau P-value pada baris Colums,
pengaruh factor pupuk N ditunjukan oleh nilai F atau P-value pada baris Sample, dan pengaruh factor
interaksi ditunjukan oleh nilai F atau P-value pada Interaction.