สูตรคำนวณเพื่อตัดคะแนนคนที่มาสาย
วันนี้ได้รับเมล์ฉบับหนึ่ง จากกลุ่มข่าว 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
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 น.)
  


แวะเข้ามาสวัสดีครูเอกอ่ะค่ะ

แบบว่าม๊าไม่ค่อยเก่งเรื่องคำนวณเลย

แงงงงงงงงงงงงงงโง่อ่ะค่ะ
โดย: นางมารร้ายจีจี้ วันที่: 11 กันยายน 2551 เวลา:22:05:44 น.
  
คำตอบที่ 2

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

ผมตั้งข้อสังเกตว่า แล้วถ้ามาสาย 15 นาทีพอดี หรือมาสาย 30 นาทีพอดี จะถูกตัดกี่คะแนน ก็ถ้าเป็นสูตรในคำตอบที่ 2 นี้ มาสาย 15 นาทีพอดี จะถูกตัด 2 คะแนน และมาสาย 30 นาทีพอดี จะถูกตัด 3 คะแนน ซึ่งดูแล้วว่า ไม่ตรงกับที่โจทย์กำหนด พนักงานคนไหนโดนตัดคะแนนตามนี้ คงโวยวายแน่ๆ
====================
ผิดครับ สูตรนี้ถ้ามาสาย 15 นาที จะถูกหัก 1 คะแนนครับ และสูตรนี้จะทำให้คำนวนถูกได้โดย เปลี่ยนค่า 0.020833333 เป็น 0.020833334
ด้วยเหตุผลเรื่องการปัดเศษทศนิยมไม่จำกัดครับ
โดย: karntapong IP: 58.9.169.90 วันที่: 12 กันยายน 2551 เวลา:0:53:01 น.
  
...โอ้โฮ..นี่การใช้ชั้นสูงเลยนะคะเนี่ย...คุณครูเอก
ลิตช์ใช้ Excel แค่ตารางพื้นฐานเองค่า...
โดย: ลิตช์ (Litchi ) วันที่: 12 กันยายน 2551 เวลา:1:27:58 น.
  
สวัสดีครับคุณม๊า และคุณลิตช์
ขอบคุณที่แวะมาเยี่ยมครับ

สวัสดีครับคุณ karntapong
จริงอยู่ที่ มาสาย 15 นาทีพอดี จะถูกตัด 1 คะแนน แต่จริงๆ แล้วเป็นความฟลุ๊ค เนื่องจากการปัดเศษทศนิยมน่ะครับ (ซึ่งผมก็ได้เขียนไว้แล้วในบทความ) ทั้งนี้เพราะ
15 นาที จะเท่ากับ 1/24/60*15 แต่น้อยกว่า 0.010416667 (เนื่องจากการปัดเศษของจำนวนทศนิยมไม่รู้จบ)

ในทำนองเดียวกัน 30 นาที ก็เท่ากับ 1/24/60*30 แต่ก็ยังมากกว่า 0.020833333 อยู่เล็กน้อย ดังนั้นการแก้ปัญหาโดยการเป็นทศนิยมหลักสุดท้ายเป็น 4 จะแก้ปัญหาได้ แต่ผมแค่จะบอกว่า 0.020833334 มันไม่เท่ากับ 30 นาทีน่ะครับ

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

ซึ่งจริงๆ แล้ว ถ้าจะตั้งเงื่อนไขตามโจทย์ ควรจะเป็นคือ "ถ้า F2 น้อยกว่าหรือเท่ากับ 15 นาที หัก 1 คะแนน, และถ้า F2 น้อยกว่าหรือเท่ากับ 30 นาที หัก 2 คะแนน ไม่เช่นนั้น หัก 3 คะแนน" เงื่อนไขแบบนี้จึงจะถือว่าถูกต้องครับ
โดย: ครูเอก วันที่: 12 กันยายน 2551 เวลา:13:19:22 น.
  
คุงเพ่ เค้าก็แค่ชอบผู้ชายหน้าตาดี ผิดตรงไหนค้า

อิอิ

ไก่ไม่กินสมภารอยู่แร้น คริคริ
โดย: patra_vet วันที่: 12 กันยายน 2551 เวลา:14:22:38 น.
  
