Excel Forum

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

※　ข้อมูลพนักงานเข้า-ออก มาจากการแสกนนิ้ว ดึงข้อมูลออกมาได้ตามไฟล์แนบค่ะ (คอลัภม์ B-F)
※ วันทำงาน จันทร์ - ศุกร์
※　การคิดค่าล่วงเวลาในวันทำงาน
　　พนักงานรายวัน และพนักงานรายเดือน 1.5 เท่า
※　การคิดค่าล่วงเวลาในวันหยุด (เสาร์-อาทิตย์)
　　พนักงานรายวัน 2 เท่า และพนักงานรายเดือน 1.5 เท่า
※　การคิดค่าล่วงเวลาในวันหยุด (นักขัตฤกษ์)
　 　พนักงานรายวัน และพนักงานรายเดือน 2 เท่า
※ ค่าล่วงเวลาในวันหยุด
　พนักงานรายวัน และพนักงานรายเดือน 2 เท่า

※　เวลาทำงานของกะกลางวัน 8:00 - 17:00 พัก 12.00 - 12.50 และ 15.00 - 15.10
*การทำงานล่วงเวลา เริ่มที่เวลา 17:20 ช่วงเวลา17.00 – 17.20น. เป็นเวลาพัก จะไม่รวมเป็นเวลา OT

※　เวลาทำงานของกะกลางคืน 20:00 - 5:00 พัก 0.00 - 0.50 และ 3.00 - 3.10
*การทำงานล่วงเวลา เริ่มที่เวลา 05:20 ช่วงเวลา 5.00 - 5.20น. เป็นเวลาพัก จะไม่รวมเป็นเวลา OT

รบกวนด้วยนะคะ ขอบพระคุณล่วงหน้าค่ะ

ขอถามคำถามเลยนะครับ
1.เราจะแยกได้ยังไงว่าคนไหนเข้ากะไหน
2.คนไหนพนักงานรายเดือน คนไหนพนักงานรายวัน

เอาทีละขั้นนะครับ แล้วค่อยถามกันต่อ

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

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

ในไฟล์ต้องเพิ่ม Column แสดงการเข้ากะก่อนครับ เพื่อเอามาแยกเวลาเขางาน จะได้ทราบว่าคนไหนสายหรือไม่
เช่น เพิ่ม Column G กำหนด Shift ให้
1.กำหนดให้ D เป็นกะเช้า
2.กำหนดให้ N เป็นกะกลางคืน

* สูตรใน Column H (หลังจากแทรก Column G กำหนด Shift แล้ว)

=IF($G2="D",IF(E2+0<=8/24,8/24,E2),IF(E2<=20/24,20/24,E2))

กำหนด Fomat 20 ชม.

เมื่อกำหนดได้แล้วก็สามารถทำขั้นตอนต่อไปได้

คำถามเพิ่ม
1.ในไฟล์กำหนดแค่ว่าถ้าเข้าก่อน 8.00 ให้กำหนดให้เป็น 8.00 แต่ถ้า
2.เข้างานหลัง 8.00 ต้องกำหนดยังไง เวลาสาย คิดสายเป็น ชม. หรือคิดเป็นนาที

ขอบคุณมากค่ะคุณ XDoll สำหรับการมาสาย ที่บริษัทเค้าแค่บันทึกว่าพนักงานมาสายกี่นาที แต่ไม่มีผลกระทบอะไรกับการคำนวณเงินเดือนหรือโอที แค่เอาไว้สรุปยอดตอนปลายปีเพื่อพิจารณาเรื่องการปรับเงินเดือนเท่านั้นค่ะ

ส่วนการปรับ format เป็น 20 ช.ม. ทำยังไงคะ เข้า google หาวิธีทำแล้วก็ไม่เจอค่ะ การใช้สูตรเกี่ยวกับการคำนวณทางฝ่ายบุคคลนี่ รู้สึกตัวเองโง่เอ็กเซลไปเลยค่ะ

cherryblossomth wrote:
ส่วนการปรับ format เป็น 20 ช.ม. ทำยังไงคะ:oops:

ขอโทษครับผมพิมพ์ผิด จริงๆ 24 ชม.
ก็คือ 13.30
**ค่อยมาต่อนะครับ ขอไปธุระก่อน

