亿信BI在设计报表时提供了多种报表参数，那什么是报表参数，包含哪些类型？都有什么作用呢？在设计报表时我们如何正确使用这些报表参数？今日课题为你详解报表参数的相关内容。

1什么是报表参数

亿信BI报表内容都可以作为参数，用户可以根据实际需求将报表中的可变量作为参数。

更简单点说，参数好比一个十字路口，站在十字路口的我们最后所到达的目的地（分析结果），会根据选择前后左右方向（选择参数）的不同而不同。

用sql的眼睛来看，参数可以影响到sql语句的任意部分，它可以决定select中的内容、where的内容、group by的内容、order by的内容，有SQL的地方就会有参数的影子。

2为什么要使用参数

按照报表内容的可变性，报表分为静态报表和动态报表（参数化报表）。

亿信BI参数面板支持编辑框、枚举框、勾选框组、维下拉框和日期型等多种参数类型。如果是枚举类型、勾选框组的参数，该类型参数的下拉列表内容可以是静态手工输入的，也可以来自于第三方数据源，其内容可以随第三方数据源数据动态变化。

参数面板中提供了参数类型对应参数内容变化的行为响应事件(参数联动)，极大的方便了用户使用参数化报表时对参数的选择或输入，减少了用户重复定制参数条件生成报表的工作量。

3参数设置入口

进入报表模板设计器界面，下图所示位置：

4参数类型及使用说明

目前亿信BI支持如下12种报表参数：

下面我们一起来了解一下报表参数的类型的属性和它的使用说明。

按钮

按钮用于需要有特殊点击事件的情形，如在报表上添加一个“导出”按钮。

还可增加其它常用按钮，比如计算、清空、编辑、导出、日志、订阅、添加到收藏夹、打印、打印预览、查看报表说明等。

按钮添加完成后，可以在按扭属性的“事件”中直接选择配置；如果你有更多的需求，可以在事件中自行写脚本实现。

下拉按钮

当分析表含有多个点击事件，但设置多个按钮界面又不够美观，那么下拉按钮就能很好的解决此问题。

如上图的效果，只需拖动一个下拉按钮参数组件到参数面板区，按需设置【菜单设置】属性就可以完成。

滑块

滑块参数使用时可以根据鼠标的拖动改变参数值，如下图所示：

具体属性设置如下：

【最大值】滑块最右边（到顶）的数值。

【最小值】滑块最左边的数值。

【步数】可将滑动块等分为所设置的步数。

滑动块常与钻取合用于即时刷新统计图。

输入框

输入框类型的参数通常用于用户手工输入参数，如下图所示：红色框架中的“20000”数据是手工录入的。

具体的属性设置如下图：

日期

该类型用于选择日期的参数。精确到天，以日历的形式展现，如下图所示：

具体的属性设置如下图：

【参数类型】有日期、年月、年供选择

【允许选择时间】勾选后即可选择具体时间，精确到秒

【起始时间】日历中可以选择的最早时间

【截止时间】日历中可以选择的最晚时间

如果勾选了“允许选择时间”，可选择精确到秒：

年月

年月类型默认是当前年月，也可以根据自己需要写默认值。其他属性的介绍详见《参数属性详细说明》。

年

起止日期

起止日期可输入“时间轴刻度”，拖动时间点来实现数据的更新。

当“最近”按钮在左侧时，时间按照“时间轴刻度”显示：

当“最近”按钮在右侧时，可选择“开始时间”和“结束时间”：

具体属性设置：

勾选框

勾选框类型参数一般用于控制参数的显示。

【默认值】是T 时表示勾选，F表示不勾选。

【选中时的值】勾选框选中后，“选中时的值”中的数值，才有作用；如果勾选框没有选，那么该值是没有作用的。

如下图所示：

勾选框组

勾选框组的数据可以是枚举值也可以使用系统的维表。

如下图，使用系统中的维表“行政区划”的属性设置：

设置后效果如图：

可直接在分析表格中对此参数进行过滤设置即可。

枚举下拉

枚举下拉列表可配合枚举值一起使用：

设置效果如下：

