На этой неделе один читатель прислал мне интересную задачу с формулой, и я решил поделиться ею как задачей с формулой. Проблема вот в чем:
У вас есть фиксированный ежемесячный платеж, дата начала и определенное количество месяцев. Какую формулу можно использовать для суммирования общих выплат по годам на основе следующей таблицы:
Другими словами, какая формула работает в E5, скопированная в I5, чтобы получить сумму за каждый указанный год?
Я сам придумал формулу, но мне тоже хотелось бы увидеть ваши идеи. Если вам интересно, оставьте комментарий к предложенной вами формуле.
При желании вы можете использовать в формуле следующие именованные диапазоны: мос (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 используется для обнаружения ошибки и возврата нуля. См. Формулы 闫 强, Аруна и Дэвида ниже.
MAX + MIN + YEAR + MONTH
Формулы Роберта и Питера выполняют почти всю работу с MAX и MIN, при этом IF не видно. Удивительный. Если идея использования MAX и MIN вместо IF для вас нова, эта статья объясняет эту концепцию.
DAYS360
Функция Excel DAYS360 возвращает количество дней между двумя датами на основе 360-дневного года. Это способ расчета месяцев, основанный на идее, что в каждом месяце 30 дней.
SUM + DATE
Это мой неэффективный (но элегантный!) Подход с использованием функции DATE и константы массива с числом для каждого месяца. функция ДАТА раскручивает дату для каждого месяца года, используя константу массива, а для проверки перекрытия используется логическая логика.
