Excel:计算非空单元格对数

huangapple go评论64阅读模式
英文:

Excel: count non-blank cell pairs

问题

我有两个相等长度的单元格范围(这些单元格不相邻)。
一些数值缺失/为空。

我需要计算有数值的单元格对的数量。
例如,如果这些对是:
1,2
2,2
3,0
2,-
-,3
-,-
计数应该是5(对1-3都有值,对4-5有一个值,对6有两个空白不计算在内)。

有没有一种方法可以用一个公式来实现,不使用任何辅助单元格
另一个限制是它应该在Excel 2019或更早版本上运行...

我的第一个想法是使用类似这样的东西:
=COUNTIFS(ISNUMBER(A1:F1),TRUE,ISNUMBER(A3:F3),TRUE)
然而,这会引发错误,甚至不能作为一个有效的公式被接受...

英文:

I have two ranges of cells of equal length (the calls are non-adjacent).
Some values are missing/blank.

I need two count how many of the cell pairs have a numerical value in them.
for example, if the pairs are:
1, 2
2, 2
3, 0
2, -
-, 3
-,-
the count should be 5 (pairs 1-3 have both values, pairs 4-5 have one value, pair 6 has two blanks and is not counted).

Is there a way do this in ONE formula, without any helper cells ?
Another constraint is that it should run on Excel 2019 or earlier...

My first thought was to use something like:

=COUNTIFS(ISNUMBER(A1:F1),TRUE,ISNUMBER(A3:F3),TRUE)

However, this raises an error, and is not even accepted as a valid formula...

答案1

得分: 2

=SUM(IF((ISNUMBER(A1:A8)+ISNUMBER(B1:B8))>0,1,0))

结果:

Excel:计算非空单元格对数

请注意,如果日期以"dd-mmm"格式表示,而您没有将其视为数字,则可能会产生误报。

英文:
=SUM(IF((ISNUMBER(A1:A8)+ISNUMBER(B1:B8))>0,1,0))

Result:

Excel:计算非空单元格对数

Keep in mind that date formatted for example as "dd-mmm" would give false positive if it is not considered as number in your case.

答案2

得分: 0

=INT(COUNT(ValuesRange)/2)+1

英文:

How about?

=INT(COUNT(ValuesRange)/2)+1

huangapple
  • 本文由 发表于 2023年5月22日 16:00:41
  • 转载请务必保留本文链接:https://go.coder-hub.com/76304108.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定