Создание и использование именных диапазонов в Microsoft Excel

Именные диапазоны в Microsoft Excel представляют собой сохраненные группы ячеек из одного столбца или строки, для которых пользователь сам задает имя. По сути, это имя является ссылкой на диапазон и может применяться для его обозначения при использовании в формулах или при реализации других задач в Экселе, включая построение диаграмм или проверку данных. В этом материале предлагаю комплексно разобраться с именными диапазонами. Начнем с методов их создания, продолжим простыми примерами использования и закончим редактированием и просмотром всех существующих ссылок.

Методы создания именных диапазонов

Обычно пользователи узнают один доступный метод реализации нужной задачи и далее применяют его по необходимости. В случае с именными диапазонами таких методов есть целых три. Давайте быстро пробежимся по каждому из них, чтобы далее вы могли понять, какой лучше выбрать и уже использовать во время стандартного взаимодействия с Microsoft Excel.

Далее каждый раз предлагаю не возвращаться к теме наименования для диапазонов, поэтому основную информацию разберем сейчас. Регистр символов соблюдать не обязательно, но не рекомендуется создавать именные диапазоны с названиями, которые соответствуют именам функций, поскольку это может вызвать конфликты при дальнейшем применении. В остальном же используйте оптимальные для своих проектов названия на подходящем языке.

Способ 1: Выпадающий список имен

Пожалуй, самый быстрый способ создания именного диапазона – переименование его через выпадающий список диапазонов. Из него в дальнейшем можно будет и выбирать все существующие диапазоны, чтобы переключаться между ними в рамках одного листа. Впрочем, начнем именно с создания.

1. В первую очередь выделите весь необходимый диапазон, затем активируйте курсор в выпадающем списке слева сверху.

2. Активируется строка ввода, поэтому можете удалить текущее название ячейки и создать свой первый именной диапазон. Обязательно нажмите Enter, чтобы применить изменения.

3. Теперь вы можете проверить успешность выполненных действий. Для этого достаточно выделить этот же самый диапазон и посмотреть, как его название отображается в этом же выпадающем списке.

4. Список можно развернуть в любой момент, чтобы просмотреть все существующие именные диапазоны и переключиться между ними.

Единственный минус этого метода – по умолчанию выбирается область применения диапазона «‎Книга», а также для него не будет никаких примечаний, что, в целом, используется не так часто. Однако переживать не стоит, поскольку в следующих методах таких недостатков не будет.

Способ 2: Контекстное меню диапазона

Через контекстное меню ячеек можно выполнить много различных действий: настроить сортировку, фильтрацию, добавить примечания или поменять их формат. Среди всего прочего есть и функция присвоения имени диапазону, которой мы и рекомендуем воспользоваться далее в качестве альтернативы предыдущему варианту.

1. Выделите необходимое количество ячеек из одной строки или столбца, из которых должен быть сформирован именной диапазон. Щелкните по любой из выделенных ячеек правой кнопкой мыши и выберите из контекстного меню пункт «‎Присвоить имя».

2. Отобразится отдельное окно создания имени, в котором задайте то самое название и выберите область применения, если хотите использовать этот же диапазон на других листах.

3. Можно добавить и примечание для данного диапазона, состоящее из нескольких предложений для справки. К сожалению, просмотреть его можно будет только через Диспетчер имен, о котором мы еще поговорим позже.

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

Способ 3: Диспетчер имен

Кажется, что использование Диспетчера имен – самый длительный процесс по добавлению названия для диапазона, если сравнивать с двумя предыдущими. Однако он тоже может быть полезен. О самом Диспетчере я еще расскажу ниже, поскольку он будет оптимален для просмотра всех существующих диапазонов и их редактирования. Сейчас же остановимся только на создании имени.

1. Выделите нужный диапазон, перейдите на вкладку «‎Формулы», разверните меню «‎Определенные имена» и выберите одно из действий. Можете открыть «‎Диспетчер имен» или нажать кнопку «‎Задать имя», чтобы открыть уже знакомое окно.

2. В нем установите оптимальные настройки, еще раз проверьте диапазон и сохраните изменения.

3. Вторая ситуация – открытие «‎Диспетчера имен».

4. Тогда нужно нажать «‎Создать», чтобы приступить к присвоению наименования для диапазона.

