Excel财务数据处理与分析实战技巧精粹

版权信息

书名：财务数据处理与分析实战技巧精粹

ISBN：978-7-115-57922-5

本书由人民邮电出版社发行数字版。版权所有，侵权必究。

您购买的人民邮电出版社电子书仅供您个人使用，未经授权，不得以任何方式复制和传播本书内容。

我们愿意相信读者具有这样的良知和觉悟，与我们共同保护知识产权。

如果购买者有侵权行为，我们可能对该用户实施包括但不限于关闭该帐号等维权措施，并可能追究法律责任。

版　　权

著　　　　郭　辉

责任编辑　牟桂玲

人民邮电出版社出版发行　　北京市丰台区成寿寺路11号

邮编　100164 　电子邮件　315@ptpress.com.cn

网址　http://www.ptpress.com.cn

读者服务热线：(010)81055410

反盗版热线：(010)81055315

内容提要

本书从财务会计人员的实际工作出发，汇集了用户使用Excel进行财务数据处理与分析过程中的热点问题，并以问题的解决方案为导向，通过100多个典型应用案例的演示与讲解，全面展示了财务数据分析和可视化的思路、方法和技巧。

本书共21章，主要包括数据的获取、自定义格式、录入、数据验证、填充、分列、排序、筛选、汇总、统计、查找、保护、定位，以及常用的Excel函数公式、图表等内容，并按照应用场景为读者分类整理出96个操作技巧，帮助读者切实提升财务数据分析工作的效率。对于一些难以理解的知识点或重要的操作步骤，本书还配有视频讲解，帮助读者轻松掌握。

本书实用性强，所介绍的分析方法和分析技巧均可直接应用于日常工作，且便于读者随用随查。即使读者是财务新人也能“拿来即用”，高效完成财务数据分析工作。

写在前面

当今的职场工作中，无论是会计工作还是财务工作，都是围绕着数据进行的。数据就像是流淌在企业肌体里的血液，数据流的通畅程度直接影响企业的肌体健康。高效的数据处理、精准的数据呈现，是每个财务职场人的必修课，熟练使用Excel当之无愧地成为财务职场人必备的工作技能。

对于从事财务会计工作的人员，我有几点建议。

一、管理好数据的前提是规范数据

在Excel的使用中，经常出现混淆数据状态的现象，具体表现为数据的初始状态、存储状态和整合后的汇总状态混用。这3种数据状态具有不同的功能，可以相互转化，但不能相互替代，而且彼此之间还存在着强烈的关联关系。

在财务工作中，这3种数据状态非常常见，如会计凭证是数据获取的初始状态，账簿是数据的存储状态，而报表则是数据的汇总状态。而在财务以外的工作中，由于没有会计准则、会计制度的严格约束，大多数人经常把它们混合成一张表，即使没有混在一起，3种状态的表格之间也缺少必要的关联关系。就拿产品入库登记表来说，很多企业的做法是把产品入库登记表中的产品信息重复地登记在很多表中，如产品入库登记台账、产品流向登记台账，以及手工计算的各种汇总表。待添加新数据后，再进行表与表之间的“目测”核对，这样出错的概率非常大。

而对于数据添加的方法，也是千差万别。例如，在应付款表中登记供货方新增的一笔欠款，有的是以记录表的方式新增一条记录；有的是在单元格的加法算式中增加一个加项（如由“=20+30+40”修改成“=20+30+40+50”）；有的甚至是用计算器计算，然后把结果直接写进单元格，等等。如果有其他备注信息需要记录，就在单元格的批注里填写。由此会出现很多问题，如工作量加大，一张单据多处登记；表与表之间没有关联，在多张表中重复录入同一个数据，由此造成勾稽关系混乱；由于数据是在计算器上计算的，在后续的数据查询和比对时无法查找到错误的原因；当修改某张单据时，需要在多处变更，难免出现表与表之间的对应关系出错，造成数据汇总输出的内容错误百出。

由此可见，数据的流转、比对、查询、汇总等都需要规范操作，包括规范数字格式、规范表的结构、规范表之间的布局。数据规范是数据处理加工的起点，是数据报表输入的保障，要做好财务数据的处理与分析工作，必须先从数据规范做起。

