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 SUMIFS
funciones, generelas usando fórmulas de Excel y luego haga referencia a las celdas que contienen estos valores de fecha calculados. ElDATE
yEOMONTH
Las funciones se pueden utilizar como se muestra en la captura de pantalla siguiente.
Luego, su SUMIFS
fórmula para enero se puede cambiar a:
=SUMIFS(Amount;DPIF;">="&Sheet1!B3;DPIF;"<="&Sheet1!C3)
Para el mes de enero, la celda Sheet1!B3
tiene la fecha de inicio del mes y Sheet1!C3
la 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!B4
y Sheet1!C4
, para marzo Sheet1!B5
y Sheet1!C5
, etc. Si sus 12 SUMIFS
fó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!B3
es efectivamente equivalente a ">=01/01/2024"
porque Sheet1!B3
entrega 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!B1
en la captura de pantalla. Todas sus 12 SUMIFS
fó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 DPIF
valores de fecha en valores numéricos de mes agregando un rango adicional a los Amount/DPIF
rangos existentes usando elMONTH
funcionan como
=MONTH(date)
donde date
representa una celda de fecha en el DPIF
rango.
Llamar a estos valores mensuales MPIF
significa 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 YPIF
rango usando elYEAR
función para obtener estos valores. Luego agregaría un segundo criterio a las SUMIFS
fó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