lunes, febrero 03, 2014

Transponer rangos que contienen referencias

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.




9 comentarios:

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

    Gracias.

    ResponderBorrar
  2. 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.

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

    ResponderBorrar
  4. 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?

    ResponderBorrar
  5. 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

    ResponderBorrar
  6. Excelente !!! Gracias...

    ResponderBorrar
  7. Muchas gracias, está súper útil la información

    ResponderBorrar
  8. Muchísimas gracias por estre truco

    ResponderBorrar

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