Agregar dados do Excel

Question

Tenho valores de ID duplicados, mas preciso preservar os dados em cada linha, e não apenas removê-los.

Aqui está o que tenho atualmente:

 
ID      course   grade
1       geo        90
2       math       80
3       geo        75
4       math       90
4       sci        90
4       geo        50
5       sci        85

Isso é o que eu quero:

ID     geo     math     sci     gradegeo     grademath     gradesci
1     1        0        0        90        
2     0        1        0                          80
3     1        0        0        75
4     0        1        1        50                90        90
5     0        0        1                                    85

Espero que alguém possa ajudar:)

ps Eu acho que isso deve ser feito em um banco de dados com várias tabelas e uma chave primária e tudo isso, mas, infelizmente, eu não tenho acesso a ele.

    

Answer 1

Use duas tabelas dinâmicas (para a primeira: Selecione seu intervalo, Inserir, Tabelas, Tabela Dinâmica, Tabela Dinâmica, OK, clique na caixa exibida, arraste ID para Rótulos de Linha, Role para Rótulos de Coluna e classifique em valores..)

Copie essa tabela dinâmica para a direita e, para a esquerda, assegure que Σ valores tenham Contagem de, para a mão direita, uma Soma de. Ocultar a linha superior e a coluna Rótulos da Linha para a tabela à direita. Renomeie os rótulos das colunas conforme necessário.

Para garantir 0 em vez de em branco na tabela à esquerda, clique com o botão direito do mouse sobre ele, Opções de tabela dinâmica, Layout & Formatar, verificar Para células vazias mostrar e inserir 0. O formato dos números de notas pode ser ajustado, se necessário, clicando com o botão esquerdo no conteúdo de valores, configurações de campo de valor (também onde alterar Soma para Contagem, etc.) e Formato numérico. (Se a formatação for ajustada diretamente na tabela dinâmica, ela será desfeita na próxima atualização).

Substitua os Rótulos de Linha pelo ID e esperamos que você obtenha o resultado que procura.

Observe que, se você adicionar dados ao seu intervalo de origem, as tabelas dinâmicas não serão alteradas até que você clique em uma delas e selecione Atualizar.

    

Answer 2

Use uma tabela dinâmica. Isso não é "transposição", por dizer, mas "agregação" - diferença de terminologia que deve ajudá-lo no googling:)

• Insira dados

• Selecione(destaque)todososdadosrelevantesecrieumatabeladinâmicacomInserir->TabelaDinâmicaem"Nova Planilha".

• Personalizeatabeladinâmicadeacordocomsuasnecessidades.Emseucasomuitoespecífico,arrasteesolteocampodocursoparaumrótulodecoluna,ocampoIDparaumrótulodelinhaearrasteovalordeclassificaçãoparaaáreaValoresduasvezes:umavez,deixe-ocomoestáparaobtera"soma" as notas (isso só resumirá um valor a menos que um único aluno faça o mesmo curso duas vezes no mesmo semestre) e, para o outro "Soma da nota", clique com o botão esquerdo na seta suspensa e observe as "Configurações do campo de valor" ". Altere de "Soma" para "Contagem".

  • Selecione toda a tabela dinâmica, pressione Ctrl + C para copiar para a área de transferência, vá para uma nova planilha e nas opções Colar (lista suspensa sob o ícone da área de transferência na guia Início da Faixa de Opções), faça " Colar valores com formatação de fonte ".

  • Reorganize as colunas e ajuste a formatação até obter exatamente como deseja. Aqui está o que eu fiz para fazer com que parecesse a saída desejada; demorei cerca de 30 segundos depois de gerar a tabela dinâmica: