jueves, abril 03, 2008

Convertir datos de matriz a columna o fila en Excel

Hace unos días atrás me dejaron en un comentario esta consulta:

COMO PUEDO PASAR LOS DATOS DE UNA MATRIZ A SOLO UNA COLUMNA??

En esta nota veremos como hacerlo, no sólo cómo poner todos los datos de una matriz en una sola columna, sino también en una sola fila.

Actualización: mucho más fácil y eficiente hacerlo con Power Query; ver este post

Empecemos por plantearnos una matriz, que por comodidad, será de tres fila por tres columnas



Nuestro objetivo es crear una columna que contenga todos los elementos de la matriz



Mi primer paso fue decidir si hacerlo con fórmulas o con macros. Decidí hacerlo con fórmulas.

El archivo con las fórmulas puede descargarse aquí.

De todas las funciones la que me pareció más apropiada es DESREF. Como ya hemos explicado en el pasado, esta función tiene cinco argumentos




En este caso, nuestro "ancla" será toda la matriz, el rango A1:C3. Este rango lo definimos en un nombre, "matriz", para mayor comodidad.

Para obtener el primer elemento de la matriz usamos la fórmula

=DESREF(matriz;0;0;1;1)



Para poner el segundo elemento de la matriz en la columna usamos la fórmula

=DESREF(matriz;1;0;1;1)



A esta altura de los acontecimientos está claro que nuestra fórmula tiene que cambiar en forma dinámica el argumento de la fila y de la columna. Es decir, una fórmula que a medida que la copiamos a lo largo de una columna, vaya generando en forma dinámica los elementos de la matriz.

Para resolver el número de fila usaremos esta fórmula

=RESIDUO(FILA()-FILA($E$2);FILAS(matriz))

Esta fórmula produce estos resultados



Usamos $E$2 como argumento ya que en esa celda empezará nuestra columna.

Ahora tenemos que lograr que el argumento de la columna cambie de 1 a 3, en nuestro caso, cada tres filas. Para esto usaremos la fórmula

=TRUNCAR((FILA()-FILA($E$2))/FILAS(matriz))

Los resultados de esta fórmula son



A los argumentos de alto y ancho de DESREF les damos un valor de 1. Finalmente, nuestra fórmula será

=DESREF(matriz;RESIDUO(FILA()-FILA($E$2);FILAS(matriz));TRUNCAR((FILA()-FILA($E$2))/FILAS(matriz));1;1)

En nuestro ejemplo empezamos la columna en la celda E2 y obtenemos este resultado



Esta fórmula copia los elementos de la matriz primero por columna y luego por fila. Si queremos copias los elementos primero por fila y luego por columna, usamos la fórmula anterior modificada de la siguiente manera

=DESREF(matriz;TRUNCAR((FILA()-FILA($E$2))/COLUMNAS(matriz));RESIDUO(FILA()-FILA($E$2);COLUMNAS(matriz));1;1)

Si queremos copiar los elementos de la matriz a lo largo de una fila tendremos que usar esta fórmula

=DESREF(matriz;TRUNCAR((COLUMNA()-COLUMNA($B$13))/COLUMNAS(matriz));RESIDUO((COLUMNA()-COLUMNA($B$13));COLUMNAS(matriz));1;1)

para copiar por columna y luego por fila, o ésta para copiar primero por fila y luego por columna

=DESREF(matriz;RESIDUO((COLUMNA()-COLUMNA($B$14));FILAS(matriz));TRUNCAR((COLUMNA()-COLUMNA($B$14))/(FILAS(matriz)));1;1)




Esta nota está basada en las fórmulas desarrolladas por Chip Pearson.

Technorati Tags:

