Un lecteur m'a envoyé un problème de formule intéressant cette semaine, alors j'ai pensé le partager comme un défi de formule. Le problème est le suivant:
Vous disposez d'un paiement mensuel fixe, d'une date de début et d'un nombre de mois donné. Quelle formule pouvez-vous utiliser pour additionner le total des paiements par année, sur la base de la feuille de calcul suivante:
En d'autres termes, quelle formule fonctionne dans E5, copiée dans I5, pour obtenir une somme pour chaque année affichée?
J'ai moi-même proposé une formule, mais j'aimerais aussi voir vos idées. Si vous êtes intéressé, laissez un commentaire avec la formule que vous proposez.
Vous pouvez utiliser les plages nommées suivantes dans votre formule si vous le souhaitez: mos (C5), montant (C6), début (C7), fin (C8).
Vous pouvez télécharger la feuille de travail ci-dessous.
Réponse (cliquez pour développer)Tant de bonnes formules! Merci à tous ceux qui ont pris le temps de soumettre une réponse. Voici mes réflexions décousues sur le problème et certaines des solutions ci-dessous.
Remarque: je n'ai jamais précisé comment les limites de mois devraient être gérées. Je suivais juste une autre feuille de calcul à titre d'exemple. Ces informations clés sont 30 paiements, à compter du 1er mars: 10 paiements en 2017, 12 paiements en 2018 et 8 paiements (le solde) en 2019.
Donc, si vous avez du mal à comprendre comment vous pourriez essayer de résoudre un problème comme celui-ci, concentrez-vous d'abord sur les paiements. Une fois que vous savez combien de paiements sont effectués par an, vous pouvez simplement multiplier ce nombre par le montant et vous avez terminé.
Alors, comment pouvez-vous trouver le nombre de paiements dans une année donnée? Dans les commentaires ci-dessous, vous trouverez de nombreuses bonnes idées. J'ai remarqué plusieurs modèles et j'en ai énuméré quelques-uns ci-dessous. Ceci est un travail en cours de réalisation…
Modèles de conception
IF + AND/OR + YEAR + MONTH
IF est une veille fiable dans tant de formules, et il est utilisé dans de nombreuses formules suggérées pour déterminer si l'année d'intérêt est "dans les limites" des dates de début et de fin. Dans de nombreux cas, IF est combiné avec OR ou AND pour garder les formules compactes.
IFERROR + DATEDIF + MAX + MIN
DATEDIF peut renvoyer la différence entre deux dates en mois, donc l'idée ici est d'utiliser MAX et MIN (par souci de brièveté, au lieu de IF) pour calculer une date de début et une date de fin pour chaque année, et laisser DATEDIF obtenir les mois entre les deux. DATEDIF renvoie une erreur #NUM lorsque la date de début n'est pas inférieure à la date de fin, donc IFERROR est utilisé pour intercepter l'erreur et renvoyer zéro. Voir les formules de 闫 强, Arun et David ci-dessous.
MAX + MIN + YEAR + MONTH
Les formules de Robert et Peter font presque tout le travail avec MAX et MIN, sans IF en vue. Incroyable. Si l'idée d'utiliser MAX et MIN pour remplacer IF est nouvelle pour vous, cet article explique le concept.
DAYS360
La fonction Excel DAYS360 renvoie le nombre de jours entre deux dates sur la base d'une année de 360 jours. C'est une façon de calculer les mois basée sur l'idée que chaque mois a 30 jours.
SUM + DATE
C'est mon approche inefficace (mais élégante!) Utilisant la fonction DATE et une constante de tableau avec un nombre pour chaque mois. la fonction DATE lance une date pour chaque mois d'une année à l'aide d'une constante de tableau et la logique booléenne est utilisée pour vérifier le chevauchement.
