คำนวณเวลาเริ่มต้น-สิ้นสุด ของการผลิต

[patcha08]

อยากทราบวิธีการเขียนสูตรคำนวนเวลาเริ่มต้น-สิ้นสุด ของการผลิตเเต่ละ Order
โดยมีเงื่อนไข หากเวลาเริ่มต้น เเละสิ้นสุดอยู่ในช่วงพักเบรคให้บวกเวลาพักเบรคเพิ่มเข้าไปด้วย
เช่น เริ่มต้นทำงานที่ 11/05/2018 8:00:00 น.
Order 01 ใช้เวลาทำงานทั้งหมด 5 ชม. หรือ 300 นาที
เวลาเริ่มต้น = 11/05/2018 8:00:00 น. เวลาสิ้นสุด = 11/05/2018 14:00:00 น. (รวมเวลาพักเที่ยง 1 ชม)
ตัวอย่างตามเอกสารแนบค่ะ

[snasui]

ต้องการคำตอบในเซลล์ใดให้คีย์ตัวอย่างคำตอบลงมาด้วยครับ

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

[patcha08]

ต้องขอโทษด้วยที่ยกตัวอย่างไม่สอดคล้องกับข้อมูล เเละอธิบายไม่ชัดเจนนะคะ
ขอยกตัวอย่างใหม่ให้สอดคล้องกับข้อมูลในไฟล์ Excel ดังนี้ค่ะ
อยากทราบวิธีการคำนวณหาเวลาเริ่มต้นเเละสิ้นสุดของการผลิตในเเต่ละ Order
โดยมีเงื่อนไข หากเวลาเริ่มต้น เเละสิ้นสุดอยู่ในช่วงพักเบรคให้บวกเวลาพักเบรคเพิ่มเข้าไปด้วย
ยกตัวอย่างเช่น เริ่มต้นทำงานที่ 2/4/2018 9:16 น.
Order : A002 ใช้เวลาทำงานทั้งหมด 6.23299 ชม.
เวลาเริ่มต้น = 2/4/2018 9:16 น. เวลาสิ้นสุด = 2/4/2018 16:30 น. (รวมเวลาพักเที่ยง 1 ชม)

ตัวอย่างตามเอกสารแนบค่ะ

[snasui]

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

1. N8 คีย์
   =L4+SUMIFS($T$5:$T$8,$R$5:$R$8,">"&$K4,$S$5:$S$8,"<="&$M4)
   Enter > Copy ลงด้านล่าง
2. O8 คีย์
   =MOD(N4,1)
   Enter > Copy ลงด้านล่าง

[patcha08]

ทดลองเอาสูตร =L4+SUMIFS($T$5:$T$8,$R$5:$R$8,">"&$K4,$S$5:$S$8,"<="&$M4) ใส่ในช่อง N8 เเล้วค่ะ
จะหลุด case ที่ เวลาเริ่ม เเละ เวลาสิ้นสุด ไม่คลุมช่วงพักเบรค
เช่น Start = 16:30 Finish = 17:08
เวลาพักเบรคที่อยู่ระหว่างช่วงนี้คือ 16:40 - 17:30
ใช้สูตรตามที่ K. snasui เเนะนำมา เงื่อนไขแรกจะเป็นจริง ส่วนเงื่อนไขที่ 2 เป็นเท็จ จึงทำให้ไม่ได้เวลาพักเบรคมา

ก่อนหน้านี้เคยทดลองใช้สูตร SUMPRODUCT ตามด้านล่างนี้ ก็หลุด Case นี้เหมือนกัน

=L4+SUMPRODUCT(--(K4<=$S$5:$S$8),--(M4>=$T$5:$T$8),$U$5:$U$8)

เเล้วก็หลุด Case บวกเวลาพักรอบเเรกไปเเล้ว เเต่ไปตกช่วงเบรคอีกช่วงค่ะ
เช่นถ้า Start = 23:10 Finish 03:43
Case นี้จะได้เวลาพักเบรค ช่วง 00:00 - 01:00 = 1 ชม.
เวลา Finish ของ Case นี้จะเท่ากับ 03:43 + 01:00 = 04:43 ซึ่ง จะไปตกช่วงเบรค 04:40 - 05:30 อีกรอบ
เท่าที่ทดลองทำดู เหมือนว่าจะต้องเช็ค 2 ครั้ง เเต่ตอนนี้ไม่รู้จะเริ่มเขียนสูตรเช็คอย่างไรดี

