作者：李云龙
出版社：清华大学出版社
格式: AZW3, DOCX, EPUB, MOBI, PDF, TXT
绝了！Excel可以这样用——数据处理、计算与分析(光盘内容另行下载，地址见书封底)试读：
前言
本书是为提升读者的Excel应用水平而写，如果您已经是一个Excel高手，那么本书可能不适合您。本书也是为了提高读者的工作效率而写，如果您在工作中经常需要和数据打交道，经常需要使用Excel处理、计算与分析数据，那么本书会非常适合您。
有时候很多读者都有一种感觉，似乎Excel很简单。真的如此吗？那么随便列举几个Excel使用的例子检测一下：
如何在单元格中录入分数、上下标、特殊符号、身份证号码、银行卡号？
如何在单元格区域中快速填充指定的序列？
如何按照特定的序列排序？
如何按照多个条件筛选数据？
如何按照多个条件统计数据？
……
还有，您会函数吗？请问函数“=IF（3，6，9）”的返回值是多少？为什么？
如果连这些看起来既简单又常用的操作您还不会，那么说明您真的需要好好学习一下Excel了。本书中涉及的内容大多是这种看起来简单，但如果没有相当时间的Excel使用经验积累却无法解决的问题。当然，掌握了本书内容，对您来说最大的实惠便是工作效率的极大提升，自然也意味着会为您节省大量的时间。这些时间，您爱干嘛就干嘛了！
诚然，很多用户已经意识到提高Excel应用水平的重要性，但面对市场上海量的Excel书籍无所适从。市场上的Excel书籍主要有大全类、技巧精粹类和行业应用类，各有特色但都不适合初学者学习。
技巧、函数和数据透视表是Excel中重要的组成部分，本书的结构正是围绕这些内容展开。书中先后讲解了表格制作管理的理念、数据的录入技巧、单元格自定义格式、数据有效性、条件格式、分列、删除重复项、排序、筛选、高级筛选、分类汇总、合并计算、数据透视表等操作技巧，在实际工作中灵活应用可以达到事半功倍的效果。
本书第2篇介绍了Excel中最精华的函数部分，从函数公式的基本概念讲起，以最常见的查找引用、日期时间和计算类函数为主线，介绍了日常工作中常用的函数及其嵌套应用，力求函数公式与操作技巧之间无缝衔接，认真阅读一定会让读者的操作水平有质的提升。
本书立足实战，从初学者的角度考虑问题，从最基础的应用讲起，不是帮助软件的翻版，不是介绍孤立的Excel技巧、更不是一步步教读者做表。从改变工作习惯开始、以规范数据源表为起点，融技巧讲解于常规操作之中。既授人以鱼，更授人以渔。
本书以目前使用最广泛的Excel 2010版本写作，内容兼容Excel 2007，函数部分基本兼容Excel的所有版本。但需要注意的是，Excel 2010的操作界面和Excel 2007之前的版本（如Excel 2003）会有差异。
本书特色
1. 视频教学
为了帮助读者高效、直观地学习本书内容，本书重点内容都专门录制了配套的多媒体教学视频，这些视频和本书涉及的其他资料一起收录于本书配套光盘中。
2. 贴近实战
本书举例全部来自实际工作中的案例，力求最大限度提升读者的工作效率，会成为实际工作中的最佳帮手。
3. 深入浅出
注重对于基础知识、基本概念的讲解，也不回避进阶应用。本书语言通俗易懂、操作步骤清晰，所介绍的实例都是作者精心选取和亲自操作过的，每个实例的完成还需要读者按照步骤来操作和认真思考。
4. 图文并茂
本书对于一些较复杂的函数公式配备了图解分析，通俗的文字加上配图的演示，让读者很轻松愉快地学习Excel。
5. 授人以渔
本书并非单纯讲解技巧，更重视思路的讲解及操作理念的传授。在理解的基础上，读者对示例文件略作修改就可以应用到实际工作中去。
本书内容及体系结构
第1篇：管理数据（第1～3章）
本篇主要内容包括：数据管理的基本理念、表格的分类与管理方法、数据源表的制作、数据录入与编辑的技巧等。通过本篇的学习，读者可以培养好的制表习惯，掌握一些常见的Excel操作技巧。
第2篇 计算数据（第4～8章）
本篇主要内容包括：函数公式的基本概念，日常工作中最常用的查找引用、日期时间、计算类函数及其嵌套应用。通过本篇的学习，读者可以掌握使用频率最高的几十个函数及其常见的嵌套应用。
第3篇 分析数据（第9～11章）
本篇主要内容包括：排序、筛选、高级筛选、合并计算、分类汇总、数据透视表等数据分析工具的讲解及其综合应用。通过本篇的学习，读者可以掌握常用的数据分析工具，并对于日常工作中如何提升工作效率有一定的认识。
本书读者对象□ 职场中的Excel用户；□ 工作中需要经常和数据处理打交道的人员；□ 工作中需要经常和数据计算与分析打交道的人员；□ Excel初学者；□ 有一定基础的Excel爱好者；□ 大中专院校的学生；□ 社会培训班学员。
本书作者
本书由李云龙主笔编写。其他参与编写的人员有陈世琼、陈欣、陈智敏、董加强、范礼、郭秋滟、郝红英、蒋春蕾、黎华、刘建准、刘霄、刘亚军、刘仲义、柳刚、罗永峰、马奎林、马味、欧阳昉、蒲军、齐凤莲、王海涛、魏来科、伍生全。
您在阅读本书的过程中若有疑问，请发E-mail和我们联系。E-mail地址：bookservice2008@163.com。
第1篇 管理数据
第1章 成功，从改变习惯开始
根据笔者的工作经验，很多加班制作Excel表格的用户，工作效率普遍是低下的。工作效率低不仅表现在技能欠缺，工作习惯往往也有问题。改变习惯是成功道路上迈出的第一步，往往也是最重要的一步。在本章中将带领大家一起敞开心扉，吸收新的理念，从改变基本的操作习惯开始，一起走上成功之路。
1.1 一些常用的操作习惯
很多用户在使用Excel上操作习惯大有问题。例如，Excel界面千人一面、找不到2003版的命令、经常使用的文件找不到、Excel工作表不会隐藏和保护。
这些看似都是小问题，实际上不仅影响工作效率，甚至还会影响到正常的工作。工欲善其事，必先利其器，要提高工作效率就应先从改变操作习惯开始。本节将分享一些常用的操作习惯，为什么会形成这些操作习惯呢？源于偷懒，源于不满足已知的所谓的常规操作，源于对更高工作效率的追求。1.1.1 DIY自己的功能区
自2007版开始，微软在Office软件中使用了全新的Ribbon功能区，分“选项卡”、“组”、“命令”三个层次，完全不同于2003版的工具栏、菜单栏模式。在2010版中，微软开放了有限制的自定义功能，用户可以在功能区中加入一些自己常用的命令按钮，进而提高工作效率。
用户可以自己定义的区域分两部分：“自定义功能区”和“快速访问工具栏”。
1. 自定义功能区
举一个简单的例子，如图1.1所示，把黄色区域公式计算得到的数值粘贴到绿色区域中。图1.1 粘贴数值
如完全使用默认的功能区命令，操作步骤将有5步之多，具体如图1.2所示。（1）选中C2：C6区域。（2）单击“开始”|“剪贴板”|“复制”按钮。（3）选取F2单元格。（4）单击“开始”|“剪贴板”|“粘贴”下拉箭头。（5）单击“粘贴值”按钮。图1.2 选择性粘贴数值
如用户在第5步的时候不知道可以直接单击“粘贴值”按钮，单击“选择性粘贴”，将弹出“选择性粘贴”对话框，如图1.3所示，这样操作步骤将会变成7步。图1.3 选择性粘贴对话框
不论5步还是7步，都有些烦琐。如果这项操作（公式运算结果数值化）经常发生，我们就有必要做一些改变。在2010版中提供了自定义功能区的功能，我们可以在自定义功能区中找到“粘贴值”这个命令，然后添加到选项卡中。
因Excel对于这项操作设置了“只能向自定义组添加命令”的限制，需先在需要的选项卡中“新建组”。
1）新建组
单击“文件”|“选项”命令，弹出“Excel选项”对话框，如图1.4所示，在“开始”选项卡中单击“新建组”按钮。图1.4 Excel选项
此时将在“开始”选项卡的最下面生成一个，如图1.5所示。对新建组重命名为“常用”，并调整其在“开始”选项卡中的位置，上移到“剪贴板”组的下方。图1.5 重命名组
操作完成效果如图1.6所示，并在功能区的“开始”选项卡下形成了一个空白的“常用”组。图1.6 “开始”选项卡下新建“常用”组后的效果
2）添加命令
下一步要做的工作就是找到“粘贴值”命令，插入到“开始”选项卡下的“常用”组中。单击“文件”|“选项”命令，弹出“Excel选项”对话框，在“自定义功能区”选项中选择“不在功能区中的命令”，然后在下拉列表中找到“粘贴值”（按命令的拼音字母顺序排列，如“粘”字是Z开头，排列较靠后），选取“开始”选项卡下的新建的“常用”组，最后单击“添加”按钮，操作如图1.7所示。单击“确定”按钮完成退出。图1.7 功能区中插入命令
完成之后在“自定义功能区”中的显示如图1.8所示。
添加“粘贴值”命令之后，如再做类似的将公式计算得到的数值粘贴到其他区域的操作，在完成前3步后，第4步直接单击“粘贴值”按钮，即可完成。图1.8 添加“粘贴值”命令后的效果
职场点睛：不要小看这几步操作步骤的减少，如对日常工作中常用的操作都能做到类似的步骤优化，积少成多，操作效率必将大幅度提升。
2. 自定义快速访问工具栏
Excel中常用的命令除了可以放在功能区中，还可以放在“自定义快速访问工具栏”中。比如“数据透视表和数据透视图向导”对话框，在2007以后的版本中被隐藏起来了，为了使用便捷，可以把这个命令找出来放在“自定义快速访问工具栏”中。
单击“文件”|“选项”命令，弹出“Excel选项”对话框，在“快速访问工具栏”选项中选择“不在功能区中的命令”，然后在下拉列表中找到“数据透视表和数据透视图向导”，单击“添加”按钮，操作步骤如图1.9所示。图1.9 添加到快速访问工具栏
完成所有操作之后，即在“快速访问工具栏”中最后添加了“数据透视表和数据透视图向导”按钮，如不满意按钮次序，可进入“Excel选项”后自行调整其在工具栏中显示的位置。
职场点睛：通过对“自定义功能区”和“快速访问工具栏”的设置，Excel程序界面将变得与众不同，可以任意快速调用那些被微软隐藏起来的命令，提高工作效率从DIY自己的功能区开始。1.1.2 管理常用的Excel文件
在日常工作中，可能有一些文件使用的频率会比较高，有些文件甚至是天天使用。但这些文件往往分布在不同的目录下，查找并不方便。
在Excel 2010中提供了将常用文件固定在文件列表中的功能，便于快速查找使用。
单击“文件”|“最近所用文件”命令，Excel会列出“最近使用的工作簿”，最多显示25个工作簿，如图1.10所示。图1.10 最近使用的工作簿
比如对于工作簿《上海各公司情况》每天都会用到，如此时打开多个工作簿，《上海各公司情况》工作簿的位置就会下沉。我们可以单击文件名右侧的按钮，此时工作簿《上海各公司情况》的位置就会上提，并更改图标显示为，如图1.11所示。今后再打开其他工作簿也不会影响《上海各公司情况》工作簿在列表中的位置。图1.11 列表中锁定工作簿
职场点睛：学会这一招，可以快速地找到日常工作中常用的工作簿，再也不需回忆文件存放的位置了。1.1.3 冻结拆分窗格
笔者认为这属于一个最最基本的操作习惯，但在实际工作中，很多人都没有此方面的意识，如图1.12所示。图1.12 未冻结表头窗格的表格
这样的表格看得很费力，比如L～N列都是数字，但都是些什么数字呢？屏幕向上滚动时能看到表头，但向下滚动时，表头又不见了。
其实Excel提供了一个冻结窗格的功能，可以按照用户的设定冻结表头及左边的列。如图1.13所示，将表格滚动到最上方，选中F2单元格，然后单击“视图”|“窗口”|“冻结窗格”命令，在下拉菜单中选择“冻结拆分窗格”命令。图1.13 冻结拆分窗格
之后再向下、向右滚动窗格，可以发现第1行的表头及左边的A～E列将一直显示，如图1.14所示。图1.14 冻结拆分窗格后的表格
Excel冻结拆分窗格的规律为冻结住选中单元格上方的行和左方的列，读者可以自行测试一下不同的冻结，加深对此操作的理解。
职场点睛：学会这一招，可以让表格的标题行一直显示。自己看得清楚，其他用户也清楚，尤其是会议中做大屏幕演示时更加有用。1.1.4 保护工作表
在日常工作中，有时候需要对工作表加以保护。保护工作表并非对他人不信任，更多时候是为了防止误操作。以笔者的工作为例，经常要设计表格，然后下发给其他财务人员填列。如不加保护，往往会发生表格结构被更改，设置的公式被改动的情况。
如图1.15所示，黄色区域中已预先设置了公式，需要达成的效果是仅绿色区域中可以输入数字，其他单元内容都不能改动。图1.15 设置公式保护
操作步骤如下：（1）选取绿色单元格区域，按Ctrl+1快捷键调出“设置单元格格式”对话框，单击“保护”选项卡，取消“锁定”复选框中的勾选，单击“确定”按钮退出，如图1.16所示。图1.16 取消“锁定”复选框的勾选（2）单击“审阅”|“更改”|“保护工作表”命令，弹出“保护工作表”对话框，输入保护密码如“123”并再次输入密码确认后，即完成工作表的保护，如图1.17所示。图1.17 保护工作表
此时仅有绿色区域能输入内容，如改动其他单元格，则会弹出如图1.18所示的提示框。图1.18 试图更改受保护单元格时的提示框
保护工作表中公式的操作步骤：□ 先改变工作表中各区域的单元格格式，考虑是否需要取消“设置单元格格式”对话框中“保护”选项下“锁定”复选框的勾选。如勾选，保护工作表后，相关单元格区域就不能再编辑了。□ 保护工作表。
关于保护工作表时还有很多选项，如图1.17所示有很多复选框可选择，读者可自行测试一下这些选项的作用。
职场点睛：如果下属不知道应该如何做，那设计好表格发下去，规定填列的区域（也只能填这些区域，其他地方动不了），尝试一下这种工作方式！1.1.5 隐藏工作表
在实际工作中，经常碰到对外发送的报表中有些工作表不希望被别人看到的情况。有时候是出于保密的考虑，有时候是为了避免不必要的麻烦。
彻底删除自然很简单，但如果没有保密的考虑，隐藏工作表也是一种选择。这里介绍两种常用的隐藏工作表的方法。
1. 常规隐藏
如图1.19所示，工作簿中有4个工作表，在文档发出时，需要隐藏工作表《价目表》。图1.19 工作表标签栏
选中《价目表》工作表，按右键，在弹出的快捷菜单中选择“隐藏”命令，如图1.20所示。图1.20 常规隐藏工作表
此时在工作表标签栏中已看不到被隐藏的工作表，如图1.21所示。图1.21 隐藏工作表《价目表》后的显示
这样操作虽然很简单，但在右击弹出的快捷菜单中“取消隐藏”命令非灰色，其他用户只要选中“取消隐藏”命令，即可轻松恢复已隐藏的工作表，如图1.22所示。图1.22 取消隐藏工作表
2. 深度隐藏
为了防止其他用户轻松恢复隐藏的工作表，这里介绍一种多数人尚不知道的方法——深度隐藏，操作步骤如下：（1）按Alt+F11快捷键进入VBE编辑器，确保“工程资源管理器”和“属性窗口”都处在打开状态，如图1.23所示。图1.23 VBE编辑器窗口（2）在VBE工程窗口中选取“Sheet3（价目表）”，然后在VBE属性窗口中对Visible属性改变取值为“2–xlSheetVeryHidden”，如图1.24所示。图1.24 更改工作表的Visible属性
此时工作表标签栏中没有《价格表》工作表，在右击弹出的快捷菜单中“取消隐藏”命令为灰色不可用，如图1.25所示。图1.25 深度隐藏工作表后的状态
在深度隐藏之后如自己需要查看已隐藏的工作表，可以按上述操作步骤，将已隐藏工作表的Visible属性改回“-1–xlSheetVisible”即可。
如果对这样的效果还不放心，可以继续设置VBE工程密码。操作步骤为单击“工具”|“VBAProject属性”命令，在弹出的“VBAProject属性”对话框中选取“保护”选项，设置密码。但该操作需在VBE编辑器中存在已插入模块时有效，有兴趣的用户可自行测试一下。
职场点睛：在深度隐藏之后，普通用户是想不到在工作簿中存在着隐藏的工作表，这样就可以在不删除工作表的同时起到了较好的保护效果。当然这样的保护还不是非常可靠，如涉及商业机密，建议保存备份后直接删除相关工作表。
类似的好的操作习惯还有很多，读者可以根据自己的工作内容选择性地吸收，最重要的是不要满足于已有的操作习惯，学会偷懒，只有对Excel的要求高一些，Excel才会用强大的功能来回报用户的需求。
本节示例文件：《1.1 一些常用的操作习惯.xlsx》。
1.2 走出数据源表制作误区
改变工作习惯仅是一个开始，从事数据分析工作如没有合适的数据表必将寸步难行。
本书中将数据分析中使用的基础数据表称为“数据源表”。那什么是“数据源表”？“数据源表”又来自何处？每个用户所处的情况各不相同，“数据源表”可能出自ERP系统，可能出自财务软件，也可能完全是手工输入维护的。
很少有文章讨论“数据源表”的管理，很多用户也不明白“数据源表”管理的重要性。现状就是数据资料的混乱，比如对应一项数据指标往往有若干个表格，真正做分析时又没有一个管用。结果就是不断做重复的工作、加班加点成为常态。
本节将介绍“数据源表”制作及管理的一些基本理念，介绍一些用户常犯的错误，带领大家走出“数据源表”制作误区。1.2.1 理清电脑中的4大类表格
每个用户的电脑中都有大量Excel文件，这些文件来源不同，用处也各不相同，管理大量的Excel文件是一件令人头痛的事情。笔者根据自己的工作经验，将电脑中与工作相关的表格分成4大类，即原始数据表、数据源表、计算分析表、结果报告表。电脑中4大类表格及其逻辑使用过程如图1.26所示。图1.26 电脑中的4大类表格□ 原始数据表：客户提供或自行编制的原始数据，表格格式应尽量接近于分析所需数据源表的要求，一般需加工后才能用于数据分析；□ 数据源表：客户提供或自行编制的用于数据分析的基础数据表，对于表格的格式有一定的要求，不对外报送；□ 计算分析表：自行编制的分析用工作底稿，一般不对外报送；□ 结果报告表：对外提供的分析结果报表，格式无要求。
备注：上述4大类工作表的提法属于笔者原创，读者也可以根据自己的习惯自行命名。
这4大类表格的地位会根据其所处位置而变化。比如客户提供的表格，对客户而言就是结果报告表，而对于报告使用者来说，可能是数据源表，也可能仅仅是原始数据表。
即使对于同一个用户的同一份表格也可能因不同的工作内容而处在不同的位置，比如A工作中的数据源表，在B工作中可能仅是原始数据表。
我们分析一份表格的设计是否合理，首先要界定这份表格在其使用者电脑中的身份地位，对于原始数据表、数据源表、结果报告表的格式要求是不一样的。
下面通过一个案例来理解一下表格的分类及编制要求。
1. 忙碌的销售助理
某企业从事化妆品的生产与销售，在全国各地有数十家直营专柜，分别由6位业务专员负责。业务专员在每天上午10点之前提供前一天各人分管专柜的销售日报表给公司的专柜销售助理，日报表格式统一如图1.27所示。图1.27 商场销售日报表
专柜销售助理收到日报表后，将文件名统一为“业务员名字-日期”的格式，比如2011年12月1日收到业务员A上报的日报表，即将文件名改成“业务员A-2011.12.1”，然后存放在硬盘中按业务员姓名、年、月设置的文件夹下，比如业务员A在2011年12月1～5日上报的文件全部在“专柜销售日报表——业务员A——2011年——12月”这个文件夹下，如图1.28所示。图1.28 销售助理电脑中的文件夹
这位销售助理自我感觉良好，觉得表格格式统一规范，文件整理得井井有条。不知道读者以为如何？
实践是检验真理的唯一标准，这位销售助理在实际工作中经常加班做表，分析专柜的销售数据。说起其加班的原因，总是抱怨老板的要求多变，分析的要求不一致。比如这个月要看区域的销售情况，下个月要根据业务员分析，有时候看总的销售额变动，有时候又要分析促销的情况。反正每次要求都不一样。
看到这里，读者可以先行思考一下，看看问题出在什么地方，应该如何解决。
2. 理解4大类表格
读者如果没有具体从事过数据分析工作，很难指出上述表格体系存在的问题。其实这是一个很典型的“数据源表”缺失的例子。
先看一下业务专员提供的销售日报表（如图1.27所示），格式是统一规定的，不能说业务专员每天上报这样的表格有问题。
再看一下销售助理的文件管理方式（如图1.28所示），文件统一格式命名，按业务员姓名、年、月设置文件夹来存放，虽非最佳方案也不能说有大错。
问题在于管理层要看的资料并不是业务员提供的单个销售日报表，要看的是汇总的分析资料。而根据已有的资料简单汇总，不能满足管理层的需要。
我们看到的在销售助理电脑中一份份的销售日报表对于提供者业务专员来说是其“结果报告表”，但对于销售助理来说，仅是其从事专柜销售分析工作的“原始数据表”，并没有可直接用于分析的“数据源表”。其工作的实质是每次接到新的分析任务后根据“原始数据表”临时组织“数据表”用于当次的分析，再据以编制“结果报告表”，其工作流程如图1.29所示。图1.29 销售助理的工作流程
比如需要分析东区专柜2010年与2011年按产品大类的销售分析，销售助理就会做出如图1.30的表格。图1.30 分年度、大类的销售额比较表
而为了做上图的表格，需要整理如图1.31的数据表，如数据不多，甚至会用计算器及手工计算填列图1.30的表格。
如下次分析的要求改为分析东区专柜2010年与2011年参加促销的产品销售额比较情况，就会发现上次整理的如图1.31的数据表已完全无用，需要再重新组织含促销金额的数据表，如图1.32所示。图1.31 临时组织的数据表1图1.32 临时组织的数据表2
根据以上分析，读者应可理解这位销售助理工作效率低下，经常加班的原因了。在于没有一个可通用于管理层对于专柜各项数据分析要求的“数据源表”。
知道了问题之所在，接下来就看看到底应该如何做。
一个理想的“数据源表”应该只有1张工作表，而非本例中多个文件夹下的多个工作簿。当然这只是一个理想的状态，在实际工作中，我们可以有两种方法来达到这种理想状态：□ 可以通过合理地组织“原始数据表”，使其达到或接近“数据源表”的要求；□ 对“原始数据表”及时汇总加工，自行打造“数据源表”。
这两种方法一般需要结合使用，本例中需要先改变“原始数据表”的格式，使其达到“数据源表”的要求，然后再对“原始数据表”加以汇总。1.2.2 汇总数据到一个工作表
假定每位业务专员提供的表格都符合销售助理分析所需的“数据源表”的要求，但这些表格分散在多层文件夹下的多个工作簿中（如图1.28所示），每个工作簿中又有多个工作表，如图1.33所示。图1.33 工作簿中的若干个工作表
汇总这么多个工作簿中的数据，不是一件容易的事情。一个一个复制粘贴的方法自然无需考虑，使用函数、透视表还是VBA？
对于普通的Excel用户，不管用什么方法都不容易。并非Excel能力有限，问题出在原始数据的组织上，原始数据人为地放在多个工作簿中是将简单的问题复杂化了。
1. 从多个工作表到一个工作表
本例中，如增加一个字段“商场”，则每个业务员每天只需报送一张工作表，而非之前在一个工作簿中存放若干个工作表，增加“商场”字段后的《商场销售日报表》，如图1.34所示。
2. 从多个工作簿到一个工作表
再增加一个“日期”字段，业务员报送的资料将从《商场销售日报表》变成《商场销售日报表汇总》，如规定将2011年12月这一个月的数据放在一个工作表中，销售助理的“专柜销售日报表——业务员A——2011年——12月”文件夹下将只有一个文件，文件中只有一个工作表。增加“日期”字段后的《商场销售日报表汇总》，如图1.35所示。图1.34 从多个工作表到一个工作表图1.35 从多个工作簿到一个工作表
3. 从多个文件夹到一个工作表
还可以继续整合，比如将每个业务员1年的数据放在1个工作表中，因自2007版开始Excel的可用行已从2003版的65 536行增加到1 048 576行，不用担心数据放不下。
再增加一个“业务员”字段（也可以在汇总“原始数据表”时加），然后整合需分析的年、月数据，这样将原来多层文件夹下多个工作簿中的数据存放在一张工作表中，增加“业务员”字段后的《商场销售日报表汇总》，如图1.36所示。1.2.3 补齐分析必需的字段
经过以上调整，每个业务员每年仅提供一张表格，但这张表格离数据源表的要求还有距离。图1.36 从多个文件夹到一个工作表
回顾一下销售助理的困惑：“老板的要求多变，分析的要求不一致。比如这个月要看区域的销售情况，下个月要根据业务员分析，有时候看总的销售额变动，有时候又要分析促销的情况。”
对应管理层的分析要求，图1.36表格中已有“业务员”、“销售额”、“促销”等资料，尚缺“销售区域”资料，如东区、南区、西区、北区之类的，增加“区域”字段后的《商场销售日报表汇总》如图1.37所示。图1.37 增加销售区域字段
在增加字段时应有一定的前瞻性，管理层虽然没有提出按“省份”分析的要求，但考虑部门组织架构，今后完全有可能提出。在准备数据源表时，可以把“省份”字段加上去，其他字段的要求以此类推。增加“省份”字段后的《商场销售日报表汇总》如图1.38所示。图1.38 增加销售省份字段1.2.4 规范字段设置
对于如图1.39的表格，已经具备了管理层分析所要求的所有字段元素，但如以此表格直接进行分析，还是存在很多问题，比如红色框线标注部分。图1.39 需规范的字段
分析一张表格设计是否合理，首先要看这张表格的身份地位，看其属于4大类表格中的哪一种。上述表格红色框线部分如作为“结果报告表”中的一部分则不能简单评论其优劣，也许报表的使用者就是喜欢这种格式，但如作为数据源表的一部分，就有下述缺陷：□ 字段记录缺失，在红色框线中有很多空格；□ 有两层字段名称，第2层字段名称（如“正常销售”、“公司促销”、“商场促销”等）本身就是第1层字段“销售方式”下的属性。
如不存在第2个问题，仅需处理第1个字段记录缺失的问题，通常的方法是将空格部分全部用其他符号（如“×”）填充，操作步骤如下：（1）选中L5：N14区域。（2）按F5键调出“定位”对话框，单击“定位条件”按钮，在弹出的“定位条件”对话框中单击选取“空值”，单击“确定”按钮退出，如图1.40所示。图1.40 定位区域中的空值（3）输入“×”符号，按Ctrl+Enter组合键结束。
以上是快速填充空白区域的操作，不仅可以填充符号，还可以填充数值和公式，涉及的操作会在后续章节中继续介绍。
这个例子关键在于字段设置不规范，把字段的属性值作为了字段名，解决的方案是把属性值放到该放的位置上。本例中就用“销售方式”作为字段，原第2层字段名称（如“正常销售”、“公司促销”、“商场促销”等）作为“销售方式”字段的属性，修改后的《商场销售日报表汇总》如图1.41所示。图1.41 规范字段设置后1.2.5 去除多余的表格名称
作为对外报送的表格，很多用户的习惯是设置一个表格名称，如图1.42所示，便于表格使用者了解其阅读表格的主题。图1.42 常用的表格名称
但如表格的用途是“数据源表”，则表格名称不仅没有必要（不对外报送，仅是用户自己使用），而且还会给数据分析带来麻烦。比如我们使用SQL语句查询其他工作表的数据，Excel默认工作表中第1行为表头，但本例中表头在第3行。
处理的方法很简单，把表格名称行删除，让工作表的第1行起就是表头。去除表格名称后的“数据源表”如图1.43所示。图1.43 去掉表格名称后的“数据源表”1.2.6 使用单层表头
作为对外报送的“结果报告表”，只要能把问题表述清楚，报表使用者能够接受，表格形式并不是关键，但作为“数据源表”就不同了。因Excel默认工作表中第1行为表头，故“数据源表”应该使用单层表头。
图1.43表格中存在双层表头，还用到了合并单元格，如取消合并单元格设置，结果如图1.44所示。图1.44 取消合并单元格后的“数据源表”
可以发现红色框线区域单元格中内容为空白，原因在于合并单元格的值可能仅存在于合并区域中的某一个单元格（一般为左上角单元格）。这个例子中I1和J1单元格之前是合并在一起的，但其中只有I1单元格有内容。
如不对多层表头加以处理，Excel默认使用第1行为表头，会造成在J列的字段名称缺失（J1单元格为空），而且I列使用“单价”作为字段名，并不符合这个数据源表的实际情况，实际需要的字段名应为“零售价”。
处理的方法是使用单层表头，不论是多层表头还是斜线表头，全部取消，修改表头设置后的“数据源表”如图1.45所示。图1.45 修改表头设置后的“数据源表”1.2.7 禁用合并单元格
在“数据源表”中是禁用合并单元格的，原因有很多，最重要的一点在于合并区域中部分单元格取值为空。比如合并如图1.46中的黄色区域，Excel在合并后仅保留区域中左上角单元格中的数据。图1.46 合并单元格时的提示
本例中G列存在合并单元格，取消合并单元格，并在空格中填充数据的操作步骤如下：（1）选取G2：G11单元格区域。（2）单击功能区中“开始”|“对齐方式”|“合并后居中”命令，取消已选取区域的合并单元格设置。（3）按F5键进入“定位”对话框，单击“定位条件”按钮进入“定位条件”对话框，单击选中“空值”，单击“确定”按钮后退出（参照图1.40），此时当前单元格为G3单元格。（4）输入“=G2”，代表当前单元格取其上一格的值，如图1.47所示。图1.47 填充合并单元格时的公式设置（5）按Ctrl+Enter组合键结束。
按上述5步操作之后，G列原先有合并单元格的地方变成了公式填充，如图1.48中M列所示。作为数据源表应将有公式的单元格全部数值化，否则如进行排序之类的操作，数据将会出错。图1.48 填充合并单元格后的效果1.2.8 删除多余的合计行
在“数据源表”中的没有必要保留“合计行”。有“合计行”在数据更新或者插入行后，有可能需要更新合计行的数字，在使用数据透视表等分析工具时，还会因“合计行”的存在造成分析结果错误。
解决的方案为删除“合计行”，删除“合计行”之后的“数据源表”如图1.49所示。图1.49 删除合计行之后的“数据源表”
职场点睛：在实际工作中如果每个业务员都能提供如图1.49所示的表格，销售助理仅需将各个业务员的表格简单汇总起来即形成了可满足目前管理层对于专柜数据分析需求的“数据源表”。这是一种通用的思路，对于实务中遇到的多个表格数据分析问题可以套用。当然对于字段，应根据业务特点结合管理层的分析要求予以增减。
本节示例文件：《1.2 走出数据源表制作误区.xlsx》。1.2.9 数据源表编制中的其他常见问题
1. 取消空行空列
在表格设计中很多人喜欢使用空行和空列，这个在“数据源表”的编制中属于大忌。图1.50为有空行的“数据源表”，如果中间没有空行隔断，鼠标放在任意单元格中，按Ctrl+A快捷键就可以选定全部数据区域，但是有空行隔断之后，就不能了。不仅是选取数据，在写公式时也一样，对于“数据源表”，保持数据之间的连续性很重要。图1.50 有空行隔断的“数据源表”
如数据源表中已存在整行空白，可运用定位的方法快速删除，步骤如下：（1）选取“数据源表”第1列中的A1：A12单元格区域。（2）按F5键进入“定位”对话框，单击“定位条件”按钮进入“定位条件”对话框，单击选中“空值”，单击“确定”按钮后退出（参照图1.40）。（3）右键快捷菜单中选择“删除”命令，在弹出的“删除”对话框中选取“整行”，如图1.51所示。图1.51 批量删除空行
对于空列的删除原理同批量删除空行，所不同的是第1步选中的是表头所在行的A1：L1单元格区域，第3步在“删除”对话框中选择“整列”，如图1.52所示，读者可自行测试一下。图1.52 有空列隔断的数据源表
2. 一格一属性
Excel在处理数据方面能力很强大，这个毋庸置疑。但有它自身的一套规则，Excel很看重数据属性，一是一，二是二。标准规范的是一个单元格记录一个属性。
图1.53左边所示就是不规范的记录，在一个单元格中既有数量又有不同的单位，右边为处理后规范的表格样式。图1.53 规范前后的数据表
即使单位完全一样，也不适合放在一个单元格中，因加了单位之后由数值变成了文本，不能直接通过简单的求和公式计算，如图1.54所示。图1.54 一格多属性对计算带来的麻烦
比较一下B6与E6单元格的公式，两者难度差异很大。E6单元格仅一个简单的求和公式：=SUM（E2:E5）
B6单元格需要输入一个数组公式，即按Ctrl+Shift+Enter组合三键结束的那种：=SUM（--SUBSTITUTE（B2:B5，"公斤"，））
B6单元格中的公式相对长了很多，初学者也难以理解。
说明：Ctrl+Shift+Enter组合三键是输入数组公式的方法。后面多处会简写为组合三键。
如果一定要在“数据源表”的单元格中显示单位，可通过单元格“自定义格式”解决，实现所见非所得，涉及内容会在之后的章节中详细讲解。
3. 使用正确的数值格式
如果在“数据源表”中涉及到日期的输入，一定要选择Excel认可的日期格式，图1.55中列出了几种常见的错误的日期格式。图1.55 错误的日期格式
日期是一种特殊的数值，使用了正确的日期格式，Excel可直接对于日期值执行计算，反之则不行。对于错误的日期格式，常规可通过分列的方法转换，我们会在之后的章节中详细讲解。
每个人电脑中默认的日期格式可能有所不同，和操作系统的版本及语言有关，如需更改默认设置，可在“控制面板”|“区域和语言”中修改默认格式，如图1.56所示。
本节示例文件：《1.2.9 数据源表编制中的其他常见问题.xlsx》。1.2.10 关于数据源表的一些总结
关于“数据源表”的设计已经介绍了很多，这里做一个简要的总结，一个理想的“数据源表”应是一个一维列表，需满足以下条件：图1.56 控制面板中的区域与语言设置□ 一张工作表；□ 有满足分析需要的字段；□ 没有表格名称；□ 单层表头；□ 没有空行空列；□ 没有合计行；□ 没有合并单元格；□ 没有空白单元格；□ 一格一属性；□ 数据格式正确。
如果我们在数据分析中有了这样一张“数据源表”，之后的数据分析工作将变得很轻松，数据透视表+常用的函数公式就能解决80%以上的问题。
1.3 动手编制数据源表
也许有的读者会认为上节中的举例与其工作无关，单位已经ERP上线，数据都可以从系统中导出，无需手工编制“数据源表”。实际工作中需要分析的数据可能不仅来源于系统，即使ERP已上线，功能非常完善，有些需要分析的数据还是不能从系统中取得。1.3.1 案例背景
举一个真实的例子，笔者的一个朋友跳槽到一家民营服装企业担任财务总监，这家公司近期有IPO的意向，该总监需要分析同行业上市公司近3年的财务指标，这些数据自然不可能从本企业的系统中导出。
上市公司的财务数据都可以在网络上搜索到，找到2010年、2011年及2012第一季度数据资料，每个公司一张工作表，如图1.57所示。图1.57 各公司资料汇总在一个工作簿中
需要分析的数据存放在每个工作表的4～17行，2010年数据在A：F列，2011年数据在H：M列，2012一季数据在O：T列，如图1.58所示。图1.58 工作表中需分析的财务数据1.3.2 数据源表准备
因数据分析的要求可能有多种，直接从各个工作表中取数并非最佳方案，可以先建立“数据源表”，一劳永逸地解决分析所需的数据问题。
1. 分析需求
经分析，可能涉及到的字段有“公司”、“年度”、“指标”、“指标值”4个字段，新建一张工作表《明细资料》，建立如图1.59所示的表头。图1.59 新建的“数据源表”表头
2. 统一原始数据表行列次序
检查如图1.57所示各个工作表的行次是否一致，确保需要分析的数据都在4～17行，列次也有类似的要求。
这一步很重要，因数据在多个工作表中，如表格的行列次序不一致，在复制链接取数时会出错。这里一个个数据的简单复制是不予考虑的。
3. 准备1个公司1年的数据
先准备“探路者”公司2010年的数据，操作步骤如下：（1）A2：A16单元格区域输入公司名称。（2）B2：B16单元格区域输入取数的年度。（3）C2：C16单元格输入需分析的指标名称。（4）D2：D16单元格链接指标的取值，结果如图1.60所示。图1.60 准备1个公司1年的数据
这里前A：C列都是手工输入或者复制粘贴，D列需要使用单元格链接，操作步骤如下：（1）选取D2单元格，输入“=”。（2）在工作表标签列表中单击《探路者》工作表。（3）在《探路者》工作表中选取需要取值的C5单元格，如图1.61所示。图1.61 跨工作表取值（4）回到《明细资料》工作表，选中D2单元格，在编辑栏中选取公式的“C5”部分，如图1.62所示。图1.62 编辑公式（5）按F4键，改变单元格的引用类型，使D2单元格公式变为“=探路者!$C$5”。
备注：关于单元格的引用类型将在以后章节中详细讲解，以上操作也可以通过“粘贴链接”的方式实现，具体根据各人的操作习惯。
其他单元格的操作以此类推，最终确保“指标值”字段公式如图1.60中E列所示。
4. 准备1个公司3年的数据
因2011年“探路者”公司数据与2010年数据在一张表中，行相同，列不同，只需复制2010年数据后更改列的链接即可，操作步骤如下：（1）选中《明细数据》表A2：D16区域，按Ctrl+C快捷键复制。（2）选中A17单元格，按Ctrl+V快捷键粘贴。（3）将B17：B31区域中的“2010年度”全部更改为“2011年度”。（4）选取D17：D31单元格区域，按Ctrl+H快捷键，弹出“查找和替换”对话框，将C列替换为J列（操作时可查找“探路者!$C”，替换为“探路者!$J”），单击“全部替换”按钮，弹出“提示框”，已完成8处替换，如图1.63所示。图1.63 替换公式链接（5）同理选取D17：D31单元格区域，将F列替换成M列，完成2011年数据准备。（6）重复上述1～5步，完成2012年一季度数据的准备。
5. 准备所有公司3年的数据
因之前已经确保所有公司数据的行列次序相同，且涉及到单元格链接的引用已全部更改为绝对引用，只需复制“探路者”公司的数据，粘贴后替换链接单元格所涉及到的工作表名即可，操作步骤如下：（1）选中《明细数据》表A2：D46区域，按Ctrl+C快捷键复制。（2）选中A47单元格，按Ctrl+V快捷键粘贴。（3）按Ctrl+H快捷键，调出“查找和替换”对话框，将“探路者”替换成“美邦服饰”，如图1.64所示（此步操作时须确保A47：D91单元格区域处在选中状态）。图1.64 替换公司名称（4）重复以上1～3步操作，完成所有公司3年数据的准备。
职场点睛：通过以上操作，可以轻松地编制用于后续分析的“数据源表”，之后的分析只需要在这个规范的一维列表中取数，不论是使用排序、筛选、分类汇总、数据透视表还是函数都会比直接在各个公司的工作表中取数方便很多。这也是一种常用的多个表格数据分析思路，读者可以在类似的工作中实践。
本节示例文件：《1.3 动手编制数据源表.xlsx》。
1.4 小结
本章从改变习惯开始，具体讲解了4大类表格的异同及“数据源表”制作及管理的基本理念。其实在数据分析的工作中，想到编制“数据源表”对于用户而言就是一个重要的改变。改变贯彻于整章的学习。
对于“数据源表”格式有一系列的要求，用户不要混淆“数据源表”与“结果报告表”，不要在“数据源表”中使用“结果报告表”中常用的格式，也不要把“数据源表”的编制要求误用到“结果报告表”的编制过程中。
对于“原始数据表”，最好格式能完全同“数据源表”一致，这样相当于把自己的工作要求前置。
第2章 数据录入的技巧
使用Excel处理数据，数据录入的问题无法回避。数据录入看似简单，人人都会，但实际上要想做到操作既快又好并不容易。本章介绍一些日常操作中常用的技巧，阅读之后，相信工作效率将有较大的提升。
2.1 轻松搞定特殊数据的录入
实际工作中，有些数据按常规方式录入并不能得到想要的结果，还有些数据根本找不到录入的方法，特殊数据的录入困扰了很多用户。本节将介绍长数字、分数、特殊符号、上下标等录入技巧。2.1.1 录入长数字
企业中的人事部门需要记录员工身份证号码，如在录入Excel文档时不加处理直接录入，结果往往会出人意料。
员工A的身份证号码为310109831226121 ，员工B的身份证号码为310107197711041790，如直接录入Excel，得到的结果如图2.1所示。图2.1 录入的身份证号码
员工A的身份证号码为15位，虽然显示的数值已改变为3.1011E+14，但输入的值并未改变。选中C2单元格，在编辑栏中可看到输入的数字仍为310109831226121，如图2.2所示。图2.2 录入15位身份证后得到的值
而对于员工B的18位身份证号码，Excel不仅将显示的数值改变为3.10107E+17，实际得到的值也非之前输入的内容。选中C3单元格，在编辑栏中可看到输入的数字已改变为310107197711041000，如图2.3所示。图2.3 录入18位身份证后得到的值已变化
输入的身份证号码自动被Excel做了调整，不仅仅在显示形式上，18位的身份证号码最终得到的结果和输入的都不一样。原因在于默认情况下，Excel中每个单元格所能显示的数字为11位，输入超过11位的数值，系统自动将其转换为科学记数格式，如果输入超过15位的数值，系统自动将15位以后的数值转换为0，而且这个转换是不可逆的。
这些都是Excel软件本身设计的原因，如果我们在单元格中输入身份证号码时直接录入，显示结果非预计。为了能让这些长数字完整地显示出来，这里介绍两种方法。
1. 先输入一个英文状态下的单引号
在输入这些长数字时，先输入一个英文状态下的单引号“'”，然后再输入数字即可。具体如图2.4的C列所示。图2.4 前置单引号输入
输入英文状态下的单引号对于Excel来说，相当于用户给其发了一个通知：“后续输入的内容为文本”，Excel得到通知之后，自然不会再为难后续输入的数字，会将这些数字当成文本处理，不会再出现11位的显示限制。
2. 输入区域设置为文本格式
也可以在输入之前先将需要录入区域的单元格格式设置为文本，如需在D列中输入，先选择D列，按Ctrl+1快捷键调出“设置单元格格式”对话框，在“数字”选项卡下的“分类”框中选择“文本”，如图2.5所示。
然后在D列中输入身份证号码，就能得到期望的结果，如图2.6所示。
3. 两种方法比较
比较一下这两种方法，显示的结果完全相同，唯一的差异在于第一种方法在单元格中多输入了一个英文状态下的单引号。这个英文状态下的单引号不影响显示和打印。
第2种方法适合批量数据的录入，第1种方法适合输入少量的长数字。图2.5 “设置单元格格式”对话框图2.6 两种身份证号码录入方法
职场点睛：这两种方法不仅适用于身份证号码的录入，也适合信用卡号等长数字及以0值开头的不用于计算的数字录入。灵活掌握会给工作带来很多便利，请看以下录入以0开头的数字的例子。
某企业集团员工工号设置为6位数字，第1位为企业等级代码，在集团母公司工作的员工，工号第1位数字为0，比如员工A，工号为037148。
如果直接输入“037148”，Excel会认为输入的是数字，自动将最前面的0去掉，正确的输入方法为先输入一个英文状态下的单引号或者先将需要输入的区域设置为文本格式，操作结果如图2.7所示。图2.7 录入以0开头的数值2.1.2 录入分数
数据录入中小数不可避免，有时候需要将录入的小数按照分数格式来显示，这时候直接在单元格中输入分数，结果也会出乎意料。
某项目完成率为二分之一，在单元格中输入“1/2”，如图2.8所示。
按回车键结束，得到的结果如图2.9所示，返回值是日期，且编辑栏中输入值也自动转换为“2012/1/2”。图2.8 在单元格中输入分数图2.9 直接输入日期后的返回结果
原因在于Excel默认的日期格式以“/”分隔，如直接输入“1/2”之类的分数，Excel会认为输入的是一个日期。该日期值未输入年度，自动以当前系统时间的年份值代替，这样输入值更改为“2012/1/2”。
由此可见，直接输入分数将会被Excel转换为日期值，这里介绍两种输入方法。
1. 以文本方式输入
为了避免被Excel误解，可以以文本方式录入，录入格式为“整数位+空格+分数”，上例中分数“1/2”没有整数位，以“0”代替。在C2单元格中输入“0 1/2”，如图2.10所示。
按回车键确认，得到的结果如图2.11所示，显示的是分数“1/2”，但实际得到的值是0.5，也即是分数运算的结果。图2.10 在单元格中输入文本格式的分数图2.11 输入文本格式的分数后得到的结果
2. 设置单元格自定义格式
从第1种方法中可以发现，Excel自动将输入的文本方式的分数转换成了一个数值，我们可以试着在Excel中直接输入一个数值，然后通过设置单元格自定义格式，将数值转换成需要的分数格式。
先选取D列，按Ctrl+1快捷键打开“设置单元格格式”对话框，在“数字”选项卡下的“分类”框中选择“分数”，在右侧的“类型”框中选取“分母为一位数”，如图2.12所示。
设置分数格式后，在D列不论输入数值“0.5”还是分数“1/2”，都会在单元格中显示分数“1/2”。
3. 两种方法比较
两种方法在输入类似于“1/2”这样的简单分数时效果是一样的，如果需要输入一些特殊的分数，就需要使用第2种方法了，例如输入可约分的分数、假分数等。图2.12 设置分数格式
如需要在单元格中输入分数“4/8”，使用第1种方法，Excel会自动将其约分为“1/2”，如使用第2种方法，预先将需要输入的单元格区域的单元格格式设置为“分数”分类下的“以8为分母”类型，就能顺利输入“4/8”这种特殊的分数了，如图2.13所示。图2.13 设置不同的分数类型
第2种方法更加灵活，读者可测试一下其他特殊分数的录入，如录入假分数（即分数的分子大于分母），试着尝试一下“分数”分类下不同的格式类型，加深对于此技巧的理解。2.1.3 录入特殊符号
在工作中有时候需要在Excel中录入一些特殊的符号，这些符号并不能在键盘中找到，这里介绍几种快捷录入特殊符号的技巧。
1. 直接插入符号
虽然在键盘中找不到，但对于常用的一些特殊符号，Excel已经集成在了一个命令中，单击“插入”|“符号”|“符号”命令，弹出“符号”对话框，在“符号”选项中可以找到常用的一些符号，如图2.14所示。
是不是觉得这些符号还不够酷？试着改变一下默认的字体，肯定会给读者带来惊喜。比如将“符号”选项卡下的字体从“宋体”更改为Wingdings，看看出现了什么？结果如图2.15所示。图2.14 常用的符号图2.15 Wingdings字体下的符号
Excel展现出一系列很酷的符号，是否没想到过在Excel中也能输入如此炫酷的符号？如果单击滚动条向下翻屏，精彩还将继续。
高兴得不要太早，如果把这些符号插入到Excel的单元格中，会是什么结果呢？比如将符号插入到A2单元格中，得到的结果如图2.16所示。
是不是Excel和读者开了个玩笑？能看到却得不到？答案显然并非如此，仅仅是单元格格式在作怪。在这里，只要将插入Wingdings字体下符号的单元格区域字体改成Wingdings字体，即可得到在“符号”对话框中看到的那些炫酷的特殊符号，如图2.17所示。图2.16 直接插入Wingdings字体下的符号图2.17 更改B列字体后的结果
可见Excel还是很厚道的，并没有欺骗读者。有时候操作中遇到不如意的结果，有必要多检讨一下自己的操作过程。
职场点睛：学会了这一招，可以在Excel中插入一些图案，转换成图片后还可以用于PPT演示，完全不需通过其他途径得到。
2. 使用Alt+ASC码
上面介绍的方法很酷，但如果仅需要输入一些常用的特殊字符，如“√”这类的，每次都要到“符号”对话框中去找，效率自然不高。在Excel中，可以通过Alt+ASC码（数字小键盘）的方式来实现特殊符号的快捷输入。
比如经常需要输入符号“√”，仅需输入Alt+41420快捷键即可（这里的41420需要通过数字小键盘录入）。
看到这里，读者可能会有以下几个疑问：□ 如何得到要输入符号的ASC码？□ ASC码是否需要记忆？□ 如果使用的是笔记本电脑，如何使用数字小键盘？
这里介绍一种简单的查询方法，比如对于符号“√”，先在L2单元格中通过功能区命令的方式插入，然后在M2单元格中录入公式“=CODE（L2）”，即可得到“√”符号的ASC码，如图2.18所示。
再测试一下符号“×”、“▲”、“★”的输入，方法同上，先插入符号，再通过CODE函数得到符号的ASC码，以后通过Alt+ASC码（数字小键盘）的方式即可实现这些符号的快捷输入，操作结果如图2.19所示。图2.18 符号的ASC码图2.19 使用CODE函数取得符号的ASC码
这种方法很简单，但可惜不是完全通用。如通过函数得到的ASC码不是5位数的，则需要查ASC码表确认（详见本节附件）。
职场点睛：对于快捷键，适当记忆能够显著提高录入效率。Excel操作高手肯定是高效的，让我们从提高录入效率开始。
如果是笔记本电脑，因各个厂商对于键盘功能的设置各不相同，如何使用数字小键盘并无一个统一的标准答案，读者可以参考一下笔记本电脑的使用手册。以IBM笔记本为例，按下Shift+ScrLk/NmLk组合键，就可以在打开/关闭数字小键盘功能之间进行切换。
3. 两种方法的比较
第2种方法更加灵活，有兴趣的读者可以研读一下ASC码表，很多特殊符号都能在里面找到。比如输入平方符号，先输入字符M，然后按下Alt+178快捷键即可，输入立方符号快捷键是Alt+179。2.1.4 录入上下标
在Excel中录入上下标也是常见的需求，上标如平方、立方符号可以通过Alt+ASC码的方式来录入，其实对于上下标，Excel中已有默认的设置。
1. 单元格自定义格式2
例如，输入平方符号M，操作步骤如下：（1）在C1单元格中输入“M2”。（2）选中“2”，按Ctrl+1快捷键。（3）在弹出的“设置单元格格式”对话框中勾选“上标”特殊效果，如图2.20所示。图2.20 设置“上标”
对于下标设置，操作类似，完成的效果如图2.21所示。
由图2.21可见，通过“单元格自定义格式”设置上下标，并没有改变原先输入的内容，仅仅是改变了显示方式，如输入M5，实际上就是对输入的字符M5做了设置，如涉及到需要字符精确匹配的场合，需要考虑这种设置可能会带来的影响。图2.21 设置上下标后的效果
单独设置上下标很简单，但如果上下标需要同时设置，就有点麻烦了，下面介绍另外两种设置上下标的方法。
2. 公式编辑器
使用公式编辑器，可以在Excel中任意编辑公式，不仅限于上下标，操作步骤如下：（1）单击“插入”|“文本”|“对象”命令，在弹出的“对象”对话框中选择“Microsoft公式3.0”对象，单击“确定”按钮完成，如图2.22所示。图2.22 插入“Microsoft公式3.0”对象（2）在弹出的“公式”浮动菜单栏中选择“下标和上标模板”，如图2.23所示。图2.23 “公式”浮动菜单栏（3）在模板中选取合适的样式后，在编辑框中完成上下标数字的录入，如图2.24所示。图2.24 编辑框中录入上下标（4）确定退出后显示的上下标还带有外边框，选取该对象，在右键快捷菜单中单击“设置对象格式”，在弹出的“设置对象格式”对话框中选择“颜色与线条”选项卡，将“填充”与“线条”颜色皆设置为无，如图2.25所示。（5）拖动已编辑完成的公式对象到适当的位置，即完成了上下标的同时输入，效果如图2.26所示。图2.25 设置公式对象格式图2.26 使用公式编辑器的编辑效果
3. 使用拼音编辑
公式编辑器功能虽然强大，但操作略显繁琐。如仅需显示上下标，可以使用“编辑拼音”的功能。“编辑拼音”的实质是把“拼音”显示在上标的位置，操作步骤如下：（1）使用自定义格式完成下标的设置。（2）选取下标数字，单击“开始”|“字体”|“显示或隐藏拼音字段”命令，在弹出的下拉菜单中选择“编辑拼音”命令，在拼音编辑框中录入需要显示的上标，如图2.27所示。图2.27 “编辑拼音”操作
完成之后还需要通过单击“开始”|“字体”|“显示或隐藏拼音字段”命令来改变这个所谓的“上标”的显示。
本节示例文件：《2.1 轻松搞定特殊数据的录入.xlsx》。
2.2 玩转系列填充
上一节中介绍了特殊数据的录入，在实际工作中，如果所有数据都需要一个一个录入，效率自然欠佳，其实对于有规律的数据，Excel提供了多种处理方式。2.2.1 自动填充的威力
1. 认识自动填充
输入一个数据或者公式后按住鼠标左键向下拖动，这种操作相信很多读者都操作过，但很多人都不知道这种操作的名称——“填充”，对于填充的威力更是知之甚少。先出个题目，考考大家对于自动填充的了解。
如果需要在A列的A1：A10000单元格中顺序输入数字1～10000，何种方法最简单（不使用函数公式）？可能会有如下几种答案：□ 自A1单元格起一个一个数字输入；□ 在A1单元格输入1，在A2单元格输入2，然后选取A1：A2单元格区域，向下拖动到A10000单元格。
答案1，肯定是不及格的；答案2，勉强算及格吧！
相信很多读者会觉得答案2就是正解，如果仅在A1：A10区域中顺序输入数字1～10，这种方法不能算错，但现在的要求是到10000行。试试拖动到10000行，这需要坚强的意志力，不要说10000行，就是1000行也不容易。
公布一下正确答案，操作步骤如下：（1）在A1单元格中输入1。（2）在名称框中输入“A1：A10000”，如图2.28所示，按回车键确认。图2.28 在名称框中选取录入范围（3）单击“开始”|“编辑”|“填充”下拉按钮。（4）在弹出的下拉菜单中选取“系列”，在“系列”对话框中直接单击“确定”按钮，如图2.29所示。图2.29 “序列”对话框
奇迹出现了！
A1：A10000单元格区域瞬间完成了1～10000的数据录入。
是不是觉得很神奇？其实这就是Excel的序列填充功能。这里的几个操作步骤都很典型，解释如下：□ 第1步是设置了起点；□ 第2步是使用“名称框”选取需要操作的单元格区域，先选取后操作，是Excel操作的基本原则；□ 第3步是设置了填充的条件，比如步长值为1，类型为“等差序列”。
完成上述步骤，等于向Excel发出了在A1：A10000单元格区域中，以初始值1、步长1的等差系列填充的命令，之后就把手工拖动的工作交给Excel去完成了。
职场点睛：不知道正确答案没关系，但对于有一定经验的用户，应该树立这样一种操作理念。即如果对于100行数据的操作所需的时间是对于10行数据操作时间的10倍，这种操作方式绝对是有问题的！应该培养批量操作的意识！
都看懂了吗？会了做一下如下填充等差序列的练习题，加深一下理解。
如何快速在C1：C10000单元格区域中输入“1、3、5、……”这样的序列？
前3步的步骤完全一样，第4步略作些变化，如图2.30所示。
需要输入系列的特征是数值间隔为2，换做Excel懂的语言就是“步长值为2”，因未确定系列中最后1个值，所有“终止值”框为空。给Excel提供了“在C1：C10000单元格区域中，以初始值1、步长2的等差系列填充”的命令。
2. 日期填充
相对于数值填充，Excel对于日期的填充更加智能，可以按“日”、“工作日”、“月”、“年”填充。某公司需要统计2012年3月份某部门的出勤人数，该公司实行双休制度，需要完成的表格如图2.31所示。图2.30 步长值为2的等差序列图2.31 《出勤人数统计表》表头
在A列中需要输入3月份的工作日日期，B列输入对应的出勤人数。如何在A列中录入？一个一个输入肯定不用考虑，这里还是要用到序列填充功能，操作步骤如下：（1）在A2单元格中输入2012年3月的第1个工作日“2012-3-1”。（2）选取A2：A25区域（因未计算过2012年3月到底有几个工作日，这里可以将区域选得大一些，多选区域不会出错）。（3）单击“开始”|“编辑”|“填充”下拉按钮。（4）在弹出的下拉菜单中选取“系列”，在“系列”对话框中“日期单位”选择“工作日”，终止值框中输入“2012-3-31”，如图2.32所示。
单击“确定”按钮后仅填充到A23单元格，在A24：A25单元格中并没有填充内容，如图2.33所示。
原因在于2012年3月份最后一个工作日为2012年3月30日，在之前选取的区域大小超出了工作日的天数。在处理类似的填充问题时，一般有两种方案：□ 放大选择区域，在“终止值”框中设置“终止值”；□ 先确认精确的填充区域，在“终止值”框中可以不做设置。图2.32 序列填充工作日图2.33 填充日期后的结果
方案1就是本例中的选择，如选择方案2，在第2步中选择A2：A23单元格区域，“终止值”框中则可不改变默认设置。读者可以根据自己的工作习惯自行选择。2.2.2 公式复制与自动填充
1. 公式填充
见识了自动填充的威力之后，再来回顾一下常见的操作方式。
A列为数量，B列为单价，在C列计算金额，C2单元格已输入公式“=A2*B2”，如图2.34所示。图2.34 公式复制
常规处理这种问题有两种做法：□ 选取C2单元格，待鼠标变成黑色十字时，按住左键向下拖动（以下简称为“拖动复制”）；□ 选取C2单元格，待鼠标变成黑色十字时，双击（以下简称为“双击”）。
两种方法这里都适用，但是否就意味着完全一样呢？如果C列需要计算的不止10行，是10 000行，相信没有人会选择方案1的做法。
2. 新的问题
是否意味着方案2的做法就是最佳选择？先不要下结论，看一下下面的例子。在数据源的第6行为空白，如按方案2的做法，选中C2单元格，双击，得到的结果如图2.35所示。
公式向下复制到第5行时就停止了，原因在于在第6行中，需要计算的A2和B2单元格都是空白。由此可见，双击填充的方法并不是完全通用。
那面对大量数据的公式复制，其中还有空行，只能手工拖动公式吗？答案自然是否定的，还是可以使用序列填充，操作步骤如下：图2.35 有空行时的双击填充效果（1）在C2单元格中录入公式“=A2*B2”。（2）选中C2：C10单元格区域。（3）单击“开始”|“编辑”|“填充”下拉按钮。（4）在弹出的下拉菜单中选取“系列”，在“系列”对话框中选择“自动填充”类型，单击“确定”按钮后退出，如图2.36所示。图2.36 自动填充公式
这个问题还有一种更加快捷的解决方案——使用快捷键。在完成前2步之后，使用Ctrl+D快捷键，即可达成图2.36所示的效果。这里的Ctrl+D起到了向下“自动填充”的功能，类似的快捷键如表2.1所示。表2.1 自动填充的快捷键
Ctrl+D快捷键操作时不需要打开“序列”对话框，还可以避免空行的问题，但Excel中此类快捷键只有2个，无法完成向上和向左的自动填充。
3. 认识“自动填充选项”
现在对于区域中的批量填充，我们已经有了4种方法：□ 拖动复制□ 双击□ 快捷键□ “序列”对话框
4种方法各有优劣，“序列”对话框中的设置显然更多，能适合不同的填充需求，但操作感觉明显不及前3种方法简便。其实，有些操作可以按常规的思路先拖动复制，然后再设置填充选项。
还是用工作日填充的例子，在A2单元格中输入“2012-3-1”，拖动复制到A7单元格，此时日期是顺序填列的，并没有按照工作日，在右下角出现一个“自动填充选项”，如图2.37所示。
单击该选项，出现一个下拉菜单，选取“以工作日填充”，结果如图2.38所示。
使用“自动填充选项”可以先有操作结果，然后改变填充方式，不失为一种灵活的操作方法。图2.37 自动填充选项图2.38 以工作日填充的结果
4. 填充时的格式问题
在序列填充中，有时候会涉及到单元格区域的格式问题，自动填充的结果往往会改变原有区域的格式设置，请看下面的例子。
对于数量在150以上的行用黄色标注，需在C2：C10区域复制公式，并不能改变该区域原有的格式设置，如图2.39所示。
最佳方案是选取C2单元格，拖动复制公式到C10单元格。单击“自动填充选项”，选取“不带格式填充”即可，如图2.40所示。此时公式完成向下复制并未改变原区域的格式设置。图2.39 复制公式并不改变格式设置图2.40 不带格式填充
职场点睛：在工作中如能灵活使用“自动填充选项”，可以在单元格拖动复制时自行在“复制”、“填充”及“格式”之间做出选择，无疑能大幅度提高工作效率。
学习了序列填充的几种方法之后，再做一个简单的小练习，在E1：E10单元格区域，依次输入数字1～10，不能使用之前讲解过的方法。
这个练习要是不加限制条件，实在是没有悬念，在公布答案之前，先总结一下可能的几种做法：□ 在E1单元格输入1，E2单元格输入2，选取E1：E2单元格区域拖动复制到E10；□ 在E1单元格输入1，选取E1：E10单元格区域，使用“序列”填充对话框填充；□ 在E1单元格输入1，拖动复制到E10，然后在“自动填充选项”中选择“填充序列”。
这3种方法都不错，这里要介绍的是结合Ctrl键的操作，操作步骤如下：（1）在E1单元格中输入1；（2）在鼠标出现黑色十字时，按下Ctrl键（观察此时鼠标箭头的变化）；（3）不松开Ctrl键，同时按住鼠标左键拖动到E10单元格。
职场点睛：操作中使用Ctrl键或者Shift键，往往会有意想不到的结果，读者可以根据数据量及自己的工作习惯在各种方法中灵活选用。2.2.3 自定义序列填充
序列填充中处理的还是与数值相关的问题（日期也是一个特殊的数值），但在实际工作中，会涉及到文本字符的填充。
比如需要输入“甲”、“乙”、“丙”、“丁”、“戊”、“己”、“庚”、“辛”、“壬”、“癸”这样的系列，使用已知的几种方法未必能够实现操作要求，如使用Ctrl+D快捷键，结果全部变成了“甲”。
正确的方法是在A1单元格中输入“甲”后使用“序列”对话框中的“自动填充”类型，或者直接向下拖动复制，结果如图2.41所示。图2.41 鼠标拖动复制系列“甲”、“乙”、“丙”、“丁”、“戊”、“己”、“庚”、“辛”、“壬”、“癸”这样的系列反复出现，原因在于Excel中已有默认的设置。
查看或者编辑自定义序列可以单击“文件”|“选项”命令，弹出“Excel选项”对话框，在“高级”选项卡下单击“编辑自定义列表”命令，如图2.42所示。图2.42 Excel选项——高级
在弹出的“自定义系列”对话框中，可见Excel已预设的“自定义序列”，如图2.43所示。图2.43 “自定义序列”对话框
上例中拖动复制后，在10个元素之后重复，原因在于这个系列仅10个元素，超出10个之后，只能重复出现。
1. 添加自定义序列
Excel已默认了11个自定义序列，如果有新的需求，可以自行添加，添加的方法有两种。
1）手动输入
在“自定义系列”对话框中选取“新系列”，在“输入序列”列表框中输入需要新增的序列，每项输入结束后按回车键换行，输入结束后单击“添加”按钮，操作如图2.44所示。图2.44 手动输入自定义序列
操作完成之后，新增的序列会出现在“自定义序列”对话框中的“自定义序列”列表框中，如图2.45所示。图2.45 手动添加自定义序列后的显示结果
2）导入
如需要添加的序列已在工作表中输入，则可以通过导入的方式来完成。
比如需要添加“第1章、第2章、……第10章”这样一个序列，该序列已输入在工作表的C1：C10单元格区域，单击“自定义序列”对话框中的序列选项按钮，选取需导入的单元格区域“C1：C10”，单击“导入”按钮完成，操作如图2.46所示。图2.46 导入自定义序列
两种方法都可以，但如果一个一个输入26个英文字母，肯定不是好办法，这里可以用函数来提升工作效率，操作步骤如下：（1）在E1单元格中输入公式“=CHAR(ROW(A65))”，返回英文大写字母A。（2）公式向下拖动复制到E26单元格，返回英文大写字母A～Z系列。（3）选取E1：E26单元格区域，使用复制粘贴数值的方法，将选定区域数值化（如跳过这一步直接导入Excel会报错），如图2.47所示。图2.47 导入含公式的单元格区域（4）按前述导入自定义序列的方法操作，操作结果如图2.48所示。图2.48 导入英文大写字母后的操作结果
这个练习给我们两点启发：□ 对于重复操作（如一个一个录入数据），很多时候都应该寻找批量操作的解决方案；□ 如果Excel报错，不用紧张，Excel报错在多数情况下仅是一个提示，不会对用户的文件造成实质性的破坏，分析一下报错的原因，换一种操作的思路，很多问题都将迎刃而解。
2. 管理自定义序列
对于已设定的自定义序列，不仅可以删除，而且还可以编辑。
1）删除自定义序列
比如对于之前创建的自定义序列“第一章、第二章、……第十章”系列已无用，可在“自定义序列”对话框中先选取该序列，然后单击“删除”按钮，如图2.49所示。图2.49 删除自定义序列
2）编辑自定义序列
比如对于已定义的序列“第1章、第2章、……第10章”，需要继续增加到第15章，在“自定义序列”对话框中选中这个系列，在“输入序列”列表框中编辑（如添加新的元素），最后单击“添加”完成，操作步骤如图2.50所示。图2.50 编辑自定义序列
3. 自定义序列的应用
使用自定义序列不仅可以提升特殊数据的录入效率，还可以用于数据列表及数据透视表中的排序操作，相关内容将在之后章节详细讲解。
本节示例文件：《2.2 玩转系列填充.xlsx》。
2.3 提升录入速度
并非所有数据都能如上节所述使用序列填充的方法快速填充，有时候，还是需要一个一个地输入。提升录入速度是一种实实在在的需求。2.3.1 控制回车键后的移动方向
很多用户习惯输入完成后按回车键结束，此时Excel会自动选定原编辑单元格下方的单元格，比如在A1单元格中编辑，完成后按回车键，此时Excel会自动选择A2单元格。
默认设置在多数情况下是符合用户操作情况的，但有时候未必。比如有时候需要的是横向移动，有时候需要的是选定单元格不改变。如需要改变录入后单元格的选定内容，有两种方法。
1. Excel选项设置
某公司人事部门需要录入一份员工登记表，如图2.51所示，在A2单元格编辑完成之后需要编辑的是B2单元格，如按常规方法直接回车键结束，Excel会回到A3单元格，这就给快速录入制造了障碍。图2.51 需录入的表格样式
解决方案为单击“文件”|“选项”命令，在弹出的“Excel选项”对话框中选取“高级”选项卡，更改“按Enter键后移动所选内容”的方向为“向右”，如图2.52所示。图2.52 更改回车键后的移动方向
更改设置之后，再按回车键，Excel会自动选取当前编辑单元格右边的单元格。
2. 使用Tab键
还有一种更简单的方法，在单元格编辑完成之后按Tab键结束，Excel会选取右侧单元格，如按Shift+Tab组合键Excel会选择左侧单元格。
3. 两种方法比较
使用更改“Excel选项”设置的方法将对所有的工作簿起作用，如仅对当前工作簿的某个区域有此操作需求，使用Tab键更加方便。
但Tab键只能控制左右移动，而“Excel选项”中可以在“上、下、左、右”中选择移动的方向，而且如取消“按Enter键后移动所选内容”选项，按回车键后将不改变选定内容，如图2.53所示。图2.53 “按Enter键后移动所选内容”选项2.3.2 区域中的快速录入
在实际工作中经常会碰到需要在矩形区域中录入数据，希望一行数据录入完毕后Excel能自动选择下一行的第一个单元格，如G2单元格录入完毕后会自动跳到A3单元格，如图2.54所示。图2.54 在矩形区域中录入数据
处理此类问题，常规有两种方法。
1. 设置录入区域
通过设置录入区域的方法，可以快速在区域中录入数据，操作步骤如下：（1）选取需要录入的区域，如选择A2：G5单元格区域。（2）在单元格中编辑完毕后，按Tab键右移、按Shift+Tab快捷键左移。
上例中当进入G2单元格编辑完毕后，按Tab键，Excel会自动选定A3单元格。
2. 使用内置的“记录单”命令
对于矩形区域的数据录入，还可以使用内置的“记录单”命令。该命令在2003版时位于“数据”菜单下，但自2007版开始，Excel将其隐藏了起来，不在功能区中。
如需使用“记录单”命令，可以通过设置“自定义功能区”或者“快速访问工具栏”的方式，先把这个命令放出来。如图2.55所示，“快速访问工具栏”中最后一个命令即“记录单”，关于设置“快速访问工具栏”的操作，详见本书第1章。图2.55 “快速访问工具栏”中的“记录单”命令
使用“记录单”命令在区域中输入的步骤如下：（1）选取单元格区域A1：G10，需确保表头处于选中状态，这样整个操作会比较直观。（2）单击“快速访问工具栏”中的“记录单”命令，弹出“记录单”录入框，如图2.56所示。图2.56 “记录单”录入框
在“记录单”录入框中，已列出了区域中已有的记录，如有多条记录，可以按“上一条”或者“下一条”按钮选择。
单击“新建”按钮，出现一张空白的“记录单”，可以在对应的字段中输入新的信息，按Tab键或者Shift+Tab组合键，可在各字段之间进行切换，如图2.57所示。图2.57 在新建“记录单”中录入数据
录入完毕后，单击“新建”或者“关闭”按钮，“记录单”中已录入的数据会添加到选定区域中的第1个空行中。
职场点睛：对于经常需要录入大量数据的用户，上述两种方法都值得尝试，毕竟录入速度也是非常重要的。
使用“设置录入区域”的方法操作比较简单，使用“记录单”命令输入时字段名称显示在输入值的左边，感觉更加直观，而且还可以在“记录单”录入框中对已录入的记录进行编辑，甚至还可以删除已有的记录。2.3.3 鼠标与键盘快捷键相结合
很多用户已经养成了操作时主要依靠鼠标的习惯，有时候鼠标操作与键盘快捷键相结合，会对操作效率有比较大的提升。
1. 与功能键相关的快捷键
功能键指键盘上F1～F12的按键，单独使用或者与Ctrl、Shift、Alt键结合使用，会产生很多有用的操作快捷键，如表2.2所示。表2.2 与功能键相关的快捷键
2. 其他有用的快捷键
其他有用的快捷键如表2.3所示。表2.3 其他有用的快捷键
相关的快捷键还有很多，读者没有必要去记忆繁多的快捷键，可以参考附件中的快捷键汇总表，根据自己的工作需要及工作习惯，有选择地记忆使用。
本节示例文件：《2.3 提升录入速度.xlsx》。
2.4 想要出错也不容易
在录入过程中出错在所难免，人不是机器，出错实属正常，但可以采取一些措施加以控制。2.4.1 巧用自动更正
某企业下属有很多分公司，在某些正式场合，需要使用公司的全称，如每次都要录入全称，不仅很长，还容易出错，类似问题可使用Excel的自动更正选项。
单击“文件”|“选项”命令，弹出“Excel选项”对话框，选择“校对”选项卡下的“自动更正选项”按钮，在弹出的“自动更正”对话框中设置替换内容，如图2.58所示。图2.58 “自动更正”对话框
如经常需要输入表2.4中的公司，可根据表中的“别名”及“公司全称”在“自动更正”对话框中加以设置。表2.4 “自动更正”项目列表
将表2.4的内容设置到“自动更正”对话框中后，在工作表中输入别名列中的“SL”，按回车键后即自动变成“上海自然美三联化妆品有限公司”，其他别名输入结果以此类推，这样就实现了快捷地输入较长的公司名称，而且还不会出错。
职场点睛：不仅是公司名称，对于实际工作中需要经常录入的较长的字符串，也可以使用“自动更正”设置达到快速录入的效果。值得注意的是，这样的设置通用于Office软件的所有组件，在Excel中的设置也可在Word及其他软件中使用。2.4.2 限制输入内容
除了“自动更正”之外，Excel还提供了“数据有效性”功能，可以限制用户可以输入的内容，防止一些明显的错误。
1. 数据有效性的常用操作
单击“数据”|“数据工具”|“数据有效性”命令，在弹出的“数据有效性”对话框中可见Excel允许设置“任何值”、“整数”、“小数”、“序列”、“日期”、“时间”、“文本长度”、“自定义”等8种有效性条件，如图2.59所示。图2.59 有效性条件允许的选项
关于数据有效性，涉及的内容很多，这里仅介绍最常见的几个应用。
1）限制输入系列中的值
某企业人事部门编制的员工登记表中有一列为“职务”，关于“职务”企业内部有特别的定义，如在录入时不做限制很容易出错，设置数据有效性操作步骤如下：（1）选取需要设置“数据有效性”的单元格区域，如D2：D10单元格区域。（2）单击“数据”|“数据工具”|“数据有效性”命令。（3）在弹出的“数据有效性”对话框中选择“设置”选项卡，有效性条件“允许”为“序列”，“来源”框中输入“总监，经理，副经理，主管，高级专员，专员”，如图2.60所示，各元素之间以半角逗号分隔。图2.60 设置“数据有效性”序列
设置完成后选取D2：D10单元格区域的任何一个单元格，都会出现一个下拉箭头，单击下拉箭头，出现一个列表框，其中内容为之前在图2.60“来源”框中的设置，如图2.61所示。图2.61 数据有效性下拉列表框
设置数据有效性不仅能提升录入速度，而且还可以在一定程度上防范错误。比如用户未在D2：D10单元格区域中选择下拉列表中已有的选项，而是另外输入了其他的数据，例如输入“总经理”，按回车键结束，这时Excel会提示错误，如图2.62所示。图2.62 “输入值非法”提示
原因在于输入的值不在预先设置的序列范围之内，Excel不让这样的输入结果通过。
设置序列之后，在来源框中除了手工录入之外，还可以选取单元格中的内容，具体操作读者可以自行测试。
2）限制输入文本长度
比如对于员工工号，统一规定为6位数字，为防止输入错误，可通过设置“数据有效性”中的“文本长度”加以限制。
在A2：A10单元格区域中设置数据有效性，在“数据有效性”对话框中的有效性条件“允许”下拉框中选择“文本长度”，“数据”下拉框中选择“等于”，“长度”框中设置为“6”，如图2.63所示。
设置完成后，在A2：A10单元区域，如输入员工工号的位数不等于6，Excel就会报错。
3）限制输入日期范围
设置数据有效性条件在很多时候是为了防止误操作，比如某企业于2002年1月1日登记成立，该企业的员工的入职日期就不应该早于2002年1月1日，也不能晚于填表当天的日期，可以在“数据有效性”对话框的有效性条件“允许”下拉框中选择“日期”，“数据”下拉框中选择“介于”，“开始日期”设置为“2002-1-1”，“结束日期”设置为“=TODAY()”，如图2.64所示。
设置之后如输入早于2002年1月1日或者晚于填表当天的日期，Excel都会报错。图2.63 设置允许的文本长度图2.64 限制输入日期范围
备注：这里“=TODAY()”是一个日期函数，返回系统当前日期。
4）自定义有效性条件
有效性允许条件选项中最有用的是“自定义”，通过自定义设置，不仅能实现以上介绍的所有功能，而且还能继续扩展。
仍以前面的例子，对于工号限制了6位文本长度能预防输入字符长度的错误，但如果用户输入的是6位文本，Excel是不会报错的，如图2.65所示。
对于此类错误，如果仅限制文本长度是不能预防的，我们可以通过“数据有效性”对话框中的有效性条件为“自定义”来设置限制条件。
选取需要设置数据有效性的A2：A10单元格区域，打开“数据有效性”对话框，在有效性条件“允许”列表框中选择“自定义”，公式框中输入如下公式，如图2.66所示。=AND（ISNUMBER($A2)，LEN($A2)=6）图2.65 输入文本后错误的结果图2.66 设置数据有效性公式1
设置之后，如在已设置区域输入非6位数字，Excel都会报错。
公式中涉及到3个函数：ISNUMBER函数判断是否为数值，LEN函数计算字符长度、AND函数判断该函数的参数是否同时满足条件。公式的含义是判断A2单元格是否同时满足是数字且长度为6位。
再看一个自定义有效性条件的例子，世界之大可谓无奇不有，对于身份证号码竟然也会有人记错。并非所有的错误都可以防止，但对于搞错身份证号码长度的错误，完全可以通过设置数据有效性加以控制。
选取需要设置数据有效性的C2：C10单元格区域，弹出“数据有效性”对话框，在有效性条件“允许”列表框中选择“自定义”，公式框中输入如下公式，如图2.67所示。=OR（LEN（$C2）=15，LEN（$C2）=18）图2.67 设置数据有效性公式2
设置之后，如在已设置区域输入字符长度不是15位或者18位，Excel都会报错。
这公式中涉及到2个函数：LEN函数计算字符长度，OR函数判断该函数的条件是否有一个以上满足条件。公式的含义是判断C2单元格字符长度是否为15位或者18位。
职场点睛：不要总是抱怨别人出错，与其抱怨还不如预先加以控制，设置数据有效性就是一种值得尝试的方法。
2. 不满足条件也能录入
在设置限制条件时往往存在考虑不周的问题，如果此时按照“数据有效性”的默认设置，不满足条件时通不过。这时有效性设置非但不能提高工作效率，反而对正常工作造成了妨碍。
比如在设置员工职务时遗漏了“副总监”这个选项，按照默认的设置，即使手工录入“副总监”这个选项，Excel也会报错。解决这个问题只需修改一下“数据有效性”对话框中“出错警告”选项卡下样式的选择，如图2.68所示，将“样式”改为“警告”。图2.68 改变出错警告样式
此时如在D列输入“副总监”，Excel的提示如图2.69所示。图2.69 输入值非法的警告提示框
单击“是”按钮即能通过，此种设置既有提示作用，又避免了错误的限制。
3. 数据有效性的局限性
数据有效性设置在防止误操作上作用很大，但其也不是万能的，使用复制粘贴功能就能轻松跳过数据有效性设置的限制。不能把数据有效性设置当成预防出错的唯一的或者是最后一道防线，要有B方案。
2.5 小结
本章讲述了数据录入中的一些技巧，限于篇幅所限，很多技巧无法一一讲述。
技巧是无限的，但思考问题的方法是有限的。掌握了正确的思考问题的方法，很多技巧无需记忆都能自行找到并且灵活运用。
这里重点要再强调的是需要培养批量操作的意识，不要满足于重复操作，绝大多数的重复操作都有更好的替代方案。只有不满足已有的常规的操作模式，才能探索更多的操作技巧。
第3章 数据的编辑
数据录入完成之后接下来的工作就是数据编辑，编辑也有广义和狭义之分，狭义的编辑仅指改变实际输入数据的内容，如将输入的数据100改成200；广义的编辑还包括在不改变实际输入内容的情况下更改在Excel单元格中的显示内容，如输入100但显示出来是200，本书中将这种方法称为Excel的易容术。
不论广义的编辑还是狭义的编辑，首先都要选定需要操作的单元格。本章将从数据的选定开始，介绍数据的选定及两种编辑方法（真正的编辑及Excel易容术）。
3.1 轻松选择“特殊”区域
Excel的操作特点是先选定后操作，也就是要先找到需要操作的对象，如工作表、单元格。说起选取单元格区域，用户应该不会陌生，但多数用户还停留在使用鼠标一个一个选取的阶段，最多结合Ctrl键和Shift键选择。本节将介绍一些批量操作的方法，如通过数据有效性、条件格式、名称框、条件定位以及快捷键来实现快速选取定位。3.1.1 找到满足特点条件的单元格
在本书2.4节中介绍过一些录入错误的预防方法，但仅限于预防，不能完全防范错误。出错并不可怕，但如果找不到错误那就麻烦了。存在错误的单元格也就是需要编辑的单元格，选定这些单元格有多种方法。
1. 圈释无效数据
某企业人事部门统计员工情况，录入了一份员工登记表，其中工号字段的规则是6位数字，在表格中已设置数据有效性预防操作错误。录入人员在操作中有部分数据是直接从其他表格中粘贴过来的，这样就绕过了数据有效性的设置。
如图3.1的表格，A列员工的工号存在错误，如何才能快速找到存在的错误？图3.1 存在录入错误的表格
在数据量大的情况下，一个一个单元格核对的方法肯定不可取，这里介绍使用“数据有效性”功能下的“圈释无效数据”命令，操作步骤如下：（1）对A2：A8单元格区域设置数据有效性，有效性条件设置为“自定义”，使用如下公式，如图3.2所示。=AND（ISNUMBER($A2)，LEN($A2)=6）图3.2 设置工号的数据有效性（2）单击“数据”|“数据工具”|“数据有效性”命令，在下拉菜单中选择“圈释无效数据”命令，如图3.3所示。图3.3 圈释无效数据
此时对于不符合要求的工号，Excel都会在该单元格上加上一个红圈，如图3.4所示。图3.4 圈释无效数据的操作结果
圈释无效数据的操作结果很直观，能够非常清楚地展示出不符合条件的数据。如果要去除表格中的红色圈释，单击“清除无效数据标示圈”命令即可。
2. 突显目标数据
录入错误的单元格混在其他单元格中格式相同无法区分，要是能给存在错误的单元格穿上一件特殊的衣服，如设置醒目的背景颜色，这样就能快速区分，这种操作就叫做条件格式。
仍用上例中工号不符合要求的例子，对不符合要求的工号单元格设置特殊的格式，操作步骤如下：（1）选取需要设置条件格式的A2：A8单元格区域。（2）单击“开始”|“样式”|“条件格式”，在弹出的下拉菜单中单击“新建规则”命令，如图3.5所示。（3）在“新建格式规则”对话框中选择规则类型为“使用公式确定要设置格式的单元格”，公式框中输入如下公式，最后单击“格式”按钮，如图3.6所示。=OR(NOT(ISNUMBER($A2))，LEN($A2)<>6)图3.5 设置条件格式图3.6 设置条件格式规则
公式解析：公式NOT(ISNUMBER($A2))判断是否不是数值、公式LEN($A2)<>6判断字符长度是否不等于6、外套OR函数表示上述2个条件只要有一个满足，即设置条件格式突显数据。
在选取A2：A8单元格区域时，当前单元格为A2，因条件格式需要对于单元格区域A2：A8生效，所以对于A2单元格要确保行方向是相对引用（这里列方向可以不使用绝对引用，但如果条件格式作用于A2：E8单元格区域，则对于当前单元格A2一定要确保是行相对、列绝对的混合引用）。
备注：关于单元格的引用类型，请参阅本书4.3节。（4）在弹出的“设置单元格格式”对话框中选择需要对满足条件的单元格设置的格式，单击“确定”按钮完成退出，如图3.7所示。图3.7 设置满足条件的单元格格式
操作结果如图3.8所示，对于不满足要求的工号，Excel已设置成了红色。图3.8 设置条件格式后的操作结果
对于使用条件格式，需要注意以下几点：□ 设置的条件和使用数据有效性时不一样，数据有效性是满足条件的可以通过，条件格式是满足条件的改变显示格式，因此两者设置的条件往往逻辑相反；□ 在设置条件格式的条件时，需要考虑对于当前单元格的引用类型。
职场点睛：出错不可怕，找不到错误才是真正的麻烦。数据有效性中的圈释无效数据及条件格式功能为快速查找错误提供了两种可行的解决方案。
条件格式不仅可用于标示存在输入错误的单元格，还可以突显一些满足特殊条件的单元格区域，举几个例子来加深一下理解。【例1】 标示未达标业绩
某企业销售业务员1～6月的销售业绩如图3.9所示，销售业绩目标为每月10万。
如对于业绩未达标的销售额用颜色标注，查看将更直观。操作步骤如下。（1）选取需设置的B2：G8单元格区域，单击“开始”|“样式”|“条件格式”|“突出显示单元格规则”|“小于”命令，如图3.10所示。图3.9 各业务员1～6月销售额图3.10 设置“突出显示单元格规则”（2）在弹出的“小于”对话框中设置条件值为100，000，在“设置为”下拉框中选取需要设置的格式，此时选中的数据区域中会自动预览已设置的格式，如图3.11所示，单击“确定”按钮完成，显示效果同预览。图3.11 设置“小于”条件格式后的预览【例2】 标示3个月以上未达标1
自Excel 2007版起，微软对于内置的条件格式设置有了很大的增强，但很多设置还是需要应用函数公式。仍沿用上例数据，需要将在半年中有3个月以上业绩未达标的业务员标示出来，就需要使用条件格式中的公式设置，操作步骤如下。（1）选取需要设置的A2：A8单元格区域，单击“开始”|“样式”|“条件格式”|“新建规则”命令，在弹出的“新建格式规则”对话框中选择规则类型为“使用公式确定要设置格式的单元格”，在公式框中输入如下公式，如图3.12所示。=COUNTIF(B2:G2，"<100000")>=3图3.12 设置公式规则
公式解析：在条件中使用了COUNTIF公式，用于计算各行中数值小于100，000的数据个数。（2）单击“格式”按钮，选择需要设置的单元格格式，单击“确定”按钮完成，Excel仅对A7单元格应用颜色，如图3.13所示。图3.13 标示业绩3个月以上未达标的业务员【例3】 标示3个月以上未达标2
仍用上例的数据，如对于业绩3个月以上未达标的业务员，不仅是A列业务员名称这里显示红色，而是整行显示，如在选取需设置条件格式的A2：G8单元格区域后简单沿用上例的公式，返回结果如图3.14所示。图3.14 设置整行条件格式时错误的返回结果
出错的原因在于简单沿用了上例中的公式“=COUNTIF(B2:G2，"<100000")>=3”。公式本身没错，但作用范围改变时，公式也应有相应的调整。这两个例子的比较如表3.1所示。表3.1 两个例子的比较
唯一的差异在于条件格式作用的区域不一样。如果不是一次对于整个矩形区域设置条件格式，而是对矩形区域中的单元格一个一个设置，此时设置的公式如下：
在A2单元格时，正确的条件格式公式是“=COUNTIF(B2:G2，"<100000")>=3”；
到了B2单元格，这个公式还应该是“=COUNTIF(B2:G2，"<100000")>=3”；
……
继续横向变动到G2单元格，公式仍然不变；
而到了A3单元格，公式应变更为“=COUNTIF(B3:G3，"<100000")>=3”；
……
其他依次类推。
由上述讲解可见，在需要设置条件格式的A2：G8单元格区域中，正确的条件格式公式引用的列都是B：G列，而引用的行随着需设置条件格式公式单元格所在行的变动而变动，这样，对于条件格式公式中的单元格区域引用，就需要使用混合引用，条件格式公式如下，操作结果见图3.15所示。=COUNTIF（$B2:$G2，"<100000")>=3图3.15 对整行设置条件格式的操作结果
这里涉及到了单元格的引用类型，具体将在第4章中详细讲解。读者可以先记住，在设置条件格式公式时，需要考虑单元格的引用类型。
3. 使用辅助列判断
如果对于条件格式公式中的单元格引用类型较难把握，可以通过辅助列来协助判断。
仍沿用之前的例子，在I2单元格输入最初的公式“=COUNTIF(B2:G2，"<100000")>=3”，向下复制到I8单元格，可见仅I7单元格值为“TRUE”，第7行符合条件（3个月以上未达标），如图3.16所示。图3.16 使用辅助列判断
使用辅助列判断相对更加容易理解，如在使用数据有效性及条件格式时出错，都可以把公式放到单元格中，看一下运算结果是否如最初设置的要求。3.1.2 使用名称框选取定位
在Excel界面功能区下方最左侧有一个名称框。虽然被叫做名称框，但实际上知名度并不高，属于默默无名，很多用户都不知道这里有个名称框，更不要说名称框的应用了。实际上名称框还是很有用的，如显示当前单元格、显示已定义的区域名称、快速定位、快速选定区域等。
1. 快速定位
例如，需要对A10000单元格进行操作，首先要定位到这个单元格。如果不知道使用名称框，看来只能用鼠标左键单击屏幕右侧的“垂直滚动条”向下箭头，要让屏幕滚动到10 000行实在也不容易。正确的操作步骤如下：（1）在名称框中输入“A10000”（2）按回车键结束，Excel即自动选中A10000单元格，如图3.17所示。图3.17 用名称框快速定位
2. 快速选定区域
选定区域实际上是定位的扩展应用，选择的范围更大。在2.2节中介绍过使用名称框选择A1：A10000单元格区域的操作，这里不再赘述。
3. 用名称选择区域
如已在Excel中定义过区域名称（关于定义名称，将在第4章中详细讲解），通过名称框，可快速选择已定义的名称区域。如图3.18所示，Excel中已定义了5个名称，其中4个是区域名称，1个是常量名称。图3.18 名称管理器中已定义的名称
单击名称框右侧的下拉箭头，出现已定义的4个区域名称，如图3.19所示。图3.19 在名称框中选择已定义的区域名称
如选择名称“商场”，Excel会自动选取已定义的名称“商场”所在的B2：B10单元格区域，如图3.20所示，其他以此类推。图3.20 使用名称框选择已定义的名称区域3.1.3 快速条件定位
除了名称框之外，Excel中还有一个功能更加强大的工具——定位条件。相对于之前介绍的使用数据有效性、条件格式找到满足条件的单元格，定位条件是根据Excel内置的选项来定位，功能既强大又相对比较封闭。
操作时使用Ctrl+G快捷键或者F5键，弹出“定位”对话框，单击“定位条件”按钮，即可调出“定位条件”对话框，如图3.21所示。图3.21 “定位条件”对话框“定位条件”对话框中内容较复杂且有些难以理解，这里先对其中的选项做一个简要的介绍。表3.2 “定位条件”选项
对于初学者理解有一定的难度，这里通过几个例子，介绍一下“定位条件”中的常见应用。【例4】 批量计算合计数
如需对黄色区域计算合计数，如图3.22所示。图3.22 批量计算合计数
常规做法先在B5单元格输入公式，然后向右拖动复制，完成之后再做第8行和第12行。严格地说，上述做法已不属于批量操作，如有多行，则必须操作多次，效率不高。使用“定位条件”的操作步骤如下：（1）选取B2：H12单元格区域（注意不能选中A列，因A列存在合并单元格，合并单元格区域仅左上角的单元格中有内容）。（2）按F5键进入“定位条件”对话框。（3）在“定位条件”对话框中选取“空值”，单击“确定”按钮，如图3.23所示。此时Excel已选中B2：H12单元格区域中的所有空格。图3.23 定位空值（4）单击“开始”|“编辑”|“合计”命令，或者直接用Alt+=快捷键即可完成对所有行的合计操作。【例5】 批量清空公式计算的结果
对于上一个例子的操作结果，如果我们需要清空公式计算的结果，最佳方案还是使用“定位条件”，操作步骤如下：（1）～（2）同上例。（3）在“定位条件”对话框中选取“公式”，单击“确定”按钮，如图3.24所示。此时Excel已选中B2：H12单元格区域中所有具有公式的单元格。图3.24 定位公式（4）此时活动单元格在B5（名称框中可见），按Backspace键删除B5单元格的公式，按Ctrl+Enter组合键完成删除所有公式的操作。【例6】 报表以万元显示
对于财务及统计报表，编制时往往要精确到分，差一分钱都不行，而对于管理层，需要看到的数字可能是到万元，甚至仅需到百万元（视企业规模而定）。
快速调整报表的显示单位，可以通过“定位+选择性粘贴”来实现，操作步骤如下：（1）在工作表空白区域任意单元格（如F1单元格）中输入数字10 000，并选取复制这个单元格。（2）选取报表中有数字的部分即C2：D18单元格区域，进入“定位条件”对话框。（3）在“定位条件”对话框中选择“常量”，如图3.25所示，单击“确定”按钮，即选取操作区域中所有值为常量的单元格。图3.25 定位常量（4）单击“开始”|“剪贴板”|“粘贴”|“选择性粘贴”命令，调出“选择性粘贴”对话框，在运算中选择“除”，如图3.26所示。图3.26 选择性粘贴——除运算【例7】 复制可见单元格
某企业工资表的格式如图3.27所示，第2行数据有隐藏，现在需要把表头及3～4行内容复制出来。图3.27 含有隐藏行的工资表
如果在选取A1：E4单元格区域后直接复制粘贴，将会把隐藏的第2行也粘贴出来，正确的操作方法还是定位。操作步骤如下。（1）选取要操作的A1：E4单元格区域，F5键调出“定位条件”对话框。（2）在“定位条件”对话框中选择“可见单元格”，如图3.28所示。图3.28 定位可见单元格（3）接下去才是没有悬念的复制粘贴，Excel不会把隐藏的第2行粘贴出来。
职场点睛：定位操作是最值得推荐的技巧之一，学会这一招能快速选取特定的单元格，后续的操作就简单了。3.1.4 选取定位常用的快捷键
关于快捷键，在2.3节中已有介绍，这里再详细介绍一下与选取区域操作有关的几个快捷键。
如图3.29的数据源，A1：N10数据区域，第5行及E列、J列为空白，看看如何使用键盘快捷键在该区域中快速移动及选定。
1. 定位常用的快捷键
关于Tab键及Shift+Tab快捷键就不多说了，这里主要介绍一下使用Ctrl+箭头键快速移动到工作表中当前数据区域的边缘。图3.29 有空行空列的数据源
在图3.29的示例中，先选中A1单元格，测试反复按Ctrl+↓快捷键，第一次操作Excel会移动到A4单元格，第二次移动到A6单元格，第三次移动到A10单元格，第四次移动到A列最后一个单元格A1048576（这个和Excel的版本有关，如是2003版，则只有65 536行）。
读者可以测试一下选中A1单元格，反复按Ctrl+→快捷键，观察一下Excel中移动单元格的变化。
通过以上操作可以发现，Ctrl+箭头键是移动到工作表中当前数据区域的边缘，如有空行或者空列则视为不在当前数据区域中，需要下一次操作才能到达。
2. 选取区域常用的快捷键
Ctrl+箭头键是定位，加上Shift键，Ctrl+Shift+箭头键就是选取，选取还有一个特殊的快捷键Ctrl+A。
1） “Ctrl+Shift+箭头键”快捷键
仍以图3.29示例为例，先选中A1单元格，测试反复按Ctrl+Shift+↓快捷键，第一次操作Excel会选中A1：A4单元格区域，第二次选中A1：A6单元格区域，第三次选中A1：A10单元格区域，第四次则选中A列所有的单元格。
Excel理解的工作原理同Ctrl+箭头键，差异仅在于Ctrl+箭头键是定位到最后一个单元格，Ctrl+Shift+箭头键则是选取起点与终点之间的单元格区域。【例8】 隐藏工作表中不使用的区域
某公司需要统计各个业务员1～6月的销售额，为防止误输入，需要把表格中不使用的部分隐藏起来，操作步骤如下：（1）选取H列，Ctrl+Shift+→快捷键选中表格右侧未使用区域，右键隐藏。（2）选取第9行，Ctrl+Shift+↓快捷键选中表格下方未使用区域，右键隐藏，完成效果如图3.30所示。
职场点睛：这一招也很管用，先把不需要录入的区域全部隐藏起来，无疑大大降低了出错的概率。
2）Ctrl+A快捷键
Ctrl+A的作用是全选，但如果工作表包含数据，则按Ctrl+A将选择当前区域（活动单元格所在区域），再选一次Ctrl+A才是选择整个工作表。图3.30 快捷键隐藏工作表中未使用区域
仍以图3.29示例为例，如当前单元格不在A1：N10单元格区域中，按Ctrl+A为全选工作表；如当前单元格在A1：N10单元格区域中，按一次Ctrl+A仅能选中部分区域，再按一次Ctrl+A才是全选工作表。
本节示例文件：《3.1 轻松选择“特殊”区域.xlsx》。
3.2 数据的批量编辑
定位到了需要操作的单元格，之后就是数据编辑工作。本节将讲述常见的批量编辑技巧，如查找替换、分列、删除重复项等。3.2.1 不一样的查找和替换
说起查找必然会联想到替换，查找和替换功能密不可分，好比是一对关系密切的亲兄弟，查找是小弟、替换是大哥。Excel在设计的时候将其放在了同一个对话框中，仅是两个不同的选项卡，替换比查找多一行选项。严格地说，查找属于定位技术，替换才是编辑技术，这里将其放在一起讲解。
关于查找和替换，很多用户的印象就是Ctrl+F和Ctrl+H快捷键，其实如果点开“选项”按钮，查找和替换中的世界也很精彩，如图3.31所示。图3.31 “查找和替换”对话框
关于查找，这里想强调以下几点：□ 查找内容中可以包括通配符，通配符“？”代表1个字符，“*”代表一串字符；□ 查找命令作用范围可以是工作表也可以是工作簿；□ 查找搜索方式可以是按行，也可以是按列；□ 查找范围可以是公式、值和批注；□ 查找时，是否勾选“单元格匹配”，得到的结果可能是不一样的；□ 查找时可以按选定单元格的格式查找。
看到这里，还觉得查找和替换很简单吗？是不是和之前使用的不一样？下面通过几个例子来介绍一下查找的基本功能。【例9】 查找含指定内容的批注
如需要对工作表中已编辑区域中含有“业务员”字样的批注全部删除，操作步骤如下：（1）选取需要操作的单元格区域，如在工作表中仅选取1个单元格，视同操作范围为“范围”框中的设置。（2）按Ctrl+F快捷键调出“查找和替换”对话框，在查找内容框中输入“业务员”，查找范围设定为“批注”，如图3.32所示。图3.32 查找批注
如果第1步的时候已选择A1：D10单元格区域，则只能查找到该区域中的批注与“业务员”相关的单元格D2，如图3.33所示。图3.33 选定区域时查找批注的返回结果
如在做第1步操作时未选定区域，则Excel视同查找整个工作表，返回结果如图3.34所示。图3.34 未选定查找区域时的返回结果（3）按Ctrl+A快捷键全选，关闭“查找和替换”对话框回到工作表中，右键单击“删除批注”即可将查找到的批注一次全部删除。
读者还可以自行测试一下，如查找范围框中为“公式”、查找内容为“业务员”时的返回结果（查找返回的内容不包括含批注单元格）。如果此时勾选“单元格匹配”复选框，查找结果将仅是C1单元格。【例10】 查找指定的值
有时候要查找的值在工作表中清清楚楚，但Excel查找返回的却是其他值。如需要查找100，“查找和替换”对话框设置保持默认，如图3.35所示。图3.35 查找100
单击“查找全部”命令，返回结果如图3.36所示。
工作表中仅有3个单元格有内容，只有1个100，查找结果竟然是3个单元格全部返回。是不是Excel出错了呢？答案肯定是否定的，原因在于在查找时查找条件设置有误。图3.36 查找100的返回结果
如在“查找范围”框中将默认设置的“公式”改为“值”，A2单元格就不会被查到(A2单元格的公式中有100）；
如勾选“单元格匹配”选项，A3单元格不会被查到（在单元格匹配的条件下，Excel只会返回和查找内容完全一致的单元格）。【例11】 查找带格式的单元格
如需要查找工作表中填充颜色为黄色的单元格，操作步骤如下：（1）在“查找和替换”对话框的“格式”下拉列表中选择“从单元格选择格式”命令，如图3.37所示。图3.37 设置“从单元格选择格式”（2）待鼠标变成吸管状态，单击已有格式的单元格，如D5单元格，此时已选择格式可预览，如图3.38所示。图3.38 选取格式可预览（3）单击“查找全部”命令，则返回工作表中所有具有图3.38中选定格式的单元格，如在第1步操作时未选取区域，则示例文件中A43：D43单元格中因有类似的格式，也将被一并选中。【例12】 批量替换公式
查找和替换功能还能替换掉公式中的部分文本，如图3.39所示，已经计算了各省份的销售数量，需要再计算销售金额。图3.39 替换公式
在K2单元格中已录入条件求和公式“=SUMIFS（E:E，B:B，I2，D:D，J2）”并向下复制到K4单元格，如需再计算销售金额，就没有必要再写一遍公式，操作步骤如下：（1）将K2单元格中的公式“=SUMIFS（E:E，B:B，I2，D:D，J2）”写在L2单元格中，并向下拖动复制到L4单元格，此时K列和L列计算结果是一样的。（2）选取L列，Ctrl+H快捷键进入“查找和替换”对话框，查找内容为“E:E”，替换为“G:G”，单击“全部替换”按钮完成操作，如图3.40所示。图3.40 查找替换部分公式
此时L列的公式会批量变成“=SUMIFS（G:G，B:B，I2，D:D，J2）”，替换掉了SUMIFS函数的第1个参数。
职场点睛：这个例子还是比较简单的，但给了我们一个启示，就是公式中的内容也是可以替换的，可替换的不仅是函数的某个参数，在涉及文件路径时还可以替换掉链接路径中的文件夹名称、文件名、单元格引用等，实现数据的批量更新。实际工作中遇到类似问题时可以尝试一下公式中的部分替换，在表格更新及批量处理大量公式时效率较高。3.2.2 分列，不得不说的技巧
财务软件及ERP系统已经逐渐普及，日常工作中处理的数据很多都是从系统中导出，但就是系统中的数据，经常遇到导入Excel后无法正常计算的情况。处理此类问题，分列往往能起到意想不到的作用，其功能已经远远超出“分列”这两个字本身的含义，值得用户用心体验。
1. 处理无法计算的数字
系统中导出的数字复制到Excel后，出现部分数字无法计算，如图3.41所示。图3.41 无法计算的数字
对于此类问题，分列是最好的解决方案，操作步骤如下：（1）选取E列，单击“数据”|“数据工具”|“分列”命令，弹出“文本分列向导-第1步，共3步”对话框，如图3.42所示。图3.42 “文本分列向导”对话框（2）直接单击“完成”按钮，可见原来靠左显示的文本数据都变成靠右显示的真正的数值，如图3.43所示。图3.43 分列操作后的结果
如果E列数据原先的单元格格式为文本，需要增加一个步骤，通过“设置单元格格式”对话框，将文本转换为数值，然后再做分列。否则数字能计算，但格式还是文本状态。
2. 处理不规范的日期
Excel中的日期是一个特殊的数值，关于日期格式，和操作系统版本及用户设置有关，国内用户默认设置日期为年月日数据以“-”或者“/”连接。如是其他格式，Excel并不将其作为日期看待，比如“2012.3.31”之类，往往不能正确计算，如图3.44所示。
试读结束[说明：试读内容隐藏了图片]