Excel基金行业权重汇总公式优化：新增行自动更新需求

Your current formula relies on hard-coded cell references, which is why it fails to automatically include new rows when added. Here are two efficient, low-maintenance solutions to fix this:

Recommended: Use Excel Tables (Built-In Dynamic Ranges) #

Excel Tables automatically expand to include new rows, so your formula will update without manual adjustments:

1. Select your entire data range (including headers, e.g., B1:E17 where B1 is "Weight" and C1/E1 are industry names).
2. Press Ctrl+T, check "My table has headers", then click OK. The range becomes a table (default name like Table1).
3. In the cell below the table (originally C18), enter this formula:

   =SUMPRODUCT(Table1[Weight], Table1[Industry 1])/SUM(Table1[Weight])
   

   Replace Industry 1 with the actual header name of column C.
4. Drag the formula across columns D, E, etc. Each column will reference its own industry header.
5. Now, when you add a new row to the table (type below the last row or use Table Tools > Insert Row), the formula will automatically include the new row's data.

Alternative: Dynamic Range with SUMPRODUCT #

If you prefer not to use tables, use a dynamic range that adjusts based on the position of your total row:

1. In cell C18, replace your existing formula with:

   =SUMPRODUCT($B$2:INDEX($B:$B, ROW($B$18)-1), C2:INDEX(C:C, ROW($B$18)-1))/$B$18
   

2. Drag this formula across columns D, E, etc.
   • INDEX($B:$B, ROW($B$18)-1) targets the last row of data above your total row (B18). When you insert rows above B18, ROW($B$18) increases, expanding the range to include new rows.

Both methods replace your lengthy SUM of products with SUMPRODUCT, which is cleaner and more efficient for weighted calculations.

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