อยากได้สูตรคำนวณค่ากะค่ะ

[tawanbus]

ต้องการได้สูตรให้เครื่องคำนวณหาค่ากะค่ะ
เข้า 8.00 ออก 17.00 จะได้ค่ากะ 0 บาท
เข้า 8.00 ออก 16.00 จะได้ค่ากะ 0 บาท
เข้า 8.00 ออก 24.00 จะได้ค่ากะ 20 บาท
เข้า 16.00 ออก 24.00 จะได้ค่ากะ 20 บาท
เข้า 16.00 ออก 8.00 จะได้ค่ากะ 70 บาท
เข้า 24.00 ออก 08.00 จะได้ค่ากะ 50 บาท
เข้า 24.00 ออก 16.00 จะได้ค่ากะ 50 บาท
เวลาเข้าและออกอยู่คนละคอลัมธ์ค่ะ

[snasui]

ส่งไฟล์ตัวอย่างมาด้วย จะได้สะดวกในการตอบของเพื่อน ๆ ครับ

[tawanbus]

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

[snasui]

สามารถทำตารางเวลามาช่วยในการ Lookup เวลาได้ครับ

โดยผมได้ทำเป็นตัวอย่างไว้ที่ CM6:CN10

จากนั้นที่ O7 คีย์

=IF(AND(LOOKUP(M7,$CM$6:$CM$10,$CN$6:$CN$10)=8/24,LOOKUP(N7,$CM$6:$CM$10,$CN$6:$CN$10)=1),20,IF(AND(LOOKUP(M7,$CM$6:$CM$10,$CN$6:$CN$10)=16/24,LOOKUP(N7,$CM$6:$CM$10,$CN$6:$CN$10)=1),20,IF(AND(LOOKUP(M7,$CM$6:$CM$10,$CN$6:$CN$10)=16/24,LOOKUP(N7,$CM$6:$CM$10,$CN$6:$CN$10)=8/24),70,IF(AND(LOOKUP(M7,$CM$6:$CM$10,$CN$6:$CN$10)=1,LOOKUP(N7,$CM$6:$CM$10,$CN$6:$CN$10)=8/24),50,IF(AND(LOOKUP(M7,$CM$6:$CM$10,$CN$6:$CN$10)=1,LOOKUP(N7,$CM$6:$CM$10,$CN$6:$CN$10)=16/24),50,0))

Enter > Copy ลงด้านล่าง > Copy ไปในคอลัมน์อื่น ๆ ที่มีการคำนวณลักษณะเดียวกัน ดูตัวอย่างตามไฟล์แนบครับ

[tawanbus]

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

[tawanbus]

ไฟล์แนบค่ะ

[snasui]

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

ทั้งนี้เนื่องจากฐานตัวเลขไม่เท่ากัน ตัวเลขปกติฐาน 100 ตัวเลขเวลาฐาน 60 การคีย์ตัวเลขเวลาให้เป็นตัวเลขปกติหรือกลับกันจะต้องแปลงกันอีกวุ่นวายครับ

[tawanbus]

ต้องการทราบการคิดโอที
การกำหนดค่าวันทำงานถ้ามาทำงาน
เข้า 8.00 ออก 17.00 เท่ากับ 1
เข้า 8.00 ออก 16.00
เข้า 8.00 ออก 24.00
เข้า 16.00 ออก 24.00
เข้า 16.00 ออก 8.00
เข้า 24.00 ออก 08.00
เข้า 24.00 ออก 16.00
นอกจากนี้ให้เป็น 0 กรณีถ้าคีย์เป็นตัวหนังสือ เช่นลากิจ ที่ไม่ใช่ค่าเวลาให้เป็น 0
การคิดค่าอาหาร
เข้า 8.00 ออก 17.00 เท่ากับ 0
เข้า 8.00 ออก 16.00 เท่ากับ 0
เข้า 8.00 ออก 24.00 เท่ากับ 1
เข้า 16.00 ออก 24.00 เท่ากับ 1
เข้า 16.00 ออก 8.00 เท่ากับ 1
เข้า 24.00 ออก 08.00 เท่ากับ 1
เข้า 24.00 ออก 16.00 เท่ากับ 1

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

[tawanbus]

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

[snasui]

ลองส่งไฟล์ที่ลองทำแล้วมาดูหน่อยครับ ช่วย Note คำตอบที่ต้องการไว้ด้วย ขอยืนยันว่าเวลากับเวลาบวก ลบ คูณ หาร กันได้แน่นอนครับ

[tawanbus]

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

[snasui]

ผมสังเกตดูตัวอย่างไม่ได้มีความหลากหลายในเรื่องเวลาที่คีย์ เลยปรับสูตรมาให้ใหม่ดังนี้ครับ

๑. ค่ากะ ที่เซลล์ J7 คีย์

=IF(AND(H7=8/24,I7=1),20,IF(AND(H7=16/24,I7=1),20,IF(AND(H7=16/24,I7=8/24),70,IF(AND(H7=1,I7=8/24),50,IF(AND(H7=1,I7=16/24),50,0)))))

Enter > Copy ลงด้านล่าง

๒. ค่า OT ที่เซลล์ K7 คีย์

=IF(OR(AND(H7=8/24,OR(I7=17/24,I7=16/24,I7=1)),AND(H7=16/24,OR(I7=1,I7=8/24)),AND(H7=1,OR(I7=8/24,I7=16/24))),1,0)

Enter > Copy ลงด้านล่าง

๓. ค่าอาหารที่ M7 คีย์

=IF(OR(AND(H7=8/24,I7=1),AND(H7=16/24,OR(I7=1,I7=8/24)),AND(H7=1,OR(I7=8/24,16/14))),1,0)

Enter > Copy ลงด้านล่าง

ดูไฟล์แนบประกอบครับ สำหรับการทำให้เป็นสีสามารถใช้การจัด Format ตามเงื่อนไขได้ ลอง Search คำว่า จัดรูปแบบตามเงื่อนไข หรือ Conditional Formatting ในฟอรัมนี้ดูครับ

[tawanbus]

อาจารย์คะ อยากทราบเวลาเข้าทำงานปกติ 16.00น. ออก 24.00น. แล้วถ้าทำโอออก 08.00น. วันรุ่งขึ้น เวลาโอทีไม่ขึ้นเป็น 8 ชั่วโมงค่ะ ของเดิมใช้สูตรที่แนบไฟล์มาให้ค่ะ
ขอบคุณค่ะอาจารย์

[snasui]

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

ส่วนคำตอบ ที่ L7 คีย์

=IF(OR(H7="",I7=""),"",MAX(0,I7-H7+(I7<H7)-J7))

Enter > Copy ลงด้านล่าง

[yuki_watashi]

อยากทรายวิธีคำนวณค่ากะค่ะ ดูตัวอย่างของเพื่อน ไม่เข้าใจค่ะ

[snasui]

อย่างแรกต้องทราบก่อนว่า '07:00 กับ 7:00 (แบบไม่มีเครื่องหมาย ' ) ไม่เหมือนกันนะครับ และถ้าไม่จำเป็นไม่ควรใส่เครื่องหมาย ' ครับ การจะให้แสดง hh:mm ต้องกำหนด Format ตัวอย่างการจัดรูปแบบให้คลิกเซลล์ที่เป็นเวลาในคอลัมน์ C แล้วกดแป้น Ctrl+1 > สังเกตการกำหนดค่า

ในไฟล์แนบผมทำตัวอย่างการคำนวณมาให้แล้วโดยที่ N2 คีย์

=IF(ISNUMBER(C2),IF(AND(C2<=7/24,D2>=19/24),20,IF(AND(C2>=7/24,C2<=19/24,D2>=7/24),40,0)),"")

Enter > Copy ลงด้านล่าง

[yuki_watashi]

ข้อมูลเวลา '07:00 มาจากโปรแกรม Payroll ค่ะ มีประมาณ 4000 แถว ต้อง ลบ ' ทีละเซลล์ ช้ามาก มีวิธีอื่นไหมคะ
ที่จะทำให้ ลบได้เร็วกว่านี้

[snasui]

ต้องสังเกตครับว่ามีเครื่องหมาย ' แล้วตามด้วยเครื่องหมายวรรค หรือไม่ โดยคลิกลงในเซลล์แล้วสังเกตครับ ยกตัวอย่างเช่นที่เซลล์ใด ๆ มีตัวเลข 7:00 ให้คลิกลงในเซลล์แล้วสังเกตว่าเป็น '7:00 (' ติดกับ 7:00) หรือ ' 7:00 (' แล้ววรรคแล้วตามด้วย 7:00)

เท่าที่สังเกตดูในไฟล์ยังมีวรรคด้านหลังตัวเลขด้วย ให้ทำการแก้ไขด้วยวิธีนี้ ครับ

1. คลิกเซลล์ใด ๆ ที่เห็นเป็นค่าว่างท้ายตัวเลข
2. กดแป้น F2 เพื่อ Edit Cell
3. กด Shift ค้างไว้แล้วกดลูกศรซ้าย 1 ครั้งจะเป็นการคลุมอักขระที่เห็นเป็นค่าว่าง
4. กด Ctrl+C เพื่อ Copy
5. กดแป้น Ctrl+H เพื่อเปิด Replace Dialog Box
6. คลิกช่อง Find what: แล้วกดแป้น Ctrl+V เพื่อวางข้อมูล > ช่อง Replace with: ปล่อยว่าง
7. คลิกปุ่ม Replace All
8. คลุมครั้งละ 1 คอลัมน์ > Data > Text to columns > Finish จะเป็นการแปลง Text ให้เป็น Number (วัน เวลา เป็น Number ไม่ใช่ Text)

[yuki_watashi]

1. คลิกเซลล์ใด ๆ ที่เห็นเป็นค่าว่างท้ายตัวเลข
2. กดแป้น F2 เพื่อ Edit Cell
3. กด Shift ค้างไว้แล้วกดลูกศรซ้าย 1 ครั้งจะเป็นการคลุมอักขระที่เห็นเป็นค่าว่าง
4. กด Ctrl+C เพื่อ Copy
5. กดแป้น Ctrl+H เพื่อเปิด Replace Dialog Box
6. คลิกช่อง Find what: แล้วกดแป้น Ctrl+V เพื่อวางข้อมูล > ช่อง Replace with: ปล่อยว่าง
7. คลิกปุ่ม Replace All
8. คลุมครั้งละ 1 คอลัมน์ > Data > Text to columns > Finish จะเป็นการแปลง Text ให้เป็น Number (วัน เวลา เป็น Number ไม่ใช่ Text)



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

[snasui]

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