Excel制作单双控件动态图表，含用组合框、选项按钮动态显示每月产量与合格数量、月份或分店营业额

在 excel 中，制作动态图表需要用组合框或选项按钮控件，如果只动态显示一项数据，用一个组合框即可；如果要显示两项以上数据，需要用选项按钮与组合框组合。例如要动态显示每个月生产产品的产量和合格数量，用一个组合框就可以完成；而要动态显示每个月或每个分店的营业额，需要用选项按钮与组合框。

Excel制作动态图表需要用 OffSet 函数，用它实现根据当前选择值返回对相应单元格的引用；如果仅用一个组合框，不需要定义名称；如果用选项按钮与组合框组合，则需要定义名称。

一、Excel显示“开发工具”选项卡

在默认情况下，Excel 不显示“开发工具”选项卡，需要先把它显示出来，方法为：右键功能区任意空白处，在弹出的菜单中选择“自定义功能区”，打开“Excel 选项”窗口，并自动选择“自定义功能区”，勾选“主选项卡”下的“开发工具”，单击“确定”，则“开发工具”显示到功能区；操作过程步骤，如图1所示：

图1

二、Excel制作单组合框控件动态图表

1、插入组合框控件。

A、选择“开发工具”选项卡，单击“插入”，选择“表单控件”下的“组合框”，鼠标变为“加号”，把它移到要画组合框的位置，按住左键并拖动，画出的组合框大小合适后放开左键，则成功插入一个组合框；

B、右键组合框，在弹出的菜单中选择“设置控件格式”， 打开“设置控件格式”窗口，把光标定位到“数据源区域”右边，框选 A2:A7 作为组合框的数据源（如果数据源行数比较多，可以直接输入，注意加上 $，以表示绝对引用）；再把光标定位到“单元格连接”的左边，选择一个单元格（如 J3）作为显示组合框当前选择值的索引（序号），单击“确定”，则把“月份”添加为组合框的数据源；

C、单击任意空白处释放组合框的选中状态，再单击它，则展开刚才添加的数据源“月份”，选择“2月”，J3 中显示 2，再选择“4月”，J3 中显示 4；操作过程步骤，如图2所示：

图2

2、复制表头和根据组合框选择值显示相应记录。

A、单击 A1 单元格选中它，按住 Shift，再单击 D1，选中表头，按 Ctrl + C 复制，选中 F1，按 Ctrl + V，把表头粘贴一份；单击“组合框”选择“1月”；

B、选中 F2，选择“公式”选项卡，单击“查找与引用”，在弹出的选项中选择 OFFSET，打开“函数参数”窗口，单击 Reference 输入框右边的“把窗口收缩起来的图标”，单击 A1，则 A1 自动输入到 Reference 的输入框中，同样方法把 J3 输入到 Rows 右边的输入框中，再把两个输入框的引用改为 $A 和 $J；把光标定位到 Cols 右边的输入框中，把 COLUMN(A1)-1 复制过去，单击“确定”，则 F2 中的值变为“1月”，正是组合框的值；

C、选中 F2，把鼠标移到 F2 右下角的单元格填充柄上，鼠标变为黑色加号后，按住左键，往右拖一直拖到 I2，则提取了“1月”的数据；操作过程步骤，如图3所示：

图3

D、公式 =OFFSET($A,$J,COLUMN(A1)-1) 说明：

（1）在“函数参数”窗口设置的其实就是公式 =OFFSET($A,$J,COLUMN(A1)-1)，公式的意思是：以 A1 为基准，返回 A1 下 1 行（J3 中的值为 1）0 列（COLUMN(A1)-1) 值为 0）的引用，即返回对 A2 的引用，也就是“1月”。

（2）$A 表示对行和列都绝对引用，当往下拖时， A1 不会变 A2、A3 等；当往右拖时，A1 不会变 B1、C1 等；$J 与 $A 是一个意思。

（3）COLUMN(A1) 返回 A1 的列号 1，COLUMN(A1)-1 等于 0；当公式拖到 G2 时，COLUMN(A1)-1 变为 COLUMN(B1)-1，结果为 1；则 OFFSET($A,$J,COLUMN(A1)-1) 变为 OFFSET($A,1,1)，即返回 A1 下 1 行 1 列的引用，即返回对 B2 的引用。

提示：如果对 OffSet 函数还不熟悉，请查看《Excel OffSet函数的使用方法，含与Sum、Match、CountIf、If、Or、Row组合实现动态求和、一个重复多个、分段》一文。

3、生成动态图表。

A、选择“插入”选项卡，单击“插入柱形图或条形图”图标，在弹出的图表样式中选择第一个“簇状柱形图”，则插入一个图表；右键图表，在弹出的菜单中依次选择“置于底层”→ 置于底层，把图表下移到最底层，不让它遮挡组合框；如果文档中插入的东西不多，也可以一层层的下移；

B、把鼠标移到图表上，鼠标变为带四个箭头的十字架后，按住左键，把图表移好位置；右键组合框选中它，按住鼠标左键，把组合框移到图表的右上角；

C、右键图表，在弹出的菜单中选择“选择数据”，打开“选择数据源”窗口，框选 F1:I2，把“图表数据区域”改为所选区域，单击“确定”，则图表 X 轴变为“月份”，单击“组合框”选择“3月”，则图表显示“3月”的数据，再选择“5月”，则图表显示“5月”的数据；操作过程步骤，如图4所示：

图4

三、Excel制作双组合框控件动态图表

1、复制表头。选中 B1 单元格，按住 Shift，单击 G1，选中 B1:G1，当前选项卡为“开始”，选中 A9，单击窗口右上角的“粘贴”，在弹出中的选项中，选择“粘贴”下的“转置”，则表格列标题被转为行；操作过程步骤，如图5所示：

图5

2、插入“选项按钮”控件。

A、选择“开发工具”选项卡，单击“插入”，在弹出的控件中，选择“选项按钮”，鼠标变为加号，把它移到要插入“选项按钮”的位置，按住左键并拖动，则画出一个选项按钮，单击里面的文字把光标插到文字前，选中所有文字，输入“分店”；

B、右键“选项按钮”，在弹出的选项中选择“设置控件格式”，打开“设置控件格式”窗口，单击“单元格链接”输入框右边的图标把窗口收缩起来，单击 B9，把它作为连接“选项按钮”的单元格，单击“确定”，“选项按钮”设置好；

C、按 Ctrl + C 复制选项按钮，再按 Ctrl + V 把它粘贴一份，把粘贴的副本与“分店”对齐，单击副本中的文字把光标定位到里面，选中所有文字，输入“月份”；按住 Shift，右键“分店”选项按钮，把两个选项按钮选中，然后把它们移到合适位置，单击一下任意空白处释放选项按钮的选中状态，单击“分店”，B9 显示 1，单击“月份”，B9 显示 2；操作过程步骤，如图6所示：

图6

3、用“定义名称”为选项控件添加引用。

1、右键“分店”选项按钮选中它，选择“公式”选项卡，单击“定义名称”打开“新建名称”窗口，在“名称”右边输入“分类选项”，选中“引用位置”右边输入框中 = 右边的文字，输入 IF(，单击 B9，输入“=1,”，框选 A2:A7，输入“,”，框选 A9:A14，输入“)”，按回车确定，则操作完成；演示如图7所示：

图7

提示：也可直接把公式 =IF(Sheet1!$B$9=1,Sheet1!$A$2:$A$7,Sheet1!$A$9:$A$14) 复制到“引用位置”右边的输入框中。

2、公式 =IF(Sheet1!$B$9=1,Sheet1!$A$2:$A$7,Sheet1!$A$9:$A$14) 说明：

Sheet1 为工作表名称；Sheet1!$B$9=1 为 IF 的条件，如果条件成立，则返回 Sheet1!$A$2:$A$7，即返回所有“分店”；否则返回Sheet1!$A$9:$A$14，即返回所有“月份”。

4、插入组合框控件。

A、选择“开发工具”选项卡，单击“插入”，选择“表单控件”下的“组合框”，鼠标变为加号，把它移到要插入组合框的位置，按下左键并拖动，则插入一个组合框；

B、右键“组合框”，在弹出的选项中选择“设置控件格式”，打开“设置控件格式”窗口，在“数据源区域”右边输入上一步定义的“选项控件”名称“分类选项”；单击“单元格链接”输入框右边的小图标，把窗口收缩起来，单击 C9 把它作为组合的链接单元格，再单击输入框右边的图标把窗口展开，单击“确定”；

C、把“组合框”移到“月份”右边，单击任意空白处释放组合框的选中状态，单击“组合框”选择“3号店”，则“3号店”作为它的当前选项，C9 中同时变为相应的值； 选择“月份”，组合框中自动变为“3月”，再次单击“组合框”，选择“4月”；操作过程步骤，如图8所示：

图8

5、定义“X轴类别”和“动态显示数据”名称。

