Como pular células ao criar um array no excel?

Question

Eu quero que as células sejam excluídas de uma fórmula de matriz quando eu criar a matriz, como em não ter a matriz nessas células. Isso é possível?

Nesta imagem, a tabela da esquerda são os dados que estou usando para produzir os relatórios no centro e à direita. Na planilha real, os dados são distribuídos em várias planilhas e mais ou essas folhas são adicionadas mensalmente.

Atualmente, a tabela central é o que usa uma matriz para saída produz, isso faz com que as somas sejam duplicadas nas linhas onde o número da tarefa é para os subitens. Eu não posso (não tenho permissão para) remover o número da tarefa para esses subitens.

A tabela à direita é o que eu tenho atualmente, com uma fórmula em cada uma das caixas de valor preenchidas. No entanto, como essa fórmula na realidade consiste em vários sumifs para as diferentes folhas de dados, eu gostaria de ter que atualizar apenas uma célula e CTRL + SHIFT + ENTER para atualizar toda a coluna de células. Esta tabela é também as células que eu gostaria que o array estivesse.

Uma fórmula da planilha atual:

=SUMIFS('April 18'!S:S,'April 18'!M:M,Summary!A:A,'April 18'!O:O,"4.2018")+SUMIFS('May 18'!T:T,'May 18'!N:N,Summary!A:A,'May 18'!P:P,"4.2018")+SUMIFS('July 18'!$U:$U,'July 18'!$N:$N,Summary!$A:$A,'July 18'!$Q:$Q,"4.2018")+SUMIFS('June 18'!$U:$U,'June 18'!$N:$N,Summary!$A:$A,'June 18'!$Q:$Q,"4.2018")

    

Answer 1

Suposições e esclarecimentos.

1. Sua fórmula original não foi verificada. O exemplo abaixo usa dados simplificados e uma fórmula básica usando SUMIFS
2. Supõe-se que você esteja usando Recuo à direita para inserir subitens. Se você estiver usando o Spaces por acaso, use o botão Recuo à direita para alinhar os subitens. Também é assumido que todos os seus itens são basicamente Alinhados à Esquerda nas respectivas células.

Neste exemplo, a tabela de entrada de amostra está nas células A1: A13 e a tabela de saída é F2: F14.

Abra o Editor VBA pressionando ALT + F11 . Insira um módulo (no menu Inserir) e cole o seguinte código nele.

Function IndLevl(cell As Range)

    Application.Volatile
    IndLevl = cell.IndentLevel

End Function 

Isso cria uma Função Definida pelo Usuário (UDF) que aceita Cell como intervalo e retorna o nível de recuo como número. Nenhuma validação está em vigor, portanto, evite passar um intervalo de células como entrada, como A1: A4. Basta passar uma única célula como parâmetro, por ex. =IndLevl(A4)

Se estiver usando o Excel 2007 ou superior, salve o arquivo como .xlsm (Folha de Excel Ativada para Macro)

Agora, em sua tabela de saída, distorça a fórmula em um IF, verificando primeiro o Nível de Indentação e, em seguida, decidindo se deseja executar a fórmula ou apenas deixá-la em branco.

=IF(IndLevl(G3)>0,"",SUMIFS($C:$C,$A:$A,F3,$B:$B,1.2))

Arraste a fórmula para as células obrigatórias. Veja a imagem abaixo. Você pode envolver sua fórmula em um IF e, se necessário, convertê-la em uma fórmula de matriz conforme desejado.

Observe que a instrução Application.Volatile torna a fórmula volátil. No entanto, ele não será executado se você alterar o nível de recuo mais tarde. Nesses casos, basta pressionar F9 para recalcular a folha.

Veja a imagem abaixo.