สอบถามสูตร excel ในการดึงค่าช่วงเวลาทำงานหน่อยครับ

[conan885]

สอบถามสูตร excel ในการดึงค่าช่วงเวลาทำงานหน่อยครับ
เบื้องต้นเมื่อก่อนใช้เครื่องตอกบัตร แล้วพึ่งเปลี่ยนมาใช้
เครื่องสแกนใบหน้าจึงเก็บข้อมูลเบื้องต้นมา2-3วัน ในชีท"Data"
และต้องการดึงค่าเวลาไปในชีท"Data"ไปที่"พนักงานall"หรือ"พนักงานA"หรือ"พนักงานB"
ที่เป็นตัวอักษรสีแดง

โดยกำหนดช่วงเวลาทำงานช่วงเช้า 8.00-12.00
ช่วงเวลาทำงานช่วงบ่าย 13.00-17.00
ช่วงเวลาทำงานช่วงOT 18.00-21.00

ช่วงเช้าIn กำหนดช่วง 06.00-08.30
ช่วงเช้าOut กำหนดช่วง 12.00-12.29
ช่วงบ่ายIn กำหนดช่วง 12.30-13.30
ช่วงบ่ายOut กำหนดช่วง 17.00-17.29
ช่วงOTIn กำหนดช่วง 17.30-18.10
ช่วงOTOut กำหนดช่วง 21.00-22.00

ส่วนการนับจำนวนแรง ช่วงเช้า 0.5 แรง ช่วงบ่าย 0.5 แรง ช่วงOT 0.5

[snasui]

ดูการปรับข้อมูลในชีต Data เพื่อให้ง่ายต่อการนำไปใช้ และการใช้ฟังก์ชั่น Sumifs เข้ามาช่วยในชีต พนักงาน All, A และ B ตามไฟล์แนบครับ

[conan885]

ขอบคุณมากครับ

[conan885]

ยังมีข้อมูลที่ดึงมาผิด ถ้าพนักงานสแกน 2 ครั้ง ทำให้ ผลลัพท์คือการเอา 2 ข้อมูลมาบวกกัน
ไม่ทราบว่าสามารถเปลี่ยนไปใช้สูตรอื่นๆได้ไหม ที่เลือกเอาเฉพาะแค่ข้อมูลแรกออกมา
ยกตัวอย่างจะไฟล์เดิม พนักงาน M สแกน 2 ครั้งในวันที่ 10 ทำให้ช่วงบ่ายOutได้ข้อมูลมาเป็น10:05 แต่ข้อมูลจริงคือ 17:01 หรือ 17:03

[logic]

แบบนี้ไหมครับ

ชีต พนักงาน All
D2 =IFERROR(INDEX(Data!$F:$F,AGGREGATE(15,6,ROW(Data!$E$2:$E$201)/(Data!$E$2:$E$201=$B3)/(Data!$B$2:$B$201=$A3)/(Data!$G$2:$G$201=LOOKUP(CHAR(255),$D$1:D$1))/(Data!$H$2:$H$201=D$2),1)),0) คัดลอกไปขวาและล่าง

ชีพพนักงานA
C2 =IFERROR(INDEX(Data!$F:$F,AGGREGATE(15,6,ROW(Data!$E$2:$E$201)/(Data!$E$2:$E$201=$A5)/(Data!$B$2:$B$201=$E$1)/(Data!$G$2:$G$201=LOOKUP(CHAR(255),$C$3:C$3))/(Data!$H$2:$H$201=C$4),1)),0) คัดลอกไปขวาและล่าง

[conan885]

แบบนี้ไหมครับ

ชีต พนักงาน All
D2 =IFERROR(INDEX(Data!$F:$F,AGGREGATE(15,6,ROW(Data!$E$2:$E$201)/(Data!$E$2:$E$201=$B3)/(Data!$B$2:$B$201=$A3)/(Data!$G$2:$G$201=LOOKUP(CHAR(255),$D$1:D$1))/(Data!$H$2:$H$201=D$2),1)),0) คัดลอกไปขวาและล่าง

ชีพพนักงานA
C2 =IFERROR(INDEX(Data!$F:$F,AGGREGATE(15,6,ROW(Data!$E$2:$E$201)/(Data!$E$2:$E$201=$A5)/(Data!$B$2:$B$201=$E$1)/(Data!$G$2:$G$201=LOOKUP(CHAR(255),$C$3:C$3))/(Data!$H$2:$H$201=C$4),1)),0) คัดลอกไปขวาและล่าง

ทดลองเบื้องต้นใช้ได้ครับ แต่พอหลายๆข้อมูลโหลดช้าไปหน่อย

[conan885]

สอบถามเพิ่มครับ เรื่องการนับจำนวนแรง
ไปหาข้อมูลมาตาม http://www.snasui.com/viewtopic.php?f=3&t=9439&p=59069
ทำให้นับแรงได้ถูกต้องแล้ว แต่มีบางครั้งที่พนักงานไปส่งงานข้างนอกทั้งวัน ทำให้ช่วงเวลากลางวันไม่ได้สแกน
ค่าที่ได้จึงไม่ถูกต้อง ยกตัวอย่างตามไฟล์แนบ
วันเสาร์ที่ 11 อยากให้ผลลัพท์ช่อง I15 ออกมา=1

พยายามใช้สูตร if เพื่อนับแต่เหมือนจะนับช่องว่างไปด้วยเพราะใส่สูตรไว้
I5=IF(COUNT(C5:F5)=4,((MIN("4:00"+0,D5-C5)+MIN("4:00"+0,F5-E5))/"8:00"),(MIN("8:00"+0,F5-C5)/"8:00"))

ไม่ทราบควรแก้ไขสูตรอย่างไรดีครับ

[snasui]

ตัวอย่างสูตรที่ I5 ครับ

=IF(COUNTIF(C5:F5,">0")=4,((MIN("4:00"+0,D5-C5)+MIN("4:00"+0,F5-E5))/"8:00"),(MIN("8:00"+0,F5-C5)/"8:00"))

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

เซลล์ที่เห็นว่าว่างจริง ๆ คือค่า 0 หากใช้ Count ค่า 0 จะถูกนับมาด้วย ดังนั้นจึงใช้ Countif เพื่อเลือกนับเฉพาะค่าที่มากกว่า 0 แทนครับ