1、本文描述了通过“Excel 表”和“INDIRECT,函数”设置二级数据有效性的方法。,问题,一个单元格的数据有效性的条件可以被设,置为整数、小数等等,并设定其相应的取值范围。当条件被设置为“序列”的时候,可以在数据有效性对话框的“来源”编辑框里输入一个以半角逗号分隔的字符串(例如“北京,天津,上海,重,庆 ” ), 或 是 指 定 一 个 引 用 区 域 ( 例 如“=$A$1:$A$9”),在单击这个单元格的时候,就会显示一个下拉菜单,每行显示一个条目,供使用者选择。,可是这种方法只能直接引用当前表单的区域,而不能设置到其他表单,例如表单 Sheet1 的有 效 值 序 列 不 能 被 设
2、 置 为“=Sheet2!$A$1:$A$9”。,痋痋只身一人去到了别的痋洞,在身无分文的情况下,靠着痋痋的努力,在痋洞生存了,有文章提到可以用名空间的办法来实现跨表单的引用,但是如果数据超出了引用区域的范围,例如“$A$1:$A$9”有 9 项,如果现在新增了 5项,就需要修改引用区域,也就是逐项改变数据有效性取值,而不能自动更新。,另外,如果是数据减少的情况,例如原本设置为“$A$1:$A$9”共有 9 项,现在只剩下 3 项,那么单击下拉箭头之后会看到菜单中出现了大,片空白。有文章提到可以用 OFFSET 加 COUNTA 函数来解决这个问题,但如果复制这样的单元格,那么函数参数中的行或
3、者列就会自动增加,使设置失效。,“Excel 表”不是“Excel 表”,为了跨表单设置数据有效性,我们需要先在,Excel 中插入“Excel 表”。,痋痋只身一人去到了别的痋洞,在身无分文的情况下,靠着痋痋的努力,在痋洞生存了,没错,就是在 Excel 中插入“Excel 表”。不是“表单”,不是“表”,不是“数据表”,不是“数据透视表”,不是新建“空簿”,不是插入行或者列,是“Excel 表”。,“Excel 表”不是“Excel 表”吗?抱歉不是,通常听到的“Excel 表”的“表”的含义,是指Excel 文件,或是 Excel 概念框架里的“单元格区域”,或者干脆就是指的 Excel
4、 软件本身。,在 Excel 的概念框架里,“Excel 表”是对一块“单元格区域”的引用,不是“用 Excel 打开的表”,而是 Excel 在内部定义和使用的一种单元格区域的组织形式。,在没有被定义为“Excel 表”之前,“单元格区域”仍然只是“单元格区域”,或者简称“区域(range)”,不论它看起来多么像一张日常生活中的表,不论其中记录了多少数字、公式。参见,痋痋只身一人去到了别的痋洞,在身无分文的情况下,靠着痋痋的努力,在痋洞生存了,Excel 帮助文档“Excel 表概述”。,在视图中,“Excel 表”的右下方有一个蓝色的小三角,。这个小三角又叫做该表的尺寸控点,表示这里有一份
5、 Excel 表,而不是一块普通的单元格区域。在图中,每一列都是一份“Excel 表” ,可以看到每一份“Excel 表”的右下方都有代表尺寸控点的蓝色小三角。,如果用户在紧邻尺寸控点的下方单元格里,输入数据后回车,控点可以自动跨越到新的数据行,并包含刚刚录入的数据,这一功能被称为“表自动扩展”。如果需要开启或关闭“表自动扩展”这一功能,可使用“Excel 选项/校对/自动更正选项/键入时自动套用格式/在表中包含新行和列”。,“,在 Excel 的概念框架里,“Excel 表”、 表”、“表格”、 Excel 表格”是同义词,是对一个单元,痋痋只身一人去到了别的痋洞,在身无分文的情况下,靠着痋
6、痋的努力,在痋洞生存了,格区域的引用,是这个单元格区域的别名,有自己的“名称”。这里的“名称”与日常生活中使用的“名称”一词也有不同的含义。,日常生活中,名称是指“称谓”。在 Excel 概念框架里, “名称”是一块单元格区域的“代词” ,表示在这份文档里,可以用这个“名称”来代替这块区域。在 Excel 文档里,当您使用一个“名称”,您实际使用的是一块单元格区域。在 Excel,文档里,当您看到一个“名称”,您实际看到的是一块单元格区域。,如果您了解一点程序设计,那么“名称”代表实例,而不是类。在“名称管理器”中可以查看到“名称”的存在,下一小节可以看到一张“名称管理器”的示意图。,创建 E
7、xcel 表,痋痋只身一人去到了别的痋洞,在身无分文的情况下,靠着痋痋的努力,在痋洞生存了,创建“Excel 表”的方法是通过“插入”标,签的“表/表”按钮。,“,创建“Excel 表”之后,可以在“名称管理器”中看到创建的结果,。名称管理器”按钮位于“公式”标签页。,可以看到刚刚选定的单元格区域现在被“名,“,称管理器”分解到“名称”、 数值”、 引用位置”三个属性之中分别描述。就像前面所提到的那样,表的“名称”可以代替这一序列,可以在文档中任何一个可以使用公式的地方被引用。在名称被引用时,它将返回完整的数值的序列。,创建的“Excel 表”将被自动命名为“表 X” ,您可以为它重新命名,将
8、它改为任何您需要的名字。打开名称管理器,点击选择您所希望修改的表,再点击上方的“编辑”按钮。,有时,你可能会删除某个已经存在的“名称” ,痋痋只身一人去到了别的痋洞,在身无分文的情况下,靠着痋痋的努力,在痋洞生存了,以便腾出位置,让它不占用您希望设定的名字。然后,你可能会发现,即便删除了那个已经存在的名字,Excel 却仍然提示“该名称已存在”。这可能是因为刚刚删除的“名称”还保留在对象模型(ObjectModel)实例以内。可以尝试保存并关闭全部 Excel 文档之后重新打开文件,或者稍加等待。,在“名称管理器”图中,除了“计量属性描,述”以外,其他的“名称”全都是“Excel 表”,可以看
9、到“Excel 表”和单纯的“名称”有不一样的图标。“名称”是一个数值序列,它可以是上文所描述的“Excel 表”,也可以仅仅是一个“名称”。本质上都是对一块“单元格区域”的引用,不同在于“Excel 表”具有尺寸控点,可以执行“表自动扩展”。,重点是,我们可以利用“引用名称将返,痋痋只身一人去到了别的痋洞,在身无分文的情况下,靠着痋痋的努力,在痋洞生存了,回名称所指代的数值序列”这一特性来设置只有一级的跨表单的数据有效性。,第一步,选择一个有数据的单元格区域,创建一份名为“抽象属性描述”的 Excel 表（参考本文档第二小节“Excel 表和名称”）。,第二步,文件内任意表的单元格的数据有效
10、性设置为“=INDIRECT(“抽象属性描述”)”。,这样,“抽象属性描述”将作为 Excel 表返回它的值序列,进而作为数据有效性的序列的来源。在这里注意 INDIRECT 函数使用的参数是字符串形式,表的名称位于两个半角双引号之间。,INDIRECT 函数,INDIRECT 函数的参数可以是文本(字符串),也可以是单元格引用。,痋痋只身一人去到了别的痋洞,在身无分文的情况下,靠着痋痋的努力,在痋洞生存了,字符串参数和单元格引用参数的形式区别在于,字符串用双引号表示,或者用字符串的算式表示,单元格引用则使用形如 R1C1 的形式。,例如,INDIRECT(“A2”)和 INDIRECT(A2
11、),前者的参数是一个字符串,后者的参数是一个R1C1 形式的单元格引用。前者参数中的“A2”位于一对半角双引号之间,表示使用者向函数传入的参数 A2 是字符串的类型。后者没有半角双引,号,表示使用者向函数传入的参数 A2 是单元格引用的类型。,INDIRECT 函数的功能在于,利用参数,最终找到的单元格区域,进而获得区域中的数值。,下表举例展示了 INDIRECT 函数的用法。使用的例子与 Excel 帮助文档中相同,但对公式的说明略有差异。,痋痋只身一人去到了别的痋洞,在身无分文的情况下,靠着痋痋的努力,在痋洞生存了,在这个例子的 A7、 A8 的公式中,$A$2 和$A$3单元格里存放的文
12、本是“B2/B3”。这时,向INDIRECT 函数传入它们所在的单元格位置,分别是$A$2和$A$3,也就是向INDIRECT传入单元格引用参数的情况。函数的计算过程是,从参数单元格取出文本“B2/B3”,再到 B2 和 B3 单元格取出数值。,此时,参数单元格中的文本“B2/B3”都是单,元格引用的格式,假如不是单元格引用的格式,函数将无法得到正确结果。,虽然在第 9 行的例子中,A4 单元格存储的文本“Gee”并不是直接的单元格引用的格式,但它表示一个单元格的“名称”,是另一种引用单元格的方式。假如文档中不存在一个名为“Gee”的名称,那么函数就会认为参数不正确,进而返回 REF 错误。而
13、假如“Gee”的区域不是一个单,痋痋只身一人去到了别的痋洞,在身无分文的情况下,靠着痋痋的努力,在痋洞生存了,元格而是一个序列,那么 Excel 会认为返回值不正确,因为只能容纳一个数值的单元格不能容纳一个数值序列,进而返回 VALUE 错误。,如果“单元格引用”是一个生僻的技术名词,那么它的意义是“对一个单元格区域的位置的描述”。假如你想表示一个单元格,请使用这个单元格的列与行,例如 A2 或者$A$2。假如你想表示一片单元格的区域,请使用这个区域左上角的单元,格引用作为起始位置,加上这个区域的右下角的单元格引用作为结束位置,加上起始位置与结束位置之间用冒号隔开,像这样“$A$2:$D$7”
14、。终止位置的行与列必须大于等于起始位置的行与列,否则没有意义。参见 Excel 帮助文档“创建或更改单元格引用”、“在相对引用、绝对引用和混合引用间切换”、“对 Excel 表格使用结构化引用”、“在公式中定义和使用名称”。,痋痋只身一人去到了别的痋洞,在身无分文的情况下,靠着痋痋的努力,在痋洞生存了,设置二级数据有效性,再次观察名称管理器这张图,注意“抽象属性描述”中的每一个数值都是另一张表的“名称”。,也就是说,假如把第一级数据有效性设置为“=INDIRECT(“抽象属性描述”)”,那么使用者在选择的时候,无论选哪一个数值,都会选到另一张表的名称。,这时,假如在第二级数据有效性中再次使用I
15、NDIRECT 函数,并且传入一级单元格引用,那么在运行的时候,INDIRECT 函数就可以从有值的一级单元格中得到一个字符串值。又因为这个字符串值恰好又是某一张二级表的名称,所以它将返回二级表的值序列。,于是可以用下面的方法来实现二级关联数,据有效性:,痋痋只身一人去到了别的痋洞,在身无分文的情况下,靠着痋痋的努力,在痋洞生存了,第一步,设定一级单元格。将一级数据有效性的序列来源设定为一级表的名称,并使一级表中的每一个值都能对应一个二级表的名称。例如,在一级数据有效性的序列来源中输入“=INDIRECT(“抽象属性描述”)”。,第二步,设定二级单元格。在二级有效值中使用 INDIRECT 函
16、数,把一级单元格的引用作为参数传入 INDIRECT 函数,例如“=INDIRECT(A2)”。,如果希望自动填充数据有效性到单元格下方的单元格，请使用形如“A2”格式的单元格引用。如果希望固定到某一个一级数据单元格，请使用形如“$A$2”的单元格引用形式。,因为 Excel 表所对应的单元格区域可以在任何一张表上,所以就实现了跨表单的二级数据有效性。,在需要增加有效性值序列的时候,只需要在,痋痋只身一人去到了别的痋洞,在身无分文的情况下,靠着痋痋的努力,在痋洞生存了,相应的“Excel 表”的下方输入新的数据,点击回车,使表自动扩展,就能使新的有效性序列被传递到单元格内。,在减少有效性数值序列的时候,尽管“Excel表”并不能自动减少，但是,“Excel 表”的尺寸控点可以指示当前序列的范围，这样就比较不容易出错。,注意,数据有效性嵌套的层级最高是 20 层。另外,可以使用数据有效性的“圈释无效数据”来检查修改有效值区域后文档中是否存在无效数据。,0c21f5ca 宝运莱 ,
