วิธี (และทำไม) ในการใช้ฟังก์ชัน Outliers ใน Excel

ค่าผิดปกติคือค่าที่สูงกว่าหรือต่ำกว่าค่าส่วนใหญ่ในข้อมูลของคุณอย่างมีนัยสำคัญ เมื่อใช้ Excel เพื่อวิเคราะห์ข้อมูลค่าผิดปกติอาจบิดเบือนผลลัพธ์ได้ ตัวอย่างเช่นค่าเฉลี่ยเฉลี่ยของชุดข้อมูลอาจสะท้อนถึงค่าของคุณอย่างแท้จริง Excel มีฟังก์ชันที่มีประโยชน์บางอย่างเพื่อช่วยในการจัดการค่าผิดปกติของคุณลองมาดูกัน

ตัวอย่างด่วน

ในภาพด้านล่างค่าผิดปกติสามารถมองเห็นได้ง่ายพอสมควรนั่นคือค่าของสองค่าที่กำหนดให้กับ Eric และค่า 173 ที่กำหนดให้กับ Ryan ในชุดข้อมูลเช่นนี้ง่ายพอที่จะระบุและจัดการกับค่าผิดปกติเหล่านั้นด้วยตนเอง

ในชุดข้อมูลขนาดใหญ่จะไม่เป็นเช่นนั้น ความสามารถในการระบุค่าผิดปกติและลบออกจากการคำนวณทางสถิติเป็นสิ่งสำคัญและนั่นคือสิ่งที่เราจะดูวิธีทำในบทความนี้

วิธีค้นหา Outliers ในข้อมูลของคุณ

ในการค้นหาค่าผิดปกติในชุดข้อมูลเราใช้ขั้นตอนต่อไปนี้:

1. คำนวณควอร์ไทล์ที่ 1 และ 3 (เราจะพูดถึงสิ่งที่อยู่ในจำนวนเล็กน้อย)
2. ประเมินช่วงระหว่างควอไทล์ (เราจะอธิบายต่อไปอีกเล็กน้อย)
3. ส่งกลับขอบเขตบนและล่างของช่วงข้อมูลของเรา
4. ใช้ขอบเขตเหล่านี้เพื่อระบุจุดข้อมูลภายนอก

ช่วงเซลล์ทางด้านขวาของชุดข้อมูลที่เห็นในภาพด้านล่างจะถูกใช้เพื่อเก็บค่าเหล่านี้

มาเริ่มกันเลย.

ขั้นตอนที่หนึ่ง: คำนวณควอไทล์

หากคุณแบ่งข้อมูลออกเป็นไตรมาสแต่ละชุดจะเรียกว่าควอไทล์ 25% ต่ำสุดของตัวเลขในช่วงประกอบด้วยควอไทล์ที่ 1 25% ถัดไปของควอร์ไทล์ที่ 2 และอื่น ๆ เราทำตามขั้นตอนนี้ก่อนเนื่องจากคำจำกัดความที่ใช้กันอย่างแพร่หลายของค่าผิดปกติคือจุดข้อมูลที่มากกว่า 1.5 ช่วงระหว่างควอไทล์ (IQRs) ต่ำกว่าควอไทล์ที่ 1 และ 1.5 ช่วงระหว่างควอไทล์เหนือควอไทล์ที่ 3 ในการกำหนดค่าเหล่านั้นอันดับแรกเราต้องหาว่าควอไทล์คืออะไร

Excel มีฟังก์ชัน QUARTILE เพื่อคำนวณควอไทล์ ต้องใช้ข้อมูลสองส่วนคืออาร์เรย์และควอร์ต

= QUARTILE (อาร์เรย์ควอร์ต)

อาร์เรย์เป็นช่วงของค่าที่คุณกำลังประเมิน และควอร์ตคือตัวเลขที่แสดงถึงควอร์ไทล์ที่คุณต้องการส่งคืน (เช่น 1 สำหรับควอร์ไทล์ที่ 1, 2 สำหรับควอร์ไทล์ที่ 2 และอื่น ๆ )

หมายเหตุ:ใน Excel 2010 Microsoft ได้เปิดตัวฟังก์ชัน QUARTILE.INC และ QUARTILE.EXC เป็นการปรับปรุงฟังก์ชัน QUARTILE QUARTILE เข้ากันได้มากกว่าเมื่อทำงานกับ Excel หลายเวอร์ชัน

กลับไปที่ตารางตัวอย่างของเรา

ในการคำนวณควอไทล์ที่ 1 เราสามารถใช้สูตรต่อไปนี้ในเซลล์ F2

= ควอร์ไทล์ (B2: B14,1)

เมื่อคุณป้อนสูตร Excel จะแสดงรายการตัวเลือกสำหรับอาร์กิวเมนต์ควอร์ต

ในการคำนวณควอไทล์ที่ 3 เราสามารถป้อนสูตรเช่นเดียวกับสูตรก่อนหน้าในเซลล์ F3 แต่ใช้สามแทนหนึ่ง

