VBA教程:如何將一個(gè)二維表轉(zhuǎn)為一維表?
作者:E圖表述來源:部落窩教育發(fā)布時(shí)間:2022-09-28 16:05:16點(diǎn)擊:1626
編按:
哈嘍,大家好,在之前的教程中給大家分享過如何用PQ來將二維表轉(zhuǎn)為一維表,今天來教大家一個(gè)一勞永逸的辦法——VBA,趕緊來看一看吧!
如果將一維數(shù)據(jù)轉(zhuǎn)化成二維數(shù)據(jù),最好最直接的方法就是使用數(shù)據(jù)透視表。那么今天的問題是,如果將二維數(shù)據(jù)轉(zhuǎn)化成一維數(shù)據(jù)呢?作者E圖表述就遇到了一位網(wǎng)友的求助,他的數(shù)據(jù)表如下圖所示:
他的需求是將這樣的一組數(shù)據(jù),轉(zhuǎn)化成下面的格式:
因?yàn)樵磾?shù)據(jù)中存在合并單元格,如果我們使用函數(shù)的做法來做,會(huì)相當(dāng)?shù)睦щy(即便沒有合并單元格,使用函數(shù)操作二維表轉(zhuǎn)一維表同樣沒那么容易)。這里作者E圖表述就給大家介紹個(gè)方法,希望在工作中能夠給大家?guī)硪恍﹩⒌稀?/span>
打開工作表后,按ALT+F11組合鍵,打開VBE界面,在工程窗口單擊鼠標(biāo)右鍵,按下圖插入一個(gè)模塊。
選中模塊,在屬性窗口(如果你沒有屬性窗口,可以按F4彈出),在名稱處直接改成你要的字即可。
雙擊剛才添加的模塊,使得代碼區(qū)是模塊的代碼輸入?yún)^(qū)域。將下面的代碼復(fù)制到你的代碼區(qū):
Sub 二轉(zhuǎn)一()
With Sheets("轉(zhuǎn)化前") '''使用工作表《轉(zhuǎn)化前》
arr = .UsedRange '''利用UsedRange屬性,將使用的單元格區(qū)域放入數(shù)組arr中,形成二維數(shù)組
End With '''with結(jié)束語句
'''在數(shù)組中將合并單元格造成的空值填充上值
For i = 2 To UBound(arr) '''循環(huán)變量i,在第2行開始,循環(huán)到最后一行數(shù)據(jù),循環(huán)第一維度
If arr(i, 1) = "" Then '''判斷語句,如果數(shù)組第一列的值為空,則
arr(i, 1) = arr(i - 1, 1) '''此數(shù)組元素值等于上一行的數(shù)組元素值
End If '''判斷語句結(jié)束
Next i '''循環(huán)語句結(jié)束
Set d = CreateObject("scripting.dictionary") '''定義d為字典腳本
For i = 2 To UBound(arr) '''再次逐行遍歷數(shù)組arr
For j = 3 To UBound(arr, 2) - 1 '''循環(huán)變量j,從第3列開始,逐列循環(huán)數(shù)組arr的第二維度,不包含最后一列
If arr(i, j) > 0 Then '''如果行列交叉點(diǎn)上的數(shù)組元素值大于0,則說明此值我們應(yīng)該羅列
s = arr(i, 1) & "|" & arr(i, 2) & "|" & arr(1, j) '''將型號、顏色、規(guī)格,合并為一個(gè)字符串s,用|間隔
d(s) = arr(i, j) '''將s字符串裝入字典d的key中,并且賦值對應(yīng)item值為對應(yīng)的數(shù)值
End If '''判斷語句結(jié)束
Next j '''j循環(huán)語句結(jié)束
Next i '''i循環(huán)語句結(jié)束
With Sheets("轉(zhuǎn)化后") '''使用工作表《轉(zhuǎn)化后》
.UsedRange.ClearContents '''使用ClearContents方法,將被使用單元格區(qū)域清空
.[A1] = "型號" '''A1單元格賦值字符串“型號”
.[B1] = "顏色" '''同上,賦值B1單元格
.[C1] = "規(guī)格" '''同上,賦值C1單元格
.[D1] = "數(shù)值" '''同上,賦值D1單元格
k = 1 '''設(shè)置一個(gè)變量k,初始值為1
For Each dic In d.keys '''在字典d中循環(huán)每一個(gè)字典元素dic
k = k + 1 '''計(jì)數(shù)器,每循環(huán)一次,累加1。作為單元格行號使用
'''Split函數(shù):按指定字符,拆分字符串,成為一個(gè)一維數(shù)組,數(shù)組編號從0開始
.Cells(k, 1) = Split(dic, "|")(0) '''對應(yīng)行號k的A列單元格,賦值拆分后的一個(gè)值
.Cells(k, 2) = Split(dic, "|")(1) '''同上理,賦值第二個(gè)值
.Cells(k, 3) = Split(dic, "|")(2) '''同上理,賦值第三個(gè)值
.Cells(k, 4) = d.Item(dic) '''對應(yīng)的item值,賦值給D列單元格
Next '''結(jié)束for each循環(huán)
End With '''with結(jié)束語句
Erase arr '''釋放arr數(shù)組
Set d = Nothing '''釋放字典d
End Sub '''工程結(jié)束
點(diǎn)擊運(yùn)行按鈕,就可以完成操作。
代碼里給大家也附上了注釋,如果你想學(xué)習(xí)代碼原理,這將是一個(gè)很好的過程。
寫在最后:工作表需另存為.XLSM格式,否則代碼會(huì)保存不上的。
好啦,以上就是今天的所有內(nèi)容,感謝你的觀看。
本文配套的練習(xí)課件請加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
如何提取唯一值?試試TEXTJOIN函數(shù)搭配VBA自定義!
八大查找函數(shù)公式,輕松搞定數(shù)據(jù)中的多條件查找
版權(quán)申明:
本文作者E圖表述;同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集!(建議收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請收好!