Snasui Excel Forum

กะเข้าเวรมีดังนี้ครับ
07.00-16.00
07.00-18.00
07.00-19.00
08.00-17.00
08.00-18.00
09.00-18.00
11.00-20.00
13.00-22.00
18.00-22.00
19.00-07.00
ทุกกรณีจะให้เข้างานสายได้ 15 นาทีก่อนเข้างานแต่ถ้าเกินจาก 15 นาที หัก 1 ชั่วโมง และถ้าสายชั่วโมงต่อไปก็จะหักครั้งละ 1 ชั่วโมงไปเลยๆ
ผมใช้ if ในการกำหนดเงื่อนไข ดังรายละเอียด
=IF(C3>0,IF(C3>TIME(18,0,0),TIME(19,0,0),IF(C3>TIME(17,0,0),TIME(18,0,0),IF(C3>TIME(16,0,0),TIME(17,0,0),IF(C3>TIME(15,0,0),TIME(16,0,0),IF(C3>TIME(14,0,0),TIME(15,0,0),IF(C3>TIME(13,0,0),TIME(14,0,0),IF(C3>TIME(12,0,0),TIME(13,0,0),IF(C3>TIME(11,0,0),TIME(12,0,0),IF(C3>TIME(10,0,0),TIME(11,0,0),IF(C3>TIME(9,15,0),TIME(10,0,0),IF(C3>TIME(8,0,0),TIME(9,0,0),IF(C3>TIME(7,15,0),TIME(8,0,0),IF(C3>TIME(6,0,0),TIME(7,0,0),""))))))))))))),"")

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

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

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

ไม่เข้าใจเงื่อนไขในไฟล์แนบ ช่วยบรรยายมาสักตัวอย่างคำตอบครับ

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

snasui wrote: Sat Mar 10, 2018 2:01 pm ไม่เข้าใจเงื่อนไขในไฟล์แนบ ช่วยบรรยายมาสักตัวอย่างคำตอบครับ

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

เวลาเริ่มงานจะมี 13 กะ
เวลาเข้า-เวลาสิ้นสุด
7:00-16:00
7:00-18:00
7:00-19:00
8:00-17:00
8:00-18:00
9:00-18:00
11:00 -20:00
13:00-22:00
18:00-22:00
19:00-7:00
ผมจะนำเวลาที่พนักงานเข้างานจริงมาใส่ในช่อง C3 และเวลาออกมาใส่ในช่อง D3
ช่อง E3 จะเป็นเงื่อนไขในการที่จะนำมาคำนวณเวลาเริ่มต้นเข้างาน เช่น กะที่ 1 เข้างานเวลา 7:00-16:00 ผมก็จะกำหนดให้ช่อง E3 ไปดึงข้อมูลใน Sheet 2 ตามเงื่อนไข ที่อยู่ในตารางแต่ละวัน
สูตรที่ผมใส่ก็คือ if ที่กำหนดให้ เวลาเริ่มงานของแต่ละกะแต่ละวัน เพราะคนทำงานเดือน 1 เข้ากะแต่ละวันไม่เหมือนกันในแต่ละเดือนเช่น
วันที่ 2 เข้า 7:00-16:00
วันที่ 3 เข้า 13:00-22:00
วันที่ 4 เข้า 18:00-22:00
ผมจึงไม่สามารถที่จะใช้เงื่อนไขเดียวกับทุกเวลาได้ ต้องมาทำแยกเป็นเงื่อนไขแต่ละวัน ว่าเข้าเวลาไหน ออกเวลาไหน
ยกตัวอย่าง เข้างานวันที่ 2 เวลา 9:00-18:00 ผมก็จะใส่สูตร if ว่าถ้ามาสแกนนิ้วก่อนเวลา 9:15 นาที จะถือว่าเข้างาน 9:00 น. แต่ถ้าเกินจะหักเป็น 1 ชั่วโมงและถ้าชั่วโมงถัดไปยังมาสายอีกก็จะหักเพิ่มเป็น 1 ชั่วโมง ทำแบบนี้ไปจนสิ้นวัน
ผมเลยคิดว่าจะหาจุดเริ่มต้นทำงานแต่ละวันให้ได้ก่อนว่าเข้างานเวลาไหนแล้วจะโดนหักเวลาไหนบ้างตามแต่ละกะที่กำหนดมาแล้วค่อยนำมาหักลบเวลาเลิกงาน

tejasid wrote: Sat Mar 10, 2018 2:50 pm เวลาเริ่มงานจะมี 13 กะ
เวลาเข้า-เวลาสิ้นสุด
7:00-16:00
7:00-18:00
7:00-19:00
8:00-17:00
8:00-18:00
9:00-18:00
11:00 -20:00
13:00-22:00
18:00-22:00
19:00-7:00

ผมนับแล้วได้แค่ 10 กะครับ

tejasid wrote: Sat Mar 10, 2018 2:50 pm ช่อง E3 จะเป็นเงื่อนไขในการที่จะนำมาคำนวณเวลาเริ่มต้นเข้างาน เช่น กะที่ 1 เข้างานเวลา 7:00-16:00 ผมก็จะกำหนดให้ช่อง E3 ไปดึงข้อมูลใน Sheet 2 ตามเงื่อนไข ที่อยู่ในตารางแต่ละวัน สูตรที่ผมใส่ก็คือ if ที่กำหนดให้ เวลาเริ่มงานของแต่ละกะแต่ละวัน เพราะคนทำงานเดือน 1 เข้ากะแต่ละวันไม่เหมือนกันในแต่ละเดือน

ฟังก์ชั่น If ไม่เหมาะกับการใช้งานลักษณะนี้เพราะเวลาในคอลัมน์ E ขึ้นกับกะและวันที่ควรจะใช้ Index และ Macth ซึ่งผมตอบให้แล้วในกระทู้ก่อนหน้า ตัวเลขการเข้างานแต่ละกะไม่เหมือนกันในแต่ละเดือนจะต้องสร้างตารางเงื่อนไขเป็นรายเดือนครับ

tejasid wrote: Sat Mar 10, 2018 2:50 pm ยกตัวอย่าง เข้างานวันที่ 2 เวลา 9:00-18:00 ผมก็จะใส่สูตร if ว่าถ้ามาสแกนนิ้วก่อนเวลา 9:15 นาที จะถือว่าเข้างาน 9:00 น. แต่ถ้าเกินจะหักเป็น 1 ชั่วโมงและถ้าชั่วโมงถัดไปยังมาสายอีกก็จะหักเพิ่มเป็น 1 ชั่วโมง ทำแบบนี้ไปจนสิ้นวัน

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

ควรอธิบายว่าเลข 10:00 ใน E3 มีการพิจาณาอย่างไรจึงได้เป็น 10:00 ยกตัวอย่างคำอธิบายเช่น

มีเงือนไข 2 เงื่อนไขคือ

1. แนวบรรทัด วันที่ในเซลล์ A3 ของ Sheet1 ตรงกับวันที่ใน C1:AG1 ของ Sheet2
2. แนวคอลัมน์ เวลาใน B3 ของ Sheet1 ตรงกับเวลาใน B2:B11 ของ Sheet2

หากตรงกันให้นำค่าในจุดตัดของบรรทัดและคอลัมน์มาแสดง ซึ่งคำตอบของกรณีนี้คือค่าใน C2 ของ Sheet2 เช่นนี้เป็นต้น

อาจารย์ครับผมได้แนบไฟล์มาใหม่อาจารย์ ช่วยแนะนำด้วยครับ

สูตร Index และ Match ในคอลัมน์ E ของ Sheet1 เป็นสูตรธรรมดาไม่ใชสูตร Array ไม่จำเป็นต้องกดแป้น Ctrl+Shift+Enter

จากไฟล์แนบในโพสต์ #6 ไม่ทราบว่าคอลัมน์ E ใน Sheet1 ได้คำตอบตรงกับที่ต้องการหรือไม่ อย่างไร หรือติดขัดปัญหาใด ช่วยอธิบายมาด้วย สำหรับเงื่อนไขในตารางของ Sheet2 จะต้องระบุเข้ามาเองด้วยมือ ไม่ใช่ด้วยสูตร เพื่อใช้ค่าตั้่งต้นที่จะดึงไปใช้ใน Sheet1 การจะคำนวณว่าสายหรือไม่สายอย่างไรควรจะไปอยู่ในคอลัมน์ใด ๆ แล้วแต่จะกำหนดของ Sheet1 ไม่ใช่มาคำนวณใน Sheet2 ครับ

จากตัวอย่างในไฟล์ล่าสุด สมมุติคอลัมน์ F เป็นคอลัมน์ใช้คำนวณว่าสายหรือไม่ เซลล์ C3 คือเข้างานเป็นเวลา 09:16:00 และ E3 คือเวลาที่กำหนดคือ 10:00 แสดงว่าเป็นการเข้างานก่อนเวลาที่กำหนด จะไม่ถูกหักมาสาย จากกรณีนี้ เวลาสายใน F3 จะเป็น 0 นี่คือลักษณะการคำนวณที่ควรจะเป็นครับ

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

