如何用Excel公式按Week计算不同Ano的Points去重求和？
Got it, let's tackle this Excel problem where you need to sum the unique Points values for each distinct Ano, grouped by Week. First, let's recap your sample data and desired outcome for clarity:
Sample Data
| Week | TID | Ano | Points |
|---|---|---|---|
| 1 | 111 | ANo1 | 1 |
| 1 | 112 | ANo1 | 1 |
| 2 | 221 | ANo2 | 0.25 |
| 2 | 222 | ANo2 | 0.25 |
| 2 | 223 | ANo2 | 0.25 |
| 2 | 331 | ANo3 | 1 |
| 2 | 332 | ANo3 | 1 |
| 2 | 333 | ANo3 | 1 |
| 2 | 999 | Ano9 | 0.25 |
| 2 | 998 | Ano9 | 0.25 |
| 3 | 421 | ANo4 | 0.25 |
| 3 | 422 | ANo4 | 0.25 |
| 3 | 423 | ANo4 | 0.25 |
| 3 | 531 | ANo5 | 0.5 |
| 3 | 532 | ANo5 | 0.5 |
| 3 | 533 | ANo5 | 0.5 |
Desired Result
| Week | Points_Sum |
|---|---|
| 1 | 1 |
| 2 | 1.50 |
| 3 | 0.75 |
Solutions Based on Excel Version
1. For Excel 365 / Excel 2021 (Dynamic Array Support)
This is the most efficient approach if your Excel supports dynamic arrays. Assume your raw data is in range A2:D16 (headers in row 1):
Step 1: Get Unique Weeks
In cellF2, enter this formula to automatically generate all unique Week values (it will spill down automatically):=UNIQUE(A2:A16)Step 2: Sum Unique Points per Week
In cellG2, use this formula to calculate the sum for each Week (it will also spill down to match the unique Weeks):=BYROW(UNIQUE(A2:A16), LAMBDA(wk, SUM(UNIQUE(FILTER(D2:D16, A2:A16=wk, "")))))
How this works:
UNIQUE(A2:A16)pulls all distinct Week values.BYROWloops through each unique Week, applying the lambda function to calculate the sum.FILTER(D2:D16, A2:A16=wk)grabs all Points values for the current Week.UNIQUE(...)removes duplicate Points values for the same Ano, thenSUMadds them up.
2. For Older Excel Versions (No Dynamic Arrays)
If you're using an older Excel version (pre-2021), use this traditional array formula. First, manually list the unique Weeks in cells F2:F4:
In cell G2, enter this formula and press Ctrl+Shift+Enter (not just Enter) to confirm it as an array formula, then drag it down to G4:
=SUM(IF(FREQUENCY(IF(A$2:A$16=F2, MATCH(D$2:D$16, D$2:D$16, 0)), ROW(D$2:D$16)-ROW(D$2)+1), D$2:D$16))
How this works:
IF(A$2:A$16=F2, MATCH(D$2:D$16, D$2:D$16, 0))filters rows for the current Week, then gets the first occurrence position of each Points value.FREQUENCY(...)counts how many times each position appears—only first occurrences have a count of 1.IF(...)retains only the first occurrence of each Points value, thenSUMadds them up.
内容的提问来源于stack exchange，提问作者Sathish
