内容简介:代码日志版权声明:翻译自:http://stackoverflow.com/questions/10921791/melt-reshape-in-excel-using-vba
我目前正在调整一份新工作,我与大家分享的大部分工作都是通过MS Excel.我经常使用枢轴表,因此需要“堆叠”数据,正是我所依赖的R中的reshape(reshape2)包中的melt()函数的输出.
有没有人可以让我开始一个VBA宏来完成这个,还是有一个已经存在?
宏观大纲将是:
>在Excel工作簿中选择一系列单元格.
>开始“融化”宏.
>宏将创建一个提示符“输入ID列数”,您可以在其中输入前面列出的标识信息. (例如,下面的代码是4).
>在excel文件中创建一个名为“melt”的新工作表
这将堆栈数据,并创建一个名为“变量”的新列
等于原始选择的数据列标题.
换句话说,输出将看起来与在R中简单执行这两行的输出完全相同:
require(reshape) melt(your.unstacked.dataframe, id.vars = 1:4)
以下是一个例子:
# unstacked data > df1 Year Month Country Sport No_wins No_losses High_score Total_games 2 2010 5 USA Soccer 4 3 5 9 3 2010 6 USA Soccer 5 3 4 8 4 2010 5 CAN Soccer 2 9 7 11 5 2010 6 CAN Soccer 4 8 4 13 6 2009 5 USA Soccer 8 1 4 9 7 2009 6 USA Soccer 0 0 3 2 8 2009 5 CAN Soccer 2 0 6 3 9 2009 6 CAN Soccer 3 0 8 3 # stacking the data > require(reshape) > melt(df1, id.vars=1:4) Year Month Country Sport variable value 1 2010 5 USA Soccer No_wins 4 2 2010 6 USA Soccer No_wins 5 3 2010 5 CAN Soccer No_wins 2 4 2010 6 CAN Soccer No_wins 4 5 2009 5 USA Soccer No_wins 8 6 2009 6 USA Soccer No_wins 0 7 2009 5 CAN Soccer No_wins 2 8 2009 6 CAN Soccer No_wins 3 9 2010 5 USA Soccer No_losses 3 10 2010 6 USA Soccer No_losses 3 11 2010 5 CAN Soccer No_losses 9 12 2010 6 CAN Soccer No_losses 8 13 2009 5 USA Soccer No_losses 1 14 2009 6 USA Soccer No_losses 0 15 2009 5 CAN Soccer No_losses 0 16 2009 6 CAN Soccer No_losses 0 17 2010 5 USA Soccer High_score 5 18 2010 6 USA Soccer High_score 4 19 2010 5 CAN Soccer High_score 7 20 2010 6 CAN Soccer High_score 4 21 2009 5 USA Soccer High_score 4 22 2009 6 USA Soccer High_score 3 23 2009 5 CAN Soccer High_score 6 24 2009 6 CAN Soccer High_score 8 25 2010 5 USA Soccer Total_games 9 26 2010 6 USA Soccer Total_games 8 27 2010 5 CAN Soccer Total_games 11 28 2010 6 CAN Soccer Total_games 13 29 2009 5 USA Soccer Total_games 9 30 2009 6 USA Soccer Total_games 2 31 2009 5 CAN Soccer Total_games 3 32 2009 6 CAN Soccer Total_games 3
我有两个帖子,有可用的代码和可下载的工作簿,在我的博客上的Excel / VBA中进行此操作:
http://yoursumbuddy.com/data-normalizer
http://yoursumbuddy.com/data-normalizer-the-sql/
以下是代码:
'Arguments 'List: The range to be normalized. 'RepeatingColsCount: The number of columns, starting with the leftmost, ' whose headings remain the same. 'NormalizedColHeader: The column header for the rolled-up category. 'DataColHeader: The column header for the normalized data. 'NewWorkbook: Put the sheet with the data in a new workbook? ' 'NOTE: The data must be in a contiguous range and the 'rows that will be repeated must be to the left, 'with the rows to be normalized to the right. Sub NormalizeList(List As Excel.Range, RepeatingColsCount As Long, _ NormalizedColHeader As String, DataColHeader As String, _ Optional NewWorkbook As Boolean = False) Dim FirstNormalizingCol As Long, NormalizingColsCount As Long Dim ColsToRepeat As Excel.Range, ColsToNormalize As Excel.Range Dim NormalizedRowsCount As Long Dim RepeatingList() As String Dim NormalizedList() As Variant Dim ListIndex As Long, i As Long, j As Long Dim wbSource As Excel.Workbook, wbTarget As Excel.Workbook Dim wsTarget As Excel.Worksheet With List 'If the normalized list won't fit, you must quit. If .Rows.Count * (.Columns.Count - RepeatingColsCount) > .Parent.Rows.Count Then MsgBox "The normalized list will be too many rows.", _ vbExclamation + vbOKOnly, "Sorry" Exit Sub End If 'You have the range to be normalized and the count of leftmost rows to be repeated. 'This section uses those arguments to set the two ranges to parse 'and the two corresponding arrays to fill FirstNormalizingCol = RepeatingColsCount + 1 NormalizingColsCount = .Columns.Count - RepeatingColsCount Set ColsToRepeat = .Cells(1).Resize(.Rows.Count, RepeatingColsCount) Set ColsToNormalize = .Cells(1, FirstNormalizingCol).Resize(.Rows.Count, NormalizingColsCount) NormalizedRowsCount = ColsToNormalize.Columns.Count * .Rows.Count ReDim RepeatingList(1 To NormalizedRowsCount, 1 To RepeatingColsCount) ReDim NormalizedList(1 To NormalizedRowsCount, 1 To 2) End With 'Fill in every i elements of the repeating array with the repeating row labels. For i = 1 To NormalizedRowsCount Step NormalizingColsCount ListIndex = ListIndex + 1 For j = 1 To RepeatingColsCount RepeatingList(i, j) = List.Cells(ListIndex, j).Value2 Next j Next i 'We stepped over most rows above, so fill in other repeating array elements. For i = 1 To NormalizedRowsCount For j = 1 To RepeatingColsCount If RepeatingList(i, j) = "" Then RepeatingList(i, j) = RepeatingList(i - 1, j) End If Next j Next i 'Fill in each element of the first dimension of the normalizing array 'with the former column header (which is now another row label) and the data. With ColsToNormalize For i = 1 To .Rows.Count For j = 1 To .Columns.Count NormalizedList(((i - 1) * NormalizingColsCount) + j, 1) = .Cells(1, j) NormalizedList(((i - 1) * NormalizingColsCount) + j, 2) = .Cells(i, j) Next j Next i End With 'Put the normal data in the same workbook, or a new one. If NewWorkbook Then Set wbTarget = Workbooks.Add Set wsTarget = wbTarget.Worksheets(1) Else Set wbSource = List.Parent.Parent With wbSource.Worksheets Set wsTarget = .Add(after:=.Item(.Count)) End With End If With wsTarget 'Put the data from the two arrays in the new worksheet. .Range("A1").Resize(NormalizedRowsCount, RepeatingColsCount) = RepeatingList .Cells(1, FirstNormalizingCol).Resize(NormalizedRowsCount, 2) = NormalizedList 'At this point there will be repeated header rows, so delete all but one. .Range("1:" & NormalizingColsCount - 1).EntireRow.Delete 'Add the headers for the new label column and the data column. .Cells(1, FirstNormalizingCol).Value = NormalizedColHeader .Cells(1, FirstNormalizingCol + 1).Value = DataColHeader End With End Sub
你会这样称呼:
Sub TestIt() NormalizeList ActiveSheet.UsedRange, 4, "Variable", "Value", False End Sub
代码日志版权声明:
翻译自:http://stackoverflow.com/questions/10921791/melt-reshape-in-excel-using-vba
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网
猜你喜欢:- 咨询服务的数字化重塑
- leetcode 566. 重塑矩阵 c++ 实现
- VMware郭尊华:数字化转型重塑安全
- Dubbo Spring Cloud 重塑微服务治理
- SDN重塑企业网络 网络管理者的角色转变
- 智能安防新格局,未来十年产业价值体系重塑
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。