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:
Nuestra solución formulada se verá así:
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)
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)
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.
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[...] |
Nuestra solución formulada se verá así:
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)
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)
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.
Hola Ismael,
ResponderEliminarPor 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
Son mas largos los comentarios que el propio código. Sin comentarios queda así, que asusta menos:
ResponderEliminarlet
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
:DD
EliminarMil gracias de nuevo, Daniel!!
Habrá que probarlo y estudiarlo
Un cordial saludo
;-)