分类：Excel 2016实用技巧大全

图4.253中B、D、F等3列都存放了若干图片，其中B列的图片尺寸不正确，导致图片溢出单元格。是否有办法只调整B列的图片大小，其他单元格的图片保持当前大小不变？

解题步骤

Excel提供了一个图片选择工具，可以选择一个区域内的所有图片，忽略其他图片。选中目标图片后从“格式”选项卡中指定高度或宽度即可，具体步骤如下。

1.单击功能区的“开始”→“查找和选择”→“选择对象”，此时鼠标指针由，表示当前处于选择图形对象的状态。

2.在需要选择的第一个图片左上方按下鼠标左键，然后向右下角拖动，直到覆盖最后一个需要选择的图片时松开鼠标。图4.254是鼠标指针处于选择状态下的效果，而图4.255则是松开鼠标后的效果。

3.打开功能区的“格式”选项卡，然后单击右上方的高度调整按钮，其中下箭头表示减小，上箭头表示增加，操作界面如图4.256所示。

每单击一次下箭头可以下调0.1厘米，在调整高度的同时需要目测图片的高度，调到适应单元格的高度时停止。最终效果如图4.257所示。

知识扩展

1.批量选择图片有两个方法：其一是用本案例中演示的使用鼠标选择图形对象；其二是使用定位工具定位对象。前者能自由选择，可以选择工作表中的部分图形对象也可以选择所有图形对象，而后者总是选中工作表中的所有图形对象。

2.默认状态下，工作表中插入的图形对象都是锁定纵横比的，因此改变图形对象的高度可以同时改变它的宽度。

3.修改图形对象的高度或宽度并不能改变图形对象占用的空间，因此调整工作表中的图片高度或者宽度后，工作簿的体积并不会变化。

数据验证工具的验证条件设置为“序列”时，可以引用一列或一行的值作为数据来源，从而在输入数据时可以产生下拉菜单，然后通过选择子菜单的方式输入数据，既能提升输入效率又能确保输入的准确度。但是当数据来源包含多行多列时则会出错。例如，图4.247是数据源，用它作为数据验证的来源时将产生图4.248所示的错误提示，是否有办法引用多行多列的区域，同时又避免错误提示呢。

解题步骤

直接引用多行多列的区域作为数据验证的来源时必定会出错，无法保存设置，而借用名称作为中转站则可以突破这个限制，从而实现需求，具体操作步骤如下。

1.选择“数据”源工作表的A1：A10，然后按组合键＜Ctrl+F3＞，打开“名称管理器”。

2.单击“新建”按钮，然后将名称设置为“姓名”，将引用位置设置为“=数据源！$A$2：$A$10”，操作界面如图4.249所示。

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

4.进入“学费表”，选择 A2：A28 区域，然后单击功能区的“数据”→“数据验证”，弹出“数据验证”对话框。

5.按图4.250所示的方式设置验证条件。

此时“学费表”的A2：A28区域只能引用“数据源”的A2：A10，要引用A2：C10的值还需要执行后面的步骤。

6.返回“数据源”工作表，按组合键＜Ctrl+F3＞，弹出“名称管理器”对话框。

7.将名称“姓名”的引用位置修改为“=数据源！$A$2：$C$10”，然后单击左方的钩，表示保存设置，操作界面如图4.251所示。

8.进入“学费表”，选择A1单元格，然后单击单元格右方的倒三角按钮，在弹出的下拉菜单中可以看到列表项目包含了“数据源”工作表的A2：C10区域的所有值，效果如图4.252所示。

知识扩展

1.从“数据验证”对话框中直接引用区域作为序列来源时仅允许引用单行或单列的区域，即使定义名称后再引用该名称作为来源，同样只允许使用单行或单列的区域，因此本例的操作步骤是先定义名称，然后将名称添加到数据验证的来源中，最后返回名称管理器中修改名称的引用范围。

