Excel数据格式转换求助：将交叉表转为带序号的明细格式

I’ll walk you through two reliable, automated methods to get this done—no manual data entry required.

Method 1: Use Power Query (No-Code, Built-In) #

Power Query is Excel’s native tool for reshaping data, and it’s ideal for this task:

1. Select your data: Highlight the entire original table, including headers like From/to, C1, C2, C3.
2. Launch Power Query: Go to the Data tab > click From Table/Range. If your data isn’t already a table, Excel will prompt you to convert it—check "My table has headers" and click OK.
3. Unpivot columns:
   • Select the From/to column in the Power Query Editor.
   • Go to the Transform tab > Unpivot Columns > choose Unpivot Other Columns. This will turn all the C1/C2/C3 columns into rows.
4. Rename columns:
   • Right-click the Attribute column header > Rename > type TO.
   • Right-click the Value column header > Rename > type Rate.
5. Add serial numbers:
   • Go to the Add Column tab > Index Column > select From 1. This creates an Index column starting at 1 (rename it to S.no if you want).
6. Rearrange columns:
   • Drag the Index (or S.no) column to the first position. Ensure the final order is: S.no → From/to (rename to From if desired) → TO → Rate.
7. Load the data: Click Close & Load (top-left corner) to export the reshaped data to a new worksheet.

Method 2: VBA Macro (One-Click Automation) #

If you prefer using a script, here’s a ready-to-run macro that outputs the result to a new sheet:

How to Use the Macro: #

1. Open your Excel file with the original data.
2. Press Alt + F11 to open the VBA Editor.
3. Right-click your workbook in the Project Explorer > Insert > Module.
4. Paste this code into the module:

Sub ConvertCrossTabToLongFormat()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim i As Long, j As Long, rowCounter As Long
    
    ' Set your source sheet (update "Sheet1" to match your sheet name)
    Set sourceSheet = ThisWorkbook.Worksheets("Sheet1")
    ' Create a new sheet for results
    Set targetSheet = ThisWorkbook.Worksheets.Add
    targetSheet.Name = "ConvertedData"
    
    ' Write headers to target sheet
    targetSheet.Range("A1:D1") = Array("S.no", "From", "TO", "Rate")
    
    ' Get boundaries of source data
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
    lastCol = sourceSheet.Cells(1, sourceSheet.Columns.Count).End(xlToLeft).Column
    
    rowCounter = 2 ' Start writing data from row 2
    
    ' Loop through each row and column to populate target sheet
    For i = 2 To lastRow
        For j = 2 To lastCol
            targetSheet.Range("A" & rowCounter).Value = rowCounter - 1 ' Serial number
            targetSheet.Range("B" & rowCounter).Value = sourceSheet.Range("A" & i).Value ' From
            targetSheet.Range("C" & rowCounter).Value = sourceSheet.Cells(1, j).Value ' TO
            targetSheet.Range("D" & rowCounter).Value = sourceSheet.Cells(i, j).Value ' Rate
            rowCounter = rowCounter + 1
        Next j
    Next i
    
    ' Auto-fit columns for readability
    targetSheet.Columns("A:D").AutoFit
    
    MsgBox "Conversion done!", vbInformation
End Sub

5. Update the source sheet name: If your data isn’t on "Sheet1", change Set sourceSheet = ThisWorkbook.Worksheets("Sheet1") to your sheet’s actual name.
6. Run the macro: Press F5 or go to Run > Run Sub/UserForm. A new sheet named ConvertedData will appear with your formatted data.

Both methods produce exactly the output you need. Power Query is great for easy updates if your source data changes, while the VBA macro is perfect for one-click automation.

内容的提问来源于stack exchange，提问作者karthik devaraneni