7 причин полюбить именованные диапазоны

Главная » Статьи » Excel » Различные инструменты Excel

Понятие именованного диапазона

Мы привыкли ссылаться на ячейки и диапазоны по их адресам. Например, A5 или B2:D17. Однако, не все знают, что диапазону можно присвоить символьное имя и использовать его в качестве полноценной замены привычных адресов в формулах. На первый взгляд это дополнительные манипуляции, которые не приносят очевидной пользы, но это не так. Мы с вами разберём, как минимум, 7 причин, почему стоит использовать именованные диапазоны.

Я считаю, что умение оперировать именованными диапазонами (далее ИД) и понимание, для чего они нужны, одни из ключевых навыков квалифицированного пользователя Excel.

Создаём ИД

Создать ИД можно двумя способами:

Быстрый способ. Выделяем необходимый диапазон или ячейку. Идём в поле адреса и вводим там имя создаваемого ИД.

Стандартный способ. Выделяем диапазон, а потом выбираем пункт меню Присвоить имя с ленты ФОРМУЛЫ. В диалоговом окне вводим желаемое имя, меняем или оставляем без изменений область действия, примечание, адрес диапазона.

Причина первая

Сравните 2 формулы:

=ИНДЕКС(Товары!$A$2:$E$26;ПОИСКПОЗ($I$3;Товары!$B$2:$B$26;0);4)*(1.18)

=ИНДЕКС(Товары;ПОИСКПОЗ(PrdSelected;Продукт;0);4)*(1+VAT_Rate)

В каком случае у вас больше шансов интуитивно догадаться, что мы вычисляем? Ответ очевиден - во втором случае. Потому что вместо скучных и безликих координат диапазонов, вы видите идентификаторы, которые рождают у вас некоторые ассоциации: Товары - это наверняка таблица с информацией о товарах, PrdSelected - это, видимо, выбранный пользователем продукт, Продукт - какой-то справочник продуктов, а VAT_Rate - без сомнения ставка НДС.

ИД делают формулы более дружественными к пользователю, наглядными и интуитивно понятными!

У вас не рябит в глазах от букв, цифр и знаков доллара в адресах диапазонов.
Причина вторая

Предположим у вас на листе 500 формул ВПР (VLOOKUP), которые ссылаются на один и тот же диапазон ячеек, из которого вы извлекаете данные. В случае изменения координат этого диапазона, вы будете вынуждены исправить его координаты в пятистах формулах. Безусловно, это можно сделать при помощи инструмента Поиск с заменой, но факт остаётся фактом - так или иначе вам надо менять формулы в большом количестве ячеек. А теперь представьте, что вы ссылаетесь в формулах на ИД. При изменении координат ИД, вы ОДИН раз изменяете его координаты в Диспетчере имён. При этом нет необходимости исправлять какие бы то ни было формулы. Это реальная экономия вашего времени!

Отпадает необходимость править формулы в ячейках из-за изменения адресов диапазонов.
Причина третья

Объявив ИД, вы можете его легко вставить в формулу. Есть 3 возможности:
1. Вызвать диалоговое окно Вставка имени (горячая клавиша F3);
2. Начав набор названия ИД с клавиатуры и завершив выбором из раскрывшегося списка объектов;
3. Воспользовавшись пунктом меню Использовать в формуле на ленте ФОРМУЛЫ.
Формулы вводятся быстрее, приятнее и у вас меньше шансов внести в формулу ошибку.
Причина четвёртая

Используя обычные адреса диапазонов вы всегда должны думать, какой тип адресации применить, чтобы формула не поползла при протягивании или, напротив, поползла в нужном направлении. Речь идёт об относительных и абсолютных ссылках. Адес диапазона, введеный как A1:B5 при распространении (копировании) формулы вниз в следующей ячейке превратится в A2:B6, потом в A3:B7 и т.д. Поэтому при вводе вы должны использовать абсолютную ссылку вида $A$1:$B$5, от которых особенно рябит в глазах. Ссылки в виде ИД лишены данного недостатка.

Нет проблем при протягивании формул.
Причина пятая

