查找表范围中的第一场比赛 [英] Find first match in table range

问题描述

  = SUMIF（MyTable [Date] ，=& $ D5，MyTable [帐户余额]）
  

偶尔， D5 不会在 MyTable [Date] 范围内匹配。我想要这样做=& $ D5 + 1 然后+1 ...等等，直到该范围的第一个匹配位置。

解决方案

下一个日期可以使用

如果[帐户余额]列中有空格需要丢弃，则 COUNTIF函数将必须成为 COUNTIFS函数和 LARGE功能将不得不成为AGGREGATE¹功能。在上面的G5中，

  = SUMIFS（MyTable [account balance]，MyTable [date]，AGGREGATE（14，6，MyTable [date] /（MyTable [account balance]<>），COUNTIFS（MyTable [date]，> =& F5，MyTable [account balance]，<" b $ b  

---

¹ _{AGGREGATE功能与Excel 2010一起引入。它是}

I have this working formula that I'd like to expand for more flexibility:

=SUMIF(MyTable[Date],"="&$D5,MyTable[Account Balance])

Occasionally, D5 will not have a match in MyTable[Date] range. I'd like it to do "="&$D5+1 and then +1... and so on until first match in the range is located.

解决方案

The next date can be easily located on a sorted or unsorted range of dates using a COUNTIF function to rank and passing that result into a LARGE function as the k parameter.

In the following, I've used SUMIFS function syntax to provide more universality than SUMIF function (e.g. the parameters are necessarily reversed).

The standard formula in G5 is,

=SUMIFS(MyTable[account balance], MyTable[date], LARGE(MyTable[date], COUNTIF(MyTable[date], ">="&F5)))

Fill down as necessary.

    

If you have blanks in the [Account Balance] column that need to be discarded, the COUNTIF function will have to become a COUNTIFS function and the LARGE function will have to become an AGGREGATE¹ function. In G5 above as,

=SUMIFS(MyTable[account balance], MyTable[date], AGGREGATE(14, 6, MyTable[date]/(MyTable[account balance]<>""), COUNTIFS(MyTable[date], ">="&F5, MyTable[account balance], "<>")))

---

¹ _{The AGGREGATE function was introduced with Excel 2010. It is not available in earlier versions.}

这篇关于查找表范围中的第一场比赛的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！