上个月，我收到了一位金融同事的Excel帮助:
同事J:“收到的表单中有隐藏空和双引号”“，Excel中没有显示，透视表汇总不正确。目前，我们只能***到文本文档。双引号" "都可以通过替换函数清除，可以手动逐行删除空单元格，然后粘贴回excel。愚蠢低效。这个问题已经困扰我们好几年了。大神能不能帮我们看看。”
情况分析
原始Excel表显示如下：
原始Excel表格如下所示:
图1 –隐藏字符在原始表格中不可见。
数据透视表报错“值不可用”错误，显示如下：
透视表报告错误“值不可用”，如下所示:
图2 –数据透视表显示错误
原因分析
***粘贴到文本文档里显示如下，发现付款人名称字段里面包含了空格和双引号：
将其粘贴到文本文档中，并显示如下。发现付款人名称字段包含空和双引号:
图3 –***到文本文档以供查看
这才是真正完整的数据，而上图中的空网格和双引号隐藏在Excel中。这种情况其实是“非印刷体”造成的。
再到站长之家里用Unicode编码转换工具验证一下这些字符，直接***单元格数据到左侧的框里，然后点“ASCII转Unicode”,你可以看到这些字符对应的ASCII码。你就知道原来隐藏的其实是制表Tab键和双引号。
去站长家里用Unicode代码转换工具验证这些字符。将单元格数据直接***到左边的框中，然后点击“ASCII到Unicode”。可以看到这些字符对应的ASCII码。你会知道实际上隐藏了Tab键和双引号。
图4 –用站长工具准确确认。
什么是非打印字符？
非打印字符是指计算机中存在某些字符，但无法显示或打印。以ASCII码表为例。0到31之间的ASCII码值是控制字符，不能显示和打印，如回车键。
知道了根源，解决了才能对症下药。有三种解决方案:
用Excel自带的CLEAN函数
函数语法：CLEAN(text)参数说明：text，表示要删除非打印字符的文本。功能说明：删除文本中所有不能打印的字符。 对从其他应用程序导入的文本使用 CLEAN，将删除其中含有的当前操作系统无法打印的字符。 例如，可以使用 CLEAN 删除某些通常出现在数据文件开头和结尾处且无法打印的低级计算机代码。
函数语法:CLEAN(text)参数说明:text，表示要删除非打印字符的文本。描述:删除文本中所有不可打印的字符。对从其他应用程序导入的文本使用CLEAN将删除当前操作系统无法打印的字符。例如，您可以使用CLEAN删除一些低级计算机代码，这些代码通常出现在数据文件的开头和结尾，并且无法打印。
图5 –使用CLEAN()函数进行清理。
处理之后，再***到文本文档里，你就发现空格和双引号都消失了。
处理后***到文本文档中，会发现空大小写和双引号都消失了。
图6 –使用CLEAN()功能确认清洗效果
说到清洗数据，我们自然也要想到Excel里强大的数据清洗插件POWER Query
，用Power Query的“修整”和“清除”，这两种方法也都可以实现。为了方便显示，我们用添加列选项卡里的”格式”来做个对比。选中数据列，分别点格式里的修整和清除。
图7 –使用电源查询进行清洗
如下图显示：
下图显示了:
图8 –使用电源查询的修剪和清洗功能进行清洗。
然后点“关闭并上载”
图9 –清洗后关闭并装载。
加载到清洗后的表格如下：
加载到清洗中的表如下:
图10 –使用超级查询清理数据
然后我们再***到文本文档里确认，成功清除。
图11 –用POWER QUERY检查清洗后的效果。
作业已成功交付。
同事J:“大神，请接受我的膜拜！这三种方式都是可行的。感谢迷茫和学习。”
我解决了困扰他们几年的问题，从此在同事J的部门确立了江湖地位，哈哈。
本文来自转身以后投稿，不代表舒华文档立场，如若转载，请注明出处：https://www.chinashuhua.cn/24/477250.html
微信扫一扫 
