如何更改Excel表格布局？将CSV宽表转换为纵向年份列格式

Hey there! Converting your wide-format CSV (with year columns) into a tidy long structure is a super common data prep task—here are three reliable methods to get it done, depending on your tool preference:

Method 1: Excel Power Query (No-Code, Recommended) #

This is the easiest built-in way to handle unpivoting in Excel, and it works seamlessly with CSV imports:

• Step 1: Import your CSV into Excel: Go to the Data tab → From Text/CSV, select your file, and click "Load To..." then choose "Only Create Connection" → check "Enable load" and click "Load". Open the Queries & Connections pane, right-click the query, and select Edit to launch the Power Query Editor.
• Step 2: In the editor, hold Ctrl and click to select the Country and Country Code columns (these are our fixed identifier columns we want to keep as-is).
• Step 3: Right-click either selected column, then choose Unpivot Other Columns. This will automatically turn all year columns into two new columns: Attribute (containing the year values) and Value (the corresponding numeric data).
• Step 4: Rename the columns to match your target structure: Right-click Attribute → Rename → type "Years"; repeat for Value to rename it "Values".
• Step 5: Click Close & Load in the Power Query ribbon, and you’ll have your tidy long-format data in a new Excel worksheet.

Method 2: VBA Macro (For Repeatable Tasks) #

If you need to automate this process for multiple files, a VBA macro can save you time:

1. Open your CSV in Excel, then press Alt + F11 to open the VBA Editor.
2. Right-click your workbook in the Project Explorer → Insert → Module.
3. Paste the following code into the module:

Sub UnpivotCSVData()
    Dim wsSource As Worksheet, wsDest As Worksheet
    Dim lastCol As Integer, lastRow As Integer, destRow As Integer
    Dim i As Integer, j As Integer
    
    ' Set source and destination sheets
    Set wsSource = ActiveSheet
    Set wsDest = ThisWorkbook.Sheets.Add
    wsDest.Name = "UnpivotedData"
    
    ' Write header row
    wsDest.Range("A1:C1") = Array("Country", "Country Code", "Years")
    wsDest.Range("D1") = "Values"
    destRow = 2
    
    ' Get bounds of source data
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    lastCol = wsSource.Cells(1, wsSource.Columns.Count).End(xlToLeft).Column
    
    ' Loop through each row and year column to populate destination
    For i = 2 To lastRow
        For j = 3 To lastCol
            wsDest.Cells(destRow, "A") = wsSource.Cells(i, "A")
            wsDest.Cells(destRow, "B") = wsSource.Cells(i, "B")
            wsDest.Cells(destRow, "C") = wsSource.Cells(1, j)
            wsDest.Cells(destRow, "D") = wsSource.Cells(i, j)
            destRow = destRow + 1
        Next j
    Next i
    
    MsgBox "Unpivot finished! Check the 'UnpivotedData' sheet.", vbInformation
End Sub

4. Return to your Excel sheet, press Alt + F8, select UnpivotCSVData, and click Run. The macro will create a new sheet with your transformed data.

Method 3: Python Pandas (For Programmers) #

If you’re comfortable with Python, Pandas makes this task extremely concise:
First, install Pandas if you haven’t already (pip install pandas), then run this script:

import pandas as pd

# Load the CSV file
df = pd.read_csv("your_input_file.csv")

# Unpivot the data: keep Country/Country Code as identifiers, melt year columns
unpivoted_df = pd.melt(
    df,
    id_vars=["Country", "Country Code"],
    var_name="Years",
    value_name="Values"
)

# Save the result to a new CSV
unpivoted_df.to_csv("your_output_file.csv", index=False)

Just replace "your_input_file.csv" and "your_output_file.csv" with your actual file paths, and you’ll get the long-format data in seconds.

内容的提问来源于stack exchange，提问作者Tim Hellegers