分类：Excel 2016实用技巧大全

包含212个案例，每个案例都分为疑难场景、解题步骤和知识扩展3部分，其中疑难场景用于描述问题；解题步骤用于阐述解决问题的方法，按步骤先后详细地罗列出来，同时配有操作过程与操作结果的截图，并在图中提供箭头指示操作对象，从而帮助读者快速掌握解题方法；知识扩展用于补充解题方案中部分代码的相关语法、思路，或者提供其他解题方案，使读者对每个步骤了解得更透彻，并可举一反三。

Excel 2016 如何保护工作簿结构，避免被破坏

图2.15所示是仓库报表，要求永远保持入库表、出库表和退货表3个工作表，禁止删除工作表或新建任意工作表，以及禁止修改工作表名称，如何才能实现此需求呢？

图2.15 仓库报表

解题步骤

禁止修改工作表名称，以及禁止增加、删除工作表，其实就是就保护工作簿结构。保护工作簿结构的操作步骤如下。

1.单击功能区的“审阅”→“保护工作簿”，弹出“保护结构和窗口”对话框，界面如图2.16所示。

图2.16 “保护结构和窗口”对话框

2.在图2.16所示的对话框中输入密码，单击“确定”按钮后再次输入密码，然后单击“确定”按钮，完成保护工作簿的操作。

3.单击工作表下方的“新工作表”按钮（图标），可以发现该按钮已经禁止使用。

4.右击工作表名称，可以发现“删除”菜单和“重命名”菜单已经禁止使用，效果图如图2.17所示。

图2.17 禁止删除工作表和修改工作表名称

知识扩展

1.保护工作簿原本包含保护结构和窗口，不过从Excel 2013开始，每个工作簿都拥有独立的窗口，因此不需要再保护窗口，微软将“保护结构和窗口”对话框中的“窗口”禁用了。

2.工作簿保护密码和工作表保护密码一样脆弱，可以瞬间被破解。

3.保护工作簿仅仅是禁止破坏结构（由哪些工作表组成），而不是禁止打开工作簿，要和保护文档区分开来。

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

解题步骤

Excel 2016的文件菜单中有专门的按钮用于加密文件，在“另存为”对话框中也提供了加密文件的功能。下面以通过文件菜单加密文件为例，操作步骤如下。

1.单击功能区的“文件”→“信息”→“保护工作簿”→“用密码进行加密”，操作界面如图2.12所示。

图2.12 打开加密对话框

2.在弹出的“加密文档”对话框中输入密码，当单击“确定”按钮后会要求确认密码，再次输入密码后即加密成功（见图2.13）。

图2.13 输入保护密码

3.保存并重启工作簿，Excel会弹出如图2.14所示的对话框，要求输入密码，否则无法打开工作簿。

图2.14 要求输入密码

知识扩展

1.加密文件的安全性远远高于加密工作表，破解难度更大。谈加密文件的安全性需要区分文件格式，如果是“*.xls”格式，那么破解难度稍微小一点，如果是“*.xlsm”或“*.xlsx”格式，采用数字加英文、标点为密码且长度超过10时，破解成功的概率接近0。

2.加密工作表的目的是防止意外修改，加密文件的目的是防止无密码者随意查看数据。

3.本例的加密方式不属于保护工作簿，在下一个疑难中会讲解加密工作簿的具体步骤。

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

Excel 2016 可以按区域设置密码吗？

图2.5所示的工作表中包含三个班级的成绩表，要求此表允许三个老师可以查看，但是三个老师只能修改自己的区域。例如，一班的老师只有修改A1：B11区域的权限，二班老师只有修改E1：F11区域的权限，三班老师只有修改G1：H11区域的权限。

图2.5 成绩表

能否为3个班级的区域指定3个不同的密码，然后每个老师只能修改自己的区域呢？

解题步骤

Excel提供了一个“允许用户编辑区域”工具，用于对同一个工作表中的区域指定不同的密码，具体操作步骤如下。

