公司用門禁系統當考勤，資料需要人工比對麻煩，需要幫助

請教各位先進:

如果小弟手邊有份打卡資料需要作比對但是人員出入的時間有非常多重覆時間

而我只要看當天第1次刷的和最後一次刷的如何比對查看??

以下為小弟的excel位置檔謝謝
https://docs.google.com/file/d/0B-xRp_Mu1bOQcG1jSFAzVHByZDg/edit?usp=sharing

1天內可能會有5~8次的進出記錄，所以排序之後還要一個一個比對非常人工，非常費時。

stillfish00

大學生 Rank: 4

帖子: 1018
主題: 15
精華: 0
積分: 1058
點名: 0
作業系統: win7 32bit
軟體版本: Office 2016 64-bit
閱讀權限: 50
性別: 男
來自: 桃園
註冊時間: 2012-5-9
最後登錄: 2022-9-28

5^#

發表於 2013-5-21 20:22 | 只看該作者

本帖最後由 stillfish00 於 2013-5-21 20:25 編輯

還有另一種...從外部資料的Microsoft Query對工作表查詢
不過要稍微懂SQL語法，底下是錄製後修改的VBA 程式

Sub ExcelQuery()
Dim sSQL, driverID As Long

driverID = 1046 'Excel2010:1046 ; Excel2003:790
sSQL = Array("SELECT `Sheet4$`.員工編號, `Sheet4$`.姓名, `Sheet4$`.刷卡卡號, `Sheet4$`.部門, `Sheet4$`.職稱, `Sheet4$`.刷卡日期, " _
         , "Min(`Sheet4$`.刷卡時間) AS 上班時間, Max(`Sheet4$`.刷卡時間) AS 下班時間" _
         , " FROM `" & ThisWorkbook.FullName & "`.`Sheet4$` `Sheet4$`" _
         , " GROUP BY `Sheet4$`.員工編號, `Sheet4$`.姓名, `Sheet4$`.刷卡卡號, `Sheet4$`.部門, `Sheet4$`.職稱, `Sheet4$`.刷卡日期" _
         )

ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
      "ODBC;DSN=Excel Files;DBQ=" & ThisWorkbook.FullName, _
      ";DefaultDir=" & ThisWorkbook.Path, _
      ";DriverId=" & driverID, _
      ";MaxBufferSize=2048;PageTimeout=5;") _
      , Destination:=Range("$A$1")).QueryTable
      .CommandText = Array(sSQL)
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .BackgroundQuery = True
      .RefreshStyle = xlInsertDeleteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .PreserveColumnInfo = True
      .ListObject.DisplayName = "MS_QUERY_連線"
      .Refresh BackgroundQuery:=False
      '不保持連線
      .Delete
End With
End Sub

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2839
主題: 10
精華: 0
積分: 2895
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-12-22

4^#

發表於 2013-5-21 17:08 | 只看該作者

也許可考慮樞紐分析表：
http://www.funp.net/841064

TOP

GBKEE

超級版主 Rank: 8 Rank: 8

帖子: 5923
主題: 13
精華: 1
積分: 5986
點名: 0
作業系統: win10
軟體版本: Office 2010
閱讀權限: 150
性別: 男
來自: 台灣基隆
註冊時間: 2010-5-1
最後登錄: 2022-1-23

2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章 2011兔年全勤獎章

3^#

發表於 2013-5-21 13:40 | 只看該作者

本帖最後由 GBKEE 於 2013-5-21 13:41 編輯

回復 2# mack078

Option Explicit
Sub Ex()
Dim AR(), i As Integer, R As Long, A, D_Max As String, D_Min As String, xlDay As String
xlDay = Format(Date, "YYYYMMDD")
With Sheets("刷卡資料庫") ' *** 請修改為你的工作表名稱 ***
.Range("A:A").AdvancedFilter xlFilterCopy, , .Cells(1, .Columns.Count), True
'進階篩選 : A欄不重複 [員工編號] 於此工作表最右欄
R = .Cells(.Rows.Count, .Columns.Count).End(xlUp).Row '工作表最右欄 : 最後列有資料的列數
If R = 1 Then Exit Sub 'R = 1 : 刷卡資料庫中沒資料
ReDim AR(1 To .Cells(.Rows.Count, .Columns.Count).End(xlUp).Row)
AR(1) = Array("員工編號", "姓名", "刷卡卡號", "部門", "職稱", "刷卡日期", "上班時間", "下班時間")
For i = 2 To .Cells(.Rows.Count, .Columns.Count).End(xlUp).Row
.Range("A1").AutoFilter 7, xlDay '自動篩選 :第7欄(刷卡日期) 準則 =xlDay
.Range("A1").AutoFilter 1, .Cells(i, .Columns.Count) '自動篩選 :第1欄(ID) 準則 =工作表最右欄的I列
R = .[A1].End(xlDown).Row
If R <> .Rows.Count Then '有篩選到資料: 最後一列的列號 <> Rows.Count=檔案最最後一列的列號
D_Min = Application.Min(Sheet1.Range("H:H").SpecialCells(xlCellTypeVisible))
'SpecialCells(xlCellTypeVisible):可見儲存格
D_Max = Application.Max(Sheet1.Range("H:H").SpecialCells(xlCellTypeVisible))
If D_Min = D_Max Then D_Max = " "
A = .Range("A" & R).Resize(1, 8) '篩出的資料最後一列之8欄範圍設為陣列元素
A(1, 6) = xlDay '修改元素的值
A(1, 7) = D_Min
A(1, 8) = D_Max
Else ' xlDay 的刷卡時間沒篩選到資料: = Rows.Count=檔案最最後一列的列號
.Range("A1").AutoFilter 7 '不設定第7欄(刷卡日期)的準則
R = .[A1].End(xlDown).Row
A = .Range("A" & R).Resize(1, 8) '篩出的資料最後一列之8欄範圍設為陣列元素
A(1, 6) = xlDay '修改元素的值
A(1, 7) = ""
A(1, 8) = ""
End If
AR(i) = A '修改元素的值
Next
.AutoFilterMode = False '取消自動篩選 :刷卡資料庫,資料全部顯示.
End With
With Sheets("查詢").[A1] ' *** 請修改為你的工作表名稱 ***
'當日刷卡時間資料,置於另一工作表.[A1]
.CurrentRegion = "" '清除舊有刷卡時間
.Resize(i - 1, UBound(AR) - 1).Value = Application.Transpose(Application.Transpose(AR)) '轉置AR陣列於範圍中
End With
End Sub