我可以在两列上使用COUNTIF来比较值吗？

I have two columns in Excel, like this:

我在Excel中有两列,如下所示:

 A   B
0.5  0.4
0.6  0.59
0.1  0.2
0.3  0.29

I want to count how many of the values in B are less than their corresponding values in A. In this case, the answer is 3.

我想计算B中有多少值小于它们在A中的对应值。在这种情况下,答案是3。

I can do this by adding an extra column, B-A and then doing COUNTIF(RANGE, "<0"), but I am wondering if there's a way to do it without adding an extra column.

我可以通过添加一个额外的列,B-A然后执行COUNTIF(RANGE,“<0”)来做到这一点,但我想知道是否有办法在不添加额外列的情况下执行此操作。

I realize this is on the fringes of what one might consider programming, but hopefully it's just on the right side of the line, rather than the wrong side.

我意识到这是在人们可能会考虑编程的边缘,但希望它只是在线的右侧,而不是错误的一面。

4 个解决方案

#1

This can be done using Excel array formulas. Try doing something like this:

这可以使用Excel数组公式完成。尝试做这样的事情:

=SUM(IF(A1:A5 > B1:B5, 1, 0))

The very very important part, is to press CTRL-SHIFT-ENTER instead of just ENTER when you finished inputting the formula. Otherwise it won't understand you want to treat the data as an array.

非常重要的部分是在输入公式后按CTRL-SHIFT-ENTER而不是按ENTER。否则它将无法理解您希望将数据视为数组。

#2

There is a solution, but it still involves 2 extra cells: DCOUNT.

有一个解决方案,但它仍然涉及2个额外的单元格:DCOUNT。

The following is an example(insert into the specified cells the exact text after the colons):

以下是一个示例(在冒号后插入指定单元格中的确切文本):

A1:Condition

A2:=B4>A4

A3:A

B3:B

A4:700

B4:5000

A5:700

B5:600

A6:7000

B6:6000

A7:700

B7:701

Cell with count formula:=DCOUNT(A3:B7,"B",A1:A2)

计数公式的单元格:= DCOUNT(A3:B7,“B”,A1:A2)

#3

Actually this is something I would do with a program.

实际上这是我用程序做的事情。

Create a macro to:

创建一个宏来:

insert column C.

插入列C.

set range("cN").value to "=bN-aN" for all N where range("aN").value <> "".

将范围(“cN”)。值设置为“= bN-aN”表示所有N的范围(“aN”)。值<>“”。

do your countif calculation and shove it into a cell (not column C).

做你的countif计算并把它推进一个单元格(不是C列)。

delete column C.

删除列C.

There may be an easier non-programming way but I don't know it (and then your question would be closed anyhow).

可能有一种更简单的非编程方式但我不知道(然后你的问题无论如何都会被关闭)。

#4

scraimer solution is ok. But for a fun,

刮刀解决方案还可以。但为了好玩,

You can also write a macro like the following and assign

您也可以编写如下的宏并进行分配

=myOwnFunction(A1:A5,B1:B5)

this approach can be extended for any other logical function such as A * B + C < C + D * E etc....

这种方法可以扩展到任何其他逻辑功能,如A * B + C

Function myOwnFunction(R1 As Range, S1 As Range)

   Dim J As Integer

   Dim Size As Integer

   Dim myCount As Integer


   Size = R1.Cells.Count

   myCount = 0

       For J = 1 To Size

           If (R1.Cells(J) > S1.Cells(J)) Then

            myCount = myCount + 1

           End If

       Next J


    myOwnFunction = myCount

End Function

#1