5. Откроется то же самое окно, в котором можете задать требуемые свойства для именного диапазона.

Далее от вас требуется только выбрать один из представленных методов, чтобы в будущем создавать нужное количество именных диапазонов и использовать их в своих целях.

Примеры использования именных диапазонов

Обычно именные диапазоны используются для удобной сортировки данных или в формулах, чтобы не вводить их полные ссылки. Реже они применяются в диаграммах или сводных таблицах на других листах. Я предлагаю в рамках этой статьи остановиться на двух простых примерах, чтобы вы понимали, как можно далее поступить с созданными именными диапазонами.

Создаем выпадающий список

Допустим, значения в вашей таблице, находящиеся в рамках одного столбца, могут быть переменными. Как раз все возможные значения и создают общий именной диапазон. Его можно использовать для создания выпадающего списка со всеми доступными для выбора значениями на этом или другом листе вашей книги. Полностью процесс создания такого списка выглядит следующим образом:

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

2. Тип данных для проверки установите как «‎Список», выбрав нужное значение, затем в качестве источника задайте созданный именной диапазон.

3. Дополнительные подсказки по вводу и сообщения об ошибке в этом случае не нужны, поскольку пользователь не сможет выполнить какое-то неправильное действие. Однако если ваша задача отличается от текущей и проверка данных реализуется для других целей, возможно, в этих вкладках понадобится внести какие-то изменения для удобства.

4. После применения проверки данных вы обратите внимание на то, что рядом с каждой ячейкой диапазона есть кнопка со стрелкой вниз. Нажмите по ней, чтобы посмотреть все доступные значения.

5. Можете выбрать любое из них, тут присутствуют только те, что есть в именном диапазоне. Соответственно, список будет расширяться, если именной диапазон в будущем будет пополнен новыми значениями.

Такой вариант оптимально использовать вместо сортировки или инструментов фильтрации, когда те неуместны или не получается осуществить задуманное. В идеале, сам именной диапазон должен находиться на одном листе, а этот выпадающий список – на другом. Так можно реализовать какие-то задачи в сводной таблице или в отчетах.

Создаем формулы с именными диапазонами

При создании формул, если нет именных диапазонов, пользователь, желая задать определенный перечень ячеек, обычно вводит значения в формате A1:A10. Вы наверняка видели такие структуры много раз. При наличии именных диапазонов такую ссылку можно заменить просто на имя, что позволит проще ориентироваться в формулах, быстрее составлять их и не путаться в том, где и какой диапазон используется.

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

2. Далее выполните математическую операцию и нажмите Enter, чтобы применить изменения. Вы увидите, как напротив каждого значения отобразился результат математической операции.

3. Вы можете использовать имена при составлении сложных формул с условными или любыми другими функциями. Например, как это показано на следующем скриншоте.

4. Формула может быть любой длины, в ней можно использовать несколько именных диапазонов.

5. Поддерживаются даже математические с именными диапазонами. Можно перемножить каждое значение, вычесть, сложить или получить процентное значение.

Конечно, это далеко не все. Можете создавать различные диаграммы и графики, указывая там не перечень ячеек, а целый именной диапазон. Ссылайтесь на них в других листах, редактируйте в любой момент и обращайтесь к стандартным операциям с функциями, как это было показано только что.

Просмотр и редактирование определенных имен

Если значений в таблице присутствует огромное количество, может возникнуть необходимость просмотреть все созданные именные диапазоны или же внести в них изменения. Все это осуществляется через уже знакомый Диспетчер имен. Ничего сложного в этом нет, поэтому давайте быстро пробежимся по основным элементам данного инструмента.

1. Найдите его на вкладке «‎Формулы», развернув меню «‎Определенные имена».

2. В новом окне можете применить фильтры, оставив имена на листе или книге, только те, которые содержат ошибки, или же имена таблиц, если такие были созданы.

3. Все подходящие значения отображаются списком. Вы видите название диапазона, список его значений, диапазон, область применения и примечания, если такие были созданы.

4. Сверху есть кнопки для создания новых именных диапазонов, изменения выделенного или его удаления.

5. Окно изменения и создания диапазонов вам уже знакомо, поэтому останавливаться на нем не будем.

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