สูตรลัด เลือกเกรดครั้งที่ดีที่สุด

“อยากเขียนสูตรเลือกเกรดครั้งที่ดีที่สุด ทำไงดี?” เพื่อนผมคนหนึ่งถาม

“ตอนนี้ทำแมนวลอยู่ เหนื่อยมากมาย” (ประโยคนี้แปลว่าต้องช่วยแล้วล่ะ)

โจทย์ของเพื่อนผมหน้าตาประมาณนี้ครับ

เพื่อนผมคนนี้เป็นอาจารย์ วิชาที่สอนมีนักเรียนลงเรียนมากกว่า 500 คน

วิชานี้มีสอบ 3 ครั้ง การสอบแต่ละครั้งตัดเกรดแยกกันไป โดยแบ่งเกรดทั้งหมดเป็น 12 ระดับ (ซอยยิบสุดๆ) ดังนี้

ด้วยเพื่อนคนนี้เป็นอาจารย์ที่ใจดีมาก ให้นักเรียนสอบทั้งหมด 3 ครั้งก็จริง แต่เกรดที่นักเรียนจะได้คือเกรดครั้งที่ดีที่สุด (ไม่ใช่นำสามครั้งมาหารเฉลี่ย)

(อาจารย์ใจดีแบบนี้ยังมีหลงเหลืออีกหรือนี่ ^^)

หรือสุดท้ายแล้วจะได้เกรดดังนี้

ปัญหาก็คือ ที่ผ่านมาเค้าเลือกเกรดครั้งที่ดีที่สุดแบบแมนวล ซึ่งใช้เวลานาน(มาก) แถมโอกาสผิดก็มี(สูง)

เลยอยากหาสูตรที่เป็นแบบอัตโนมัติ ประมาณว่าเขียนปรื๊ดเดียว ใช้ได้เลย

ทำไงดี?

ติ๊กต่อก ติ๊กต่อก …

…

โจทย์ข้อนี้ใช้ Excel แก้ปัญหาได้ (อย่างน้อย) 2 วิธีครับ

ผมขอเรียกว่า แบบยาว กับ แบบสั้น

มาดู แบบยาว กันก่อนครับ

ที่เรียกว่า แบบยาว เพราะเป็นวิธีการคิดแบบตรงๆ สูตรค่อนข้างยาว

หรือถ้าเขียนเป็นสเต็ปการคำนวณก็คือ

แปลงเกรดจากตัวอักษรเป็นคะแนน (ตัวเลข)
เลือกคะแนนที่สูงที่สุด
แปลงคะแนนสูงที่สุดให้เป็นตัวอักษร

มาเริ่มจากสเต็ปที่ 1 แปลงเกรดจากตัวอักษรเป็นคะแนน กัน

สมมติสร้างตารางแปลงเกรดไว้ที่เซลล์ L2-M13 ตามนี้

แปลงเกรดจากตัวอักษรเป็นคะแนน โดยใช้สูตร

=VLOOKUP( B2, $L$2:$M$13, 2, 0)

ได้ผลลัพธ์หน้าตาประมาณนี้ครับ

จบสเต็ปที่ 1 แล้ว

คราวนี้ก็มาสเต็ปที่ 2 เลือกคะแนนที่สูงที่สุด

สเต็ปนี้ง่ายมาก เราก็ใช้ฟังก์ชัน MAX หรือเขียนสูตรเป็น

=MAX(F2:H2)

ได้ผลลัพธ์หน้าตาประมาณนี้ครับ

จบสเต็ปที่ 2 แบบเร็วๆ

คราวนี้ก็มาถึงสเต็ปที่ 3 แล้ว นั่นคือการ แปลงคะแนนสูงที่สุดให้เป็นตัวอักษร

เคสนี้เราคงใช้ฟังก์ชัน VLOOKUP ไม่ได้ เพราะคะแนนที่เป็นตัวอ้างอิงอยู่ด้านขวามือ (ถ้าจะใช้ได้ ต้องสร้างตารางแปลงเกรดใหม่โดยให้คะแนนอยู่คอลัมน์ด้านซ้าย และเกรดที่เป็นตัวอักษรอยู่คอลัมน์ด้านขวา) เราจึงใช้ฟังก์ชัน INDEX และ MATCH แทน

(อ่านรายละเอียดการใช้ฟังก์ชัน INDEX + MATCH ได้ใน บทความนี้ ครับ)

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

