Excel использование именованных диапазонов в формулах

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

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

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

Существуют обязательные требования к наименованию группы ячеек:

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

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

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

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

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

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



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

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

1. Выделяем массив, над которым требуется выполнить операцию. Клацаем по выделению правой кнопкой мыши. В открывшемся списке останавливаем выбор на варианте «Присвоить имя…».



2. Открывается окошко создания названия. В область «Имя» следует вбить наименование в соответствии с озвученными выше условиями. В области «Диапазон» отображается адрес выделенного массива. Если вы провели выделение верно, то вносить изменения в эту область не нужно. Жмем по кнопке «OK».



3. Как можно видеть в поле имён, название области присвоено успешно.

Ещё один вариант выполнения указанной задачи предусматривает использование инструментов на ленте.

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



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

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

1. Выделяем массив. На вкладке «Формулы», клацаем по крупному значку «Диспетчер имен», расположенному всё в той же группе «Определенные имена». Или же можно вместо этого применить нажатие сочетания клавиш Ctrl+F3.



2. Активируется окно Диспетчера имён. В нем следует нажать на кнопку «Создать…» в верхнем левом углу.



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

Урок: Как присвоить название ячейке в Экселе

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

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

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

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



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



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

   =Модели

   Жмем по «OK».



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



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

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

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

1. Прежде всего, каждой строке соответствующего филиала в таблице присвоим название. Для Филиала 1 выделяем область с ячейками, в которых содержатся данные о выручке по нему за 3 месяца. После выделения в поле имен пишем наименование «Филиал_1» (не забываем, что название не может содержать пробел) и щелкаем по клавише Enter. Наименование соответствующей области будет присвоено. При желании можно использовать любой другой вариант присвоения наименования, о котором шел разговор выше.



2. Таким же образом, выделяя соответствующие области, даем названия строкам и других филиалов: «Филиал_2», «Филиал_3», «Филиал_4», «Филиал_5».



3. Выделяем элемент листа, в который будет выводиться итог суммирования. Клацаем по иконке «Вставить функцию».



4. Инициируется запуск Мастера функций. Производим перемещение в блок «Математические». Останавливаем выбор из перечня доступных операторов на наименовании «СУММ».



5. Происходит активация окошка аргументов оператора СУММ. Данная функция, входящая в группу математических операторов, специально предназначена для суммирования числовых значений. Синтаксис представлен следующей формулой:

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

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

   Всего оператор СУММ может насчитывать от одного до 255 аргументов. Но в нашем случае понадобится всего три аргумента, так как мы будет производить сложение трёх диапазонов: «Филиал_1», «Филиал_3» и «Филиал_5».

   Итак, устанавливаем курсор в поле «Число1». Так как мы дали названия диапазонам, которые требуется сложить, то не нужно ни вписывать координаты в поле, ни выделять соответствующие области на листе. Достаточно просто указать название массива, который подлежит сложению: «Филиал_1». В поля «Число2» и «Число3» соответственно вносим запись «Филиал_3» и «Филиал_5». После того, как вышеуказанные манипуляции были сделаны, клацаем по «OK».



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

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

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

Урок: Как посчитать сумму в Майкрософт Эксель

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

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

1. Чтобы перейти в Диспетчер, перемещаемся во вкладку «Формулы». Там следует кликнуть по иконке, которая так и называется «Диспетчер имен». Указанная иконка располагается в группе «Определенные имена».



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

   Для того, чтобы вернутся к полному перечню наименований, достаточно выбрать вариант «Очистить фильтр».



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



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

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

   После того, как редактирование данных окончено, жмем на кнопку «OK».

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

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

1. Чтобы провести процедуру удаления, выделяем нужный элемент из перечня и жмем на кнопку «Удалить».



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



3. Как видим, выбранный элемент был удален из перечня Диспетчера. Это означает, что массив, к которому он был прикреплен, утратил наименование. Теперь он будет идентифицироваться только по координатам. После того, как все манипуляции в Диспетчере завершены, клацаем по кнопке «Закрыть», чтобы завершить работу в окне.

