|
如何使用实据有效性限制单元格只能输入小写字母与空格？
下面以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)
[C语言系列]使用C#实现ADSL自动拨号 [Web开发]狂人采集器规则使用详解
[电脑技术]windows7快捷键使用大全 [办公软件]PowerPoint模板使用经验之谈
[办公软件]如何在office(PowerPoint,Word,Excel)中制作带圈的… [办公软件]批量删除Office文档(word,excle,powerpoint)中的超…
[办公软件]如何在PowerPoint中使用(插入)Media Player控件播… [办公软件]如何在PowerPoint中使用(插入、创建)书签及书签的…
[办公软件]如何在PowerPoint中插入(使用)条形码 [办公软件]Office(Word,Excel)密码破解软件(Office Password…
|
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
