书名：Excel经典教程——公式与函数
ISBN：978-7-115-57315-5
本书由人民邮电出版社发行数字版。版权所有，侵权必究。
您购买的人民邮电出版社电子书仅供您个人使用，未经授权，不得以任何方式复制和传播本书内容。
我们愿意相信读者具有这样的良知和觉悟，与我们共同保护知识产权。
如果购买者有侵权行为，我们可能对该用户实施包括但不限于关闭该帐号等维权措施，并可能追究法律责任。
著 [美] 保罗 • 麦克费德里斯（Paul McFedries）
译 刘静华
责任编辑 贾鸿飞
人民邮电出版社出版发行 北京市丰台区成寿寺路11号
邮编 100164 电子邮件 315@ptpress.com.cn
网址 http://www.ptpress.com.cn
读者服务热线：(010)81055410
反盗版热线：(010)81055315
Authorized translation from the English language edition, entitled FORMULAS AND FUNCTIONS: MICROSOFT EXCEL 2010, 1st Edition, 9780789743060 by MCFEDRIES, PAUL, published by Pearson Education, Inc, publishing as Que Publishing, Copyright©2010, 800 East 96th Street, Indianapolis,IN 46240 USA.
All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording or by any information storage retrieval system, without permission from Pearson Education, Inc.
Chinese Simplified language edition published by POSTS AND TELECOMMUNICATIONS PRESS, Copyright © 2021.
本书中文简体字版由Pearson Education（培生教育出版集团）授权人民邮电出版社在中华人民共和国境内（不包括香港、澳门特别行政区及台湾地区）独家出版发行。未经出版者书面许可，不得以任何方式抄袭、复制或节录本书中的任何部分。
本书封底贴有Pearson Education（培生教育出版集团）激光防伪标签，无标签者不得销售。
本书全面、系统、细致地讲解了使用Excel公式与函数各方面的知识。本书共20章，分别介绍了如何将区域的作用发挥到极致、如何使用区域名称、如何建立基本公式和高级公式、如何解决公式中出现的问题，并详述了函数的概念，进而对文本函数、逻辑函数、信息函数、查找函数、日期和时间函数、数学函数、统计函数的用法逐一进行了讲解，然后讲解了如何使用表、数据透视表、分析工具、回归分析、规划求解等工具或模型，结合函数进行数据分析，最后介绍了如何使用函数建立贷款公式、投资公式和贴现公式。
本书内容非常丰富，几乎涵盖了Excel公式与函数涉及的全部知识点，讲解由浅入深，又不乏生动，提及的案例贴近实际工作，非常适合想全面、透彻掌握公式与函数知识的职场人士阅读。
保罗•麦克费德里斯（Paul McFedries），任Logophilia Limited公司总裁，自1975年起一直学习各种计算机知识，钻研各类操作系统与应用软件的使用，涵盖Windows、macOS、Microsoft Office等；1991年开始编写计算机图书，目前已出版90多本，全球总销量超过400万册。
作为Excel的核心功能之一，公式与函数的使用是非常值得花时间和精力来学习并理解的。在这个学习的过程中，你将发现以前不能理解的、想象不到的高效处理日常任务的思路和方法。
与很多直接讲解公式和函数的图书不太一样的是，本书开篇介绍的是区域—作为函数的参数，区域看似很简单，却关联着不少我们应该掌握的知识和技巧。在正式开始介绍Excel公式之前，还讲解了区域名称的应用，这将给高效使用公式和函数带来不少便利。
当然，如何应对公式中出现的错误，文本函数、逻辑函数、信息函数、查找函数、日期和时间函数、数学函数、统计函数等常见函数的用法，本书会详尽而深入地进行介绍，并结合案例和经验，给出在应用场景下的使用技巧。
随后，本书将使用大量的篇幅讲解在区域转换而来的表中如何分析数据；如何通过公式与函数，在数据透视表、分析工具、规划求解、回归分析等工具与模型中对数据进行分析和预测，相信这在以“公式与函数”作为主题的图书中是不多见的。
最后，本书用3章的篇幅，通过基础知识介绍与案例分析，详细讲解了财务函数在建立贷款公式、投资公式和贴现公式中的实际应用。
公式与函数的“介入”，对于提升工作效率的作用无需赘述。下面简单列出一些阅读完本书的收获，包含但不局限于这些内容。
■ 创建更强大的公式；
■ 使用条件格式立即揭示异常、问题或机会；
■ 解决公式、范围和函数的问题；
■ 熟练使用各类函数对单元格及单元格区域中的数据进行处理；
■ 使用标准表格和数据透视表分析数据；
■ 使用复杂条件过滤列表中的数据；
■ 了解数据之间的相关性；
■ 执行复杂的假设分析；
■ 使用回归来跟踪趋势并进行预测；
■ 熟练使用贷款、投资和贴现公式。
相信不少读者有过这样的经历，面对工作需要用公式与函数解决的问题，上互联网搜索解决方案，最后发现方案不止一种—有些方案公式嵌套了一层又一层，有些方案则相对简单，甚至比其他的少了三四层嵌套。这种解决方案的多样性形成的原因是方案提供者的思路不同，而决定思路的固然有思维的灵活程度，更重要的却是信息储备。关于公式与函数的信息储备—很多人不知道有可以实现某些功能的函数的存在，谈何用来简化公式呢？这就引出一个问题：Excel有11种共计400多个函数，怎么才能把名称、参数及用法都记住呢？
事实上，要做到这一点非常不容易，而且也完全没有必要。但不得不说的是，尽管不需要记住每一个函数的名称、参数及用法，但想成为一名熟练使用Excel的职场人士，至少需要知道Excel有哪些函数，可以实现什么样的功能。而且，使用频率高的函数，如IF( )、LEN( )、MID( )、AND( )、INDEX( )、VLOOKUP( )、IFERROR( )、FIND( )等，对其用法必须烂熟于心。也就是说，对这些函数，有些必须要记住名称及用法，有些则只需知道大概是用来干什么的—否则，连存在可以用来实现某一项功能的函数都不知道，怎么可能给出一个最佳的解决方案。
另外，对函数的参数引用方式、数组知识、函数出错的解决方式等，也需要熟练掌握，否则面对实际工作中的问题，同样不一定能给出最佳的函数方案。
想要透彻理解并熟练使用函数，最好遵循由浅入深、从简单到复杂、基础知识到实际使用的路径学习。在将来的某一天，你会发现打下的牢固基础对解决实际问题，特别是复杂的问题有多么大的作用。
我们总是说，大多数用户只使用了Excel很少的一部分功能，实际上多数时候也不用使用大部分功能。而公式与函数作为Excel的核心功能，如果可以熟练掌握其用法，就能更充分地发挥Excel的威力。希望你们不要被这些看起来深奥的词汇所击败，跨出第一步并坚持下去，收获必将越来越大。
使用Excel时，除了数据输入这些琐事，我们的时间大部分都花在区域操作上了。不管是复制、移动、格式化、命名还是填充，都会涉及区域相关操作。面对区域比面对很多单独的单元格简单多了。举例来说，假设想求出从B1到B30这一列数字的平均值，我们可以在AVERAGE函数中把30个参数一一输入，不过那样的话你大概也就不想再待在计算机屏幕前了；而毫无疑问，输入“=AVERAGE(B1:B30)”将会更快，也更准确。
换句话说，区域能帮我们节省时间，同时保护手指。区域还是发掘Excel潜在功能的有力工具。对区域了解得越多，我们能用Excel做到的事就越多，尤其是在建立公式时。本章将带你打破区域常规，并告诉你一些运用Excel区域的高级技巧。
当我们使用Excel时，有3种情况需要选择一个单元格区域。
■ 出现对话框要求时。
■ 输入函数参数时。
■ 选择所需区域中输入的命令时。
在出现对话框和输入函数参数的情况下，最简单的选择区域的方式就是直接手动输入区域坐标。你可以输入左上角单元格（我们称之为定位格）的地址，接着输入一个冒号，然后输入右下角单元格的地址。使用这种方法时，你必须能够看到所选择的区域，或者是预先知道区域坐标。因为通常情况并非如此，所以大部分人会使用鼠标或者键盘来选择区域。
本章介绍的不是那些基本而普通的区域选择方法，而是把区域选择变得简单快捷的高级技巧。
使用鼠标选择区域时可以用到以下几个小窍门。
■ 当选择的是长方形且相邻的区域时，如果右下角单元格选择错误，区域将会过大或过小。要解决这个问题，你可以按住【Shift】键，同时单击正确的右下角单元格，区域会被自动调整为合适的大小。
■ 当我们选择了一个很大的区域时，不向下滚动页面就无法看到完整的当前活动单元格。此时可以使用滚动条来查看，或者按【Ctrl】+【BackSpace】组合键。
■ 也可以使用鼠标来选取一个长方形的区域，这里我们会用到Excel的扩展模式：单击所要选择区域的左上角单元格，按【F8】键打开扩展模式（此时可以在状态栏中看到【扩展式选定】选项），然后单击所要选择区域的右下角单元格，Excel会选择好整个区域。按【F8】键关闭扩展模式。
■ 如果要选择的区域或单元格是不相连的，那就需要把它们整合到一个不相邻的区域内。窍门就是选择的时候按住【Ctrl】键，在选择了第一个区域或单元格以后，继续按住【Ctrl】键不放，然后选择其余的单元格或者区域即可。
警告： 当选择不相邻区域时，记得在选定第一个单元格或区域后要一直按着【Ctrl】键。否则，在你将当前选定的单元格或者区域定义为函数的参数时，它会作为非相邻区域的一部分被循环引用。
→想了解什么是循环引用，请看“5.2.3 解决循环引用”。
Excel中有很多小窍门，它们使得用键盘选择区域变得简单而高效。
■ 如果你想选择的是一片包含数据的区域，首先选择区域左上角的单元格，然后按【Ctrl】+【Shift】+【End】组合键。
■ 如果所选择的区域很大，超出屏幕显示范围，我们可以使用【Scroll Lock】键（屏幕滚动锁定键）来滚动所选择的单元格区域。当【Scroll Lock】键在打开状态下时，按方向键（或【Page Up】【Page Down】键）来滚动单元格可以使所选择的区域在滚动时保持完整。
三维区域选择是指在多重工作表上进行区域选择。这是一个很有用的功能，因为这意味着我们可以在两个或更多的工作表中选择区域并输入数据、应用格式或下达命令，而这些操作将同时对多重工作表中的所有选择区域起作用。当我们面对的是多重工作表，而且其中的一些或所有标签都相同时，这个就非常有用了。举例来说，制作各部门费用计算表时，每个工作表中的部门不同，但项目一样，当我们需要在所有工作表的单元格A1中标注费用的时候，三维区域选择就派上用场了。
使用三维区域选择时，需要先将几个工作表归到同一个组中。我们可以使用以下几种方法。
■ 选择相邻的工作表时，单击第一个工作表的标签，按住【Shift】键，然后单击最后一个工作表的标签。
■ 选择不相邻的工作表时，按住【Ctrl】键，然后单击所有需要的工作表的标签。
■ 选择一个工作簿中的所有工作表时，在任意工作表标签上右击，然后选择【选定全部工作表】选项。
每个被选择的工作表标签都会呈高亮显示，同时工作簿的标题栏处会出现【工作组】字样。若要取消组合，单击任意一个不在组内的工作表的标签即可。或者，也可以右击组内的任意工作表，在弹出的快捷菜单中选择【取消组合工作表】选项。
我们可以使用工作组中任意一个工作表来创建三维区域并进行区域选择。Excel会同时选中工作组中的其他所有工作表中相同的单元格。
也可以手动输入公式来选择三维区域，格式通常为：
FirstSheet : LastSheet ! ULCorner : LRCorner其中，FirstSheet是三维区域中第一个工作表的名字，LastSheet是三维区域中最后一个工作表的名字，ULCorner和LRCorner是指所需选择区域的单元格。举例来说，想选择工作表1、工作表2、工作表3的A1到E10的区域，使用如下公式即可：Sheet1: Sheet3! A1:E10。
警告： 三维区域选择完成后，请一定记得取消工作组，以免因不小心而覆盖数据或者因疏忽而造成错误。
我们平时在工作表中使用的函数在三维区域中也都是可用的。这些函数包括AVERAGE( )、COUNT( )、COUNTA( )、MAX( )、MIN( )、PRODUCT( )、STDEV( )、STDEVP( )、SUM( )、VAR( )，以及VARP( )。（以上以及更多函数的用法你将在后续章节中学到。）
对于特别大的区域，可以使用【定位】命令跳转到所需要的单元格或区域。以下几步将告诉你如何实现这个操作。
1．选中左上角单元格。
2．选择【开始】【查找和选择】选项，在下拉列表中选择【转到】选项（也可以按【F5】键或【Ctrl】+【G】组合键）。此时会出现【定位】对话框，如图1.1所示。
3．在【引用位置】文本框中输入所选区域的右下角单元格的地址。
小贴士： 也可以在【引用位置】文本框中输入坐标来选择区域。
图1.1 使用【定位】对话框选择较大的区域
4．按住【Shift】键并单击【确定】按钮，此时区域选择就完成了。
小贴士： 另一个选择较大区域的方法是选择【视图】【显示比例】选项，在弹出的【显示比例】对话框中选择缩放比例，如50%或25%；也可以拖曳位于状态栏右边的滑块来调整缩放比例；还可以按住【Ctrl】键并滚动鼠标滚轮来缩放区域。之后就可以选择区域了。
在一个工作表中，我们通常会根据位置来选择单元格。Excel提供了一个有用的工具，让我们能够根据内容或特殊属性来选择单元格。选择【开始】【查找和选择】【定位条件】选项（或单击位于【定位】对话框左下角的【定位条件】按钮），打开【定位条件】对话框，如图1.2所示。
图1.2 使用【定位条件】对话框，根据单元格的内容、公式关联等选择区域
【定位条件】对话框中有很多选项，但其中只有4项可以根据内容来选择单元格。表1.1总结了这4项的具体内容。（其余的选项会在后面讲到。）
表1.1 根据类型选择单元格
| 选项 |
描述 |
|---|---|
| 【批注】 |
可以选择所有包含批注的单元格。也可以通过选择【开始】【查找和选择】【批注】选项来使用此选项 |
| 【常量】 |
可以选择所有包含常量的单元格。此常量指【公式】选项下边列出的4种类型中的前3种。也可以通过选择【开始】【查找和选择】【常量】选项来完成 |
| 【公式】 |
可以选择所有包含公式的单元格。也可通过选择【开始】【查找和选择】【公式】选项来完成。 |
| 【数字】：选择所有包含数字的单元格 |
|
| 【文本】：选择所有包含文本的单元格 |
|
| 【逻辑值】：选择所有包含逻辑值的单元格 |
|
| 【错误】：选择所有包含错误的单元格 |
|
| 【空值】 |
选择所有空白的单元格 |
如果需要选择与当前活动单元格相邻的单元格，【定位条件】对话框中有两个选项可供使用。选择【当前区域】选项，可以选择与当前活动单元格相邻的非空白单元格的矩形区域。
如果当前活动单元格是数组的一部分，选择【当前数组】选项可以选择所有位于数组中的单元格。
→如果想深入全面地了解Excel数组，请看“4.1 使用数组”。
我们可以通过对比行或列内的数据，利用差异来选择区域，步骤如下。
1．选好想要进行比较的行或列（确保当前活动单元格与所要比较的对比值都在选中的行或列中）。
2．打开【定位条件】对话框，选择以下选项中的一个。
■ 【行内容差异单元格】选项——此选项将当前活动单元格所在列的数据作为对比值。这与在Excel中选择相应行的单元格是不同的。
■ 【列内容差异单元格】选项——此选项将当前活动单元格所在行的数据作为对比值。这与在Excel中选择相应列的单元格是不同的。
3．单击【确定】按钮。
举例来说，图1.3显示的是一组数字区域的选择情况。B列的值是分配给公司各部门的预算值，C列和D列分别是东区和西区实际的支出费用。假设现在你想知道在结束的时候，各部门的支出是在预算之内还是超出了预算，那么，你需要把C、D两列与B列比较一下，找出不同的值。此时你想比较的是位于同一行的数据，所以需要选择【定位条件】对话框中的【行内容差异单元格】选项。图1.4显示的就是结果。
图1.3 使用【定位条件】对话框前，选中要比较的所有数据所在的整个区域
图1.4 选择【行内容差异单元格】选项后，C、D两列中与B列不同的值被标注了出来
如果一个单元格内包含公式，那么Excel会将此公式涉及的那些单元格定义为引用单元格。举例来说，如果单元格A4包含公式“=SUM(A1:A3)”， 那么单元格A1、A2、A3就是单元格A4的引用单元格。间接引用单元格指的是与引用单元格有关的单元格。举例来说，如果单元格A1包含公式“=B3*2”，那么单元格B3就是A4的间接引用单元格。
Excel会将一个包含公式的单元格定义为从属单元格，而这个公式会涉及此单元格的引用单元格。在上述例子中，单元格A4便是A1的从属单元格。和引用单元格一样，从属单元格也分为直接与间接两种形式。
注意： 可以这样理解从属单元格：单元格A4中的值取决于A1中输入的值。
你可以使用【定位条件】对话框，按以下的步骤选择引用单元格和从属单元格。
1．选择好所需区域。
2．打开【定位条件】对话框。
3．选择【引用单元格】选项或【从属单元格】选项。
4．选择【直属】选项，可以选中所有引用单元格和从属单元格；如果同时还需要选择两种单元格的间接形式，则可以选择【所有级别】选项。
5．单击【确定】按钮。
【定位条件】对话框中还有一些选项可以帮助我们完成区域选择等琐事，功能如表1.2所示。
表1.2 【定位条件】对话框中的其他选项的功能
| 选项 |
描述 |
|---|---|
| 【最后一个单元格】 |
选择工作表中包含数据或格式的最后一个单元格（即右下角单元格） |
| 【可见单元格】 |
仅选择未隐藏的单元格 |
| 【条件格式】 |
仅选择包含条件格式的单元格（也可以通过选择【开始】【查找和选择】【条件格式】选项来完成） |
| 【数据有效性】 |
选择包含数据有效性规则的单元格（可通过选择【开始】【查找和选择】【数据有效性】选项来完成）。如果选择【全部】选项，Excel将选择包含数据有效性规则的全部单元格。选择【相同】选项，Excel将选择与当前活动单元格有相同数据有效性规则的单元格 |
→学习条件格式，请看“1.8 在区域中应用条件格式”。
→学习数据有效性，请看“4.6 在单元格中应用数据有效性规则”。
表1.3列出了一些快捷键，可以用来进行定位操作。
表1.3 进行定位操作的快捷键
| 快捷键 |
选项 |
|---|---|
| 【Ctrl】+【*】 |
【当前区域】 |
| 【Ctrl】+【/】 |
【当前数组】 |
| 【Ctrl】+【\】 |
【行内容差异单元格】 |
| 【Ctrl】+【|】 |
【列内容差异单元格】 |
| 【Ctrl】+【[】 |
【直接引用单元格】 |
| 【Ctrl】+【]】 |
【直接从属单元格】 |
| 【Ctrl】+【{】 |
【所有级别引用单元格】 |
| 【Ctrl】+【}】 |
【所有级别从属单元格】 |
| 【Ctrl】+【End】 |
【最后一个单元格】 |
| 【Alt】+【;】 |
【可见单元格】 |
如果我们事先知道将要在哪片区域输入数据，那么先选择好这片区域将会事半功倍。可以使用表1.4所列的快捷键来选择单元格。
表1.4 选择区域的快捷键
| 快捷键 |
结果 |
|---|---|
| 【Enter】 |
向下移动一行 |
| 【Shift】+【Enter】 |
向上移动一行 |
| 【Tab】 |
向右移动一列 |
| 【Shift】+【Tab】 |
向左移动一列 |
| 【Ctrl】+【.】（句号） |
由区域中的一角移动到另一角 |
| 【Ctrl】+【Alt】+【右箭头】 |
移动到不相邻区域中的下一块 |
| 【Ctrl】+【Alt】+【左箭头】 |
移动到不相邻区域中的上一块 |
这个小窍门的好处是当前活动单元格总在所选区域内。举例来说，如果我们在所选区域最后一行的单元格内输入数据后按【Enter】键，那么当前活动单元格会切换回所选区域的首格。
如果我们需要用特殊的值或公式来填充一个区域，Excel提供了以下两种方法。
■ 选好所需填充的区域，输入值或公式，然后按【Ctrl】+【Enter】组合键。此时Excel会将公式栏中输入的所有数据都填充在所选区域中。
■ 输入原始数据，选择好所要填充的区域（包括原始数据格在内），然后选择【开始】【填充】选项，在下拉列表里选择适当的选项。举例来说，如果要填充原始数据格以下的区域，选择【向下】选项即可。如果选择的是多重工作表，则选择【开始】【填充】【成组工作表】选项来填充。
小贴士： 按【Ctrl】+【D】组合键实现的功能与选择【开始】【填充】【向下】选项实现的功能相同；
按【Ctrl】+【R】组合键实现的功能与选择【开始】【填充】【向右】选项实现的功能相同。
填充柄是所选区域或当前活动单元格右下角的一个黑色小方块。这个小工具有很多功能，包括创建一个序列文本或序列数值。接下来的几小节将告诉你如何使用填充柄。
工作表中经常会用到序列文本（如一月、二月、三月或星期天、星期一、星期二）和序列数值（如1、3、5或2009、2010、2011），我们可以使用填充柄来让Excel自动创建它们，而不必一一手动输入。这个很方便的功能就叫作【自动填充】，下面是使用步骤。
1．创建序列文本时，选好所需区域的第一个单元格，然后输入原始数据。如果是序列数值，则输入前两个数据，然后将其全选中。
2．将鼠标指针放到填充柄处，此时鼠标指针会变成加号（+）状。
3．按住鼠标左键并拖曳鼠标，直至所需区域已被全部选中。如果你不确定该什么时候停止拖曳，可以看着鼠标指针的旁边，那里会显示最后一个选择的单元格的数据。
4．释放鼠标左键。此时序列文本或序列数值就填充好了。
当我们使用完自动填充功能并释放鼠标左键后，会出现一个【自动填充选项】小标签，单击小标签的向下箭头可以看到下拉列表。创建不同类型的序列，下拉列表中的内容也会有所不同，不过，至少会有以下4个选项。
■ 【复制单元格】：以复制原始单元格中的数据的方式填充选中的单元格。
■ 【填充序列】：以序列的方式填充选中的单元格。
■ 【仅填充格式】：仅以原始单元格的格式来填充选中的单元格。
■ 【不带格式填充】：不按照原始单元格的格式填充，仅填充序列数据。
→详细的【自动填充】使用方法，请看“1.5 创建序列”
图1.5所示为使用填充柄创建的一些序列。阴影部分是输入的原始数据。特别要注意的是，在Excel中，任何数据都可以递增，如第1季度（E列）和客户1001（F列）。
图1.5 填充柄使用举例。阴影部分是输入的原始数据
使用填充柄的时候要注意以下几点。
■ 单击并拖曳填充柄向下或向右是增加值，向上或向左为递减值。
■ 填充柄可识别标准缩写，如Jan（一月）和Sun（星期日）。
■ 想要隔行填充，需要在拖曳前选中所输入的前两个数据。举例来说，输入第一、第三，则此序列将会以第一、第三、第五……的方式来排列。
■ 如果原始数据为3个或以上的数字，Excel可以创建最佳匹配或趋势线。
→更多趋势分析的知识，请看“第16章 使用回归分析追踪趋势并作出预测”。
正如之前看到的，Excel能够识别一些固定的数据，如一月到十二月、星期日到星期六、第1季度到第4季度等。当我们拖曳包含以上数据的单元格的填充柄时，Excel会用合适的数据来填充单元格。当然，我们不会局限于Excel所能识别的这几个有限的列表上，我们可以自由地创建自己的自动填充表，步骤如下。
1．选择【文件】【选项】选项，调出【Excel选项】对话框。
2．选择【高级】选项，然后单击【常规】选项组下的【编辑自定义列表】按钮，此时【自定义序列】对话框出现。
3．在【自定义序列】对话框中，选择【新序列】选项，此时旁边的【输入序列】文本框中会出现插入光标。
4．在【输入序列】文本框中输入所要创建的项目，按【Enter】键。重复这一步，直到所有的项目都输入完毕，如图1.6所示。（请确保所输入的项目是按照所需要的顺序排列的。）
图1.6 使用【自定义序列】对话框创建自己的填充表
5．单击【添加】按钮，将新的填充表添加到【自定义序列】列表框中。
6．单击【确定】按钮完成添加，再单击【确定】按钮回到工作表中。
注意： 如果需要删除自定义填充表，在【自定义序列】列表框中选中它并单击旁边的【删除】按钮即可。
小贴士： 如果在工作表中已经有整理好的填充表，就不用那么麻烦地手动输入每个项目了。此时可以使用单元格编辑框中的【导入】按钮，为所选区域设定一个引用范围。我们可以手动输入引用范围，也可以直接从工作表上选取引用范围。最后单击【导入】按钮，将填充表添加到【自定义序列】列表框中。
我们可以使用填充柄在区域中添加数据或者公式。具体操作是输入原始数据或公式，选中此数据或公式，然后单击并拖曳填充柄直至选中所有目标区域。（此时我们假设所输入的数据不是创建过的序列。）当松开鼠标左键的时候，区域已被填充好了。
需要注意的是，如果原始单元格内的公式包含相对引用，那么Excel会根据其进行调整。举例来说，假设原始单元格包含公式“=A1”，那么此时向下填充时，下一个单元格会包含公式“=A2”，再下一个会包含公式“=A3”，以此类推。
→关于相对引用，请看“3.4.3 了解相对引用格式”
除了填充柄，我们还可以使用Excel的【系列】命令来创建序列，步骤如下。
1．选中需要的第一个单元格，输入起始值。如果想创建的序列不是特定模式（如2、4、6等），则要输入足够的数据。
2．选中需要填充的所有区域。
3．选择【开始】【填充】【系列】选项，【序列】对话框出现，如图1.7所示。
图1.7 使用【序列】对话框创建序列
4．选择【行】或【列】选项。
5．使用【类型】选项组中的选项来创建需要的序列类型。其选项有以下4个。
■ 【等差序列】：选择此选项会以增加步长值的方式创建序列（参见第6步）。
■ 【等比序列】：选择此选项会以同等比值增长的方式创建序列。
■ 【日期】：选择此选项会根据所选择的【日期单位】选项组中的选项来创建序列，如【日】【工作日】【月】和【年】。
■ 【自动填充】：此选项和填充柄差不多。我们可以使用它来扩展数值模式或创建文本序列，如第1季度、第2季度、第3季度等。
如果想扩展数列趋势，可以选中【预测趋势】复选框。此复选框只在选择【等差序列】或【等比序列】选项时可用。
6．如果我们选择的是【等差序列】【等比序列】或【日期】，则需在下面的【步长值】文本框内输入一个数字，这个数字是Excel用来在序列中增量的值。
7．若要设立一个范围，可以在【终止值】文本框中输入合适的数字。
8．单击【确定】按钮完成设置，返回工作表中。
图1.8所示为一些例子。注意“等比序列”这一列（C列）在单元格C12处（数值128）停止了，这是因为下一个数值（256）超出了所设定的数值范围；“日”列（D列）为每隔一天的日期，因为步长值设置为2；而“工作日”列（E列）有些许不同，即日期是连续的，但周末被跳过了。
图1.8 选择【序列】选项的举例
普通的区域复制方法（例如选择【开始】【复制】选项或按【Ctrl】+【C】组合键，然后选择【开始】【粘贴】选项或按【Ctrl】+【V】组合键）通常会将区域内每一个单元格的内容都复制下来，包括数据、公式、格式或任意单元格的命令。而使用高级区域复制技巧时，我们可以根据属性来选择性地复制，或将数据转置到别的行或列。此外，我们还可以用运算方法合并原始资料与目标区域。以上这些都可以通过Excel的【选择性粘贴】命令来实现。接下来的3个小节会讲到这些技巧。
整理工作表时，合并单元格属性可以帮我们节省时间。举例来说，当我们想复制一些公式到某个区域但又不想破坏已有的格式时，可以只复制公式。
想只复制选定的单元格的某些属性，可以按以下步骤进行操作。
1．将需要的区域选中并复制好。
2．选择目标区域。
3．选择【开始】【粘贴】选项，在下拉列表中选择【选择性粘贴】选项。此时会出现【选择性粘贴】对话框，如图1.9所示。
图1.9 使用【选择性粘贴】对话框，按设定的属性来选择性粘贴
小贴士： 我们也可以按【Ctrl】+【Alt】+【V】组合键来打开【选择性粘贴】对话框，或者在目标区域右击，在弹出的快捷菜单中选择【选择性粘贴】选项。
4．在【粘贴】选项组中，选择所需要粘贴到目标区域的单元格的属性。
■ 【全部】：粘贴单元格的所有属性。
■ 【公式】：仅粘贴单元格内的公式。（也可以选择【开始】【粘贴】【公式】选项，即【粘贴】下拉列表中【粘贴】选项下的第二个图标。）
■ 【数值】：将单元格内的公式转换为数值进行粘贴。（也可以选择【开始】【粘贴】【值】选项，即【粘贴】下拉列表中【粘贴数值】项下的第一个图标。）
■ 【格式】：仅粘贴单元格格式。
■ 【批注】：仅粘贴单元格批注。
■ 【有效性验证】：仅粘贴遵循有效性规则的单元格。
■ 【所有使用源主题的单元】：粘贴单元格的所有属性，同时将目标区域按照源主题格式化。
■ 【边框除外】：粘贴除边框以外的单元格的所有属性。（也可以选择【开始】【粘贴】【无边框】选项，即【粘贴】下拉列表中【粘贴】项下的第五个图标。）
■ 【列宽】：改变目标区域的列宽以适应源列宽。此时无数据粘贴。
■ 【公式和数字格式】：粘贴单元格内的公式和数字格式。
■ 【值和数字格式】：将单元格内的公式转换为数值，且只粘贴数值和数字格式。
■ 【所有合并条件格式】粘贴单元格的所有属性，并根据源文件和目标区域合并条件格式。
5．如果不想粘贴空白的单元格，可选中【跳过空单元】复选框。
6．如果仅仅想粘贴公式，让目标单元格的数据和源单元格的一致，可以单击【粘贴链接】按钮。举例来说，源单元格为A1，则目标单元格的数据会根据公式“=$A$1”来设置。如果不需如此，则单击【确定】按钮即可。
Excel可以用运算方法合并两片区域。举例来说，我们想将一片区域内的常量都翻一番。除去使用公式或更笨的方法，如手动将每个单元格乘以2，我们还可以创建一个和之前的区域一样大的只包含2的新区域，然后将这两个区域合并，并使用Excel将它们相乘。接下来的几步会告诉你怎么做。
1．选好目标区域。（请确保目标区域和源区域形状大小都相同。）
2．输入所需要的常量，然后按【Ctrl】+【Enter】组合键。Excel会用此常量将目标区域填满。
3．选择并复制源区域。
4．再次选择目标区域。
5．选择【开始】菜单，在【粘贴】的下拉列表中选择【选择性粘贴】选项，打开【选择性粘贴】对话框。
6．使用【运算】选项组中的选项来完成运算。
■ 【无】：不进行任何操作。
■ 【加】：将目标单元格与源单元格相加。
■ 【减】：用目标单元格内的数字减去源单元格内的数字。
■ 【乘】：将目标单元格与源单元格相乘。
■ 【除】：用目标单元格内的数字除以源单元格内的数字。
7．如果在操作时想避免选中空白单元格，可以选中【跳过空单元】复选框。
8．单击【确定】按钮。此时目标单元格内粘贴的就是运算结果。注意此结果为最终数据，而不是公式。
如果我们想将行内的数据转移到列内，或者反过来，则可以使用【转置】命令来完成，步骤如下。
1．选择并复制源单元格。
2．选中目标区域左上角的单元格。
3．选择【开始】【粘贴】选项，在下拉列表中选择【转置】（即【粘贴】项下的最后一个图标）。如果此时【选择性粘贴】对话框是打开的，直接选中【转置】复选框也可以。最后单击【确定】按钮。Excel就完成了对源区域的转置，如图1.10所示。
图1.10 使用【转置】命令将列内的内容转移到行内，或反之
删除一片区域意味着将单元格从工作表中完全删去。但如果我们想清除单元格里面的内容或公式，同时又想保留单元格，可以使用【清除】命令，请看以下步骤。
1．选择好想要清除的区域。
2．选择【开始】【清除】选项，调出【清除】下拉列表。
3．按需要选择【全部清除】【清除格式】【清除内容】【清除批注】或【清除超链接】选项。
使用填充柄来清除区域内的数据或公式时，可以使用以下两个小窍门。
■ 如果只是想清除区域内的数据和公式，可先选择好区域，然后单击并拖曳填充柄扫过所需清除的单元格，此时这些单元格变成了灰色。释放鼠标左键的时候，数据和公式就都被清除了。
■ 如果想清除掉区域内的所有内容，包括数据、公式、格式和批注等，可以先选好区域，然后按住【Ctrl】键不放，接着单击并拖曳填充柄扫过所需清除的单元格。释放鼠标左键的时候，单元格的内容就都被清除掉了。
Excel中总是包含数以百计的数据，本书余下的章节会帮助你通过创建公式、应用函数、执行数据分析等来将复杂的数据理出个头绪。不过，有时候并不需要分析整个工作表，也许我们只是想知道某些简单问题的答案，例如，哪个单元格内的数据小于零？最大的10个数据是哪些？哪些单元格内的数据高于或低于平均水平？
仅仅瞥一眼工作表并不能轻松回答这些简单的问题，而且数据越多，这些问题就越难回答。为帮助你盯好自己的工作表并回答以上或类似的问题，Excel提供了条件格式来帮助你处理单元格。条件格式是一种特殊格式，仅适用于那些满足某种条件——在Excel中称之为规则的单元格。例如，我们可以使用条件格式来将所有的负值变为红色字体。
【突出显示单元格规则】是指应用于能满足指定标准的单元格的规则。要创建这个规则，可以选择【开始】【条件格式】【突出显示单元格规则】选项。此时下拉列表中有以下7个选项。
■ 【大于】：选择此选项可应用格式于比指定的值大的单元格。举例来说，我们想确认销售额比去年多10%的销售人员，则可以使用一列来计算年销售百分比差异（见图1.12的D列），并在此列应用【大于】规则来查看大于0.1的增长。
■ 【小于】：选择此选项可应用格式于比指定的值小的单元格。例如，我们想知道哪个销售员的业绩不如往年，则可以应用此选项来查看百分比，或查看小于0的绝对差。
■ 【介于】：选择此选项可应用格式于指定的两个值之间的单元格。举例来说，我们有一批固定收益投资待选择，但你仅对中期投资周期的项目感兴趣，此时可以应用这个规则来突出显示投资周期处于5至10年之间的值。
■ 【等于】：选择此选项可应用格式于与指定的值相同的单元格。例如，我们对库存清单上最近已脱销的产品感兴趣，可以应用此规则突出显示一列中等于0的值。
■ 【文本包含】：选择此选项可应用格式于包含指定文本的单元格（英文状态下不区分大小写）。举例来说，在你面前有一份包含等级评定的债券表，你只对那些评定等级为中上或更高（A级、AA级或AAA级）的债券感兴趣，则可以应用此规则来突出显示那些包含字母A的单元格。
注意： 【文本包含】选项不适用于某些包含字母的低等级的特定等级代码，如Baa、Ba等。
■ 【发生日期】：选择此选项可应用格式于符合指定日期的单元格，如昨天、今天、明天、上周、下周等。例如，员工数据表里面包含各员工的出生日期，我们可以利用此规则来找出下周过生日的员工，以便提前做好庆祝的准备。
■ 【重复值】选择此选项可应用格式于那些在区域内出现不止一次的单元格。举例来说，你手头上有一份账号表，每个客户的账号都不会与他人的相同，此时可以运用此规则来确保每个客户账号的唯一性。你也可以将单元格格式化为唯一值，即仅在区域内出现一次的值。
每次应用规则时，都会出现一个用来指定条件和格式的对话框。例如，图1.11显示的就是【小于】对话框。在此规则下，你可以查找小于0的值。图1.12显示的是应用条件格式的工作表。
图1.11 【小于】对话框
图1.12 应用图1.11中显示的条件格式规则后的结果显示在本图D列中
【项目选取规则】是指在区域内将最高或最低的数据标出的规则。举例来说，我们要和一堆数字打交道，【项目选取规则】可以帮你标出那些最大或最小的数字。我们可以使用【项目选取规则】根据绝对值来选择，如选择值最大的10项；或根据百分比来选择，如选择值最小的25%。也可以在那些高于或低于平均水平的单元格内使用此格式。选择【开始】【条件格式】【项目选取规则】选项，在下拉列表中有以下6个选项。
■ 【值最大的10项】：选择此选项，区域内值最大的N个单元格会被标出——此处的“N”是由我们所规定的数字，默认为10。例如，在产品销售表中，我们可以利用此规则来查看排名前50的产品。
■ 【值最大的10%项】：选择此选项，区域内值最大的N%的单元格会被标出——此处的“N”是由我们所规定的百分比，默认为10。例如，在销售人员销售业绩表中，我们可以利用此规则来查看位于前5%的销售精英。
■ 【值最小的10项】：选择此选项，区域内值最小的N个单元格会被标出——此处的“N”是由我们所规定的数字，默认为10。举例来说，我们可以运用此规则查看单位产品销售额表格中哪些产品是处于最后20名，以便决定是对它们进行促销还是停产。
■ 【值最小的10%项】：选择此选项，区域内值最小的N %的单元格会被标出——此处的“N”是由我们所规定的数字，默认为10。例如，在产品生产缺陷表中，我们可以运用此规则查看缺陷最少排名前10%的产品。
■ 【高于平均值】：选择此选项，可以找到整个区域中高于平均值的单元格。举例来说，在一份投资回报表中，我们可以利用此规则查看那些高于平均回报水平的投资。
■ 【低于平均值】：选择此选项，可以找到整个区域中低于平均值的单元格。例如，将此规则套用于一份产品收益表中，我们就可以找到那些收益低于平均水平的产品，然后决定下一步措施是改进销售还是降低成本。
每次应用【项目选取规则】时，都会出现一个用来设定条件的对话框。对于【值最大的10项】【值最大的10%项】【值最小的10项】以及【值最小的10%项】4个规则，我们可以指定适用于单元格的条件和格式；而对于【高于平均值】和【低于平均值】规则，只可以指定格式。举例来说，图1.13所示为【10个最大的项】对话框。在这个规则下，我们可以查找区域内10个最大的数值。图1.14显示的是套用了条件格式的工作表。
图1.13 【10个最大的项】对话框
图1.14 套用图1.13中显示的条件格式规则后的结果显示在本图C列中
想在复杂的工作表中找出特殊数据，使用【突出显示单元格规则】和【项目选取规则】是很好的方法。不过有时候你可能对工作表中相似值之间的关系更感兴趣。举例来说，我们手里有一份产品表，其中有一列的内容是单位产品销售额。那么如果想比较一下所有相关产品的销售额该怎么办呢？我们可以创建一列新的数据，用来计算每种产品的销售额与最高销售额之间的百分比。例如最高销售额是1000，那么销售了500的产品会显示为50%。
在系统内存范围内，Excel提供了无限量的条件格式规则。不过需要记住的是，当我们在一片区域使用一个规则时，如果此时需要应用另一个规则，原有规则不会被替代，而新的规则会被添加到原有规则中。如果想改变原有规则，可以选择【开始】【条件格式】【管理规则】选项，选择需要改变的规则，然后单击【编辑规则】按钮。
这个方法也许可行，但那样的话我们所做的就只是在工作表中加了一堆数字，却对工作没多大帮助。现在我们所需要的是一个可以很容易地看到区域中相关数据的方法，而这个方法就是使用数据条。
数据条是一些彩色的水平方向的横条，填充于区域内的单元格中，看起来有点像横条图表。它的主要特征是数据条的长度取决于单元格内的数据值：数值越大，数据条越长。数值最大的数据条最长，数值大小都反映在数据条的长度上。举例来说，单元格内的数据是最大数值的一半，那么它的数据条的长度就是最大值数据条长度的一半。
想要使用【数据条】，可以先选择好区域，选择【开始】【条件格式】【数据条】选项，然后选择好颜色。图1.15显示的即在销量列使用了【数据条】规则后的情况。
在Excel中，默认最大的数值的数据条最长，最小的数值的数据条最短。但如果我们想要根据不同的标准来查看数据条该怎么办呢？例如，在一份测验分数表中，我们想看处于0到100之间的分数。在这种情况下，不管最高分是多少，50分的数据条都只有一半长。
想要使用自定义数据条，先选择好区域，然后选择【开始】【条件格式】【数据条】【其他规则】选项，这时弹出【新建格式规则】对话框，如图1.16所示。此时在【编辑规则说明】选项组里，要确保【格式样式】下拉列表框里选择的是【数据条】。注意【格式样式】下面的【类型】下拉列表框分为【最小值】和【最大值】两个部分，这两个部分决定了如何应用数据条。【类型】下拉列表框中有6个选项。
■ 【自动】：这个是默认选项，Excel会根据数据自动选择类型。
■ 【最低值】/【最高值】：选择这两个选项时，最小的数值的数据条最短，最大的数值的数据条最长。这是最常用的类型，当我们选择【自动】时通常会使用这个类型。
图1.15 使用【数据条】规则查看区域中有关联的数据
图1.16 使用【新建格式规则】对话框来设置不同类型的数据条
■ 【数字】：选择此选项时，数据条的长度取决于我们所指定的数字，此数字需在【值】文本框中输入。区域中最短的数据条为所有小于或等于指定的最小值的数据，而大于或等于指定的最大值的数据所在的单元格将会得到最长的数据条。
■ 【百分比】：选择此选项时，最大值的百分比决定了数据条的长度。小于或等于指定的百分比的数据所在的单元格的数据条最短，举例来说，如果指定百分比为10%，同时区域内的最大值为1000，那么所有小于或等于100的单元格将会得到最短的数据条。而大于或等于指定的百分比的数据所在的单元格的数据条最长，例如指定百分比为90%，同时区域内的最大值为1000，那么所有大于或等于900的单元格的数据条都会是最长的。
■ 【公式】：选择此选项时，数据条的长度取决于单元格内的公式。
■ 【百分点值】：选择此选项时，数据条的长度取决于区域内每个数据占所有数据的百分点值。Excel会将所有数据进行排列并分配位置，那些排在所指定的最小百分点值前面的数据将会得到最短的数据条。举例来说，100个数据，指定最小百分点值为10，则排在第10或更靠前的单元格的数据条会最短。而那些排在所指定的最大百分点值后面的数据的数据条会最长。例如，100个数据，指定最大百分点值为75，那么排在第75位或更高位置的单元格将会得到最长的数据条。
检查数据的时候，“查看大图”总是很有用。举例来说，我们可能想知道数据整体分布情况，如是否有很多的低值，而高值只有一点点？是否大部分值都处在平均水平？是否有些特殊的值大大高于或低于其他所有或大部分的值？同样地，我们也可能想对数据做个判断，如产品数量少但销售额高为优，而利润低、员工流动快为差。
我们可以使用【色阶】命令来对工作表中的以上或更多的情况进行分析。色阶和数据条很相似，都是用来比较区域中相关数据的，不过数据条表现为单元格内的横条，而色阶则表现为数据下面的底纹。例如，最低的值是红色底纹，较高的为浅红色底纹，然后依次是橙色、黄色、淡绿色，最后最高值为深绿色。
这种颜色分配方法可以方便用户直观地看清区域内各单元格的数据分布情况。举例来说，因为颜色完全不同于别的单元格，所以那些特殊值一眼就可以被找出来。此时数据判断就非常容易了，我们可以把红色的想象成红灯，所以为差；而绿色是绿灯，所以是优。
使用【色阶】命令时，先选好区域，接下来选择【开始】【条件格式】【色阶】选项，然后选好颜色。图1.17显示的是使用了【色阶】命令的多国GDP增长率表。
【色阶】选项的配置和之前学过的【数据条】选项的配置差不多。想要自定义色阶，先选好区域，然后选择【开始】【条件格式】【色阶】【其他规则】选项，此时弹出【新建格式规则】对话框。在【编辑规则说明】选项组中，对于【格式样式】，你可以选择【双色刻度】或【三色刻度】选项。如果选择的是【三色刻度】选项，那么【类型】【颜色】下拉列表框和【值】文本框都有3个参数可选择，分别是【最小值】【中间值】和【最大值】，如图1.18所示。注意【类型】下拉列表框中的选项和我们之前学习的【数据条】中的选项是一样的。
图1.17 使用【色阶】命令可方便用户直观查看区域内数据的分布情况
图1.18 在【格式样式】下拉列表框中选择【三色刻度】选项
如果你正试图整理一大堆数据，那么图标将会是个不错的帮手。例如，在看电影评论时，一个简单的大拇指朝上或拇指向下的图标可以让你快速理解并得到一些关于电影的有用信息。
很多别的图标同样有很强的联想性。例如，“钩”号表示某事是很好的、已完成的，或可接受的；而相反地，“叉”号表示某事很不好、未完成或不可接受。绿色的圈表示肯定，而红色的圈则表示否定——就像红绿灯一样。笑脸表示好，愁容表示差。向上的箭头表示事物是向前发展的，向下的箭头表示倒退，而水平的箭头则意味着一成不变。
Excel的图标集中有很多这样的很好用的图标。和使用数据条与色阶一样，我们可以使用图标集来查看区域内有关联的数据。而且，图标集中有很多特别的图标可以让我们更清楚地知道一个单元格内的数据和别的单元格内的数据之间的关系。例如，最高值的单元格内有向上的箭头，最低值的单元格内有向下的箭头，而中间值的单元格内则为水平箭头。
使用【图标集】命令时，先选好区域，接下来选择【开始】【条件格式】【图标集】选项，然后选好一套图标。我们选择了“五向箭头”来显示员工销售额百分比的增长与减少，如图1.19所示。
图1.19 使用【图标集】中有含义的图标查看相关数据
自定义【图标集】与之前学习的自定义【数据条】和【色阶】差不多，不过需要自己指定图标的类型和值。要记住，最小值的图标范围总是比次小值的图标范围要小。选好区域，选择【开始】【条件格式】【图标集】【其他规则】选项，此时弹出【新建格式规则】对话框，如图1.20所示。在【编辑规则说明】选项组中，选择好所需要的图标样式，然后设置好【值】【类型】等。
图1.20 使用【新建格式规则】对话框来自定义【图标集】
尽管区域可以帮助我们有效地处理大量的单元格，但是在用区域坐标时仍然有以下障碍。
■ 不能一次使用多于一组的区域坐标。每次使用区域时，都需要重新定义一次坐标。
■ 区域表示法不是很直观。想知道类似“=SUM(E6:E10)”这样的公式是由哪些数字相加的，还得再对着单元格区域在表里看一遍。
■ 定义区域坐标时，稍有不慎就可能会导致灾难性后果，尤其是当我们删除区域时。
不过以上障碍都可以通过定义“区域名称”，即给单个的单元格或单元格区域起名字来克服。区域名称可以用来代替区域坐标。举例来说，一片包含公式或者命令的区域，我们可以使用区域名称来选中，而不必手动选择或输入坐标。区域名称没有数量限制，想起多少就可以起多少，甚至还可以给同一片区域指定多重名称。
区域名称还可以使我们的公式直观而易读。例如，你将E6到E10这片区域定义为“八月销售额”，于是公式“=SUM(八月销售额)”的功能就不言而喻了。因为不需要再指定区域坐标，所以区域名称还可以增加区域操作的准确性。
除了可以解决以上问题，区域名称还有很多的好处，如下所示。
■ 名称比坐标好记。
■ 即使移动区域到另一个位置，名称也不会改变。
■ 在区域内插入或删除行或列时，名称会自动调整。
■ 区域名称使得工作表操作更加简便。可以使用【定位】命令来快速跳转到已定义的区域。
■ 可以使用工作表标签来快速定义区域名称。
本章不仅会告诉你如何定义并使用区域名称，而且会带你感受区域名称在烦琐工作中的便利性。
区域名称的定义是非常灵活的，只需遵循以下几点规则即可。
■ 名称最多包含255个字符或汉字。
■ 名称必须以字母、汉字或下划线开头，其余的可以是字母、汉字、数字或符号，但不可以是空格。对英文名称来说，可以用下划线将词与词之间分开，或英文字母大小写混合，如Cost_Of_Goods或CostOfGoods。但其实Excel不能区分区域名称中的大小写，这么做只是为了自己明白。
■ 不要使用类似Q1这样的单元格名称作为区域名称，也不要使用诸如+、-、*、/、＜、＞、&此类的运算符号来定义区域名称，否则的话当我们在公式中使用区域名称时会造成混乱。
■ 为了方便输入，在保证清楚明白的情况下名称越短越好。输入“2010总利润”比“2010年财务年度利润总额”要快得多，同时也比“10利润”要清楚明白得多。
■ 不要使用Excel的保留字符串，如Auto_Activate、Auto_Close、Auto_Deactivate、Auto_Open、Consolidate_Area、Criteria、Data_Form、Database、Extract、FilterDatabase、Print_Area、Print_Titles、Recorder、Sheet_Title等。
有了以上规则，接下来的几小节会告诉你如何定义区域。
Excel编辑栏旁边的【名称框】里显示的一般是当前活动单元格的地址。不过，它也有很多额外的功能，这些功能使得定义区域更简单。
■ 完成区域定义后，不管何时选择那个区域，区域名称都会显示在【名称框】里，如图2.1所示。
图2.1 选择已定义的区域时，该区域名称会显示在【名称框】中
■ 【名称框】同时也是下拉列表框。想要快速选择已定义的区域，单击【名称框】右侧的小黑三角，在下拉列表中选择需要的名称，此时Excel会选中定义为此名称的单元格。
【名称框】有一个很方便的功能，就是可以调整大小。如果当前名称显示不完整，可以将鼠标指针移动到【名称框】的右边，当鼠标指针变成一个双向水平箭头的时候，单击并拖曳箭头就可以调整其大小了。
使用【名称框】可以很容易地定义一个区域，步骤如下。
1．选好需要定义的区域。
2．单击【名称框】，此时可以看到插入光标。
3．输入名称，按【Enter】键。这时Excel会自动定义此区域。
使用【名称框】来定义区域比较便捷而直观。不过，它有以下两个虽小却让人很恼火的问题。
■ 如果我们要定义的名称已经存在，那么Excel会直接放弃新选择的区域，回到与已存在名称相对应的区域。这就意味着我们不得不重新选择一遍区域并使用另一个名称定义。
■ 如果我们选择的区域不正确，那么定义的时候，Excel不会直接让你选择是确定区域还是删除区域，而是会重新开始。
想解决以上两个问题，可以使用【新建名称】对话框，它有很多优点，如下所示。
■ 它会将定义好的名称一一列举，这样可以避免区域重名。
■ 如果不小心犯了错误，它可以很轻松地帮我们确定区域坐标。
■ 可以随时删除区域名称。
我们可以通过以下几步来使用【新建名称】对话框定义区域名称。
1．选好需要定义名称的区域。
2．选择【公式】【定义名称】选项。或者，也可以右击选好的区域，然后选择【定义名称】选项。此时弹出【新建名称】对话框，如图2.2所示。
图2.2 使用【新建名称】对话框来定义区域名称时，所选择区域的坐标会自动显示在【引用位置】文本框中
3．在【名称】文本框中输入名称。
小贴士： 当你用英文定义区域名称时，记得将首字母大写。为什么呢？因为当你需要解决公式中的问题时，这就派上大用场了。你在已定义好的区域（此区域名称是区分大小写的）中插入一个公式，插入时可以全部以小写字母输入。公式输入完成后，Excel会将你所插入的名称转换为当初定义的字母。如果这时候此名称依然全部是小写字母，说明Excel没有识别出这个名称，也就是说，你应该在输入名称的时候拼错了。
4．在【范围】下拉列表框中选择所定义名称的适用范围。大部分情况下应选择【工作簿】选项。在接下来的“2.1.3 改变范围来定义表级名称”小节中，我们会讨论到限定工作表名称的好处。
5．在【备注】文本框中输入对区域名称的描述或注解。在公式中使用此名称时，这个备注会出现。稍后在“2.2.2 使用自动填充”小节中，我们会详细讨论此命令。
6．如果显示在【引用位置】文本框中的区域不正确，可以使用以下两种方法来修正。
■ 输入正确的单元格地址。记住输入时要以=（等号）开头。
■ 单击【引用位置】文本框右侧的【暂时缩小对话框】按钮，然后用鼠标或键盘在工作表中选择新的区域。
警告： 如果需要使用方向键在引用位置周围移动以便编辑区域地址，记得要先按【F2】键，让Excel进入编辑状态。否则的话，Excel将会保持点状态，程序会认为你在选择一个单元格而不是一片区域。
7．单击【确定】按钮回到工作表。
我们可以使用Excel来定义区域名称的范围，也可以借此范围知道在公式中区域名称能被识别的程度。举例来说，在【新建名称】对话框中，如果我们在【范围】下拉列表框中选择了【工作簿】选项或直接在【名称】文本框中输入了名称，那么此名称将会适用于此工作簿中的所有工作表，这个就叫作簿级名称。也就是说，工作表1中的公式可以引用工作表3中的区域，只要直接引用已定义的区域名称即可。但是，如果我们需要在不同的工作表中使用相同的名称，例如，存在4个工作表，分别为第一季度、第二季度、第三季度和第四季度，我们需要在每个工作表中分别定义名称为预算时，以上情况就有些麻烦了。
如果我们需要在不同的工作表中使用相同的名称，可以为特定的工作表的名称定义一个范围，这个范围内的名称就叫作表级名称，这就意味着此名称只会涉及所定义的工作表区域，而非整个工作簿。
我们可以通过使用【新建名称】对话框来创建表级名称，具体操作是在【范围】下拉列表框中选择所需要的工作表。
使用【新建名称】对话框时，Excel有时会给选好的区域提供命名建议。举例来说，Excel建议C9到F9这片区域命名为工资，如图2.3所示。“工资”其实是所选区域的行标题，Excel会根据相邻单元格内的文本来推测我们会使用的名称。
图2.3 Excel根据相邻单元格内的文本来推测我们会使用的名称
如果不想等待Excel的推测，我们可以明确指示程序使用相邻文字来作为区域名称，请看以下步骤。
1．选好要定义的区域，记得要包含准备用作名称的单元格，如图2.4所示。
图2.4 选择区域时要包含准备用作名称的单元格
2．选择【公式】【根据所选内容创建】选项，或按【Ctrl】+【Shift】+【F3】组合键。打开【以选定区域创建名称】对话框，如图2.5所示。
图2.5 使用【以选定区域创建名称】对话框来指定用作名称的单元格的位置
Excel会猜测用作名称的单元格的位置并选中合适的复选框。在上面的例子中，Excel选中了【最左列】复选框。如果这不是你想要的，取消选中即可，然后选中合适的复选框。
3．单击【确定】按钮。
注意： 如果用作名称的单元格里包含非法字符，如空格，则Excel会用下划线（_）来代替这些非法字符。
文本定义区域并不局限于一行或一列，我们可以选择一片既包含行也包含列的区域，Excel会根据每行或每列的标题来定义区域。图2.6所示的【以选定区域创建名称】对话框中，【首行】和【最左列】复选框都被选中。
图2.6 Excel可以同时定义行和列的名称
当我们使用以上方法定义区域时，Excel会给所选区域的左上角单元格以“特殊待遇”。具体来说，它会将此单元格内的文本作为所选择的包含表格数据的整个区域的名称。举例来说，在图2.6中，所选区域的左上角单元格是包含标签“费用支出”的B5，在创建名称后，所有的表格数据，即C6到F10这一片区域都被命名为“费用支出”如图2.7所示。
图2.7 同时命名行和列时，Excel会使用左上角单元格内的标签作为包含表格数据的区域的名称
让工作表变得易懂的最佳方法之一就是为每一个常量命名。举例来说，如果在工作表中，很多公式都涉及了一个可变利率，那么我们可以将其命名为利率并在公式中使用此名称，这样公式都变得易读多了。
命名常量的具体方法是先在工作表中为常量预留一个区域，然后将单元格分别命名。举例来说，图2.8所示为包含3个已命名常量的工作表：利率（单元格B5）、期限（单元格B6），以及合计（单元格B7）。请注意单元格E5内的公式是如何使用这3个已命名的常量的。
图2.8 将公式中的常量分组并命名，使工作表变得易读
如果不想让工作表显得很凌乱，可以将常量命名，并使其不在工作表中显示。选择【公式】【定义名称】选项，打开【新建名称】对话框，在【名称】文本框中输入常量的名称，然后在【引用位置】文本框中输入等号（=）以及此常量的值，如图2.9所示。
图2.9 在【新建名称】对话框中创建并命名常量
小贴士： 常量的命名并不局限于数字或字符串，也可以使用函数来命名。例如，我们可以在【引用位置】文本框中输入“=YEAR(NOW())”来创建一个常量，让其总是返回当前的年份。不过，这个方法只有在使用一个长而复杂的公式，且这个公式会在不同的地方使用时才比较好用。
创建好一个名称后，我们就可以将它运用在公式或函数中了，还可以对其进行编辑、删除操作。在接下来的几小节中，你会学到以上这些操作，以及更多的技巧。
区域名称可以直接在公式或函数中应用：用区域名称代替区域坐标即可。举例来说，一个单元格内包含以下公式：
=G1这个公式将此单元格的值设置为单元格G1的当前值。如果G1的名称是总费用，那么刚才的公式也就相当于：
=总费用同样地，假设有一个公式：
SUM (E3:E10)如果E3到E10这片区域的名称是销售额，那么这个公式也就相当于：
SUM (销售额)→想了解更多关于Excel公式中名称的使用，请看“3.7 在公式中使用区域名称”。
如果你对某个特定名称不太确定，可以通过如下步骤将它粘贴在工作表中。
1．输入公式或函数。
2．在需要插入区域名称的时候选择【公式】【用于公式】选项，此时下拉列表中会显示当前工作表中的名称，如图2.10所示。
图2.10 选择【用于公式】选项，下拉列表中会显示已定义的区域名称
3．单击所需要的名称，此时Excel会将其粘贴在指定位置。
如果我们使用的是表级名称，那么“怎么用”取决于“在哪里”，如下所示。
■ 如果我们使用的名称就在其所定义的工作表中，则可以直接使用。也就是说，不用另外指定工作表名称。
■ 如果我们使用的名称在别的工作表中，那么需要写出全称，包括工作表名称和区域名称（工作表名称!区域名称）。
■ 如果要使用的名称在别的工作簿里，那么在此名称前面，必须加上由单引号（‘’）引起来的工作簿名称。例如，工作簿“抵押分期偿还”中包含区域名称“利率”，当我们使用此名称时，需要用以下格式：
'抵押分期偿还. xlsx'!利率警告： Excel并不介意我们将表级名称命名得和簿级名称一样。在工作簿所有其他的工作表中，如果我们单独使用一个名称，Excel会认为我们所提到的是簿级名称。但如果是在命名此名称的工作表中使用这个名称，那么Excel会认为我们所要使用的是表级名称。
那么如果想要在命名了此名称的工作表中使用簿级名称该怎么办呢？答案是在此名称前面加上工作簿的名称以及一个感叹号（！）。举例来说，假设在“费用支出”工作簿中，当前工作表有一个名为“合计”的表级名称，同时也有一个同样的簿级名称。想在当前工作表中使用后者，使用如下格式即可：
费用支出.xlsx!合计
在“第6章 理解函数”中，我们会学到Excel的【自动填充】功能。在输入时，会有一个和所输入的函数相配的函数名称下拉列表出现，我们可以从中选择想要的函数，而不必输入剩余的函数名称，这样更快、更准确。区域命名也同样可以使用【自动填充】功能。当我们在公式中输入区域名称的前几个字母时，Excel就把它们加入了自动填充列表中。Excel甚至还会显示相关注释文本，如图2.11所示。想要将名称插入公式中，使用方向键在列表中选择，然后按【Tab】键即可。
图2.11 Excel中的【自动填充】功能
选择已经命名的区域很简单，有以下两种方法。
■ 使用【名称框】，快速选择想要的名称。
■ 选择【开始】【查找和选择】【转到】选项，打开【定位】对话框。在【定位】对话框中选择需要的区域名称并单击【确定】按钮。
如果我们需要给别人提供工作表或自己需要查找几个月前的表格，可以粘贴一份工作表区域名称列表。这份列表可以是区域的名称，以下为粘贴区域名称列表的步骤。
1．将活动单元格移动到工作表的空白区域。这个区域要足够大，还不能重叠在别的有数据的区域上。注意这个区域需要两列的位置：一列用来写名称，另一列用来标注与之相配的区域坐标。
2．选择【公式】【用于公式】，打开【粘贴名称】对话框，或按【F3】键，弹出【粘贴名称】对话框。
3．单击【粘贴列表】按钮，此时Excel会将定义的名称和对应的范围粘贴至选定的区域。
Excel中的【名称管理器】对话框为用户提供了一个界面来管理区域名称。选择【公式】【名称管理器】选项（或按【Ctrl】+【F3】组合键），弹出【名称管理器】对话框，如图2.12所示。
图2.12 使用【名称管理器】对话框来修改、筛选或删除区域名称
如果工作簿中定义了太多的名称，那么使用【名称管理器】对话框会很不方便。我们可以使用Excel中的筛选功能来缩小名称范围。单击【筛选】按钮，然后可以选择以下筛选选项。
■ 清除筛选：选择此选项，撤销所有筛选。
■ 名称扩展到工作表范围：选择此选项，查看以当前工作表为范围的名称。
■ 名称扩展到工作簿范围：选择此选项，查看范围为当前工作簿的名称。
■ 有错误的名称：选择此选项，仅查看那些有错误的名称，如#NAME?、#REF!或#VALUE!。
■ 没有错误的名称：选择此选项，仅查看那些没有错误的名称。
■ 定义的名称：选择此选项，查看Excel内置名称或被用户定义的名称（也就是说，我们不会看到那些Excel自动创建的名称，如表名称等）。
■ 表名称：选择此选项，仅查看Excel创建的表名称。
如果想让已存在的区域名称用于另一组区域坐标，有以下两种方法可以实现。
■ 移动区域。当我们移动区域的时候，Excel会同时将名称一起移动。
■ 如果想调整已存在区域的坐标或者将一个名称与不同的区域相关联，打开【名称管理器】对话框，单击想要改变坐标的名称，然后在【引用位置】文本框中输入区域坐标即可。
在电子表格中，向一行或一列中不断地加入数据是很常见的。例如，你在跟进一份进行中的工程的花费清单，或想要跟踪某种产品每日的销售额。从区域名称的角度来看，如果你一直在已选定的区域中输入数据是没什么问题的，因为Excel会根据新数据来自动调整区域坐标。但是，如果你一直在区域的最后输入新数据就不行了，那样你就需要手动调整区域坐标来将新数据包含进区域内。数据输入得越多，麻烦就越大。为了避免这种情况的发生，下面提供了两种解决方法。
方法1：在区域的最后加入一个空白单元格。第一个解决方法是将区域命名并在最后加入一个额外的空白单元格。举例来说，区域C4到C12被命名为总计，其中C12就是一个空白单元格，如图2.13所示。
图2.13 想要Excel自动调整区域名称坐标，可以在区域最后加入一个空白单元格
这种方法的好处就是当你每次在区域中间插入数据的时候，Excel会自动调整“总计”这个名称对应的区域坐标，而空白的单元格还是会在区域的最下面，如图2.14所示。从图中可看到，中间增加了一行数，而选中的区域变成C4∶C13，其对应的区域名称仍然是“总计”。
图2.14 空白单元格仍在区域最下方
方法2：命名整个行或列。更简便的方法是将要加入数据的整个行或列命名。首先选好行或列，在【名称框】中输入名称，然后按【Enter】键。使用这个方法，不管我们在行或列中加入什么数据，都会自动成为该区域的一部分。
警告： 在使用方法2时，要插入数据的行或列中不能有其他相冲突的数据。举例来说，在我们要插入数据的那行里，有别的无关联的数字，这些数字也会被包含在区域名称中，而这些数字会妨碍我们在公式中使用该名称。
如果需要改变一个或多个区域的名称，或者改变了一些行或列的标签，需要重新命名区域并删掉旧的名称，可以按照以下方法来做。
打开【名称管理器】对话框，选中需要改变的名称，然后单击【编辑】按钮，调出【编辑名称】对话框。在【名称】文本框中输入新的名称，单击【确定】按钮即可。
如果我们不再需要一个区域名称，那么可以从工作表中删掉它，以避免杂乱。以下是必要的步骤。
1．选择【公式】【名称管理器】选项。
2．选择想要删除的名称。
3．单击【删除】按钮，此时会弹出确认删除的对话框。
4．单击【确定】按钮。
5．单击【确定】按钮。
如果有区域是重叠的，可以对重叠的区域进行交集操作。举例来说，图2.15所示的工作表有两个区域：C4到E9和D8到G11。那么对于交集单元格D8到E9，可以用C4:E9 D8:G11来记录。
图2.15 两片区域有交集
如果工作表中有已经命名了的区域，那么交集操作可以使得工作表更易读，因为我们可以通过单元格的行或列的名称来使用单独的单元格。例如，在图2.16中，C6到C10被命名为一月，而C7到F7被命名为租金。那么它们的交集C7即为一月租金（请看单元格I7）。
图2.16 区域命名完毕后，可以给单独的单元格以行和列的标题来定义交集名称
警告： 如果定义交集的时候Excel显示#NULL!，那说明你所定义的两个区域没有交集。