Применение именованного диапазона способно облегчить работу с формулами, функциями и другими инструментами Excel. Самими именованными элементами можно управлять (изменять и удалять) при помощи специального встроенного Диспетчера.

В Excel в Интернете вы можете использовать именованные диапазоны, которые вы определили в Excel для Windows или Mac. Выберите имя в поле «Имя» для перехода к расположению диапазона или использования именованного диапазона в формуле.

На данный момент создание нового именованного диапазона в Excel в Интернете недоступно.

Обычно ссылки на диапазоны ячеек вводятся непосредственно в формулы, например

=СУММ(А1:А10)

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

Назовем

Именованным диапазоном в MS EXCEL,

диапазон ячеек, которому присвоено

Имя

(советуем перед прочтением этой статьи ознакомиться с правилами создания

Имен

).

Преимуществом

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

является его информативность. Сравним две записи одной формулы для суммирования, например, объемов продаж:

=СУММ($B$2:$B$10)

и

=СУММ(Продажи)

. Хотя формулы вернут один и тот же результат (если, конечно, диапазону

B2:B10

присвоено имя

Продажи

), но иногда проще работать не напрямую с диапазонами, а с их именами.

Совет

: Узнать

на какой диапазон ячеек ссылается

Имя

можно через

Диспетчер имен

расположенный в меню

.

Ниже рассмотрим как присваивать имя диапазонам. Оказывается, что диапазону ячеек можно присвоить имя по разному: используя

абсолютную или смешанную адресацию

.

Задача1 (Именованный диапазон с абсолютной адресацией)

Пусть необходимо найти объем продаж товаров (см.

файл примера

лист

1сезон

):

Присвоим

Имя

Продажи

диапазону

B2:B10

. При создании

имени

будем использовать

абсолютную адресацию

.

Для этого:

• выделите, диапазон
  
  B
  
  2:
  
  B
  
  10
  
  на листе
  
  1сезон
  
  ;
• на вкладке
  
  Формулы
  
  в группе
  
  Определенные имена
  
  выберите команду
  
  Присвоить имя
  
  ;
• в поле
  
  Имя
  
  введите:
  
  Продажи
  
  ;
• в поле
  
  Область
  
  выберите лист
  
  1сезон
  
  (имя будет работать только на этом листе) или оставьте значение
  
  Книга
  
  , чтобы имя было доступно на любом листе книги;
• убедитесь, что в поле
  
  Диапазон
  
  введена формула
  
  =’1сезон’!$B$2:$B$10
  
• нажмите ОК.

Теперь в любой ячейке листа

1сезон

можно написать формулу в простом и наглядном виде:

=СУММ(Продажи)

. Будет выведена сумма значений из диапазона

B2:B10

.

Также можно, например, подсчитать среднее значение продаж, записав

=СРЗНАЧ(Продажи)

.

Обратите внимание, что EXCEL при создании имени использовал

абсолютную адресацию

$B$1:$B$10

.

Абсолютная ссылка

жестко фиксирует диапазон суммирования:

в какой ячейке на листе Вы бы не написали формулу

=СУММ(Продажи)

– суммирование будет производиться по одному и тому же диапазону

B1:B10

.

Иногда выгодно использовать не абсолютную, а относительную ссылку, об этом ниже.

Задача2 (Именованный диапазон с относительной адресацией)

Теперь найдем сумму продаж товаров в четырех сезонах. Данные о продажах находятся на листе

4сезона

(см.

файл примера

) в диапазонах:

B2:B10

,

C

2:

C

10

,

D

2:

D

10

,

E2:E10

.

Формулы поместим соответственно в ячейках

B11

,

C

11

,

D

11

,

E

11

.

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

именованных диапазона

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

относительной адресации

можно ограничиться созданием только

одного

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

Сезонные_продажи

.

Для этого:

