จัดอันดับด้วย RANK และ PERCENTILE

บ่อยครั้งคุณไม่ได้ต้องการเพียงค่าหนึ่งค่า แต่ต้องการทราบตำแหน่งของค่านั้นภายในกลุ่ม เช่น พนักงานขายคนนี้อยู่อันดับ 1 หรือ 12 หรือคะแนนนี้อยู่ใน 10% แรกหรือไม่

ฟังก์ชันสองกลุ่มนี้ช่วยตอบคำถามดังกล่าว: RANK บอกตำแหน่งตามลำดับ และ PERCENTILE บอกค่าที่จุดตัดเปอร์เซ็นต์หนึ่ง ๆ เมื่อนำมาใช้ร่วมกัน ทั้งสองจะเปลี่ยนตัวเลขดิบให้เป็นอันดับและเกณฑ์อ้างอิง

RANK.EQ ให้ตำแหน่งของค่าภายในช่วงข้อมูล คุณต้องระบุค่า ช่วงข้อมูล และตัวกำหนดลำดับ

• ลำดับ 0 (หรือเว้นไว้) จัดอันดับจากค่ามากไปน้อย
• ลำดับ 1 จัดอันดับจากค่าน้อยไปมาก

ในที่นี้ ตัวเลขยอดขายสูงสุดจะได้อันดับ 1

สังเกตเครื่องหมายดอลลาร์: $B$2:$B$20 ช่วงข้อมูลต้องคงที่เมื่อคุณคัดลอกสูตรลงด้านล่าง ขณะที่การอ้างอิงค่า B2 จะเลื่อนไปเป็น B3, B4 และต่อไปเรื่อย ๆ

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

RANK.EQ ให้อันดับเดียวกันแก่ค่าที่เท่ากัน จากนั้นจะข้ามอันดับถัดไป หากมีสองค่าเสมอกันที่อันดับ 2 ทั้งคู่จะแสดงเป็น 2 และค่าถัดไปจะได้อันดับ 4 (ไม่มีอันดับ 3)

หากคุณต้องการให้อันดับของค่าที่เท่ากันเป็นค่าเฉลี่ย ให้ใช้ RANK.AVG ค่าสองค่าที่เสมอกันสำหรับอันดับ 2 และ 3 จะแสดงเป็น 2.5 ทั้งคู่

หากต้องการบังคับให้อันดับไม่ซ้ำกันแม้มีค่าที่เท่ากัน ให้เพิ่มตัวช่วยตัดสินด้วย COUNTIF ฟังก์ชันนี้จะนับจำนวนค่าที่เท่ากันซึ่งปรากฏอยู่เหนือแถวปัจจุบัน แล้วนำจำนวนนั้นไปเพิ่มเป็นค่าชดเชย

วิธีนี้รับประกันลำดับ 1, 2, 3, 4 ที่ต่อเนื่องและไม่มีค่าซ้ำ เหมาะสำหรับตารางจัดอันดับ

PERCENTILE.INC ตอบคำถามว่า ค่าที่จุดเปอร์เซ็นต์หนึ่ง ๆ คือเท่าใด ให้ระบุช่วงข้อมูลและเศษส่วนระหว่าง 0 ถึง 1

สูตรด้านล่างส่งคืนเปอร์เซ็นไทล์ที่ 90 ซึ่งเป็นค่าที่ข้อมูล 90% มีค่าต่ำกว่าค่านี้ เหมาะอย่างยิ่งสำหรับกำหนดเกณฑ์ เช่น "ผู้ปฏิบัติงานที่อยู่ใน 10% แรก"

ควอร์ไทล์แบ่งข้อมูลออกเป็นสี่ส่วน ฟังก์ชัน QUARTILE.INC เป็นทางลัด โดยควอร์ไทล์ที่ 1 เท่ากับเปอร์เซ็นไทล์ที่ 25 ควอร์ไทล์ที่ 2 เท่ากับเปอร์เซ็นไทล์ที่ 50 หรือค่ามัธยฐาน และควอร์ไทล์ที่ 3 เท่ากับเปอร์เซ็นไทล์ที่ 75

สูตรด้านล่างจะส่งคืนค่าควอร์ไทล์ที่สาม ซึ่งเหมือนกับ PERCENTILE.INC(A2:A101,0.75)

มีอยู่สองรูปแบบ ได้แก่ PERCENTILE.INC (แบบรวม ซึ่งรับค่า 0 และ 1 ได้) และ PERCENTILE.EXC (แบบไม่รวม ซึ่งรับเฉพาะค่าระหว่าง 0 ถึง 1 และไม่รวมค่าปลายช่วง)

สำหรับการรายงานทั่วไปในชีวิตประจำวัน .INC เป็นตัวเลือกมาตรฐาน ใช้ .EXC เมื่อต้องปฏิบัติตามหลักสถิติที่ไม่รวมจุดปลายช่วง

RANK ให้ตำแหน่ง ส่วน PERCENTILE ให้ค่าที่จุดตัด PERCENTRANK.INC ทำงานกลับด้านกับเปอร์เซ็นไทล์ กล่าวคือ เมื่อกำหนดค่าให้แล้ว จะบอกว่าค่านั้นอยู่ที่เปอร์เซ็นไทล์ใด

หากนักเรียนได้คะแนน 88 และ PERCENTRANK ส่งคืนค่า 0.92 นักเรียนคนนั้นทำคะแนนได้ดีกว่าสมาชิกในกลุ่ม 92% ให้คูณด้วย 100 เพื่อแสดงผลเป็นเปอร์เซ็นต์

RANK และ PERCENTILE ใช้ได้เฉพาะกับตัวเลขเท่านั้น ป้ายข้อความและเซลล์ว่างภายในช่วงจะถูกละเว้น ดังนั้นหัวตารางที่หลงอยู่จะไม่ทำให้การจัดอันดับผิดพลาด

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

คุณมีพนักงานขาย 20 คน โดยมียอดขายอยู่ในคอลัมน์ B และต้องการทราบอันดับของพนักงานแต่ละคน รวมถึงตรวจสอบว่าอยู่ในควอร์ไทล์สูงสุดหรือไม่

• อันดับ: =RANK.EQ(B2,$B$2:$B$21,0)
• ตัวบ่งชี้ควอร์ไทล์สูงสุดจะเปรียบเทียบยอดขายของพนักงานกับเปอร์เซ็นไทล์ที่ 75

สูตรด้านล่างจะส่งคืนค่า Top หรือ Standard สำหรับพนักงานแต่ละคนตามค่าจุดตัด

ทดสอบความเข้าใจเกี่ยวกับฟังก์ชันการจัดอันดับของคุณ

คุณได้เรียนรู้วิธีระบุตำแหน่งของค่าภายในกลุ่มแล้ว:

• RANK.EQ ให้ตำแหน่งตามลำดับ โดยลำดับ 0 = เรียงจากค่ามากที่สุดก่อน และ 1 = เรียงจากค่าน้อยที่สุดก่อน ให้ตรึงช่วงด้วย $
• RANK.AVG จะหาค่าเฉลี่ยของอันดับที่ซ้ำกัน และสามารถเพิ่ม COUNTIF เพื่อให้ได้อันดับที่ไม่ซ้ำกัน
• PERCENTILE.INC ส่งคืนค่าที่จุดตัด ส่วน QUARTILE.INC เป็นทางลัดสำหรับค่า 25/50/75
• PERCENTRANK.INC ทำงานกลับด้าน โดยให้ค่าเปอร์เซ็นไทล์ของค่าที่กำหนด