การใช้ฟังก์ชัน

แบบฝึกหัด

ตอนที่ 10. ฟังก์ชันที่ใช้ในการกำหนดเกรดโดยวิธีอิงเกณฑ์

---

10.1. กล่าวนำ การวัดประเมินผลการเรียนการสอนของนักศึกษาในระดับอุดมศึกษา จะใช้ผลของการศึกษาในหลาย ๆ ด้าน ทั้งทางด้านการเขียน การพูด การบรรยาย การฝึกปฏิบัติ รวมถึงการทดสอบความสามารถทางด้านวิชาการเป็นผลการศึกษาที่ออกมาในรูปของการให้คะแนน และนำมากำหนดเกรดจากคะแนนที่ได้
การกำหนดเกรดโดยใช้ Excel มีหลายวิธี สำหรับการเรียนรู้การใช้งาน Excel ในการบรรยายครั้งนี้จะกล่าวถึงวิธีการนำฟังก์ชัน IF และฟังก์ชัน VLOOKUP มาใช้ในการกำหนดเกรด และจะสรุปผลจำนวนนักศึกษาและเปอร์เซ็นต์ที่ได้จากช่วงคะแนนต่ำสุดและสูงสุดในแต่ละเกรดด้วย

10.2. แนวทางการกำหนดเกรด การกำหนดเกรดสามารถทำได้ใน 2 วิธี คือกำหนดเกรดโดยอิงกลุ่มและกำหนดเกรดโดยอิงเกณฑ์ สำหรับการบรรยายในครั้งนี้จะกล่าวถึงการกำหนดเกรดโดยอิงเกณฑ์จากคะแนนและเกรดที่กำหนดไว้เป็นหลัก

10.3. คะแนนและเกรดที่กำหนดไว้
10.3.1 คะแนน เป็นเกณฑ์ช่วงคะแนนที่กำหนดไว้มี 8 ช่วงคะแนน
10.3.2 เกรด เป็นช่วงเกรดที่กำหนดไว้จากที่เทียบจากคะแนนมี 8 เกรด คือ A,B+,B,C+,C,D+,D และ E
คะแนนและเกรดที่กำหนดไว้ที่จะนำมาใช้เป็นตัวอย่างในครั้งนี้ มีแสดงไว้ในรูปที่ 10-1
รูปที่ 10-1 คะแนนและเกรดที่กำหนดไว้
รูปที่ 10-2 คะแนนและเกรดที่ได้
10.4. ฟังก์ชัน IF
เป็นฟังก์ชันที่สร้างเงื่อนไขของคะแนนขึ้นมาและให้แสดงผลเกรดออกมาตามเงื่อนไขที่กำหนด ช่วงคะแนนของเงื่อนไขที่สร้างขึ้นในเซลล์ที่กำหนด สามารถเปลี่ยนแปลงได้ตามความต้องการ มีการกำหนดเกรดให้
มีทั้งหมด 8 เกรด ดังนั้นจึงต้องสร้างเงื่อนไขทั้งหมด 8 เงื่อนไข
รูปแบบสูตร
=IF(เซลล์คะแนนที่ได้>=เซลล์คะแนนที่กำหนดไว้ลำดับที่8,"A",IF(เซลล์คะแนนที่ได้>=เซลล์คะแนนที่กำหนดไว้ลำดับที่7,"B+",IF(เซลล์ของคะแนนที่ได้>=เซลล์ของคะแนนที่กำหนดไว้ลำดับที่6,"B",IF(เซลล์ของคะแนนที่ได้>=เซลล์ของคะแนนที่กำหนดไว้ลำดับที่5,"C+",IF(เซลล์ของคะแนนที่ได้>=เซลล์ของคะแนนที่กำหนดไว้ลำดับที่4,"C",IF(เซลล์ของคะแนนที่ได้>=เซลล์ของคะแนนที่กำหนดไว้ลำดับที่3,"D+",IF(เซลล์ของคะแนนที่ได้>=เซลล์ของคะแนนที่กำหนดไว้ลำดับที่2,"D",IF(เซลล์ของคะแนนที่ได้>=เซลล์ของคะแนนที่กำหนดไว้ลำดับที่1,"E"))))))))
วิธีการ
10.4.1 สร้างตารางและเกรดที่ได้ (รูปที่ 10-2)
10.4.2 ไปที่เซลล์ F18 สร้างฟังก์ชัน IF โดยการพิมพ์ลงในเซลล์ F18 ดังนี้
=IF(E18>=C12,"A",IF(E18>=C11,"B+",IF(E18>=C10,"B",IF(E18>=C9,"C+",IF(E18>=C8,"C",IF(E18>=C7,"D+",IF(E18>=C6,"D",IF(E18>=C5,"E")))))))) กดปุ่ม Enter
10.4.3 ตรึงเซลล์ที่อ้างถึงจากคะแนนที่กำหนดไว้ การตรึงเซลล์ไว้จะทำให้ผลการคัดลอกในเซลล์ที่ตรึงไว้จะไม่เปลี่ยนแปลง เนื่องจากในทางปฏิบัติ การสร้างฟังก์ชัน IF ขึ้นมา จะสร้างเพียงเซลล์เดียว หลังจากนั้นจะใช้วิธีคัดลอกไปยังส่วนของเซลล์ที่เหลือ ถ้าไม่ได้ตรึงเซลล์นั้น ๆ ไว้ ผลการคัดลอกจะไม่ถูกต้องโดยจะวนไปยังเซลล์ในส่วนอื่นที่ไม่เป็นคะแนนที่กำหนดไว้ เซลล์ที่ต้องตรึงไว้มีเซลล์ C12,C11,C10,C9,C8,C7,C6 และ C5 โดยไปที่สูตรที่เขียนไว้ในเซลล์ F18 กดปุ่มให้แถบ cursor ไปกระพริบอยู่หน้าทุกตัวอักษร C ดังกล่าว กดปุ่ม F4 กดปุ่ม Enter จะได้สูตรที่ได้ตรึงเซลล์ไว้แล้วดังนี้
=IF(E18>=$C$12,"A",IF(E18>=$C$11,"B+",IF(E18>=$C$10,"B",IF(E18>=$C$9,"C+",IF(E18>=$C$8,"C",IF(E18>=$C$7,"D+",IF(E18>=$C$6,"D",IF(E18>=$C$5,"E"))))))))

