Читател ми изпрати интересен проблем с формулата тази седмица, така че помислих, че ще го споделя като предизвикателство за формула. Проблемът е следният:
Имате фиксирано месечно плащане, начална дата и определен брой месеци. По каква формула можете да сумирате общите плащания по години въз основа на следния работен лист:
С други думи, каква формула работи в E5, копирана в I5, за да се получи сума за всяка показана година?
Сам измислих формула, но и аз бих се радвал да видя вашите идеи. Ако се интересувате, оставете коментар с формулата, която предлагате.
Можете да използвате следните именувани диапазони във вашата формула, ако искате: mos (C5), количество (C6), начало (C7), край (C8).
Можете да изтеглите работния лист по-долу.
Отговор (щракнете за разгъване)Толкова много страхотни формули! Благодаря на всички, които отделиха време да изпратят отговор. По-долу са моите разтърсващи мисли по проблема и някои от решенията по-долу.
Забележка: Никога не съм изяснявал как трябва да се обработват границите на месеца. Просто следвах друг работен лист като пример. Ключовата информация за тях е 30 плащания, започващи на 1 март: 10 плащания през 2017 г., 12 плащания през 2018 г. и 8 плащания (салдото) през 2019 г.
Така че, ако се мъчите да разберете как бихте могли да се опитате да разрешите проблем като този, фокусирайте се първо върху плащанията. След като разберете колко плащания са за една година, можете просто да умножите този брой по сумата и сте готови.
И така, как бихте могли да намерите броя на плащанията през дадена година? В коментарите по-долу ще намерите много добри идеи. Има няколко модела, които забелязах и изброих няколко по-долу. Това е в процес на работа …
Дизайнерски модели
IF + AND/OR + YEAR + MONTH
IF е надежден режим на готовност в толкова много формули и се използва в много от предложените формули, за да разбере дали годината на интерес е "в границите" на началната и крайната дата. В много случаи IF се комбинира с OR или AND, за да запази формулите компактни.
IFERROR + DATEDIF + MAX + MIN
DATEDIF може да върне разликата между две дати в месеци, така че идеята тук е да се използват MAX и MIN (за краткост, вместо IF), за да се изчисли начална дата и крайна дата за всяка година и нека DATEDIF получи месеците между DATEDIF изхвърля грешка #NUM, когато началната дата не е по-малка от крайната дата, така че IFERROR се използва за улавяне на грешката и връщане на нула. Вижте формулите на 闫 强, Arun и David по-долу.
MAX + MIN + YEAR + MONTH
Формулите на Робърт и Питър вършат почти цялата работа с МАКС и МИН, без да се вижда АКО. Удивително. Ако идеята за използване на MAX и MIN за заместване на IF е нова за вас, тази статия обяснява концепцията.
DAYS360
Функцията Excel DAYS360 връща броя дни между две дати въз основа на 360-дневна година. Това е начин за изчисляване на месеците въз основа на идеята, че всеки месец има 30 дни.
SUM + DATE
Това е моят неефективен (но елегантен!) Подход, използващ функцията DATE и константа на масив с число за всеки месец. функцията DATE завърта дата за всеки месец от годината, използвайки константа на масив, и логическа логика се използва за проверка на припокриване.
