Búsquedas Dinámicas y Basadas en Parámetros
Desliza para mostrar el menú
El libro de trabajo ya admite búsquedas relacionales e informes dinámicos. En este capítulo, se construyen resúmenes a nivel de categoría y se introduce lógica basada en parámetros que cambia los cálculos dinámicamente según los escenarios seleccionados por el usuario.
Estructura de SUMPRODUCT
=SUMPRODUCT(array1 * array2 * ...)
array1: primer arreglo de cálculo;array2: segundo arreglo de cálculo;TRUE: convertido en1;FALSE: convertido en0.
Esto permite que las condiciones lógicas y la agregación ocurran dentro de una sola fórmula.
Estructura de INDIRECT
=INDIRECT(ref_text, [a1])
ref_text: texto convertido en una referencia activa;[a1]: argumento opcional de estilo de referencia.
INDIRECT permite que las fórmulas cambien referencias dinámicamente según los valores de las celdas.
Dentro de la hoja Summary, agrega los siguientes encabezados:
Category
Total_Revenue
Total_Cost
Total_Profit
En A10, escribe:
=UNIQUE(Products[Category])
La lista de categorías ahora se expande automáticamente a medida que se agregan nuevas categorías.
En B10, escribe:
=SUMPRODUCT((XLOOKUP(Sales_Data[Product],Products[Product],Products[Category],"")=A10)*Sales_Data[Revenue])
XLOOKUP(...): recupera los valores de categoría para cada producto;=A10: verifica si la categoría coincide;Sales_Data[Revenue]: valores que se están agregando.
Rellena la fórmula hacia abajo en la columna.
En C10, escribe:
=SUMPRODUCT((XLOOKUP(Sales_Data[Product],Products[Product],Products[Category],"")=A10)*XLOOKUP(Sales_Data[Product],Products[Product],Products[Cost],0)*Sales_Data[Units])
La fórmula calcula dinámicamente el costo total por categoría.
En D10, escribe:
=B10-C10
Rellena la fórmula hacia abajo y da formato apropiado a todos los valores.
Dentro de la hoja Summary, crea una celda para:
Active Pricing Scenario
Aplica Validación de Datos usando las siguientes opciones:
Pricing_Tiers
Pricing_Tiers_Promo
En Sales_Data, reemplaza la fórmula de descuento anterior por:
=XLOOKUP([@Units],INDIRECT(Summary!$F$9&"[Min_Units]"),INDIRECT(Summary!$F$9&"[Discount_Rate]"),0,-1)
Summary!$F$9: tabla de escenario seleccionada;INDIRECT(...): convierte texto en referencias activas de tabla;-1: modo de coincidencia aproximada.
La búsqueda ahora cambia dinámicamente entre escenarios de precios.
Cambia el valor seleccionado en el menú desplegable de escenarios.
Confirma que:
Discount_Ratese actualiza automáticamente;Discounted_Revenuese actualiza automáticamente;- Todos los cálculos dependientes reaccionan al modelo de precios seleccionado.
1. ¿Cuál es el papel de SUMPRODUCT en esta lección?
2. ¿Por qué se utiliza INDIRECT en modelos impulsados por parámetros?
3. ¿Cuál es el principal beneficio de usar UNIQUE con SUMPRODUCT en tablas de resumen?
¡Gracias por tus comentarios!
Pregunte a AI
Pregunte a AI
Pregunte lo que quiera o pruebe una de las preguntas sugeridas para comenzar nuestra charla
