Если вывести данные в виде реестра, например такого
Аналогичные реестры можно формировать в 1С
или настроить отдельным отчетом (делает программист), то в Excel можно будет формировать большое количество отчетов.
Практические советы по работе с экономическими данными в Excel, читайте в моей книге "Excel для экономистов. 10 статей про отчеты, сверки, сопоставления"
Например, отчеты по соблюдению планов по затратам на обучение.
Этот же отчет по блокам
Данные по месяцам извлекаются из реестра при помощи формулы СУММЕСЛИМН
По итоговой строке "Затраты на обучение" формула выглядит так
=СУММЕСЛИМН(реестр!$I$2:$I$77;реестр!$F$2:$F$77;"ЗУ";реестр!$B$2:$B$77;L1)
формула по блокам
реестр!$I$2:$I$77 - ссылка на диапазон суммирования "сумма с НДС, руб.")
реестр!$F$2:$F$77 - ссылка на столбец с первым критерием - "статья"
ЗУ - условие 1 - код выбираемой статьи, пишется так как в столбце "статья", так как критерий записывается в виде текста, то название нужно взять в кавычки "ЗУ"
реестр!$B$2:$B$77 - ссылка на столбец со вторым критерием "месяц"
L1 - условие 2 - название месяца, так как в нашей форме отчета название месяцев перечислено в шапке, то данное условие в формулу вносится в виде ссылки на соответствующую ячейку (так удобней копировать формулу)
Символы $ нужны для копирования формулы - диапазоны постоянные для всех столбцов и при копирование не должны меняться, а ссылка на условие 2 должна меняться в соответствии с названием месяца, поэтому символа доллар нет.
В строки по ЦФО добавляется еще одно условие, т.е. формула будет выглядеть так
=СУММЕСЛИМН(реестр!$I$2:$I$77;реестр!$F$2:$F$77;"ЗУ";реестр!$B$2:$B$77;L1;реестр!$H$2:$H$77;"ЦФО 1")
Так как реестр будет обновляться ежемесячно, ссылки на диапазоны придется менять. Чтобы не менять ссылки ежемесячно, реестр можно сделать "умной таблицей". Для этого устанавливаем курсор на ячейку А1, выбираем в меню Вставка/Таблица
После этого реестр примет такой вид и при добавлении новых данных все ссылки на реестр будут автоматически расширяться
Важно, преобразовывать таблицу в умную нужно до ввода в формул в таблицу отчета, так как формула со ссылками на "умную таблицу" выглядит так
=СУММЕСЛИМН(Таблица2[сумма с НДС, руб.];Таблица2[статья];"ЗУ";Таблица2[[месяц ]];L1)
Если преобразование в "умную таблицу" будет сделано после занесения формул в отчет, то на формулах данное преобразование никак не отразится.
В первой части отчета используются формулы суммирования
В завершении статьи сделаем на основании реестра аналогичную сводную таблицу
Сводная таблица не позволяет занести планы и рассчитать суммы по кварталам без введения в реестр дополнительных столбцов, но она позволяет быстро увидеть нужные данные.
Для более быстрого поиска нужной статьи я сделала подборки:
Бюджетирование
Excel для работы с данными
Себестоимость и цены
Учет закупок и расчетов в Excel
Примеры в Excel и файлы к статьям можно найти по этой ссылке
