jueves, 24 de julio de 2014

Suma condicional tridimensional 3D sin macros.

Frecuentemente se me pregunta por la manera de realizar sumas condicionadas sobre diferentes hojas, y siempre les dirijo hacia esta entrada del blog:
VBA: Una función personalizada de fórmula 3D.

Y es que la teoría nos dice que la función SUMAR.SI o SUMAR.SI.CONJUNTO y sus hermanas para contar o promediar, no admiten el método 'tridimensional' (como se explica en el link anterior).

El trabajo a realizar en nuestra hoja de cálculo (sin macros) es, en esencia, similar al procedimiento detallado en el post de más arriba.
Lo que haremos será, mediante matriciales, recorrer y sumar de manera condicional, cada hoja que indiquemos.


Comencemos con el desarrollo viendo las hojas a sumar: tenemos en nuestro ejemplo tres hojas: DptoA, DptoB y DptoC con unos orígenes de estructura idéntica, tres columnas de datos: Mes, Comercial e Importe:

Suma condicional tridimensional 3D sin macros.


En la hoja Resumen llegaremos, con una sola fórmula a:



El primer paso fundamental es crear un Nombre definido con una Constante matricial que represente los nombres de las hojas que queremos sumar. En mi ejemplo:
Hojas ={"DptoA";"DptoB";"DptoC"}
puesto que son esas tres hojas sobre las que quiero trabajar.


El siguiente paso, y último, es generar nuestra fórmula.. si nos situamos en la celda C3 de la hoja 'Resumen' escribiremos:
=SUMA(SUMAR.SI.CONJUNTO(INDIRECTO(Hojas&"!C2:C13");INDIRECTO(Hojas&"!A2:A13");$B3;INDIRECTO(Hojas&"!B2:B13");C$2))
y ejecutaremos matricialmente!!! (presionando Ctrl+Mayusc+Enter en lugar de solo Enter).


Si nos fijamos tenemos una 'doble suma', ya que hemos anidado un SUMAR.SI.CONJUNTO en una función SUMA.
El truco consiste en aplicar un SUMAR.SI.CONJUNTO sobre cada hoja de las descritas en el nombre definido 'Hojas' de manera independiente, y luego al ser una fórmula matricial, el resultado de cada hoja se acumula con la función SUMA... es decir, es como si hiciéramos:
=SUMAR.SI.CONJUNTO(DptoA!$C$2:$C$13;DptoA!$A$2:$A$13;$B3;DptoA!$B$2:$B$13;C$2)+SUMAR.SI.CONJUNTO(DptoB!$C$2:$C$13;DptoB!$A$2:$A$13;$B3;DptoB!$B$2:$B$13;C$2)+SUMAR.SI.CONJUNTO(DptoC!$C$2:$C$13;DptoC!$A$2:$A$13;$B3;DptoC!$B$2:$B$13;C$2)


Interesante ver cómo hemos construido, con la función INDIRECTO, los rangos sobre los que trabajar, por ejemplo:
INDIRECTO(Hojas&"!C2:C13")
que devolvería convertido en referencia de Excel los rangos:
DptoA!C2:C13
DptoB!C2:C13
DptoC!C2:C13

1 comentario: