第1章 职场必备Excel技能
在使用Excel 2016时，首先要熟悉软件功能和常用操作技巧。这些功能和技巧是数据管理工作中必备的，本章就从Excel 2016软件的界面功能、实用技能和多表操作三个方面来进行介绍。
本章内容知识点的思维导图见图1-1。
图1-1 第1章内容思维导图
1.1 Excel 2016界面工具
Excel 2016在界面和功能上都做了很多改进。如果对于软件界面不熟悉，可以通过本节介绍的Tell Me按钮和快速访问工具，来搜索和收藏自己熟悉的命令按钮。
1.1.1 Tell Me（告诉你）按钮在哪里
使用Office 2016时，很多人在使用时找不到常用的命令。注意到图1-2软件右上方的小灯泡了吗？
图1-2 操作搜索
这是微软在Office软件新版本中，让用户获取帮助的一种新方式——搜索与帮助（Tell Me，以下简称TM）。只需在TM框中输入操作需求，它便会提供相关操作的命令或帮助，从而大大提高工作效率。
应用情景 1
糟糕，又忘了VLOOKUP函数怎么用了？
别急，只需单击TM框或按快捷键【Alt+Q】后，输入“VLOOKUP”，就可以查看帮助文档，如图1-3所示。
图1-3 通过TM框获取函数VLOOKUP函数帮助
应用情景 2
我要做多个表格数据汇总，不知道用什么工具按钮？
只需在TM框“汇总”，就可以智能搜索到所有相关工具，单击想要的工具即可。比如“合并数据”，单击后自动打开“合并计算”工具窗口，如图1-4所示。
图1-4 通过TM框智能搜索命令工具
1.1.2 快速访问工具栏
默认情况下，Excel 2016快速访问工具栏只包含4个按钮，分别是“保存”“撤销”“恢复”和“触摸/鼠标模式”。有的人很少去使用它们，如图1-5所示。
图1-5 默认快速访问工具栏
快速访问工具栏的功能从名称上就能看出来，就是能自定义用户经常使用的按钮，并快速找到它。不要小瞧这个工具，除了能提高工作效率外，还能把看不到的按钮添加上去。
下面介绍如何为快速访问工具栏添加命令按钮，分别是系统命令、常用命令和后台命令。
1.添加系统命令按钮
单击工具栏右侧的下拉按钮，在弹出的菜单中选择要添加到快速访问工具栏中的命令，如图1-6所示。已添加的命令左侧会显示一个对号标记。
图1-6 为快速访问工具栏添加系统命令
2.添加常用命令
右击功能区中经常使用的命令图标，在弹出的菜单中选择“添加到快速访问工具栏”，如图1-7所示。
图1-7 右键添加常用命令到快速访问工具栏
如果要添加的命令没有出现在功能区中，或者希望对快速访问工具栏中的命令位置进行重新排列，那么需要使用下面介绍的第3种方法。
3.添加后台命令
在快速访问工具栏上或者功能区任意位置，单击鼠标右键并选择【自定义快速访问工具栏】命令，进入自定义快速访问工具栏界面，如图1-8所示。
图1-8 自定义快速访问工具栏
从左侧列表框中选择要添加的命令，通过单击“添加”按钮将所选命令添加到右侧列表框中，如图1-9所示。可以在左侧“从下列位置选择命令”下拉列表中选择命令所属的类型，下方的列表框会根据所选类型自动筛选其中包含的命令。左侧命令列表是根据命令首字符排序的。对于右侧列表框中的命令，可以在选中命令后，使用最右侧的
和
按钮调整命令顺序。
图1-9 为快速访问工具栏添加后台命令
图1-9所示的是添加后台命令“朗读单元格”，这个功能适用于电子版与纸质版文件校对，也用于眼睛看文字和数据累的时候，闭上眼睛用耳朵听计算机读出这些文字和数据。关于朗读单元格功能共有4个命令，可参考图1-10。
图1-10 朗读单元格系列命令
在默认情况下，快速访问工具栏位于标题栏的左侧、功能区的上方。笔者习惯将其置于功能区下方，以便显示更清晰。右击快速访问工具栏，在弹出的菜单中选择“在功能区下方显示快速访问工具栏”命令，即可将其移动到功能区下方，效果如图1-11所示。
图1-11 将快速访问工具栏置于功能区下方
如果办公室的计算机中已经设置好了快速访问工具栏，要在家中的计算机也做同样的设置，可以用导入法。先在办公室的计算机上的自定义功能区或自定义快速访问工具栏窗口中单击“导入/导出”-“导出所有自定义设置”，保存导出的OfficeUI文件，再将该文件复制到家中的计算机上，单击“导入自定义文件”即可，如图1-12所示。
图1-12 快速访问工具栏导入导出设置
1.1.3 恢复未保存文档
有时在编辑Excel文档时，由于断电或其他原因导致文档未保存就异常退出，当再次打开文档时，却发现之前编辑的内容不见了。那么该如何恢复没有保存的Excel文档呢？
首先要做的事情就是开启Excel自动保存文档功能，可以让数据丢失的可能性减少到最低，如图1-13所示，设置自动保存的时间和自动恢复的文件位置。
图1-13 文档自动保存设置
如果遇到异常情况，可以在之前所设置的“自动恢复文件位置”目录中恢复未保存的文档，也可以通过命令来恢复未保存的工作簿，如图1-14所示，功能简单却可以解决大问题。笔者建议还是养成好的文档保存习惯，在文档编辑过程中经常保存，快捷键是【Ctrl+S】。
图1-14 恢复未保存文档设置
1.1.4 新工作簿的默认字体
在使用Excel时，Excel 2016版本默认字体是“等线”，Excel 2007-2013版本默认是“宋体”，要做调整时，每次都要全选所有单元格，然后将字体设置为“微软雅黑”。更恼人的是，新建工作表时，又要重新设置一次。
其实不用这么麻烦，在Excel选项中可以设置新工作簿的默认字体，如图1-15所示。只有重新启动Excel软件，新设置才能生效。
图1-15 设置默认字体
1.2 实用技能
作者见过很多学员在数据表格的选择和操作时，仍然采用手工的方法，费时费力。本节主要介绍数据的快速选择和定位，选择性粘贴和重复操作，让读者在面对规律性的数据选择和操作上，更快一步。
1.2.1 区域选择
要选取一个单元格区域，除了常规的鼠标拖动，还有其他哪些方法？如果有成千上万行数据要选取，怎么操作最快？在包含隐藏行列的情况下，怎样只选择可见部分数据？其实，这些复杂的操作，只需几个快捷键，就可以让工作效率提升N倍。
1.选择首、尾单元格
单击任何一个单元格，按＜Ctrl+方向键＞，可以实现在同一行和同一列首末单元格，如图1-16所示。如果有空白单元格，则定位到与空格相邻的非空单元格。
图1-16 定位行列首尾单元格
其实，在Excel中无论当前活动单元格在哪里，都可以通过＜Ctrl+Home＞和＜Ctrl+End＞快捷键来快速定位区域首末单元格，如图1-17所示。
图1-17 定位矩形区域首尾单元格
2.选择连续区域
很多人选择连续表格区域是直接按住鼠标向下拖动。如果是几十万行的大表格，这样要拖到什么时候才能结束？如果要选择连续区域，可以用快捷键＜Ctrl+Shift+方向键＞将当前活动单元格扩展到同一行或同一列中不间断的最后一个非空单元格，如图1-18所示。
图1-18 选择连续区域
3.选择连续矩形区域
如果要选择规范表格范围，可以单击数据区域的某一个单元格，然后使用＜Ctrl+A＞快捷键。这个快捷键可以选中活动单元格所在的【当前区域】，如图1-19所示。
图1-19 选择连续矩形区域
所谓【当前区域】，指的是当前单元格周围包含数据的连续相邻（包括斜角方向相邻）单元格的最大矩形范围，工作中常常是无间断的矩形区域。
4.选择不连续区域
对于选择不连续区域有两种方法，快捷键不同，分别是＜Ctrl＞和＜Shift+F8＞。
按住＜Ctrl＞键同时用鼠标拖选区域，可以选中多个不连续的单元格区域，如图1-20所示。
图1-20 选择不连续区域方法 1
也可以先按下＜Shift+F8＞快捷键，然后用鼠标选择不同的区域即可，这种方法常用于选择较多区域范围，如图1-21所示。
图1-21 选择不连续区域方法 2
这两种方法选定的多个不连续区域称之为“多重选定区域”，多重选定区域通常情况下不能进行整体复制和剪切操作（同行同列的除外），但是可以进行数据输入、设置格式等基本操作。
5.选择指定区域
例如要选取区域范围A1：B100，最简便的方法就是在左上角的名称框中输入【A1：B100】，按＜Enter＞键即可，如图1-22所示。
图1-22 选择指定区域
1.2.2 选择性粘贴
在使用Excel时，用得最多的功能恐怕就是复制、粘贴了。但许多人不清楚，Excel还有个非常实用的功能，就是选择性粘贴，如图1-23所示。
选择性粘贴的快捷键是＜Ctrl+Alt+V＞，之后会出现图1-23的命令窗口，也可以直接单击鼠标右键，出现图1-24所示的菜单。
下面介绍职场中选择性粘贴常用的6个功能，如表1-1所示。
图1-23 选择性粘贴窗口
图1-24 选择性粘贴右键菜单
表1-1 选择性粘贴常用的6个功能
1.粘贴值
通过函数公式计算的结果，如果引用数据删除或将结果区域复制到新位置，常常会出现错误提示，解决办法就是采用粘贴值的方法。复制包含公式的区域，单击鼠标右键并单击粘贴值按钮
即可。在Excel 2007～2010版本中，单击鼠标右键不会直接出现粘贴值按钮
，可以单击【选择性粘贴】，出现命令窗口后，选择【数值】，如图1-25所示。
图1-25 粘贴数值
粘贴值功能去除了公式的同时，也去除了源数据单元格的格式。
2.保留原列宽
列宽不等的表格直接复制、粘贴后，列宽都发生了变化，再重新调整会耽误不少时间。这时就要用到保留原列宽按钮
，如图1-26所示，列宽和原表完全相同。
图1-26 粘贴并保留原列宽
该功能同样支持保留行高，前提是只有选取整行复制时，行的高度才会复制过去。
3.行列转置
常常会遇到数据表格行、列互换的情况，这时可以在粘贴时选择行列转置按钮
，效果如图1-27所示。
图1-27 行列转置效果
4.表格变图片
如果希望单元格区域复制到新位置时显示效果不变，可以用到粘贴图片。在选择性粘贴菜单中，按钮
表示静态图片，图片显示内容不会根据源数据的变化自动更新；按钮
表示动态图片，图片显示内容可以根据源数据的变化自动更新，这也是常说的“照相机”的功能。
如图1-28所示，有2015年～2017年3张年度销售数据表，每张表格通过数据透视表汇总出当年的销售数据，“分析表”是销售数据分析表，通过【复制】和【链接图片】的功能，可以将各表统计结果在首页上快速显示，并能根据每年数据变化而自动更新。
该功能常用于多工作表中数据图表在另一张表上显示，作为工作汇报的摘要页面，快速显示汇报结论，符合金字塔原理的“结论先行”原则。
需要说明的是，“链接图片”功能仅支持Excel软件不同工作簿或工作表之间的动态链接。如果把图片复制到PowerPoint和Word软件中，图片不会根据源数据的变化自动更新。
图1-28 表格变图片效果示意
5.跳过空单元格
在复制、粘贴时，如果源数据中的空单元格不需要粘贴，就要用到【跳过空单元格】功能，如图1-29所示。
图1-29 跳过空单元格功能
这个功能常常用于数据的规范整理过程中。
示例1见图1-30，将B列数据复制到A列，用【跳过空单元格】功能可以让两列文本快速合并成一列。
图1-30 跳过空单元格示例 1
示例2见图1-31，是对费用进行校对调整。B列是费用列表，C列是校对审核过的费用，其中C列空白单元格表示B列的费用无误，C列有数据的单元格表示对应的B列的费用需要更新调整。将C列数据复制到B列，用【跳过空单元格】功能可以实现费用更新，实现的效果如E列。
图1-31 跳过空单元格示例 2
6.批量运算
如果要对已有数据做批量加减乘除运算，一般都是通过函数计算得出的结果复制、粘贴到原来的位置。现在使用选择性粘贴，可以在数据原本位置直接计算出结果。
工作中经常会遇到万元与元的转换，如图1-32所示，单位是元的利润表，需要转换成单位是万元。
图1-32 单位是元的利润表
只需在区域外单元格中输入10000，复制10000，选中需要转换的区域，调出选择性粘贴框，选择“数值”和“除”，然后确定就可以了，步骤如图1-33所示。
图1-33 选择性粘贴运算
转换后的表格如图1-34所示。
图1-34 单位是万元的利润表
如果在选择性粘贴中没有选择第3步的“数值”，就会把10000所在单元格的格式也复制过去，影响表格的美观。
工作中，以下这些场合都能用到此方法：
•对计量单位的转换；
•文本形式存储的数字转换成数值格式可以用以上方法乘1或者除1；
•给指定区域的数值加、减、乘、除固定数值。
1.2.3 使用＜F4＞键重复上一次操作
是否经常遇到以下这几种情形？
•手动标注多处问题数据；
•有多处要合并的单元格；
•在表中插入多行。
如果在Excel中需要多次重复操作，先给软件做个示范，然后使用快捷键＜F4＞即可自动重复刚才的操作。具体在应用时要看使用的键盘，部分键盘需要辅助功能按键＜Fn+F4＞。
情形1：快速设置单元格格式
先手工设置某个单元格填充为红色，然后选择指定数据范围，按＜F4＞键即可快速填充红色，如图1-35所示。
图1-35 使用＜F4＞键设置单元格格式
情形2：快速合并单元格
如图1-36所示，先手动合并7月的两个单元格，然后选择8月区域，按＜F4＞键快速进行合并，9月也是同样的方法。
图1-36 使用＜F4＞键设置合并单元格
情形3：批量插入或删除空行
先手工在表中插入1行，然后选取指定行，按＜F4＞键就可以自动插入行，如图1-37所示。
图1-37 使用＜F4＞键可快速插入行
1.2.4 使用＜F5＞键快速定位
Excel软件中的定位是十分有用的功能，它可以根据单元格数据或格式的属性来有条件地选中这些单元格。按＜F5＞键或组合键＜Ctrl+G＞可以打开定位对话框，单击【定位条件】就可以在其中选择所需的条件，如图1-38所示。常用的条件包括常量、公式、空值和可见单元格等。
图1-38 定位与定位条件
在使用定位功能前，先选定一个区域，那就会在这个区域内查找和选取符合条件的单元格，否则就会在整个工作表中进行查找。
情形1：定位空值
当需要对报表中的空白单元格进行编辑时，如何快速选中空白单元格？选择数据范围，在定位对话框中选择“空值”，可以快速选取区域内空白单元格，如图1-39所示。
情形2：定位公式
对于报表，如果想知道哪些单元格数据是由公式计算出来的，可以用到定位中的“公式”，如图1-40所示。这样也可以快速对公式进行保护和隐藏。
图1-39 定位空值
图1-40 定位公式
情形3：定位文本型数字
有时候使用SUM求和的结果和手工计算不一致，很可能是文本型数字搞的鬼，在定位时选择“常量”中的“文本”，能将文本型数字快速定位出来，如图1-41所示。
图1-41 定位文本型数字
情形4：定位差异数据
工作中经常需要核对两行或两列内容是否完全一致，除了可以用IF公式来判断，还可以用定位功能来实现。如图1-42所示，先选中需要比较的两列数据
图1-42 定位差异数据
（B2：B13），在定位条件中选择“行内容差异单元格”，确定后把不同的5个单元格快速定位选择出来了。
情形5：定位可见单元格
对做了数据筛选或者隐藏行列的表格，如果只选择可见单元格，也可以用定位功能实现，如图1-43所示。
图1-43 定位可见单元格
该功能也可以使用快捷键＜ALT+；＞（分号；）快速实现。
1.3 多表操作
在处理多个表格数据时，是否遇到过下面这些囧境：
•在包含很多Sheet的Excel文件中，要找到其中一个Sheet，翻了半天还没翻到那一页；
•比较多个表格数据不断切换窗口查看；
•多个相同结构的表格做同样的操作，一个一个表格设置。
其实这些都是可以避免的，本节就介绍这些操作方法。
1.3.1 多工作表快速定位跳转
多工作表之间定位和跳转其实可以又快又准的。先找到工作表左下角的箭头位置，如图1-44所示。
图1-44 多工作表之间定位跳转位置
使用表1-2总结的快捷键可以实现工作表的快速定位和跳转。
表1-2 多工作表之间定位跳转的快捷键
需要说明的是，表中的“滚动”只是把工作表名称从折叠状态展开，需要单击工作表名称才能定位到该工作表；“定位”是直接选择并激活指定工作表。
1.3.2 两文件并排查看
在使用Excel的时候，都会遇到这样的问题：需要查看多个工作簿，所以就不得不在各个工作簿之间来回切换。这样操作不仅很麻烦，而且很容易出错。这里就给大家介绍一下如何在一个窗口下同时显示两个工作簿，并能实现同步滚动，对于比较两个文件的数据特别方便。
这个功能是“并排查看”，先看下它的按钮位置，如图1-45所示。如果这个按钮不可用，说明还没有打开两个Excel文件窗口。
图1-45 窗口并排查看的位置
比如打开了两个文件，分别是“2015业务数据”和“2016业务数据”，第一次用“并排查看”功能时，两个窗口有可能是上下排列，按图1-46所示的3个步骤实现垂直排列，并能实现同步滚动。
图1-46 并排查看方向调整
如果是一个Excel文件的两个工作表，要实现并排查看，需按图1-47所示位置新建窗口才可以。
图1-47 新建窗口
1.3.3 工作组批量操作多工作表
在很多情况下，都会需要同时在多张工作表的相同单元格中输入同样的内容。
比如已有9个城市的1月销售数据表，现在到2月份了，每个工作表的标题要统一修改为2月，可以按图1-48的操作步骤进行批量操作。
图1-48 工作组批量操作
工作组的操作可以批量输入相同内容和公式，从而解决多工作表同步修改或计算的问题，节省工作时间。