ขอบคุณสำหรับความช่วยเหลือค่ะ

[snasui]

จะหลุด case ที่ เวลาเริ่ม เเละ เวลาสิ้นสุด ไม่คลุมช่วงพักเบรค
เช่น Start = 16:30 Finish = 17:08
เวลาพักเบรคที่อยู่ระหว่างช่วงนี้คือ 16:40 - 17:30...

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

[patcha08]

น่าจะได้เเล้วนะคะ
ใช้วิธีเช็ค Break 2 ช่วง
Check Break ช่วงเเรกที่ช่อง G
=IF(F4>=$O$8,SUMPRODUCT(--(D4<=$N$5:$N$8),--(F4>$N$5:$N$8),$P$5:$P$8),
IF(D4>=$O$8,SUMPRODUCT(--(D4>=$N$5:$N$8),--(F4>$N$5:$N$8),$P$5:$P$8),
SUMPRODUCT(--(D4<=$N$5:$N$8),--(F4>$N$5:$N$8),$P$5:$P$8)))

เเล้วก็เอาเวลาผลิต(ช่อง B) + Start(ช่อง C) + Break1(ช่อง G) = ช่อง H
จากนั้นก็นำค่าที่ได้จาก ช่อง H ไปเช็คเวลา Break อีกครั้ง โดยใช้สูตร
=IF(AND(I4>$N$5,I4<=$O$5),$P$5,
IF(AND(I4>$N$6,I4<=$O$6),$P$6,
IF(AND(I4>$N$7,I4<=$O$7),$P$7,
IF(AND(I4>$N$8,I4<=$O$8),$P$8,0))))

เเต่ไม่เเน่ใจว่าจะหลุดช่วงไหนอีกหรือเปล่า รบกวนช่วยดูสูตรให้อีกครั้งนะคะ
เเละขอสอบถามเพิ่มเติมค่ะ..ถ้าต้องการเอาเขียนสูตร Sumproduct ใน VBA จะต้องเขียนอย่างไรคะ

ขอบคุณค่ะ

[snasui]

ยินดีด้วยครับ เอาไว้พบปัญหาแล้วค่อยนำมาถามกันต่อครับ

่ส่วนการแปลงสูตรเป็น VBA ก็ต้องแปลงมาเองเช่นกันตามกฎการใช้บอร์ดข้อ 5 ด้านบน ติดตรงไหนค่อยถามกันต่อครับ

[patcha08]

Code: Select all

If Cells(rw, cl + 4) >= Range("O8").Value Then
            break1 = Evaluate("=Sumproduct(--(Cells(rw, cl + 2)" & " <= " & "Sheet4.Range(“N5:N8”).Value),--( Cells(rw, cl + 4)" & " > " & "Sheet4.Range(“N5:N8”).Value ")
        ElseIf Cells(rw, cl + 2) >= Range("O8").Value Then
           break1 = Evaluate("=Sumproduct(--(Sheet4.Range(“N5:N8”).Value" & "<="" & Cells(rw, cl + 2)),--(Sheet4.Range(“N5:N8”).Value & " < " & Cells(rw, cl + 4) ")
        Else
           break1 = Evaluate("=Sumproduct(--(Sheet4.Range(“N5:N8”).Value" & ">="" & Cells(rw, cl + 2)),--(Sheet4.Range(“N5:N8”).Value & " < " & Cells(rw, cl + 4) ")
        End If

ต้องการเอาสูตร Sumproduct ด้านล่างนี้ ไปเขียนใน VBA ค่ะ ทดลองเขียนแล้วติด Error : Run-time Error 13 ค่ะ
=IF(F4>=$O$8,SUMPRODUCT(--(D4<=$N$5:$N$8),--(F4>$N$5:$N$8),$P$5:$P$8),
IF(D4>=$O$8,SUMPRODUCT(--(D4>=$N$5:$N$8),--(F4>$N$5:$N$8),$P$5:$P$8),
SUMPRODUCT(--(D4<=$N$5:$N$8),--(F4>$N$5:$N$8),$P$5:$P$8)))

[snasui]

ตัวอย่าง Code ครับ

Code: Select all