=MATCH( I2, $M$2:$M$13, 0)

เคสนี้ I2 = 1.75 ผลลัพธ์ที่ได้คือ 8

(1.75 อยู่ลำดับที่ 8 ขอข้อมูล)

(สูตรนี้เทียบกับ $M$2:$M$13 จึงไม่นับเซลล์ที่เป็นหัวตาราง (M1))

ลำดับที่ 8 ถ้าเทียบเป็นเกรด ก็คือ C- นั่นเอง

เขียนสูตรเพื่อเปลี่ยนลำดับที่ 8 ให้กลายเป็นเกรดโดยใช้ฟังก์ชัน INDEX

=INDEX($L$2:$L$13, 8)

8 ก็คือ MATCH( I2, $M$2:$M$13, 0) รวมสูตรได้เป็น

=INDEX($L$2:$L$13,MATCH(I2, $M$2:$M$13, 0) )

เสร็จแว๊ว !!

วิธีนี้ต้องเพิ่มคอลัมน์ช่วยคำนวณ (Helper Column) หลายคอลัมน์

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

=INDEX($G$2:$G$13, MATCH( MAX( VLOOKUP(B2,$G$2:$H$13,2, 0),
VLOOKUP(C2,$G$2:$H$13,2,0),
VLOOKUP(D2,$G$2:$H$13,2,0)),
$H$2:$H$13,0))

ได้ผลลัพธ์เหมือนกัน เย้ !

ทั้งสองวิธีใช้สเต็ปการคิดเหมือนกัน เพียงแต่วิธีที่สองไม่ต้องใช้คอลัมน์ช่วย (Helper Column) เท่านั้น

คุณชอบวิธีไหนครับ ^__^

คราวนี้มาดูวิธี แบบสั้น กันบ้าง วิธีนี้มีสเต็ปการคิดต่างออกไป ดังนี้

แปลงเกรดจากตัวอักษรให้เป็นลำดับ (ไม่ใช่คะแนน) โดยเทียบจากตารางเกรด
เลือกลำดับที่น้อยที่สุด
แปลงลำดับที่น้อยที่สุดให้เป็นตัวอักษร

วิธีนี้ต้องสร้างตารางเกรดเช่นกัน แต่ตารางเกรดนี้ต้องการเพียงแค่ลำดับการเรียงเกรด ไม่ต้องการตัวเลข

เช่น สร้างตารางเกรดไว้ที่เซลล์ G2-G13 ตามนี้

มาดูกันทีละสเต็ป

เริ่มจากสเต็ป 1 แปลงเกรดจากตัวอักษรให้เป็นลำดับ โดยใช้สูตร

=MATCH( B2:D2, $G$2:$G$13, 0)

เนื่องจากอาร์กิวเมนต์ที่ 1 ของ MATCH เป็นช่วงข้อมูล (B2:D2) ไม่ใช่เซลล์เดี่ยวๆ สูตรนี้จึงต้องเป็นสูตรอาร์เรย์ (Array Formula) เวลาใช้งานต้องกด Ctrl+Shft+Enter แทนการกด Enter

มาดูค่า B2:D2 กันบ้าง

B2 = C-

C2 = D

D2 = D-

ผลลัพธ์จากสูตรนี้ก็คือ

{8,10,11}

(สูตรนี้เทียบกับ $G$2:$G$13 จึงไม่นับเซลล์ที่เป็นหัวตาราง (G1))

คราวนี้ก็มาถึงสเต็ปที่ 2 เลือกลำดับที่น้อยที่สุด

เหตุผลที่เลือกลำดับที่น้อยที่สุด (ไม่ใช่มากที่สุด) เพราะเกรดสูง (A = 1) จะมีลำดับน้อยกว่าเกรดต่ำ (F = 12)

สเต็ปนี้ง่ายมาก เลือกลำดับที่น้อยที่สุดโดยใช้ฟังก์ชัน MIN หรือเขียนสูตรเป็น

=MIN({8,10,11})

หรือได้ผลลัพธ์ = 8

เปลี่ยน {8,10,11} ให้กลับเป็น MATCH( B2:D2, $G$2:$G$13, 0) หรือกลายเป็น

=MIN(MATCH( B2:D2, $G$2:$G$13,0))

