如何使用Pivot Table计算每日苹果占比并生成平均总计？

Here's a step-by-step, actionable method to build the pivot table you need:

1. Prepare Raw Data #

Ensure your dataset includes columns like Date, Fruit, and Quantity (use a constant value of 1 for Quantity if each row represents a single fruit entry). Add an auxiliary column named Apple Count with this formula:

=IF([@Fruit]="Apple", [@Quantity], 0)

This column isolates apple quantities while setting non-apple rows to 0.

2. Build the Base Pivot Table #

• Select your full data range and insert a pivot table.
• In the PivotTable Fields pane:
  • Drag Date to the Rows area.
  • Drag Apple Count to Values (set to summarize by Sum).
  • Drag Quantity to Values (set to summarize by Sum).

3. Calculate Daily Apple Percentage #

• Right-click the Sum of Quantity column header > Value Field Settings > Show Values As > % of Row Total.
• Rename this column to Apple % for clarity. You’ll now see the daily percentage of apples relative to total fruits that day.

4. Adjust Grand Total to Show Average of Percentages #

By default, the grand total will display the overall apple percentage (total apples / total fruits). To switch it to the average of daily percentages:

• Right-click the grand total cell in the Apple % column > Value Field Settings.
• Under Summarize value field by, select Average.
• Click OK to apply the change.

Troubleshooting #

• If your earlier auxiliary column attempt failed, confirm you’re summarizing Apple Count and Quantity as sums (not counts) when working with quantity values.
• If dates are grouped incorrectly, right-click dates in the Rows area > Group to adjust date grouping (e.g., by day, month).

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