รูปที่ 10-3 ผลเกรดที่ได้ของฟังก์ชัน IF

10.4.4 คัดลอกเซลล์ F18 โดยกดปุ่มเลือกเซลล์ F18 คลิกเมาส์ปุ่มขวาเพื่อเรียกเมนูลัด เลือกคัดลอก กดเลือกช่วงเซลล์ F19:F25 กดปุ่ม Enter ผลการใช้ฟังก์ชัน IF มีแสดงไว้ในรูปที่ 10-3
ข้อสังเกต การนำฟังก์ชัน IF มาใช้ในการกำหนดเกรดนี้ต้องเขียนสูตรยาว รูปแบบสูตรจะมีคำสั่ง ข้อกำหนดและมีสัญญลักษ์ต่าง ๆ มากมาย จึงมีโอกาสผิดพลาดได้มาก และมีข้อจำกัดอีกอย่างหนึ่งก็คือตัวโปรแกรมกำหนดให้เขียนฟังก์ชัน IF ซ้อนได้ไม่เกิน 8 ชั้น

10.5. ฟังก์ชัน VLOOKUP เป็นฟังก์ชันที่นำมาใช้ในการค้นหาข้อมูลของคะแนนในแนวตั้ง (Vertical) โดยการค้นหาผลเกรดจากคะแนนที่ได้ว่าตรงตามตามช่วงตารางคะแนนและเกรดที่กำหนดไว้หรือไม่ ถ้าผลการค้นหาตรงกัน ก็จะแสดงผลเกรดออกมา ตารางคะแนนและเกรดสามารถเปลี่ยนแปลงได้ตามความต้องการ ก่อนใช้ฟังก์ชัน VLOOKUP ต้องจัดเรียงข้อมูลจากน้อยไปหามาก เนื่องจากฟังก์ชัน VLOOKUP ไม่สามารถแสดงผลการค้นหาออกมาได้ถ้าข้อมูลที่อ้างอิงถึงยังไม่ได้จัดเรียงข้อมูลจากน้อยไปหามากมาก่อน (คะแนนที่กำหนดไว้ได้จัดเรียงตามลำดับแล้ว)
รูปแบบสูตร
=VLOOKUP(เซลล์ของคะแนนที่ได้,ชื่อตารางคะแนนและเกรดที่ตั้งไว้,ลำดับที่สดมภ์ของตารางเกรด)
วิธีการ
10.5.1 ตั้งชื่อตารางคะแนนและเกรดที่ตั้งไว้เป็นชื่อ เกรด โดยไปที่เมนู แทรก/ชื่อ/กำหนด ที่กรอบตอบโต้ กำหนดชื่อ ในส่วนของชื่อในแผ่นงานให้พิมพ์ เกรด ในส่วนของอ้างอิงไปยังให้เลือกช่วงเซลล์ที่เป็นตารางคะแนนและเกรดที่กำหนดไว้โดยการคลิกที่ เลือกช่วงเซลล์ C5:D12 คลิกปุ่มอีกครั้งหนึ่ง กดปุ่ม ตกลง
รูปที่ 10-4 ผลเกรดที่ใช้ฟังก์ชัน VLOOKUP
10.5.2 จากตารางรูปที่ 10-2 สร้างฟังก์ชัน VLOOKUP โดยการพิมพ์ลงในเซลล์ G18 ดังนี้
=VLOOKUP(E8,เกรด,2) กดปุ่ม Enter จะได้เกรดออกมาในเซลล์ G18
10.5.3 คัดลอกสูตรจากเซลล์ G18 ไปยังเซลล์ G19:G25 จะได้เกรดออกมาทั้งหมด (รูปที่ 10-4)
10.6. สรุปผลการกำหนดเกรด เป็นการหาผลรวมของจำนวนนักศึกษาที่ได้ในแต่ละเกรดโดยนำช่วงคะแนนที่ได้ทั้งหมด (ที่ตั้งชื่อเป็น Grades) มาตรวจสอบดูว่าอยู่ในช่วงจากคะแนนต่ำสุด-สูงสุดที่ตั้งไว้ในแต่ละช่วงมีจำนวนกี่คนและมีกี่เปอร์เซ็นต์
รูปแบบสูตร =SUM(ชื่อช่วงเซลล์คะแนนทั้งหมด>=เซลล์ค่าต่ำสุดของคะแนนในเกรดนั้น)*(ชื่อช่วงเซลล์คะแนนทั้งหมด<=เซลล์ค่าสูงสุดสุดของคะแนนในเกรดนั้น)
การปรับสูตร สูตรที่ได้สร้างขึ้นต้องให้แสดงผลแบบ Array (สูตร Array เป็นสูตรที่ใช้เพื่อให้แสดงผลที่สลับซับซ้อนที่สูตรธรรมดาไม่สามารถแสดงผลลัพท์ได้) หลังจากสร้างสูตรเสร็จ ให้กดปุ่ม Ctrl+Shift+Enter สูตรดังกล่าวจะใส่เครื่องหมายปีกกาให้
วิธีการหาจำนวนนักศึกษาและเปอร์เซ็นต์ของเกรดที่ได้
10.6.1 สร้างตารางจำนวนนักศึกษาและเปอร์เซ็นต์ที่ได้ที่ได้ (รูปที่ 10-5)
10.6.2 ตั้งชื่อคะแนนที่ได้ในช่วงเซลล์ E18:E25 เป็นชื่อ คะแนน โดยไปที่เมนู แทรก/ชื่อ/กำหนด ที่กรอบตอบโต้ กำหนดชื่อ ในส่วนของชื่อในแผ่นงานให้พิมพ์ คะแนน ในส่วนของอ้างอิงไปยังให้เลือกช่วงเซลล์ที่เป็นคะแนนที่ได้โดยการคลิกที่ เลือกช่วงเซลล์ E18:E25 คลิกปุ่ม อีกครั้งหนึ่ง กดปุ่ม ตกลง
10.6.3 จากตารางรูปที่ 10-5 สร้างสูตรเพื่อสรุปหาจำนวนนักศึกษาที่ได้ในแต่ละเกรดโดยการพิมพ์ลงไปที่เซลล์ J5 ดังนี้ =SUM((คะแนน>=G5)*(คะแนน<=H5)) หลังจากนั้นให้กดปุ่ม Ctrl+Shift+Enter สูตรดังกล่าวจะใส่เครื่องหมายปีกกาให้ ดังนี้ {=SUM((คะแนน>=G5)*(คะแนน<=H5))} จะได้จำนวนนักศึกษาในเซลล์ J5 คัดลอกสูตรจากเซลล์ J5 ไปยังเซลล์ J6:J12 จะได้จำนวนนักศึกษาออกมาทั้งหมด (รูปที่ 10-6)
10.6.4 หาเปอร์เซ็นต์ที่ได้ในแต่ละเกรด ไปที่เซลล์ K5 พิมพ์ =J5/SUM($J$5:$J$12) กดปุ่ม Enter จะได้จำนวนเปอร์เซ็นต์ปรากฏออกมาในเซลล์ K5 คัดลอกสูตรจากเซลล์ K5 ไปยังเซลล์ K6:K12 จะได้เปอร์เซ็นต์ออกมาทั้งหมด (รูปที่ 10-6)

รูปที่ 10-5 ตารางจำนวนนักศึกษาและเปอร์เซ็นต์ที่ได้  

รูปที่ 10-6 ผลสรุปจำนวนนักศึกษาและเปอร์เซ็นต์ที่ได้
10.7. แบบฝึกหัด ให้กำหนดเกณฑ์คะแนนของเกรดที่ได้ขึ้นมาเองลงในช่องที่กำหนดมาให้ นำเกณฑ์ดังกล่าวมากำหนดเกรดให้นักศึกษาโดยใช้ฟังก์ชัน IF และฟังก์ชัน VLOOKUP และสรุปผลการกำหนดเกรดที่ได้ด้วย (รูปที่ 10-7)
https://docs.google.com/spreadsheet/viewform?formkey=dHU4YXE1ZVpuUUVIaUsxVHFsc2YwTXc6MQ