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

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

Это работает только в Excel.

СТИЛЬ ССЫЛОК R1C1

В Excel есть два стиля ссылок — описанный выше (и более распространенный) стиль A1 и стиль R1C1, в котором и столбцы, и строки обозначаются числами.

Изменить стиль ссылок можно в параметрах Excel:

Формулы — Стиль ссылок R1C1

(Formulas — R1C1 reference style).

С включенным стилем R1C1 заголовки столбцов превратятся из латинских букв в числа.

Если вам прислали книгу Excel и в ней используется неудобный вам стиль ссылок (например, вы видите числа в качестве номеров столбцов, а привыкли к стилю A1) — просто поменяйте его в «Параметрах».

В Google Таблицах нет стиля ссылок R1C1.

Стиль ссылок R1C1 поможет с пониманием относительных и абсолютных ссылок. Вот так выглядит формула со ссылками стиля A1 (с включенной опцией «Показать формулы», с которой в ячейках видны сами формулы).

Одна часть этой формулы — ссылка на ячейку слева, на ячейку в столбце B в строке с формулой. Такая ссылка называется относительной. Она выглядит как B2, B3 и так далее.

Вторая часть — $F$1 — абсолютная ссылка на адрес F1 (ставку роялти 8%).

А так эта же формула выглядит со стилем R1C1.

Здесь обе ссылки выглядят одинаково во всех строках:

=RC[-1]*R1C6

Относительная ссылка — та, которая была разной в каждой строке при стиле A1, — здесь везде выглядит одинаково. И тут хорошо отражена ее суть, ведь RC[-1] — это ссылка на ячейку в той же строке (R без квадратных скобок) в столбце левее (-1 после буквы C).

А абсолютная ссылка выглядит так: R — номер строки, C — номер столбца. В нашем примере R1C6 — первая строка, шестой столбец, или ячейка F1 (при стиле ссылок A1).

ОБЪЕДИНЕНИЕ КНИГ EXCEL (ССЫЛКИ НА ДРУГИЕ КНИГИ В ФОРМУЛАХ)

В Excel можно ссылаться на другие книги в формулах (создавать связи).

Эти ссылки выглядят по-разному в зависимости от того, открыт источник (исходная книга) или нет.

Ссылка на другую (открытую в настоящий момент) книгу Excel выглядит так:

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

Например:

Ссылка на другую (закрытую в настоящий момент) книгу Excel:

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

Например:

Чтобы просмотреть, какие есть ссылки на другие книги, нажмите на кнопку «Изменить связи» (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 используются функции. Функции принимают на входе аргументы (в подавляющем большинстве случаев; есть несколько