Intervalo de datas da tabela dinâmica do Excel como colunas

Question

Eu tenho os seguintes dados que gostaria de pivotar:

Student    JoinDate    ReleaseDate
John       01/05/2011  05/05/2011
James      02/05/2011  04/05/2011

Gostaria de criar uma tabela dinâmica que mostre uma contagem de alunos dentro de um determinado período:

01/05/2011  02/05/2011  03/05/2011  04/05/2011  05/05/2011
1           2           2           2           1

Agora, o problema que tenho é que eu quero que a tabela dinâmica expanda as datas de alguma forma, não consigo descobrir como conseguir isso com a minha estrutura de dados atual.

Eu posso alcançá-lo com a seguinte estrutura:

Student    JoinDate    ReleaseDate   PivotDate
John       01/05/2011  05/05/2011    01/05/2011
John       01/05/2011  05/05/2011    02/05/2011
John       01/05/2011  05/05/2011    03/05/2011
John       01/05/2011  05/05/2011    04/05/2011
John       01/05/2011  05/05/2011    05/05/2011
James      02/05/2011  04/05/2011    02/05/2011
James      02/05/2011  04/05/2011    03/05/2011
etc...

Mas essa técnica não é dimensionada muito bem, devido à quantidade de dados em uso. Estou atingindo facilmente o número máximo de linhas de uma única planilha na pasta de trabalho.

Alguém tem alguma sugestão sobre como posso criar a tabela dinâmica desejada sem ter uma linha para cada data em que o aluno está 'ativo' ..?

    

Answer 1

Se você estiver disposto a tentar uma solução que não envolva tabelas dinâmicas:

Remova a coluna Data de pivotagem. Organize o intervalo de datas nos cabeçalhos e utilize a fórmula AND () para determinar se essa data se enquadra no mandato do aluno. Seus dados serão parecidos com isto:

VejacomoéafórmulaemD2(observeasreferênciasabsolutas).Bastaarrastaroucopiarafórmulaparaorestantedascolunaselinhas.Opreenchimentoautomáticodevecuidardorestante:

=AND(D$1>=$B2,D$1<=$C2)+0

Oformatonuméricoestádefinidoparaisto:"+";;

A fórmula para o número total de alunos por período é muito fácil. Aqui está a fórmula para D26 acima.

=sum(D2:D25)

Para adicionar colunas, basta copiar a última coluna e alterar a data acima. Para adicionar alunos, insira uma linha em branco acima do último aluno (neste caso, acima do Banshee ). Grave uma macro para facilitar a atualização dos dados. Dessa forma, você não precisa ajustar manualmente a fórmula SUM na linha inferior. Você pode classificar os dados por nome do aluno, data de associação ou data de lançamento; as fórmulas à direita devem estar bem.

Aqui está uma cópia da planilha mostrada acima (requer o MS Excel 2007): link

    

Answer 2

Você pode obter essas contagens com uma fórmula de matriz. Não há necessidade de Pivot.

Assumindo que JoinDate está em column B , ReleaseDate em column C e apenas as duas linhas de dados para este exemplo.
As datas a serem somadas estão em columns E em diante: Fórmula em E2 . Insira como uma fórmula de matriz (pressione Ctrl-Shift-Enter em vez de apenas Enter para concluir a fórmula)

=SUM(((($B$2:$B$4<=E$1)*1+($C$2:$C$4>=E$1)*1)=2)*1)

Copie na linha 2 para cada data que você quiser no total

A fórmula parece melhor e é mais fácil de manter se você usar Intervalos nomeados para os dados de data

=SUM((((JoinDate<=E$1)*1+(ReleaseDate>=E$1)*1)=2)*1)

Os intervalos de datas podem se estender além dos dados existentes para permitir a adição de mais segundos. Por exemplo, $B$1:$B$100
A coluna inteira ( $B:$B ) também funciona, mas diminuirá o recálculo

A propósito, se você estava usando o Excel 2007 ou este último uma nova função está disponível (digite como fórmula regular, não há necessidade de array):

=COUNTIFS(JoinDate,"<="&E$1,ReleaseDate,">="&E$1)

    

Answer 3

Pode não ser adequado ao seu aplicativo, mas você deve tentar a função Pivot no MS Access muito superior ao Excel