Como dividir uma linha no Excel em várias linhas para gerenciamento de banco de dados

Question

Eu tenho um conjunto de dados que atualmente possui 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 ). O resto das células são preenchidas com uma letra que corresponde ao método de votação (ou null, se não votou). Preciso reorganizar essa 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 pareça 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

Eu acho que isso vai exigir script VBA, e eu tentei juntar pedaços de script que eu encontrei online (porque eu nunca aprendi VBA), mas eu não consigo fazer isso funcionar certo. 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, agradeço!

    

Answer 1

Com o VBA, supondo que os dados estejam na Folha1 começando em A1 e que a Folha2 exista:

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 Folha2:

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

    

Answer 2

Para completar, agora mostro como faço isso sem recorrendo ao VBA. Devo avisar que o código a seguir é complicado e é difícil escalonar com eficiência.

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    |         |
 … |                 …

Folha2

   |  A |   B  |       C       |
---+----+------+---------------+
 1 | ID | Date | Voting method |
 2 | #1 |  #2  |       #3      |

A célula denominada # 1 tem a seguinte fórmula:

=INDIRECT(ADDRESS(FLOOR((ROW(A2)-2)/27 + 2,0),1,,,"Sheet1"))

O que essa fórmula faz é mapear a célula atual para as células adequadas na Planilha1. Isso é feito com a ajuda da função FLOOR . A função aumentará em 1 sempre que 27 linhas forem passadas, mapeando corretamente as linhas na Planilha2 com o conteúdo na Planilha1.

A função ADDRESS constrói uma referência utilizável para uma célula a partir de nomes de entrada e planilha numéricos, enquanto a função INDIRECT 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"))

Nesse caso, a função MOD alterna sequencialmente entre 0 e 26, que é então convertida em uma sequência entre 2 e 28 (em outras palavras, onde as células com as datas são).

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 seqüências usadas antes. A razão é porque o conteúdo varia com relação ao ID (como tal, a parte da célula # 1 é chamada) e Data (que é onde a parte da célula # 2 entra).

Depois de inserir essas funções nas células corretas, basta arrastar para baixo e os resultados devem 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 linha A na Folha2) e vá para Dados > Filtrar > Filtro Automático (ou o equivalente na versão do 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.

    

Answer 3

Eu usaria o suplemento de consulta de energia para isso. Não requer nenhum código ou funções complexas. Começando do zero, provavelmente levará menos de 5 minutos para concluir essa tarefa.

Você pode iniciar uma consulta a partir de uma tabela existente do Excel. Em seguida, usaria o comando Unpivot para transformar os dados conforme necessário.

link

A beleza de sua implementação do Unpivot é que ele aceitará quaisquer outras colunas adicionadas (novas datas) e as processará sem qualquer alteração na sua definição de Consulta.

Eu renomeio as colunas conforme necessário e envio o resultado para uma tabela do Excel.