Rangos transpuestos: las funciones TRANSPONER y DESREF.

En el último mes me han llegado bastantes consultas sobre la manera de transponer rangos en nuestras hojas de Excel. Claro está que Microsoft Office Excel nos ofrece una forma rápida de trabajar y transponer rangos, con su función TRANSPONER, lógicamente función matricial (para ejecutar presionando Ctrl+Mayusc+Enter).

En esencia el funcionamiento de nuestra función es muy sencillo, sólo es necesario tener presente que TRANSPONER devuelve un rango de celdas vertical como un rango horizontal o viceversa. Recordar en todo momento que TRANSPONER debe especificarse como una fórmula matricial y que el rango 'destino' tenga el mismo número de filas o columnas que el rango de origen.

Por ejemplo, si queremos transponer el rango en columna A1:A10 (de diez filas x una columna), podremos hacerlo matricialmente seleccionando el rango C1:L1 y en la celda activa C1 escribir:
=TRANSPONER(A1:A10)
y presionar Ctrl+Mayusc+Enter en lugar de Enter.

Esta función nos puedes ser útil en diferentes ocasiones. En el ejemplo de hoy construiremos un informe resumen basado en datos de diferentes hojas de un mismo libro de trabajo de Excel. En este informe completaremos, en función a un dato de periodicidad (Mensual, Trimestral o Anual) y al mes correspondiente, datos de servicios a prestar.
Para esta tarea empelaremos las funciones TRANSPONER, DESREF, INDIRECTO y Nombres definidos.

Veamos los datos con que contamos. Tenemos un Libro con cuatro hojas: Resumen, Mensual, Trimetral, Anual. En las hojas 'Mensual', 'Trimetral' y 'Anual' tenemos los listados por mes a prestar según la periodicidad contratada:

He asignado diferentes Nombres definidos para distintas partes de esas hojas:
ene_Anual =Anual!$B$2
ene_Mensual =Mensual!$B$2
ene_Trimestral =Trimestral!$B$2
feb_Anual =Anual!$B$6
feb_Mensual =Mensual!$B$8
feb_Trimestral =Trimestral!$B$7
mar_Anual =Anual!$B$11
mar_Mensual =Mensual!$B$13
mar_Trimestral =Trimestral!$B$11

Fijémosnos que los Nombres hacen referencia a una sola celda, la primera de cada grupo de mes en las tres hojas de periodicidad. Esto lo hemos hecho así para poder conseguir, mediante el uso de DESREF, un rango dinámico (aunque hay formas más sencillas de conseguirlo, por ejemplo, trabajando con Tablas).

Además disponemos de la hoja principal 'Resumen' donde completaremos con los valores anteriores:

El objetivo es completar los rangos D3:G8, H3:K8 y L3:O8 con sus valores correspondientes por Periodicidad, según la hoja del Libro donde se encuntre, y según el mes dentro de cada hoja.

La función buscada es:
=TRANSPONER(DESREF(INDIRECTO(D$2&"_"&$C3);;;CONTAR.SI(INDIRECTO($C3&"!$A:$A");D$2);1))
que aplicaremos por rangos horizontales de cuatro columnas de ancho, esto es, al rango D3:G3, D4:G4, etc.

Tras copiar y pegar en nuestro informe esto es lo que vemos:

Vemos que en algunos casos, cuando el rango origen es de menor tamaño que el rango destino, aparece un error #N/A; esto es motivado por la explicación dada al inicio de este post. Posteriormente podríamos configurar un Formato condicional para que no se vieran estos errores.

Explicaremos o desgranaremos nuestra fómula desde lo más profundo. Vemos que dentro de la función DESREF, como argumento ancla hemos añadido la función
INDIRECTO(D$2&"_"&$C3)
que convierte en una referencia entendible por Excel una combinación de celdas, las necesarias para componer uno de nuestros Nombres definido; por ejemplo D$2&"_"&$C3 devuelve 'ene_Mensual'.
De igual manera que dentro de otro argumento de DESREF se ha empleado la fución CONTAR.SI para determinar el alto del rango
CONTAR.SI(INDIRECTO($C3&"!$A:$A");D$2);1)
con esta función contamos el número de registros del mes de análisis. También usamos INDIRECTO para referirnos a un rango (la columna A:A) de cada hoja (Mensual, Trimestral o Anual).

En su conjunto, con
DESREF(INDIRECTO(D$2&"_"&$C3);;;CONTAR.SI(INDIRECTO($C3&"!$A:$A");D$2);1)
obtenemos el rango concreto correspondiente a la hoja y al mes deseado.

El resto de la fórmula es sencillo, ya que sobre el rango determinado y conseguido con DESREF, que es un rango vertical, aplicamos TRANSPONER para colocarlo horizontalmente en nuestro informe.