Как рассчитать ежемесячные расходы из диапазонов дат

Question

Я даже не знаю, как правильно сформулировать этот заголовок. Заранее благодарю за терпение.

У меня есть несколько счетов, которые охватывают разные периоды времени, и мне нужно придумать ежемесячные суммы.

Например:

10/09/2018 - 12/11/2019    Water    $ 77.58
11/15/2018 - 01/14/2019    Sewer    $124.12
12/01/2018 - 01/31/2019    Trash    $ 43.14
12/11/2018 - 02/14/2019    Water    $ 68.99
02/01/2019 - 03/31/2019    Trash    $ 43.14

Каждый из них охватывает 2 месяца, но с разными точками начала / окончания, а не в начале / конце месяцев.

Я хотел бы ввести вышеупомянутое в таблицу и затем вычислить, сколько воды + канализация + мусор было в январе, феврале, марте и т.д.

Моей первой мыслью было рассчитать количество дней, охватываемых периодом, а затем рассчитать стоимость за день. Но потом я застрял, потому что я не знаю, как перейти к следующему шагу января всего ....

Я не уверен в правильном подходе к этой проблеме. Я был бы признателен за любые советы по указателям, ссылки на веб-страницы, как я на самом деле называю / классифицирую эту проблему как - я даже не знаю слов.

Спасибо.

Answer 1

Используя ваш набор данных, я хотел бы предложить эту формулу, чтобы получить общие расходы на воду, канализацию и мусор в январе-марте.

=SUM(SUMIFS($D$2:$D$6,$C$2:$C$6,{"Water","Sewer","Trash"},$A$2:$A$6,">=01/01/18",$B$2:$B$64,"<=03/31/19"))

Чтобы сделать формулу динамичной, я хотел бы предложить использовать ссылки на ячейки для начальной и конечной дат в качестве критериев.

И ваша Формула должна быть:

=SUM(SUMIFS($D$2:$D$6,$C$2:$C$6,{"Water","Sewer","Trash"},$A$2:$A$6,">="&$E$1,$B$2:$B$6,"<="&$F$1))

NB

• Поскольку ваш набор данных имеет даты между 2018 и 2019, так что я использовал этот период в первой формуле. В противном случае это может быть с 19 января по 19 марта.

• E1 и F1 - ячейка содержит начальную и конечную даты.

Отредактировано:

Делая небольшие изменения в E1 и F1 для начальной и конечной даты, можно использовать ту же формулу, чтобы получить и другие значения.

• Например, чтобы получить общие расходы (вода + канализация + мусор) только за январь, напишите 01/01/19 в E1 и 01/31/19 в F1 .

• Другой должен, суммарные расходы на воду за 3 месяца, для этого просто исключите Sewer & Trash из списка и оставьте только {"Water"}, или вы можете написать так же:$C$2:$C$6,"=Water" ,

• При необходимости измените ссылки на ячейки в формуле.

Answer 2

Ты на правильном пути. Вот макет с использованием вспомогательных ячеек и столбцов, чтобы немного упростить ситуацию:

Вот формулы на этом листе:

Вот формулы в виде текста для копирования и вставки.

Начало и конец месяца:
=DATEVALUE( G1 & " 1, " & F1)
=EOMONTH(F2,0)

Стоимость в день: =D3/DAYS(B3,A3)

Находится ли начало месяца в объявленном диапазоне дат? Количество дней с начала этого месяца до конца выставленного диапазона дат или конца месяца, в зависимости от того, что наступит раньше. Умножается на стоимость за день.

=IF(AND(F$2>=$A3,F$2<=$B3),$E3*DAYS(IF($B3<G$2,$B3,G$2),F$2) +1,"")

Конец месяца в диапазоне ... с дополнительным ограничением на то, что начало месяца также не входит в этот диапазон дат. Если это так, то расчетный диапазон охватывает весь месяц и уже вычислен в столбце начала месяца.

=IF(AND(G$2>=$A3,G$2<=$B3,$A3>F$2),$E3*DAYS(G$2,IF($A3>F$2,$A3,F$2)) +1,"")

Итоговые итоги смотрите в столбцах начала и конца месяца. Если есть номер, это часть стоимости за этот месяц. Суммируйте оба столбца для каждой записи, соответствующей заголовку этой ежемесячной утилиты (строки):

Вода =SUMIF(C3:C7,H2,F3:G7)
Канализация =SUMIF(C4:C8,H3,F4:G8)
Корзина =SUMIF(C5:C9,H4,F5:G9)