Access挑战Excel第四期：数据有效性

roych

截至发帖前，挑战赛第三期还没有收到任何提交答案，第四波又来袭了：这次就挑战的是数据有效性（2007版本以上称为“数据验证”）。
前面放了一个大招，站长觉得我估计把大家吓跑了，所以这次，我只放一个小招：

关于“吓跑大家”这一点，我就闲话几句吧：

昨晚沙漠妹纸私信给我，问我有没有参加擂台赛，便说了两句。她觉得，应该出一些Excel做不到的题目，这样才能显示出Access的优越性。
我说，其实我出的题目，Excel应该可以做出来的，只是成本很高。在我看来，如果出的题目Excel做不出来，就没什么可比性了。
例如，Excel的图表，数组公式等等，如果拿来挑战，Access基本只能靠第三方控件或者插件之类了。为完成一个不可能的任务而不惜抛弃易用的Excel，无异于钻牛角尖，完全没必要，对么？


要求就不需要多说了。正则表达式、文本函数、辅助表等……只要你能想到的都可以用，除了Access组件。提示？其实我已经说了

轻风

R兄看来对EXCEL也是了然于胸啊

tmtony

越战越勇
来自: 微社区

purplerose

E和A 来PK  ，  前提是数据量不太大的情况下了 ；那就拿出各自的优势来，必竟电子表格有它的局限性，和数据库的A来比，有些地方略有弱势了，不是做不到，而是要很费劲了；^_^  ROYCH要打第四期的擂台了，支持一下您！

pureshadow

Roy说得对，其实PK的目的不在于谁输谁赢，而在于去学习不同的解题思路。

purplerose

小妖，您好！对不起了！PK的目的不在于输赢，而在于体现同一个问题可以用不同方法来解决的，更重要的是高手对决，有福的是我们这些菜鸟了，可以从中学获得更多的知识，在此多谢了！

pureshadow

Excel函数里没有正则表达式，这个貌似是做不到了，但是Excel里有单元格……

1. 
   
2. =(IFERROR(AREAS(INDIRECT(LEFT($D$6)&1)),)=1)*(COUNT(-MID($D$6,ROW($1:$99),1))>0)*(COUNT(-(0&IFERROR(AREAS(INDIRECT(MID($D$6,ROW($1:$99),1)&1)),MID($D$6,ROW($1:$99),1))))<99)
   

复制代码

roych

pureshadow 发表于 2016-3-26 17:53
Excel函数里没有正则表达式，这个貌似是做不到了，但是Excel里有单元格……

貌似Excel 2013里直接用公式（自定义）好像有点问题，例如在编辑状态下按enter就会出现这个问题：

如果把公式写入一个单元格，然后引用该单元格作为数据验证倒是可以的（详见附件）。不知道算不算一个bug。

我想到的是将输入的字符串mid到每个单元格，然后加上三个辅助列（字母，数字，特殊字符）分别来find这些单元格，最后组合起来判断。从理论上讲应该是可行的，不过数组公式可能比较复杂。
当然，VBA相对简单些：

1. Function Invalid(ByVal rngInput As Range) As Boolean
   
2.     Dim i As Long
   
3.     Dim blnResult As Boolean
   
4.     'Const NUMBER_TEST As String = "0123456789"
   
5.     Const CHAR_TEST As String = "qwertyuioplkjhgfdsazxcvbnmQWERTYUIOPLKJHGFDSAZXCVBNM"
   
6.     Const SYMBOL_TEST As String = "~!@#$%^&*()"
   
7.     '先判断开头字母
   
8.     If InStr(1, CHAR_TEST, Left(rngInput.Value, 1)) Then
   
9.     '判断是否包含数字
   
10.         If rngInput.Value Like "*#*" Then
    
11.     '然后判断是否包含特殊字符，一次循环搞定。
    
12.              For i = 2 To Len(rngInput.Value)
    
13.                 If InStr(1, SYMBOL_TEST, Mid(rngInput.Value, i, 1)) Then
    
14.                     blnResult = True
    
15.                     Exit For
    
16.                 End If
    
17.             Next
    
18.         End If
    
19.     End If
    
20.     Invalid = blnResult
    
21. End Function

复制代码

不过同样需要用别的单元格来存储，不能定义名称，然后在有效性里使用：

pureshadow

roych 发表于 2016-3-27 13:06
貌似Excel 2013里直接用公式（自定义）好像有点问题，例如在编辑状态下按enter就会出现这个问题：

如果 ...

我试了2013版和2016版都没问题啊，你的Excel是地球版的吗？

pureshadow

用鼠标点一下数据验证里那个来源的框，就好用了……
不知是抽的哪门子的风。
这类问题肯定是用VBA最万无一失了，函数公式，实在是有点BT。