如何用Excel巨集搜尋比對特定字串並取回對照資料? - 汽車
By Rae
at 2013-08-02T00:00
我的問題是如何用Excel巨集搜尋比對特定字串並取回對照資料?例如:工作表1姓名學號賴育蘋A9205079黃坤明A9450808張嘉珍A9450802黃孟杰A9308011黃郁安A9108001林昌炫A9803091工作表2賴育蘋買蘋果100100120101養顏美容黃坤明買香蕉120100120102幫助消化修車子找張嘉珍2000100120201定期保養修理水電-黃孟杰1200100120202維修抽水馬達朋友拜訪-黃郁安500100120301同班老同學賴育蘋看醫生500100112001感冒吃大餐張嘉珍出錢1700100112501王品牛排黃坤明買禮物425100120103項鍊想要在工作表1後面加上花費姓名學號花費賴育蘋A9205079黃坤明A9450808張嘉珍A9450802黃孟杰A9308011黃郁安A9108001林昌炫A9803091基本上上面資料是亂打的,有點類似VLOOKUP的功能,但是在比對的部分總是會出問題我可以用函數達到我想要的目的,但是還是想要學巨集要怎麼寫...煩請大大們可以在巨集內加入些註解好讓我能理解理解
上傳檔案看看表格結構，也許SUMIF即可解決！2013-08-0313:15:09補充：～～表格結構文字及數字如何存放？如何分欄分列？還是文字串在單一儲存格，不同結構有不同解法，沒有樣本檔，什麼也做不了！2013-08-0318:56:01補充：＜參考檔＞VBA：http://www.funp.net/4842222013-08-0411:12:48補充：＜參考檔．改1版＞：http://www.funp.net/843536將四個欄位以四個陣列表達可較清楚，請自行依樣改看看，有問題再說！2013-08-0413:09:50補充：～～可以用成搜尋有字串就帶出值嗎?這是模糊比對，與公式FIND一樣意思，但準確率誤差太大，例如：G1 與G1,G10,TG1,BIG108比對都符合， 這使用VBA去執行MATCH("G1*",欄位,0)，再加INDEX即可逹到， 但有誤抓可能！～～現在是由"/"做字串的分隔，那如果關鍵字內有出現分數(例如:十六分之一1/16) 資料的建立，基本要有規則，這比使用高超的公式或VBA更重要， 規則雜混，任誰也做不了！2013-08-0416:06:43補充：＜參考檔．改2版＞：http://www.funp.net/383057～～如果儲存格內沒東西的話,一樣會比對出結果 儲存格有東西的，叫〔空字符〕""，並非真正空白格， 加一行判斷即可！ 此版加入多個不同混合〔分隔符號〕，可試試！ 另加一個公式解，請參考！（模糊比對，不建議使用此法） 也許將完整資料呈現，較好一次抓出規則，否則這樣剥蔥，花時間啊！2013-08-0416:11:12補充：瘦子大的檔案，程式碼雖然只是錄製，但只要能達到目的，依然是好用的工具！VBA最初的目的，是將重覆的工作使用程式碼來執行，錄製即是EXCEL最人性化的介面！2013-08-0419:25:20補充：EXCELVBA．依據〔關鍵字〕逐一比對文字串，取出對應值 ＜．准提部林．＞－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－■比對字串來源：Sheet212-34567890A1//A2,A3;A4-A5~A6_A7/A8/A9/A1012-34567891B1/12-34567892/C2/D312-34567893F412-34567894G112-34567895G4/G5///G6■取得結果：Sheet109-87654321A112-3456789009-87654333G412-3456789509-87654334D1 09-87654335C1 09-87654336D312-3456789209-87654337D4 09-87654338F412-34567893■程式碼： SubTEST() Dimi&,j&,y&,Arr,Brr,Crr,Drr,xDAsObject,TT,T,uMark uMark=Array(",",";","-","~","_") '字串中可能含有的分隔符號 SetxD=CreateObject("Scripting.Dictionary") y=[Sheet2!A65536].End(xlUp).Row Arr=[Sheet2!B1].Resize(y) '關鍵字組合文字串欄 Brr=[Sheet2!A1].Resize(y) '各關鍵字對應值欄 Fori=1Toy TT=Arr(i,1) Forj=0ToUBound(uMark) TT=Replace(TT,uMark(j),"/") '將不同分隔符號，全轉換為"/" Next TT=Split(TT,"/") '以"/"分割字串為陣列 ForEachTInTT IfT<>""ThenxD(T)=Brr(i,1) Next Next y=[Sheet1!B65536].End(xlUp).Row Crr=[Sheet1!B1].Resize(y) '要比對的關鍵字欄 ReDimDrr(1Toy,0) '建立對應值空陣列 Fori=1Toy '逐一填對應值至陣列 IfCrr(i,1)<>""ThenDrr(i,0)=xD(Crr(i,1)) Next [Sheet1!C1].Resize(y)=Drr '將陣列內容填入欄 EndSub－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－＜範例檔＞下載：檔案名稱：20130803a02(比對關鍵字取值).rar下載連結：http://www.funp.net/383057－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－2013-08-0419:27:44補充：＜說明＞※四個欄位可能不相鄰，故使用四個Array，可自行更改實際欄位。...
呃...我Google了一下會計帳好像有點太高級了...我需要把同學A的表格和同學B的表格對照取出想要的值而已@@2013-08-0321:23:01補充：感謝版大,這個可以運作了!不過我想請問,如果我要對照的資料不在B在D請問該如何修改呢?我把範圍改成這樣Arr=[Sheet2!A1].Resize(y,4)會比對不到東西耶2013-08-0322:10:44補充：我這樣問好了...Sheet1的B要改成DSheet2的B要改成Y這樣要如何修改呢?2013-08-0412:36:07補充：再請問一個問題因為現在是由"/"做字串的分隔那如果關鍵字內有出現分數(例如:十六分之一1/16)這樣判斷是不是會出錯?可以用成搜尋有字串就帶出值嗎?2013-08-0414:52:44補充：那請問一下可以用兩個關鍵字做字串分隔嗎?比如說現在是用"/"再加入一個"//"剛剛試了一下.如果是A1//A2/A4/A8如果儲存格內沒東西的話,一樣會比對出結果應該是搜尋到//中間的空格吧??這有辦法避免嗎?2013-08-0416:39:29補充：其實我的問題早在前面第一個檔案就已解決剩餘只是我的一些疑問罷了,,真的非常感謝我再努力研究研究--最後一個問題...這個知識+要怎麼選正解......
http://www.funp.net/826812
准大個人對此題的看法基本上應該當成會計帳裡的(日記帳)模式來處理較妥既然是以日記帳方式登錄就不適合用VLOOKUP函數來帶此函數有個限制就是無法比對相同2筆以上資料如果表二是純支出當然用SUMIF即可輕易帶出答案但我猜問題應該沒那麼簡單表二的敘述類似會記帳裡傳票的(摘要)如要加上巨集跟按鈕將答案設計到表三那設計出來的表三應該就會屬於會記帳裡的總分類帳(內頁)如再加上表四等同會記帳的試算表....不然以此題來看光有支出沒有收入是很奇怪呢2013-08-0318:35:12補充：版主所提供的範例檔2013-08-0316:51:52補充https://dl.dropboxusercontent.com/u/54264485/Sampl...這是範例的檔案原始資料是Sheet1和Sheet2我想要弄個巨集,讓Sheet1變成Sheet3那樣越看越像會計帳裡的模式.....表一等同(科目)表二A欄等同摘要第一組數字為花費金額第二組數字前6碼為年月日最後一碼為傳票號碼表三為會計帳內的總分頁帳(科目明細)....差別在將不同分頁綜合成一個頁面而已....2013-08-0319:30:26補充：准大出手了希望能搞定版主需求如果不是由/來判定的話我看還有得搞呢^^2013-08-0321:47:19補充：准大休息了吧?我來練習猜猜准大的模組看看對不對既然版主想改比對D試著改這個看看y=[Sheet1!B65536].End(xlUp).RowArr=[Sheet1!B1].Resize(y)裡面的B2個都改成D看看範圍不需要動它吧?這樣對嗎?2013-08-0323:13:35補充：很晚了老實說模組語法我不會可能須等准大出面囉一般的巨集語法還都大略看得懂較(白話文)些^^只需抓一些關鍵字比如第幾欄或是範圍由哪到哪至於其他一長串的ABC倒不需死背或理解VBA模組語法卻是需要(硬背)...很多是單一個字甚至符號來代替這也是非專業人士所能介入的領域囉^^2013-08-0414:42:23補充：抱歉插個花關公(准大)面前耍大刀....准大別介意黑假設將表一當成我上傳的參考檔(簡易會計)第二分頁表二當成參考檔的第一分頁(輸入位置從排)表三就可當成參考檔的第三分頁了....這是我日前幫一位會計小姐製作的簡易會計帳如果將此題以會計角度來設計或許能跳脫模糊比對此函數的限制....http://www.funp.net/422150用法在第一分頁查詢位置輸入要查詢的項目點按鈕即可在第二分頁查到此檔跟之前另一版主問題查詢國籍人數有點雷同......
Related Posts
