Anonymous
Anonymous asked in 電腦與網際網路軟體 · 2 decades ago

EXCEL驗證方式(判定含有特殊字串)

比如說有幾筆地址

A1--------中清路100號-----(正確)

A2--------台中市-------------(錯誤)

A3--------北屯區中清路----(錯誤)

要如何判定是否為有效地址?

目前我所想到的驗證方式

1.字元<5個字(XX路1號)--------------LEN(儲存格)->再排序手動移除

2.字串中至少要有"號"這個字-------???????????

2 Answers

Rating
  • 2 decades ago
    Favorite Answer

    我最多只能幫你做出有問題的地址用顏色區分出來。

    但你還是要自已去將他找出來,或是在b1利用if傳回正確的1,不正確的2,再用排序去解決。

    在b1傳回的方式

    b1輸入

    =IF(ISNUMBER(SEARCHB("號",INDIRECT(CONCATENATE("a",ROW())))),1,2)

    =IF(ISNUMBER(SEARCHB("號",a1)),1,2) <---這跟上面那段是一樣的選一種就好

    INDIRECT(CONCATENATE("a",ROW()))

    這一段其實就是等於a1,都可以往下複製。

    用顏色標記的方式

    將a全部選取

    格式-->設定格式化條件-->公式設為

    =SEARCHB("號",INDIRECT(CONCATENATE("a",ROW())))>0

    顏色設你喜歡的就好

    ---------------------------------------------------

    翻譯

    =IF(ISNUMBER(SEARCHB("號",INDIRECT(CONCATENATE("a",ROW())))),1,2)

    -----------------------------------------------------------------------------

    個別翻譯

    row 傳回這一欄的列數 如果在b1 就是 1、b2就是2

    INDIRECT 傳回一文字串所指定的參照位址,如果你在b1裡有a1這文字,就傳回a1

    SEARCHB 在文字中,找到你所指定的文字,看他出現在第幾個字元,這適用雙位元字,就是中文啦,search 就是單位元字

    ISNUMBER 是數字的話,傳回true,不是的話flase。

    -----------------------------------------------------------------------------------------

    合併翻譯

    CONCATENATE("a",ROW()) 將a及row合併,如果在b1 就會出現a1,b2就是a2

    INDIRECT(CONCATENATE("a",ROW()))傳回a1

    SEARCHB("號",INDIRECT(CONCATENATE("a",ROW()))) 找出在a1中,號出現在第幾個位元。

    ISNUMBER(SEARCHB("號",INDIRECT(CONCATENATE("a",ROW()))))

    找出在a1中,號出現在第幾個位元,是數字的話傳回true,不是的話就flase

    當然沒號的話,是不會有數字的

    IF(ISNUMBER(SEARCHB("號",INDIRECT(CONCATENATE("a",ROW())))),1,2)

    如果 ISNUMBER(SEARCHB("號",INDIRECT(CONCATENATE("a",ROW())))) 是數字的話,傳回1,不是的話傳回2。

    ------------------------------------------------------------------------------------------

    最後一次修改

    兩個條件都判別,都同時符合的話才會出現 1,其中一個不合就出現2

    =IF(AND(ISNUMBER(SEARCHB("號",INDIRECT(CONCATENATE("a",ROW())))),LEN(INDIRECT(CONCATENATE("a",ROW())))>5),1,2)

    也可輸入成

    =IF(AND(ISNUMBER(SEARCHB("號",A1)),LEN(A1)>5),1,2)

    2005-09-19 14:57:56 補充:

    謝謝你的票啊,但我希望說有更簡單的方式。

    我的方式太煩雜了,我自已看了都很頭痛。

    看到更高明的方式,可以讓自已更進一步囉。

    顏色標記的方式是看

    COUNTIF(A:A:A1)-1學來的

    但我還是喜歡用

    COUNTIF(A:A:A1)>1

    因為我真看不懂為什麼-1可以判別。

    2005-09-21 10:33:15 補充:

    其實我好像繞了一圈。

    直接在b1輸入searchb("號",a1)

    這樣就可以了。

    有號的話就是數字,沒號的話會出現#vxxx

    再去排序就可以了,不是嗎?

    ..=.="..

    2005-09-21 10:46:36 補充:

    另外,雙判別可以改成

    =IF(AND(ISNUMBER(SEARCHB("號",INDIRECT(CONCATENATE("a",ROW())))),LEN(INDIRECT(CONCATENATE("a",ROW())))>5),0,1)>0

    可以放到改顏色那,只要一個條件不符就變色。

    至於上面那個

    =SEARCHB("號",INDIRECT(CONCATENATE("a",ROW())))>0

    這個我沒跟你說清楚,這是符合條件的才會變色,不符合的不會變。

    好大的紕漏.....

    • Commenter avatarLogin to reply the answers
  • 2 decades ago

    漂亮, 深夜O度C 一票

    • Commenter avatarLogin to reply the answers
Still have questions? Get your answers by asking now.