EXCEL讲座3 动态表的制作

CHOOSE函数的使用与应用
(4)选择E4单元格，向下拖动复制到最后一个员工所 在的行，确定每个员工应得的礼品种类 (5)经过以上函数运算之后，每一个员工应得的礼品 种类已经确定，如下图所示

说明：根据上面的发放标准，工龄25年以上的员工 礼品相同，因此此处使用了IF函数来判断工龄是否 在25年以上。如果是，则CHOOSE函数的索引值取 6，否则根据工龄计算索引值，请大家思考用 “D4/5+1”计算索引值的道理所在。

CHOOSE函数的使用与应用
下面介绍利用CHOOSE函数解决本问题的操作方法。 操作步骤如下： (1)在D4单元格中输入公式“=YEAR($H$1-C4)1900”，求第一个员工的工龄 (2)选择D4单元格，然后向下拖动复制到最后一个 员工所在的行，计算每一个员工的工龄 (3)在E4单元格中输入如下公式，确定第一个员工 应得的礼品种类 (3)在E4单元格中输入如下公式，确定第一个员工 应得的礼品种类。 =CHOOSE(IF(D4>=25,6， D4/5+1)$H$4,$H$5,$H$6,$H$7,$H$8,$H$9)

CHOOSE函数的使用与应用
(2)选定C3单元格，然后向下拖动复制到最后一个销 售人员所在的行。 (3)经过以上操作，即可计算出每一个销售人员的销 售提成金额，如下图所示。

VLOOKUP函数和复选框制作动态图表

数据表A2:L6单元格区域为睿鑫公司2005年--2008 年期间各项管理费用的数据；图中右下方的条线图 表是反映各年度各项管理费用的对比情况，其中的 年份可以根据复选框进行选取或取消，与之对应的 数据图表也将做出相应的调整。
VLOOKUP函数和复选框制作动态图表
VLOOKUP函数和复选框制作动态图表
问题分析：本实例的制作原理如下： 首先，如上图所示，该图引入了一个辅助数据区 域A11：E22；该区域中各个年份的对应管理费用通 过VLOOKUP函数从原来表格中可查找到。 其次，在VLOOKUP查找函数中，又嵌套了IF判 断函数，通过B8：E8单元格区域的取值是否为 “TRUE”进行了对应区域是否显示相关管理费用的 控制。如果为“TRUE"，则显示对应数字，如果为 “FALSE”，则显示为“0”。 最后，B8：E8单元格区域中的取值是随图中4个 复选框的选取而变化的。
CHOOSE函数的使用与应用
CHOOSE函数的使用与应用

说明：对于该问题，当然也可用IF函数来解决，但 是IF函数的嵌套会使公式比较复杂。此题有6个结论， 所以需要有5层IF函数的嵌套，尽管最新的Excel 2007已经将IF函数嵌套由以前版本的7层提高到64 层，但是，一般说来如果结论超过8个以上，在使用 IF函数时就会觉得非常麻烦，而利用CHOOSE函数 将非常方便，下面再看一个实例。
CHOOSE函数的使用与应用
数据表的A2：B17单元格区域为某公司各销售人员 的销售金额；而E2：F17单元格区域为销售人员按 销售额进行排名后，对应各个不同名次人员的提成 比例标准。现在要求在C3：C17单元格区域中计算 出每一个销售人员的提成金额。 问题分析：该问题有15个结论，对于Excel 2007来 说，通过使用IF函数嵌套是可以实现的(但是对于 Excel 2003以及以前的版本，是比较困难的)，但是 公式的嵌套势必非常繁琐。可能有些读者会想到另 一种方法，那就是——先对每个销售人员的销售额 进行排序，然后根据个人的排列名次再作对应的运 算，这也是解决问题的一种方法。
图表制作补充---动态表的制作
动态图表的制作

所谓动态图表，是指数据图表的数据源可以根据需 要进行动态变化，从而使数据图表也作相应调整。 这种调整一般通过三种方式实现。第一，利用有关 函数设置动态数据区域；第二，通过定义数据区域 名称，并引入辅助数据区域；第三，利用动态控件 链接图表中的引用数据，可实现用户数据的自由选 择。将介绍几种典型的动态图表制作方法。

