Excel Forum

ต้องการหาสูตรในการคำนวนสิทธิ์การลาพักร้อนของพนักงาน
โดยปีแรกที่ครบ 1 ปี 3 ปี 8 ปี 10 ปี และ 15 ปี จะถัวเฉลี่ยวันลาพักร้อน

พนักงาน ครบ 1 ปี นับตั้งแต่วันเริ่มงาน ได้สิทธิ์ลาพักร้อน 6 วันทำงาน
พนักงาน ครบ 3 ปี นับตั้งแต่วันเริ่มงาน ได้สิทธิ์ลาพักร้อน 8 วันทำงาน (ครบปีแรกเฉลี่ยตามเดือน)
พนักงาน ครบ 8 ปี นับตั้งแต่วันเริ่มงาน ได้สิทธิ์ลาพักร้อน 10 วันทำงาน (ครบปีแรกเฉลี่ยตามเดือน)
พนักงาน ครบ 10 ปี นับตั้งแต่วันเริ่มงาน ได้สิทธิ์ลาพักร้อน 15 วันทำงาน (ครบปีแรกเฉลี่ยตามเดือน)
พนักงาน ครบ 15 ปีขึ้นไป นับตั้งแต่วันเริ่มงาน ได้สิทธิ์ลาพักร้อน 20 วันทำงาน (ครบปีแรกเฉลี่ยตามเดือน)
โดยปีแรกที่ครบ 1 ปี 3 ปี 8 ปี 10 ปี และ 15 ปี จะถัวเฉลี่ยวันลาพักร้อน

รหัส ชื่อ สกุล ว/ด/ป ที่เริ่มงาน อายุงาน วันพักร้อนที่ได้
1000006 นายส้มใส หน้าสวย 8 ก.พ. 55 6 ปี 8 เดือน 28 วัน 8
1000007 นายสมุทร ยิ่งใหญ่ 26 มิ.ย. 58 3 ปี 4 เดือน 30 วัน 7
1000008 นายอำนวย อวยพร 15 ธ.ค. 57 3 ปี 11 เดือน 10 วัน 6
1000009 นางสิริ มงคล 27 เม.ย. 60 1 ปี 6 เดือน 29 วัน 4
1000010 น.ส.ไสว ไปมา 31 ก.ค. 60 1 ปี 3 เดือน 25 วัน 3
1000011 น.ส.เสนา ยิ่งเจริญ 28 มี.ค. 61 0 ปี 7 เดือน 28 วัน 0

เพิ่ม Column "ค่าล่างของปีที่ทำงาน" และ เพิ่ม Row ที่ระบุเดือนเป็นเลข ตามรูปที่แนบครับ
แล้วใส่สูตร =INDEX($C$11:$N$21,MATCH(LOOKUP(DATEDIF(F26,TODAY(),"y"),$B$11:$B$21,$B$11:$B$21),$B$11:$B$21,0),MONTH(F26)) เพื่อดึงจำนวนพักร้อนครับ ลองทดสอบดูว่าได้คำตอบถูกต้องตามที่ต้องการหรือไม่ อีกทีนะครับ

ติดปัญหาตรงที่พนักงานเข้างาน วันที่ 26/12/59 มีอายุงาน 1 ปี 10 เดือน 30 วัน ได้สิทธ์ลาพักร้อน 1 วัน (ตามจริงต้องได้ 6 วัน หรือเปล่าคะ)
แต่พนักงานที่เข้างาน วันที่ 7 ส.ค. 60 มีอายุงาน 1 ปี 3 เดือน 18 วัน ได้สิทธิ์ลาพักร้อน 3 วัน

duean1983 wrote: Sun Nov 25, 2018 5:43 pm ติดปัญหาตรงที่พนักงานเข้างาน วันที่ 26/12/59 มีอายุงาน 1 ปี 10 เดือน 30 วัน ได้สิทธ์ลาพักร้อน 1 วัน (ตามจริงต้องได้ 6 วัน หรือเปล่าคะ)
แต่พนักงานที่เข้างาน วันที่ 7 ส.ค. 60 มีอายุงาน 1 ปี 3 เดือน 18 วัน ได้สิทธิ์ลาพักร้อน 3 วัน

แต่ละค่าที่อธิบายมานับช่องไหน อย่างไร จึงได้ค่าเท่านั้น กรุณาอธิบายมาอย่างละเอียดครับ

ต้องเอาวันเดือนปีที่เข้างานมาคำนวนก่อนค่ะว่าในปี 2561 ครบรอบ 1 ปี ช่วงเดือนไหนแล้วค่อยนำไปเทียบในตารางแรก โดยสิทธิ์ลาพักร้อนที่จะถัวเฉลี่ยคือจะถัวเฉลี่ยในปี พ.ศ.
ที่ครบ 1 ปีเพราะว่าสิทธิ์ลาพักร้อนจะตัดทุกสิ้นปีค่ะ

ตัวอย่างอย่างล่างค่ะ

พนักงานที่เข้างานวันที่ 7/8/2560 ครบ 1 ปี คือในเดือน สิงหาคม 2561 (คือปีปัจจุบัน) เมื่อนำไปเทียบในตารางแรก อัตราถัวเฉลี่ยวันพักร้อนที่มีสิทธิ์ คือ 3 วันค่ะ
ส่วนพนักงานอีกคน พนักงานที่เข้างานวันที่ 26/12/59 ครบ 1 ปี คือในเดือน ธันวาคม 2560 เท่ากับว่าในปี 2560 พนักงานได้สิทธิ์ลาพักร้อน 1 วัน ( เมื่อนำไปเทียบในตาราง 1 แถว 1 ปี)
แต่พอในปี 2561 เท่ากับว่าเริ่มปีใหม่ คือปี 2561 สิทธิ์วันลาพักร้อนพนักงานคนนี้จึงได้เต็ม 6 วัน ค่ะ (ตรงชื่อด้านหน้าคอลัมภ์ อาจทำให้สับสนค่ะ)

