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