EXCEL2007跨表单设置二级数据有效性.pptx

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 概念框架里

4、的“单元格区 域”,或者干脆就是指的 Excel 软件本身。,在 Excel 的概念框架里,“Excel 表”是对一 块“单元格区域”的引用,不是“用 Excel 打开 的表”,而是 Excel 在内部定义和使用的一种单 元格区域的组织形式。,在没有被定义为“Excel 表”之前,“单元格 区域”仍然只是“单元格区域”,或者简称“区 域(range)”,不论它看起来多么像一张日常生活 中的表,不论其中记录了多少数字、公式。参见,痋痋只身一人去到了别的痋洞,在身无分文的情况下,靠着痋痋的努力,在痋洞生存了,Excel 帮助文档“Excel 表概述”。,在视图中,“Excel 表”的右下方有一个蓝

5、色 的小三角,。这个小三角又叫做该表的尺寸控点, 表示这里有一份 Excel 表,而不是一块普通的单 元格区域。在图中,每一列都是一份“Excel 表” , 可以看到每一份“Excel 表”的右下方都有代表 尺寸控点的蓝色小三角。,如果用户在紧邻尺寸控点的下方单元格里,输入数据后回车,控点可以自动跨越到新的数据 行,并包含刚刚录入的数据,这一功能被称为“表 自动扩展”。如果需要开启或关闭“表自动扩展” 这一功能,可使用“Excel 选项/校对/自动更正选 项/键入时自动套用格式/在表中包含新行和 列”。,“,在 Excel 的概念框架里,“Excel 表”、 表”、 “表格”、 Excel 表

6、格”是同义词,是对一个单元,痋痋只身一人去到了别的痋洞,在身无分文的情况下,靠着痋痋的努力,在痋洞生存了,格区域的引用,是这个单元格区域的别名,有自 己的“名称”。这里的“名称”与日常生活中使 用的“名称”一词也有不同的含义。,日常生活中,名称是指“称谓”。在 Excel 概 念框架里, “名称”是一块单元格区域的“代词” , 表示在这份文档里,可以用这个“名称”来代替 这块区域。在 Excel 文档里,当您使用一个“名 称”,您实际使用的是一块单元格区域。在 Excel,文档里,当您看到一个“名称”,您实际看到的是 一块单元格区域。,如果您了解一点程序设计,那么“名称”代 表实例,而不是类。

7、在“名称管理器”中可以查 看到“名称”的存在,下一小节可以看到一张“名 称管理器”的示意图。,创建 Excel 表,痋痋只身一人去到了别的痋洞,在身无分文的情况下,靠着痋痋的努力,在痋洞生存了,创建“Excel 表”的方法是通过“插入”标,签的“表/表”按钮。,“,创建“Excel 表”之后,可以在“名称管理器” 中看到创建的结果,。名称管理器”按钮位于“公 式”标签页。,可以看到刚刚选定的单元格区域现在被“名,“,称管理器”分解到“名称”、 数值”、 引用位置” 三个属性之中分别描述。就像前面所提到的那样,表的“名称”可以代替这一序列,可以在文档中 任何一个可以使用公式的地方被引用。在名称被

8、 引用时,它将返回完整的数值的序列。,创建的“Excel 表”将被自动命名为“表 X” , 您可以为它重新命名,将它改为任何您需要的名 字。打开名称管理器,点击选择您所希望修改的 表,再点击上方的“编辑”按钮。,有时,你可能会删除某个已经存在的“名称” ,痋痋只身一人去到了别的痋洞,在身无分文的情况下,靠着痋痋的努力,在痋洞生存了,以便腾出位置,让它不占用您希望设定的名字。 然后,你可能会发现,即便删除了那个已经存在 的名字,Excel 却仍然提示“该名称已存在”。这 可能是因为刚刚删除的“名称”还保留在对象模 型(ObjectModel)实例以内。可以尝试保存并关 闭全部 Excel 文档之

9、后重新打开文件,或者稍加 等待。,在“名称管理器”图中,除了“计量属性描,述”以外,其他的“名称”全都是“Excel 表”, 可以看到“Excel 表”和单纯的“名称”有不一 样的图标。“名称”是一个数值序列,它可以是上 文所描述的“Excel 表”,也可以仅仅是一个“名 称”。本质上都是对一块“单元格区域”的引用, 不同在于“Excel 表”具有尺寸控点,可以执行“表 自动扩展”。,重点是,我们可以利用“引用名称将返,痋痋只身一人去到了别的痋洞,在身无分文的情况下,靠着痋痋的努力,在痋洞生存了,回名称所指代的数值序列”这一特性来设置 只有一级的跨表单的数据有效性。,第一步,选择一个有数据的单

