Excel Forum

สวัสดีค่ะ
รบกวนขอคำแนะนำการแก้สูตรคำนวณโอทีและค่ากะค่ะ

รายละเอียดข้อมูลแบ่งเป็น 2 ส่วน
ตารางการทำงานกะของพนักงานฝ่ายผลิต
และตารางการทำงานของฝ่ายสำนักงาน

รายละเอียดตามไฟล์ที่แนบมาและมีคำถามอยู่ในไฟล์ค่ะ
ขอบคุณมากค่ะ

ค่อย ๆ ถามตอบกันไปครับ

ที่เซลล์ D3 คีย์สูตร

=IF(C3="","",LOOKUP(IF(C3<MIN($T$4:$T$6),MIN($T$4:$T$6),C3),$T$4:$T$6,$S$4:$S$6))

Enter > Copy ลงด้านล่าง ให้ตรวจสอบว่าถูกต้องหรือไม่ ผิดที่เซลล์ใด ให้แจ้งมาด้วยว่าที่ถูกคือค่าใด คิดอย่างไรจึงได้ค่าเท่านั้น

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

ขอบพระคุณอาจารย์คนควนสำหรับคำตอบค่ะ

สำหรับ Column D ยังติดปัญหาอยู่นิดหน่อยค่ะ คือ ส่วนของกะ 2 OT (ซึ่งในไฟล์แรก ใส่ข้อมูลไว้เป็นกะ 3 แต่จริงๆแล้วจะเป็นกะ 2 OT ค่ะ เนื่องจากเวลาของกะ 1 หากมีโอทีจะสิ้นสุดที่ 19.40น. ทำให้เวลาเข้างานของกะ 2 เลื่อนออกไปจาก 16.00 เป็น 19.30น. แทนค่ะ)

ตัวอย่างเช่น ช่อง D12 เทียบกับเวลาเข้างานจากช่อง C12 = 19:15:36 จะต้องได้คำตอบเป็น กะ 2 OT
หากข้อมูลถูกต้อง สูตรในช่อง E12 จะได้ผลลัพธ์ออกมาเป็น 19:30:00 และส่งผลคำนวณไปในช่อง I (มาสาย) ได้ค่ะ

ในส่วนตารางการทำงาน ได้ทำการเพิ่มข้อมูลเรื่องเวลาพักเบรก และวันหยุดนักขัตฤกษ์ และเงื่อนไขการคิด OT ของพนักงาน 2 ประเภทแนบไว้ในไฟล์ค่ะ

รบกวนสอบถาม และขอคำแนะนำในคำถามส่วนที่เหลือค่ะ
ขอบคุณค่ะ

ขออภัยค่ะ แนบไฟล์ไม่ติดในข้อความที่ตอบไปตอนแรกค่ะ

เวลาของกะใด ๆ จะต้องมีการกำหนดไว้แน่นอนไม่ต้องเผื่อไปถึง OT หากเลยเวลานั้นค่อยคิด OT ครับ

การคำนวณจะคิดเวลาปกติของกะนั้น ๆ และ เวลาโอทีของกะนั้น ๆ เช่นนี้ครับ

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

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

ในส่วนคำถามข้ออื่นๆ รบกวนขอคำแนะนำในการเขียนสูตรค่ะอาจารย์

ขอบคุณมากค่ะ

จากคำถามที่ 2

Amptt wrote: ขอบคุณมากค่ะคำถาม 2
รบกวนตรวจสอบสูตรที่มีเงื่อนไข
= เข้างานเวลา 16:00 หรือ 19:30 น. จะได้ค่ากะ 40 บาท

สูตรควรจะเป็นด้านล่างครับ

=IF(G11="","",IF(OR(G11="16:30"+0,G11="19:30"+0),40,""))

หมายถึงเข้างาน 16:00 หรือ 19:30 จึงจะแสดงค่า 40 ซึ่งเมื่อใส่สูตรแล้วไม่มีเซลล์ใดได้คำตอบ เพราะไม่มีค่าในคอลัมน์ G ที่เข้าตามเงื่อนไขนี้ ช่วยอธิบายเพิ่มเติมว่าเงื่อนไขเป็นเช่นที่ถามมาหรือเป็นช่วงเวลาครับ

เรียน อาจารย์คนควน

คำถามที่ 2 ต้องลิงค์สูตรมาจาก Column E ค่ะ ขออภัยค่า ได้ทำการแก้ไขแล้วค่ะ ได้คำตอบที่ถูกต้องแล้วค่ะ

