Excel Forum

ต้องการหาสิทธิ์วันลาคงเหลือ
โดยที่ต้องการหา
1. ต้องการหาสูตรที่ใส่รหัสตรงคอลัมภ์ I แล้ว ชื่อพนักงาน สิทธิ์การลาที่ใช้ไปทั้งหมดที่ใช้ไปของพนักงานแต่ละคน ปรากฏอยู่ตรงคอลัมภ์ K-T
2. ต้องการหาสูตรในการคำนวณหาสิทธิ์คงเหลือของพนักงาน ว่าเหลือกี่วัน กี่ชั่วโมงและกี่นาที
3. โดยใน 1 วัน พนักงานทำงานทั้งหมด 8 ชั่วโมง 40 นาที (เริ่ม 08.00-12.20 น. พักเที่ยง 12.20-13.20 น. และเริ่มงานเวลา 13.20-17.40 น.) โดยหากลา 1/2 วัน ก็อยู่ที่ 4 ชั่วโมง 20 นาที
4. โดยหากช่องนาทีผลรวมมากกว่า 60 นาที ให้ไปบวกเพิ่มในช่องชั่วโมง
5. และในช่องชั่วโมง = 8 และช่อง นาที = 40 ให้ไปบวกเพิ่มในช่องวันลานั้นๆ โดยพนักงานคนอื่นๆก็เหมือนกันค่ะ ทั้งลากิจและลาป่วย ก็เหมือนกันกับลาพักร้อนค่ะ

ลองแบบนี้ครับ

J4
=VLOOKUP(I4,$A$3:$B$20,2,0)

K4
=SUMIFS($E$3:$E$20,$A$3:$A$20,$I4,$C$3:$C$20,LOOKUP(CHAR(255),$K$2:K$2))+(SUMIFS($F$3:$F$20,$A$3:$A$20,$I4,$C$3:$C$20,LOOKUP(CHAR(255),$K$2:K$2))+
SUMIFS($G$3:$G$20,$A$3:$A$20,$I4,$C$3:$C$20,LOOKUP(CHAR(255),$K$2:K$2))/60>8+4/6)
L4
=INT(MOD((SUMIFS($F$3:$F$20,$A$3:$A$20,$I4,$C$3:$C$20,LOOKUP(CHAR(255),$K$2:K$2))+
SUMIFS($G$3:$G$20,$A$3:$A$20,$I4,$C$3:$C$20,LOOKUP(CHAR(255),$K$2:K$2))/60)/24,"8:40")*24)
M4
=INT(MOD(MOD((SUMIFS($F$3:$F$20,$A$3:$A$20,$I4,$C$3:$C$20,LOOKUP(CHAR(255),$K$2:L$2))+
SUMIFS($G$3:$G$20,$A$3:$A$20,$I4,$C$3:$C$20,LOOKUP(CHAR(255),$K$2:L$2))/60)/24,"8:40")*24,1)*60)
คัดลอกสูตรใน K4:M4 ไปวางไว้ที่ N4:P4 และ Q4:S4 ตามลำดับครับ

T4
=SUMIFS($G$3:$G$20,$A$3:$A$20,$I4,$C$3:$C$20,T$2)

AD4
=U4-K4-(L4+M4>0)
AE4
=INT((IF(L4+M4>0,TIME(8,40,1))-TIME(L4,M4,))*24)
AF4
=INT(MOD(IF(L4+M4>0,TIME(8,40,1))-TIME(L4,M4,),1/24)*24*60)
คัดลอกสูตรใน AD4:AF4 ไปวางไว้ที่ AG4:AI4 และ AJ4:AL4 ตามลำดับ

จากนั้นคัดลอกสูตรทั้งหมดลงล่างครับ

ได้แล้วค่ะ ขอบคุณมากค่ะ

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

