数据有效性的经典应用

数据有效性除了可以使用Excel已经提供的有效性条件，还可以使用“自定义”功能来灵活设定有效性条件。设定方法如下：

在“允许”框中，单击“自定义”。然后在“公式”编辑框中，输入计算结果为逻辑值（数据有效时为 TRUE，数据无效时为 FALSE）的公式。

下面介绍自定义数据有效性的一些经典应用。为了方便介绍，本小节的实例均假定要设定有效性的单元格区域为A1：A10，且单元格A1处于激活状态（2. 8．2．6智能列表功能除外），如图2.43：

图2.47 要设定有效性单元格区域

2．8．2．1   只允许输入数值或文本

        限定单元格区域只能输入数值，数据有效性的公式为：

=ISNUMBER(A1)

        限定单元格区域只能输入文本，数据有效性的公式为：

=ISTEXT(A1)

2．8．2．2  不允许输入重复值

        不允许在A1：A10区域输入重复值，数据有效性的公式为：

=COUNTIF($A$1:$A$10,A1)<2

        对上面的公式稍做修改将有效性条件设定为：最多只允许出现二次，则公式为：

=COUNTIF($A$1:$A$10,A1)<3

2．8．2．3  仅允许输入特定格式的文本：

        只允许输入以“罗”开始的文本，则数据有效性的公式为：

=LEFT(A1)="罗"

        只允许输入类似“23-826”、“ab-cde”的文本，则数据有效性的公式为：=COUNTIF(A1,"??-???")=1

        只能输入以“CQ”或“HN”开头的六个字符的文本，则数据有效性公式为：

=OR(AND(LEFT(A1,2)="cq",LEN(A1)=6),AND(LEFT(A1,2)="hn",LEN(A1)=6))

        只允许输入包含“龙逸凡”的文本，则数据有效性公式为：

=COUNTIF(A1,"*龙逸凡*")=1

2． 8．2．4不允许输入包含空格的文本

正如第一章所述，如果文本中包含空格将影响查找功能引用公式的引用，因而有必要对输入的数据进行检验，限制输入包含空格的文本。其数据有效性公式为：

=(LEN(A1)-LEN(SUBSTITUTE((A1)," ","")))=0

更简单的公式为：

=NOT(COUNTIF(A1,"* *")=1)

或者： =COUNTIF(A1,"* *")=0

        不允许输入前置空格，其数据有效性公式为:

=countif(a1," *")=0

        不允许输入后缀空格，其数据有效性公式为:

=countif(a1,"* ")=0

2． 8．2．5按大小顺序输入

如果希望按大小顺序输入数据（日期或数字），即后面单元格不能小于前面的数据，则数据有效性公式为：

=MAX($A$1:A1)=A1

限定只能按倒序（从大到小）顺序输入，则数据有效性公式为：

=Min($A$1:A1)=A1

2. 8．2．6智能列表功能

在数据有效性公式中设定相关的公式还可实现智能列表功能，后一单元格的有效性可以根据前一单元格数值变化而变化。

 

图2.48 使用数据有效性实现智能列表功能

如图2.48中，单元格H2的有效性序列将根据G2单元格的值变化而变化，其数据有效性公式为：

=OFFSET($A$1,1,MATCH(G2,A1:E1,)-1,10,1)

此有效性公式不太智能，有效性序列个数固定为10个，把它改进一下，以使序列个数随实际情况而变化，H2单元格数据有效性公式为：

=OFFSET($A$1,1,MATCH(G2,A1:E1,)-1,COUNTA(INDIRECT("C"&MATCH(G2,A1:E1,),0))-1,1)