如何计算值小于excel中另一个单元格的范围内的单元格？

My Table looks like below

我的表如下所示

if "team1" value is less than "general" value in that month, then it has blue color, if "team2" value is less than "general" value in that month, then it has pink color,

如果“team1”值小于那个月的“一般”值,那么它有蓝色,如果“team2”值小于那个月的“一般”值,那么它有粉红色,

Now I want to count how many blue colored and how many pink colored cells on each row in a year (cells AK3 and AL3)

现在我想计算一年中每行上有多少蓝色和多少个粉红色的细胞(细胞AK3和AL3)

What is the most appropriate formula for that?

最合适的配方是什么?

3 个解决方案

#1

EDIT: merged two answers here:

编辑:在这里合并两个答案:

This formula will do what you are looking for, assuming you move everything one column to the right (adding an empty column in column A):

假设您将所有内容向右移动一列(在A列中添加空列),此公式将执行您要查找的内容:

=SUM(IF(C2:AK2="Team1";IF(C3:AK3 < B3:AJ3;1;0)))

What this does is that it first looks if you have Team1 in the column. It then proceeds to check if the data below is less than the one preceding that. It is important you have the last if as A and the others as B because otherwise it will summarise the wrong data. (For team2 you will have to change the last B3:AJ3 to A3:AI3)

这样做的第一步看起来你是否在列中有Team1。然后继续检查下面的数据是否小于之前的数据。重要的是你将最后的if作为A而其他的作为B,因为否则它会汇总错误的数据。 (对于team2,您必须将最后一个B3:AJ3更改为A3:AI3)

Also, use shift+enter when you enter this to make sure it becomes an array formula.

此外,输入此选项时使用shift + enter以确保它成为数组公式。

---

I would highly recommend you switch your columns and rows to a more standardised form first in order to have an easier workflow with your data. I mean something like this

我强烈建议您先将列和行切换为更标准化的表单,以便更轻松地处理数据。我的意思是这样的

I would then recommend you check out the answer for a similar question here. To summarise, you gather the data in one column and then use a

我建议你在这里查看类似问题的答案。总而言之,您将数据收集在一列中,然后使用

=SUM(IF(B:B < A:A;1;0)) 

assuming you have team1 in column B and general in column A.

假设您在B列中有team1,在A列中有一般。

#2

You can use XL4 macros (Excel formula) to count up cells with different backcolor or even font colour in excel :) See this LINK. For Font color the type_num is 24. And for backcolor we will use 63

您可以使用XL4宏(Excel公式)来计算excel中具有不同背景颜色甚至字体颜色的单元格:)请参阅此链接。对于Font颜色,type_num为24.而对于backcolor,我们将使用63

1. Open the Name Manager

打开名称管理器

2. Give a name. Say BackColor

给个名字。说BackColor

3. Type this formula in Refers To =GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),-1,0)) and click OK

在引用= GET.CELL(63,OFFSET(INDIRECT(“RC”,FALSE), - 1,0)中键入此公式,然后单击“确定”。

The explanation of =GET.CELL() is mentioned in the above link.

上面的链接中提到了= GET.CELL()的解释。

Now let's say your workbook looks like this

现在让我们说你的工作簿看起来像这样

Next put this formula in row 2.

接下来将此公式放在第2行。

=backcolor

Next put =COUNTIF(A2:J2,8) and =COUNTIF(A2:J2,7) in cell C5 and C6 respectively and you will get the total count of colors.

然后分别在单元格C5和C6中输入= COUNTIF(A2:J2,8)和= COUNTIF(A2:J2,7),您将获得颜色的总数。

#3

Try this:

in B4 put this formula:

在B4中放了这个公式:

=IF(B2="Team1",IF(B3<A3,1,0),IF(B2="Team2",IF(B3<OFFSET(A3,0,-1),1,0),""))

Then then copy it till AJ4.

然后将其复制到AJ4。

Then in AK3 put this formula:

然后在AK3中放入这个公式:

=COUNTIFS($A$2:$AJ$2,"Team1",$A$4:$AJ$4,1)

Similarly in AL3 put this formula.

同样在AL3中放了这个公式。

=COUNTIFS($A$2:$AJ$2,"Team2",$A$4:$AJ$4,1)

Hope this approach works for you.

希望这种方法适合你。