二、财务数据分析的原则

企业对内和对外的通用语言是财务报表，而财务报表展示的内容非常有限，各家企业内部都有专属的数据统计表和数据分析方法。数据分析的目的是把常规财务报表中的数据做深层拆分，通过更多的分析方法发现异常变化，找出指标增减变动的影响因素，进而指导企业的宏观决策。

在28年的财务工作中，本人总结了几个百试不爽的财务数据分析通用原则，希望能帮到Excel数据分析新手及初入职场的财务工作者。

1．避免数据分析误区

数据分析的误区主要体现在3个方面，一是分析人认为数据分析高深莫测，不敢轻易踏入这个领域；二是分析人对分析的目的和分析的作用不明确，所以勉强应付；三是过分看重分析结果而忽略了数据规范，把主要精力放在分析结果的输出上，殊不知由于缺乏规范性的数据所做支撑的分析结果是华而不实的。数据规范不但是数据分析的基础，也是企业管理和企业决策的基础。

2．数据分析的具体内容

数据分析涵盖的面非常广泛，简单地说就是把枯燥的、抽象的数据通过不同角度进行输出，把数据内含的问题或趋势展示出来。展示的方法可以是表格形式，也可以是动态图表形式，再配以文字说明，让数据背后的问题清晰地呈现出来。借助Excel表格把诸多看似普通的数据，通过收集、整理，并使之与相关的指标进行关联分析，可从中发现问题。例如，单位水电费与产量的关系对比分析、单位成本的变化分析、收入的预测分析、主材消耗与产量的变化分析，等等。从分析中找出变动后不匹配的、有异常的数据指标或项目，将结果上报公司决策机构，为公司决策提供依据。

3．数据分析的方向

数据分析可分为事后分析和事前预测。事后分析主要是费用消耗、成本变动等，对已经发生的数据做出分析，例如管理费用增加，通过环比分析和同比分析找出具体增加的明细项目，进而找出导致费用增加的开支由哪几笔支出构成，为后面的开支原则或预算制订提供依据。事前预测主要包括收入预测、利润预测，预测的方法是根据以往的数据记录测算出未来的预测指标。预测指标毕竟是根据历史数据计算而来，因而还要结合市场因素、季节因素、淡旺季因素等多方面的信息综合制订出科学合理的预测指标。

作为一名财务数据分析人员，要对企业有全局意识，对数据处理有一定的驾驭能力，善于找出独特的分析角度，揭示企业存在的问题，善于对数据做出多角度的拆分、组合，再拆分再组合，通过改变分析角度，选择不同的分析方向，确定分析重点，结合企业实际需求编制专属于本企业的分析报告。数据分析能力固然重要，但更重要的是善于站在企业管理者的高度看问题，把自己当作企业的管理者，从企业发展的角度思考，分析的思路会越来越宽广。

三、本书给你的帮助

本书以工作中的实际案例为主线，一个案例解决一个工作中的实际问题，解决问题的方法是利用Excel中的各种功能或工具，高效处理与分析数据，并以可视化的方式呈现数据分析结果。

书中涉及的Excel功能包括录入、自定义格式、填充、排序、查找、筛选、分列、汇总、数据验证（数据有效性）、定位等，涉及的函数有VLOOKUP、OFFSET、INDIRECT、INDEX、SUMPRODUCT等。书中案例来源于实践，读者学懂弄通后便能服务于实践。希望本书能成为一本真正解决实际问题的书。

由于作者水平有限，书中存在的错漏之处，恳请广大读者批评指正。读者的建议或意见可发送电子邮箱：muguiling@ptpress.com.cn。

郭辉　　　　

2021年10月1日

通用技巧篇

第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函数清除。

第2章　数据的自定义格式

数据的自定义格式指的是数据按照一定的样式显示的格式。通俗地说就是把不同类型的数据，诸如整数数据、带有小数的数据、带有计量单位的数据等，以形式各异的样式显示出来。显示样式有包含1位或2位小数的样式、带有千分位分隔符的样式、不影响计算的带有计量单位的样式、手机号码或银行账号分段显示的样式、百分数样式等。不同的数据需要不同的显示样式，这就是数据格式。然而格式就是格式，无论设置何种样式的格式，都不会改变数据本身的大小。数据自定义格式的样式更多，用户可以根据需要进行灵活设置。