ИД позволяют реализовать динамические диапазоны, которые подстраиваются под фактические размеры вашей таблицы. Это очень удобно, когда вы добавляете к какому-либо справочнику новые строки или столбцы. Делается это при помощи формул СМЕЩ+СЧЁТЗ (OFFSET+COUNTA), так:

=СМЕЩ(Товары!$A$1;1;0;СЧЁТЗ(Товары!$A:$A)-1;СЧЁТЗ(Товары!$1:$1)), где
- Товары!$A$1 - точка отсчёта. Берите верхний левый угол таблицы;
- 1 - смещение по строкам для верхнего левого угла ИД. Поскольку у нас таблица с заголовками, то данные начинаются на 1 строку ниже, относительно указанной точки отсчёта;
- 0 - смещение по столбцам для верхнего левого угла ИД. Его у нас нет.
- СЧЁТЗ(Товары!$A:$A)-1 - эта функция подсчитывает количество непустых строк в указанном диапазоне ($A:$A), а единицу вычитаем за счёт пропущенной строки с заголовками столбцов. При расширении таблицы вниз колонка A должна обязательно заполняться данными, либо выберите другой столбец, для которого это условие будет выполняться;
- СЧЁТЗ(Товары!$1:$1) - подсчёт количества непустых столбцов по тому же принципу (на основе строки 1:1).
Проконтролировать правильно ли вы написали формулу, можно зайдя в Диспетчер имён, выделив нужный ИД и встав курсором на формулу. При этом сам диапазон должен выделиться пунктирной линией.

Возможность задать динамические границы при определении ИД.
Причина шестая

ИД облегчают перенос формул на другие листы, в другие книги, либо перенос данных в другое место. Особенно удобно использовать ИД, когда вы ссылаетесь на другую книгу, потому что видеть в формулах длиннющие префиксы с путём к файлу книги, содержащий нужный диапазон, не составляет никакого удовольствия. Причём ИД в этом случае определять надо в той книге, в которой вы строите формулы, так как формула может использовать только ИД, определенный в родной книге. А сам ИД будет ссылаться на другую книгу.

Облегчение ссылок на другие книги, упрощение переноса формул на другие листы/книги.
Причина седьмая

В Excel 2007 и ранее нельзя было определить в Проверке данных (Data Validation) список (list), который бы ссылался на диапазон, располагающийся на другом листе. То есть вы не могли указать в качестве источника значений списка =Лист4!$A$2:$A$4, а надо было обязательно определить ИД, и вот только так можно было сослаться на другой лист.

Ссылка на источник данных для списка в функции Проверка данных.

Управление ИД

Осуществляется при помощи данного блока меню на ленте ФОРМУЛЫ:

Получение списка всех ИД

Через диалог Вставка имени (F3 или через меню Использовать в формуле - Вставить имена...) можно получить в ячейки таблицы список всех ИД. Это выглядит так:

Замена обычных диапазонов на ИД

Если вы сначала написали формулы, а потом решили определить ИД, то обычные координаты диапазонов можно заменить на ИД через меню Присвоить имя, диалог Применить имена...

До

После

Массовое создание ИД

ИД можно создавать массово на основе ваших таблицы и заголовков столбцов/строк. Выделите необходимый диапазон данных и выберите пункт меню Создать из выделенного. Будет предложен такой диалог:

Укажите в нём, где располагаются имена ваших ИД. Если вы укажете, что имена расположены в верхней строке, то Excel создаст ИД для каждой строки и назовёт их теми идентификаторами, которые в верхней строке располагаются. В предложенном примере мы имеем 5 столбцов и после нажатия OK можно проконтролировать, что создано 5 ИД с соответствующими именами:

Тоже самое можно проделать и со строками, если на то есть необходимость.

Именованные константы

Помимо ссылок на ячейки в ИД можно указать ссылку на числовую константу. Например:

Именованную константу можно использовать в формулах наравне с другими числами, однако её имя будет вам напоминать о её назначении, а в случае необходимости вы всегда легко её сможете изменить без необходимости правки формул.

P.S.

Многими возможнотями ИД (и даже больше) обладают также умные таблицы.

Понятие именованного диапазона

Создаём ИД