保持爱你 电影粤语:数据有效性的经典应用
来源:百度文库 编辑:中财网 时间:2024/05/04 07:50:42
数据有效性除了可以使用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
=COUNTIF($A$1:$A$10,A1)<2
=COUNTIF($A$1:$A$10,A1)<3
2.8.2.3
=LEFT(A1)="罗"
=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)