技巧013　分段显示长数据

扫码看视频

应用场景

手机号码、银行账号、身份证号码等数据，由于位数较长而不利于阅读。如果按照预先设定的间隔位数，用空格有规律地分段显示这些数据，那么读取操作就容易多了。

下面以分段显示手机号码为例，介绍利用Excel的单元格格式功能分段显示长数据的方法。原始数据如图2-1所示。

图2-1

解决方案

（1）选中要分段显示的数据区域，如A2:A14单元格区域，单击鼠标右键，在弹出的快捷菜单中选择【设置单元格格式】命令，弹出【设置单元格格式】对话框，在【数字】选项卡的【分类】列表框中选择【自定义】选项，在【类型】文本框中输入自定义分段显示的样式，如“000 0000 0000”（一个0相当于1个占位符），单击【确定】按钮，分段显示效果如图2-2所示。

（2）在【类型】文本框中输入不同的显示格式，可以获得不同的分段显示效果。例如，对于C2:C14单元格区域，若在【类型】文本框中输入“00 00 00 00 00 0”,那么电话号码的分段显示效果则如图2-3所示。

图2-2

图2-3

拓展应用　分段显示用户账号

若要将一组用户账号按照4位一组显示，假设每个用户账号均有16位，4位一组，则共计4组。在【设置单元格格式】对话框的【类型】文本框中输入“0000 0000 0000 0000”，单击【确定】按钮后的显示效果如图2-4所示。

除了利用单元格格式功能分段显示长数据外，还可以借助TEXT文本函数分段显示长数据。例如，基于图2-1中的数据源（A2:A14单元格区域），在B2单元格中输入公式【=TEXT(A2,"000 0000 0000")】，然后向下复制公式，效果如图2-5所示。

图2-4

图2-5

技术看板

默认情况下，Excel中每个单元格所能显示的数字为11位。如果输入超过11位的数字，系统会自动将其转换为科学记数格式。例如，若输入“123456789012”，则会显示为“1.23457E+11”。虽然不能完整显示数据，但是录入的“123456789012”仍然完整地保存在单元格中。如果输入超过15位的数值，系统会自动将15位以后的数字自动置零。例如，若输入“1234567890123456”，则会显示为“1.23457E+15”，并且在单元格中保存下来的实际数据是“1234567890123450”，最后一位数字“6”被系统变更为“0”。

要保证输入的长数字（11位数以上）不变，可以在录入数据之前，先输入一个英文状态下的单引号，也可以先设置数字格式为【文本】（设置方式见图2-6），然后录入数据。

图2-6

技巧014　带有计量单位的数据求和

扫码看视频

应用场景

很多表格中的数据都需要提供计量单位，但如果把计量单位直接写在数字后面会造成数据无法正常运算。能否在不影响计算的情况下添加计量单位是很多财务人员梦寐以求的事，通过设置单元格格式可以让你美梦成真。

假设一张数据统计表中有数量、单价、金额等具体数据，如图2-7所示。现需要为数据添加计量单位的同时，还要保证能正常运算。

图2-7

解决方案

（1）选中C2:C9单元格区域，单击鼠标右键，在弹出的快捷菜单中选择【设置单元格格式】命令，弹出【设置单元格格式】对话框，在【数字】选项卡中选择【分类】列表框中的【自定义】选项，在【类型】文本框中输入【0千克】，然后单击【确定】按钮，如图2-8所示。

（2）再次打开【设置单元格格式】对话框，可以看到系统自动为“千”和“克”分别添加了双引号，如图2-9所示。

图2-8

图2-9

如果你觉得该内容显示凌乱，可以手工修改，如把“千克”两个字用一组双引号括起来。

（3）选中D2:D9单元格区域，单击鼠标右键，在弹出的快捷菜单中选择【设置单元格格式】命令，弹出【设置单元格格式】对话框，在【数字】选项卡的【分类】列表框中选择【自定义】选项，在【类型】文本框中输入【0.0"元/千克"】，单击【确定】按钮，如图2-10所示。

