jueves, 2 de octubre de 2014

Transponer con la Función DESREF

"Al importar información de la base de datos de mi empresa sobre clientes y sus direcciones, obtengo en una sola columna y en 4.000 filas los datos referentes a nombre, dirección, código postal y ciudad. Lo que necesito es reordenar esta información en cuatro columnas en la que la primera sea el nombre, la segunda la dirección, etcétera."

Para realizar esta tarea no podemos utilizar directamente la opción de Pegado especial transponer porque, evidentemente, nos transformaría la matriz actual de 1 columna y 4.000 filas en 4.000 columnas y 1 fila. Por ello vamos a hacer uso de la función DESREF para transponer la información pero como nos interesa. Para hacer más manejable el ejemplo partiremos de una lista con 20 filas, como se muestra a continuación:


 Lo que pretendemos conseguir es lo siguiente:


Empezamos contando el número de entradas que tenemos y que en nuestro ejemplo es de 20 filas (desde B3 hasta B22). Queremos generar una matriz (una tabla) con cuatro columnas. Así las cosas, si dividimos el número de filas de partida (20) por dicho número de columnas (4) obtendremos el número de filas resultantes, esto es, 5. Esto es importante porque lo primero que tenemos que hacer es generar una lista de números partiendo de 0 y creciendo de 4 en 4. El motivo es que la información original de cada campo dista entre si exactamente 4 filas. Es decir el cliente 1 está en B3 el 2 en B7, el 3 en B11, etcétera. Entre estas celdas hay 4 filas. Lo mismo nos pasará si tomamos los datos de la dirección, B4, B8, B12, etcétera.

Una vez escrito los rótulos en el rango E2:H2, nos situamos en D3 y escribimos 0. En D4 escribimos 4. Seleccionamos D3:D4 y copiamos hacia abajo rellenando la serie hasta 16 (nuestras 5 filas):

Nos situamos ahora en E3 y escribimos la fórmula:
=DESREF($B$3;$D3;0)
Esto significa que que partiendo de la celda B3 "me traiga" el valor que se encuentra el número de filas D3. Como en D3 tenemos el valor 0, nos devolverá el valor de la celda B3, ya que le estamos diciendo que se desplace 0 filas y 0 columnas (el cero de las columnas lo podríamos omitir ya que no nos vamos a desplazar de columna). Al copiar esta fórmula en E4 se transformará en =DESREF($B$3;$D4;0) lo que significa que partiendo de B3 debe desplazarse el número de filas que le indique D4, y que en nuestro ejemplo es el valor 4. Por lo tanto "nos traerá" el valor de la celda B7, que es la que se encuentra 4 filas por debajo de B3. Copiamos entonces la fórmula de E3 hasta la celda E7 y ya tendremos el primer campo resuelto:


Ahora podemos copiar la fórmula de E3 en F3. De esta manera quedará como:
=DESREF($B$3;$D3;0) y lo único que tenemos que hacer es cambiar la referencia de fila de $B$3 por $B$4 y copiar hacia abajo hasta F7. Haremos lo mismo en G3 cambiando la referencia también a $B$5 y, finalmente en H6 donde la referencia debe ser $B$6. En resumen:
En E3: =DESREF($B$3;$D3;0)
En F3: =DESREF($B$4;$D3;0)
En G3: =DESREF($B$5;$D3;0)
En H3: =DESREF($B$6;$D3;0)
Copiamos hacia abajo y obtenemos el resultado deseado: