Как собрать сводку и подтянуть чужие данные: функции Google Таблиц по шагам

Таблица продаж растёт каждый день, и протягивать формулы или переписывать сводку вручную быстро надоедает. Справка Google описывает ARRAYFORMULA как обёртку, которая выводит результат одной формулы сразу в несколько строк и столбцов^[3], а QUERY и IMPORTRANGE закрывают сводку и импорт.

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

Ключевые факты: функции Google Таблиц

• QUERY строит сводку по запросу на языке Google Visualization API Query Language: QUERY(data, query, [headers])^[1].
• При первом вызове IMPORTRANGE показывает #REF! «Необходимо связать листы.», и нужно нажать «Разрешить доступ»^[2].
• IMPORTRANGE проверяет обновления каждый час, пока документ открыт, и передаёт не больше 10 МБ за запрос^[2].
• Формулы массива нельзя экспортировать, поэтому ARRAYFORMULA не переносится в Excel^[3].
• В Excel строку сводки QUERY повторяет СУММЕСЛИ: для Ковалёвой обе дают 25600^[4].

Что умеют QUERY, IMPORTRANGE и ARRAYFORMULA и чем их заменить в Excel

QUERY, IMPORTRANGE и ARRAYFORMULA — функции Google Таблиц для сводки, импорта и расчёта столбца. У каждой есть замена в Excel, но записывается она иначе.

Выбирать функцию проще по задаче. Нужна сводка внутри файла — QUERY. Данные лежат в чужой таблице — IMPORTRANGE. Строки прибавляются каждый день, а протягивать формулу некому — ARRAYFORMULA. Функции Google Таблиц можно и сочетать: сначала импортировать диапазон, затем построить на нём сводку.

Справка Google отмечает, что многие формулы массива и без ARRAYFORMULA распространяются на соседние ячейки^[3]. Поэтому в новой формуле функция-обёртка нужна не всегда: сначала проверьте, растянулся ли результат сам.

Разница в аргументах тоже важна. QUERY получает текст запроса в кавычках или ссылку на ячейку с ним, например QUERY(A2:E6,F2,FALSE)^[1], а IMPORTRANGE — ссылку на чужой файл и строку с диапазоном. Обычные функции ЕСЛИ и СУММ можно вкладывать в ARRAYFORMULA — справка приводит пример ARRAYFORMULA(SUM(IF(A1:A10>5, A1:A10, 0)))^[3].

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

Как работает QUERY: запрос select, where и group by

QUERY в Google Таблицах считает сводку одной формулой по запросу select и group by. Синтаксис по справке Google: QUERY(data, query, [headers]), где последний аргумент — число строк заголовков^[1].

1. Введите =QUERY(A1:D9;"select A, sum(D) where A is not null group by A";1) в свободную ячейку.
2. Последний аргумент 1 говорит, что шапка занимает только первую строку. Часть where A is not null отсекает пустые строки, group by A собирает строки по менеджерам.
3. Сверьте итог: для Ковалёвой сумма должна совпасть с =СУММЕСЛИ(A2:A9;"Ковалёва";D2:D9), а это 25600^[4].

В запросе QUERY к столбцам обращаются буквами A, B или обозначением Col, а не названиями из шапки^[1]. Запрос select Менеджер, sum(Сумма) поэтому не сработает, даже если такие заголовки есть в первой строке.

Из трёх функций Google Таблиц QUERY самая гибкая: один запрос и фильтрует, и группирует строки. Для проверки сводки удобно держать рядом СУММЕСЛИ: если итоги совпали, запрос написан верно. Запрос умеет и поворачивать данные: select avg(A) pivot B считает среднее по столбцу A, а значения столбца B превращает в новые столбцы^[1]. Если заголовки не указаны или равны -1, функция определяет их число по содержимому диапазона. Однако сводка внутри одного файла — только часть дела: данные часто лежат в другой таблице.

Как подключить другую таблицу через IMPORTRANGE

IMPORTRANGE подставляет в лист диапазон из другой таблицы после разрешения доступа. Синтаксис: IMPORTRANGE(url_таблицы; диапазон), где диапазон — строка вида «Лист1!A2:B6»; без названия листа берётся первый лист^[2].

