excel - 如何使用级别值自动嵌套 Excel 电子表格的行?
问题描述
我正在尝试将分组应用于 Excel 2016 电子表格,以便可以更轻松地查看和解释它。电子表格中的数据格式类似于以下:
A B C
1 1 x y
2 1 x z
3 2 y y
4 2 x z
5 2 z x
6 1 x y
A 列已经包含与我想要在我的电子表格中的嵌套级别相对应的数字,即第 3、4 和 5 行是第 2 行的“子级”,因此应该相应地组合在一起。在这个特定的电子表格中达到的最高级别是 5。我不需要在电子表格中的行之间进行任何进一步的交互,例如计算小计。电子表格大约有 800 行,一个好的解决方案将在其他地方使用,因此手动执行此操作不是一个理想的解决方案。
如何让 Excel 2016 中的 group 函数将 A 列识别为我的分组并相应地应用大纲?
解决方案
此 VBA 脚本已更新为包含更多级别的分组。
它将按照您的要求执行,根据增量将行分组到上面的行。
它的工作原理在脚本中作为注释进行了解释,包括可能导致失败的原因。
请注意,如果 A 列中除了数字以外的任何其他内容,并且如果它不符合示例注释中指定的标准,它将失败。
Sub GroupRanges()
' Group levels must start at one and increase by one for each group level
' An error is produced if any levels are skipped
' Excel can only handle eight groups, the script will give a message and end if there are more than eight level groups
' Example: 1 1 2 3 3 4 4 5 will work
' Example: 1 1 2 2 2 4 4 5 will fail and produce an error, in this case group level 3 was skipped.
' Example: 1 2 3 4 5 6 7 8 9 Will fail, too many levels (more than 8)
Dim Sht As Worksheet
Dim LastRow As Long
Dim CurRow As Long
Dim StartRng As Integer
Dim EndRng As Integer
Dim GrpLvl As Integer
Dim MaxLvl As Integer
' This can be changed to define a sheet name
Set Sht = ActiveSheet
' find the highest number in the range to set as a group level
MaxLvl = WorksheetFunction.Max(Range("A:A"))
' If the Max level is greater than 8, then end the script as grouping cannot go beyond 8 levels
If MaxLvl >= 9 Then
MsgBox "You have " & MaxLvl & " group levels, Excel can only handle up to eight groups. This script will now end."
Exit Sub ' end the script if above eight groups
End If
'Set the Starting Group Level.
GrpLvl = 2
' find the last used row
LastRow = Sht.Cells(Sht.Rows.Count, "A").End(xlUp).Row
' Change the grouping to the cell above the range
Sht.Outline.SummaryRow = xlAbove
' Remove existing groups to prevent unrequired group levels.
' We now need to suppress error massages when trying to remove group levels that may not exist.
On Error Resume Next ' disable error messages
For x = 1 To 10 ' Repeat 10 times
Sht.Rows.Ungroup ' Remove Groups
Next x
On Error GoTo 0 ' Now it is important re-enable error messages
' Start the first loop to go through for each group level
For y = 2 To MaxLvl
'Reset the variables for each group level pass
CurRow = 1
StartRng = 0
EndRng = 0
' Start the inner loop through each row
For Z = 1 To LastRow
' Check value of cell, if value is 1 less than current group level then clear the Start/End Range Values
If Sht.Range("A" & CurRow) = GrpLvl - 1 Then
StartRng = 0
EndRng = 0
End If
' If cell value equals the group level then set Range Values accordingly
If Sht.Range("A" & CurRow) >= GrpLvl Then
' Check if row is the first of the range
If Sht.Range("A" & CurRow - 1) = GrpLvl - 1 Then
StartRng = CurRow
End If
' Check if row is the Last of the range
If Sht.Range("A" & CurRow + 1) <= 1 Then
EndRng = CurRow
End If
' If both range values are greater than 0 then group the range
If StartRng > 0 And EndRng > 0 Then
Sht.Rows(StartRng & ":" & EndRng).Rows.Group
End If
End If
CurRow = CurRow + 1 ' increase for the next row
Next Z ' repeat the inner loop
' Increase to the next group Level
GrpLvl = GrpLvl + 1
Next y ' repeat the first loop
End Sub
推荐阅读
- javascript - 如果系列稍后更新,则不会显示高图表导出按钮
- python - Pyspark将JSON对象列拆分为多列
- python - Django bool 字段说明默认为 False,null 为 True
- python - 如何使用 matplotlib.pyplot 连接散点
- python - 在没有显示的情况下运行可执行的 PyQt5 python 程序
- python - 如何使用 curses 模块打印 Pi 符号
- c# - 如何隐藏 Datagridview winform 应用程序中的列?
- python - 尝试使用 eval() 在 Python 中获取关键字参数时出现 SyntaxError
- java - 如何将列表视图中我从 Firebase 实时数据库调用的数据移动到 android studio 中的另一个活动?
- arrays - 通过使用 Spring Boot 插入来自循环的所有数据来创建 JSON 数组