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 hasil. Misalnya, rata-rata rata-rata kumpulan data mungkin benar-benar mencerminkan nilai Anda. Excel menyediakan beberapa fungsi yang berguna untuk membantu mengelola pencilan Anda, jadi mari kita lihat.

Contoh Cepat

Pada gambar di bawah, pencilan cukup mudah dikenali — nilai dua ditetapkan ke Eric dan nilai 173 ditetapkan ke Ryan. Dalam kumpulan data seperti ini, cukup mudah untuk menemukan dan menangani pencilan tersebut secara manual.

Dalam kumpulan data yang lebih besar, itu tidak akan terjadi. Mampu mengidentifikasi pencilan dan menghapusnya dari penghitungan statistik itu penting — dan itulah yang akan kita lihat bagaimana melakukannya di artikel ini.

Cara Menemukan Pencilan di Data Anda

Untuk menemukan outlier dalam kumpulan data, kami menggunakan langkah-langkah berikut:

  1. Hitung kuartil ke-1 dan ke-3 (kita akan membahasnya sebentar lagi).
  2. Evaluasi rentang interkuartil (kami juga akan menjelaskannya sedikit lebih jauh).
  3. Kembalikan batas atas dan bawah rentang data kami.
  4. Gunakan batasan ini untuk mengidentifikasi titik data terpencil.

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 Pertama: Hitung Kuartilnya

Jika Anda membagi data menjadi beberapa bagian, setiap kumpulan tersebut disebut kuartil. 25% angka terendah dalam rentang tersebut membentuk kuartil pertama, 25% berikutnya adalah kuartil kedua, dan seterusnya. Kami mengambil langkah ini terlebih dahulu karena definisi pencilan yang paling banyak digunakan adalah titik data yang lebih dari 1,5 rentang interkuartil (IQR) di bawah kuartil pertama, dan 1,5 rentang interkuartil di atas kuartil ke-3. Untuk menentukan nilai-nilai itu, pertama-tama kita harus mencari tahu apa itu kuartil.

Excel menyediakan fungsi QUARTILE untuk menghitung kuartil. Ini membutuhkan dua bagian informasi: array dan quart.

= QUARTILE (larik, liter)

The Array adalah rentang nilai yang Anda mengevaluasi. Dan kuart adalah angka yang mewakili kuartil yang ingin Anda kembalikan (misalnya, 1 untuk kuartil pertama, 2 untuk kuartil kedua, dan seterusnya).

Catatan: Di Excel 2010, Microsoft meluncurkan fungsi QUARTILE.INC dan QUARTILE.EXC sebagai penyempurnaan fungsi QUARTILE. QUARTILE lebih kompatibel ke belakang saat bekerja di beberapa versi Excel.

Mari kembali ke tabel contoh kita.

Untuk menghitung Kuartil ke-1 kita dapat menggunakan rumus berikut di sel F2.

= KUARTIL (B2: B14,1)

Saat Anda memasukkan rumus, Excel menyediakan daftar opsi untuk argumen quart.

Untuk menghitung kuartil ke-3, kita dapat memasukkan rumus seperti yang sebelumnya di sel F3, tetapi menggunakan tiga, bukan satu.

= KUARTIL (B2: B14,3)

Sekarang, kita punya poin data kuartil yang ditampilkan di sel.

Langkah Kedua: Evaluasi Rentang Interkuartil

Rentang interkuartil (atau IQR) adalah 50% tengah dari nilai dalam data Anda. Ini dihitung sebagai selisih antara nilai kuartil ke-1 dan nilai kuartil ke-3.

Kita akan menggunakan rumus sederhana ke dalam sel F4 yang mengurangi kuartil pertama dari kuartil ke-3:

= F3-F2

Sekarang, kita dapat melihat jangkauan 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. Setiap nilai yang lebih kecil atau lebih besar dari nilai terikat ini adalah pencilan.

Kami akan menghitung batas bawah di sel F5 dengan mengalikan nilai IQR dengan 1,5 dan kemudian menguranginya dari titik data Q1:

= F2- (1,5 * F4)

Catatan: Tanda kurung dalam rumus ini tidak diperlukan karena bagian perkalian akan dihitung sebelum bagian pengurangan, tetapi tanda kurung tersebut membuat rumus lebih mudah dibaca.

Untuk menghitung batas atas di sel F6, kita akan mengalikan IQR dengan 1,5 lagi, tetapi kali ini menambahkannya ke titik data Q3:

= F3 + (1,5 * F4)

Langkah Empat: Identifikasi Pencilan

Sekarang setelah kita menyiapkan semua data yang mendasarinya, inilah waktunya untuk mengidentifikasi titik data terluar 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 dalam sel C2:

= ATAU (B2 $ F $ 6)

Kami kemudian akan menyalin nilai itu ke dalam sel C3-C14 kami. Nilai TRUE menunjukkan pencilan, dan seperti yang Anda lihat, kita punya dua di data kita.

Mengabaikan Pencilan saat Menghitung Rata-Rata Rata-Rata

Menggunakan fungsi QUARTILE, mari kita hitung IQR dan bekerja dengan definisi pencilan yang paling banyak digunakan. Namun, saat menghitung rata-rata rata-rata untuk suatu rentang nilai dan mengabaikan pencilan, ada fungsi yang lebih cepat dan lebih mudah untuk digunakan. Teknik ini tidak akan mengidentifikasi pencilan seperti sebelumnya, tetapi akan memungkinkan kita untuk fleksibel dengan apa yang mungkin kita anggap sebagai bagian pencilan kita.

Fungsi yang kita butuhkan disebut TRIMMEAN, dan Anda dapat melihat sintaksnya di bawah ini:

= TRIMMEAN (larik, persen)

The Array adalah rentang nilai yang ingin Anda rata-rata. The persen adalah persentase titik data untuk mengecualikan dari atas dan bawah dari kumpulan data (Anda dapat memasukkannya sebagai persentase atau nilai desimal).

Kami memasukkan rumus di bawah ini ke sel D3 dalam contoh kami untuk menghitung rata-rata dan mengecualikan 20% pencilan.

= TRIMMEAN (B2: B14, 20%)

Di sana Anda memiliki dua fungsi berbeda untuk menangani pencilan. Apakah Anda ingin mengidentifikasinya untuk beberapa kebutuhan pelaporan atau mengecualikannya dari penghitungan seperti rata-rata, Excel memiliki fungsi yang sesuai dengan kebutuhan Anda.