Excel 用函数与条件格式实现时间表的生成

发表于 更新于

最近在帮朋友做一个项目成员的空闲时间表，看上去是这样的：

TimeTable

之前一直是手动添加的填充色之类的样式来实现，但是因为最近时间总是在频繁更改，所以就想用Excel的函数以及条件格式来实现根据时间表自动生成，可以减少不少工作量。

数据处理

关于原始数据，我们需要记录姓名(Name)、星期几(Day)、开始时间(Start Time)、结束时间(End Time)，同一个人在同一天可以有多个时间段，为了方便函数的使用，我们这些数据生成如下这样的表格（选中数据及表头，插入→表格→确定生成。生成后点击表设计，修改表名称为一个好记得名字，这里我改为了TimeSheet）：

我又使用公式=ROW()-ROW(TimeList[[#标题],[id]])（该行行号-表头所在行号），添加了一列id值，使数据具有了唯一键值，便于后续操作（不必要）

使用的函数

SUMPRODUCT

用于将指定范围或数组中的元素相乘，并返回其的乘积之和。

  • SUMPRODUCT (array1, [array2], …)
    • array1 必需。其需要将其内部元素进行相乘并求和的第一个数组参数。

SUMPRODUCT函数会将非数元素作为0处理。

具体实现方法

以(TimeTable表的)星期一为例：

完整公式也很简单，如下：

1
SUMPRODUCT((TimeSheet[Day]=$E$2)*(TimeSheet[Name]=F$32)*($B5>=TimeSheet[Start Time])*($B5<TimeSheet[End Time]))
  • $E$2 为TimeTable表 对应星期值（Mon, Tue, …）
  • F$32 为TimeTable表 该列对应姓名
  • $B5 为TimeTable表中小单元格对应时间段的开始时间

这里活用了一下SUMPRODUCT函数，我们的目的是判断TimeSheet列表中是否存在同时满足一下条件的条目：
- Day为相应星期名
- Name为对应人的姓名
- Start Time小于等于该时间段的开始时间
- End Time 大于该时间段的开始时间
若条件全部符合，则函数会返回1，若有任意条件不符合，则会返回0。

通过自动填充功能，现在整个表格应该布满了0和1，最后我们来对这些单元格设置条件格式；
选中所有单元格，点击条件格式→新建规则，在弹出的窗口中设置规则为只为包含以下内容的单元格设置格式，单元格值 等于 1：

然后设置填充色，点击确定后，就可以发现符合条件的单元格已经被填充上颜色了。

此时我们选中设置完格式的单元格，右键设置单元格格式，将数字选项改为自定义，在类型中输入 ;;; （三个英文分号），此设置可以将单元格的内容在样式上进行隐藏。

这样我们就完成了时间表的制作，同时也可以进行一些美化，如：将不同人的填充色设置为不同颜色。

TimeTable

优点

使用函数的方式生成，可以大大降低工作量，并且便于更新（可以在修改TimeSheet表的同时进行更新），而且可以通过在TimeSheet表中插入新行，来添加新的时间段。