如何用函数实现多余刷卡记录的自动删除

vivid1982 · 发表于 2007-4-30 15:05

如何用函数实现多余刷卡记录的自动删除

UbAVWbGO.rar (1.6 KB, 下载次数: 30)

[此贴子已经被作者于2007-4-30 15:12:48编辑过]

LangQueS · 发表于 2007-4-30 15:08

请楼主上传附件。 <a title="《[新手必读]发帖须知、上传附件、截图动画、压缩解压、靓帖推荐》
作者：hjkla
发表于：2006-8-24 11:44:10
最后发贴：新来的，来报到..." href="http://club.excelhome.net/viewthread.php?tid=184567&px=0" target="_blank">[新手必读]发帖须知、上传附件、截图动画、压缩解压、靓帖推荐</a>

vivid1982 · 发表于 2007-4-30 15:08

原始表<table cellspacing="0" cellpadding="0" width="304" border="0" style="WIDTH: 228pt; BORDER-COLLAPSE: collapse;"><colgroup><col width="176" style="WIDTH: 132pt; mso-width-source: userset; mso-width-alt: 6436;"></col><col span="2" width="64" style="WIDTH: 48pt;"></col></colgroup><tbody><tr height="16" style="HEIGHT: 12pt;"><td width="176" height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 132pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;">2007-04-23 08:08:13</td><td width="64" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">A</td><td width="64" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">财务部</td></tr><tr height="16" style="HEIGHT: 12pt;"><td height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;">2007-04-23 17:36:14</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">A</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">财务部</td></tr><tr height="16" style="HEIGHT: 12pt;"><td height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;">2007-04-24 08:10:11</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">A</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">财务部</td></tr><tr height="16" style="HEIGHT: 12pt;"><td height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;">2007-04-24 17:36:23</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">A</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">财务部</td></tr><tr height="16" style="HEIGHT: 12pt;"><td height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;">2007-04-25 07:42:36</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">A</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">财务部</td></tr><tr height="16" style="HEIGHT: 12pt;"><td height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;"></td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td></tr><tr height="16" style="HEIGHT: 12pt;"><td height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;"></td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td></tr><tr height="16" style="HEIGHT: 12pt;"><td height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;">2007-04-23 08:08:35</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">B</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">hr</td></tr><tr height="16" style="HEIGHT: 12pt;"><td height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;">2007-04-23 08:08:36</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">B</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">hr</td></tr><tr height="16" style="HEIGHT: 12pt;"><td height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;">2007-04-24 08:10:11</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">B</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">hr</td></tr><tr height="16" style="HEIGHT: 12pt;"><td height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;">2007-04-24 17:36:23</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">B</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">hr</td></tr><tr height="16" style="HEIGHT: 12pt;"><td height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;">2007-04-25 07:42:36</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">B</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">hr</td></tr></tbody></table>目标表<table cellspacing="0" cellpadding="0" width="304" border="0" style="WIDTH: 228pt; BORDER-COLLAPSE: collapse;"><colgroup><col width="176" style="WIDTH: 132pt; mso-width-source: userset; mso-width-alt: 6436;"></col><col span="2" width="64" style="WIDTH: 48pt;"></col></colgroup><tbody><tr height="16" style="HEIGHT: 12pt;"><td width="176" height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 132pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;">2007-04-23 08:08:13</td><td width="64" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">A</td><td width="64" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">财务部</td></tr><tr height="16" style="HEIGHT: 12pt;"><td height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;">2007-04-23 17:36:14</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">A</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">财务部</td></tr><tr height="16" style="HEIGHT: 12pt;"><td height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;">2007-04-24 08:10:11</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">A</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">财务部</td></tr><tr height="16" style="HEIGHT: 12pt;"><td height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;">2007-04-24 17:36:23</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">A</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">财务部</td></tr><tr height="16" style="HEIGHT: 12pt;"><td height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;">2007-04-25 07:42:36</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">A</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">财务部</td></tr><tr height="16" style="HEIGHT: 12pt;"><td height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;"></td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td></tr><tr height="16" style="HEIGHT: 12pt;"><td height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;"></td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td></tr><tr height="16" style="HEIGHT: 12pt;"><td height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;">2007-04-23 08:08:35</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">B</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">hr</td></tr><tr height="16" style="HEIGHT: 12pt;"><td height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;"></td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td></tr><tr height="16" style="HEIGHT: 12pt;"><td height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;">2007-04-24 08:10:11</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">B</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">hr</td></tr><tr height="16" style="HEIGHT: 12pt;"><td height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;">2007-04-24 17:36:23</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">B</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">hr</td></tr><tr height="16" style="HEIGHT: 12pt;"><td height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;">2007-04-25 07:42:36</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">B</td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">hr</td></tr></tbody></table>目标要求：部门人员都很多， 这是下载后排序后的结果， 目标表格要求每天只有上班 一次，下班一次，未打卡时 为空白，例如B员工23号 上班打卡只要第一次的即 8：35那次；B员工4月23号 未打卡，在目标表上显示为空白 。

LangQueS · 发表于 2007-4-30 15:15

员工号在哪里呀……还是上传个Excel附件比较好。

vivid1982 · 发表于 2007-4-30 15:23

打卡没有员工号，谁先打就谁先显示，原始表是按照姓名筛选后的数据，一共有10多个部门，每个部门是5到20人，附近在楼顶

vivid1982 · 发表于 2007-4-30 15:25

以前每次都是人工查找，有没有未打卡或重复打卡的，现在想看看有没有简便的方法。

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