2.从单元格的下拉菜单中选择项目替代手工输入字符，其优点有两个：一是提升输入效率（仅针对要输入的字符串较长时，如地名）；二是防错，避免输入同音字或指定范围以外的字符。

图4.243是接单表，它包含了多个客户的下单集合，订单数据按日期排列。现需要单独提取天宏电子厂的订单数据，存放到一个新工作表中，应该如何才能一次性提取出来呢？

解题步骤

天宏电子厂的订单数据凌乱地排列在工作表中，要一次性复制它们到新工作表中必须通过筛选工具将天宏电子厂以外的数据隐藏起来，然后才能实现需求。具体操作步骤如下。

1.选择A1单元格。

2.单击功能区的“数据”→“筛选”，从而让工作表进入筛选状态。

3.单击“客户名称”右方的倒三角按钮，在对话框中单击“全选”，然后再单击“天宏电子厂”，最后单击“确定”按钮执行筛选。图4.244是筛选条件设置界面，图4.245则是筛选结果。

默认状态下所有项目都是选择了的，此时单击“全选”按钮相当于执行“全部不选”的功能。其后再逐个单击需要筛选的对象，从而确保筛选目标以外的项目都处于未选择状态。

4.选择A1单元格，按组合键＜Ctrl+A＞全选数据区域，再按组合键＜Ctrl+C＞复制数据。

5.新建一个工作表，然后按下组合键＜Ctrl+V＞粘贴数据，最终效果如图4.246所示。

知识扩展

1.筛选工具用于隐藏目标以外的数据。当目标数据多于其他数据时，在图 4.244 所示的界面中应该采取取消选择非目标数据的方式设置筛选方式，而在目标数据少于其他数据时，则应该单击“全选”按钮让所有项目都处于未勾选的状态，然后再逐一选择目标项目。

2.当工作表处于筛选状态下时，删除、复制、选择或设置格式都只对筛选目标生效，自动忽略隐藏的单元格，因此本例在筛选状态下全选并复制时会略过天宏电子厂以外的数据。

有时会遇到某个工作表的部分单元格带有前置撇号的情况，其表现形式如图4.240所示。

当工作表中数千个单元格都有这种撇号时，要逐个删除显然是不现实的，如何才能批量地清除这些前置撇号呢？

解题步骤

这种前置的撇号是一种格式，而非字符，手工删除撇号是不可能成功的，你会发现将光标定位到撇号右方并按下BackSpace键清除撇号后，只要单击Enter键，再返回单元格查看，该撇号又会自动产生。要清除这种前置的撇号应该通过格式设置来实现，具体步骤如下。

1.在远离数据区域的空白区域选择空白单元格，然后复制该单元格。

2.由于图4.240中A2：A10区域和D2：10区域都带有前置的撇号，因此同时选择A2：A10和D2：10区域，然后单击右键，从右键菜单中选择“选择性粘贴”。

3.在“选择性粘贴”对话框中选择“格式”，然后单击“确定”按钮执行粘贴。图4.241是设置粘贴方式的界面，而图4.242是粘贴结果。

此时可以发现A2：A10区域和D2：10区域都已经没有前置的撇号了，不过单元格的边框也没有了，因此需要重新设置一下边框。

4.选择A2：A10区域和D2：10区域，单击“开始”选项卡中的田字格边框，从而为这两个区域添加边框。

知识扩展

1.单元格在默认状态下的格式是“常规”，而带有前置撇号的单元格格式为“文本”，将“常规”格式的单元格粘贴格式到“文本”单元格，“文本”单元格会变成“常规”格式，但是此时单元格中的前置0仍然不会消失，只有重新输入数值或双击单元格并按下Enter键，单元格中的前置0才会消失。

2.撇号分为半角和全角的，如果是全角撇号，那么直接使用替换工具将它替换成空白符即可。

有时想要修改某个单元格，又担心影响其他单元格的值。因此有必要在修改前了解哪些单元格的公式引用了本单元格的值，那么如何才能快速找出引用了本单元格的值的所有单元格呢？

