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: MS Excel
usted es un duro. lo felicito. y le invito a visitar mi pagina web. www.compexcel.net
ResponderBorrarAmigo 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!).
ResponderBorrarSó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.
Hola Natxo
ResponderBorrarno, 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.
¿Qué tal Jorge?
ResponderBorrarAntes 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
Hola
ResponderBorrarsuponiendo 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.
Muchas gracias por su comentario, realmente me fue de mucha utilidad y era precisamente lo que estaba buscando, que pase un excelente día.
ResponderBorrarSaludos
Marco
No sabe ud. señor dunkelman el tiempo de trabajo q me ha ahorrado.
ResponderBorrarMuchisimas gracias desde Córdoba
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.
ResponderBorrarNo, la fórmula crea un vector horizontal o vertical.
ResponderBorrarPodrías usar la fórmula 12 veces, usando cada vez 46 filas como referencia.
Creo que una macro sería una mejor solución.
Desde venezuela un abrazo amigo
ResponderBorrarslds.. y muchas gracias por este post
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?
ResponderBorrargrá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!
Mi sugerencia es que descargues el excelente complemento "Morefunc" de Laurent Longre y utilices las funcion UNION.OFFSET
ResponderBorrarDe antemano las gracias por su respuesta, mi pregunta es: ¿como puedo obtener la diagonal de una matriz en una fila o columna?
ResponderBorrarSuponiendo que la matriz ocupa el rango A1:D4, podemos usar
ResponderBorrar=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.
Y si la matriz tiene espacios vacíos, cómo hago para que la fila o la columna obvie estos espacios?
ResponderBorrarGraicas
No se me ocurre cómo y no creo que pueda hacerse con fórmulas. Puede hacerse con Vba.
ResponderBorrarMaría Luisa, has probado de hacer un filtro y marcar la casilla "blank" (vacío) y borrarlas todas?
ResponderBorrarBuenas, 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?
ResponderBorrarPodrí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.
ResponderBorrarEn realidad son valores de energía para dos parejas de ángulos. Por ejemplo,
ResponderBorrar10 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?
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.
ResponderBorrarhola a todos sin duda son conocedores del tema es por ello me atrebo a pedirles que me ayuden como un problema que tengo.
ResponderBorrarbueno 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
He mostrado una solución a este problema en esta nota.
ResponderBorrarBuenas tardes, tengo la siguiente cuestión.
ResponderBorrarTengo 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?
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.
ResponderBorrarHola Jorge
ResponderBorrarEstoy 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
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.
ResponderBorrarSin 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.
Hola Jorge
ResponderBorrarMuchas 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
Excelente respuesta me sirvio de muchisimo,
ResponderBorrarmuchisimas gracias,
te doy un 10!
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!
ResponderBorrarNo tengo claro que significa inercalado en tu caso, pero te sugiero que intentes usar la t[ecnica que uestro en esta nota.
ResponderBorrarMuchas felicidades! Por este post! Muchas gracias por compartir la sabiduría que a muchos nos falta en Excel
ResponderBorrarGracias, indiscutible el valor del aporte, me resulto totalmente apropiada la información.
ResponderBorrarGracias mil.
Que tal? cómo puedo utilizar los macros para convertit una columna de 500 filas en una matriz de 50 x 10
ResponderBorrarEscribiendo el código que haga la tarea :)
ResponderBorrarEn los próximos días publicaré una nota sobre el tema.
Hola Jorge,
ResponderBorrarTengo 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
Fijate en este post del blog
ResponderBorrarGracias por salvarme la vida. <3
ResponderBorrarGracias totales, muy útil.
ResponderBorrarHola. ¿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¿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).
ResponderBorrarTengo 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.
ResponderBorrarMuchísimas gracias.
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.
ResponderBorrarTe 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.
ResponderBorrarLeo,
ResponderBorraral igual que a Anónimo, le mejor forma de hacer es con Unpivot de Power Query.
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
ResponderBorrarTengo 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
Tal como lo planteas no se puede hacer sin utilizar Vba (macros).
ResponderBorrarHola Jorge,
ResponderBorrarMe acabas de salvar de un problema muy grande con este invento, solo quiero agradecerte! Saludos.
Hola Jorge,
ResponderBorrarTambién me acabas de salvar de un problema muy grande con este invento, solo quiero agradecerte! Saludos.
Buenas noches:
ResponderBorrarEn 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.
Igor, se puede hacer con fórmulas agregando una columna auxiliar a tu tabla y combinando las funciones ESNUMERO con COINCIDIR.
ResponderBorrarPuedes descargar un ejemplo con este enlace
http://1drv.ms/1UGRTVY
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"
ResponderBorrarhttp://1drv.ms/1UGRTVY
Buenos dias.
ResponderBorrarMuchas gracias por el apunte. Funciona perfectamente y me a salvado de un problema gordo.
=DESREF(matriz;TRUNCAR((FILA()-FILA($E$2))/COLUMNAS(matriz));RESIDUO(FILA()-FILA($E$2);COLUMNAS(matriz));1;1)
ResponderBorrarcomo ignoro los duplicados ?
Eliminándolos del rango de origen o del rango del resultado. No creo que valga la pena comlicar la fórmula.
ResponderBorrarHay otras soluciones pero determinar la más eficiente depende del diseño y del tamaño de los datos.
Buenos días, necesitaría convertir convertir una matriz (de 24*600) en una única columna pero utilizando macros, es posible? Gracias de antemano
ResponderBorrarSi, se puede.
ResponderBorrarEsta 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
ResponderBorrarBuenos días Jorge,
ResponderBorrarsería posible que me indicaras cómo? LLevo días pensando y no doy con la solución...
GrRacias.
Javier.
Hola Javier,
ResponderBorrarcomo 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.
Hola Jorge,
ResponderBorrargracias por atender mi consulta. Espero tus comentarios.
Feliz fin de semana!!
Javier.
Javier, la publiqué más rápido de lo pensado. Puedes leer el post aquí
ResponderBorrarJorge, gracias por tu rapidez!!
ResponderBorrarJavier.
Hola Jorge,
ResponderBorrarNo 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.
Hola:
ResponderBorrarFelicidades por tu solución. Podrías indicarme como se puede adaptar tu fórmula a una matriz de mayores dimensiones?
Saludos y gracias
Hola:
ResponderBorrarFelicidades por tu solución. ¿Podrías explicarme como adaptar la fórmula a matrices de mayor tamaño?
Gracias y saludos
A partir de cierto tamaño conviene usar algunas de las otras soluciones que propongo:
ResponderBorrarcon macros
con Power Query
Desde Uruguay, simplemente aplausos por la solucion. Gracias!!
ResponderBorrarNo obtengo los mismos resultados, no me dan los mismos resultados al emplear estas formulas, pero lo que describes es tal cual lo que necesito.
ResponderBorrarFijate en este post donde muestro como hacerlo con Power Query.
ResponderBorrarMás eficiente y más sencillo.
excelente, muchas gracias. en verdad estaba buscando esta formula muy agradecida.
ResponderBorrarUn trabajo fantástico
ResponderBorrar