Excel開個掛,數據整理效率暴增!#Excel小思維
大家好,我是秋小葉~
《斷舍離》全球長期熱銷,整理理念也越來越流行。朋友圈中,你一定看到過整理相關的課程推廣。
你知道嗎?Excel 表格的數據也是需要整理的。對於一些不規範的數據,如果不加以整理,根本沒有辦法進行計算分析。而 Excel 中最難的一部分,應該就是和各種雜七雜八的數據奮勇鬥爭了。
這不,有位妹紙拋出了一個難題:
從系統導出的表格中有 ID 1 和 ID 2 兩列數據,兩列數據中都包含了一個個的 ID,各個 ID 之間用逗號分隔開來。
現在我需要從兩列 ID 中挑出重複出現的 ID,要怎麼做呢?
為了讓你看得更清楚,這還是經過加工的數據。原始數據中,每個 ID 都很長,而且有上萬行。如果肉眼比對,手工輸入,一個星期都搞不完。怎麼辦?
要解決這個問題,有 3 種不同的思路:
思路一:
先對兩列數據執行分列,按照逗號拆分到兩個表格中,再用數組公式去解決。這會非常複雜,而且運算量太大表格可能會很卡。
思路二:
分列到不同的表格中,再用公式添加行號作為分類標籤。再用公式合併到一張表格中,用透視表的差異分析法。這樣中間步驟太多,很難解釋清楚。
思路三:
直接用 VBA 寫幾行代碼自定義一個函數,讓妹紙複製粘貼到自己的Excel表中,然後用一個函數批量搞定。
是的,今天就是分享一個 VBA 自定義函數的用法。
即使你沒有編程基礎也別擔心,只要學會複製粘貼,用別人寫好的成熟代碼,也能解決很多問題,達到事半功倍的目的。
把代碼粘貼到模塊
打開 Excel 表格後,同時按下 ALT+F11 鍵,可以打開代碼編輯窗口。插入一個模塊:
然後將下麵灰色區域的代碼完整的複製粘貼到模塊窗口中(沒有計算機編程基礎可以忽略具體含義),保存:
Function 提取重複(rg1 As Range, rg2 As Range) "提取兩個長串數據中重複的值
Dim arr1, arr2
arr1 = Split(rg1, ",") "將長串數據1,按逗號拆分成一組數據
arr2 = Split(rg2, ",") "將長串數據2,按逗號拆分成一組數據
a = ""
For h = 0 To UBound(arr1) "逐個比對兩組數
For i = 0 To UBound(arr2)
If arr1(h) = arr2(i) Then
a = a & arr2(i) & ","
End If
Next
Next
Dim arr, d As Object "剔除結果中的重複值
Set d = CreateObject("scripting.dictionary")
arr = Split(Mid(a, 1, Len(a) - 1), ",")
For i = 0 To UBound(arr)
d(arr(i)) = ""
Next i
提取重複 = Join(d.keys, ",")
End Function
輸入函數公式
上面的代碼實際上是設計了一個自定義函數,它的語法結構非常簡單:
=提取重複(數據 1,數據 2)
(功能是將數據 1 和數據 2 分別按逗號拆分,並互相比對,提取出重複出現在兩個數據中的 ID,如果多次出現,剔除重複只保留其中一個)
怎麼用?超級簡單,就跟你使用普通的函數公式一樣:
GIF/29K
通過這個自定義的函數,幾秒鐘的時間,就能夠把大批量的數據整理好。那效率就跟開掛了一樣。
保存數據
需要特別留意的時,如果要保留這個自定義函數及計算結果,需要將工作簿另保存成特殊的文件類型:Excel 啟用宏的工作簿,擴展名是 .xlsm。
如此一來,代碼就會跟隨 Excel 文件,去到其他電腦也能繼續使用。
好啦。本文實際上只教你三板斧:
複製粘貼代碼
輸入自定義函數
保存帶宏代碼的工作簿
設計自定義函數,僅僅是 VBA 應用的冰山一角而已。
如果你的工作中有大規模的重複操作,需要反覆執行,你又有一點點計算機基礎,可以考慮學點 VBA,享受享受點一點就完成別人半個月工作量的愜意。
但即使不會 VBA,掌握常用的操作也可以大大提升工作的效率。
我們的和秋葉一起學Excel課程,課程內容完全聚焦於普通表哥表妹的日常工作痛點,高效解決各種數據表格問題。
還有各種讓你大呼過癮的騷氣小技巧,比如讓表格的行列互換:
GIF/223K
讓你像打怪升級一樣,學Excel學到根本停不下來!!!
※你的PPT軟體和別人的不一樣?一招教你快速認全!
※離2017結束只剩一個月,你有想辭職嗎?
※用好1個鍵,效率就能飛起,你知道是哪一個嗎?
※當高手在看PPT的時候,他們在看些什麼?
※怎麼辦,明明點了顏色,表格線框顏色卻沒變?
TAG:秋葉PPT |