การจัดลำดับแบบไม่ซ้ำกัน โดยมีเงื่อนไข ใน Excel 2010 ด้วย rank.eq() และ sumproduct()

โจทย์ปัญหา
ถ้าต้องการจัดลำดับ โดยกำหนดเกณฑ์ว่า ถ้าได้คะแนนรวมเท่ากัน ให้พิจารณาผลคะแนนอื่นประกอบ เช่น ในการสอบคัดเลือก ถ้าคะแนนรวมได้เท่ากัน ให้พิจารณาคะแนนในการสอบ ภาค ก. ถ้าใครได้คะแนนสอบภาค ก. มากกว่า จะได้ลำดับที่ดีกว่า

สังเกตการจัดลำดับผู้สอบคัดเลือกได้ ในตารางข้างล่างนี้

จากตัวอย่าง จะเห็นว่า ผู้เข้าสอบมีคะแนนรวม ที่เท่ากัน หลายคน เช่น เลขที่ 1, 6 และ 7 ได้คะแนน 100 คะแนน เท่ากัน แต่จัดลำดับที่ต่างกัน โดยพิจารณาจากคะแนนใน ภาค ก. ประกอบ ได้แก่

• เลขที่ 7 ได้คะแนนรวม 100 คะแนน และได้คะแนนภาค ก. 40 คะแนน ได้ลำดับที่ 4
• เลขที่ 6 ได้คะแนนรวม 100 คะแนน และได้คะแนนภาค ก. 30 คะแนน ได้ลำดับที่ 5
• เลขที่ 1 ได้คะแนนรวม 100 คะแนน และได้คะแนนภาค ก. 20 คะแนน ได้ลำดับที่ 6

การเรียงลำดับลักษณะนี้ ใช้ฟังก์ชั่น Rank() อย่างเดียวไม่พอ ต้องใช้ Sumproduct() เพื่อกำหนดเงื่อนไขเพิ่มเติม

วิธีการ มีดังนี

1. ใช้ RANK.EQ() เพื่อหาลำดับที่ตามปกติ จะได้ดังนี้
   
2. จะเห็นว่า มีลำดับที่ซ้ำกัน คือ ลำดับที่ 2 มี 2 คน และ ลำดับที่ 4 มี 3 คน เกณฑ์ต่อมา คือต้องพิจารณาว่า ตำแหน่งที่เท่ากัน หรือซ้ำกัน ใครได้คะแนนในภาค ก. สูงกว่ากัน ผู้ที่ได้คะแนนสูงกว่า จะได้ลำดับที่ดีกว่า 
3. การพิจารณาตามเงื่อนไขนี้ เราใช้ฟังก์ชั่น Sumproduct() เพื่อหาดูว่า ในบรรดาผู้ที่ได้คะแนนเท่ากัน (จัดกลุ่มด้วย sumproduct) มีผู้ที่ได้คะแนน ภาค ก. (ซึ่งใช้เป็นเกณฑ์ตัดสิน) สูงกว่า คะแนนของคนนั้น ๆ กี่คน ซึ่งจะได้ดังนี้
   
4. เมื่อรวมทั้งสองอย่างเข้าด้วยกัน ก็จะได้ลำดับที่ ที่ไม่ซ้ำกันเลย ดังนี้
   
5. ถ้ามีเงื่อนไขเพิ่มเติม เช่น เมื่อพิจารณาจาก ภาค ก. แล้ว ก็ยังเท่ากันอีก ให้พิจารณาภาค ข. ต่อไป ก็ให้หาลำดับที่ ที่ซ้ำกันต่อ และกำหนดให้ภาค ข. (คอลัมน์ D) เป็นเกณฑ์ เป็นต้น 
6. ในตัวอย่างข้างล่าง มีการปรับคะแนนของคนที่ 1 และคนที่ 5 เพื่อให้คะแนนภาค ก. เท่ากัน ทำให้ได้ลำดับที่เท่ากัน ดังนั้นจึงต้องไปพิจารณาคะแนน ในภาค ข. ซึ่งไม่เท่ากัน จึงทำให้ได้ลำดับที่ ที่ไม่ซ้ำกัน
   
7. ดูรายละเอียดเพิ่มเติมเรื่อง sumproduct คลิกที่นี่
8. ดาวน์โหลดไฟลตัวอย่าง คลิกที่นี่