= ควอไทล์ (B2: B14,3)

ตอนนี้เรามีจุดข้อมูลควอไทล์ที่แสดงในเซลล์

ขั้นตอนที่สอง: ประเมินช่วง Interquartile

ช่วงระหว่างควอไทล์ (หรือ IQR) คือค่ากลาง 50% ของค่าในข้อมูลของคุณ คำนวณเป็นผลต่างระหว่างค่าควอไทล์ที่ 1 และค่าควอไทล์ที่ 3

เราจะใช้สูตรง่ายๆในเซลล์ F4 ที่ลบควอไทล์ที่ 1 ออกจากควอไทล์ที่ 3:

= F3-F2

ตอนนี้เราสามารถเห็นช่วง interquartile ของเราแสดง

ขั้นตอนที่สาม: คืนขอบเขตล่างและบน

ขอบเขตล่างและบนคือค่าที่เล็กที่สุดและใหญ่ที่สุดของช่วงข้อมูลที่เราต้องการใช้ ค่าใด ๆ ที่น้อยกว่าหรือมากกว่าค่าที่ถูกผูกไว้เหล่านี้ถือเป็นค่าผิดปกติ

เราจะคำนวณขีด จำกัด ล่างในเซลล์ F5 โดยการคูณค่า IQR ด้วย 1.5 แล้วลบออกจากจุดข้อมูล Q1:

= F2- (1.5 * F4)

หมายเหตุ:ไม่จำเป็นต้องใช้วงเล็บในสูตรนี้เนื่องจากส่วนการคูณจะคำนวณก่อนส่วนการลบ แต่จะทำให้อ่านสูตรได้ง่ายขึ้น

ในการคำนวณขอบเขตบนในเซลล์ F6 เราจะคูณ IQR ด้วย 1.5 อีกครั้ง แต่คราวนี้เพิ่มเข้าไปในจุดข้อมูล Q3:

= F3 + (1.5 * F4)

ขั้นตอนที่สี่: ระบุสิ่งผิดปกติ

ตอนนี้เราได้ตั้งค่าข้อมูลพื้นฐานทั้งหมดแล้วก็ถึงเวลาระบุจุดข้อมูลภายนอกของเราซึ่งเป็นจุดที่ต่ำกว่าค่าขอบเขตล่างหรือสูงกว่าค่าขอบเขตบน

เราจะใช้ฟังก์ชัน OR เพื่อทำการทดสอบเชิงตรรกะนี้และแสดงค่าที่ตรงตามเกณฑ์เหล่านี้โดยป้อนสูตรต่อไปนี้ลงในเซลล์ C2:

= หรือ (B2 $ F $ 6)

จากนั้นเราจะคัดลอกค่านั้นไปยังเซลล์ C3-C14 ของเรา ค่า TRUE บ่งบอกถึงค่าผิดปกติและอย่างที่คุณเห็นเรามีข้อมูลสองอย่าง

ละเว้นค่าผิดปกติเมื่อคำนวณค่าเฉลี่ยเฉลี่ย

การใช้ฟังก์ชัน QUARTILE ช่วยให้เราคำนวณ IQR และทำงานกับคำจำกัดความที่ใช้กันอย่างแพร่หลายของค่าผิดปกติ อย่างไรก็ตามเมื่อคำนวณค่าเฉลี่ยเฉลี่ยสำหรับช่วงของค่าและไม่สนใจค่าผิดปกติจะมีฟังก์ชันที่ใช้งานง่ายและรวดเร็ว เทคนิคนี้จะไม่ระบุค่าผิดปกติเหมือน แต่ก่อน แต่จะช่วยให้เรามีความยืดหยุ่นกับสิ่งที่เราอาจพิจารณาส่วนที่ผิดปกติ

ฟังก์ชันที่เราต้องการเรียกว่า TRIMMEAN และคุณสามารถดูไวยากรณ์ได้ด้านล่าง:

= TRIMMEAN (อาร์เรย์เปอร์เซ็นต์)

อาร์เรย์เป็นช่วงของค่าที่คุณต้องการโดยเฉลี่ย ร้อยละเป็นร้อยละของจุดข้อมูลในการยกเว้นจากด้านบนและด้านล่างของชุดข้อมูล (คุณสามารถใส่มันเป็นเปอร์เซ็นต์หรือค่าทศนิยม)

เราป้อนสูตรด้านล่างลงในเซลล์ D3 ในตัวอย่างของเราเพื่อคำนวณค่าเฉลี่ยและไม่รวมค่าผิดปกติ 20%

= TRIMMEAN (B2: B14, 20%)

คุณมีสองฟังก์ชันที่แตกต่างกันสำหรับการจัดการค่าผิดปกติ ไม่ว่าคุณต้องการระบุสิ่งเหล่านี้สำหรับความต้องการในการรายงานบางอย่างหรือแยกออกจากการคำนวณเช่นค่าเฉลี่ย Excel มีฟังก์ชันที่เหมาะกับความต้องการของคุณ