สูตรคำนวณเพื่อตัดคะแนนคนที่มาสาย

วันนี้ได้รับเมล์ฉบับหนึ่ง จากกลุ่มข่าว SiamHRM สอบถามเรื่อง การเขียนสูตรเพื่อหักคะแนนการมาสาย สำหรับการประเมินผลงานประจำปี มีรายละเอียดดังนี้

ขอความช่วยเหลือสูตร Excel ค่ะ *ข้อมูล*** Column 1-6 เป็นข้อมูลที่ได้จาก Time Card ของพนักงาน Column 7 เป็นคะแนนที่จะหักพนักงานในการประเมินผลงานประจำปี *คำถาม*** ถ้ามาสายไม่เกิน 15 นาที โดนหัก 1 คะแนน ถ้ามาสายเกินกว่า 15 นาทีแต่ไม่เกิน 30 โดนหัก 2 คะแนน ถ้ามาสายเกินกว่า 30 นาทีโดนหัก 3 คะแนน เราจะเขียนสูตรใน Column 7 อย่างไรดีคะ? รบกวนผู้รู้ด้วยค่ะ* ขอบคุณค่ะ

จากข้อมูลดังกล่าว ผมทำเป็นรูปภาพออกมาให้เห็นชัดๆ ดังรูป



ก่อนหน้าที่ผมจะให้คำตอบ สำหรับคำถามนี้ ได้มีเพื่อนสมาชิกท่านอื่น ได้ให้คำตอบไว้บ้างแล้ว ซึ่งมีอยู่ 2 ท่าน 2 คำตอบ แต่เมื่อผมพิจารณาจากเงื่อนไขของผู้ถาม มีความเห็นว่า ทั้งสองคำตอบดังกล่าว ยังไม่ถูกต้อง เรามาดูและวิเคราะห์กันทีละคำตอบครับ


คำตอบที่ 1

=IF(MINUTE(F2)>0,IF(MINUTE(F2)>15,IF(MINUTE(F2)>30,3,2),1),0)

สำหรับสูตรในคำตอบที่ 1 นี้ จริงๆ แล้วก็ถือว่าใช้ได้ครับ แต่..ไม่ครอบคลุม ทั้งนี้เพราะ ฟังก์ชัน MINUTE นั้น จะส่งเฉพาะค่า "นาที" ออกมาเท่านั้น ไม่ได้คำนึงว่า จะเป็นเวลากี่ชั่วโมง กี่นาที ดังนั้น ถ้ามีพนักงานมาสาย 2ชั่วโมง 3 นาที จากสูตรข้างต้น ก็จะส่งค่าออกมาแค่ 3 นาที จึงถูกตัดคะแนนมาสายเพียง 1 คะแนนเท่านั้น (พนักงานคงยิ้ม แต่ฝ่ายบุคคลคงยิ้มไม่ออก) แต่ถ้ากรณีพนักงานมาสายไม่เกิน 1 ชั่วโมง สูตรนี้ถือว่าใช้ได้ครับ


คำตอบที่ 2

=IF(F2>0,IF(F2<0.010416667, 1,IF(F2<0.020833333,2,3)),0)

สำหรับสูตรในคำตอบที่ 2 นี้ ถือว่าใกล้เคียงครับ และน่าจะใช้ได้เลยล่ะ แต่เมื่อผมพิจารณาจากเงื่อนไขของผู้ถาม ผมเห็นว่า คำตอบนี้ก็ยังไม่ถูกต้องเช่นกัน ทั้งนี้ ถ้าแปลความหมายของสูตรนี้ออกมาจะได้ว่า ถ้า F2 น้อยกว่า 15 นาที หัก 1 คะแนน, และถ้า F2 น้อยกว่า 30 นาที หัก 2 คะแนน ไม่เช่นนั้น หัก 3 คะแนน

ผมตั้งข้อสังเกตว่า แล้วถ้ามาสาย 15 นาทีพอดี หรือมาสาย 30 นาทีพอดี จะถูกตัดกี่คะแนน ก็ถ้าเป็นสูตรในคำตอบที่ 2 นี้ มาสาย 15 นาทีพอดี จะถูกตัด 2 คะแนน และมาสาย 30 นาทีพอดี จะถูกตัด 3 คะแนน ซึ่งดูแล้วว่า ไม่ตรงกับที่โจทย์กำหนด พนักงานคนไหนโดนตัดคะแนนตามนี้ คงโวยวายแน่ๆ

