Snasui Excel Forum

รบกวนอาจราย์ทุกท่านช่วยผมด้วครับปวดตาแล้วครับ

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

1. E5 คีย์
   =IF(OR(C5>="20:00"+0,D5<C5),"Night","Day")
   Enter > Copy ลงด้านล่าง
2. F5 คีย์
   =MAX(0,D5-C5+(D5<C5))-CHOOSE(MATCH(E5,$K$5:$K$6,0),AND(C5<=$N$5,D5>=$M$5)/24,AND(C5<=$M$6,D5>=$MN$6,D5<C5)/24)
   Enter > Copy ลงด้านล่าง

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

1. E5 คีย์
   =IF(OR(C5>="20:00"+0,D5<C5),"Night","Day")
   Enter > Copy ลงด้านล่าง
2. F5 คีย์
   =MAX(0,D5-C5+(D5<C5))-CHOOSE(MATCH(E5,$K$5:$K$6,0),AND(C5<=$N$5,D5>=$M$5)/24,AND(C5<=$M$6,D5>=$MN$6,D5<C5)/24)
   Enter > Copy ลงด้านล่าง

ผมรบกวนถามต่ออีกนิดนะครับว่า
AND(C5<=$M$6,D5>=$MN$6,D5<C5)/24)
กะเช้ากับกะดึกมันเกี่ยวอะไรกับเวลาทำงานครับ แล้วมาหาร24 ยิ่งอ่าสูตรยิ่งไม่เข้าใจครับพี่

ทุกกะย่อมเกี่ยวกับเวลาทำงานทั้งหมดครับ

สูตรนั้นเป็นการหาเวลาพัก ว่าต้องหักด้วยเวลาพักหรือไม่ หากเข้างานก่อนเวลาพัก (C5<=$M$6) และออกงานหลังเวลาพัก (D5>=$MN$6) และถ้าเข้างานก่อนเที่ยงคืน เลิกงานหลังเที่ยงคืน เวลาเริ่มจะมากกว่าเวลาสิ้นสุด (D5<C5) ซึ่งต้องเข้าเงื่อนไขข้างต้นทั้งสามเงื่อนไขจึงจะหักเวลาพัก

หากเข้าเงื่อนไขทั้งหมดค่าที่ได้คือ True จึงนำ True (True มีค่าเท่ากับ 1 เทียบได้กับเวลา 1 ชม.) การหารด้วย 24 เพื่อทำให้เป็นระบบเวลา

1 วันมี 24 ชม. หรือ 24/24
ดังนั้น 1 ชม. คือ 1/24 หรือ True/24 ตามสูตรข้างบน

snasui wrote: ทุกกะย่อมเกียวกับเวลาทำงานทั้งหมดครับ

สูตรนั้นเป็นการหาเวลาพัก ว่าต้องหักด้วยเวลาพักหรือไม่ หากเข้างานก่อนเวลาพัก (C5<=$M$6) และออกงานหลังเวลาพัก (D5>=$MN$6) และถ้าเข้างานก่อนเที่ยงคืน เลิกงานหลังเที่ยงคืน เวลาเริ่มจะมากกว่าเวลาสิ้นสุด (D5<C5) ซึ่งต้องเข้าเงื่อนไขข้างต้นทั้งสามเงื่อนไขจึงจะหักเวลาพัก

หากเข้าเงื่อนไขทั้งหมดค่าที่ได้คือ True จึงนำ True (True มีค่าเท่ากับ 1 เทียบได้กับเวลา 1 ชม.) การหารด้วย 24 เพื่อทำให้เป็นระบบเวลา

1 วันมี 24 ชม. หรือ 24/24
ดังนั้น 1 ชม. คือ 1/24 หรือ True/24 ตามสูตรข้างบน

อาจารย์ครับ พอผลลัพธ์ที่ออกมาทำไมไม่ได้ค่าเท่ากับแปดชัวโมงครับ ผลลัมออกมาเป็นเวลารวมทั้งหมดครับ

ในไฟล์แนบผมเห็นว่าเป็น 8:00 ตามที่ต้องการอยู่แล้วครับ ไม่ทราบว่าผิดพลาดที่เซลล์ไหน กรุณาลงรายละเอียดในเซลล์นั้น ผมต้องการเข้าถึงปัญหาโดยเร็วครับ

snasui wrote: ในไฟล์แนบผมเห็นว่าเป็น 8:00 ตามที่ต้องการอยู่แล้วครับ ไม่ทราบว่าผิดพลาดที่เซลล์ไหน กรุณาลงรายละเอียดในเซลล์นั้น ผมต้องการเข้าถึงปัญหาโดยเร็วครับ

ขอโทษครับอาจารย์แสดงว่าผมทำสูตรถูกแล้วครับ
แต่ผมสามารถใช้สูตรงนี้หาเวลาโอทีจะได้หรือเปล่าวครับ
I7 คีย์
=MAX(0,D5-C5+(D5<C5))-CHOOSE(MATCH(E5,$K$5:$K$6,0),AND(C5<=$N$5,D5>=$M$5)/24,AND(C5<=$M$6,D5>=$MN$6,D5<C5)/24)
เพราะโอทีเริ่มห้าโมงครึ่งครับ แต่พนักงานบางคนสแกนนิ้วออกห้าโมงสามสิบสาม สามห้านะครับ แบบนี้สูตรจะErrorหรือเปล่าครับ

