В Excel Суммируйте иерархические данные, основанные на существовании подданных

Question

У меня есть ряд таких строк, которые отображают некоторые иерархические данные.

      A  |    B    | C | D | E | F 

1   Task |         | 1 |   | 2 |  
2   Task |         | 1 |   | 2 |  
3   Task |         |   |   | 8 |  
4        | Subtask |   | 2 |   | 4  
5        | Subtask |   | 2 |   | 4  
6   Task |         | 1 |   | 2 |   
7   Task |         | 1 |   | 2 | 

Столбцы A и B - это задачи и подзадачи, столбцы C и D - входные данные, соответствующие каждой задаче / подзадаче. Столбцы E и F являются столбцами преобразований C & D (здесь они просто дублируются).

Обратите внимание, что C3 пусто, потому что задаче не присваивается значение напрямую; скорее это сумма подзадач. Соответственно, E3 = SUM(F4:F5).

В настоящее время я делаю это вручную. Я хотел бы функцию, которая будет делать что-то вроде следующего псевдокода:

IF(
  NOT(C3=""),
  C3,
  SUM_UNTIL(E:E,NOT(""),F:F)
)

Таким образом, ячейка E3 будет:

• используйте значение в C3, если C3 не пустое.
• Если C3 пусто, SUM() ячейки в F4:F для любого количества смежных пустых ячеек в C.

Я очень хорошо очень хорошо в Excel. Кроме того, я программист, но я совсем не знаю VB (парень из Linux), поэтому я был бы счастлив с решением VB. Я свободно владею JavaScript, и это тоже сработало бы, если есть какой-нибудь плагин javascript для excel).

Я использую последнюю версию Excel (может быть, 2016? Тот, что в последней деловой редакции офиса).

Answer 1

Это может быть не самая эффективная формула, но это работает

=IF(AND(ISBLANK(B3); ISBLANK(C3));
    SUM(INDIRECT("F"&ROW()+1&":
                  F"&MATCH(TRUE; INDEX($B4:$B$16=""; 0); 0)+ROW()-1));
    C3*2)

объяснение

AND(ISBLANK(B3); ISBLANK(C3)); определяет, когда начинать формулу суммы. Требуется, чтобы обе ячейки B3 и C3 были пустыми (если вы используете только C3 , тогда C4 и C5 также вызовут формулу суммы вместо удвоения их значения)

INDIRECT( ... ) формирует диапазон, который будет использоваться в SUM

"F"&ROW()+1&": диапазон начинается с F и ниже текущей строки (в этом примере F4)

F"&MATCH(TRUE; INDEX($B4:$B$16=""; 0); 0)+ROW()) и заканчивается строкой F в следующей пустой ячейке столбца B

Примечание: пожалуйста, помните, что диапазон начинается и заканчивается на одну строку ниже текущей строки. Также обратите внимание на положение абсолютных знаков ($)

---

Дальнейшее объяснение MATCH(TRUE; INDEX($B4:$B$16=""; 0); 0)+ROW()-1 Я нашел формулу здесь

Во-первых, INDEX($B4:$B$16=""; 0) создает массив B4 = "" и B5 = "" и т.д. Вы можете увидеть этот массив, поместив формулу в строку 1 любого столбца, изменив 0 на ROW()-1 и перетащив формулу вниз.

FALSE
FALSE
FALSE
TRUE
and so on...

Затем MATCH(TRUE; ... ; 0) найдет первую TRUE , то есть первую следующую пустую строку после этой строки (возвращая индекс 3)

Тем не менее, результатом является индекс массива 3, а не номер строки. Таким образом, мы добавляем индекс с текущей строкой, 3 + 3 и вуаля! У нас есть номер следующей пустой строки. Просто добавьте -1 чтобы убедиться, что мы суммируем правильный диапазон F4:F5 , а не F4:F6 .