vba 优秀。使用 If Then 来增加另一个单元格的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26769513/
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. Use If Then to increase the value of another cell
提问by a13hockey
I apologize if something like this has been asked, but I've been through many excel questions and can't find something to cover this.
如果有人问过这样的问题，我深表歉意，但我遇到了很多 excel 问题，但找不到可以涵盖的内容。
I have 2 columns. I want to see if one column is less than the other. If it is, in a third column I want a single cell to gain + 1. So if twice column A's cells were less than Column B, then column C would show a value of 2, and on.
我有 2 列。我想看看一列是否小于另一列。如果是，在第三列中，我希望单个单元格获得 + 1。因此，如果 A 列的单元格小于 B 列的两倍，则 C 列将显示值 2，以此类推。
As a bonus I would like the third column to be on a separate sheet, which I can figure out myself, but I'm sure someone can do it better.
作为奖励，我希望将第三列放在单独的工作表上，我可以自己弄清楚，但我相信有人可以做得更好。
To clarify: If A1 < B1 then C1=C1+1 and If A2 < B2 then C1=C1+1
澄清：如果 A1 < B1 那么 C1=C1+1 并且如果 A2 < B2 那么 C1=C1+1
If they are not less than, nothing should happen.
如果它们不小于，则不应发生任何事情。
回答by hfontanez
To reference cells in different sheets: sheet name followed by '!' followed by cell. For example" =Sheet1!A1
要引用不同工作表中的单元格：工作表名称后跟“！” 其次是细胞。例如"=Sheet1!A1
Circular references are not allowed in Excel formulas. However, you can use a macro to do so. The following macro adds one to cell C1 if the values of A1 and B1 are not equal
Excel 公式中不允许使用循环引用。但是，您可以使用宏来执行此操作。如果 A1 和 B1 的值不相等，则以下宏向单元格 C1 加一
Sub addOneToCell()
val1 = Range("A1").Value
val2 = Range("B1").Value
val3 = Range("C1").Value
If val1 <> val2 Then Range("C1").Value = val3 + 1
End Sub
UPDATE:
更新：
This is the same as above, but iterating through cells. WARNINGas I expected, it is VERY slow:
这与上面相同，但遍历单元格。 警告正如我所料，它非常慢：
Sub addOneToCell()
For i = 1 To Rows.Count
If Not IsEmpty(Cells(i, 1).Value) And Not IsEmpty(Cells(i, 2).Value) And Cells(i, 1).Value <> Cells(i, 2) Then Range("C1").Value = Range("C1").Value + 1
Next i
End Sub
回答by Adam
I am reading this question as "How do I count the total number of values in column A which are less than their corresponding values in column B?"
我将这个问题读为“如何计算 A 列中小于 B 列中相应值的值的总数？”
If that is correct, then you want an array formula in cell C1:
如果这是正确的，那么您需要单元格 C1 中的数组公式：
=sum(if(A1:A10<B1:b10,1,0))
When you type this in, enter with control-shift-enter all at once. That should make it look like this:
当您输入此内容时，请使用 control-shift-enter 一次全部输入。这应该使它看起来像这样：
={sum(if(A1:A10<B1:b10,1,0))}
and should show the total count in cell c1.
并应在单元格 c1 中显示总计数。
回答by Jason W
There may be better ways, but I would start like this.
可能有更好的方法，但我会这样开始。
- Give formula for first row of
=if(a1<b1,1,0) - Give formula for second row of
=c1+if(a2<b2,1,0) - Fill second row down through your data set
- 给出第一行的公式
=if(a1<b1,1,0) - 给出第二行的公式
=c1+if(a2<b2,1,0) - 向下填充数据集的第二行
