如何在Excel中拆分rating字段并将类别设为列名统计count值

Hey there! Let's get this sorted out for you. It sounds like you've already split the rating field into rows with categories like Funny and Beautiful, but now you need to pivot those categories into column headers with their corresponding count values. Here are a couple of reliable ways to pull this off in Excel:

This is the fastest way for most cases:

• Highlight your entire dataset (make sure it includes both the split category column and your count column)
• Head to the Insert tab and click PivotTable
• In the PivotTable Fields pane that pops up:
  • Drag your category column (the one with Funny, Beautiful, etc.) to the Columns section
  • Drag your count column to the Values section
  • Double-check the value field settings—if your count is already a calculated number, set it to Sum; if you need to count occurrences, choose Count
• Boom! Your categories will now be column headers, with each row showing the exact count value that corresponds to it.

If you expect your data to update regularly and want a workflow you can reuse, Power Query is perfect:

• Select your data range, go to the Data tab, and click From Table/Range (just make sure your data has clear headers first)
• In the Power Query Editor:
  • Navigate to the Transform tab and hit Pivot Column
  • In the dialog box:
    • Set Value Column to your count column
    • Under Advanced options, pick Don't aggregate if your count is already tied to each row; if you need to calculate counts on the fly, choose Sum or Count
  • Click OK, then go to Home > Close & Load to bring the pivoted table back to your Excel sheet

If you'd rather stick with formulas instead of pivot tools, here's how:

1. First, get a unique list of your category names. If you have Excel 365/2021, use the UNIQUE function:

   =UNIQUE(your_category_column_range)
   

2. For each row and category, use a combination of INDEX and MATCH to pull the corresponding count. For example:

   =INDEX(count_column_range, MATCH([@YourRowID]&category_cell, row_id_range&category_column_range, 0))
   

   • Replace YourRowID, count_column_range, row_id_range, and category_column_range with your actual column references
   • Note: In older Excel versions, this is an array formula—you'll need to press Ctrl+Shift+Enter to activate it instead of just Enter.

内容的提问来源于stack exchange，提问作者K.Zarar