第1章 数据的获取
使用Excel管理数据的主要目的是高效、便捷地完成数据分析工业，但前提是所分析的数据必须是规范的。在工作中，我们经常会从不同来源、不同渠道获取数据，但有时获取的数据由于不规范，或多或少都存在问题。例如，从各种软件、数据库、网页等导出的数据不能直接用于求和计算，或者数据首尾有多余的空字符，或者导出的文本内容与解决方案不匹配；收到的数据报表格式不统一，汇总后无法进行图表、公式或数据透视表等操作。本章主要介绍规范数据的方法和规范数据表的样式，帮助用户提高Excel应用的基本技能，轻松建立适合自己实际工作需要的数据及表格，为进一步的数据处理和分析创造便利条件。
技巧001 批量导入文本数据
应用场景
很多软件导出的数据多为文本格式，即扩展名为TXT的文本文件。将文本格式的数据（以下简称文本数据）批量导入Excel有两种方法，一种是利用系统自带的获取外部数据功能；另一种就比较简单粗暴，即直接复制、粘贴。在导入文本数据时，对于一些特殊的数据，如一条有15位以上长数字的文本数据，Excel会将超出15位的数字自动置零。
解决方案
1．常规文本数据的导入
（1）现将图1-1所示的文本数据批量导入Excel。可在一个空白的Excel工作表中，单击【数据】选项卡下【获取外部数据】组中的【自文本】按钮，如图1-2所示。
图1-1
（2）弹出【导入文本文件】对话框，从中找到文本文件的存放位置，选中该文件后，单击【导入】按钮，如图1-3所示。
图1-2
图1-3
（3）弹出【文本导入向导-第1步，共3步】对话框。在该对话框下方的【预览文件】区域中可以预览即将导入数据的效果。如果该区域中显示的内容为乱码，可以通过选择【文件源始格式】中的【936：简体中文GB2312】选项获得正确的显示结果，单击【下一步】按钮，如图1-4所示。
（4）弹出【文本导入向导-第2步、共3步】对话框中，选择分列数据所用的分隔符号，系统默认使用的分隔符号为【Tab键】。观察【数据预览】区域中数据的分隔情况是否正确，如果显示的结果是把不相同的内容归入同一列中，可以通过选择其他分隔符号尝试解决。本案例无此问题，故保持系统默认的选择，直接单击【下一步】按钮，如图1-5所示。
图1-4
图1-5
（5）弹出【文本导入向导-第3步，共3步】对话框，在该对话框中可以设置即将导入的数据列的格式。例如，对日期类的数据选择日期格式等。操作方法：在【数据预览】区域中选中该列，在对话框的左上方选择格式类型。如果无须设置数据格式，可以直接单击【完成】按钮，如图1-6所示。
（6）在弹出的【导入数据】对话框中选择导入数据的存放位置，系统默认的是当前工作表，也可以选择新建一个工作表。如果无须新建工作表，可直接单击【确定】按钮，如图1-7所示。
图1-6
图1-7
至此，导入文本格式的数据操作全部完成，导入的数据样式效果如图1-8所示。
2．15位以上长数字的文本数据导入
除了常规的文本数据外，实际工作中我们还可能遇到一些特殊的数据，如银行卡号或身份证号码等长数字，如图1-9所示。银行卡号的长度一般是16位，身份证号的长度是18位。这时如果还按照上述案例中的常规方式导入，会出现图1-10所示的结果。
图1-8
图1-9
这是因为Excel默认只保留15位数字，超过15位的部分会自动置零。选中C2单元格，在编辑栏内可以看到超过15位数的数字被系统自动置零了，如图1-11所示。
图1-10
图1-11
为了避免发生这种情况发生，在导入数据时需指定身份证号码列按文本格式导入。导入过程可按照上述案例的操作步骤进行，不同之处在【文本导入向导-第3步，共3步】对话框中，选中银行卡号列，设置【列数据格式】为【文本】，如图1-12所示。身份证号列同样也需要设置成文本格式。
将银行卡号和身份证号设置为文本格式后完成后续的操作步骤，获得导入数据的效果如图1-13所示。
图1-12
图1-13
拓展应用 使用Power Query工具导入文本数据
除了使用上述方式导入文本数据外，还可以借助Power Query工具导入。这里仍以导入图1-9所示的文本数据为例进行说明。
（1）在一个空白工作表中，选中A1单元格，单击【数据】选项卡，在【获取和转换】组中单击【新建查询】下拉按钮
，在弹出的下拉列表中选择【合并查询】→【启动Power Query 编辑器】选项，如图1-14所示。
（2）弹出【Power Query 编辑器】窗口，单击【新建源】下拉按钮，在弹出的下拉列表中选择【文件】→【文本/CSV】选项，如图1-15所示。
图1-14
图1-15
（3）在弹出的【导入数据】对话框中选中需要导入的文本文件“工资发放表”，单击【导入】按钮，如图1-16所示。
（4）弹出一个过渡窗口，如图1-17所示，单击【确定】按钮，将文本数据导入到Power Query 编辑器中。
图1-16
图1-17
（5）在Power Query 编辑器中可以看到，导入的数据有4列，分别是姓名、银行卡号、身份证号和工资，其中银行卡号和身份证号系统默认为数字格式且以科学计数法的形式显示，如图1-18所示，需要将二者转换为文本格式。选中【银行卡号】列，单击鼠标右键，在弹出的右键菜单中选择【更改类型】→【文本】命令，如图1-19所示。
图1-18
图1-19
（6）弹出【更改列类型】对话框，单击【替换当前转换】按钮，如图1-20所示。
（7）通过上述列格式转换后，银行卡号即被转换为文本格式，如图1-21所示。按照上述方法将身份证号也转换为文本格式，效果如图1-22所示。
图1-20
图1-21
（8）单击功能区最左侧的【关闭并上载】下拉按钮，在弹出的列表中选择【关闭并上载】命令，如图1-23所示。
图1-22
图1-23
（9）系统自动关闭Power Query 编辑器，并将转换好的数据导入Excel表格中，如图1-24所示。
至此，通过Power Query 编辑器导入文本数据，并将超过15位数的银行卡号和身份证号转换为文本格式的操作全部完成。
Power Query 编辑器作为一款新增的数据加工处理插件，由于其操作步骤可视化、可修改、可复用，极大地简化了工作时间，一经面世就受到了大众的青睐。而且相比于Excel函数，其功能更强大，可轻松处理大量数据，告别卡顿。
图1-24
3．选择性地导入文本数据
在导入一组有很多列的文本格式的数据时，若使用者只希望导入其中的几列数据，其他的列最好能在导入环节就被排除在外。这种选择性地导入文本数据的操作可参考技巧001中的“常规文本数据的导入”，不同之处在于第5步骤中，选中不需要的列，再选择【不导入此列（跳过）】单选按钮，在【数据预览】区域中可以看到系统提示“忽略列”，如图1-25所示。设置好不需要导入的数据后单击【完成】按钮。
图1-25
技巧002 批量导入网页数据
应用场景
批量导入网页数据对于每天需要从网站下载数据，甚至需要同步网页数据的使用者来说，是一个非常重要的技能。通过Excel软件自带的获取网站数据功能，可以轻松批量导入网页数据。下面以导入中国建设银行网页中的人民币贷款基准利率数据为例，讲解具体的导入方法。
解决方案
（1）登录中国建设银行网站，找到人民币贷款基准利率表，如图1-26所示。
（2）新建一个Excel工作表，选中A1单元格，在【数据】选项卡的【获取外部数据】组中单击【自网站】按钮，如图1-27所示。
图1-26
图1-27
（3）弹出【新建Web查询】窗口，在顶部的【地址】栏内粘贴中国建设银行的网站地址，单击【转到】按钮，在【新建Web查询】窗口中打开含有人民币贷款基准利率表的建设银行网站，如图1-28所示。
（4）单击人民币贷款基准利率表左上方的
按钮，当该按钮变成
后表示该数据表已被选中，如图1-29所示。
图1-28
图1-29
（5）单击窗口右下方的【导入】按钮，弹出【导入数据】对话框，在该对话框中选择导入数据的存放位置，系统默认的位置是A1单元格，如图1-30所示。
（6）单击【确定】按钮后网站数据即被成功导入到Excel表格中，如图1-31所示。
图1-30
图1-31
技巧003 批量填充导出数据
扫码看视频
应用场景
从软件导出的数据中经常有相同类别的数据记录，仅在第一行有类别名称，其他行次均为空白的情况，对于这样的数据无法实现函数公式或数据透视表等方式的运算操作。
面对一张如图1-32所示的表格，如果要求将空白单元格按照上一行的内容填充，以方便后续的排序、求和、数据透视表等操作，该怎么做呢？
图1-32
常规解决方案
（1）选中A2:C5单元格区域，按【Ctrl+D】组合键，瞬间实现填充。填充效果如图1-33所示。
（2）按照上述方法，分别把上一行的内容逐一填充到其下的空白单元格中。
如果数据有几百行或几千行，显然这样的操作效率非常低下，那么有没有批量操作的方法呢？
图1-33
高效解决方案
（1）选中A2:C13单元格区域，如图1-34所示。如果数据有几百行、几千行，同样需要一次性选中。
（2）按【Ctrl+G】组合键或【F5】键，弹出【定位】对话框，单击【定位条件】按钮，在弹出的【定位条件】对话框中选择【空值】单选按钮，如图1-35所示。
图1-34
图1-35
（3）单击【确定】按钮退出【定位条件】对话框。在编辑栏中输入【=】，然后用鼠标选中空值单元格的上一行单元格，如A2单元格，如图1-36所示。
（4）按【Ctrl+Enter】组合键，完成快速填充操作，效果如图1-37所示。
图1-36
图1-37
填充后的单元格中的内容不是纯粹的数值，而是公式，如果在后续的操作中需要进行排序等操作，则会破坏填充的结果，因此，需要把公式转化成数值。
（5）复制前三列的数据区域，执行【粘贴】→【选择性粘贴】命令，在弹出的【选择性粘贴】对话框中选择【数值】单选按钮，单击【确定】按钮，如图1-38所示，即可把单元格中的公式转化成数值。
图1-38
技术看板
如果想快速选中几千行的数据区域，可先选中数据区域的左上角单元格，然后拖动右侧的纵向滚动条到表格的最底端，按住【Shift】键不放，用鼠标选中数据区域的右下角单元格即可。
技巧004 批量修改数据
应用场景
在工作中经常遇到对某个数据进行整体修改的情况。例如，将图1-39所示的数据表中多次出现的“应收帐款”替换为“应收账款”，将“借方”和“贷方”列中的0值全部清除。如果一个一个地修改，效率极其低下，那么能否采用一种快捷的方式批量进行修改呢？
解决方案
1．将表格中的“应收帐款”替换为“应收账款”
（1）选中整张表格。如果仅希望替换D列单元格中的内容，也可以只选择D列单元格。
（2）按【Ctrl+H】组合键，弹出【查找和替换】对话框，在【替换】选项卡的【查找内容】文本框中输入【应收帐款】，在【替换为】文本框中输入【应收账款】，单击【全部替换】按钮，如图1-40所示。
图1-39
（3）系统将弹出提示对话框，提示被替换的内容的数量，单击【确定】按钮，关闭对话框，返回【查找和替换】对话框。
（4）单击【关闭】按钮，完成操作，效果如图1-41所示。
图1-40
图1-41
2．将“借方金额”列和“贷方金额”列中的0值全部清除
（1）同时选中F列和G列的单元格，按【Ctrl+H】组合键，在弹出的【替换】对话框的【查找内容】文本框中输入【0】，选中【单元格匹配】复选框，如图1-42所示。
（2）单击【全部替换】按钮，系统将弹出提示对话框，提示被替换的内容的数量，如图1-43所示。
（3）单击【确定】按钮，返回【查找和替换】对话框，单击【关闭】按钮。
图1-42
图1-43
注意
【替换】选项卡中的【单元格匹配】复选框用于匹配单纯出现的查找值。例如，查找0值时，如果不选中该复选框，诸如200、209、1001的数值也将被列为要替换的数据，因此，想把单纯的0值清除，务必需要勾选此复选框。
技巧005 剔除重复记录
应用场景
在图1-44所示的数据源中有许多重复记录，利用Excel中的删除重复项功能，可快速剔除重复记录。
图1-44
解决方案
（1）选中数据区域A1:A10，如图1-45所示。
（2）切换到【数据】选项卡，在【数据工具】组中单击【删除重复项】按钮，如图1-46所示。
图1-45
图1-46
（3）在弹出的【删除重复值】对话框中查看【数据包含标题】复选框是否被勾选，如果没有，需要勾选此复选框，然后单击右下方的【确定】按钮，如图1-47所示。
图1-47
技术看板
如果数据表中有标题行，且需要删除标题行中的重复值，则需要勾选【数据包含标题】复选框，反之则不需要勾选。
（4）弹出提示对话框，显示删除的结果，如图1-48所示。单击【确定】按钮，完成操作，效果如图1-49所示。
图1-48
图1-49
技巧006 批量处理不规范的日期数据
应用场景
很多来自下级或同级传递的表格中，如果日期数据的录入方式不规范，将为后续的计算操作带来障碍。采用查找、替换的方式可以快速规范日期数据。
解决方案
（1）选中不规范的日期数据区域，如图1-50所示。
（2）按【Ctrl+H】组合键，弹出【查找和替换】对话框，在【替换】选项卡的【查找内容】文本框中输入【.】，在【替换为】文本框中输入【-】，然后单击【全部替换】按钮，如图1-51所示。
图1-50
图1-51
（3）系统将弹出提示对话框，提示被替换的内容的数量。单击【确定】按钮关闭该对话框。
（4）单击【关闭】按钮，退出【查找和替换】对话框。选中数据区域，单击鼠标右键，在弹出的快捷菜单中选择【设置单元格格式】命令，在弹出的【设置单元格格式】对话框中选择自己喜欢的日期类型，如图1-52所示。
（5）单击【确定】按钮关闭对话框，完成操作后的效果如图1-53所示。
图1-52
图1-53
注意
在【查找和替换】对话框的【替换】选项卡中勾选【单元格匹配】复选框的查找替换后，再执行其他查找替换操作时，务必先恢复【单元格匹配】复选框的非勾选状态，否则本环节操作会报错。
技巧007 行数据和列数据的转换
应用场景
在工作中经常遇到把一列数据转换成一行数据，或者把一行数据转换成一列数据的情况，抑或需要将一个横向的表格转换成纵向的表格，采用转置粘贴的方法可以快速实现行数据和列数据的转换。
解决方案
（1）选中需要行列转换的数据区域，如图1-54所示，单击鼠标右键，在弹出的快捷菜单中选择【复制】命令，如图1-55所示。
图1-54
图1-55
（2）选中数据区域外的一个空白单元格，如G1单元格，单击鼠标右键，在弹出的快捷菜单中选择【选择性粘贴】命令。
（3）在弹出的【选择性粘贴】对话框中选择【转置】复选框，单击【确定】按钮，如图1-56所示。
数据行和数据列转换后的效果如图1-57所示。
图1-56
图1-57
技巧008 快速拆分英文代码和汉字名称的混合数据
扫码看视频
应用场景
一些从网页中导出的数据或者从Word文档中复制的数据，当粘贴到Excel表格中时，所有数据将被粘贴到一个单元格里，这会造成计算、筛选等操作无法正常进行。采用分列的方法可以实现数据分列，将不同类别的内容分别存放到不同的列中。下面以拆分产品的型号和名称为例，讲解快速拆分此类数据的方法。
解决方案
（1）选中要拆分的数据区域，如A列单元格，在【数据】选项卡的【数据工具】组中单击【分列】按钮，如图1-58所示。
（2）在弹出的【文本分列向导-第1步，共3步】对话框中单击【下一步】按钮，如图1-59所示。
图1-58
图1-59
（3）在弹出的【文本分列向导-第2步，共3步】对话框中选择【空格】复选框，再次单击【下一步】按钮，如图1-60所示。
（4）在弹出的【文本分列向导-第3步，共3步】对话框中单击【目标区域】折叠按钮，选择放置拆分内容的目标列，如C1单元格，再次单击折叠按钮，返回【文本分列向导-第3步，共3步】对话框，如图1-61所示。
图1-60
图1-61
（5）单击【完成】按钮，完成拆分操作后的效果如图1-62所示。
技术看板
在第4步的操作中，如果在【文本分列向导-第3步，共3步】对话框的【数据预览】区域中有不需要导入的列，可以选中该列，然后选择【不导入此列（跳过）】单选按钮，如图1-63所示。
图1-62
图1-63
技巧009 快速拆分日期
扫码看视频
应用场景
很多人在录入日期型数据时，习惯把起止日期放在同一个单元格中，中间用“ - ”或“--”隔开，但当需要用日期数据进行计算或筛选操作时，会发现这样的数据根本无法直接使用，需要先将其拆分处理。
解决方案
（1）选中要拆分的日期数据区域，如A列单元格，如图1-64所示。
（2）在【数据】选项卡的【数据工具】组中单击【分列】按钮，在弹出的【文本分列向导-第1步，共3步】对话框中单击【下一步】按钮，如图1-65所示。
图1-64
图1-65
（3）在弹出的【文本分列向导-第2步，共3步】对话框中选择【其他】复选框，在其后面的文本框中输入“-”，单击【下一步】按钮，如图1-66所示。
（4）弹出【文本分列向导-第3步，共3步】对话框，在该对话框的【数据预览】区域中选择中间的那一列，再选择【不导入此列（跳过）】单选按钮，将【目标区域】设置为A1单元格，如图1-67所示。
图1-66
图1-67
（5）单击【完成】按钮完成操作，拆分效果如图1-68所示。
图1-68
拓展应用 快速拆分日期和时间
往往从应用软件中导出的日期数据中包含时间数据，其格式为文本格式，如图1-69所示。采用分列方法同样可以将日期和时间数据拆分到两列单元格中。
图1-69
按照技巧009中的方法进行分列拆分操作，不同之处为在【文本分列向导-第2步，共3步】对话框中选择的分隔符号为空格，如图1-70所示。分列后的效果如图1-71所示。
图1-70
图1-71
技巧010 两种方法解决导出的数据不能求和的问题
应用场景
图1-72是某公司从其销售系统中导出的旗下各门店的交易数据。从该图的编辑栏中可以看到数据356.82前有个单引号。在Excel中，凡是带有单引号的数据都被视为文本型数据，而文本型数据是不能进行求和运算的。本例中，若要对D列的交易金额正确求和，就需要将文本格式的交易金额转换为数字格式。这里，我们通过分列处理法和运算处理法来实现数据格式的转换。
图1-72
解决方案
方案1 用分列处理法处理包含单引号的数据
分列处理法是基于Excel自带的分列功能，把一列数据中有相同分隔符号的内容拆分成多列，或者把固定位置的内容拆分成多列。例如，在整理员工资料档案时，将员工的18位身份证号码中的第7~12位、第11~14位和第17位分别拆分出来，即可获取该员工的出生年月、生日日期和性别等信息。
1．处理包含单引号的普通数据
（1）选中D2:D11单元格区域，切换到【数据】选项卡，单击【数据工具】组中的【分列】按钮，如图1-73所示。
（2）弹出【文本分列向导-第1步，共3步】对话框，单击【下一步】按钮，如图1-74所示。
图1-73
（3）弹出【文本分列向导-第2步，共3步】对话框，保持默认设置，单击【下一步】按钮，如图1-75所示。
图1-74
图1-75
（4）弹出【文本分列向导-第3步，共3步】对话框，单击【完成】按钮，如图1-76所示。分列后的数据效果如图1-77所示，可以看到编辑栏内的356.82前面的单引号没有了，D2:D11单元格左上角的绿色三角符号也消失了，说明该列的数据格式已由文本型转换为常规型，可以正常进行求和计算了。
图1-76
图1-77
2．处理包含单引号的日期数据
图1-78中的C列是一组日期数据，从图中左上角的编辑栏中可以看到数据“2018/9/20”前面有个单引号。由于单引号的存在会影响后续的计算操作。例如，如果需要判断C2单元格中的“2018/9/20”是否大于DATE(2018,7,28)，结果显示“TRUE”表示大于。而实际上，由于DATE（2018,7,28）的结果是2018/7/28，应小于2018/9/20。因此，需要将“创建时间”数据由文本型转换为日期型。转换方法参照上面“处理包含单引号的普通数据”部分的介绍。注意：对于本例，在第4步进行【文本分列向导-第3步，共3步】对话框的设置时，需要将【列数据格式】设置为【日期】，如图1-79所示。
图1-78
图1-79
方案2 用运算处理法处理包含单引号的数据（伪数据）
运算处理法是指当一个单元格区域中的文本型数据需要转换成数值型数据时，通过一次不影响数据大小的数学运算实现格式的转换的方法。一般常用的数学运算是加0或乘以1，加0和乘以1的运算都不影响数据本身的大小。
1．处理包含单引号的数据
（1）在图1-72所示的表格中，在任意一个空白单元格中输入【1】，复制该单元格，然后选中D2:D11单元格区域，如图1-80所示。把鼠标指针悬停在选中的单元格区域上，单击鼠标右键，在弹出的快捷菜单中选择【选择性粘贴】命令。
（2）在弹出的【选择性粘贴】对话框中选择运算方式为【乘】，单击【确定】按钮，如图1-81所示。
图1-80
图1-81
执行完选择性粘贴后，D2:D11单元格区域中显示的是一组数据，如图1-82所示。
2．处理包含单引号的日期数据
（1）在图1-72所示的表格中，在任意一个空白单元格中输入【0】，然后复制该单元格。选中C2:C11单元格区域，如图1-83所示。把鼠标指针悬停在选中的单元格区域上，单击鼠标右键，在弹出的快捷菜单中选择【选择性粘贴】命令。
图1-82
图1-83
（2）在弹出的【选择性粘贴】对话框中选择运算方式为【加】，单击【确定】按钮完成操作，如图1-84所示。
执行完选择性粘贴后，C2:C11单元格区域显示的是一组数据，如图1-85所示，这是一种正常现象，看到此处时也不必担心原有的日期数据会丢失。
图1-84
图1-85
（3）再次选中C2:C11单元格区域，设置单元格格式为日期格式，显示结果立即恢复正常，如图1-86所示。
图1-86
技巧011 批量剔除导出数据首部或尾部的不可见字符
应用场景
在电商管理软件或ERP系统等中导出的数据，通常在数据的首部或尾部会有多余的不可见字符。例如，在图1-87中共有4列数据，分别是终端号、门店名称、创建时间、交易金额。当使用函数汇总不同店铺的交易金额，或者使用终端号查询交易金额进行数据核对时，会出现汇总金额错误或查询失败的结果，这是由于数据中含有不可见的字符导致的。因此，若想正常使用这些数据，就得先把这些不可见的字符剔除。下面以剔除【终端号】字段中的不可见字符为例进行讲解。
解决方案
1．判断不可见字符是否存在
（1）在A13单元格中输入【420047】，在B13单元格中输入公式【=VLOOKUP(A13, A2:D11,5,0)】，目的是调用交易金额。公式返回“#N/A”，如图1-88所示。
图1-87
数据表的A列中明明有终端号“420047”，但是VLOOKUP函数为什么不能正常查找呢？
图1-88
造成这种不能正常查找匹配的原因有多种，最常见的是以下几种。
① 单元格格式不匹配。从电商管理软件或ERP系统等中导出的数据往往是文本格式，而Excel默认手工录入的数据是数字格式，二者格式不同，导致VLOOKUP函数无法实现查找匹配。
解决方法：先把A2:A11单元格区域的单元格格式设置为【常规】，如图1-89所示。再选中数据表中的任意一个空白单元格，按【Ctrl】+【C】组合键复制该单元格格式，然后选中A2:A11单元格区域，在【开始】选项卡的【剪贴板】组中，单击【粘贴】按钮，选择【选择性粘贴】命令，在弹出的【选择性粘贴】对话框中选择【运算】组中【加】单选按钮，单击【确定】按钮，如图1-90所示。操作原理是把文本格式的数据进行一次“加0”的数学运算，将其转变成数值格式。
图1-89
图1-90
② 如果数据表中的“终端号”数据是数值格式，而用于查询的“终端号”是常规格式，同样会发生不匹配问题。解决此类问题的错误操作是在录入前将单元格的格式设置成文本格式，但在录入数据时，系统默认是文本格式的数据，仍会造成不匹配问题。
解决方法：先设置查询的数据项所在的单元格格式为【常规】，然后再录入一遍数据，或者设置单元格格式后，双击数据项所在的单元格也能实现格式转换。还可以考虑用公式解决。例如，本案例中把原来的查询公式【=VLOOKUP(A13,A2:D11,5,0)】修改为【=VLOOKUP(A13+0,A2:D11,5,0)】，可以得到同样的效果。
③ 数据表中包含不可见的字符，如本案例中的终端号数据中包含多余的空白字符。此时可以使用LEN函数鉴别数据表中是否包含不可见字符。
函数解析：LEN函数用于返回文本字符串中的字符个数。例如，在A1单元格中输入【淘宝店铺】，在B1单元格中输入公式【=LEN(A1)】，其结果为4。
（2）在E2单元格中输入公式【=LEN(A2)】，作用是获取A2单元格中字符串“终端号”的字符个数，如图1-91所示。
（3）拖曳E2单元格的填充柄，向下复制公式至E11单元格，结果如图1-92所示。
图1-91
图1-92
E列中的公式结果都为7，而实际终端号的位数只有6位数，说明终端号数据中包含不可见的字符。此时需要使用LEFT函数和RIGHT函数鉴别不可见字符的位置。
函数解析：LEFT函数用于从一个文本字符串的左侧第一个字符开始返回指定个数的字符。RIGHT函数用于从一个文本字符串的右侧第一个字符开始返回指定个数的字符。
（4）在F2单元格中输入公式【=LEFT(A2,6)】，在G2单元格中输入公式【=RIGHT(A2,6)】，如图1-93所示。这里LEFT函数的作用是从左侧第一个字符开始返回终端号文本字符串中的6个字符，RIGHT函数的作用是从右侧第一个字符开始返回终端号文本字符串中的6个字符。
（5）分别拖曳F2和G2单元格的填充柄，向下复制公式至F11和G11单元格，复制公式后的效果如图1-94所示。
图1-93
图1-94
从公式的结果中可以看出，LEFT函数返回的结果是6位数，而RIGHT函数返回的结果是5位数。为了增加不可见字符的演示效果，这里将公式【=RIGHT(A2,6)】中的返回个数“6”分别修改为“1”和“2”后再观察返回结果。当返回个数为1时，F列单元格中不显示任何内容；当返回个数为2时，G列单元格中只显示终端号的最后一位，如图1-95所示。
图1-95
由此可断定不可见字符的确存在，而且就在终端号字符串的右侧。
2．剔除不可见字符
剔除不可见字符有两种方法，一种是使用函数，另一种是使用文本分列向导。
方法1：使用函数剔除不可见字符。
使用函数剔除不可见字符的原理：不可见字符在字符串的两端，虽然不可见，但是可以使用LFET或RIGHT函数把可见的字符从整串字符串中截取出来，从而达到把不可见字符剔除的目的。
经过前面的一系列操作，我们已经判断出不可见字符在终端号数据的右侧，因此，采取从左侧截取6位字符的方法最适合。在数据区域右侧的任意空白单元格中输入公式，如在H2单元格中输入【=LEFT(A2,6)+0】或【=--LEFT(A2,6)】，都可以获得正确的结果。公式【=LEFT(A2,6)+0】中有个“加0”的设计，其作用是把截取后的数字做一次不影响数据大小的数学运算。也可以采用“乘1”的方法，两种方法都能获得正确的结果。公式【=--LEFT(A2,6)】中“--”符号的作用可以理解成负负为正，同样可以视同为一次不影响数据大小的数学运算。之所以设计这个运算环节，是为了解决LFET函数截取出的数据是文本型的问题，通过一次运算将文本型数据转换成数值型数据。然后将该公式向下复制至H11单元格。此时，A列中的数据仍然包含不可见字符，而H列中的数据是进行过处理的、没有不可见字符的数据。H列仅仅是个数据处理的过渡列，接下来需要把H列的数据置换到A列。操作方法：复制H列的数据，以选择性粘贴的方式（选【数值】单选按钮）粘贴到A列中，最后删除H列数据。
方法2：使用文本分列向导剔除不可见字符。
① 选中A2:A11单元格区域，切换到【数据】选项卡，单击【数据工具】组中的【分列】按钮，在弹出的【文本分列向导-第1步，共3步】对话框中选择【固定宽度】单选按钮，单击【下一步】按钮，如图1-96所示。
② 弹出【文本分列向导-第2步，共3步】对话框，在【数据预览】区域中紧贴终端号数据的右侧单击鼠标左键，系统会自动添加一条竖线，如图1-97所示。
图1-96
图1-97
③ 单击【下一步】按钮，弹出【文本分列向导-第3步，共3步】对话框，在【数据预览】区域中有两列数据，左列是终端号数据，右列为空白列，其中空白列中有不可见的字符，需通过分列将其清除。用鼠标单击右侧空白列的顶部，将其选中，然后选择【列数据格式】组中的【不导入此列（跳过）】单选按钮，再单击【目标区域】文本框右侧的折叠按钮，在数据表中选择H2单元格（任意一个空白列的第二行都可以），返回对话框，单击【完成】按钮，如图1-98所示。
④ 分列操作后的结果如图1-99所示，在H2:H11单元格区域中出现了一组新填充的终端号，而且数据处于右对齐状态，说明该列数据已经被转换成数值型数据。
⑤ 把H2:H11单元格中的数据复制、粘贴到A列中，清空H列的数据即可完成全部操作。此时，我们会发现，复制、粘贴操作后，A列中的终端号由原来的左对齐变成了右对齐，如图1-100所示。
图1-98
图1-99
⑥ 在Excel中，单元格中的数字和文本都有默认的对齐格式。如果在任意一个没有经过设置格式的单元格中输入一个数字并按【Enter】键后，该数字会默认以右对齐的方式显示；如果输入的是一个汉字或字母等文本，按【Enter】键后，该文本会默认以左对齐的方式显示。由此我们可以轻松识别数字单元格的格式。就本案例来说，A列中的终端号居右对齐，并且也没有设置横向位置，说明是数字格式，如图1-101所示。
图1-100
图1-101
技巧012 解决导出的文本内容不匹配问题
应用场景
在文本查询操作中，常遇到这种情况：数据源是从平台或系统导出的，而查询内容是手工输入的，由于数据项与查询内容格式不相同，数据项中含有不可见字符或空格时，导致查询失败。
例如，某淘宝店铺根据快递公司提供的报价数据自行计算快递费，在计算过程中需要根据目的地在报价表中查找对应的首重费用、续重单价等信息，可应用VLOOKUP函数进行相关信息的查询。如图1-102所示，A1:D12单元格区域为数据源，在F2单元格中输入查询内容“内蒙古”，在G2单元格中输入公式【=VLOOKUP(F2,A2:D12,4,0)】，结果返回错误值“#N/A”。这说明在A2:D12单元格区域的第一列中没有找到F2单元格的内容“内蒙古”。
在图1-102中，从表面上看，F2单元格的内容与A2单元格的内容完全一致，但在G2单元格中使用公式查找时却返回错误值。为了验证这两个单元格的内容是否相同，我们采取如下方法进行测试。
在任意一个空白单元格，如F4单元格中输入公式【=A2=F2】，公式结果为FALSE，如图1-103所示。这说明虽然看上去两个单元格的内容相同，但实际上A2和F2单元格的内容是不同的，原因是A2单元格中含有不可见的字符。
图1-102
图1-103
解决方案
（1）利用CLEAN函数清除A列单元格中不可见的字符。在E2单元格中输入公式【=CLEAN(A2)】，如图1-104所示。
公式解析：CLEAN函数用于删除文本中含有的当前操作系统无法打印的字符（不可见字符）。其语法格式如下。
CLEAN（字符串或单元格地址）（2）复制E2单元格的公式到数据源的最后一行，结果如图1-105所示。
图1-104
图1-105
（3）复制E2:E12单元格区域，在【开始】选项卡的【剪贴板】组中选择【粘贴】→【选择性粘贴】命令，在弹出的【选择性粘贴】对话框中选择【数值】单选按钮，单击【确定】按钮关闭对话框，然后将其粘贴到A2:A12单元格区域中，最后把E列的内容删除，至此完成清除不可见字符的操作。
（4）再次在F2单元格中输入查询内容【内蒙古】，在G2单元格中输入公式【=VLOOKUP(F2,A2:D12,4,0)】，结果返回1.5，如图1-106所示。这说明文本内容不匹配问题已解决。
图1-106
如果使用上述方法仍不能解决不匹配问题，可尝试下面的几种方法。
1．用TRIM函数去除空格字符
函数解析：TRIM函数用于除去字符串开头和末尾的空格或其他字符。其语法格式如下。
TRIM（字符串或单元格地址）TRIM函数主要用于把单元格内容前后的空格去掉，但并不去除字符之间的空格。在实际应用中，如果有些字符串使用CLEAN函数无法完全清除，可以试试使用TRIM函数。如果你嫌用CLEAN和TRIM函数分别测试麻烦，也可以组合使用这两个函数一次完成测试。例如，假设待处理的字符串在A2单元格中，在空白单元格中输入公式【=TRIM(CLEAN(A2))】，可以把一些不明确的问题一并解决。
TRIM函数去除空格的操作往往用在导出数据中，很多从电商软件导出的数据中都包含空格，而且这个空格与使用键盘操作录入的空格是不同的，用查找替换的方法无法完全清除，采用函数法清除反而比较有效。
2．用N函数和NUMBERVALUE函数转换数据格式
函数解析：N函数的作用是将不是数值形式的值转换为数值形式。NUMBERVALUE函数是Excel 2013版本之后才有的一个函数，主要功能是将与区域无关的文本转换为数字。两个函数的语法格式如下。
N（要进行转换的值）
NUMBERVALUE（要转换主数字的字符串）这两个函数的使用方法与CLEAN函数和TRIM函数相同。例如，假设字符串在A2单元格中，清除公式为“=N(A2)”或“=NUMBERVALUE(A2)”。
技术看板
在实际查询操作中，出现不能匹配的情况时，数字类的内容判断顺序如下。
① 格式是否匹配。
② 是否含有空格。
③ 是否含有不可见字符。
若含有，可用CLEAN函数清除。
文本类的内容判断顺序如下。
① 是否含有空格。
② 是否含有不可见字符。
若含有，可用CLEAN函数清除。