Excel文本数据的处理方法和技巧
在Excel中文本数据是工作表数据的重要组成元素之一,每一张Excel工作表中的数据既有文本又有数值,因此了解和掌握Excel文本数据的处理方法和技巧是非常重要的。
Excel提供了27个文本函数。在这些文本函数中。最常用的是从字符串中取字符的几个文本函数:LEN函数、LEFT函数、RIGHT函数、MID函数以及FIND函数。
LEN函数用于获取文本字符串中的字符数。
LEFT函数用于获取字符串左边指定个数的字符数。
RlGHT函数用于获取字符串右边指定个数的字符数。
MlD函数用于获取字符串从指定位置开始指定个数的字符数。
FIND函数用于在区分大小写的情况下。查找某字符在字符串中第一次出现的位置。
下面举倒说明这几个函数的使用方法。
图1所示是从数据库导入的员工联系地址信息,员工姓名、邮政编码和地址之间用符号“|”分隔。现在要求把这3项数据分开呈3列保存。
图1
这个问题最简单的解决方法是使用“分列”工具。不过。这里使用相关的文本函投进行分列。
首先设计表格。如图2所示。
图2
在单元格B2中输入公式“=LEFT(A2.FlND("|",A2)-1)”。并向下复制。得到员工姓名。
在单元格C2中输入公式“=MID(A2.LEN(B2)+2.6)”。并向下复制。得到邮政编码。
在单元格D2中输入公式“=MlD(A2.LEN(B2)+9.99)”。并向下复制,得到地址。
注意,这里要先用FlND函数确定符号“|”第一次出现的位置,然后利用LEFT函数把该符号左边的字符取出来,就是姓名。
邮政编码的开始位置是姓名字符个数加上2(因为符号“|”占用一个位置)。因此提取邮政编码要使用MID函数。其起始位置是表达式LEN(B2)+2的结果。而字符长度是6。
地址的开始位置是姓名字符数加上9(因为邮政编码宇符数是6.还有两个符号“|”)。因此提取地址也要使用MID函数,其起始位置是表达式LEN(B2)+9的结果。而字符长度是后面所有的字符,这里设置了一个充分大的数字99.因为很少有地址的字符数超过99位的。如果仍不放心。也可以把这个数改为999甚至更大的数宇。
可以利用有关的文本函数。从身份证号码提取信息。图3所示是根据员工身份证号码提取有关信息的表格。在这个表格中。把出生日期分成3列分别保存年、月、日3个数字。这样做一方面可以简化公式。另一方面也便于数据分析,例如可以筛选某年、某月、某日出生的员工。
图3
在单元格E3中输入公式“=1*IF(LEN(D3)=15.19&M1D(D3.7.2).MID(、"D3.7.4))”,并向下复制。得到出生年份数字。
在单元格F3中输入公式“=1*IF(LEN(D3)=15.MID(D3.9.2).MID(D3.11.2))”。并向下复制。得到出生月份数字。
在单元格G3中输入公式“=1*IF(LEN(D3)=15.MlD(D3.11.2).MID(D3.13.2))”。并向下复制。得到出生日数宇。
在单元格H3中输入公式“=DATE(E3,F3.G3)”,并向下复制。将出生年、月、日3个数字合并为一个真正的出生日期。
在单元格13中输八公式“=lF(ISEVEN(IF(LEN(D3)=15.RIGHT(D3)。MID(D3.17.1))),“女”。“男”)”。并向下复制,以判断性别。注意,判断性别时。对于15位身份证号码。是根据量后一位数字判断的;而对于18位身份证号码。则是根据倒数第二位数字(也就是第17位数字)判断的。
图4所示是一个会计科目分录表。要计算净利润。一般会使用下面的公式:
=C2+C7+C8+C9+C10+C11+C22+C23+C24+C70+C110+C116+C117+C118
图4
这个公式最大的缺点是要一个单元格一个单元格地相加。很容易加错单元格。造成计算结果错误。
考虑到计算净利润时。仅仅计算总账科目。而总账科目的科目编码只有4位数。这样就可以利用LEN函数进行判断。并构建相应的高效计算公式了。计算公式如下:
=SUMPRODUCT((LEN(A2:All8)=4)*C2:Cll8)
了解和掌握了Excel文本数据的处理方法和技巧以后,我们就再也不用担心加错单元格的问题了。今天我们学习了5个文本处理函数和列举了2个例子,大家应该好好熟悉一下。
数据分析咨询请扫描二维码
CDA数据分析师在中国航信高科技产业园进行了面向测试度量的数据分析培训课程,培训人数近2 ...
2024-05-01CDA数据分析师走进深圳迈瑞生物医疗电子股份有限公司,在迈瑞总部展开了为期两天的培训,本次课程参训人员线上及线下近百人, ...
2024-05-01CDA数据分析师在合肥市对合肥阳光新能源科技有限公司开展了为期8天的企业内训。 合肥阳光新能源科技 ...
2024-05-01CDA数据分析师走进海尔大学,进行了《数据治理与数据中台建设的道与术》专题培训,培训现场爆满,近百人参加了此次培训。 ...
2024-05-01在中国银行苏州分行培训中心开始数据分析师培训,此次培训课程共10天内容,包括Excel、MySQL、概率论与数理统计、SPSS等内容, ...
2024-05-01从实际的业务需求出发,结合行业的典型应用特点,围绕实际的商业问题,探讨数据挖掘、机器学习模型在金融领域的应用,包括获客、信用评分、细分画像、交叉销售、反欺诈、违规识别、时序预测、运筹优化、流程挖掘九个方面,形成 ...
2024-05-01本次培训课程为线上+线下的模式,由于学员编程能力不一、部分学员没有编程基础,故提供统计学、python基 ...
2024-05-01华夏银行信用卡中心-机器学习培训 1、课程亮点 取材于业界一流企业和顶级咨询公司的行业实践;已经被证明是人人 ...
2024-05-01主 题:数据中台建设及数据分析应用主题分享 1. 数据中台市场洞察 2. 主流数据中台产品比较 3. 某企业数据中 ...
2024-05-01围绕“数据驱动”战略,全力打造我行 300 人数字化人才梯队,着力培养数字化管理人才、大数据专业团队 ...
2024-05-01在当今数据驱动的商业环境中,数据分析成为了企业决策的重要依据。通过对大量数据的收集、处理和分析,企业能够更好地理解市场 ...
2024-04-29在人工智能(AI)的世界里,提示词(Prompt)是一种强大的工具,它能够引导AI按照用户的需求产生特定的输出。本文将深入探讨AI ...
2024-04-29CDA立足未来职场,拓展前沿视野——对外经贸大学保险学院举办“三全育人大讲堂”分享行业最新动态。 ...
2024-04-294月2日,CDA数据分析师创始发起人兼协会理事长赵坚毅博士受邀在浙江万里学院举办了一场以“数字化能力在职场中的作用” ...
2024-04-29随机森林(Random Forests)现在机器学习中比较火的一个算法,是一种基于Bagging的集成学习方法,能够很好地处理分类和回归的问 ...
2022-12-23方差分析是数据分析中常用的一种统计分析方法,接下来让我们简单了解一下方差分析的基本思想和原理吧。 方差分析(Analysis ...
2022-12-23来源:关于数据分析与可视化 关于streamlit-aggrid 数据排序 表格样式的调整 数据 ...
2022-08-03作者:麦叔 定义 「把上面晦涩的概念汇成一句话就是:」 ❝ 回调函数就是一个被作为参 ...
2022-08-03现今,高学历人群日益增多,物以稀为贵的高学历光环淡去。无论本科生还是研究生,甚至博士生,求职竞争力都大不如前,就业压力越来越大。
2022-06-01某家企业10个人面试,有9个本科生……如何脱颖而出,除得体的举止和良好的沟通力外,证书成重要筹码,这也是很多人考证的关键所在。
2022-04-14