Bagaimana (dan Mengapa) Menggunakan Fungsi Pencilan di Excel
Pencilan adalah nilai yang secara signifikan lebih tinggi atau lebih rendah daripada sebagian besar nilai dalam data Anda. Saat menggunakan Excel untuk menganalisis data, pencilan dapat mengubah hasilnya. Misalnya, rata-rata rata-rata kumpulan data mungkin benar-benar mencerminkan nilai Anda. Excel menyediakan beberapa fungsi yang berguna untuk membantu mengelola outlier Anda, jadi mari kita lihat.
Contoh Cepat
Pada gambar di bawah ini, outlier cukup mudah dikenali-nilai dua ditugaskan untuk Eric dan nilai 173 ditugaskan untuk Ryan. Dalam kumpulan data seperti ini, cukup mudah untuk mengenali dan menangani outlier secara manual.
Dalam kumpulan data yang lebih besar, itu tidak akan terjadi. Mampu mengidentifikasi outlier dan menghapusnya dari perhitungan statistik adalah penting - dan itulah yang akan kita lihat bagaimana melakukannya di artikel ini.
Cara Menemukan Pencilan dalam Data Anda
Untuk menemukan pencilan dalam kumpulan data, kami menggunakan langkah-langkah berikut:
- Hitung kuartil ke-1 dan ke-3 (kita akan membicarakan apa itu hanya sedikit).
- Evaluasi kisaran interkuartil (kami juga akan menjelaskan ini sedikit lebih jauh ke bawah).
- Kembalikan batas atas dan bawah dari rentang data kami.
- Gunakan batas ini untuk mengidentifikasi titik data yang ada.
Rentang sel di sebelah kanan kumpulan data yang terlihat pada gambar di bawah ini akan digunakan untuk menyimpan nilai-nilai ini.
Mari kita mulai.
Langkah Satu: Hitung Kuartil
Jika Anda membagi data menjadi empat bagian, masing-masing set itu disebut kuartil. 25% angka terendah dalam kisaran membentuk kuartil 1, 25% berikutnya kuartil ke-2, dan seterusnya. Kami mengambil langkah ini pertama karena definisi outlier yang paling banyak digunakan adalah titik data yang lebih dari 1,5 rentang interkuartil (IQR) di bawah kuartil 1, dan 1,5 rentang interkuartil di atas kuartil ke-3. Untuk menentukan nilai-nilai itu, pertama-tama kita harus mencari tahu apa kuartil itu.
Excel menyediakan fungsi QUARTILE untuk menghitung kuartil. Ini membutuhkan dua bagian informasi: array dan quart.
= QUARTILE (array, quart)
Itu array adalah rentang nilai yang Anda evaluasi. Dan itu kuart adalah angka yang mewakili kuartil yang ingin Anda kembalikan (mis., 1 untuk 1st kuartil, 2 untuk kuartil ke-2, dan seterusnya).
catatan: Di Excel 2010, Microsoft merilis fungsi QUARTILE.INC dan QUARTILE.EXC sebagai peningkatan fungsi QUARTILE. QUARTILE lebih kompatibel ke belakang saat bekerja di beberapa versi Excel.
Mari kita kembali ke tabel contoh kita.
Untuk menghitung 1st Kuartil kita bisa menggunakan rumus berikut di sel F2.
= QUARTILE (B2: B14,1)
Saat Anda memasukkan rumus, Excel menyediakan daftar opsi untuk argumen quart.
Untuk menghitung 3rd kuartil, kita bisa memasukkan rumus seperti yang sebelumnya di sel F3, tetapi menggunakan tiga bukannya satu.
= QUARTILE (B2: B14,3)
Sekarang, kami memiliki titik data kuartil yang ditampilkan di sel.
Langkah Dua: Evaluasi Rentang Interkuartil
Rentang interkuartil (atau IQR) adalah 50% nilai tengah dalam data Anda. Itu dihitung sebagai perbedaan antara nilai kuartil 1 dan nilai kuartil ke-3.
Kita akan menggunakan formula sederhana ke dalam sel F4 yang mengurangi angka 1st kuartil dari 3rd kuartil:
= F3-F2
Sekarang, kita dapat melihat rentang interkuartil kita ditampilkan.
Langkah Tiga: Kembalikan Batas Bawah dan Atas
Batas bawah dan atas adalah nilai terkecil dan terbesar dari rentang data yang ingin kita gunakan. Nilai apa pun yang lebih kecil atau lebih besar dari nilai terikat ini adalah outlier.
Kami akan menghitung batas batas bawah dalam sel F5 dengan mengalikan nilai IQR dengan 1,5 dan kemudian mengurangkannya dari titik data Q1:
= F2- (1,5 * F4)
catatan: Kurung dalam rumus ini tidak diperlukan karena bagian perkalian akan menghitung sebelum bagian pengurangan, tetapi mereka membuat rumus lebih mudah dibaca.
Untuk menghitung batas atas dalam sel F6, kami akan mengalikan IQR dengan 1,5 lagi, tapi kali ini menambahkan ke titik data Q3:
= F3 + (1,5 * F4)
Langkah Empat: Identifikasi Outliers
Sekarang kita telah menyiapkan semua data dasar kita, saatnya untuk mengidentifikasi poin data outlying kita - yang lebih rendah dari nilai batas bawah atau lebih tinggi dari nilai batas atas.
Kami akan menggunakan fungsi OR untuk melakukan tes logis ini dan menunjukkan nilai yang memenuhi kriteria ini dengan memasukkan rumus berikut ke sel C2:
= ATAU (B2 $ F $ 6)
Kami kemudian akan menyalin nilai itu ke sel C3-C14 kami. Nilai TRUE menunjukkan pencilan, dan seperti yang Anda lihat, kami memiliki dua di data kami.
Mengabaikan Outlier saat Menghitung Rata-Rata Rata-Rata
Menggunakan fungsi QUARTILE, mari kita menghitung IQR dan bekerja dengan definisi pencilan yang paling banyak digunakan. Namun, ketika menghitung rata-rata rata-rata untuk rentang nilai dan mengabaikan outlier, ada fungsi yang lebih cepat dan lebih mudah digunakan. Teknik ini tidak akan mengidentifikasi pencilan seperti sebelumnya, tetapi akan memungkinkan kita untuk fleksibel dengan apa yang kita anggap bagian pencilan kita.
Fungsi yang kami butuhkan disebut TRIMMEAN, dan Anda dapat melihat sintaksnya di bawah ini:
= TRIMMEAN (larik, persen)
Itu array adalah rentang nilai yang ingin Anda rata-rata. Itu persen adalah persentase titik data yang akan dikecualikan dari atas dan bawah kumpulan data (Anda dapat memasukkannya sebagai persentase atau nilai desimal).
Kami memasukkan rumus di bawah ini ke dalam sel D3 dalam contoh kami untuk menghitung rata-rata dan mengecualikan 20% dari pencilan.
= TRIMMEAN (B2: B14, 20%)
Di sana Anda memiliki dua fungsi yang berbeda untuk menangani pencilan. Apakah Anda ingin mengidentifikasi mereka untuk beberapa kebutuhan pelaporan atau mengecualikan mereka dari perhitungan seperti rata-rata, Excel memiliki fungsi yang sesuai dengan kebutuhan Anda.
