แยกเฉพาะเวลารูดบัตรเข้า-ออก จากข้อมูลสแกนนิ้วมือ

[aommyp]

มีวิธีไหนคะ ที่จะสามารถแยกข้อมูลเฉพาะเวลาสแกนนิ้วมือ เข้าครั้งแรก และเวลาสแกนนิ้วมือ ออกครั้งสุดท้าย ใน 1 วันค่ะ
เนื่องจากTime Attendance ดึงข้อมูลมาจากเครื่องสแกนลายนิ้วมือของพนักงานซึ่งจะแสดงการรูดบัตรทุกครั้งที่สแกนเข้าออกประตูออฟฟิศค่ะ

ช่วยด้วยนะคะ แยกเวลาเองแล้วปวดตามาก และช้ามากด้วยค่ะ ขอบคุณล่วงหน้านะคะ

[snasui]

ตัวอย่างสูตรที่เซลล์ F2 ครับ

=IF(B2=MIN(IF($A$2:$A$408=A2,IF($E$2:$E$408=E2,$B$2:$B$408))),"In",IF(B2=MAX(IF($A$2:$A$408=A2,IF($E$2:$E$408=E2,$B$2:$B$408))),"Out",""))

Ctrl+Shift+Enter > Copy ลงด้านล่าง กรุณาอ่าน Note ด้านล่างอย่างละเอียด ไม่เช่นนั้นจะไม่ได้คำตอบครับ

!

Note: Ctrl+Shift+Enter เป็นการสร้างสูตร Array โดยสามารถเลือกดำเนินการตามข้อ 1 หรือ 2 ด้านล่าง กรณีคีย์สูตรเอง เมื่อคีย์สูตรแล้ว แทนที่จะกด Enter ให้กดแป้น Ctrl และ Shift พร้อมกันค้างไว้แล้วกด Enter เพื่อสร้างเป็นสูตร Array กรณี Copy สูตรไปวางให้กดแป้น F2 เพื่อทำการ Edit Cell นั้นก่อน จากนั้นกดแป้น Ctrl และ Shift พร้อมกันค้างไว้แล้วตามด้วย Enter เพื่อสร้างเป็นสูตร Array หากกดแป้นถูกต้องสูตรนั้นจะมีเครื่องหมายปีกกาครอบ เช่น {=YourFormulas(...)} ปีกกานี้ไม่สามารถคีย์เข้าไปเอง จะต้องมาด้วยการกดแป้น Ctrl+Shift+Enter เท่านั้น ถ้ายังไม่เห็นปีกกาครอบสูตรแสดงว่ากดแป้นให้รับสูตรไม่ถูกต้อง การแก้ไขเปลี่ยนแปลงสูตร Array จะต้องกดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter ทุกครั้ง

[menem]

ผมชอบใช้วิธีสร้าง Sheet ขึ้นมาเพิ่ม เพื่อสร้างเงื่อนไขสำหรับการดึงข้อมูล
ตามตัวอย่างในไฟล์ที่แนบมาครับ , แต่ที่สงสัยคือ ทำไม Format ของคอลัมน์ A
ตั้งแต่บรรทัดที่ 361 ถึงต่างกับส่วนก่อนหน้า ?

เพราะอันก่อนหน้า ข้อมูลเป็น Text และแสดงเป็น m/d/yyyy
แต่ตั้งแต่ A361 เป็นต้นไป ข้อมูลเป็น Date และแสดงเป็น dd mm yyyy

[aommyp]

คือความจริงแล้วข้อมูลดิบที่ดึงมาจากเครื่องสแกนนิ้วมือเป็นแบบนี้ค่ะ ข้อมูลมาเป็น Notepad นำมาวางที่ Excel เองค่ะ ข้อมูลจะอยู่ที่ช่อง Columns A คอลัมน์เดียวค่ะ ลำบากในการแบ่งข้อความออกเป็นช่องๆ มากเลยค่ะ

1/23/2016 10:42:51 AM FL2 - Security Room (C3:D4) ACCESS GRANTED - CARD sawas
1/23/2016 10:48:01 AM FL1 - HPC (C1:D6) ACCESS GRANTED - CARD sawas

