สูตรและฟังก์ชันใน Google Sheets สำหรับสมุดบันทึกคะแนนของนักเรียน

สูตรและฟังก์ชันใน Google Sheets ช่วยให้คุณคำนวณผลลัพธ์โดยอัตโนมัติจากค่าในสเปรดชีต เริ่มต้นสูตรด้วยเครื่องหมายเท่ากับ (= =) จากนั้นป้อนการคำนวณหรือฟังก์ชัน เช่น `input` AVERAGE, VLOOKUP`input` หรือ ` IFinput` ตัวอย่างนี้สร้างสมุดบันทึกคะแนนของนักเรียนที่แปลงคะแนนเป็นเกรดตัวอักษร มอบรางวัล และทำเครื่องหมายนักเรียนแต่ละคนว่าผ่านหรือสอบไม่ผ่าน

คำแนะนำเก่าๆ เกี่ยวกับ “สเปรดชีต Google Docs” นั้นหมายถึงอินเทอร์เฟซที่เลิกใช้งานแล้ว ปัจจุบัน Google Sheets เป็นแอปพลิเคชันสเปรดชีต และสามารถป้อนสูตรลงในเซลล์ได้โดยตรง กล่องช่วยเหลือสูตรจะแสดงไวยากรณ์ของฟังก์ชันและคำแนะนำขณะที่คุณพิมพ์ สำหรับขั้นตอนการทำงานในปัจจุบัน โปรดดูคู่มือของ Google เกี่ยวกับการเพิ่มสูตรและฟังก์ชัน

ตั้งค่าสมุดบันทึกคะแนน

ในกระดาษเปล่า ให้สร้างหัวข้อเหล่านี้ในแถวที่ 1: นักเรียน , คะแนน , เกรด , รางวัลและสถานะป้อนชื่อนักเรียนในคอลัมน์ A และคะแนนตัวเลขในคอลัมน์ B เก็บข้อมูลต้นฉบับและผลลัพธ์ที่คำนวณได้ไว้ในคอลัมน์ที่แยกกัน เพื่อให้คุณสามารถตรวจสอบหรือเปลี่ยนแปลงคะแนนได้โดยไม่ต้องเขียนทับสูตร

สำหรับการคำนวณอย่างรวดเร็ว คุณสามารถป้อน=AVERAGE(B2:B6)ค่าเฉลี่ยลงในเซลล์ว่างได้ สูตรคือข้อความเต็มที่ป้อนลงในเซลล์ ส่วนฟังก์ชันคือการดำเนินการในตัว เช่นAVERAGEที่สามารถเป็นส่วนหนึ่งของข้อความนั้นได้ Google Sheets จะไฮไลต์เซลล์ที่อ้างอิงขณะที่คุณแก้ไข และอาจแสดงความช่วยเหลือเกี่ยวกับฟังก์ชันไว้ใต้สูตร

ใช้ฟังก์ชัน VLOOKUP เพื่อแปลงคะแนนเป็นเกรด

สร้างตารางเกณฑ์สองคอลัมน์ใน H1:I6 ใส่Score Minใน H1 และGradeใน I1 จากนั้นป้อนเกณฑ์ตามลำดับจากน้อยไปมาก: 0 / F, 60 / D, 70 / C, 80 / B และ 90 / A เกณฑ์เหล่านี้เป็นตัวอย่าง ให้แทนที่ด้วยกฎที่ใช้ในหลักสูตรของคุณ เนื่องจากตารางนี้ใช้การจับคู่โดยประมาณ ให้เรียงลำดับเกณฑ์ตัวเลขจากน้อยไปมาก

คลิกที่ C2 แล้วป้อน:

=VLOOKUP(B2,$H$2:$I$6,2,TRUE)

VLOOKUPฟังก์ชันนี้จะค้นหาค่าจากเซลล์ B2 ในคอลัมน์แรกของช่วงที่เลือก ตัวเลข 2 บอกให้ฟังก์ชันส่งคืนค่าจากคอลัมน์ที่สอง และTRUEขอให้ค้นหาค่าที่ตรงกันโดยประมาณ ด้วยค่าเกณฑ์ข้างต้น 88 จะส่งคืนค่า B ในขณะที่ 67 จะส่งคืนค่า D เครื่องหมายดอลลาร์ทำให้ช่วงการค้นหาเป็นแบบสัมบูรณ์ ดังนั้นช่วงการค้นหาจะไม่เปลี่ยนแปลงเมื่อคุณคัดลอกสูตร Google แนะนำให้เรียงลำดับจากน้อยไปมากสำหรับการค้นหาค่าที่ตรงกันโดยประมาณ ตารางที่ไม่ได้เรียงลำดับอาจส่งคืนเกรดที่ไม่ถูกต้อง โปรดดูไวยากรณ์และคำแนะนำ อย่างเป็นทางการของ VLOOKUP

เติมสูตรลงในคอลัมน์

