☰

无法输入指定的公式，因为它包含的值、引用和/或名称超出了当前文件格式允许的范围

tag-icon • tag-icon microsoft-excel worksheet-function
无法输入指定的公式，因为它包含的值、引用和/或名称超出了当前文件格式允许的范围

我正在尝试建立一个电子表格，显示每天每 15 个增量中有多少名员工。我使用过：

=COUNTIF(c8:c41,"7")+COUNTIF(c8:c41,"7.15")+COUNTIF(c8:c41,"7.30")+COUNTIF(c8:c41,"7.45")+COUNTIF(c8:c41,"8.00")+COUNTIF(c8:c41,"8.15")+COUNTIF(c8:c41,"8.30")+COUNTIF(c8:c41,"8.45")+COUNTIF(c8:c41,"9.00")+COUNTIF(c8:c41,"9.15")+COUNTIF(c8:c41,"9.30")+COUNTIF(c8:c41,"9.45")+COUNTIF(c8:c41,"10.00")+COUNTIF(c8:c41,"10.15")+COUNTIF(c8:c41,"10.30")+COUNTIF(c8:c41,"10.45")+COUNTIF(c8:c41,"11.00")+COUNTIF(c8:c41,"11.15")+COUNTIF(c8:c41,"11.30")+COUNTIF(c8:c41,"11.45")+COUNTIF(c8:c41,"12.00")+COUNTIF(c8:c41,"12.15")+COUNTIF(c8:c41,"12.30")+COUNTIF(c8:c41,"12.45")+COUNTIF(c8:c41,"13.00")+COUNTIF(c8:c41,"13.15")+COUNTIF(c8:c41,"13.30")+COUNTIF(c8:c41,"13.45")+COUNTIF(c8:c41,"14.00")+COUNTIF(c8:c41,"14.15")+COUNTIF(c8:c41,"14.30")+COUNTIF(c8:c41,"14.45")+COUNTIF(c8:c41,"15.00")+COUNTIF(c8:c41,"15.00")+COUNTIF(c8:c41,"15.15")+COUNTIF(c8:c41,"15.30")+COUNTIF(c8:c41,"15.45")+COUNTIF(c8:c41,"16.00")+COUNTIF(c8:c41,"16.15")+COUNTIF(c8:c41,"16.30")+COUNTIF(c8:c41,"16.45")+COUNTIF(c8:c41,"17.00")+COUNTIF(c8:c41,"17.15")+COUNTIF(c8:c41,"17.30")+COUNTIF(c8:c41,"17.45")+COUNTIF(c8:c41,"18.00")-COUNTIF(d8:d41,"11.00")-COUNTIF(d8:d41,"11.15")-COUNTIF(d8:d41,"11.30")-COUNTIF(d8:d41,"11.45")-COUNTIF(d8:d41,"12.00")-COUNTIF(d8:d41,"12.15")-COUNTIF(d8:d41,"12.30")-COUNTIF(d8:d41,"12.45")-COUNTIF(d8:d41,"13.00")-COUNTIF(d8:d41,"13.15")-COUNTIF(d8:d41,"13.30")-COUNTIF(d8:d41,"13.45")-COUNTIF(d8:d41,"14.00")-COUNTIF(d8:d41,"14.15")-COUNTIF(d8:d41,"14.30")-COUNTIF(d8:d41,"14.45")-COUNTIF(d8:d41,"15.00")-COUNTIF(d8:d41,"15.15")-COUNTIF(d8:d41,"15.30")-COUNTIF(d8:d41,"15.45")-COUNTIF(d8:d41,"16.00")-COUNTIF(d8:d41,"16.15")-COUNTIF(d8:d41,"16.30")-COUNTIF(d8:d41,"16.45")-COUNTIF(d8:d41,"17.00")-COUNTIF(d8:d41,"17.15")-COUNTIF(d8:d41,"17.30")-COUNTIF(d8:d41,"17.45")-COUNTIF(d8:d41,"18.00")

但是我知道收到此错误消息。我需要转到 21:00 点。

请有人帮忙 - 我想不出解决这个问题的办法:(

答案1

下面是我在电子表格中使用的一个简单方法的示例，它可以完成相同的操作。

电子表格示例

列F代表BJ截至 21:00 的每个 15 分钟区块，并且单元格被有条件地格式化为

  • 填充颜​​色并
  • 将文本颜色更改为与填充颜色相同
  • 什么时候the cell value = 1。

条件格式

细胞F2有以下公式
=IF(AND(F$1>=$C2,F$1<$D2),1,"")

（如果单元F1格 >= 且start time单元格F1< finish time，则将 a 放入1单元格中）

并将公式拖动到 BJ 列并填充到人员列表的底部。

由于现在每个人在他们所处的 15 分钟时间段内都有一个 1，因此需要使用公式对 1 的列求和SUM()（在屏幕截图中的第 4 行）。

相关内容