Page 1 of 2
ต้องการสูตรวิธีคำนวณเวลาให้หักลบพักเบรค
Posted: Wed Oct 09, 2013 3:19 pm
by want09
สมมุติว่า
Line หยุดทำงานตั้งแต่เวลา 9:30 น- 10:30 น = ต้องเสียเวลาไป 60 นาที แต่เนื่องจาก 10:00 น-10:10 น เป็นเวลาพักเบรคนั่นเท่ากับว่า เสียเวลาไป 50 นาทีเท่านั้น
ผมจะต้องใช้เขียนสูตรอย่างไร ให้ลบช่วงเวลาเวลาพักเบรคออกไป
นำไปเขียนเป็นสูตรให้ได้ถูกต้อง
ข้างล่างนี้คือเวลาพักเบรคของเเต่ละกะ
กะกลางวัน----------------------กะกลางคืน
8:00 - 8:10-------------------20:00 - 20:10
10:00 - 10:10 ---------------22:00 - 22:10
11:30 - 12:20----------------00:00 - 00:50
15:00 - 15:10 ---------------03:00 - 03:10
16:50 - 17:00 ---------------4:50 - 5:00
17:00 - 17:30 ---------------5:00 - 5:30
19:50 - 20:00 ---------------7:50 - 8:00
ผมใช้สูตรนี้ครับ
=(D3-B3)*1440
แต่ไม่รู้จะเขียนอย่างไรให้หักลบเวลาพักเบรคตามข้อมูลด้านบนของแต่ละกะ
จุดประสงค์ของผมคือต้องการผลลัพธ์ออกมาเป็นนาที เพื่อที่จะได้นำค่าในช่องสีเหลืองที่ได้ของเเต่ละวัน นำไปทำเป็นข้อมุลสรุปการสูญเสียเวลาในการผลิต เพื่อที่จะนำไปคิดเป็นประสิทธิภาพในการผลิต ประจำเดือน
ขอบคุณล่วงหน้าครับ
Re: ต้องการสูตรวิธีคำนวณเวลาให้หักลบพักเบรค
Posted: Wed Oct 09, 2013 4:22 pm
by bank9597
ลองตามนี้ครับ
ที่ E2 คีย์
Code: Select all
=IF(IF(B2<="20:00:00"+0,1,2)=1,IF($D2<=INDEX($H$3:$H$9,MATCH(1,IF($G$3:$G$9>=$B2,1),0)),$D2-$B2,($D2-$B2)-TIME(0,INDEX($I$3:$I$9,MATCH(1,IF($G$3:$G$9>=$B2,1),0)),0)),IF($D2<=INDEX($K$3:$K$9,MATCH(1,IF($J$3:$J$9>=$B2,1),0)),$D2-$B2,($D2-$B2)-TIME(0,INDEX($L$3:$L$9,MATCH(1,IF($J$3:$J$9>=$B2,1),0)),0)))
กด ctrl+shift+enter คัดลอกลงมา
Re: ต้องการสูตรวิธีคำนวณเวลาให้หักลบพักเบรค
Posted: Wed Oct 09, 2013 4:37 pm
by want09
bank9597 wrote: ลองตามนี้ครับ
ที่ E2 คีย์
Code: Select all
=IF(IF(B2<="20:00:00"+0,1,2)=1,IF($D2<=INDEX($H$3:$H$9,MATCH(1,IF($G$3:$G$9>=$B2,1),0)),$D2-$B2,($D2-$B2)-TIME(0,INDEX($I$3:$I$9,MATCH(1,IF($G$3:$G$9>=$B2,1),0)),0)),IF($D2<=INDEX($K$3:$K$9,MATCH(1,IF($J$3:$J$9>=$B2,1),0)),$D2-$B2,($D2-$B2)-TIME(0,INDEX($L$3:$L$9,MATCH(1,IF($J$3:$J$9>=$B2,1),0)),0)))
กด ctrl+shift+enter คัดลอกลงมา
ขอบคุณครับ
ลองใช้สูตรนี้ดูเเล้วใช้ได้ครับผลครับ
ผมขอเพิ่มเติมอีก ข้อนะครับ
คือใน เวลาทำงานของกะกลางวันไม่มีปัญหาครับ เเต่ถ้าลงเวลาของกะกลางคืนเเล้ว มันขึ้น #N/A ครับ
สมมุติว่า
Line หยุดทำงานตั้งแต่ วันที่9/10/56 เวลา 23:00 น- วันที่ 10-10-56 เวลา1:30 น
ซึ่งเป็นเวลาที่ข้ามวันใหม่ไปเเล้วครับ ต้องทำอย่างไรต่อดี ครับ
Re: ต้องการสูตรวิธีคำนวณเวลาให้หักลบพักเบรค
Posted: Wed Oct 09, 2013 4:49 pm
by bank9597
Line หยุดทำงานตั้งแต่ วันที่9/10/56 เวลา 23:00 น- วันที่ 10-10-56 เวลา1:30 น
ซึ่งเป็นเวลาที่ข้ามวันใหม่ไปเเล้วครับ ต้องทำอย่างไรต่อดี ครับ
สูตรที่ให้ไปไม่ได้ครอยคลุมเงือนไขที่ว่ามาครับ แม้แต่เงื่อนไขเดิมก็ยังไม่แน่ใจว่าจะถูกต้องหมดหรือไม่ จนกว่าผู้ถามจะนำไปทดสอบใช้กับข้อมูลจริง แล้วตรวจสอบดูว่าผิดพลาดตรงไหนบ้าง แล้วนำมาถามต่อไป
ในกรณีที่หยุดข้ามวัน สูตรย่อมทำงานผิดพลาดแน่นอน เพราะคุณ want09 ไม่ได้ระบุวันที่กำกับมาให้ด้วย
ดังนั้น ให้ลองกำกับวันที่มาให้ด้วยครับ เดี๋ยวจะปรับสูตรให้อีกครั้งนึง
ย้ำอีกครั้งคือ ให้เอาสูตรเดิมไปทดสอบกับเวลาหลายๆแบบก่อนครับ เพื่อหาข้อผิดพลาดแล้วแจ้งผมอีกครั้งครับ
Re: ต้องการสูตรวิธีคำนวณเวลาให้หักลบพักเบรค
Posted: Wed Oct 09, 2013 5:02 pm
by want09
bank9597 wrote:Line หยุดทำงานตั้งแต่ วันที่9/10/56 เวลา 23:00 น- วันที่ 10-10-56 เวลา1:30 น
ซึ่งเป็นเวลาที่ข้ามวันใหม่ไปเเล้วครับ ต้องทำอย่างไรต่อดี ครับ
สูตรที่ให้ไปไม่ได้ครอยคลุมเงือนไขที่ว่ามาครับ แม้แต่เงื่อนไขเดิมก็ยังไม่แน่ใจว่าจะถูกต้องหมดหรือไม่ จนกว่าผู้ถามจะนำไปทดสอบใช้กับข้อมูลจริง แล้วตรวจสอบดูว่าผิดพลาดตรงไหนบ้าง แล้วนำมาถามต่อไป
ในกรณีที่หยุดข้ามวัน สูตรย่อมทำงานผิดพลาดแน่นอน เพราะคุณ want09 ไม่ได้ระบุวันที่กำกับมาให้ด้วย
ดังนั้น ให้ลองกำกับวันที่มาให้ด้วยครับ เดี๋ยวจะปรับสูตรให้อีกครั้งนึง
ย้ำอีกครั้งคือ ให้เอาสูตรเดิมไปทดสอบกับเวลาหลายๆแบบก่อนครับ เพื่อหาข้อผิดพลาดแล้วแจ้งผมอีกครั้งครับ
รับทราบครับ
งั้นเดี๋ยวผมจะลงรายละเอียดที่ต้องใช้จริงๆดู เพราะถ้าผมเอาสูตรแบบครึ่งๆกลางๆ ไปใช้เดี่ยวมันจะไม่ได้สูตรที่ถูกต้อง
Re: ต้องการสูตรวิธีคำนวณเวลาให้หักลบพักเบรค
Posted: Wed Oct 09, 2013 10:07 pm
by want09
want09 wrote:bank9597 wrote:Line หยุดทำงานตั้งแต่ วันที่9/10/56 เวลา 23:00 น- วันที่ 10-10-56 เวลา1:30 น
ซึ่งเป็นเวลาที่ข้ามวันใหม่ไปเเล้วครับ ต้องทำอย่างไรต่อดี ครับ
สูตรที่ให้ไปไม่ได้ครอยคลุมเงือนไขที่ว่ามาครับ แม้แต่เงื่อนไขเดิมก็ยังไม่แน่ใจว่าจะถูกต้องหมดหรือไม่ จนกว่าผู้ถามจะนำไปทดสอบใช้กับข้อมูลจริง แล้วตรวจสอบดูว่าผิดพลาดตรงไหนบ้าง แล้วนำมาถามต่อไป
ในกรณีที่หยุดข้ามวัน สูตรย่อมทำงานผิดพลาดแน่นอน เพราะคุณ want09 ไม่ได้ระบุวันที่กำกับมาให้ด้วย
ดังนั้น ให้ลองกำกับวันที่มาให้ด้วยครับ เดี๋ยวจะปรับสูตรให้อีกครั้งนึง
ย้ำอีกครั้งคือ ให้เอาสูตรเดิมไปทดสอบกับเวลาหลายๆแบบก่อนครับ เพื่อหาข้อผิดพลาดแล้วแจ้งผมอีกครั้งครับ
รับทราบครับ
งั้นเดี๋ยวผมจะลงรายละเอียดที่ต้องใช้จริงๆดู เพราะถ้าผมเอาสูตรแบบครึ่งๆกลางๆ ไปใช้เดี่ยวมันจะไม่ได้สูตรที่ถูกต้อง
อันนี้คือรายละเอียดครับ ตามเอกสารข้างล่างนี้ เเทบสีเขียวนี้คือ Sheet ใช้งานจริงครับ ส่วน สีเเดงนั้น Sheet ที่คุณ bank9597 ทำให้ผมลองกรอกข้อมูลดูเเล้วก็ยังพบว่ามีส่วนที่ผิดอยู่เช่น
ผมคงต้องกลับบ้านเเล้วครับ พรุ่งนี้ผมจะเข้ามาดูอีกที่เพื่อผมทำข้อมูลให้ไม่เคลียร์ ครับ
Re: ต้องการสูตรวิธีคำนวณเวลาให้หักลบพักเบรค
Posted: Thu Oct 10, 2013 2:05 am
by bank9597
ลองตามนี้ครับ
เอาข้อมูลกะกลางคืนมาต่อที่ G10 (20:00น เป็นต้นไป)
ที่ I3 คีย์ =H3-G3 คัดลอกลงมา
ที่ J2 คีย์
Code: Select all
=MIN(B2,IF(MIN(IF($G$3:$G$16>=B2,$G$3:$G$16))="00:00:00"+0,"24:00:00"+0,MIN(IF($G$3:$G$16>=B2,$G$3:$G$16))))
กด ctrl+shift+enter คัดลอกลงมา
ที่ K2 คีย์
Code: Select all
=MAX(IF(AND(D2>="00:00:00"+0,D2<="08:00:00"+0),D2+1,D2),IF(AND(MIN(IF(MIN(IF($G$3:$G$16>=B2,$G$3:$G$16))=$G$3:$G$16,$H$3:$H$16))>="00:00:00"+0,MIN(IF(MIN(IF($G$3:$G$16>=B2,$G$3:$G$16))=$G$3:$G$16,$H$3:$H$16))<="08:00:00"+0),MIN(IF(MIN(IF($G$3:$G$16>=B2,$G$3:$G$16))=$G$3:$G$16,$H$3:$H$16))+1,MIN(IF(MIN(IF($G$3:$G$16>=B2,$G$3:$G$16))=$G$3:$G$16,$H$3:$H$16))))
กด ctrl+shift+enter คัดลอกลงมา
ที่ L2 คีย์
Code: Select all
=IF(MAX(IF($G$3:$G$16<=D2,$G$3:$G$16))=MIN(IF($G$3:$G$16>=B2,$G$3:$G$16)),0,MAX(IF($G$3:$G$16<=D2,$G$3:$G$16)))
กด ctrl+shift+enter คัดลอกลงมา
ที่ M2 คีย์ =IF(L2=0,0,MAX(IF($G$3:$G$16<$K2,$G$3:$G$16))) กด ctrl+shift+enter คัดลอกลงมา
ที่ N1 คีย์ =SUM(IF($G$3:$G$16>MIN(IF($G$3:$G$16>=B2,$G$3:$G$16)),IF($G$3:$G$16<=M2,$I$3:$I$16))) กด ctrl+shift+enter คัดลอกลงมา
ที่ E2 คีย์ =(K2-J2)-MIN(IF(MIN(IF($G$3:$G$16>=B2,$G$3:$G$16))=$G$3:$G$16,$I$3:$I$16))-N2 กด ctrl+shift+enter คัดลอกลงมา
Re: ต้องการสูตรวิธีคำนวณเวลาให้หักลบพักเบรค
Posted: Thu Oct 10, 2013 4:09 pm
by want09
ขอบคุณครับ คุณ bank9597 ที่ช่วยสร้างสูตรให้ผม
วันนี้ผมลองโหลดไฟล์สูตรเอาไปใช้ ลองทำดูตั้งเเต่ 9 โมง ผมยังทำไม่ได้เลย แล้วพอใส่ค่าลงไปเเล้ว มีทั้งผิดและถูก ก็ไม่ขึ้นเลยตามภาพข้างล่างพร้อมไฟล์แนบมาด้วยครับ ช่วยเเก้ไขให้ด้วยนะครับ
ไฟล์ก่อนหน้านี้ผิดอันนะครับ ใช้ไฟล้นี้ครับ ขออภัยด้วยครับ
Re: ต้องการสูตรวิธีคำนวณเวลาให้หักลบพักเบรค
Posted: Thu Oct 10, 2013 5:07 pm
by bank9597
1. คุณต้องเอาเวลา กะกลางคืน มาอยู่ในคอลัมน์เดียวกันใน กะกลางวัน และเอาเวลามาลบกัน ก็จะได้เวลาพักเบรค เช่น 08:10 - 08:00 จะได้ 10 นาที โดยข้อมูลจะอยู่ในรูปแบบวันที่โดยอัตโนมัติ การทำแบบนี้ ทำให้ลดขั้นตอนการใช้สูตรได้อีกเยอะทีเดียว
2. เงื่อนไขมีความซับซ้อนมาก ไม่สามารถจะคำนวนโดยใช้สูตรเดียวได้ ต้องมีคอลัมน์อื่นๆมาช่วยแจกแจงด้วย
Re: ต้องการสูตรวิธีคำนวณเวลาให้หักลบพักเบรค
Posted: Fri Oct 11, 2013 8:06 am
by want09
bank9597 wrote:
1. คุณต้องเอาเวลา กะกลางคืน มาอยู่ในคอลัมน์เดียวกันใน กะกลางวัน และเอาเวลามาลบกัน ก็จะได้เวลาพักเบรค เช่น 08:10 - 08:00 จะได้ 10 นาที โดยข้อมูลจะอยู่ในรูปแบบวันที่โดยอัตโนมัติ การทำแบบนี้ ทำให้ลดขั้นตอนการใช้สูตรได้อีกเยอะทีเดียว
2. เงื่อนไขมีความซับซ้อนมาก ไม่สามารถจะคำนวนโดยใช้สูตรเดียวได้ ต้องมีคอลัมน์อื่นๆมาช่วยแจกแจงด้วย
ผมอัพไฟล์ผิดอันครับ ขออภัยด้วย ต้องเป็นไฟล์นี้ครับ
Re: ต้องการสูตรวิธีคำนวณเวลาให้หักลบพักเบรค
Posted: Fri Oct 11, 2013 4:03 pm
by bank9597
want09 wrote:bank9597 wrote:
1. คุณต้องเอาเวลา กะกลางคืน มาอยู่ในคอลัมน์เดียวกันใน กะกลางวัน และเอาเวลามาลบกัน ก็จะได้เวลาพักเบรค เช่น 08:10 - 08:00 จะได้ 10 นาที โดยข้อมูลจะอยู่ในรูปแบบวันที่โดยอัตโนมัติ การทำแบบนี้ ทำให้ลดขั้นตอนการใช้สูตรได้อีกเยอะทีเดียว
2. เงื่อนไขมีความซับซ้อนมาก ไม่สามารถจะคำนวนโดยใช้สูตรเดียวได้ ต้องมีคอลัมน์อื่นๆมาช่วยแจกแจงด้วย
ผมอัพไฟล์ผิดอันครับ ขออภัยด้วย ต้องเป็นไฟล์นี้ครับ
ผมก็จนปัญญาอยู่เหมือนกันครับ เลยต้องใช้วิธีที่เข้าใจยากและสูตรยาวมากๆ มาใช้ ลองดูครับว่าถูกต้องหรือไม่
ที่ตารางกะกลางคืน ต้องบวก 1 ให้กับเวลาตั้งแต่ 00:00 น. ถึง 08:00 น. ครับ
ที่ P4 คีย์
Code: Select all
=(MAX(O4,IFERROR(INDEX($C$4:$C$17,MATCH(MIN(M4,IFERROR(INDEX($B$4:$B$17,MATCH(1,IF(HOUR(M4)=HOUR($B$4:$B$17),1),0)),IF(HOUR(M4)>=0,IF(HOUR(M4)<8,M4+1,M4)))),$B$4:$B$17,0)),IF(HOUR(O4)>=0,IF(HOUR(O4)<8,O4+1,O4)))))-(MIN(M4,IFERROR(INDEX($B$4:$B$17,MATCH(1,IF(HOUR(M4)=HOUR($B$4:$B$17),1),0)),IF(HOUR(M4)>=0,IF(HOUR(M4)<8,M4+1,M4)))))-((SUM(IF(HOUR(M4)=HOUR($B$4:$B$17),$D$4:$D$17))+SUM(IF(HOUR(O4)<>HOUR(M4),IF(HOUR(O4)<>17,IF(HOUR(O4)<>20,IF(HOUR(O4)<>5,IF(HOUR(O4)=HOUR($B$4:$B$17),$D$4:$D$17)))))))+(SUM(IF(HOUR($B$4:$B$17)>HOUR(M4),IF(HOUR($B$4:$B$17)<HOUR(O4),$D$4:$D$17)))))
กด ctrl+shift+enter คัดลอกลงมา
ส่วนในหัวข้อื่นๆ ให้ปรับชื่อเซลล์ ไปตามนั้นครับ
Re: ต้องการสูตรวิธีคำนวณเวลาให้หักลบพักเบรค
Posted: Fri Oct 11, 2013 8:25 pm
by want09
ที่บอกว่าให้ ตารางกะกลางคืน ต้องบวก 1 ให้กับเวลาตั้งแต่ 00:00 น. ถึง 08:00 น.
เขียนยังไงครับ ผมลองเขียนเเล้วมันไม่ขึ้นเลย
เเล้วอีกเรื่องครับที่ คอลัมน์ P ก็ยังไม่ถูกครับ ผมไม่เก่งเรื่องสูตรครับ ยิ่งยาวๆๆอย่างนี้ มึนเลยครับผม แต่ผมก็ขอบคุณมากๆๆ ที่ช่วยผม
Re: ต้องการสูตรวิธีคำนวณเวลาให้หักลบพักเบรค
Posted: Fri Oct 11, 2013 8:32 pm
by snasui
จากไฟล์แนบด้านบน
ช่วยอธิบายวิธีคำนวณที่ Q25, Q27 คำตอบที่ถูกต้องคือ 2:20:00 และ 3:20:00 ตามลำดับ มีวิธีคิดอย่างไรถึงได้ค่าเท่านั้นครับ
Re: ต้องการสูตรวิธีคำนวณเวลาให้หักลบพักเบรค
Posted: Fri Oct 11, 2013 9:19 pm
by bank9597
ที่บอกว่าให้ ตารางกะกลางคืน ต้องบวก 1 ให้กับเวลาตั้งแต่ 00:00 น. ถึง 08:00 น.
เขียนยังไงครับ ผมลองเขียนเเล้วมันไม่ขึ้นเลย
แม้จะบวก 1 เข้าไปแล้ว ค่าที่แสดงก็จะเป็นเท่าเดิมครับ เมื่อเราบวก 1 แล้ว เอาเมาส์ไปคลิ๊กที่เวลา 00:00 น แล้วดูที่แถบสูตร ก็จะเห็นค่าเป็น 1/1/1900 00:00:00 ซึ่งเท่ากับเลยมา 1 วันแล้วครับ
เเล้วอีกเรื่องครับที่ คอลัมน์ P ก็ยังไม่ถูกครับ
ผมเช็คตามเงื่อนไขที่คุณให้มาแล้ว ค่อนข้างมั่นใจว่าถูกต้องแล้วครับ
หากข้อไหนยังไม่ถูก ลองให้ตัวอย่างคำตอบที่ถูกต้องมาดูอีกทีครับ
Re: ต้องการสูตรวิธีคำนวณเวลาให้หักลบพักเบรค
Posted: Sat Oct 12, 2013 9:24 am
by want09
snasui wrote: จากไฟล์แนบด้านบน
ช่วยอธิบายวิธีคำนวณที่ Q25, Q27 คำตอบที่ถูกต้องคือ 2:20:00 และ 3:20:00 ตามลำดับ มีวิธีคิดอย่างไรถึงได้ค่าเท่านั้นครับ
ตามภาพข้างล่างนี้เลยครับอาจารย์
Re: ต้องการสูตรวิธีคำนวณเวลาให้หักลบพักเบรค
Posted: Sat Oct 12, 2013 12:10 pm
by want09
bank9597 wrote:ที่บอกว่าให้ ตารางกะกลางคืน ต้องบวก 1 ให้กับเวลาตั้งแต่ 00:00 น. ถึง 08:00 น.
เขียนยังไงครับ ผมลองเขียนเเล้วมันไม่ขึ้นเลย
แม้จะบวก 1 เข้าไปแล้ว ค่าที่แสดงก็จะเป็นเท่าเดิมครับ เมื่อเราบวก 1 แล้ว เอาเมาส์ไปคลิ๊กที่เวลา 00:00 น แล้วดูที่แถบสูตร ก็จะเห็นค่าเป็น 1/1/1900 00:00:00 ซึ่งเท่ากับเลยมา 1 วันแล้วครับ
เเล้วอีกเรื่องครับที่ คอลัมน์ P ก็ยังไม่ถูกครับ
ผมเช็คตามเงื่อนไขที่คุณให้มาแล้ว ค่อนข้างมั่นใจว่าถูกต้องแล้วครับ
หากข้อไหนยังไม่ถูก ลองให้ตัวอย่างคำตอบที่ถูกต้องมาดูอีกทีครับ
ลองกรอกข้อมูลเพิ่มไปดู ยังแปลกๆๆอยู่ครับ
Re: ต้องการสูตรวิธีคำนวณเวลาให้หักลบพักเบรค
Posted: Sat Oct 12, 2013 12:13 pm
by want09
bank9597 wrote:ที่บอกว่าให้ ตารางกะกลางคืน ต้องบวก 1 ให้กับเวลาตั้งแต่ 00:00 น. ถึง 08:00 น.
เขียนยังไงครับ ผมลองเขียนเเล้วมันไม่ขึ้นเลย
แม้จะบวก 1 เข้าไปแล้ว ค่าที่แสดงก็จะเป็นเท่าเดิมครับ เมื่อเราบวก 1 แล้ว เอาเมาส์ไปคลิ๊กที่เวลา 00:00 น แล้วดูที่แถบสูตร ก็จะเห็นค่าเป็น 1/1/1900 00:00:00 ซึ่งเท่ากับเลยมา 1 วันแล้วครับ
เเล้วอีกเรื่องครับที่ คอลัมน์ P ก็ยังไม่ถูกครับ
ผมเช็คตามเงื่อนไขที่คุณให้มาแล้ว ค่อนข้างมั่นใจว่าถูกต้องแล้วครับ
หากข้อไหนยังไม่ถูก ลองให้ตัวอย่างคำตอบที่ถูกต้องมาดูอีกทีครับ
ผมลองกรอกใส่ข้อมูลเวลาเพิ่มไปอีกที รู้สึกว่ายังแปลกๆๆ อยู่ครับ
Re: ต้องการสูตรวิธีคำนวณเวลาให้หักลบพักเบรค
Posted: Sat Oct 12, 2013 12:23 pm
by snasui
ลองดูตัวอย่างคำตอบที่
คอลัมน์ R ของชีท Report ส่วนชีท Config เป็นการทำตารางเวลาพักโดยนำข้อมูลในตารางด้านขวามาสรุปใส่ตารางด้านซ้ายเพื่อทำเป็นเวลาพักสะสมแล้วค่อย Lookup ไปใช้ครับ
Re: ต้องการสูตรวิธีคำนวณเวลาให้หักลบพักเบรค
Posted: Sat Oct 12, 2013 3:51 pm
by want09
snasui wrote: ลองดูตัวอย่างคำตอบที่
คอลัมน์ R ของชีท Report ส่วนชีท Config เป็นการทำตารางเวลาพักโดยนำข้อมูลในตารางด้านขวามาสรุปใส่ตารางด้านซ้ายเพื่อทำเป็นเวลาพักสะสมแล้วค่อย Lookup ไปใช้ครับ
อีกนิดนะครับ
ข้อ1
ในช่อง K กรณี ไม่มีข้อมูลลง มันขึ้น เป็น #N/A ครับ
ผมต้องการให้มันขึ้น - หรือไม่ขึ้นเลยก็ได้ครับเป็นช่องว่างๆ เพราะผมต้อง Sum ข้อมูลทั้งหมดในช่องสีเหลือง
เอาไปใช้งานครับ
ข้อ2
ที่ช่อง G5-G35 อยากให้เป็นอย่างนี้ครับ เวลาผมลงข้อมูลที่ BP1 เมื่อลงเเล้วต้องการให้ วันในช่องG5-G35เป็นไปตามรอบของปฎิทินที่ระบุ วันจันทร์-อาทิตย์ด้วยครับ
ช่วยเขียนสูตรให้ด้วยครับ
ข้อ3
ถ้าวันไหนตรงกับวันอาทิตย์ ทำอย่างไรให้กลายเป็นสีเดียวกันทั้งแถวครับ
ขอบคุณครับ
Re: ต้องการสูตรวิธีคำนวณเวลาให้หักลบพักเบรค
Posted: Sat Oct 12, 2013 4:28 pm
by snasui
ลองตามนี้ครับ
- การซ่อนค่า Error สามารถคีย์สูตรที่ K5 เป็นด้านล่าง
=If(I5="","",J5-H5+(J5<H5)-IF(AND(J5>H5,INDEX(Config!$B$3:$B$16,MATCH(1,INDEX(1/(Config!$B$3:$B$16>=H5),0),0))>=J5),0,INDEX(Config!$C$3:$P$16,MATCH(1,INDEX(1/(H5<=Config!$B$3:$B$16),0),0),MATCH(1,INDEX(1/(J5>=Config!$C$2:$P$2),0)))))
Enter > Copy ลงด้านล่าง
- การ Run ค่าวันที่จากเซลล์ BP1 ที่เซลล์ G5 คีย์สูตร
=$BP$1+ROWS(G$5:G5)-1
Enter > Copy ลงด้านล่าง
- การจัด Format วันอาทิตย์
คลุม F5:BO35 > เข้าเมนู Home > Conditoinal Formatting > Manage Rule > เลือก Rule ที่ต้องการ > Edit Rule > เลือก Use a formula to determine whice cells to format > ในช่อง Format values where this formula is true: คีย์สูตร =$F5="วันอาทิตย์" > OK > ตรง Appies to กำหนดช่วงเป็น =$F$5:$BO$35 > Apply > OK