如何提取未打卡数据

ptacwang

表格中行标题为日期，列为姓名，如何提取出未打卡记录？如：王一：1、3、10日未打卡，求助

sunya_0529

两个方案，
一个是公式，采用原始的字符串拼接——

1. =A2&"："&IF(B2="未打卡",B$1,"")&IF(C2="未打卡","、"&C$1,"")&IF(D2="未打卡","、"&D$1,"")&IF(E2="未打卡","、"&E$1,"")&IF(F2="未打卡","、"&F$1,"")&IF(G2="未打卡","、"&G$1,"")&IF(H2="未打卡","、"&H$1,"")&IF(I2="未打卡","、"&I$1,"")&IF(J2="未打卡","、"&J$1,"")&IF(K2="未打卡","、"&K$1,"")&IF(L2="未打卡","、"&L$1,"")&IF(M2="未打卡","、"&M$1,"")&IF(N2="未打卡","、"&N$1,"")&IF(O2="未打卡","、"&O$1,"")&IF(P2="未打卡","、"&P$1,"")&IF(Q2="未打卡","、"&Q$1,"")&IF(R2="未打卡","、"&R$1,"")&IF(S2="未打卡","、"&S$1,"")&IF(T2="未打卡","、"&T$1,"")&IF(U2="未打卡","、"&U$1,"")&IF(V2="未打卡","、"&V$1,"")&IF(COUNTIF(B2:V2,"未打卡"),"日未打卡","正常出勤")

复制代码

另一个方案，采用自定义函数——

1. =wdk(A2,B2:V2,$B$1:$V$1)

复制代码

代码如下：

1. Function wdk(r1 As Range, r2 As Range, r3 As Range)
   
2. On Error Resume Next
   
3. Dim a%, i%, j%, arr
   
4. If Not r1 Is Nothing And Not r2 Is Nothing And Not r3 Is Nothing And r2.Cells.Count = r3.Cells.Count Then
   
5.   a = Application.CountIf(r2, "未打卡") '统计“未打卡”次数
   
6.   ReDim arr(a - IIf(a > 0, 1, 0)) '定义未打卡数据存放数组的维度
   
7.   For i = 1 To r2.Cells.Count '循环检测考勤时间区域，记录“未打卡”数据对应的日期，并写入数组arr()
   
8.     If r2.Cells(i) = "未打卡" Then
   
9.       arr(j) = r3.Cells(i)
   
10.       j = j + 1
    
11.     End If
    
12.   Next
    
13.   wdk = r1 & "：" & IIf(j > 0, Join(arr, "、") & "日未打卡", "正常出勤") '合并字符串
    
14. Else
    
15.   wdk = "错误：单元格引用参数不正确！"
    
16. End If
    
17. End Function

复制代码

hsgy

sunya_0529老兄，已经多次看到他们对你的好主意不置可否

ptacwang

多谢啊多谢

hsgy

楼主善良，有始有终