สามารถทดลองได้เลยจะทราบว่าใช้ได้หรือไม่ได้ครับ

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

snasui wrote: สามารถทดลองได้เลยจะทราบว่าใช้ได้หรือมไม่ได้ครับ

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

I7 คีย์
=MAX(0,D7-C7+(D7<C7))-CHOOSE(MATCH(E7,$L$7:$L$8,0),AND(N7<=$S$7,D7>=$R$7),AND(N7<=$R$8,D7>=$MN$8,D7<N7))
ผิดครับอาจารย์แสดงว่าผมยังไม่เข้าถึงสูตรนี้ หรือเป็นที่ผมคิดเรื่องสูดมากเกินไปครับ ขอบพระคุณครับที่ชี้แนะผมมาตลอดครับ

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

snasui wrote: อ่านที่ผมโพสต์แจ้งไปเรื่องการอธิบายเงื่อนไข กรุณาอธิบายมาอย่างละเอียด หรือหากเขียนไว้แล้วในตำแหน่งใดในไฟล์ให้แจ้งมาด้วยเสมอครับ

อาจารย์ครับ ไม่ทราบว่าผมเข้าใจผิดตรงไหนเหมือนกันครับ
วันที่ รายละเอียด เวลาเข้า เวลาออก กะ เวลาทำงานปกติ เวลาทำงานรวม OT1 OT1.5 OT3
26-เม.ย.-17 07:51 21:04 Day 13:13 13:13
27-เม.ย.-17 07:57 21:02 Day 13:05 13:05
28-เม.ย.-17 08:02 17:36 Day 09:34 09:34
29-เม.ย.-17 07:56 22:03 Day 14:07 14:07

จากที่ผมป้อนสูตร
F7 คีย์
=MAX(0,D7-C7+(D7<C7))-CHOOSE(MATCH(E7,$L$7:$L$8,0),AND(C7<=$N$7,D7>=$M$7)/24,AND(C7<=$M$8,D7>=$MN$6,D7<C7)/24)
ซึ่งผลลัพธ์ ออกมาเป็น 13.13 แต่ในความเป็นจริงแล้ว คำตอบต้องได้ = 8 ครับ
F8 คีย์
=MAX(0,D7-C7+(D7<C7))-CHOOSE(MATCH(E7,$L$7:$L$8,0),AND(C7<=$N$7,D7>=$M$7),AND(C7<=$M$8,D7>=$MN$6,D7<C7))
สูตรนี้ผมเอาหาร 24 ออกเพื่อไม่ให้สูตรลบ 1 ชั่วโมงพักออก ซึ่งผลลัพธ์ ออกมาเป็น 13.13 แต่ในความเป็นจริงแล้ว คำตอบต้องได้ = 14.30 ครับ
ชีตที่ผมทำงานอยู่คือ N006 ครับ ขอบคุณครับอาจารย์

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

อีกเรื่องหนึ่งที่สำคัญมาก ชีตเช็คเวลา ข้อมูลเวลาเป็น Text การนำมาใช้จะต้องแปลง Text ให้เป็น Number เสียก่อน สูตรใน C7 จึงควรเป็น

=VLOOKUP($M$4,เช็คเวลา!A:C,3,0)+0 เช่นนี้เป็นต้น

ค่อย ๆ แก้ไปทีละประเด็น ดูไฟล์ตัวอย่างประกอบครับ

snasui wrote: ผมทำตัวอย่างไฟล์การคิดเวลาทำงานปกติมาให้ดูลำดับการทำงาน โดยมีการแทรกเวลาทำงานปกติมาให้ด้วยเพื่อสะดวกในการเขียนสูตรครับ โดยได้แทรกคอลัมน์ F:J เข้ามาช่วย การคิด OT ต่าง ๆ ก็จะมีลักษณะเช่นเดียวกัน คอลัมน์ช่วยพวกนี้สามารถซ่อนเอาไว้ได้เพื่อการพิมพ์ แต่ในการใช้คำนวณไม่จำเป็นต้องซ่อนครับ

อีกเรื่องหนึ่งที่สำคัญมาก ชีตเช็ตเวลา ข้อมูลเวลาเป็น Text การนำมาใช้จะต้องแปลง Text ให้เป็น Number เสียก่อน สูตรใน C7 จึงควรเป็น

=VLOOKUP($M$4,เช็คเวลา!A:C,3,0)+0 เช่นนี้เป็นต้น

ค่อย ๆ แก้ไปทีละประเด็น ดูไฟล์ตัวอย่างประกอบครับ

อาจารย์ครับไฟล์ที่อาจารย์ให้มา ไม่ได้แทรกคอลัมน์ F:Jครับ ขอบคุณครับ

ผมเปลี่ยนไฟล์ให้ใหม่แล้วครับ

snasui wrote: ผมเปลี่ยนไฟล์ให้ใหม่แล้วครับ

ขอบคุณครับอาจารย์