我的书签
添加书签
移除书签第12章 其他Excel技术
Excel总是能够给我们带来惊喜和挑战：那些制作Excel超级公式的人真的很聪明吗？你知道Excel模拟运算表功能吗？你知道什么是数组公式吗？你用过Excel规划求解工具吗？你是否见过能与用户互动的Excel动态图表？你知道Excel中的“照相机”功能在什么时候派上用场吗？别急，本章将为你一一解答。
12.1 制作超级公式
在工作中，我们难免会遇到对数据进行多重判断的问题，这时我们会很自然地想到使用多重IF()函数嵌套。然而，对于没有经过逻辑训练的一般Excel用户来说，一两个IF()函数嵌套还能够对付，嵌套层数再多一点，我们会比Excel还糊涂。
多重IF()函数嵌套的优点是能够在一个单元格完成所有判断，缺点是不便于维护，如果可以，一般建议用VLOOKUP，CHOOSE等函数替代多重IF函数嵌套。
虽然我们不提倡使用嵌套层级过多的“超级公式”，但制作超级公式的技巧还是需要知道的，如果你能理解下面介绍的方法，制作超级公式不会凌乱了。
假设我们是某学校的老师，学生考试成绩出来以后，要按照如下规则为学生评级：
（1）成绩低于60分的，评级为“差”；
（2）成绩大于等于60分小于70分的，评级为“可”；
（3）成绩大于等于70分小于80分的评级为“中”；
（4）成绩大于等于80分小于90分的评级为“良”；
（5）成绩大于等于90分的，评级为“优”。
这类问题看起来似乎很复杂，但是如果我们掌握了解决该类问题的正确方法和步骤，一切都会变得简单。
多重IF()函数嵌套公式的编写就像剖洋葱，一次只处理一种情况，其他情况叫做“待处理”，也就是说需要另案处理。就这样，一层层地，把洋葱剖到不想剖了为止。然后再回过头来，把公式从里向外一步步地替换回去，就这样，超级公式就制作出来了。
参照图12-1，在制作超级公式之前，我们需要明确：我们是针对B2“单元格”编写公式，而不是针对B2单元格中的“数字“编写公式，因此，我们编写的公式会根据B2单元格中的分数的变化得到不同的判断结果。
图12-1
我们现在处理第一种情况，即成绩大于等于90分的情况，在C2 单元格中输入公式：
该公式的意思是，如果B2单元格中的数字大于等于90，则评级为“优”，那么其他情况呢？为了避免公式变得复杂，我们要另做处理，叫做“待处理”，这样我们就能够把复杂的问题变成简单的问题分步处理。
下面处理“待处理”的问题。在D3单元格中输入公式：
公式的含义是：如果B2单元格中的数字大于等于80，小于90，评级为“良”。AND函数表示AND函数括号里面的条件“同时满足”时，返回TRUE，这时IF函数返回第一个结果。AND函数里面的多个条件用逗号分开。
现在我们开始处理第三种情况，即如果B2单元格中的数字大于等于70，小于80的情况，其公式为：
以此类推，处理完所有情况。我们需要注意的是最后一个公式，即G6单元格中的公式：
它表示如果B2单元格中的公式小于60，则评级为“差”，如果还有其他情况存在，则表示碰到了我们没有预料到的情况（比如不小心在B2单元格中输入了文本数据），公式结果显示为“未定义”。
分步处理完所有情况后，我们就可以按照图中箭头所示自下向上逐步用当前公式替换上一个公式中的“待处理”部分（替换时不包括当前公式前面的等号），最终完成的“超级公式”如下：
最后，我们把B2单元格中的“超级公式”向下自动填充至单元格B11中，查看结果是否正确，如图12-2所示。
图12-2
现在我们了解了超级公式的设计原理，但是还是建议尽量把复杂的公式分步完成，尽量地使问题的求解过程明白易懂。我们经常在网上见到这种情况，本来增加一两个Excel辅助列就能简单解决的问题，但一些热心的网友给求助者制作一个“超级公式”，极力追求用一个公式解决问题，很有一种智力竞赛的感觉。我想，如果求助者不能理解你的公式，那么很可能他也不敢用你的公式。
12.2 规划求解加载宏
你可能听说过著名的“鸡兔同笼”问题：已知有鸡和兔共有15只，共有40只脚，问鸡和兔各有几只？
这里有一个有趣的算法：假设鸡和兔都经过特殊培训，我们大喝一声，它们都抬起一只脚，现在挨着地的脚的数量是：40-15=25。我们再大喝一声，它们又都抬起一只脚，现在挨着地的脚的数量是：25-15=10，这时鸡都是两脚朝天地躺地上了，兔子还两只脚立着。所以，兔子有10÷2=5只，鸡有15-5=10只。
不过，现实生活中遇到的问题往往要比这个复杂得多，而且绝大多数实际问题都不太可能用这种取巧的方法解答的。幸好我们有Excel，Excel中的“规划求解”是解决这类问题的简便易用的工具之一。
在默认情况下，我们在Excel界面中是找不到规划求解工具的。在Excel 2010中，选择“文件→选项→加载项”命令，在“查看和管理Microsoft Office加载项”对话框最下方的“管理”下拉框中选择“Excel加载项”，然后单击对话框右侧的“转到”按钮，弹出“加载宏”对话框，如图12-3所示。
图12-3
在弹出的“加载宏”对话框中的“可用加载宏”选项列表中，勾选“规划求解加载项”复选框，然后单击“确定”按钮添加加载项，同时关闭对话框，完成Excel规划求解加载项的加载。
现在Excel规划求解加载项已经加载到Excel中了，选择功能区中的“数据”功能标签，你会发现其中多了一个叫做“分析”的功能组。“规划求解”按钮就在那里。
Excel中的规划求解工具能够帮助我们解决很多复杂的问题，下面让我们结合一个有趣的案例来了解Excel规划求解工具的强大能力。
假如我们有1000元钱，要买5种商品（每种商品至少要买一件或以上），每种商品的价格如图12-4所示，如果希望正好把1000元钱用掉，每种商品应该各买多少件？
图12-4
作为使用Excel规划求解工具第一步，也是最重要的一步，我们必须把问题用Excel表达出来，变成Excel规划求解工具能够理解的模型。
如图12-4所示，我们把C5:C9单元格命名为“可变单元格”，把E5单元格命名为“目标单元格”。在目标单元格中输入公式：“=SUMPRODUCT(B5:B9*C5:C9)”，用来计算当前商品的总价。该公式也可以写成“=SUMPRODUCT(B5:B9,C5:C9)”。
建立规划求解模型的关键在于，首先必须使用Excel函数和公式建立起“可变单元格”和“目标单元格”之间的联系，也就是所谓的建立规划求解模型。根据实际问题建立起“可变单元格”和“目标单元格”之间的联系后，就可以试着通过改变可变单元格中的每个数值，可以观察目标单元格的计算结果，看一看什么时候能正好凑到1000元钱。
如果问题比较复杂，人工试探凑数的过程是相当漫长、几乎不能完成的，不过幸好有计算机，幸好有Excel，幸好Excel中有规划求解工具，Excel中的规划求解工具能够帮助我们自动完成这个任务。
单击“数据→分析→规划求解”命令，弹出“规划求解参数”对话框，图12-5所示。
图12-5
这个对话框看起来很复杂，其实我们要做的只有三方面内容：
（1）设置目标；
（2）设置可变单元格；
（3）设置需要遵守的约束条件。
下面具体分析每个步骤的具体操作。
（1）设置目标参数：即图12-6中的第1部分，在这里要告诉Excel规划求解工具，那个已经与“可变单元格”建立了联系的“目标单元格”的位置，以及期望目标单元格达到的目标值，目标值可以是求解问题可能实现的最大值、最小值和我们所指定的特定值，在本例中我们使用指定的值1000。
图12-6
（2）设置可变单元格：即图12-6中的第2部分，在这里要告诉Excel规划求解工具哪些是驱动目标单元格变化的可变单元格。
（3）设置需要遵守的约束条件：即图12-6中的第3部分，在很多问题中，可变单元格的变化不是天马行空，不受任何约束的。比如在本案例中，可变单元格必须是整数（商品不能买半个）和每件商品的数量必须大于一件。在该对话框的“遵守约束”中可以为Excel规划求解工具指定约束条件。
利用Excel规划求解工具解决管理实践问题的关键是“用Excel公式和函数建立可变单元格和目标单元格之间的关系”，用专业一点的术语来说就是建立可变量和目标量之间Excel模型。这样Excel就可以利用计算机超快的计算能力，在遵守约束条件的前提下，按照某种算法，在不断改变可变单元格中数值的同时观察比较目标单元格中的数值，直到找到一组符合条件的答案！
规划求解的求解方法看起来很笨，但是计算机的运算速度弥补了它的“笨”！其实，在细节上，Excel规划求解也并非傻傻地寻找答案，其中也应用了一些先进算法，不过，具体细节不是我们普通Excel用户关注的范围，为了便于理解，我们暂且就认为它“傻”吧！
了解了Excel规划求解工具的基本工作原理之后，现在继续完成我们的规划求解过程。
在“规划求解参数”对话框中的“设置目标”文本框处，选取目标单元格E5，在“目标值”文本框中输入目标值1000；在“通过更改可变单元格”文本框中，选取我们的可变单元格区域C5:C9，设置结果如图12-7所示。
图12-7
现在开始在“遵守约束”中设置我们的约束条件，单击“遵守约束”列表框右侧的“添加”按钮，弹出“添加约束”对话框。
在这里我们要告诉规划求解工具在搜索答案时必须遵守的约束条件：每个可变单元格必须大于等于1（每件商品至少买一件），如图12-8所示。然后单击“确定”按钮，回到“规划求解参数”对话框。
图12-8
这时我们发现，“遵守约束”列表框中出现了刚刚添加的约束条件。下面继续添加另外一个约束条件，单击“遵守约束”列表框右侧的“添加”按钮，弹出“添加约束”对话框，如图12-9所示。
图12-9
这一次，我们要在“添加约束”对话框中告诉Excel规划求解工具，可变单元格必须是整数，我们做如图12-10所示设定，这里的“Int”是英文Integer的缩写，意思是“整数”。然后单击“确定”按钮回到“规划求解参数”对话框。
图12-10
这时我们发现，“遵守约束”列表框中出现了我们刚刚添加的两个约束条件，如图12-11所示。
单击“规划求解参数”对话框下方的“求解”按钮，开始Excel规划求解运算，Excel弹出“规划求解结果”对话框，显示“规划求解找到一个在误差范围内的整数解。可满足所有约束”，这表示规划求解工具已经找到了一个问题的答案。我们在该消息的下方选择“保留规划求解的解”单选框，然后单击“确定”按钮关闭该对话框，这时找到的一组解，如图12-11所示。
图12-11
从上面的问题的求解过程我们可以感觉到，Excel规划求解工具相当强大。它能够把复杂的问题简化成Excel能够解决的问题，通过建立相对简单的规划求解模型，快速找到问题的答案。
现在我们改变问题的要求（即改变问题的约束条件），看一看Excel规划求解工具有没有能力应付。新的问题是：每种东西必须买3件以上，怎么才能正好把1000元钱一分不剩正好花掉呢？
很简单，我们只需把问题的第二个约束条件变成“可变单元格>=3”，求解即可，如图12-12所示。
图12-12
下面是我们得到的改变了约束条件的新答案，选择“保留规划求解的解”单选框，然后单击“确定”按钮把规划求解的答案保存起来，如图12-13所示。
图12-13
直觉告诉我们，对于这个花钱的问题，可能有多种答案，但遗憾的是，Excel规划求解工具每次只能找到问题的一组解决方案，如果我们尝试找出另外一组答案，可以通过修改Excel规划求解工具的约束条件，排除已经找到的那一个答案的方式来解决。
下面回顾一下规划求解的整个工作过程。
（1）建立Excel规划求解模型：把实际问题用Excel表达出来，确定可变单元格、目标单元格和约束条件所对应的单元格区域。
（2）用Excel公式和函数建立可变单元格和目标单元格之间的联系。
（3）设置规划求解工具对话框：即设置目标单元格、可变单元格和遵守的约束。
我们可以看到，Excel规划求解工具把复杂的问题的求解过程变得相当简单。使用Excel规划求解工具的核心是建立实际问题的Excel规划求解模型，即建立可变单元格和目标单元格之间的联系。如果模型建好了，问题就解决了一大半。
关于Excel规划求解工具的其他设置，这里不再介绍。读者有兴趣的话，可上网搜索相关帮助。
Excel规划求解虽然有很多局限，但是其“易获得性”和“易用性”使得我们能够随时用它解决一些管理问题，只要告诉Excel规划求解工具解决问题的思路，把复杂的求解过程交给Excel规划求解工具处理就可以了！
12.3 数组公式
有人说，熟练掌握Excel公式和函数的高级应用，可以锻炼头脑。我认为这有点儿道理，熟练使用Excel函数和公式，特别是数组公式，它对于锻炼头脑，防止患上老年痴呆更有效果！
言归正传，下面说一说Excel数组公式。你也许见过这样一种Excel公式，这种公式最显著的特点是最外面包着一对大括号“{}”。
在图12-14中，C6单元格中的公式“{=SUM(B2:B4*C2:C4)}”就是数组公式。它所表示的计算逻辑是：将单元格区域B2:B4和C2:C4里横向对应的单元格中的数值分别相乘，然后用SUM函数汇总成一个数值。
图12-14
对于一般的Excel用户来说，数组公式虽然编写和理解起来可能有一些难度，但鉴于其强大的功能，还是很值得我们花一点精力了解和学习的。
Excel数组公式能够把通常需要多个步骤才能完成的计算浓缩成一个公式完成，并且能够防止用户因不小心而修改，我们在构建比较复杂的Excel表格和管理模型时可能会用到数组公式。
如图12-14所示的例子，{=SUM(B2:B4C2:C4)}用普通公式替代就是“=B2C2+B3 C3+B4C4”，它们的作用相同，都是用来计算总销售额，但数组公式看起来要简洁一些，特别是当公式中有很多参数的时候。
数组公式两端的大括号并不是手动输入进去的，而是在C6单元格中输入公式=SUM(A2:A4*B2:B4)后，把鼠标置于公式栏中，同时按Ctrl＋Shift＋Enter组合键后，Excel会自动在公式两边加上一对大括号把刚刚输入的公式包裹起来；这表示该公式不再是普通的公式，而是数组公式，会按照数组公式的逻辑进行计算。
如果不是用上述的方法输入数组公式，而是手动在公式两边加上一对“{}”，Excel则不会把输入的公式转变为数组公式，只能把你的公式转换为一般文本。所以，一定要记住，输入数组公式一定要用前面所描述的正确的方法。
因为输入数组公式需要使用Ctrl、Shift、Enter三个键，我们把数组公式叫做“CSE公式”，我个人比较喜欢这种称呼，因为它让我们记住了数组公式的输入方法。
数组公式里面的“数组”，可以理解为Excel工作表中的一个矩形的单元格区域。比如图12-14中的数组公式“{=SUM(B2:B4*C2:C4)}”里面的B2:B4和C2:C4就可以理解为两个“3行1列”的数组。
数组公式计算的中间结果通常也是数组，但是在多情况下，我们往往把数组公式最终的计算结果用某个汇总函数（比如SUM，AVERAGE，COUNT等函数）汇总成一个数值。
如果我们不打算把数组公式的最终计算结果汇总成单个数值以便放在一个单元格中，则需要预先判断数组公式的运算结果的行数和列数，然后在工作表中按照数组公式运算结果的行数和列数选取同样大小的单元格区域，最后在公式栏中输入相应的数组公式。
理解数组公式最好的方法是了解它的计算过程，Excel中的“公式求值”工具能够帮助我们直观地理解和学习数组公式！Excel中的“公式求值”工具是一个非常有用的学习和分析Excel公式的工具，我们要习惯使用这个工具调试、分析和理解数组公式。
下面我们用“公式求值”工具分析一下C6单元格中的数组公式的计算过程：把鼠标光标放置在C6单元格，选择“公式→公式审核→公式求值”命令，在弹出的对话框中连续单击“求值”按钮，这样我们就能够很直观地观察到数组公式的整个计算过程。
对于如图12-14所示的这个例子，当我们第二次单击“公式求值”对话框中的“求值”按钮时，“求值”下方的文本框中显示了数组公式计算的中间结果，显示的是SUM函数公式中的两个区域中对应单元格数值的乘积，继续单击“求值”按钮完成计算，如图12-15所示。
当我们打算使用数组作为公式或函数的参数时之一时，通常情况下，数组公式中的所有的参数可能具有相同的行数和列数（比如前面的案例，两个参数都是3行1列的数组）。
如果数组公式中的数组参数具有不同的行数或者列数，Excel会查看数组公式中的所有参数，把每一个数组参数的行数扩展到所有数组参数中具有最大行的那个数组的行数；把每一个数组参数的列数扩展到所有数组参数中具有最大列的那个数组的列数。即使不是数组的参数也要进行扩展转换操作，但这些扩展转换操作是在Excel内部完成的，我们完全感觉不到它们的存在。下面具体介绍数组参数的自动扩展转换方式。
在图12-15中，我们要计算3个单元格区域对应元素之和，三个单元格区域分别是B2:C5，J2:M3和R2，其中R2区域只有一个单元格。根据前面描述的数组公式的数组参数的扩展规律，可以预先判断：计算3个单元格区域对应元素之和的公式“{=B2:C5+J2:M3+R2}”的计算结果为4行4列的数组。因此，先选择一个4行4列的单元格区域用来容纳该数组公式的计算结果。选中Z2:AC5单元格区域，然后在公式栏中输入“=B2:C5+J2:M3+R2”，保持鼠标光标在公式栏的状态下，按下Ctrl＋Shift+Enter组合键，完成数组公式“{=B2:C5+J2:M3+R2}”的输入。数组公式的计算结果如图12-15所示。
图12-15
在图12-15所示的表格的下半部分是数组公式的扩展操作的示意，在数组参数的扩展过程中，没有数值的部分Excel会当做#N/A处理（即相当于Excel函数“=NA()”返回的结果）。
参照图12-15，关于数组公式中的数组参数在运算过程中的自动扩展方式总结如下。
数组与单个单元格运算：Excel数组公式会把单个单元格扩展为与公式中数组参数的行列相同的数组后再进行运算，单个单元格扩展后形成的数组中的每个单元格中的数值与原单元格中的数值相同。
在数组公式中，如果参与运算的数组的行数和列数各不相同，则Excel数组公式会把每一个数组参数的行数扩展到所有数组参数中具有最大行的那个数组的行数；把每一个数组参数的列数扩展到所有数组参数中具有最大列的那个数组的列数。扩展后的数组中没有数据的部分为#N/A（表示什么都没有，即函数NA()返回的结果）。
在图12-16中，E2单元格中的数组公式为{=SUM(A2:A4*C2)}，第一个参数是3行1列的数组，第二个参数是单个数字，Excel数组公式在计算时，把1个单元格中的数值（第二个参数）也扩展成了3行1列的数组{4;4;4}，然后再进行运算，从而得到正确的结果。我们可以通过前面介绍的“公式求值工具”来验证这一点。
图12-16
前面已经提过，如果数组公式运算的结果是一个数组，那么，我们需要预先判断运算结果数组的行数和列数，然后在工作表中选取相应大小的单元格区域，在公式栏中输入相应的数组公式。如果选取的范围比数组公式的计算结果小，那么只能显示数组公式计算结果的部分内容。相反，如果你输入数组公式时选取的范围比数组公式计算结果大，那么数组公式计算结果以外的地方会被填充为“#NA”。
Excel数组公式的计算实际上是在计算机内存中形成了一个个的看不见的单元格区域（内存数组），理解了数组公式的工作原理，我们就可以尝试编写一些有用的数组公式来帮助我们工作。
举一个例子：计算1+2+3+…+100的和，可用如下数组公式完成：{=SUM(ROW(1:100))}。具体操作如下。
（1）选中A1单元格。
（2）在公式栏中输入=SUM(ROW(1:100))。
（3）保持鼠标光标在公式栏内，按下Ctrl＋Shift＋Enter组合键，如图12-17所示。
图12-17
如果你还不理解这个数组公式的计算逻辑，别忘了用Excel的“公式求值”工具分析一下！
怎么样，Excel很神奇吧！Excel数组公式为我们开辟了Excel公式应用的一个全新的领域！但是，我要告诉大家，如果你认为数组公式理解和编写起来实在太难，也没关系，因为在Excel中，Excel数组公式能做到的事情，用普通公式一步步地也能完成，只是稍微烦琐一点而已。
12.4 模拟运算表
模拟运算表（Data Table），虽然这个工具的名字很令人费解，但是这个工具确实挺有用的。假设老板让我们制作一张表格：公司有一些闲置资金，打算存到银行，下面计算一下以不同的金额，在银行存储不同的年限时，到期后能够取出多少现金，如图12-18所示。
图12-18
如果你以前没有听说过“模拟运算表(Data Table)”，你可能用公式把本金和存款年限的每一种组合都计算出来，然后再小心地把计算出来的结果一个一个地填入表格中。
如果你真的这样去做，那就太落伍了，而且容易出现错误！
如果使用Excel模拟运算表工具，我们只需要一次列出公式，告诉它我们的计算逻辑，Excel模拟运算表会自动帮助我们填写好表格。
下面介绍具体的操作步骤。
首先列出计算公式，为了方便说明问题，先给每个单元格命名一个有意义的名字。选中单元格区域B2:C5，然后选择“公式→定义的名称→根据所选内容创建”命令，如图12-19所示。
图12-19
在弹出的“以选定区域创建名称”对话框中，选择“最左列”复选框，这说明是用选中区域的最左边一列作为其右面每一行数据的名称，然后单击“确定”按钮。这样我们就为每个单元格定义了有意义的名称。以后在公式里可以用相应的名称代替单元格引用。每个名称和对应的单元格详细信息如图12-20所示。
图12-20
下面在C5单元格“到期金额”中输入公式“=本金*(1+年利)^存款年限”。公式中的“^”符号是Excel中的“乘方”符号。
到现在为止，事实上我们在单元格区域B2:C5中建立了一个小小的Excel模型，该模型说明了我们的存款、取款的计算逻辑。
如果我们现在还不知道有“模拟运算表”这个技术，我们在填写这个表格时可能使用的方法是：用表格中的第一行中的每个数值替代模型中的“本金”；用表格中的第一列中的每个数值替代模型中的“存款年限”，在对应的行和列的交叉点处的单元格中填写模型的计算结果“到期金额”。
其实Excel模拟运算表用的就是这个逻辑。选择模型下方表格的第一行和第一列的交叉点所在的单元格D8，输入公式“=到期金额”。这一步很重要，“到期金额”这个名称所对应的单元格中是一个公式，在D8单元格中引用该公式确立了模拟运算表与我们建立的模型之间的联系。
下面以单元格D8作为所选取区域的左上角，选择单元格区域D8:N23，然后在Excel 2010的功能区中选择“数据→模拟分析→模拟运算表”命令，此时弹出“模拟运算表”对话框，在“输入引用行的单元格(R)”中选择单元格$C$3，表示我们要用所选区域的第一行中的每一个数值替代单元格$C$3中的内容（本金）；在“输入引用列的单元格(C)”中选择单元格$C$4，表示我们要用所选区域的第一列中的每一个数值替代单元格$C$4中的内容（存款年限）。设置完成后单击“确定”按钮，如图12-21所示。
图12-21
至此，Excel瞬间完成了整个表格的填写，如图12-22所示。建立好模拟运算表后，除了可以修改模拟运算表的行标题和列标题中的数据外，还可以修改C2单元格中的“年利”数据，这时会立马得到更新了的运算结果。
图12-22
通过以上案例可以总结出在使用Excel模拟运算表工具时需要分以下几个步骤实现。
（1）建立双变量模型；
（2）建立模拟运算表表格；
（3）在模拟运算表左上角单元格中建立对双变量模型计算结果公式所在单元格引用；
（4）应用模拟运算表。
12.5 动态图表
动态图表是一种具有和用户交互能力的图表。如图12-23所示是某公司各个销售部门在2010年的月度销售额统计。假设该公司共有5个销售部门，如果我们把这5个销售部门的数据全部显示在图表上，那将显得非常拥挤和凌乱。
图12-23
如果我们给用户提供一个选择，让他们能够自行选择哪些数据显示在图表中，进行比较，图表将会变得整洁和更有意义。
动态图表除了能够让图表变得简洁，更容易理解外，还能够节省空间，在有限的空间里展示更多的信息。
动态图表看起来很高级，是不是需要编写程序？不需要，只需掌握了基本的Excel控件和公式的用法，每个人都能制作出实用的动态图表。
动态图表的基本逻辑介绍如下。
（1）用Excel控件驱动单元格数值变化；
（2）用变化的单元格数值驱动公式重新计算，利用公式从源数据中抓取制作图表所需要的数据；
（3）基于动态抓取的数据制作图表。
这样，控件就能够控制图表动作了。
动态图表不是图表自己“主动”在动，而是被“制作图表时所基于的数据”驱动，基于动态数据所制作的图表就是动态图表。
下面我们就按着上面的逻辑思路制作一个“动态图表”。假如我们有如图12-24所示的数据。
图12-24
如果我们按通常的方式展示数据，效果可能如图12-25所示，非常凌乱，拥挤不堪！特别是当数据更多一些的时候，简直不堪入目！难道你会把这样的图表直接交给老板吗？绝对不能，如果给老板这样的图表，还不如直接交给他原始数据！
图12-25
下面开始优化我们的图表。
首先，复制一个原始数据的表格结构，注意只复制原始数据“表的结构”，不包含具体的数字，并在表格的最后增加一列，列的标题为“选择？”，我们将在这个新增加的列里控制是否抓取原始表格中的数据。
选中单元格区域C10:N14，注意，此时活动单元格为C10，在公式栏中针对活动单元格输入公式“=IF($O10,C3,NA())”，输入完毕后按下Ctrl＋Enter组合键，此时，活动单元格中的公式会填充到整个选中区域，并且公式会根据单元格引用地址的相对引用和绝对引用原理自动调整，如图12-26所示。
图12-26
公式“=IF($O10,C3,NA())”的含义是：查看工作表O列当前行中的数值（即新增的“选择？”列的数值），如果该单元格里面的数值为TRUE，则将原始数据中对应单元格的数值抓取到当前表格中来，否则返回NA()。
这里的NA()，是Excel中的一个函数，表示“什么都没有”，如果单元格中是“=NA()”时，基于该单元格制作的图表既不会把它当做0处理，也不会把它当做“空”来处理，这样会让基于这些单元格制作的图表显得非常整洁，如图12-27所示。
图12-27
下面我们做一个试验，看一看原始表格中的数据能否按照我们的期望抓取到新的表格中来。在“选择？”列中的第一个和第二个单元格中分别输入公式“=TRUE()”和“=FALSE()”，这两个函数的计算结果分别返回Excel的逻辑值TRUE和FALSE。
这时，我们发现“选择？”列为TRUE的单元格所对应的行已经成功地把原始表格中的数据抓取过来了，如图12-28所示。
图12-28
如果我们能用Excel中的“复选框”控件控制“选择？”列中的单元格，使其或者为TRUE或者为FALSE，这样我们不就达到动态抓取数据的目的了吗？
下面我们就来添加控件，选择“开发工具→控件→插入→表单控件→复选框”命令，在工作表中插入一个复选框。选中刚刚插入的复选框，再复制4个复选框，现在一共是5个复选框。更改复选框的标题分别为“市场1部”至“市场5部”。调整5个复选框位置，使之排列整齐，如图12-29所示。
图12-29
接着在按Ctrl键的同时，点选每一个复选框，然后在鼠标右键快捷菜单中选择“组合”命令，组合的目的是把5个复选框变成一个整体，便于调整布局。
用鼠标右键单击第一个标题为“市场1部”的复选框，在弹出的菜单中选择“设置对象格式”命令，弹出“设置对象格式”对话框。在该对话框中的“单元格链接”列表框中，选择与该控件建立联系的单元格。对于“市场1部”的复选框，与其建立联系的单元格是新表格中新增的“选择？”列中的O10。以此类推，分别设置各个控件所链接的单元格，如图12-30所示。
图12-30
下面我们再做一个试验，看一看我们插入的复选框控件是否能够成功地控制“选择？”列单元格中的内容。
先勾选所有复选框，然后取消勾选第2、第3、第5个复选框，我们看到“选择？”列中的各个单元格中的数值随着控件的勾选状态的变化在TRUE和FALSE之间切换（选中为TRUE，没选中为FALSE）。而TRUE和FALSE控制着新表格中的公式是否抓取原始数据表格中的数据，如图12-31所示。
图12-31
下面，我们基于新表格制作Excel图表。选中新表格中除“选择？”列之外的所有数据，选择“插入→图表→折线图”命令。基于选中区域制作的表格如图12-32所示，此时，改变控件状态已经能够控制图表变化了。实际上，动态图表的制作逻辑至此已经完成，下面对动态图表的外观做一下简单的调整。
图12-32
选中已经组合好的控件，单击鼠标右键，在弹出的快捷菜单中选择“置于顶层”命令，这样操作的目的是为了在将控件组移动至图表时，不会被图表遮盖住。
将控件组移动至图表上面合适的位置，同时选中图表和控件组，单击鼠标右键，在弹出的菜单中选择“组合”命令，将图表和控件组合在一起，这样移动图表的时候，控件会跟着一起移动。
调整图表的位置，使其遮盖住作为动态图表辅助功能的数据表，并隐藏辅助数据表中的“选择？”列。继续对动态图表做其他一些调整，并增加图表标题，最后，动态图表的效果如图12-33所示。
图12-33
大功告成，下面总结一下制作动态图表的基本思路。
（1）用Excel控件驱动单元格数值变化；
（2）用变化的单元格数值驱动公式重新计算，使公式从数据源中抓取图表所需要的数据；
（3）基于动态抓取的数据制作图表。
通过以上步骤，控件就能够控制图表动作了。
12.6 用Excel照相
很多人可能听说过Excel中的“照相机”功能，我觉得把Excel中的“照相机”功能称做“取景器”更合适一些，这是因为它能够把Excel中指定的单元格区域内的“风景”动态地反映到取景器的取景框里面，“取景器”是Excel中的一个神奇的功能。
下面就制作一个“取景器”，用它来获取任意单元格区域内“风景”的动态图像。在图12-34所示的工作表中，A1:B7单元格区域中有一个图片，这就是我们要取用的“风景”。
首先选中“任意一个”空白单元格，比如C1单元格，复制该空白单元格，然后单击“开始→剪贴板→粘贴”按钮下方的下拉箭头按钮，在展开的下拉列表中，选择“其他粘贴选项”中的“粘贴图片链接”选项，如图12-34所示。
图12-34
此时，工作表上出现了一个与被复制的单元格位置重叠、大小相同的Excel“图片链接”对象，如图12-35所示，这就是我们制作的“取景器”。调整“取景器”的大小和位置，保持“取景器”处于选中的状态，观察公式栏，我们发现公式栏里的公式是“=$C$1”，这就是“取景器”所取得的真实景色的位置，由于C1单元格所包围的范围没有任何景色，所以“取景器”里一片空白。
图12-35
现在我们知道，可以用公式来控制“取景器”的取景位置，这简直太棒了！下面我们调整取景器的中的公式：选中“取景器”，在公式栏中输入“=A1:B7”。按Enter键后，我们发现“取景器”里已经是单元格区域“A1:B7”所包围的范围内的“景色”了。
试着改变A1:B7单元格范围内的内容，比如缩小A1:B7范围内的图像，我们会发现，A1:B7单元格范围内的改变都实时地反映到“取景器”中了，如图12-36所示。
图12-36
好了，现在我们知道了什么叫“取景器”以及“取景器”是如何“取景”的了，下面举例具体介绍一下“取景器”的使用。
如果一个公司里只有几十名员工，那么，借助Excel中的“取景器”功能，我们可以在不写一句VBA代码的情况下，制作一个图文并茂的Excel员工管理工具。
该工具完成后的效果如图12-37所示，当选择不同员工的工号时，在工号下方会显示该员工的详细信息，并且，在员工工号的右侧同时会显示该员工的照片。
图12-37
下面介绍该工具的详细制作过程。
首先，准备好员工基本信息数据，并将该工作表取名为“员工名单”。在“员工名单”中，每位员工占据一行信息，在每行信息的最后一个单元格内是该员工的照片。
注意：为了使Excel文件不会太大，我们尽量使用小尺寸的照片；另外，为了使员工的照片的边缘与单元格的边缘对齐，可以在按着Alt键的同时调整照片的大小，如图12-38所示。
图12-38
新增一个工作表，取名为“证件打印”，并在该工作表中设计如图12-39所示的表格。在B2单元格中，利用Excel数据有效性功能设计一个员工工号的下拉列表。在B4和B6单元格中，利用B2单元格中的工号和VLOOKUP函数从“员工名单”中提取出该员工的其他相关信息。
图12-39
现在，制作一个“取景器”：复制任意一个空白单元格，然后单击 “开始→剪贴板→粘贴”按钮下方的下拉箭头，在展开的下拉列表中，选择“其他粘贴选项”中的“粘贴图片链接”选项，如图12-40所示。
图12-40
此时，工作表上出现了一个与被复制的单元格位置重叠、大小相同的Excel“链接图片”对象，这就是我们制作的“取景器”。调整“取景器”的大小和位置，使之位于“员工识别证”右侧合适的位置。为了使“取景器”与单元格的边缘对齐，我们可以按着Alt键的同时用鼠标调整图片的尺寸。
保持“取景器”处于选中的状态，在公式栏中修改“取景器”中的公式为“=员工名单!$D$2”，按Enter键后，“取景器”出现了公式所对应的单元格中的图片，如图12-41所示。
图12-41
接下来的任务是建立“取景器”中的图片与“员工识别证”中的“工号”的动态链接。以便当选择不同的员工工号时，在其右侧显示对应的员工照片。
选中“取景器”，在公式栏中输入公式：
该公式的含义是：从单元格区域“员工名单!$A:$D”中的第4列取一个单元格，该单元格位于由公式“MATCH(证件打印!$B$2, 员工名单!$A:$A,0)”所确定的行。
我们设想上述公式应该能实现如下效果：当我们在“证件打印!$B$2”单元格中选择不同的员工工号时，上述公式就会把与该员工对应的照片所在的单元格内的“风景”取出来。
不幸的是，上述公式虽然看起来“逻辑”正确，但是“取景器”并不接受该公式，当我们选中“取景器”，在公式栏中完成公式输入，按下Enter键时，Excel弹出一个对话框，提示公式含有“错误”，如图12-42所示。
图12-42
其实，我们的公式并非含有“错误”，而是Excel“取景器”不接受含有Excel函数的公式。这也许是Excel功能上值得改进的地方。不过“不幸中的万幸”是：我们可以通过“Excel命名公式”的方法绕过Excel的这个限制。
选择“公式→定义的名称→定义名称”命令，打开“新建名称”对话框。在该对话框里，我们给公式取名为“myPic”，有效范围选择“工作簿”，在“引用位置”处，输入我们前面设计的公式，然后单击“确定”按钮关闭对话框。
这时，再次选中“取景器”，在公式栏中输入“=myPic”，按Enter键后，在“取景器”中出现了当前B2单元格中的工号所对应的员工的照片，如图12-43所示。
图12-43
在B2单元格中选择不同的工号，查看动态效果，我们发现，员工工号发生变化时，员工姓名、员工部门、员工照片都会跟着发生相应的变化，如图12-44所示。
图12-44
