Excel - FUSIONAR BUSCAR.SI.CONJUNTO Y DESREF PARA OBTENER SUMAS CON RANGOS DINÁMICO

¡Hola a todos!

Verán, necesito calcular el valor de venta promedio de departamentos ponderado por la cantidad de cada una de sus tipologías.
Actualmente lo estoy calculando apoyándome en la fórmula SUMAR.SI.CONJUNTO (con rangos estáticos), sin embargo el cálculo de ésta resulta demasiado pesada cuando trabajo con los 500 mil registros que tengo en mi tabla de datos.

Para ilustrar mi duda, primero explicaré cómo lo estoy haciendo, y luego plantearé mi pregunta; lamento si quizás es algo extenso, pero no encuentro mejor manera de explicarme:

Tengo la siguiente tabla de ejemplo, que me muestra los precios que tienen diferentes departamentos, de distintos edificios, en un determinado momento o periodo del año:



Sobre la cabecera de la tabla:

Trimestre es el trimestre del año, cada proyecto se puede repetir a lo largo del mismo.
Año Año de cada registro
Código es el código del edificio
Contador identificador de proyecto en un periodo único.
Proyecto es el nombre del edificio
Cantidad es el número total de departamentos por cada tipología del edificio
Por ejemplo: El Edificio PROY1, tiene 5 tipologías (o tipos)de departamentos; la primera tipología tiene 50 departamentos, la segunda 15, etc. Bajo ésta lógica, el edificio PROY1 tiene en total 85 departamentos, el Edificio PROY2 tiene 55 departamentos, etc.
Precio Prom. es el precio de cada tipología.
Precio Prom Pond (columna I) Es el precio promedio ponderado según la cantidad de cada tipología
Por ejemplo:El proyecto 1 tiene 65 departamentos "caros" (1ra y 2da tipologías sumadas) con precios entre los 450 a 500 mil, y sólo 20 departamentos "baratos" (3ra, 4ta y 5ta tipologías) con precios entre 90 a 150mil.

Para calcular el precio promedio ponderado de los departamentos en cada edificio (1 valor único por cada proyecto) hago primero el siguiente cálculo:

1. calculo cuál es el precio total que ocupa cada una de las tipologías en la columna "H" (Precio x Cantidad): H= F * G



Seguido, calculo cuánto dinero suman en total todas las tipologías de cada proyecto, en cada periodo.

=SUMAR.SI.CONJUNTO( $H$2:$H$29 ; $D$2:$D$29 ;D2 )
Rango de suma en columna H, Rango de Criterios D, indicador D



Posteriormente, hago lo mismo con el total de departamentos por tipología, para calcular cuántos departamentos tienen en total cada proyecto.

=SUMAR.SI.CONJUNTO($F$2:$F$29 ; $D$2:$D$29 ; D2)
Rango de suma en columna F, Rango de Criterios D, indicador D



Finalmente, solamente resto la columna "H" entre la "I", de ésta forma obtendré un único valor que me muestre el precio promedio ponderado por cada proyecto.





AHORA A LA PREGUNTA

Sé que puedo unir las fórmulas en una sola columna para obtener el mismo resultado, el problema es que, necesito que los rangos sean dinámicos, no estáticos, porque al ser estáticos, estoy obligado a coger el 100% de registros (cerca de 1/4 millón) en cada fórmula, lo que termina convirtiendo a la hoja de cálculo en un dolor de cabeza a la hora de calcular las fórmulas por la tremenda cantidad de tiempo que se toma.

Entonces, ¿Es posible fusionar/utilizar la fórmula DESREF para optimizar el cálculo y obtener el mismo resultado?
No soy pro en Excel, y realmente no alcanzo a dar con la forma para hacerlo. ¿Quizás incluso exista otra manera alterna de hacerlo más rápido?

Quedaré eternamente agradecido.

Martin J.

Hola Martín:
Te propongo que cambies de siglo. Una base de datos con fechas, Excel las agrupa por trimestres, meses, años, etc., y Tablas dinámicas para los informes, sin fórmulas, ni sencillas ni complejas.
https://www.youtube.com/playlist?list=PLF1OL9sNXkSyOJETR9ST8xjwjFj9WrkME

Muchas gracias por la pronta respuesta, sé que puedo calcularlo con tablas dinámicas y de hecho lo hago de cara a informes.
Sin embargo, ésta base de datos alimenta otra plataforma en la que creo dashboards con distintas métricas, y mi pregunta va precisamente por necesito tener éste cálculo en la tabla base, por ésto, nuevamente, ¿es posible o no usar la fórmula SUMAR.SI.CONJUNTO con DESREF para volver sus rangos dinámicos o cualquier otra(s) fórmula(s) para hacerlo?

Hola Martin, tengo un archivo con 541.000 registros de 22 MB, tengo otro con mas de 1 millón de registros de 105 MB y ambos son tablas dinámicas, es lo mas rápido que vas a conseguir en Excel para consolidar información.

La tabla dinámica te permite conectarte a otras fuentes de datos como archivos de texto, bases de datos en Access, Sql, Oracle, etc y hacer los cálculos muchos mas rápidos que las propias formulas, y como dijo Wordexperto, esta es la mejor solución; la tabla puede calcular a través de campo calculado el precio del edificio, la cantidad de apartamentos y el precio promedio sin importar la cantidad de tipos de apartamentos.

Yo tengo un archivo con una tabla dinámica con todo detalle de las ventas y de allí construyo la data que necesito para generar todos los reportes y dashboards que necesito, así mantengo mis reportes "livianos" como para pasarlos por correo y un solo archivo "grande" con la tabla dinámica.

Salu2
Dirk

Muchas gracias Dirk, igualmente Wordexperto por su tiempo y gentileza. Precisamente sucumbí ante la tabla dinámica, definitivamente es más rápido, y logré dar solución al impase. Ahora uso tabla dinámica junto con la formula IMPORTARDATOSDINAMICOS y funciona de lujo.
Sin embargo, me queda el bichito, en caso tuviera poquísimos registros, y quisiera hacer el ejercicio de volver dinámicos los rangos de la fórmula SUMAR.SI.CONJUNTO ¿se podría?