Excel数据整理：统计多次迟到的救护车班组及累计迟到时长

Got it, let's walk through how to tackle this Excel task—whether you prefer a quick formula-based approach for small datasets or an automated setup that handles daily new sheets, I’ve got you covered!

方法1：手动公式汇总（适合数据量不大的场景） #

First, we need to consolidate all your daily Excel sheets into one master table to simplify stats:

• Create a new worksheet named 全量数据. Copy and paste all rows from each daily sheet into this master tab, and make sure to add a 日期列 if your daily sheets don’t already have one (this helps verify individual records later). Keep consistent headers across all data:
  • A: 日期
  • B: 是否按时待命
  • C: 延迟分钟数（正数=迟到，负数=提前）
  • D: 当班班组
  • E: 迟到原因

Next, create your target stats worksheet (e.g., 班组迟到统计) and use these formulas:

1. Extract unique teams: In cell A2, enter =UNIQUE('全量数据'!D:D)—this will auto-list every distinct team that’s appeared.
2. Count late occurrences: In cell B2, enter =COUNTIFS('全量数据'!D:D,A2,'全量数据'!C:C,">0"). Drag this formula down to fill all rows. This counts how many times each team was late (only counts positive delay values).
3. Calculate total late minutes: In cell C2, enter =SUMIFS('全量数据'!C:C,'全量数据'!D:D,A2,'全量数据'!C:C,">0"). Drag down to fill—this sums up all the positive delays for each team to get total late time.
4. Filter teams with >1 late instances: Select the header row (A1:C1), go to the Data tab, click Filter. Then use the filter dropdown in column B to select "Greater Than" → 1. You’ll now see only teams that were late more than once, with their total late time.

方法2：Power Query自动化（适合每日新增表格，无需手动复制粘贴） #

If you’re adding a new daily sheet every day, manual consolidation will get tedious. Power Query lets you automate the entire process:

1. Go to the Data tab → Get Data → From File → From Workbook. Select your current Excel file.
2. In the Navigator window, hold Ctrl to select all your daily sheets (skip the stats/master sheets we’ll create), then click Transform Data.
3. In the Power Query Editor:
   • Click Append Queries → Append Queries as New to stack all rows from the daily sheets into one table.
   • (Optional but helpful) Add a custom column to mark late entries: Go to Add Column → Custom Column, enter =if [延迟分钟数] > 0 then 1 else 0, name it 迟到标记.
   • Click Transform → Group By. Configure the grouping like this:
     • Group by: 班组
     • New column 1: Name it 迟到次数, Operation: Sum, Column: 迟到标记
     • New column 2: Name it 累计迟到时长, Operation: Sum, Column: 延迟分钟数
     • Pro tip: Before grouping, you can filter the table to only include rows where 延迟分钟数 > 0—this ensures we don’t count early arrivals in our totals.
4. Filter the table to show only rows where 迟到次数 > 1, then click Close & Load to export the results to a new worksheet. Going forward, just refresh the query (right-click the table → Refresh) whenever you add a new daily sheet, and the stats will update automatically.

Key Notes #

• Make sure all daily sheets have exact matching column headers—this is critical for both methods to work smoothly.
• Use IFERROR with formulas if some sheets have empty delay values (e.g., =IFERROR(SUMIFS(...), 0) to avoid #VALUE! errors).
• Double-check that positive values in the delay column represent late arrivals (adjust the >0 in formulas if your data uses negative values for lateness instead).

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