ขอบคุณมากค่ะที่สละเวลามาสอนให้ค่ะ ลองทำดูแล้วค่ะ แล้วก็ลองคำนวณช่องมาสาย แต่ตรงคอลัมภ์ O ที่ใส่สีแดงไว้
จะให้แสดงว่าถ้าเข้าเกิน 8:00 ให้เป็นมาสายสำหรับกะเช้า , ถ้าเกิน 20:00 ให้เป็นมาสายสำหรับกะดึก
แต่ออกมาผลตรง 20:00 ไม่ถูกต้องน่ะค่ะ

ขอโทษที่มาตอบช้าครับ

cherryblossomth wrote:ตรงคอลัมภ์ O ที่ใส่สีแดงไว้
จะให้แสดงว่าถ้าเข้าเกิน 8:00 ให้เป็นมาสายสำหรับกะเช้า , ถ้าเกิน 20:00 ให้เป็นมาสายสำหรับกะดึก

ไฮไลท์ Column O เพื่อเช็คว่าใครมาสายโดยใช้ Conditional Formatting วิธี
1.ลากคลุมช่วง O4:O23
2.Menu Home ไปที่ Conditional Formatting > Manage Rules >New Rule>Use a formala to determine which cells to format คีย์ =SUMPRODUCT(--($G4=$G$1:$G$2),--(H4>$H$1:$H$2)) แล้ว
3.คลิ๊กปุ่ม Format เลือกแท็ป Fill > สีที่ต้องการ > ok > ok > ok

--------------------------------------
คำนวณ OT
1.ขอเพิ่ม Column เพื่อแยกว่า พนักงานรายวัน หรือ รายเดือนนะครับ
-เพิ่ม Column D กำหนดให้
-F=รายวัน
-S=รายเดือน

2.แก้ไขหัวตาราง(ง่ายต่อการเขียนสูตร)
-M1 เป็น 1.5
-N1 เป็น 2
-O1 เป็น 3

3 เพิ่มตาราง(ตามภาพ)
4 คีย์สูตรเพื่อระบุจำนวนชั่วโมงการทำงานว่าอยู่ในส่วน 1.5,2,3 เท่า โดย
-ที่ M4 คีย์ =IF(INDEX($W$11:$X$14,INDEX($T$4:$T$34,MATCH($E19,$S$4:$S$34,0))+1,MATCH($D19,$W$10:$X$10,0))=M$3,IFERROR(FLOOR(IF($F19+0<=0,"",IF($H19="D",IF(($G19+0)-0.722222222222222<=0,0,($G19+0)-0.722222222222222),IF((G19+0)-0.222222222222222<=0,0,(G19+0)-0.222222222222222)))*24,0.5),""),"") Enter คัดลอกไปถึง O4 และคัดลอกลงล่าง

*ตรงสีแดง คือตัวกำหนดว่า OT คิดเต็มชัวโมง(1,2,3,4,...) หรือ ครึ่งชม(0.5,1,1.5,2,2.5,...)
-หากคิดแบบเต็มชม. ให้กำหนดค่าเป็น 1
-หากคิดแบบครึ่งชม. ให้กำหนดค่าเป็น 0.5

ลองดูตามไฟล์แนบนะครับ ได้ตรงตามต้องการหรือไม่แจ้งด้วยครับ..แล้วค่อยถามกันต่อครับ

เพิ่มเติมครับ ให้แก้สูตรที่ P4 เป็น =I4-VLOOKUP($H4,$H$1:$I$2,2,0) Enter คัดลอกลงล่าง (สูตรเดิมที่เขียนไว้ไม่ถูกนะครับ)

สวัสดีค่ะคุณ XDoll,
ขอบคุณมากๆ อีกครั้งค่ะ ลองเข้าไปเพิ่มเติมข้อมูลตามของจริงดู แล้วใส่สูตรตามที่คุณ XDoll สอน เอาไปเทียบกับที่ HR คนเก่าเค้าทำเอาไว้ ปรากฎว่ามีบางตัวไม่ตรงกันน่ะค่ะ (เทียบคอลัมภ์ M กับ AF , N กับ AG, O กับ AH) แล้วก็ตรงที่เป็นกะดึก ช่อง P ขึ้น ##### ค่ะ

※ ขอโทษด้วยค่ะ ใส่ค่าล่วงเวลาในวันหยุดผิด ที่ถูกต้องคือ 3เท่าค่ะ
　พนักงานรายวัน และพนักงานรายเดือน 3 เท่า

※ กะเช้า ค่าล่วงเวลาเริ่มต้นที่ 17:20
※ กะดึก ค่าล่วงเวลาเริ่มต้นที่ 05:20

