Работа с именованным диапазоном в Microsoft Excel

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

Манипуляции с именованными областями

Именованный диапазон — это область ячеек, которой пользователь присвоил определенное имя. В этом случае это имя рассматривается Excel как адрес указанной области. Его можно использовать как часть формул и аргументов функций, а также в специализированных инструментах Excel, таких как «Проверка входных значений».

К названию группы ячеек предъявляются обязательные требования:

• В нем не должно быть пробелов;
• Оно должно начинаться с буквы;
• Его длина не должна превышать 255 символов;
• Он не должен быть представлен координатами A1 или R1C1;
• В книге не должно быть одинаковых имен.

Имя области ячейки может отображаться, если оно выбрано в поле имени, расположенном слева от строки формул.

Если имя диапазона не назначено, в поле выше, когда он выбран, отображается адрес верхней левой ячейки массива.

Создание именованного диапазона

Прежде всего, давайте узнаем, как создать именованный диапазон в Excel.

1. Самый быстрый и простой способ назвать массив — это ввести его в поле имени после выбора соответствующей области. Затем выберите массив и введите в поле имя, которое мы сочтем нужным. Желательно, чтобы содержимое ячеек легко запоминалось и соответствовало. И, конечно же, он должен соответствовать вышеперечисленным обязательным требованиям.
2. Чтобы программа ввела это имя в свой реестр и запомнила его, нажмите клавишу Enter. Заголовок будет присвоен выбранной области ячеек.

Выше был назван самый быстрый способ назвать массив, но он далеко не единственный. Эту процедуру также можно выполнить через контекстное меню

1. Выберите массив, над которым вы хотите выполнить операцию. Щелкните выделение правой кнопкой мыши. В открывшемся списке перестаньте выбирать опцию «Дать имя…».
2. Откроется окно для создания имени. Имя должно быть введено в поле «Имя» в соответствии с вышеуказанными условиями. В области «Диапазон» отображается адрес выбранного массива. Если вы сделали свой выбор правильно, вам не нужно вносить никаких изменений в эту область. Щелкните кнопку «ОК».
3. Как вы можете видеть в поле имени, имя области было успешно присвоено.

Другой вариант сделать это — использовать инструменты на ленте.

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

Последний вариант, который мы рассмотрим для наименования области ячейки, — это использование диспетчера имен.

1. Назначьте массив. На вкладке «Формулы» щелкните большой значок «Диспетчер имен», расположенный в той же группе «Определенные имена». В качестве альтернативы вы можете использовать сочетание клавиш Ctrl + F3.
2. Окно управления именами активировано. В нем следует нажать кнопку «Создать…» в верхнем левом углу.
3. Затем запускается уже знакомое окно создания файлов, в котором нужно произвести те манипуляции, о которых говорилось выше. Имя, которое будет присвоено массиву, отобразится в Диспетчере. Его можно закрыть, нажав на стандартную кнопку закрытия в правом верхнем углу.

Операции с именованными диапазонами

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

На одном листе у нас есть список моделей компьютерной техники. Наша задача — создать выпадающий список из этого списка на втором листе таблицы.

1. Прежде всего, на листе списка назовите диапазон одним из описанных выше способов. Следовательно, когда список выбран, имя этой матрицы должно отображаться в поле имени. Пусть это будет имя «Модель».
2. Далее переходим к листу, где находится таблица, в которой нам нужно создать выпадающий список. Выберите в таблице область, в которой мы планируем реализовать выпадающий список. Перейдите на вкладку «Данные» и нажмите кнопку «Проверить данные» на панели инструментов «Обработка данных» на ленте.
3. В открывшемся окне проверки данных перейдите на вкладку «Параметры». В поле «Тип данных» выберите значение «Список». В поле «Источник» в обычном случае нужно вручную ввести все элементы будущего выпадающего списка или указать ссылку на их список, если он есть в документе. Это не очень удобно, особенно если список находится на другом листе. Но в нашем случае все намного проще, так как мы присвоили имя соответствующему массиву. Поэтому просто ставим знак «равно» и пишем это имя в поле. Получаем следующее выражение:

   = Модели

   Щелкните «ОК».

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

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

Итак, у нас есть таблица с ежемесячной выручкой пяти филиалов предприятия. Нам необходимо знать общую выручку Филиала 1, Филиала 3 и Филиала 5 за весь период, указанный в таблице.

