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?

Итак, если вам необходимо часто обрабатывать какие-либо конкретные элементы таблицы — вы можете просто «назвать» их и выделять уже по названию (имени).

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

Допустим, у нас есть такая табличка:

Например, здесь мы можем создать диапазон Дат, имен продавцов и продаж.

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

Как я уже сказал, мы можем создать диапазон ячеек из каждого столбика:

А также присвоить ей конкретное имя.

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

Давайте рассмотрим плюсы использования этой функции.

Используем имена в функциях

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

К примеру, так можно посчитать сумму всех значений диапазона:

=СУММ(SALES)

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

• Сумма продаж тех продавцов, которые продали более 500 позиций: =СЧЁТЕСЛИ(Sales,»>500″);

• Сумма продаж продавца Tom: =СУММЕСЛИ(SalesRep, «Tom»,Sales);

• Чистая прибыль продавца Joe:

=СУММЕСЛИ(SalesRep, "Joe",Sales)*Комиссия

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

Прописать имя гораздо быстрее чем диапазон ячеек

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

Вы начинаете вводить его имя и Excel сам понимает что вы хотите написать, таким образом ошибки исключены:

Функции становятся динамическими

Если вы использовали определенные значения при расчетах, а после поменяете эти значения — результат не изменится.

А если же вы используете именованный диапазон — все будет пересчитываться каждый раз!

Как создать именованный диапазон

Есть 3 метода, давайте рассмотрим каждый.

Функция «Присвоить имя»

Пошаговая инструкция:

• Выделите ячейку (или диапазон);

• Щелкните на «Формулы» -> «Присвоить имя»;

• Итак, укажите имя и проверьте диапазон;

• Подтвердите.

Готово!

С помощью поля «Имя»

• Выделите ячейку (или диапазон);

• А теперь просто впишите имя в поле «Имя»;

• Диапазон будет создан для всего файла Excel (а не для отдельного листа). Если вам нужно создать диапазон только одного листа, вам нужен рассмотренный ранее вариант.

Функция «Создать из выделенного»

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

Пошаговая инструкция:

• Выделите ячейку (или диапазон);

• Щелкните на «Формулы» -> «Создать из выделенного»;

• В открывшемся окне, выберите ту опцию, которая соответствует положению заголовков в вашей табличке. Грубо говоря, укажите где Excel должен искать заголовки таблички (в нашем случае — «в строке выше»);

Таким образом, он создал три диапазона с названиями наших столбиков, меняя пробел на «_»;

Важная информация

Есть несколько моментов, которые вам нужно знать о именованных диапазонах:

• Первым символом имени может быть только буква, «_» или «». Во всех остальных случаях будет ошибка;

• Именем не может быть диапазон ячеек;

• А также в именах не может быть пробелов, вы можете использовать «_»;

• Регистр не имеет значения;

• Максимальная длина имени — 255 символов.

Поиск нужного именованного диапазона

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

Что же делать?

Давайте я покажу.

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

Пошаговая инструкция:

• Щелкните на «Формулы» -> «Использовать в формуле»;

• Далее — «Вставить имена…»;

Итак, открылся список всех диапазонов.

Автоподстановка имени диапазона

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

Редактируем уже созданный именованный диапазон

Пошаговая инструкция:

• Щелкните на «Формулы» -> «Диспетчер имен»;

• В открывшемся окне будут все созданные диапазоны и их описания’

• Щелкните на «Изменить…»;

• Поменяйте то что нужно;

• Подтвердите.

Горячие клавиши

Какие есть горячие клавиши:

• Список всех диапазонов — F3;

• Открыть «Диспетчер имен» — CTRL + F3;

• Создать диапазон из выделенного — CTRL + SHIFT + F3.

Динамический именованный диапазон

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

Если мы создали B1:B20 диапазон, то он всегда будет ссылаться на эту область.

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

В Excel можно создать и автоматически изменяемый именованный диапазон. Если я добавлю несколько новых ячеек в столбик, он будет включать в себя и их.

Для этого мы будем вызывать функцию ИНДЕКС.

Допустим у вас есть такая табличка:

Пошаговая инструкция:

• Щелкните на «Формулы» -> «Присвоить имя»;

