vba 如何在几行上重复功能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2559631/
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
How do I repeat function over several row
提问by ChrisBD
I'll admit that I'm not an Excel guru so maybe someone here can help me.
我承认我不是 Excel 大师，所以也许这里有人可以帮助我。
On my worksheet I have several blocks of data.
在我的工作表上，我有几个数据块。
I calculate the sum of all items within column D of that block. Within each block I am checking the value of the cell in column C and if it contains the letter "y" and the value in column D of that row is equal to zero I must exclude the total sum of column D.
我计算该块 D 列中所有项目的总和。在每个块中，我正在检查 C 列中单元格的值，如果它包含字母“y”并且该行 D 列中的值等于零，我必须排除 D 列的总和。
Currently I am doing this by multiplying the sum value by either 1 or 0 which is produced by running a test over the cell contents.
目前，我通过将总和值乘以 1 或 0 来执行此操作，这是通过对单元格内容运行测试而产生的。
Below is an example of what I am using to test rows 23 to row 25 inclusively for data in Column D. I am also performing the same on Column E and G, but the "y" character is always in column C, hence the absolut column reference.
下面是我用来测试 D 列数据的第 23 行到第 25 行的示例。我也在 E 列和 G 列上执行相同的操作，但“y”字符始终在 C 列中，因此绝对值列参考。
=IF(AND($C23="y",D23=0),0,1)*IF(AND($C24="y",D24=0),0,1)*IF(AND($C25="y",D25=0),0,1)
There must be a more efficient way to do this.
必须有一种更有效的方法来做到这一点。
Ideally I would like to write a function that I can paste into a cell and then select the rows or cells over which I run the test.
理想情况下，我想编写一个可以粘贴到单元格中的函数，然后选择运行测试的行或单元格。
Can anyone point me in the right direction?
任何人都可以指出我正确的方向吗？
回答by Dick Kusleika
I'm not sure if I understand the question 100%, but here's an answer anyway:
我不确定我是否 100% 理解这个问题，但无论如何这里有一个答案：
{=NOT(SUM((C23:C25="y")*(D23:D25=0)))*SUM(D23:D25)}
Don't enter the curly braces, rather enter the formula using Control+Shift+Enter to make it an array formula. Here's what it does:
不要输入花括号，而是使用 Control+Shift+Enter 输入公式，使其成为数组公式。这是它的作用：
First, it counts all the rows where C is 'y' and D is zero. That will return (in this example) 0, 1, 2, or 3. The NOT will change the zero to a 1 and change anything else to a zero. Then that one or zero will be multiplied by the sum of column D.
首先，它计算 C 为 'y' 且 D 为零的所有行。这将返回（在本例中）0、1、2 或 3。NOT 会将零更改为 1，并将其他任何内容更改为零。然后那个一或零将乘以 D 列的总和。
So if zero rows have both 'y' and 0, multiply the SUM by 1. If more than zero rows have both 'y' and 0, multiply the SUM by 0.
因此，如果零行同时具有 'y' 和 0，则将 SUM 乘以 1。如果多于零行同时具有 'y' 和 0，则将 SUM 乘以 0。
回答by marg
This Function should work:
此功能应该工作：
Option Explicit
Public Function getMySum(src As Range, sumColumn As Integer)
Dim iRow As Range
Dim yColumn As Integer
yColumn = 1
getMySum = 0
For Each iRow In src.Rows
If (Strings.StrConv(iRow.Cells(1, yColumn), vbLowerCase) <> "y") Or (iRow.Cells(1, sumColumn) <> 0) Then
getMySum = getMySum + iRow.Cells(1, sumColumn)
Else
getMySum = 0
Exit For
End If
Next iRow
End Function
You need to add this Code to a VBA Module
您需要将此代码添加到 VBA模块
The function call for your Example would be: =getMySum("C:23:D25", 2)
您的示例的函数调用将是： =getMySum("C:23:D25", 2)
The only other option I see would be to combine the value in c and d like =C23&";"&D23and sumif VLOOKUP that searches for "y;0" returns an error.
我看到的唯一其他选择是将 c 和 d 中的值组合起来，=C23&";"&D23并且 sumif VLOOKUP 搜索“y;0”返回错误。