解题步骤

B1单元格的公式引用了A1的值，那么A1就是B1单元格的从属单元格。因此要知道哪些单元格引用A1的值，只要单击“追踪从属单元格”即可。

通过以下步骤可以了解“追踪从属单元格”的操作方法和该工具的工作模式。

1.在B5单元格中输入数值10。

2.在E2单元格中输入公式“=B5+123”。

3.在D8单元格中输入公式“=2*B5”。

4.在E5单元格中输入公式“=TODAY（）”。

5.选中单元格B5，单击功能区的“公式”→“追踪从属单元格”，执行结果如图4.239所示。

知识扩展

1.在本例中，B5单元格是E5和E2单元格的从属单元格，而E5和E2单元格则是B5单元格的引用单元格。也可以通过箭头的方向来判断，图4.239中上方的箭头从B5指向E2，因此表示E2引用B5的值。

2.追踪从属单元格或追踪引用单元格所产生的箭头会在保存工作簿后自动消失。

如图4.230所示，在文件夹中将鼠标指针指向工作簿时，在指针下方会弹出一个提示信息，其中一项是作者姓名。由于某些原因，不想让文件的属性中产生自己的真实姓名，有没有办法批量修改多个文件的作者姓名呢？

解题步骤

通过右键菜单可以打开文件的“属性”对话框，然后在其中可以修改或删除作者信息，支持批量删除，具体操作步骤如下。

1.在资源管理器中选择需要修改作者姓名的所有工作簿。

2.对选中的任意文件单击右键，并从右键菜单中选择“属性”菜单。

3.打开“详细信息“选项卡，然后单击下方的”删除属性和个人信息”，操作界面如图4.231所示。

4.在“删除属性”对话框中单击“从此文件中删除以下属性”，然后选择列表中的“作者”复选框并单击“确定”按钮执行删除，操作界面如图4.232所示。

5.返回“属性”对话框后，在“作者”文本框中输入任意昵称，例如“表格居士”，单击“确定”按钮后，将鼠标指向文件可以预览新的作者信息。图4.233是添加作者信息的界面，图4.234是预览界面。

知识扩展

1.作者信息来自于“Excel选项”对话框中的“用户名”，如果修改此“用户名”的值可以让新的工作簿产生新的作者信息。不过对于以前创建的文件，则需要在资源管理器中修改文件属性，修改的办法是先删除再添加，因为一个文件允许有多个作者，所以直接添加并不会覆盖以前的作者姓名。

2.在“属性”对话框中的“作者”下方还有一个“最后一次保存者”，它也会泄露作者信息，在删除“作者”属性值时应该把“最后一次保存者”也一并删除。

一个区域有可能包含几百个数值也可能包含几万个数值，如何才能快捷地找出区域中的最小值呢？

解题步骤

快速地从一个很大的区域中找到最大值或最小值有两种方法，操作步骤如下。

1.右键单击状态栏，从而打开“自定义状态栏”菜单。

2.分别单击“最小值”和“最大值”，界面如图4.227所示。

3.选择需要计算最大值和最小值的区域。

4.在状态栏查看最小值与最大值，结果分别是50和99，界面如图4.228所示。

状态栏总是显示当前区域的最大值和最小值，选择新的区域后就无法看到前一个区域的最大值和最小值。

下面的步骤可以看到每一个区域的最大值和最小值。

1.假设要求计算图4.229中B3：F12和I3：M12区域的最大值和最小值，那么首先在B14区域输入公式“=MAX（B3：F12）”，在D14区域输入公式“=MIN（B3：F12）”，两个公式可以分别取得B3：F12区域的最大值和最小值。

2.复制A14：D14到H14单元格，在I14和K14单元格中会显示H3：M12区域的最大值和最小值。

知识扩展

1.状态栏可以同时显示页码、平均值、计数、最大值、最小值与求和等信息，但是最大值和最小值两个项目在默认状态下是未勾选的，需要用户设置好后才能正常工作。

