การคำนวณคะแนนทีปกติ และตัดเกรดโดยใช้ โปรแกรมสำเร็จรูป Excel

 

บทความต่อไปนี้เป็นการนำโปรแกรมสำเร็จรูป Excel มาคำนวณคะแนนทีปกติ และตัดเกรด เพื่อเปรียบเทียบกับผลลัพธ์ที่ได้จากโปรแกรม TScoreCal

ข้อมูลที่ใช้ในครั้งนี้เป็นคะแนนดิบของผลการสอบวิชาฟิสิกส์ 1 ปีการศึกษา 1/2550 ของสาขาวิชาฟิสิกส์ คณะวิทยาศาสตร์และเทคโนโลยี มหาวิทยาลัยเทคโนโลยีราชมงคล ธัญบุรี จำนวน 1,413 คน ในที่นี้จะใช้ Excel รุ่น 2003 ในการคำนวณ

ลำดับขั้นตอนการคำนวณคะแนนทีปกติ โดยใช้ Excel มีดังนี้

1. เมื่อเรียกใช้โปรแกรม Excel แล้ว ให้ป้อนคะแนนดิบ เก็บไว้ในคอลัมน์ A ดังรูปที่ 1 (ต้องการทดลองทำตาม ให้ download ไฟล์ชื่อ p1_1_50.txt เปิดไฟล์ด้วย NotePad แล้ว copy และ paste ไปยังโปรแกรม Excel)

 

รูปที่ 1 นำคะแนนดิบเก็บไว้ในคอลัมน์ A

2. เรียงลำดับข้อมูลในคอลัมน์ A ในที่นี้จะเรียงลำดับจากคะแนนสูงไปคะแนนต่ำ

เริ่มต้นด้วยการใช้เมาส์ลากระบายคอลัมน์ A ตั้งแต่แถวที่ 1 จนถึงแถวที่ 1413 จากนั้นคลิกที่เมนู Data -> Sort… จะปรากฏหน้าต่างขึ้นมาดังรูปที่ 2

รูปที่ 2 แสดงหน้าต่าง Sort ข้อมูล

 

ที่หัวข้อ Sort by ในหน้าต่าง Sort ให้คลิกที่ปุ่ม Descending

ที่หัวข้อ My data range has ให้คลิกที่ No header row

คลิกปุ่ม OK โปรแกรมจะทำการเรียงลำดับคะแนนจากค่าสูงสุดไปยังค่าต่ำสุด

 

3. ข้อมูลในคอลัมน์ A ยังคงถูกระบายทึบ (ถ้ายังไม่ถูกระบาย ให้ลากเมาส์ระบายข้อมูลในคอลัมน์ A ทั้งหมดอีกครั้ง )

คลิกที่เมนู Data -> Filter -> advance Filter เพื่อทำการคัดเลือกคะแนนที่ไม่ซ้ำกันเลย และเก็บไว้ในคอลัมน์ C

รูปที่ 3 เลือกเฉพาะคะแนนที่ค่าไม่ซ้ำกันเลย เก็บไว้ในคอลัมน์ C

คลิกปุ่ม OK ที่คอลัมน์ C จะปรากฏคะแนนจำนวน 415 แถว ซึ่งเป็นคะแนนที่มีค่าไม่ซ้ำกัน ดังรูปที่ 4

รูปที่ 4 คะแนนในคอลัมน์ C จะมีค่าไม่ซ้ำกัน

 

4. หาความถี่ของคะแนนดิบแต่ละค่า ที่คอลัมน์ C เราอาจเว้นที่ว่างสำหรับเขียนหัวคอลัมน์ ซึ่งต่อไปจะมีเพิ่มอีกหลายคอลัมน์ เพื่อจะได้ไม่สับสนในภายหลัง

ที่คอลัมน์ D ให้พิมพ์สูตรต่อไปนี้ลงในเซลล์ D2

=countif($A$1:$A$1413,C2)

ดังรูปที่ 5

รูปที่ 5 ใส่สูตรลงไปในเซลล์ C2

