Tenho dados no Excel parecidos com estes:
Shop | Division | Launch Year
-------+-------------+-------------
Shop 1 | Division 1 | 2005
Shop 2 | Division 1 | 2006
Shop A | Division 2 | 2005
Shop B | Division 2 | 2007
...
E assim por diante. Existem cerca de 100 lojas no total em 4 divisões. Preciso pegar uma tabela mostrando quantas lojas estão abertas no final de cada ano, por divisão. Com uma tabela dinâmica, posso mostrar facilmente quantas novas lojas foram abertas naquele ano, mas não quantas no total.
Eu sei como adicionar totais acumulados a uma tabela dinâmica, mas no meu caso isso não funciona, pois não tenho números para adicionar - preciso adicionar contagens contínuas. Como fazer isso?
Estou procurando este resultado final:
Year | Division 1 | Division 2 | ...
-----+------------+------------+----
2005 | 1 | 1 |
2006 | 2 | 1 |
2007 | 2 | 2 |
...
Responder1
Uma contagem contínua é um total contínuo de 1s.
Se os dados permitirem, basta lançar uma coluna adicional cheia de 1s e usá-las em seu pivô para obter um total acumulado.
Responder2
Se sua tabela for relativamente estática (colunas e linhas não mudam muito), use os cabeçalhos de coluna e de linha. No corpo, use fórmulas como (em pseudocódigo):
=countifs('Launch Year',"<="&'row header', 'Division', 'column header')
Se você usar a ancoragem adequada, poderá construir a fórmula uma vez e copiá-la e colá-la em todo o corpo.
Responder3
Você não precisa de uma tabela dinâmica para isso.
Crie sua tabela conforme mostrado na saída desejada. Preencha a área de dados com a seguinte fórmula de matriz, inserida Ctrl Shift Entere copiada para cima e para baixo:
=SUMPRODUCT(--IF($C$2:$C$5<=$E2,1,0),IF($B$2:$B$5=F$1,1,0))
Atualize as referências de células conforme necessário, prestando atenção à ancoragem.
Função somaprodutodocumentaçãodo MS.
Você está usando IFas instruções dois para gerar duas matrizes de valores correspondentes de tamanho idêntico (as --voltas TRUEto 1e FALSEto 0), multiplicando-as entre si para encontrar registros que correspondam a ambos os critérios e, em seguida, somando a lista resultante de 1's e 0' s para obter seu total.
EDITAR: Para responder ao seu comentário/pergunta
A configuração mostrada aqui até agora é uma pequena tabela de dados e resultados para prova de conceito. Para tornar isso mais extensível, você provavelmente transformará a tabela de entrada em uma tabela nomeada. Para fazer isso, selecione qualquer célula do intervalo relevante (exemplo: B4na configuração mostrada) e pressione Ctrl+ Te confirme na janela modal resultante. Isso tornará mais fácil escrever referências de células e atualizar automaticamente quaisquer referências de células a esses intervalos de dados à medida que os dados são adicionados.
Você também pode transformar a tabela de resultados em uma tabela, usando o mesmo método. Desta forma, ao adicionar um novo ano, basta inserir o ano na próxima linha abaixo da tabela e todas as fórmulas serão coladas automaticamente:
Observe que esta atualização automática da tabela de saída não funcionará para adicionar uma nova Divisão. A tabela será expandida para acomodá-la, mas você terá que copiar manualmente a SUMPRODUCTfórmula para o novo campo.
Boa sorte e divirta-se!