Thep Excel
PERCENTILEหาค่าเปอร์เซ็นไทล์
คำนวณค่าเปอร์เซ็นไทล์ที่ k ของชุดข้อมูล เช่น P90 หรือ P50 เหมาะสำหรับวิเคราะห์การกระจายตัวของข้อมูลและกำหนดเกณฑ์มาตรฐาน
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | คำอธิบาย |
|---|---|---|
| array | Range/Array | ช่วงข้อมูลหรืออาร์เรย์ตัวเลขที่ต้องการหาค่าเปอร์เซ็นไทล์ |
| k | Number | ค่าเปอร์เซ็นไทล์ที่ต้องการ ต้องอยู่ระหว่าง 0 ถึง 1 เช่น 0.9 = P90, 0.5 = P50 (มัธยฐาน) |
Additional Notes
PERCENTILE รับชุดข้อมูลและค่า k (0 ถึง 1) แล้วคืนค่าที่ตำแหน่งเปอร์เซ็นไทล์นั้น เช่น k=0.9 หมายถึง 90% ของข้อมูลอยู่ต่ำกว่าค่านี้ ถ้า k ไม่ตรงกับตำแหน่งข้อมูลพอดี Excel จะ interpolate ให้อัตโนมัติ ฟังก์ชันนี้เป็นเวอร์ชันเก่า (Legacy) ที่ยังคงใช้ได้ใน Excel ทุกเวอร์ชัน
ที่เจ๋งคือ แค่เปลี่ยน k ก็ได้ทุก percentile ที่ต้องการโดยไม่ต้องเรียงข้อมูลเอง — หาค่ามัธยฐาน (P50), ค่าควอร์ไทล์ (P25/P75) หรือ P90 สำหรับ KPI ได้ในสูตรเดียว
ส่วนตัวผม ถ้าเปิด file เก่าที่ใช้ PERCENTILE อยู่แล้วก็ไม่จำเป็นต้องเปลี่ยน แต่ถ้าเขียนสูตรใหม่ผมแนะนำให้ใช้ PERCENTILE.INC แทนเลย เพราะชื่อมันบอกชัดว่า inclusive ทำให้คนอื่นอ่านสูตรแล้วเข้าใจได้ทันทีว่าใช้วิธีคำนวณแบบไหน 😎
Examples
หาค่า P90 ของชุดข้อมูลคะแนนPERCENTILE({55,60,65,70,75,80,85,90,95,100},0.9)
หาค่าเปอร์เซ็นไทล์ที่ 90 จากคะแนน 10 คน ผลลัพธ์คือ 95.5 แปลว่า 90% ของนักเรียนได้คะแนนต่ำกว่า 95.5
หาค่ามัธยฐาน (P50) ด้วย PERCENTILEPERCENTILE({10,20,30,40,50,60,70,80,90,100},0.5)
k=0.5 คือ P50 ซึ่งเทียบเท่ากับค่ามัธยฐาน ผลลัพธ์ 55 คือจุดกึ่งกลางของชุดข้อมูลนี้
หาค่า P75 สำหรับกำหนดเกณฑ์PERCENTILE({100,200,300,400,500,600,700,800,900,1000},0.75)
P75 หมายความว่า 75% ของข้อมูลอยู่ต่ำกว่า 775 ใช้ได้ดีสำหรับตั้งเกณฑ์ผ่านหรือ bonus threshold
หาค่า P25 (ควอร์ไทล์ที่ 1)PERCENTILE({5,10,15,20,25,30,35,40,45,50},0.25)
P25 เทียบเท่ากับ Q1 (ควอร์ไทล์ที่ 1) Excel จะ interpolate ให้อัตโนมัติเมื่อค่าไม่ตรงกับตำแหน่งข้อมูลพอดี ผลที่ได้คือ 16.25
FAQs
PERCENTILE ต่างจาก PERCENTILE.INC และ PERCENTILE.EXC อย่างไร?+
PERCENTILE กับ PERCENTILE.INC ให้ผลลัพธ์เหมือนกันทุกประการ ทั้งคู่ใช้วิธีคำนวณแบบ inclusive (k=0 และ k=1 ใช้ได้) ส่วน PERCENTILE.EXC ใช้วิธีแบบ exclusive ซึ่ง k ต้องอยู่ระหว่าง 0 ถึง 1 เท่านั้น (ไม่รวม 0 กับ 1) ผมแนะนำให้ใช้ PERCENTILE.INC สำหรับงานใหม่ เพราะชื่อบอกชัดว่าใช้วิธีไหน
ทำไมสูตรถึงได้ค่า #NUM! error?+
สาเหตุหลักคือค่า k อยู่นอกช่วง 0–1 เช่น ใส่ k=90 แทนที่จะเป็น k=0.9 ผมเคยเจอบ่อยมากตอนที่พิมพ์เป็นตัวเลขเปอร์เซ็นต์โดยตรง ให้จำว่า k ต้องเป็นทศนิยม 0.1 ถึง 0.9 ไม่ใช่ 10 ถึง 90
ถ้าชุดข้อมูลมีค่าว่าง (blank) หรือ text จะเกิดอะไรขึ้น?+
Excel จะ ignore ค่าว่างโดยอัตโนมัติ ซึ่งสะดวกมาก แต่ถ้ามี text ปะปนอยู่ในช่วงข้อมูล จะได้ #VALUE! error ผมมักจะใส่ ISNUMBER check หรือทำความสะอาดข้อมูลก่อนเสมอ
PERCENTILE สามารถใช้กับข้อมูล large dataset ได้ไหม?+
ได้เลย ไม่มี limit พิเศษนอกจาก row limit ของ Excel (1,048,576 แถว) ประสิทธิภาพก็ดีมาก ผมเคยใช้กับข้อมูลหลักแสน row ก็ยังคำนวณเร็วมาก
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ PERCENTILE จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
LOGNORM.INV คำนวณค่าผกผันของการแจกแจง Lognormal โดยรับค่าความน่าจะเป็นและส่งคืนค่า X ที่สอดคล้องกับความน่าจะเป็นนั้น ใช้ในการวิเคราะห์ความเสี่ยงและการวางแผนทางการเงิน
MEDIAN คืนค่าที่อยู่ตำแหน่งตรงกลางของชุดข้อมูลที่เรียงลำดับแล้ว หากมีจำนวนข้อมูลเป็นเลขคี่ จะได้ค่าตรงกลางพอดี แต่ถ้าเป็นเลขคู่ จะนำ 2 ค่าตรงกลางมาหาค่าเฉลี่ย เหมาะสำหรับหาค่ากลางของข้อมูลที่มีค่าสุดโต่ง (Outliers) ปะปนอยู่ (เช่น เงินเดือน หรือราคาบ้าน)
NORM.INV ช่วยหาค่า x ของการแจกแจงปกติเมื่อทราบความน่าจะเป็น ค่าเฉลี่ย และส่วนเบี่ยงเบนมาตรฐาน เป็นฟังก์ชันผกผันของ NORM.DIST ที่ใช้ในการวิเคราะห์ทางสถิติและการทำนาย
PERCENTILE.EXC หาค่าเปอร์เซ็นไทล์ของข้อมูล โดยไม่รวมค่าที่ 0% และ 100% (Exclusive method) เหมาะสำหรับข้อมูลที่มีการกระจายตัวแบบปกติและต้องการการประเมินที่แม่นยำยิ่งขึ้น
หาค่าเปอร์เซ็นไทล์ที่ k (แบบ Inclusive: 0 ≤ k ≤ 1) ซึ่งรวมค่าขอบ 0% และ 100% ในการคำนวณ
ฟังก์ชันที่หาว่าค่าใดค่าหนึ่งอยู่ที่อันดับเปอร์เซ็นไทล์เท่าไหร่ โดยขอบเขตเป็น 0 ถึง 1 (ไม่รวมขอบเขต)
PERCENTRANK.INC หาว่าค่าที่ระบุอยู่ที่ตำแหน่งเปอร์เซ็นไทล์ไหน (0-100%) โดยรวม 0% และ 100% ได้
หาค่าเฉลี่ยโดยตัดค่าหัวท้ายออกตามเปอร์เซ็นต์ที่กำหนด เหมาะสำหรับข้อมูลที่มี Outliers
ฟังก์ชัน BETAINV ค้นหาค่าผกผันของฟังก์ชันการแจกแจงความน่าจะเป็น Beta แบบสะสม ใช้ในการวิเคราะห์ความน่าจะเป็นและการวางแผนโครงการ
ฟังก์ชันเก่า (Legacy) ที่หาจำนวนความสำเร็จขั้นต่ำที่ทำให้ความน่าจะเป็นสะสมมากกว่าหรือเท่ากับค่าที่กำหนด แนะนำให้ใช้ BINOM.INV แทน
GAMMAINV เป็นฟังก์ชันเก่าที่ใช้หาค่า x จากความน่าจะเป็นของการแจกแจงแบบ Gamma ในปัจจุบันแนะนำให้ใช้ GAMMA.INV แทน
PERCENTRANK เป็นฟังก์ชันชื่อเก่า (legacy) สำหรับคำนวณอันดับแบบเปอร์เซ็นต์ของค่าหนึ่งภายในชุดข้อมูล โดยคืนค่าอยู่ระหว่าง 0 ถึง 1 ปัจจุบันแนะนำให้ใช้ PERCENTRANK.INC หรือ PERCENTRANK.EXC แทนเพื่อความชัดเจนและรองรับเวอร์ชันใหม่
ฟังก์ชัน QUARTILE แบ่งชุดข้อมูลออกเป็นสี่ส่วน แล้วคืนค่า Q1, Q2 (มัธยฐาน), หรือ Q3 ใช้วิเคราะห์การกระจายข้อมูลได้ทันที แต่ปัจจุบัน Microsoft แนะนำให้ใช้ QUARTILE.INC แทน
AGGREGATE เป็นฟังก์ชันสารพัดประโยชน์ที่รวม 19 ฟังก์ชันต่างๆ (SUM, AVERAGE, COUNT, MAX, MIN ฯลฯ) แต่มีความสามารถพิเศษคือ สามารถข้าม Error ได้ และข้ามแถวที่ถูกซ่อน (Hidden Rows) ได้ เหมาะสำหรับ Dashboard, รายงาน หรือข้อมูลที่อาจมีช่องว่างและข้อมูลผิดพลาด
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่