Как сложить сетевые дни для нескольких календарей для нескольких людей

Question

Я использую 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) ? Есть ли нотация, чтобы объединить календарь отпусков человека А с календарем отпусков команды А?

Answer 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)