[snasui]

สามารถใช้เมนู Data > Text to columns ในการแบ่ง หากต้องการคำตอบโดยละเอียดให้แนบไฟล์ Excel ตัวอย่างที่ได้จากการนำข้อมูลมาวางครับ

[menem]

ขอตัวอย่างที่มาแบบช่อง A สำหรับวันที่ 5-15 ครับ

[aommyp]

แนบไฟล์ ตัวอย่างที่ได้มาค่ะ

[aommyp]

แนบไฟล์ ตัวอย่างแล้วค่ะ

[snasui]

ตัวอย่างสูตรในชีต "Timeที่ได้จากเครื่องแสกนนิ้วมือ" ครับ

1. แทรกบรรทัด 2 บรรทัด ข้อมูลจะเลื่อนไปอยู่บรรทัดที่ 3 หากต้องการนำข้อมูลต้นฉบับมาวางก็ให้เริ่มวางที่ A3 ลงไปด้านล่าง
2. D1:F1 คีย์ข้อความ ':, 'ACCESS, '- CARD ตามลำดับ
3. B2:H2 คีย์หัวบรรทัดให้กับข้อมูล ได้แก่ Date, Date Time_Excel, Date_System, Place, Type, Name, In/Out ตามลำดับ
4. D3 คีย์สูตร
   =LEFT($A3,FIND(D$1,$A3)+9)
   Enter > Copy ลงด้านล่าง
5. E3 คีย์สูตร
   =MID($A3,LEN($D3)+2,FIND(E$1,$A3)-LEN($D3)-2)
   Enter > Copy ลงด้านล่าง
6. F3 คีย์สูตร
   =MID($A3,FIND(E$1,$A3),FIND(F$1,$A3)+6-FIND(E$1,$A3))
   Enter > Copy ลงด้านล่าง
7. G3 คีย์สูตร
   =TRIM(MID($A3,FIND(F$1,$A3)+6,255))
   Enter> Copy ลงด้านล่าง
8. C3 คีย์สูตร
   =TEXT(RIGHT(SUBSTITUTE(LEFT(D3,FIND(" ",D3)-1),"/",REPT(" ",5)),4)&LEFT(TEXT(LEFT(SUBSTITUTE(LEFT(D3,FIND(" ",D3)-1),"/",REPT(" ",5)),2),"00"),2)&LEFT(TEXT(MID(SUBSTITUTE(LEFT(D3,FIND(" ",D3)-1),"/",REPT(" ",5)),3,8),"00"),2),"0000\/00\/00")+MID(D3,FIND(" ",D3)+1,255)
   Enter > Copy ลงด้านล่าง
9. B3 คีย์สูตร
   =INT(C3)
   Enter > Copy ลงด้านล่าง
10. H3 คีย์สูตร
    =IF(C3=MIN(IF($B$3:$B$408=B3,IF($G$3:$G$408=G3,$C$3:$C$408))),"In",IF(C3=MAX(IF($B$3:$B$408=B3,IF($G$3:$G$408=G3,$C$3:$C$408))),"Out",""))
    Ctrl+Shift+Enter > Copy ลงด้านล่าง
11. กรองข้อมูลโดยใช้คอลัมน์ H เป็นหลักเพื่อนำไปใช้งานตามต้องการ

อย่าลืมปรับช่วงข้อมูลในสูตรด้านบนให้ตรงกับการใช้งานจริง

[menem]

ผมเห็นว่ามีการเริ่มงาน หลังเที่ยงคืนด้วย ถูกต้องไหมครับ ?
3/1/2017 0:04:xx

[aommyp]

ใช่ค่ะ พนักงานทำงานเป็นกะด้วยค่ะ

[aommyp]

ขอบคุณมากค่ะ จะลองนำไปทำนะคะ

[aommyp]

ทำได้สำเร็จแล้วค่ะ ขอบคุณมากๆๆ นะคะ ช่วยได้เยอะมากๆๆๆ ค่ะ ^___^