excel-formula - 我想查找日期范围内的文本计数作为数组中的标准 1 和标准 2

您可能遇到的部分问题是日期。确保您的日期是 excel 日期，而不是看起来像日期的字符串/文本。简单地更改单元格的格式并不能使其成为日期，它只是告诉 excel 如何尝试在单元格中显示信息。

excel 中的日期存储为整数，它们表示自 1900/1/1 以来的天数，该日期为第 1 天。测试单元格是否包含日期或字符串的最简单方法之一是：

=ISTEXT(A1)
or
=ISNUMBER(A1)

Where A1 is the cell with the date to be tested.

如果它为第一个公式返回 TRUE，则它是一个字符串/文本。FALSE 表示它是一个数字。相反的 TRUE 和 FALSE 结果适用于第二个公式。

在您的公式中，当引号“”之间有某些内容时，它将被解释为字符串。所以类似的"<=19/05/26"意思是它寻找一个小于那个的字符串，而不是一个小于那个的日期。要进行日期比较，请将文本比较与包含日期的单元格连接起来以比较日期的"<="&B2位置B2，或者如果您想对其进行硬编码，请使用类似"<="&Date(19,05,26)

为了使以下解决方案起作用，您的日期都需要存储为一个数字。又名 Excel 序列日期格式。

根据下图所示的数据，您可以使用 COUNTIFS、INDEX 和 MATCH 来获取您要查找的日期。我建议分别找到您的 D 和 N 计数，然后将它们加在一起。但是，如果您希望在单个单元格公式解决方案中使用它，则可以通过 N 列中的结果来实现和证明。但是，公式开始变长，并且可能难以在以后读取/维护。

该解决方案的核心将是 COUNTIFS 函数。COUNTIFS 函数的格式如下：

COUNTIFS(Range to count 1, Criteria 1, Range to count 2, Criteria 2,...,Range to count n, Criteria n)

让我们开始一次构建您的公式一个标准。第一个条件将是大于或等于起始日期的所有日期。如果您只想要起始日期之后的日期，请删除等号或条件。

=COUNTIFS($A$2:$A$4,">="&$G2,

注意 $ 以锁定单元格引用。这样做是为了在复制公式时，$ 旁边的列或行引用不会改变。

然后第二个条件类似于第一个条件，只是您要获取小于或等于截止日期的所有日期。再次包括/删除等号以满足您的需要。

=COUNTIFS($A$2:$A$4,">="&$G2,$A$2:$A$4,"<="&$H2,

下一个标准将是获取与列标题 D 或 N 匹配的所有单元格。现在让我们只关注 D。棘手的部分是定义要查看的列。现在让我们调用要查看 XXX 的列，这将使公式变为：

=COUNTIFS($A$2:$A$4,">="&$G2,$A$2:$A$4,"<="&$H2,XXX,J$1)
OR
=COUNTIFS($A$2:$A$4,">="&$G2,$A$2:$A$4,"<="&$H2,XXX,"="&J$1)

NOTE: both formulas are the same.  When no comparison operator is provided
      it is taken as "=" by default.

现在为了定义 XXX，INDEX 和 MATCH 将成为你的朋友。关于 INDEX 的一个重要注意事项是，它不直接返回单元格的值，而是返回一个单元格地址，该地址反过来提取一个单元格值。INDEX的基本格式是：

INDEX(Range to look in, Range's ROW to look in, Range's COLUMN to look in)

那是一个二维范围。如果您的范围是一维的，或者只是一列或只是一行，那么只需要提供第二个参数“Range's Row...”，它表示列表要走多远。

2D INDEX 的有趣之处在于，当为 ROW 或要查看的列提供 0 时，它不会抛出错误，而是返回所有行或列。这很重要，因为您只需要 1 个特定列的所有行。这意味着您的 INDEX 公式将开始如下所示：

INDEX($B$2:$E$4,0,SPECIFIC COLUMN NUMBER)

所以现在你需要找到一个特定的列号。那就是 MATCH 将成为您的朋友的地方。MATCH 采用以下格式：

MATCH(Value to find, 1D range to look in, what type of search)

您已经知道您将尝试匹配您的班次列，这将是您的查找值，而要查找的范围将是您的列标题。在这种情况下，您需要的搜索类型是精确匹配，用 0 表示。这意味着您的 MATCH 公式将如下所示：

MATCH($I2,$B$1:$E$1,0)

现在组合各个部分，将 MATCH 公式放入您的 INDEX 并替换“SPECIFIC COLUMN...”。您的 INDEX 现在看起来像：

INDEX($B$2:$E$4,0,MATCH($I2,$B$1:$E$1,0))

上面的公式现在可以替换您的 COUNTIFS 公式中的 XXX，您将得到：

=COUNTIFS($A$2:$A$4,">="&$G2,$A$2:$A$4,"<="&$H2,INDEX($B$2:$E$4,0,MATCH($I2,$B$1:$E$1,0)),J$1)

将上面的公式放在J2中，然后将单元格向下和向右复制。

在 L2 中，使用两个公式之一来获取日期范围内 D 和 N 的总和：

=SUM(J2:K2)
OR
=J2+K2

现在要将您的公式全部放在一个单元格中，请查看上面的第二个公式。您可以简单地转到单元格 J2 的内容并复制整个公式。然后编辑单元格 L2 并将 J2 的单元格引用替换为复制的公式。通过复制 K2 中的公式并替换 L2 中对 K2 的引用来重复该过程。你会得到一个看起来像这样的公式：

=COUNTIFS($A$2:$A$4,">="&$G2,$A$2:$A$4,"<="&$H2,INDEX($B$2:$E$4,0,MATCH($I2,$B$1:$E$1,0)),J$1)+COUNTIFS($A$2:$A$4,">="&$G2,$A$2:$A$4,"<="&$H2,INDEX($B$2:$E$4,0,MATCH($I2,$B$1:$E$1,0)),K$1)

更长更难阅读，这就是为什么我建议将公式分解为 D 和 N 的部分。

现在作为一种替代方法，您可以使用 SUMPRODUCT 并进入数组操作。您放置在 I2 中并向下复制的 SUMPRODUCT 公式可以是：

=SUMPRODUCT(($A$2:$A$4>=$G2)*($A$2:$A$4<=$H2)*(INDEX($B$2:$E$4,0,MATCH($I2,$B$1:$E$1,0))=J$1))

excel-formula - 我想查找日期范围内的文本计数作为数组中的标准 1 和标准 2

问题描述

价值 -？

解决方案

推荐阅读