• В открывшемся окне укажите данные на ваш выбор, а в поле «Диапазон» введите — =$A$2:ИНДЕКС($A$2:$A$100,СЧЁТЕСЛИ($A$2:$A$100,»<>»&»»)

• Подтвердите.

Готово!

Как это работает?

Чтобы понять как эта «фишка» работает, нужно знать как работает функция ИНДЕКС.

Чаще всего ИНДЕКС используют для получения значения из какой-либо ячейки.

Формула которую мы использовали при создании диапазона:

=$A$2:ИНДЕКС($A$2:$A$100,СЧЁТЕСЛИ($A$2:$A$100,"<>"&""))

ИНДЕКС($A$2:$A$100,СЧЁТЕСЛИ($A$2:$A$100,»<>»&»») -> выдаст нам значение последнего элемента столбика.

Но если функция будет выглядеть так: (=$A$2:INDEX($A$2:$A$100,COUNTIF($A$2:$A$100,»<>»&»»))) то она вернет порядковый номер последней ячейки, а не значение.

В нашем случае, это — $A$2:$A$11.

И соответственно если мы создадим новые ячейки — все будет пересчитано.

Обратите внимание:

Эта конструкция сработает только в том случае, если в ячейках идущих друг за другом не будет пустых. Если вам необходимо больший диапазон в функции ИНДЕКС — укажите его.

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

We can use the name for the cell Ranges instead of the cell reference (such as A1 or A1:A10). We can create a named range for a range of cells and use then use that name directly in the Excel formulas. When we have huge data sets, Excel-named ranges make it easy to refer (by directly using a name to that data set).

Creating an Excel Named Range :

There can be 3 ways to create named ranges in Excel :

Method 1: Using Define Name

Use the following steps to create named range using Define Name :

• Select the range B1:B5.
• Click on the Formulas tab.
• Then click on Define Name.

• Give a new Name(PriceTotal in our example) & click Ok. (You can see the range in the bottom refers to section, here absolute referencing is used,  $ before the row number/ column letter locks the row/column).

• Now, the next thing is to see that how to use this named range in any of the Excel formulas. For example, if you want to get the sum of all numbers in the above name range then, can say simply write: =SUM(PriceTotal).

Here, B7 =SUM(PriceTotal) = 100 + 22.5 + 843 + 256 (all the numbers in the named range)= 1220.5

Note: The named Range created by this method is restricted to a worksheet.

Method 2: Use the Name Box

Use the following steps to create a named range using the name box :

• Choose the range for which you’d want to give it a name (not the headers).
• Type the name of the range with which you wish to construct the Named Range in the Name Box on the left of the Formula bar.

Note: The Name created this way is available in the entire excel current Workbook. 

Method 3: From Selection Option

When you have tabular data and wish to construct named ranges for each column/row, this is the preferred method.

Example: We have 3 columns having headers: Product Name, Amount & Tax Percent.

Use the following steps to create a named range from the selection option :

• Select the complete data set (the 3 columns with the headers).
• Click on the Formulas tab.
• Click Create from Selection (or press  Control + Shift + F3).

• After the click, Create Names from Selection’ dialogue box will be opened.

• Check the settings where you have the headers in the Create Names from Selection dialogue box. Because the heading is in the top row, we only select the top row. You can choose both if you have headers in the top row and left column. We only need to check the Left Column option, if the data is organized with the headers only in the left column.

Here we select only Top row because we have headers in the top row.

Result: The data set you selected in column A will be having a named Range: Product_Name (Spaces not allowed, so underscore automatically replaces space) 

The data set you selected in column B will be having a named Range: Amount

The data set you selected in column C will be having a named Range: TAX_Percent (Spaces not allowed, so underscore automatically replaces space) 

Benefits of Creating Named Ranges in Excel:

The following are benefits of creating & use Named Ranges in Excel :

Instead of using cell references every time, we can directly use Named Reference

Example 1: In the above example, to calculate the sum, we used B7 =SUM(PriceTotal) instead of B7 = SUM(B2:B5) for the above data range.

Example 2: If for the same-named Range, if we write, B9 = SUMIF(PriceTotal,”>50″), then the sum will be equal to the sum of all numbers > 50 in the named range.

Here, sum is done for all the numbers > 50 in the named range “PriceTotal” = 100+842+256 = 1198.

• To Select Cells, you do not need to return to the data set to choose the cells. You can directly use the Named range, by typing the first few characters of the named range, excel shows a list of named ranges that matches the typed characters.

Example :

As you can see that after just typing Pr, a drop-down list for the available options(formula & named Range) is pooped up.

• The formulas become dynamic using named Ranges:
  • Excel formulas become dynamic if we use Named Ranges. In the above example, if we add another cell of Tax Percent (2.5%)& you name it as “TAX” . Now to calculate Final Price (including tax), we can use the Named Range instead of using the value 2.5.
  • Now, if later tax is increased to 3%, you just have to update the Named Range, and all the calculations would be done automatically & we will get the Final Price according to the latest tax percent.
• Finding a named cell is less time-consuming.

Using Create From Selection Option :

When generating Named Ranges in Excel, you should be aware of the following naming conventions:

• A letter and underscore character(_), or a backslash, shall be the initial character in a Named Range (). It will display an error if it is anything else is used. Letters, numbers, special characters, a period, or an underscore can make up the remaining characters.
• When establishing named ranges, you can’t use spaces. Tax Percent, for example, cannot be a named range. We can use an underscore, a period, or capital characters if we wish to make a Named Range out of two words, You may, for example, Tax_Percent, TaxPercent, etc.
• In Excel, you can’t use names that are also cell references. You can’t use C1 because it’s also a cell reference.
• You can only have a maximum of 255 characters in a named range.
• For Excel, uppercase and lowercase letters are the same when generating named ranges. For example, if you create a named range called ‘TAX’, you cannot create another named range called ‘Tax’ or ‘tax’.

Name Rows and Columns in Excel:

You may find yourself producing a lot of Named Ranges in Excel when working with large data sets and complex models. It is possible that you can’t recall the name of the Named Range you made. What to do then?

Solutions: 

1. Getting the Names of All the Named Ranges

• Click on the formula tab.
• Choose – Use in Formula(In the Defined Named group).

• Choose Paste names & you will get a list of all the Named Ranges in the workbook.

2. Displaying the Matching Named Ranges

As discussed earlier, type a few initial characters, if you have some glimpse about the Name, and a drop-down list of matching ones will be shown. 

Editing the Named Range in Excel :

To change/ edit the already created named range, follow these steps :

• Click the Formulas tab.
• Click the name manager(or Ctrl + F3).

• All of the Named Ranges in that workbook will be listed in the Name Manager dialogue box. Double-click the Named Range you’d like to change.

• Edit Name dialog box will pop up, make the modifications.

Example: 

Here when we double-click Amount, the edit window for the same get open & we rename that named range from Amount to any other name.

• Click OK and close the name manager window.

Useful Named Range Shortcuts:

When dealing with Named Ranges in Excel, the following keyboard shortcuts can come in use frequently :

• F3: Will give a list of all the Named Ranges and pasting it in any Formula.
• Ctrl + + Shift + F3 : To pop up create Named Ranges from Selection window.
• Ctrl + F3 : To pop up the name manager window directly.
• F1: For Excel Help.

Динамический диапазон с автоподстройкой размеров

Есть ли у вас таблицы с данными в Excel, размеры которых могут изменяться, т.е. количество строк (столбцов) может увеличиваться или уменьшаться в процессе работы? Если размеры таблицы «плавают», то придется постоянно мониторить этот момент и подправлять:

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

Все это в сумме не даст вам скучать

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

Способ 1. Умная таблица

Выделите ваш диапазон ячеек и выберите на вкладке Главная – Форматировать как Таблицу (Home – Format as Table):

Если вам не нужен полосатый дизайн, который добавляется к таблице побочным эффектом, то его можно отключить на появившейся вкладке Конструктор (Design). Каждая созданная таким образом таблица получает имя, которое можно заменить на более удобное там же на вкладке Конструктор (Design) в поле Имя таблицы (Table Name).

Теперь можно использовать динамические ссылки на нашу «умную таблицу»:

• Таблица1 – ссылка на всю таблицу кроме строки заголовка (A2:D5)
• Таблица1[#Все] – ссылка на всю таблицу целиком (A1:D5)
• Таблица1[Питер] – ссылка на диапазон-столбец без первой ячейки-заголовка (C2:C5)
• Таблица1[#Заголовки] – ссылка на «шапку» с названиями столбцов (A1:D1)

Такие ссылки замечательно работают в формулах, например:

=СУММ(Таблица1[Москва]) – вычисление суммы по столбцу «Москва»

или

=ВПР(F5;Таблица1;3;0) – поиск в таблице месяца из ячейки F5 и выдача питерской суммы по нему (что такое ВПР?)

Такие ссылки можно успешно использовать при создании сводных таблиц, выбрав на вкладке Вставка – Сводная таблица (Insert – Pivot Table) и введя имя умной таблицы в качестве источника данных:

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

При создании выпадающих списков прямые ссылки на элементы умной таблицы использовать нельзя, но можно легко обойти это ограничение с помощью тактической хитрости – использовать функцию ДВССЫЛ (INDIRECT), которая превращает текст в ссылку:

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

Способ 2. Динамический именованный диапазон

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

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

Нам потребуются две встроенных функции Excel, имеющиеся в любой версии – ПОИКСПОЗ (MATCH) для определения последней ячейки диапазона и ИНДЕКС (INDEX) для создания динамической ссылки.

Ищем последнюю ячейку с помощью ПОИСКПОЗ

ПОИСКПОЗ(искомое_значение;диапазон;тип_сопоставления) – функция, которая ищет заданное значение в диапазоне (строке или столбце) и выдает порядковый номер ячейки, где оно было найдено. Например, формула ПОИСКПОЗ(“март”;A1:A5;0) выдаст в качестве результата число 4, т.к. слово «март» расположено в четвертой по счету ячейке в столбце A1:A5. Последний аргумент функции Тип_сопоставления = 0 означает, что мы ведем поиск точного соответствия. Если этот аргумент не указать, то функция переключится в режим поиска ближайшего наименьшего значения – это как раз и можно успешно использовать для нахождения последней занятой ячейки в нашем массиве.

Суть трюка проста. ПОИСКПОЗ перебирает в поиске ячейки в диапазоне сверху-вниз и, по идее, должна остановиться, когда найдет ближайшее наименьшее значение к заданному. Если указать в качестве искомого значение заведомо больше, чем любое имеющееся в таблице, то ПОИСКПОЗ дойдет до самого конца таблицы, ничего не найдет и выдаст порядковый номер последней заполненной ячейки. А нам это и нужно!

Если в нашем массиве только числа, то можно в качестве искомого значения указать число, которое заведомо больше любого из имеющихся в таблице:

Для гарантии можно использовать число 9E+307 (9 умножить на 10 в 307 степени, т.е. 9 с 307 нулями) – максимальное число, с которым в принципе может работать Excel.

Если же в нашем столбце текстовые значения, то в качестве эквивалента максимально большого числа можно вставить конструкцию ПОВТОР(“я”;255) – текстовую строку, состоящую из 255 букв «я» — последней буквы алфавита. Поскольку при поиске Excel, фактически, сравнивает коды символов, то любой текст в нашей таблице будет технически «меньше» такой длинной «яяяяя….я» строки:

Формируем ссылку с помощью ИНДЕКС

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

ИНДЕКС(диапазон; номер_строки; номер_столбца)

Она выдает содержимое ячейки из диапазона по номеру строки и столбца, т.е. например функция =ИНДЕКС(A1:D5;3;4) по нашей таблице с городами и месяцами из предыдущего способа выдаст 1240 – содержимое из 3-й строки и 4-го столбца, т.е. ячейки D3. Если столбец всего один, то его номер можно не указывать, т.е. формула ИНДЕКС(A2:A6;3) выдаст «Самару» на последнем скриншоте.

Причем есть один не совсем очевидный нюанс: если ИНДЕКС не просто введена в ячейку после знака =, как обычно, а используется как финальная часть ссылки на диапазон после двоеточия, то выдает она уже не содержимое ячейки, а ее адрес! Таким образом формула вида $A$2:ИНДЕКС($A$2:$A$100;3) даст на выходе уже ссылку на диапазон A2:A4.

И вот тут в дело вступает функция ПОИСКПОЗ, которую мы вставляем внутрь ИНДЕКС, чтобы динамически определить конец списка:

=$A$2:ИНДЕКС($A$2:$A$100; ПОИСКПОЗ(ПОВТОР(«я»;255);A2:A100))

Создаем именованный диапазон

Осталось упаковать все это в единое целое. Откройте вкладку Формулы (Formulas) и нажмите кнопку Диспетчер Имен (Name Manager). В открывшемся окне нажмите кнопку Создать (New), введите имя нашего диапазона и формулу в поле Диапазон (Reference):

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

Ссылки по теме

• Использование функции ВПР (VLOOKUP) для связывания таблиц и подстановки значений
• Как создать автоматически наполняющийся выпадающий список
• Как создать сводную таблицу для анализа большого массива данных

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

=СУММ(А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 добавит к именам формул, начинающихся на эту букву, еще и имя диапазона!

Именованные диапазоны, вероятно, один из самых полезных инструментов в 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.

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

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

Требования к именам ячеек

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

1. Нельзя использовать пробелы, запятые, двоеточия, точки с запятыми в качестве разделителя слов (выходом из ситуации может быть замена на нижнее подчеркивание или точку).
2. Максимальная длина символов – 255.
3. В начале названия должны быть буквы, нижнее подчеркивание или обратная косая черта (никаких цифр и прочих символов).
4. Нельзя указывать адрес ячейки или диапазона.
5. Название должно быть уникальным в рамках одной книги. При этом следует помнить, что буквы в разных регистрах программа будет воспринимать как полностью одинаковые.

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

Допустим, ячейке B2 присвоено имя “Продажа_1”.

Если она будет участвовать в формуле, то вместо B2 мы пишем “Продажа_1”.

Нажав клавишу Enter убеждаемся в том, что формула, действительно, рабочая.

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

Строка имен

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

1. Любым удобным способом, например, с помощью зажатой левой кнопки мыши, выделяем требуемую ячейку или область.
2. Щелкаем внутри строки имен и вводим нужное название согласно требованиям, описанным выше, после чего нажимаем клавишу Enter на клавиатуре.
3. В результате мы присвоим выделенному диапазону название. И при выделении данной области в дальнейшем мы будем видеть именно это название в строке имен.
4. Если имя слишком длинное и не помещается в стандартном поле строки, его правую границу можно сдвинуть с помощью зажатой левой кнопки мыши.

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

Использование контекстного меню

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

1. Как обычно, для начала нужно отметить ячейку или диапазон ячеек, с которыми хотим выполнить манипуляции.
2. Затем правой кнопкой мыши щелкаем по выделенной области и в открывшемся перечне выбираем команду “Присвоить имя”.
3. На экране появится окно, в котором мы:
   • пишем имя в поле напротив одноименного пункта;
   • значение параметра “Поле” чаще всего остается по умолчанию. Здесь указывается границы, в которых будет идентифицироваться наше заданное имя – в пределах текущего листа или всей книги.
   • В области напротив пункта “Примечание” при необходимости добавляем комментарий. Параметр не является обязательным для заполнения.
   • в самом нижнем поле отображаются координаты выделенного диапазона ячеек. Адреса при желании можно отредактировать – вручную или с помощью мыши прямо в таблице, предварительно установив курсор в поле для ввода информации и стерев прежние данные.
   • по готовности жмем кнопку OK.
4. Все готово. Мы присвоили имя выделенному диапазону.

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

По умолчанию имена диапазонов ячеек автоматически считаются абсолютными ссылками.

Для имен действует ряд ограничений:

– имя может содержать до 255 символов;

– первым символом в имени должна быть буква, знак подчеркивания (_) либо обратная косая черта (), остальные символы имени могутбыть буквами, цифрами, точками и знаками подчеркивания;

– имена не могут быть такими же, как ссылки на ячейки;

– пробелы в именах не допускаются;

– строчные и прописные буквы не различаются.

Управление существующими именованными диапазонами (создание, просмотр и изменение) можно осуществлять при помощи диспетчера имен. В Excel 2007 диспетчер находится на вкладке “Формулы”, в группе кнопок “Определенные имена”.

Выделение диапазонов

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

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

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

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

Сравнение диапазонов

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

Например, для построчного сравнения часто используется логическая функция “ЕСЛИ” и какой-либо из операторов сравнения (также можно использовать и другие функции, например “СЧЕТЕСЛИ” из категории статистические для проверки вхождения элементов одного списка в другой).

Также для поиска отличий по столбцам или по строкам используется стандартное средство Excel, которое находится на вкладке “Главная”, в группе кнопок “Редактирование”, в меню кнопки “Найти и выделить”. Если в этом меню выбрать пункт “Перейти” и далее нажать кнопку “Выделить”, то в диалоговом окне “Выделение группы ячеек” можно выбрать одну из опций “Отличия по строкам” или “Отличия по столбцам”.

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

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

Задача

Имеется таблица продаж по месяцам некоторых товаров (см. Файл примера ):

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

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

Для формирования динамических диапазонов будем использовать функцию СМЕЩ() , которая возвращает ссылку на диапазон в зависимости от значения заданных аргументов. Можно задавать высоту и ширину диапазона, а также смещение по строкам и столбцам.

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

Для создания динамического диапазона:

• на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя
  
• в поле Имя введите: Месяц
  
• в поле Область выберите лист Книга
  
• в поле Диапазон введите формулу =СМЕЩ(лист1!$B$5;;;1;СЧЁТЗ(лист1!$B$5:$I$5))
• нажмите ОК.

Skip to content

Sub PrisvaivaemImyaDiapazonu()
'Присваиваем имя диапазону
Range("D6:D17").Name = "MyData"
End Sub

Sub ViborDiapazona()
Range("MyData").Select
End Sub

Sub ViborDiapazonaFormatirovanieWith()
With Range("MyData")
.NumberFormat = "#,##0"
.Font.Bold = True
.Interior.ColorIndex = 36
End With
End Sub