คราวนี้ก็มาถึงสเต็ปที่ 3 แปลงลำดับที่น้อยที่สุดให้เป็นตัวอักษร

จาก MIN(MATCH( B2:D2, $G$2:$G$13,0)) ได้ผลลัพธ์คือ 8

สเต็ปนี้ก็คือการแปลง 8 ให้กลายเป็นตัวอักษร (เมื่อเทียบกับตารางเกรด) หรือเขียนสูตรเป็น

=INDEX($G$2:$G$13,8)

แทนค่า 8 ด้วย MIN(MATCH( B2:D2, $G$2:$G$13,0))

=INDEX($G$2:$G$13,MIN(MATCH(B2:D2,$G$2:$G$13,0)))

เนื่องจาก MATCH(B2:D2,$G$2:$G$13,0) เป็นสูตรอาร์เรย์ สูตรนี้จึงกลายเป็นอาร์เรย์ด้วย เมื่อพิมพ์สูตรเสร็จแล้ว ต้องกด Ctrl+Shft+Enter แทนการกด Enter

{=INDEX($G$2:$G$13,MIN(MATCH(B2:D2,$G$2:$G$13,0)))}

อ้อ! วงเล็บปีกกาในสูตรไม่ได้เกิดจากการพิมพ์นะครับ แต่เกิดจากการกด Ctrl+Shft+Enter

จะเห็นได้ว่าวิธี แบบสั้น นั้นสะดวกกว่า แบบยาว (ทั้งแบบยาวที่มีคอลัมน์ช่วย และไม่มีคอลัมน์ช่วย) มาก

งั้นแปลว่าวิธี แบบสั้น ดีกว่า แบบยาว หรือเปล่า?

คำตอบคือไม่ใช่

งั้นแปลว่าวิธี แบบยาว ดีกว่า แบบสั้น ?

คือตอบคือไม่ใช่อีกเช่นกัน

เอ๊ะ งง ยังไงกันแน่?

คำตอบคือ เลือกวิธีที่เหมาะกับเรา

วิธีแบบสั้น แม้เขียนสูตรสั้นก็จริง แต่เหมาะกับคนที่มีความเข้าใจเรื่องสูตรระดับหนึ่ง และต้องเข้าใจสูตรอาร์เรย์ (Array Formula) ซึ่งถือเป็นหนึ่งในเรื่องยากที่สุดของ Excel

ถ้าเราไม่เข้าใจสูตรอาร์เรย์ การใช้สูตรแบบสั้นอาจหาคำตอบได้เฉพาะครั้งนี้

แต่ถ้าครั้งหน้ามีเกรด A+ เพิ่มมาด้วยล่ะ?

แต่ถ้าครั้งหน้าเพิ่มการทดสอบจาก 3 เป็น 5 ครั้ง แล้วเลือกเกรดที่ดีที่สุดจาก 5 ครั้งล่ะ?

แต่ถ้าครั้งหน้าเงื่อนไขไม่เหมือนเดิมล่ะ?

เราจะปรับสูตรตามเงื่อนไขที่เปลี่ยนแปลงได้ไหม?

ถ้าไม่ได้ การใช้สูตรแบบยาวที่มีคอลัมน์ช่วย (วิธีที่ 1) แม้อาจดูวุ่นวาย แต่เป็นวิธีการที่ชัดเจน สะดวกต่อการแก้ไข

สูตรที่ดีที่สุด ไม่ใช่สูตรที่สั้นที่สุด แต่เป็นสูตรที่เราเข้าใจมากที่สุด

(แน่นอนว่า ถ้าเราเข้าใจสูตรแบบสั้น ถ้าครั้งหน้าเงื่อนไขเปลี่ยนแปลงก็ไม่ใช่ปัญหา จะปรับสูตรให้พลิกแพลงยังไงก็ได้)

เลือกสูตรที่เหมาะกับเรา สั้นบ้าง ยาวบ้าง ไม่เห็นเป็นไรเลย …

ได้คำตอบที่ถูกต้องก็พอแล้ว ^__^

วันนี้เขียนสูตรยาว ฝึกใช้ ฝึกคิดบ่อยๆ ความเข้าใจก็มากขึ้น วันหน้าสูตรก็สั้นเองครับ ^__^

ใครสนใจไฟล์ตัวอย่างของบทความนี้ ดาวน์โหลดเพื่อศึกษาได้เลยครับ