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

class="z1" alt="" src="images/i_214.jpg"/>

А еще можно просто ввести в этом поле адрес любой ячейки и нажать Enter, чтобы к ней перейти. Кстати, это пригодится, чтобы посмотреть, какие данные есть в скрытой ячейке (и даже отредактировать их).

Также перейти к любой ячейке можно с помощью окна «Переход» (вызывается клавишей F5).

Итак, в чем польза имен:

• формулы легче воспринимать;

• имена можно выбирать из выпадающего списка при вводе формул;

• быстрый переход к именованному диапазону через поле «Имя»;

• абсолютная ссылка на ячейку/диапазон без необходимости использовать знаки доллара.

Кроме того, они будут отображаться при уменьшении масштаба до того уровня, когда смогут поместиться поверх соответствующих диапазонов.

ССЫЛКА НА ПЕРЕСЕЧЕНИЕ ДИАПАЗОНОВ

Файл с примером: Пересечение диапазонов.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 — точный поиск с символами подстановки.

Вводим формулу с нужным словом и звездочкой — там, где может встречаться любой текст. Не забудьте про апострофы! После ввода формулы в ней появятся отдельные ссылки на все листы, в которых есть слово «Доходы». Обратите внимание,