A、选择“公式”选项卡，单击“定义名称”，打开“新建名称”窗口，把“X轴类别”复制到“名称”后，把公式 =IF(Sheet1!$B$9=2,Sheet1!$A$2:$A$7,Sheet1!$A$9:$A$14) 复制到“引用位置”后并覆盖原有文字，单击“确定”；

B、再次单击“定义名称”，在打开的窗口中，把“动态显示数据”复制到“名称”后，把公式 =IF(Sheet1!$B$9=1,OFFSET(Sheet1!$A$1,Sheet1!$C$9,1,1,6),OFFSET(Sheet1!$A$1,1,IF(Sheet1!$C$9<=6,Sheet1!$C$9,6),6,1)) 复制到“引用位置”输入框中且覆盖原有文字，单击“确定”；两个名称定义好；操作过程步骤，如图9所示：

图9

C、公式说明：

（1）=IF(Sheet1!$B$9=2,Sheet1!$A$2:$A$7,Sheet1!$A$9:$A$14) 意思是：如果 B9 中的数值为 2，则返回 A2:A7 中的“分店”，否则返回 A9:A14 中的“月份”。

（2）=IF(Sheet1!$B$9=1,OFFSET(Sheet1!$A$1,Sheet1!$C$9,1,1,6), OFFSET(Sheet1!$A$1,1,IF(Sheet1!$C$9<=6,Sheet1!$C$9,6),6,1))

a、Sheet1!$B$9=1 是 IF 的条件，意思是：如果 B9 中的值等于 1（即当选择“分店”时），则执行 OFFSET(Sheet1!$A$1,Sheet1!$C$9,1,1,6)，也就是显示当前选择的“分店”每个月的营业额；否则执行 OFFSET(Sheet1!$A$1,1,IF(Sheet1!$C$9<=6,Sheet1!$C$9,6),6,1)，即显示当前选择的“月份”每个店的营业额。

b、OFFSET(Sheet1!$A$1,Sheet1!$C$9,1,1,6) 中，$C$9 返回的是“组合框”的选择值，假如“组合框”的当前选择值为“4号店”，则$C$9 返回 4，则 OFFSET 返回以 A1 为基准、A1 下 4 行 1 列且高度为 1、宽度为 6 的单元格引用，即返回 $B$5:$G$5，也就是返回“4号店”每个月的营业额（可以参考最后一步的演示）。

c、OFFSET(Sheet1!$A$1,1,IF(Sheet1!$C$9<=6,Sheet1!$C$9,6),6,1)

IF(Sheet1!$C$9<=6,Sheet1!$C$9,6) 用于返回列号，意思是：如果 C9 中的值小于等于 6，则反回 C9 中的值，否则返回 6，这主要是为了防止组合框选择值超过表格要显示到图表中的列数（即“1月”到“6月”的 6 列）而发生异常；

假如“组合框”当前选择值为“4月”，则 IF(Sheet1!$C$9<=6,Sheet1!$C$9,6) 返回 4，则 OFFSET(Sheet1!$A$1,1,IF(Sheet1!$C$9<=6,Sheet1!$C$9,6),6,1)  变为 OFFSET(Sheet1!$A$1,1,4,6,1)，意思是：以 A1 基准，返回 A1 下 1 行 4 列且高度为 6 宽度为 1 的单元格引用，即返回 $E$2:$E$7，也就是返回“4月”每个分主店的营业额。

6、生成双控件动态图表。

A、选择“插入”选项卡，单击“插入柱形图”图标，然后选择第一个图表，则插入一个空白图表，右键该图表，在弹出的菜单中选择“置于底层”→ 置于底层，把图表置于底层以显示“选项按钮和组合框”；

B、把图表拖到合适的位置，右键它，在弹出的菜单中选择“选择数据”，打开“选择数据源”窗口，单击“添加”，打开“编辑数据系列”窗口，选中“系列值”输入框中所有文字，按 Delete 键把它们删除，单击工作表名称 Sheet1，再把前面定义的名称“动态显示数据”复制过去，单击“确定”返回“选择数据源”窗口；

C、单击“编辑”，打开“轴标签”窗口，单击工作表名称 Sheet1，把前面定义的名称“X轴类别”复制过去，单击“确定”，再次单击“确定”；把“图表标题”移到左边，单击“组合框”，选择“4号店”，则显示“4号店”每个月的营业额；选择“月份”，则显示“4月”每个分店的营业额，再选择“5月”，则显示“5月”每个店的营业额；操作过程步骤，如图10所示：

图10

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。