Bagaimana (dan Mengapa) Menggunakan Fungsi Outliers di Excel

Outlier adalah nilai yang jauh lebih tinggi atau lebih rendah daripada kebanyakan nilai dalam data anda. Semasa menggunakan Excel untuk menganalisis data, outliers dapat membelokkan hasilnya. Contohnya, rata-rata rata set data mungkin benar-benar menggambarkan nilai anda. Excel menyediakan beberapa fungsi yang berguna untuk membantu menguruskan luaran anda, jadi mari kita lihat.

Contoh Pantas

Dalam gambar di bawah, outlier cukup mudah dilihat — nilai dua diberikan kepada Eric dan nilai 173 diberikan kepada Ryan. Dalam kumpulan data seperti ini, cukup mudah untuk melihat dan menangani penyekat tersebut secara manual.

Dalam sekumpulan data yang lebih besar, itu tidak akan berlaku. Mampu mengenal pasti penyekat dan menghapusnya dari pengiraan statistik adalah penting - dan itulah yang akan kita perhatikan bagaimana untuk dilakukan dalam artikel ini.

Cara Mencari Outliers dalam Data anda

Untuk mencari outliers dalam set data, kami menggunakan langkah-langkah berikut:

  1. Hitungkan kuartil pertama dan ke-3 (kita akan membincangkan apa yang ada dalam sekejap).
  2. Nilai julat interkuartil (kami juga akan menerangkannya sedikit lebih jauh).
  3. Kembalikan batas atas dan bawah julat data kami.
  4. Gunakan had ini untuk mengenal pasti titik data terpencil.

Julat sel di sebelah kanan kumpulan data yang dilihat pada gambar di bawah akan digunakan untuk menyimpan nilai-nilai ini.

Mari kita mulakan.

Langkah Pertama: Hitung Kuartil

Sekiranya anda membahagikan data anda menjadi satu perempat, setiap set tersebut dipanggil kuartil. 25% nombor terendah dalam julat membentuk kuartil pertama, 25% kuartil berikutnya, dan seterusnya. Kami mengambil langkah ini terlebih dahulu kerana definisi outlier yang paling banyak digunakan adalah titik data yang melebihi 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 mengetahui apa itu kuartil.

Excel menyediakan fungsi KUANTITI untuk mengira kuartil. Ia memerlukan dua maklumat: susunan dan kuart.

= KUANTITI (susunan, kuart)

Yang mudah adalah pelbagai nilai-nilai yang anda menilai. Dan kuart adalah nombor yang mewakili kuartil yang ingin anda kembalikan (contohnya, 1 untuk kuartil pertama, 2 untuk kuartil ke-2, dan seterusnya).

Catatan: Di Excel 2010, Microsoft melepaskan fungsi QUARTILE.INC dan QUARTILE.EXC sebagai penambahbaikan pada fungsi QUARTILE. QUARTILE lebih serasi ke belakang ketika bekerja di pelbagai versi Excel.

Mari kembali ke jadual contoh kami.

Untuk mengira Kuartil Pertama, kita boleh menggunakan formula berikut dalam sel F2.

= KUANTITI (B2: B14,1)

Semasa anda memasukkan formula, Excel menyediakan senarai pilihan untuk argumen kuart.

Untuk mengira kuartil ke-3, kita dapat memasukkan formula seperti yang sebelumnya di sel F3, tetapi menggunakan tiga bukannya satu.

= KUANTITI (B2: B14,3)

Sekarang, kami mendapat titik data kuartil yang dipaparkan di dalam sel.

Langkah Kedua: Nilai Julat Interkuartil

Julat interkuartil (atau IQR) adalah 50% pertengahan nilai dalam data anda. Ia dikira sebagai perbezaan antara nilai kuartil pertama dan nilai kuartil ke-3.

Kita akan menggunakan formula mudah ke dalam sel F4 yang mengurangkan kuartil pertama dari kuartil ke-3:

= F3-F2

Sekarang, kita dapat melihat rentang interkuartil kita dipaparkan.

Langkah Ketiga: Kembalikan Had Bawah dan Atas

Batas bawah dan atas adalah nilai terkecil dan terbesar dari julat data yang ingin kita gunakan. Sebarang nilai yang lebih kecil atau lebih besar daripada nilai terikat ini adalah garis besar

Kami akan mengira had had bawah dalam sel F5 dengan mengalikan nilai IQR dengan 1.5 dan kemudian mengurangkannya dari titik data Q1:

= F2- (1.5 * F4)

Catatan: Tanda kurung dalam formula ini tidak diperlukan kerana bahagian pendaraban akan dikira sebelum bahagian pengurangan, tetapi bahagian tersebut menjadikan formula lebih mudah dibaca.

Untuk mengira batas atas dalam sel F6, kita akan mengalikan IQR dengan 1.5 lagi, tetapi kali ini menambahkannya ke titik data Q3:

= F3 + (1.5 * F4)

Langkah Keempat: Kenal pasti Outliers

Sekarang setelah kami menyiapkan semua data asas kami, inilah masanya untuk mengenal pasti titik data terpencil kami — titik yang lebih rendah daripada nilai batas bawah atau lebih tinggi daripada nilai batas atas

Kami akan menggunakan fungsi OR untuk melakukan ujian logik ini dan menunjukkan nilai yang memenuhi kriteria ini dengan memasukkan formula berikut ke dalam sel C2:

= ATAU (B2 $ F $ 6)

Kami kemudian akan menyalin nilai tersebut ke dalam sel C3-C14 kami. Nilai BENAR menunjukkan orang luar, dan seperti yang anda lihat, kami mempunyai dua data kami.

Mengabaikan Outliers ketika Mengira Purata Purata

Dengan menggunakan fungsi QUARTILE mari kita mengira IQR dan bekerja dengan definisi outlier yang paling banyak digunakan. Namun, ketika menghitung rata-rata rata-rata untuk julat nilai dan mengabaikan outliers, ada fungsi yang lebih cepat dan lebih mudah digunakan. Teknik ini tidak akan mengenal pasti orang luar seperti sebelumnya, tetapi akan memungkinkan kita untuk bersikap fleksibel dengan apa yang kita anggap bahagian luar kita.

Fungsi yang kita perlukan dipanggil TRIMMEAN, dan anda dapat melihat sintaksnya di bawah:

= TRIMMEAN (susunan, peratus)

Yang mudah adalah pelbagai nilai yang anda mahu purata. The peratus ialah peratusan titik data untuk mengecualikan daripada bahagian atas dan bawah set data (anda boleh memasukkannya sebagai peratusan atau nilai perpuluhan).

Kami memasukkan formula di bawah ke dalam sel D3 dalam contoh kami untuk mengira purata dan mengecualikan 20% daripada outlier.

= TRIMMEAN (B2: B14, 20%)

Di sana anda mempunyai dua fungsi yang berbeza untuk menangani outlier. Sama ada anda ingin mengenalinya untuk beberapa keperluan pelaporan atau mengecualikannya dari pengiraan seperti rata-rata, Excel mempunyai fungsi yang sesuai dengan keperluan anda.