2.MAX函数用于计算指定区域中的最大值，支持最多255个区域；MIN函数用于计算指定区域中的最小值，支持最多255个区域。

3.通过状态栏查看区域的最大值或者最小值较使用公式更快捷，但是公式的优点是可以将结果永远保留在单元格中，随时更新计算结果，允许同时计算多个区域。

工作中每天都需要用到图4.224所示的报表，报表的行标题、列标题、签名栏都是固定的，只需要每天填入机台编号、生产产品、产量、不良品、良品率、质检员等6项数据即可。

有没有办法将以下样本转换成报表模板，在每天新建工作簿时自动生成包含图中数据的新工作簿呢？

解题步骤

Excel可以将普通工作簿文件转换成模板文件，在新建工作簿时调用模板即可生成与预设样本完全一致的新工作簿，具体操作步骤如下。

1.打开图4.224所示的工作簿。

2.打开“另存为”对话框，将保存类型修改为“Excel模板”，将文件名修改为“生产表.xltx”然后单击“确定”按钮返回工作表界面，图4.225是操作界面。

图4.225 “另存为”模板文件

3.关闭Excel，然后重新打开Excel。

4.单击菜单“文件”→“新建”，在右方会显示默认的“特色”模板，将它切换到“个人”，然后单击“生产表”，Excel会自动生成一个新工作簿，工作簿包含一个名为“1日”的工作表，工作表的内容和图4.224完全一致。

图4.226是调用模板的界面，所有自定义的模板都会出现在这个地方。但是不能在这个地方删除模板，要删除模板只能进入文件夹中，删除对应的xltx格式的文件，

知识扩展

1.创建一个模板文件主要有两个步骤：其一是设置好格式和基本数据，所有固定的值和格式都应该预先设置好；其二是保存为xltx格式的模板文件，保存时不能修改路径，采用默认的路径即可。

2.调用工作簿模板必须借助文件菜单下的“新建”按钮，直接按组合键＜Ctrl+N＞或单击快速访问工具栏中的“新建”按钮只能调用默认的空白工作簿。

3.模板包含工作簿模板和工作表模板，这里展示的是工作簿模板。

当工作簿中有总表和分表，且分表的数量较多时，在分表中添加一个单击返回总表的链接可以提升工作效率。如何才能快速地在多个分表中创建返回总表的链接呢？

解题步骤

以图4.221为例，在“总表”以外的工作表中创建超级链接，使单击单元格时可以返回总表，具体步骤如下。

1.进入“一月”工作表。

2.右键单击A1单元格，从右键菜单中选择“超链接”，弹出“插入超链接”对话框。

3.单击“本文档中的位置”，然后再单击右方的总表，表示链接对象是本文档的总表，而不是链接到网址或邮箱地址，操作界面如图4.222所示。

4.单击“确定”按钮返回工作表，在A1单元格将会产生“总表！A1”字样，鼠标指针指向A1时会产生提示，效果如图4.223所示。单击A1单元格，Excel会打开“总表”，并且激活A1单元格。

5.复制A1单元格，然后按住Shift键单击最后一个工作表，表示同时选中“总表”以外的所有工作表。

6.选择A1单元格，然后按组合键＜Ctrl+V＞粘贴数据，此时“总表”以外的所有工作表的A1单元格都支持单击返回“总表”并激活A1单元格。

知识扩展

1.超链接可以链接到文件、网页、邮箱地址和本工作簿的任意单元格，如果链接到文件和网址，则单击链接时打开对应的文件或网站；如果链接到邮箱地址，则单击链接后打开发邮件的对话框；如果链接到单元格地址，则单击链接时激活对应的单元格。

2.在单元格中创建超链接后，单元格中会显示链接目标的地址。

可以随意修改单元格的值而不影响链接目标，不过需要选择单元格后按住鼠标左键停留3秒钟再松开，否则会激活链接功能。