Excel实战技巧91： 安排工作时间进度计划表（又一种形式）

学习Excel技术，关注微信公众号：

excelperfect

导言：在《Excel实战技巧90：安排工作时间进度计划表》中，以类似甘特图的形式使用公式计算每天各项任务的时间，从而形成一个时间进度计划表。本文介绍另一种形式：按竖向排列的进度计划表。

如下图1所示，在“源数据”工作表中列出了完成某项目需要依次做的工作任务以及每项任务所需要的时间。示例中的项目需要依次执行任务A、任务B、任务C、任务D。

图1

现在，如果每天的工作时间按24小时安排，要排出完成这个项目每天所需完成的任务及相应的时间，如下图2所示的“时间安排”工作表。例如，该项目完成任务A需要40小时，那么第1天占用了全部的24小时，还剩余16小时（40-24=16）需要安排在第2天执行，这样第2天还多8个小时（24-16=8）安排执行任务B，…依此类推。

图2

这里，用到了一个辅助列。在“源数据”工作表中的列C中，计算完成项目的累计时间，如下图3所示。

图3

下面是公式中要用到的几个定义的名称：

名称：MaxHrsPerDay

引用位置：=24

名称：WorkList

引用位置：=源数据!$A$2:$A$5

名称：WorkDuration

引用位置：=源数据!$B$2:$B$5

名称：CumulativeDuration

引用位置：=源数据!$C$2:$C$5

在“时间安排”工作表的单元格A2中输入数组公式：

=IF(SUM(C$1:C1)>=SUMPRODUCT(WorkDuration),"…", MAX( N(A1) + (SUMIFS(C$1:C1, A$1:A1, A1)>=MaxHrsPerDay), 1))

向下拖至相应的单元格。

在“时间安排”工作表的单元格B2中输入数组公式：

=IF(SUM(C$1:C1)>=SUMPRODUCT(WorkDuration),"…",INDEX(WorkList, MATCH(TRUE, (CumulativeDuration-SUM(C$1:C1))> 0, 0)))

向下拖至相应的单元格。

在“时间安排”工作表的单元格C2中输入数组公式：

=IF(SUM(C$1:C1)>=SUMPRODUCT(WorkDuration),"…",MIN(INDEX(WorkDuration, MATCH(TRUE, CumulativeDuration-SUM(C$1:C1)> 0, 0)) - SUMIFS(C$1:C1, B$1:B1,B2), MaxHrsPerDay-SUMPRODUCT((A$1:A1=A2)*IF(ISNUMBER(C$1:C1), C$1:C1, 0))))

可见，这与《Excel实战技巧90：安排工作时间进度计划表》横向排列的时间计划表相比。公式要复杂得多。

公式分析

列A中的公式中：

SUM(C$1:C1)>=SUMPRODUCT(WorkDuration)

用来计算列C中的时间之和是否大于累积的时间，如果大于则表明全部任务已完成，输入“…”，否则计算下面公式：

MAX( N(A1) + (SUMIFS(C$1:C1, A$1:A1,A1)>=MaxHrsPerDay), 1)

其中的SUMIFS(C$1:C1, A$1:A1, A1)求同一天的时间之和，如果大于等于每天的工作时间MaxHrsPerDay，则对于单元格A2中的公式转换为：

MAX( N(A1) +1, 1)

即：

MAX(1, 1)

结果为：

1

列B中的公式前半部分与上面所讲的列A中的公式前半部分相同。其后半部分（对于单元格B2中的公式来说）：

INDEX(WorkList, MATCH(TRUE,(CumulativeDuration-SUM(C$1:C1)) > 0, 0))

转换为：

INDEX(WorkList, MATCH(TRUE, ({40;60;65;80}-0)> 0, 0))

转换为：

INDEX(WorkList, MATCH(TRUE, {TRUE;TRUE;TRUE;TRUE}, 0))

转换为：

INDEX({"任务A";"任务B";"任务C";"任务D"}, 1)

得到：

任务A

列C中的公式中：

SUMPRODUCT((A$1:A1=A2)*IF(ISNUMBER(C$1:C1), C$1:C1, 0))

计算直到上一行为止的所有与当前行所在同一天的时间的总和，再使用MaxHrsPerDay减去求得的时间，即为该天还剩余的时间。

公式中的：

SUMIFS(C$1:C1, B$1:B1,B2)

计算当前行所在的工作任务已经用去的时间。

公式中的：

SUM(C$1:C1)

计算直到当前行的前一行为止所累积的时间。

公式中的：

CumulativeDuration-SUM(C$1:C1)

随着每项任务所分配的时间，而减少累积时间。如果数组中某项为0，则意味着相应的任务所需要的时间已完全分配。这样，公式：

MATCH(TRUE, CumulativeDuration-SUM(C$1:C1)> 0, 0)

能够找到非零值的位置，即一项新任务开始。代入INDEX函数中：

INDEX(WorkDuration, MATCH(TRUE,CumulativeDuration-SUM(C$1:C1) > 0, 0))

从WorkDuration中获取任务开始时相对应的时间值，对于单元格C2来说：

INDEX(WorkDuration, 1)

即：

40

这样，单元格C2中的公式即：

=IF(False,"…",MIN(40, 24))

得到：

24

公式中，进行了一些数学计算，从而得到想要的结果，非常巧妙！有兴趣的朋友可以在选择公式中的某部分后使用F9键或者“公式求值”查看公式运行的中间结果，以加深对公式的理解。

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料。