ตัวอย่างสูตรตามด้านล่างโดยอาศัยไฟล์ตามโพสต์ #1 ครับ

1. ที่ N11:N21 คีย์ 0 - 16 ตัวเลขดังคอลัมน์ B ในภาพจาก #2 ครับ
   Enter > Copy ลงด้านล่าง
2. ที่ K26 คีย์
   =OFFSET($B$11,MATCH(DATEDIF(E26,TODAY(),"y"),$N$11:$N$21)-1,MATCH(TEXT(E26,"ดดด"),$B$10:$M$10,0)-1)
   Enter > Copy ลงด้านล่าง

แบบนี้หรือเปล่าคะ แต่ว่าพนักงานที่เข้างาน 26/12/59 สิทธิ์วันลาพักร้อนที่ได้รับ ยังขึ้น 1 วัน เหมือนเดิมค่ะ

ถ้าไม่ใช่เลข 1 ต้องเป็นเลขอะไร มาจากเซลล์ไหนในตารางด้านบนหรือครับ

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

แต่สูตรที่ใช้ในการคำนวณหาวันลาพักร้อนแบบถัวเฉลี่ย ในปีที่ครบอายุงาน 1,3,8,10,15 ปี ให้ใช้อัตราถัวเฉลี่ย เนื่องจากพนักงานเข้าทำงานไม่ครบปีในปีปฎิฑินที่เข้างานปีแรก
โดยใช้สูตร
(จำนวนเดือนที่เหลือหลังจากครบ 1 ปี นับจนถึงสิ้นปีปฎิฑิน x จำนวนวันที่ได้รับสิทธิ์ลาพักร้อน)/ จำนวนเดือนใน 1 ปี (12 เดือน)

ตัวอย่าง นาย A เริ่มงานเมื่อวันที่ 1กรกฎาคม 2560 จะทำงานครบ 1 ปี ในวันที่ 30 มิถุนายน 2561 ตามระเบียบจะสามารถใช้สิทธิ์ลาพักร้อนได้ดังนี้
วันที่ 1 กรกฎาคม 2560 – 30 มิถุนายน 2561 (ครบ1ปี) = ยังไม่ได้รับสิทธิ
วันที่ 1 กรกฎาคม 2561 – 31 ธันวาคม 2561 (6เดือน) = ได้สิทธิลา 3 วัน (6 * 6)/12 = 3 วัน
วันที่ 1 มกราคม 2562 – 31 ธันวาคม 2562 (อายุงานครบ 2ปี 6เดือน) = ได้สิทธิลา 6 วัน (12 * 6)/12 = 6 วัน
วันที่ 1 มกราคม 2563 – 31 ธันวาคม 2563 (อายุงานครบ 3 ปี) = ได้สิทธิลา 7 วัน
โดยคำนวนถัวเฉลี่ยแบบนี้
*** 1 มกราคม 2563 ถึง 30 มิถุนายน 2563 = ได้สิทธิลา 3 วัน (6 * 6)/12 =3 วัน)
1 กรกฎาคม 2563 ถึง 30 ธันวาคม 2563 = ได้สิทธิลา 4 วัน (6 * 8)/12 = 4 วัน)

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

เซลล์ H13 คำตอบคือ 18 มีวิธีคิดอย่างไรจึงได้ค่าเท่านั้นครับ

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

เซลล์ H13 ใส่ตัวเลขผิดค่ะ ไม่ใช่ 18 ที่ถูกต้อง H13 = 19 ค่ะ ได้แก้ไขมาให้ใหม่แล้วค่ะ พอดีเอาไปเทียบกับตารางเลยตาลายไปหน่อย ขอโทษด้วยนะคะ

ประเด็นคือคำนวณมาอย่างไรจึงได้ค่าเท่านั้น ช่วยลำดับมาให้ใหม่อีกรอบครับ

ได้ยกตัวอย่าง วิธีการหาคำตอบของเซลล์ ที่ H13 : ที่มีค่าเท่ากับ 19 มาให้แล้วค่ะ ตามรายละเอียดด้านล่างค่ะ

ช่วยยืนยันว่า E29:K30 มีการวางข้อมูลได้ถูกต้องไม่ได้วางสลับกัน

้ถ้าถูกต้องเหตุใด 1 ปีจึงอยู่ก่อน 9 เดือน วันที่เริ่มในคอลัมน์ F ถูกต้องแล้วหรือไม่ครับ

E29 คือ 1 ปี 27/4/46-26/4/47
E30 คือ นับเดือนจำนวนเดือนหลังจากที่ 1 ปี ตั้งแต่ 27/4/47 -31/12/47 เลยพิมพ์ 9 เดือนค่ะ แต่ถ้านับตั้งแต่เริ่มงานจนถึงสิ้นปี 47 คือ 1 ปี 9 เดือน ค่ะ

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

ไฟล์จาก #7 เข้าใจว่าดู แถวจากอายุงานว่ากี่ปี(นับถึงปีใหม่ปีนั้น ๆ) แล้วดู คอลัมน์จากเดือนที่เข้างาน
ลองปรับ I26=OFFSET($B$11,MATCH(YEAR(NOW())-YEAR(E26),$N$11:$N$21)-1,MATCH(TEXT(E26,"ดดด"),$B$10:$M$10,0)-1)

ใช่แล้วค่ะ ได้ลองสูตรนี้แล้ว สามารถใช้งานได้ตรงกับตารางพอดีค่ะ ขอบคุณมากค่ะ