2012年12月13日 星期四

Excel VBA 搜尋多個關鍵字是否在多個字串中出現

突然會寫這篇,我自己其實也挺意外的
某一天某位學長丟了某份資料和某個題目給某位同學(?)
因緣際會之下某份題目和某份資料也讓我看到了...
就在某一天晚上,我也突發奇想,不小心就把問題複雜化了
和某位同學討論後,問了某些學長姐,就真的把已經複雜化的某樣問題給解決了(?)

好吧,我不知道自己在寫什麼了...



好了,現在要來界定問題了,如上圖,今天可樂希望Excel能做到的目標是


可以在 多個字串裡(橘色範圍) 查詢 一組(多個)關鍵字(藍色範圍) 是否有出現
如果有該關鍵字有出現,就在工作表的某一處印出(紅色範圍)
另外,是否能換另一組關鍵字(如:紫色範圍)? 是否能動態新增關鍵字(藍色範圍)?

當時在模擬這個問題的情境其實也挺詭異的:
假設今天業主有上千筆紀錄在Excel裡,希望你能隨時幫他找出想要的特定資料
而那些特定資料的關鍵字(如專有名詞之類)在Excel中的某一處,你不知道,業主也不知道
唯一知道的是,那些關鍵字是一個有名稱的範圍,也可能是一個資料表
但是不知道被哪個白癡業務塞去哪裡,也不知道範圍有多大,然後業主又想新增關鍵字
然後關鍵字可能又不只一組!! 總之就是一個頭兩個大的問題一堆...

如果對Excel夠熟的朋友們,我想應該怎麼解大家都有個底了

和同學與學長姐的討論下,我決定還是用VBA去完成它



在開工前,我先把範圍名稱設定好,方便工作,也較有彈性
首先,我把上圖多個待處理字串(橘色範圍)設定成一個範圍名稱:StringArray
再來,關鍵字部分有兩種做法,一個是以資料表作為動態關鍵字(藍色範圍):表格1 (Class)
另一個則是需要手動設定範圍,只有範圍名稱關鍵字範圍(紫色範圍):Class2

資料表作為清單的好處是新增一行或一列時,資料表會自動擴展範圍,不用再重新設定範圍名稱

然後為了方便處理,我將用來承接答案的儲存格也訂了範圍名稱(紅色範圍):Answer
但在實務面,資料量大的時候,可以採用在某一列直接將答案印出,如此較為方便處理文件


接下來開始實做:

先從開發人員那邊要一顆按鈕(按鈕1)下來擺著,就可以開始寫VBA了

 Sub 按鈕1_Click()
Sub 按鈕1_Click()
Dim strSA As String      '定義字串變數,用來承接範圍名稱
Dim strC As String
Dim strA As String
Dim Dilimiter As String

strSA = "StringArray" '待搜尋字串的範圍名稱(橘色範圍)
strC = "表格1"        '欲在(紅色範圍)中搜尋的關鍵字(藍色範圍)
strA = "Answer"       '用來承接答案的範圍名稱(紅色範圍)
Dilimiter = ","       '用來做為區隔符號的變數,方便後續文書處理用


For i = 1 To Range(strSA).Rows.Count 'Range("範圍名稱")就是一個工作表中的"範圍名稱"所代表的範圍
'第一個迴圈代表從第一筆字串搜尋到到最後一筆字串

  Range(strA).Cells(i, 1).Value = ""   '設定承接答案用的儲存格為空值,避免重複按按鈕時資料累加

    For j = 1 To Range(strC).Rows.Count '.Rows.Count 為總資料行數
    '第二個迴圈代表從第一個關鍵字開始尋找 到最後一個關鍵字
 
        If InStr(Range(strSA).Cells(i, 1), Range(strC).Cells(j, 1).Value) <> 0 Then
        
            ' InStr(S,F)為傳回F字串在S字串中的第幾個字,若無則傳回 0
            '.Cells(row,column)為儲存格中的相對位置 .Value為儲存格中的值
            
            If Range(strA).Cells(i, 1).Value = "" Then  '若承接答案用的儲存格為空,則直接放入關鍵字
                 
                 Range(strA).Cells(i, 1).Value = Range(strC).Cells(j, 1).Value
                 
            Else    '若不為空值則保留原來資料 插入分隔符號 再插入新資料
            
                 Range(strA).Cells(i, 1).Value = _
                 Range(strA).Cells(i, 1).Value & Dilimiter & Range(strC).Cells(j, 1).Value
            End If
        End If
    Next
Next

End Sub 

後來學長姐們其實也有討論是否要直接用IF條件+Find以及其他函數去完成這個答案
但是和同學討論後,還是決定用VBA解決比較快,而且看起來也比較方便整理(?)

不過作法還是因人而異啦

這篇文章能寫出來,真的要感謝厲害的同學和學長姐們一同討論,才能有這個成果阿w

2 則留言: