สูตรคำนวณค่าล่วงเวลา

[BomZaii69]

คือผมต้องการให้ ช่อง N9 ให้สูตรคำนวณออกมาเป็นจำนวนเงินได้หรือเปล่าครับ โดยอ้างอิงจากวันหยุด ตามตาราง HolidayList ด้วย เพื่อคำนวณว่า ค่าล่วงเวลา เป็น 1 เท่า 1.5 เท่า หรือ 3 เท่า

(ช่อง G ล่วงเวลาจะเป็น 1 เท่า ครับ)

IF(OR(ISBLANK($B$9),ISBLANK($I9),ISBLANK(H9))=0,0,IF(AND(WEEKDAY($B9)>1,WEEKDAY($B9)<=7,NOT(ISNA(MATCH($B9,$P$9:$P$23,0)))=FALSE),1.5,IF(AND($I9>=8/24,$I9<=18/24),1,3)))



จะวางสูตรนี้อย่างไร ให้สูตรทำงานครับ คือผมลองวางแล้วมันไม่ทำงาน *VLOOKUP(N9,$M$3:$N$5,2,0)) รบกวนหน่อยครับ

[snasui]

ช่วยเขียนบรรยายมาเป็นข้อ ๆ ว่าเงื่อนไขมีกี่ข้อ อะไรบ้าง

เขียนตัวอย่างคำตอบมาสัก 2-3 ตัวอย่างว่าเซลล์ไหนต้องการคำตอบเป็นเท่าใด ด้วยเงื่อนไขใดจึงได้ค่าเท่านั้น จะได้เช้าใจตรงกันครับ

[BomZaii69]

ขอโทษครับ ที่คำถามไม่เคลียร์

คือช่อง N9 - N39 อยากให้สูตรคำนวณออกมาเป็นจำนวนเงินเลยได้หรือเปล่าครับ สูตรที่ผมใส่ไว้คือ หาว่า ค่าล่วงเวลาเป็น 1 เท่า หรือ 1.5 เท่า หรือ 3 เท่า ได้แล้ว แต่ จะใส่สูตรให้ คิดออกมาเป็นจำนวนเงินเลย ผมใส่ไม่ถูกครับ แต่มี สูตรแล้วคือ *VLOOKUP(N9,$M$3:$N$5,2,0)) (ซึ่งไม่รู้ว่าถูกหรือเปล่าด้วย)

ช่อง G9 - G39 ล่วงเวลาจะเป็น 1 เท่า วันอาทิดย์/วันหยุดนักขัตฤกษ์ ตรงตามเงื่อนไข ข้อ 3. ครับ

เช่น คือ N9 = J9 X N4 = 300 บาท

N22 = (G22 x N3) + (J22 x N5) =1,500 บาท


เงื่อนไขการคำนวณค่าล่วงเวลา

1. เวลาเข้างานปกติ จ-ศ 09.00-17.30น. เริ่ม โอที 1.5 เท่า ตั้งแต่ 18.00 น. - 08.00 น. ของวันรุ่งขึ้น
2. วันเสาร์ เข้างาน 09.00-12.00 เริ่ม โอที 1.5 เท่า ตั้งแต่ 13.00 น. - 08.00 น วันอาทิตย์
3. วันอาทิดย์/วันหยุดนักขัตฤกษ์ โอที 1 เท่า เป็นรายชั่วโมงต่อค่าแรงต่อวัน เริ่มโอที 3 เท่า ตั้งแต่ 18.00น - 08.00น. ของ วันรุ่งขึ้น

[snasui]

จากที่อธิบายมา เซลล์ N9 ใช้สูตรนี้ครับ

=J9*VLOOKUP(IF(OR(ISBLANK($B$9),ISBLANK($I9),ISBLANK(H9)),0,IF(AND(WEEKDAY($B9)>1,WEEKDAY($B9)<=7,NOT(ISNA(MATCH($B9,$P$9:$P$23,0)))=FALSE),1.5,IF(AND($I9>=8/24,$I9<=18/24),1,3))),$M$3:$N$5,2,0)*24