หลังจากตรวจสอบแล้วว่า C2 แสดงเกรดที่คาดหวัง ให้เลือก C2 แล้วลากตัวจัดการการเติมสีน้ำเงินเล็กๆ ที่มุมล่างขวาลงมาจนถึงแถวนักเรียนที่เหลือ การอ้างอิงแถวจะเปลี่ยนจาก B2 เป็น B3, B4 และอื่นๆ ในขณะที่$H$2:$I$6ค่าคงที่ยังคงอยู่ ตรวจสอบแถวสุดท้ายเพื่อให้แน่ใจว่าสูตรที่คัดลอกมาครอบคลุมนักเรียนทุกคน

หากชีตของคุณมีเพียงไม่กี่แถว การคัดลอกและวางสูตรก็ใช้ได้เช่นกัน แต่สำหรับรายการที่ยาวกว่า การใช้ฟังก์ชันเติมลง (fill down) จะเร็วกว่า ควรขยายช่วงการค้นหาให้กว้างพอสำหรับทุกเกณฑ์ และตรวจสอบให้แน่ใจว่าคะแนนเป็นตัวเลข ไม่ใช่ข้อความ

คำนวณคะแนนและสถานะผ่านหรือไม่ผ่าน

คุณสามารถใช้ตารางค้นหาที่สองสำหรับรางวัลได้ ใส่เกรดลงใน K2:K6 และป้ายกำกับรางวัลหรือจำนวนเงินรางวัลที่เกี่ยวข้องลงใน L2:L6 ใน D2 ให้ป้อน=VLOOKUP(C2,$K$2:$L$6,2,FALSE)โดยที่FALSEต้องตรงกับเกรดอย่างแม่นยำ ซึ่งเหมาะสมสำหรับหมวดหมู่เช่น A, B, C, D และ F หากค่าที่ได้เป็นจำนวนเงิน ให้จัดรูปแบบคอลัมน์ D เป็นสกุลเงิน หากเป็นป้ายกำกับ สูตรเดียวกันจะส่งคืนข้อความ เพิ่มแถวสำหรับทุกเกรดเพื่อให้แต่ละเกรดมีรางวัลที่ตรงกัน

หากต้องการระบุคะแนนต่ำกว่า 60 ว่าสอบไม่ผ่าน ให้ป้อนสูตรนี้ในช่อง E2:

=IF(B2<60,"Fail","Pass")

IFตรวจสอบว่าเงื่อนไขเป็นจริงหรือไม่ สูตรนี้จะคืนค่า “สอบตก” เมื่อคะแนนต่ำกว่า 60 และ “ผ่าน” ในกรณีอื่น ๆ เปลี่ยนค่า 60 หากหลักสูตรของคุณใช้เกณฑ์การผ่านที่แตกต่างออกไปเอกสารอ้างอิงอย่างเป็นทางการของฟังก์ชัน IFจะอธิบายเงื่อนไข ผลลัพธ์ที่เป็นจริง และผลลัพธ์ที่เป็นเท็จ

กรอกข้อมูลใน D2 และ E2 เหมือนกับที่ทำใน C2 สำหรับการค้นหาข้อมูลรางวัล ช่วงคะแนนคงที่ยังคงอยู่ ในขณะที่การอ้างอิงเกรดจะเปลี่ยนไปตามแถว ส่วนสำหรับสถานะ การอ้างอิงคะแนนจะเปลี่ยนไปตามแถว

ตรวจสอบปัญหาสูตรทั่วไป

• เกรดที่แสดงไม่ถูกต้อง:โปรดตรวจสอบว่าค่าเกณฑ์ใน H2:H6 เป็นตัวเลขและเรียงลำดับจากน้อยไปมาก เมื่ออาร์กิวเมนต์ VLOOKUP ตัวสุดท้ายเป็นTRUE.
• โปรดทราบ#N/Aว่าการค้นหาแบบตรงตัวอาจไม่พบเกรด หรือคะแนนอาจต่ำกว่าเกณฑ์ขั้นต่ำ ตรวจสอบการสะกดคำ ช่องว่าง และตรวจสอบว่าแต่ละเกรดมีแถวอยู่ในตารางรางวัลหรือไม่
• สูตรที่คัดลอกมาจะเปลี่ยนแปลงตาราง:เพิ่มเครื่องหมายดอลลาร์ลงในช่วงการค้นหา ดังเช่น$H$2:$I$6.
• หากชื่อฟังก์ชันหรือไวยากรณ์ไม่คุ้นเคย ให้เริ่มพิมพ์หลังจากนั้น=แล้วใช้กล่องช่วยเหลือ หรือเรียกดูรายการฟังก์ชัน Google Sheets ปัจจุบันของ Google

เมื่อค่าเกณฑ์ ช่วงการค้นหา และกฎการผ่านตรงกับนโยบายการให้คะแนนของคุณแล้ว โปรแกรมจะคำนวณเกรดและสถานะใหม่เมื่อคุณอัปเดตคะแนน เก็บสำเนาเกณฑ์การให้คะแนนไว้ข้างๆ ข้อมูลเพื่อให้ผู้ร่วมงานสามารถดูได้ว่าสูตรต่างๆ ใช้กฎใดบ้าง