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

В 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 в названии. Или с определенным городом. И эти листы могут идти не по порядку.

Тогда можно использовать символ подстановки — звездочку: на месте этого символа может быть любой текст (в том числе нулевой длины, то есть ничего).