多工作表逾期/30天内到期任务汇总表制作技术问询

Hey there! Let's figure out how to build that summary table you need for your 7 team worksheets. I've got two reliable methods to share—one that's super easy to refresh, and another using formulas if that's more your style.

This is my go-to because it handles combining data from multiple sheets effortlessly, and you can refresh the summary with one click when your team data updates.

• 1. Head to the Data tab, click Get Data > From File > From Excel Workbook, and select your current workbook.
• 2. In the Navigator pane, hold down Ctrl and select all 7 team worksheets. Then click Combine & Load > Combine & Load To...
• 3. Choose Only Create Connection, check the box for Enable load, then hit OK.
• 4. Go to Data > Queries & Connections, right-click the combined connection you just made, and select Edit to open the Power Query Editor.
• 5. Clean up and filter your data in the editor:
  • First, remove blank rows and category headers: Click Home > Remove Rows > Remove Blank Rows. Then use the filter dropdown on the Action column to uncheck any category title values (like those blank-row headers in your example) to exclude them.
  • Next, filter the Status column to keep only Overdue and Due Within 30 Days—just check those two options in the filter dropdown.
  • Finally, keep only the columns you need: Hold Ctrl, select the Action and Team Name columns, right-click them, and choose Remove Other Columns.
• 6. Click Home > Close & Load To..., select Table, pick where you want your summary (e.g., a new worksheet named "Summary"), and click OK.
• Pro tip: Whenever your team sheets get updated, just right-click the summary table and hit Refresh to pull in the latest tasks.

If you'd rather stick with formulas, here's how to do it depending on your Excel version.

For Excel 365/2021 (Dynamic Array Support) #

This formula will automatically combine, filter, and extract your data in one go:

1. Make a new worksheet called "Summary".
2. In cell A1, type Action; in B1, type Team.
3. In cell A2, paste this formula (adjust sheet names/ranges to match yours):

=LET(
    allData, VSTACK(Team1:Team7!C2:J1000), // Replace Team1:Team7 with your actual sheet names; adjust C2:J1000 to cover all task rows
    filteredRows, FILTER(allData, (INDEX(allData,,8)="Overdue")+(INDEX(allData,,8)="Due Within 30 Days"), ""),
    actions, INDEX(filteredRows,,1),
    teams, INDEX(filteredRows,,4),
    HSTACK(actions, teams)
)

• This formula stacks all data from your 7 team sheets, filters for the two statuses you care about, and pulls just the Action and Team columns into your summary.

For Older Excel Versions (Pre-365) #

You'll need to use array formulas with INDEX and SMALL—note this is less efficient for large datasets:

1. In cell A2 of your Summary sheet, enter this formula and press Ctrl+Shift+Enter to activate it as an array formula:

=IFERROR(INDEX(Team1!$C:$C, SMALL(IF((Team1!$J:$J="Overdue")+(Team1!$J:$J="Due Within 30 Days"), ROW(Team1!$C:$C)-MIN(ROW(Team1!$C:$C))+1), ROW(A1))), "")

2. Drag this formula down until you get blank cells, then repeat the process for each team sheet (or chain them together with IFERROR).
3. For the Team column (cell B2), use this array formula (again, Ctrl+Shift+Enter):

=IFERROR(INDEX(Team1!$F:$F, SMALL(IF((Team1!$J:$J="Overdue")+(Team1!$J:$J="Due Within 30 Days"), ROW(Team1!$F:$F)-MIN(ROW(Team1!$F:$F))+1), ROW(A1))), "")

Quick Notes #

• Double-check that all your team worksheets have the exact same column layout (you mentioned they do, which is perfect—this avoids messy errors).
• If your category header rows have a consistent marker (like blank values in the DaysToDue column), you can add an extra filter in Power Query to exclude those rows even faster.

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