分类：效率篇

每次都打开资源管理器，然后查找最常用的文件或文件夹，此操作比较费时，有什么办法对最常用的文件和文件夹指定快捷键呢？

解题步骤

Excel自带的功能无法为文件或文件夹指定快捷键，但是可以对文件或文件夹创建快捷方式，然后对快捷方式指定快捷键，具体操作步骤如下。

1.在桌面空白处单击右键，从右键菜单中选择“新建”→“快捷方式”。

2.在“创建快捷方式”对话框中单击“浏览”，找到“D：\生产表”后双击打开，从而指定要创建快捷方式的文件夹路径，界面如图4.278所示。

3.单击“下一步”按钮，弹出如图4.279所示的对话框，不做任何修改，然后单击“完成”按钮即可，此时桌面会产生图4.280所示的快捷方式。

4.单击右键桌面的“生产表”快捷方式，从右键菜单中选择“属性”，弹出“属性”对话框。

5.单击“快捷键”文本框，然后按下C键，Windows系统会自动显示组合键＜Ctrl+Alt+C＞，效果如图4.281所示。

6.单击“确定”按钮保存设置，然后在桌面按下组合键＜Ctrl+Alt+C＞，此时 Windows会自动打开对应的文件夹“D：\生产表”。

为文件创建快捷键和为文件夹创建快捷键的方法完全相同，因此不再赘述。

知识扩展

1.为了方便调用，快捷方式一定要放在桌面上，然后在桌面上按下组合键就能调用目标。如果将快捷方式放在D盘，那么必须进入D盘后才能使用组合键调用。

2.为了避免与其他软件的快捷键重复，Windows禁止使用一个功能键加一个字母的组合方式，不管按下哪个功能键，它都会同时指定Ctrl和Shift两个功能键与其组合。

若采用F1、F2这种功能键，则允许只使用两键组合，如＜Ctrl+F1＞。

Excel可以创建日历吗？日历必须可以随意调整年份，即需要2014年的日历就设置为2014、需要2015年的日期则设置为2015即可。

解题步骤

要自己设计一个可随意变化年份的日历相当困难，涉及大量的数组公式。不过Excel提供了一份日历模板，调用现成模板只需要几秒钟，具体步骤如下。

1.单击功能区的“文件”→“新建”，然后单击右方的“日历”按钮，从而进入日历模板页面，菜单界面如图4.274所示。

2.按图4.275所示的方式单击右方“分类”项目表中的“任何年份”，因为其他日历是不可以随意调整的。

3.在左边的日历模板预览界面单击第5个模板“任何年度的日历”，此时会弹出单一模板的预览界面，效果如图4.276所示。

4.从图4.276中单击“创建”按钮，从而生成图4.277所示的日历。

日历的默认年份是2013年，单击2013字样右下角的箭头可以改变年份，在改变年份的同时整页内容都会相应地改变。

知识扩展

1.Excel 2016提供了数百个模板，其中关于日历的模板就有72个，单击模板名称就可以调用模板从而生成对应的工作簿。

2.只有包含在“任何年份”下的日历模板才能生成可调整内容的模板，其他模板都是固定内容的。

部分工作表的数据并不会一次性输入完成，而是每天追加一部分。假设工作表中有公式（见图4.267），输入新数据后还需要手工填充公式，这无疑拖慢了制表节奏。

有没有方法让Excel根据用户新增的数据自动填充公式呢？

解题步骤

让公式自动向下填充有两个方法：其一是设置Excel选项，并改造公式，从而让Excel将第5行以后的公式自动向下填充；其二是将区域转换成表，从而不需要修改公式就可以让公式自动填充，具体步骤如下。

1.将公式“=RANK.EQ（C2，C$2：C$6）”修改为“=RANK.EQ（C2，C：C）”。

2.按组合键＜Alt+T+O＞，弹出“Excel选项”对话框。

3.打开“高级”选项卡，选择“扩展数据区域格式及公式”，操作界面如图4.268所示。

选择此选项后并不会立即生效，必须满足它的条件才会自动扩展公式，其条件是当前数据区域已经存在不少于5行数据，数据区域中包含不少于5条公式，同时当前的5条公式必须一致，然后在后面的第一个空行新增数据时才会自动填充公式。

4.在 A7：C7 中分别输入张震、6＃、1000，单击 Enter 键后 D7 单元格会自动产生公式“=RANK.EQ（C7，C：C）”，同时前面5个单元格的公式也会相应地更新，效果如图4.269所示。

以上方法能实现需求，但是有条件限制，需要修改公式，而且必须已经存在不少于5行资料，然后才能自动填充公式。下面的方法没有任何条件限制，操作方法也简单，步骤如下。

1.重新做一个表，输入图4.270中的数据。其中D2单元格有公式，其他单元格没有公式。

2.选择A1：D2区域，按组合键＜Ctrl+T＞，弹出“创建表”对话框，然后单击“确定”按钮。

3.在A3单元格输入“黄至中”，当单击Enter键后光标移到B3单元格，此时D2单元格的公式已经自动填充到 D3，只不过由于 C3 还没有输入数值，因此公式的计算结果是错误值，效果如图4.271所示。

