Snasui Excel Forum

สวัสดีครับทุกท่าน

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

1.อยากให้ช่วยเขียนสูตรในช่อง E โดยให้เช็คว่าจำนวนชั่วโมงการทำงานของช่อง C ซึ่งเป็นเวลาการทำงานขาเข้า และ ช่อง D ซึ่งเป็นเวลาการทำงานขาออก

มีจำนวนรวมในการทำงานเท่าไหร่ ( โดยให้มีการหักพัก 1 ชั่วโมงครับ )

2.อยากจะให้ช่วยเขียนสูตรในช่อง F โดยมีเงื่อนไขดังนี้ครับ

2.1 หากช่อง B แสดงว่าทำงาน ให้เช็คช่อง C , D หากมีข้อมูลทั้ง 2 ช่อง ให้นำข้อมูลในช่อง G มาแสดง
หากจำนวนชั่วโมงการทำงานในช่อง G เกิน 8 ชั่วโมง ให้นำข้อมูลในช่อง G + กับข้อมูลในช่อง H และนำมาแสดงครับ

2.2 หากช่อง B แสดงว่าหยุด W ให้เช็คช่อง C , D หากมีข้อมูลทั้ง 2 ช่อง ให้นำข้อมูลในช่อง I มาแสดง
หากจำนวนชั่วโมงการทำงานในช่อง I เกิน 8 ชั่วโมง ให้นำข้อมูลในช่อง I + กับข้อมูลในช่อง J และนำมาแสดงครับ

2.3 หากช่อง B แสดงว่าหยุด H ให้เช็คช่อง C , D หากมีข้อมูลทั้ง 2 ช่อง ให้นำข้อมูลในช่อง K มาแสดง
หากจำนวนชั่วโมงการทำงานในช่อง K เกิน 8 ชั่วโมง ให้นำข้อมูลในช่อง K + กับข้อมูลในช่อง J และนำมาแสดงครับ

ผมได้แนบ File ตัวอย่างมาให้เรียบร้อยแล้วครับ

E2, F2 สามารถคีย์สูตรด้านล่างตามลำดับครับ

1. Code: Select all

   =IF(AND(C2="",D2=""),0,D2-C2+(D2<C2)-1/24)

2. Code: Select all

   =IF(AND(C2<>"",D2<>""),CHOOSE(MATCH(B2,{"ทำงาน","หยุด W","หยุด H"},0),IF(G2>=8/24,G2+H2,G2),IF(I2>=8/24,I2+J2,I2),IF(K2>=8/24,K2+J2,K2)),0)

snasui wrote: E2, F2 สามารถคีย์สูตรด้านล่างตามลำดับครับ

1. Code: Select all

   =IF(AND(C2="",D2=""),0,D2-C2+(D2<C2)-1/24)

2. Code: Select all

   =IF(AND(C2<>"",D2<>""),CHOOSE(MATCH(B2,{"ทำงาน","หยุด W","หยุด H"},0),IF(G2>=8/24,G2+H2,G2),IF(I2>=8/24,I2+J2,I2),IF(K2>=8/24,K2+J2,K2)),0)

ผมมีเรื่องรบกวนอาจาร์ยเพิ่มเติมจากเดิม 2 เรื่องครับ

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

1.ปรากฏว่ามีอยู่ 35 ข้อมูลที่ขึ้น ###### ผมไม่แน่ใจว่าผมได้ทำผิดขั้นตอนอะไรรึเปล่า และผมได้แนบ File ตัวอย่างที่ขึ้น ##### มาให้แล้วครับโดยตัวอย่างอยู่ในช่องข้อมูลที่ 5 ถึง 39 ครับ

2.ผมอยากจะขอให้อาจารย์ช่วยเพิ่มเงื่อนไขของสูตรในช่อง F ให้ว่า หากทำตามเงื่อนไขแล้ว ไม่พบ จำนวน WH หรือจำนวน WH+OT ให้ขึ้นข้อความว่า Erro ครับผมได้แนบ File ตัวอย่างที่ต้องการให้สูตรออกมาแล้วในช่อง F2,F3,F4 ครับ

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