• выделите ячейку
  
  B11
  
  , в которой будет находится формула суммирования (при использовании относительной адресации важно четко фиксировать нахождение активной ячейки в момент создания

  имени

  );

• на вкладке
  
  Формулы
  
  в группе
  
  Определенные имена
  
  выберите команду
  
  Присвоить имя
  
  ;
• в поле
  
  Имя
  
  введите:
  
  Сезонные_Продажи
  
  ;
• в поле
  
  Область
  
  выберите лист
  
  4сезона
  
  (имя будет работать только на этом листе);
• убедитесь, что в поле
  
  Диапазон
  
  введена формула
  
  =’4сезона’!B$2:B$10
  
• нажмите ОК.

Мы использовали

смешанную адресацию

B$2:B$10

(без знака $ перед названием столбца). Такая адресация позволяет суммировать значения находящиеся в строках

2

,

3

,…

10

, в том столбце, в котором размещена формула суммирования. Формулу суммирования можно разместить в любой строке ниже десятой (иначе возникнет циклическая ссылка).

Теперь введем формулу

=СУММ(Сезонные_Продажи)

в ячейку

B11.

Затем, с помощью

Маркера заполнения

, скопируем ее в ячейки

С11

,

D

11

,

E

11

, и получим суммы продаж в каждом из 4-х сезонов. Формула в ячейках

B

11, С11

,

D

11

и

E

11

одна и та же!

СОВЕТ:

Если выделить ячейку, содержащую формулу с именем диапазона, и нажать клавишу

F2

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

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

).

Использование именованных диапазонов в сложных формулах

Предположим, что имеется сложная (длинная) формула, в которой несколько раз используется ссылка на один и тот же диапазон:

=

СУММ(E2:E8)+СРЗНАЧ(E2:E8)/5+10/СУММ(E2:E8)

Если нам потребуется изменить ссылку на диапазон данных, то это придется сделать 3 раза. Например, ссылку

E2:E8

поменять на

J14:J20

.

Но, если перед составлением сложной формулы мы присвоим диапазону

E2:E8

какое-нибудь имя (например,

Цены

), то ссылку на диапазон придется менять

только 1 раз

и даже не в формуле, а в

Диспетчере имен

!

=

СУММ(Цены)+СРЗНАЧ(Цены)/5+10/СУММ(Цены)

Более того, при создании формул EXCEL будет сам подсказывать имя диапазона! Для этого достаточно ввести первую букву его имени.

Excel добавит к именам формул, начинающихся на эту букву, еще и имя диапазона!

Users of all levels have different feelings about Excel named ranges. Some find them cumbersome, while others use them religiously.

In addition to allowing quick access to complex references, named ranges can make your formulas a lot easier to read and maintain. Once you make the habit of using named ranges in your Excel workbooks, you’re going to feel the difference in how quickly you can work and manage your data.

In this article, we’re going to cover the basics, advanced features, and various ways of how Excel named ranges can make life easier. We’re going to be using different examples for each feature. Please feel free to download our sample workbook below.

What is a Named range?

Named ranges are cells, ranges, tables, formulas, or constant values that are represented with meaningful names, besides their workbook reference (i.e. A1, B23, A1:B23). Using named ranges make formulas easier to read and maintain.

Let’s take an example. Here we have a formula using INDEX function that looks up a value.

=INDEX(B3:E11,MATCH(I3,C3:C11,0),4)

Using named ranges, this formula would look like below.

=INDEX(sales_data,MATCH(current_date,dates,0),amount_column)

The second example is easier to interpret as you don’t need to go back to that specific reference and identify what these cell references are.

Creating Named Ranges

There are several ways to create Excel named ranges. The easiest method is selecting the cell range, and then typing in the name of your named range into the reference box (right before the formula box on the top bar).

Please note that there are certain limitations for what names you can use as a named range. Consider the items below when creating named ranges.

• The first character of a name must be a letter, an underscore character ( _ ), or a backslash ( ).
• The subsequent characters in the name can be letters, numbers, periods, and underscore characters.

