vba 从连续的最后 3 个非空白单元格中获取值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16262263/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get values from last 3 non-blank cells in a row
提问by lethalMango
I currently have an excel worksheet similar to the following (#'s represent blank cells)
我目前有一个类似于以下的 excel 工作表（# 代表空白单元格）
1 2 3 4 5 6 7
37 21 30 32 25 22 34
# 17 26 28 27 17 31
# # # # # # 38
25 23 27 35 33 # #
27 11 23 # # # #
In column 8 I need the sum of the last 3 non-blank cells (the number of columns is increased regularly).
在第 8 列中，我需要最后 3 个非空白单元格的总和（列数定期增加）。
An example of what I need to achieve is below:
我需要实现的一个例子如下：
1 2 3 4 5 6 7 8
37 21 30 32 25 22 34 25+22+34=81
# 17 26 28 27 17 31 27+17+31=75
# # # # # # 38 N/A
25 23 27 35 33 # # 27+35+33=95
27 11 23 # # # # 27+11+23=61
I have managed to get very close with the LARGEfunction but obviously this only give me the 3 largest, not the last 3 non-blank:
我已经设法非常接近该LARGE函数，但显然这只给了我最大的 3 个，而不是最后 3 个非空白：
=(LARGE(C3:J3,1)+LARGE(C3:J3,2)+LARGE(C3:J3,3))
采纳答案by Dick Kusleika
Here's another one:
这是另一个：
=IF(COUNT(A2:G2)<3,"NA",SUM(G2:INDEX(A2:G2,LARGE(COLUMN(A2:G2)*(A2:G2<>""),3))))
From http://dailydoseofexcel.com/archives/2004/05/03/sum-last-three-golf-scores/
来自http://dailydoseofexcel.com/archives/2004/05/03/sum-last-three-golf-scores/
It identifies the column to sum from and since blank cells are zero, it doesn't matter how many are intermingled.
它标识要从中求和的列，并且由于空白单元格为零，因此混合多少个单元格并不重要。
回答by barry houdini
If data is in A1:G1 try this formula in H1
如果数据在 A1:G1 中，请在 H1 中尝试此公式
=IF(COUNT(A1:G1)>2,SUM(OFFSET(A1,0,MATCH(9.99E+307,A1:G1)-1,1,-3)),NA())
=IF(COUNT(A1:G1)>2,SUM(OFFSET(A1,0,MATCH(9.99E+307,A1:G1)-1,1,-3)),NA())
回答by user2140261
I know the pros here will hate this one. But I work with data in the hundreds of thousands of rows and millions of cells, And I prefer helper rows, and complex formulas over Array formulas any day. As it will cut calculations to fractions. Even if maintenance is more work, I spend less time fixing a formula once then waiting on calculations of arrays on every change of data (for me my data changes every 10 minutes) So, hundreds of thousands of arrays on millions of cells every 10 minutes will simply not work. So, here is just an alternative.
我知道这里的专业人士会讨厌这个。但是我处理数十万行和数百万个单元格中的数据，而且我更喜欢辅助行和复杂的公式，而不是数组公式。因为它会将计算减少到分数。即使维护是更多的工作，我花更少的时间来修复一个公式，然后等待每次数据更改时的数组计算（对我来说，我的数据每 10 分钟更改一次）因此，每 10 分钟数百万个单元格上的数十万个数组根本行不通。所以，这里只是一个替代方案。
=IF(COUNT(A2:G2)<3,"NA",IF(COUNT(E2:G2)=3,SUM(E2:G2),IF(COUNT(D2:G2)=3,SUM(D2:G2),
IF(COUNT(C2:G2)=3,SUM(C2:G2),IF(COUNT(B2:G2),SUM(B2:G2),IF(COUNT(A2:G2)=3,
SUM(A2:G2)))))))
If there's not 3 values to sum it will return NA, other wise working right to left will check for the first cell with from that cell to the end has 3 number in it then will sum that.
如果没有 3 个要求和的值，它将返回 NA，否则从右到左工作将检查第一个单元格，从该单元格到末尾有 3 个数字，然后将其求和。
If you data does involve more columns then your question. You can use dynamic ranges to clean up the code a little.
如果您的数据确实涉及更多列，那么您的问题。您可以使用动态范围来稍微清理代码。
Another working non-array formula:
另一个有效的非数组公式：
=SUM(IF(COUNT(A2:G2)=3,A2:G2,IF(COUNT(B2:G2)=3,B2:G2,IF(COUNT(C2:G2)=3,C2:G2,
IF(COUNT(D2:G2)=3,D2:G2,IF(COUNT(E2:G2)=3,E2:G2,"NA"))))))
simply put will sum what ever range from the end has a count of 3.
简单地说，将总结从最后开始的范围的计数为 3。
回答by Ryan
Newbee alert: I know the post is old however if anyones still out there, I'm keen to get help on derivative of the formula Dick provided whereby I'd like to run the count over several rows for example [2 in this case]:
Newbee 警报：我知道这篇文章已经过时了，但是如果有人还在那里，我很想获得有关 Dick 提供的公式的派生类的帮助，例如，我想对多行进行计数[在这种情况下为 2] ：
=IF(COUNT(A1:G2)<3,"NA",SUM(G2:INDEX(A1:G2,LARGE(COLUMN(A1:G2)*(A1:G2<>""),3))))
=IF(COUNT(A1:G2)<3,"NA",SUM(G2:INDEX(A1:G2,LARGE(COLUMN(A1:G2)*(A1:G2<>""),3))))
Ultimately I'm trying to pull a sum of the last three cells with figures in running 12 accross
最终，我试图将最后三个单元格的总和与运行 12 个的数字相加
[A:L=months of year] by several rows down [1:5=consecutive years].
If anyone can shed light I'd be much obliged.
如果有人能阐明，我将不胜感激。
Many thanks: Ryan
非常感谢：瑞安