【枚举值】参数可选值由显示值和实际值组成，用分号’;’分隔开。例如：人民币:10;美元:20;日元:30。

【允许多选】勾选后，参数可以选择多个；不勾选，则只能选择一个。

维下拉

该类型用维表作为参数，方便用户选择，与枚举下拉框一样可以支持多选。

此处“参数类型”包含3种：

维下拉框

如果选择了维下拉框，下方会显示“维表”属性用用户选择，此处会列出主题域下所有维表。

单位级次下拉框

单位次级下拉框是根据主题域或者主题集设置的数据级次维和当前用户的权限来显示，支持多选。

数据期下拉框

该类型是根据所设置的主题表的数据期来显示的。选择该参数时所列的可选项是根据数据的实际情况显示，即实际数据中有数据期才在报表期下拉框中列出，否则没有。必须和某一主题表关联，该主题表是可选择的；而且该主题表需要设置数据期字段。

具体属性详解如下：

【高级输入】如果勾选选择，将直接显示数据期面板，参数名称必须是set_bbqoption，所选的数据期参数将会自动作用报表。

【主题表】设置数据期类型参数时，需指定主题表，即使用某一主题表的数据期内容。如不指定，即为空的状态，系统会自动分析当前报表所引用的主题表，选取第一个作为显示数据期的主题表。

【显示方式】可以只显示代码或者文字，也可以都显示。

【自动过滤】 勾选后，会根据该参数的相关属性，自动设置过滤条件，不需要手工书写和该参数相关的过滤条件。例如：勾选后，计算时会自动产生报表过滤条件：dim('维xxx')=@yyy。该属性还可用于“单位级次下拉框”、“数据期下拉框”参数类型。

【根节点显示】所有下级节点都可以设置为根节点。输入方式形如A0000;B0000;C0000 这样的格式，表示该下拉列表的根节点有三项。例如：110000;120000。如图235北京市和天津市被设置为了根节点。该属性的值也可以用其他参数值，如是其他参数的值，只需要填入该参数的名称即可。

【包含根节点】当勾选时，就显示“根节点显示”中指定的根节点，如果“根节点显示”为空表示以维度的顶级节点为根节点，该属性是和“根节点显示”属性配合使用！

【节点过滤】根据节点过滤表达式筛选出符合条件的节点。维节点表达式中的特殊对象，item在遍历每一个节点时，item是当前遍历的维节点对象，根据item对象可以获取到维的相关属性。

例如要返回维代码前三位为010的节点，那节点表达式为left(item.id,3)=’010’。

【节点排序】格式是级数和排序参数用冒号分隔，每一级在用分号分隔开，例如0:desc;1:asc级数从0开始，0表示第一级，左边的参数表示第1级降序第二级升序。

在下图中“节点排序”属性中输入“0:desc”，数据按降序显示：

【节点选择】该属性设置控制参数值选择时只能选择叶子节点，不能选择父节点。

其他属性的介绍详见《参数属性详细说明》。

5参数属性详细说明

各属性的详细介绍：

【代号】参数容器的代号

【左边距、上边距、宽、高】用来确定参数的边距和大小

【叠放次序】多个参数相互有重叠遮盖时的叠放次序

【大小自适应】参数很多并且并排放时可以自适应大小

【水平位置、垂直位置、背景颜色、背景图片、边框】用于设置位置和外观显示

【显示】下拉框选项，分别有隐藏、不打印可预览、不打印不预览、不导出

【禁用】可对参数进行禁用，禁用后参数将不起作用。

【钻取设置】可对参数进行点击钻取设置。使用方法可参阅钻取章节。

【参数类型】设置参数的数据类型，有文本、整数和浮点供选择。

【参数顺序】多个参数时，显示的顺序，系统会根据添加先后自动给出默认顺序值。

【参数名称】报表参数的代号，建议使用数字、字母与特殊字符的组合，主要用于对参数的引用。例如：@zczj。

【参数标题】给参数命名，报表参数的标题。例如：注册资金。

【参数标题颜色】给参数标题设置颜色。

【显示参数标题】可以控制显示或者不显示参数的标题。

【长度】参数值的最大长度

【默认值】没有选择参数前，默认的值。

