求Excel公式：合并无关联标识的实体多行货币数据至单行

Hey there! I get it—cleaning up tables where extra amount rows don’t have matching entity labels can be frustrating, even for Excel pros. Let’s walk through two solid solutions to get those amounts consolidated into single rows per entity.

First, let’s clarify the scenario with example tables (aligning with your original/target table setup):

Original Table (Messy Version) #

Target Table (Desired Outcome) #

Solution 1: Formula-Based Approach (For Small to Medium Datasets) #

This uses a helper column to "attach" entity labels to unlabeled amount rows, then consolidates with TEXTJOIN.

1. Add a Helper Column: Insert a new column (e.g., Column A, next to your Entity column). In cell A2 (assuming headers are in row 1), use this formula:

   =IF(B2<>"", B2, A1)
   

   Drag this formula down the entire column. It will fill empty entity cells with the last valid entity name above them.

2. Create Your Target Table: List all unique entities (use UNIQUE(B:B) to pull these automatically if you have Excel 365/2021).

3. Consolidate Amounts: In the "All Amounts" column of your target table, use TEXTJOIN with FILTER to grab all amounts for each entity:

   =TEXTJOIN(", ", TRUE, TEXT(FILTER(C:C, A:A=E2),"$#,##0.00"))
   

   • E2 = the entity cell in your target table
   • TEXT(...) ensures amounts keep their currency format when merged

Solution 2: Power Query Approach (For Large Datasets or One-Click Refresh) #

Power Query is automated, repeatable, and ideal for big datasets or regular data updates.

1. Load Data to Power Query: Select your original table → Go to the Data tab → Click From Table/Range (ensure your table has headers).

2. Fill Empty Entity Cells:

   • Select the Entity column → Go to the Transform tab → Click Fill → Down. This propagates the last entity name down to all empty rows below it.

3. Group and Merge Amounts:

   • Go to the Home tab → Click Group By.
   • Set grouping options:
     • Group by: Entity
     • New column name: All Amounts
     • Operation: Combine Values
     • Separator: ,
   • Click OK.

4. Load Back to Excel: Click Close & Load to bring the cleaned, consolidated table back into your workbook.

Both methods work perfectly—formulas are quick for small tables, while Power Query shines if you need to refresh data regularly or handle large volumes. Let me know if you need tweaks for your specific table structure!

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