☰

如何计算 Excel 电子表格中有多少个单元格具有非特定时间范围？

tag-icon • tag-icon microsoft-excel
如何计算 Excel 电子表格中有多少个单元格具有非特定时间范围？

背景：我是一家零售企业的经理，正在尝试找出某一年出生的人平均在什么时间/什么日子来我们店里。为此，我列出了该年龄段的所有会员，然后列出了所有人来店的所有时间。目前，我正在手动检查所有顾客的交易，并记录他们来店的时间。这非常繁琐且耗时。

如果我知道该怎么做，我想我可以更快地解决这个问题：我希望能够找到所有单元格，例如，时间戳从 12:00:00 PM 到 12:59:59 PM。这样我就可以得到此列表中所有客户访问商店的总次数。我该怎么做呢？我真的更喜欢使用“查找全部”工具来做到这一点。我以为我可以输入类似 12:xx:xx PM 的内容，但那没用。

希望我已经解释得足够清楚，能够得到答案，或者推动正确的方向。

干杯

答案1

使用数据透视表。

将时间放入行和值中。

确保它设置为时间计数：

在此处输入图片描述

答案2

您要做的事情比使用仅带有简单COUNTIFS()或 的公式要复杂一些SUMPRODUCT()。它需要二维数组公式。

我创建了一个示例工作表，希望能够帮助您入门。

设置如下工作表：

工作表截图

数组输入（Ctrl++ Shift）Enter以下公式I3，然后向下复制粘贴/填充：

{=SUM(($C$3:$C$15>=G3)*($C$3:$C$15<=H3)*($D$3:$D$15=TRANSPOSE(OFFSET($K$3:$K$15,,MATCH(F3,$K$2:$M$2,0)-1))))}

该公式的美化版本如下：

{=
SUM(
   ($C$3:$C$15>=G3)
  *($C$3:$C$15<=H3)
  *($D$3:$D$15=TRANSPOSE(OFFSET($K$3:$K$15,,MATCH(F3,$K$2:$M$2,0)-1)))
)}

解释：

里面的前两个子表达式SUM()创建一维列数组来过滤所需的时间间隔。

第三个子表达式创建了一个二维数组，该数组由出生年份表相应列的每个单元格组成，从而仅筛选出与该年份出生的人匹配的交易。

OFFSET($K$3:$K$15,,MATCH(F3,$K$2:$M$2,0)-1)根据 中的值选择适当的出生年份列，F3例如，值1991将选择$L$3:$L$15。TRANSPOSE()需要将列数组转换为行数组，以便乘法结果为二维数组。 笔记：

  • 美化的公式确实可以起作用。

答案3

假设您想计算某个时间间隔内访问过的客户数量，那么您的公式应该是以下任一项：

=COUNTIFS(A2:A20,">="&C2,A2:A20,"<="&D2)

=SUMPRODUCT((A2:A20>=C2) * (A2:A20<=D2))

如果数据范围有日期时间戳那么你的公式应该是：

=SUMPRODUCT((ROUND(MOD(A2:A20,1),10)>=C2) * (ROUND(MOD(A2:A20,1),10)<=D2))

注意：其中 C2 为 08:30 且 D2 为 12:00。

相关内容