สอบถามวิธีคิด OT หน่อยครับ
ตามภาพ
1.เวลาเข้า 7.30 ทำไมมี OTเช้า 0.5 (มีโอทีก่อนเข้างานปกติ<8.00 น.>ด้วยเหรอครับ)
2.เวลาออก 20.51 ถ้าคิดหลัง 17.20 ไปแล้ว หัก-ลบ จะได้เวลา 3 ชม. 31 นาที ชึ่งคิดเป็น 3.5 ไม่น่าจะใช่ 3.25

ถามเพิ่มเติม
OT คิดทุก 15 นาทีรึเปล่าครับ (เพราะตามที่ดูมี 0.25,0.50,1.25,1.50) หรือเริ่มต้นที่กี่ นาที/ชม. เป็นขั้นต่ำ ช่วยบอกด้วยครับ

1. โอทีเช้า มีเป็นบางครั้งค่ะ แต่กรณีนี้ ไม่มีค่ะ ในช่องคอลัมภ์ AB, AC สัณนิษฐานว่าเค้าน่าจะทำไว้ดูคร่าวๆนะคะ แต่ดูแล้วก็ยังงงกับที่เค้าใส่ไว้เหมือนกันค่ะ แต่ยอดที่เค้าเอาไปคำนวณเงินค่าล่วงเวลาจริงๆจะเป็นในคอลัมภ์ AF - AH ซึ่งเค้าคีย์มือเค้าไปค่ะ
2. เวลาที่ถูกต้องควรจะเป็นยอด 3.5 ค่ะ ซึ่งยอดที่เอาไปคำนวณค่าล่วงเวลา เค้าก็เอา 3.5 ไปคำนวณค่ะ

สำหรับ OT คิดทุก 15 นาทีค่ะ
รบกวนอีกครั้งค่ะ

1.ถ้างั้นช่วยคีย์คำตอบ (ชม.OT) ที่ถูกต้องแล้วแนบไฟล์มาให้ใหม่อีกทีครับ
2.ในกรณีหากมี OT เช้าด้วย มีเงื่อนไขอะไรที่คิด อาจจะต้องปรับสูตรเพิ่ม
3.ในกรณีคนๆเดียวกัน เขากะไม่เหมือนกัน ใน Column H หากใส่ไม่ถูกมีผลต่อการคำนวนโอทีผิดพลาดเช่น H63,H64,H65 ควรจะเป็น D หรือเปล่าครับ ถ้าดูจากเวลาเข้า-ออกงาน (อันนี้ไม่แน่ใจว่าผมเข้าใจถูกไหม)

cherryblossomth wrote:ตรงที่เป็นกะดึก ช่อง P ขึ้น ##### ค่ะ

แก้ I4 เป็น =IF($H4="D",IF(F4+0<=8/24,8/24,F4),IF(F4+0<=20/24,20/24,F4)) ครับ

เพื่อให้OT คิดทุก 15 นาที ต้องแก้สูตร
M4 เป็น =IF(INDEX($W$11:$X$14,INDEX($T$4:$T$34,MATCH($E4,$S$4:$S$34,0))+1,MATCH($D4,$W$10:$X$10,0))=M$3,IFERROR(FLOOR(IF($F4+0<=0,"",IF($H4="D",IF(($G4+0)-0.722222222222222<=0,0,($G4+0)-0.722222222222222),IF((G4+0)-0.222222222222222<=0,0,(G4+0)-0.222222222222222)))*24,0.25),""),"") คัดลอกไปขวา และลงล่าง
สีแดงคือส่วนที่ต้องแก้ครับ( ถ้า 6 นาที = 0.1 ,ถ้า 15 นาที = 0.25 ,ถ้า 30 นาที = 0.5 )

มีแก้ไขในไฟล์นิดหน่อยนะคะ
1. คำตอบที่ถูกต้องคีย์ใหม่แล้วค่ะ อยู่ในคอลัมภ์ AB - AH นะคะ
2. โอทีในช่วงเช้า ไม่มีเงื่อนไขค่ะ ขึ้นอยู่กับหัวหน้าแผนกเค้าจะสั่งให้ทำ แต่มีไม่บ่อยค่ะ นานๆจะมีสักทีค่ะ ซึ่งจะมีใบเขียนโอทีมาให้เทียบอีกทีนึง ถ้าหากยุ่งยาก ใช้วิธีแก้มือก็ได้ค่ะ
3. ขอโทษด้วยค่ะ คีย์ผิดเองค่ะ ต้องเป็นกะ D ค่ะ แก้ไขแล้วนะคะ

