如何根据条件在Excel-2007中获取没有数组公式的最后记录位置? [英] How to get last record position without array formula in Excel-2007 based on condition?

问题描述

我试图在不使用数组方法的情况下查找特定项目在列中的最后记录位置.

I am trying to find last record position of particular item in a column without array approach.

Org
---
Exim
Exim
Agrani
BCBL
Rupali
DSE
NCC
NRBCB
UTTARA
Exim
Rupali
BDBL
BCBL

使用以下数组公式，我可以得到它.是否有任何非数组公式能达到相同的效果?我知道excel最新版本上有几个选项可用，但我的限制仅在Excel-2007上.

With the following array formula I can get it. Is there any non array formula got achieve same? I know several option is available on excel latest versions but my limitation is only on Excel-2007.

=LARGE(IF(B3:B15=D5,ROW(B3:B15)-ROW(B2),""),1)

推荐答案

尝试

'for xl2007 as standard formula
=MAX(INDEX(ROW(1:13)-(B3:B15<>D5)*1E+99, , ))

'for xl2007 as array formula with CSE
=large(if(b3:b15=d5, row(1:13)), 1)

'for xl2010 and later as standard formula
=aggregate(14, 6, row(1:13)/(b3:b15=d5), 1)

这篇关于如何根据条件在Excel-2007中获取没有数组公式的最后记录位置?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！