Автоматизация отчётов в Excel и Google Таблицах: 4 способа

Отчёт в Excel или Google Таблицах автоматизируют четырьмя способами, от простого к сложному: формулы, которые пересчитываются сами; Power Query, который одной кнопкой собирает однотипные выгрузки из папки; макросы и скрипты внутри таблицы — VBA, Office Scripts, Apps Script; отдельный скрипт на Python, который по расписанию сам забирает данные из API и 1С. Берите самый простой способ, который закрывает задачу целиком: каждая следующая ступень умеет больше, но дороже в поддержке.

Если данные приходят файлами одного вида, хватает второй ступени: Power Query настраивают своими силами за вечер-два, дальше отчёт обновляется кнопкой. Выше идут, только когда отчёт должен собираться без человека или данных нет в выгрузке, — и только если это окупается.

Пример: еженедельный отчёт по продажам с маркетплейсов и 1С

Возьмём условный отчёт. Каждый понедельник менеджер собирает продажи за неделю: скачивает отчёты из кабинетов Wildberries и Ozon, выгружает из 1С остатки и себестоимость, копирует всё в одну книгу, приводит столбцы к общему виду и обновляет сводную. Уходит два с половиной часа.

Раз в неделю — это примерно четыре раза в месяц, 48 раз в год: 120 часов. При 600 ₽ за час — 72 000 ₽ в год за один отчёт. Проведём его по всем четырём ступеням.

1. Формулы: ВСТОЛБИК, ГРУПППО, QUERY и IMPORTRANGE

Если выгрузки вставляют на листы одной книги, остальное делают формулы. ВСТОЛБИК (в английском Excel — VSTACK) ставит таблицы WB и Ozon друг под другом, ПРОСМОТРX подтягивает себестоимость из выгрузки 1С по артикулу, ГРУПППО (GROUPBY) одной формулой строит сводку по товарам. ФИЛЬТР оставляет одну площадку или неделю, УНИК даёт список артикулов без повторов. Вставили свежие данные — отчёт пересчитался.

Оформите каждую выгрузку как таблицу (Ctrl+T) и назовите её на вкладке «Конструктор таблиц»: тогда формулы сами подхватят новые строки, без пустого хвоста внизу.

