IMPORTRANGE и ARRAYFORMULA в Google Таблицах

Короткий ответ

• IMPORTRANGE берёт диапазон из другой таблицы и показывает его в текущей. Исходные данные меняются — меняется и импорт.
• ARRAYFORMULA позволяет написать формулу один раз в верхней ячейке, и она посчитает весь столбец. Не нужно протягивать формулу на тысячи строк и следить, чтобы новые строки её получили.

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

В русской локали аргументы разделяются точкой с запятой — так в примерах ниже.

IMPORTRANGE: синтаксис

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/КЛЮЧ/edit"; "Продажи!A1:F")

• Первый аргумент — ссылка на таблицу или только её ключ (часть адреса между /d/ и /edit).
• Второй — диапазон в виде текста: имя листа, восклицательный знак, адрес.
• Оба аргумента можно хранить в ячейках: =IMPORTRANGE(B1; B2). Тогда при переименовании листа достаточно поправить одну ячейку-настройку.

Как открыть доступ

1. Вставьте формулу. В первый раз появится #ССЫЛКА! (#REF!) с подсказкой.
2. Наведите курсор на ячейку и нажмите «Открыть доступ».
3. Для этого у вас должен быть доступ хотя бы на просмотр к исходной таблице.

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

ARRAYFORMULA: одна формула на весь столбец

Вместо =B2*C2, протянутой вниз, в ячейку D2:

=ARRAYFORMULA(IF(A2:A=""; ; B2:B*C2:C))

• Ссылки — диапазоны, а не отдельные ячейки: B2:B вместо B2.
• Проверка IF(A2:A=""; ; ...) оставляет пустыми строки, где ещё нет данных, иначе столбец заполнится нулями.
• Сочетание Ctrl+Shift+Enter при вводе формулы сразу обернёт её в ARRAYFORMULA.
• Ячейки ниже формулы должны быть пустыми. Если там что-то есть, массив не сможет развернуться и формула вернёт #ССЫЛКА!.

Удобный приём — разместить формулу прямо в строке заголовка, тогда она не потеряется при сортировке:

={"Сумма"; ARRAYFORMULA(IF(A2:A=""; ; B2:B*C2:C))}

Поиск по справочнику для всего столбца:

=ARRAYFORMULA(IF(A2:A=""; ; IFNA(VLOOKUP(A2:A; Справочник!A:C; 3; FALSE); "нет в справочнике")))

Ограничение: функции, которые сворачивают диапазон в одно значение (SUM, AND, OR), внутри ARRAYFORMULA не работают построчно. Вместо AND(условие1; условие2) используйте умножение (условие1)*(условие2), вместо OR — сложение. Для сложной построчной логики есть BYROW и MAP с LAMBDA.

Рабочая схема для отчётов

1. В файле отчёта создайте лист «Импорт» с одной функцией IMPORTRANGE на каждый источник.
2. Импортируйте только нужные столбцы и ограниченный диапазон.
3. Все расчёты делайте на отдельных листах, ссылаясь на «Импорт», — через ARRAYFORMULA, QUERY, FILTER.
4. Защитите лист с формулами: «Данные → Защищённые листы и диапазоны».

Как не сделать медленную и хрупкую таблицу

• Не вызывайте IMPORTRANGE в каждой ячейке. Каждый вызов — отдельный запрос к другому файлу. Один импорт и локальные ссылки работают быстрее.
• Избегайте цепочек «файл А → файл Б → файл В → отчёт». Задержки складываются, а одна поломка останавливает всё дальше по цепочке.
• Удаляйте пустые строки и столбцы внизу и справа листа. ARRAYFORMULA по A2:A обрабатывает все строки листа, в том числе пустые.
• Не злоупотребляйте NOW, TODAY, RAND и RANDBETWEEN в больших диапазонах — они заставляют таблицу часто пересчитываться.
• Не переименовывайте листы-источники и не вставляйте столбцы в середину: диапазон в IMPORTRANGE — это текст, и он не обновится сам. Новые столбцы добавляйте в конец.
• Следите за ростом. Когда десятки файлов связаны между собой, а данные вносят много людей, таблица становится узким местом — это сигнал перейти к базе данных или CRM.