![如何计算 Excel 电子表格中有多少个单元格具有非特定时间范围?](https://linux22.com/image/1563483/%E5%A6%82%E4%BD%95%E8%AE%A1%E7%AE%97%20Excel%20%E7%94%B5%E5%AD%90%E8%A1%A8%E6%A0%BC%E4%B8%AD%E6%9C%89%E5%A4%9A%E5%B0%91%E4%B8%AA%E5%8D%95%E5%85%83%E6%A0%BC%E5%85%B7%E6%9C%89%E9%9D%9E%E7%89%B9%E5%AE%9A%E6%97%B6%E9%97%B4%E8%8C%83%E5%9B%B4%EF%BC%9F.png)
背景:我是一家零售企业的经理,正在尝试找出某一年出生的人平均在什么时间/什么日子来我们店里。为此,我列出了该年龄段的所有会员,然后列出了所有人来店的所有时间。目前,我正在手动检查所有顾客的交易,并记录他们来店的时间。这非常繁琐且耗时。
如果我知道该怎么做,我想我可以更快地解决这个问题:我希望能够找到所有单元格,例如,时间戳从 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。