将Excel表中的数据表视图转换为垂直视图
将Excel表中的数据表视图转换为垂直视图
提问于 2018-05-30 14:59:03
下面的代码在小数据集上正确工作，但在大数据集中超时和崩溃。此代码的目标是在一个工作表上获取数据的表格视图，并将其转换为垂直视图。Excel在将其缩放到45k+行时会因性能问题而崩溃。代码是功能性的；它只是在大型输入上表现很差。
Sub Unpivot()
Call ReversePivotTable("Sheet1", "A", "C", "Sheet2", "Name")
End Sub
Sub ReversePivotTable(source_sheet, from_col, to_col, target_sheet, Optional type_header = "type", Optional value_header = "value")
Application.ScreenUpdating = False
LAST_ROW = Sheets(source_sheet).Cells(Rows.count, 1).End(xlUp).Row
If LAST_ROW > 1 Then
Sheets(target_sheet).Cells.ClearContents
Else
Exit Sub
End If
pvt_type_col = Sheets(target_sheet).Range(to_col & 1).Offset(0, 1).column 'D
pvt_value_col = Sheets(target_sheet).Range(to_col & 1).Offset(0, 2).column 'E
'get headers
Sheets(source_sheet).Range(from_col & ":" & to_col).copy
Sheets(target_sheet).Range("A1").PasteSpecial xlPasteValues
Sheets(target_sheet).Cells(1, pvt_type_col).Value = type_header
Sheets(target_sheet).Cells(1, pvt_value_col).Value = value_header
'tranform data
curr_row = 2
With Sheets(source_sheet)
last_col = .Cells(1, Columns.count).End(xlToLeft).column
For Each c In .Range("A2", .Range("A" & Rows.count).End(xlUp))
Set rng = .Range(.Cells(c.Row, pvt_type_col), .Cells(c.Row, last_col))
numbers = Application.WorksheetFunction.CountIf(rng, "<>""")
If numbers > 0 Then
Sheets(source_sheet).Range(from_col & c.Row & ":" & to_col & c.Row).copy
Sheets(target_sheet).Range(from_col & curr_row & ":" & from_col & curr_row + numbers - 1).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
b = curr_row
For a = pvt_type_col To last_col Step 1
If IsNumeric(.Cells(c.Row, a).Value) Then
'If .Cells(c.Row, a).Value <> "" Then
Sheets(target_sheet).Cells(b, pvt_type_col) = .Cells(1, a)
Sheets(target_sheet).Cells(b, pvt_value_col) = .Cells(c.Row, a)
b = b + 1
End If
Next a
curr_row = curr_row + numbers
If curr_row Mod 10 = 0 Then DoEvents
End If
Next c
End With
Sheets(target_sheet).Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub在添加样本数据之前：
+---------+------------------+-----------------+--------------+-------------+--------------+-------------+--------------+--------------+-------------+-------------+-------------+--------------+--------------+-------------+-------------+--------+--------+--------+--------------+--------------+-------------+--------------+-------------+-------------+-------------+-------------+--------------+-------------+--------------+-------------+--------------+-------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| col 1 | col 2 | col 3 | col 4 | col 5 | col 6 | col 7 | col 8 | col 9 | col 10 | col 11 | col 12 | col 13 | col 14 | col 15 | col 16 | col 17 | col 18 | col 19 | col 20 | col 21 | col 22 | col 23 | col 24 | col 25 | col 26 | col 27 | col 28 | col 29 | col 30 | col 31 | col 32 | col 33 | col 34 | col 35 | col 36 | col 37 | col 38 | col 39 | col 40 | col 41 | col 42 | col 43 | col 44 | col 45 | col 46 | col 47 | col 48 | col 49 |
+---------+------------------+-----------------+--------------+-------------+--------------+-------------+--------------+--------------+-------------+-------------+-------------+--------------+--------------+-------------+-------------+--------+--------+--------+--------------+--------------+-------------+--------------+-------------+-------------+-------------+-------------+--------------+-------------+--------------+-------------+--------------+-------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| stack | questions forums | excel questions | -540.0689323 | 1543.570725 | -144.7954348 | 2298.261951 | -9019.970702 | -14669.27805 | 2400.31011 | 642.2459256 | 5573.176935 | -19167.60096 | -17070.78503 | 2884.343252 | 2262.2904 | 0 | 0 | 0 | -4866.524221 | -5470.616311 | 6722.889306 | -6749.153327 | 8483.707603 | 7513.052842 | 3768.659869 | 8600.703543 | -8642.799155 | 1322.251923 | -1323.911031 | 3651.739593 | -259.3401823 | 9369.890794 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| stack | questions forums | excel questions | -325.5117945 | 641.8568521 | -58.21010305 | 977.4626836 | -3505.695779 | -7455.410001 | 777.9341271 | 385.2714806 | 1932.531773 | -8861.136183 | -6679.463121 | 1177.775583 | 881.2548725 | 0 | 0 | 0 | -1813.822794 | -2266.860562 | 2278.669772 | -2361.758467 | 3356.446385 | 2741.992369 | 1461.950204 | 3289.154294 | -3469.10217 | 804.7989704 | -816.9003551 | 1907.515323 | 432.8435868 | 3074.256129 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| stack | questions forums | excel questions | -36.42618332 | 65.26139258 | -6.513963305 | 99.38442773 | -435.0485137 | -1047.099199 | 79.09717611 | 39.17283622 | 186.7060257 | -1272.372107 | -922.750792 | 118.3261869 | 89.60240903 | 0 | 0 | 0 | -210.3183182 | -267.1376584 | 214.6223869 | -280.0000537 | 293.4738136 | 248.5196226 | 144.0720039 | 288.5506437 | -430.0886416 | 81.82868405 | -91.41469707 | 184.4395708 | 44.00977438 | 272.8284368 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| stack | questions forums | excel questions | -582.3647427 | 1316.573479 | -165.4555206 | 1925.519573 | -7138.977944 | -17532.94829 | 1404.004642 | 930.6126154 | 3648.013625 | -19585.55834 | -13758.8035 | 2376.319408 | 1898.9449 | 0 | 0 | 0 | -3625.886962 | -4833.808881 | 4232.764078 | -4449.956081 | 6883.584715 | 5398.12044 | 4048.773452 | 6632.405148 | -7240.871663 | 1959.676076 | -2008.657583 | 4413.431721 | 1360.661107 | 5484.849776 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+---------+------------------+-----------------+--------------+-------------+--------------+-------------+--------------+--------------+-------------+-------------+-------------+--------------+--------------+-------------+-------------+--------+--------+--------+--------------+--------------+-------------+--------------+-------------+-------------+-------------+-------------+--------------+-------------+--------------+-------------+--------------+-------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+希望在下列情况之后：
+---------+------------------+-----------------+-----------+--------------+
| col 1 | col 2 | col 3 | Attribute | Value |
+---------+------------------+-----------------+-----------+--------------+
| stack | questions forums | excel questions | col 4 | -540.0689323 |
| stack | questions forums | excel questions | col 5 | 1543.570725 |
| stack | questions forums | excel questions | col 6 | -144.7954348 |
| stack | questions forums | excel questions | col 7 | 2298.261951 |
| stack | questions forums | excel questions | col 8 | -9019.970702 |
| stack | questions forums | excel questions | col 9 | -14669.27805 |
| stack | questions forums | excel questions | col 10 | 2400.31011 |
| stack | questions forums | excel questions | col 11 | 642.2459256 |
| stack | questions forums | excel questions | col 12 | 5573.176935 |
| stack | questions forums | excel questions | col 13 | -19167.60096 |
| stack | questions forums | excel questions | col 14 | -17070.78503 |
| stack | questions forums | excel questions | col 15 | 2884.343252 |
| stack | questions forums | excel questions | col 16 | 2262.2904 |
| stack | questions forums | excel questions | col 17 | 0 |
| stack | questions forums | excel questions | col 18 | 0 |
| stack | questions forums | excel questions | col 19 | 0 |
| stack | questions forums | excel questions | col 20 | -4866.524221 |
| stack | questions forums | excel questions | col 21 | -5470.616311 |
| stack | questions forums | excel questions | col 22 | 6722.889306 |
| stack | questions forums | excel questions | col 23 | -6749.153327 |
| stack | questions forums | excel questions | col 24 | 8483.707603 |
| stack | questions forums | excel questions | col 25 | 7513.052842 |
| stack | questions forums | excel questions | col 26 | 3768.659869 |
| stack | questions forums | excel questions | col 27 | 8600.703543 |
| stack | questions forums | excel questions | col 28 | -8642.799155 |
| stack | questions forums | excel questions | col 29 | 1322.251923 |
| stack | questions forums | excel questions | col 30 | -1323.911031 |
| stack | questions forums | excel questions | col 31 | 3651.739593 |
| stack | questions forums | excel questions | col 32 | -259.3401823 |
| stack | questions forums | excel questions | col 33 | 9369.890794 |
| stack | questions forums | excel questions | col 34 | 0 |
| stack | questions forums | excel questions | col 35 | 0 |
| stack | questions forums | excel questions | col 36 | 0 |
| stack | questions forums | excel questions | col 37 | 0 |
| stack | questions forums | excel questions | col 38 | 0 |
| stack | questions forums | excel questions | col 39 | 0 |
| stack | questions forums | excel questions | col 40 | 0 |
| stack | questions forums | excel questions | col 41 | 0 |
| stack | questions forums | excel questions | col 42 | 0 |
| stack | questions forums | excel questions | col 43 | 0 |
| stack | questions forums | excel questions | col 44 | 0 |
| stack | questions forums | excel questions | col 45 | 0 |
| stack | questions forums | excel questions | col 46 | 0 |
| stack | questions forums | excel questions | col 47 | 0 |
| stack | questions forums | excel questions | col 48 | 0 |
| stack | questions forums | excel questions | col 49 | 0 |
| stack | questions forums | excel questions | col 4 | -325.5117945 |
| stack | questions forums | excel questions | col 5 | 641.8568521 |
| stack | questions forums | excel questions | col 6 | -58.21010305 |
| stack | questions forums | excel questions | col 7 | 977.4626836 |
| stack | questions forums | excel questions | col 8 | -3505.695779 |
| stack | questions forums | excel questions | col 9 | -7455.410001 |
| stack | questions forums | excel questions | col 10 | 777.9341271 |
| stack | questions forums | excel questions | col 11 | 385.2714806 |
| stack | questions forums | excel questions | col 12 | 1932.531773 |
| stack | questions forums | excel questions | col 13 | -8861.136183 |
| stack | questions forums | excel questions | col 14 | -6679.463121 |
| stack | questions forums | excel questions | col 15 | 1177.775583 |
| stack | questions forums | excel questions | col 16 | 881.2548725 |
| stack | questions forums | excel questions | col 17 | 0 |
| stack | questions forums | excel questions | col 18 | 0 |
| stack | questions forums | excel questions | col 19 | 0 |
| stack | questions forums | excel questions | col 20 | -1813.822794 |
| stack | questions forums | excel questions | col 21 | -2266.860562 |
| stack | questions forums | excel questions | col 22 | 2278.669772 |
| stack | questions forums | excel questions | col 23 | -2361.758467 |
| stack | questions forums | excel questions | col 24 | 3356.446385 |
| stack | questions forums | excel questions | col 25 | 2741.992369 |
| stack | questions forums | excel questions | col 26 | 1461.950204 |
| stack | questions forums | excel questions | col 27 | 3289.154294 |
| stack | questions forums | excel questions | col 28 | -3469.10217 |
| stack | questions forums | excel questions | col 29 | 804.7989704 |
| stack | questions forums | excel questions | col 30 | -816.9003551 |
| stack | questions forums | excel questions | col 31 | 1907.515323 |
| stack | questions forums | excel questions | col 32 | 432.8435868 |
| stack | questions forums | excel questions | col 33 | 3074.256129 |
| stack | questions forums | excel questions | col 34 | 0 |
| stack | questions forums | excel questions | col 35 | 0 |
| stack | questions forums | excel questions | col 36 | 0 |
| stack | questions forums | excel questions | col 37 | 0 |
| stack | questions forums | excel questions | col 38 | 0 |
| stack | questions forums | excel questions | col 39 | 0 |
| stack | questions forums | excel questions | col 40 | 0 |
| stack | questions forums | excel questions | col 41 | 0 |
| stack | questions forums | excel questions | col 42 | 0 |
| stack | questions forums | excel questions | col 43 | 0 |
| stack | questions forums | excel questions | col 44 | 0 |
| stack | questions forums | excel questions | col 45 | 0 |
| stack | questions forums | excel questions | col 46 | 0 |
| stack | questions forums | excel questions | col 47 | 0 |
| stack | questions forums | excel questions | col 48 | 0 |
| stack | questions forums | excel questions | col 49 | 0 |
| stack | questions forums | excel questions | col 4 | -36.42618332 |
| stack | questions forums | excel questions | col 5 | 65.26139258 |
| stack | questions forums | excel questions | col 6 | -6.513963305 |
| stack | questions forums | excel questions | col 7 | 99.38442773 |
| stack | questions forums | excel questions | col 8 | -435.0485137 |
| stack | questions forums | excel questions | col 9 | -1047.099199 |
| stack | questions forums | excel questions | col 10 | 79.09717611 |
| stack | questions forums | excel questions | col 11 | 39.17283622 |
| stack | questions forums | excel questions | col 12 | 186.7060257 |
| stack | questions forums | excel questions | col 13 | -1272.372107 |
| stack | questions forums | excel questions | col 14 | -922.750792 |
| stack | questions forums | excel questions | col 15 | 118.3261869 |
| stack | questions forums | excel questions | col 16 | 89.60240903 |
| stack | questions forums | excel questions | col 17 | 0 |
| stack | questions forums | excel questions | col 18 | 0 |
| stack | questions forums | excel questions | col 19 | 0 |
| stack | questions forums | excel questions | col 20 | -210.3183182 |
| stack | questions forums | excel questions | col 21 | -267.1376584 |
| stack | questions forums | excel questions | col 22 | 214.6223869 |
| stack | questions forums | excel questions | col 23 | -280.0000537 |
| stack | questions forums | excel questions | col 24 | 293.4738136 |
| stack | questions forums | excel questions | col 25 | 248.5196226 |
| stack | questions forums | excel questions | col 26 | 144.0720039 |
| stack | questions forums | excel questions | col 27 | 288.5506437 |
| stack | questions forums | excel questions | col 28 | -430.0886416 |
| stack | questions forums | excel questions | col 29 | 81.82868405 |
| stack | questions forums | excel questions | col 30 | -91.41469707 |
| stack | questions forums | excel questions | col 31 | 184.4395708 |
| stack | questions forums | excel questions | col 32 | 44.00977438 |
| stack | questions forums | excel questions | col 33 | 272.8284368 |
| stack | questions forums | excel questions | col 34 | 0 |
| stack | questions forums | excel questions | col 35 | 0 |
| stack | questions forums | excel questions | col 36 | 0 |
| stack | questions forums | excel questions | col 37 | 0 |
| stack | questions forums | excel questions | col 38 | 0 |
| stack | questions forums | excel questions | col 39 | 0 |
| stack | questions forums | excel questions | col 40 | 0 |
| stack | questions forums | excel questions | col 41 | 0 |
| stack | questions forums | excel questions | col 42 | 0 |
| stack | questions forums | excel questions | col 43 | 0 |
| stack | questions forums | excel questions | col 44 | 0 |
| stack | questions forums | excel questions | col 45 | 0 |
| stack | questions forums | excel questions | col 46 | 0 |
| stack | questions forums | excel questions | col 47 | 0 |
| stack | questions forums | excel questions | col 48 | 0 |
| stack | questions forums | excel questions | col 49 | 0 |
| stack | questions forums | excel questions | col 4 | -582.3647427 |
| stack | questions forums | excel questions | col 5 | 1316.573479 |
| stack | questions forums | excel questions | col 6 | -165.4555206 |
| stack | questions forums | excel questions | col 7 | 1925.519573 |
| stack | questions forums | excel questions | col 8 | -7138.977944 |
| stack | questions forums | excel questions | col 9 | -17532.94829 |
| stack | questions forums | excel questions | col 10 | 1404.004642 |
| stack | questions forums | excel questions | col 11 | 930.6126154 |
| stack | questions forums | excel questions | col 12 | 3648.013625 |
| stack | questions forums | excel questions | col 13 | -19585.55834 |
| stack | questions forums | excel questions | col 14 | -13758.8035 |
| stack | questions forums | excel questions | col 15 | 2376.319408 |
| stack | questions forums | excel questions | col 16 | 1898.9449 |
| stack | questions forums | excel questions | col 17 | 0 |
| stack | questions forums | excel questions | col 18 | 0 |
| stack | questions forums | excel questions | col 19 | 0 |
| stack | questions forums | excel questions | col 20 | -3625.886962 |
| stack | questions forums | excel questions | col 21 | -4833.808881 |
| stack | questions forums | excel questions | col 22 | 4232.764078 |
| stack | questions forums | excel questions | col 23 | -4449.956081 |
| stack | questions forums | excel questions | col 24 | 6883.584715 |
| stack | questions forums | excel questions | col 25 | 5398.12044 |
| stack | questions forums | excel questions | col 26 | 4048.773452 |
| stack | questions forums | excel questions | col 27 | 6632.405148 |
| stack | questions forums | excel questions | col 28 | -7240.871663 |
| stack | questions forums | excel questions | col 29 | 1959.676076 |
| stack | questions forums | excel questions | col 30 | -2008.657583 |
| stack | questions forums | excel questions | col 31 | 4413.431721 |
| stack | questions forums | excel questions | col 32 | 1360.661107 |
| stack | questions forums | excel questions | col 33 | 5484.849776 |
| stack | questions forums | excel questions | col 34 | 0 |
| stack | questions forums | excel questions | col 35 | 0 |
| stack | questions forums | excel questions | col 36 | 0 |
| stack | questions forums | excel questions | col 37 | 0 |
| stack | questions forums | excel questions | col 38 | 0 |
| stack | questions forums | excel questions | col 39 | 0 |
| stack | questions forums | excel questions | col 40 | 0 |
| stack | questions forums | excel questions | col 41 | 0 |
| stack | questions forums | excel questions | col 42 | 0 |
| stack | questions forums | excel questions | col 43 | 0 |
| stack | questions forums | excel questions | col 44 | 0 |
| stack | questions forums | excel questions | col 45 | 0 |
| stack | questions forums | excel questions | col 46 | 0 |
| stack | questions forums | excel questions | col 47 | 0 |
| stack | questions forums | excel questions | col 48 | 0 |
| stack | questions forums | excel questions | col 49 | 0 |
+---------+------------------+-----------------+-----------+--------------+回答 3
Code Review用户
发布于 2018-06-01 05:39:46
我非常想回顾一下这段代码，但我无法理解它。让我们检查一些变量。我看到a，b，c，to_col，from_col，curr_row，pvt_type_col，pvt_value_col，numbers。
我会赞扬from_col，to_col和curr_row，尽管名字很糟糕。但是b是什么呢？它迭代，但我不知道它为什么迭代。我通常记得c是一个细胞，但是a是什么呢？
更不用说，没有一个变量是声明的，所以我甚至无法考虑知道它可能是什么类型的数据，这可能会让我想到它可能是什么。
为您提供变量有意义的名称
总是打开Option Explicit。您可以通过使用VBE中的Tools ->选项并检查Require声明选项来自动获得它。这样，如果没有定义任何变量，编译器就会通知您。
当您不定义变量时，VBA将将其声明为可容纳任何类型数据的变量类型。虽然这可能更灵活，但在VBA决定或测试该类型时，它会增加宏的处理时间。此外，由于变体可以是任何类型的数据，您可能会遗漏关于类型不匹配的有价值的故障排除信息
当然，给你的变量取有意义的名字。如果a是支点表列，那么至少只使用pivotTableColumn。
而numbers -我甚至不能。
好吧，听起来有点刻薄。请不要这样做，但我很沮丧，因为我帮不了你。也有一些基本的东西要拿走-
- 你知道复制和粘贴都很慢。当您可以在变量中完成工作时，没有任何理由使用它们。关于StackOverflow 解决这个问题有一个很好的问题。
- 为什么要使用
countif-一个工作表函数？然后用一个if跟踪它。就像for each cell in range if not cell = """ then go ahead一样 - 这种类型的东西
Sheets(source_sheet).Range(from_col & c.Row & ":" & to_col & c.Row).Copy不是很清楚。 - 您可能会有一些评论来提醒自己，但是注释- 代码告诉你怎么做，评论告诉你为什么。代码应该自己说明，如果需要注释，可能需要更清楚地说明。如果没有，评论应该描述你为什么要做某事，而不是你是怎么做的。这里有一个几个原因，以避免所有的评论在一起。
- 你不需要
Call潜艇，它已经过时了。相反，只需使用Sub argument, argument - 例程中的参数都是隐式传递
ByRef的。如果可能的话，您应该传递参数ByVal而不是ByRef。
很好地尝试写一些VBA来做你需要做的事情--真的，我们都是从某个地方开始的。我并不是想让你对你的代码感到内疚--你不应该这么做，但有时候把基本知识弄清楚可以解决问题，如果不是，那么至少可以正确理解它。
我的意思是，您在这里进行代码评审，所以您已经投入了比很多人更多的精力，并且您希望学习如何做得更好。这些都是伟大的东西！
Code Review用户
发布于 2018-06-01 18:37:18
几个注意事项：
- 声明你的变量。明确类型。如果有理由你需要使用变体，太好了！如果没有，则使用性能更好的类型(循环迭代器长、列名字符串等)。
- 如果要为迭代器使用一个字母的名称，最好的做法是使用i、j和k，而不是a、b和c，这不是什么大问题，但它有助于提高可读性。
- 通常最好将工作表引用传递给函数，而不是传递工作表的名称。这样，如果您不小心键入了工作表的名称，错误将发生在您犯错误的地方(函数调用)，而不是在被调用的函数本身内。
- 既然您正在处理表格数据，请考虑使用表格或ListObjects，因为它们在VBA中是众所周知的。它们使代码的可读性大大提高，因为它们允许您按名称引用列；( b)随着新数据的添加，它们会自动调整大小。
- 虽然禁用ScreenUpdating通常是个好主意，但在这样的助手/实用程序函数中这样做并不是一个好主意；相反，应该在主过程的开始/结束时这样做。在这种情况下，没关系，因为您只需要调用一个函数。但是，如果您是将数据作为一系列函数的一部分(一个去枢轴、一个格式化、一个创建图表等等)，那么您不会希望每次输入过程: Sub ()‘禁用屏幕更新’在“助手”过程中更新时禁用/重新启用mainSubBad。不要这样做。调用firstSubBad secondSubBad Call thirdSubBad End Sub Sub firstSubBad() Application.ScreenUpdating = False '...do main Application.ScreenUpdating = True End Sub secondSubBad() Application.ScreenUpdating = False '...do main Application.ScreenUpdating = True End Sub () Application.ScreenUpdating = False '...do stuff Application.ScreenUpdating = True End Sub mainSubGood()‘禁用主过程中的屏幕更新。代之而行。Application.ScreenUpdating = False Call firstSubGood Call secondSubGood Call thirdSubGood Application.ScreenUpdating = True End Sub Sub firstSubGood() '...do Sub secondSubGood() '...do False Sub ()
- 最后，每次与工作表的交互(从单元格读取值、设置单元格的值等)。在VBA中花费很大。复制/粘贴操作也很昂贵。这也是大型数据集性能如此差的最大原因。一般情况下，您不应该在VBA中单独遍历单元格。相反，将单元格值读入数组并循环如下:函数countCatsBad(rng作为范围)对于rng中的每个单元格的长度为cell.Value = "Cat“，则catCount = catCount +1 End如果Next countCatsBad = catCount End Function Function countCatsGood(rng作为范围)，则长Dim与可变的vals = rng.Value‘相同，如果只有一个单元格在范围内，如果rng.Count =1检查值并退出，则countCats = IIf(vals = "Cat"，1，0)退出函数结束为‘如果在范围内有多个单元格，则循环遍历Dim catCount长为长Dim的值，i= LBound(vals，1)到UBound(vals，1)，j=LBound(LBound，2)到UBound(vals，2)，如果是，catCount = catCount +1(如果下一个countCatsGood = catCount端点函数的话)
下面是我在代码中使用的un透视函数的一个版本(以及一些帮助函数)。它可以将一个10K行+ 12个旋转列的表转换为一个120 K行的无枢轴表，大约在一秒钟内。
Function unPivot(srcTbl As ListObject, ByVal toUnpivot As Variant, ByVal toRepeat As Variant, colsLabel As String, _
valsLabel As String, destWS As Worksheet, destTblName As String) As ListObject
'Change arrays/strings of columns to repeat/unpivot to arrays of base 1
toUnpivot = changeArrayBase(toUnpivot)
toRepeat = changeArrayBase(toRepeat)
'Set up array that will contain table data from both lists of columns
ReDim tblData(1 To srcTbl.ListRows.Count, 1 To UBound(toUnpivot) + UBound(toRepeat)) As Variant
'Add data from columns that will be repeated in each row of the unpivoted data
Dim i As Long
Dim j As Long
Dim tempArr As Variant
For j = 1 To UBound(toRepeat)
tempArr = srcTbl.ListColumns(toRepeat(j)).DataBodyRange.Value2
For i = 1 To UBound(tempArr, 1)
tblData(i, j) = tempArr(i, 1)
Next
Next
'Add data from columns that are being unpivoted
Dim repeatCount As Long
repeatCount = UBound(toRepeat)
For j = 1 To UBound(toUnpivot)
tempArr = srcTbl.ListColumns(toUnpivot(j)).DataBodyRange.Value2
For i = 1 To UBound(tempArr, 1)
tblData(i, j + repeatCount) = tempArr(i, 1)
Next
Next
'Set up results array
'Number of rows = number of rows in original table * number of columns being unpivoted
'Number of columns = number of repeated columns + 2 (one column for label, one column for unpivoted values)
ReDim newArr(1 To UBound(tblData, 1) * UBound(toUnpivot), 1 To repeatCount + 2) As Variant
'Loop through table data and fill results array
Dim oldRow As Long
Dim unpivotCol As Long
Dim repeatCol As Long
Dim newRow As Long
newRow = 1
'Loop through each row in source data
For oldRow = 1 To UBound(tblData, 1)
'Loop through each column to be unpivoted
For unpivotCol = 1 To UBound(toUnpivot)
'Repeat values from repeated columns
For repeatCol = 1 To repeatCount
newArr(newRow, repeatCol) = tblData(oldRow, repeatCol)
Next
'Add name of unpivoted column to 'label' column
newArr(newRow, repeatCount + 1) = toUnpivot(unpivotCol)
'Add value from unpivoted column to 'value' column
newArr(newRow, repeatCount + 2) = tblData(oldRow, unpivotCol + repeatCount)
'Move to next row of results array
newRow = newRow + 1
Next
Next
'Loop through table data and fill results array
Dim newRow As Long
newRow = 1
'Loop through each row in source data
For i = 1 To UBound(tblData, 1)
'Loop through each column to be unpivoted
For j = 1 To UBound(toUnpivot)
'Add values from repeated columns
Dim k As Long
For k = 1 To repeatCount
newArr(newRow, k) = tblData(i, k)
Next
'Add name of unpivoted column to 'label' column
newArr(newRow, repeatCount + 1) = toUnpivot(j)
'Add value from unpivoted column to 'value' column
newArr(newRow, repeatCount + 2) = tblData(i, j + repeatCount)
'Move to next row of results array
newRow = newRow + 1
Next
Next
'Write unpivoted data to sheet and create table
With destWS
'Add headers
.Cells.Clear
.Range(.Cells(1, 1), .Cells(1, repeatCount)).Value = widen1dArray(toRepeat, 2)
.Cells(1, repeatCount + 1).Value = colsLabel
.Cells(1, repeatCount + 2).Value = valsLabel
'Add data
.Range(.Cells(2, 1), .Cells(UBound(newArr, 1) + 1, UBound(newArr, 2))).Value = newArr
'Create table
Dim tblRange As Range
Set tblRange = .Range(.Cells(1, 1), .Cells(UBound(newArr, 1) + 1, UBound(newArr, 2)))
.ListObjects.Add(xlSrcRange, tblRange, , xlYes).Name = tblName
End With
Set unPivot = destWS.ListObjects(tblName)
End Function
Function changeArrayBase(ByVal arr As Variant, Optional ByVal newBase As Long = 1) As Variant
'Changes base of 1D or 2D array (arr) to specified value (newBase)
'If arr is not an array, it is turned into a 1-element array containing the original value
Dim tempArr As Variant
Dim i As Long
Dim j As Long
Dim numDims As Long
numDims = getDims(arr)
If numDims = 0 Then
ReDim tempArr(newBase To newBase) As Variant
If IsObject(arr) Then
Set tempArr(newBase) = arr
Else
tempArr(newBase) = arr
End If
ElseIf numDims = 1 Then
ReDim tempArr(newBase To UBound(arr) - LBound(arr) + newBase) As Variant
j = newBase
For i = LBound(arr) To UBound(arr)
If IsObject(arr(i)) Then
Set tempArr(j) = arr(i)
Else
tempArr(j) = arr(i)
End If
j = j + 1
Next
ElseIf numDims > 2 Then
MsgBox "Error: Cannot change base of arrays with >2 dimensions"
Stop
Exit Function
Else
Dim x As Long
Dim y As Long
x = UBound(arr, 1) - LBound(arr, 1) + newBase
y = UBound(arr, 2) - LBound(arr, 2) + newBase
ReDim tempArr(newBase To x, newBase To y) As Variant
x = newBase
For i = LBound(arr, 1) To UBound(arr, 1)
y = newBase
For j = LBound(arr, 2) To UBound(arr, 2)
If IsObject(arr(i, j)) Then
Set tempArr(x, y) = arr(i, j)
Else
tempArr(x, y) = arr(i, j)
End If
y = y + 1
Next
x = x + 1
Next
End If
changeArrayBase = tempArr
End Function
Function widen1dArray(ByRef arr As Variant, Optional ByVal bigDim As Long = 1, Optional ByVal newBase As Long = 1) As Variant
'Takes a 1d array and turns it into a 2d array in which one dimension has size 1
'IE it takes an array with these dimensions:
'Dim arr(1 To 10)
'And turns it into an array with either of these dimensions:
'Dim arr(1 To 10, 1 To 1) [bigDim = 1]
'Dim arr(1 To 1, 1 To 10) [bigDim = 2]
Dim numDims As Long
numDims = getDims(arr)
If numDims = 0 Then
ReDim bigArr(newBase To newBase, newBase To newBase) As Variant
bigArr(newBase, newBase) = arr
widen1dArray = bigArr
Exit Function
ElseIf numDims > 1 Then
MsgBox "Error: Array already has more than 1 dimension"
Stop
Exit Function
ElseIf bigDim = 2 Then
ReDim tempArr(newBase To newBase, newBase To UBound(arr) - LBound(arr) + newBase) As Variant
Else
ReDim tempArr(newBase To UBound(arr) - LBound(arr) + newBase, newBase To newBase) As Variant
End If
Dim i As Long
Dim j As Long
j = newBase
If bigDim = 2 Then
For i = LBound(arr) To UBound(arr)
If IsObject(arr(i)) Then
Set tempArr(newBase, j) = arr(i)
Else
tempArr(newBase, j) = arr(i)
End If
j = j + 1
Next
Else
For i = LBound(arr) To UBound(arr)
If IsObject(arr(i)) Then
Set tempArr(j, newBase) = arr(i)
Else
tempArr(j, newBase) = arr(i)
End If
j = j + 1
Next
End If
widen1dArray = tempArr
End Function
Function getDims(x As Variant) As Long
'Gets dimensions of an array; returns 0 for non-arrays
On Error GoTo Err
Dim i As Long
Dim tempVal As Long
i = 0
Do While True
i = i + 1
tempVal = UBound(x, i)
Loop
Err:
On Error GoTo 0
getDims = i - 1
End Function编辑: OP请求一个如何使用"unPivot“函数的示例。基本设置与OP类似，因为有一个主要的子+助手函数。假设源数据已经在表中，主子应该如下所示：
Dim sourceTable As ListObject
Dim newTable As ListObject
Set sourceTable = ThisWorkbook.Sheets("Example").ListObjects("ExampleTable")
Set newTable = unPivot(sourceTable, Array("Person 1", "Person 2", "Person 3"), "Household", _
"Person", "Name", ThisWorkbook.Sheets("Results"), "ResultsTable")
'Then do whatever you want with the results, like deleting blank rows, formatting, etc.Code Review用户
发布于 2018-06-06 00:19:08
尝试在顶部添加application.calculation=xlmanual并删除doevents。在底部添加application.calculation=xlautomatic，然后添加do事件。
我觉得你在重新计算你正在处理的数据
页面原文内容由Code Review提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://codereview.stackexchange.com/questions/195491
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号