基于OFFSET的Excel动态求和：十万行CSV数据集预处理需求

Got it, let's work through this without needing DAX or VBA—just straightforward Excel functions that can handle your 100k+ rows smoothly. First, let's align on your data structure: it sounds like you have data chunks (subsets) where each starts with a count row labeled 1, increments to some random number ≥1, and each count row is immediately followed by a data row. We’ll group each chunk and calculate its total data sum.

Here's the step-by-step breakdown:

First, we need a way to tag every row in the same chunk with the same ID. Let’s assume your count rows are in Column A, and your data values are in Column B (adjust column letters to match your actual CSV).

1. Insert a new column (let’s use Column C) for Group IDs.
2. In cell C2, enter this formula:

   =IF(A2=1, MAX($C$1:C1)+1, $C1)
   

3. Drag the fill handle all the way down to the last row of your data.

How this works: #

• When it hits a count row with 1 (the start of a new chunk), it generates a new group ID by adding 1 to the highest ID above it.
• For all other rows (counts >1 and data rows), it inherits the group ID from the row above, keeping the entire chunk grouped together.

You have two options depending on where you want the sum to appear:

Option 1: Show the Sum in the Last Row of Each Chunk #

If you want the total sum to appear in the final row of each chunk (right after the last count/data pair):

1. Insert another new column (Column D) for sums.
2. In cell D2, enter this array formula (for pre-Excel 365, press Ctrl+Shift+Enter after typing; Excel 365 users can just press Enter):

   =IF(A2=MAX(IF($C$2:$C$100000=C2,$A$2:$A$100000)), SUMIF($C$2:$C$100000,C2,$B$2:$B$100000), "")
   

3. Drag the fill handle down to the last row.

How this works: #

• The IF check identifies if the current row's count is the maximum count in its group (i.e., the last row of the chunk).
• If yes, it uses SUMIF to add up all data values in that group. If not, it leaves the cell blank for a clean look.

Option 2: Create a Summary Table of All Chunk Sums #

If you want a separate, clean table with each group ID and its total sum (perfect for PowerBI reporting):

1. Navigate to an empty area of your sheet (e.g., cell F1).
2. In F1, enter Group ID; in G1, enter Total Sum.
3. In F2, enter this dynamic array formula (Excel 365):

   =UNIQUE(C2:C100000)
   

   This will automatically spill all unique group IDs into Column F.
4. In G2, enter:

   =SUMIF(C:C,F2,B:B)
   

   Drag this down to match the number of unique group IDs.

• Double-check a few chunks manually to confirm sums are correct (critical for large datasets!).
• Save your processed Excel file (or re-save as CSV if you prefer).
• When importing into PowerBI, you can use either the original data with group IDs and chunk sums, or the summary table—whichever fits your reporting needs better.

Pro Tips for 100k+ Rows: #

• Avoid full column references (like C:C) if possible; use exact row ranges (e.g., C2:C105000) to speed up calculations.
• Ensure your count column (Column A) is formatted as numbers, not text—text values will break the group ID formula.

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