代码之家 › 专栏 › 技术社区 › BinaryCat

Excel-将单元格转换为具有成对数据的行

transpose vba excel

BinaryCat · 技术社区 · 2 年前

我有一个CSV导出,其布局如下:

身份证件	剖面图A	B部分	...
1.	val1,val2	val3、val4、val5	...
2.	val6、val7、val8	val9,val10	...

我希望将数据集转换为以下输出:

身份证件	部分	价值
1.	剖面图A	val1
1.	剖面图A	val2
1.	B部分	val3
1.	B部分	val4
1.	B部分	val5
2.	剖面图A	val6
2.	剖面图A	val7
2.	剖面图A	val8
2.	B部分	val9
2.	B部分	val10
...	...	...

我面临的挑战是,我必须处理成千上万的这些行,因此我希望能够对整个数据集应用相同的解决方案。

我知道了如何将逗号分隔的值转换为多行,但似乎不知道如何通过编程将转换后的值与相应的 ID 和 Section .

下面是我为换位准备的VBA代码,以防有人想看到它:

Sub TransposeTest()
Dim rng As Range
Dim inputRng As Range, outputRng As Range
titleTxt = "Transpose Test"
Set inputRng = Application.Selection.Range("A1")
Set inputRng = Application.InputBox("Input cell:", titleTxt, inputRng.Address, Type:=8)
Set outputRng = Application.InputBox("Output cell:", titleTxt, Type:=8)
output = VBA.Split(inputRng.Range("A1").Value, ",")
outputRng.Resize(UBound(output) - LBound(output) + 1).Value = Application.Transpose(output)
End Sub

2 回复 | 直到 2 年前

Foxfire And Burns And Burns 2 年前

您可以利用阵列和拆分来轻松转换数据:

Sub test()
Dim i As Long, j As Long, k As Long, s As Long
Dim MyData As Variant
Dim SubData As Variant
Dim TotalColumns As Long


MyData = Range("A1").CurrentRegion.Value
TotalColumns = Range("A1").CurrentRegion.Columns.Count

Range("E1").Value = "ID"
Range("F1").Value = "Section"
Range("G1").Value = "Value"

k = 2 'initial row to paste data
For i = 2 To UBound(MyData) Step 1
    For j = 2 To TotalColumns Step 1 'j=2 because first column because it's ID
        SubData = Split(MyData(i, j), ", ")
        For s = 0 To UBound(SubData) Step 1
            Range("E" & k).Value = MyData(i, 1) 'ID always in first column
            Range("F" & k).Value = MyData(1, j) 'Section always in first row
            Range("G" & k).Value = SubData(s)
            k = k + 1
        Next s
    Next j
Next i

Erase SubData
Erase MyData

End Sub

我在同一个工作表中获得了输出,但代码可以很容易地进行调整,使其在不同的工作表中。

Spectral Instance 2 年前

如果表格中的9个单元格被配置为名为“Data”的Excel表格,则M代码如下:

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Section A", type text}, {"Section B", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Section A", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Section A"),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Split Column by Delimiter", {{"Section B", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Section B"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter1", {"ID"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Section"}}),
    #"Trimmed Text" = Table.TransformColumns(#"Renamed Columns",{{"Value", Text.Trim, type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Trimmed Text")
in
    #"Removed Duplicates"

将在PowerQuery中呈现此结果 (您需要 按分隔符拆分列 (每个部分的步骤)