☰

在 Excel 中，仅当每行的数字总和 > 0 时，我如何计算数组中的行数？

tag-icon • tag-icon microsoft-excel worksheet-function
在 Excel 中，仅当每行的数字总和 > 0 时，我如何计算数组中的行数？

我在 Excel 中有一个 150 列 x 360 行的数组，其中包含随机数（例如 A2 到 ET361）。

如何计算每一列（即从单元格 B1 到 ET1）之前列中有多少行大于零？

标准：

B1 需要计算大于 0 的单元格数量（A2 到 A361）。C1
需要计算每行总和大于 0 的行数量（A2:B2、A3:B3、...、到 A361:B361）
。D1 需要计算每行总和大于 0 的行数量（A2:C3、...、到 A361:C361）。

我尝试使用 COUNTIF 公式，但它只返回单元格数，而不是行数。
我想我需要嵌套的 ROWS() 和 IF() 公式？我也不想创建另一个 150 x 360 矩阵来解决这个问题，因为我想节省 Excel 文件中的空间。

我也不想使用宏和 VBA，因为它们使我的电子表格变得复杂。

我给整个等式增加了一个复杂性，因此小计函数不起作用。

我需要计算矩阵中每个单元格上方的行数，其中每行的列数总和大于零。Barry 的解决方案在这种情况下不起作用（我已经测试过了），因为“Subtotal”公式不适用于具有“subtotal”公式的单元格。

我们还有其他选择吗？

答案1

虽然我还没能想出一个公式解决方案（也许其他人会！），但我确实想出了一个比另一个 150 x 360 矩阵占用更少电子表格空间的方案。

基本思想是计算每一行中某一列数据的累计总数，然后在数据表（“假设分析”）中使用该总数来生成所有列的计数。

起点是单个数据列中行的计算列。

如下面的截图所示，我设置了一个包含 10 列数据的工作表。

辅助栏

在数据的右侧，我设置了辅助列 L。

单元格 L1 包含COUNTIF该列中总和大于零的行。

对于行总和，我使用函数返回的范围的总和，而不是简单地对每行的列进行求和（同样，仅针对 A 列）。OFFSET此函数的形式为

OFFSET(reference cell, number of rows to offset, number of columns to offset, 
       height of range to return, width of range to return)

单元格 L3 包含第一个表达式SUM(OFFSET(...))。它计算单元格 A2 下方 0 ​​行、右侧 0 列的区域的行总和，高度为 1 行，宽度等于单元格 L2 中的值。在本例中，L2 的值为 1。

此公式向下复制到 360 行，每次计算 1 行高的范围的总和，其宽度由单元格 L2 中的值决定。

例如，如果将 L2 中的值更改为 2，则列中的公式将计算 360 行中每行 A 列和 B 列的值的逐行总和。单元格 L1 将显示范围 A2:B361 中总和大于 0 的行数。

计算单个数据列的行数

数据表

Excel 的数据表功能可以快速确定改变一个（或两个）输入值对计算的影响。它通过功能区上选项卡部分What-If Analysis中的按钮进行设置。Data ToolsData

附图显示了数据表设置。

数据表将在 R1:S10 范围内创建。表格顶部的单元格 S1 是结果单元格，输入将根据结果单元格而变化。在本例中，结果单元格包含公式，它只是对辅助列 L 顶部公式=L1的引用。COUNTIF

我在单元格 R2:R10 中预先输入了“假设”值。显示的值 - 1、2、...、9 - 表示 OFFSET 将返回的范围的宽度。而“列输入单元格”是单元格L1，该单元格确定辅助列中求和的行的宽度。

简而言之，我们输入宽度 1-9（相当于列“A”、“A:B”、“A:C”等），数据表会计算每列跨度总和大于 0 的行数。

数据表设置

最后一张图是最终结果。数据表计算了输入数据每列的行数，即（前几列的）行总和大于 0 的计数。这些计数在数据表的单元格 S2:S10 中返回。我使用函数将计数转移到原始数据的第一行TRANSPOSE。

最终结果

包含所有计算的示例工作表可供使用这里。

答案2

如果我理解正确的话，你希望第一行显示每一列的单个细胞并且其之前的所有列的值都大于 0。对吗？

如果是这样，使用CountIf和使用$符号锁定引用就非常简单了。

在单元格 B1 中输入=CountIf($A2:A361,">0")。单击并向右拖动。符号$锁定A，以便它始终计算 A 列和当前列之间的所有内容。拖动时，公式将如下所示：

  • C1=Countif($A2:B361,">0")
  • D1：=Countif($A2:C361,">0")
  • E1：=Countif($A2:D361,">0")
  • ETC...

CountIf可以对整个范围进行计数，您不必每次只选择一个单元格或公式。因此，通过这种方式，您可以轻松地对当前列左侧的每个单元格进行计数。

答案3

OFFSET函数允许您分离出范围内的各个行...然后您可以用对每一行求和SUBTOTAL并计算行数> 0 SUMPRODUCT，因此在B1中复制的这个公式应该可以在没有辅助单元格的情况下完成工作

=SUMPRODUCT((SUBTOTAL(9,OFFSET($A2:A2,ROW(A2:A361)-ROW(A2),0))>0)+0)

使用与所述类似的技术这里[这里没有过滤，但仍然需要使用 SUBTOTAL 对 OFFSET 生成的每个范围求和]

这将给你与 chuff 的解决方案相同的结果

相关内容