'Other code
        If Cells(rw, cl + 4) >= Range("O8").Value Then
            break1 = Evaluate("=Sumproduct(--(Cells(rw, cl + 2) " <= "" & "Sheet4.Range(""N5:N8"").Value),--( Cells(rw, cl + 4) > " & "Sheet4.Range(""N5:N8"").Value ")
        ElseIf Cells(rw, cl + 2) >= Range("O8").Value Then
           break1 = Evaluate("=Sumproduct(--(Sheet4.Range(""N5:N8"").Value" & "<="" & Cells(rw, cl + 2)),--(Sheet4.Range(""N5:N8"").Value & " < " & Cells(rw, cl + 4) ")
        Else
           break1 = Evaluate("=Sumproduct(--(Sheet4.Range(""N5:N8"").Value" & ">="" & Cells(rw, cl + 2)),--(Sheet4.Range(""N5:N8"").Value & " < " & Cells(rw, cl + 4) ")
        End If
'Other code

[patcha08]

ทดลองเขียนสูตรตามที่เเนะนำมา
เเต่ลอง Debug ดู ค่า Break1 รอบเเรกที่ลงมามันได้ค่า -1 ค่ะ
ซึ่งจริงๆ รอบเเรกจะต้องได้ค่าเป็น 0 ค่ะ
เเละที่สงสัยอีกจุดก็คือ Sumproduct ที่ใช้ใน VBA ต้องมีค่าสุดท้ายที่บอกว่าเมื่อเงื่อนไขเป็นจริง เเล้วให้เอาค่าช่องไหนมาคูณกัน หรือเปล่าคะ
เห็นในตัวอย่างไม่มี เเต่ก็ทดลองเติมเข้าไปเเล้ว รอบเเรกก็ยังได้ค่าเป็น -1 เหมือนเดิมค่ะ

Code: Select all

  If chk_befor_break >= Range("I8").Value Then
            break1 = Evaluate("=Sumproduct(--(Cells(rw, cl + 2) " <= "" & "Sheet4.Range(""H5:H8"").Value),--( Cells(rw, cl + 4) > " & "Sheet4.Range(""H5:H8"").Value), ")
        ElseIf chk_befor_break >= Range("I8").Value Then
           break1 = Evaluate("=Sumproduct(--(Sheet4.Range(""H5:H8"").Value" & "<="" & Cells(rw, cl + 2)),--(Sheet4.Range(""H5:H8"").Value & " < " & Cells(rw, cl + 4) ")
        Else
           break1 = Evaluate("=Sumproduct(--(Sheet4.Range(""H5:H8"").Value" & ">="" & Cells(rw, cl + 2)),--(Sheet4.Range(""H5:H8"").Value & " < " & Cells(rw, cl + 4) ")
        End If

[snasui]

จากไฟล์แนบล่าสุดสูตร Sumproduct ที่ถูกต้องเขียนว่าอย่างไรช่วยโพสต์มาอีกทีครับ

[patcha08]

จากไฟล์แนบล่าสุดสูตร Sumproduct ที่ถูกต้องเขียนว่าอย่างไรช่วยโพสต์มาอีกทีครับ

Code: Select all

If stop_time >= Range("I8").Value Then
           break1 = Evaluate("=Sumproduct(--(" & start_time & "   <=   " & break_start & " ),--( " & stop_time & "  >  " & break_start & ")," & total_break & ")")
        ElseIf start_time >= Range("I8").Value Then
           break1 = Evaluate("=Sumproduct(--(" & start_time & "   >= " & break_start & " ),--( " & stop_time & "  >  " & break_start & ")," & total_break & ")")
        Else
           break1 = Evaluate("=Sumproduct(--(" & start_time & "   <=   " & break_start & " ),--( " & stop_time & "  >  " & break_start & ")," & total_break & ")")
        End If

ปรับตาม Code นี้ สามารถใช้งานได้เเล้วค่ะ
ตอนเเรกไม่เข้าใจวิธีการใช้ Evaluate เเต่ไปหาอ่านข้อมูลใน Web ดู เลยพอจะเข้าใจว่ามันคือการเอาข้อความ หรือ string ไปใส่ไว้ในวงเล็บ
เช่น Evaluate("ข้อความหรือสูตร")
ส่วนการเอาตัวเเปรไปเเทนค่าในสูตรจะต้องอยู่ในรูปเเบบ "& ตัวแปร &"

ขอบคุณสำหรับความช่วยเหลือค่ะ