如何在Excel中将长表单转换为宽表单? [英] How to convert a long form table to wide form table in Excel?
本文介绍了如何在Excel中将长表单转换为宽表单?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
使用这些信息,我想以编程方式生成这样的表格(一种在宽表格中解开长表格)表格:
如何实现?
解决方案
使用VBA: / strong>
范围(G1:K99)清除
对于每个xx范围内(A: A)
如果xx.Value =然后退出Sub
范围(G1)。偏移(xx.Value,0)= xx.Value
对于e = 1到99
如果范围(G1)偏移(xx.Value,e)=然后
范围(G1)偏移(xx.Value,e)= xx.Offset(0, 1).Value
退出
结束如果
下一个
下一个
表是c从G栏开始。如果您想要另一张表:
表格(2).Range(G1:...
添加表格之前...
没有VBA,遵循以下方案: / strong>
添加公式:
M2 - > = IFERROR(MATCH ($ A $ 1; $ A $ 8;);)
pre>
N2 - > = IFERROR(MATCH(L2; INDIRECT($ A&(M2 + 1)&:$ A $ 8 +;)
O2 - > = IFERROR(MATCH(L2; INDIRECT($ A&(N2 + 1)&:$ A $ 8);)+ N2;)
P2 - > = IFERROR(INDEX($ B $ 1:$ B $ 8; M2);)自动填充列还有R
和自动完成...
A picture is worth a thousand words. Let's say in one sheet I have the following table:
Using this information, I want to programatically generate the table like this(sort of un-melting the long table into the wide form) in another sheet:
How can you achieve this?
解决方案Using VBA:
Range("G1:K99").Clear For Each xx In Range("A:A") If xx.Value = "" Then Exit Sub Range("G1").Offset(xx.Value, 0) = xx.Value For e = 1 To 99 If Range("G1").Offset(xx.Value, e) = "" Then Range("G1").Offset(xx.Value, e) = xx.Offset(0, 1).Value Exit For End If Next Next
The table it's created from column "G". If you want another sheet:
Sheets(2).Range("G1: ...
add the Sheets before ...
Without VBA, following the scheme:Adding the formulas:
M2 -> =IFERROR(MATCH(L2;$A$1:$A$8;);"") N2 -> =IFERROR(MATCH(L2;INDIRECT("$A" & (M2+1) & ":$A$8");)+M2;"") O2 -> =IFERROR(MATCH(L2;INDIRECT("$A" & (N2+1) & ":$A$8");)+N2;"") P2 -> =IFERROR(INDEX($B$1:$B$8;M2);"") Autocomplete also columns to R
and Autocomplete ...
这篇关于如何在Excel中将长表单转换为宽表单?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文