โห วันนี้ดูบล๊อกไม่เข้าใจฮ่ะ คิดมะเป็น เด้กป4อย่างอะฮั้น อิๆ

แหม ครูก้อ น้องหมอเขาอาจทำอร่อยกว่าเราก็ได้
อยากรู้ก็ตามไปชิมสิจ๊ะ

ฝันดีค่า
โดย: kai (aitai ) วันที่: 12 กันยายน 2551 เวลา:20:50:46 น.
  
สวัสดีค่ะครูเอก

มาเร้วววค่ะ

เพิ่งจะกลับมา

รีบมาเยี่ยมครู

ช่วงนี้เพื่อนๆหลายคนเงียบไป

คงจะกำลังเคลียดเรื่องบ้านเมือง

ครูเอกละค่ะ เคลียดมั้ย
โดย: Opey วันที่: 13 กันยายน 2551 เวลา:0:32:47 น.
  
0.0208449074074074* (0:30:01)
0.0208333333333333* (0:30:00)

ตามนี้แหละครับ
ขึ้นอยู่กับว่าหน่วยวัดละเอียดขนาดไหน ถ้าเอาละเอียดเกินหลักวินาที วิธีผมก็จะใช้ไม่ได้ครับ
โดย: karntapong IP: 58.9.176.73 วันที่: 15 กันยายน 2551 เวลา:12:34:55 น.
  
โอ้โห้ excel ชั้นเทพ...

อยากรบกวนถามครูเอกบ้าง (นอกเรื่องจาก excel) เรื่องโปรแกรม winfax ไม่ทราบว่า ครูเอกใช้โปรแกรมตัวนี้บ่อยไหมคะ

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

wanichtha@hotmail.com
โดย: อยากรบกวนถาม.. IP: 58.147.35.228 วันที่: 15 กันยายน 2551 เวลา:16:33:46 น.
  
สวัสดีครับ คุณ karntapong


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

********************************

สวัสดีครับ คุณ อยากรบกวนถาม..
ผมเองก็เคยใช้ WinFax Pro มานานแล้ว แต่ตอนหลังนี้ เปลี่ยนมาใช้ WinXP Fax เพราะมันมีอยู่แล้วในวินโดวส์ ชักจำไม่ได้แล้วว่า WinFax นั้น มันมีหน้าตายังงัย ต้องขอโทษด้วยนะครับ
โดย: ครูเอก วันที่: 15 กันยายน 2551 เวลา:17:35:40 น.
  
หวัดดีครับ พี่เอก

ขอบคุณที่ Link ไปยัง Blog ผมครับ สำหรับปัญหาเรื่องการแสดงค่า # จนเต็มความกว้างเซลล์สามารถใช้สูตรได้ดังนี้ครับ

=Max(0,D2-E2)

และสำหรับการหาค่า Point ตามโจทย์สามารถใช้สูตรที่ G2 ดังนี้ครับ

=IF(F2>"0:30"+0,3,IF(F2>"0:15"+0,2,1))
โดย: คนควน วันที่: 19 กันยายน 2551 เวลา:18:24:23 น.
  
คำถามช่วยตอบหน่อยครับ
คือ
ผมต้องการระบุวันสิ้นสุดการทำงานลงในสัญญาการทำงานของพนักจากวันที่เริ่ม นับไปอีก แล้วแต่เราจะตกลง (จำนวนวันที่ตกลงกันนะครับ) ต้องการรู้วันที่หลังจากที่ตกลงกันแล้ว ผมต้องมาเสียเวลาในเปิดนับปฏิทินกันกี่นะ ทำให้งานช้าลงไปอีก ช่วยนะนำด้วยนะครับ
โดย: ลักเรียน IP: 118.172.137.91 วันที่: 3 ตุลาคม 2551 เวลา:15:51:59 น.
  
sb_tt_12@hotmail.com
โดย: ลักเรียน IP: 118.172.137.91 วันที่: 3 ตุลาคม 2551 เวลา:15:53:21 น.
  
