我的书签
添加书签
移除书签第2章 Excel你必须懂的
我总认为Excel的设计工程师们个个才华横溢，聪明绝顶，因此他们设计的产品应该有很严谨的设计逻辑，他们不会让用户那么痛苦地去学习Excel，应该通过一种方法，让用户快乐地学习Excel，在较短的时间里从入门级达到所谓的高手水平。
那么这个Excel的设计逻辑是什么呢？到现在似乎也没有人真正地总结出来，我经常思考，如何用最短的时间，把Excel最基本的规律传递给Excel用户，让Excel用户在掌握了这些基本规律后，能够举一反三，在较短的时间里把自己的Excel水平从初级晋升到所谓的高级，而不用去“痛”下决心，花费大量时间去“啃”那些大部头的Excel图书？
经过多年的实践，我认为Excel学习完全不必如此枯燥，只要掌握了几条Excel软件的基本规律，我们每个人都可以很快地掌握Excel软件的操作和应用，并成为所谓的高手。
在这里，我试图总结出这些Excel学习的基本规律，让大家在了解这些基本规律后，能够在较短的时间内，使自己对Excel的理解和应用水平上升到一个新的高度，为大家的工作效率和个人生产力的提升做一点贡献。
在本章中，我们需要重点掌握的是：①尽管Excel单元格中的内容千变万化，但它本质上只存储两种内容——文本和数字；②日期和时间的本质也是数字；③单元格地址的引用方式和公式的自动复制原理；④如何识别选中区域中的活动单元格。
2.1 智能感应菜单
你有没有遇到过这种情况：你需要用到的Excel功能，翻遍了所有Excel菜单也没找到？
在Excel 2003版本中，有时需要的Excel功能经常需要进入好几级子菜单才能找到，从Excel 2007版本以后，微软冒着可能不被用户认可的风险，对Excel的菜单结构进行了颠覆性的设计，采用了所谓的“功能区（Ribbon）”的概念，通过这种改变，使得Excel在有限的电脑屏幕空间下，让尽可能多的Excel功能控件出现在Excel界面上，避免了在Excel 2003中，必须点开好几级菜单以后才能找到所需的功能的麻烦。
目前流行的Excel 2010版本和Excel 2007版本相比，操作界面的变化并不大，因此对于已经习惯了Excel 2007操作界面的用户来说，在适应Excel 2010操作界面上应该不会有任何困难。
而对于刚刚从Excel 2003过渡到Excel 2007或者Excel 2010的用户来说，他们对Excel 2007的界面一时很难适应，需要花一些时间熟悉。不过一旦你适应了Excel 2007的界面，就再也不愿回到那个古老的Excel 2003的时代了。
微软为了帮助用户从Excel 2003快速地过渡到Excel 2010，专门提供了一个Flash互动版的Excel 2003到Excel 2010命令参考指南，让我们以交互的方式快速熟悉Excel 2010的界面环境。可以以关键字“Microsoft Excel 2010 菜单到功能区的交互式指南”在互联网上搜索到该指南。
Excel界面从传统的菜单变成了功能区模式，在很多方面确实给用户带来了方便，但是还会有一些问题会对用户造成困扰。
假如你在Excel中制作的一个图表，然后又在图表旁边的单元格里输入了一些说明性的文字，当你回过头来打算继续对图表进行美化时，你可能会感到非常诧异，怎么找不到对图表进行美化的那些按钮了！
答案其实很简单：你得先选中图表，让Excel知道你要对什么东西（或者叫做“对象”）进行操作！例如，当你想对图表进行操作时，你必须首先选中图表！当鼠标选中图表时，你会发现，在Excel的功能区的最右边多出来了三个新增的功能区标签（图表工具标签），关于图表操作的大部分控件就包含在这些功能区标签中。这种根据所选择Excel对象不同而自动出现相应菜单的技术，就是所谓的Excel“智能感应”菜单。
自Excel 2007以后，Excel采用了大量的智能感应菜单，或者叫“上下文相关“菜单技术。那些只对某些Excel特定对象适用的操作，只有在你选中（或者激活）相应的对象时才会出现。比如，只有你选中了图表，才会出现与图表相关的菜单；只有你选中了数据透视表，才会出现与数据透视表相关的菜单，如图2-1所示。
图2-1
作为一般规律，当你需要对Excel进行某项操作时，大致可以按照以下逻辑去寻找相关的功能菜单。
（1）首先选中（激活）你要操作的对象，让Excel知道对什么对象进行操作。
（2）在右键快捷菜单里有你要找的功能选项吗？
（3）Excel功能区里有你要找的功能选项吗？
（4）Excel功能区里有没有新出现一个与选中对象相关的“智能感应”标签？
都没有？——那赶紧告诉微软，他们开发的软件不符合用户操作习惯！没准你还能因为这个建议得到他们的奖励呢！
2.2 辅助列
几何问题中有辅助线的概念，类似地，Excel里也经常用到“辅助列（或者辅助行）”的方法。适当地使用辅助列（行），可以使我们的工作化繁为简，事半功倍。
2.2.1 分离数据
如图2-2中A列所示数据，每一个产品型号的前半部分是产品颜色，后半部分是产品代码，我们的任务是将A列数据中的产品颜色部分单独分离出来。
这时很多人的大脑开始飞速运转，思考着用一个什么样的公式来完成这个任务。其实这个任务可以分步来完成：我们通过观察数据发现，字符串后半部分的产品代码全部以字母“U”开始，因此，可以先用FIND函数找到字母“U”的位置，然后用字符串函数LEFT取出“U”前面的所有字符即可。
为了使过程更清晰，我们使用辅助列完成。如图2-2所示，在B列中先用FIND函数找到字母U的位置（位置是一个整数）；然后在C列中用字符串函数LEFT取出字母“U”前面的所有字符。
图2-2
使用辅助列，避免了使用复杂的函数嵌套，使公式编写过程变得简单且不容易出错。
2.2.2 每隔一行插入一个空行
在工作表中经常需要每隔一行插入一个空行，如果手动插入空行往往费时费力，我们可以巧妙地借助辅助列来完成这个任务。
如图2-3所示，我们需要在每一行数据下方增加一个空白行。具体操作方法如下所示。
图2-3
（1）先增加一列辅助列，在辅助列中，所有非空的行顺序填写奇数，填写方法如下，在D2，D3单元格分别填写数字1和3，然后选中这两个单元格，将鼠标悬停在D3单元格右下角，直到光标变成小十字，然后双击鼠标，Excel会自动填充其余的奇数。
（2）在D11和D12单元格分别填写2和4，然后选中这两个单元格，将鼠标悬停在D12单元格的右下角，直到光标变成小十字形状，然后向下拖动鼠标，Excel会自动按顺序填充其余的偶数，如图2-3所示。
（3）把鼠标光标置于D列中任意一个单元格，点单击鼠标右键，在弹出的快捷菜单中选择“排序→升序”命令，即可实现每隔一行插入一个空行的效果。任务完成后，删除辅助列，如图2-4所示。
图2-4
利用上面的方法，可以每隔一行插入n行空行。我们要做的只是调整好辅助列的数字排列，让空白行在排序时插入到非空白行的中间就可以了。
2.2.3 随时恢复到排序前的状态
我们在对数据进行分析时，常常需要按照不同列的内容进行排序以检查数据。多次排序操作以后，我们希望能够随时、快速地恢复到排序之前的原始状态，这也可以借助辅助列来完成。
具体方法是：在数据分析之前，我们需要在数据的最右侧添加一列整数序号，这样在任何时候，只要对辅助列升序排序，数据就会恢复到最初状态了，如图2-5所示。
图2-5
加上这列辅助列后，现在就可以对数据放心地进行各种排序操作了。现在，我们按照B列排序后，排序后效果，如图2-6所示。
图2-6
如果想恢复到排序前的状态，我们只需把辅助列升序排列即可，一步即可完成，如图2-7所示。
图2-7
2.3 文本和数字
很多人有这样的疑惑：“明明看起来是数字，为什么SUM函数不能正确地求和呢？而有些看似文本的内容却能够正常地参与Excel计算！”（这里需要提醒一下：SUM函数求和计算时会忽略文本类型的数据。）
对于这些疑问，我要告诉大家，我们在Excel单元格里用肉眼看到的东西只是现象，我们还需要透过现象看本质。
我们可以简单地认为Excel单元格中只存储两种类型的数据（注意：“数据”和 “数字”是两个不同概念）：
（1）能够参与数学计算的数据——数字（真正的日期和时间本质上也是数字）；
（2）不能参与数学计算的数据——文本。
Excel中的数字就像七十二变的孙悟空一样，能够以各种“外在的形式”在单元格中显示，比如，如图2-8所示的B列数据，数字“100”虽然被设置成了不同格式，但其本质没有改变，它们还是数字，还可以参与相关的计算！
之所以说图2-8中B列中的数字本质没有变，因为我们可以通过用鼠标选中一个单元格，观察该单元格中的内容在公式栏里的显示形式来证明。在图2-8中选中B5单元格，可以看到，虽然B5在单元格中显示为“壹佰”，但是在公式栏里，所对应的内容为数字100，这就是它的本质。
图2-8
如何让Excel中的数字七十二变，让它们在单元格中显示为不同的“外在形式”呢？具体操作如下。
选中要设置格式的单元格区域，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”命令，此时弹出“设置单元格格式”对话框，在这里可以根据需要把单元格中的内容设置成不同的“外在”形式。
在默认情况下，无论把Excel工作表中的数字设置成了什么格式，数字总是右对齐的,而文本总是左对齐的。当我们用Excel处理从其他系统中导入Excel的数据时，这个规律能够帮助我们快速判断导入的数据是否能够直接参与数学运算。
无论单元格中的数字被设置成了什么格式，只需一招即可使其现出原形：选中数字所在的单元格区域，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”命令，在“设置单元格格式”对话框中选择“自定义”分类中心的“通用移式”类型，设置完成后，那些变化了的数字就会被立即打回原形（这个功能对应的快捷键是“Ctrl＋Shift＋~”）。
现在我们已经知道，“伪装”成文本的数字并不影响其参与数字计算，因为它们本质上还是数字，而且我们很容易把它们打回原形。因此，了解了其本质后，伪装成文本的数字并不会给我们的工作造成多少麻烦；而我们工作中经常遇到的情况往往是另一个问题：很多看起来像数字的数据却无法参与计算！比如SUM()、AVERAGE()等汇总函数在计算时会忽略文本类型的数字，因而得到我们意想不到的错误结果。
之所以这些“数字”不能参与计算，是因为这些看起来像数字的内容其实是以文本的形式存储在Excel单元格中的，其本质是“文本”，只是看起来像是数字。
比如，我们可能需要从其他系统中复制或导入数据到Excel，有时会发现，复制或导入到Excel中的“看起来”是数字的数据不能参加数学计算，怎么办？我曾经亲眼看见一位小女孩把工作表里的所有数字又重新输入了一遍，我打心底佩服她的仔细和耐心！
其实，我们可以通过对其做一个不改变其代表的数值大小的数学运算，把它们转化成真正的数字。
在图2-9中，由于A列数据是以文本形式存储的数字（默认是左对齐的，从这一点上也可以看得出来异常），在用SUM函数汇总时得到的结果是0，为了将A列转换成真正的数字，可以把A列的内容乘以一个计算后不改变其代表的数值大小的1（或者加上0），这样，转化成真正数字后就可以参加正常的汇总计算了。
图2-9
然而，在实际中遇到的情况往往没有那么简单。如图2-10所示是从公司的业务数据库系统中导入到Excel的数据，通过试验我们发现将“数量”一列通过乘以1或加上0的方法也不能把数据转化成能够参与计算的数字！
图2-10
原来，在“数量”一列中的每个数字后面都有一个多余的空白字符（并不是空格，而是某种不可见的字符），必须先把这个不可见的字符去掉，然后再乘以1才能把它们转化成真正的数字。
完成这个任务的一种比较通用的方法是，先从这列数据中复制一个引起麻烦的那个不可见字符，然后用Excel中的“查找和替换”功能把不可见字符替换成长度为0的字符串，再乘以1或加上0。或者，用SUBSTITUTE函数把复制出来的不可见字符替换成长度为0的字符串然后再乘以1或加上0。
最后，我们再说一个“典型”的常见问题：关于身份证号码的处理。在一般情况下，由于Excel单元格所能容纳的数字精度的限制，在Excel输入整数时，超过15位以后的数字就会变成0，而我们的第二代身份证号有18位，如果直接输入到Excel中，最后3位数字信息就会丢失。
如果明确身份证号码只是识别每一个人的代码而已，并不会用来参与加减乘除等有关数字的运算，那么就可以把它以文本的方式存储在Excel里，从而避免由于Excel数字精度限制引起的问题。具体方法是：在输入身份证号码之前，先把可能用到的Excel单元格区域设置成文本格式，然后再输入身份证号码即可。
2.4 日期和时间
在Excel管理应用实践中，日期和时间恐怕是最让一般Excel用户感到困惑的数据类型了。看起来明明是日期和时间的数据，当作为YEAR、MONTH、HOUR等日期和时间相关函数的参数时，却无法得到正确的计算结果；明明是日期和时间的数据，当被公式引用到工作表的其他位置时，得到的却可能是一堆普通的数字！还有，为什么真正的日期和时间可以与代表天数的数字进行简单的加减运算？如何快速地把文本形式的日期和时间转换成真正的日期和时间？通过本节的学习，你将不会再受到这类问题的折磨，掌握日期和时间的本质，一切将变得简单！
2.4.1 日期和时间的本质
我最近才发现，我们小区新来的保安竟然是一个哲学家，他在小区入口拦住我问了三个问题：“你是谁，你从那里来，你要到哪里去”？我一下子蒙住了，不知该如何做答，活了这么多年，我还真没有认真思考过这个问题，偶尔想到的只是：“到现在为止，我到底活了多少天？”如果没有Excel的存在，这个问题还真是一个问题！
有了Excel后解决这个问题就会变得非常容易！假如某人的生日是1981年9月2日，在单元格A1里输入1981-9-2，在B1单元格里输入=Today()，在C1单元格里输入=B1-A1，然后按下Enter键，C1单元格里立即得到这个人来到这个世界的天数，如图2-11所示。
图2-11
现在回到关于Excel中日期和时间的问题，为什么两个时期相减就能得到它们相隔天数呢？原来Excel在本质上将日期和时间的存储为一个数字，默认情况下，Excel把1900-1-1 0:00:00存储为1，把1900-1-1 0:00:00以后的每一个时刻存储为该时刻与1900-1-1 0:00:00这个时刻的差值（以天为单位）。
不信？请在A列单元格中输入“1900-1-1 0:00:00”，单击鼠标右键，在弹出的快捷菜单里选择“设置单元格格式”命令，最后在弹出的“设置单元格格式”对话框中，把单元格格式设置为“数值”。你会发现，当把日期设置成数值格式后，单元格的内容变成了1。
由此看来Excel中真正的日期和时间本质上是以数值的形式存储在单元格里的，不管你把日期的格式设置成了什么样的“外在”显示形式。
如图2-12所示，在B列中的单元格中我们把日期和时间“2011-6-23 0:00”设置成了不同的外在显示形式。
图2-12
好了，既然在Excel中，日期和时间本质上是以天为单位的数值形式存储的，那么我们就可以直接对日期进行加减计算了。
假设A1单元格中内容为“2011-6-23”，那么再过100天是几月几日？在B1中输入“=A1+100”，结果是“2011-10-1”；那此日期前100天是几月几日？在B1中输入“=A1-100”，结果是“2011-3-15”。
而此日期前38小时又是什么时间呢？在B1输入公式“=A1-38/24”，结果是“2011-6-21 10:00”，因为38小时等于38/24天。
注意：在计算完成后，还需要把A1:B1单元格设置成能够显示日期和时间的格式，如图2-13所示。
图2-13
2.4.2 真正的日期时间
工作中经常遇到这种情况：从公司业务数据库系统导出的数据看起来是日期时间，却不能在Excel函数公式中参与时间和日期相关的计算！
那是因为虽然单元格中的内容看起来像日期和时间，但实际上，它们在单元格中却是以文本的形式存储的（在默认情况下，真正的日期和时间应该是靠右对齐的，因为它们本质上是数字）。
判断一串字符是否为真正的日期和时间的方法是：选中这些单元格，看一看这些看似日期时间的内容是否能设置格式为“数值”格式，如果可以，那么这些内容就是真正的日期和时间，能够参与和日期时间相关的计算；否则，这些单元格内容的真正存储形式是文本，无法直接参与和日期时间相关的计算。
如图2-14所示，“2011.6.23”在Excel中不是以日期的形式存储的，因为它不能设置格式为数值，无法参与和日期有关的计算！
图2-14
那么如何把这些内容转换成真正的日期呢？下面介绍的两种具体操作方法。
方法1：公式法
以图2-15中A列所示的“以文本形式存储的”日期数据为例，下面讲解如何把它们转化成能够参与日期相关计算的真正的日期。
图2-15
使用公式法转换日期，我们必须首先了解，在当前的Excel环境中，真正日期的年、月、日之间是用什么符号作为分隔符的。假如在我们当前使用的Excel中，日期格式的年、月、日之间是用“-”符号分隔的。则我们可以用以下函数和公式把A列数据转化成真正日期：“=SUBSTITUTE(A1,".","-")*1”。其中函数SUBSTITUTE(A1,".","-")的含义是：用“-”字符替换文本中的“.”字符，把类似“2012.5.23”的数据变成了Excel的默认的日期形式“2012-5-23”；但需要注意，处理到这一步，数据的本质上还是文本，所以我们还需要在函数后面乘以1，把它转化成真正的代表日期的数字。
需要提醒的是，通过以上方式转化成的日期，有可能是以数字形式显示的，因此，作为最后一步，我们需要单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”命令，把数字设置为日期格式。
方法2：使用“数据→数据工具→分列”命令
如果我们遇到的问题更加复杂，如图2-16所示：Excel导入的外部数据中的年、月、日之间的分隔符有多种形式，并且月份和日期有时是两位数字，有时是一位数字，显然，这种情况用公式处理起来比较复杂。但通过仔细观察发现，不管分隔符是什么，所有日期都是按年、月、日的顺序排列的！在这种情况下，有一个处理该类问题的通用方法：“文本分列”法！
在图2-16中，A列中大部分内容是以文本类型存储的日期数据，其中还混杂着一些真正的日期（默认靠右对齐的数据）。下面用“文本分列法”把这些数据批量地转化为真正的日期。
图2-16
首先选中A列，然后选择“数据→数据工具→分列”命令。在弹出的“文本分列向导——第1步，共3步”对话框中的“请选择最合适的文件类型：”处选择“分隔符号”单选框，然后单击“下一步”按钮，如图2-17所示。
图2-17
在“文本分列向导——第2步，共3步”对话框中，取消勾选所有分隔符号复选框，然后单击“下一步”按钮，如图2-18所示。
图2-18
在“文本分列向导——第3步，共3步”对话框中的“列数据格式”选项中，选择“日期”单选框，并在日期选项后面的下拉列表中选择要处理数据的年、月、日的排列方式（Y代表年，M代表月，D代表日），然后单击“完成”按钮，如图2-19所示。
图2-19
此时我们发现，原始数据已经按照我们期望转化成了真正的日期，从形式上看也已经全部变成了右对齐排列，如图2-20所示。
图2-20
从这个过程我们可以看到，Excel真的很聪明，只要告诉它“原始数据代表的是日期”和“年、月、日的排列顺序”，那么无论年、月、日之间用什么分隔符分开，Excel都能将其转化成真正的日期。现在我们就可以进行与日期相关的计算了。
2.4.3 DATEDIF函数
谈到日期，就不能不提到在Excel中的一个隐藏的函数DATEDIF。DATEDIF函数用来计算两个日期之间的间隔的年、月、天的数量。比如某个人是在1971年9月2日出生的，那么到2012年1月1日，他一共活了多少天，多少个月，多少年呢？用DATEDIF函数可以轻松解决这个问题，如图2-21所示。
图2-21
DATEDIF函数的用法是：
DATEDIF（开始日期，结束日期，日期间隔单位）
其中“开始日期”和“结束日期”必须是Excel承认的真正的日期格式；“日期间隔单位”可以为：“Y”代表两个日期相隔的整年数；“M”代表两个日期相隔的整月数；“D”代表两个日期相隔的天数。
注意：“结束日期”必须晚于“开始日期”，否则函数会报错，这大概和在英语里，描述时间开始和结束时习惯用“From…To…”有关吧。
非常令人奇怪的是，这么有用的函数在Excel帮助和Excel插入函数对话框里竟然找不到！至于Excel为什么把这么有用的函数隐藏起来，也许微软Excel项目开发组一定有自己的“难言之隐”吧！
2.5 引用！引用！
Excel中的相对引用、绝对引用和混合引用是Excel中最终要的概念，必须要透彻地理解。
让我们先从一个例子说起，如图2-22所示的一个Excel表格，我们需要在D4:D13单元格中计算出每种产品的销售金额，即销售数量×单价。我们需要这样做：首先在D4单元格中输入公式“=B4*C4”，得到第一个产品“A”的销售金额（D4单元格中的数值）。
图2-22
计算完D4单元格中A产品的销售金额后，其他单元格中的公式怎么输入呢？当然不需要在D列的其他单元格中分别输入=B5C5、=B6C6……那样太低估了Excel的能力！
我们只需把光标移动到D4单元格的右下角，直到光标变成黑色的十字形状，向下拖动鼠标，一直拖动鼠标到D13单元格（或干脆在鼠标光标变成黑色十字形状时双击鼠标），Excel会自动把D4单元格的公式复制到D5:D13单元格中。
且慢！Excel真的把D4单元格中的公式“原封不动”地复制到了D5:D13单元格中了吗？
Excel并非把D4单元格中的公式“原封不动”复制到D5:D13单元格中，而是把D4单元格中公式所确定的“计算逻辑”复制到了D5:D13。我们发现，D5单元格中的公式为“=B5C5”；D6单元格中的公式为“=B6C6”；D7单元格中的公式为“=B7*C7”……
这个公式所确定的“计算逻辑”是：D4:D13每个单元格中的公式都是与其所处单元格同一行左侧的两个单元格的乘积，而这正是我们所需要的效果。
如果我们想查看一下工作表中公式而不是计算出来的数值，可以选择“公式→公式审核→显示公式”命令，把工作表设置成“显示公式”模式（对应的快捷键是“Ctrl＋` ”），设置完成后的显示效果如图2-23所示。
图2-23
Excel能够自动对公式进行调整，这个能力看起来够酷！但是，我们不免会产生疑问：Excel是真的这么聪明，能够了解我们的“计算逻辑”；还是仅仅是巧合而已，这里面还存在我们尚未了解的规律？因此，我们必须做进一步的研究。
下面我们来看一看E4单元格里关于佣金数额的公式。在E4单元格中输入公式“=D4*B1”（B1单元格里存储的是提取佣金的比率），公式输入完成后，在E4单元格里可以看到佣金数额的计算结果（D4单元格中的数值），如图2-24所示。
图2-24
把光标移动到E4单元格的右下角，直到光标变成黑色的十字形状，向下拖动鼠标直到E13单元格，Excel会自动把E4单元格的公式复制到E5:E13单元格中。
和前面讲的“销售金额”公式情况一样，Excel把E4单元格中公式所决定的“计算逻辑”复制到了E5:E13单元格中。
这个公式所确定的“计算逻辑”解释如下：以公式所在的单元格为参考点，将向左移动3个单元格，向上移动3个单元格所对应的那个单元格中的数值，乘以公式单元格左边第一个单元格中的数值的乘积，如图2-25所示。
图2-25
现在的问题是：这个逻辑只对E4单元格适用，如果E5:E13单元格中的公式也使用这个逻辑的话，那就大错特错了！
看来Excel不能够猜出我们的心思。事实上，我们必须明确地告诉Excel：E4单元格中的公式在向下复制的时候，提取佣金的比例必须一直使用B1单元格中的数值！
为了告诉Excel在公式复制时的调整逻辑，我们必须把E4单元格中的公式修改成“=D4*$B$1”，然后把光标移动到E4单元格的右下角，直到光标变成黑色的十字形状，向下拖动鼠标直到E13单元格，如此这般，Excel会就可以把E4单元格中“公式所确定的计算逻辑”按照我们的期望正确地复制到E5:E13单元格中了。
为什么把E4单元格中的公式“=D4B1”修改成“=D4$B$1”就能在公式复制时正确地执行我们的“计算逻辑”呢？原来，修改后的公式“=D4*$B$1”中所引用的单元格地址$B$1中的行号（“B”）和列标（“1”）前的“$”符号的作用类似一颗钉子，把对B1单元格的引用位置“牢牢”地固定住，表示$B$1所引用的单元格地址在公式复制的时候不再进行自动调整，即：无论公式复制到哪里，永远引用B1单元格中的数值。
修改后的公式自动复制的效果如图2-26所示，E4单元格中的公式为：=D4$B$1，当E4单元格中的公式复制到E13单元格中时，公式调整为=D13$B$1，结果正确，如图2-26所示。
图2-26
现在，我们介绍一下Excel中单元格地址的几种引用形式区别。
我们把类似$B$1形式的单元格地址引用，即在单元格地址的“行号”和“列标”前面都有一个$符号的引用，称做绝对引用。如果公式中含有这种形式的地址引用，那么无论公式复制到哪里，这种引用形式的单元格地址都不会自动调整，就像被钉子固定住了一样。
我们把类似B1形式的单元格地址引用，称做相对引用。如果公式中含有这种形式的单元格地址引用，那么公式中的该类型的地址引用会随着公式的复制而自动调整并和使用它的公式一直保持固定的“相对位置”，像影子一样跟随着引用了它的公式，无论公式被复制到了工作表的哪个位置。
有的时候，我们需要在公式复制时只有“行号”或者“列标”之一保持不变，这时，只需要在希望保持不变的单元格地址的“行号”或者“列标”之前加上一个$符号即可，即类似B$1，$B1形式的引用。我们把类似B$1，$B1形式的引用称做混合引用。如果公式中含有这种形式的单元格地址引用，那么该引用地址只会在行或列的“一个方向上追随”引用了它的公式，而在另一个方向上不随着引用了它的公式位置的变动而变动。
Excel公式中单元格地址的引用类型分为三种：
● 类似$B$1形式的引用称做绝对引用；
● 类似B1形式的引用称做相对引用；
● 类似$B1或者B$1形式的引用称做混合引用。
如果我们把公式比喻为“母亲”，把公式中引用的单元格地址称为“孩子”，那么：
（1）如果母亲公式中引用的单元格地址为绝对引用（类似$B$1形式的引用）时，那么，当公式母亲被复制到了其他单元格，孩子就像被绑住了双腿，一直停留在原来的为位置，动弹不得，不会跟随公式母亲一起移动，我们可以称之为“坚定不移”。
（2）如果母亲公式中引用的单元格地址为相对引用（类似B1形式的引用），那么，我们把母亲公式复制到其他单元格时，孩子会像母亲的影子一样，跟随母亲一起移动并一直和母亲保持固定的“相对位置”，可以说是“如影相随”。
（3）最后一种情况，如果母亲公式中引用的单元格地址为混合引用（类似$B1或者B$1形式的引用），那么，当公式母亲复制到其他单元格时，孩子只能在一个方向上（横向或列向）跟随母亲移动，而另一个方向上一直停留在原来的位置，相当于只在一个方向上没有了自由，而在另一个方向上保持自由！我们可以称之为“半身不随”。
有趣的是：那个限制孩子的自由，能够让孩子老老实实听话的符号竟然是$（美元符号）！真不知道Excel开发者当初是怎么想的！
我们在设计Excel表格或者模型时，在开始设计公式的时候就要根据公式将来复制的方向（横向复制、纵向复制还是两者都有），精确地调整好公式中的单元格地址的引用形式，这可以为我们后续的工作带来很大便利，并且能够避免一些可能出现的错误。
Excel小技巧：
公式中单元格地址引用形式的快速切换方法：把光标放置在单元格地址中间，连续按F4键，单元格地址会在相对引用、绝对引用、混合引用之间快速切换，例如：B1→$B$1→B$1→$B1→B1。
2.6 识别活动单元格
当用鼠标选中Excel中的一个单元格区域时，不知道你注意到没有，在选中的单元格区域中（通常在选中区域的左上角）有一个与众不同的单元格，也就是那个“反白”显示的单元格，这个单元格叫做“活动单元格（Active Cell）”，即如图2-27中所示的C4单元格。
图2-27
活动单元格一定要在选中区域的左上角吗？答案是“不一定”。举例来说，如果我们以如下的方法选中一个单元格区域：在工作表中选中任意一个单元格，然后以该单元格为起点，向“左上方”扩展选中区域，那么活动单元格就会位于选中区域的右下角，如图2-28所示。
图2-28
事实上，Excel活动单元格可以在选中区域中的任何位置，操作方法如下。
选中一个单元格区域，然后按Ctrl键，保持Ctrl键按下的同时，用鼠标点击选中区域中的任意单元格，此时Excel活动单元格就能够切换到选中区域中的任意单元格，如图2-29所示。
图2-29
识别“选中区域中的活动单元格”的位置很重要！下面我们介绍一个与之相关的Excel技巧，帮助我们初步理解“识别活动单元格在选中区域中的位置”的重要性。
在工作表中，选中一个单元格区域，观察活动单元格所在位置，然后在公式栏中填写一个字符，保持光标在公式栏内，同时按Ctrl和Enter键，你会发现，活动单元格中的内容会瞬时填充到选中区域中的所有单元格！如果活动单元格中填写的是公式，那么所复制的公式当然会遵循单元格地址引用的变化规律。
为了更深入地理解“识别选中区域中的活动单元格”的重要性，下面再举一个例子。
Excel“条件格式”功能可以根据单元格中的内容动态地应用不同的格式，用以强调显示那些Excel中满足某种条件的单元格。
如图2-30所示，假设我们要逐月比较2010年和2011年两组数据，希望设置一个条件格式，如果某个月的2011年的数值“低于”2010年相同月份的数值则以橙色高亮显示，条件格式完成后的效果如图2-30所示。
图2-30
下面我们用两种方式完成这个任务，两种方式的主要区别是：一开始时，选中区域中的活动单元格的位置不同，因而造成我们使用的条件格式的判断公式也不相同，具体完成任务的过程如下。
1.第一种方式
（1）选中C2单元格，保持鼠标按下状态，向下拖动鼠标，选中单元格区域C2:C13。注意，用这种方式选择的单元格区域时，活动单元格为C2单元格。
（2）选择“开始→样式→条件格式→新建规则”命令，调出“新建格式规则”对话框，在该对话框上部选择“使用公式确定要设置格式的单元格”选项。
（3）在“为符合此公式的值设置格式”下方的文本框中，输入我们的条件格式判断公式。此时需要特别注意：因为这时我们“选中区域中的活动单元格”在C2单元格中，所以在文本框中输入的公式应该是针对活动单元格C2编写的，而仅就这个活动单元格C2而言，对应的条件格式判断公式为“=C2<B2”，我们在“为符合此公式的值设置格式”下方的文本框中输入该公式，如图2-31所示。
图2-31
（4）单击“新建格式规则”对话框中的“格式”按钮，在弹出“设置单元格式”对话框中，我们设置满足公式条件的单元格的背景为橙色，然后单击“确定”按钮回到“新建格式规则”对话框。最后单击“新建格式规则”对话框中下方的“确定”按钮，完成条件格式的设置。
在这里需要特别指出的是：在我们单击“确认”按钮时，Excel会把针对活动单元格的“条件格式判断逻辑”复制到选中区域中的所有单元格，并遵循我们前面讲到的公式的相对引用绝对引用原理，因此，我们必须在针对“活动单元格”设计公式时，根据公式的复制方向（横向、纵向或二者都有）提前设置好公式中所引用的单元格地址的引用形式。
2.第二种方式
在第二种方式中，由于我们是从数据区域的下方开始向上选取单元格区域，造成选中区域中的活动单元格的位置与第一种方式有所不同，因而我们编写的条件格式公式也与前面所讲的方式不一样。
（1）这一次，首先选中C13单元格，然后保持鼠标按下状态，向上移动光标，以这种方式选中单元格区域C2:C13，注意：用这种方式选择单元格区域时，活动单元格为C13单元格。
（2）选择“开始→样式→条件格式→新建规则”命令，调出“新建格式规则”对话框，在该对话框上半部分我们选择“使用公式确定要设置格式的单元格”选项。
（3）在“为符合此公式的值设置格式”处，输入我们的判断公式。此时需要特别注意的是：这一次选中区域中的活动单元格在C13中，在文本框中输入公式必须是针对该活动单元格编写的，因此，仅就这个活动单元格C13而言，对应的公式为“=C13<B13”，我们在“为符合此公式的值设置格式”下方的文本框中输入该公式，如图2-32所示。
图2-32
（4）单击“新建格式规则”对话框中的“格式”按钮，在弹出的“设置单元格式”对话框中，我们设置满足条件的单元格背景颜色为橙色，然后单击“确定”按钮回到“新建格式规则”对话框，最后单击“新建格式规则”对话框中下方的“确定”按钮，完成条件格式的设置。
这里需要再次强调的是：在我们单击最后的“确认”按钮时，Excel在背后偷偷做了什么呢？原来，Excel会把针对活动单元格的条件格式判断逻辑复制到选中区域中的所有单元格，并遵循我们前面讲到的相对引用和绝对引用原理，因此我们必须根据公式的复制方向（横向、纵向或二者都有）提前设置好公式中所引用的单元格地址的引用形式（相对引用，绝对引用和混合引用）。
在第二种方式中，如果使用我们在第一种方式中设计的公式，则不会达到期望的条件格式效果，这正说明了“识别选中区域中的活动单元格”的重要性。读者有兴趣的话可以试一试。