10、元格区域,创 建一份名为“抽象属性描述”的 Excel 表（参考 本文档第二小节“Excel 表和名称”）。,第二步,文件内任意表的单元格的数据有效 性设置为“=INDIRECT(“抽象属性描述”)”。,这样,“抽象属性描述”将作为 Excel 表返 回它的值序列,进而作为数据有效性的序列的来 源。在这里注意 INDIRECT 函数使用的参数是字 符串形式,表的名称位于两个半角双引号之间。,INDIRECT 函数,INDIRECT 函数的参数可以是文本(字符串),也可以是单元格引用。,痋痋只身一人去到了别的痋洞,在身无分文的情况下,靠着痋痋的努力,在痋洞生存了,字符串参数和单元格引用参数的形式

11、区别 在于,字符串用双引号表示,或者用字符串的算 式表示,单元格引用则使用形如 R1C1 的形式。,例如,INDIRECT(“A2”)和 INDIRECT(A2), 前者的参数是一个字符串,后者的参数是一个 R1C1 形式的单元格引用。前者参数中的“A2”位 于一对半角双引号之间,表示使用者向函数传入 的参数 A2 是字符串的类型。后者没有半角双引,号,表示使用者向函数传入的参数 A2 是单元格引 用的类型。,INDIRECT 函数的功能在于,利用参数,最终 找到的单元格区域,进而获得区域中的数值。,下表举例展示了 INDIRECT 函数的用法。使 用的例子与 Excel 帮助文档中相同,但对

12、公式的 说明略有差异。,痋痋只身一人去到了别的痋洞,在身无分文的情况下,靠着痋痋的努力,在痋洞生存了,在这个例子的 A7、 A8 的公式中,$A$2 和$A$3 单元格里存放的文本是“B2/B3”。这时,向 INDIRECT 函数传入它们所在的单元格位置,分别 是$A$2和$A$3,也就是向INDIRECT传入单元格引 用参数的情况。函数的计算过程是,从参数单元 格取出文本“B2/B3”,再到 B2 和 B3 单元格取出 数值。,此时,参数单元格中的文本“B2/B3”都是单,元格引用的格式,假如不是单元格引用的格式, 函数将无法得到正确结果。,虽然在第 9 行的例子中,A4 单元格存储的文 本

13、“Gee”并不是直接的单元格引用的格式,但它 表示一个单元格的“名称”,是另一种引用单元 格的方式。假如文档中不存在一个名为“Gee” 的名称,那么函数就会认为参数不正确,进而返 回 REF 错误。而假如“Gee”的区域不是一个单,痋痋只身一人去到了别的痋洞,在身无分文的情况下,靠着痋痋的努力,在痋洞生存了,元格而是一个序列,那么 Excel 会认为返回值不 正确,因为只能容纳一个数值的单元格不能容纳 一个数值序列,进而返回 VALUE 错误。,如果“单元格引用”是一个生僻的技术名词, 那么它的意义是“对一个单元格区域的位置的描 述”。假如你想表示一个单元格,请使用这个单元 格的列与行,例如

14、A2 或者$A$2。假如你想表示一 片单元格的区域,请使用这个区域左上角的单元,格引用作为起始位置,加上这个区域的右下角的 单元格引用作为结束位置,加上起始位置与结束 位置之间用冒号隔开,像这样“$A$2:$D$7”。终 止位置的行与列必须大于等于起始位置的行与 列,否则没有意义。参见 Excel 帮助文档“创建 或更改单元格引用”、“在相对引用、绝对引用和 混合引用间切换”、“对 Excel 表格使用结构化引 用”、“在公式中定义和使用名称”。,痋痋只身一人去到了别的痋洞,在身无分文的情况下,靠着痋痋的努力,在痋洞生存了,设置二级数据有效性,再次观察名称管理器这张图,注意“抽象属 性描述”中

15、的每一个数值都是另一张表的“名 称”。,也就是说,假如把第一级数据有效性设置为 “=INDIRECT(“抽象属性描述”)”,那么使用者 在选择的时候,无论选哪一个数值,都会选到另 一张表的名称。,这时,假如在第二级数据有效性中再次使用 INDIRECT 函数,并且传入一级单元格引用,那么 在运行的时候,INDIRECT 函数就可以从有值的一 级单元格中得到一个字符串值。又因为这个字符 串值恰好又是某一张二级表的名称,所以它将返 回二级表的值序列。,于是可以用下面的方法来实现二级关联数,据有效性:,痋痋只身一人去到了别的痋洞,在身无分文的情况下,靠着痋痋的努力,在痋洞生存了,第一步,设定一级单元

16、格。将一级数据有效 性的序列来源设定为一级表的名称,并使一级表 中的每一个值都能对应一个二级表的名称。例如, 在一级数据有效性的序列来源中输入 “=INDIRECT(“抽象属性描述”)”。,第二步,设定二级单元格。在二级有效值中 使用 INDIRECT 函数,把一级单元格的引用作为参 数传入 INDIRECT 函数,例如“=INDIRECT(A2)”。,如果希望自动填充数据有效性到单元格下方的 单元格，请使用形如“A2”格式的单元格引用。 如果希望固定到某一个一级数据单元格，请使用 形如“$A$2”的单元格引用形式。,因为 Excel 表所对应的单元格区域可以在任 何一张表上,所以就实现了跨表单的二级数据有 效性。,在需要增加有效性值序列的时候,只需要在,