Excel跨表关联计算：按项目归集参与员工及对应成本

Alright, let's break this down into actionable steps to build your project cost list. You've got two tables—Employees with salary/insurance data, and Projects tracking hours per employee per project. We need to tie these together to get each project's total labor costs (salary + insurance for each worker).

First, let's assume your tables are set up like this:

• Employees Table (Sheet1): A=Year, B=Name, C=Type, D=Amount
• Projects Table (Sheet2): A=Year, B=Project_Name, C=Employee_Name, D=Hours_Worked

Step 1: Add Salary and Insurance Columns to Projects Sheet #

In Sheet2, add two new columns: E=Salary, F=Insurance.

For Salary (E2 cell):

If you have Excel 365/2021 (or later), use XLOOKUP for cleaner syntax:

=XLOOKUP(1, (Sheet1!$A:$A=Sheet2!$A2)*(Sheet1!$B:$B=Sheet2!$C2)*(Sheet1!$C:$C="Salary"), Sheet1!$D:$D, "No Data")

This formula matches the year, employee name, and "Salary" type to pull the correct amount.

If you're on an older Excel version, use INDEX/MATCH instead:

=INDEX(Sheet1!$D:$D, MATCH(1, (Sheet1!$A:$A=Sheet2!$A2)*(Sheet1!$B:$B=Sheet2!$C2)*(Sheet1!$C:$C="Salary"), 0))

Note: Press Ctrl+Shift+Enter to enter this as an array formula in older Excel.

For Insurance (F2 cell):

Similar logic, just change the Type to "Insurance":
XLOOKUP version:

=XLOOKUP(1, (Sheet1!$A:$A=Sheet2!$A2)*(Sheet1!$B:$B=Sheet2!$C2)*(Sheet1!$C:$C="Insurance"), Sheet1!$D:$D, "No Data")

INDEX/MATCH version:

=INDEX(Sheet1!$D:$D, MATCH(1, (Sheet1!$A:$A=Sheet2!$A2)*(Sheet1!$B:$B=Sheet2!$C2)*(Sheet1!$C:$C="Insurance"), 0))

Drag these formulas down to apply them to all rows in the Projects sheet.

Step 2: Calculate Total Cost per Employee per Project #

Add a G column for Total Employee Cost:

=E2+F2

This gives you the sum of salary and insurance for each employee on the project.

Step 3: Aggregate Costs by Project #

To get the total cost per project, you can use SUMIFS in a new summary table:

• Create a table with unique project names (use UNIQUE(Sheet2!$B:$B) for this in Excel 365)
• For each project, calculate total cost:

=SUMIFS(Sheet2!$G:$G, Sheet2!$B:$B, [@Project_Name])

If you anticipate more data later, Power Query is a more scalable way to join and aggregate the tables:

1. Go to Data > Get Data > From File > From Excel Workbook (select your file)
2. Select both the Employees and Projects tables, load them into Power Query Editor
3. In the Projects query, go to Home > Merge Queries > Merge Queries as New
   • Select Projects as the first table, Employees as the second
   • Match columns: Year (Projects) to Year (Employees), Employee_Name (Projects) to Name (Employees)
   • Join kind: Left Outer (all from first, matching from second)
4. Expand the merged Employees column, only select Type and Amount
5. Pivot the table: Go to Transform > Pivot Column
   • Values column: Amount
   • Columns to pivot: Type
   • Aggregate value function: Sum (each employee-year-type is unique, so Sum works perfectly)
6. Add a custom column for Total Cost: [Salary] + [Insurance]
7. Group by Project_Name to get total project cost: Transform > Group By
   • Group by: Project_Name
   • New column name: Total Project Cost
   • Operation: Sum
   • Column: Total Cost
8. Load the result back to Excel as a table.

This method auto-updates if you add new data—just right-click the table and select Refresh.

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