excel - 根据列中的日期值使用 COUNTIF

Question

我有一个看起来像这样的表:
表 1(起始 A1)



03-02-2021
03-03-2021


角色_1
名称_1
在
出去

角色_1
名称_2
在
在


我需要创建第二个表格，告诉我每个角色每天的“IN”数量。所以它看起来像:
表 2(起始 A1):



03-02-2021
03-03-2021


角色_1
2
1


所以基本上，每天，计算我拥有的“IN”数量。我尝试通过执行以下公式来做到这一点:

=COUNTIFS(Table_1!A:A,A2,INDEX(table_1,,MATCH(B1,Table_1!1:1,0)),"IN")

这里， COUNTIF 有两个部分:

Table_1!A:A,A2

该位在表 1 的第一列 (A) 中查找角色的数量。

INDEX(table_1,,MATCH(B1,Table_1!1:1,0)),"IN"

在表 1 中查找并通过特定日期的 INDEX MATCHING 派生相关列的 INDEX MATCH，然后计算“IN”。
虽然这两个都独立工作，但它们不能一起工作(因此没有给我正确的答案)。一起公式只是错误。
我应该如何调整它来做我想做的事？

Answer 1

我在名为 Table_1 的工作表中制作了假数据集像这样:

此外，还有一个名为 Table_2 的工作表。明白啦:

我在 B2 中的公式是:

=SUMPRODUCT(--(Table_1!$C$2:$D$5="IN")*--(Table_1!$C$1:$D$1=Table_2!B$1)*--(Table_1!$A$2:$A$5=Table_2!$A2))

只需向右和向下拖动，它应该可以工作。
这是它的工作原理(以 B2 为例):

--(Table_1!$C$2:$D$5="IN")将创建一个 1 和 0 的数组，具体取决于单元格值是否为 ÌN或 OUT .所以它返回 {1;0\1;1\1;0\0;1}

--(Table_1!$C$1:$D$1=Table_2!B$1)如果它们等于列中的日期，则将返回标题行的 1 和 0 的数组。所以它返回 {1;0}

将第 1 步和第 2 步中的两个数组相乘，因此返回 {1;0\1;1\1;0\0;1} * {1;0} = {1;0\1;0\1;0\0;0}

--(Table_1!$A$2:$A$5=Table_2!$A2)如果行值是否相同，将返回一个 1 和 0 的数组。所以它返回 {1\1\0\0}

我们从第 4 步和第 3 步的数组中得到产品，所以 {1;0\1;0\1;0\0;0} * {1\1\0\0} = {1;0\1;0\0;0\0;0}

{1;0\1;0\0;0\0;0} = 2 的最终总和

希望这可以帮助。您可以使用 Excel 中的 EVALUATE FORMULA 按钮查看它是如何工作的，并自己查看数组。