Распределите значения по месяцам и категориям расходов

Question

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

Обратите внимание, что Начальный месяц и Конечный месяц отображаются в виде мм-гггг.

Для уточнения расчетов:

• Во второй таблице есть столбец для каждого месяца, который содержится в любом из интервалов событий.
• Расходы на каждое событие равномерно распределяются по месяцам для этого события.

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

  Так, например, скажем, оба мероприятия были связаны с рекламой, а событие А стоило 600 долларов. Ежемесячное распределение события А составило бы 200 долларов, потому что это трехмесячное событие. Таким образом, сумма рекламы за апрель в таблице 2 составит 200 долларов США для события А и 500 долларов США для события Б, или 700 долларов США.

Answer 1

Честно говоря, первая таблица может быть удобной для чтения человеком, но не подходит для компьютера, чтобы вычислять или делать какую-то статистику. Чтобы получить таблицу 2, идеал прост - получить разделенное значение для каждого месяца для каждого события для каждой категории расходов (и сохранить их в несколько вспомогательных ячеек), а затем суммировать их соответствующим образом. Чтобы получить разделенные расходы, нам нужно сначала получить флаг, если данный месяц попадает в диапазон времени.

Я полагаю, вам нужно двенадцать месяцев. Я также предполагаю, что ячейка для начального месяца имеет дату первого дня этого месяца (например, 2017-04-01 для B2 , 04/2017), и, соответственно, ячейка для конечного месяца имеет дату последнего дня этого месяца (например, 2017 -06-30 для C2 , 06/2017).

1. Флаг: использует даты от H1 до S1 качестве даты для каждого месяца, т.е. 01/2017 (2017-01-01), 02/2017 (2017-02-01), ..., 12/2017 (2017-12-01), и использует левые ячейки столбца H S качестве флага, если этот месяц находится в диапазоне. В H2 используется формула =AND($B2<=H$1,H$1<=$C2) . Предупреждение: обратите внимание на различное использование $ в формуле, чтобы иметь абсолютную ссылку на ячейки, чтобы можно было легко копировать. Это означает, что если текущий месяц (в верхней строке текущего столбца) находится между началом и концом месяца, возвращает true, в противном случае возвращает false. Затем скопируйте эту формулу вправо до столбца S и скопируйте до последнего из событий.

2. Count: Использует столбец T качестве счетчика месяцев, между которыми расходы должны быть разделены. Формула в T2: =COUNTIF(H2:S2,TRUE) , затем скопируйте формулу вниз.

3. Разделенные расходы для каждой категории: Опять же, обратите внимание на использование $ в следующей формуле.

   3.1 1-я категория: использует столбец U AF (1-я группа из 12 столбцов) в качестве разделенного расхода для 1-й категории, то есть "Реклама"; Формула U2 is =IF(H2,$E2/$T2,0) , означает, что если флаг этого месяца истинен (т. Е. Этот месяц находится в диапазоне), возвращает разделенные расходы (Adv. Расход / количество месяцев), в противном случае ноль. Затем скопируйте вправо (до столбца AF), скопируйте вниз.

   3.2 2-я категория: использует столбец AG AR (2-я группа из 12-ти столбцов) в качестве разделенного расхода для 2-й категории, то есть "Ярмарки и семинары"; Формула AG2: =IF(H2,$F2/$T2,0) . Затем скопируйте право (до AR) и скопируйте вниз.

   3.3 3-я категория: использует столбец AS BD (3-я группа из 12 столбцов) в качестве разделенного расхода для 3-й категории, то есть "Проживание"; Формула AS2 =IF(H2,$G2/$T2,0) . Затем скопируйте право (до BD) и скопируйте вниз.

4. Подведите их итог: во второй таблице за каждый месяц сложите соответствующие расходы по всем событиям.