На основании реестра из статьи "Как систематизировать информацию о платежах в Excel" сделаем БДДС.
Например, такой
В реестре, ссылка на которой дана выше, занесены платежи только по ткущей деятельности. Чтобы разделить поступления и расходы по видам деятельности в БДДС введем в реестра дополнительный столбец и назовем его "БДДС". Всем доходам и расходам, занесенным в предыдущей статье присвоим признак "ТД" - текущая деятельность.
После заполнения реестр будет выглядеть так
Чтобы пример был наглядным добавим поступления и расходы по финансовой (ФД) и инвестиционной деятельности (ИД).
Заполненный БДДС будет выглядеть так
Формулы для заполнения таблицы
Для заполнения данных в БДДС используется формула СУММЕСЛИМН (при работе с формулой лучше использовать мастер формул, так как формула достаточно сложная)
В качестве примера рассмотрим формулу для суммирования поступлений выручки от текущей деятельности (строка 5 таблицы БДДС), которая выглядит так
=СУММЕСЛИМН('Реестра платежей'!$J:$J;'Реестра платежей'!$A:$A;B2;'Реестра платежей'!$F:$F;"Приход";'Реестра платежей'!$G:$G;"ТД";'Реестра платежей'!$H:$H;"Выручка")
Ссылка на столбцы реестра в форме $J:$J используется по той причине, что реестр платежей будет обновляться ежедневно на протяжении всего года, т.е. при использовании в формуле ссылки на конкретный диапазон ячеек возникнет необходимость постоянно изменять ссылки для увеличения диапазона . Чтобы не обновлять ссылки на формулы ежедневно и копировать формулы в следующий месяц и используется ссылка вида $J:$J. Эта ссылка берет данные из всего столбца, без указания начальной и конечной строки, знак доллара нужен для копирования формул в следующий месяц.
Условие в формуле СУММЕСЛИМН можно записывать как в виде ссылки, например, B2 - ссылка на номер месяца, так и виде текста "Выручка" (заключается в кавычке, пишется точно так же как в реестре)
Внимание!!! Чтобы формула работала необходимо заполнить все признаки (условия) на которые она ссылается. Так из предыдущего рисунка с реестром платежей видно, что наименование статей заполнены не для всех движений денежных средств. На рисунке ниже представлена таблица в которой заполнены все условия, на которые ссылается формула
БДДС можно сформировать и в виде сводной таблицы
Настройки для сводной таблицы
БДДС, заполненный формулами, и сформированный в виде сводной таблице можно использовать параллельно, так как сводная таблица позволяет быстро проверить правильно или нет работает формула.
Для более быстрого поиска нужной статьи я сделала подборки:
Бюджетирование
Excel для работы с данными
Себестоимость и цены
Учет закупок и расчетов в Excel
Примеры в Excel и файлы к статьям можно найти по этой ссылке