Un lector me envió un problema de fórmula interesante esta semana, así que pensé en compartirlo como un desafío de fórmula. El problema es este:
Tiene un pago mensual fijo, una fecha de inicio y un número determinado de meses. ¿Qué fórmula puede usar para sumar los pagos totales por año, según la siguiente hoja de trabajo?
En otras palabras, ¿qué fórmula funciona en E5, copiada a I5, para obtener una suma por cada año que se muestra?
Yo mismo se me ocurrió una fórmula, pero también me encantaría ver tus ideas. Si te interesa deja un comentario con la fórmula que propongas.
Puede usar los siguientes rangos con nombre en su fórmula si lo desea: mos (C5), amount (C6), start (C7), end (C8).
Puede descargar la hoja de trabajo a continuación.
Respuesta (haga clic para expandir)¡Tantas fórmulas geniales! Gracias a todos los que se tomaron el tiempo de enviar una respuesta. A continuación se muestran mis pensamientos divagantes sobre el problema y algunas de las soluciones a continuación.
Nota: Nunca aclaré cómo se deben manejar los límites de los meses. Solo estaba siguiendo otra hoja de trabajo como ejemplo. La información clave son 30 pagos, a partir del 1 de marzo: 10 pagos en 2017, 12 pagos en 2018 y 8 pagos (el saldo) en 2019.
Por lo tanto, si tiene dificultades para comprender cómo podría intentar resolver un problema como este, concéntrese primero en los pagos. Una vez que sepa cuántos pagos hay en un año, puede simplemente multiplicar ese número por la cantidad y listo.
Entonces, ¿cómo podría encontrar la cantidad de pagos en un año determinado? En los comentarios a continuación, encontrará muchas buenas ideas. Hay varios patrones que noté y he enumerado algunos a continuación. Este es un trabajo en progreso …
Patrones de diseño
IF + AND/OR + YEAR + MONTH
IF es un recurso seguro en muchas fórmulas y se usa en muchas de las fórmulas sugeridas para determinar si el año de interés está "dentro de los límites" de las fechas de inicio y finalización. En muchos casos, IF se combina con OR o AND para mantener las fórmulas compactas.
IFERROR + DATEDIF + MAX + MIN
DATEDIF puede devolver la diferencia entre dos fechas en meses, por lo que la idea aquí es usar MAX y MIN (por brevedad, en lugar de IF) para calcular una fecha de inicio y una fecha de finalización para cada año, y dejar que DATEDIF obtenga los meses intermedios. DATEDIF arroja un error #NUM cuando la fecha de inicio no es menor que la fecha de finalización, por lo que IFERROR se usa para detectar el error y devolver cero. Vea las fórmulas de 闫 强, Arun y David a continuación.
MAX + MIN + YEAR + MONTH
Las fórmulas de Robert y Peter hacen casi todo el trabajo con MAX y MIN, sin IF a la vista. Increíble. Si la idea de usar MAX y MIN para reemplazar IF es nueva para usted, este artículo explica el concepto.
DAYS360
La función DÍAS360 de Excel devuelve el número de días entre dos fechas según un año de 360 días. Es una forma de calcular meses basada en la idea de que cada mes tiene 30 días.
SUM + DATE
Este es mi enfoque ineficiente (¡pero elegante!) Utilizando la función FECHA y una constante de matriz con un número para cada mes. la función FECHA hace girar una fecha para cada mes de un año usando una constante de matriz, y la lógica booleana se usa para verificar la superposición.
