Инструкция: создание управленческих отчётов с помощью «Google Таблиц»

На примере отчёта о движении денежных средств (ДДС) мы покажем, как организовать отчётность в «Google Таблицах» так, чтобы настройка не занимала много времени.

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

Процесс довольно трудоёмкий и занимает много времени, из-за чего состав и вид отчёта часто ограничен и появляется риск появления ошибки.

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

Разбираться, как это сделать, будем на примере реального кейса. Рассмотрим некоторые основы формул в «Google Таблицах», организационную составляющую по работе с данными и бизнес-анализа.

Чтобы вникнуть и разобраться, потребуется примерно час.

Чтобы отчётность занимала минимум времени, нужно организовать её как модель. Модель — это статичная форма, которая меняется при изменении параметров или исходных данных. Забегая вперед, сразу покажем, как выглядит конечная цель — создание модели отчёта.

По строкам выводятся движения денег на каждый месяц года. Год регулируется в верхней части выбором из выпадающего списка. Также можно посмотреть в разрезе направления и типы оплаты.

В качестве инструмента будем использовать «Google Таблицы». Итоговое решение будет представлять собой модель, которая зависит от исходных данных. Приводятся формулы, расчёты и ссылка на готовую таблицу.

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

Одно юрлицо, два расчётных счёта, касса, наличные и карта физлица.

У нас есть два решения:

Поскольку все направления смежны, выберем первый вариант и введём единые справочники статей ДДС.

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

Нормальным количеством будет считаться 30–40 самостоятельных групп. Если вы один раз за период покупали покрышки для авто, то не стоит создавать для этой операции отдельную группу, отнесите текущий расход, например, к группе «Расходы на обслуживание авто».

Постарайтесь сделать наиболее полный и достаточный список с учетом стратегии компании на год вперёд. Список статей ДДС будет определять форму отчёта и изменение его структуры исказит результаты в динамике за длительный промежуток времени.

Создайте список из выделенных в банковских выписках групп. Далее постарайтесь изменить их названия так, чтобы любой сотрудник при совершении учётных операций мог однозначно определять их принадлежность к этим группам. Например, так, как показано на рисунке, группировать нельзя:

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

Список групп после работы с терминологией превращается в статьи движения денег. Теперь можно проектировать на их основе финансовый учёт и форму отчёта.

Статьи движения денег будут основой для отчета:

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

Также можно будет анализировать отчёт о движении денег в разрезе определённого счета или кассы.

Теперь определим структуру таблицы, где будут отчёт и данные, на основе которых она строится. По сценарию решаемого кейса, учёт финансов двух интернет-магазинов ведётся в системе «Мой склад», а для барбершопа и салона красоты используется YClients. Есть возможность делать выгрузки данных за период.

Наша задача — создать лист с аналогичными столбцами в таблице с отчётом. Это нужно, чтобы сделать выгрузку, скопировать из неё данные и вставить на этот лист ниже ранее добавленной информации.

Вот так выглядит часть выписки из системы «Мой склад»:

Вот такой вид примет лист, содержащий данные из системы «Мой склад», который будет служить основой для модели отчетности:

Аналогичные действия необходимо проделать для выгрузки из YClients. Ниже представлена часть выгрузки из системы:

Только важно учесть, что выгрузка из системы «Мой склад» была единой для двух интернет-магазинов, а в YClients для каждой точки есть свой личный кабинет и свои выгрузки. Чтобы разделять финансы разных заведений, добавляем одноименный столбец в начале таблицы:

Чтобы при загрузке данных пользователь выбирал определенное значение наименования заведения, создаём в столбце «Заведение» выпадающий список. Он нужен в первую очередь для того, чтобы исключить вероятность ошибки пользователя. Если будет ошибка в слове, то некорректно заполнится отчёт.

Выделяем диапазон ячеек, где хотим видеть выпадающий список.

В меню на вкладке «Данные» выбираем пункт «Проверка данных».

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

Указываем диапазон на листе справочника, который нам понадобится для аналогичных справочных данных.

Получаем результат.

В классическом варианте отчёт о движении денег показывает поступления и события по категориям за выбранный период:

В текущем кейсе мы разберём вариант реализации отчёта в динамике:

Форма отчёта рассчитана на год. В отчёте по столбцам располагаются суммы на каждый месяц года. Периоды легко сравнить между собой, поэтому все отклонения видны наглядно.

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

Самое время открыть таблицу с примером, чтобы разобраться в тонкостях. Посмотрите на вид формы отчета, она расположена на листе Cash Flow.

Мы подготовили листы для исходных данных, справочника и формы отчета. Следующая задача — создать модель. То есть нужно сделать так, чтобы форма отчета обновлялась автоматически при изменении информации на листах с данными.

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

Разберем на примере легкого кейса немного теории по «Google Таблицам». Переходите на лист «Данные», где расположен массив с информацией.

Лист «Отчет» — укрупненная модель отчёта статьи. Мы специально упростили, чтобы показать основы «Google Таблиц», которые понадобятся для реализации задачи в целом.

Лист «Данные» содержит информацию учёта финансов. Неважно, вели учет в таблице или скопировали из системы, мы будем использовать формулы, чтобы на их основе сделать отчет.

Лист «Отчет» состоит из двух таблиц, что для нас будет означать два этапа работы с формулой. Сперва разберём простой вариант, а далее усложним его.

