Access挑戰(zhàn)Excel第四期：數(shù)據(jù)有效性

roych

截至發(fā)帖前，挑戰(zhàn)賽第三期還沒有收到任何提交答案，第四波又來襲了：這次就挑戰(zhàn)的是數(shù)據(jù)有效性（2007版本以上稱為“數(shù)據(jù)驗證”）。
前面放了一個大招，站長覺得我估計把大家嚇跑了，所以這次，我只放一個小招：

關于“嚇跑大家”這一點，我就閑話幾句吧：

昨晚沙漠妹紙私信給我，問我有沒有參加擂臺賽，便說了兩句。她覺得，應該出一些Excel做不到的題目，這樣才能顯示出Access的優(yōu)越性。
我說，其實我出的題目，Excel應該可以做出來的，只是成本很高。在我看來，如果出的題目Excel做不出來，就沒什么可比性了。
例如，Excel的圖表，數(shù)組公式等等，如果拿來挑戰(zhàn)，Access基本只能靠第三方控件或者插件之類了。為完成一個不可能的任務而不惜拋棄易用的Excel，無異于鉆牛角尖，完全沒必要，對么？


要求就不需要多說了。正則表達式、文本函數(shù)、輔助表等……只要你能想到的都可以用，除了Access組件。提示？其實我已經(jīng)說了

輕風

R兄看來對EXCEL也是了然于胸啊

tmtony

越戰(zhàn)越勇
來自: 微社區(qū)

purplerose

E和A 來PK  ，  前提是數(shù)據(jù)量不太大的情況下了 ；那就拿出各自的優(yōu)勢來，必竟電子表格有它的局限性，和數(shù)據(jù)庫的A來比，有些地方略有弱勢了，不是做不到，而是要很費勁了；^_^  ROYCH要打第四期的擂臺了，支持一下您！

pureshadow

Roy說得對，其實PK的目的不在于誰輸誰贏，而在于去學習不同的解題思路。

purplerose

小妖，您好！對不起了！PK的目的不在于輸贏，而在于體現(xiàn)同一個問題可以用不同方法來解決的，更重要的是高手對決，有福的是我們這些菜鳥了，可以從中學獲得更多的知識，在此多謝了！

pureshadow

Excel函數(shù)里沒有正則表達式，這個貌似是做不到了，但是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 發(fā)表于 2016-3-26 17:53
Excel函數(shù)里沒有正則表達式，這個貌似是做不到了，但是Excel里有單元格……

貌似Excel 2013里直接用公式（自定義）好像有點問題，例如在編輯狀態(tài)下按enter就會出現(xiàn)這個問題：

如果把公式寫入一個單元格，然后引用該單元格作為數(shù)據(jù)驗證倒是可以的（詳見附件）。不知道算不算一個bug。

我想到的是將輸入的字符串mid到每個單元格，然后加上三個輔助列（字母，數(shù)字，特殊字符）分別來find這些單元格，最后組合起來判斷。從理論上講應該是可行的，不過數(shù)組公式可能比較復雜。
當然，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.     '判斷是否包含數(shù)字
   
10.         If rngInput.Value Like "*#*" Then
    
11.     '然后判斷是否包含特殊字符，一次循環(huán)搞定。
    
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 發(fā)表于 2016-3-27 13:06
貌似Excel 2013里直接用公式（自定義）好像有點問題，例如在編輯狀態(tài)下按enter就會出現(xiàn)這個問題：

如果 ...

我試了2013版和2016版都沒問題啊，你的Excel是地球版的嗎？

pureshadow

用鼠標點一下數(shù)據(jù)驗證里那個來源的框，就好用了……
不知是抽的哪門子的風。
這類問題肯定是用VBA最萬無一失了，函數(shù)公式，實在是有點BT。