当一系列数字变为负数时，Excel公式确定单元格ID [英] Excel formula to determine cell ID when a series of numbers turns negative

问题描述

样本数据

     A            B
1  Date        Amount
2  Apr 1        $6,000
3  May 1        $4,250
4  June 1       $2,750
5  July 1       $1,000
6  Aug 1       -$0.075   <- This Cell/Row
7  Sept 1     -$0.2500

在一列数字（实际100-200行）中，当值变为负值时，例如如果这些我们是欠贷款的金额，那么贷款将被偿还。请注意，数字之间的真正差异基于利息，税收，一次性付款等而波动。因此，我不能仅计算（总/付款）=月数。

In a column of numbers (in reality 100-200 rows), when the value changes to negative, e.g. if these we're amounts owed on a loan, when the loan would be paid off by. Note the real difference between the numbers fluctuates based on interest, taxes, one-off payments etc. So I can't just count (total / payment) = number of months.

有没有办法使用Excel的公式来确定这个？这可能是需要VBA（这是很好的）的情况，但如果我可以避免它，我想。

Is there a way to use Excel's formulas to determine this? This may be a case of requiring VBA (which is fine) but if I can avoid it, I'd like to.

推荐答案

match函数返回范围索引

The match function returns a range index

=MATCH(matchValeu, range, matchType: 0=exact, 1=greater than, -1=less than


=MATCH(0, B2:B7, -1)

匹配范围B2中的小于0的第一个单元格：B7。从样本数据中，这将返回5

Match the first cell that is less than 0 in range B2:B7. From your sample data this would return 5

使用偏移函数根据索引值

Use the Offset function to return a particular cell based on the index value

这篇关于当一系列数字变为负数时，Excel公式确定单元格ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！