Transponer rangos que contienen referencias

lunes, febrero 03, 2014

Excel facilita la tarea de transponer rangos, es decir, invertir el orden filas/columnas. Todo lo que hay que hacer es seleccionar el rango, copiarlo (Ctrl+C) y pegarlo en otra área usando la opción Transponer de Pegado Especial.
El proceso funciona sin problemas si el rango a transponer contiene sólo valores



y por lo general también si el rango contiene fórmulas. En el ejemplo de arriba, la celda C3 (Enero/Norte) contiene la fórmula

=SUMAPRODUCTO((Mes=$B3)*(Sucursal=C$2)*Ventas)

donde "Mes", "Sucursal" y "Ventas" son nombres definidos que se refieren a los rangos que contienen los datos.

En la tabla transpuesta las ventas de Enero de la zona norte se encuentran en la celda C13 que contiene la fórmulas

=SUMAPRODUCTO((Mes=C$12)*(Sucursal=$B13)*Ventas)



Pero hay casos en que Excel puede dar resultados incorrectos como en este ejemplo. Los datos en el rango D2:E6 se refieren a los datos del rango B2:B11


El valor en D2 está ligado a B2; E2 a B7; D3 a B3; etc.

Dado que las referencias son relativas, al transponer el rango Excel "reordena" las referencias


Podemos transponer copiando sólo lo valores (Pegado Especial-Valores-Transponer), pero si queremos conservar las referencias/fórmulas podemos aplicar esta técnica que publicó Chandoo en su blog:


  1. Seleccionamos el rango a transponer (D2:E6 en nuestro ejemplo);
  2. usamos Buscar y Reemplazar (Ctrl+L) para reemplazar los "=" por "#"
  3. copiamos (Ctrl+C) y transponemos el rango modificado;
  4. en el rango transpuesto reemplazamos los "#" por "="
Al reemplazar los "=" por "#", las referencias se convierten en constantes; al reemplazar los "#" por "=" en el rango transpuesto, convertirmos las constantes nuevamente en referencias. Este video muestra el proceso



Este método es útil sólo si los símbolos "=" aparecen al principio de la referencia/fórmula.




6 comments:

pepesoto 24 febrero, 2014 04:52  

MI estimado Jorge:
Una pregunta off-topic.
¿¿Qué programa usa para convertir el video de la primera imagen de este post en gif..??

Gracias.

mcmaicky 26 febrero, 2014 15:25  

Muchísimas gracias Jorge, llevo días intentando buscar la forma de transponer unas columnas a filas con fórmulas que me daban error al transponer. Me ha ayudado un montón ese truco. Muchas gracias.

Anónimo,  21 enero, 2016 12:17  

Vaya crack el que inventó la forma de transponer fórmulas. Que creatividad!!!! Gracias por el post

Enrique Lois Jhonatan Chiclayo Fulgencio 08 julio, 2016 20:43  

Estimado, y cómo podría hacer si tengo datos pero quiero que lo transpuesto sea el vínculo de los datos iniciales? es decir si tengo un rango de A1:A5 con los datos 1,2,3,4,5 y quiero que estén vinculados de manera horizontal en el rango B2:F2 donde B2=A1, C2=A2, D2=A3, E2=A4 y F2=A5.
cómo generarías un artificio para eso utilizando el "reemplazar" caracteres?

Jorge Dunkelman 09 julio, 2016 20:59  

Suponiendo que entendí el planteo, tendrías que usar la función DESREF de esta manera:

en la celda B2 introducir esta fórmula: =DESREF($A$1,COLUMNA()-2,0)
y copiarla al resto del rango B2:F2

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP