excel部分字段相同模糊匹配
一、提出问题
你要么获取一批数据,然后根据它提问,或者先提问,然后根据问题收集数据。在这两种情况下,好的问题可以帮助你将精力集中在数据的相关部分,并帮助你得出有洞察力的分析。
二、理解数据
1、理解各字段的意思,如果有英文可修改成中文更易理解。
2、在数据清洗前复制一份保存,将CSV文件另存为xlsx类型保存。
3、Excel有四种数据了类型:
(1)文本型:中、英文、混合文本、符号和字符串形成存储的数值(123)
(2)数值型:数值、科学计数法、时间、日期和货币.....
(3)逻辑性:TRUE和FALSE
(4)错误值:#NAME?、#N/A、#DIV/0、#REF!、#VALUE!、#NUM!、#NULL! 三、数据清洗 1.选择子集
不需要的列可以隐藏,不要删除。需要用到隐藏子集的操作:格式--隐藏和取消隐藏 或者选择取消隐藏的行/列,右键点取消隐藏。 2.列名重命名
双击列名字段进行修改。 3.删除重复数据项
操作:数据---删除重复项---取消全选---选择需要检查的列---确定。选择数据集中的唯一编号列。 4.缺失值处理
单击唯一编号列,右下角查看该数据集的总行数。依次点击其他列。缺失值数据 = 唯一编号列总数 - 其他列总数。 缺失值处理的4种方法
,根据情况灵活使用:
(1)通过人工手动补全:缺失值很少的情况下。
定位缺失值操作:开始---查找和选择---定位条件---空值---输入填充的值---同时按住Ctrl+Enter,填充到其他空白单元格。
(2)删除缺失的数据 :字段缺失数据超过50%,缺失过多就没有意义,考虑删除。
(3)用平均值代替缺失值:计算该列的平均值代替。
(4)用统计模型计算出的值去代替缺失值。 5.一致化处理
(1)分列一致化:列中有多个标签(企业服务,数据服务),需要分列处理,分列功能会覆盖掉右列单元格,所以我们先要复制这一列到最后一个空白列的地方,再进行分列操作。
分列操作:选中该列---数据---分列---分隔符号---下一步---取消Tab键,选择其他(本例中用逗号,)---下一步---确定。
(2)插入几个数据清洗常用函数:
①筛选列数据信息:IF(COUNT(FIND({关键字1,关键字2},单元格)),"是","否") 筛选单元格中的关键字,有则显示是,没有则显示否。
②平均数AVERAGE(A1:A3),
③查找函数Find(要查找的字符串,字符串所在单元格位置),
④数据抽取Left/Right(字符串所在单元格位置,从左/右开始到XX位置进行截取,从第几个位置开始截取[默认为1]),Mid(字符串所在单元格位置,开始位置,截取长度),
⑤统计单元格长度函数Len(A1)。
(3)错误值处理
用复制粘贴为数值将以字符串形式存储的数字转化为纯数值。
使用函数查找字符串要注意字符串的大小写,不匹配会报错。
善于利用查找替换功能,去除多余的字或转换大小写。“以上”--->“”;k --> K 6.数据排序
排序操作:开始---排序和降序---降序/自定义排序---扩展选定区域。 7.异常值处理
过大或过小,在实际中不可能存在的数据。如年龄:120岁,-1岁,薪资:1000万.... 四、构建模型
1.数据透视表:Ctrl+A选择整个数据表,插入---数据透视表。
(1)数据透视表有四个区域:筛选器,列,行,值。
(2)数据透视表的几个常用功能:
①将所需字段拖入透视表的区域,筛选器和值区域可同时拖入多个字段。
②值字段设置:值---右键---值字段设置---总和/计数/
③排序:单击行标签/列标签---右键---升序/降序。
④值汇总方式:单击值标签---值汇总依据---求和/计数/平均值/最大值/最小值....
⑤值显示方式:单击值标签---值显示方式---总计的百分比/列汇总的百分比/行汇总的百分比...
2.分析工具库:数据分析
安装数据分析功能:文件---选项---excel加载表>转到---分析工具库。
如何应用?数据---数据分析---描述统计,接着操作如下:
![900a1bdc5af572d05086c6beb26e2cbf.png](https://img-
blog.csdnimg.cn/img_convert/900a1bdc5af572d05086c6beb26e2cbf.png)
3.多表关联查询:vlookup函数
(1)精确查找和近似查找(模糊查找)的区别。
①精确查找是指从第一行开始往最后一行逐个查找。一找到匹配项就停止查询,所以返回找到的第一个值。
②当你要近似查找的时候,它就会苦逼地查遍所有的数据,返回的是最后一个匹配到的值。
(2)在使用vlookup函数时,在很多情况下使用的是精确匹配,而在进行分组时需要用模糊匹配,所以这里要输入“1”来进行模糊匹配。分组时要注意三要素:阈值,分组名称,区域范围。
(3)Excel设置了快捷键F4帮助用户迅速切换相对引用、绝对引用和混合引用。步骤如下:
①选定包含该公式的单元格;
②在编辑栏中选择要更改的公式内容,并按 F4 键;
③以引用单元格A1为例,每次按 F4 键时,Excel会依次在以下组合间切换: 按一次F4是绝对引用 按两次、三次F4是混合引用 按四次F4是相对引用。
(4)使用这个函数过程中,如果出现错误标识“#N/A”,一般是3个原因导致:
①第2个参数:查找范围里第一列的值必须是要查找的值。 比如这个案例里第2个参数选定的的范围里第一列是姓名,是要查找值的列。
②数据存在空格,此时可以嵌套使用TRIM函数将空格批量删除。
③数据类型或格式不一致,此时将数据类型或格式转为一致即可。 六、得出结论
根据数据透视表的数据分析得出结论。