Excel进阶之路：工作簿与工作表 |
您所在的位置：网站首页 › excel中工作簿和工作表的关系是一样的吗 › Excel进阶之路：工作簿与工作表 |
Excel进阶之路：工作簿与工作表
|
Excel进阶之路：工作簿与工作表
2023-11-06
Excel
约 2257 字
预计阅读 5 分钟
文章目录
二者关系
工作表是Excel中数据的载体，一个工作簿下可以有多个工作表，工作表叫做Worksheet，也可以叫Sheet。而表格Table是工作表中的一个区域，所以工作簿、工作表、表格三者关系如图所示：
一个保存的工作簿名称就是文件名，同样的，每个工作表也有名称，默认情况下，新建工作簿中的工作表总是以”Sheet1“，”Sheet2“……自动命名，显示在底部的标签中，那如何获取当前活动的工作表名称呢？我们可以使用CELL函数获取工作表的完整路径，再经过一定的处理获取工作表名称： 1 2 3 4 5 6 // 获取完整名称: C:\Users\vince\Desktop\[demo.xlsx]Sheet3 =CELL("filename") // 获取工作簿名称：demo.xlsx =REPLACE(REPLACE(CELL("filename"), FIND("]",CELL("filename")), 99,), 1,FIND("[", CELL("filename")),) // 获取工作表名称：Sheet3 =REPLACE(CELL("filename"), 1, FIND("]", CELL("filename")), "")⚠CELL函数只对保存过的Excel工作簿有效，尚未保存的新建工作簿则无法获取信息。 获取工作表的总数和当前工作表顺序编号Excel2013中新增了两个函数SHEETS和SHEET，用于返回当前工作簿的工作表数量和当前工作表的顺序编号，假设共有Sheet1,Sheet2,Sheet3三个工作表，在Sheet2任意单元格使用公式： 1 2 =SHEET() // 2 =SHEETS() // 3 合并与拆分话说天下大事，分久必合，合久必分，Excel的工作簿和工作表也一样，日常工作中可能会频繁涉及工作簿、工作表的合并与拆分，相信这个问题也苦恼了很多”表哥“和”表妹“，现在，就把这个问题作一次全面总结，以后再遇到直接来这里抄作业。 ⚠这里合并拆分要求的每个工作簿、工作表具有相同的表头 比如有多个工作簿，现在需要合并成一个工作簿，合并后的每一个工作表对应合并前的工作簿的内容，然后要将这些工作表继续合并成一个工作表，可以看出这是一个循序渐进的过程，后面合并也是按照这个顺序进行讲解。 合并与拆分是一个互逆的过程，一个工作表要根据某列拆分成多个工作表，再将这些工作表拆分到不同的工作簿，所以拆分方面先介绍工作表拆分，再介绍工作簿拆分。话不多说，直接开始。 工作簿合并、拆分人工操作合并只能处理文件数量不多的场景，通过工作表的移动复制功能实现合并，对待合并的工作簿依次操作：
此方法比较费劲，工作簿数量多的话不推荐。 工作表合并、拆分人工对工作表进行合并、拆分同样只能应对数据量较小的场景，通过Ctrl+c和Ctrl+v如果是大量数据，建议使用VBA实现。 这里附上VBA实现代码，具体这段VBA如何使用，请参考VBA章节。 附录：VBA源码 构造类模块 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 '新建类模块 Power Option Explicit Private Function RegReplace(ByVal Expression, Optional Pattern = "\||\/|\|""|:|\*|\\|\?", Optional Ch = "_") '根据正则表达式替换 Dim RegEx Set RegEx = CreateObject("vbscript.regexp") RegEx.Global = True RegEx.IgnoreCase = False RegEx.Pattern = Pattern RegReplace = RegEx.Replace(Expression, Ch) Set RegEx = Nothing End Function Public Function SplitWorkSheet() '拆分工作表 '根据当前激活单元格进行拆分 '当前单元格的列表示待拆分的列，当前单元格的行以上部分认为是表头, Dim tb As Workbook Dim ts, ws As Worksheet Dim arr(), a, c As Variant Dim dict As Object Dim nRow, nCol, hRow As Long '表头还是默认为第一行吧 hRow = 1 nCol = ActiveCell.Column Application.ScreenUpdating = False 't = Timer '记录开始时间 Set tb = ActiveWorkbook Set ts = tb.ActiveSheet ' 获取数据最后一行行号 nRow = ts.Range("A1048575").End(xlUp).Row '使用字典去重复 Set dict = CreateObject("Scripting.Dictionary") arr = ts.Range(ts.Cells(hRow + 1, nCol), ts.Cells(nRow, nCol)).Value For Each a In arr dict(a) = 1 Next For Each c In dict.Keys Debug.Print "Create New Worksheet: " & c With tb.Sheets.Add(After:=tb.Worksheets(tb.Worksheets.Count)) ts.UsedRange.AutoFilter Field:=nCol, Criteria1:="=" & c ts.UsedRange.SpecialCells(xlCellTypeVisible).Copy .Cells(1, 1) .Name = RegReplace(c) End With Next ts.UsedRange.AutoFilter 'Debug.Print "Time Cost: " & Format(Timer - t, "0.000") & "秒" Application.ScreenUpdating = True End Function Public Function MergeWorkSheet(Optional HeaderRow = 1, Optional TableName = "Sumary") '合并工作表 ' 将选中的sheet合并 Application.ScreenUpdating = False 'Debug.Print "Power.Merge Running" '// TODO Dim tb As Workbook Dim ts, ws, sht0 As Worksheet Dim i, nRow As Long Dim t, sh As Variant Dim TableNames() As String Dim Name As Variant Dim r, c As Long '保存待合并的 SheetName i = 0 ReDim TableNames(ActiveWindow.SelectedSheets.Count - 1) For Each t In ActiveWindow.SelectedSheets TableNames(i) = t.Name i = i + 1 Next Set tb = ActiveWorkbook tb.Sheets(1).Activate '多表选中状态VBA新建将使excel崩溃，所以这里将第一个表激活取消多选状态 '删除汇总表 For Each sh In tb.Sheets If sh.Name = TableName Then Application.DisplayAlerts = False sh.Delete Application.DisplayAlerts = True Exit For End If Next Set ts = tb.Sheets.Add(After:=tb.Sheets(tb.Sheets.Count)) ts.Name = TableName For Each Name In TableNames Set ws = tb.Sheets(Name) nRow = ts.Range("A1048575").End(xlUp).Row 'Debug.Print (Name & " - " & nRow) If nRow > 1 Then '剔除表头后复制 r = ws.UsedRange.Rows.Count c = ws.UsedRange.Columns.Count ws.Range(ws.Cells(HeaderRow + 1, 1), ws.Cells(r, c)).Copy ts.Cells(nRow + 1, 1) Else '复制整个表 ws.UsedRange.Copy ts.Cells(1, 1) End If Next 'tb.Save Application.ScreenUpdating = True End Function Public Function SplitWorkBook(ByVal Folder As String) '拆分工作簿 Dim tb, wb As Workbook 'nb: NewWorkbook wb: Workbook Dim ws As Worksheet 'ns: NewWorksheet Dim t As Variant Dim fso As Object 'Dim Folder As String Application.ScreenUpdating = False Set tb = ActiveWorkbook Set fso = CreateObject("Scripting.FileSystemObject") If Not fso.FolderExists(Folder) Then fso.CreateFolder (Folder) End If For Each t In ActiveWindow.SelectedSheets tb.Sheets(t.Name).Copy Set wb = ActiveWorkbook 'wb.SaveAs Filename:=folder & "\" & t & ".xlsx" 'fso.BuildPath(Folder, t & ".xlsx") wb.SaveAs Filename:=fso.BuildPath(Folder, t.Name & ".xlsx") wb.Close False Next 'fso.MoveFile Application.ScreenUpdating = True End Function Public Function MergeWorkBook() '合并工作簿 Dim nb, wb As Workbook 'nb: NewWorkbook wb: Workbook Dim ns, ws As Worksheet 'ns: NewWorksheet ws: Worksheet Dim FileNames() As Variant Dim f As Variant Dim SheetIndex As Integer SheetIndex = 1 Application.ScreenUpdating = False Application.SheetsInNewWorkbook = 1 With Application.FileDialog(msoFileDialogFilePicker) .Title = "合并工作簿" .InitialFileName = ActiveWorkbook.Path '当前工作簿的路径设为初始路径 .AllowMultiSelect = True '是否允许多选 .Filters.Clear '清除文件过滤器 .Filters.Add "Excel Files", "*.xls;*.xlsx" .Filters.Add "All Files", "*.*" '设置两个文件过滤器 If .Show = -1 Then 'FileDialog 对象的 Show 方法显示对话框，并且返回 -1（如果您按 OK）和 0（如果您按 Cancel）。 Set nb = Workbooks.Add 'nb.Windows(1).Visible = False For Each f In .SelectedItems Set wb = Workbooks.Open(f) wb.Windows(1).Visible = False If SheetIndex 1 Then For Each ws In wb.Sheets ws.Copy After:=nb.Sheets(nb.Sheets.Count) Set ns = nb.Sheets(nb.Sheets.Count) 'ns.Name = Split(wb.Name, ".")(0) & "_" & ws.Name Next Else wb.Sheets(1).Copy After:=nb.Sheets(nb.Sheets.Count) Set ns = nb.Sheets(nb.Sheets.Count) 'ns.Name = Split(Dir(f), ".")(0) End If wb.Close False Next Application.DisplayAlerts = False nb.Sheets(1).Delete Application.DisplayAlerts = True Else Exit Function End If End With Application.ScreenUpdating = True End Function 调用示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Option Explicit '新建普通模块 Main Public Sub 拆分工作表() Dim p As New power p.SplitWorkSheet End Sub Public Sub 拆分工作簿() Dim p As New power p.SplitWorkBook ActiveWorkbook.Path End Sub Public Sub 合并工作簿() Dim p As New power p.MergeWorkBook End Sub Public Sub 合并工作表() Dim p As New power p.MergeWorkSheet End Sub文章作者 kessil 上次更新 2023-11-06 Excel2013 |
【本文地址】
今日新闻 |
推荐新闻 |