Excel宏将垂直数据转换为水平数据 [英] Excel Macro to Transpose Vertical Data to Horizontal Data
问题描述
有人可以帮我写一下 宏来将物种丰度数据从页面转移到整个页面。
can someone please help me to write macro to transpose species abundance data from going down the page to across the page.
来自:
感谢您的帮助
干杯
Nutman
推荐答案
在这里:
Sub Transform()
Dsh wshS As Worksheet
Dim wshT As Worksheet
Dim s As Long
Dim m As Long
Dim t As Long
Dim c As Long
Dim dict As Object
Dim strS As String
Application.ScreenUpdating = False
设置wshS = ActiveSheet
'创建目标表单$
设置wshT = Worksheets.Add(After:= wshS)
wshT.Cells(1,2).Value =" Species"
wshT.Cells(2,1).Value =" Date"
wshT.Cells(2,2).Value =" Replicate"
'获取独特的物种名称
设置dict = CreateObject(" Scripting.Dictionary")
m = wshS.Cells(wshS.Rows.Count,1).End(xlUp).Row
对于s = 2 To m
strS = wshS.Cells(s,3).Value
如果不是dict.Exists(strS)则为
dict.Add Key:= strS,Item:= strS
结束如果是
下一个s
使用wshT.Cells(2,3).Resize(1,dict.Count)
'填充目标纸张的第2行
.Value = dict.Keys
'并将其分类为
.Sort Key1:= wshT.Cells(2,3),Header:= xlNo,Orientation:= xlSortRows
结束与$
'转置数据
t = 2
对于s = 2 To m
如果wshS.Cells(s,2).Value<> wshS.Cells(s - 1,2).Value然后是
t = t + 1
wshT.Cells(T,1)。价值= wshS.Cells(S,1)。价值
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ; wshT.Cells(t,2).Value = wshS.Cells(s,2).Value
结束如果
C = wshT.Range(>> 2:2英寸)。查找(什么:= wshS.Cells(S,3)。价值,注视:= xlWhole).COLUMN
&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP; wshT.Cells(t,c).Value = wshS.Cells(s,4).Value
下一个s
'调整列宽▼
wshT.UsedRange.EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
Sub Transform()
Dim wshS As Worksheet
Dim wshT As Worksheet
Dim s As Long
Dim m As Long
Dim t As Long
Dim c As Long
Dim dict As Object
Dim strS As String
Application.ScreenUpdating = False
Set wshS = ActiveSheet
' Create target sheet
Set wshT = Worksheets.Add(After:=wshS)
wshT.Cells(1, 2).Value = "Species"
wshT.Cells(2, 1).Value = "Date"
wshT.Cells(2, 2).Value = "Replicate"
' Get the unique species names
Set dict = CreateObject("Scripting.Dictionary")
m = wshS.Cells(wshS.Rows.Count, 1).End(xlUp).Row
For s = 2 To m
strS = wshS.Cells(s, 3).Value
If Not dict.Exists(strS) Then
dict.Add Key:=strS, Item:=strS
End If
Next s
With wshT.Cells(2, 3).Resize(1, dict.Count)
' Populate row 2 of target sheet
.Value = dict.Keys
' And sort it
.Sort Key1:=wshT.Cells(2, 3), Header:=xlNo, Orientation:=xlSortRows
End With
' Transpose the data
t = 2
For s = 2 To m
If wshS.Cells(s, 2).Value <> wshS.Cells(s - 1, 2).Value Then
t = t + 1
wshT.Cells(t, 1).Value = wshS.Cells(s, 1).Value
wshT.Cells(t, 2).Value = wshS.Cells(s, 2).Value
End If
c = wshT.Range("2:2").Find(What:=wshS.Cells(s, 3).Value, LookAt:=xlWhole).Column
wshT.Cells(t, c).Value = wshS.Cells(s, 4).Value
Next s
' Adjust column widths
wshT.UsedRange.EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
这篇关于Excel宏将垂直数据转换为水平数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!