☰

如何对多个人的多个日历的网络日进行求和

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
如何对多个人的多个日历的网络日进行求和

我正在使用 MS Excel 2010。我需要做什么才能创建这样的表格：

+----------------+--------+-----------------+
| Milestone Date | Team   | Hours Remaining |
+----------------+--------+-----------------+
| 5/27/2016      | Team A | ???             |
+----------------+--------+-----------------+

这样我就可以输入任意的“里程碑日期”并选择一个团队并计算“剩余时间”，如下所示：

sum for each person on Team A NETWORKDAYS(TODAY(), Milestone Date, Person X Holiday Calendar + Person X Vacation Calendar) * 8 hrs/day

我有一组如下所示的表格：

+-----------------------------------------+
| Team Member | Team   | Holiday Calendar |
+-----------------------------------------+
| Person 1    | Team A | Calendar A       |
+-----------------------------------------+
| Person 2    | Team A | Calendar A       |
+-----------------------------------------+
| Person 3    | Team B | Calendar A       |
+-----------------------------------------+
| Person 4    | Team B | Calendar B       |
+-----------------------------------------+

+------------+------------+
| Calendar A | Calendar B |
+------------+------------+
| 5/30/2016  | 9/1/2016   |
+------------+------------+
| 5/31/2016  | 10/1/2016  |
+------------+------------+
| 12/24/2016 | 11/1/2016  |
+------------+------------+

+---------------------------------------------------+
|                 Vacation Calendar                 |
+------------+------------+------------+------------+
|  Person 1  |  Person 2  |  Person 3  |  Person 4  |
+------------+------------+------------+------------+
|  5/13/2016 |  5/02/2016 |  5/22/2016 |  4/27/2016 |
+------------+------------+------------+------------+
|  5/13/2016 |            |  6/3/2016  |            |
+------------+------------+------------+------------+

例如，有没有办法将 NETWORKDAYS() 包装成某种FOR_EACH(team member on Team A)构造？是否有某种符号可以将人员 A 的假期日历与团队 A 的假期日历结合起来？

答案1

我已经搞清楚了。首先，我扩展了“团队成员”表，以包括每个团队成员的工作时间。

+-----------------------------------------+--------------+
| Team Member | Team   | Holiday Calendar | Hours Remain |
+-------------+--------+------------------+--------------+
| Person 1    | Team A | Calendar A       |  FORMULA     |
+-------------+--------+------------------+--------------+
| Person 2    | Team A | Calendar A       |  FORMULA     |
+-------------+--------+------------------+--------------+
| Person 3    | Team B | Calendar A       |  FORMULA     |
+-------------+--------+------------------+--------------+
| Person 4    | Team B | Calendar B       |  FORMULA     |
+-------------+--------+------------------+--------------+

公式是

=NETWORKDAYS(TODAY(),Milestone Date,ArrayFormula({HLOOKUP(Which Holiday Calendar,Holidays!$A:$F,ROW(Holidays!$A$2:$A$100),0),HLOOKUP(Which Vacation Calendar,Vacation!$A:$F,ROW(Vacation!$A$2:$A$100),0)}))

其中的关键是 CSE 公式用于获取日期数组，以及 {} 用于连接两个数组。（我最初将此发布用于 Excel，但最终使用了 Google Sheets。因此，语法略有不同。但是，Excel 可能有办法获得类似的效果。）

然后在最终的表格中，剩余小时数只是一个 SUMIF（A 队剩余的小时数）

相关内容