สูตรการตัดเกรดคะแนนโดยใช้ฟังก์ชัน LOOKUP

มีคนถามผมว่า
"ขอทราบสูตรการตัดเกรดที่มี 10 ระดับด้วยครับ คือทดลองใช้ฟังก์ชัน IF แล้ว ไม่สามารถทำได้ เพราะติดข้อจำกัดที่ฟังก์ชัน IF ซ้อนกันได้ไม่เกิน 7 ชั้น แต่เนื่องจากงานของผมมีเกรด 10 ระดับ (ดังรูปที่แนบมา) ซึ่งเคยได้ยินมาว่า มีวิธีที่ทำให้ฟังก์ชัน IF สามารถคำนวณซ้อนกันได้มากกว่า 7 ชั้น จึงใคร่ขอคำแนะนำด้วยครับ"


รูปที่ 1 เงื่อนไขการตัดเกรด


ใน EXCEL 2003 และรุ่นก่อนหน้านั้น มีข้อจำกัดในเรื่องฟังก์ชันซ้อนกันได้ไม่เกิน 7 ชั้น (เดาว่า..ผู้ผลิตเองก็คงไม่คิดว่าจะมีการใช้ฟังก์ชัน ซ้อนกันเกินกว่า 7 ชั้น เพราะลำพัง 2–3 ชั้น ก็แทบจะไล่สูตรกันไม่ถ้วนแล้ว แต่ก็ยังไม่พอกับความต้องการ ของผู้ใช้บางท่าน) ดังนั้นใน EXCEL 2007 จึงเพิ่มความสามารถ ในการซ้อนฟังก์ชันได้ถึง 64 ชั้น ไล่สูตรกันให้ตาลายไปข้างหนึ่งเลยล่ะ คราวนี้

แต่ถ้าจำเป็นต้องซ้อนฟังก์ชันมากกว่า 7 ชั้น ก็พอจะมีวิธีการพลิกแพลงอยู่บ้าง แต่ผมไม่ใคร่จะแนะนำให้ใช้ ยิ่งโดยเฉพาะคนที่มีพื้นฐาน การใช้สูตรและฟังก์ชัน EXCEL ที่ยังไม่ค่อยแข็งแรงนัก ก็อย่างที่บอกไปแล้วว่า แค่สูตรที่มีการซ้อนฟังก์ชันเพียง 2–3 ชั้น ก็ยังไล่กันไม่ค่อยถูก งงแล้ว งงอีก ดังนั้นการซ้อนฟังก์ชันที่มากกว่า 7 ชั้น ก็ขอให้ลืมไปได้เลย เปลี่ยนความคิดไปศึกษาสูตรแบบอื่น ที่สั้นและกระชับจะดีกว่า


รูปที่ 2 ตารางเกรดผลลัพธ์ตามระดับคะแนน


มาดูตัวอย่างกันสักหน่อย

ถ้าเราต้องการใช้ฟังก์ชัน IF ซ้อนกันมากกว่า 7 ชั้น เพื่อเขียนสูตรในการตัดเกรด โดยมีเงื่อนไขดังรูปที่ 2 ข้างบนนี้ ก็สามารถกระทำได้ โดยมีข้อพึงพิจาณา 2 ประการคือ ประการแรก ถ้าผลลัพธ์คืนค่าเป็นตัวเลข เราจะใช้การบวกฟังก์ชัน เช่น ให้ A1 คือคะแนนรวม เราจะเขียนสูตรเพื่อให้แสดงเกรดที่เซลใดๆ ดังนี้



แต่ในทางกลับกัน ถ้าผลลัพธ์คืนค่าเป็นข้อความ หรือตัวอักษร เราจะใช้การเชื่อมต่อฟังก์ชัน เช่น ถ้า A1 คือคะแนนรวม เราจะเขียนสูตรเพื่อให้แสดงเกรดที่เซลใดๆ ดังนี้



ถึงแม้เราจะสามารถใช้ฟังก์ชัน IF เพื่อตรวจสอบเงื่อนไขที่มีมากถึง 10 เงื่อนไขได้ก็ตาม แต่การเขียนสูตรแบบนี้จะยาวมาก ดังนั้นในโจทย์ข้อนี้ ผมแนะนำให้เปลี่ยนจาก การใช้ฟังก์ชัน IF ไปใช้ฟังก์ชัน LOOKUP แทน เพราะสูตรจะสั้นและกระชับกว่า

อย่าไปยึดติดว่า การคิดเกรด หรือการทดสอบเงื่อนไข (ถ้า....) จะต้องใช้ฟังก์ชัน IF เสมอไป เพราะการเขียนสูตร เพื่อให้ได้มาซึ่งผลลัพธ์ใดๆ ใน EXCEL นั้น อาจจะมีวิธีการมากกว่า 1 วิธี ส่วนจะเลือกใช้วิธีไหนนั้น ขึ้นอยู่กับประสบการณ์ในการเรียนรู้ EXCEL ของผู้ใช้แต่ละคน

ดังนั้น จากรูปที่ 1 (ในโจทย์) ที่ตำแหน่ง E2 พิมพ์สูตรดังนี้

=LOOKUP(D2, $B$11:$B$20, $E$11:$E$20)

หรือถ้าต้องการแสดงผลลัพธ์เป็นตัวเลข ก็แก้ไขสูตรให้เป็นดังนี้

=LOOKUP(D2, $B$11:$B$20, $F$11:$F$20)

หลังจากนั้น ก็คัดลอกสูตรลงมาทุกบรรทัด จะได้ผลลัพธ์ดังรูปข้างล่าง


รูปที่ 3


คำแนะนำเพิ่มเติม 

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

ส่วนข้อจำกัดของการใช้ฟังก์ชัน LOOKUP คือ ระดับคะแนน จะต้องมีการเรียงลำดับ จากน้อยไปหามาก มิฉะนั้นแล้ว ผลลัพธ์จะไม่ถูกต้อง