Convertir datos de matriz a columna o fila en Excel

jueves, abril 03, 2008

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.

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:

33 comments:

Anónimo,  03 abril, 2008 23:25  

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

Natxo 04 abril, 2008 13:32  

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.

Jorge L. Dunkelman 04 abril, 2008 18:01  

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.

Marco 12 abril, 2008 03:32  

¿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

Jorge L. Dunkelman 12 abril, 2008 11:25  

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.

Marco 14 abril, 2008 18:52  

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

perita,  27 marzo, 2009 20:57  

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

Muchisimas gracias desde Córdoba

Anónimo,  04 febrero, 2010 02:34  

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.

Jorge L. Dunkelman 04 febrero, 2010 10:37  

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.

Anónimo,  01 junio, 2010 08:32  

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

Alejandro Marín Gutiérrez 31 enero, 2011 15:32  

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!

Jorge L. Dunkelman 01 febrero, 2011 18:22  

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

Anónimo,  10 agosto, 2011 20:31  

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

Jorge L. Dunkelman 11 agosto, 2011 08:36  

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.

María Luisa Eslava 04 enero, 2012 04:58  

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

Graicas

Jorge L. Dunkelman 04 enero, 2012 22:04  

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

Anónimo,  23 abril, 2012 15:00  

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

Anónimo,  06 agosto, 2012 19:40  

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?

Jorge L. Dunkelman 06 agosto, 2012 21:20  

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.

Anónimo,  06 agosto, 2012 22:52  

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?

Jorge L. Dunkelman 10 agosto, 2012 18:11  

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.

Anónimo,  08 diciembre, 2012 20:46  

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

Jorge L. Dunkelman 09 diciembre, 2012 16:59  

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

Jorge 18 febrero, 2013 23:45  

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?

Jorge L. Dunkelman 19 febrero, 2013 07:13  

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.

APCE 06 marzo, 2013 21:39  

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

Jorge L. Dunkelman 07 marzo, 2013 07:41  

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.

APCE 08 marzo, 2013 00:33  

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

Anónimo,  06 abril, 2013 01:05  

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

Fede Priotti 05 julio, 2013 22:31  

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!

Jorge Dunkelman 06 julio, 2013 10:39  

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

Anónimo,  25 julio, 2013 11:27  

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

Jose Gonzalez 06 febrero, 2014 16:27  

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

Publicar un comentario

Seguidores

Google+ Followers

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP