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

亮术网 2019-11-30 本网原创

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

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

 

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

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

Excel显示开发工具选项卡

图1

 

 

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

1、插入组合框控件。

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

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

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

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

图2

 

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

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

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

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

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

图3

 

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

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

(2)$A$1 表示对行和列都绝对引用,当往下拖时, A1 不会变 A2、A3 等;当往右拖时,A1 不会变 B1、C1 等;$J$3 与 $A$1 是一个意思。

(3)COLUMN(A1) 返回 A1 的列号 1,COLUMN(A1)-1 等于 0;当公式拖到 G2 时,COLUMN(A1)-1 变为 COLUMN(B1)-1,结果为 1;则 OFFSET($A$1,$J$3,COLUMN(A1)-1) 变为 OFFSET($A$1,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所示:

Excel生成单控件动态图表

图4

 

 

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

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

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

图5

 

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

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

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

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

Excel插入选项按钮控件

图6

 

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

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

Excel用定义名称为选项控件添加引用

图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所示:

Excel插入组合框控件

图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所示:

Excel定义X轴类别和动态显示数据名称

图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所示:

Excel生成双控件动态图表

图10