图2-10

（4）【金额】列单位的添加方法请参看【数量】列单位的添加过程。

技术看板

利用【设置单元格格式】命令添加数字后缀时应注意如下几点。

（1）添加纯汉字后缀时，可以不用添加双引号，因为系统会帮你自动添加。例如，0元会被系统自动修改为【0"元"】。

对于自定义格式，“0元”表示整数，“0.0元”表示显示一位小数。如果希望显示两位小数，则可在【类型】文本框中将其修改为【0.00元】。

（2）添加带有字母、横杆或斜杠的后缀时，务必添加双引号。例如，若要添加后缀“元/千克”，则需在【类型】文本框中输入【0.0"元/千克"】。

（3）如果希望在数字和计量单位间拉开一点距离，可以在计量单位前添加一个空格。例如，在【类型】文本框中输入【0.00"　元"】。

技巧015　自动填写表头的3种方法

扫码看视频

应用场景

很多表格的表头中都带有月份、年份等数字。例如，11月销售统计表、3月生产记录表、2018年6月设备登记台账等。在进行月份转换时，需要在编辑栏中或单元格中修改月份，虽然这不是一个复杂的操作，但是一不小心就会把其他内容误删除了。通过设置自定义单元格格式，可以实现便捷的月份或年份转换，确保准确无误地修改月份或年份信息。

解决方案

1．月份在前的表头填写方法

（1）选中表头单元格，单击鼠标右键，在弹出的快捷菜单中选择【设置单元格格式】命令，弹出【设置单元格格式】对话框，如图2-11所示。

（2）在【数字】选项卡的【分类】列表框中选择【自定义】选项，在【类型】文本框中输入【0"月工资计算表"】，单击【确定】按钮退出该对话框，如图2-12所示。

图2-11

图2-12

（3）在表头单元格中任意输入一个1~12的数字，如这里输入【3】，按【Enter】键后，表头内容便完整地呈现在该单元格中，如图2-13所示。

图2-13

技术看板

如果希望用两位数显示月份，如“06月工资计算表”，在【类型】文本框中将原来输入的【0"月工资计算表"】修改为【00"月工资计算表"】即可。

2．月份在中间的表头填写方法

（1）选中表头单元格，单击鼠标右键，在弹出的快捷菜单中选择【设置单元格格式】命令，弹出【设置单元格格式】对话框，在【数字】选项卡的【分类】列表框中选择【自定义】选项，在【类型】文本框中输入【"人力资源"00"月工资计算表"】，单击【确定】按钮，如图2-14所示。

（2）在表头单元格中任意输入一个1～12的数字，如这里输入【06】，按【Enter】键后，表头内容便完整地呈现在该单元格中，如图2-15所示。

图2-14

图2-15

3．仅显示年月的自定义表头填写方法

（1）选中表头单元格，单击鼠标右键，在弹出的快捷菜单中选择【设置单元格格式】命令，弹出【设置单元格格式】对话框，在【数字】选项卡的【分类】列表框中选择【自定义】选项，在【类型】文本框中输入【yyyy"年"m"月工资计算表"】，单击【确定】按钮，如图2-16所示。

（2）在表头单元格中输入一个完整的日期，如这里输入【2018-2-1】，按【Enter】键后，表头内容便完整地呈现在该单元格中，如图2-17所示。虽然表头没有显示是2018年2月的几日，但必须输入一个完整的日期。

图2-16

图2-17

技术看板

若需要在年份的前面添加部门名称，如“财务部”，把【自定义】的【类型】设置由【yyyy"年"m"月工资计算表"】修改为【"财务部"yyyy"年"m"月工资计算表"】即可，如图2-18（a）所示。

注意：该技巧要求录入完整的日期数据，如“2018-5-10”，输入完成后按【Enter】键，系统便会自动添加自定义格式中的内容。如果录入的日期格式错误，系统会返回错误的年月值。例如，若输入【2018.5】后按【Enter】键确认，返回的错误结果如图2-18（b）所示。

（a）

（b）

图2-18

