我有一個具有以下布局的 CSV 匯出：
| ID | A節 | B節 | ... |
|---|---|---|---|
| 1 | 值 1，值 2 | val3、val4、val5 | ... |
| 2 | val6、val7、val8 | val9, val10 | ... |
我想將資料集轉換為以下輸出：
| ID | 部分 | 價值 |
|---|---|---|
| 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 |
| ... | ... | ... |
我的挑戰是我必須處理數萬行這樣的行，所以我希望能夠將相同的解決方案應用于整個資料集。
我想出了如何將逗號分隔的值轉置為多行，但似乎無法弄清楚如何以編程方式將轉置的值與相應的IDand配對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
uj5u.com熱心網友回復：
您可以從陣列和拆分中受益，輕松轉換資料：
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
我在同一個作業表中得到了輸出，但是可以很容易地調整代碼以使其在不同的作業表中。
uj5u.com熱心網友回復：
如果表格中的 9 個單元格配置為名為“資料”的 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 中呈現此結果
（您需要為每個部分設定一個按分隔符拆分列的步驟）
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/485918.html