ข้อสังเกต
ตัวเลข 0.010416667 และ 0.020833333 คืออะไร? มาจากไหน?

คนที่ใช้งาน Excel มาพอสมควร และเข้าใจเรื่องของ วัน-เวลา ใน Excel แล้วนั้นคงทราบว่า Excel ใช้เลขลำดับในการคำนวณเรื่องของวันที่และเวลา โดยเลขจำนวนเต็มแทนวัน เศษทศนิยมแทนเวลา (กรุณาอ่านรายละเอียดเพิ่มเติม เกี่ยวกับเรื่อง วัน-เวลาใน Excel ได้ที่ Blog ของคุณคนควน)


ดังนั้นผมขอเสนอคำตอบที่ 3 ครับ

=IF(F2>(1/24/60*30),3,IF(F2>(1/24/60*15),2,IF(F2>0,1,"")))

เนื่องจากตัวเลข 0.010416667 และ 0.020833333 เป็นทศนิยมไม่รู้จบ แต่การที่สูตรในคำตอบที่ 2 ตัดทศนิยมมาแค่ 9 หลัก แม้จะดูว่าละเอียดแล้ว แต่ก็ใช่ว่าจะถูกต้อง เพราะค่าที่ได้ จะคลาดเคลื่อนจากเวลาจริงเล็กน้อย ซึ่งการคำนวณของ Excel สามารถจับได้ เช่นในกรณี มาสาย 30 นาทีพอดี จะให้ผลลัพธ์ที่ผิด (ส่วนกรณีมาสาย 15 นาทีพอดีก็เช่นเดียวกัน แต่ด้วยความฟลุ๊คของตัวเลขทศนิยม จึงทำให้ผลลัพธ์ออกมาถูกต้อง)

ดังนั้น ผมจึงไม่ใช้เลขทศนิยมดังกล่าว แต่ใช้การคำนวณแทน โดยคิดจาก 1 วันมี 24 ชั่วโมง, 1 ชั่วโมงมี 60 นาที และสูตรที่ 3 ของผมนี้ ถ้ามาสาย 15 นาทีพอดี หรือ 30 นาทีพอดี ก็จะแสดงคะแนนที่จะต้องโดนตัดได้อย่างถูกต้อง


เพิ่มเติมเกี่ยวกับการหาเวลาที่มาสาย

จากรูปข้างบน ในคอลัมน์ F คือ ระยะเวลาที่มาสาย โดยคิดจาก

เวลาเข้างานจริง (Attend Time) - เวลาเข้างานที่กำหนด (Office Time)

สมมติว่า ถ้าเรามีข้อมูลพนักงานเป็นร้อยเป็นพันคน ซึ่งมีทั้งที่มาสาย และไม่สาย ถ้าเราใส่สูตร =D2-E2 และคัดลอกสูตรไปใช้กับทุกบรรทัด จะทำให้คนที่มา "ไม่สาย" จะมีเวลาเป็น ######## ซึ่งดูไม่งาม

ดังนั้นที่ F2 จึงควรพิมพ์สูตรดังนี้ =IF(D2>E2, D2-E2, "")

ฝากไว้เพื่อพิจารณาครับ

Create Date : 11 กันยายน 2551 Last Update : 11 กันยายน 2551 18:25:53 น. Counter : 9605 Pageviews.		20 comments
Share Tweet

	No. 1054 บริษัทชอบแก่ แบบไหน ..? ไวน์กับสายน้ำ (11 ม.ค. 2565 05:29:10 น.)		Tesla ดีตรงไหน ทำไมขายดีนัก gollygui (12 ม.ค. 2565 11:56:12 น.)
	เคสไอโฟน ลายน่ารักสุดคิ้วท์ สาวกไอโฟนต้องมี มันต๊าชมาก !! ไม่กินขอบ ไม่ดันฟิลม์ ไม่ติดกล้อง ~ 🎉ร สมาชิกหมายเลข 1495151 (8 ม.ค. 2565 01:25:08 น.)		"ฝนหลวงฯ"เตรียมฝึกบินทบทวนประจำปีก่อนลุยปฏิบัติการ สมาชิกหมายเลข 3402302 (7 ม.ค. 2565 16:16:26 น.)