本技巧中的两个自定义表头在录入方法上有着本质的区别，第一、二个方案录入的是一个数字，数字前后的内容通过格式设置实现；第三个方案录入的是一个日期，系统通过“yyyy”和“m”把日期中的年份和月份分别提取出来，再和自定义格式设置中的文字内容共同组成显示内容。掌握了设置原理，就可以设计出更多形式的自定义格式。

技巧016　批量编辑多个单元格区域

应用场景

当在一张表格的某个区域中需要录入一个相同的数据时，除了复制、粘贴外，还有没有其他批量录入的方法呢？答案是肯定的。下面介绍两个批量录入的方法，其中，方法1适用于在多个空白单元格区域中批量录入，方法2适用于在指定单元格区域的多个空白单元格中批量录入。

解决方案

1．在多个空白单元格区域中批量录入相同内容

（1）若要在图2-19所示的3个连续的空白单元格区域中批量录入文字【武汉加油】，则先选中A1:C7单元格区域，然后按住【Ctrl】键不放，再用鼠标分别选中B9:E15和G3:I9单元格区域，选中之后释放【Ctrl】键。

（2）在编辑栏中输入【武汉加油】，然后按【Ctrl+Enter】组合键，效果如图2-20所示。

图2-19

图2-20

2．在指定的单元格区域的多个空白单元格中批量录入相同内容

（1）若要在图2-21所示的3个单元格区域的空白单元格中批量录入文字【武汉加油】，则先选中A1:C7单元格区域，然后按住【Ctrl】键不放，用鼠标分别选中B9:E15和G3:I9单元格区域，选中之后释放【Ctrl】键。

图2-21

（2）按【Ctrl+G】组合键，在弹出的【定位】对话框中单击【定位条件】按钮，在弹出的【定位条件】对话框中选中【空值】单选按钮，单击【确定】按钮，如图2-22所示。

（3）在编辑栏中输入【武汉加油】，然后按【Ctrl+Enter】组合键，效果如图2-23所示。

图2-22

图2-23

上述两个案例均为定位功能的应用。定位功能非常强大，可用于定位空值（空白单元格）、常量、公式数字、公式逻辑值、可见单元格、条件格式等。通过定位功能指定单元格后，可以实现批量填充内容、批量删除内容、批量设置单元格格式、批量删除批注等操作。

技巧017　隐藏敏感数据

扫码看视频

应用场景

在工作中难免遇到一些敏感信息，诸如报价信息、采购价格、成本信息等。这些信息往往不希望被他人看到。若采用工作表加密的方式来保护这些信息，只能控制包含敏感信息的表格不被他人打开，但是在编辑、浏览这些信息时难免会被走过、路过的人瞄到。本技巧将帮你实现在编辑敏感数据的同时隐藏数据，如果操作迅速，即使操作人自己也看不清相关数据。

图2-24所示为一个产品数量和价格的统计表，下面通过单元格格式设置来隐藏单价信息。

图2-24

解决方案

（1）选中D2:D8单元格区域，单击鼠标右键，在弹出的快捷菜单中选择【设置单元格格式】命令。

（2）弹出【设置单元格格式】对话框，在【数字】选项卡的【分类】列表框中选择【自定义】选项，在【类型】文本框中输入3个英文分号“;;;”，如图2-25所示。

（3）单击【确定】按钮退出对话框后，发现单价信息全部被隐藏了，并且当再次录入或修改单价信息，按【Enter】键后，【单价】列单元格中仍然不显示任何内容，如图2-26所示。

图2-25

图2-26

本技巧设置的效果实质是把单元格中的信息隐藏起来，但该信息仍然存在于该单元格中。选中该单元格，可以在编辑栏内看到该单元格中的内容。同时，该单元格可以继续参与相关计算，并且不会影响计算结果。

技巧018　根据日期自动获取对应的星期

扫码看视频

应用场景

在录入数据表时，有时需要在录入日期数据的同时再录入对应的星期。若人工进行查找、填写，不仅工作量大，而且效率低下。其实，通过设置单元格格式可以轻松实现日期数据对应的星期或月份的自动填写。

解决方案

