精华内容
下载资源
问答
  • 前言step 1 进入excel vba编程界面step 2 粘贴代码step 3 运行代码step 4 删除宏代码 前言 这两天接触到excel比较,才发现...如果我们要把这个表按照班级拆分为三个表,可能一般我们都是通过筛选后复制粘贴。但这...

    前言

    这两天接触到excel比较多,才发现使用excel不仅仅是简单的复制粘贴。
    excel vb代码是非常强大,学会使用一些常用的功能,可以大大减少工作量。
    废话不多少,来看
    如下表格学生成绩表
    在这里插入图片描述

    如果我们要把这个表按照班级拆分为三个表,可能一般我们都是通过筛选后复制粘贴。但这是最土的办法,并且如果分类很多,数据量又大的话,非常浪费时间。
    来看使用excel宏轻松搞定。excel宏代码如下:

    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
    

    step 1 进入excel vba编程界面

    在excel工作表中标签处右键查看代码
    在这里插入图片描述

    进入如下界面
    在这里插入图片描述

    step 2 粘贴代码

    然后把代码粘贴窗口中如下图:
    在这里插入图片描述

    step 3 运行代码

    点击下图中红框按钮
    在这里插入图片描述
    出现下面弹框
    在这里插入图片描述
    选择需要拆分标题区域。只需要用鼠标拖动选择区域即可
    在这里插入图片描述
    确定后再选择拆分的单元格,即按照班级拆分,选择班级
    在这里插入图片描述
    确定,结果如下
    在这里插入图片描述

    step 4 删除宏代码

    如果为了保存方便,我们需要把宏代码删掉,不然保存需要保存为支持宏的格式。
    运行成功后删除宏
    找到工具 选择宏。
    在这里插入图片描述
    删除刚刚的宏即可,然后关闭代码,保存即可
    在这里插入图片描述

    展开全文
  • 有时候需要在一个工作簿中建立多个工作表,并且需要自定义工作表的名称,手动的一个个双击表名去修改非常麻烦,特别是这次我需要建立一百多个表的时候。。。。 方法一:使用数据透视表功能 方法二 使用VB写模块...

    Excel 2016
    参考https://jingyan.baidu.com/article/d7130635071d2313fdf47585.html

    有时候需要在一个工作簿中建立多个工作表,并且需要自定义工作表的名称,手动的一个个双击表名去修改非常麻烦,特别是这次我需要建立一百多个表的时候。。。。

    方法一:使用数据透视表功能

    • 插入->数据透视表

    • 选择表名数据区域,并且为新的区域

    • 把空白框中的待选项拖动到筛选框中

    • 数据透视表工具-> 选项->显示报表筛选页

    • 然后确定

    方法二 使用VB写模块工具

    总的数据表类似于

    一个课号100多个学生,然后根据一个总的成绩表来建立每个学生的这样评分信息表,如果手动去复制粘贴就显得很麻烦了

    所以就在上面的自动建立多个表的基础上,在建立的过程中把每个人的数据也自动的导入到每张表中方便使用。

    ecxel 自定义模块

    • 首先需要启用宏功能
      文件-》选项-》信任中心-》信任中心设置-》宏设置-》启用所有宏

    • excel文件另存为启用宏的工作簿

    • 建立模块
      按alt+F11-》选项栏中选择 插入-》模块

    这可以完美兼容office16,应该也可以在13上使用,但是在10版本上需要修改才可以使用,需要在excel2010上使用的直接拉到最后

    把下面的代码复制粘贴到代码框中然后保存ctrl +S,直接关闭VB编辑窗口,回到主界面。

    Sub CFGZB()
        Dim myRange As Variant
        Dim myArray
        Dim titleRange As Range
        Dim title As Variant
        Dim columnNum As Integer
        myRange = Application.InputBox(prompt:="请选择标题行:", Type:=8)
        myArray = WorksheetFunction.Transpose(myRange)
        Set titleRange = Application.InputBox(prompt:="请选择拆分的表头,必须是第一行,且为一个单元格,如:“姓名”", Type:=8)
        title = titleRange.Value
        columnNum = titleRange.Column
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Dim i&, Myr&, Arr, num&
        Dim d, k
        For i = Sheets.Count To 1 Step -1
            If Sheets(i).Name <> "数据源" Then
                Sheets(i).Delete
            End If
        Next i
        Set d = CreateObject("Scripting.Dictionary")
        Myr = Worksheets("数据源").UsedRange.Rows.Count
        Arr = Worksheets("数据源").Range(Cells(2, columnNum), Cells(Myr, columnNum))
        For i = 1 To UBound(Arr)
            d(Arr(i, 1)) = ""
        Next
        k = d.keys
        For i = 0 To UBound(k)
            Set conn = CreateObject("adodb.connection")
            conn.Open "provider=Microsoft.ACE.OLEDB.12.0;extended properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
            Sql = "select * from [数据源$] where " & title & " = '" & k(i) & "'"
            Worksheets.Add after:=Sheets(Sheets.Count)
            With ActiveSheet
                .Name = k(i)
                For num = 1 To UBound(myArray)
                    .Cells(1, num) = myArray(num, 1)
                Next num
                .Range("A2").CopyFromRecordset conn.Execute(Sql)
            End With
            Sheets(1).Select
            Sheets(1).Cells.Select
            Selection.Copy
            Worksheets(Sheets.Count).Activate
            ActiveSheet.Cells.Select
            Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Application.CutCopyMode = False
        Next i
        conn.Close
        Set conn = Nothing
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
    End Sub
    • 在空白处右键 -》自定义功能区-》勾选开发工具

    使用宏控件

    • 开发工具-》插入按钮,选择我们刚才的自定义的宏


      然后把我们的保存数据的表名称改为 数据源
      直接点击按钮根据提示操作

    如果提示文本类型错误需要把作为表名称的数据存在数字类型改为文本类型。
    然后卡一下就完成了

    后续操作

    把每个人数据建立成一个个人分析表

    • 全选工作表除了数据源,后面的操作都是在一个表操作,然后所有选中的工作表都会自动同步。
    • 把个人数据移动到合适位置
    • (第一次)复制粘贴模板

    • 如果出现格式不一致,需要在第一次粘贴过来,粘贴内容还在选中状态,点击 开始-》粘贴-》保持列宽。就可以还原原来的格式了。

    • 然后就是把刚才分表过程中分配过来的数据根据选项引用就行了

      • 在一个引用之后使用,选中刚才已经引用数据的单元格,在开始-》填充-》至同组工作表。就可以把所有的表的同个位置进行引用数据。
    • 其他的数据也是这样一个个的填,填完之后对表格区域选定,进行复制,然后不要动,直接选择 开始-》粘贴-》粘贴为值。再把引用的数据删除就ok了

    excel2010版本

    参考原作者https://jingyan.baidu.com/article/d7130635071d2313fdf47585.html

    • 数据格式报错修改方法
    把原来的title定义
    Dim title As String
    修改wei
    Dim title As Variant
    • sheets(i).delete这句代码出错,要拆分的数据工作表名称为“数据源”,而不是新建工作簿时的sheet1这种。手动改成“数据源”即可。
    展开全文
  • excel如按某一列拆分成多个工作表(支持多列),或多个Excel文件,或按某几列拆分成多个文件。 使用这个在线工具,无需vba,不用复杂的透视表,地址:http://www.excelfb.com/,点击:(按指定列)拆分成多个表 ...

      excel如按某一列拆分成多个工作表(支持多列),或多个Excel文件,或按某几列拆分成多个文件。

     

      使用这个在线工具,无需vba,不用复杂的透视表,地址:http://www.excelfb.com/ ,点击: 拆分表--》(按指定列)拆分成多个表

     操作如下图:(如果是多列,例如A列和B列,在按第几列填入A,B  用‘,’隔开A和B)

     

       

    展开全文
  • 本例介绍在excel中如何将一个工作表根据条件拆分成多个工作表。 注意:很多朋友反映sheets(i).delete这句代码出错,要注意下面第一个步骤,要拆分的数据工作表名称为“数据源”,而不是你新建工作簿时的sheet1...

    本例介绍在excel中如何将一个工作表根据条件拆分成多个工作表。

    注意:很多朋友反映sheets(i).delete这句代码出错,要注意下面第一个步骤,要拆分的数据工作表名称为“数据源”,而不是你新建工作簿时的sheet1这种。手动改成“数据源”即可。

    操作步骤:

    原始数据表如下(名称为:数据源),需要根据B列人员姓名拆分成每个人一个工作表。

    点击【开发工具】-【Visual Basic】或者Alt+F11的快捷键进入VBE编辑界面。

    如下图所示插入一个新的模块。

    如下图,粘贴下列代码在模块中:

    复制内容到剪贴板
    1. Sub CFGZB()   
    2.   
    3.     Dim myRange As Variant   
    4.   
    5.     Dim myArray   
    6.   
    7.     Dim titleRange As Range   
    8.   
    9.     Dim title As String   
    10.   
    11.     Dim columnNum As Integer   
    12.   
    13.     myRange = Application.InputBox(prompt:="请选择标题行:", Type:=8)   
    14.   
    15.     myArray = WorksheetFunction.Transpose(myRange)   
    16.   
    17.     Set titleRange = Application.InputBox(prompt:="请选择拆分的表头,必须是第一行,且为一个单元格,如:“姓名”", Type:=8)   
    18.   
    19.     title = titleRange.Value   
    20.   
    21.     columnNum = titleRange.Column   
    22.   
    23.     Application.ScreenUpdating = False   
    24.   
    25.     Application.DisplayAlerts = False   
    26.   
    27.     Dim i&, Myr&, Arr, num&   
    28.   
    29.     Dim d, k   
    30.   
    31.     For i = Sheets.Count To 1 Step -1   
    32.   
    33.         If Sheets(i).Name <> "数据源" Then   
    34.   
    35.             Sheets(i).Delete   
    36.   
    37.         End If   
    38.   
    39.     Next i   
    40.   
    41.     Set d = CreateObject("Scripting.Dictionary")   
    42.   
    43.     Myr = Worksheets("数据源").UsedRange.Rows.Count   
    44.   
    45.     Arr = Worksheets("数据源").Range(Cells(2, columnNum), Cells(Myr, columnNum))   
    46.   
    47.     For i = 1 To UBound(Arr)   
    48.   
    49.         d(Arr(i, 1)) = ""  
    50.   
    51.     Next   
    52.   
    53.     k = d.keys   
    54.   
    55.     For i = 0 To UBound(k)   
    56.   
    57.         Set conn = CreateObject("adodb.connection")   
    58.   
    59.         conn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName   
    60.   
    61.         Sql = "select * from [数据源$] where " & title & " = '" & k(i) & "'"  
    62.   
    63.         Worksheets.Add after:=Sheets(Sheets.Count)   
    64.   
    65.         With ActiveSheet   
    66.   
    67.             .Name = k(i)   
    68.   
    69.             For num = 1 To UBound(myArray)   
    70.   
    71.                 .Cells(1, num) = myArray(num, 1)   
    72.   
    73.             Next num   
    74.   
    75.             .Range("A2").CopyFromRecordset conn.Execute(Sql)   
    76.   
    77.         End With   
    78.   
    79.         Sheets(1).Select   
    80.   
    81.         Sheets(1).Cells.Select   
    82.   
    83.         Selection.Copy   
    84.   
    85.         Worksheets(Sheets.Count).Activate   
    86.   
    87.         ActiveSheet.Cells.Select   
    88.   
    89.         Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _   
    90.   
    91.                                SkipBlanks:=False, Transpose:=False   
    92.   
    93.         Application.CutCopyMode = False   
    94.   
    95.     Next i   
    96.   
    97.     conn.Close   
    98.   
    99.     Set conn = Nothing   
    100.   
    101.     Application.DisplayAlerts = True   
    102.   
    103.     Application.ScreenUpdating = True   
    104.   
    105. End Sub   
    106.   

    5、如下图所示,插入一个控件按钮,并指定宏到刚才插入的模块代码。

    6、点击插入的按钮控件,根据提示选择标题行和要拆分的列字段,本例选择“姓名”字段拆分,当然也可以选择C列的“名称”进行拆分,看实际需求。

    7、代码运行完毕后在工作簿后面会出现很多工作表,每个工作表都是单独一个人的数据。具体如下图所示:

    8、注意:

    1)原始数据表要从第一行开始有数据,并且不能有合并单元格;

    2)打开工作簿时需要开启宏,否则将无法运行代码。

    以上就是excel将一个工作表根据条件拆分成多个工作表图文教程,希望能对大家有所帮助!

    展开全文
  • 用python将一个excel工作表根据条件拆分成多个工作表(只用openpyxl库) 最近在学python,刚刚了解了openpyxl库,就想写点实用的功能。比如按某列值拆分工作表。 先放代码 from openpyxl import Workbook, load_...
  • 最近需要筛选Excel的数据 看到网上有大神做出来的Demo 但是在mac上...Sub 如何将一个Excel工作表的数据拆分成多个工作表() Dim Arr, Rng As Range, Sht As Worksheet, Dic As Object Dim k, t, Str As String, i A
  • 前面我们已经掌握了单元格、工作表、按钮关联宏的使用今天我们就做一综合案例 做案例之前我们先补充一点新知识 Cells(2,3) //表示两行三列的单元格 Range(“a1”).offset(1,2)// 下移一行,右移2行 Range(...
  • excel如何快速把多个工作表合并到一个excel表 时间:2014-10-04 14:03 来源:网管之家整理 字体:[大 中 小] 评论:0       有这么一种情况,就是我们有很多张excel,而且excel表里面的公式基本一样,我们就想...
  • Sub 工作表拆分2() '通过筛选方法完成需求,速度快,但当有合并单元格时就不能用。读者可以根据实际情况选用 Dim SplitCol As String, ColNum As Integer, HeadRows As Byte, arr, lastrow, i, ShtIndex, only As ...
  • 用VBA对一个EXCEL中的多个工作表排序

    千次阅读 2016-12-21 17:02:30
    要求:对一EXCEL中的工作表进行相同的排序 第一次进行VBA代码实际操作,先录制的宏,然后再对代码进行微调。 虽然还有所欠缺,不过是第一次,记在这里做小纪念,继续努力!
  • 表格拆分的两种方式 拆分成多个excel工作表多个excel文件 拆分Excel,可以分为3种层次:拆分excel单元格;拆分成多个excel工作表;拆分成多个excel文件 其中,第1种拆分是无法完成的,因为单元格是excel中的最小...
  • Excel:一次性创建多个工作表技巧

    千次阅读 2017-03-28 21:51:32
    在日常的工作中,经常需要在一个工作簿中,创建N多个命名比较类似或者有规律的工作表,下面是常见的一种方式,还是比较快捷的,供大家参考。 第一步:制作工作表名称 DAY DATE NAME Day1 (03-02) Day1...
  • wps表格怎么多个工作表检索内容?

    千次阅读 2020-05-29 11:04:45
    1.准备工作 2.Ctrl+F 调出查询界面 3.查询结果
  • 在使用Excel的时候,常常会遇到这样的问题:有2数据,数据1中记录了大量的信息,比如人员的培训的信息,包含姓名、时间、培训内容等,如图1所示 另外还有一张2,包含的是单纯的名单信息,如图2 ...
  • 在使用Excel的时候,常常会遇到这样的问题:有2数据,数据1中记录了大量的信息,比如人员的培训的信息,包含姓名、时间、培训内容等,如图1所示 另外还有一张2,包含的是单纯的名单信息,如图2 现在想...
  • 将excel文件按照某一列条件筛选,划分成多个工作簿,并对工作簿命名。需要自己按自己需要稍微修改下代码进行使用。
  • Sub 合并工作表() For i = 2 To Sheets.Count Sheets(i).UsedRange.Copy Destination:=Sheets(“汇总”).Range(“A” & Sheets(“汇总”).UsedRange.Rows.Count + 1) Next End Sub ALT+F11,打开代码窗口.粘贴...
  • 工作中,筛选条件肯定不止一,有时候要筛选第一列,第二列,而不是某一固定的列的条件 以上的ABCDEF列都有可能被当成筛选对象 代码如下 Sub chaifenshuju() Dim sht As Worksheet Dim k, i, j As Integer Dim irow ...
  • Python办公自动化实践1:从多个excel表中提取数据并汇总到一个工作表页中,表格,抽取,sheet 发表时间:2020-04-26 问题:从当前目录或子目录中查询符合条件的excel表格,并从这些excel表格中抽取符合条件的行汇总到...
  • 当然这两个工作表的数据都很,所以人工检索的话工作量太大。我在网上找到了可以用VLOOKUP这个函数。   昨天又有同事问我,我一点也想不起来这个函数是怎么用的了,于是又在网上查了半天,还没全部搞清楚,...
  • Oracle查询优化-03操作多个表

    千次阅读 2017-02-07 20:41:36
    OR3.3 组合相关的行3.4 IN、EXISTS 和 INNER JOIN3.5 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL JOIN 解析3.6 自关联3.7 NOT IN、NOT EXISTS 和 LEFT JOIN3.8 外连接中的条件不要乱放3.9 检测两个表中的数据及对应...
  • 工作中遇到一张统计多个字段,每个字段都有其筛选条件,写工作汇报需要每个字段的占比来进行分析数据背后的意义。如果按照原来的一次次点击每个字段的筛选条件查询将会耗费大量时间,运用数据透视能够节约三...
  • 原数据表如下(datasource.xlsx),在同一个工作簿中有三张工作表,里面都有数据。目标效果如下(allcase.xls):import xlwt import xlrd newWb = xlwt.Workbook() #创建新工作簿newWb newSheet = newWb...
  • Sub 筛选唯一值() '筛选唯一值 Dim Des As New Collection Dim Newsheet As Worksheet Dim Oldsheet As Worksheet Dim ItemX As Variant Dim Rg As Range Dim i As Integer Application....
  • 在一个工作簿中,想对某一列做筛选,并且拆分到后面的工作表里,通用代码如下: Sub chaifenshuju() Dim sht As Worksheet Dim k, i, j As Integer Dim irow As Integer '这个说的是一共多少行 Dim l As Integer Dim sht...
  • VBA单元格、工作表、工作簿

    千次阅读 2019-04-30 23:08:35
    详解VBA单元格、工作表、工作簿各种表示、方法,注意事项。
  • 时, 除了连接两个表的字段条件外,我们往往还需要一些等值或者范围 等等类似的数据筛选条件。 那么对于初学者,往往会犯一错误,就是 想当然 地 认为,ON 后面的条件是逐一执行的,因为没有了解清楚 ON 后面...
  • 团队中每成员都有不同的工作安排,为了方便管理,对不同成员进行单独的AP管理固然重要,但是当AP积累到一定程度时,每天去AP中查询时就会很耗费时间,并且容易遗漏一些AP。那么能不能实现既可以对每成员的AP...
  • excel条件筛选公式

    千次阅读 2015-02-04 19:49:02
    实现用公式多条件筛选数据,比如有很多个工作簿,每个工作簿都有以下数据。   A B C D 1 0 1 2 44 2 1 2 3 55 3 2 3 4 66 找出A列为0,B列为1,C列为2的那一行,也就是第一行,输出D列数据44。 最后...
  • Excel 高级筛选实现条件筛选数据

    千次阅读 2019-12-25 16:41:36
    由于工作原因,需要在一堆Oracle表名中筛选出需要更新结构的,可以用excel高级筛选实现该功能,故记录下来以供讨论。 实际场景: 需要在几百张表中筛选出需要更新的结构的几十张表,如果使用普通筛选方式只能...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 94,161
精华内容 37,664
热门标签
关键字:

多个工作表同时筛选