วิธีใช้ (และทำไม) เพื่อใช้ฟังก์ชัน Outliers ใน Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

แถว คือช่วงของค่าที่คุณกำลังประเมิน และ ควอร์ต คือตัวเลขที่แทนควอไทล์ที่คุณต้องการส่งคืน (เช่น 1 สำหรับ 1^{เซนต์} ควอไทล์ 2 สำหรับควอไทล์ที่ 2 และอื่น ๆ ).

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

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

เพื่อคำนวณค่า 1^{เซนต์} ควอไทล์เราสามารถใช้สูตรต่อไปนี้ในเซลล์ F2.

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

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

เพื่อคำนวณค่า 3^ถ ควอไทล์เราสามารถใส่สูตรเหมือนสูตรก่อนหน้าในเซลล์ F3 ได้ แต่ใช้สูตรสามสูตรแทนสูตรหนึ่ง.

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

ตอนนี้เรามีจุดข้อมูลควอไทล์ปรากฏในเซลล์.

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

ช่วงระหว่างควอไทล์ (หรือ 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:

= OR (B2 $ F $ 6)

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

ไม่สนใจค่าผิดปกติเมื่อคำนวณค่าเฉลี่ยเฉลี่ย

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

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

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

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

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

= TRIMMEAN (B2: B14, 20%)

---

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