Tengo una hoja de cálculo que utiliza la función "sumif" y cuenta los meses en rangos de fechas;
=SUMIFS(Cantidad;DPIF;">=1/1/2021";DPIF;"<=31/1/2021")
Esta fórmula se repite doce veces (para cada mes) y con el cambio de 2021 a 2022 ahora tengo que cambiar, manualmente, los rangos de fechas (años) en las doce fórmulas.
Intenté cambiar solo el año (2021 en la fórmula) para hacer referencia a otra celda, pero Excel ya no lo ve como una fecha.
¿Cómo puedo hacer que mi hoja de cálculo sea utilizable de un año a otro?
Respuesta1
Enfoque 1
En lugar de codificar fechas de inicio/finalización en sus SUMIFSfunciones, generelas usando fórmulas de Excel y luego haga referencia a las celdas que contienen estos valores de fecha calculados. ElDATEyEOMONTHLas funciones se pueden utilizar como se muestra en la captura de pantalla siguiente.
Luego, su SUMIFSfórmula para enero se puede cambiar a:
=SUMIFS(Amount;DPIF;">="&Sheet1!B3;DPIF;"<="&Sheet1!C3)
Para el mes de enero, la celda Sheet1!B3tiene la fecha de inicio del mes y Sheet1!C3la fecha de finalización. Cambie estas referencias de celda a dondequiera que coloque los valores de fecha calculados. Para febrero, las celdas de fecha de inicio/finalización son Sheet1!B4y Sheet1!C4, para marzo Sheet1!B5y Sheet1!C5, etc. Si sus 12 SUMIFSfórmulas mensuales están organizadas en un rango de 12 filas por 1 columna, podrá copiar la fórmula de enero a los siguientes 11 meses. .
Cada uno de los símbolos (&) en las fórmulas concatena un operador de comparación con el valor en la celda a la que se hace referencia, por lo que en la fórmula modificada ">="&Sheet1!B3es efectivamente equivalente a ">=01/01/2024"porque Sheet1!B3entrega el valor de fecha del 01/01/2024.
Para cambiar el año simplemente cambie el valor del año en la celda correspondiente - celda Sheet1!B1en la captura de pantalla. Todas sus 12 SUMIFSfórmulas mensuales se referirán automáticamente a las fechas de inicio y fin del nuevo año, eliminando la necesidad de editar las fórmulas.
Este enfoque también gestiona la fecha de fin de mes de febrero en los años bisiestos (como se muestra en la captura de pantalla de la celda C4).
Enfoque 2
Un enfoque alternativo es convertir los DPIFvalores de fecha en valores numéricos de mes agregando un rango adicional a los Amount/DPIFrangos existentes usando elMONTHfuncionan como
=MONTH(date)
donde daterepresenta una celda de fecha en el DPIFrango.
Llamar a estos valores mensuales MPIFsignifica que luego podría usar fórmulas como
=SUMIFS(Amount;MPIF;1)
=SUMIFS(Amount;MPIF;2)
...
=SUMIFS(Amount;MPIF;12)
sin tener que preocuparte por el año en absoluto.
Este enfoque mensual supone que no tiene varios años en sus rangos de datos. Si lo hace, sumará los datos de todos los de enero, todos los de febrero, etc., lo cual posiblemente no sea lo que desea.
En este caso, una mejora de la solución es agregar valores de año a los datos en un YPIFrango usando elYEARfunción para obtener estos valores. Luego agregaría un segundo criterio a las SUMIFSfórmulas para incluir estos valores de año, por ejemplo
=SUMIFS(Amount;MPIF;1;YPIF;2024)
Por supuesto, esto significa que todavía tendrías 12 fórmulas para modificar cada año, aunque las ediciones sean un poco más simples que las que tienes actualmente. Para evitar esto, coloque el valor del año en su propia celda y haga referencia a él usando una fórmula como
=SUMIFS(Amount;MPIF;1;YPIF;Sheet1!$B$1)
Enfoque 3
No es uno para recomendar, pero se incluye para que esté completo, cambie sus fórmulas a
=SUMIFS(Amount;DPIF;">=01/01/"&Sheet1!$B$1;DPIF;"<=31/01/"&Sheet1!$B$1)
=SUMIFS(Amount;DPIF;">=01/02/"&Sheet1!$B$1;DPIF;"<=28/02/"&Sheet1!$B$1)
etc.
Se trata de un trabajo de edición único que funcionará durante los años 2021, 2022 y 2023, pero omitirá el 29/02/2024 de la suma mensual de febrero en 2024, a menos que alguien recuerde hacer un cambio, y posiblemente causará problemas en 2025 si no se modifica. atrás.
Respuesta2
- Coloque las dos fechas completas (no solo el año) cada una en una celda separada. Luego, en su fórmula, reemplace el valor de fecha con referencias a las celdas
- para cambiar todas las fórmulas a la vez, ejecute "reemplazar todo" dentro de su hoja
- el próximo año, cambie solo los valores de fecha en las dos celdas separadas