在考勤表里查询缺勤日期

gzlive · 发表于 2011-6-7 10:26

希望在考勤表左侧的出勤打卡记录中查询某人缺勤的日期，并输出到右边标示为蓝色的缺勤日期处

考勤表.rar (5.29 KB, 下载次数: 66)

蓝桥玄霜 · 发表于 2011-6-7 11:15

每个人都是从5月3日开始统计？

gzlive · 发表于 2011-6-7 11:20

5月的工作日是从3号开始的，6月重新列工作日

蓝桥玄霜 · 发表于 2011-6-7 12:21

Sub yy()
Dim i&, j&, Myr&, Myr1%, Arr, Arr1
Dim d, k, t, d1, k1, t1, x$, nm$, rq, col%, n%
Set d = CreateObject("Scripting.Dictionary")
Set d1 = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
Myr = [a65536].End(xlUp).Row
Arr = Range("a2:c" & Myr)
Myr1 = [m65536].End(xlUp).Row
Arr1 = Range("m4:m" & Myr1)
For i = 1 To UBound(Arr)
d(Arr(i, 2)) = d(Arr(i, 2)) + 1
Next
k = d.keys
t = d.items
[n4:iv1000].Clear
[n4].Resize(d.Count, 1) = Application.Transpose(k)
[o4].Resize(d.Count, 1) = Application.Transpose(t)
For i = 0 To UBound(k)
For j = 1 To UBound(Arr1)
x = k(i) & "|" & Format(Arr1(j, 1), "yyyy-m-d")
d1(x) = 0
Next
Next
For i = 1 To UBound(Arr)
x = Arr(i, 2) & "|" & Arr(i, 3)
d1(x) = d1(x) + 1
Next
k1 = d1.keys
t1 = d1.items
For i = 0 To UBound(k1)
nm = Split(k1(i), "|")(0)
rq = Split(k1(i), "|")(1)
Set r1 = [n:n].Find(nm, , , 1)
n = r1.Row - 4
If t(n) <> UBound(Arr1) Then
If t1(i) = 0 Then
col = Cells(r1.Row, "iv").End(xlToLeft).Column + 1
Cells(r1.Row, col) = rq
End If
End If
Next
Application.ScreenUpdating = True
End Sub

复制代码

蓝桥玄霜 · 发表于 2011-6-7 12:25

请见附件。

gzlive · 发表于 2011-6-7 13:43

原帖由 蓝桥玄霜 于 2011-6-7 12:25 发表
请见附件。

收到了，非常不错，谢谢！

zhaogang1960 · 发表于 2011-6-7 16:52

原帖由 蓝桥玄霜 于 2011-6-7 12:21 发表
Sub yy()
Dim i&, j&, Myr&, Myr1%, Arr, Arr1
Dim d, k, t, d1, k1, t1, x$, nm$, rq, col%, n%
Set d = CreateObject("Scripting.Dictionary")
Set d1 = CreateObject("Scripting.Dictionary")
Application.S ...

看了蓝老师的代码没有看懂，我用字典嵌套写了一个，请蓝老师斧正：
Sub Macro1()
Dim arr, brr(), d As Object, ds As Object, dic As Object, k, t, ks, i&, j&, y%, m%, ld%, lc%, n%
Set d = CreateObject("scripting.dictionary")
Set ds = CreateObject("scripting.dictionary")
Set dic = CreateObject("scripting.dictionary")
arr = Range("B2:C" & Range("B65536").End(xlUp).Row)
y = Year(arr(1, 2))
m = Month(arr(1, 2))
ld = Day(DateSerial(y, m + 1, 0))
For i = 1 To UBound(arr)
      If Not d.Exists(arr(i, 1)) Then Set d(arr(i, 1)) = CreateObject("scripting.dictionary")
      d(arr(i, 1))(arr(i, 2)) = ""
      ds(arr(i, 2)) = ""
      dic(arr(i, 1)) = dic(arr(i, 1)) + 1
Next
k = d.keys
t = dic.items
ks = ds.keys
ReDim brr(d.Count - 1, 1 To 28)
For i = 0 To d.Count - 1
      brr(i, 1) = k(i)
      brr(i, 2) = t(i)
      n = 2
      For j = 0 To ds.Count - 1
         If Not d(k(i)).Exists(ks(j)) Then
            n = n + 1
            brr(i, n) = ks(j)
            lc = IIf(n > lc, n, lc)
         End If
      Next
Next
ActiveSheet.UsedRange.Offset(3, 12).ClearContents
[m4].Resize(ds.Count) = Application.Transpose(ds.keys)
[n4].Resize(d.Count, lc) = brr
End Sub

考勤表zg.rar (17.68 KB, 下载次数: 72)

[ 本帖最后由 zhaogang1960 于 2011-6-7 17:04 编辑 ]

蓝色风 · 发表于 2011-6-22 15:14

看看先哦 00

刘永存 · 发表于 2012-7-16 12:51

蓝桥玄霜发表于 2011-6-7 12:25
请见附件。

里面正是我需要的内容，但是公式是怎么用的不明白

刘永存 · 发表于 2012-7-16 12:54

没弄明白，刚入门的我统计考勤好吃力呀，那个公式是怎么编的，能不能给个公式呀

		自动登录	找回密码
密码			免费注册

[求助] 在考勤表里查询缺勤日期