organizar datos para que Excel lo entienda

La manera más cómoda para que una persona introduzca los datos no siempre es la mejor manera para que Excel lo entienda y pueda tratar la información.

Las personas ven muy claro una tabla de doble entrada o un cuadrante, sin embargo Excel (y casi todos los programas informáticos) entienden un dato como un registro que debe de estar en una fila. Si no tenemos los datos ordenados de esta manera no podremos utilizar muchas de las capacidades de Excel y nos obligaremos a perder mucho tiempo trabajando a mano.

Queremos convertir esto en esto otro
lo que entienden las personas lo que entiende Excel

 

En este tema voy a explicar una macro que hace esta transformación para que Excel pueda entender bien nuestros datos, ahorrándonos horas y horas de artesanía.

Lo que las personas entendemos

Seguro que estos ejemplos te suenan:

Una hoja de imputación de horas con el nombre del proyecto en la primera fila y las fechas en la primera columna, donde los datos son horas trabajadas

Variaciones con nombres de personas, lugares de trabajo, periodos de tiempo, tareas o actividades, etc.

Un calendario con fechas en la primera fila y horas en la primera columna, donde los datos son citas.

Una relación de clientes en la columna, con meses en la primera fila, siendo los datos los importes facturados a cada uno de ellos.

Lo que un humano entiende tiene una estructura parecida a esta, con una primera fila (en verde) y una primera columna (en azul) para los rótulos de la información, que está contenida en el área de la tabla.

estructura de lo que entendemos

Esta disposición nos permite anotar de forma compacta dos dimensiones de información, en el ejemplo de la imagen, un un área de 7filas x 4 columnas, 28 celdas utilizadas, tenemos 6x3 celdas con datos, 18 datos útiles.

Lo que Excel entiende

Excel entiende estos datos mejor si cada dato está en una fila.
La misma información de antes sería más comprensible para una máquina de esta otra manera:

cómo lo entiende mejor Excel

Necesitaríamos una primera columna donde almacenar la información del rótulo de la parte izquierda,
una segunda celda que identifique el rótulo que antes estaba en la parte superior,
y por último una celda donde almacenar el dato.

Para la misma información que antes, 18 datos, ahora necesitamos 18 filas, 18 registros, más una fila adicional para los rótulos,
así que ocupamos un área de 17x3 celdas, es decir, 51 celdas.

Para que Excel lo entienda bien, nuestros datos ocuparán más de espacio. Tiende hacia el triple, ya que en este caso necesitamos tres celdas para cada dato.

La macro que convierte una cosa en otra

¿cómo podemos convertir un cuadrante "humano" en un listado "exceliano"?

Lo primero que se me ocurre es realizar un bucle que recorra las filas, y para cada fila, realizar un bucle que recorra todas las columnas de esa fila.

Se puede hacer de muchas maneras pero, por ejemplo, podría ser algo así:

For fila = 2 To RangoDatos.Rows.Count
For Columna = 2 To RangoDatos.Columns.Count
' aquí el código para leer el dato y ponerlo en otro lado
Next
Next

los bucles empiezan en 2 porque la primera fila y la primera columna no tienen datos, sino rótulos.

he llamado RangoDatos al rango donde está el cuadrante con los datos originales.

Bueno, conociendo la fila y la columna, y empleando .Cells nos referimos una celda dentro del rango

RangoDatos.Cells(fila,columna).value

el nombre de la fila está en la primera columna, así que será

RangoDatos.Cells(fila,1).value

el nombre de la columna está en la primera fila, así que...

RangoDatos.Cells(1,columna).value

¿pero dónde ponemos los datos?

Se me ocurre insertar una nueva hoja en el libro, y allí insertar una tabla a la que ir añadiendo una fila para cada dato de la tabla.

por ejemplo, algo así:

Set hoja = ActiveWorkbook.Worksheets.Add
Set tabladestino = hoja.ListObjects.Add(xlSrcRange, Range("A1:C1"), , xlNo)

