Hemos visto la función SUMAR.SI,CONJUNTO, y, la función INDIRECTO, pues, ahora, vamos a ver un ejemplo entre ambas funciones.
Tenemos el modelo con el que suelo trabajar, es decir, ventas de productos en diferentes centros comerciales, y, provincias, pero lo tenemos dividido en dos hojas, una hoja para el año 2017, y, otra hoja para el año 2018.
Ambos modelos están en formato de tabla.
Tenemos una hoja, donde vamos a especificar un año, y, una provincia, y, queremos ver las ventas para cada producto.
Lo primero que vamos a hacer es crear una lista desplegable con los años, para ello, vamos a la pestaña de datos, hacemos clic en validación de datos dentro del grupo herramientas de datos.
Desplegamos permitir, y, elegimos lista.
En la ventana de origen escribimos los años.
Aceptamos.
Vamos a crear otra lista desplegable, para las provincias, pero para este ejemplo, vamos a usar solo tres provincias.
Pues, queremos saber las ventas para cada producto.
¿Qué problema se presenta?
Bien, vamos a elegir un año, y, una provincia, y, queremos ver las ventas de esos productos para la selección.
Pero ¿Cómo se lo indicamos con la función SUMAR.SI.CONJUNTO?
Pues bien, no podemos.
Vamos a hacerlo para el año 2017, por lo que en la lista desplegable voy a elegir el año 2017, además, seleccionamos una provincia.
En la celda C8, vamos a escribir la función.
Escribimos el signo igual, seguido del nombre de la función, y, abrimos un paréntesis.
=SUMAR.SI.CONJUNTO(
Vamos a la hoja año_2017, y, seleccionamos la columna de total, que es el argumento rango de suma.
=SUMAR.SI.CONJUNTO(año_2017[Total]
Punto y coma, el siguiente argumento es rango para criterio1, que es la columna de año de la hoja año_2017.
=SUMAR.SI.CONJUNTO(año_2017[Total];año_2017[Año]
Punto y coma, el siguiente argumento es criterio1, pues seleccionamos el año que hemos elegido en el desplegable, en este caso, el año 2017, y, fijamos la celda.
=SUMAR.SI.CONJUNTO(año_2017[Total];año_2017[Año];$C$3
Punto y coma, el siguiente argumento es rango de criterios 2, pues seleccionamos la columna provincia de la hoja año_2017.
=SUMAR.SI.CONJUNTO(año_2017[Total];año_2017[Año];$C$3;año_2017[Provincia]
Punto y coma, el siguiente argumento es criterio2, pues seleccionamos la celda donde tenemos la provincia, ,y la fijamos también.
=SUMAR.SI.CONJUNTO(año_2017[Total];año_2017[Año];$C$3;año_2017[Provincia];Hoja3!$C$5
Punto y coma, el siguiente argumento es rango de criterios 3, pues seleccionamos la columna de productos de la hoja año_2017.
=SUMAR.SI.CONJUNTO(año_2017[Total];año_2017[Año];$C$3;año_2017[Provincia];Hoja3!$C$5;año_2017[Producto]
Como ultimo argumento es criterios3, que es el producto, pero lo dejamos en relativa, para que al copiar, se actualice al siguiente producto, cerramos paréntesis y aceptamos.
=SUMAR.SI.CONJUNTO(año_2017[Total];año_2017[Año];$C$3;año_2017[Provincia];Hoja3!$C$5;año_2017[Producto];Hoja3!B8)
Arrastramos, y, tenemos las ventas para cada producto para el año 2017, y, la provincia seleccionada.
¿Qué problema tenemos?
Pues solo podemos ver las ventas para el año 2017, quiere decir que no es dinámico.
Y, queremos ver las ventas del año que seleccionemos.
Esto lo vamos a arreglar con la función INDIRECTO.
Vamos a ello.
En dos celdas, voy a poner el año 2017 y 2018.
Voy a ir a la hoja año_2017.
Con una celda dentro del modelo, vamos a la pestaña análisis de tabla, y, copiamos el nombre de la tabla.
Lo pegamos en la celda de al lado donde hemos puesto el año 2017.
Hacemos lo mismo para el año 2018.
Si nos fijamos en la función SUMAR.SI.CONJUNTO.
=SUMAR.SI.CONJUNTO(año_2017[Total];año_2017[Año];$C$3;año_2017[Provincia];Hoja3!$C$5;año_2017[Producto];Hoja3!B8)
Vemos que estamos haciendo uso de cuatro encabezados, que son:
· Total
· Año
· Provincia
· producto
Pero, el encabezado de año, ya no nos va a ser falta, porque con la función INDIRECTO, vamos a decidir a qué hoja debemos de ir.
Pues vamos a usar la función buscarv, para buscar el año seleccionado en el rango anterior, y, que nos devuelva el nombre de la tabla, para ello:
Fijamos el rango de búsqueda, y, el valor buscado.
=BUSCARV($C$3;$E$3:$F$4;2;FALSO)
Ahora, vamos a concatenar a buscarv, el encabezado de total.
=BUSCARV($C$3;$E$3:$F$4;2;FALSO)&"[Total]"
Tenemos el nombre de la primera tabla más el encabezado total.
Copiamos a la derecha.
En este caso, ponemos el encabezado de provincia.
=BUSCARV($C$3;$E$3:$F$4;2;FALSO)&"[Provincia]"
Volvemos a copiar hacia la derecha, y, ponemos el encabezado de producto.
=BUSCARV($C$3;$E$3:$F$4;2;FALSO)&"[Producto]"
Si seleccionamos el año 2018, vemos cómo cambia.
Pues, vamos a modificar la formula anterior.
El argumento rango de suma, lo cambiamos por la función INDIRECTO, que va a tomar el valor de la celda G3.
=SUMAR.SI.CONJUNTO(INDIRECTO($G$3)
Para el argumento rango de criteros1, ponemos indirecto de la celda H3.
=SUMAR.SI.CONJUNTO(INDIRECTO($G$3);INDIRECTO($H$3);$C$5
Por último, cambiamos la referencia de producto, por indirecto, haciendo referencia a la celda I3.
=SUMAR.SI.CONJUNTO(INDIRECTO($G$3);INDIRECTO($H$3);$C$5;INDIRECTO($I$3);B8)
Aceptamos.
Para el año 2017, tenemos los mismos resultados.
Si cambiamos de año vemos como aparecen las ventas para el año 2018.
Ahora, si es dinámico.
Commenti