Excel-如何在Excel中将包含货币的单行拆分为多行? [英] Excel - How do I split a single row with currencies into multiple rows in Excel?

问题描述

我有以下数据:

Product           GBP        USD        EUR         CAD
----------------------------------------------------------
Adidas Shoe       8         30         25           25 
Puma Shoe         7         40         30           25

如何在Excel 2020中将数据拆分为以下内容?使用宏?

How do I split the data into the following, in Excel 2020? Using a macro?

Product           VALUE     CURRENCYISO
----------------------------------------------------------
Adidas Shoe       8          GBP
Adidas Shoe       30         USD
Adidas Shoe       25         EUR
Adidas Shoe       25         CAD
Puma Shoe         7          GBP
Puma Shoe         40         USD
Puma Shoe         30         EUR
Puma Shoe         25         CAD

推荐答案

不是最干净的解决方案，但可以提供帮助:

Not the cleanest one, but can help:

Dim MiMatriz As Variant
Dim i As Long, ZZ As Long
Dim MyRow As Long


MiMatriz = Range("A1").CurrentRegion.Value

'type row number where data is going to be pasted. HEaders will be one row over
MyRow = 10

Range("A" & MyRow - 1).Value = "Product"
Range("B" & MyRow - 1).Value = "Value"
Range("C" & MyRow - 1).Value = "Currency ISO"


'we start array at 2 because first index got headers
For i = 2 To UBound(MiMatriz) Step 1
    For ZZ = 2 To 5 Step 1 'your range got 5 columns of data
        Range("A" & MyRow).Value = MiMatriz(i, 1) 'product
        Range("B" & MyRow).Value = MiMatriz(i, ZZ) 'value
        Range("C" & MyRow).Value = MiMatriz(1, ZZ) 'header
        MyRow = MyRow + 1
    Next ZZ
Next i

Erase MiMatriz

这篇关于Excel-如何在Excel中将包含货币的单行拆分为多行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！