Como vamos a necesitar tres columnas para los datos, creo la tabla en las celdas de la A1 a la C1

Ahora sí, para pasar un dato, primero insertamos una fila en la tabla y luego copiamos los valores
en la columna 1 el rótulo de la fila,
en la columna 2 el rótulo de la columna
y en columna 3 el dato.

Añado también una comprobación, para sólo escribir un dato si la celda no está vacía,
algo así:

If RangoDatos.Cells(fila, Columna).Value <> "" Then
Set nuevafila = tabladestino.ListRows.Add.Range
nuevafila.Cells(1, 1).Value = .Cells(fila, 1).Value
nuevafila.Cells(1, 2).Value = .Cells(1, Columna).Value
nuevafila.Cells(1, 3).Value = .Cells(fila, Columna).Value
End If

Así que sí lo ponemos todo junto, empleando un With para el rango de datos y añadiendo alguna cosilla más, quedaría de esta manera:

Sub TransformarCuadranteEnListado()
Dim fila, Columna
Dim RangoDatos As Range, hoja As Worksheet, tabladestino As ListObject, nuevafila As Range
Set RangoDatos = Selection
Set hoja = ActiveWorkbook.Worksheets.Add
Set tabladestino = hoja.ListObjects.Add(xlSrcRange, Range("A1:C1"), , xlNo)
With RangoDatos
For fila = 2 To .Rows.Count
For Columna = 2 To .Columns.Count
If .Cells(fila, Columna).Value <> "" Then
Set nuevafila = tabladestino.ListRows.Add.Range
nuevafila.Cells(1, 1).Value = .Cells(fila, 1).Value
nuevafila.Cells(1, 2).Value = .Cells(1, Columna).Value
nuevafila.Cells(1, 3).Value = .Cells(fila, Columna).Value
End If
Next
Next
End With
Set RangoDatos = Nothing
Set tabladestino = Nothing
Set nuevafila = Nothing
Set hoja = Nothing
End Sub

¿Y para hacer el camino inverso?

Podríamos debatir mucho tiempo sobre este tema, pero mi consejo es que si tienes los datos, los resumas mediante una tabla dinámica.

Seamos sinceros, por buenas que sean nuestras macros no podrán llegar a superar nunca las capacidades de una tabla dinámica.

¿necesitas más?

Si quieres aprovechar Excel al máximo, hazte con tu ejemplar de
Ya sé Excel, pero necesito más

Temática: 

Comentarios

Re: organizar datos para que Excel lo entienda

Imagen de Cron

Y yo que me programé una macro anoche mismo para hacer lo mismo...

Me mandan una serie de datos tomados cada día a 3 horas diferentes, y la disposición era esa.

Cuando lo que quieres es hacer una gráfica para ver las tendencias (es decir, que lo entienda Excel) hay que ponerlo todo fila a fila.

Es curioso que estuviéramos trabajando exactamente en lo mismo ayer por la tarde, jaja.

Saludos

Re: organizar datos para que Excel lo entienda

Imagen de xoan ninguen

Ya sé que no es el objeto del post, pero últimamente he estado leyendo un poquito de optimización del código, y puesto que los bucles

lee en Cells(fila, columna) / escribe en Cells(fila, columna) son muy lentos, yo recomiendo a todo cristo que se vaya acostumbrando a declaraciones del tipo:

Cells(fila, columna).Value2 (o Value)

... se notaría en rapidez de algunas macros.

Un saludo, y excelente post

-----------------------------------------------------------------

www.TrazEX.com (soluciones sobre Excel)

más vale tarde que nunca

Imagen de pacomegia

He actualizado el envío original con los .Value

Inicialmente no quise tocar el envío original, pero así también verán la versión buena aquellos que no leen nunca los comentarios.

Gracias Xoan por tus acertadas observaciones.

 

Data Tools Suite
datos y tablas con Excel