Como faço para contar o número de linhas em uma matriz SOMENTE SE a soma dos números de cada linha for 0 no Excel?

Question

Eu tenho uma matriz de linha de 150 colunas x 360 com números aleatórios (digamos A2 para ET361) no Excel.

Como faço para calcular para cada coluna (ou seja, da célula B1 para ET1) quantas linhas são maiores que zero para as colunas antes dela?

Critérios:

B1 precisa calcular # de células (A2 a A361) que são > 0.
C1 precisa calcular # de linhas (A2: B2, A3: B3, ..., para A361: B361) onde a soma de cada linha é > 0.
D1 precisa calcular # de linhas (A2: C3, ..., para A361: C361) onde a soma de cada linha é > 0.

Eu tentei usar a fórmula COUNTIF, mas ela só retorna o número de células, não o número de linhas.
Eu acho que preciso de uma fórmula aninhada ROWS () e IF ()? Eu também não quero criar outra matriz de 150 x 360 para lidar com esse problema, pois eu quero economizar espaço no meu arquivo do Excel.

Eu também não quero usar macros e VBA, pois eles complicam minha planilha.

Eu adicionei uma complexidade à equação inteira, em que a função subtotal não funciona.

Eu preciso que cada célula dentro da matriz calcule o número de linhas acima dela para as quais a soma das colunas para cada linha é maior que zero. A solução de Barry não funcionará neste exemplo (testei), pois a fórmula de 'Subtotal' não funciona para células com fórmulas 'subtotais'.

Temos outras alternativas?

    

Answer 1

Embora eu não tenha sido capaz de pensar em uma única solução de fórmula (talvez outra pessoa o faça!), eu inventei algo que ocupa muito menos espaço na planilha do que outra matriz de 150 x 360.

A idéia básica é calcular os totais acumulados em cada linha para uma coluna de dados e depois usá-los em uma tabela de dados ("análise de hipóteses") para gerar as contagens de todas as colunas.

O ponto de partida é a coluna de cálculos das linhas em uma única coluna de dados.

Como mostrado na captura de tela abaixo, eu configurei uma planilha com 10 colunas de dados.

Coluna auxiliar

À direita dos dados, configurei a coluna auxiliar L.

A célula L1 contém o COUNTIF das linhas nessa coluna que têm uma soma maior que zero.

Para as somas de linha, em vez de uma simples soma das colunas em cada linha (novamente, apenas para a coluna A), eu uso uma soma do intervalo retornado pela função OFFSET . Esta função tem a forma

OFFSET(reference cell, number of rows to offset, number of columns to offset, 
       height of range to return, width of range to return)

A célula L3 tem a primeira das expressões SUM(OFFSET(...)) . Calcula a soma da linha para o intervalo que é 0 linhas abaixo da célula A2 e 0 colunas para a direita, com uma altura de 1 linha e uma largura igual ao valor na célula L2. Nesse caso, L2 tem o valor de 1.

Esta fórmula é copiada para baixo por 360 linhas, em cada caso, calculando a soma de um intervalo com 1 linha de altura e com uma largura determinada pelo valor na célula L2.

Por exemplo, se o valor em L2 fosse alterado para 2, as fórmulas na coluna calculariam as somas em linha dos valores nas colunas A & B para cada uma das 360 linhas. E a célula L1 mostraria o número de linhas no intervalo A2: B361 com uma soma maior que 0.

Tabeladedados

AfuncionalidadedatabeladedadosdoExcelpermitedeterminarrapidamenteoimpactoemumcálculodevariaçãodovalordeuma(ouduas)dasentradasparaessecálculo.EleéconfiguradopormeiodobotãoWhat-IfAnalysisnaseçãoDataToolsdaguiaDatanafaixadeopções.

Afiguraemanexomostraaconfiguraçãodatabeladedados.

AtabeladedadosserácriadanointervaloR1:S10.Notopodatabela,nacélulaS1éacéluladeresultadoparaaqualasentradasserãovariadas.Nessecaso,acélularesultantecontémafórmula=L1,queéapenasumareferênciaàfórmulaCOUNTIFnotopodacolunaauxiliarL.

Eupré-inseriosvalores"what-if" nas células R2: R10. Os valores mostrados - 1, 2, ..., 9 - representam as larguras dos intervalos que o OFFSET retornará. E a "célula de entrada da coluna" é a célula L1 , a célula que determina a largura das linhas que são somadas na coluna auxiliar.

Em suma, nós nos alimentamos nas larguras 1-9 (equivalente às colunas "A", "A: B", "A: C", etc.) e a tabela de dados calcula o número de linhas que possuem soma maior que 0 para cada um desses períodos de coluna.

Aúltimafotomostraosresultadosfinais.Atabeladedadoscalculouascontagensdelinhaparacadacolunadosdadosdeentrada,ouseja,ascontagensdassomasemlinha(dascolunasanteriores)quesãomaioresque0.EssascontagensforamretornadasnascélulasS2:S10dosdadosmesa.EutransferiascontagensparaaprimeiralinhadosdadosoriginaisusandoafunçãoTRANSPOSE.

A planilha de exemplo com todos os cálculos está disponível aqui .

    

Answer 2

A função

OFFSET permite separar linhas individuais dentro de um intervalo ... e você pode somar cada linha com SUBTOTAL e contar as linhas > 0 com SUMPRODUCT , portanto, essa fórmula em B1 copiada deve fazer o trabalho sem células auxiliares

=SUMPRODUCT((SUBTOTAL(9,OFFSET($A2:A2,ROW(A2:A361)-ROW(A2),0))>0)+0)

Isso usa uma técnica semelhante à descrita aqui [não há filtragem aqui mas SUBTOTAL ainda precisa ser usado para somar cada faixa gerada pelo OFFSET]

Isso lhe dará os mesmos resultados que a solução de chuff

    

Answer 3

Se eu entendi corretamente o que você está perguntando, você quer que a linha superior mostre, para cada coluna, o número total de células individuais com um valor > 0 em todas as colunas anteriores. Certo?

Nesse caso, é muito simples usar CountIf e usar o sinal $ para bloquear a referência.

Na célula B1, coloque em =CountIf($A2:A361,">0") . Clique e arraste para a direita. O sinal $ bloqueia o A para que esteja sempre contando tudo entre a coluna A e a coluna atual. A fórmula terá esta aparência quando você a arrastar:

• C1 =Countif($A2:B361,">0")
• D1: =Countif($A2:C361,">0")
• E1: =Countif($A2:D361,">0")
• etc ...

CountIf pode contar em um intervalo inteiro, você não precisa selecionar apenas uma única célula ou fórmula por vez. Portanto, ao usá-lo dessa maneira, você pode contar facilmente todas as células à esquerda da coluna atual.