Note: Although you can create names that consist of a single letter, such as «a», «e» or «j»; you cannot use the uppercase and lowercase characters separately (i.e. «C», «c», «R», or «r»).

• Names cannot be the same as a cell reference (i.e. Z$100 or R1C1).
• You can’t use a space.
• A name can contain up to 255 characters.
• Names are NOT case sensitive. According to Excel «data», «Data» and «DATA» are the same.

Another way to create a named range is by going into the Define Name menu from the FORMULAS tab in the ribbon. First select your cell, then go to this menu and type in the desired name into Name box and click OK to apply.

Note: The same New Name dialog will pop up if you click the New… button in the Name Manager window. Follow these steps to create a name using the Name Manager:

1. Go to the FORMULAS tab in the ribbon
2. Click Name Manager
3. In the Name Manager window, click the New… button

If you want to add several named ranges in one go, go to Create Names from Selection. This feature allows you to add name ranges using the labels adjacent to your ranges. To do this:

1. Select your data
2. Go to the FORMULAS tab in the ribbon
3. Click Create Names from Selection
4. Select options that applies to your data. For example, selecting Top row and Left column options will give 5 named ranges in our example:
   1. Inputs =B2:B5
   2. First_Name =B2
   3. Last_Name =B3
   4. Age =B4
   5. Occupation =B5
5. Click OK to add named ranges

Note: If the labels next to selected cells do not meet the limitations mentioned before, Excel automatically removes unsupported characters and replaces them with underscore characters ( _ ). As a result, «First Name» in this example was registered as «First_Name».

Use Cases

References

Named ranges are typically used for giving meaningful names to cell and ranges. You can use those names like regular references without having to use the actual cell range references.

sales_data = B3:E11

current_date = I3

dates = C3:C11

Constants

Just like cell and range references, constant values can also be used as named ranges. Instead of entering a commonly used value every time, entering a name instead can be easier and make your formulas easier to understand.

To save a constant value as a named range you need to use the Name Manager. In Name Manager window, click the New… button, and type in the value after an equal sign into the Refers to: panel.

Formulas

Formulas can also be given named ranges. You can create a named range from a formula the same way you would with constant values using the Name Manager.

Entering your formula after an equal sign in the Refers to section will save this entire formula as a name.

This ability shines especially when creating dynamic ranges that can grow or shrink automatically based on workbook data. For example, =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) formula in this example returns an array of values and excludes spaces, when you enter data into column A. With this formula, you can create dynamic dropdown lists or dynamic charts without unnecessary spaces. Using a named range instead is certainly faster than using the entire formula. For more information about dynamic charts check out our Excel’s Dynamic Charts: A Tutorial On How To Make Life Easier article.

Tables

When you add a Table, more accurately, when you convert your data into a Table, Excel automatically adds a Named Range that refers to that table. Although table names are listed in Name Manager, they differ from regular Excel Named Ranges – You can’t edit the reference range or delete it from the Name Manager window. Table named ranges are also shown with a different icon than named ranges.

Table named ranges are dynamic by default, so they can grow and shrink as you add or remove data. To add a table and use this functionality, just select your data and press Ctrl + T and follow the steps.

Other Advantages

Names in formulas list

Named Ranges are automatically listed in the formulas list when you start typing.

Even if you have long named ranges, you can select them quickly for using them in formulas. Pressing the Tab key will add it to your formula automatically.

Call a name from name list

There is an alternative to using named ranges in formulas method which allows you to see only named ranges in a separate list and add them to your formula. While typing in a formula, press the F3 key to bring up the Paste Name list.

Select the named range and click OK to add it.

Copy formulas between workbooks and worksheets

Excel named ranges make working between several workbooks and worksheets a breeze. For example, if you have similar workbooks that cover user input data from different years, you might want to use similar formulas with different range references. When applying the formulas from one worksheet to another, you need to update the formula ranges manually. However, if you use names instead of references and use the same names for similar data, the copying process will be much easier as formula syntax will remain the same between workbooks.

