Управленческие отчёты в Google Таблицах: формулы и структура

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

Лист «Операции» — единственный, куда вносят данные. Одна строка на движение денег: дата, тип, счёт или точка, статья, сумма, комментарий. Отдельной колонкой — месяц, он считается формулой и нужен для отчётов: =ЕСЛИ(A2="";"";ДАТА(ГОД(A2);МЕСЯЦ(A2);1)).

Лист «Статьи» — справочник на 12–15 позиций, из него собираются выпадающие списки. Лист «Отчёты» — только формулы, туда никто не пишет руками. Такое разделение позволяет спокойно давать доступ продавцам: сломать отчёты они не могут, потому что не заходят на этот лист.

Для учёта достаточно четырёх функций. Первая — СУММЕСЛИМН, она даёт постоянную структуру отчёта: строки-статьи, колонки-месяцы. Вторая — QUERY, она строит сводки на лету и заменяет собой сводные таблицы.

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

Третья — ЕСЛИОШИБКА, чтобы пустые ячейки не показывали ошибку. Четвёртая — ВПР или ИНДЕКС с ПОИСКПОЗ для подтягивания групп статей из справочника.

У Ани две цветочные точки: на Ленина и в торговом центре. Выручка за месяц — 1 400 000 ₽: 820 000 на Ленина и 580 000 в ТЦ. Раньше флористы отправляли фото чеков в мессенджер, а Аня по вечерам переносила их в свой файл.

Теперь после смены флорист открывает таблицу с телефона и вносит две-три строки: выручка за день, закупка цветов, мелкие расходы. Статью выбирает из списка, точку — из списка, сумму пишет руками. Полторы минуты.

Отчёты пересчитываются сами. За месяц: цветы и упаковка — 560 000 ₽, зарплата флористов — 340 000 ₽, аренда двух помещений — 190 000 ₽, прочее — 85 000 ₽, налог — 84 000 ₽. Прибыль 141 000 ₽, ровно десятая часть выручки.

Полезное открытие дала сводка через QUERY, где Аня сгруппировала списания по точкам. На Ленина в брак и усушку уходило 4,5% закупки, в ТЦ — 9%, вдвое больше при меньшей выручке. Причина оказалась бытовой: в ТЦ витрина стояла у входа на сквозняке. Перенесли — списания за два месяца выровнялись, это около 10 000 ₽ в месяц.

Главный страх владельца при общем доступе — что кто-то случайно сотрёт формулу. В Google Таблицах это решается тремя настройками, каждая делается за минуту.

Первое: защита диапазонов. Выделяете лист «Отчёты» целиком и ставите защиту — редактировать может только владелец, остальные видят. То же для справочника статей. Второе: выпадающие списки через проверку данных — тогда в статье не появится «цветы», «Цветы» и «цвты» тремя разными строками. Третье: история изменений, где видно, кто и что менял; она включена всегда и не раз спасала при спорах.

Отдельный приём для точек — именованные диапазоны или отдельный файл на точку со сведением через IMPORTRANGE. Второе надёжнее, если сотрудников много и доверия к общему файлу нет, но добавляет задержку: данные подтягиваются с небольшим опозданием.

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

Хрупкость структуры. Вставленная не туда строка или скопированный диапазон ломают ссылки в отчётах, и найти это можно не сразу. Отсюда правило: формулы живут отдельно от данных, и никто, кроме владельца, туда не заходит.

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