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

[bigbu]

คือผมจะนำเอาข้อมูลจาก Time ทั้งปี มาเพื่อหาพนักงานที่มาสายในแต่ละเดือน โดยมีเงื่อนไขว่า หาเฉพาะพนักงานที่ภายในเดือนนั้น ๆ /ของปี มาทำงานสายเกิน 08:30 ตั้งแต่5ครั้งขึ้นไปและ5ครั้งนั้นเอาเวลามารวมกันแล้วเกิน30นาที จึงเอามาหัก 2 คะแนนในเดือนนั้น ๆ ครับ ผมลองเขียนฟังก์ชั่นดู งงเองครับ ไปไม่ถูกครับ ขอความช่วยเหลือด้วยครับ ผมแนบไฟล์มาด้วยครับ..ขอบคุณครับ

[snasui]

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

1. I4 คีย์คำว่า ชื่อ, J4:U4 คีย์ชื่อย่อของเดือนด้วยค่า Jan-Dec
2. ที่ E5 คีย์
   =LOOKUP(2,1/(LEFT(A$5:A5)="S"),A$5:A5)
   Enter > Copy ลงด้านล่างจนสุดข้อมูล
3. ที่ F5 คีย์
   =LOOKUP(2,1/(LEFT(A$4:A5)="0"),A$4:A5)
   Enter > Copy ลงด้านล่างจนสุดข้อมูล
4. ที่ G5 คีย์
   =IF(ISNUMBER(A5),A5,TODAY())
   Enter > Copy ลงด้านล่างจนสุดข้อมูล
5. ที่ I5 คีย์
   =IFERROR(INDEX($E$5:$E$841,MATCH(0,INDEX(COUNTIF(I$4:I4,$E$5:$E$841),0),0)),"")
   Enter > Copy ลงด้านล่างจนได้ผลลัพธ์เป็นค่าว่าง
6. ที่ J5 คีย์
   =IF($I5="","",--(AND(COUNTIFS($E$5:$E$841,$I5,$G$5:$G$841,"<="&EOMONTH(1&J$4,1),$G$5:$G$841,">="&EOMONTH(1&J$4,0))>=5,SUMIFS($D$5:$D$841,$E$5:$E$841,$I5,$G$5:$G$841,"<="&EOMONTH(1&J$4,1),$G$5:$G$841,">="&EOMONTH(1&J$4,0))>="0:30"+0)))
   Enter > Copy ไป U5 > ลงด้านล่าง

ผลลัพธ์ที่เป็นเลข 1 ภายใต้เดือนใด ๆ แสดงว่าเข้าเงื่อนไขครับ

[bigbu]

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

[Bo_ry]

Column D เป็น Text ไม่ใช่ตัวเลขจึงคำนวณไม่ได้
ให้เลือก column D ,Ribbon data > Text to columns > finish

สูตรอีกแบบ
J4 ใส่ 1/1
K4 ใส่ 1/2

เลือก J4:K4 ลากไปถึง U4 > custom format ddd

E5
=IF(LEFT(A5)="0",A6,LOOKUP("z",A$4:A5))

I5
=INDEX(A:A,AGGREGATE(15,6,ROW($A$5:$A$841)/(LEFT($A$5:$A$841)="s"),ROWS(I$5:I5)))

J5:U5
=--AND(COUNTIFS($A$6:$A$841,">="&J$4,$A$6:$A$841,"<"&EDATE(J$4,1),$E$6:$E$841,$I5)>4,SUMIFS($D$6:$D$841,$A$6:$A$841,">="&J$4,$A$6:$A$841,"<"&EDATE(J$4,1),$E$6:$E$841,$I5)>0.5/24)

[bigbu]

ขอขอบพระคุณ คุณ Bo_ry นะครับ สำหรับสูตรวิธีทำอีกแบบ...ขอถามเป็นความรู้นิดนึงนะครับ ตรง Lookup("z", ..."Z" คืออะไรเหรอครับ ในexcel เขาใช้แทนค่าตัวอะไรบ้างครับ ผมลองเปลี่ยนเป็น "Y"หรือ "X" มันก็ยังเป็นค่าเดิมครับ แต่พอผมเปลี่ยนเป็น "A" ค่าเปลี่ยนไปเป็นอีกตัวนึงครับ..ข้อ2.ตัวสุดท้าย >0.5/24) 24แทนค่าตัวใดบ้างครับ... แนะนำผมด้วย ต้องขออภัยที่ถามยิบย่อยนะครับ แต่ผมอยากจะอ่านสูตรได้บ้างและเข้าใจในตัวสูตรครับ

[Bo_ry]

Lookup เป็น Approximate match จะหาค่าเท่ากับหรือน้อยกว่าค่าที่หา
ต้องการรหัสพนักงานขึ้นต้นด้วย "s..." ค่าที่หาต้องมากว่า "s" ก็คือ "t" ขึ้นไปใช้ได้หมด

เวลาใน Excel 24 ชั่วโมง = 24/24 = 1 วัน
12 ชั่วโมง = 12/24 = 0.5 วัน
12 ชั่วโมง = 1/24 วัน
30 min = 1/2 ชั่วโมง = 0.5 ชั่วโมง =0.5/24 วัน =1/48 วัน

[bigbu]

Lookup เป็น Approximate match จะหาค่าเท่ากับหรือน้อยกว่าค่าที่หา
ต้องการรหัสพนักงานขึ้นต้นด้วย "s..." ค่าที่หาต้องมากว่า "s" ก็คือ "t" ขึ้นไปใช้ได้หมด

เวลาใน Excel 24 ชั่วโมง = 24/24 = 1 วัน
12 ชั่วโมง = 12/24 = 0.5 วัน
12 ชั่วโมง = 1/24 วัน
30 min = 1/2 ชั่วโมง = 0.5 ชั่วโมง =0.5/24 วัน =1/48 วัน

ขอบพระคุณ คุณ Bo_ry อีกครั้งครับ