Информация на листе «Данные» представляет собой множество финансовых операций. Каждая из них имеет поля: «Дата», «Статья ДДС», «Сумма».

Начнём с упрощённой версии отчёта и выведем суммы расходов по категориям.

Первым делом расскажем про её вид.

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

Дублируем ссылку на таблицу ниже:

Таблица доступна только для просмотра. Сохраните копию на свой «Google Диск»: Файл → Создать копию.

Выделяем ячейку D5 таблицы отчета и вводим =СУММЕСЛИМН(.

Указываем диапазон суммирования. Для этого при активной формуле перемещаемся на лист «Данные» и выделяем столбец D:

Указываем диапазон критериев. В нашем случае это столбец «Статья ДДС» на листе «Данные»:

Указываем критерий. Чтобы сделать формулу универсальной, нужно сослаться на наименование статьи ДДС в форме отчета. Таким образом мы ищем указанное значение в таблице с данными и суммируем все значения, равные критерию.

Закрываем скобку и нажимаем на клавиатуре «Enter»:

Протягиваем формулу и проверяем полученные значения:

Поле «Итого» заполняем формулой СУММ, которая объединяет значение из вышележащих ячеек.

Следующая задача отличается только дополнительными критериями. Предыдщий отчет выводился в разрезе статей ДДС по всем данным. Теперь нам нужно разбить эти суммы на интервалы в виде месяцев года.

На листе «Данные» столбец В содержит даты. Наша задача — получить из ячейки с датой порядковый номер месяца. Для этого будем использовать одноименную формулу МЕСЯЦ.

На листе «Данные» правее таблички добавляем столбец и называем его «Месяц».

В ячейке Е4 вводим формулу =МЕСЯЦ( и в качестве единственного аргумента ссылаемся на ячейку с датой В4. Закрываем скобку, протягиваем формулу вниз и получаем искомые значения.

Теперь у нас есть столбец с критериями, а сами критерии содержатся в заголовке отчёта, можно заполнить отчёт данными.

В ячейке D14 вводим формулу СУММЕСЛИМН по аналогии с простым отчётом, который мы разобрали выше:

Вводим второй диапазон критериев. Выделяем столбец Е листа «Данные»:

Задаём в качестве критерия ссылку на форму отчёта:

Теперь по сценарию предыдущих примеров нужно протянуть формулу, но результаты вычислений будут неверными:

Нужно поработать с адресацией ячеек. Ссылка на ячейку имеет формат =A1, где А — координата столбца, 1 — координата строки.

Если написать в любой ячейку формулу вида =А1 и протянуть ее вниз, то можно заметить, что в нижележащих ячейках будет =А2, =А3 и так далее. Чтобы запретить изменение ссылки по строкам, нужно поставить знак $ перед координатой строки. Пример: =A$1.

Чтобы запретить изменение координаты по столбцам, нужно поставить знак $ перед координатой столбца. Пример: =$A1.

Чтобы запретить изменение координаты по строкам и столбцам, нужно поставить знак $ перед координатой строки и столбца. Пример: =$A$1.

Протягиваем формулу на всю таблицу, проверяем результат:

Заполняем поле «Итоги» и получаем искомый результат:

Предположим, что на листе «Данные» может быть информация за несколько лет. В таком случае отчёт будет некорректный. То есть в каждом месяце данные будут по нескольким годам.

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

На листе «Данные» создаем колонку F и называем её «Год».

В ячейке F4 вводим формулу =ГОД(. Затем ссылаемся на ячейку с датой в той же строке и протягиваем формулу до конца таблицы:

Переходим к отчету. В самой первой ячейке отчета D14 добавляем в формулу в качестве диапазона критериев столбец, где мы проставили формулу ГОД, а в качестве критерия ссылаемся на ячейку, где должен быть выбран год в верхней части отчета.

И сразу же работаем с адресацией ячеек, проставляя знак $ в нужных местах, следуя логике описанной выше.

Протягиваем её на весь отчет и получаем результат:

Теперь сделаем выпадающий список в ячейке для выбора года.

Выделяем ячейку, в меню сверху выбираем: «Данные» → «Проверка данных».

Откроется диалоговое окно:

В поле «Правила» выбираем пункт «Значения из списка»:

В соседнем окне вводим несколько значений через запятую и нажимаем «Сохранить»:

Теперь данные в отчёте меняются в зависимости от выбранного года:

Выше мы разобрали упрощенный и очень наглядный пример кейса. Для нашей исходной задачи принцип аналогичен.

Всем, кто дошёл до этого этапа, большое спасибо за внимание. Материал будет полезен, если вы составляете управленческие отчеты. Аналогичным образом можно реализовать задачи, связанные с бизнес-анализом.

Всем, кто проделал вышеизложенный материал, мы бесплатно поможем разобраться в его применении на свою бизнес-модель. Пишите нам в чат, будем помогать разбираться.

Для закрепления материала завершите кейс самостоятельно. Инструменты и подход к решению аналогичен изложенному материалу. Только учтите, что листа с данными два. В таком случае нужно прописать формулу СУММЕСЛИМН к обоим листам, итоговая формула для отчета примет вид: =СУММЕСЛИМН1() + СУММЕСЛИМН2() … СУММЕСЛИМНN()

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

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

Успехов!

#инструкция