Bagaimana (dan Kenapa) Menggunakan Fungsi Outlier di Excel
Outlier adalah nilai yang jauh lebih tinggi atau lebih rendah daripada kebanyakan nilai dalam data anda. Apabila menggunakan Excel untuk menganalisis data, outliers boleh menyerang hasilnya. Sebagai contoh, purata purata satu set data mungkin benar-benar mencerminkan nilai anda. Excel menyediakan beberapa fungsi yang berguna untuk membantu mengatasi kelebihan anda, jadi mari lihat.
Contoh Pantas
Dalam imej di bawah ini, outlier cukup mudah dilihat-nilai dua yang diberikan kepada Eric dan nilai 173 yang diberikan kepada Ryan. Dalam satu set data seperti ini, ia cukup mudah untuk melihat dan menangani orang-orang yang keluar secara manual.
Dalam satu set data yang lebih besar, itu tidak akan berlaku. Mampu mengenal pasti kelebihan dan menghilangkannya daripada pengiraan statistik adalah penting-dan itulah yang kita akan cari bagaimana untuk melakukan dalam artikel ini.
Cara Cari Pengecualian dalam Data anda
Untuk mencari penjelasan dalam set data, kami menggunakan langkah-langkah berikut:
- Kira kuartil 1 dan ke-3 (kita akan bercakap tentang apa yang mereka hanya sedikit).
- Evaluasi pelbagai interquartile (kami juga akan menerangkan sedikit ini ke bawah).
- Kembali batas atas dan bawah julat data kami.
- Gunakan batas-batas ini untuk mengenal pasti titik data terpencil.
Julat sel di sebelah kanan set data yang dilihat pada imej di bawah akan digunakan untuk menyimpan nilai-nilai ini.
Mari kita mulakan.
Langkah Pertama: Kira Kuartil
Jika anda membahagikan data anda ke dalam kuarters, setiap set tersebut dipanggil kuartil. Yang paling rendah 25% daripada bilangan dalam julat membentuk kuartil pertama, seterusnya 25% kuartil ke-2, dan sebagainya. Kami mengambil langkah ini terlebih dahulu kerana takrif yang paling banyak digunakan bagi outlier adalah titik data yang lebih daripada 1.5 julat interquartile (IQRs) di bawah kuartil 1, dan 1.5 interquartile di atas kuartil ke-3. Untuk menentukan nilai-nilai tersebut, kita perlu terlebih dahulu mengetahui apa yang kuartil tersebut.
Excel menyediakan fungsi QUARTILE untuk mengira kuartil. Ia memerlukan dua maklumat: array dan kuartal.
= QUARTILE (array, kuart)
The array adalah pelbagai nilai yang anda menilai. Dan juga quart adalah nombor yang mewakili kuartil yang anda ingin pulangkan (mis., 1 untuk 1st kuartil, 2 untuk kuartil ke-2, dan sebagainya).
Catatan: Di Excel 2010, Microsoft melancarkan fungsi QUARTILE.INC dan QUARTILE.EXC sebagai penambahbaikan fungsi QUARTILE. QUARTILE adalah serasi yang lebih mundur ketika bekerja di pelbagai versi Excel.
Mari kita kembali ke meja contoh kami.
Untuk mengira 1st Quartile kita boleh menggunakan formula berikut dalam sel F2.
= QUARTILE (B2: B14,1)
Semasa anda memasukkan formula, Excel menyediakan senarai pilihan untuk argumen quart.
Untuk mengira 3rd kuartil, kita boleh memasukkan formula seperti yang sebelumnya dalam sel F3, tetapi menggunakan tiga bukannya satu.
= QUARTILE (B2: B14,3)
Sekarang, kami mempunyai mata data kuartil yang dipaparkan di dalam sel.
Langkah Dua: Menilai Julian Interquartile
Julian interquartile (atau IQR) adalah 50% nilai tengah dalam data anda. Ia dikira sebagai perbezaan antara nilai kuartil 1 dan nilai kuartil ke-3.
Kami akan menggunakan formula mudah ke dalam sel F4 yang menolak 1st kuartil dari 3rd kuartil:
= F3-F2
Sekarang, kita dapat melihat pelbagai interquartile kami dipaparkan.
Langkah Tiga: Kembalikan Bound yang Rendah dan Atas
Had-baris yang lebih rendah dan atas adalah nilai terkecil dan terbesar dalam julat data yang ingin kita gunakan. Mana-mana nilai yang lebih kecil atau lebih besar daripada nilai-nilai terikat ini adalah pengingkaran.
Kami akan mengira had terikat bawah dalam sel F5 dengan mendarabkan nilai IQR sebanyak 1.5 dan kemudian menolaknya dari titik data Q1:
= F2- (1.5 * F4)
Catatan: Kurungan dalam formula ini tidak diperlukan kerana bahagian pendaraban akan dikira sebelum bahagian penolakan, tetapi mereka menjadikan formula lebih mudah dibaca.
Untuk menghitung teratas di sel F6, kami akan membiak IQR sebanyak 1.5 lagi, tetapi kali ini Tambah ia ke titik data Q3:
= F3 + (1.5 * F4)
Langkah Empat: Kenal pasti Outliers
Sekarang bahawa kami telah mendapat semua data asas kami, tiba masanya untuk mengenal pasti titik data terpencil kami-yang lebih rendah daripada nilai terikat yang lebih rendah atau lebih tinggi daripada nilai teratas.
Kami akan menggunakan fungsi ATAU untuk melaksanakan 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 itu ke dalam sel C3-C14 kami. Nilai TRUE menunjukkan luaran, dan seperti yang anda lihat, kami mempunyai dua dalam data kami.
Mengabaikan Outliers apabila Mengira Purata Purata
Menggunakan fungsi QUARTILE mari kita menghitung IQR dan bekerjasama dengan takrif yang paling banyak digunakan bagi seorang outlier. Walau bagaimanapun, apabila mengira purata min bagi pelbagai nilai dan mengabaikan pengecualian, terdapat fungsi yang lebih cepat dan mudah digunakan. Teknik ini tidak akan mengenal pasti lebih jauh seperti sebelum ini, tetapi ia akan membolehkan kita menjadi fleksibel dengan apa yang kita boleh pertimbangkan bahagian penjelasan kami.
Fungsi yang kami perlukan dipanggil TRIMMEAN, dan anda boleh melihat sintaksinya di bawah:
= TRIMMEAN (pelbagai, peratus)
The array adalah julat nilai yang anda mahu rata-rata. The peratus adalah peratusan titik data untuk mengecualikan dari 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 penglihatan.
= TRIMMEAN (B2: B14, 20%)
Di sana anda mempunyai dua fungsi yang berbeza untuk mengendalikan outlier. Sama ada anda ingin mengenal pasti mereka untuk beberapa keperluan pelaporan atau mengecualikan mereka daripada pengiraan seperti purata, Excel mempunyai fungsi untuk memenuhi keperluan anda.