สีแดงคือส่วนที่ต้องแก้ครับ( ถ้า 6 นาที = 0.1 ,ถ้า 15 นาที = 0.25 ,ถ้า 30 นาที = 0.5 ) หมายถึงให้แก้ยังไงหรือคะ หรือว่าหมายถึงว่า ถ้ากรณีที่เงื่อนไขการทำโอทีเปลี่ยน ให้มาแก้ตรงนี้หรือเปล่าคะ

※ ตรงวันหยุดเสาร์-อาทิตย์, นักขัตฤกษ์ (ประเภทวันทำงาน เป็นเลข 1, 2) ก่อน และหลัง เวลาทำงาน OT 3 เท่าค่ะ
ปล.ประเภทวันหยุด 3 วันหยุดนักขัตฤกษ์ (จ่ายเบี้ยเลี้ยง) ลองถามพนักงานที่บริษัทเค้าบอกว่าไม่มีค่ะ ตัดออกจากในชีทแล้วค่ะ จะได้ไม่งง

※ ช่อง สาย (นาที) คอลัมภ์ P ตัว conditioning format ที่จะให้ช่องที่สายเป็นสีแดง ไม่ขึ้นด้วยค่ะ

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

ขอแก้ไปทีละอย่างนะครับ
1.เรื่มจาก conditioning format เนื่องจากสูตรที่ให้ไปก่อนหน้าอ้างอิงไฟล์เดิม ยังไม่มีการแทรก Column เลยใช้สูตรนั้นไม่ได้ ผมได้เขียนใหม่ให้แล้ว ลองดูในไฟล์นะครับ
2.

cherryblossomth wrote:
สีแดงคือส่วนที่ต้องแก้ครับ( ถ้า 6 นาที = 0.1 ,ถ้า 15 นาที = 0.25 ,ถ้า 30 นาที = 0.5 ) หมายถึงให้แก้ยังไงหรือคะ หรือว่าหมายถึงว่า ถ้ากรณีที่เงื่อนไขการทำโอทีเปลี่ยน ให้มาแก้ตรงนี้หรือเปล่าคะ

ใช่ครับ เป็นตัวอย่างกรณีที่เงื่อนไขการทำโอทีเปลี่ยน และเปรียบเทียบได้ว่า จากไฟล์ที่แนบมาใหม่พบว่า OT เช้าไม่ได้คิดทุก 15 นาที แต่คิดเต็มชม.คือ 60 นาที ต้องแก้เป็น 1

3.เพิ่ม Column เพื่อคิด OT ก่อนเข้างานปกติ ที่ Column M:O ครับ
คีย์สูตรที่ M4 =IF(INDEX($AB$11:$AC$14,INDEX($Y$4:$Y$34,MATCH($E4,$X$4:$X$34,0))+1,MATCH($D4,$AB$10:$AC$10,0))=M$3,IFERROR(FLOOR(IF($F4+0<=0,"",IF($H4="D",IF(VLOOKUP($E4,$X$4:$Y$34,2,0)=1,0.708333333333333-($I4+0),IF(0.333333333333333-($F4+0)<=0,0,0.333333333333333-($F4+0))),IF(VLOOKUP($E4,$X$4:$Y$34,2,0)=1,(($E4+1)+0.16666666667)-($E4+$I4),IF(0.833333333333333-($F4+0)<=0,0,0.833333333333333-($F4+0)))))*24,1),0),0) Enter คัดลอกไปถึง O4 และลงล่าง
สีแดง ที่อธิบายไว้ด้านบน

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

ถ้า OT เข้าก่อนเวลาปกติคิดแค่ 1.5 เท่าเท่านั้น ให้ใช้ไฟล์นี้ครับ

สวัสดีอีกครั้งค่ะคุณ XDoll
ลองดูข้อมูลในไฟล์แล้ว เหลือที่ยังติดอยู่ดังนี้ค่ะ
1. Q30 เนื่องจากเป็นวันหยุด (1) แสกนเข้า 07:13, ออก 20:25 ได้โอทีวันหยุด 1.5 เท่า สำหรับ 8 ชั่วโมงแรก (8:00-17:00) และได้โอทีล่วงเวลาวันหยุด 3 เท่า สำหรับ 3 ชั่วโมงหลัง (17:20-20:20) ค่ะ
2. ที่ไฮไลท์สีเหลืองไม่ผิดค่ะ เพราะหัวหน้าสั่งให้ทำถึงแค่ xx:20 แต่แสกนนิ้วออกหลังจากนั้น ดังนั้นจึงตัดแค่ xx:20 ค่ะ