ส่วน N22 ช่วยอธิบายมาอย่างละเอียดว่าเงื่อนไขเป็นอย่างไรจึงคำนวณแบบนั้น

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

[BomZaii69]

จากที่อธิบายมา เซลล์ N9 ใช้สูตรนี้ครับ

=J9*VLOOKUP(IF(OR(ISBLANK($B$9),ISBLANK($I9),ISBLANK(H9)),0,IF(AND(WEEKDAY($B9)>1,WEEKDAY($B9)<=7,NOT(ISNA(MATCH($B9,$P$9:$P$23,0)))=FALSE),1.5,IF(AND($I9>=8/24,$I9<=18/24),1,3))),$M$3:$N$5,2,0)*24

ส่วน N22 ช่วยอธิบายมาอย่างละเอียดว่าเงื่อนไขเป็นอย่างไรจึงคำนวณแบบนั้น

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

ขอบคุณมากครับ ผมคิดจนปวดหัวก็ไม่ได้สักที คราวนี้ได้แล้วครับ

ส่วน N22 คือ วันอาทิดย์/วันหยุดนักขัตฤกษ์ โอที 1 เท่า เป็นรายชั่วโมงต่อค่าแรงต่อวัน[/color] เริ่มโอที 3 เท่า ตั้งแต่ 18.00น - 08.00น. ของ วันรุ่งขึ้น
ใน N22 ผมจะเข้างาน 14:30 - 17:30 = 3 ชม.จะเป็นโอที 1 เท่า*(N3) เลยอยากให้สูตรใน cell N22 คำนวณรวมไปเลย ครับ ก็คือ วันหยุด/วันหยุดนักขัตฤกษ์ จะมีโอที 1เท่า (G22*N3) + 3 เท่า (J22*N5) ครับ ไม่รู้ผมจะอธิบายงง หรือ เปล่า ถ้า ยังไงต้องขอโทษด้วยครับ และ ขอบคุณอีกครั้ง คือผมคิดมาหลายวันจนงงและปวดหัวไปหมดแล้วครับ

[snasui]

ลองตามนี้ครับ

เซลล์ N9 ปรับสูตรเป็น

=G9*VLOOKUP(1,$M$3:$N$5,2,0)*24+J9*VLOOKUP(IF(OR(ISBLANK($B$9),ISBLANK($I9),ISBLANK(H9)),0,IF(AND(WEEKDAY($B9)>1,WEEKDAY($B9)<=7,NOT(ISNA(MATCH($B9,$P$9:$P$23,0)))=FALSE),1.5,IF(AND($I9>=8/24,$I9<=18/24),1,3))),$M$3:$N$5,2,0)*24

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

[BomZaii69]

ขอบคุณครับ ได้แล้ว รบกวนอาจารย์หลายครั้งเลย สูตรโอเค แล้วครับ

[BomZaii69]

คือจะมีบาง cell ที่ขึ้น #N/A พอผมใส่สูตร SUMIF (N40) มันไม่แสดงผลลัพท์ ลองเปลี่ยน format cell แล้วก็ยังไม่ได้
หรือต้องแก้สูตร ไม่ให้แสดงค่า #N/A ครับ

ขออภัยที่ต้องรบกวนอีกครั้งครับ

[snasui]

สูตรในชีทนั้นมีการคำนวณวน แก้ไม่ให้คำนวณวนเสียก่อนครับ

จากภาพแนบจะเห็นว่าเซลล์ B2 เป็นเซลล์ที่มีปัญหาในการคำนวณวน และหากลบ B2 ออกไปแล้วอาจจะเกิดที่เซลล์อื่นได้อีก ต้องสังเกตในตำแหน่งตามภาพว่าเซลล์ใดยังมีปัญหาต้องแก้ไปเรื่อย ๆ จนหมด

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

[BomZaii69]

ผมแก้เรียบร้อยแล้วครับ ขอบคุณอาจารณด้วย สำหรับสูตรและคำชี้แนะต่างๆครับ