下载掌阅APP，畅读海量书库
立即打开
虽然我们一再强调数据规范的重要性，但实际统计分析中，由于数据来源不同，数据表中难免存在一些不规范的数据，比如不规范的文本、日期、数值等。因此掌握一些处理不规范数据的技巧是有必要的。
有时拿到的数据表会出现众多的文本型数字，从而导致无法进行正确的计算与分析。例如，在如图2-42所示的数据表中，由于各组成绩都是文本型数字，因此无法进行最大值、最小值、平均分及协方差的计算。下面来对文本数字进行批量转换。
图2-42
1
选中A3:D18单元格区域，然后单击右上角的
按钮，在弹出的菜单中选择
“转换为数字”
命令，如图2-43所示。
2 完成上面操作后，即可将文本型数据转换为数字，并且公式自动返回正确的运算结果，如图2-44所示。
图2-43
图2-44
强制换行与自动换行不一样，它是用户按Alt+Enter组合键产生的换行。在如图2-45所示的表格中，在B9单元格输入公式计算总销量时，得到的结果是不正确的，这是因为B4和B5单元格中含有强制换行符，无法被Excel识别为数据，无法进行求和运算。
图2-45
在数据处理中，为了能够一次性处理所有数据并且没有遗漏，可以通过查找替换功能，一次性删除表格中的所有换行符。
1 按Ctrl+H组合键打开 “查找和替换” 对话框，在 “查找内容” 文本框中按Ctrl+J组合键，如图2-46所示。
图2-46
2 单击 “全部替换” 按钮，弹出提示框，提示有多少处换行符已被替换，如图2-47所示。
图2-47
3 单击 “确定” 按钮，即可删除全部的换行符。本例中删除换行符后，数据即可正常计算，如图2-48所示。
图2-48
输入日期数据或通过其他途径导入数据时，经常会产生文本型日期。不规范的日期会导致数据无法计算和无法汇总统计。
输入日期型数据时，不能输入 “20170325”“2017.3.25”“17.3.25” 等不规范的格式，否则在后期数据处理时会出现无法运算、运算错误的现象。如图2-49所示，要根据员工的入职时间来计算工龄，同时还要计算工龄工资，由于当前的入职日期不是程序能识别的日期格式，导致了后面的公式计算错误。此表在进行筛选时，也不能按日期筛选，如图2-50所示。
图2-49
图2-50
在Excel中，必须按指定的格式输入日期，才会被当作日期型数值，否则会视为不可计算的文本。输入以下4种日期格式的日期，Excel均可识别。
· 短横线 “-” 分隔的日期，如 “2020-4-1”“2020-5” 。
· 用斜杠 “/” 分隔的日期，如 “2020/4/1”“2020/5” 。
· 使用中文年月日输入的日期，如 “2020年4月1日”“2020年5月” 。
· 使用包含英文月份或英文月份缩写输入的日期，如 “April-1”“May-17” 。
用其他符号间隔的日期或数字形式输入的日期，如 “2020.4.1”“20200401” 等，Execl都将其视为文本数据。对于这种不规则的文本日期，可以利用分列功能将其转换为标准日期。
当遇到不规范的日期时，我们可以使用Excel中的 “分列” 功能，将非标准日期格式转换为标准日期格式。
1 选中目标单元格区域，在 “数据” 选项卡的 “数据工具” 组中单击 “分列” 按钮，如图2-51所示，打开 “文本分列向导-第1步，共3步” 对话框，如图2-52所示。
图2-51
图2-52
2 保持默认选项，依次单击 “下一步” 按钮，进入 “文本分列向导-第3步，共3步” 对话框，在 “列数据格式” 栏中选中 “日期” ，如图2-53所示。
3 单击 “完成” 按钮，即可把所有文本日期转换为规范的标准日期。转换后在如图2-54所示中可以看到之前无法计算的 “工龄” 与 “工龄工资” 都正确计算出来了。再单击 “入职日期” 列标识右侧下拉按钮，则能出现对日期数据分组统计的筛选选项（标准的日期会自动进行日期分组，牵涉多月的按月分组，牵涉多年的按年分组），如图2-55所示。
图2-53
图2-54
图2-55
像20200812这种格式的日期，也可以按相同的方法，运用分列功能一次性转换为日期。
不标准的日期格式多种多样，针对不同情况，需要进行不同处理。例如，针对如图2-56所示的不规则日期，也可以利用 “分列” 功能转换为标准日期，其操作如下。
图2-56
1 选中目标区域，依次进入到 “文本分列向导-第2步，共3步” 对话框中，在 “分隔符号” 栏中选中 “其他” ，并设置符号为 “(” （如图2-57所示），单击 “完成” 按钮；接着再次选中目标区域，进入到 “文本分列向导-第2步，共3步” 对话框中，在 “分隔符号” 栏中选中 “其他” ，并设置符号为 “)” （如图2-58所示），单击 “完成” 按钮。
图2-57
图2-58
2 完成设置后即可实现变向删除左括号与右括号，修正了不规则的日期。
有些文本中会存在一些不显眼的空格，这些数据如果只是用来显示查看，并不会造成什么影响，但如果用于数据统计分析，往往会导致结果出错。如图2-59所示的表格中，在使用数据透视表统计数据时，出现了 “哑铃” 和 “哑铃” 两个统计项，原因就是空格导致程序默认这是两个数据项。
图2-59
文本中存在的多余空格，有时肉眼很难发现，可以使用查找替换的方法一次性进行处理。
1 按Ctrl+H组合键，打开 “查找和替换” 对话框，光标定位到 “查找内容” 文本框中，按一次空格键， “替换为” 内容栏中保持空白，如图2-60所示。
图2-60
2 单击 “全部替换” 按钮，即可实现批量且毫无遗漏地删除多余空格，恢复正确的统计结果，效果如图2-61所示。
图2-61
除了上述方法外，还可以借助Word软件删除不可见字符。将Excel表格中的目标数据复制下来，然后粘贴到Word文档中（可以建立一个空白文档），再在Word文档中选中数据并复制、粘贴回Excel表格中，即可整理成标准的数字格式。