ขอบคุณมากเลยครับ ผมเข้าใจแล้วว่าต้องทำอย่างไร เป็นเพราะ Web ของอาจารย์ ทำให้คิดอะไรที่เกี่ยวกับการทำงานของ Excel ได้อีหลายแบบ ขอบคุณครับ
โดย: ลักเรียน IP: 118.172.137.91 วันที่: 3 ตุลาคม 2551 เวลา:16:14:36 น.
  
สวัสดีครับ คุณลักเรียน

การหา "วันที่สิ้นสุด" นั้น ก็เอา "วันที่เริ่มต้น" ตั้ง แล้วบวกด้วย "จำนวนวัน" ที่ตกลงกันไว้ ก็จะได้วันที่สิ้นสุดออกมาครับ

แต่มีข้อพึงระวังคือ การบวกวันที่นั้น จะไม่ได้นับวันเริ่มต้นด้วย แต่จะเริ่มนับจากวันถัดไป เช่น สัญญา 5 วันนับจากวันปัจจุบัน คุณจะเอาวันที่ปัจจุบัน บวก 5 ไม่ได้นะครับ มิฉะนั้นจะกลายเป็น 6 วัน คุณจะต้องบวกด้วย 4 เพราะเมื่อรวมกับวันปัจจุบันด้วย ก็จะเป็น 5 วันครับ
โดย: ครูเอก วันที่: 4 ตุลาคม 2551 เวลา:11:36:29 น.
  
thanks
โดย: หนูชอบงง IP: 118.172.197.3 วันที่: 31 ตุลาคม 2551 เวลา:6:48:53 น.
  
ต้องการทราบว่า Excel สามารถเรียงคำตามตัวอักษรได้หรือเปล่า
โดย: เต่า IP: 61.7.141.141 วันที่: 22 พฤศจิกายน 2551 เวลา:13:32:09 น.
  
สวัสดีครับ คุณเต่า
ถ้าหมายถึง การจัดเรียงข้อมูลแต่ละเซล ก็ใช้การ Sort ครับ โดยเข้าไปที่เมนู Data > Sort.. แล้วเข้าไปเลือกว่า จะเรียงจากมากไปน้อย หรือจากน้อยไปมาก
โดย: ครูเอก วันที่: 22 พฤศจิกายน 2551 เวลา:16:25:54 น.
  
Excelข้อมูลความจุ size เกินความเป็นจริงเป็นเพราะอะไรครับ อย่างเช่น ข้อมูลความจุจริงเท่ากับ 192 KB
แต่มันเปลี่ยนเป็น 4.01MB ครูคิดว่าไงครับ ช่วยกรุณา
บอกผมหน่อยครับ เพราะว่าผมส่งข้อมูลทางอิน
เตอร์เน็ตแล้วมันช้า
โดย: ศักดิ์ดา IP: 125.26.168.216 วันที่: 23 ธันวาคม 2551 เวลา:13:14:45 น.
  
ตอบคุณศักดิ์ดา

ลองดูว่า มีการใส่รูปภาพในลงไฟล์หรือไม่ ถ้าใส่..รูปนั้นอาจจะมีขนาดใหญ่มาก ให้ลดความละเอียดของรูปลง

นอกจากนั้น ให้ดูว่ามีการจัดรูปแบบเซลเกินความจำเป็นหรือไม่ เช่น การเติมสี สีเส้น หรือการจัดรูปแบบเซล ไปยังเซลที่ยังไม่ถูกใช้งาน (เช่นเลือกจัดรูปแบบทั้งคอลัมน์ แต่ใช้งานจริง แค่ 3 บรรทัด เป็นต้น) ให้ลบรูปแบบที่ไม่ได้ใช้งานออกบ้าง จะช่วยให้ไฟล์เล็กลงครับ

ลองดูก่อนนะครับ
โดย: ครูเอก วันที่: 23 ธันวาคม 2551 เวลา:14:07:41 น.
ชื่อ :
Comment :
 *ใช้ code html ตกแต่งข้อความได้เฉพาะสมาชิก
 

Samroeng.BlogGang.com

ครูเอก
Location :
กรุงเทพฯ  Thailand

[ดู Profile ทั้งหมด]
 ผู้ติดตามบล็อก : 112 คน [?]

บทความทั้งหมด