Eu tenho uma matriz de 150 colunas x 360 linhas com números aleatórios (digamos A2 a ET361) no Excel.
Como calculo para cada coluna (ou seja, da célula B1 a ET1) quantas linhas são maiores que zero para as colunas anteriores?
Critério:
B1 precisa calcular o número de células (A2 a A361) que são >0.
C1 precisa calcular o número de linhas (A2:B2, A3:B3, ..., até A361:B361) onde a soma de cada linha é >0.
D1 precisa calcular o número de linhas (A2:C3, ..., a A361:C361) onde a soma de cada linha é >0.
Tentei usar a fórmula CONT.SE, mas ela retorna apenas o número de células, não o número de linhas.
Acho que preciso de uma fórmula ROWS() e IF() aninhada? Também não quero criar outra matriz 150 x 360 para resolver esse problema, pois quero economizar espaço no meu arquivo Excel.
Também não quero usar macros e VBA porque complicam minha planilha.
Eu adicionei uma complexidade a toda a equação, em que a função subtotal não funciona.
Preciso que cada célula da matriz calcule o número de linhas acima dela para as quais a soma das colunas de cada linha é maior que zero. A solução de Barry não funcionará neste caso (eu testei), pois a fórmula 'Subtotal' não funciona para células que possuem a fórmula 'subtotal'.
Temos outras alternativas?
Responder1
Embora eu não tenha conseguido pensar em uma única solução de fórmula (talvez alguém o faça!), descobri algo que ocupa muito menos espaço na planilha do que outra matriz de 150 x 360.
A ideia básica é calcular os totais cumulativos em cada linha para uma coluna de dados e depois usar isso em uma tabela de dados ("análise hipotética") para gerar as contagens para todas as colunas.
O ponto de partida é a coluna de cálculos para as linhas em uma única coluna de dados.
Conforme mostrado na imagem abaixo, configurei uma planilha com 10 colunas de dados.
Coluna Auxiliar
À direita dos dados, configurei a coluna auxiliar L.
A célula L1 contém as COUNTIFlinhas dessa coluna que possuem uma soma maior que zero.
Para as somas das linhas, em vez de uma simples soma das colunas em cada linha (novamente, apenas para a coluna A), uso uma soma do intervalo retornado pela OFFSETfunção. 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 possui a primeira das SUM(OFFSET(...))expressões. Ele calcula a soma das linhas para o intervalo que está 0 linhas abaixo da célula A2 e 0 colunas à direita, com altura de 1 linha e largura igual ao valor na célula L2. Neste caso, L2 tem o valor 1.
Esta fórmula é copiada em 360 linhas, em cada caso calculando a soma de um intervalo de 1 linha de altura e com 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 por linha dos valores nas colunas A e B para cada uma das 360 linhas. E a célula L1 mostraria o número de linhas no intervalo A2:B361 com soma maior que 0.
Tabela de dados
A funcionalidade da tabela de dados do Excel torna possível determinar rapidamente o impacto da variação do valor de uma (ou duas) das entradas desse cálculo em um cálculo. Ele é configurado por meio do What-If Analysisbotão na Data Toolsseção da Dataguia da faixa de opções.
A imagem a seguir mostra a configuração da tabela de dados.
A tabela de dados será criada no intervalo R1:S10. No topo da tabela, na célula S1 está a célula de resultado para a qual os insumos serão variados. Nesse caso, a célula de resultado contém a fórmula =L1, que é apenas uma referência à COUNTIFfórmula no topo da coluna auxiliar L.
Eu pré-inseri os valores "e se" 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.
Resumindo, alimentamos as larguras de 1 a 9 (equivalente às colunas "A", "A:B", "A:C", etc.) e a tabela de dados calcula o número de linhas que possuem somas maiores que 0 para cada um desses vãos de coluna.
A última foto mostra os resultados finais. A tabela de dados calculou as contagens de linhas para cada coluna dos dados de entrada, ou seja, as contagens das somas de linhas (das colunas anteriores) que são maiores que 0. Essas contagens foram retornadas nas células S2:S10 dos dados mesa. Transferi as contagens para a primeira linha dos dados originais usando a TRANSPOSEfunção.
A planilha de exemplo com todos os cálculos está disponívelaqui.
Responder2
Se bem entendi o que você está perguntando, você deseja que a linha superior mostre, para cada coluna, o número total decélulas individuaiscom um valor > 0 em todas as colunas anteriores. Certo?
Nesse caso, é muito simples usar CountIfe usar o $sinal para bloquear a referência.
Na célula B1, coloque =CountIf($A2:A361,">0"). Clique e arraste para a direita. O $sinal bloqueia Apara que sempre conte tudo entre a coluna A e a coluna atual. A fórmula ficará assim quando você a arrastar:
- C1
=Countif($A2:B361,">0") - D1:
=Countif($A2:C361,">0") - E1:
=Countif($A2:D361,">0") - etc...
CountIfpode contar em um intervalo inteiro, você não precisa apenas selecionar uma única célula ou fórmula de cada vez. Portanto, ao usá-lo dessa forma, você pode contar facilmente cada célula à esquerda da coluna atual.
Responder3
OFFSETA função permite separar linhas individuais dentro de um intervalo... e então você pode somar cada linha SUBTOTALe contar as linhas> 0 com SUMPRODUCT, portanto, esta 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 à descritaaqui[não há filtragem aqui, mas SUBTOTAL ainda precisa ser usado para somar cada intervalo gerado por OFFSET]
Isso lhe dará os mesmos resultados que a solução do chuff