Одним из инструментов, который упрощает работу с формулами и позволяет оптимизировать работу с массивами данных, является присвоение этим массивам наименования. Таким образом, если вы хотите сослаться на диапазон однородных данных, то не нужно будет записывать сложную ссылку, а достаточно указать простое название, которым вы сами ранее обозначили определенный массив. Давайте выясним основные нюансы и преимущества работы с именованными диапазонами.
Манипуляции с именованными областями
Именованный диапазон — это область ячеек, которой пользователем присвоено определенное название. При этом данное наименование расценивается Excel, как адрес указанной области. Оно может использоваться в составе формул и аргументов функций, а также в специализированных инструментах Excel, например, .
Существуют обязательные требования к наименованию группы ячеек:
- В нём не должно быть пробелов;
- Оно обязательно должно начинаться с буквы;
- Его длина не должна быть больше 255 символов;
- Оно не должно быть представлено координатами вида или ;
- В книге не должно быть одинаковых имен.
Наименование области ячеек можно увидеть при её выделении в поле имен, которое размещено слева от строки формул.
В случае, если наименование диапазону не присвоено, то в вышеуказанном поле при его выделении отображается адрес левой верхней ячейки массива.
Создание именованного диапазона
Прежде всего, узнаем, как создать именованный диапазон в Экселе.
- Самый быстрый и простой вариант присвоения названия массиву – это записать его в поле имен после выделения соответствующей области. Итак, выделяем массив и вводим в поле то название, которое считаем нужным. Желательно, чтобы оно легко запоминалось и отвечало содержимому ячеек. И, безусловно, необходимо, чтобы оно отвечало обязательным требованиям, которые были изложены выше.
- Для того, чтобы программа внесла данное название в собственный реестр и запомнила его, жмем по клавише . Название будет присвоено выделенной области ячеек.
Выше был назван самый быстрый вариант наделения наименованием массива, но он далеко не единственный. Эту процедуру можно произвести также через контекстное меню
- Выделяем массив, над которым требуется выполнить операцию. Клацаем по выделению правой кнопкой мыши. В открывшемся списке останавливаем выбор на варианте .
- Открывается окошко создания названия. В область следует вбить наименование в соответствии с озвученными выше условиями. В области отображается адрес выделенного массива. Если вы провели выделение верно, то вносить изменения в эту область не нужно. Жмем по кнопке .
- Как можно видеть в поле имён, название области присвоено успешно.
Ещё один вариант выполнения указанной задачи предусматривает использование инструментов на ленте.
- Выделяем область ячеек, которую требуется преобразовать в именованную. Передвигаемся во вкладку . В группе производим клик по значку .
- Открывается точно такое же окно присвоения названия, как и при использовании предыдущего варианта. Все дальнейшие операции выполняются абсолютно аналогично.
Последний вариант присвоения названия области ячеек, который мы рассмотрим, это использование .
- Выделяем массив. На вкладке , клацаем по крупному значку , расположенному всё в той же группе . Или же можно вместо этого применить нажатие сочетания клавиш .
- Активируется окно . В нем следует нажать на кнопку в верхнем левом углу.
- Затем запускается уже знакомое окошко создания файлов, где нужно провести те манипуляции, о которых шёл разговор выше. То имя, которое будет присвоено массиву, отобразится в . Его можно будет закрыть, нажав на стандартную кнопку закрытия в правом верхнем углу.
Урок: Как присвоить название ячейке в Экселе
Операции с именованными диапазонами
Как уже говорилось выше, именованные массивы могут использоваться во время выполнения различных операций в Экселе: формулы, функции, специальные инструменты. Давайте на конкретном примере рассмотрим, как это происходит.
На одном листе у нас перечень моделей компьютерной техники. У нас стоит задача на втором листе в таблице сделать выпадающий список из данного перечня.
- Прежде всего, на листе со списком присваиваем диапазону наименование любым из тех способов, о которых шла речь выше. В итоге, при выделении перечня в поле имён у нас должно отображаться наименование данного массива. Пусть это будет наименование .
- После этого перемещаемся на лист, где находится таблица, в которой нам предстоит создать выпадающий список. Выделяем область в таблице, в которую планируем внедрить выпадающий список. Перемещаемся во вкладку и щелкаем по кнопке в блоке инструментов на ленте.
- В запустившемся окне проверки данных переходим во вкладку . В поле выбираем значение . В поле в обычном случае нужно либо вручную вписать все элементы будущего выпадающего списка, либо дать ссылку на их перечень, если он расположен в документе. Это не очень удобно, особенно, если перечень располагается на другом листе. Но в нашем случае все намного проще, так как мы соответствующему массиву присвоили наименование. Поэтому просто ставим знак и записываем это название в поле. Получается следующее выражение:
=Модели
Жмем по .
- Теперь при наведении курсора на любую ячейку диапазона, к которой мы применили проверку данных, справа от неё появляется треугольник. При нажатии на этот треугольник открывается список вводимых данных, который подтягивается из перечня на другом листе.
- Нам просто остается выбрать нужный вариант, чтобы значение из списка отобразилось в выбранной ячейке таблицы.
Именованный диапазон также удобно использовать в качестве аргументов различных функций. Давайте взглянем, как это применяется на практике на конкретном примере.
Итак, мы имеем таблицу, в которой помесячно расписана выручка пяти филиалов предприятия. Нам нужно узнать общую выручку по Филиалу 1, Филиалу 3 и Филиалу 5 за весь период, указанный в таблице.
- Прежде всего, каждой строке соответствующего филиала в таблице присвоим название. Для Филиала 1 выделяем область с ячейками, в которых содержатся данные о выручке по нему за 3 месяца. После выделения в поле имен пишем наименование (не забываем, что название не может содержать пробел) и щелкаем по клавише . Наименование соответствующей области будет присвоено. При желании можно использовать любой другой вариант присвоения наименования, о котором шел разговор выше.
- Таким же образом, выделяя соответствующие области, даем названия строкам и других филиалов: , , , .
- Выделяем элемент листа, в который будет выводиться итог суммирования. Клацаем по иконке .
- Инициируется запуск . Производим перемещение в блок . Останавливаем выбор из перечня доступных операторов на наименовании .
- Происходит активация окошка аргументов оператора . Данная функция, входящая в группу математических операторов, специально предназначена для суммирования числовых значений. Синтаксис представлен следующей формулой:
=СУММ(число1;число2;…)
Как нетрудно понять, оператор суммирует все аргументы группы . В виде аргументов могут применяться, как непосредственно сами числовые значения, так и ссылки на ячейки или диапазоны, где они расположены. В случае применения массивов в качестве аргументов используется сумма значений, которая содержится в их элементах, подсчитанная в фоновом режиме. Можно сказать, что мы «перескакиваем», через действие. Именно для решения нашей задачи и будет использоваться суммирование диапазонов.
Всего оператор может насчитывать от одного до 255 аргументов. Но в нашем случае понадобится всего три аргумента, так как мы будет производить сложение трёх диапазонов: , и .
Итак, устанавливаем курсор в поле . Так как мы дали названия диапазонам, которые требуется сложить, то не нужно ни вписывать координаты в поле, ни выделять соответствующие области на листе. Достаточно просто указать название массива, который подлежит сложению: . В поля и соответственно вносим запись и . После того, как вышеуказанные манипуляции были сделаны, клацаем по .
- Результат вычисления выведен в ячейку, которая была выделена перед переходом в .
Как видим, присвоение названия группам ячеек в данном случае позволило облегчить задачу сложения числовых значений, расположенных в них, в сравнении с тем, если бы мы оперировали адресами, а не наименованиями.
Конечно, эти два примера, которые мы привели выше, показывают далеко не все преимущества и возможности применения именованных диапазонов при использовании их в составе функций, формул и других инструментов Excel. Вариантов использования массивов, которым было присвоено название, неисчислимое множество. Тем не менее, указанные примеры все-таки позволяют понять основные преимущества присвоения наименования областям листа в сравнении с использованием их адресов.
Урок: Как посчитать сумму в Майкрософт Эксель
Управление именованными диапазонами
Управлять созданными именованными диапазонами проще всего через . При помощи данного инструмента можно присваивать имена массивам и ячейкам, изменять существующие уже именованные области и ликвидировать их. О том, как присвоить имя с помощью мы уже говорили выше, а теперь узнаем, как производить в нем другие манипуляции.
- Чтобы перейти в , перемещаемся во вкладку . Там следует кликнуть по иконке, которая так и называется . Указанная иконка располагается в группе .
- После перехода в для того, чтобы произвести необходимую манипуляцию с диапазоном, требуется найти его название в списке. Если перечень элементов не очень обширный, то сделать это довольно просто. Но если в текущей книге располагается несколько десятков именованных массивов или больше, то для облегчения задачи есть смысл воспользоваться фильтром. Клацаем по кнопке , размещенной в правом верхнем углу окна. Фильтрацию можно выполнять по следующим направлениям, выбрав соответствующий пункт открывшегося меню:
- Имена на листе;
- в книге;
- с ошибками;
- без ошибок;
- Определенные имена;
- Имена таблиц.
Для того, чтобы вернутся к полному перечню наименований, достаточно выбрать вариант .
- Для изменения границ, названия или других свойств именованного диапазона следует выделить нужный элемент в и нажать на кнопку .
- Открывается окно изменение названия. Оно содержит в себе точно такие же поля, что и окно создания именованного диапазона, о котором мы говорили ранее. Только на этот раз поля будут заполнены данными.
В поле можно сменить наименование области. В поле можно добавить или отредактировать существующее примечание. В поле можно поменять адрес именованного массива. Существует возможность сделать, как применив ручное введение требуемых координат, так и установив курсор в поле и выделив соответствующий массив ячеек на листе. Его адрес тут же отобразится в поле. Единственное поле, значения в котором невозможно отредактировать – .
После того, как редактирование данных окончено, жмем на кнопку .
Также в при необходимости можно произвести процедуру удаления именованного диапазона. При этом, естественно, будет удаляться не сама область на листе, а присвоенное ей название. Таким образом, после завершения процедуры к указанному массиву можно будет обращаться только через его координаты.
Это очень важно, так как если вы уже применяли удаляемое наименование в какой-то формуле, то после удаления названия данная формула станет ошибочной.
- Чтобы провести процедуру удаления, выделяем нужный элемент из перечня и жмем на кнопку .
- После этого запускается диалоговое окно, которое просит подтвердить свою решимость удалить выбранный элемент. Это сделано во избежание того, чтобы пользователь по ошибке не выполнил данную процедуру. Итак, если вы уверены в необходимости удаления, то требуется щелкнуть по кнопке в окошке подтверждения. В обратном случае жмите по кнопке .
- Как видим, выбранный элемент был удален из перечня . Это означает, что массив, к которому он был прикреплен, утратил наименование. Теперь он будет идентифицироваться только по координатам. После того, как все манипуляции в завершены, клацаем по кнопке , чтобы завершить работу в окне.
Применение именованного диапазона способно облегчить работу с формулами, функциями и другими инструментами Excel. Самими именованными элементами можно управлять (изменять и удалять) при помощи специального встроенного .
Мы рады, что смогли помочь Вам в решении проблемы.
Помимо этой статьи, на сайте еще 13048 полезных инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам. Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.