Download - Modul Excel Statistik.docx
Excel Statistik
Menggunakan Excel Statistik Untuk Ungrouped Data
Sebagai suatu software pengolah angka, tentunya Microsoft Excel dapat
digunakan untuk mengerjakan permasalahan yang bersifat matematis, salah satu
dari permasalahan matematis yang bisa dikerjakan dengan bantuan MS Excel
adalah statistika. Excel menyediakan fungsi-fungsi statistic (statistical category) yang
dapat digunakan untuk membantu menyelesaikan penelitian, skripsi, survey, dan
kegiatan lainnya yang membutuhkan data kuantitatif.
Dalam bagian sebelumnya tentu kalian sudah mengerti cara menggunakan
formula dalam MS Excel, dengan begitu, dalam bagian ini kalian hanya perlu
memelajari formula yang digunakan untuk menyelesaikan permasalahan statistic
dasar dengan MS Excel (2013).
Berikut adalah formula yang sering digunakan dalam Excel Statistic :
N
o
Fungsi Statisik Kegunaan
1 COUNT Mencari banyak data dari suatu suatu range
data.
2 SUM Mencari jumlah total dari suatu range data.
3 AVERAGE Mencari nilai rata-rata dari suatu range data.
4 MAX Mencari nilai terbesar dari suatu range data.
5 MIN Mencari nilai terkecil dari suatu range data.
6 STDEV Mencari standar deviasi/standar baru dari suatu
range data
7 MODE Mencari nilai yang paling sering muncul dari
suatu range data.
8 MEDIAN Mencari nilai tengah dari suatu range data
Untuk memasukan fungsi/formula statistic yang akan digunakan, salah satu
cara yang bisa dilakukan, yaitu sebagai berikut :
1. Pilih tab menu formulas seperti pada gambar berikut :
2. Klik Insert Function pada tab menu Formulas, maka akan muncul tampilan
sebagai berikut :
3. Pilih salah satu fungsi statistic yang ingin digunakan.
4. Pilih area yang ingin dihitung oleh fungsi tersebut, seperti ini
5. Klik OK
Tips :
1. Selain dengan cara di atas, statistic function dapat juga digunakan dengan
cara seperti berikut :
=fungsi(range)
2. Dalam box Insert Function, kamu bisa memilih kategori function agar lebih
sesuai dengan kebutuhan, seperti ini :
Contoh Soal Grouped Data
Pak Budi, seorang seorang guru sekolah menengah pertama baru saja
mengadakan ujian tengah semester untuk kelasnya yang berjumlah 30 orang,
dengan nilai sebagai berikut :
72 74 68 65 80 90
79 76 50 85 65 55
56 45 46 56 70 80
95 85 75 80 70 55
55 60 66 45 55 80
Dari data tersebut, pak Budi ingin mengetahui rata-rata, nilai tertinggi, nilai
terendah, rentang, median, modus, dan simpangan bakunya. Jika batas kelulusan
dari UTS merupakan rata-rata nilai keseluruhan, berapa siswa yang lulus dan berapa
yang harus melakukan remedial. Selesaikanlah beberapa fungsi tersebut untuk guna
memperoleh informasi yang dibutuhkannya dengan menggunakan fungsi statistik
pada Microsoft Excel 2013
Menggunakan Excel Statistik Untuk Grouped Data
Dalam penyelesaian permasalahan grouped data menggunakan Excel, kita
tidak bisa langsung menggunakan formula seperti halnya penyelesaian masalah
dalam ungrouped data. Penghitungan statistic grouped data memerlukan kita
mengelompokan data-data yang berasal dari ungrouped data kedalam sebuah table
frekuensi dengan rumus tertentu.
Akan tetapi, meski kita tidak bisa menggunakan formula untuk
menyelesaikan permasalahan grouped data, penggunaan MS Excel dapat
membantu menyelesaikan perhitungan dengan lebih cepat, terutama jika kita
memiliki data yang cukup banyak, yang biasanya memerlukan waktu yang lama jika
dihitung secara manual.
Langkah-langkah dalam mengerjakan perhitungan statistic grouped data
adalah sebagai berikut :
1. Menentukan jumlah kelas
Rumus Jumlah Kelas : 1+3.33 log n
Dimana n adalah banyak data yang tersedia.
2. Menentukan panjang kelas
Rumus panjang kelas : range
jumlah kelas
Dimana range adalah selisih nilai data terbesar dan nilai data terkecil.
3. Menentukan titik bawah kelas ke-n
Rumus titik bawah kelas ke-n : (batas bawah kelas ke−n )−0.5
Dimana batas bawah merupakan nilai terendah dari kelas tersebut.
4. Menentukan titik atas kelas ke-n
Rumus titik atas kelas ke-n : (batas atas kelas ke−n )+0.5
Dimana batas atas merupakan nilai tertinggi dari kelas tersebut.
5. Menentukan titik tengah kelas ke-n (Xi)
Rumus titik tengah kelas ke-n:
(batas bawahkelas ke−n )+(batas atas kelas ke−n)2
6. Membuat table frekuensi kumulatif
Table frekuensi kumulatif merupakan table yang memuat jumlah
frekuensi data. Table frekuensi kumulatif terlihat seperti ini
Data Frekuensi Frekuensi
Kumulatif
1-10 f1 f1
11-20 f2 f1 + f2
21-30 f3 f1 + f2 + f3
Setelah mendapatkan hasil dari perhitungan-perhitungan di atas, barulah
kita bisa mencari mean, median, dan modus dari data grouped data tersebut.
Berikut adalah rumus-rumus yang perlu kita gunakan untuk mencari mean, median,
dan modus :
1. Mean
Untuk mencari nilai mean, atau rata-rata dari grouped data, kita perlu
menggunakan rumus sebagai berikut :
Mean = ∑ fiXi
n
Dimana fi adalah frekuensi kelas ke-n dan Xi adalah titik tengah kelas ke-n
2. Median
Untuk mencari median, yaitu nilai tengah dari grouped data, rumus di
bawah ini perlu digunakan :
Median = b+( n2−Ff ) pDimana :
b = batas bawah kelas median
F = frekuensi kumulatif sebelum kelas median
kelas median merupakan kelas dimana data ke n2
berada
n = frekuensi total
f = frekuensi di kelas median
p = panjang kelas
3. Modus
Untuk mencari modus, nilai yang paling banyak muncul dalam suatu
grouped data, diperlukan penggunaan rumus berikut :
Modus = b+( b1b1+b2 ) p
Dimana :
b = batas bawah kelas modus
kelas modus merupakan kelas dengan frekuensi terbesar
b1 = selisih antara frekuensi kelas modus dengan kelas sebelumnya
b2 = selisih antara frekuensi kelas modus dengan kelas setelahnya
Contoh Soal Grouped Data
Pak Budi, seorang guru sekolah menengah pertama baru saja mengadakan
ujian tengah semester untuk kelasnya yang berjumlah 30 orang, dengan nilai
sebagai berikut :
72 74 68 65 80 90
79 76 50 85 65 55
56 45 46 56 70 80
95 85 75 80 70 55
55 60 66 45 55 80
Dari data tersebut, pak Budi ingin mengetahui rata-rata, nilai tertinggi, nilai
terendah, rentang, median, modus, dan simpangan bakunya. Jika batas kelulusan
dari UTS merupakan rata-rata nilai keseluruhan, berapa siswa yang lulus dan berapa
yang harus melakukan remedial. Selesaikanlah beberapa fungsi tersebut untuk guna
memperoleh informasi yang dibutuhkannya dengan menggunakan fungsi statistik
pada Microsoft Excel 2013
Menggunakan Analysis Toolpack Pada Microsoft Excel 2013 Untuk
Perhitungan Statistic
Dalam aplikasi MS Excel, terdapat salah satu fitur yang bisa digunakan untuk
mempermudah kita dalam mengerjakan analisis data statistic. Namun secara
default fitur ini tidak diaktifkan oleh MS Excel.
Langkah-langkah untuk mengaktifkan fitur ini adalah sebagai berikut :
1. Klik File, lalu klik Excel Option, maka akan muncul box excel options
2. Klik Add-ins, pada Manage Box, pilih Excel Add-ins kemudian klik Go
3. Jika tampil peringatan bahwa add-ins belum terinstall, klik yes untuk
menginstall add-ins dan tunggu sampai proses instalasi selesai, kemudian
klik go untuk melihat daftar add-ins yang sudah terinstall. Jika sudah
terinstall maka akan muncul box seperti berikut, ceklis Add-Ins Analysis
Toolpack untuk mengaktifkannya.
4. Sekarang analysis toolpack sudah aktif pada tab Data
Contoh Soal Analysis Toolpack
Berdasarkan contoh soal untuk perhitungan ungroup data di atas, kita akan
mencoba menyelesaikan soal tersebut dengan menggunakan fitur analysis toolpack,
langkah-langkah pengerjaannya adalah sebagai berikut:
1. Pilih tab data kemudia klik Data Analysis, maka akan muncul dialog box
seperti di bawah ini
2. Pilih descriptive statistics dan sorot range yang akan dihitung (dalam soal ini
adalah nilai)
3. Di output options, pilih New Worksheet untuk menampilkan hasil di
worksheet baru, dan ceklis 4 box yang berada di bawah (Summary Statistics,
Confident level for mean 95%, Kth Largest, Kth Smallest)
4. Klik OK
5. Maka akan muncul tampilan seperti ini di worksheet baru, bandingkan
dengan jawaban yang menggunakan formula
Membuat Histogram Menggunakan Analysis Toolpack
Dengan menggunakan analysis toolpack, kita bisa membuat histogram untuk
menampilkan persebaran grouped data dalam bentuk grafik. Langkah-langkah
pembuatan histogram dengan menggunakan analysis toolpack adalah sebagai
berikut :
1. Pilih tab Data, lalu klik Data Analysis, maka akan muncul dialog box.
2. Pilih Histogram pada dialog box analysis tools
3. Isikan Input Range (dalam hal ini nilai) dan Bin Range (dalam hal ini batas
atas tiap kelas)
4. Pilih new worksheet dan ceklis ketiga checkbox yang ada di bawah (Pareto,
Cumulative percentage, Chart Output)
5. Klik OK
Latihan Bersama
Berikut adalah data penjualan sebuah perusahaan furniture yang
memiliki 50 orang sales selama satu bulan :
138 118 140 142 142 143 146 147 157 164
118 124 125 126 128 128 146 154 158 165
132 145 135 135 144 145 146 155 161 168
136 137 138 140 144 145 147 155 162 173
148 149 150 150 150 152 153 156 163 178
Berdasarkan data tersebut tentukanlah :
1. Kategori prestasi dari karyawan perusahaan tersebut (Menggunakan If)
dengan kriteria :
- Kategori “Cukup” untuk sales yang menjual >110 unit
- Kategori “Baik” untuk sales yang menjual >130 unit
- Kategori “Sangat Baik” untuk sales yang menjual >160 unit
2. Tentukan jumlah sales di setiap kategorinya (Menggunakan CountIf)
3. Hitung rata-rata, median, modus, penjualan tertinggi, penjualan terendah,
rentang, dan simpangan bakunya.
4. Buatlah distribusi frekuensi group dengan langkah sebagai berikut :
- Tentukan jumlah kelas
- Tentukan panjang kelas
- Tentukan tepi bawah kelas ke-n
- Tentukan tepi atas kelas ke-n
- Tentukan titik tengah dari masing-masing kelas(Xi)
5. Hitung rata-rata, median, dan modus untuk Grouped Data tersebut
6. Dengan menggunakan Analysis Toolpack, buatlah Statistik Deskriptif dan
Histogramnya
Jawaban :
Penjuala
n
Keteranga
n
138 Baik
118 Cukup
118 Cukup
124 Cukup
125 Cukup
126 Cukup
128 Cukup
128 Cukup
132 Baik
145 Baik
135 Baik
135 Baik
136 Baik
137 Baik
138 Baik
140 Baik
140 Baik
142 Baik
142 Baik
143 Baik
144 Baik
144 Baik
145 Baik
145 Baik
146 Baik
146 Baik
146 Baik
147 Baik
147 Baik
148 Baik
149 Baik
150 Baik
150 Baik
150 Baik
152 Baik
153 Baik
154 Baik
155 Baik
155 Baik
156 Baik
157 Baik
158 Baik
161 Sangat Baik
162 Sangat Baik
163 Sangat Baik
164 Sangat Baik
165 Sangat Baik
168 Sangat Baik
173 Sangat Baik
178 Sangat Baik
Rata-Rata 146.02
Median 146
Modus 145
Penjualan Tertinggi 178
Penjualan Terendah 118
Rentang 60
Standar Deviasi 13.43387
Jumlah Sales Yang Berprestasi
Sangat Baik 8
Baik 35
Cukup 7
No Kelas Tepi Bawah Tepi Atas Fi Xi FiXi Frekuensi Kumulatif
1 118 126 117.5 126.5 5 122 610 5
2 127 135 126.5 135.5 7 131 917 12
3 136 144 135.5 144.5 10 140 1400 23
4 145 153 144.5 153.5 15 149 2235 37
5 154 162 153.5 162.5 7 158 1106 44
6 163 171 162.5 171.5 4 167 668 48
7 172 178 171.5 178.5 2 175 350 50
Total 50 7286
Rata-Rata 145.72
Median
146.210
9
Modus 148.493
Banyak Kelas
6.60660
1
Panjang
Kelas
9.08182
6
Column1
Mean 146.02
Standard Error
1.89983
7
Median 146
Mode 145
Standard Deviation
13.4338
7
Sample Variance 180.469
Kurtosis -0.04817
Skewness
0.01945
2
Range 60
Minimum 118
Maximum 178
Sum 7301
Count 50
Largest(1) 178
Smallest(1) 118
Confidence
Level(95.0%)
3.81786
5
Bin
Frequen
cy
Cumulative
% Bin
Frequen
cy
Cumulative
%
126 5 10.00% 153 15 30.00%
135 5 20.00% 144 11 52.00%
144 11 42.00% 162 8 68.00%
153 15 72.00% 126 5 78.00%
162 8 88.00% 135 5 88.00%
171 4 96.00% 171 4 96.00%
178 2 100.00% 178 2 100.00%
More 0 100.00% More 0 100.00%
153162
135178
05
101520
0.00%
40.00%
80.00%
120.00%
Histogram
FrequencyCumulative %
Bin
Freq
uenc
y