“อยากเขียนสูตรเลือกเกรดครั้งที่ดีที่สุด ทำไงดี?” เพื่อนผมคนหนึ่งถาม
“ตอนนี้ทำแมนวลอยู่ เหนื่อยมากมาย” (ประโยคนี้แปลว่าต้องช่วยแล้วล่ะ)
โจทย์ของเพื่อนผมหน้าตาประมาณนี้ครับ
เพื่อนผมคนนี้เป็นอาจารย์ วิชาที่สอนมีนักเรียนลงเรียนมากกว่า 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) แม้อาจดูวุ่นวาย แต่เป็นวิธีการที่ชัดเจน สะดวกต่อการแก้ไข
สูตรที่ดีที่สุด ไม่ใช่สูตรที่สั้นที่สุด แต่เป็นสูตรที่เราเข้าใจมากที่สุด
(แน่นอนว่า ถ้าเราเข้าใจสูตรแบบสั้น ถ้าครั้งหน้าเงื่อนไขเปลี่ยนแปลงก็ไม่ใช่ปัญหา จะปรับสูตรให้พลิกแพลงยังไงก็ได้)
เลือกสูตรที่เหมาะกับเรา สั้นบ้าง ยาวบ้าง ไม่เห็นเป็นไรเลย …
ได้คำตอบที่ถูกต้องก็พอแล้ว ^__^
วันนี้เขียนสูตรยาว ฝึกใช้ ฝึกคิดบ่อยๆ ความเข้าใจก็มากขึ้น วันหน้าสูตรก็สั้นเองครับ ^__^
ใครสนใจไฟล์ตัวอย่างของบทความนี้ ดาวน์โหลดเพื่อศึกษาได้เลยครับ
.
หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่ๆโดยคลิก Like เฟสบุ๊คแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่
อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^__^
