如何计算 Excel 工作表中数据的行数？

发布于 2024-11-26 21:47:42 字数 284 浏览 0 评论 0原文

我正在尝试计算电子表格中几列中至少包含一个非空白值的行数：即

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

公式将等于 3，因为行 1、2、& 3 至少一列中有文本值。同样，如果第 1 行的每列（A、B 和 C）中都有一个文本值，则这将被计为 1。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

挽你眉间 2024-12-03 21:47:42

使用公式，您可以做的是：

在新列（例如列 D - 单元格 D2）中，添加 =COUNTA(A2:C2)
将此公式拖到末尾您的数据（在我们的示例中为单元格 D4）
添加最后一个公式来求和（例如在单元格 D5 中）：=SUM(D2:D4)

回复收藏 0 原文

穿越时光隧道 2024-12-03 21:47:42

如果您想要一个简单的衬线来为您完成所有操作（假设没有值，您意味着一个空白单元格）：

=(ROWS(A:A) + ROWS(B:B) + ROWS(C:C)) - COUNTIF(A:C, "")

如果没有值，您意味着该单元格包含 0

=(ROWS(A:A) + ROWS(B:B) + ROWS(C:C)) - COUNTIF(A:C, 0)

该公式的工作原理是首先将所有行相加A、B 和 C 列（如果需要计算更多行，只需增加范围内的列即可。例如 ROWS(A:A) + ROWS(B:B) + ROWS(C:C) + ROWS (D:D) + ... +行(Z:Z))。

然后，该公式计算同一范围内空白值（或第二个示例中的 0）的数量。

最后，该公式从总行数中减去没有值的单元格总数。这将为您留下每行中包含值的单元格数量

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

=(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)).

Then the formula counts the number of values in the same range that are blank (or 0 in the second example).

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

回复收藏 0 原文

走过海棠暮 2024-12-03 21:47:42

如果您不介意 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

它是如何工作的：我正在利用 256 行限制这一事实。工作表公式 CountBlank 将告诉您一行中有多少个单元格为空白。如果该行没有包含值的单元格，那么它将是 256。所以我只需负 256，如果它不是 0，那么我知道某处有一个具有某些值的单元格。

If you don't mind VBA, here is a function that will do it for you. Your call would be something like:

=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.

回复收藏 0 原文