效率提高99.99%的工作表極速拆分法!
?
作者:夏雪來源:部落窩教育發(fā)布時間:2018-09-27 11:28:40點(diǎn)擊:7203
編按:
工作表有匯總就有拆分。如何快速拆分工作表呢?文章分享了兩個方法。第一個方法提供給那些希望感覺火箭速度的人,第二種方法提供給那些喜歡坐高鐵的人。第一種方法將用到VBA。對VBA小白的讀者不要怕,作者貼心的提供了代碼文件和具體的代碼解釋。收藏本篇文章,以后可以直接下載代碼簡單修改幾個參數(shù)值就能用于自己的工作表拆分。
各位小伙伴有沒有遇到過這樣的問題:當(dāng)我們把所有的信息匯總在一張表里后,又需要將這張大表按某一條件再拆分成多個工作表。那怎么才能實現(xiàn)呢?可能最笨的方法就是在原工作表篩選數(shù)據(jù)然后復(fù)制粘貼到新工作表,不過這種方法不適合數(shù)據(jù)多的案例,并且新工作表也需要一一重命名,顯得繁瑣。今天就給大家介紹兩種快捷實用的工作表拆分方法。
如圖,現(xiàn)在要把這個工作表的內(nèi)容按城市拆分成多個工作表。
第1種:極速拆分——VBA(文中提供有代碼)
VBA是EXCEL處理大量重復(fù)工作最好用的工具。不過很多人對VBA一竅不通,所以今天給大家分享一段代碼,并且詳細(xì)解釋了如何根據(jù)實際表格修改代碼值,方便大家在工作中使用。
(1)按住Alt+F11打開VBA編輯器,點(diǎn)擊“插入”菜單下的“模塊”。
(2)在右側(cè)代碼窗口輸入下列代碼。不想動手輸入的可以加群下載已經(jīng)準(zhǔn)備好的代碼文件,直接復(fù)制粘貼即可。
Sub 拆分表()
Dim i, iRow, iCol, t, iNum As Integer, sh As Worksheet, str As String
Application.ScreenUpdating = False
With Worksheets("Sheet1")
iRow = .Range("A65535").End(xlUp).Row
iCol = .Range("IV1").End(xlToLeft).Column
t = 3
For i = 2 To iRow
str = .Cells(i, t).Value
On Error Resume Next
Set sh = Worksheets(str)
If Err.Number <> 0 Then
Set sh = Worksheets.Add(, Worksheets(Worksheets.Count))
sh.Name = str
End If
sh.Range("A1").Resize(1, iCol).Value = .Range("A1").Resize(1, iCol).Value
iNum = sh.Range("A" & Rows.Count).End(xlUp).Row
sh.Range("A" & iNum + 1).Resize(1, iCol).Value = .Range("A" & i).Resize(1, iCol).Value
Next i
End With
Application.ScreenUpdating = True
End Sub
代碼解析:
這里用紅色文字表示需要根據(jù)實際修改的代碼參數(shù);'用于表示注釋,其后的文字并不影響代碼的運(yùn)行,只是用于說明代碼的。這里特意用灰色表示注釋文字。
Sub 拆分表 '文件名稱,根據(jù)自己的文件名修改
Dim i, iRow, iCol, t, iNum As Integer, sh As Worksheet, str As String
Application.ScreenUpdating = False '關(guān)閉屏幕刷新
With Worksheets("Sheet1") '雙引號內(nèi)是工作簿名稱,根據(jù)實際工作簿名稱修改
iRow = .Range("A65535").End(xlUp).Row '從A列的最后一行開始向上獲取工作表的行數(shù),一般只改動Range中的列參數(shù),如要工作表有效區(qū)域是從B列開始的,值就是B65535
iCol = .Range("IV1").End(xlToLeft).Column '從最后列(IV)第1行開始向左獲取工作表的列數(shù),一般只改動Range中的行參數(shù),如要工作表有效區(qū)域是從第2行開始的,值就是IV2
t = 3 't為列數(shù),設(shè)置依據(jù)哪一列進(jìn)行拆分,譬如,如果是按E列拆分,這里就是t=5
For i = 2 To iRow 'i為行數(shù),設(shè)置從第幾行開始獲取拆分值,要根據(jù)工作表實際改動
str = .Cells(i, t).Value '獲取單元格(i, t)的值作為拆分后的表格名稱
On Error Resume Next
Set sh = Worksheets(str) '創(chuàng)建以上述獲取值為名的工作表
If Err.Number <> 0 Then '如果不存在這個工作表則添加一個并命名
Set sh = Worksheets.Add(, Worksheets(Worksheets.Count))
sh.Name = str
End If '如果存在這個工作表
sh.Range("A1").Resize(1, iCol).Value = .Range("A1").Resize(1, iCol).Value '獲取工作表標(biāo)題,一般只改動Range的列值和Resize中的行值,譬如工作表的標(biāo)題是從B列第3行開始的,則這句代碼就變成 sh.Range("B1").Resize(3, iCol).Value = .Range("B1").Resize(3, iCol).Value '
iNum = sh.Range("A" & Rows.Count).End(xlUp).Row '一般只改Range中的列值,如工作表是從B列開始的,這里就變成Range("B" & Rows.Count).End(xlUp).Row
sh.Range("A" & iNum + 1).Resize(1, iCol).Value = .Range("A" & i).Resize(1, iCol).Value
'在新表中粘貼工作表數(shù)據(jù),一般只改動Range的列值,若工作表是從B列開始的,則就改成B變成Range("B" & iNum + 1).Resize(1, iCol).Value = .Range("B" & i).Resize(1, iCol).Value
Next i
End With
Application.ScreenUpdating = True '打開屏幕刷新
End Sub
(3)代碼輸入完成后,點(diǎn)擊菜單欄里的“運(yùn)行子過程”。這樣工作表就拆分完成了。
完成如下:
這樣就通過這種方式一鍵完成工作表拆分了。
第2種:常規(guī)拆分——數(shù)據(jù)透視表
數(shù)據(jù)透視表真的非常好用,它不僅在數(shù)據(jù)統(tǒng)計分析上擁有絕對的優(yōu)勢,而且利用篩選頁也可以幫助我們實現(xiàn)拆分工作表的功能。步驟如下:
(1)選擇數(shù)據(jù)源任一單元格,單擊插入選項卡下的“數(shù)據(jù)透視表”。位置選擇現(xiàn)有工作表,單擊確定。
(2)把要拆分的字段“城市”放到篩選字段,“日期”“業(yè)務(wù)員”字段放在行字段,“銷售額”放在值字段。
(3)修改數(shù)據(jù)透視表格式,便于在生成新工作表的時候形成表格格式。
選擇“數(shù)據(jù)透視表工具”下方“設(shè)計”選項卡里的“報表布局”下拉菜單的“以表格形式顯示”。
選擇“數(shù)據(jù)透視表工具”下方“設(shè)計”選項卡里的“報表布局”下拉菜單的“重復(fù)所有項目標(biāo)簽”。
選擇“數(shù)據(jù)透視表工具”下方“設(shè)計”選項卡里的“分類匯總”下拉菜單的“不顯示分類匯總”。
完成結(jié)果如下:
(4)最后把透視表拆分到各個工作表。選擇“數(shù)據(jù)透視表工具”下方“分析”選項卡“數(shù)據(jù)透視表”功能塊里的“選項”下拉菜單的“顯示報表篩選頁”,選定要顯示的報表篩選頁字段為“城市”。
(5)為了方便后續(xù)處理,把數(shù)據(jù)透視表修改成普通表格。選擇第一個工作表 “北京”,按住Shift,點(diǎn)擊最后一個工作表“重慶”,形成工作表組。這樣就能批量對所有工作表進(jìn)行統(tǒng)一操作。
全選復(fù)制粘貼為值。
刪除前兩行,再把日期這列列寬調(diào)整一下就完成了。結(jié)果如下:
數(shù)據(jù)透視表這種方法比較容易上手,但是步驟比較多,而VBA操作簡單,但需要學(xué)習(xí)的東西很多。大家根據(jù)自己實際情況選擇使用,覺得不錯的話點(diǎn)贊吧!
另外想練習(xí)是否看懂了VBA代碼修改的讀者可以利用文章配套的拆分表素材2進(jìn)行練習(xí)。
本文配套的練習(xí)課件請加入QQ群:264539405下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
《 Excel數(shù)據(jù)透視表入門教程1:按需匯總數(shù)據(jù)和工作表拆分》
《Excel數(shù)據(jù)透視表系列教程第一節(jié):匯總業(yè)績》
《Excel數(shù)據(jù)透視表系列教程第六節(jié):分組問題》
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!