เช่น
พนักงานรหัส 800005 C พักร้อน สูตรรวมได้ 6 วัน 7 ชั่วโมง 59 นาที แต่ที่พนักงานลารวมทั้งหมดจริงๆที่ถูกต้องคือ 7 วัน 7 ชั่วโมง
พนักงานรหัส 800006 D พักร้อน สูตรรวมได้ 12 วัน แต่ที่พนักงานลารวมทั้งหมดจริงๆที่ถูกต้องคือ 13 วัน
พนักงานรหัส 800008 F พักร้อน สูตรรวมได้ 14 วัน 7 ชั่วโมง 9 นาที แต่ที่พนักงานลารวมทั้งหมดจริงๆที่ถูกต้องคือ 14 วัน 7 ชั่วโมง 10 นาที

K5
=INT(SUMPRODUCT(MMULT($E$3:$G$390,{480;60;1}),($A$3:$A$390=$I4)*($C$3:$C$390=LOOKUP("ๆ",$K$2:K$2)))/480)

L5
=INT(MOD(SUMPRODUCT(MMULT($E$3:$G$390,{480;60;1}),($A$3:$A$390=$I4)*($C$3:$C$390=LOOKUP("ๆ",$K$2:K$2))),480)/60)

M5
=MOD(SUMPRODUCT(MMULT($E$3:$G$390,{480;60;1}),($A$3:$A$390=$I4)*($C$3:$C$390=LOOKUP("ๆ",$K$2:K$2))),60)

K5:M5 ลากลง แล้ว copy ไป N5, Q5


AD5
=QUOTIENT(SUMPRODUCT(U4:W4-K4:M4,{480,60,1}),480)
AE5
=SIGN(AD4)*INT(MOD(ABS(SUMPRODUCT(U4:W4-K4:M4,{480,60,1})),480)/60)
AF5
=SIGN(AD4)*MOD(ABS(SUMPRODUCT(U4:W4-K4:M4,{480,60,1})),60)

AD5:AF5 ลากลง แล้ว copy ไป AG5, AJ5

ปรับสูตรเดิมโดยให้คิดจากนาทีแทนครับ ผมดันไปเขียนให้คิดจากวัน

K4
=INT((SUMIFS($E$3:$E$390,$A$3:$A$390,$I4,$C$3:$C$390,LOOKUP(CHAR(255),$K$2:K$2))*520+SUMIFS($F$3:$F$390,$A$3:$A$390,$I4,$C$3:$C$390,LOOKUP(CHAR(255),$K$2:K$2))*60+
SUMIFS($G$3:$G$390,$A$3:$A$390,$I4,$C$3:$C$390,LOOKUP(CHAR(255),$K$2:K$2)))/520)

L4
=INT(MOD(SUMIFS($E$3:$E$390,$A$3:$A$390,$I4,$C$3:$C$390,LOOKUP(CHAR(255),$K$2:K$2))*520+SUMIFS($F$3:$F$390,$A$3:$A$390,$I4,$C$3:$C$390,LOOKUP(CHAR(255),$K$2:K$2))*60+
SUMIFS($G$3:$G$390,$A$3:$A$390,$I4,$C$3:$C$390,LOOKUP(CHAR(255),$K$2:K$2)),520)/60)

M4
=MOD(MOD(SUMIFS($E$3:$E$390,$A$3:$A$390,$I4,$C$3:$C$390,LOOKUP(CHAR(255),$K$2:K$2))*520+SUMIFS($F$3:$F$390,$A$3:$A$390,$I4,$C$3:$C$390,LOOKUP(CHAR(255),$K$2:K$2))*60+
SUMIFS($G$3:$G$390,$A$3:$A$390,$I4,$C$3:$C$390,LOOKUP(CHAR(255),$K$2:K$2)),520),60)

หรือตามสูตรของคุณ Bo_ry ก็กระชับกว่าครับ แต่ต้องเปลี่ยนเลข 480 เป็น 520 และที่ M4 ควรเป็น
=MOD(MOD(SUMPRODUCT(MMULT($E$3:$G$390,{520;60;1}),($A$3:$A$390=$I4)*($C$3:$C$390=LOOKUP("ๆ",$K$2:K$2))),520),60)