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