4.在B2和C2单元格中分别输入2＃和890，单击Enter键后D2单元格的公式会重新计算产量890在C2：C3区域的排名，效果如图4.272所示。

5.继续在第4行输入数据：陈明、3＃、798。单击Enter键，D3单元格的公式会自动向下填充到D4单元格，计算结果为1，同时还会更新D2：D3区域的公式结果，效果如图4.273所示。

知识扩展

1.让公式自动向下填充可以减少手工填充公式的时间，采用本例中第二种方法既设置简单又无条件限制。不过区域转换成表后会自动应用表的样式，在表中奇偶行会显示为不同颜色，如果不喜欢这种颜色，可以将其样式由名称“中等深浅2”修改为“无”。

2.本例中第一步将公式“=RANK.EQ（C2，C$2：C$6）”修改为“=RANK.EQ（C2，C2：C）”，其目的是扩大引用范围，避免公式向下填充后计算出错。公式“=RANK.EQ（C2，C$2：C$6）”仅适用于第2行到第6行，填充到第7或以后的单元格就会出错，而公式“=RANK.EQ（C2，C2：C）”则适用于任何一行，因此要让公式向下填充，有必要修改公式。

但是，采用第二种方法填充公式时是不必修改公式的，它会在填充公式时相应地修改引用范围，其计算结果总是正确的。因此不管任何时候，采用第二种方法填充公式既简单又准确。

图4.264中第一周的值班表已经安排好，现在要求将第二周的姓名随机打乱，不再按第一周的顺序安排值日。尽管数据不多，可以手工打乱姓名，但现在期望获得一个通用的方法，不管有数据多少都可以用相同的步骤、相同的时间随机打乱这些姓名。

解题步骤

随机打乱一列数据主要依靠两个小功能的组合，其一是在该列数据右方产生一列随机数，其二是对这两列数组排序，以随机数作为排序依据，具体操作步骤如下。

1.选择E2：E7，然后输入公式“=RAND（）”，并按组合键＜Ctrl+Enter＞结束，此时E2：E7将会产生图4.265所示的随机数。

随机数是不确定的，因此读者在使用以上公式时产生的值并不会和图4.265中的值一致，而且修改其他单元格时随机数还会马上变化，按下F9键时随机数也会变化。

2.选择E2：D7区域，单击功能区的“数据”→“升序”（图标为），排序结果如图4.266所示。

3.再次单击“升序”按钮，D列的姓名会重新打乱，每次排序的结果都不同，因为E列的随机数在变化，那么排序的结果也会相应地变化。

4.删除E列的辅助数据。

知识扩展

1.函数RAND的功能是生成随机数，其值在大于0、小于1这个范围之内。RAND函数是易失性函数，因此修改工作表中任意单元格时，函数的值都会变化一次。

2.一个区域排序时是以活动单元格所有列为排序条件的，本例中要对D2：E7区域排序，以E列的值为排序条件，因此应该先选中E2，然后向左下角拖动鼠标，直到选中D7时松开，称之为选中E2：D7，此时E2处于活动状态。假设按平常的操作，选择D2：E7，那么D2单元格处于活动状态，排序后无法达到随机效果，而是D列的姓名按拼音顺序升序排列。

3.由于E列的值是随机数，因此升序排列还是降序排列并不影响最终的排序结果。

图4.258中A列的职员信息包含了职员的姓名、所在车间及工号，现要求提取其中的姓名，有什么方法快速提取所有姓名？

解题步骤

由于姓名在职员信息中有比较强的规律性，因此可以借助Excel自带的快速填充完成需求，具体步骤如下。

1.在E2单元格输入“张邦民”。

2.将鼠标指向 E2 的右下角，指针变成黑色十字形时，右键按下指针向下填充，当填充到F7单元格时松开右键。

3.在弹出的右键菜单中选择“快速填充”，Excel会根据“张邦民”在A2单元格中的位置和特征分析A3：A7区域中对应的姓名，然后将姓名填充到E3：F7区域中。图4.259和图4.260分别是调用快速填充的界面及填充结果。

知识扩展

1.快速填充工具是Excel 2013开始添加进来的工具，以往的版本中无法使用。

2.本例中A列的职员信息有较明显的规律，如姓名前面都有“车间”二字，姓名后面全是字母，因此Excel可以按照这个规律去提取所有姓名，假设将数据按图4.261所示的方式修改，姓名右方全是汉字，Excel 就无法再准确地提取出姓名来。假设仍然采用相同的方式执行填充，那么填充结果将会出错，效果如图4.262所示。

3.快速填充工具比较智能，年、月、日这样的字符也可以正确地提取出来。例如，图4.263中，A1包含了年、月、日，而且月与日的长度并不相同，但是在A1单元格输入“12月”然后向下填充时，Excel总能根据规则准确地提取出对应的字符。

图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.追踪从属单元格或追踪引用单元格所产生的箭头会在保存工作簿后自动消失。