72 comentarios:

  1. usted es un duro. lo felicito. y le invito a visitar mi pagina web. www.compexcel.net

    ResponderBorrar
  2. Amigo Jorge, una vez más consigues sorprenderme con lo ingenioso de tus soluciones. Confieso que me da rabia el no habérseme ocurrido algo así las veces que se me ha presentado esta cuestión. Otro problema resuelto (¡y ya son muchos!).

    Sólo tengo una pregunta que hacerte: he utilizado la fórmula propuesta por tí, sólo que sustituyendo el primer argumento de la función DESREF (es decir, el nombre "matriz") por la primera celda de la misma (en tu caso $A$1). Aparentemente al menos, funciona exactamente igual. ¿Ves alguna diferencia funcional entre ambas fórmulas que esté pasando por alto?.

    Gracias de antemano por tu tiempo y recibe un saludo desde Bilbao.

    ResponderBorrar
  3. Hola Natxo
    no, no veo ninguna diferencia. Originalmente también había diseñado la función con la primer celda de la matriz como ancla. Luego, buscando cómo resolver el tema del paso de una columna a otra encontré la solución de Chip Pearson, que menciono en la nota. Él usa la matriz como "ancla", pero obviamente Excel sólo "ve" el primer elemento ya que la función no es matricial.

    ResponderBorrar
  4. ¿Qué tal Jorge?

    Antes que nada, dejeme felicitarlo por sus comentarios y orientaciones tan acertadas sobre el manejo de excel.

    A este respecto aquí le planteo mi pregunta.

    Estoy trabajando sobre un diseño estadistico de bloques completamente al azar, en el que tengo 6 tratamientos con 3 repeticiones. ¿Es posible hacer el sorteo y diseño de las parcelas en excel?

    le dejo un ejemplo plasmado aquí mismo

    tratamientos
    r 1 2 3 4 5 6
    e
    p 2 4 6 1 3 5
    e
    t 2 5 3 1 6 5

    la situación es que yo escriba la primer fila y excel calcule automáticamnete las otras dos filas de las repeticiones siguientes.

    Espero me haya entendido y pued ayudarme en esta situación que me trae vuelto loco.

    Saludos

    ResponderBorrar
  5. Hola
    suponiendo que cada valor aparece en un celda, la mejor opción es usar la función MRAND del complemento de Laurent Longre como muestro en esta nota sobre números aleatorios sin repeteciones.

    ResponderBorrar
  6. Muchas gracias por su comentario, realmente me fue de mucha utilidad y era precisamente lo que estaba buscando, que pase un excelente día.

    Saludos

    Marco

    ResponderBorrar
  7. No sabe ud. señor dunkelman el tiempo de trabajo q me ha ahorrado.

    Muchisimas gracias desde Córdoba

    ResponderBorrar
  8. Se puede utilizar esta fórmula para convertir una matriz columna de 552 filas en una matriz de 46 filas y 12 columnas? lo he intentado pero no lo he conseguido. Agradecería mucho una aclaración.

    ResponderBorrar
  9. No, la fórmula crea un vector horizontal o vertical.
    Podrías usar la fórmula 12 veces, usando cada vez 46 filas como referencia.
    Creo que una macro sería una mejor solución.

    ResponderBorrar
  10. Desde venezuela un abrazo amigo
    slds.. y muchas gracias por este post

    ResponderBorrar
  11. hola! me podría decir alguien como consigo lo inverso? es decir, desde una columna de 4 filas, o en otras palabras, una matriz(1*4), convertirla en una matriz 2 * 2? y que quede ordenado?

    gráficamente sería algo como
    1
    2
    3
    4
    que quede
    12
    34
    ??

    muchas gracias por vuestros comentarios! ojalá sean solo formulas ya que la programacion no se me da muy bien!

    ResponderBorrar
  12. Mi sugerencia es que descargues el excelente complemento "Morefunc" de Laurent Longre y utilices las funcion UNION.OFFSET

    ResponderBorrar
  13. De antemano las gracias por su respuesta, mi pregunta es: ¿como puedo obtener la diagonal de una matriz en una fila o columna?

    ResponderBorrar
  14. Suponiendo que la matriz ocupa el rango A1:D4, podemos usar

    =INDICE($A$1,$D$4,,FILAS($1:1)

    Pomemos esta fórmula en alguna celda en la fila 1 y la copiamos hasta la fila 4.

    ResponderBorrar
  15. Y si la matriz tiene espacios vacíos, cómo hago para que la fila o la columna obvie estos espacios?

    Graicas

    ResponderBorrar
  16. No se me ocurre cómo y no creo que pueda hacerse con fórmulas. Puede hacerse con Vba.

    ResponderBorrar
  17. María Luisa, has probado de hacer un filtro y marcar la casilla "blank" (vacío) y borrarlas todas?

    ResponderBorrar
  18. Buenas, me gustaría saber cómo hacer justo lo contrario. Tengo una columna de valores A, otra columna de valores B, y otra de valores C que son los valores de una función de A y B. Lo que quiero hacer es poner A como columna, B como fila y que la columna C se coloque automáticamente en la tabla matricial correspondiente .... ¿Es posible?

    ResponderBorrar
  19. Podrías usar una tabla dinámica con los valores de la columna A en el área de las filas; la columna B en el área de las columnas y los de la columna C en el área de los valores. Pero como hay una relación funcional entre A y B, lo que obtendrás es una matriz con valores sólo en la diagonal.

    ResponderBorrar
  20. En realidad son valores de energía para dos parejas de ángulos. Por ejemplo,

    10 20 30
    10
    20
    30

    La columna C, tiene los valores de todas las combinaciones angulares posibles (10 10, 10 20, 10 30, 20 10 ...). ¿No podría entonces para cada pareja de valores de ángulos poner los correspondientes datos en forma tabular?

    ResponderBorrar
  21. Ahora veo el planteo. Vuelvo a inisitir que la mejor solución es una tabla dinámica. Podés mandarme el archivo (la dirección aparece en el enlace Ayuda), para que pueda mostrarte cóo hacerlo.

    ResponderBorrar
  22. hola a todos sin duda son conocedores del tema es por ello me atrebo a pedirles que me ayuden como un problema que tengo.
    bueno yo tengo una matriz de la siguiento forma
    clave n1 n2 n3 n4 n5 n6 n7
    2564 12 15 06 12 15 12 07
    2567 07 15 19 12
    2568 04 12
    2569 12 12 15
    2570 12 16 19 15
    .
    .
    .
    y re quiero pasarlo a dos fila de la siguente manera.
    clave dato
    2564 12
    2564 15
    2564 06
    2564 15
    2564 12
    2564 07
    2567 07
    2567 15
    2567 19
    2567 12
    2568 04
    2568 12
    2569 12
    2569 12
    2569 15
    .
    .
    .
    espero puedan ayudar con este problema

    ResponderBorrar
  23. He mostrado una solución a este problema en esta nota.

    ResponderBorrar
  24. Buenas tardes, tengo la siguiente cuestión.

    Tengo en una columna 8760 valores (filas) y me gustaría convertirlos en una matriz de 365 filas x 24 columnas. ¿Cómo podría hacerlo?

    ResponderBorrar
  25. Podríamos adaptar la fórmula y aplicarla a 365 filas, pero lo forma más eficiente que se me ocurre es usando una macro.

    ResponderBorrar
  26. Hola Jorge
    Estoy buscando una solucion para poder pasar los siguientes datos que tengo en una sola fila a dos filas.
    Los datos originales estan asi:

    6P-4109-00
    9
    4L-5356-00
    12
    4L-5491-00
    3
    3M-4111-00
    3
    3M-5025-00
    12
    2B-4054-00
    24
    7B-4061-00
    12

    Y yo los requiero asi:

    6P-4109-00 9
    4L-5356-00 12
    4L-5491-00 3
    3M-4111-00 3
    3M-5025-00 12
    2B-4054-00 24
    7B-4061-00 12

    Ojala me puedas ayudar porque tengo muchismos datos asi que tengo que acomodar en ese layout.

    Gracias
    Saludos

    ResponderBorrar
  27. Hola, entiendo que te referís a que los datos están en una sola columna y querés pasarlos a dos columnas (no filas). Como se trata de muchos datos, digamos miles de filas, lo más eficiente sería usar una macro.
    Sin usar Vba podrías hacerlo en varios pasos. Suponiendo que los datos están en la columna A, empesando en la celda A2,
    1 - En la columna B ponés esta fórmula =CONCATENAR(A2," ",A3)y la copiás a lo largo del rango;
    2 - Cancelás las fórmulas de la columna B usando Copiar-Pega Especial-Valores
    3 - En la columna C ponés esta fórmula =RESIDUO(FILA(),2). Esta fórmula da 0 para las filas pares y 1 para las impares.
    4 - Como queremos eliminar las filas impares aplicamos Autofiltro y filtramos usamos como criterio en la columna C el valor 1. Seleccionamos todo el rango visible y eliminamos las filas.
    5 - Quitamos el Autofiltro y eliminamos la columna C.

    ResponderBorrar
  28. Hola Jorge

    Muchas gracias por la explicación, si me funciono , lo único es que me deja los datos en una sola columna, no se si me falto algún paso.

    Y lo que estoy haciendo es separarlos con un Text to columns

    Gracias
    Saludos

    ResponderBorrar
  29. Excelente respuesta me sirvio de muchisimo,
    muchisimas gracias,
    te doy un 10!

    ResponderBorrar
  30. Hola Jorge, excelente explicación. Mi problema es que tengo 9 columnas, la primera con las fechas y luego 4 corresponden a insumos y las otras 4 a las cantidades usadas, pero están de manera intercalada. Entonces quiero que que me queden 3 columnas, una con la fecha, otra con los insumos y la otra con la cantidad usada de cada uno. Será factible? Muchas gracias!

    ResponderBorrar
  31. No tengo claro que significa inercalado en tu caso, pero te sugiero que intentes usar la t[ecnica que uestro en esta nota.

    ResponderBorrar
  32. Muchas felicidades! Por este post! Muchas gracias por compartir la sabiduría que a muchos nos falta en Excel

    ResponderBorrar
  33. Gracias, indiscutible el valor del aporte, me resulto totalmente apropiada la información.
    Gracias mil.

    ResponderBorrar
  34. Que tal? cómo puedo utilizar los macros para convertit una columna de 500 filas en una matriz de 50 x 10

    ResponderBorrar
  35. Escribiendo el código que haga la tarea :)

    En los próximos días publicaré una nota sobre el tema.

    ResponderBorrar
  36. Hola Jorge,

    Tengo una fila con 402 columnas.
    Las quiero transformar en una matriz de 6 columnas por 67 filas.
    No logro hacerlo en forma automática.

    gracias

    ResponderBorrar
  37. Gracias por salvarme la vida. <3

    ResponderBorrar
  38. Hola. ¿Cómo puedo copiar el contenido de las celdas A1, B1, C1 y D1 de una hoja en las celdas A1, B2, C3 y D4 de otra hoja? Muchas gracias por amabilidad.

    ResponderBorrar
  39. ¿Existe alguna situación en particular? Porque de lo contrario, sencillamente y tratándose de cuatro celdas lo más practico es copiar y pegar (con o sin vínculo).

    ResponderBorrar
  40. Tengo una matríz simétrica (en la que el valor de (i,j) es el mismo que el de (j,i)) y me gustaría convertir a columna solo una de las dos partes simétricas de la matriz, incluída la diagonal principal, claro. He estado intentado darle vueltas a la fórmula que popone pero no lo consigo. El motivo de porqué quiero hacerlo es porque la matriz es ya enorme y al pasarla a columnas me salen mas líneas de las que admite el Excel. Si solo paso una de las dos mitades simétricas entonces si tendría bastantes líneas.
    Muchísimas gracias.

    ResponderBorrar
  41. Hola buenas tardes Jorge. Tengo un contratiempo. Tengo matrices de 24x31 o 24x30 o 24x28 que corresponden a datos tomados por día y hora en un mes. Yo quiero graficar linealmente esos datos por hora durante más de un mes para lo cual creo que tengo que colocarlos primero todos en una fila o columna. Cuál sería la forma más eficiente de hacerlo? Gracias.

    ResponderBorrar
  42. Te sugiero que uses el Unpivot de Power Query (fijate en este post). No tengo claro con qué versión de Excel estás trabajando. Desde la versión 2007 en adelante Excel tiene más de un millón de líneas por hoja. Si necesitas más, Power Query (y Power Pivot) serían la mejor solución.

    ResponderBorrar
  43. Leo,
    al igual que a Anónimo, le mejor forma de hacer es con Unpivot de Power Query.

    ResponderBorrar
  44. Hola, he intentando hacer algo a lo que expones en el ejemplo sin tener que utilizar bva y no lo consigo. Te expongo mi ejemplo

    Tengo una tabla con los siguientes datos
    Producto cantidad
    Perro 6
    Canario 4
    Lobo 2
    Gato 3

    Quiero transformar los datos de la tabla a una lista para que quede de la siguiente manera:

    Perro
    Perro
    Perro
    Perro
    Perro
    Perro
    Canario
    Canario
    Canario
    Canario
    Lobo
    Lobo
    Gato
    Gato
    Gato

    Dependiendo la cantidad es el número de veces que se repite el producto en cada fila. Alguna sugerencia? Gracias de antemano

    ResponderBorrar
  45. Tal como lo planteas no se puede hacer sin utilizar Vba (macros).

    ResponderBorrar
  46. Hola Jorge,
    Me acabas de salvar de un problema muy grande con este invento, solo quiero agradecerte! Saludos.

    ResponderBorrar
  47. Hola Jorge,
    También me acabas de salvar de un problema muy grande con este invento, solo quiero agradecerte! Saludos.

    ResponderBorrar
  48. Buenas noches:

    En un análisis para mi tesis doctoral, tengo casi un millar de datos de la siguiente forma:

    Nombre Codigo Empresa
    Igor 11
    Igor 14
    Igor 17
    Aitor 11

    Y me gustaría automatizar la conversión de esos datos en una matriz binaria de dos dimensiones, de tal forma que el resultado fuera el siguiente

    11 14 17
    Igor 1 1 1
    Aitor 1 0 0

    ¿Cómo lo podría hacer con Excel? Mi cerebro a estas horas no llega a dar con la solución... y realmente, si consiguiera automatizar, sería un ahorro de tiempo bestial.

    Gracias por adelantado. Saludos, IGOR.

    ResponderBorrar
  49. Igor, se puede hacer con fórmulas agregando una columna auxiliar a tu tabla y combinando las funciones ESNUMERO con COINCIDIR.
    Puedes descargar un ejemplo con este enlace

    http://1drv.ms/1UGRTVY

    ResponderBorrar
  50. Igor, acabo de agregar una opción más eficiente que las fórmulas, usar tablas dinámicas. Descarga el archivo y fijate en la hoja "tabla dinámica"

    http://1drv.ms/1UGRTVY

    ResponderBorrar
  51. Buenos dias.
    Muchas gracias por el apunte. Funciona perfectamente y me a salvado de un problema gordo.

    ResponderBorrar
  52. =DESREF(matriz;TRUNCAR((FILA()-FILA($E$2))/COLUMNAS(matriz));RESIDUO(FILA()-FILA($E$2);COLUMNAS(matriz));1;1)

    como ignoro los duplicados ?

    ResponderBorrar
  53. Eliminándolos del rango de origen o del rango del resultado. No creo que valga la pena comlicar la fórmula.
    Hay otras soluciones pero determinar la más eficiente depende del diseño y del tamaño de los datos.

    ResponderBorrar
  54. Buenos días, necesitaría convertir convertir una matriz (de 24*600) en una única columna pero utilizando macros, es posible? Gracias de antemano

    ResponderBorrar
  55. Esta formula es una autentica maravilla la voy a guardar como un tesoro, debo admitir que no la entiendo jajajaja, pero funciona a la perfección gracias amigo me has salvado de horas de trabajo

    ResponderBorrar
  56. Buenos días Jorge,
    sería posible que me indicaras cómo? LLevo días pensando y no doy con la solución...
    GrRacias.
    Javier.

    ResponderBorrar
  57. Hola Javier,

    como te decía, se puede. Una solución más práctica que usar macros es usar el complemento Power Query. Publicaré una nota durante el fin de semana sobre el tema. Si puedo publicaré tambien el código Vba para hacerlo.

    ResponderBorrar
  58. Hola Jorge,

    gracias por atender mi consulta. Espero tus comentarios.
    Feliz fin de semana!!
    Javier.

    ResponderBorrar
  59. Javier, la publiqué más rápido de lo pensado. Puedes leer el post aquí

    ResponderBorrar
  60. Jorge, gracias por tu rapidez!!

    Javier.

    ResponderBorrar
  61. Hola Jorge,
    No tenía ni idea del Power Query y la verdad que es una muy buena opción. Pero preferiría hacerlo con macro. Sigo buscando.

    Gracias.
    Javier.

    ResponderBorrar
  62. Hola:

    Felicidades por tu solución. Podrías indicarme como se puede adaptar tu fórmula a una matriz de mayores dimensiones?

    Saludos y gracias

    ResponderBorrar
  63. Hola:

    Felicidades por tu solución. ¿Podrías explicarme como adaptar la fórmula a matrices de mayor tamaño?

    Gracias y saludos

    ResponderBorrar
  64. A partir de cierto tamaño conviene usar algunas de las otras soluciones que propongo:

    con macros

    con Power Query

    ResponderBorrar
  65. Desde Uruguay, simplemente aplausos por la solucion. Gracias!!

    ResponderBorrar
  66. No obtengo los mismos resultados, no me dan los mismos resultados al emplear estas formulas, pero lo que describes es tal cual lo que necesito.

    ResponderBorrar
  67. Fijate en este post donde muestro como hacerlo con Power Query.
    Más eficiente y más sencillo.

    ResponderBorrar
  68. excelente, muchas gracias. en verdad estaba buscando esta formula muy agradecida.

    ResponderBorrar
  69. Un trabajo fantástico

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.