【记录结果集】上次选择的参数内容是否保留以便下次使用时不需要再次选择。

【自动过滤】勾选后，会根据该参数的相关属性，自动设置过滤条件，不需要手工书写和该参数相关的过滤条件。

6参数布局

亿信BI中的参数一般都是按默认的方式进行排列的，每个参数的宽度默认为200px，当一行显示不了的时候，在自动换行显示。如下图所示：

这样的方式可以满足大多数的需求，但有些报表需要特殊排版，所以我们提供了参数布局的设置面板。在参数面板空白处，点击右键->高级设置，如下图：

有3种排版方式可供选择：

系统默认布局方式为系统自适应，如果无法满足您的需求，也可以自己设置每行n个报表参数，或者设置自助拖动后，自己拖动报表参数到想要的位置。

“在excel2016中设置联动下拉菜单”的操作步骤是：

1、以Excel 2016为例，打开Excel工作表；

2、在F:H列以一级菜单为标题，二级菜单纵向排列的方式建表；

3、选择一级菜单设置区域，在“数据”选项下的“数据验证”中，“设置”选项选择“序列”，来源选择F1:H1单元格，确定；

4、选择F:H列，在“公式”选项下的“根据所选内容创建”，在“选定区域创建名称”对话框中，勾选“首行”，按“确定”，完成创建名称；

5、选择二级菜单设置区域，在“数据”选项下的“数据有效性”中，“设置”选项选择“序列”，来源输入=INDIRECT(A2) 确定；

6、在一级菜单设置区域，点击下拉选项按钮，选择相应的菜单内容；

7、在二级菜单设置区域，点击选项按钮，会弹出与一级菜单相对应的二级联动菜单，供选择。

8、整个设置过程中，按照“一级菜单为标题，二级菜单纵向排列的方式”建立表格格式是关键，方便根据所选内容创建名称，并通过INDIRECT引用创建的名称，生成二级联动菜单。

（本文内容由百度知道网友AHYNLWY贡献）

JS多级联动下拉列表，可用于省、市、区三级联动，或多级分类联动选择等。

不限级数，兼容浏览器，使用方法请看下面的HTML示例。

示例效果如下图所示：

部分源码：源码地址如下

http://blog.csdn/oukunqing/article/details/43406435

问题来源：

一位做人事管理的朋友，说他每次发下EXCEL，让员工们填写数据，员工们交上来的数据都填的不规范，给统计工作带来很多麻烦。他问有没有方法让各部门员工从下拉菜单时选取规范数据？

我的方法是：制作多级联动菜单，规范数据输入。

本示例隐藏了那位朋友的公司数据，以省份、地市、区县数据为例。

动图演示：

关键步骤提示：

第一步：分级数据整理

红色部分是一级菜单，黄色部分是二级菜单，无填充部分是三级菜单需要的数据。如下图：

第二步：自定义名称

在名称管理器中可以看到已经建立的名称：

第三步：建立各级菜单

一级菜单：

二级菜单：

三级菜单：

图/文 | 安伟星

早就承诺大家要写一篇Excel制作下拉菜单的教程，一直拖了这么久，这次用一篇文章让你完全掌握！

下拉菜单，从制作方法上，可以分为数据有效性法、控件法；从功能上，可以分为一级下拉菜单、多级联动下拉菜单、查询下拉菜单。

01、下拉菜单制作方法

下拉菜单有两者制作方法，最常用的是我们熟知的数据有效性，其实Excel中还有一个工具可以制作下拉菜单，它就是控件。

由于控件灵活性非常强，篇幅有限，本文只做简要介绍，将主要精力放在数据有效性上面。

①数据有效性法

数据有效性在2016版Excel中叫做数据验证。

如图所示，需要为部门列设置一级下拉菜单，设置下拉菜单之后，不仅能够提高录入效率，而且可以有效防止不规范地输入。

Step1：选择要添加下拉菜单的单元格C2:C7，切换到「数据」选项卡，点击「数据验证」

Step2：验证条件中，「允许」中选择「序列」

Step3：「来源」框内选择已制作好的列表区域（也可手动录入选项，选项之间用英文状态下的逗号隔开）

GIF动图演示