เซลล์ E5 ค่าที่ถูกต้องเป็นเท่าใด คำนวณอย่างไรจึงได้ค่านั้นครับ

หากคำตอบคือ 1:00:00 แสดงว่าคุณ spinoza ให้ข้อมูลเกี่ยวกับเวลาพักมาไม่ครบ ให้อธิบายเกียวกับเวลาพักมาใหม่อย่างละเอียดครับ

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

เซลล์ E5 ค่าที่ถูกต้องเป็นเท่าใด คำนวณอย่างไรจึงได้ค่านั้นครับ

หากคำตอบคือ 1:00:00 แสดงว่าคุณ spinoza ให้ข้อมูลเกี่ยวกับเวลาพักมาไม่ครบ ให้อธิบายเกียวกับเวลาพักมาใหม่อย่างละเอียดครับ

คำึตอบที่ถูกต้องในช่อง E5 คือ 1:00:00 ครับ

ผมต้องขออภัยด้วยครับผมให้ข้อมูลไม่ครบเกี่ยวกับเรื่องการหักพักตามที่อาจาร์ยบอกมาครับ

ผมจะข้อเพิ่มเติมเกี่ยวกับการหักพักว่าหาก มีเวลาการทำงานเกิน 5 ชั่วโมงให้หักพัก 1 ชั่วโมงครับ โดยยึดข้อมูลจากช่อง C , D เหมือนเดิมครับ

ต้องขออภัยอีกครั้งที่ให้ข้อมูลไม่ครบครับ

ปรับสูตรที่เซลล์ E2 เป็นด้านล่างครับ

=IF(AND(C2="",D2=""),0,D2-C2+(D2<C2)-((D2-C2+(D2<C2))>=5/24)*1/24)

ได้เรียบร้อยแล้วครับ ขอบพระคุณมากนะครับ

snasui wrote: ปรับสูตรที่เซลล์ E2 เป็นด้านล่างครับ

=IF(AND(C2="",D2=""),0,D2-C2+(D2<C2)-((D2-C2+(D2<C2))>=5/24)*1/24)

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

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

=IF(AND(Isnumber(C2),Isnumber(D2)),D2-C2+(D2<C2)-((D2-C2+(D2<C2))>=5/24)*1/24,"")

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

snasui wrote: ปรับสูตรที่เซลล์ E2 เป็นด้านล่างครับ

=IF(AND(C2="",D2=""),0,D2-C2+(D2<C2)-((D2-C2+(D2<C2))>=5/24)*1/24)

อาจาร์ยครับผมต้องรบกวนขอให้อาจาร์ยช่วยปรับสูตรให้อีกครั้งครับเนื่องจากผมต้องการเพิ่มเงื่อนไขอีก 1 ข้อครับ

โดยที่ หากช่อง C2 มีข้อมูล แต่ช่อง D2 ไม่มีข้อมูล
และ หากช่อง D2 มีข้อมูล แต่ช่อง C2 ไม่มีข้อมูล

อยากให้ช่อง F แสดงว่า ขาดเวลาทำงานครับ

ผมได้แนบ File ที่อาจารย์ได้เคยแนะนำสูตรพร้อมใส่ตัวอย่างที่ต้องการมาอีกครั้งครับ

ต้องขออภัยที่ไม่แจ้งเงื่อนไขให้เรียบร้อยครับ

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

=IF(COUNT(C2:D2)<>2,"ขาดเวลาทำงาน",IF(AND(C2="",D2=""),0,D2-C2+(D2<C2)-((D2-C2+(D2<C2))>=5/24)*1/24))

เรียนอาจารย์ครับ

ต้องขอภัยและขอรบกวนอาจารย์อีกครั้งนะครับ

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

พอดีผมติดปัญหาซึ่งอยากรบกวนให้อาจาร์ยช่วย ดูและแนะนำสูตรเพิ่มเติมให้อีกครั้งครับ