จากนั้นให้นำเวลาเข้างานใน C3 ของ Sheet1 ไปตรวจสอบว่าอยู่ในช่วงใดเวลาเข้างานตามที่กำหนดไว้ใน Sheet2 ให้นำเวลาเริ่มของเวลาตามกำหนดมาแสดงที่ B3 ของ Sheet1

สายหรือไม่สายเกิดจากการนำเวลาเข้างานจริงคือ C3 ของ Sheet1 ตั้ง หักด้วยเวลาตามกำหนดที่ดึงมาจาก Sheet2 คือ B3 ของ Sheet1 จะได้ผลลัพธ์มา 1 ค่า สมมุติว่าผลลัพธ์นี้อยู่ที่ F3 สูตรจะได้เป็น

=Max(0,C3-B3)

เราจะนำผลลัพธ์ใน F3 นี้ไปตรวจสอบว่าเข้าเกณฑ์จะหักการมาสายหรือไม่ เช่นนี้เป็นต้นครับ

อาจารย์ครับผมขอ ID Line ของอาจารย์ได้ไมครับ ผมอธิบายด้วยการพิมพ์ไม่ถูก ถ้าอาจารย์จะกรุณา หรืออาจารย์จะกรุณา Add ID LINE ผมก็ได้ครับ Id line : tejasid ครับ

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

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

OK ครับผมจะค่อยๆอธิบายนะครับ
ขอเรื่องเงื่อนไขการเข้างานก่อนนะครับ เอาตั้งแต่เริ่มแรกเลยนะครับ
ผมได้งานที่ต้องคำนวณ เงินเดือน โอที พนักงาน ซึ่งพนักงานมีหลายแผนก แต่ละแผนกก็มีเวลาเข้างานที่ต่างกัน เฉลี่ยแล้วคนละ 8 ชั่วโมง และ
11 ชั่วโมง หักพักกลางวันแล้ว บางคนเข้างาน เดือนหนึ่งมีหลายกะ เช่น วันนี้เข้า 9:00-18:00 วันต่อไปเข้า 13.00-22.00 เป็นต้น
ผมต้องมาดูว่า เวลาเข้างานเริ่มเมื่อไหร่ แล้วดูว่าพนักงานที่เข้ากะมาสแกนนิ้วสายหรือไม่ ถ้าสายเกิน 15 นาทีก็ต้องหักไป 1 ชั่วโมงแต่ถ้ามาสายเกินชั่วโมงต่อไปจะโดนหักไปอีกชั่วโมงหนึ่งเลื่อยๆ อาจารย์พอจะเข้าใจตรงจุดนี้ไมครับ
ผมต้องเอาเงื่อนไขตรงนี้แหละนำไปคำนวณชั่วโมงที่เหลือเพื่อคำนวณค่าแรงครับ

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

จากข้างต้นจะเป็น 3 คอลัมน์ คือ กะ, เวลาเข้า, เวลาออก

เดือนหนึ่งพนักงานจะเข้าเวลาไหนก็ตาม วันไหนก็ตาม สามารถนำมา Match กับเวลาเข้าออกในตารางนี้ครับ

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

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

tejasid wrote: Sat Mar 10, 2018 9:56 pm อาจารย์ครับผมทำเป็นกะ เวลาเข้า เวลาออก แล้วครับแต่ก็คิดไม่ออกว่าจะใช้เงื่อนไขได้อย่างไร

เนื่องจากมีการนำค่ากะมาใช้แล้วในคอลัมน์ E จึงไม่จำเป็นต้อง Lookup มาวาง ผมแยกคอลัมน์ E เป็น E:F แล้วนำไปเทียบกับเวลาเข้าจริง ผลต่างอยู่ที่คอลัมน์ K จากนั้นคิดเวลาเข้ากะที่คอลัมน์ L ลองดูว่าตรงกับเวลาที่เริ่มคิดเข้ากะหรือไม่ อย่างไร ค่อย ๆ ปรับไปครับ

เป็นวิธีคิดที่ผมไม่เคยคิดมาก่อนเลยครับ ไม่ต้องใช้สูตร if ซ่อนกันหลายชั้น ตอนนี้ถึงขั้นต่อไปนะครับอาจารย์ เวลาปกติจะสามารถคำนวณเวลาได้แต่ถ้าเป็นกะดึก เวลาเข้างาน 18.00-07.00 มันไม่สามารถคำนวณได้ครับต้องเขียนสูตรอย่างไรครับ

ตัวอย่างการปรับสูตรที่ H3 ครับ

=IF(OR(ISTEXT(B3),E3=""),"",E3-G3+(E3<G3))

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