vba 如何计算 Excel 工作表中包含数据的行数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6867070/
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 can I count the rows with data in an Excel sheet?
提问by David
I am trying to count the number of rows in a spreadsheet which contain at least one non-blank value over a few columns: i.e.
我正在尝试计算电子表格中的行数，这些行数在几列中至少包含一个非空值：即
row 1 has a text value in column A
row 2 has a text value in column B
row 3 has a text value in column C
row 4 has no values in A, B or C
The formula would equate to 3, because rows 1, 2, & 3 have a text value in at least one column. Similarly if row 1 had a text value in each column (A, B, & C) this would be counted as 1.
该公式等于 3，因为第 1、2 和 3 行在至少一列中具有文本值。类似地，如果第 1 行在每列（A、B 和 C）中都有一个文本值，这将被计为 1。
回答by JMax
With formulas, what you can do is:
使用公式，您可以做的是：
- in a new column (say col D - cell
D2), add=COUNTA(A2:C2) - drag this formula till the end of your data (say cell
D4in our example) - add a last formula to sum it up (e.g in cell
D5):=SUM(D2:D4)
- 在新列中（比如 col D - cell
D2），添加=COUNTA(A2:C2) - 将此公式拖到数据末尾（
D4例如我们示例中的单元格） - 添加最后一个公式来总结它（例如在单元格中
D5）：=SUM(D2:D4)
回答by Jason McKindly
If you want a simple one liner that will do it all for you (assuming by no value you mean a blank cell):
如果你想要一个简单的一个衬里来为你做这一切（假设没有价值，你的意思是一个空白单元格）：
=(ROWS(A:A) + ROWS(B:B) + ROWS(C:C)) - COUNTIF(A:C, "")
If by no value you mean the cell contains a 0
如果没有值，则表示该单元格包含 0
=(ROWS(A:A) + ROWS(B:B) + ROWS(C:C)) - COUNTIF(A:C, 0)
The formula works by first summing up all the rows that are in columns A, B, and C (if you need to count more rows, just increase the columns in the range. E.g. ROWS(A:A) + ROWS(B:B) + ROWS(C:C) + ROWS(D:D) + ... + ROWS(Z:Z)).
该公式首先将 A、B 和 C 列中的所有行相加（如果您需要计算更多行，只需增加范围内的列。例如ROWS(A:A) + ROWS(B:B) + ROWS(C:C) + ROWS(D:D) + ... + ROWS(Z:Z)）。
Then the formula counts the number of values in the same range that are blank (or 0 in the second example).
然后，该公式计算同一范围内为空白（或第二个示例中的 0）的值的数量。
Last, the formula subtracts the total number of cells with no value from the total number of rows. This leaves you with the number of cells in each row that contain a value
最后，该公式从总行数中减去没有值的单元格总数。这为您留下每行中包含值的单元格数
回答by aevanko
If you don't mind VBA, here is a function that will do it for you. Your call would be something like:
如果您不介意 VBA，这里有一个可以为您完成的功能。你的电话是这样的：
=CountRows(1:10)
Function CountRows(ByVal range As range) As Long
Application.ScreenUpdating = False
Dim row As range
Dim count As Long
For Each row In range.Rows
If (Application.WorksheetFunction.CountBlank(row)) - 256 <> 0 Then
count = count + 1
End If
Next
CountRows = count
Application.ScreenUpdating = True
End Function
How it works: I am exploiting the fact that there is a 256 row limit. The worksheet formula CountBlank will tell you how many cells in a row are blank. If the row has no cells with values, then it will be 256. So I just minus 256 and if it's not 0 then I know there is a cell somewhere that has some value.
它是如何工作的：我正在利用有 256 行限制的事实。工作表公式 CountBlank 将告诉您一行中有多少个单元格是空白的。如果该行没有带有值的单元格，那么它将是 256。所以我只是减去 256，如果它不是 0，那么我知道某处有一个单元格具有某些值。
回答by Mike Feeney
Try this scenario:
试试这个场景：
Array = A1:C7. A1-A3have values, B2-B6have value and C1, C3and C6have values.
数组 = A1:C7。A1-A3有价值观，B2-B6有值和C1，C3和C6具有价值。
To get a count of the number of rows add a column D(you can hide it after formulas are set up) and in D1put formula =If(Sum(A1:C1)>0,1,0). Copy the formula from D1through D7(for others searching who are not excel literate, the numbers in the sum formula will change to the row you are on and this is fine).
要计算行数，请添加一列D（您可以在设置公式后隐藏它）并输入D1公式=If(Sum(A1:C1)>0,1,0)。从复制公式D1至D7（替他人寻找谁不擅长识字的求和公式中的数字会变成你在该行，这是罚款）。
Now in C8make a sum formula that adds up the Dcolumn and the answer should be 6. For visually pleasing purposes hide column D.
现在C8制作一个求和公式，将D列相加，答案应该是6. 为了视觉上令人愉悦的目的，隐藏列D。
回答by Rasmus ?stergaard Kj?r Voss
You should use the sumif function in Excel:
您应该在 Excel 中使用 sumif 函数：
=SUMIF(A5:C10;"Text_to_find";C5:C10)
=SUMIF(A5:C10;"Text_to_find";C5:C10)
This function takes a range like this square A5:C10 then you have some text to find this text can be in A or B then it will add the number from the C-row.
这个函数需要一个像这样的正方形 A5:C10 的范围然后你有一些文本来找到这个文本可以在 A 或 B 中，然后它会添加 C 行中的数字。
回答by Tanima
This is what I finally came up with, which works great!
这是我最终想出的，效果很好！
{=SUM(IF((ISTEXT('Worksheet Name!A:A))+(ISTEXT('CCSA Associates'!E:E)),1,0))-1}
{=SUM(IF((ISTEXT('Worksheet Name!A:A))+(ISTEXT('CCSA Associates'!E:E)),1,0))-1}
Don't forget since it is an array to type the formula above without the "{}", and to CTRL + SHIFT + ENTER instead of just ENTER for the "{}" to appear and for it to be entered properly.
不要忘记，因为它是一个数组，可以在没有“{}”的情况下键入上面的公式，并使用 CTRL + SHIFT + ENTER 而不是 ENTER 来显示“{}”并正确输入它。