จากนั้นให้ copy สูตรในเซลล์ C2 ลงมาใส่ในทุกแถวของคะแนนทุกค่า เครื่องหมาย $ หน้าชื่อคอลัมน์และหน้าตัวเลขบอกแถว เป็นการบอกให้ Excel รู้ว่าช่วงที่กำหนดไว้ คือ A1 ถึง A1413 นี้จะคงที่เสมอ ไม่ว่าสูตรจะถูก copy ไปยังเซลล์ใด

ความหมายของสูตร countif($A$1:$A$1413,C2) คือ ให้นับจำนวนคะแนนที่เท่ากับคะแนนในเซลล์ C2 (ในที่นี้คือ 72) ในช่วงตั้งแต่ A1 ถึง A1413 มีอยู่กี่ตัว เป็นการหาความถี่ของคะแนนแต่ละค่านั่นเอง

รูปที่ 6 แสดงค่าความถี่ที่ได้

5. หาความถี่สะสม (Cumulative frequency, cf) หาได้จากนำความถี่สะสมของคะแนนที่อยู่ต่ำกว่า 1 แถว บวกด้วยความถี่ของคะแนนในแถวนั้น

เลื่อน scrollbar ไปจนถึงคะแนนต่ำสุด ในที่นี้คือ แถวที่ 416 คะแนนดิบ 1.45

ที่เซลล์ E416 พิมพ์ 1 ลงไปเซลล์นี้ ( 1 นี้คือความถี่ของคะแนนดิบ 1.45 )

ที่เซลล์ E415 พิมพ์สูตร = E416 + D415

Copy สูตรในเซลล์ E415 ลงไปในเซลล์ E414 จนถึง E2

จะได้ผลลัพธ์ซึ่งเป็นค่าความถี่สะสมในคอลัมน์ E

 

รูปที่ 7 แสดงการเขียนสูตรหาความถี่สะสม

 

ถ้าทำได้ถูกต้อง จะสังเกตเห็นว่า เซลล์ E2 ซึ่งเป็นเซลล์แรกสุดของความถี่สะสม จะมีค่าเท่ากับจำนวนของคะแนนดิบ ในที่นี้คือ 1413 ดังรูปที่ 8

รูปที่ 8 แสดงค่าความถี่สะสม (CF ) ที่คำนวณได้

 

6. คำนวณหาเปอร์เซ็นต์ไทล์ของคะแนนดิบแต่ละคะแนน หาได้จากสูตร

เลื่อน scrollbar ลงไปด้านล่างของตารางคำนวณ ใช้เมาส์คลิกที่เซลล์ F416

พิมพ์ 0.0354 ลงไปในเซลล์ F416 ( ตัวเลขนี้ได้มาจาก (0.5)(1)*100 / (1413) )

ที่เซลล์ F415 เขียนสูตรต่อไปนี้ลงในเซลล์

= (E416+0.5*D415)*100 / 1413

รูปที่ 9 เขียนสูตรเพื่อหา Percentile

 

Copy สูตรจากเซลล์ F415 ไปไว้ในเซลล์ F414 ขึ้นไปเรื่อย ๆ จนถึง เซลล์ F2

จะได้ค่าเปอร์เซ็นต์ไทล์ เก็บไว้ในคอลัมน์ F

ที่คอลัมน์ ปรับตำแหน่งทศนิยมของ Percentile ให้เหลือ 2 ตำแหน่ง

รูปที่ 10 แสดงเปอร์เซ็นต์ไทล์ในคอลัมน์ F หลังจากปรับเลขทศนิยมให้เหลือ 2 ตำแหน่ง

7. คำนวณหาคะแนนทีปกติ โดยคำนวณจากพื้นที่ใต้เส้นโค้งปกติ เรียกใช้ฟังก์ชัน
NormSInv( ) ซึ่งเป็นฟังก์ชัน Built in ในโปรแกรม Excel

คลิกเมาส์ที่เซลล์ G2 พิมพ์สูตรต่อไปนี้ลงไปในเซลล์นี้

= 10*NormSInv(F2/100) +50

รูปที่ 11 แสดงการใส่สูตรเพื่อหาคะแนนทีปกติ

Copy สูตรจากเซลล์ G2 ไปใส่ในเซลล์ G3 จนถึงเซลล์ G416

จัดรูปแบบตัวเลขในคอลัมน์ G2 ให้เหลือทศนิยมเพียง 2 ตำแหน่ง

