No Excel, soma os dados hierárquicos com base na existência de subdata

Question

Eu tenho um conjunto de linhas como essas que exibem alguns dados hierárquicos.

      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 | 

Colunas A e B são tarefas e subtarefas, colunas C e D são entradas correspondentes a cada tarefa / subtarefa. As colunas E e F são colunas de transformação C & D (aqui eles são simplesmente duplicados).

Observe que o C3 está vazio, porque a tarefa não recebe um valor diretamente; em vez disso, é a soma das subtarefas. Correspondentemente, E3 é = SUM (F4: F5).

Atualmente, faço isso manualmente. Eu gostaria de uma função que faria algo como o seguinte pseudocódigo:

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

Então a célula E3 iria:

• use o valor em C3 se C3 não estiver em branco.
• Se C3 estiver em branco, SUM () as células em F4: F para qualquer número de células em branco contíguas em C.

Eu sou muito bom em muito bom em excel. Além disso, eu sou um programador, mas eu não sei nada sobre o VB (linux guy), então eu ficaria feliz com uma solução VB. Eu sou fluente em JavaScript para que funcione também se houver algum tipo de plugin javascript para excel).

Estou usando a versão mais recente do Excel (talvez 2016? A que está na última edição de negócios do escritório).

    

Answer 1

Esta pode não ser a fórmula mais eficiente, mas isso funciona

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

Explicação

AND(ISBLANK(B3); ISBLANK(C3)); detecta quando iniciar a fórmula da soma. Exige que ambas célula B3 e C3 estejam vazias (se você usar apenas C3 , então C4 e C5 também acionará a fórmula de soma, em vez de duplicar seu valor )

INDIRECT( ... ) forma o intervalo a ser usado no SUM

"F"&ROW()+1&": o intervalo começa em F e abaixo da linha atual (neste exemplo F4 )

F"&MATCH(TRUE; INDEX($B4:$B$16=""; 0); 0)+ROW()) e termina na linha F na próxima célula em branco na coluna B

Observação: lembre-se de que o intervalo começa e termina uma linha abaixo da linha atual. Observe também a posição dos sinais absolutos ( $ )

Mais explicações sobre MATCH(TRUE; INDEX($B4:$B$16=""; 0); 0)+ROW()-1 Eu encontrei a fórmula aqui

Primeiro, INDEX($B4:$B$16=""; 0) cria uma matriz, seja B4 = "" e B5 = "" e assim por diante. Você pode ver essa matriz colocando a fórmula na linha 1 em qualquer coluna e alterando 0 para ROW()-1 e arrastando a fórmula para baixo.

FALSE
FALSE
FALSE
TRUE
and so on...

Em seguida, MATCH(TRUE; ... ; 0) encontrará o primeiro TRUE , que é a primeira próxima linha em branco após esta linha (retornando a linha índice 3 )

No entanto, o resultado é o índice da matriz 3 , em vez de número da linha . Então, adicionamos o índice com a linha atual, 3 + 3 e voila! Temos o número da linha da próxima linha em branco. Basta adicionar -1 para ter certeza de que estamos somando o intervalo correto de F4:F5 , não F4:F6 .