②控件法

控件是Excel中比较高级的一种功能，多用于VBA开发。它被集成在「开发工具」选项卡。控件法创建的下拉菜单，多数用于数值的选择，一般创建的较少，不能批量创建。

Excel中的控件

如果你的Excel中，没有开发工具这个选项卡，需要先在「自定义功能区」中将「开发工具」添加进来。

勾选如下图中的开发工具即可。

创建方法：

切换到在「开发工具」选项卡，在「控件」分区，点击「插入」，选择「组合框」控件

在工作表的任意位置绘制生成控件，选中控件点击「鼠标右键」→「设置控件格式」，在弹出的对话框中设置数据源区域，其他项保持默认即可。

控件的使用非常灵活，它和OFFSET函数、CHOOSE函数、MTATCH函数、INDEX函数等结合，能制作出非常高效的动态图表，这里不详细展开。

可以看出，不管是是用数据验证还是控件，制作一级下拉菜单都非常简单，其本质就是将下拉菜单中的数据作为数据源提前存储在菜单中，我们要做的就是设置好数据源即可，Excel自身会生成菜单。

02、多级联动下拉菜单

首先制作二级联动菜单。

二级联动菜单指的是，当我们选择一级菜单之后，对应的二级菜单会随着一级菜单的不同而选项也不同。二级菜单的创建方法有很多种，这里我们讲最常用的：通过indirect函数创建

如图所示，我们要创建省份是一级下拉菜单，对应的市名是二级下拉菜单的联动菜单。

①为省市创建“名称”

名称是一个有意义的简略表示法，可以在Excel中方便的代替单元格引用、常量、公式或表。

比如将C20:C30区域定义为名称：MySales，那么公式=SUM（MySales）可以替代=SUM（C20:C30），可见名称比单元格区域更具有实际意义。

按住Ctrl键，分别用鼠标选取包含省、市名的三列数据，要点是不要选择空单元格。（也可以通过Ctrl+G调出定位条件，设置定位条件为在常量来选取数据区域）

在菜单栏中切换到【公式】选项卡→选择【定义的名称】分区→点击【根据所选内容创建】，在弹出的菜单中，勾选【首行】选项，如图所示，这样就创建了三个省份的“名称”，“名称”的值为对应着城市名。

②创建联动菜单

创建一级菜单

为区域中的省份一列创建一级菜单，创建方法通过“引用区域”的方式，直接将第一个图中的B1:D1区域作为数据来源，这里不在赘述。

为上图中的“市”创建二级菜单

选中【市】列需要设置的单元格区域→在验证条件中选择【序列】→【来源】中输入公式=INDIRECT($C3)→点击【确定】，此时会弹出错误提示，点击【是】继续下一步即可，如图。

提示：这里出错的原因是此时C3单元格中为空，还未选择省份的数据，找不到数据源，不影响二级菜单的设置。

完成之后，就实现了二级联动菜单，如图所示。

原理解析

实现二级联动菜单的核心是：定义名称和INDIRECT函数，理解这两个核心是解题的关键。

原理①：根据“名称”的作用，当我们定义名称“江苏省”时，那么在函数引用中，“江苏省”能够代替“南京、苏州……”

原理②：INDIRECT函数为间接引用，他可将文本转化为引用。

如图是间接引用于直接引用的不同。

将原理①和原理②结合起来，以江苏为例，在来源中输入的公式=INDIRECT（$C3）的意思是，首先C3单元格中的值是“江苏省”，而INDIRECT可以将文本换成引用，而“江苏省”已经定义为名称，代表的是“南京、苏州……”，所以二级下拉菜单中出现的南京市、苏州市等。

多级下拉菜单的制作原理是完全一样的，学会了二级下拉菜单，三级菜单甚至四级菜单应该也不成问题，自己动手试一试吧！

03、查询式下拉菜单

下拉菜单的目的之一是提高输入的效率，但是，如果选项过多，那么下拉列表势必会很长，此时要想快速从下拉菜单中找到目标选项就非常困难。

我经常在想，如果能进行搜索下拉菜单该多好啊，这里教给你的方法，虽然没有搜索框，但是能模拟搜索的效果。

