如何在Excel数据透视表中按组生成含数值与计算字段的行？

I get where you're coming from—Excel's pivot tables are incredibly powerful, but nailing the exact layout you want often takes a few targeted steps. Let's walk through setting up this pivot table to summarize on-time milestone data with group-level values and calculated fields:

1. Prep Your Source Data First #

Start by formatting your refreshable data source as an Excel Table (select the full data range > Insert > Table). This ensures the pivot table automatically picks up new or updated data when you refresh later. Confirm your data includes these key columns:

• A grouping column (e.g., "Project Name", "Milestone Category")
• An on-time status column (e.g., "OnTime" with values like 1 for on-time, 0 for late, or text like "On Time"/"Late")
• A unique milestone identifier (e.g., "Milestone ID") to calculate total milestone counts per group.

2. Build the Base Pivot Table #

• Select any cell in your Excel Table.
• Go to Insert > PivotTable. Choose a location (a new sheet is ideal for dedicated reporting) and click OK.
• In the PivotTable Fields pane:
  • Drag your grouping column (e.g., "Project Name") to the Rows area.
  • Drag your "OnTime" column to the Values area. By default, this will count on-time entries. If you used 1/0 values, right-click the value field > Value Field Settings > Sum to get the total number of on-time milestones per group.
  • Drag your "Milestone ID" column to Values—it will default to a count, which gives you the total milestones per group.

3. Add a Calculated Field for On-Time Percentage #

Now let's add the custom metric that will update automatically per group:

• Click any cell inside the pivot table.
• Go to PivotTable Analyze > Fields, Items, & Sets > Calculated Field.
• In the dialog box:
  • Name your field (e.g., "On-Time %").
  • Enter a formula using your existing value field names (use the exact names shown in your Values area):

    =('Sum of OnTime'/'Count of Milestone ID')
    

  • Click Add, then OK. The new calculated field will appear in your Values area.

4. Adjust Layout to Show Values Per Group Row #

Choose the layout that fits your needs:

Option A: Metrics as Columns (Same Row as Group) #

This is the default clean layout—each group row will have columns for "On-Time Milestones", "Total Milestones", and "On-Time %". To polish it:

• Right-click any value column header > Value Field Settings to rename fields (e.g., change "Sum of OnTime" to "On-Time Milestones").
• Format the percentage column: Select the column > Home > Number Format > Percentage.

Option B: Metrics as Sub-Rows Under Each Group #

If you want each metric to appear as a separate row under its group:

• Drag the Values field (found in the Fields pane) below your grouping column in the Rows area.
• Now each group will expand to show sub-rows for each metric (e.g., "Project A" > "On-Time Milestones", "Project A" > "Total Milestones", "Project A" > "On-Time %").

5. Set Up Auto-Refresh #

Since your source data is refreshable, ensure your pivot table updates with it:

• Right-click the pivot table > Refresh after updating your source data.
• If your source connects to an external database, set up automatic refresh: Go to Data > Queries & Connections > Properties > Check "Refresh data when opening the file" or set a custom refresh interval.

Troubleshooting Tips #

• If your calculated field breaks, double-check that field names in the formula match exactly (spaces and capitalization matter!).
• If the pivot table misses new data, confirm your source is an Excel Table—if not, re-select the full data range in PivotTable Analyze > Change Data Source.

内容的提问来源于stack exchange，提问作者Indy-Jones