Para el siguiente video, tenemos una tabla, donde tenemos una fecha de compra, una fecha de envío, unas ventas, y, un producto.
Tenemos que crear un informe donde aparezcan por año, las ventas vendidas y enviadas, no todos los años tiene todos los meses de venta.
Los meses de venta para el año 2019 son los siguientes:
Los meses de venta para el año 2020 son los siguientes:
Los meses de venta para el año 2021 son los siguientes:
Vamos a hacerlo de tres formas diferentes, la primera de ellas, la haremos de forma matricial, donde usaremos la función FILTRAR.
Pero, lo primero que vamos a hacer es extraer los años únicos, para ello, en una celda aplicamos la función UNICOS al rango B3:B20.
=UNICOS(AÑO(B3:B20))
Ordenamos, por si no estuvieran ordenados.
=ORDENAR(UNICOS(AÑO(B3:B20)))
Ya tenemos los años únicos.
Ahora, en una celda, escribimos el signo igual, seguido de la función FILTRAR, y, abrimos un paréntesis.
=FILTRAR(
Como argumento array, seleccionamos el rango D3:D20, es decir, las ventas.
=FILTRAR(D3:D20
Como argumento include, usamos la función AÑO en el rango B3:B20, es decir, las fechas de ventas, y, lo igualamos al valor de la celda H4, es decir, al año 2019.
Cerramos paréntesis, y, aceptamos.
=FILTRAR(D3:D20;AÑO(B3:B20)=H4)
Tenemos las ventas para el año 2019.
Ahora tenemos que arrastrar a la derecha, para calcular las ventas enviadas, por lo que debemos de fijar el rango D3:D20, las ventas, el del rango B3:B20, solo debemos de fijar la fila, para que, al copiar hacia la derecha, la columna se actualice, y, toma la fecha de envío, pero cuando copiemos hacia abajo, la fila no debe de actualizarse.
=FILTRAR($D$3:$D$20;AÑO(B$3:B$20)=H4)
Arrastramos hacia la derecha, y, tenemos las ventas enviadas.
Ahora, seleccionamos ambas cantidades, y, arrastramos hacia abajo, y, tenemos las ventas y las enviadas para cada año.
Lo siguiente es calcular las ventas, no las enviadas, aunque ya tenemos el resultado, pero vamos a ver como lo podemos hacer con la función SUMAR.SI.CONJUNTO, podíamos hacerlo desde primero de año hasta final de año de cada año, pero lo vamos a complicar un poco, lo vamos a hacer desde la primera venta hasta fin de año.
Lo primero que debemos de hacer es encontrar la primera fecha de venta para cada año, para ello, voy a filtrar el rango B3:B20, siempre que el año sea igual al valor de H4.
=FILTRAR(B3:B20;(AÑO(B3:B20)=H4))
Obtenemos una matriz desbordada con las ventas para el año 2019.
Con la función MIN, me quedo con la fecha mínima.
=MIN(FILTRAR(B3:B20;(AÑO(B3:B20)=H4)))
Arrastramos y tenemos la fecha mínima de ventas para cada año.
Ahora, vamos a usar la función SUMAR.SI.CONJUNTO, para calcular las ventas desde la primera fecha de venta hasta final de año, para ello, escribimos el signo igual, seguido de la función, y, abrimos un paréntesis.
=SUMAR.SI.CONJUNTO(
Como argumento rango de suma, seleccionamos el rango D3:D20, las ventas.
=SUMAR.SI.CONJUNTO(D3:D20;
Como argumento rango de criterios 1, seleccionamos el rango B3:B20.
=SUMAR.SI.CONJUNTO(D3:D20;B3:B20;
Como argumento criterio 1, entre comillas dobles, ponemos “>=”, y, lo concatenamos con H9, es decir la primera fecha de venta.
=SUMAR.SI.CONJUNTO(D3:D20;B3:B20;">="&H9
Ahora, como argumento rango de criterios 2, volvemos a seleccionar las fechas de ventas.
=SUMAR.SI.CONJUNTO(D3:D20;B3:B20;">="&H9;B3:B20;
Como argumento criterio 2, debemos de especificar que es menor o igual al final de año, pero ¿cómo le indicamos el final de año?, tenemos una función llamada FIN.MES, que nos devuelve el final de un mes de un mes especificado, esta función tiene dos argumento, que es fecha inicial, que es la celda con la primera venta, y, meses, bien, si a 12 le restamos el numero de meses de la primera fecha de venta, tendremos el numero de meses a sumar, quedaría:
=SUMAR.SI.CONJUNTO(D3:D20;B3:B20;">="&H9;B3:B20;"<="&FIN.MES(H9;12-MES(H9)))
Cerramos paréntesis, y, aceptamos, y, vemos que tenemos el mismo resultado que el obtenido anteriormente.
Antes de arrastrar, fijamos las siguientes referencias.
=SUMAR.SI.CONJUNTO($D$3:$D$20;B$3:B$20;">="&H9;B$3:B$20;"<="&FIN.MES(H9;12-MES(H9)))
Ya tenemos las ventas desde la primera fecha de venta hasta final de año.
Lo siguiente es calcular las ventas enviadas, pero, igual que antes desde la primera fecha de venta hasta final de año, pero en esta ocasión, lo haremos con la función SUMAPRODUCTO.
Para calcular la primera fecha de venta enviada, es la misma función que hemos usado anteriormente, pero debemos de cambiar el rango B3:B20, por C3:C20.
=MIN(FILTRAR($C$3:$C$20;(AÑO($C$3:$C$20)=H4)))
Pues, ponemos la función SUMAPRODUCTO, y, abrimos un paréntesis.
=SUMAPRODUCTO(
Abrimos un paréntesis, para poner la primera condición, que es que el rango C3:C20, debe de ser mayor o igual al valor de H15, es decir, la primera fecha de envío, y, cerramos paréntesis.
=SUMAPRODUCTO((C3:C20>=H15)
Ponemos el símbolo de asterisco (*), que ya sabemos que es igual al operador lógico Y, es decir, que devuelve VERDADERO, si todas las condiciones se cumplen.
Abrimos un paréntesis para poner la segunda condición, que es que el rango C3:C20, debe de ser menor o igual que el final de año, el cual, lo calculamos como lo hemos hecho para el calculo de la primera fecha de venta.
Cerramos paréntesis.
=SUMAPRODUCTO((C3:C20>=H15)*(C3:C20<=FIN.MES(H15;12-MES(H15)))
Ponemos punto y coma, y, como argumento matriz3, seleccionamos el rango D3:D20, es decir, las ventas, cerramos paréntesis, y, aceptamos.
=SUMAPRODUCTO((C3:C20>=H15)*(C3:C20<=FIN.MES(H15;12-MES(H15)));D3:D20)
Tenemos las ventas desde la primera fecha de envío del año 2019 hasta final de año.
Fijamos las siguientes referencias y arrastramos.
=SUMAPRODUCTO(($C$3:$C$20>=H15)*($C$3:$C$20<=FIN.MES(H15;12-MES(H15)));$D$3:$D$20)
Ya tenemos las ventas para el resto de los años.
Vemos que tenemos los mismos resultados que para el primer ejemplo.
Pues, ya tenemos nuestro ejercicio realizado.
Comments