Ознакомительная версия. Доступно 12 страниц из 57
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 — точный поиск с символами подстановки.
Вводим формулу с нужным словом и звездочкой — там, где может встречаться любой текст. Не забудьте про апострофы! После ввода формулы в ней появятся отдельные ссылки на все листы, в которых есть слово «Доходы». Обратите внимание,
Ознакомительная версия. Доступно 12 страниц из 57