我把它称为查询式下拉菜单。

如图，要根据A列的集团列表，在E2单元格创建查询式下拉菜单，更方便地选择集团。该下拉菜单可以根据E2单元格内输入的第一个字来动态显示所有以输入汉字开头的集团，即实现查询作用。

对A列的集团进行升序排序。

选中E2单元格，打开「数据验证」对话框。在“允许”中选择“序列”，并在“来源”中输入公式：

=OFFSET($A$1,MATCH($E$2&"*",$A$2:$A$15,0),,COUNTIF($A$2:$A$15,$E$2&"*"),1)

在「数据验证」对话框，切换到「出错警告」窗口，取消勾选「输入无效数据时显示出错警告」，然后点击确定，完成设置。

最终的效果如下动图所示：

操作步骤同样很简单，难点是来源里面设置的公式。

①为什么要对集团数据列进行升序排序

排序之后，可以将第一个字相同的集团排在一起，这样在后面的输入首字进行查询式，这些集团都能够显示出来。

②OFFSET函数

它的语法形式是 OFFSET(reference,rows,cols,height,width)，参数1为参照系，参数2为偏移行数，参数3为偏移列数，参数4为返回几行，参数5为返回几列。

总之，这里主函数OFFSET的作用就是：当E2单元格内输入首字时，找到以输入的汉字开头的集团名称，并引用所有符合条件的集团作为下拉菜单的显示内容。

③MATCH($E$2&"*",$A$2:$A$15,0)

在集团列表中查找以E2单元格字符开头的集团名称，返回找到的对应的第一个集团在列表中的序号；

④COUNTIF($A$2:$A$15,$E$2&"*")

在列表中统计以E2中字符开头的集团的个数

这里，MATCH函数作为OFFSET的第二个参数，即向下移动的行数；COUNTIF函数作为OFFSET的第4个参数，即从集团列表中返回的行数。

举例：当E2中输入“广”时

MATCH($E$2&"*",$A$2:$A$15,0)返回以广开头的集团在$A$2:$A$15中的序号，即2（广发集团排在第二位）。

此时COUNTIF($A$2:$A$15,$E$2&"*")统计出以广开头的集团共有三个，所以返回值为3。

主函数就变为OFFSET($A$1,2,,3,1)，即返回「以A1为参照，向下移动移动两行（A3），行数总计为3行（A3:A5）的一个区域」，这个区域正是以广开头的三家集团：广发集团、广汇集团、广汽集团。

⑤为什么不能勾选出错警告

数据验证，要求输入的内容和设置的源中的内容必须一致，否则将提示警告，导致无法正常输入。我们因为是首字匹配，因此要取消警告。

最后，再次强调，函数是重点，理解了函数在本里中充当的含义，才能灵活的设置查询式下拉菜单。

·The End·

作者：安伟星，微软Office认证大师，领英中国专栏作者，《竞争力：玩转职场Excel，从此不加班》图书作者

Excel中通过数据有效性设置下拉菜单功能可以帮助我们节省很多输入时间，通过选取下拉菜单中的值来实现输入数据，非常快捷、方便、并且能最大限度减少差错的发生。但是日常工作中，我们常需要一个下拉菜单，让后面的下拉菜单依据前面的下拉菜单的内容的改变而改变（也就是多级联动、动态更新的下拉菜单）。

目前网上能看到的教程大多是通过逐级进行定义名称，在通过indirect函数引用名称的方式实现联动菜单的效果。但此种方式有很大的局限性。具体来说有以下几点：

定义名称繁琐。前一级菜单有多少个选项，就需要定义多少个名称，如果有上千个选项就需要定义上千个名称，工作量太大。

选项可能不能定义为名称，名称的格式有要求：如必须以下划线或文字、字母开头，不能出现特殊的符号等，这样就限制了选项的范围。

更新选项不便。若选项需要调整，需要逐级修改定义名称或范围，这对于需要经常更新的工作场景下显得非常不便。

那么有没有一种方法，可以避免上述的问题呢？答案是有的。今天我就这一方法首次公布于众，希望能给你的工作带来帮助……