VLOOKUP函数和复选框制作动态图表
(3)在B11：E11单元格区域中建立辅助数据区域的 列标签。 因为这些列标签将出现在图例中，为了实现题目要 求的动态显示效果，不能采用与行标签一样的方法， 即不能通过“选择性粘贴”中的“转置”得到，而是要 编辑相关公式，使得它们的内容是否显示与B8：E8 单元格区域的取值进行关联，进而与复选框的选取 与否建立联系。为此，为它们建立一定的公式。
VLOOKUP函数和复选框制作动态图表

(4)利用VLOOKUP函数的查找功能，填充辅助数据区 域中间的数据。 其中：
B12单元格中的公式为： “=IF($B$8=TRUE,VLOOKUP($B$11，$2：$6,ROW()10,0)，0)”； C12单元格中的公式为： “=IF($C$8=TRUE,VLOOKUP($C$11，$2：$6,ROW()10,0)，0)”； D12单元格中的公式为： “=IF($D$8=TRUE,VLOOKUP($D$lI,$2：$6,ROW()10,0)，0)”； E12单元格中的公式为： “=IF($E$8=TRUE,VLOOKUP($E$1 1，$2：$6,ROW()10,0)，0)”；

用CHOOSE函数和组合框建立动态图表
(5)单击“开发工具”→“控件”→“插入”命令下面的箭 头，在出现的控件列表中，单击“组合框”表单控件 按钮，在工作表中画出一个组合框表单控件。 (6)在上面制作出的组合框上单击鼠标右键，从弹 出的快捷菜单中选择“设置控件格式”菜单项，弹出 “设置控件格式”对话框，在“控制”选项卡中，“数据 源区域”和“单元格链接”通过单击其后的折叠按钮， 到工作表中相应区域去选取，选取的地址分别为 K2:K6单元格区域和L2单元格；将“下拉显示项数” 设置为“5”，并选中“三维阴影”复选框。
CHOOSE函数的使用与应用
在“五一”节来临之际，公司决定依据员工的工龄长 短，分别为他们发放不同的礼品。B3：C21单元格 区域为各员工进入本单位工作时间的相关信息，而 G3：H9单元格区域为本次礼品发放的具体标准，礼 品按照工龄的长短分成了6种情况。 现在需要在D4:D21单元格区域中求出员工工龄，在 E4:E21单元格区域中确定他们应该得到的礼品种类。 问题分析：本例的任务主要在于如何确定CHOOSE 函数的索引值，以便在E4:E21单元格区域中查询每 一个员工应得的礼品种类。仔细分析标准，可以发 现工龄的间隔是固定的5年，为此，可以考虑利用简 单的数学运算，得到在使用CHOOSE函数时的索引 值。

用CHOOSE函数和组合框建立动态图表
(7)以H2:I8单元格区域为数据源，按照前面讲解的 方法在本工作表中创建一个嵌入式柱形图表，并对 其大小和位置进行适当调整，对其各个组成部分的 格式进行优化设置。 (8)把组合框移动到图表中，并将其移动到合适位置。 经过以上步骤，就得到了如下图柱形图表。从组合 框中选择某一个费用名称时，就可以得到各个分公 司该项费用的对比情况柱形图。


而B13：E22单元格区域是通过选取B12：E12，然后进 行向下拖动复制公式得到的。
VLOOKUP函数和复选框制作动态图表

说明：以上B12单元格的公式中，首先用IF函数判 断B8单元格的内容是否为TRUE，如果为 TRUE(说明选取了对应的复选框)，就用 VLOOKUP函数到上面的数据区域中查询对应的数 值，否则(说明取消了对应的复选框选取状态)显示为 0；其中单元格“SB$8、$B$11”以及单元格区域“$2： $6”设置为绝对引用，返回值的列号用“ROW()-10” 表示(意思是用当前的行号减去10作为返回数据区对 应的列)，它们的目的都是为了保证B12中的公式可 以通过向下拖动进行复制。其他几个公式的理解与 B12公式的理解方法一样。

用CHOOSE函数和组合框建立动态图表
CHOOSE函数的使用与应用
CHOOSE函数的功能是从值的列表中选择一个值。 使用该函数可以返回多达254个基于给定待选数值中 的任一数值。下面介绍一下该函数的基本功能、使 用方法与应用实例。 CHOOSE函数的语法格式为：CHOOSE(index _num，value1，value2，…) 其中： 参数index_num用以指明待选参数序号的参数值， 它必须为1到254之间的数字，或者是包含数字1到 254的公式或单元格引用 参数value1，value2，…为1到254个数值参数， 可以为数字、单元格引用、己定义的名称、公式、 函数或文本

