如何在Excel/Google Sheets中将绩效评级记录转置为非聚合列？

Excel Solutions #

Method 1: Pivot Table (No Aggregation Required) #

You can use pivot tables here—they don’t have to be for aggregation if your ID-Cycle pairs are unique:

• Select your entire data range (including headers: ID, Cycle, Rating)
• Go to Insert > PivotTable and choose a destination for the output
• In the PivotTable Fields pane:
  • Drag ID to Rows
  • Drag Cycle to Columns
  • Drag Rating to Values
• Click the dropdown on the Rating field in Values > Value Field Settings
• Under Summarize value field by, pick First (or Max/Min—since each ID-Cycle has exactly one rating, all options return the same result)
• You’ll immediately get the wide format with IDs as rows, cycles as columns, and ratings as values.

Method 2: Dynamic Array Formula (Excel 365/2021) #

For a formula-based approach:

1. Get distinct IDs in column E (starting at E2):
   =UNIQUE(A:A) (replace A:A with your ID column range)
2. Get distinct cycles as headers in row 1 (starting at F1):
   =TRANSPOSE(UNIQUE(B:B)) (replace B:B with your Cycle column range)
3. In cell F2, use this formula and drag across/down (or let dynamic arrays auto-fill):
   =XLOOKUP($E2&F$1, $A:$A&$B:$B, $C:$C, "")
   For a fully dynamic single formula:
   =BYROW(UNIQUE(A:A), LAMBDA(id, XLOOKUP(id&TRANSPOSE(UNIQUE(B:B)), A:A&B:B, C:C, "")))

Google Sheets Solutions #

Method 1: QUERY Function (Fixing Your Previous Attempt) #

The QUERY function can pivot directly—you just need the right syntax. Assuming your data is in A1:C (headers included):

=QUERY(A1:C, "SELECT A, MAX(C) WHERE A IS NOT NULL GROUP BY A PIVOT B", 1)

• MAX(C) works because each ID-Cycle pair has exactly one rating (you can use MIN or ANY instead too)
• GROUP BY A groups results by ID
• PIVOT B converts cycle values into columns
• The final 1 tells QUERY the first row is headers.

Method 2: Pivot Table #

Same logic as Excel, with a slightly different workflow:

• Select your data range
• Go to Data > Pivot table
• In the pivot table editor:
  • Rows: Add ID
  • Columns: Add Cycle
  • Values: Add Rating, then change the summary function to First (or Max/Min)
• The pivot table will generate the wide format automatically.

Method 3: Array Formula with XLOOKUP #

For a formula-only solution:

1. Distinct IDs in column E (E2):
   =UNIQUE(A:A)
2. Distinct cycles as headers in row 1 (F1):
   =TRANSPOSE(UNIQUE(B:B))
3. In cell F2, use this array formula to fill the entire table:
   =ARRAYFORMULA(XLOOKUP($E2:$E&F$1:$1, $A:$A&$B:$B, $C:$C, ""))

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