我们先来看下最终的效果：（四级动态选择，若需要增加、减少选项只需在AreaCode工作表中进行操作，再选择全部刷新选项即可，更新维护非常简单。）

多级联动菜单演示效果

这一方法的实现思路是：

利用数据透视表的去重功能得到不重复的选项数据。

利用OFFSET函数实现对选项数据的动态范围引用，并将该动态区域定义为名称。

利用数据有效性将名称作为数据源，从而形成联动效果。

下面我们就分步骤来看下如何实现这一效果：

第1步：将数据源定义为名称，方便后续建立透视表

第2步：在辅助表中建立数据透视表，为OFFSET函数提供数据

第3步：构建省份的动态引用

第4步：创建第2个透视表为城市提供数据

第5步：构建城市的动态引用

第6步：构建区县的动态引用

第7步：检查各项参数

第8步：新建名称

第9步：新建的名称及引用位置

第10步：为每个选项设置数据有效性

结语：其实excel多级联动菜单原理非常简单，只需要熟悉offset、match、countif、counta等函数的用法，就能轻松实现这一效果。在此基础之上，你还可以做出多级联动列表框的效果，是不是很酷呢？快来试试看吧！

多级联动列表框演示效果

千万别学excel

在Excel实际的工作中，下拉菜单非常的常见，其优点显而易见，不仅可以规范数据源，而且可以处理冗余数据……但是对于如何去创建，大部分同学了解甚少……

一、效果图。

从效果图中我们可以看出，三级菜单进行了联动。符合我们的实际要求。

二、设置步骤。

（一）、规范数据源。

方法：

1、将一级菜单项整理在同一行，在同一列中列出二级菜单项。如下图。

2、将二级菜单整理在同一行，在同一列中列出三级菜单项。如下图。

3、一二三……级菜单整体数据分布。如果有四五……等级菜单，设置方式类似。

（二）、设置步骤。

1、一级菜单。

步骤：

1、选定目标单元格。

2、【数据】-【数据验证】，选择【允许】中的【序列】，在【来源】中输入：=$B$2:$D$2（B2:D2为一级菜单所在的单元格区域，需要绝对引用）。

2、二级菜单。

方法：

1、选定一二级菜单项所在单元格区域。

2、Ctrl+G打开【定位】对话框，【定位条件】-【常量】-【确定】。

3、【公式】-【根据所选内容创建】，选定【首行】-【确定】。

4、单击二级菜单项所在目标单元格，【数据】-【数据验证】，选择【允许】中的【序列】，在【来源】中输入公式：=indirect($g$3)并【确定】。

备注：

公式：=indirect($g$3)中的G3为一级菜单所在的单元格地址。

3、三级菜单。

方法：

1、选定二三级菜单项所在单元格区域。

2、Ctrl+G打开【定位】对话框，【定位条件】-【常量】-【确定】。

3、【公式】-【根据所选内容创建】，选定【首行】-【确定】。

4、单击二级菜单项所在目标单元格，【数据】-【数据验证】，选择【允许】中的【序列】，在【来源】中输入公式：=indirect($h$3)并【确定】。

备注：

1、公式：=indirect($h$3)中的H3为二级菜单所在的单元格地址。

2、四五六……级菜单的设置技巧和三级菜单的设置技巧一样，在此不再讲解。

最近好多人反馈自己经常需要去收集反馈一些表，然而，反馈来的各式各样，每个人按照自己的想法去填，这种情况我们一般会用数据有效性去解决，然而，很多现在我们的数据已经不只是那么简单了，往往第二列的填写内容由上一列的决定，那么就需要二级联动下拉菜单了

二级下拉菜单常用：一级二级行业、省市地区、前后关联，因果关系等等

今天我们分享下excel的二级下拉菜单的创建

1、规整原始数据（即所需要用的下拉菜单的数值）

①将我们所需要设置的所有一级选项放在第一行，例如下图红框内的内容（将所需设定的一级行业）

②在每个一级菜单下按列填写对应的二级菜单，如下图蓝色框中内容

2、创立名称管理器

①选中所有的要创建菜单的内容，点击公式→根据所选内容创建

②在以选定区域创建名称的框中选择首行→确定

上述步骤操作过后可以通过以下地方检测