1.单击功能区的“审阅”→“允许用户编辑区域”，弹出如图2.6所示的对话框。

图2.6 “允许用户编辑区域”对话框

2.单击对话框中的“新建”按钮，弹出“新区域”对话框，然后将标题修改为“一班”，将引用单元格修改为“=A1：B11”，直接选择区域就会自动产生区域地址，最后将区域密码设置为“a”，操作界面如图2.7所示。

3.单击“确定”按钮，然后在弹出的新对话框中再次录入密码“a”，操作界面如图 2.8 所示。

图2.7 指定第一个区域和密码

图2.8 确认密码

4.重复步骤2和步骤3，继续添加标题为“二班”、“三班”，区域为“=D1：E11”、“=G1：H11”，密码为“b”、“c”的两个区域，添加完成后“允许用户编辑区域”将显示为如图2.9所示的状态。

图2.9 添加3个区域后的对话框状态

5.单击“允许用户编辑区域”左下角的“保护工作表”按钮，然后在图2.10所示的对话框中输入密码“qwert”，单击“确定”按钮后再次确认密码。

图2.10 对工作表加密

6.返回工作表后，在一班A1：B11区域的任意单元格输入字符，Excel会弹出的图2.11所示的对话框，要求取消密码保护，否则禁止修改。

图2.11 取消“一班”区域的密码

7.输入密码“a”，然后A1：B11区域将自动解锁，允许随意编辑。

8.在D1：E11区域输入字符，同样会弹出如图2.11所示的对话框，要求输入密码，不过此处必须使用密码“b”，否则会解密失败。

9.在A1：B11、D1：E11和G1：H11以外的区域输入字符，Excel仍然要求输入密码，否则禁止修改。不过在A1：B11、D1：E11和G1：H11以外的区域的密码是“qwert”，而不是“a”、“b”、“c”中的任何一个。

知识扩展

1.“允许用户编辑区域”可以对多个区域指定不同的密码，适用于分区域控制权限的工作表，让多人各自控制自己的区域。

2.“允许用户编辑区域”在指定分区密码后，必须搭配工作表保护使用，否则分区密码无效。

3.输入分区密码后，仅仅是暂时解除密码、允许编辑，保存并重启工作簿后密码仍然存在，需要重新输入密码才可以继续编辑单元格。

4.分区密码和工作表密码一样脆弱，极易被破解，因此不能过于依赖此工具。

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

Excel 2016 如何加密工作表？

通常领导的桌上有很多文件夹和物品，任意物品掉在键盘上都可能破坏单元格中的数据。同时由于领导通常仅需查看报表，不需要编辑数据，因此最安全的办法是将报表发送给领导前保护所有的工作表数据，只能查看、不能编辑，从而避免误删、误改。

那么应该如何保护工作表数据？

解题步骤

在审阅选项卡中，Excel提供了安全性相关的菜单，具体操作步骤如下。

1.单击功能区的“审阅”→“保护工作表”，弹出“保护工作表”对话框。

2.在对话框中的文本框中输入密码，操作界面如图2.1所示。通常密码不少于6位。

3.单击“确定”按钮后Excel会继续弹出如图2.2所示的对话框，要求确认密码。在其中再次输入与步骤2相同的密码，然后单击“确定”按钮。

图2.1 输入密码

图2.2 确认密码

保护成功后，审阅选项卡中原本名为“保护工作表”的按钮将自动变成“撤销工作表保护”按钮。

4.随意修改任意单元格的值，Excel会弹出如图2.3所示的提示框，表示当前单元格禁止修改。

图2.3 禁止修改的提示框

5.单击功能区的“审阅”→“撤销工作表保护”按钮，然后输入密码，单击“确定”按钮后再修改任意单元格，将不再有限制。如果忘记密码则无法编辑。

知识扩展

1.不允许批量设置工作表的密码，只能一次设置单张工作表的密码。

