Thep Excel
QUARTILE.EXCหาควอร์ไทล์ (Exclusive)
QUARTILE.EXC หาค่าควอร์ไทล์ของชุดข้อมูล โดยใช้วิธี Exclusive ที่ไม่นับค่าต่ำสุดและสูงสุดเป็นตำแหน่งควอร์ไทล์ที่เป็นไปได้
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | คำอธิบาย |
|---|---|---|
| array | Range/Array | ช่วงข้อมูลหรือ array ที่ต้องการหาควอร์ไทล์ ควรมีอย่างน้อย 3 ค่าเพื่อไม่ให้ตำแหน่งหลุดช่วงจนเกิด #NUM! |
| quart | Number | ค่าควอร์ไทล์ที่ต้องการ (1=Q1 ที่ 25%, 2=Median ที่ 50%, 3=Q3 ที่ 75%) ใส่ 0 หรือ 4 จะได้ #NUM! เพราะ QUARTILE.EXC ไม่รองรับค่าต่ำสุด/สูงสุด |
Additional Notes
QUARTILE.EXC ใช้หาจุดแบ่งข้อมูลออกเป็นสี่ส่วน (Q1, Q2, Q3) ด้วยวิธี exclusive คือคำนวณตำแหน่งด้วยสูตร quart × (n+1) / 4 ซึ่งไม่มีทางตกที่ตำแหน่งค่าต่ำสุดหรือสูงสุดของชุดข้อมูลพอดี ต่างจาก QUARTILE.INC ที่ใช้สูตร 1 + quart × (n-1) / 4 ซึ่งรวมค่าต่ำสุด/สูงสุดเป็นตำแหน่งที่เป็นไปได้เสมอ
งานที่ใช้จริงบ่อยที่สุดคือ HR/การเงินที่ต้องหาช่วงเงินเดือนตามเปอร์เซ็นไทล์ (เช่น Q1-Q3 ของฐานเงินเดือนตำแหน่งเดียวกัน) และงานคุณภาพ/สถิติที่ต้องหา outlier ด้วยกฎ 1.5×IQR (ค่าที่ต่ำกว่า Q1-1.5×IQR หรือสูงกว่า Q3+1.5×IQR ถือว่าผิดปกติ) เพราะ QUARTILE.EXC ไม่ให้ค่าสุดขั้วของข้อมูลมามีอิทธิพลต่อตำแหน่งควอร์ไทล์โดยตรง จึงเหมาะกับงานตรวจจับค่าผิดปกติมากกว่า QUARTILE.INC
ข้อจำกัดสำคัญที่ต้องรู้คือ QUARTILE.EXC ต้องการข้อมูลอย่างน้อย 3 ค่าขึ้นไปถึงจะคำนวณ Q1/Q3 ได้โดยไม่ error เพราะตำแหน่งที่คำนวณได้ต้องอยู่ในช่วง 1 ถึง n เท่านั้น ข้อมูลแค่ 2 ค่าจะทำให้ตำแหน่งของ Q1 และ Q3 หลุดออกนอกช่วงและได้ #NUM! ทันที นี่คือจุดที่คนสลับมาใช้ QUARTILE.INC บ่อยที่สุดเวลาข้อมูลมีไม่ครบ
Examples
พื้นฐาน: หา Q1 (25%) จากชุดข้อมูลเรียงเท่าๆ กันA2:A10 = 10,20,30,40,50,60,70,80,90 =QUARTILE.EXC(A2:A10, 1)
n=9 ตำแหน่ง Q1 คำนวณจาก 1×(9+1)/4 = 2.5 คือกึ่งกลางระหว่างตัวที่ 2 (20) กับตัวที่ 3 (30) ได้ 20+0.5×(30-20)=25 สังเกตว่าตำแหน่ง 2.5 ไม่มีทางตรงกับค่าต่ำสุด (ตำแหน่ง 1) พอดี นี่คือความหมายของ 'exclusive'
พื้นฐาน: หามัธยฐาน (Q2 – 50%)QUARTILE.EXC(A2:A10, 2)
ตำแหน่ง Q2 = 2×10/4 = 5 ตรงกับตัวที่ 5 พอดี (50) ไม่ต้องเฉลี่ย ผลลัพธ์นี้เท่ากับ MEDIAN(A2:A10) เสมอไม่ว่าจะใช้ .EXC หรือ .INC เพราะ Q2 อยู่กึ่งกลางข้อมูลพอดีทั้งสองวิธี
HR: หาขอบเขตเงินเดือน Q1-Q3 ของทีมB2:B13 = เงินเดือน 12 คน (พันบาท): 25,28,30,32,35,38,40,45,50,55,60,70 =QUARTILE.EXC(B2:B13, 3) – QUARTILE.EXC(B2:B13, 1)
Q1=30.5 (ตำแหน่ง 3.25) และ Q3=53.75 (ตำแหน่ง 9.75) ได้ IQR=23.25 พันบาท หมายความว่าเงินเดือนตรงกลาง 50% ของทีมกระจายอยู่ในช่วงกว้าง 23,250 บาท ใช้เทียบตอนตั้งเกรดเงินเดือนตำแหน่งใหม่ว่าอยู่ในช่วงที่เหมาะสมไหม
ตรวจจับ outlier ด้วยกฎ 1.5×IQR จากคะแนนสอบC2:C12 = คะแนน 11 คน: 45,50,52,55,58,60,62,65,68,70,95 ขอบบน = QUARTILE.EXC(C2:C12,3) + 1.5*(QUARTILE.EXC(C2:C12,3)-QUARTILE.EXC(C2:C12,1))
Q1=52, Q3=68, IQR=16 ขอบบนตามกฎ = 68+1.5×16=92 คะแนน 95 ของคนสุดท้ายสูงกว่าขอบบนนี้ จึงถูกตีเป็น outlier ทางสถิติ (อาจเป็นคนที่เก่งจริงหรือมีการทุจริต ต้องตรวจสอบเพิ่ม) นี่คือประโยชน์หลักที่ QUARTILE.EXC ถูกเลือกใช้แทน QUARTILE.INC ในงานควบคุมคุณภาพ
FAQs
QUARTILE.EXC กับ QUARTILE.INC ต่างกันอย่างไร ใช้ตัวไหนดี?+
ต่างกันที่สูตรคำนวณตำแหน่ง: .EXC ใช้ quart×(n+1)/4 ซึ่งไม่มีทางตรงกับค่าต่ำสุด/สูงสุดพอดี ส่วน .INC ใช้ 1+quart×(n-1)/4 ซึ่งรวมค่าต่ำสุด/สูงสุดเป็นตำแหน่งที่เป็นไปได้ ผลคือ .INC ใช้ได้กับข้อมูลน้อยกว่า 4 ค่า ส่วน .EXC เหมาะกับชุดข้อมูลใหญ่และงานหา outlier ที่ไม่อยากให้ค่าสุดขั้วมามีน้ำหนักโดยตรง
ทำไมใส่ quart=0 หรือ 4 แล้วได้ #NUM! error ทั้งที่ QUARTILE.INC ทำได้?+
QUARTILE.INC ยอมให้ quart=0 (=ค่าต่ำสุด) และ quart=4 (=ค่าสูงสุด) เพราะวิธี inclusive นับค่าสุดขั้วเป็นควอร์ไทล์ที่ 0% และ 100% ได้ แต่ QUARTILE.EXC ออกแบบมาไม่ให้ตำแหน่งตรงกับค่าสุดขั้วพอดี จึงรองรับเฉพาะ quart=1,2,3 เท่านั้น
ทำไมข้อมูลแค่ 2 ค่าแล้วใช้ QUARTILE.EXC หา Q1 หรือ Q3 ไม่ได้ (ขึ้น #NUM!)?+
ตำแหน่งของ Q1 คำนวณจาก 1×(n+1)/4 เมื่อ n=2 จะได้ตำแหน่ง 0.75 ซึ่งต่ำกว่า 1 (นอกช่วงข้อมูล) Excel จึงคืน #NUM! ทันที กฎง่ายๆ คือ QUARTILE.EXC ต้องการข้อมูลอย่างน้อย 3 ค่าขึ้นไปถึงจะคำนวณทั้ง Q1 และ Q3 ได้โดยไม่ error (ที่ n=3 พอดี ตำแหน่ง Q1 และ Q3 จะตรงกับค่าต่ำสุด/สูงสุดเป๊ะ ไม่ได้แปลว่าค่าที่ได้มีความหมายทางสถิติมากนัก)
QUARTILE() แบบไม่มี .EXC หรือ .INC คืออะไร ยังใช้ได้ไหม?+
QUARTILE() เป็นฟังก์ชันรุ่นเก่าที่คำนวณแบบเดียวกับ QUARTILE.INC ทุกประการ Excel ยังรองรับไว้เพื่อความเข้ากันได้ย้อนหลัง (compatibility function) แต่ไมโครซอฟท์แนะนำให้เปลี่ยนมาใช้ QUARTILE.INC หรือ QUARTILE.EXC ให้ชัดเจนแทน เพราะบอกวิธีคำนวณตรงตัวกว่า
PERCENTILE.EXC กับ QUARTILE.EXC สัมพันธ์กันยังไง เลือกใช้ตัวไหน?+
QUARTILE.EXC(data, 1) ให้ผลเท่ากับ PERCENTILE.EXC(data, 0.25) ทุกประการ เช่นเดียวกับ Q2=50% และ Q3=75% ถ้าต้องการเฉพาะจุด 25/50/75% ใช้ QUARTILE.EXC อ่านง่ายกว่า แต่ถ้าต้องการเปอร์เซ็นไทล์ค่าอื่น เช่น 90% หรือ 10% ต้องใช้ PERCENTILE.EXC เพราะ QUARTILE.EXC รับได้แค่ 1,2,3
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ QUARTILE.EXC จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
MEDIAN คืนค่าที่อยู่ตำแหน่งตรงกลางของชุดข้อมูลที่เรียงลำดับแล้ว หากมีจำนวนข้อมูลเป็นเลขคี่ จะได้ค่าตรงกลางพอดี แต่ถ้าเป็นเลขคู่ จะนำ 2 ค่าตรงกลางมาหาค่าเฉลี่ย เหมาะสำหรับหาค่ากลางของข้อมูลที่มีค่าสุดโต่ง (Outliers) ปะปนอยู่ (เช่น เงินเดือน หรือราคาบ้าน)
PERCENTILE.EXC หาค่าเปอร์เซ็นไทล์ของข้อมูล โดยไม่รวมค่าที่ 0% และ 100% (Exclusive method) เหมาะสำหรับข้อมูลที่มีการกระจายตัวแบบปกติและต้องการการประเมินที่แม่นยำยิ่งขึ้น
หาค่าเปอร์เซ็นไทล์ที่ k (แบบ Inclusive: 0 ≤ k ≤ 1) ซึ่งรวมค่าขอบ 0% และ 100% ในการคำนวณ
ฟังก์ชันที่หาค่าควอร์ไทล์ของชุดข้อมูล โดยอิงจากการแบ่งข้อมูล 4 ส่วนเท่า ๆ กัน
ฟังก์ชัน QUARTILE แบ่งชุดข้อมูลออกเป็นสี่ส่วน แล้วคืนค่า Q1, Q2 (มัธยฐาน), หรือ Q3 ใช้วิเคราะห์การกระจายข้อมูลได้ทันที แต่ปัจจุบัน Microsoft แนะนำให้ใช้ QUARTILE.INC แทน
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่