=ВСТОЛБИК(ВЫБОРСТОЛБЦ(Продажи_WB; 1; 4; 6); ВЫБОРСТОЛБЦ(Продажи_Ozon; 2; 3; 9))
=ГРУПППО(ИНДЕКС(A2#;;1); ИНДЕКС(A2#;;3); СУММ)

Первая формула берёт из каждой выгрузки артикул, количество и сумму — у площадок они стоят в разных столбцах — и ставит их друг под другом. Вторая, если первая стоит в A2, складывает суммы по артикулам.

В Google Таблицах то же делают QUERY — запрос к диапазону на языке, похожем на SQL, — и IMPORTRANGE, которая подтягивает данные из других таблиц. При первом запуске IMPORTRANGE покажет #REF!: нажмите «Разрешить доступ».

=QUERY(VSTACK(IMPORTRANGE("ссылка на таблицу WB"; "Лист1!A2:C"); IMPORTRANGE("ссылка на таблицу Ozon"; "Лист1!A2:C")); "select Col1, sum(Col3) where Col1 is not null group by Col1")

Где упирается:

• Версия Excel. ФИЛЬТР, УНИК и ПРОСМОТРX есть начиная с Excel 2021, ВСТОЛБИК и ВЫБОРСТОЛБЦ — с 2024, ГРУПППО — только в Microsoft 365. Отчёт, собранный на подписке, у коллеги с Excel 2019 покажет ошибки.
• Другие книги. Формула динамического массива, которая ссылается на другую книгу, работает, только пока открыты обе, иначе выдаёт #ССЫЛКА!; ДВССЫЛ с закрытой книгой — тоже. Обычные ссылки на закрытую книгу обновляются, но новый файл в папке формулы не подхватят: ссылку на каждую выгрузку придётся вписывать руками.
• Положение столбцов. ВСТОЛБИК складывает таблицы по положению столбцов, а не по названиям. Вставит площадка в выгрузку новый столбец — номера в ВЫБОРСТОЛБЦ съедут, и в сумму молча попадёт соседний столбец.
• Google Таблицы. IMPORTRANGE получает не больше 10 МБ за запрос и проверяет обновления раз в час, пока таблица открыта. QUERY при смешанных типах в столбце берёт преобладающий тип, а остальные значения считает пустыми: если часть артикулов — числа, а часть — текст, меньшинство тихо выпадет из сводки.

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

2. Power Query: собрать данные из нескольких файлов Excel в один

Power Query встроен в Excel для Windows начиная с версии 2016: вкладка «Данные», группа «Получить и преобразовать». Он запоминает шаги — какие столбцы убрать, как переименовать, что отфильтровать — и повторяет их на новых файлах одной кнопкой.

Как объединить файлы из папки

1. Заведите папку только под выгрузки одного вида, например «Продажи WB». Power Query возьмёт все файлы из неё и из подпапок, так что посторонние попадут в отчёт.
2. «Данные» → «Получить данные» → «Из файла» → «Из папки», выберите папку.
3. «Объединить» → «Объединить и преобразовать данные». В открывшемся окне выберите слева лист или таблицу с данными. Образцом служит первый файл, другой можно выбрать в списке «Образец файла».
4. В редакторе уберите лишние столбцы, задайте типы и общие названия. Правки образца применятся ко всем файлам.
5. «Закрыть и загрузить». Новая неделя — новый файл в папку и «Обновить все» на вкладке «Данные».

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

Первым столбцом итога Power Query ставит имя файла, Source.Name. Называйте выгрузки по неделям, и будет видно, откуда каждая строка. После обновления откройте фильтр этого столбца и сверьте список с папкой: флажок «Пропускать файлы с ошибками» в окне объединения не даёт битому файлу уронить запрос, но такой файл молча выпадает из отчёта. А итог недели сверьте с суммой в кабинете площадки — это одна ячейка.

Если у площадки есть API

Продажи WB Power Query забирает и без скачивания: «Данные» → «Из Интернета» → «Дополнительно». Адрес — метод продаж с датой начала периода, https://statistics-api.wildberries.ru/api/v1/supplier/sales?dateFrom=2026-10-05; в «Параметрах заголовка HTTP-запроса» — Authorization и токен из кабинета продавца; вход — «Анонимный». Ответ придёт списком записей: «В таблицу», затем разверните столбец.

Минусы: токен хранится в книге и виден каждому, у кого есть файл; дату в адресе придётся менять или вычислять в коде запроса; обновление — кнопкой в открытой книге. Больше 80 000 строк за раз метод не отдаёт, следующую порцию придётся запрашивать кодом на языке M. Запросы к Ozon идут методом POST, их в этом окне не собрать — только правкой кода.

Как забрать данные из 1С без выгрузки руками

1С можно не выгружать руками. Оба способа ниже настраивает тот, кто ведёт вашу 1С.

Рассылка отчётов. В Библиотеке стандартных подсистем, на которой построены типовые конфигурации, есть «Рассылки отчётов». Если они входят в вашу конфигурацию, 1С сама по расписанию сформирует отчёт, сохранит его в XLSX и положит в сетевую папку или на FTP либо отправит почтой. Для Power Query заведите под этот отчёт отдельную папку, к выгрузкам WB его не кладите: структура другая. Отчёт придёт таким, каким его видно на экране, с шапкой и итогами, — в запросе их отрезают один раз.

OData. Платформа 1С умеет сама отдавать справочники, документы и регистры через стандартный интерфейс OData. Попросите три вещи: опубликовать базу на веб-сервере со стандартным интерфейсом OData, включить в него нужные справочники, документы и регистры и завести для отчёта пользователя только на чтение: через этот интерфейс данные можно и менять. Дальше в Excel:

1. «Данные» → «Получить данные» → «Из других источников» → «Из веб-канала OData».
2. Адрес вида https://сервер/база/odata/standard.odata/, проверка подлинности «Базовый» — логин и пароль этого пользователя.
3. В окне «Навигатор» отметьте нужные таблицы.

Если база доступна не только из офисной сети, публикуйте её только по https, иначе логин и пароль уходят по сети открытым текстом. Слияния таблиц из OData идут медленно и могут обрываться ошибкой о том, что удалённый узел разорвал подключение. Microsoft советует перед слиянием буферизовать меньшую таблицу через Table.Buffer.

Почему Power Query не обновляет данные

• Файл закрыт. В «Свойствах подключения» на вкладке «Использование» можно включить «Обновлять каждые … мин» и «Обновить данные при открытии файла», но всё это работает, только пока книга открыта в Excel. Отчёт к девяти утра без человека Power Query сам не соберёт.
• Mac, браузер, телефон. На Mac Power Query есть только в подписке Microsoft 365, и запрос «Из папки» там не создать — можно лишь обновлять созданный на Windows. В Excel в браузере локальную папку не подключить: обновляются только папки SharePoint и OneDrive для работы и учёбы. На телефоне Power Query нет вовсе.
• Файл не похож на образец. Все файлы открываются так же, как образец: если в нём выбран лист «Лист1», файл, где лист называется иначе, даст ошибку, а с флажком пропуска молча выпадет. Регистр тоже важен: «Артикул» и «артикул» для Power Query — разные столбцы, и у файла с другим заголовком этот столбец придёт пустым или вызовет ошибку.
• Переименовали столбец. Если шаги запроса ссылаются на старое имя, обновление падает с ошибкой Expression.Error: Столбец "…" таблицы не найден. Если не ссылаются, хуже: у новых файлов столбец молча придёт пустым. Откройте запрос («Данные» → «Запросы и подключения», двойной щелчок) и найдите шаг со старым именем, обычно это «Измененный тип» в конце итогового запроса. Поправьте имя или удалите шаг и задайте типы в запросе-образце.
• Formula.Firewall. Ошибку выдаёт защита конфиденциальности, когда запрос смешивает данные разных источников, например папки и 1С. Если ошибка про уровни конфиденциальности, задайте обоим источникам один уровень, организационный: «Данные» → «Получить данные» → «Параметры источника данных» → «Изменить разрешения». Если про ссылки на другие запросы, обращения к источникам надо собрать в один запрос. Если все источники ваши, проверку можно выключить для книги: «Получить данные» → «Параметры запроса» → «Текущая книга» → «Конфиденциальность».

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

3. Макросы и скрипты: VBA, Office Scripts, Apps Script

Код внутри таблицы нажимает кнопки за человека. Поддерживает его автор, и с уходом автора уходит понимание, как собирается отчёт, — поэтому хотя бы опишите, что делает скрипт и где он лежит.

Макросы Excel на VBA

Макрос умеет в Excel всё, что умеет человек: открыть файлы, обновить запросы, разложить данные по листам, сохранить PDF. Начать можно с записи своих действий: «Разработчик» → «Запись макроса». Вкладка «Разработчик» по умолчанию скрыта, её включают в «Файл» → «Параметры» → «Настроить ленту». Книгу сохраните как «Книга Excel с поддержкой макросов», это формат .xlsm: в обычном .xlsx макрос не сохранится. Если макрос обновляет запросы Power Query, а потом сохраняет PDF, снимите в свойствах запросов фоновое обновление, иначе PDF уйдёт со старыми цифрами.

В Excel в браузере макросы не запускаются, а в настольном упираются в две вещи:

• Блокировка. Office для Windows по умолчанию блокирует макросы в файлах из интернета, в том числе во вложениях писем: красный баннер «Риск для безопасности» без кнопки «Включить содержимое». Отсюда вечное «макрос не работает на другом компьютере». Один файл разблокируют флажком «Разблокировать» в его свойствах, а для отчётов, которые регулярно приходят из учётной системы, Microsoft предлагает «Надёжное расположение».
• Ночь. Схема «Планировщик открывает Excel и запускает макрос» держится на том, чего Microsoft не рекомендует и не поддерживает: без пользователя Excel может зависнуть на диалоговом окне, которое некому закрыть.

Office Scripts и Power Automate

Office Scripts — скрипты для Excel в браузере, на Windows и Mac по рабочей или учебной подписке Microsoft 365 с OneDrive для бизнеса; в личной и семейной подписке они пока в предварительной версии. Встроенное расписание временно отключено, по расписанию скрипты запускает Power Automate. Но из потока скрипт почти ничего не обновляет: запросы Power Query, сводные и формулы со ссылками на другие книги остаются как были, а метод обновления подключений молча отвечает «успешно». Внешние данные скрипт в потоке сам не заберёт: их приносят HTTP-действия Power Automate, платные, класса Premium, и передают скрипту параметрами. Так что Office Scripts перекладывают и оформляют данные внутри книги, а собирать их без платного потока не умеют.

Автоматизация Google Таблиц: Apps Script

Аналога Power Query в Google Таблицах нет. Несколько таблиц сводят формулы из первого раздела, а собрать файлы из папки на Диске или забрать данные из API можно только скриптом. Редактор — «Расширения» → «Apps Script». Скрипт умеет ходить в API, например за продажами Wildberries, и запускаться по расписанию, от раза в минуту до раза в месяц: «Триггеры» → «Добавить триггер», источник события — по времени. Токен WB храните не в ячейке, а в «Настройках проекта» → «Свойства скрипта»: ячейку видит каждый, кому открыта таблица.

Что надо знать заранее:

• Триггер «в 9:00» сработает в какой-то момент между 9 и 10.
• Триггер работает от имени того, кто его создал. Уволился сотрудник, отключили аккаунт — отчёт встал. Создавайте триггеры под общим рабочим аккаунтом, а не под личным.
• Одно выполнение — до 6 минут, всем триггерам — 90 минут в день на обычном аккаунте и 6 часов в Workspace, и эти квоты Google может поменять без уведомления. Против ошибки «Exceeded maximum execution time» первое средство — читать диапазон одним getValues и писать одним setValues, а не обращаться к каждой ячейке в цикле.
• Скрипт работает на серверах Google, и 1С в офисной сети ему недоступна, пока базу не откроют в интернет, а это уже вопрос безопасности. Себестоимость в таком варианте придётся переносить в таблицу отдельно.
• В одной таблице — до 20 млн ячеек. В строке продаж WB почти три десятка полей, так что историю копите только нужными столбцами.

В примере макрос в Excel по времени не лучше Power Query: скачивание из кабинетов он может заменить запросом к API, но это уже программирование на VBA. Apps Script в Google Таблицах забирает данные WB прямо из API — это уже четвёртая ступень, только внутри таблицы и в её квотах.

4. Автоматизация Excel с помощью Python: скрипт по расписанию

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

Однотипные выгрузки из папки на Python собираются в несколько строк. Нужны библиотеки pandas и openpyxl, они ставятся командой pip install pandas openpyxl:

from pathlib import Path
import pandas as pd

files = [f for f in Path("выгрузки").glob("*.xlsx") if not f.name.startswith("~$")]
report = pd.concat(
    [pd.read_excel(f).assign(source=f.stem) for f in files],
    ignore_index=True,
)
report.to_excel("продажи за неделю.xlsx", index=False)

Если отчёт кладут в готовый шаблон, to_excel его перезапишет; дописывают через pd.ExcelWriter с mode="a", engine="openpyxl" и if_sheet_exists="overlay", но openpyxl не пересчитывает формулы и теряет фигуры шаблона.

В Excel для Windows ради склейки Python не нужен — это умеет Power Query. Код выше пригодится там, где сбора из папки нет: на Mac, в Р7-Офисе и МойОфисе. К API Power Query и VBA тоже обращаются, но такой запрос обновится только в открытой книге, токен будет лежать в файле, а постраничную выдачу WB придётся писать на языке M. Смысл четвёртой ступени в другом: отчёт собирается без человека, по расписанию, и сам проверяет, что данные пришли. Источники для него:

• Wildberries отдаёт продажи и возвраты по API. Данные обновляются раз в 30 минут и хранятся 90 дней с даты заказа: историю для сравнения с прошлым годом придётся копить у себя. За один ответ — до 80 000 строк. С «Персональным» токеном метод можно вызывать раз в минуту, с «Базовым» — раз в два часа. Данные предварительные: в ответе может не быть заказов с отложенной оплатой и в рассрочку, а поля с ценой могут прийти нулями и дозаполниться в течение суток. Для денег WB советует детализацию к отчётам реализации. Отчёт, собранный в понедельник в 00:05, может показать нули по воскресным продажам.
• Ozon даёт Seller API по ключам Client-Id и Api-Key: аналитика, отчёты по отправлениям, отчёт о реализации.
• 1С — тот же OData-интерфейс, что и для Power Query, или файл из рассылки отчётов.

Запускают скрипт Планировщиком заданий Windows или cron на сервере. У Планировщика есть тихая ловушка: если у учётной записи сменился или истёк пароль, а в задаче остался старый, задача просто перестаёт запускаться.

Главная работа на этой ступени — не код сбора, а проверки. Источник может вернуть пустой ответ из-за истёкшего токена или сбоя, и скрипт честно разошлёт «продаж за неделю — ноль». Отчёт без человека должен отличать «продаж не было» от «данные не пришли» и в сомнительном случае звать человека, а не врать. Для этого отчёта мы бы поставили такие проверки:

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

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

Python в Excel эту ступень не заменяет: код там выполняется в облаке Microsoft без доступа к сети и к файлам компьютера, внешние данные попадают в него только через Power Query.

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

Если вместо Excel — Р7-Офис или МойОфис

При переходе на отечественный офис отчёты на Power Query и VBA приходится переделывать. Что есть по справкам самих производителей:

Р7-Офис. Макросы пишут на JavaScript и запускают через «Плагины» → «Макросы». VBA из Excel не работает: раздел справки «Конвертация макросов MS VBA» показывает, как переписать макрос на JavaScript вручную, автоматического конвертера нет. Доступа к системе, а значит и к файлам на диске, у макросов нет. «Получить данные из файла» на вкладке «Данные» берёт только локальный TXT/CSV или XML: сбора из папки и редактора запросов, как в Power Query, нет.

МойОфис. В редакторе «МояТаблица» макросы на Lua, и хранятся они только внутри документа. VBA-макросы из Excel не запускаются: встроенный конвертер переводит их в Lua, но на первом неподдерживаемом методе закомментирует весь код ниже. Аналога Power Query со сбором из папки в справке мы не нашли.

Вторая ступень в обоих редакторах фактически выпадает. Однотипные выгрузки здесь сводит внешний скрипт вроде того, что в разделе 4, а XLSX, который он соберёт, откроют и Р7-Офис, и МойОфис.

Какой способ выбрать

Когда автоматизация отчёта окупается

Посчитаем пример при 600 ₽ за час и 48 отчётах в год. Минуты на каждой ступени — наша прикидка для этого отчёта, у вас будут свои.

Power Query окупается за пару недель, если он вам знаком, и примерно за месяц, если осваивать его с нуля. Он снимает 88 часов в год, 52 800 ₽: каждый понедельник возвращает почти два часа.

Скрипт поверх Power Query для этого отчёта не окупается. Он снимет ещё 16 часов, 9 600 ₽ в год. Автоматизация у нас стоит от 25 000 ₽, а с тремя источниками выйдет больше. Даже минимум вернётся только через 2,6 года, и это без поддержки. Если же продажи WB Power Query забирает по API, разница ещё меньше. Честный ответ: оставьте Power Query.

Картина меняется, если:

• отчёт нужен каждый день. Те же 20 минут разницы × 21 рабочий день × 12 месяцев = 84 часа, 50 400 ₽ в год. Минимальные 25 000 ₽ вернутся примерно за полгода, вдвое большая сумма — за год;
• данных нет в выгрузке. Power Query нечего собирать, и скрипт сравнивают уже с ручной работой: экономия — 104 часа и 62 400 ₽ в год;
• таких отчётов несколько. Доступы, проверки и расписание делаются один раз на все;
• отчёт держится на одном человеке, и в его отпуск не собирается вовсе.

Подставьте свои числа в калькулятор стоимости ручной операции: для еженедельного отчёта ставьте 1 раз в день и 4 рабочих дня в месяц. Почему час стоит дороже, чем оклад, делённый на 160, и почему автоматизация не снимает операцию целиком, — в статье о цене ручной операции.

Когда справитесь сами

• Данные приходят файлами одного вида — выгрузки из кабинетов, банка, 1С, — и скачать их можно за пару минут.
• Отчёт нужен раз в день или реже, и его всё равно открывают в Excel для Windows.
• 1С подключается по OData, присылает отчёт рассылкой, или выгрузка из неё делается за минуту.
• Строк меньше миллиона, или вы грузите их в модель данных, а не на лист.
• Вы в Google Таблицах, а источники — несколько таблиц: хватит QUERY с IMPORTRANGE. Если данные надо забирать из API, нужен человек, который поддержит Apps Script, и триггеры под общим рабочим аккаунтом.

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

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

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