3、创建一级菜单的名称

选中所有要创建的一级行业，然后在箭头所指的地方编辑一个名称即可，此处小编写的是一级行业（主要是年纪大了，为了好记）

4、设置一级行业下拉框

数据→数据有效性→序列→=一级行业（第3步所编辑的一级菜单的名称）

5、设置二级下拉选项

数据→数据有效性→序列→=INDIRECT($H2)（($H2为二级菜单对应的前一列的单元格）

一级二级行业下边下拉即可

学会二级下拉菜单，瞬间觉得自己的表格高大上了

如有问题欢迎留言，为防止用时忘了建议收藏哦

导读：

在EXCEL知识的海洋里，办公室工作的每个人，从小白到高手，每个阶段，都有必须掌握的技巧和公式，而有一条通向高手的毕竟之路，就是indirect函数，今天重点讲解一下。

作者简介：

MOS办公软件国际认证大师级

10年办公应用经验，企业部门负责人

函数释义：

INDIRECT(ref_text,[a1])

此函数立即对引用进行计算，并显示其内容。当需要更改公式中单元格的引用，而不更改公式本身，请使用此函数，INDIRECT为间接引用。

题引：

在上图中，F1单元格值为100，G2单元格值为F1，如果我们在H2输入公式=inderect（G2），得到的结果，是100，而不是F1，这是因为这个函数不是直接的引用函数，而是作为一种迂回存在，先得到G2只为F1，而后返回F1的值。

功能简介：5种用法，助你成就高手之路

用法1：二级下拉菜单制作

图二

如图二，制作步骤：

在D列输入列标题，部门，而后在部门下面，新建公司的部门， 这里只做两个部门，品质部和销售部以D列新建的两个部门为列标题，而后新建两列，并在每个部门下面，输入部门的人员名单选中基础数据区域，就是部门区域，而后点击公式，根据所选内容创建，在弹出的窗口，我们选中首行选中首行后，就产生了三个名称区域，1，名称为部门，包含销售部，品质部；2，销售部，包含销售部人员；3品质部，包含品质部人员开始引用，选中B列，而后点击数据，有效性，而后允许值改为序列，在公式那里，输入=部门，因为部门下面，有品质部和销售部，所以我们再输入B列部门的时候，下拉菜单的序列，才显示的是销售部，品质部选中C列，而后点击数据，有效性，序列，而后输入公式=indirect(B1)这里的B1，因为要进行变化的所以，不能用绝对引用，如果写作indirect(B$1),就是错误的，如果这样写，永远返回以B1为名称的值，就是部门用法2：创建开始区域插入行列不受影响的数据区域

如图1，当我们计算总和的时候，用到了sum公式，当我们在中间插入一行，发现没有问题，很多亲们就认为会了，其实不然。

当我们在引用区域的首行，插入一行，而后我们就可以看到，原来引用的区域，是无法自动调节的，这时原来的sum(F2:F12),变成了sum(F3:F12)，首行引用区域变了，就导致了总和的变化，不准确，那么再这种情况下，如何创建固定引用范围呢？

正确做法：将原来的公式，调整为=sum(indirect("F2"):F13)

这样在indirect("F2")的引用区域内，是不会自动变化的，不会因为插入，或是删除，而改变，就达到了固定数据区域的目的

用法3：一列转三列

如果需要转4列，就讲原来的3，改为4，而后将2，改为3，就可以了，这里不做解释，大家理解下

公式：=INDIRECT("B"&3*ROW(C6)-2+COLUMN(C6))&""

用法4：多工作表合并

用到的公式：=INDIRECT(B$1&"!B"&ROW())

用法5：多工作表合并汇总求和

用到的公式：=SUMPRODUCT(SUMIF(INDIRECT({"销售部","品质部","采购部","公关部"}&"!a:a"),B2,INDIRECT({"销售部","品质部","采购部","公关部"}&"!B:B")))

这个公式，是有难度的，是indirect函数和sum的配合，(INDIRECT({"销售部","品质部","采购部","公关部"}&"!a:a")，这部分，其实就是为了返回每个工作表的A列，而后和关键词B2对比，求出每个工作表B列对应的值的汇总，刚开始的亲们，理解不了，只要知道有这种用法，知道用就行了

评论+转发，让更多的人学会

今天就做到这里，希望对在办公室工作的亲们，有所帮助，如有疑问，大家一起讨论！

搞定Office每周三更新

「搞定Office」是黑马公社全新的七大版块之一，每周三更新，教授Office等办公软件的各种应用技巧。

◆◆◆

Excel表格如何实现二级下拉菜单的联动

黑马说：有时候我们需要为表格做下拉菜单，一级的下拉菜单你可能直接用数据验证或者数据有效性就可以实现，那今天黑马要教给大家的是有关二级菜单的联动，Office达人可要看过来了哦！

BY：Andy

◆◆◆

图文说明

效果展示

点击这里“市”下方的下拉菜单后，这里就会有“成都、北京、杭州、上海”四个选项，当我们点击成都以后，在“区”下方单元格的就会相应的出现成都的区。

同样，当我们在市这里选择了杭州，或者是北京、上海等，在区这里就会出现对应城市的区县。

这样二级联动下拉菜单是如何实现的呢？今天黑马就教大家来实现这样的菜单栏效果！

indirect函数

今天所用到的是上周介绍过的indirect函数，如果想要了解上期视频的小伙伴可点击下方蓝色文字：如果你有100个表格需要统计，那indirect函数会让你快的倍爽

下面黑马就来教教大家如何实现上述所说的二级下拉菜单的联动！

首先选中表格中的基础数据，如果列之间没有对齐，需要把空白区域去除掉。点击键盘上的Ctrl+G，就会弹出下面的定位窗口。

然后点击下方的定位条件，选择常量，然后点击确定。这样操作之后，我们就只选中了我们有数据的单元格。

然后这个时候，我们不要点击其他地方。直接点击上方菜单栏中的“公式” -->"根据所选内容创建"，对其名称进行定义，选择“首行”。因为我们这里的第一行单元格是“市”，所以选择首行。

这个时候，我们就可以在“定义名称”菜单中看见我们定义的城市：成都、北京、上海、杭州，以及其在下方对应的有关的区所在的单元格位置。

然后我们需要对一级下拉菜单进行设置，一级下菜单只是引用的是第一行的数据，我们还需要对其进行定义。选中第一行的数据，点击菜单栏中的“定义名称”，在输入区域名称这里输入“市”，然后点击确定。可以看到在定义名称这里，就多了一个市。

定义完成后，选中市下方的单元格，点击“数据”，在数据这里有一个数据验证（在2010版Excel之前叫做数据有效性），点击它。在允许选项中选中“列表”（在2010版Excel之前叫做序列），然后在“源”这里输入“=市”，点击确定即可。

通过以上操作，一级菜单就被设置好了，接下来我们来看看二级下拉菜单如何设计。

在二级下拉菜单中我们需要用到数据验证（数据有效性），以及indirect函数。点击“数据验证”（或者是数据有效性），在允许这里点击列表（或者是序列），然后在源这里输入“=indirect()”，因为我们需要直接引用F4这个单元格中的数据，所以我们需要将鼠标移至括号中，然后点击这个单元格。点击确定后，这里会提示一个错误提醒，可无需理会，直接点击“是”。

然后我们来看看现在的表格，在市这里点击“北京”，然后在区下方就会出现对应的区县名称。

那如果有时候我们有多个单元格需要进行下拉菜单设置，那怎么办呢？如果我们直接向下拉的话，就会发现后面的二级下拉菜单引用的数据其实还是来自于第一个单元格。比如在第一个市下方单元格中选择上海，我们刚刚直接下拉的所有单元格都是来自上海的区县，而不是其对应的杭州的区县。

因为这里我们设置的是对单元格进行绝对引用，这里我们需要进行修改。点击“数据验证”（“数据有效性”），将源下方indirect函数后面的第二个美元符号删除即可。

删除之后，可以再次操作刚刚所直接下拉的其他单元格中的二级菜单，发现区和县就相互对应了。

这就是今天介绍二级联动下拉菜单的使用方法，学会了制作这个，是不是对Excel又更熟练了呢？