求助：Excel数据透视表按列标签显示行及任务分类查询方案

Hey Jeff, let's tackle this Excel pivot table challenge step by step—since you need to keep the original sheet editable for non-technical folks while making the data readable, here's a solid, actionable plan:

First, let's lock down the core data to prevent accidental edits that could break your pivot sources, while still letting non-technical users edit the necessary cells:

• Right-click the original sheet tab > Select Protect Sheet
• Uncheck "Select locked cells" but leave "Select unlocked cells" checked (this lets users only edit cells you've unlocked)
• Set a simple password (share it only with people who need to modify the raw task data)
• This keeps your source data intact without restricting the editing access your team needs.

This pivot will let you filter and summarize tasks by department:

1. Navigate to the original sheet, click any cell within your data range
2. Go to the Insert tab > Click PivotTable
3. In the pop-up window, confirm your data range is correct, then select "New Worksheet" (this keeps the original sheet uncluttered) > Hit OK
4. In the PivotTable Fields pane:
   • Drag your Department field to the Rows area (this groups tasks by department)
   • Drag your task identifier (like Task Name) to the Values area—if you want a count of tasks per department, set the value field to Count; if you have metrics like hours, use Sum
   • Add extra context fields (like Task Status or Due Date) to the Columns or Filters area to let you drill down into active/overdue tasks, etc.
5. Spruce up the readability: Use a built-in style from the PivotTable Styles gallery under the PivotTable Analyze tab.

This pivot will track tasks assigned to individual people:

1. Repeat steps 2.1-2.3, but choose another new worksheet for this pivot (name it something clear like "Employee Task Summary")
2. In the PivotTable Fields pane:
   • Drag your Employee Name field to the Rows area
   • Drag Task Name (or relevant metrics) to the Values area
   • Add Department to the Filters area so you can narrow down to specific teams if needed
   • For a clearer breakdown, drag Task Status to the Columns area to see how many completed/in-progress tasks each employee has.

Since non-technical users will update the original data, you need the pivots to reflect those changes automatically:

• Right-click any pivot table > Select PivotTable Options
• Go to the Data tab > Check "Refresh data when opening the file"
• For real-time refresh (while the file is open), add a simple VBA macro:

  Private Sub Worksheet_Change(ByVal Target As Range)
      ' Update sheet and pivot table names to match yours
      ThisWorkbook.Worksheets("Department Summary").PivotTables("PivotTable1").RefreshTable
      ThisWorkbook.Worksheets("Employee Summary").PivotTables("PivotTable2").RefreshTable
  End Sub
  

  • To add this: Press Alt + F11, double-click the original sheet in the Project pane, paste the code above
  • Note: Save the file as .xlsm (macro-enabled workbook) for this to work.

• Rename your sheets to something intuitive: "Original Task Data", "Department Task Summary", "Employee Task Tracker"
• Add a text box on the original sheet with simple instructions: "Edit task details here—use the other sheets to view organized summaries!"
• Hide the PivotTable Fields pane if you don't want users adjusting the pivots: Right-click the pivot > Hide Field List

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