[算表] VBA 暗號解碼比對抓錯運算 需提高效率

看板Office作者 (☒☒)時間4年前 (2019/10/08 00:40), 4年前編輯推噓1(1推 0噓 21→)
留言22則, 2人參與, 4年前最新討論串1/1
軟體:OFFICE 365 EXCEL 版本:1908 目前正在編寫的功能是直接把一個工作表當成暗號表 A欄=暗號，B欄=解碼後的對應詞 另一個工作表則是雜亂無章的暗號跟解碼後的詞彙 然後要用暗號表去抓出文章內是否有出現暗號 而且是否解碼後的詞彙是否是正確的 例如 暗號表 A1=9527 B1=唐伯虎 A2=88 B2=掰掰 (下略) 解碼表 A1=pppij"9527"clizxj B1=pppij"唐伯X"clizxj C1=解碼偵錯：出現暗號：9527 解碼遺漏：唐伯虎 大概是這種感覺 目前編寫的代碼大致如下： For Row1 = 2 To 10000 For Row2 = 4 To 10000 Sheets("解碼").Activate If Sheets("暗號").Cells(Row1, 1).value <> "" Then If Sheets("解碼").Cells(Row2, 1).value <> "" Then Code = Sheets("暗號").Cells(Row1, 1).value '暗號 Decry = Sheets("暗號").Cells(Row1, 2).value '解碼對應詞 Decode1 = InStr(1, (Cells(Row2, 1)), Code) '判斷暗號文章是否有暗號 Decode2 = InStr(1, (Cells(Row2, 2)), Decry) '判斷解碼後是否有對應詞 If Decode1 <> 0 And Decode2 = 0 Then '若暗號文章有暗號但解碼後無對應詞 For Col = 7 To 52 If Cells(Row2, Col).value = "" Then Cells(Row2, Col).value = "解碼遺漏：" & Decry '在該文章列的7～52欄個別記下解碼遺漏的對應詞(因為可能一格內有多個不同暗號) Exit For End If Next End If End If End If Next Next 最後再用Textjoin把紀錄在7～52欄的遺漏紀錄整合在同一格內 然後為了方便辨識雜亂文章中的各個暗號 也去網路上找了幫特定字串上色的寫法 https://access-excel.tips/excel-vba-change-partial-text-color/ 我是用Macro 1 – Change partial text color using VBA Macro這個 目前的寫法雖然可以正常運作 但速度上還是有點慢，在有600條暗號的情況下，至少也要60秒才能檢查完 不知道是否有其他方式可以加快運算的速度？ 而且現在還有個問題是如果暗號中有兩個9527 但解碼後只有一個唐伯虎，這種情況也不會被判斷成解碼缺失 所以在想是不是應該寫另一個用資料剖析功能運算的版本來測試看看..... 把每個暗號個別分開，就可以連同數量不一致的也抓出來 希望有人可以提供意見，感謝 -- ※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 123.193.189.222 (臺灣) ※ 文章網址: https://www.ptt.cc/bbs/Office/M.1570466433.A.3AB.html
→
10/08 07:27, 4年前 , 1F
1.減少巢狀迴圈次數range.end().row來取得最後一筆有值的
10/08 07:27, 1F
→
10/08 07:27, 4年前 , 2F
儲存格列號
10/08 07:27, 2F
→
10/08 07:27, 4年前 , 3F
2.只針對出現暗號的儲存格，range.find或range.autofilter
10/08 07:27, 3F
→
10/08 07:27, 4年前 , 4F
3.減少range.value的寫入，而是先寫入變數內，最後再一次
10/08 07:27, 4F
→
10/08 07:27, 4年前 , 5F
性的寫入範圍內
10/08 07:27, 5F
→
10/08 07:27, 4年前 , 6F
暗號有2個9527，只有1個唐伯虎方面(len(儲存格)-len(
10/08 07:27, 6F
→
10/08 07:27, 4年前 , 7F
replace(取代9527[變數])))/len(9527[變數])，或是ubound(
10/08 07:27, 7F
→
10/08 07:27, 4年前 , 8F
split())，這樣可得知字串內有出現多少次
10/08 07:27, 8F
→
10/08 07:27, 4年前 , 9F
再判斷是否次數有相符
10/08 07:27, 9F
啊 忘了修文 迴圈上限的部分我後來有修正了 這樣就不用多檢查沒有值的欄位 range.find或range.autofilter我會再研究看看 另外 寫入變數內再拿出來的做法有範例嗎？ 網站也可 然後雖然可能不會用到了，但還是想問一下 range.value=值 跟 range="值" 在運算速度上是有差的嗎？ ※ 編輯: Kamikiri (60.248.94.55 臺灣), 10/08/2019 10:16:46
→
10/08 09:56, 4年前 , 10F
寫入再拿出來，不確定所謂的拿出來是指？一次性寫入範圍內
10/08 09:56, 10F
→
10/08 09:58, 4年前 , 11F
嗎？如果是的話寫入變數的話，變數(索引值,索引值[二維])=
10/08 09:58, 11F
→
10/08 09:59, 4年前 , 12F
判斷內原本要寫入儲存格的值；最後迴圈都執行完畢，在
10/08 09:59, 12F
→
10/08 10:00, 4年前 , 13F
range(和變數維度、個數相同的範圍）=變數 的方式寫入
10/08 10:00, 13F
→
10/08 10:06, 4年前 , 14F
range.value 或 range=值方面測試寫入10次10萬資料上
10/08 10:06, 14F
→
10/08 10:06, 4年前 , 15F
二者看來是差不多的 https://i.imgur.com/ShXYHag.jpg
10/08 10:06, 15F
感謝，剛剛在看到你的推文前上網查了一下 目前只想出以下寫法(還沒寫好寫正確 但應該類似)？ Dim Array(1,52) as Variant if 解碼有誤 Array(Row2,Col) = "解碼遺漏：" & Decry 不知道創個Dictionary寫進去之後再整批拿出來是否也相同 總之目前大致上已經有方向，應該沒問題了，感謝解答 ※ 編輯: Kamikiri (60.248.94.55 臺灣), 10/08/2019 10:22:03
→
10/08 10:22, 4年前 , 16F
如非一維的話，這要看dictionary是否可產生二維以上
10/08 10:22, 16F
→
10/08 10:23, 4年前 , 17F
一維的話，一樣range= dictionary.keys的方式
10/08 10:23, 17F
→
10/08 10:24, 4年前 , 18F
dictionary.key寫入列的話，應需用工作表函數transpose轉
10/08 10:24, 18F
→
10/08 10:24, 4年前 , 19F
置
10/08 10:24, 19F
了解，我晚點再GOOGLE研究看看 最後一問，如果你還有看到的話再麻煩解答 假如我想在解碼中使用萬用字元* 例如 95*7 讓9507~9597全部都視為應該被解碼為唐伯虎的話 是否只能使用Range.Find才有辦法？ 發現其他寫法似乎都只會把*視為一般字元而非萬用字元 萬分感謝 ※ 編輯: Kamikiri (60.248.94.55 臺灣), 10/08/2019 11:33:39
→
10/08 11:59, 4年前 , 20F
like或range.autofilter 方面可用萬用字元
10/08 11:59, 20F
推
10/10 17:57, 4年前 , 21F
一樣推薦Dictionary，30萬筆的比較時，效率還可以；超過
10/10 17:57, 21F
→
10/10 17:57, 4年前 , 22F
的時候一樣有效能問題，要再多做些手段
10/10 17:57, 22F
‣ 更多 Kamikiri 的文章
文章代碼(AID): #1Tcsg1Eh (Office)