Excel 2016多表数据合并求助：按ID匹配计算生成结果表

Hey there! Let's tackle this Excel 2016 problem step by step. Based on your requirements, here are a couple of reliable methods to get your result table updated automatically whenever your input or supplementary tables change:

Power Query is ideal here because it simplifies combining tables and lets you refresh the result with one click whenever your source data changes—perfect for monthly updates.

1. Load tables into Power Query

   • Select your input table, go to the Data tab > From Table/Range (ensure your table has clear headers)
   • Repeat this step for your supplementary table, so both are loaded into the Power Query editor

2. Combine and aggregate data

   • In the Power Query editor, go to the Home tab > Append Queries > Append Queries as New
   • Select both your input and supplementary tables, then click OK to merge all rows
   • Select the ID column, navigate to the Transform tab > Group By
     • In the Group By window:
       • Set Group by to ID
       • Add two aggregation rules:
         • For CASH: Choose Sum as the operation, then select your CASH column
         • For HOURS: Choose Sum as the operation, then select your HOURS column
   • Click OK, and you’ll have a table grouped by ID with summed values for matching entries

3. Load the result back to Excel

   • Go to the Home tab > Close & Load To > Choose Table and pick where you want your result table to appear
   • Whenever your input or supplementary tables update, just right-click the result table > Refresh to sync the changes

If you’d rather use direct formulas in your result table, here’s how to cover all three scenarios:

First, generate a unique list of all IDs from both tables. Enter this formula in the first cell of your result table’s ID column (e.g., cell A2):

=IFERROR(INDEX(InputTable[ID], ROWS($A$2:A2)), IFERROR(INDEX(SupplementaryTable[ID], ROWS($A$2:A2)-ROWS(InputTable[ID])), ""))

Drag this formula down until you see blank cells to cover all IDs from both tables.

For the CASH column (e.g., cell B2):

=SUMIF(InputTable[ID], $A2, InputTable[CASH]) + SUMIF(SupplementaryTable[ID], $A2, SupplementaryTable[CASH])

For the HOURS column (e.g., cell C2):

=SUMIF(InputTable[ID], $A2, InputTable[HOURS]) + SUMIF(SupplementaryTable[ID], $A2, SupplementaryTable[HOURS])

Drag these formulas down to all rows of your result table. This will automatically sum values for IDs present in both tables, pull the single value for IDs only in one table, and return 0 for any unmatched entries (you can adjust this with an IF statement if you prefer blanks instead of 0).

Quick Notes: #

• Replace InputTable and SupplementaryTable with the actual named ranges of your tables (go to the Formulas tab > Define Name to name your tables for easier reference)
• For the formula method, if new IDs are added to your input or supplementary tables, you’ll need to drag the ID formula down to include them. Power Query eliminates this extra step with a simple refresh.

If you run into any snags—like formula errors or issues with Power Query refresh—feel free to share more details about your table structure, and I’ll help you troubleshoot further!

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