รูปที่ 12 แสดงคะแนน T ปกติที่คำนวณได้ ในคอลัมน์ G

 

8. ขั้นต่อไปคือการตัดเกรด ในที่นี้จะตัดเกรดเพียง 5 เกรด คือ A, B, C, D และ F จะให้โปรแกรม Excel ใส่เกรดให้ โดยเขียนคำสั่งใน Visual Basic Editor ดังต่อไปนี้

คลิกเมนูที่คำสั่ง Tools -> Marco -> Visual Basic Editor หรือใช้คีย์ลัด คือ ALT + F11

รูปที่ 13 แสดงการเข้า Visual Basic Editor

หน้าจอภาพจะเปลี่ยนไปเป็นหน้าต่างของ Visual Basic Editor

คลิกเมาส์ปุ่มขวา ที่ชื่อโปรเจกต์ (ในที่นี้คือ VBAProject (Book1.xls)) แล้วคลิกที่ Insert -> Module ดังรูปที่ 14

รูปที่ 14 สร้าง Module สำหรับ Book1.xls

 

จะได้ Module ใหม่ชื่อว่า Module1 พร้อมกับมีหน้าต่างว่าง ขึ้นมาสำหรับพิมพ์คำสั่ง

รูปที่ 15 แสดง Visual Basic Editor สำหรับพิมพ์คำสั่ง

พิมพ์ฟังก์ชันชื่อว่า Grade ( ) เพื่อให้โปรแกรมสามารถกำหนดเกรดให้อย่างอัตโนมัติ พิมพ์คำสั่งลงไป ตามรูปที่ 16 ฟังก์ชัน Grade( ) นี้ใช้ได้เฉพาะ worksheet นี้เท่านั้น เพราะค่า Tmax และ Tmin ได้มาจากข้อมูลในตารางนี้ ถ้าต้องการใช้ฟังก์ชัน Grade ( ) กับ worksheet อื่น ๆ ให้เปลี่ยนค่า Tmax และ Tmin ให้สอดคล้องกับค่าที่ได้ใน worksheet นั้น

รูปที่ 16 คำสั่งที่พิมพ์ลงใน editor ของ VBA ( Visual Basic Application)

Save ฟังก์ชัน Grade ( ) แล้ว ให้กลับมาที่หน้าต่าง worksheet เดิมที่ได้คำนวณคะแนนทีปกติ

ที่ เซลล์ H2 พิมพ์สูตรลงไปในเซลล์ดังนี้

= Grade(G2)

รูปที่ 17 แสดงการเรียกใช้งานฟังก์ชัน Grade ( )

Copy สูตรจากเซลล์ H2 ไปยังเซลล์ H3 จนถึงเซลล์ H416 จะเห็นเกรดปรากฏในคอลัมน์ H

รูปที่ 18 เกรดจะแสดงให้เห็นในคอลัมน์ H

9. เปรียบเทียบผลลัพธ์ที่ได้จากโปรแกรม Excel พบว่า ความถี่ ความถี่สะสม Percentile คะแนน T ปกติ ได้ค่าตรงกับค่าที่ได้จากคำนวณของโปรแกรม TScoreCal ทุกคอลัมน์ (ที่ทศนิยม 2 ตำแหน่ง) ขั้นตอนวิธีการตัดเกรดเมื่อเขียนคำสั่งให้ Excel ประมวลผล จะได้ผลลัพธ์ตรงกับที่คำนวณได้จากโปรแกรม TScoreCal เช่นกัน ดังรูปที่ 19 ด้านซ้ายมือ บริเวณพื้นที่สีขาวคือผลลัพธ์ที่ได้จากโปรแกรม Excel ด้านขวามือ บริเวณพื้นที่ที่แรเงาเป็นผลลัพธ์ที่ได้จากการคำนวณของโปรแกรม TScoreCal โดยใช้วิธี copy และ paste เข้ามาในโปรแกรม Excel ถ้าต้องการดูตารางที่ครบสมบูรณ์ สามารถเปิดดูได้จากไฟล์ชื่อ p1_1_50.xls

รูปที่ 19 แสดงผลลัพธ์ช่วงต้น ๆ และช่วงท้ายของตารางเปรียบเทียบผลลัพธ์การคำนวณ คะแนนทีปกติจากโปรแกรม