☰

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

tag-icon • tag-icon microsoft-excel
Распределите значения по месяцам и категориям расходов

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

Скриншот

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

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

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

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

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

решение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. Считать: Использует столбец Tкак счетчик месяцев, среди которых расход должен быть разделен. Формула в T2, =COUNTIF(H2:S2,TRUE)затем скопируйте формулу вниз.

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

    3.1 1-я категория: Использует столбец U( AF1-я группа из 12 столбцов) как разделенные расходы для 1-й категории, т. е. "Реклама"; Формула , означает, U2что =IF(H2,$E2/$T2,0)если флаг этого месяца истинен (т. е. этот месяц находится в диапазоне), возвращает разделенные расходы (расходы на рекламу/количество месяцев), в противном случае ноль. Затем копируем вправо (до столбца 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. Подытожим их: В таблице 2 для каждого месяца просуммируйте соответствующие расходы по всем событиям.

Связанный контент