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