Tengo un conjunto de datos que actualmente tiene 39.000 filas y 27 columnas. La primera columna es el número de identificación. Las columnas siguientes corresponden a elecciones, con la fecha de la elección en la fila 1 (celdas B1- AA1). El resto de las celdas se llenan con una letra que corresponde al método de votación (o nula si no votó). Necesito reorganizar esta tabla para que haya un total de tres columnas: ID, fecha y método de votación. Por ejemplo:
Tabla actual:
ID 05/2005 11/2005 03/2006 (etc., for 27 total columns)
2345 P V
3789 A V
4321 V A V
7890 I
Y necesito que se vea así:
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
Creo que esto requerirá un script VBA, y he intentado juntar fragmentos de script que encontré en línea (porque nunca aprendí VBA), pero parece que no puedo hacerlo funcionar del todo bien. ¿Quizás esta función ya existe en Excel?
Aquí está el script con el que estoy trabajando hasta ahora:
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
Si alguien tiene alguna sugerencia, ¡se la agradecería!
Respuesta1
Con VBA, asumiendo que los datos están en la Hoja1 comenzando en A1 y que la Hoja2 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 en la Hoja 2:
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
Respuesta2
Para completar, ahora muestro cómo hago esto.sinRecurrir a VBA. Debo advertir que el siguiente código es complicado y es difícil escalarlo de manera efectiva.
Supongamos la siguiente condición inicial:
Hoja1
| 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 | |
… | …
Hoja2
| A | B | C |
---+----+------+---------------+
1 | ID | Date | Voting method |
2 | #1 | #2 | #3 |
La celda nombrada#1tiene la siguiente fórmula:
=INDIRECT(ADDRESS(FLOOR((ROW(A2)-2)/27 + 2,0),1,,,"Sheet1"))
Lo que hace esta fórmula es asignar la celda actual a las celdas adecuadas en la Hoja1. Esto se hace con la ayuda de la FLOORfunción. La función aumentará en 1 cada vez que se hayan pasado 27 filas, asignando correctamente las filas de la Hoja2 con el contenido de la Hoja1.
La ADDRESSfunción construye una referencia utilizable a una celda a partir de entradas numéricas y nombres de hojas, mientras que la INDIRECTfunción recupera el contenido al que apunta la referencia.
El resto de las otras funciones siguen un razonamiento idéntico: utiliza una función auxiliar para asignar las coordenadas de la celda actual a la celda correcta en la Hoja1.
Para la celda nombrada#2:
=INDIRECT(ADDRESS(1,MOD(ROW(A2)-2,27)+2,,,"Sheet1"))
En este caso, la MODfunción alterna secuencialmente entre 0 y 26, que luego se convierte a una secuencia entre 2 y 28 (es decir, donde están las celdas con las fechas).
Finalmente, para la celda denominada#3:
=INDIRECT(ADDRESS(FLOOR((ROW(A2)-2)/27 + 2,0),MOD(ROW(A2)-2,27)+2,,,"Sheet1"))
Es una mezcla de las dos secuencias utilizadas antes. La razón es porque el contenido varía con respecto a la identificación (como tal, la parte de la celda#1se llama) y Fecha (que es donde se llama la parte de la celda#2entra).
Después de ingresar esas funciones en las celdas correctas, simplemente arrastre hacia abajo y los resultados deberían ser evidentes, con un pequeño problema: los votos nulos también están apareciendo.
Sin embargo, puedes filtrar esos resultados. Seleccione el encabezado (en este caso, la Afila de la Hoja 2) y vaya a Datos > Filtro > Filtro automático (o su equivalente en la versión de Excel que esté utilizando). Haga clic en el menú desplegable de la columna del método de votación y personalice la clasificación para excluir los resultados que constan de cero.
Respuesta3
Usaría el complemento Power Query para esto. No requiere ningún código ni funciones complejas. Comenzando desde cero probablemente le llevará menos de 5 minutos completar esta tarea.
Puede iniciar una consulta desde una tabla de Excel existente. Luego usaría el comando Unpivot para transformar los datos según sea necesario.
http://office.microsoft.com/en-au/excel-help/unpivot-columns-HA104053356.aspx
Lo bueno de su implementación de Unpivot es que aceptará más columnas agregadas (nuevas fechas) y las procesará sin ningún cambio en la definición de su consulta.
Cambiaría el nombre de las columnas según sea necesario y entregaría el resultado en una tabla de Excel.