vba 当一系列数字变为负数时，用于确定单元格 ID 的 Excel 公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/650306/
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
Excel formula to determine cell ID when a series of numbers turns negative
提问by scunliffe
Sample data
样本数据
A B
1 Date Amount
2 Apr 1 ,000
3 May 1 ,250
4 June 1 ,750
5 July 1 ,000
6 Aug 1 -=MATCH(matchValue, range, matchType: 0=exact, 1=greater than, -1=less than
=MATCH(0, B2:B7, -1)
.075 <- This Cell/Row
7 Sept 1 -=MATCH(lookup value, lookup range, lookup type)
=MATCH(0,B1:B7,-1)
.2500
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.
在一列数字（实际上是 100-200 行）中，当值变为负数时，例如，如果这些是贷款的欠款，那么贷款何时还清。请注意数字之间的实际差异会根据利息、税收、一次性付款等而波动。所以我不能只计算（总计/付款）= 月数。
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.
有没有办法使用 Excel 的公式来确定这一点？这可能是需要 VBA 的情况（这很好）但如果我可以避免它，我想。
回答by TFD
The match function returns a range index
match 函数返回一个范围索引
=MATCH(0,B1:B7,-1)+1
Match the first cell that is less than 0 in range B2:B7. From your sample data this would return 5
匹配范围 B2:B7 中小于 0 的第一个单元格。从您的示例数据中，这将返回 5
Use the Offset function to return a particular cell based on the index value
使用 Offset 函数根据索引值返回特定单元格
回答by Robert Mearns
Use the MATCHformula to determine the row number.
使用MATCH公式确定行号。
=ADDRESS(Row number, Column number)
=ADDRESS(MATCH(0,B1:B7,-1)+1,2)
You will need to use match type of -1, as your data is in descending order. This setting will return the smallest value that is greater than or equal to the lookup value of 0.
您将需要使用匹配类型 -1，因为您的数据是按降序排列的。此设置将返回大于或等于查找值 0 的最小值。
Based on your data, this would return the row number 5. You are expecting to see the row number 6, so the formula needs to be extended as follows.
根据您的数据，这将返回行号5。您希望看到行号6，因此需要按如下方式扩展公式。
=OFFSET(A1,MATCH(0,B1:B7,-1),0)
=OFFSET(A1,MATCH(0,B1:B7,-1),1)
To determine the cell ID you would need to wrap this formula into an ADDRESSformula.
要确定单元格 ID，您需要将此公式包装到ADDRESS公式中。
=IF(YOUR_CELL_ACTUAL_FORMULA < 0 , 0, YOUR_CELL_ACTUAL_FORMULA)
This would return the value $B$6
这将返回值$B$6
It would probably be more useful to return the related date or value. This can be done with an OFFSETformula.
返回相关日期或值可能更有用。这可以通过OFFSET公式来完成。
=MATCH(0, YOUR_AMOUNT_RANGE, -1)
The first formula would return the date in A6, Aug 1
第一个公式将返回 A6 中的日期，8 月 1 日
The second formula would return the value in B6, -$0.075
第二个公式将返回 B6 中的值-$0.075
回答by MarmouCorp
I'm not sure want you want to do.
我不确定你想要做什么。
If you want to avoid having negative number you can do :
如果你想避免负数，你可以这样做：
##代码##If you want to know when the number turn to be negative you can do :
如果您想知道数字何时变为负数，您可以执行以下操作：
##代码##This will give you the first line number when the amount is negative.
当金额为负数时，这将为您提供第一行编号。