1. Скопируйте ссылку на исходную таблицу и вставьте её первым аргументом в кавычках.
2. Вторым аргументом укажите лист и диапазон, например "Продажи!A1:D100".
3. При первом импорте функция покажет #REF! с текстом «Необходимо связать листы.» — нажмите «Разрешить доступ».
4. Проверьте, что данные появились, и только потом стройте на них QUERY или СУММЕСЛИ.

Разрешение действует, пока у того, кто его дал, есть доступ к исходной таблице^[2]. Если этот доступ отозвали, импорт перестанет работать у всех читателей сводки. Ссылку удобно хранить в отдельной ячейке: первый аргумент принимает и ссылку на ячейку с URL^[2].

IMPORTRANGE не обновляется при перезагрузке страницы: данные проверяются каждый час, пока документ открыт^[2]. Функция выполняется при первом открытии документа или если он открывался не более 5 минут назад.

Лимит на функции импорта считается для автора документа и учитывает все такие формулы во всех его файлах. При превышении в ячейке появится «Загрузка…» и ошибка о большом количестве запросов^[2]. Поэтому одна широкая формула IMPORTRANGE лучше десятка узких.

Разобравшись с импортом, переходим к расчёту на весь столбец — там помогает ARRAYFORMULA.

Чем ARRAYFORMULA отличается от обычной формулы

ARRAYFORMULA отличается от обычной формулы тем, что одна запись считает весь диапазон и выводит результат в несколько строк. Если при правке записи нажать Ctrl+Shift+Enter, Таблицы сами добавят ARRAYFORMULA( в начало^[3].

В примере продаж запись =ЕСЛИ(B2:B9="";"";B2:B9*C2:C9) в D2 разливается на D2:D9: в D2 получается 9600, в D8 — 10000, а последняя строка без данных остаётся пустой^[4]. Условие ЕСЛИ с пустой строкой нужно, чтобы расчёт не писал ноль там, где данных ещё нет.

Размер результата можно ограничить функцией ARRAY_CONSTRAIN — она обрезает массив до заданного числа строк и столбцов^[3]. Формулы массива нельзя экспортировать — это важно, если таблицу потом скачают в Excel. Для файла, который уйдёт коллеге, проверьте результат после скачивания или замените его значениями. В самом Excel перенос означает, что несколько значений результата помещаются в смежные ячейки, а занятая область даёт #ПЕРЕНОС!; статья справки Microsoft относится к Excel для Microsoft 365, в том числе для Mac^[5]. Остаётся понять, в каких случаях эти функции лучше не использовать вовсе.

Когда функции Google Таблиц не подойдут

Функции Google Таблиц не подойдут, если файл уйдёт в Excel или источник импорта закрыт. Ещё один предел — объём данных в одном запросе, и справка Google называет его прямо^[2].

• Если таблицу скачают в Excel, ARRAYFORMULA не поможет: формулы массива нельзя экспортировать.
• Если за один запрос IMPORTRANGE нужно передать больше 10 МБ данных, импорт упрётся в лимит^[2].
• Когда исходная таблица запрещает скачивание, печать и копирование, новые вызовы IMPORTRANGE не экспортируют данные.
• Если импорт ссылается на NOW, RAND или RANDBETWEEN, результат будет #ERROR!; исключение — СЕГОДНЯ, которая обновляется раз в день.
• Если в запросе QUERY указаны названия столбцов вместо букв, сводка не построится.

В Excel те же задачи решают другие инструменты: строку сводки даёт функция СУММЕСЛИ по одному условию, а скачать таблицу Google в формате Excel можно через File → Download as^[6]. Перед скачиванием замените массивы значениями — так файл откроется без сюрпризов. Тот же приём справка Google советует при ошибке с NOW и RAND: скопировать результаты и вставить их как значения^[2]. Повторите примеры в тренажёре: урок из 7 шагов проходит ARRAYFORMULA, QUERY и IMPORTRANGE на тех же продажах^[4]. Как создать саму таблицу и открыть к ней доступ — на странице о Google Таблицах для общей работы, а порядок всех тем — в разделе первые шаги в Excel: формулы и ссылки.