คำถาม 3
ได้ลองนำสูตรจากคำถามที่ 2 มาปรับใช้
เงื่อนไขคือ พนักงานที่จะได้รับโอทีในวันทำงานปกติ เมื่อ..
1. เข้างานเวลา 7.30 น. และออกจากงานเวลา 19.40 น.
2. เข้างานเวลา 19.30 และออกจากงานเวลา 7.40 น.
(โอทีจะคิดเป็น 3 ชั่วโมง โดยคำนวณในช่อง OT เรท 1.5 อิงเวลาใน Column G คือเวลาที่พนักงานออกจากงานเป็นหลัก เทียบกับเวลา 7.40 หรือ 19.40 น. )
คำตอบที่ถูกต้อง คือ 3 ชม (แต่สูตรที่ใช้ยังไม่ถูก เนื่องจากพนักงานที่ออกจากงานหลังช่วงเวลา 16.10 สูตรยังนับเป็นโอทีไปด้วยค่ะ)
คำถามเพิ่มเติมค่ะ ..ในบางครั้ง จะมีโอทีเรียกเข้าในกรณีมีงานด่วน
*พนักงานที่เข้างานเวลา 16.10 และออกจากงานเวลา 0.30 น. จะถูกเรียกให้ทำโอทีเพิ่มจนถึงเวลา 3.30 น
สูตรควรมีการเปลี่ยนแปลงอย่างไรคะ

คำถาม 4
หาสูตรคำนวณโอที **วันอาทิตย์และวันหยุดนักขัตฤกษ์**
ช่วงเวลาทำงานปกติ 7.30-16.10 น. หรือ 16.00-00.30 น. พนักงานที่ออกจากงานเวลา 16.10 หรือ 00.30 น. ในช่อง G จะได้โอที 8 ชม โดยจะคิดในช่องเรทโอที 2 เท่า.
N25 = คำตอบคือ 8 ชม.
หากมีการทำโอที ช่วงเวลาการทำงานจะกลายเป็น 7.30-19.40 และ 19.30-7.40 น. ดังนั้นหากพนักงานออกจากงานเวลา 19.40 หรือ 7.40 น. (เปรียบเทียบเวลาจาก Column G) จะคิดโอที 3 ชม. โดยจะคิดในช่องเรทโอที 3 เท่า
O26 = 3 ชม

คำถาม 5
พนักงานออฟฟิต จะคิดโอทีหลังเวลา 17.00
เรท 1.5 เท่า ในวันธรรมดา (จันทร์-เสาร์)
เรท 2 เท่า ในวันอาทิตย์และวันหยุดนักขัตฤกษ์ ในเวลางาน 8.00-17.00 น.
เรท 3 เท่า ในวันอาทิตย์และวันหยุดนักขัตฤกษ์หลัง 17.00น.
โดย OT จะตัดแค่ 30 นาที และ 60 นาที

