办公专题

excel 按指定列进行分组并拆分成多个sheet工作表

2018-11-27  本文已影响0人  Marydon

您的一个点赞,一句留言,一次打赏,就是小编创作的动力源泉!
转载须注明原作者及出处!
》》》打赏直通车


1.情景展示

总共有20w条数据,现在需要按城镇列进行分组,并且拆分成独立的excel表格,
如何实现?

2.解决方案

实现方式:通过宏命令实现

打开excel表,ALT+F11-->打开VBA编辑器-->插入-->模块

将下列代码拷贝至弹出的窗口

Option Explicit
Option Base 1
 
Sub 按指定列分组拆分数据()
 
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
 
    Dim self As Worksheet
    Set self = ActiveSheet
    
    Dim nLastRowNum As Long
    Dim nLastColumnNum As Long
    
    Dim i As Long
    
    ' 删除其他的sheet
    
    For i = Sheets.Count To 1 Step -1
        If Sheets(i).Name <> self.Name Then
            Sheets(i).Delete
        End If
    Next i 
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True   
    
    '获取全部数据范围
    nLastRowNum = Cells(Rows.Count, 1).End(xlUp).Row
    nLastColumnNum = Cells(nLastRowNum, Columns.Count).End(xlToLeft).Column
    
    '获取标题
    Dim titleRange As Range
    Set titleRange = Application.InputBox(prompt:="请选择标题区域:将要当做标题行的每一个单元格", Type:=8)
     
    ' 有效数据开始行
    Dim nRowValidData As Long
    nRowValidData = titleRange.Row + titleRange.Rows.Count
 
    ' 获取拆分列的信息,只需要列号
    Dim splitColumnRange As Range
    Set splitColumnRange = Application.InputBox(prompt:="请选择拆分的列:选择任何一个该列的单元格即可", Type:=8)
    Dim columnNumToSplit As Long
    columnNumToSplit = splitColumnRange.Column
    
    ' 需要拆分的值字典
    Dim splitValueDict As Object
    ' 辅助字典用来保证顺序
    Dim splitValueDictReverse As Object
    Dim indexArray() As Long
    
    Set splitValueDict = CreateObject("Scripting.Dictionary")
    Set splitValueDictReverse = CreateObject("Scripting.Dictionary")
    
    Dim cellValue As String
    Dim ws As Worksheet
    
    For i = nRowValidData To nLastRowNum Step 1
        cellValue = Cells(i, columnNumToSplit).Text
        
        '1. 创建新的sheet;
        '2. 拷贝标题信息到新的sheet
        If Not splitValueDict.Exists(cellValue) Then
            splitValueDict(cellValue) = i
            splitValueDictReverse(i) = cellValue
            Set ws = Sheets.Add(After:=Worksheets(Worksheets.Count))
            ws.Name = cellValue
            self.Activate
            
            titleRange.Copy _
                ws.Range(ws.Cells(titleRange.Row, titleRange.Column), ws.Cells(nRowValidData - 1, titleRange.Column))
            
        End If
        
        ' 拷贝其他内容
        
        Range(Cells(i, 1), Cells(i, nLastColumnNum)).Copy _
            GetLastPasteRangeBySheetName(cellValue, nLastColumnNum)
                
    Next i
            
End Sub
 
Public Function GetLastPasteRangeBySheetName(ByRef SheetName As String, columnNum As Long) As Variant
    Dim wks As Worksheet
    Dim nLastRowNum As Long
 
    Set wks = ActiveWorkbook.Worksheets(SheetName)
    nLastRowNum = wks.Cells(wks.Rows.Count, 1).End(xlUp).Row
    
    Set GetLastPasteRangeBySheetName = wks.Range(wks.Cells(nLastRowNum + 1, 1), wks.Cells(nLastRowNum + 1, columnNum))
    
End Function

按F5运行;

运行

切换到excel视图;

选中要当作标题行中的所有列(标题一般都是第一行);

确定

选中要分组的所在列的任一行即可;

确定

程序运行(鼠标一直处于转圈状态)。

说明:
根据数据多少,运行一段时间,并不是死机,一般会有几分钟时间,如果你的文档有上万行那会更久。
亲测结果:固态盘,4列数据,23w条数据,需至少处理30分钟,excel会调用GPU进行运算。
保存(Ctrl + s)

3.效果展示

每一个sheet工作表的表名为分组列的类名;
每一张表都是相同的标题行。


相关推荐


赞赏码,收钱码,红包码
上一篇下一篇

猜你喜欢

热点阅读