34.6.1 Excel数据有效性的高级应用【动态二级级联菜单/如百度输入框Ajax效果的下拉菜单/圈出无效数据】

声明：本博客分类《Excel2010》中的 标题带章节号如 34.6 的文章均为

本人阅读 Excel Home书籍作品《Excel2010应用大全》的读书笔记

 

这本书非常好，对Excel2010讲解的非常详细和专业

但是就其中的例子，可能是因为书的篇幅的缘故，没有对公式进行详细说明

写这篇文章一方面是帮助自己深化对例子核心公式和思路的剖析；

另一方面，是对书籍中公式的一个啰嗦的解析。

 

小小感慨下：

Excel2010还是不够强大，如本文中的这些用例，如果能做成界面操作，不需函数，三两点键即可自动完成的该多好啊。

 

34.6 数据有效性的高级应用示例

1，圈释无效数据

2，限制输入重复数据

3，制作动态下拉菜单（一级，二级关联）

4，设置两级级联菜单

5，根据关键字动态设置数据有效性

 

 

1，圈释无效数据

 

实现步骤：

设置需求区域的数据有效性——自定义——公式

=SUMIF($B$5:$B$17,$B5,$F$5:$F$17)=SUMIF($B$5:$B$17,$B5,$G$5:$G$17)

 

 

公式解释：

=SUMIF($B$5:$B$17,$B5,$F$5:$F$17)=SUMIF($B$5:$B$17,$B5,$G$5:$G$17)

首先公式有效区域为 F5：G17

注意公式中的绝地引用和混合引用，最后达到的效果是什么样的呢？

你点击某一单元格，看看它的数据有效性会发现：

如F14的有效性为：

=SUMIF($B$5:$B$17,$B14,$F$5:$F$17)=SUMIF($B$5:$B$17,$B14,$G$5:$G$17)

这就是因为，第二个参数$B5为混合引用，向右复制公式不改变引用关系，但是向下会改变，所以$B5变成了$B14

从而满足了我们的需求

 

参考：单元格引用类型及特性

引用类型	A1样式	R1C1样式	特性
绝对引用	$A$1	R1C1	向右向下复制公式不改变引用关系
混合引用	A$1	R1C[*]	向下复制公式不改变引用关系
混合引用	$A1	R[*]C1	向右复制公式不改变引用关系
相对引用	A1	R[*]C[*]	向右向下复制公式都会改变引用关系

 

SUMIF（）函数是对满足条件的单元格求和

SUMIF（range，criteria，【sum_range】）

因此公式的左边结果为：

对【借方金额】求和，按照【凭证号】分别等于1 2  3 ...进行求和

同时公式的右边结果为：

对【贷方金额】求和，按照【凭证号】分别等于1 2  3 ...进行求和

前面再加一个等于号，就是根据二者是否相等而返回 True Or False

 

这个示例涉及的要点：

混合引用，数据有效性之圈释无效数据，SUMIF函数

 

 

2，限制输入重复数据

 

 

有效性函数：=SUMPRODUCT(N($B$4:$B$12=B4))=1

注意，这里还是对B4运用了相对引用，也就是说它会往下变成 B5  B6  B7 等

如果本行输入的身份证号码超过2个，即不符合=1的有效性，将警告

 

这里之所以用SUMPRODUCT函数进行统计而非COUNTIF（B4：B12，B4）=1

是因为，身份证号为18位，而Excel的运算精度为15位，所以COUNTIF函数对身份证号码第16位以后不同的号码误作为相同号码进行统计

 

 

3，制作动态下拉菜单

首先，【油品品种】设置数据有效性=名称【油品】

而名称【油品】的定义为：=OFFSET(价格表!$A$2,1,,COUNTA(价格表!$A:$A)-2)

这样就实现了下拉菜单

 

而如何实现，根据下拉菜单选择菜单项后，单价跟着改变呢？

定义单价列的公式为：=IF(B5="",,VLOOKUP(B5,价格表!$A$3:$B$6,2,0))

 

未完待续

4，设置两级级联菜单

5，根据关键字动态设置数据有效性