Tenho um conjunto de dados que atualmente tem 39.000 linhas e 27 colunas. A primeira coluna é o número de identificação. As colunas subsequentes correspondem a eleições, com a data da eleição na linha 1 (células B1- AA1). As restantes células são preenchidas com uma letra que corresponde ao método de votação (ou nula se não votou). Preciso reorganizar esta tabela para que haja um total de três colunas: ID, Data e Método de Votação. Por exemplo:
Tabela atual:
ID 05/2005 11/2005 03/2006 (etc., for 27 total columns)
2345 P V
3789 A V
4321 V A V
7890 I
E eu preciso que fique assim:
ID Date Voting Method
2345 05/2005 P
2345 11/2005 V
3789 11/2005 A
3789 03/2006 V
4321 05/2005 V
4321 11/2005 A
4321 03/2006 V
7890 11/2005 I
Acho que isso vai exigir um script VBA, e tentei juntar pedaços de script que encontrei on-line (porque nunca aprendi VBA), mas não consigo fazê-lo funcionar direito. Talvez esta função já exista no Excel?
Aqui está o script com o qual estou trabalhando até agora:
Sub NewLayout()
For i = 2 To Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
For j = 0 To 26
If Cells(i, 3 + j) <> vbNullString Then
intCount = intCount + 1
Cells(i, 1).Copy Destination:=Cells(intCount, 10)
Cells(i, 2).Copy Destination:=Cells(intCount, 11)
[I think this one is wrong. It needs to copy the column name,
not the cell value, if there is a cell value.]
Cells(i, 3 + j).Copy Destination:=Cells(intCount, 12)
End If
Next j
Next i
End Sub
Se alguém tiver alguma sugestão, eu agradeço!
Responder1
Com o VBA, assumindo que os dados estão na Planilha1 começando em A1 e que a Planilha2 existe:
Sub normalize()
Dim wks1 As Worksheet, wks2 As Worksheet
Dim iColCount As Integer, iRowCount As Integer
Dim i As Integer, j As Integer, k As Integer
Set wks1 = ActiveWorkbook.Sheets("Sheet1")
Set wks2 = ActiveWorkbook.Sheets("Sheet2")
iColCount = Application.WorksheetFunction.CountA(wks1.Range("1:1"))
iRowCount = Application.WorksheetFunction.CountA(wks1.Range("A:A"))
k = 1
For i = 2 To iRowCount
For j = 2 To iColCount
If wks1.Cells(i, j) <> vbNullString Then
wks1.Cells(i, 1).Copy Destination:=wks2.Cells(k, 1)
wks1.Cells(1, j).Copy Destination:=wks2.Cells(k, 2)
wks1.Cells(i, j).Copy Destination:=wks2.Cells(k, 3)
k = k + 1
End If
Next j
Next i
End Sub
Resultados na Planilha2:
2345 5/2005 P
2345 11/2005 V
3789 11/2005 A
3789 3/2006 V
4321 5/2005 V
4321 11/2005 A
4321 3/2006 V
7890 11/2005 I
Responder2
Para completar, agora mostro como faço issosemrecorrendo ao VBA. Devo avisar que o código a seguir é complicado e difícil de escalar de forma eficaz.
Vamos supor a seguinte condição inicial:
Folha1
| A | B | C | D | …
---+----+---------+---------+---------+---
1 | ID | 05/2005 | 11/2005 | 03/2006 |
2 |2345| P | V | |
3 |3789| | A | V | …
4 |4321| V | A | V |
5 |7890| | I | |
… | …
Planilha2
| A | B | C |
---+----+------+---------------+
1 | ID | Date | Voting method |
2 | #1 | #2 | #3 |
A célula chamada#1tem a seguinte fórmula:
=INDIRECT(ADDRESS(FLOOR((ROW(A2)-2)/27 + 2,0),1,,,"Sheet1"))
O que esta fórmula faz é mapear a célula atual para as células adequadas na Planilha1. Isso é feito com a ajuda da FLOORfunção. A função aumentará em 1 sempre que 27 linhas forem passadas, mapeando corretamente as linhas da Planilha2 com o conteúdo da Planilha1.
A ADDRESSfunção constrói uma referência utilizável para uma célula a partir de entradas numéricas e nomes de planilhas, enquanto a INDIRECTfunção recupera o conteúdo apontado pela referência.
O restante das outras funções segue um raciocínio idêntico: você usa uma função auxiliar para mapear as coordenadas da célula atual para a célula correta na Planilha1.
Para a célula chamada#2:
=INDIRECT(ADDRESS(1,MOD(ROW(A2)-2,27)+2,,,"Sheet1"))
Neste caso, a MODfunção alterna sequencialmente entre 0 e 26, que depois é convertida para uma sequência entre 2 e 28 (ou seja, onde estão as células com as datas).
Finalmente, para a célula chamada#3:
=INDIRECT(ADDRESS(FLOOR((ROW(A2)-2)/27 + 2,0),MOD(ROW(A2)-2,27)+2,,,"Sheet1"))
É uma mistura das duas sequências usadas anteriormente. A razão é porque o conteúdo varia em relação ao ID (como tal, a parte da célula#1é chamada) e Data (que é onde a parte da célula#2entra).
Depois de inserir essas funções nas células corretas, basta arrastar para baixo e os resultados deverão ficar aparentes, com um pequeno problema: os votos nulos também estão aparecendo.
Você pode filtrar esses resultados. Selecione o cabeçalho (neste caso, a Alinha da Planilha2) e vá em Dados > Filtro > Filtro Automático (ou equivalente na versão Excel que você está usando). Clique no menu suspenso na coluna do método de votação e personalize a classificação para excluir resultados que consistem em zero.
Responder3
Eu usaria o suplemento Power Query para isso. Não requer nenhum código ou funções complexas. Começando do zero, provavelmente levará menos de 5 minutos para concluir esta tarefa.
Você pode iniciar uma consulta a partir de uma tabela Excel existente. Eu usaria então o comando Unpivot para transformar os dados conforme necessário.
http://office.microsoft.com/en-au/excel-help/unpivot-columns-HA104053356.aspx
A beleza da implementação do Unpivot é que ele aceitará quaisquer outras colunas adicionadas (novas datas) e as processará sem qualquer alteração na definição da sua consulta.
Eu renomearia as colunas conforme necessário e entregaria o resultado em uma tabela do Excel.