如何直接从源数据计算分组站点的日均工单量（支持自动更新）

Got it, let's break this down to solve your pivot table manual update headache and build auto-calculating daily averages directly from your source data—even when you add new rows or filter by groups like site size, category, etc.

First: Prep Your Source Data for Auto-Updates #

First, convert your raw repair ticket data into an Excel Table (press Ctrl+T when your cursor is in the data range, check "My table has headers"). Name it something clear like RepairTicketData (go to the Table Design tab in the ribbon to rename it). This is key because any new rows added to the table will automatically be included in your formulas—no more adjusting cell ranges manually.

Assume your source table has these columns (adjust to match your actual data):

• SiteName (your site identifiers like Site 1, Site 2)
• Date (the work order date)
• TicketCount (number of tickets that day)
• SiteSize (e.g., Small/Medium/Large)
• SiteCategory (e.g., Tier 1 City/Tier 2 City)
• Any other grouping columns you need (up to 6-8, as you mentioned)

1. Calculate Daily Average for Individual Sites #

To replicate your existing "Avg Column" but make it auto-update, use this formula (replace A2 with the cell containing your site name):

=SUMIFS(RepairTicketData[TicketCount], RepairTicketData[SiteName], A2) / COUNTIFS(RepairTicketData[SiteName], A2, RepairTicketData[TicketCount], ">0")

• What it does:
  • SUMIFS totals all tickets for the specified site.
  • COUNTIFS counts how many days that site had at least 1 ticket (matches your "site effective days" logic from the example—Site 1 has 2 effective days, so 15/2=7.5).
• Drag this formula down for all your sites, and it’ll auto-update if you add new sites or ticket data.

2. Calculate Averages for Groups (Size, Category, Etc.) #

If you need averages for groups (e.g., all Medium-sized sites, or all Tier 1 City sites), adjust the formula to target your grouping column instead of individual sites.

Example 1: Total Average for a Group (Total Tickets / Total Effective Days for the Group)

Replace D2 with the cell containing your group label (e.g., "Medium"):

=SUMIFS(RepairTicketData[TicketCount], RepairTicketData[SiteSize], D2) / COUNTIFS(RepairTicketData[SiteSize], D2, RepairTicketData[TicketCount], ">0")

Example 2: Average of Individual Site Averages for a Group

If you want the average of each site’s daily average within a group (instead of the group’s overall total average), use AVERAGEIFS with your individual site averages:

=AVERAGEIFS($F$2:$F$100, RepairTicketData[SiteSize], D2)

(Here, $F$2:$F$100 is the range where you calculated individual site averages.)

3. Handling "Effective Days" as Actual Operational Days (Not Just Ticket Days) #

If your "site effective days" refers to days the site was operational (even if no tickets came in), create a separate small table (call it SiteOperationalDays) with two columns: SiteName and OperationalDays. Then use this formula instead:

=SUMIFS(RepairTicketData[TicketCount], RepairTicketData[SiteName], A2) / VLOOKUP(A2, SiteOperationalDays, 2, FALSE)

This pulls the fixed operational day count for each site and divides total tickets by that number.

Why This Beats the Pivot Table Manual Column #

• Auto-updates: Add new tickets or sites to your RepairTicketData table, and all formulas recalculate automatically.
• No manual adjustments after filtering: Unlike your pivot table’s external column, these formulas work independently of pivot filters—they always use the full source data (or you can add filter criteria to the SUMIFS/COUNTIFS if you need to match pivot filters).

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