行政部日常统计汇总数据的时候,总会遇到一些意料之外的事。例如,某员工提交了多次表格造成数据重复,如果检查不出来,那么就会为后续的数据处理会带来很大的麻烦。所以,我们控制了数据的有效性,还得控制数据的唯一性。 前一篇“学习Excel数据有效性:小技巧大作为,提升数据汇总效率”发布后,获得了众多值友认可。有值友提出了“合并表格时,缺少检查数据的唯一性。”,我觉得这也是办公数据处理必须要面对的现实。有条件的公司,可以自己搭建网络平台,通过后台数据库技术确保数据的准确可靠唯一;不涉敏信息也可以借助在线表单表格来便捷处理;当然,还有一些诸多需要依靠离线方式处理数据的工作场景,所以本篇就这类工作场景围绕数据唯一性展开。 方法1:条件格式场景1:表格发放前设置通过”条件格式“检查录入数据的唯一性,达到数据准确规范。这种情况不适合单人填表提交,因为填表的时候就一条数据。只有单表多条数据录入的时候,适用! ▲运用“条件格式”中的“重复值”,当录入相同数据的时候,用颜色进行标识提醒。 ▲Excel 2016中的设置演示。当录入已经存在的工号1101的时候,就出现颜色提示,此时录入者应该能意识到录入错误,需检查核实。 场景2:回收表格后检查(1)适合处理数据少的场景,如下图举例,数据回收后可以明显看到数据有重复,可以继续采用上述的条件格式。(数据量大的时候,更不容易检查) ▲工号1101,重复了4条。 ▲运用“条件格式”中的“重复值”,重复工号可以颜色提示 ▲工号排序后,删除重复多余数据,达到“工号”数据的唯一性目的。 方法2:删除重复数据项场景3:回收表格后检查(2)如果数据相对场景2来说比较多,且重复概率高,那么可以采用“删除重复数据”。 ▲选定数据区域,在数据栏点击上图红色框选的“删除重复项”,在对话框中选择“工号”并确认 ▲对话框提示发现了3个重复项,保留了9个唯一值,达到“工号”数据的唯一性目的。 方法3:Power query场景4:多文件回收处理更新数据上述的“方法1:条件格式”和“方法2:删除重复数据项”仅能处理完全相同的重复数据, (1)覆盖源文件,刷新数据 这种情况一般是员工提交最新数据表后(未改文件名),覆盖原来的表文件即可。这属于数据内容更新,一般不会发生数据唯一性冲突。 ▲员工李沧海,性别填写错误,修改后源文件保存后发给了办公室,办公室行政人员覆盖了其之前提交的文件。 ▲接下来只要打开保存过查询的工作簿,在数据选项卡点击“全部刷新”或者选中需要刷新的单元格记录行后点击“刷新”。 ▲操作GIF动态图,请注意刷新后性别的变化。 (2)保留源文件,查询合并为新查询 如果员工有数据更新后重复提交(且修改了文件名),此刻作为唯一性标识的工号可能会导致重复,但是其它数据项是最新的,我们肯定需要留下其最新提交数据,该如何操作?在上篇“学习Excel数据有效性:小技巧大作为,提升数据汇总效率”最后的提到方法中进一步实现。 ▲假设同事张然宝提交了2次或多次文件。(实际工作场景中可能有很多人因为种种原因数据更新而重复提交,这种情况可能无法及时通过删除旧文件,保留最新文件) ▲在Power query编辑器中,红色框内的时间是关键,我们需要使用Date Modified。
Date modified和Date accessed两个属性都可以记录文件最后的保存时间,我们选择其一作为最新文件提交时间判断值,后续再综合运用条件格式即可。 ▲新建查询后,在文件夹路径对话框中,点击下面“合并”按钮下来中的“合并和编辑”选项。 ▲选择工号,剔除空值,同时注意该查询的名称是“基本信息”。(这里俺不做更改了) ▲红框内可以选择“新建源”或者直接使用“最近使用的源”。 ▲注意修改查询属性值“文件信息”,同时删除不必要的属性列,保留文件名和文件修改时间属性。 ▲选择“基本信息”查询后,点击合并查询,并且选择“将查询合并为新查询” ▲合并对话框内,将先前得到的两个查询进行合并,注意设置联接字段。(红色箭头) ▲选择新查询Merge1(可以自己改名)。 ▲展开该查询列项,选择“Date modified”并确定,得到文件修改时间(此内容略)。 ▲最后“关闭并上载”,系统自动返回工作簿。 ▲通过Power Query得到的查询均会在工作簿中生成Sheet工作表,在上图中我们可以通过条件格式,看到20180302工号数据有重复且有变化,最后通过Date modified判断取舍,得到最新值。▲在Power query编辑器中的完整操作示例。(录屏软件ScreenToGif运行崩溃,上图采用LICEcap软件录制) 技术和知识都是不断更新的,本文介绍技术属于入门型,旨在帮助无编程技能的办公人员,提升他们的数据处理能力,高手可以忽略。 Excel不是万能的,属于轻量级数据库处理工具,可以解决常规OFFICE中的实际问题;如果有数万条数据记录或者更高,想要更好更高效的数据处理,那么微软和IBM等公司的数据库软件就很值得学习应用。 OK,欢迎点赞和收藏,文中若有欠缺之处,请指正,谢谢!!! |
|