2.工作表密码只能防止误删数据，并不能避免破解。解除工作表密码比较简单，有很多软件可以实现。笔者自己开发的 Excel 插件“E 灵”就可以瞬间解除工作表密码，下载地址：http：//excelbbx.net/Eling.rar。以下网址则是动画演示：http：//excelbbx.net/Eling/JieChuMiMaBaoFu.htm。

3.保护工作表并非总是保护工作表中所有单元格，而是只保护工作表中“锁定”属性打钩的单元格。Excel 2016默认有1 048 576×16 384个单元格，每个单元格的“锁定”属性都是单独控制的。如果不想保护A10：C20区域，那么选择A0：C20区域，然后按组合键＜Ctrl+1＞，弹出“设置单元格格式”对话框，并将“保护”选项卡中的“锁定”复选框去掉勾即可，操作界面如图2.4所示。

图2.4 取消单元格的“锁定”属性

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

Excel 2016如何修改批注的默认签名？

在单元格中添加批注时Excel会自动在首行生成签名，该签名可能是“Administrator”，也可能是“Windows 用户”，还有可能是“ABC”、“User”，效果如图1.48所示。

图1.48 批注签名

是否能将批注的默认签名修改为我的昵称“天使的翅膀”呢？

解题步骤

批注签名来自安装Office时输入的用户名称，允许后期修改，修改步骤如下。

1.单击菜单“文件”→“选项”，弹出“Excel选项”对话框。

2.将“常规”选项卡右边的用户名修改为“天使的翅膀”，操作界面如图1.49所示。

图1.49 修改用户名

3.单击“确定”按钮保存设置，同时返回工作表界面。

4.再次插入批注，批注的签名会自动变成“天使的翅膀”。

知识扩展

1.安装Windows和Office时都会提示输入用户名，两者通常是不同的。在Excel中生成批注时调用的用户名是安装office时输入的用户名。

2.Excel不允许用户名留空，假如用户删除了用户名，Excel会自动调用安装Windows时输入的用户名到Excel中。假设安装Windows用的名字是“ABC”，那么删除Excel的用户名后添加批注时会自动调用“ABC”作为签名。

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

Excel 2016是否可以禁止输入网址或邮箱地址时产生超链接？

在单元格中输入网址或邮箱地址后，Excel会自动在该单元格产生对应的链接，即单击网址后可打开对应的网站，单击邮箱地址后则打开发送邮件的对话框，双击单元格同样会打开对应的链接。图1.45是输入邮箱地址后产生的超链接展示。

图1.45 输入邮箱地址后自动产生超链接

是否可以关闭此功能，从而在双击单元格时不打开链接而是直接进入编辑状态呢？

解题步骤

通过设置Excel选项可以关闭自动转换超链接的功能，具体步骤如下。

1.单击菜单“文件”→“选项”，弹出“Excel选项”对话框。

2.打开“校对”选项卡，然后再单击右方的“自正更正选项”按钮，操作界面如图1.46所示。

图1.46 单击“自动更正选项”按钮

3.打开“自动更正”对话框中的“键入时自动套用格式”选项卡，然后取消选择“Internet及网络路径替换为超链接”复选框，操作界面如图1.47所示。

图1.47 关闭自动转换成超链接的功能

4.单击“确定”按钮保存设置，同时返回工作表界面。

5.在单元格中输入网址或邮箱地址，Excel不再自动为它们添加超链接。

知识扩展

1.Excel自动对网址或邮箱地址添加超链接的本意是方便工作，然而当用户只需要网址和邮箱地址本身时，它会影响工作。读者可以根据自己的实际需求决定是否关闭此功能。

2.如果只打算取消某一个单元格的网址链接，而不是关闭 Excel 的自动添加链接的功能，可以右击单元格，然后从右键菜单中选择“取消超链接”选项即可。

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

Excel 2016 如何对“快速访问工具栏”添加常用命令

