|
|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
我目前使用的版本是2010，应该没有powerquery，多数人没有学VBA，可能也没有时间去学，最多录制下宏。使用VBA也需要一定的时间和精力。因此碰到一个问题我都会尽量尝试在非编程的方式下解决问题，就算需要多步解决，如果这些步骤也都是可以使用填充等方式自动完成，那效率可能也是可以的。当然，录制宏如果能解决，也不失为一种方式，大不了等处理完再将宏删除。或者继续保存为.xlsx就不会保存宏了。
这个问题从一贴出来，我就开始想，现在可能算解决了吧，数据太多，只是检查了几种情况。
这个问题不好做，主要因为数据表不规范，体现打卡存在多种情况，因此就无法使用公式等工作表操作一步解决。打卡有以下情况，上n下m表示上上午打几次卡，下午打几次卡
1、上1下1，王小五，2020-6-20
2、上0下2，王艳，2020-6-15
3、上1下0，王旭奔 2020-6-16
4、上0下1。朱俊秀 2020-6-7
5、上1下2，孙玲 2020-6-10
6、上0下2，卯升梅 2020-6-15
7、上1下3，明也 2020-6-11
8、上2下1，贺申平 2020-6-1
步骤如下：
1、C1：上班打卡，C列目前是文本类型，将C列转换成时间类型，并设置格式：hh:mm:ss，这样可以省下一个函数。
2、D1：下班打卡。
D2：=IF(C2>=0.5,C2,0)，用于将下午时间从打卡时间列分离出来。并填充。因为时间是将1（天）去分的，所以中午12点是0.5。写0是因为等下要用到max的数组公式，参数列表中有空项会出错。复制并原位置粘贴，选择数值，这样就D列没有公式了。
3、E2：=IF(C2<0.5,C2,"")，用于将上午时间从打卡时间分离出来。并填充，复制结果，粘贴到C列>选择数值，如果粘贴后C列不显示时间，设置C列单元格格式为hh:mm:ss。删除E列
以上3步做完后就将上午和下午的时间分开了。
假定数据所在的工作表是sheet1，
4、创建几个名称，这样公式书写会比较简单，检查也比较快。
4.1、tbl_name1：选中A2，使用组合键ctrl+shift+↓，选中除标题外的姓名列，添加名称tbl_name1，在再按确定之前。将引用位置的内容由=Sheet1!$A$2:$A$1958改成=Sheet1!$A2:$A$1958
4.2、tbl_name3：选中A2，使用组合键ctrl+shift+↓，选中除标题外的姓名列，添加名称tbl_name3。
4.3、tbl_date1：选中B2，使用组合键ctrl+shift+↓，选中除标题外的日期列，添加名称tbl_date1，在再按确定之前。将引用位置的内容由=Sheet1!$B$2:$A$1958改成=Sheet1!$B2:$A$1958
4.4、tbl_date3：选中B2，使用组合键ctrl+shift+↓，选中除标题外的日期列，添加名称tbl_date3。
5、F1：计数1，G1：计数2。
6、F2：=COUNTIFS(tbl_name1,A2,tbl_date1,B2)，这是返回活动从公式所在行开始姓名（如王小五）在日期（2020-6-20）的记录数，公式在F2时是2，因为王小五从A2，B2开始6月20日，打了2次卡。填充。根据引用的规则，当公式在F3时，就只剩下1了，因为因为王小五从A3，B3开始6月20日，打了1次卡。
7、G2：=COUNTIFS(tbl_name3,A2,tbl_date3,B2)，总是返回公式所在行的用户在同行日期的打卡数，所在G2和G3一样，都是2。
8、E1：下班打卡1。E2：=IF(AND(F2=G2,G2>1),MAX(OFFSET(A2,0,3,F2)),IF(AND(G2=1,C2=""),D2,""))，这是一个数组公式，输入结束后要按ctrl+shift+回车。简单解释下这个公式，如果同一日存在多条打卡记录，则下班打卡取最晚一次。如果只有一条打卡记录（只打上班，或只打下班，即G2=1），如果有上班卡时间就不填下班卡时间，否则就填写下班卡时间。正式使用中，如果中间步骤F和G要取消，可以用F2和G2的公式替换E2中所有引用F2和G2的内容，这样G和F列就可以删除了。
9、将A到E的数据部分复制到新表，使用粘贴数值和格式选项，这样就没有公式了。或者原位置粘贴也行，也选择仅粘贴数值。删除D列（下班打卡），启用简单筛选。
10、同时筛选C和D为空的行
11、将筛选出来的行，整行删除。选中使用ctrl+-快捷键
12、取消C和D的筛选
|
评分
-
1
查看全部评分
-
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2020-6-23 11:23
楼主