The same approach can be used between worksheets as well. However, in this case, named range scopes should be selected as “worksheets”, instead of “workbook”. We will cover named range scopes later in this article.

Navigation

Named Ranges are useful for fast navigation. All named ranges are listed in the dropdown menu under the reference box. Clicking a name will automatically move your focus to the named range.

Named ranges are also listed in the Go To window. Press F5 to open the Go To window and double click the name you want to go to.

Named can be used as hyperlinks

Named Ranges will appear in the Insert Hyperlink window. To add a hyperlink;

1. Right-click the cell
2. Click Hyperlink from the menu
3. In the Hyperlink window, click Place in This Document
4. Select the name you want to link and click OK

An alternative way to create hyperlinks is to use the HYPERLINK function. You can use Named Ranges in HYPERLINK function by placing a hash character (#) before the name.

=HYPERLINK(«#State»,»Go to State List»)

Dynamic Ranges via tables

We’ve already learned that dynamic ranges can be created using formulas and a Named Range. An alternative way to do this is by using a Table reference inside a Named Range. We’ve mentioned that table named ranges are dynamic and can automatically grow or shrink, and we will be using this feature to do the same for a single named range.

Tables have their own reference syntax. Table name comes first and column name follows, inside brackets. For example, «Table1[State]» refers to column name «State» in «Table1». Because this table is dynamic, its columns will be dynamic as well. The trick is creating a Named Range that refers to a table reference, instead of a single range reference or a formula.

«States» named range is now a dynamic range too.

Zoom out to see Excel Named Ranges

If you have data that spans over dozens of rows and/or columns, giving those tables named ranges and zooming out can help visualize the big picture. Excel starts showing names when the zoom level is below 40%.

Scope

Named Ranges can be defined under workbooks or worksheets. This is the scope of the named range. Scope of named ranges are Workbook by default.

‘Workbook’ scope means you can access the specified named range from all sheets and it always refers to the same value, formula, or reference.

On the other hand, if a named range’s scope is set to ‘worksheet’, that name works inside that specific worksheet. This feature is useful when you have same kind of data across different worksheets. Excel allows giving the same name into multiple ranges as long as they are not under overlapping scopes. The example below contains 3 named ranges, all of which are named «data».

=SUM(data) formula returns 3 different values when the formula is used in each sheet (i.e. Sheet2, Sheet3, Sheet4).

• In Sheet2, =SUM(data) returns 10
• In Sheet3, =SUM(data) returns 110
• In Sheet4, =SUM(data) returns 1110

To call a local scoped named range from different worksheet, you need to use the parent worksheet name as well. For example:

• In Sheet2, =SUM(Sheet2!data) returns 10
• In Sheet3, =SUM(Sheet2!data) returns 10
• In Sheet4, =SUM(Sheet2!data) returns 10

You can determine the scope of a named range when you add it. You need to use Define Name menu or the Name Manager to add a local scoped named range. In the New Name menu, choose a worksheet under the Scope dropdown.

Please note that you can’t change this afterwards. The only way to do this by deleting the named range and create it again.

How to manage and delete Excel Named Ranges

The Name Manager window is like the base of operations for Named Ranges. Here, you can find a list of all named ranges, add/edit/delete them.

Deleting Named Ranges

To delete name ranges, first select names you want to delete, and then click the Delete button. You can use Ctrl and Shift keys to select multiple names and delete them with one click.

Filtering and sorting names

Name Manager allows sorting and filtering names. You can sort names by clicking the columns for Name, Value, Refers To, Scope or Comment.

Multiple filters can be applied from the Filter dropdown.

Additional Features

Apply Names

If you create named ranges after creating your formulas, the formulas will keep using regular references if you do not update them. To update formulas, you have 2 options:

1. Changing references using the Find & Replace
2. Using Apply Names feature

For the first method, open the Find & Replace dialog (i.e. Ctrl + F), then enter reference and named range. Click the Replace All button to replace all occurrences.

The second method is a bit easier and, in some cases, safer than Find & Replace because you don’t need to enter a reference. To use Apply Names, click the down arrow next to the Define Name under FORMULAS tab and go to Apply Names. This will bring up the Apply Names window with a list of names. Select the names you want to update and click OK to convert ranges into names automatically.

Paste List

You can open the Paste Name dialog by pressing the F3 key on your keyboard, or find it under the Use in Formula dropdown in the FORMULAS ribbon.

In the Paste Name dialog box, clicking Paste List button creates a list of existing names in the active worksheet.

Please note that table names and local names that are not under the active sheet scope are not listed here.

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

Итак, несколько советов, которые сделают вашу работу с именованными диапазонами в Excel более быстрой и продуктивной.

Многоразовое создание именованного диапазона в один прием

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

К примеру, у вас имеется набор данных (как показано ниже) и вы хотите создать отдельные именованные диапазоны для каждой колонки. Вместо того чтобы создавать их по одному, вы можете воспользоваться сочетанием клавиш CTRL + SHIFT + F3, которое откроет диалоговое окно Создание имен из выделенного диапазона. Тоже самое окно доступно во вкладке Формулы -> Определенные имена –> Создать из выделенного. Теперь вы можете создать больше одного диапазона – по строкам, столбцам, оба варианта.

Когда вы щелкните ОК, Excel создаст четыре именованных диапазона. Заголовок каждого диапазона будет служить его названием. При необходимости вы можете легко отредактировать любой атрибут диапазонов.

Доступ к управлению именованными диапазонами

Чтобы открыть диалоговое окно Диспетчер имен, перейдите по вкладке Формулы в группу Определенные имена и щелкните по кнопке Диспетчер имен. Либо нажатием сочетаний клавиш Ctrl + F3.

Использование формулы СМЕЩ

Именованные диапазоны и вполовину не были бы такими полезными и интересными без формулы СМЕЩ. Функция СМЕЩ помогает позиционировать и расширять данный диапазон. Результатом использования ее может стать мощный динамический диапазон, который имеет способность расширяться и изменяться.

Использование абсолютных ссылок при работе с именованными диапазонами

На самом деле не уверен, это конструктивная особенность или ошибка. Используя относительные ссылки (A1 вместо $A$1) при определении именованного диапазона, они не остаются на том же месте, как бы вы этого не хотели. Давайте рассмотрим этот случай на примере. Предположим, вы хотите создать диапазон, который смещается вниз на 10 строк от ячейки A1. Первое, что приходит в голову, это написать формулу =СМЕЩ(A1;10;0).

Пока все хорошо. Если вы захотите воспользоваться этим именованным диапазоном, необходимо подобрать для нее ячейку (скажем B1) и ввести что-то типа =мой_имен_диап. Где мой_имен_диап  — это имя, которое вы дали диапазону на предыдущем шаге.

Но если вы выберите другую ячейку и снова откроете диспетчер имен, формула, которую вы ввели до этого =СМЕЩ(Лист1!A1;10;0), магическим образом преобразится (к примеру, =СМЕЩ(Лист1!A1048576;10;0)). Это происходит потому, что при создании именованного диапазона мы использовали относительные ссылки, т.е. целевой диапазон будет все время смещаться в зависимости от адреса, выбранной на данный момент, ячейки.

Использование F2 для изменения именованного диапазона

Еще одна полезная вещь, использование F2 при изменении именованного диапазона. Попробуйте воспользоваться кнопками стрелок на клавиатуре для навигации по формуле именованного диапазона, вы увидите замечательные преобразования.

Чтобы избежать недоразумений при использовании стрелок, нажмите клавишу F2.

Возможно у вас имеются свои трюки по использованию именованных диапазонов?! Не хотите поделиться?)

Скачать пример использования именованных диапазонов с проверкой данных.