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

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

      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 году? В последней версии Office для бизнеса).

Источник

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,

Источник
Другие вопросы по тегам