Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)

'Путь на диске[Имя_книги]Название_листа'!Диапазон

Например:

Чтобы просмотреть, какие есть ссылки на другие книги, нажмите на кнопку «Изменить связи» (Edit Links) на вкладке «Данные».

Здесь можно:

• обновить связь (Update Values), чтобы использовались актуальные данные из источника;

• поменять книгу-источник (Change Source), если он переехал в другую папку на диске;

• открыть книгу-источник (Open Source) или разорвать связь (Break Link); после разрыва связи ссылки на книгу-источник превратятся в значения и обновляться, соответственно, больше не будут.

Кнопка «Запрос на обновление связей» (Startup Prompt) вызовет небольшое диалоговое окно, в котором можно настроить поведение Excel при открытии книги.

ФУНКЦИЯ IMPORTRANGE В GOOGLE ТАБЛИЦАХ

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

Для загрузки вам нужно иметь доступ к этому файлу — на редактирование или на комментирование/просмотр (без запрета на скачивание и копирование данных: если он включен, то не получится импортировать данные и с помощью IMPORTRANGE).

У функции два аргумента — ссылка на таблицу и ссылка на диапазон.

Ссылка на таблицу может указываться в двух видах — полная (со ссылкой на лист или без нее — не имеет значения, лист в любом случае определяется вторым аргументом функции) или ключ (набор символов после общей части ссылки docs.google.com/spreadsheets/d/).

=IMPORTRANGE(ссылка на файл; ссылка на лист и диапазон)

Пример нескольких ссылок на файл, которые будут работать одинаково в функции IMPORTRANGE (первый аргумент):

• https://docs.google.com/spreadsheets/d/1wWrgdcpIPeS3THHjZzkcPGMqwFCDHSTVlE4j1G6Dppc/edit#gid=1556931255 (полная ссылка с указанием номера листа);

• https://docs.google.com/spreadsheets/d/1wWrgdcpIPeS3THHjZzkcPGMqwFCDHSTVlE4j1G6Dppc (полная ссылка, но без листа; повторимся: в любом случае лист будет указываться отдельно во втором аргументе, в ссылке его отсутствие или присутствие ни на что не влияет);

• 1wWrgdcpIPeS3THHjZzkcPGMqwFCDHSTVlE4j1G6Dppc (только ключ).

Второй аргумент — диапазон — может задаваться несколькими способами:

• без указания названия листа (например, "B2:E"; в таком случае данные будут тянуться из диапазона B2:E с первого по порядку листа в исходном документе);

• с указанием названия листа: "Продажи! A2:D" или "Продажи! A1:L20";

• с использованием имени диапазона, если в исходном файле есть таковые. Например, "Налог".

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

Обратите внимание на следующие нюансы.

Функция IMPORTRANGE выводит массив данных, а не одну ячейку (в частном случае она может возвращать и одну ячейку, если вы указали такой диапазон во втором аргументе, но в большинстве случаев она все же используется для загрузки таблиц, а не одиночных ячеек). Это значит, что справа и снизу от него должно быть достаточно пустых ячеек для вывода этих данных. Если в ячейках есть данные, то функция не сможет их «перезаписать» выводимым массивом и вернет ошибку.

IMPORTRANGE возвращает только значения и числовые форматы, но не переносит стилевое форматирование — заливку, шрифт и так далее. Форматирование нужно настраивать отдельно в каждой таблице.

Не обязательно сначала выводить данные из другого файла, а потом их обрабатывать — их можно обработать, используя IMPORTRANGE как аргумент другой функции: например, сразу получить среднее из диапазона в другом файле с помощью СРЗНАЧ, аргументом которой будет IMPORTRANGE. Тогда не нужно будет выводить сами данные в конечной таблице.

Функции рабочего листа Excel

В формулах Excel используются функции. Функции принимают на входе аргументы (в подавляющем большинстве случаев; есть несколько функций без аргументов, об этом ниже) — параметры, ссылки на ячейки и диапазоны — и возвращают на выходе некий результат в соответствии со своим назначением. Например, одна из самых простых и известных функций СУММ / SUM возвращает сумму всех своих аргументов (чисел, введенных вручную, чисел из отдельных ячеек и из диапазонов). В Excel более 500 функций — есть специализированные, используемые редко, есть актуальные для большинства пользователей.

Следующая функция будет возвращать сумму чисел из ячейки B5, диапазона A1:B2 и числа 1200:

=СУММ(B5; 1200; A1:B2)

А следующая функция будет возвращать среднее значение чисел из диапазона B2:B12: