Serie Ejercicios de Excel Avanzado
|
Ejercicio #6 Nivel de dificultad: 7/10
Suma dinámica de valores con función SI, DESREF y BUSCARH
Figura 1 |
 Índice de contenido |
PLANTEAMIENTO DEL PROBLEMA
Para los datos que se muestran en la figura 1, se requiere realizar 2 tipos de cálculo tanto para Ventas Total como para Clientes Total. En primer lugar se debe poder seleccionar si el cálculo se va a realizar a nivel de mes o como acumulado y luego se debe seleccionar un mes, tal como se muestra a continuación:
Figura 2.
Si se elige el cálculo a nivel de mes:
De la tabla de la figura 1 debe traerse el valor que corresponda al mes seleccionado, para Ventas Total y para Clientes Total.
Si se elige cálculo acumulado:
De la tabla de la figura 1 debe traerse la suma de los valores que van desde enero hasta el mes seleccionado, para Ventas Total y para Clientes Total.
Puedes DESCARGAR AQUÍ el libro de Excel completo con todo terminado.
SOLUCIÓN A ESTE PROBLEMA DE EXCEL
Funciones a utilizar:
- SI
- BUSCARH
- SUMA
- DESREF
- COINCIDIR
Paso 1. Establecer los datos necesarios para los cálculos
En la figura 2, las celdas en color amarillo muestran los valores que se deben ingresar, en este caso: el mes del año y el tipo de cálculo (mes o acumulado).
Paso 2. Análisis del problema
La parte más difícil del problema se da cuando el tipo de cálculo es acumulado, puesto que el rango de los valores a sumar es dinámico y en cuyo caso debemos hacer uso de la función DESREF.
Dado que existen dos tipos de cálculos (nivel de mes y acumulado) se debe emplear la función lógica SI.
En nuestro libro, los valores mensuales se encuentran en la hoja DATA.
Paso 3. Cálculo a nivel de mes
Cuando el cálculo es a nivel de mes, el simple uso de la función BUSCARH servirá para hallar los valores buscados de Ventas Total y Clientes Total para dicho mes.
Ventas Total a nivel de mes:
BUSCARH(C$1;DATA!B3:M4;2;FALSO)
Clientes Total a nivel de mes:
BUSCARH(C$1;DATA!B3:M7;5;FALSO)
Paso 4. Cálculo cuando es acumulado
Cuando el cálculo es acumulado, se debe usar una combinación de las funciones SUMA, DESREF y COINCIDIR.
La función DESREF permite construir el rango dinámico para la función SUMA. La función COINCIDIR permite definir el alto (en cantidad de filas) del rango dinámico que será creado por DESREF.
Ventas Total (acumulado):
SUMA(DESREF(DATA!B$4; 0; 0; 1; COINCIDIR(C$1;DATA!B$3:M$3;0)))
Clientes Total (acumulado):
SUMA(DESREF(DATA!B$7; 0; 0; 1; COINCIDIR(C$1;DATA!B$3:M$3;0)))
Paso 5. Fórmulas finales integradas
Como en nuestro problema existen dos tipos de cálculo (a nivel de mes y acumulado), entonces nuestras fórmulas finales deben hacer uso de la función SI de Excel para la toma de decisión según la selección del usuario.
De esta manera, nuestras fórmulas finales (ya integradas) quedan así:
Ventas Total:
=SI(C$2=”mes”;BUSCARH(C$1;DATA!B3:M4;2;FALSO);SUMA(DESREF(DATA!B$4; 0; 0; 1; COINCIDIR(C$1;DATA!B$3:M$3;0))))
Clientes Total:
=SI(C$2=”mes”;BUSCARH(C$1;DATA!B3:M7;5;FALSO);SUMA(DESREF(DATA!B$7; 0; 0; 1; COINCIDIR(C$1;DATA!B$3:M$3;0))))
