Dadas múltiplas datas de início, como atribuir contagens a valores de data relativos?

Question

Estou tentando criar um gráfico de antes e depois no Excel, alinhando várias datas de marco no mês zero e atribuindo números de meses relativos a meses anteriores e posteriores ao mês de marco.

Minha planilha tem cerca de 5.000 linhas e se parece com isso:

+------------+-------------+---------+---------+---------+---------+---------+---------+
|  UserID    |MilestoneDate| 2014-01 | 2014-02 | 2014-03 | 2014-04 | 2014-05 | 2014-06 |
+------------+-------------+---------+---------+---------+---------+---------+---------+
|    7       | 2014-01-02  | 232     | 22      | 19      | 77      | 11      | 89      |
+------------+-------------+---------+---------+---------+---------+---------+---------+
|    89      | 2014-04-02  | 345     | 45      | 564     | 13      | 122     | 77      |
+------------+-------------+---------+---------+---------+---------+---------+---------+
|    678     | 2014-03-11  | 55      | 14      | 17      | 222     | 109     | 111     |
+------------+-------------+---------+---------+---------+---------+---------+---------+
|    897     | 2014-03-07  | 234     | 56      | 201     | 19      | 55      | 78      |
+------------+-------------+---------+---------+---------+---------+---------+---------+
|    789     | 2014-02-22  | 331     | 33      | 67      | 108     | 111     | 56      |
+------------+-------------+---------+---------+---------+---------+---------+---------+
|    989     | 2014-01-09  | 12      | 89      | 97      | 125     | 323     | 99      |
+------------+-------------+---------+---------+---------+---------+---------+---------+

MilestoneDate = o dia em que um usuário fez o download de um aplicativo, com colunas de mês representando as contagens mensais de visitas de usuários a um website.

Por fim, gostaria de gerar um gráfico de linhas com o mês relativo # no eixo X e todos os meses MilestoneDate configurados como zero. As visitas anteriores ao MilestoneDate devem contar com os respectivos meses negativos (por exemplo, a atividade de março do usuário 89 deve ocorrer no mês '-1' e sua atividade de fevereiro no mês '-2').

Estou com uma perda de como eu poderia fazer isso no Excel (programaticamente criar uma nova tabela com valores de deslocamento - como?). Eu nem sei como é chamado um gráfico como esse, o que tornou o Googling por ajuda não muito fácil.

Alguma ajuda?

    

Answer 1

Veja se isso se encaixa no projeto.

Uma tabela precisa ser criada para a origem do gráfico. Nesta tabela, os usuários são listados na mesma ordem que na fonte de dados original. Cada StartDate do usuário é inspecionado e um Offset é calculado em A16 e abaixo usando

=MONTH(B5)-MONTH(MonthZero)

Os rótulos na linha 15 de -5 a 6 são inseridos manualmente. Uma fórmula preenche a tabela que começa em C16, copiada para baixo e entre

=IF(IFERROR(OFFSET($C5:$H5,0,$A16+C$15,1,1),NA())=$A5,NA(),IF(IFERROR(OFFSET($C5:$H5,0,$A16+C$15,1,1),NA())=$B5,NA(),IFERROR(OFFSET($C5:$H5,0,$A16+C$15,1,1),NA())))

A fórmula pega os dados originais do usuário e os posiciona em relação ao mês de junção usando o deslocamento calculado. Dois IFs aninhados são usados para evitar que os nomes de usuário e sua data de associação apareçam na tabela, e o IFError garante que apenas os deslocamentos válidos sejam retornados.

Em seguida, crie um gráfico de linhas com as linhas 14 e 15 como o eixo X e o ID do usuário como a série.

Eucompartilheioarquivo usado para criar a captura de tela no meu OneDrive.

Observação: Esse tipo de visualização de dados não deve ser tentado com a série 5000 para 5000 usuários. Você gostaria de agregar os dados de alguma forma.

Edit: Aqui está outra versão com uma fórmula diferente. Eu iniciei outra planilha e organizei os dados originais nas colunas A a H e a fonte do gráfico lado a lado nas colunas J e seguintes.

A função Offset é volátil e causará lentidão. Esta função de índice agora na célula L5 e copiada para cima e para baixo é muito mais curta, mais rápida e fornece o mesmo resultado.

=IFERROR(IF($J5+L$4>=0,INDEX($C5:$H5,($J5+L$4)+1),NA()),NA())

Você pode encontrar isso na Folha 2 no arquivo do OneDrive.