（1）如果希望在【中文星期】列中填写【日期】列数据对应的星期，则先在C2单元格中输入公式【=B2】，即调取【日期】列中的日期数据。然后拖曳C2单元格的填充柄至C7单元格，复制该公式，效果如图2-27所示。

（2）选中C2:C7单元格区域，单击鼠标右键，在弹出的快捷菜单中选择【设置单元格格式】命令，弹出【设置单元格格式】对话框，在【数字】选项卡的【分类】列表框中选择【自定义】选项，在【类型】文本框中输入【aaaa】，如图2-28所示。

图2-27

图2-28

（3）单击【确定】按钮，设置后的效果如图2-29所示。此后，若修改B列的日期数据后，C列的星期数据也会随之改变。

（4）若需显示对应的英文月份，只需在步骤（2）的【设置单元格格式】对话框中，将【类型】文本框中的【aaaa】修改为【mmmm】。设置后的效果如图2-30所示。

图2-29

图2-30

（5）若需显示对应的英文星期，也只需在步骤（2）的【设置单元格格式】对话框中，将【类型】文本框中的【aaaa】修改为【dddd】。设置后的效果如图2-31所示。

本技巧应用的关键是3个字母序列:aaaa、mmmm、dddd。其中，aaaa表示中文的星期（星期一到星期日），mmmm表示英文的月份（January到December），dddd表示英文的星期（Sunday到Saturday）。把字母的重叠数量分别调整为1个、2个、3个，可获得更多的设置效果，如aaa代表简写的星期日到星期六，m代表不带前导零的中文月份，mm代表带前导零的中文月份（01～12），mmm代表英文的月份简写（Jan到Dec），d代表不带前导零的日（1～31），dd代表带前导零的日（01～31），ddd代表简写的英文星期（Sun到Sat）。具体显示效果如图2-32所示。

图2-31

图2-32

技巧019　高效录入编码

扫码看视频

应用场景

在编制各种产品、设备等统计表的过程中，经常会涉及一些自定义的编码，如GH-020、FGT-0031、KT-024等。在录入或修改这些编码的操作中，需要反复切换输入法并配合半字线（-）和数字的录入，如果编码的数字位数较多，如GD-000001，连续录入类似的多行数据后会让人抓狂。本技巧可帮你摆脱录入此类编码的烦恼。

解决方案

（1）选中图2-33中的B2单元格，单击鼠标右键，在弹出的快捷菜单中选择【设置单元格格式】命令。

图2-33

（2）弹出【设置单元格格式】对话框，在【数字】选项卡的【分类】列表框中选择【自定义】选项，在【类型】文本框中输入【"GH-"0000】，如图2-34所示。

（3）单击【确定】按钮，退出对话框后在B2单元格中输入编码中的数字部分，如对于编码“GH-0021”，只需要在B2单元格中输入【21】，按【Enter】键后，B2单元格中显示的结果为GH-0021,如图2-35所示。

图2-34

图2-35

拓展应用　统一部分规则，实现局部高效录入

本技巧应用的局限在于编码规则的不确定性，如果编码既有GH开头的，又有TF开头的，甚至还有其他不同的编码规则，就需要重新设计解决方案了。

例如，在图2-34的B列（【编码】列）左侧插入两个辅助列——【编码前缀】列和【编码数字】列，其中，【编码前缀】列负责存放编码的前缀部分，如GH、TF等；【编码数字】列负责存放编码中的数字部分，如0021、0307等。在D2单元格中输入公式【=B2&"-"&TEXT(C2,"0000")】，此后再输入编码时，只须要在B列中输入编码前缀，在C列中输入编码数字，就可以在D列中自动生成编码了，如图2-36所示。公式中的“0000”表示编码数字由四位数组成，如果希望编码数字是两位数或六位数，则将“0000”修改为“00”或“000000”即可。

图2-36

本案例展示的是如何高效录入。通过此案例，读者可发现，需求的改变势必影响设置的改变，录入的内容规律性越强，设置越简单；规律性越差，设置越复杂，同时还引发更多的录入操作。想提高录入效率，需尽可能地统一规则，即便其中含有多个规则，也可以把相同规则的部分放在一起，实现局部高效录入。