Цього тижня читач надіслав мені цікаву проблему з формулою, тому подумав, що поділюсь нею як виклик із формулою. Проблема в наступному:
У вас є фіксований щомісячний платіж, дата початку та задана кількість місяців. Яку формулу можна використовувати для підсумовування загальної суми виплат за роками, виходячи з наступного аркуша:
Іншими словами, яка формула працює в 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 використовується для виявлення помилки та повернення нуля. Див. Формули 闫 强, Аруна та Девіда нижче.
MAX + MIN + YEAR + MONTH
Формули Роберта та Пітера виконують майже всю роботу з МАХ і МІН, без видимості ІФ Дивовижний. Якщо ідея використання MAX та MIN для заміни IF для вас нова, ця стаття пояснює цю концепцію.
DAYS360
Функція Excel DAYS360 повертає кількість днів між двома датами на основі 360-денного року. Це спосіб обчислення місяців, заснований на ідеї, що кожен місяць має 30 днів.
SUM + DATE
Це мій неефективний (але елегантний!) Підхід із використанням функції DATE та константи масиву з числом для кожного місяця. функція DATE обертає дату кожного місяця року, використовуючи константу масиву, а логічна логіка використовується для перевірки перекриття.
