Excel/CSV文件复杂转置(Complex Transpose)/格式重构(Reformatting)方案咨询（问卷数据转换）

Hey there! Let's break down how to tackle this questionnaire data reshaping—this is a super common "unpivot" scenario with a small twist, and you’ve already got great tools to work with!

方案1：用Power Query（最适合有基础的你） #

Since you mentioned you have basic Power Query skills, this is probably the easiest no-code route:

• First, select your data range in Excel, go to the Data tab → From Table/Range (make sure "My table has headers" is checked).
• In the Power Query Editor, select the first 4 columns (the ones you want repeated for every row), right-click them, and choose Unpivot Other Columns.
• Now you’ll see an Attribute column (which will have your "Field Type X" and "Question Value X" labels) and a Value column. To pair these correctly:
  • Add a custom column with the formula: Number.RoundDown((Table.ColumnPosition(Source, [ColumnName]) - 4)/2) + 1 — this creates a group number for each pair of Field Type/Question Value columns.
  • Select the first 4 columns plus this new group column, right-click, and choose Group By. Set the operation to All Rows (name the grouped column something like GroupedData).
  • Expand the GroupedData column, then use Pivot Column (under the Transform tab): set Attribute as the "Values Column" and Value as the "Values to Aggregate".
• Finally, delete the group number column, adjust your column order, and load the data back to Excel.

方案2：用VBA（适合自动化重复操作） #

If you want to automate this for future use, VBA works perfectly. Here’s a ready-to-use script:

Sub ReshapeQuestionnaireData()
    Dim srcSheet As Worksheet, destSheet As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim i As Long, j As Long, destRow As Long
    
    ' Replace with your source sheet name
    Set srcSheet = ThisWorkbook.Sheets("原始数据")
    Set destSheet = ThisWorkbook.Sheets.Add
    destSheet.Name = "转换后数据"
    
    ' Copy header rows
    destSheet.Range("A1:D1").Value = srcSheet.Range("A1:D1").Value
    destSheet.Range("E1").Value = "Field Type"
    destSheet.Range("F1").Value = "Question Value"
    destRow = 2
    
    lastRow = srcSheet.Cells(srcSheet.Rows.Count, "A").End(xlUp).Row
    lastCol = srcSheet.Cells(1, srcSheet.Columns.Count).End(xlToLeft).Column
    
    ' Loop through each source row
    For i = 2 To lastRow
        ' Loop through each Field Type/Question Value pair (step by 2 columns)
        For j = 5 To lastCol Step 2
            ' Copy the first 4 columns
            destSheet.Range(destSheet.Cells(destRow, 1), destSheet.Cells(destRow, 4)).Value = _
                srcSheet.Range(srcSheet.Cells(i, 1), srcSheet.Cells(i, 4)).Value
            ' Paste Field Type
            destSheet.Cells(destRow, 5).Value = srcSheet.Cells(i, j).Value
            ' Paste Question Value
            destSheet.Cells(destRow, 6).Value = srcSheet.Cells(i, j + 1).Value
            destRow = destRow + 1
        Next j
    Next i
    
    MsgBox "数据转换完成！", vbInformation
End Sub

To use this:

• Press Alt + F11 to open the VBA Editor
• Insert a new module (right-click your workbook → Insert → Module)
• Paste the code, update the source sheet name if needed
• Run the macro (press F5 or use the run button)

方案3：用Python Pandas（适合大数据量场景） #

If you’re dealing with thousands of questionnaire responses, Python’s Pandas library is way faster than Excel. Here’s a simple script:

import pandas as pd

# Load your data (replace with your file path)
df = pd.read_excel("问卷数据.xlsx")

# Reshape the data assuming column names follow "Field Type X" / "Question Value X"
df_reshaped = pd.wide_to_long(
    df,
    stubnames=["Field Type", "Question Value"],
    i=df.columns[:4].tolist(),  # Use first 4 columns as identifiers
    j="Group"
).reset_index().drop("Group", axis=1)

# Save the reshaped data to a new file
df_reshaped.to_excel("转换后问卷数据.xlsx", index=False)

快速建议 #

• Stick with Power Query if this is a one-off task or you prefer working in Excel without code.
• Use VBA if you need to run this transformation regularly on similar datasets.
• Go with Pandas if you have a huge dataset (10k+ rows) or want to integrate this into a larger data pipeline.

备注：内容来源于stack exchange，提问作者Mayo