โดยที่ ผมติดปัญหา อยู่ 2 อย่างครับ

1.เพราะเหตุใด ช่อง (X5,Y5) (X9,Y9) (X10,Y10) (X11,Y11) ถึงมีค่าไม่เท่ากันครับ เพราะหากดูจากตาเปล่า ค่าที่ได้นั้นเท่ากันครับ
2.ผมอยากรบกวนให้อาจาร์ยช่วยปรับสูตรในช่อง X ครับ โดยมีเ้งื่อนไขเพิ่มเติมขึ้นมาว่า หาก เวลาการทำงาน มี OT แต่ไม่ถึง ครึ่งชั่วโมงให้แสดงข้อมูลตามจริงครับ ( ระบบที่ผมใช้นั้น ค่าล่วงเวลาจะคิดให้เมื่อเวลาการทำงาน OT เกินครึ่งชํ่วโมงครับ )

ผมได้ตัดข้อมูลที่มีปัญหาแนบ File ตัวอย่างมาให้เรียบร้อยแล้วครับ

ข้อ 1 ศึกษาจาก Link นี้ครับ wordpress/difference-form-calculation/

หากจะให้เท่ากับต้องใช้ฟังก์ชั่นปัดเศษเข้ามาช่วยเช่น Round

spinoza wrote:หาก เวลาการทำงาน มี OT แต่ไม่ถึง ครึ่งชั่วโมงให้แสดงข้อมูลตามจริงครับ

ปรับจากสูตรไหน และโปรแกรมจะทราบได้อย่างไรว่ามี OT ลองอธิบายมาอีกรอบโดยอ้างอิงจากเซลล์ในไฟล์ปัจจุบันครับ

snasui wrote: ข้อ 1 ศึกษาจาก Link นี้ครับ wordpress/difference-form-calculation/

หากจะให้เท่ากับต้องใช้ฟังก์ชั่นปัดเศษเข้ามาช่วยเช่น Round

spinoza wrote:หาก เวลาการทำงาน มี OT แต่ไม่ถึง ครึ่งชั่วโมงให้แสดงข้อมูลตามจริงครับ

ปรับจากสูตรไหน และโปรแกรมจะทราบได้อย่างไรว่ามี OT ลองอธิบายมาอีกรอบโดยอ้างอิงจากเซลล์ในไฟล์ปัจจุบันครับ

อยากให้อาจารย์ช่วยปรับสูตรเพิ่มเงื่อนไขในช่อง Y ครับ โดยอยากให้คำตอบที่ได้ออกมาในเป็นแบบ ช่อง X6 , X7 , X8 ครับ

สำหรับโปรแกรมนั้นจะคิด OT ตามชั่วโมงการทำงานที่แสดงในช่อง L , M ซึ่งเป็นเวลาเข้า และ เวลาออกครับ

โดยระบบจะคิดว่าเป็น OT ในกรณีที่มีชั่วโมงการทำงานรวมหักพัก 1 ชั่วโมงแล้ว ตั้งแต่ 08.30 ชั่วโมงขึ้นไปครับ

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

วันที่ 1 เวลาเข้า 07.00 เวลาออก 16.29
วันที่ 2 เวลาเข้า 07.00 เวลาออก 16.30

วันที่ 1 จะไม่ได้ชั่วโมง OT แต่สำหรับวันที่ 2 จะได้ชั่วโมงในส่วนของ OT 30 นาทีครับ

สูตรในคอลัมน์ Z ที่เขี่ยนมาแล้ว ไม่ตรงกับความต้องการอย่างไรจึงต้องเขียนใหม่ครับในคอลัมน์ Y ครับ

snasui wrote: สูตรในคอลัมน์ Z ที่เขี่ยนมาแล้ว ไม่ตรงกับความต้องการอย่างไรจึงต้องเขียนใหม่ครับในคอลัมน์ Y ครับ

