• быстрый переход к именованному диапазону через поле «Имя»;
• абсолютная ссылка на ячейку/диапазон без необходимости использовать знаки доллара.
Кроме того, они будут отображаться при уменьшении масштаба до того уровня, когда смогут поместиться поверх соответствующих диапазонов.
Файл с примером: Пересечение диапазонов.xlsx
Это не то, с чем вы, скорее всего, столкнетесь на практике. Но для понимания, что любой символ в формулах (пока он не взят в кавычки — тогда он становится текстом, а не частью формулы; об этом мы подробно поговорим в разделе про текст) что-то может значить, стоит обсудить пробел. Это символ пересечения диапазонов. Если у вас в формуле есть два диапазона, между которыми стоит пробел, то это будет ссылка на общие ячейки этих диапазонов.
Например, если присвоить всем диапазонам в этой таблице имена (это можно сделать с помощью команды «Создать из выделенного» на вкладке ленты «Формулы»)…
…то можно будет ссылаться на показатель и квартал как на пересечение двух диапазонов:
=Материалы март
Конечно, пробел будет работать и с обычными диапазонами, а не именами. Повторимся: это вряд ли будет использоваться часто, но это хороший пример того, что любой символ в формулах может что-то значить, пока не взят в кавычки.
В Google Таблицах оператор пересечения диапазонов не работает.
Ссылка на другой лист в Excel и Google Таблицах выглядит так: название листа, восклицательный знак, ссылка на диапазон (ячейку).
'название листа'!ссылка на диапазон
Название листа отделяется от ячейки или диапазона восклицательным знаком. Название листа берется в апострофы:
'Лемур'!A1:B300 — это ссылка на диапазон A1:B300 на листе «Выручка».
'Продажи Лемур'!D7 — это ссылка на ячейку D7 на листе «Продажи Лемур».
Если название листа состоит из одного слова (если в нем нет пробелов, дефисов, плюсов — таких символов, которые могут быть частью формулы в Excel и интерпретироваться соответственно) — апострофы не обязательны.
То есть будут работать оба варианта:
'Лемур'!A1:B300
Лемур! A1:B300
А такой вариант ссылки работать не будет:
Продажи Лемур! D7
Необязательно вводить ссылки на другие листы вручную. Достаточно щелкнуть на ярлык листа при вводе формулы, чтобы перейти на него, и далее выделить ячейку или диапазон. Ссылка сформируется автоматически.
На именованный диапазон (если область его действия — вся книга) можно ссылаться с других листов без имени.
Также на таблицы (смотрите соответствующую главу «Таблицы Excel» ниже) можно ссылаться с других листов по имени таблицы и названию столбца без упоминания названия листа.
Файл с примером: Несколько листов.xlsx
А что, если нужно обработать данные с нескольких листов формулой? Например, сложить несколько ячеек с группы листов или рассчитать среднее значение?
Чтобы ссылаться на группу листов в формулах, нужно указать их через двоеточие — первый и последний (в том порядке, в каком идут их ярлыки). А затем — как в обычной ссылке на другой лист — восклицательный знак и адрес ячейки/диапазона.
Допустим, нам нужна сумма чисел из ячеек B2 на листах от «Москва» до «Казань». Формула получится такой:
=СУММ(Москва: Казань! B2)
Обратите внимание, что, если после ввода формулы порядок листов поменяется (вы переместите какие-то листы), формула не изменится: она по-прежнему будет суммировать данные с группы листов, первый (левый ярлык) из которых называется «Москва», а последний (самый правый ярлык) — «Казань».
То есть если между этими листами добавится новый, то данные с него тоже попадут в расчет. Если, наоборот, какой-то из листов окажется левее «Москвы» или «Казани», то есть покинет эту группу, данные с него не будут суммироваться.
P. S. Если в названии хотя бы одного из крайних листов есть пробелы/дефисы, то нужно будет взять оба названия в апострофы (как и в случае со ссылкой на один лист с этими символами):
=СУММ('Санкт-Петербург: Казань'!B2:B4)
Файл с примером: Ссылка на листы с символом подстановки.xlsx
Допустим, у вас в книге есть много листов с названиями «Доходы январь», «Доходы февраль», «Расходы январь», «Расходы февраль» или другие группы листов, в которых есть определенные слова. Например, с городами — по два листа (доходы и расходы) на каждый город.
И вам нужно обработать данные на всех листах, например, с расходами — во всех городах. Или с символами HR в названии. Или с определенным городом. И эти листы могут идти не по порядку.
Тогда можно использовать символ подстановки — звездочку: на месте этого символа может быть любой текст (в том числе нулевой длины, то есть ничего).
Символы подстановки (звездочка — любой текст любой длины; знак вопроса — один любой символ) используются в Excel не только для решения этой задачи — они работают в некоторых функциях и инструментах.
Символы подстановки работают по умолчанию в следующих функциях:
— ВПР / VLOOKUP и ПОИСКПОЗ / MATCH;
— СУММЕСЛИ (МН) / SUMIF(S), СЧЁТЕСЛИ(МН) / COUNTIF(S), СРЗНАЧЕСЛИ(МН) / AVERAGEIF(S);
— COUNTUNIQUEIFS;
— в функциях баз данных: БДСУММ / DSUM, ДСРЗНАЧ / DAVERAGE, БСЧЁТ / DCOUNT, БСЧЁТА / DCOUNTA и других;
— ПОИСК / SEARCH.
В новых функциях ПОИСКПОЗX / XMATCH и ПРОСМОТРX / XLOOKUP символы подстановки по умолчанию не работают! Но у них есть аргумент «режим_сопоставления» (match_mode), в котором имеются следующие варианты:
0 — точный поиск (по умолчанию), символы подстановки не работают;
1 — ближайшее большее значение (или точное совпадение);
-1 — ближайшее меньшее значение (или точное совпадение);
2 — точный поиск с символами подстановки.
Вводим формулу с нужным словом и звездочкой — там, где может встречаться любой текст. Не забудьте про апострофы! После ввода формулы в ней появятся отдельные ссылки на все листы, в которых есть слово «Доходы». Обратите внимание, что если такие листы идут подряд, то будет ссылка на несколько листов сразу (в нашем примере 'Доходы Москва: Доходы Санкт-Петербург', а если отдельно — то будут отдельные ссылки ('Доходы Казань').
Увы, такая ссылка не будет динамической, то есть, если в структуре книги будут изменения и появятся новые листы с нужным словом, они не попадут в формулу. Ссылка со звездочкой трансформируется в конкретные ссылки на отдельные листы в момент ввода формулы.
Это работает только в Excel.
В 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:
'Путь на диске[Имя_книги]Название_листа'!Диапазон
Например:
Чтобы просмотреть, какие есть ссылки на другие книги, нажмите на кнопку «Изменить связи» (Edit Links) на вкладке «Данные».
Здесь можно:
• обновить связь (Update Values), чтобы использовались актуальные данные из источника;
• поменять книгу-источник (Change Source), если он переехал в другую папку на диске;
• открыть книгу-источник (Open Source) или разорвать связь (Break Link); после разрыва связи ссылки на книгу-источник превратятся в значения и обновляться, соответственно, больше не будут.
Кнопка «Запрос на обновление связей» (Startup Prompt) вызовет небольшое диалоговое окно, в котором можно настроить поведение Excel при открытии книги.