1. Прежде всего, назовем каждую строку соответствующей ветки в таблице. Для Филиала 1 выберите область с ячейками, содержащими данные о его доходе за 3 месяца. Выделив поле имени, введите имя «Branch_1» (не забывайте, что имя не может содержать пробелов) и нажмите клавишу Enter. Будет присвоено название соответствующей области. При желании вы можете использовать любой из других вариантов именования, описанных выше.
2. Таким же образом, выделяя соответствующие области, даем названия линиям других веток: «Рамо_2», «Рамо_3», «Рамо_4», «Рамо_5».
3. Выбираем элемент листа, в котором будет отображаться сумма. Щелкните значок «Вставить функцию».
4. Мастер функций запускается. Перейдем к блоку «Математический». Останавливаем выбор из списка доступных операторов по имени «СУММ».
5. Окно аргументов оператора SUM активировано. Эта функция, входящая в группу математических операторов, специально разработана для добавления числовых значений. Синтаксис представлен следующей формулой:

   = СУММ (число1; число2;…)

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

   Всего у оператора SUM может быть от одного до 255 аргументов. Но в нашем случае нам нужно всего три аргумента, так как мы добавим три диапазона: «Ramo_1», «Ramo_3» и «Ramo_5».

   Затем мы помещаем курсор в поле «Число 1». Поскольку мы дали имена диапазонам, которые необходимо добавить, нет необходимости вводить координаты в поле или выбирать соответствующие области на листе. Просто укажите имя добавляемого массива: «Branch_1». В полях «Number2» и «Number3» соответственно создаем запись «Ramo_3» и «Ramo_5». После выполнения описанных выше манипуляций нажимаем «ОК».

6. Результат расчета отображается в выбранной ячейке перед входом в мастер.

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

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

Управление именованными диапазонами

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

1. Для перехода в Диспетчер перейдите на вкладку «Формулы». Там вы должны нажать на значок, который называется «Диспетчер имен». Указанный значок находится в группе «Определенные имена».
2. Зайдя в Диспетчер, чтобы произвести необходимые манипуляции с диапазоном, нужно найти его название в списке. Если список предметов невелик, это сделать достаточно просто. Но если текущая книга содержит несколько десятков и более именованных массивов, имеет смысл использовать фильтр для упрощения задачи. Нажмите кнопку «Фильтр», расположенную в правом верхнем углу окна. Фильтрацию можно производить по следующим направлениям, выбрав соответствующий пункт в открывшемся меню:
   • Имена на листе;
   • в книге;
   • с ошибками;
   • без ошибок;
   • Некоторые имена;
   • Имена таблиц.

   Чтобы вернуться к полному списку элементов, просто выберите опцию «Очистить фильтр».

3. Чтобы изменить границы, имя или другие свойства именованного диапазона, выберите нужный элемент в Диспетчере и нажмите кнопку «Изменить…».
4. Откроется окно для изменения названия. Он содержит точно такие же поля, как и окно для создания именованного диапазона, о котором мы говорили ранее. Только на этот раз поля будут заполнены данными.

   В поле «Название» вы можете изменить название области. В поле «Заметка» вы можете добавить или отредактировать существующую заметку. В поле «Диапазон» вы можете изменить адрес именованного массива. Это можно сделать либо вручную введя требуемые координаты, либо поместив курсор в поле и выделив соответствующий массив ячеек на листе. Ваш адрес сразу появится в поле. Единственное поле, которое нельзя изменить, — это «Регион».

   После изменения данных нажмите кнопку «ОК».

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

Это очень важно, потому что если вы уже использовали удаленное имя в какой-либо формуле, после удаления имени эта формула станет неверной.

1. Чтобы выполнить процедуру удаления, выберите нужную запись из списка и нажмите кнопку «Удалить».
2. Затем открывается диалоговое окно с просьбой подтвердить свое решение удалить выбранный элемент. Это сделано для предотвращения случайного выполнения пользователем этой процедуры. Итак, если вы уверены в необходимости удаления, вам необходимо нажать кнопку «ОК» в окне подтверждения. Если нет, нажмите кнопку «Отмена».
3. Как видите, выбранный элемент был удален из списка диспетчера. Это означает, что массив, к которому он был прикреплен, потерял свое имя. Теперь его можно будет идентифицировать только по координатам. После того, как все манипуляции в Диспетчере завершены, нажмите кнопку «Закрыть», чтобы завершить работу в окне.

Использование именованного диапазона может упростить использование формул, функций и других инструментов Excel. Одноименные элементы можно проверять (редактировать и удалять) с помощью специального интегрированного Менеджера.