Excel 2016将数百个命令分放在多个功能区选项卡中，要调用这些命令需要在不同选项卡中切换，不太便利，因此微软公司为Excel设计了快速访问工具栏，它总是固定在工作表界面的顶部，不需要切换选项卡，因此调用命令比较快捷。

在快速访问工具栏中默认只有保存、撤销和恢复3个按钮，效果如图1.40所示，可单击右方的“自定义快速访问工具栏”按钮添加新建、打开、电子邮件、快速打印等选项。是否可以将计算器和朗读单元格两个功能也添加到快速访问工具栏呢？

图1.40 默认状态的快速访问工具栏

解题步骤

通过“自定义快速访问工具栏”可以将任意工具添加到快速访问工具栏中，以计算器和朗读单元格两个功能为例，操作步骤如下。

1.单击快速访问工具栏最右端的自定义快速访问工具栏，从弹出的列表中选择“其他命令”。

2.从“从下列位置选择命令”的下拉列表中选择“不在功能区中的命令”，操作界面如图1.41所示。

图1.41 选择“不在功能区中的命令”

3.将滚动条向下拉，大概拉到三分之一左右即可看到“计算器”，选中“计算器”并单击“添加”按钮，添加结果如图1.42所示。

图1.42 将“计算器”添加到快速访问工具栏

4.重复步骤 3，将后面的“朗读单元格”也添加到快速访问工具栏中，单击“确定”按钮后快速访问工具栏将显示为如图1.43所示的效果。

图1.43 添加结果

其中计算器在快速访问工具栏中显示的名称是“自定义”，而朗读单元格工具的名称则为“朗读单元格”。

5.单击快速访问工具栏的“自定义”，Excel会弹出一个计算器。

6.在A1单元格中输入“E灵——Excel制表插件之精灵”，然后选中A1单元格，单击快速访问工具栏的“朗读单元格”，如果计算机的音响设备已经插好且已开启电源，声卡驱动也正常，那么可以听到朗读声。Windows7 和 Windows8 中文版都集成了汉语发音引擎，可以正确识别汉字和英文单词。

知识扩展

1.Excel 2016 有一两百个功能都没有放在选项卡中，可以通过自定义快速访问工具栏将常用的功能按钮添加到快速访问工具栏，方便调用；也可以新建一个选项卡，然后将这些功能按钮放在新选项卡中调用。

2.在快速访问工具栏右键单击按钮，从右键菜单中选择“从快速访问工具栏删除”即可删除自定义的新按钮，也可以在图 1.42 所示的界面单击“重置”按钮，从而一键删除所有自定义的新按钮。

3.也可以将计算器、朗读单元格等按钮放在新的自定义选项卡中，类似于图1.44所示的效果。操作方法是：打开“Excel 选项”对话框，然后在“自定义功能区”选项卡的右方单击“新建选项卡”，并通过“重命名”按钮修改选项卡的名字和组的名字，通过“上移”按钮调整新选项卡的位置，最后将计算器和朗读单元格两个按钮添加到新选项卡的组中（见图1.44），且通过“重命名”按钮将计算器的默认名称“自定义”修改为“计算器”。

图1.44 将计算器和朗读单元格添加到新选项卡

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

Excel 2016如何关闭自动更新链接？

当工作簿中某个单元格引用了其他工作簿的值时，打开工作簿时就可能会产生如图 1.38 所示的提示对话框。每天反复多次单击“更新”或“不更新”，会浪费不少时间，现在要求Excel不弹出该提示对话框，用什么方法能实现呢？

图1.38 更新外部引用的提示信息

解题步骤

通过设置Excel选项可以关闭以上提示信息，具体步骤如下。

1.单击菜单“文件”→“选项”，弹出“Excel选项”对话框。

2.打开“高级”选项卡，然后取消选择右边的“请求自动更新链接”的复选框，操作界面如图1.39所示。

