การให้ลำดับข้อมูลคะแนนโดยค่าเดียวกันได้ลำดับเดียวกันและไม่ข้ามลำดับ

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

	A	B	C
1	คะแนน	Rank	ที่ต้องการ
2	300	1	1
3	200	2	2
4	200	2	2
5	100	4	3

จากตารางจะเห็นว่าด้วยฟังก์ชั่น Rank จะมีการให้ลำดับข้ามไป คือไม่มีลำดับที่ 3 หากต้องการผลลัพธ์ตามคอลัมน์ B เราสามารถคีย์สูตรที่ B2 เป็น

=Rank($A2,$A$2:$A$5)

Enter > Copy ลงด้านล่าง

แต่หากต้องการผลลัพธ์ที่มีการให้ลำดับต่อเนื่องกันไปเช่นเดียวกับผลลัพธ์ในคอลัมน์ C ที่ C2 คีย์

=MATCH($A2,LARGE(IF(FREQUENCY(MATCH($A$2:$A$5,$A$2:$A$5,0),ROW($A$2:$A$5)-ROW($A$2)+1),$A$2:$A$5),ROW(INDIRECT("1:"&SUM(1/COUNTIF($A$2:$A$5,$A$2:$A$5))))),0)

Ctrl+Shift+Enter > Copy ลงด้านล่าง

สำหรับสูตรนี้ค่อนข้างจะซับซ้อนครับ เนื่องจากว่าเป็นสูตร Array การกดแป้นให้รับสูตรจะต้องกด 3 แป้น โดยกดแป้น Ctrl+Shift ค้างไว้ก่อนแล้วตามด้วย Enter การแก้ไขเปลี่ยนแปลงสูตรจะต้องกดให้รับสูตรด้วย 3 แป้นนี้เสมอ หากว่ากดแป้นถูกต้องจะเห็นเครื่องหมายปีกกาคร่อมสูตร ปีกกานี้จะคีย์เข้าไปไม่ได้ครับ

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