Инструмент Дня
Большинство отчётов в Google Sheets собирается одинаково: скопировал формулу вниз на 500 строк, руками отфильтровал, посчитал итог. Две формулы — QUERY и ARRAYFORMULA — закрывают большую часть этой рутины: одна фильтрует и суммирует данные как мини-база данных, вторая растягивает формулу на весь столбец без копирования.
QUERY — «SQL для таблиц»: фильтруй, сортируй и агрегируй одной строкой. ARRAYFORMULA — формула, которая сама заполняет весь диапазон. Вместе они заменяют половину ручных операций в отчётах.
- Подойдёт: тем, кто регулярно собирает отчёты из повторяющихся данных — заявки, продажи, расходы, трекинг времени.
- Инструменты: Google Sheets (бесплатно). Аналог QUERY в Excel — Power Query, но там всё заметно сложнее.
QUERY: запрос к таблице одной строкой
QUERY принимает диапазон и текст запроса на упрощённом SQL:
=QUERY(A2:F500; "select B, sum(E) where D = 'Оплачено' group by B order by sum(E) desc"; 1)Эта строка делает то, что руками заняло бы пять шагов: берёт только оплаченные строки, группирует их по клиенту, считает сумму и сортирует по убыванию. Меняешь данные — результат пересчитывается сам.
- Фильтрация:
where C > 1000— оставляем только крупные суммы; условия можно комбинировать черезand/or. - Агрегация:
sum(),count(),avg()сgroup by— сводные таблицы без сводных таблиц. - Динамика: условия можно собирать из ячеек:
"select * where B = '"&B1&"'"— меняешь значение в ячейке, отчёт перестраивается.
ARRAYFORMULA: одна формула на весь столбец
Обычная формула живёт в одной ячейке — её копируют вниз, и новые строки в таблице остаются пустыми, пока не протянешь снова. ARRAYFORMULA заполняет весь диапазон сразу:
=ARRAYFORMULA(IF(A2:A="";"";A2:A*B2:B))Одна формула в первой ячейке — и все строки (включая будущие) посчитаны. Это же касается СУММ, конкатенации и любых векторных операций: =ARRAYFORMULA(UPPER(B2:B)) переведёт весь столбец в верхний регистр.
Связка, которая экономит больше всего времени
ARRAYFORMULA готовит данные (метки, категории, вычисляемые колонки), а QUERY собирает из них готовый отчёт на отдельном листе. Добавляешь новые записи в исходник — отчёт обновляется сам, без единого ручного действия. Именно так стоит собирать еженедельные сводки.
Три ошибки
- Смешивать типы в столбце. Если в колонке цен затесался текст, QUERY тихо теряет строки. Проверяй исходник: один столбец — один тип данных.
- Забывать про заголовок. Третий аргумент QUERY (например,
1) — сколько строк заголовка пропустить. Ошибка тут ломает весь результат. - Дублировать форматирование формулами. QUERY возвращает числа без формата — применяй форматирование к диапазону результата, а не пытайся починить это в запросе.
Вывод: QUERY и ARRAYFORMULA — это переход от «копирую формулы и фильтрую руками» к «настроил один раз — таблица работает сама». Вечер на изучение, зато каждый следующий отчёт собирается в одну строку.