图1.39 关闭自动更新提示

3.单击“确定”按钮保存设置，同时返回工作表界面。

4.关闭Excel 2016，重新打开该工作簿，Excel 2016不再弹出如图1.38所示的提示框。

知识扩展

1.当工作簿中有任意单元格通过公式引用其他工作簿的某个单元格时，打开工作簿时Excel会提示用户是否更新公式的值，如果数据源有更新，那么更新活动工作簿的公式可以确保公式总是引用最新的值。如果可以确定数据源不会再更新，那么关闭提示信息能节约文件开启时间。

2.如果原文件不存在了，或者移动了位置、修改了工作簿名称，那么一定无法更新公式的值，此时关闭图1.38所示的提示框才是上策。

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

Excel 2016可以关闭受保护的视图吗？

打开部分工作簿后无法正常编辑，选项卡中的大部分菜单都呈灰色禁用状态。仔细观察能发现Excel界面顶部的工作簿名称后面有一个“受保护的视图”，在编辑栏上方也有“受保护的视图”标示（见图1.35），只有单击右方的“启用编辑”按钮后才能编辑和另存工作簿。

图1.35 受保护的视图

如何才能避免产生这种提示，从而缩短操作时间呢？

解题步骤

Excel提供了相应的选项，用于控制哪些情况下产生受保护的视图，可以按以下步骤关闭此功能。

1.单击菜单“文件”→“选项”，弹出“Excel选项”对话框。

2.打开“信任中心”选项卡，然后单击右方的“信任中心”选项，操作界面如图1.36所示。

图1.36 打开“信任中心”界面

3.在“信任中心”对话框中单击“受保护的视图”，然后取消选择右方三个复选框，操作界面如图1.37所示。

图1.37 取消受保护的视图

4.单击“确定”按钮保存设置，同时返回工作表界面。

5.再次打开相同的文件，Excel 2016不会再产生“受保护的视图”提示。

知识扩展

1.微软设计“受保护的视图”的原意是帮助用户提升安全性，因为Excel文件支持宏代码，而宏代码可以用于设计病毒，给计算机带来危害。在受保护的视图中只允许用户查看工作表中的数据，禁止执行代码也禁止保存文件。不过如果用户确定要打开的文件是安全的，禁用受保护的视图能提升工作效率、减少操作步骤。

2.直接打开网络上的工作簿或邮件中的附件都可能产生“受保护的视图”提示。

3.对于“*.xlsx”格式的工作簿，可以确定它不包含VBA代码，绝对不会中宏病毒，对于“*.xls”和“*.xlsm”格式的工作簿，则可能含有VBA代码。但是并非VBA代码一定是病毒，会带来破坏性。

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

Excel 2016如何显示所有批注及标识符

当工作表中存在批注时，默认状态只在单元格的右上角显示批注的红色倒三角符号，效果如图1.33所示。

图1.33 默认状态下的批注

在默认状态下要查看批注的内容必须将鼠标指针指向单元格，然后批注才能完整地显示出来。是否可以让工作表中的所有批注都同时显示红色倒三角符号和批注内容呢？

解题步骤

Excel 2016提供了两种方法显示批注内容：其一是一次显示单个单元格的批注；其二是一次显示所有批注的内容。后者的操作步骤如下。

1.激活需要显示批注的工作表。

2.单击功能区的“审阅”→“显示所有批注”，工作表中的所有批注都会同时显示出来，效果如图1.34所示。

图1.34 显示所有批注

3.再次单击菜单“显示所有批注”，那么上一步所显示出来的批注将自动隐藏。

知识扩展

1.显示所有批注有利于阅读，使批注内容一目了然。不过当批注右边一列有数据时会覆盖右边的数据，

2.显示所有批注的内容后，仅仅方便了阅读，打印时会自动略过这些批注。当需要打印所有批注时，需要进行相应的设置，在后面的章节会有相应的描述。

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。