ตามสูตรในคอลัมน์ Z ผมต้องการทำสูตรไว้ช่วยเช็คชั่วโมงการทำงานของพนักงานตามข้อมูลในช่อง L M ครับ

( สูตรนี้ต้องให้ช่วยเช็คการคิดจำนวนชั่วโมงการทำงานทั้งหมดตามจริงครับ )

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

(สูตรนี้ต้องการให้ช่วยเช็คเวลาการทำงานทั้งหมดเช่นเดียวกันครับ แต่ที่เกิน 8 ชั่วโมงหลังจากที่รวมหักพักแล้ว ให้ช่วยเช็คเพิ่มเติมว่าระบบนั้นทำการจ่ายโอทีให้กับพนักงานได้ถูกต้องหรือไม่ครับ โดยนำไป + กับค่าโอทีที่ทางระบบคิดให้ครับโดยค่าโอทีแสดงในช่อง AA ถึง AG ครับ )

ปัญหาที่ติดอยู่นิดเดียวคือว่า หากเวลาการทำงานของพนักงานเกิน 8 ชั่วโมงหลังจากหักที่พักแล้ว แต่มีเวลาการทำงานล่วงเวลาไม่ถึง 30 นาที ระบบจะไม่คิด OT ให้ครับ ซึ่งพอนำสูตรทั้ง 2 สูตรมาเทีบยกันในช่อง W จึงได้ค่าเป็น FALSE ครับ

โดยผมจะนำทั้งสองช่อง มาตรวจสอบว่าตรงกันไหม ในช่อง W อีกที ครับ

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

ตามข้อมูลตั้งแต่ AA:AG ไม่สามารถหาคำตอบตามที่ต้องการได้ ให้ตรวจสอบเงื่อนไขใหม่ครับ

ยกตัวอย่างสูตรที่ Y6 คือ สูตรที่เราใช้จะมี 3 สูตรเพื่อตรวจสอบว่าคอลัมน์ I เป็นค่าใด หากเป็นค่า ทำงาน จะใช้สูตร IF(AA6>=8/24,AA6+AC6,AA6) มาเป็นคำตอบ

หากค่าเป็น หยุด W จะใช้สูตร IF(AE6>=8/24,AE6+AF6,AE6) มาเป็นคำตอบ

หากค่าเป็น หยุด H จะใช้สูตร IF(AG6>=8/24,AG6+AF6,AG6) มาเป็นคำตอบ

และเนื่องจาก I6 เป็น หยุด W คำตอบที่เราต้องการจะมาจากสูตร IF(AE6>=8/24,AE6+AF6,AE6)

สูตรนี้เป็นการตรวจสอบว่า AE6 มีค่ามากกว่าหรือเท่ากับ 8:00 หรือไม่ หากใช่ ให้นำ AE6 ไปบวกกับ AF6 หากไม่ใช่ให้นำ AE6 มาเป็นคำตอบ

สำหรับกรณีนี้ AE6 มีค่า 8:00 ดังนั้นคำตอบจึงต้องนำ AE6+AF6 ซึ่งก็คือนำ 8:00+0

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

ตามที่อาจารย์ได้อธิบายมาผมเข้าใจเรียบร้อยแล้วครับ ผมต้องขออภัยอาจารย์อีกครั้งที่ให้เงื่อนไขไม่ชัดเจนครับ

หากเป็นแบบนั้น ผมขอให้อาจารย์ช่วยปรับสูตรในช่อง Z แทนครับ

โดยรบกวนอาจารย์เพิ่มเงื่อนไขที่ว่า

หากคำนวณเวลาการทำงานหลังจากที่หักพัก 1 ชั่วโมงแล้ว ได้จำนวนชั่วโมงการทำงานได้ตั้งแต่ 8.01 ถึง 8.29 ให้แสดงค่า 8.00 แทนครับ

ผมได้แนบ File ตัวอย่างพร้อมคำตอบที่ต้องการมาให้เรียบร้อยแล้วครับ

ตัวอย่างการปรับสูตรตามด้านล่างครับ

Z5 คีย์