大众计算机学习网

专门提供各种电脑教程

使用Excel数组公式限制单元格只能输入字母和空格

Admin | 2008-1-2 13:07:13 | 被阅次数 | 10678

        如何使用实据有效性限制单元格只能输入小写字母与空格?

        下面以A1到A100单元格为例子做如下说明

        用鼠标选定A1:A100区域,点"数据"->"有效性"->"设置",在"允许"下面选择"自定义",在"公式"下面的框中输入数组公式:

=AND(CODE(MID(SUBSTITUTE(A1," ",""),ROW(INDIRECT("$1:$"&LEN(SUBSTITUTE(A1," ","")))),1))>=97,CODE(MID(SUBSTITUTE(A1," ",""),ROW(INDIRECT("$1:$"&LEN(SUBSTITUTE(A1," ","")))),1))<=122)

        公式以Ctrl+Shift+回车结束。

        点"确定",这时会出现一个"公式目前包含错误,是否继续?"的对话框,点"是"就可以了。

        如下图:

公式详细说明:

        因为空格的代码值为32,不在小写字母的代码值范围(97-122)内,并且空格的出现位置和数量不定,所以不好用它的代码值来判断。这里先用替换函数将空格全部替换掉SUBSTITUTE(A1," ",""),然后用LEN函数计算出替换了空格后的字符串的长度,用MID逐个取出字符串中的单个字符,用CODE计算其代码值,用AND函数判断其代码值是否在97至122之间。公式中还用到了一个单元格地址函数INDIRECT和行号函数ROW,主要用于取得字符串的开始和结束位置。
注意:公式中SUBSTITUTE(A1," ","")里面的两个双引号,前面一个双引号里面有一个空格,而后面一个双引号里面没有空格。

知识扩展:

如何利用数据有效性限制单元格只能输入大写字母

        如限制A列

        选择A列,“数据”-“有效性”-“设置”。“允许”里选“自定义”;“公式”=SUM(IF((CODE(MID(A1,ROW(INDIRECT("$A$1:$A$"&LEN(A1))),1))>64)*(CODE(MID(A1,ROW(INDIRECT("$A$1:$A$"&LEN(A1))),1))<91),1,0))=LEN(A1)

同时按Ctrl+Shift+Enter结束。不用理会错误。

        公式解释:

        1 ROW(INDIRECT("$A$1:$A$"&LEN(A1)))       取得以1为初值,以A1长度为终值,步长为1的等差数组。如A1为“ASDF”,就为{1,2,3,4}

        2 MID(A1,ROW(INDIRECT("$A$1:$A$"&LEN(A1))),1)) 就产生了A1每个字母的集合。如“ASDF”,就为{"A","S","D","F"}。

        3 CODE(MID(A1,ROW(INDIRECT("$A$1:$A$"&LEN(A1))),1)) 就是每个字母的ASCII码。

        4 (CODE(MID(A1,ROW(INDIRECT("$A$1:$A$"&LEN(A1))),1))>64)*(CODE(MID(A1,ROW(INDIRECT("$A$1:$A$"&LEN(A1))),1))<91) 判断是否为“A”~“Z”之间,因为“A”ASC码65;“Z”ASC码为90。

        5 SUM(IF((CODE(MID(A1,ROW(INDIRECT("$A$1:$A$"&LEN(A1))),1))>64)*(CODE(MID(A1,ROW(INDIRECT("$A$1:$A$"&LEN(A1))),1))<91),1,0)) 大写字母的数量。

        6 SUM(IF((CODE(MID(A1,ROW(INDIRECT("$A$1:$A$"&LEN(A1))),1))>64)*(CODE(MID(A1,ROW(INDIRECT("$A$1:$A$"&LEN(A1))),1))<91),1,0))=LEN(A1) ,全为大写字母返回True,否则为False。
 
        重点:ROW(INDIRECT("$A$1:$A$"&LEN(A1)))   返回数组{1: len(a1)