รบกวนดูสีฟ้าในไฟล์แนบนะคะ ขอบคุณค่ะ

cherryblossomth wrote: ลองดูข้อมูลในไฟล์แล้ว เหลือที่ยังติดอยู่ดังนี้ค่ะ
1. Q30 เนื่องจากเป็นวันหยุด (1) แสกนเข้า 07:13, ออก 20:25 ได้โอทีวันหยุด 1.5 เท่า สำหรับ 8 ชั่วโมงแรก (8:00-17:00) และได้โอทีล่วงเวลาวันหยุด 3 เท่า สำหรับ 3 ชั่วโมงหลัง (17:20-20:20) ค่ะ

1.แก้ไขสูตรเป็น 3 เท่าให้แล้วครับ อยู่ในไฟล์แนบครับ

cherryblossomth wrote:
2. ที่ไฮไลท์สีเหลืองไม่ผิดค่ะ เพราะหัวหน้าสั่งให้ทำถึงแค่ xx:20 แต่แสกนนิ้วออกหลังจากนั้น ดังนั้นจึงตัดแค่ xx:20 ค่ะ

รบกวนดูสีฟ้าในไฟล์แนบนะคะ ขอบคุณค่ะ

2.เนื่องจากสูตรกำหนดให้ทุก 15นาทีเอามาคิดเป็น OT โดยจับจากเวลาแสกนออก จึงไม่สามารถ กำหนดได้ นอกจากระบุเวลาออกที่ชัดเจน หรือ เวลาออก ไม่เกินจากกำหนด 15 นาที ครับเช่น
2.1 เริ่มโอทีจาก 17.20 ออกเวลา 19.34 หักลบแล้ว 2 ชม.14 นาที สูตรก็ จะคิดให้ 2.00
2.2 เริ่มโอทีจาก 17.20 ออกเวลา 19.36 หักลบแล้ว 2 ชม.16 นาที สูตรก็ จะคิดให้ 2.25

3.แต่หากกำหนดให้สูตร คิดทุกๆ 60 นาที OT ก็จะออกมาเป็น 1,2,3 จะไม่มีเศษ
4.โดยสรุปแล้ว จะติดแค่เวลาออกครับ ผมเข้าใจว่า
4.1 ใน 2.ชมแรกคิดทุก 15 นาที
4.2 หลังจากนั้น คิดทุก 30 นาที
***แก้ไขโดย ต้องเพิ่มเงื่อนไขให้เวลาออกครับ***

ปล.เนื่องจากเท่าที่ดู OT เช้ามีแค่ 1.5 เท่า เท่านั้น ผมได้ตัด Column ที่คิด 2 เท่าและ 3 เท่าออกครับ

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

สวัสดีค่ะคุณ XDoll
วันนี้ลองเอาข้อมูลทั้งหมดของเดือน 5 มาลองใส่ดูค่ะ และมีปรับตารางบางช่อง พบว่าข้อมูลมีที่ไม่ตรงดังนี้ค่ะ

1. เพิ่มคอลัมภ์ H:I เพื่อใช้สำหรับปรับเวลาเข้า-ออกงานกรณีที่ไม่ได้เป็น OT แต่พนักงานมาก่อนเวลาเอง แต่พอเพิ่มแล้ว เจอปัญหาว่า ช่องที่ระบุกะทำงานของพนักงานเปลี่ยนไปด้วยค่ะ ไม่แน่ใจว่าใส่สูตรผิดหรือเปล่า เช่น H154 พอแก้จาก 18:48 เป็น 20:00 เพื่อไม่ให้คำนวณโอทีตอนเข้า เนื่องจากพนักงานมาก่อนเวลาเอง (ยังไม่เริ่มทำงานจริง) ทำให้ช่อง K เปลี่ยนจาก N เป็น D , ทำให้ช่อง AB (มาสาย) ขึ้นเป็น 12 ชั่วโมง , และทำให้โอทีจากเวลา 5:20 - 8:20 หายไป 3 ชั่วโมงค่ะ

2. ช่อง P613 ทำโอทีวันหยุดประเภท 1 ตั้งแต่ 8:00-12:00 แต่ขึ้นเป็น 8 ชม
3. พนักงานรายวัน ช่อง D ที่เป็นโค้ด T ทำงานวันหยุดประเภท 1 ต้องได้โอที 2 เท่า แต่ขึ้นแค่ 1.5 เท่าค่ะ

รายละเอียดตามไฟล์แนบค่ะ