Excel中如何按相同ID汇总多列数值？含示例说明

Hey there! Let's tackle this Excel data aggregation task where you need to sum up dr_amount_inr and cr_amount_inr values for each unique purchase_id. I'll walk you through the solution with sample data and clear methods below.

Sample Raw Data #

Here’s what your source data might look like:

Desired Aggregated Result #

This is the output you want, with summed values for each unique purchase_id:

Two Easy Methods to Achieve This #

1. Using a Pivot Table (Quick & Visual) #

This is the simplest way for most cases:

• Select your entire raw data range (including headers)
• Go to the Insert tab → click PivotTable
• In the PivotTable Fields pane:
  • Drag purchase_id to the Rows area
  • Drag dr_amount_inr to the Values area (it will default to Sum; if not, click the dropdown → Value Field Settings → choose Sum)
  • Drag cr_amount_inr to the Values area as well, same Sum setting
• Rename the value columns to total_dr_amount and total_cr_amount for clarity

2. Using SUMIFS Formula (Flexible for Custom Layouts) #

If you prefer a formula-driven approach (e.g., to keep the result in a specific range):

• First, extract unique purchase_id values (you can use the UNIQUE() function in Excel 365/2021: =UNIQUE(A:A) where A is your purchase_id column)
• For the total DR amount, use: =SUMIFS(B:B, A:A, D2) (replace B with your dr_amount_inr column, A with purchase_id, D2 with the unique ID cell)
• For the total CR amount, use: =SUMIFS(C:C, A:A, D2) (replace C with your cr_amount_inr column)
• Drag the formulas down to apply to all unique IDs

Either method will get you the aggregated sums you need. Let me know if you run into any snags with specific Excel versions!

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