英文:
Find Last Occurrence of A Duplicate, Sum All Duplicate Values, and Insert Result Only At The Last Duplicate Occurence's Address
问题
在其中一列中，我有重复的数值，我想要找到该列中重复值的最后一次出现，然后将属于这些重复值的所有数值相加，并将求和结果仅插入到重复值的最后一次出现的行/列地址。
在下面的示例图像中，列A包含重复的数值，在示例中，列已排序，但实际上可能不会排序。列D包含需要对重复值进行求和的数值。
A2中值的最后一次出现出现在A4中，所以我需要对D2到D4进行求和，并将结果放在一个新的列中，比如我将创建的E4列。因此，E4中的结果将仅为18:00:00，其他出现的地方将保持空白。
英文:
In one of the columns, I have duplicate values, I want to find the last occurrence of the duplicate value in that column, and then sum all the values belonging to those duplicates, and insert the result of the sum only at the last occurrence of the duplicate's row/column address.
In the sample image below, column A contains the duplicate values, in the sample the column is sorted but in reality it won't be. Column D contains the values that need to be summed for the duplicates.
The last occurence of the value in A2 appears in A4, so what I require is to sum D2 to D4 and place the result, let's say in a new column that I will create, E4. So the result in E4 will be 18:00:00 only, and other occurrence's will remain blank.
I can easily achieve this with VBA, but unfortunately I'm trying to automate the sheets using formulas intended to work for Office 2016 where macro-enabled files are also not allowed to be opened/runned due to security policies. My development environment is Excel 365 for Mac. If suggestions based on 365 are also provided, I can look for converting them to work on 2016.
I have tried multiple SUMPRODUCT, SUMIF, COUNTIF, LOOKUP etc to try and circumvent this issue but unable to get the desired result or unable to combine the functions to achieve the desired result.
答案1
得分: 3
让我知道这是否有效，对我来说似乎有效，但我不能100%确定。如果这有效，那么它适用于Excel 2010。
名为"data"的表必须从A列开始
| 姓名 | 值 | fin |
|---|---|---|
| a | 1 | |
| b | 1 | |
| c | 1 | 1 |
| a | 1 | |
| a | 1 | 3 |
| d | 1 | |
| d | 1 | 2 |
| b | 1 | 2 |
"fin"列的公式:
=IF(ISERROR(MATCH([@name],INDIRECT("A" & (ROW() + 1) & ":A" & (ROWS(data)+ROW(data))),0)),SUMIF([name],[@name],[val]),"")
截图:
英文:
Let me know if this works, it appears to me that it does but I'm not 100% sure. If this does work, it is OK for Excel 2010.
Table called "data", must start from column A
| name | val | fin |
|---|---|---|
| a | 1 | |
| b | 1 | |
| c | 1 | 1 |
| a | 1 | |
| a | 1 | 3 |
| d | 1 | |
| d | 1 | 2 |
| b | 1 | 2 |
formula for the "fin" column:
=IF(ISERROR(MATCH([@name],INDIRECT("A" & (ROW() + 1) & ":A" & (ROWS(data)+ROW(data))),0)),SUMIF([name],[@name],[val]),"")
screenshot:
答案2
得分: 1
我已经尝试了多次SUMPRODUCT、SUMIF、COUNTIF
这正是你所需要的，但在COUNTIF中需要使用动态范围：
C2单元格中的公式是：
=IF(SUMPRODUCT(--(COUNTIF($A$2:A2,A2)=COUNTIF($A$2:$A$22,A2)))=0,"",SUMIF($A$2:$A$22,A2,$B$2:$B$22))
请注意第一个COUNTIF使用了动态范围（当你拖动公式时，它会增加大小）。另外，我在使用SUMIF，而不是SUMIFS（但SUMIFS也可以工作）。
英文:
I have tried multiple SUMPRODUCT, SUMIF, COUNTIF
That's exactly what you need but with dynamic ranges in the COUNTIF:
Formula in cell C2 is:
=IF(SUMPRODUCT(--(COUNTIF($A$2:A2,A2)=COUNTIF($A$2:$A$22,A2)))=0,"",SUMIF($A$2:$A$22,A2,$B$2:$B$22))
Please, notice the first COUNTIF used dynamic range (it increases size when you drag down formula). Also, I'm using SUMIF, not SUMIFS (but SUMIFS will work too)
答案3
得分: 0
=IF(COUNTIF($A$2:$A$29, A2)>1, IF(ISNUMBER(XLOOKUP(A2, A3:$A$29, A3:$A$29)), "",TRUE), TRUE)
将上述公式粘贴到单元格 E2 中，将在每个最后出现的数值实例处返回 TRUE 值，这可以用于构建 SUMIF 函数。
我犹豫是否要进一步进行，因为我对 Office 2016 不是很熟悉（我相信需要替换 XLOOKUP），但希望你能根据我的答案进行调整使其适用。
英文:
=IF(COUNTIF($A$2:$A$29, A2)>1, IF(ISNUMBER(XLOOKUP(A2, A3:$A$29, A3:$A$29)), "",TRUE), TRUE)
Pasting the above in E2 will return a TRUE value at every instance of a last occurring value, which could be used to build a SUMIF function.
I hesitate to go further as I'm not too experienced with Office 2016 (I believe XLOOKUP will need replacing), but hopefully you can adapt my answer to work.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论