M30 = ต้องการสูตรหา OT ของวันทำงานปกติ เรท 1.5 เท่า
L31,N31 ต้องการสูตรคำนวณ OT วันอาทิตย์ โดยคำตอบของ L31 = 8 ชม. (มาจากคำนวณเวลาทำงานปกติ 8.00-17.00 น. (เรท 2 เท่า) และ N31 คำตอบคือ 1.30 ชม. มาจากทำงานล่วงเวลา (เรท 3 เท่า)

Amptt wrote:เรียน อาจารย์คนควน

คำถามที่ 2 ต้องลิงค์สูตรมาจาก Column E ค่ะ ขออภัยค่า ได้ทำการแก้ไขแล้วค่ะ ได้คำตอบที่ถูกต้องแล้วค่ะ

คำถาม 3
ได้ลองนำสูตรจากคำถามที่ 2 มาปรับใช้
เงื่อนไขคือ พนักงานที่จะได้รับโอทีในวันทำงานปกติ เมื่อ..
1. เข้างานเวลา 7.30 น. และออกจากงานเวลา 19.40 น.
2. เข้างานเวลา 19.30 และออกจากงานเวลา 7.40 น.
(โอทีจะคิดเป็น 3 ชั่วโมง โดยคำนวณในช่อง OT เรท 1.5 อิงเวลาใน Column G คือเวลาที่พนักงานออกจากงานเป็นหลัก เทียบกับเวลา 7.40 หรือ 19.40 น. )
คำตอบที่ถูกต้อง คือ 3 ชม (แต่สูตรที่ใช้ยังไม่ถูก เนื่องจากพนักงานที่ออกจากงานหลังช่วงเวลา 16.10 สูตรยังนับเป็นโอทีไปด้วยค่ะ)
คำถามเพิ่มเติมค่ะ ..ในบางครั้ง จะมีโอทีเรียกเข้าในกรณีมีงานด่วน
*พนักงานที่เข้างานเวลา 16.10 และออกจากงานเวลา 0.30 น. จะถูกเรียกให้ทำโอทีเพิ่มจนถึงเวลา 3.30 น
สูตรควรมีการเปลี่ยนแปลงอย่างไรคะ

ค่อย ๆ ถามและตอบให้ผ่านไปทีละข้อครับ

ยกตัวอย่างสูตรเดิม

=IF(G12="","",IF(OR(G12>="7:40"+0,G12>="19:30"+0),3,""))

ด้านบนเป็นการตรวจสอบด้วย Or แปลว่า หรือ คือเข้าเงื่อนไขใดเงื่อนไขหนึ่งก็จะได้คำตอบเป็น 3

หากว่าต้องเข้าทั้งสองเงื่อนไขเราจะเปลี่ยนจาก Or เป็น And ครับ เช่น

=IF(G12="","",IF(And(C12>="7:40"+0,G12>="19:30"+0),3,""))

คือต้องเข้าทั้งสองเงื่อนไข สังเกตว่าเวลาเข้าคือ C12 (ไม่ใช่ตรวจสอบแค่เวลาออก) และเวลาออกคือ G12 ทั้งเวลาเข้าและเวลาออกต้องอยู่ในเงื่อนไขจึงจะได้ผลลัพธ์เป็น 3 ลองตรวจสอบว่าตรงกับที่ต้องการหรือไม่ อย่างไรครับ

เรียน อาจารย์คนควน

หากต้องการตรวจสอบทั้งเวลาเข้าและเวลาออก จะได้จำนวนโอทีขึ้นถูกต้องตามสูตรที่อาจารย์แนะนำค่ะ แต่ติดตรงที่เวลาเข้าพนักงานมี 2 กะ คือ 7.30 น.และ 19.30 น. แล้วเวลาออกจะเป็น 19.40 น.และ 7.40 น.ค่ะ จะต้องปรับสูตรเพิ่มเติมอย่างไรคะ

(และคำถามเพิ่มเติมส่วนเดิม จากคำถามข้อ3 สามารถเขียนสูตรร่วมกันได้ด้วยหรือไม่คะ
"คำถามเพิ่มเติมค่ะ ..ในบางครั้ง จะมีโอทีเรียกเข้าในกรณีมีงานด่วน พนักงานที่เข้างานเวลา 16.10 และออกจากงานเวลา 0.30 น. จะถูกเรียกให้ทำโอทีเพิ่มจนถึงเวลา 3.30 น สูตรควรมีการเปลี่ยนแปลงอย่างไรคะ")

ขอบคุณมากค่ะ

Amptt wrote:เรียน อาจารย์คนควน

หากต้องการตรวจสอบทั้งเวลาเข้าและเวลาออก จะได้จำนวนโอทีขึ้นถูกต้องตามสูตรที่อาจารย์แนะนำค่ะ แต่ติดตรงที่เวลาเข้าพนักงานมี 2 กะ คือ 7.30 น.และ 19.30 น. แล้วเวลาออกจะเป็น 19.40 น.และ 7.40 น.ค่ะ จะต้องปรับสูตรเพิ่มเติมอย่างไรคะ

ขอบคุณมากค่ะ

ตัวอย่างสูตรครับ

=IF(G12="","",IF(Or(And(C12<="7:40"+0,G12>="19:30"+0),And(C12>="19:30"+0,G12<="7:40"+0)),3,""))

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

ตามสูตรที่อาจารย์ให้มา ได้ลองปรับเปลี่ยนเครื่องหมาย > และ < ในช่อง M12 เป็น
=IF(G12="","",IF(OR(AND(C12<="7:40"+0,G12>="19:30"+0),AND(C12<="19:30"+0,H12>="7:40"+0)),3,""))
คำตอบได้ตามที่ต้องการในช่อง M12 ค่ะ
*ติดตรงที่ช่อง M4 , M6 , M8 ซึ่งจากข้อมูลเวลาเข้า-ออกพนักงานแล้วไม่มีการทำโอทีค่ะ แต่ข้อมูลยังขึ้น OT จำนวน 3 ชม.อยู่ รบกวนอาจารย์ช่วยแนะนำสูตรอีกครั้งค่ะ

ในส่วนของคำถามเพิ่มเติม ขออนุญาตอธิบายเวลาการทำงานตามกะ ดังนี้ค่ะ
ช่วงที่ทำงาน 16 ชั่วโมง
กะ 1 7:30 - 16:10
กะ 2 16:00 - 0:30
ช่วงที่ทำงาน 24 ชม.
กะ 1.5 7:30 - 19:40 (รวมโอที 3 ชม.)
กะ 2.5 19:30 - 7:40 (รวมโอที 3 ชม.)
จากคำถามเพิ่มเติมนั้นคือ คำถามกรณีพิเศษจากกะ 2 (16.00-0.30 น.) บางครั้งจะมีการเรียกทำโอทีเพิ่ม 3 ชม. ซึ่งจะไปจบในเวลา 03.30 น. อยากให้คำตอบช่องตัวอย่าง ช่อง M18 ขึ้นจำนวน 3 ชม.เช่นกัน แต่เป็นไปได้หรือไม่ว่าสูตรจะต้องอยู่ในเงื่อนไขเพิ่มเติมจากสูตรข้างต้น (สูตรในช่อง M12) ค่ะ เนื่องจากเป็นโอที 1.5 เท่าเหมือนกันค่ะ
ขอบคุณค่ะ

พนักงานที่ต้องทำงานด่วน ไม่เป็นไปตามระบบเวลาปกติ ควรมีการแยกคิดต่างหาก จะได้ลดการเขียนเงื่อนไขซับซ้อน

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

สำหรับที่ยังติดอยู่ M4, M6 และ M8 ช่วยสรุปเงื่อนไขมาให้ใหม่ สรุปเวลาเข้าและออกให้ครบทุกประเด็น ยกเว้นรายการพิเศษ

เข้าเวลาใด ออกเวลาใด เป็นรายการปกติ เข้าเวลาใด ออกเวลาใด เป็นการคิดโอที

สูตรลักษณะนี้ไม่ได้ยุ่งยาก เพราะว่ามีเวลาเริ่มและเวลาสิ้นสุดตายตัวอยู่แล้ว

กรณีเข้าดึกแล้วออกเช้า เช่น 19:30 - 7:40 สำหรับช่วงเวลาในตอนเช้าการเขียนสูตรจะต้องระบุว่า And(Cell1>="19:30"+0,Cell2<="7:40") ไม่ใช่ And(Cell1>="19:30"+0,Cell2>="7:40") เพราะเงื่อนไขตัวที่สองจะเข้าเงื่อนไขได้จนถึง 24:00

เรียน อาจารย์ คนควน

ในกรณีปกติจะมี 2 ช่วงเวลาที่คิดโอที 3 ชั่วโมงให้กับพนักงานค่ะ
1. กะ 1.5 เข้า 7:30 ออก 19:40 (รวมโอที 3 ชม.)
*ถ้าพนักงานออกเวลา 16.10 จะต้องไม่คิดโอที (ในไฟลเดิมตามสูตร ยังขึ้นคำตอบ 3 ชม. อยู่ ซึ่งคำตอบยังไม่ถูกต้องค่ะ)
2. กะ 2.5 เข้า 19:30 ออก 7:40 (รวมโอที 3 ชม.)

ุ้ถ้าคิดตามสูตรที่อาจารย์แนะนำ ขอรบกวนถามเพิ่มค่ะ

"กรณีเข้าดึกแล้วออกเช้า เช่น 19:30 - 7:40 สำหรับช่วงเวลาในตอนเช้าการเขียนสูตรจะต้องระบุว่า And(Cell1>="19:30"+0,Cell2<="7:40") ไม่ใช่ And(Cell1>="19:30"+0,Cell2>="7:40") เพราะเงื่อนไขตัวที่สองจะเข้าเงื่อนไขได้จนถึง 24:00"

พนักงานที่เข้ากะดึก ตอนแสกนหน้าออกตอนเช้า เวลาจะต้องขึ้นหลังจาก 7.40 (7.41 เป็นต้นไป) ไม่ใช่ก่อนหน้า ทำให้สูตรนับรวมจำนวนชั่วโมงไปจนถึง 24.00 ดังนั้นสามารถเขียนสูตรกำหนดเวลาได้หรือไม่คะ เช่น เข้างาน ก่อน 19.30 และออกหลัง 7.40 แต่ไม่เกิน 8.00 น. เป็นต้น

ขอบคุณมากค่ะ

Amptt wrote:1. กะ 1.5 เข้า 7:30 ออก 19:40 (รวมโอที 3 ชม.)

ข้อความด้านบน เหตุใดจึงต้องนำเวลา OT เข้ามารวม ถ้าพนักงานไม่ทำ OT จะออกเวลาใด หรือ มีหลักการใดที่ต้องนำเวลาออกไปรวมเวลา OT ไว้เช่นนั้น แยกเเป็นเวลาปกติและเวลา OT ได้หรือไม่ หากไม่ได้ ติดปัญหาใดจึงไม่ได้ กรุณาอธิบายมาอย่างละเอียดครับ

เวลาในการเข้าออกปกติและ OT จะต้องแยกออกจากกันให้ชัดเจน เข้าปกติ ออกปกติ คือเวลาใด และ หากเข้าและออกเวลาใดจึงจะคิด OT เช่นนี้เป็นต้นครับ

จากคำอธิบายที่ว่าออกหลัง 7:40 แต่ไม่เกิน 8:00 สามารถปรับสูตรเป็นตามด้านล่างครับ

And(Cell1>="19:30"+0,Cell2<="8:00"+0)