Excel多列表格合并重复条目并求和（游戏王卡牌归档）

Hey there! Let's tackle this card inventory problem—you're so close with Power Query, we just need to add a few steps to get the summarized table you want without losing any columns. Here are two solid methods tailored to your experience level:

Method 1: Upgrade Your Existing Power Query (Best for Many Sheets) #

Since you already started with Power Query, this is the most efficient path. We'll fix the recursive issue properly and add a grouping step to sum duplicate card counts.

Step-by-Step Power Query Modification #

1. Open your existing Power Query editor (Data > Get Data > Launch Power Query Editor)
2. Replace your current code with this updated version:

let
    // Get all sheets in the workbook
    Source = Excel.CurrentWorkbook(),
    // Filter out the master sheet (rename "Master_Inventory" to your final sheet name)
    // Also ensure we only include sheets with all 4 required columns
    #"Filtered Valid Sheets" = Table.SelectRows(Source, each 
        [Name] <> "Master_Inventory" and 
        List.ContainsAll(Table.ColumnNames([Content]), {"Card Name", "Category", "No.", "Game"})
    ),
    // Expand all the card data columns
    #"Expanded Card Data" = Table.ExpandTableColumn(#"Filtered Valid Sheets", "Content", 
        {"Card Name", "Category", "No.", "Game"}, 
        {"Card Name", "Category", "No.", "Game"}
    ),
    // Remove the sheet name column we don't need
    #"Removed Sheet Names" = Table.RemoveColumns(#"Expanded Card Data",{"Name"}),
    // Group by unique card identity (Card Name + Category + Game) and sum the No. column
    #"Summarized Card Counts" = Table.Group(#"Removed Sheet Names", 
        {"Card Name", "Category", "Game"}, 
        {{"Total No.", each List.Sum([No.]), type number}}
    )
in
    #"Summarized Card Counts"

Key Improvements Explained: #

• No More Hardcoded Sheet Exclusions: Instead of excluding Rayquaza_deck, we exclude your final master sheet (rename "Master_Inventory" to whatever you call your merged table sheet) and only include sheets that have all 4 required columns—this prevents accidental missing data or recursion.
• Automatic Summation: The Table.Group step groups cards by their unique identity (name + category + game) and sums up the No. values for duplicates.

How to Use: #

• After pasting the code, click Close & Load to send the summarized table to a new sheet.
• If you add new cards/sheets later, just go to Data > Refresh All to update the master table.

Method 2: Excel Functions (For Those Who Prefer Formula Approach) #

If you'd rather stick with functions you already know, you can use UNIQUE + SUMIFS to build your master table. This works well if you don't want to dive deeper into Power Query.

Step-by-Step: #

1. Extract Unique Card Combinations:
   In a new sheet, enter this formula in cell A2 to get all unique Card Name + Category + Game entries across all your sheets:

   =UNIQUE(STACK(Sheet1!A2:D, Sheet2!A2:D, Sheet3!A2:D, ...))
   

   Replace Sheet1!A2:D with every deck/single card sheet's data range (skip headers).

2. Sum Duplicate Counts:
   In cell D2 (next to your first unique entry), enter this formula to sum the No. values for that card:

   =SUMIFS(
       STACK(Sheet1!D2:D, Sheet2!D2:D, Sheet3!D2:D, ...),
       STACK(Sheet1!A2:A, Sheet2!A2:A, Sheet3!A2:A, ...), A2,
       STACK(Sheet1!B2:B, Sheet2!B2:B, Sheet3!B2:B, ...), B2,
       STACK(Sheet1!C2:C, Sheet2!C2:C, Sheet3!C2:C, ...), C2
   )
   

   Drag this formula down to apply it to all unique entries.

Note: #

• This method requires listing every sheet explicitly in the STACK function, which can get tedious if you have 10+ sheets. Power Query is better for scalability here.

Quick Checks to Avoid Issues: #

• Make sure all sheets have exact matching column names (e.g., "Card Name" not "CardName" or "card name")—Power Query and functions are case-sensitive for column names.
• Ensure the No. column is formatted as a number (not text) in all sheets, otherwise summation won't work.

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