Converta séries de tempo em colunas para séries de tempo em linhas no Excel

Question

Estou tentando preparar uma planilha do excel na qual movo dados de séries de tempo que estão em linhas em colunas quando cada categoria (países nesse caso) tem um número diferente de pontos de dados. Desculpas de antemão se não estou descrevendo isso perfeitamente.

Vou deixar isso claro com um exemplo. Este é um mock-up do que eu tenho atualmente:

Country         Year     Data
Afghanistan     2000     10  
Afghanistan     2004     12
Afghanistan     2006     15
Afghanistan     2010     30 
Albania         2001     40
Albania         2002     50
Albania         2005     55
Albania         2006     70
Albania         2008     60
Albania         2009     60
Algeria         2000     23
Algeria         2005     30
Algeria         2006     40
Algeria         2007     41
Algeria         2008     39
Algeria         2009     35
Algeria         2010     40  
...

O conjunto de dados é grande e é executado por um longo período de tempo.

O que eu quero que o conjunto de dados seja assim é:

Country       2000    2001    2002    2003    2004    2005    2006   2007     2008      2009    2010  
Afghanistan:  10                              12              15                                30  
Albania:              40       50                     55      70              60        60  
Algeria:      23                                      30      40     41       39        35      40 
...

Eu posso gerar a estrutura que eu quero com bastante facilidade:

Country      2000    2001     2002     2003    2004   2005    2006   2007     2008     2009    2010  
Afghanistan  
Albania    
Algeria      
...

Mas não consigo descobrir como preencher a tabela. Acho que preciso fazer um hlookup ou vlookup, movendo-se de colunas para linhas, e ele precisa lidar com vários critérios para correspondência - 'country' + 'year' ( Eu tentei concatriar, mas isso fez muitos pontos de dados: Afeganistão2000, Afeganistão, 2001, etc etc.).

Além disso, a tabela dinâmica não parece funcionar porque soma os dados e copia "if (e (" instruções não funcionam porque suas colunas são linhas.

Eu tenho estado em toda a internet e não posso trabalhar a ferramenta para isso. No entanto, tenho certeza de que existe uma solução simples, mas não consigo resolver isso.

Se alguém puder ajudar, será muito apreciado, pois recebo muitos dados de séries temporais neste formato.

    

Answer 1

Obrigado a todos pelas sugestões, mas na verdade consegui acabar resolvendo isso usando uma tabela dinâmica. Tudo o que eu tive que fazer foi criar uma tabela dinâmica. Defina as colunas como 'ano', defina as linhas como 'país' e defina os valores como 'dados'.

Depois, tive que definir a 'configuração de valor do campo' para dados como 'max' e a tabela foi preenchida. Demorou cerca de 30 segundos no final ...

Você pode acessar a "Configuração do valor do campo" no menu suspenso no campo "dados" na caixa de valores, dentro da caixa de diálogo Campos da tabela dinâmica.

Obrigado mais uma vez pelas respostas que irei definitivamente tentar o jogo, fórmulas de índice em algum momento.

    

Answer 2

se os dados forem classificados, você poderá ir com INDEX:INDEX e MATCH , como para B2 (Sheet1 para a lista e Sheet2 para formula asumed):
primeiro defina o intervalo para analisar via MATCH

MATCH(Sheet2!$A2,Sheet1!$A:$A,0)
MATCH(Sheet2!$A3,Sheet1!$A:$A,0)-1

Dessa forma, você obtém a primeira e a última linha do país. Agora defina o intervalo para olhar.

INDEX(Sheet1!$B:$B,MATCH(Sheet2!$A2,Sheet1!$A:$A,0)):INDEX(Sheet1!$B:$B,MATCH(Sheet2!$A3,Sheet1!$A:$A,0)-1)

A saída terá a mesma aparência apenas para $C:$C .
Para encontrar a linha certa, usamos

MATCH(Sheet2!B$1,[Search range formula],0)

Coloque isso na fórmula completa

INDEX([output range],[right row])

Como isso vai erro se nenhuma correspondência for encontrada também colocar uma parte do erro em torno dele

IFERROR([full formula],"")

todos juntos devem ficar assim

=IFERROR(INDEX(INDEX(Sheet1!$C:$C,MATCH(Sheet2!$A2,Sheet1!$A:$A,0)):INDEX(Sheet1!$C:$C,MATCH(Sheet2!$A3,Sheet1!$A:$A,0)-1),MATCH(Sheet2!B$1,INDEX(Sheet1!$B:$B,MATCH(Sheet2!$A2,Sheet1!$A:$A,0)):INDEX(Sheet1!$B:$B,MATCH(Sheet2!$A3,Sheet1!$A:$A,0)-1),0)),"")

Isso pode ser preenchido automaticamente para a esquerda e para baixo conforme necessário. Basta adicionar uma linha depois da última linha em ambas as listas, como "zzz", ou o último país não produzirá nada.

Feito por telefone e pode conter erros.

    

Answer 3

Você pode fazer isso sem fórmulas criando uma consulta usando Obtenha e Transforme , supondo que você esteja na versão mais recente do Office no Windows.

Veja como fazer isso para os dados que você forneceu:

1. Primeiro, coloque os dados originais em uma tabela (selecione qualquer célula no intervalo > ALT+N -> T ) e assegure-se de que a coluna do ano esteja classificada do menor para o maior
2. Em seguida, faça uma consulta na tabela ( ALT+O -> PT )
3. No Editor de Consultas, vá para a guia Transform e selecione Pivot Column
4. Para o Values Column select Data
5. Na guia Home , selecione Close and Load

Isso gera a seguinte tabela:

A melhor parte disso é quando a fonte original muda, você só precisa reexecutar a consulta com ALT+A -> R -> A e ela irá preencher novos dados.

    

Answer 4

Digite

=SUMPRODUCT((TRIM($A$2:$A$18)=TRIM($E3))*(TRIM($B$2:$B$18)=TRIM(F$2))*$C$2:$C$18)

em F3 e copie / arraste-o para preencher a tabela inteira ( F3:P5 no exemplo):

Esta fórmula coloca 0 , quando não há dados, para que você possa usar facilmente a função IF() se você precisar ter células vazias.