病毒安全知识,电脑网络技术,手工杀毒方法,答疑解决笔记

导航

« VMware Workstation internal monitor error临时表空间不足引起的ORA-01114错误 »

Excel使用有效性分辨中英文

  又是同事要求,说是要在Excel文档中对输入的内容进行检查,要求符合“两个中文+两个数字”的格式。这个检查可以用有效性(菜单-数据-有效性)来做,具体检查可以使用code函数,得到目标字串中第一个字符的ANSI码,汉字是双字节的,ANSI码范围高字节从B0-F7,低字节从A1-FE,所以远大于数字与英文的这ANSI码值,可以用此来分辨中文、英文和数字。做法如下,在“数据有效性”的设置-有效性条件-允许中选择“自定义”,在公式上填写如下内容:

=AND(LEN(A1)=4,CODE(MID(A1,1,1))>45216,CODE(MID(A1,2,1))>45216,CODE(MID(A1,3,1))>47,CODE(MID(A1,3,1))<58,CODE(MID(A1,4,1))>47,CODE(MID(A1,4,1))<58)

先用len函数检查单元格内字符总数是否有4个,再分别用MID函数逐一分离出字符检查是否两个中文+两个数字的格式,因为code只取目标字串中第一个字符,至于英文和数字的CODE值是多少范围,实际在excel中试做一下就出来了(a~z,0~9)。其实中文检查也不用那么精确,写大于10000也行,保证分得开。然后再在“出错警告”选择出错时(即不符合指定要求时)给出的动作与提示内容(excel的有效性还是挺强大的)。做完后一个单元格的有效性设置后,可以象公式那样拖拉复制,就可以实现对一个区域的输入内容检查的目的。

excel的数据有效性设置

  后来想想,反正只是为了给个提醒,没必要这么精确,一个一个字符检查多麻烦(幸好本例中检查的字符不多),可以改用模糊一点的方法,公式改用LENB和LEN函数,LENB得出的是字符串的字节数,而LEN返回的字符数,汉字是两个字节,英文与数字是单字节的,这样只要检查总字符数加前两个字符的字节数就可以了,公式改成

=AND(LEN(A1)=4,LENB(LEFT(A1,2))=4,LENB(RIGHT(A1,2))=2) 或者 =AND(LEN(A1)=4,LENB(A1)=6,LENB(LEFT(A1,2))=4)

至少公式短了。

  至于这个有效性,还有其它功能,比如,在“允许”那选择“序列”,再在后面的“来源”上输入具体值(用逗号分割),就可以做成下列菜单的形式,很好玩的。如果要用公式做判断,要写成逻辑判断式,开始同事为了给我演示有效性的用法,想做个要求单元格中的输入值为1的限制,在公式直接写=1或1,就是不成功,其实应该是写成A1=1的形式,其实就是一个逻辑判断式,就象前面两组公式一样:LEN(A1)=4(AND本来就是逻辑函数)。


>> 除非说明均为原创,如转载请注明来源于http://www.stormcn.cn/post/1210.html

发表评论(欢迎交流,无须注册 | 如申请友链与本站要求不符,恕不回复,见谅):

验证码

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

  • 收藏文章:
  • 新浪微博:
  • 订阅博客:
  • 腾讯微博:

最新发表

最新评论及回复

本站出现的所有广告均不代表本人及本站观点立场 | 关于我 | 网站地图 | 联系邮箱 | 返回顶部
Copyright 2008-2016 www.stormcn.cn. All Rights Reserved. Powered By Z-Blog. 闽ICP备09000343号

闽公网安备 35010202000133号