martes, 23 de abril de 2019

Reordenar columnas por filas

Hace algunas semanas publiqué un artículo donde explicaba una manera de desapilar columnas (ver aquí).
Era un ejemplo inicialmente pensado para datos distribuidos de manera homogénea o que respondiera a un patrón...
de hecho, a través de un comentario, un lector (Daniel Herce) proponía muy hábilmente una alternativa más simple con la función DESREF (gracias de nuevo por el aporte) ;-)

La ventaja, sin embargo, del método publicado frente a la elegante solución de Daniel, es que éste nos abre el abanico para soluciones no homogéneas o sin patrón.

Siguiendo aquel post daremos solución a un lector que preguntaba:
[...]tengo varios datos en la columna A y deseo pasarlos a una matriz que debe iniciar en fila nueva cada vez que encuentre un numero con guiones (ejemplo 1-1-1), anexo un ejemplo de lo que tengo y como debería quedar la matriz[...]

Reordenar columnas por filas



Nuestra solución formulada se verá así:

Reordenar columnas por filas



El primer paso consiste en insertar dos columnas B2:B22 y C2:C22 con cálculos auxiliares.
En B2 añadimos la siguiente fórmula que arrastraremos hasta B22:
=SI(ESERROR(ENCONTRAR("-";A2));B1+1;1)

Reordenar columnas por filas


El resultado es para cada tramo, diferenciado por el valor inicial (que incluye un guión), un secuencial 1,2,3,4,5,6,...

En C2 añadimos la siguiente fórmula que arrastraremos hasta C22:
=SI(B2=1;C1+1;C1)

Reordenar columnas por filas


Como resultante vemos numeradas, todas las filas de cada bloque, secuencialmente.

Estos dos rangos serán el apoyo para nuestra fórmula final.

Disponemos los encabezados necesarios en el rango E7:K13...
En F7:K7: 1-2-3-4-5-6
En E8:E13: 1-2-3-4-5-6- etc

Y completamos en F8 que luego copiaremos y pegaremos al resto del rango F8:K13:
=SI.ERROR(INDICE($A$2:$A$22;SI(SUMAPRODUCTO(($C$2:$C$22=$E8)*($B$2:$B$22=F$7)*(FILA($A$2:$A$22)))=0;NOD();SUMAPRODUCTO(($C$2:$C$22=$E8)*($B$2:$B$22=F$7)*(FILA($A$2:$A$22)-1))));"")



En esencia es la misma fórmula expuesta en el post anteriromente comentado (aquí)...
Pero incorpora algunos condicionales para gestionar la no existencia en todos los tramos de todos los elementos...


Con esto se soluciona cualquier casuística de elementos dados por columnas y traspasados a filas.

3 comentarios:

  1. Hola Ismael,

    Por alusiones ;-) te mando una solución en Power Query que sin duda es mucho más "elegante" :-)

    Dejo el código comentado, un poco de dificultad sí que tiene.

    let

    Origen = Excel.CurrentWorkbook(){[Name="Test"]}[Content],

    // Toma como origen una tabla del Libro con nombre "Test".

    Grupo = Table.AddColumn(Origen, "Grupo", each if Text.Contains(Text.From([Datos]),"-") then [Datos] else null),

    // Añade una columna llamada "Grupo". Si el texto del valor de "Datos" contiene "-" repite el valor de "Datows", si no nulo.

    Huecos = Table.FillDown(Grupo,{"Grupo"}),

    // Rellena la columna de "Grupo" cambiando los nulos por el valor anterior no nulo.

    Agrupar = Table.Group(Huecos, {"Grupo"}, {{"Datos", each Table.Transpose(Table.FromValue([Datos]))}}),

    // Agrupa la tabla por los valores de la columna de "Grupo". Para cada valor distinto crea una tabla con los datos y la traspone.

    Limpiar = Table.RemoveColumns(Agrupar,{"Grupo"}),

    // Elimina la columna de "Grupos", que sobra.

    NombresCol = List.Union(List.Transform({0..(Table.RowCount(Limpiar)-1)}, each Table.ColumnNames(Limpiar[Datos]{_}))),

    // Como la longitud de cada tabla de datos es variable hay que obtener la lista con los nombres de todas las columnas de datos.

    // A partir de una lista de listas, "List.Union" crea una sola lista sin repeticiones.

    // "List.Transform" es un iterador, "pasa" valores de otra lista con valores desde el 0 hasta el número de filas de la tabla menos 1.

    // Con cada valor pasado "_", se obtiene una lista con los nombres de las columnas de cada tabla en el campo "Datos" de la tabla general.

    Expandir = Table.ExpandTableColumn(Limpiar, "Datos", NombresCol)

    // Expande los valores de cada tabla de la columna de "Datos". "NombresCol" es la lista con todos los nombres de las columnas.

    in

    Expandir


    Un abrazo,

    Daniel Herce

    ResponderEliminar
  2. Son mas largos los comentarios que el propio código. Sin comentarios queda así, que asusta menos:

    let
    Origen = Excel.CurrentWorkbook(){[Name="Test"]}[Content],
    Grupo = Table.AddColumn(Origen, "Grupo", each if Text.Contains(Text.From([Datos]),"-") then [Datos] else null),
    Huecos = Table.FillDown(Grupo,{"Grupo"}),
    Agrupar = Table.Group(Huecos, {"Grupo"}, {{"Datos", each Table.Transpose(Table.FromValue([Datos]))}}),
    Limpiar = Table.RemoveColumns(Agrupar,{"Grupo"}),
    NombresCol = List.Union(List.Transform({0..(Table.RowCount(Limpiar)-1)}, each Table.ColumnNames(Limpiar[Datos]{_}))),
    Expandir = Table.ExpandTableColumn(Limpiar, "Datos", NombresCol)
    in
    Expandir

    Daniel

    ResponderEliminar
    Respuestas
    1. :DD
      Mil gracias de nuevo, Daniel!!
      Habrá que probarlo y estudiarlo

      Un cordial saludo
      ;-)

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.