VLOOKUP函数和复选框制作动态图表





各相关单元格的公式分别为： B11单元格中的公式为：“=IF($B$8=TRUE,$A$3， "")” C11单元格中的公式为：“=IF($C$8：TRUE，$A$4， "")” D1l单元格中的公式为：“=IF($D$8=TRUE,$A$5， "")” E11单元格中的公式为：“=IF($E$8=TRUE,$A$6， "")” 说明：B11单元格的公式中，用IF函数判断B8的内容是 否为TRUE，如果为TRUE(说明选取了对应年份的复选 框)，才显示对应年份，否则(取消了对应复选框的选取状 态)就什么也不显示(“”表示什么都不显示，注意它与空格 还不一样)。其他公式的理解与B11一样。
用CHOOSE函数和组合框建立动态图表
用CHOOSE函数和组合框建立动态图表
如上图A2：F8单元格区域为某公司6个分公司各项 管理费用的数据一览表，下面的柱状图表是反映各 分公司费用的对比情况，其中的组合框可以进行不 同种类管理费用的选取，柱状图表的显示可随选择 费用的不同而发生相应的变化。 制作思路：本实例主要利用CHOOSE函数和L2单 元格数值的变化实现图表的动态 显示。当L2=l时， CHOOSE函数选择B列的数值，也就是“招待费”； 当L2=2时，CHOOSE函数选择C列的数值，也就 是“礼品费”，依次类推。而单元格L2值的变化是根 据组合框表单控件实现的，当我们选择组合框中的 不同项目时，L2会相应地变化。Βιβλιοθήκη CHOOSE函数的使用与应用
不过，下面介绍的利用CHOOSE函数的计算可以不 用排序。具体操作步骤如下： (1)在C3单元格中输入以下公式： =CHOOSE(RANK(B3，$B$3:$B$17)，$F$3， $F$4，$F$5，$F$6，$F$7，$F$8，$F$9， $F$10，$F$11， SF$12,$F$13,$F$14,$F$15,$F$16,$F$17)*B3 说明：该公式中，利用了RANK函数的返回值，作 为CHOOSE函数的索引值，达到了题目中的计算要 求，同时也没有对原表格中的数据进行位置的调整。

VLOOKUP函数和复选框制作动态图表
要实现以上制作要求，具体操作步骤如下： (1)首先，制作好原数据所在的表格，即图中A1： L6单元格区域。 (2)在A12：A22单元格区域中建立辅助数据表的 行标签。首先，复制B2-L2单元格区域，然后选中 A12单元格并单击鼠标右键，从弹出的快捷菜单中 选择“选择性粘贴”，再从出现的对话框中选中“转置” 复选框，单击“确定”按钮后将B2：L2复制到A12： A22区域。

用CHOOSE函数和组合框建立动态图表
要实现以上的制作要求，具体操作步骤如下： (1)复制单元格区域A3:A8到H3:H8。 (2)在I2单元格中输入公式 “=CHOOSE($L$2,B2,C2,D2,E2,F2)"。 (3)向下拖动I2单元格，直到I8，这样就将I2中的 公式复制到I3：I8区域。 (4)复制B2:F2单元格区域后，将光标定位到K2单 元格，首先单击鼠标右键，从弹出的快捷菜单中选 择“选择性粘贴”，然后在随后出现的“选择性粘贴” 对话框中选择“转置”复选框，最后单击“确定”按钮。 至此，B2:F2的内容被复制到K2:K6单元格区域。

CHOOSE函数的使用与应用

使用时，函数CHOOSE基于index_num的结果值， 从value1，value2，…中选择一个数值或执行相应 的操作。对于index_ num的取值，有如下几个约 定：


如果index_num为1，函数CHOOSE则返回value1： 如果为2，函数CHOOSE则返回value2，……，以此类 推 如果index_num小于1或大于列表中最后一个值的序号， 函数CHOOSE将返回错误值#VALUE 如果index_num为小数，则在使用前将首先被截尾取整