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

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

Мы привыкли ссылаться на ячейки и диапазоны по их адресам. Например, A5 или B2:D17 . Однако, не все знают, что диапазону можно присвоить символьное имя и использовать его в качестве полноценной замены привычных адресов в формулах. На первый взгляд это дополнительные манипуляции, которые не приносят очевидной пользы, но это не так. Мы с вами разберём, как минимум, 7 причин, почему стоит использовать именованные диапазоны.

Я считаю, что умение оперировать именованными диапазонами ( далее ИД ) и понимание, для чего они нужны, одни из ключевых навыков квалифицированного пользователя Excel.

Создаём ИД

Создать ИД можно двумя способами:

Быстрый способ . Выделяем необходимый диапазон или ячейку. Идём в поле адреса и вводим там имя создаваемого ИД.

Стандартный способ . Выделяем диапазон, а потом выбираем пункт меню Присвоить имя с ленты ФОРМУЛЫ . В диалоговом окне вводим желаемое имя, меняем или оставляем без изменений область действия, примечание, адрес диапазона.

7 причин полюбить именованные диапазоны

Причина первая

Сравните 2 формулы:

В каком случае у вас больше шансов интуитивно догадаться, что мы вычисляем? Ответ очевиден – во втором случае. Потому что вместо скучных и безликих координат диапазонов, вы видите идентификаторы, которые рождают у вас некоторые ассоциации: Товары – это наверняка таблица с информацией о товарах, PrdSelected – это, видимо, выбранный пользователем продукт, Продукт – какой-то справочник продуктов, а VAT_Rate – без сомнения ставка НДС.

ИД делают формулы более дружественными к пользователю, наглядными и интуитивно понятными!

У вас не рябит в глазах от букв, цифр и знаков доллара в адресах диапазонов.

Причина вторая

Предположим у вас на листе 500 формул ВПР (VLOOKUP), которые ссылаются на один и тот же диапазон ячеек, из которого вы извлекаете данные. В случае изменения координат этого диапазона, вы будете вынуждены исправить его координаты в пятистах формулах. Безусловно, это можно сделать при помощи инструмента Поиск с заменой , но факт остаётся фактом – так или иначе вам надо менять формулы в большом количестве ячеек. А теперь представьте, что вы ссылаетесь в формулах на ИД. При изменении координат ИД, вы ОДИН раз изменяете его координаты в Диспетчере имён . При этом нет необходимости исправлять какие бы то ни было формулы. Это реальная экономия вашего времени!

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

Причина третья

Объявив ИД, вы можете его легко вставить в формулу. Есть 3 возможности:

Вызвать диалоговое окно Вставка имени (горячая клавиша F3 );

Начав набор названия ИД с клавиатуры и завершив выбором из раскрывшегося списка объектов;

Воспользовавшись пунктом меню Использовать в формуле на ленте ФОРМУЛЫ .

Формулы вводятся быстрее, приятнее и у вас меньше шансов внести в формулу ошибку.

Причина четвёртая

Используя обычные адреса диапазонов вы всегда должны думать, какой тип адресации применить, чтобы формула не поползла при протягивании или, напротив, поползла в нужном направлении. Речь идёт об относительных и абсолютных ссылках. Адес диапазона, введеный как A1:B5 при распространении (копировании) формулы вниз в следующей ячейке превратится в A2:B6 , потом в A3:B7 и т.д. Поэтому при вводе вы должны использовать абсолютную ссылку вида $A$1:$B$5 , от которых особенно рябит в глазах. Ссылки в виде ИД лишены данного недостатка.

Нет проблем при протягивании формул.

Причина пятая

ИД позволяют реализовать динамические диапазоны, которые подстраиваются под фактические размеры вашей таблицы. Это очень удобно, когда вы добавляете к какому-либо справочнику новые строки или столбцы. Делается это при помощи формул СМЕЩ + СЧЁТЗ (OFFSET+COUNTA), так:

Товары!$A$1 – точка отсчёта. Берите верхний левый угол таблицы;

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

0 – смещение по столбцам для верхнего левого угла ИД. Его у нас нет.

СЧЁТЗ(Товары!$A:$A)-1 – эта функция подсчитывает количество непустых строк в указанном диапазоне ( $A:$A ), а единицу вычитаем за счёт пропущенной строки с заголовками столбцов. При расширении таблицы вниз колонка A должна обязательно заполняться данными, либо выберите другой столбец, для которого это условие будет выполняться;

СЧЁТЗ(Товары!$1:$1) – подсчёт количества непустых столбцов по тому же принципу (на основе строки 1:1 ).

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

Возможность задать динамические границы при определении ИД.

Причина шестая

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

Облегчение ссылок на другие книги, упрощение переноса формул на другие листы/книги.

Причина седьмая

В Excel 2007 и ранее нельзя было определить в Проверке данных (Data Validation) список (list), который бы ссылался на диапазон, располагающийся на другом листе. То есть вы не могли указать в качестве источника значений списка = Лист4!$A$2:$A$4 , а надо было обязательно определить ИД, и вот только так можно было сослаться на другой лист.


Ссылка на источник данных для списка в функции Проверка данных.

Управление ИД

Осуществляется при помощи данного блока меню на ленте ФОРМУЛЫ :

Получение списка всех ИД

Через диалог Вставка имени ( F3 или через меню Использовать в формуле – Вставить имена. ) можно получить в ячейки таблицы список всех ИД. Это выглядит так:

Замена обычных диапазонов на ИД

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

Массовое создание ИД

ИД можно создавать массово на основе ваших таблицы и заголовков столбцов/строк. Выделите необходимый диапазон данных и выберите пункт меню Создать из выделенного . Будет предложен такой диалог:

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

Тоже самое можно проделать и со строками, если на то есть необходимость.

Именованные константы

Помимо ссылок на ячейки в ИД можно указать ссылку на числовую константу. Например:

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

Многими возможнотями ИД (и даже больше) обладают также умные таблицы.

Источник: perfect-excel.ru

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

Обычно ссылки на диапазоны ячеек вводятся непосредственно в формулы, например =СУММ(А1:А10) . Другим подходом является использование в качестве ссылки имени диапазона. В статье рассмотрим какие преимущества дает использование имени.

Назовем Именованным диапазоном в MS EXCEL, диапазон ячеек, которому присвоено Имя (советуем перед прочтением этой статьи ознакомиться с правилами создания Имен ).

Преимуществом именованного диапазона является его информативность. Сравним две записи одной формулы для суммирования, например, объемов продаж: =СУММ($B$2:$B$10) и =СУММ(Продажи) . Хотя формулы вернут один и тот же результат (если, конечно, диапазону B2:B10 присвоено имя Продажи ), но иногда проще работать не напрямую с диапазонами, а с их именами.

Совет : Узнать на какой диапазон ячеек ссылается Имя можно через Диспетчер имен расположенный в меню Формулы/ Определенные имена/ Диспетчер имен .

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

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

Пусть необходимо найти объем продаж товаров (см. файл примера лист 1сезон ):

Присвоим Имя Продажи диапазону B2:B10 . При создании имени будем использовать абсолютную адресацию .

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

Теперь в любой ячейке листа 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 , то соответствующие ячейки будут обведены синей рамкой (визуальное отображение Именованного диапазона ).

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

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

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

Но, если перед составлением сложной формулы мы присвоим диапазону E2:E8 какое-нибудь имя (например, Цены ), то ссылку на диапазон придется менять только 1 раз и даже не в формуле, а в Диспетчере имен !

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

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

Источник: excel2.ru

Ссылка на именованные диапазоны Refer to Named Ranges

Диапазоны легче идентифицировать по имени, чем с помощью нотации A1. Ranges are easier to identify by name than by A1 notation. Чтобы присвоить имя выбранному диапазону, щелкните поле имени с левой стороны строки формул, введите имя и нажмите клавишу ВВОД. To name a selected range, click the name box at the left end of the formula bar, type a name, and then press ENTER.

Примечание. Существует два типа именованных диапазонов: именованный диапазон книги и именованный диапазон определенного листа. Note There are two types of named ranges: Workbook Named Range and WorkSHEET Specific Named Range.

Именованный диапазон книги Workbook Named Range

Именованный диапазон книги относится к определенному диапазону в любом месте книги (применяется глобально). A Workbook Named Range references a specific range from anywhere in the workbook (it applies globally).

Как создать именованный диапазон книги: How to Create a Workbook Named Range:

Как указано выше, обычно он создается путем ввода имени в поле “Имя” с левой стороны строки формул. As explained above, it is usually created entering the name into the name box to the left end of the formula bar. Обратите внимание, что имя не может содержать пробелов. Note that no spaces are allowed in the name.

Именованный диапазон определенного листа WorkSHEET Specific Named Range

Именованный диапазон определенного листа относится к диапазону конкретного листа и не является глобальным для всех листов в книге. A WorkSHEET Specific Named Range refers to a range in a specific worksheet, and it is not global to all worksheets within a workbook. Сослаться на такой именованный диапазон с этого же листа можно просто с помощью имени, но из другого листа потребуется использовать имя листа с добавлением “!” и имени диапазона (пример: диапазон “Имя” “= Лист1!Имя”). You can refer to this named range by just the name in the same worksheet, but from another worksheet you must use the worksheet name including “!” the name of the range (example: the range “Name” “=Sheet1!Name”).

Преимущество заключается в возможности использования кода VBA для создания новых листов с одинаковыми именами для одних и тех же диапазонов на этих листах без возникновения ошибки, сообщающей, что имя уже используется. The benefit is that you can use VBA code to generate new sheets with the same names for the same ranges within those sheets without getting an error saying that the name is already taken.

Как создать именованный диапазон определенного листа: How to Create a WorkSHEET Specific Named Range:

  1. Выделите диапазон, которому нужно присвоить имя. Select the range you want to name.
  2. Перейдите на вкладку “Формулы” на ленте Excel в верхней части окна. Click on the “Formulas” tab on the Excel Ribbon at the top of the window.
  3. Нажмите кнопку “Присвоить имя” на вкладке формул. Click “Define Name” button in the Formula tab.
  4. В диалоговом окне “Создание имени” в поле “Область” выберите конкретный лист, где расположен диапазон, которому нужно присвоить имя (например, “Лист1”), чтобы связать имя с этим листом. In the “New Name” dialogue box, under the field “Scope” choose the specific worksheet that the range you want to define is located (i.e. “Sheet1”)- This makes the name specific to this worksheet. Если выбрать вариант “Книга”, это будет имя книги. If you choose “Workbook” then it will be a WorkBOOK name).

Пример именованного диапазона определенного листа: выделенный диапазон A1:A10 для присвоения имени. Example, of WorkSHEET Specific Named Range: Selected range to name are A1:A10

Выбранное имя диапазона — “Имя”. В пределах одного листа ссылайтесь на именованный диапазон, просто введя в ячейку “=Имя”. Из другого листа ссылайтесь на диапазон определенного листа, указав в ячейке имя листа: “= Лист1!Имя”. Chosen name of range is “name” within the same worksheet refer to the named name mere by entering the following in a cell “=name”, from a different worksheet refer to the worksheet specific range by included the worksheet name in a cell “=Sheet1!name”.

Ссылка на именованный диапазон Referring to a Named Range

В следующем примере выполняется ссылка на диапазон с именем MyRange в книге с именем MyBook.xls. The following example refers to the range named “MyRange” in the workbook named “MyBook.xls.”

В следующем примере выполняется ссылка на диапазон определенного листа с именем Sheet1!Sales в книге с именем Report.xls. The following example refers to the worksheet-specific range named “Sheet1!Sales” in the workbook named “Report.xls.”

Чтобы выбрать именованный диапазон, используйте метод GoTo, который активирует книгу и лист, а затем выбирает диапазон. To select a named range, use the GoTo method, which activates the workbook and the worksheet and then selects the range.

В следующем примере показано, как можно написать эту же процедуру для активной книги. The following example shows how the same procedure would be written for the active workbook.

Пример кода предоставил: Деннис Валлентин VSTO & .NET & Excel Sample code provided by: Dennis Wallentin, VSTO & .NET & Excel

В этом примере в качестве формулы для проверки данных используется именованный диапазон. This example uses a named range as the formula for data validation. В этом примере данные проверки должны быть на листе 2 в диапазоне A2:A100. This example requires the validation data to be on Sheet 2 in the range A2:A100. Они используются для проверки данных, введенных на листе 1 в диапазоне D2:D10. This validation data is used to validate data entered on Sheet 1 in the range D2:D10.

Читайте также:  Ms excel сводные таблицы

Циклический переход по ячейкам в именованном диапазоне Looping Through Cells in a Named Range

В следующем примере выполняется циклический переход по каждой ячейке именованного диапазона с помощью цикла For Each. Next. The following example loops through each cell in a named range by using a For Each. Next loop. Если значение любой ячейки в диапазоне превышает значение Limit , цвет ячейки изменяется на желтый. If the value of any cell in the range exceeds the value of Limit , the cell color is changed to yellow.

Об участнике About the Contributor

Деннис Валлентин (Dennis Wallentin) — автор блога VSTO & .NET & Excel, посвященного решениям .NET Framework для Excel и службам Excel. Dennis Wallentin is the author of VSTO & .NET & Excel, a blog that focuses on .NET Framework solutions for Excel and Excel Services. Деннис разрабатывает решения Excel более 20 лет и также является соавтором книги “Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA, and .NET (2nd Edition)”. Dennis has been developing Excel solutions for over 20 years and is also the coauthor of “Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA and .NET (2nd Edition).”

Поддержка и обратная связь Support and feedback

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Have questions or feedback about Office VBA or this documentation? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь. Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Источник: docs.microsoft.com

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

Адаптированный перевод статьи Тома Огера (Tom Auger) Named Ranges in Excel that Automatically Expand (Dynamic Ranges Part 1). Статья была доступна здесь.

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

Именованные диапазоны в Excel — это отличный инструмент. Они позволяют делать такие вещи, как выпадающие списки в пункте Проверка данных. Или можно присвоить имя диапазону с данными и в дальнейшем ссылаться на него вместо того, чтобы указывать координаты (A1:B5).

Одна из неприятностей, связанных с поддержкой списков — необходимость править диапазон в Формулы > Диспетчер имён после каждого добавления/удаления строк данных в исходном диапазоне. Чтобы избежать подобной ситуации, можно создать динамический диапазон, применив формулы вместо жёстко заданных координат. Чаще всего используется функция СМЕЩ, как показано ниже. Запрос «Excel динамический диапазон» в любом поисковике вернёт сотни ссылок, большинство из которых будут вариантами формулы:

=СМЕЩ(Лист!$A$1, 0, 0, СЧЁТЗ ($A:$A), 1)

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

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

Смещпострокам: обычно 0, т.к. стартовую позицию мы уже определили.

Смещпостолбцам: так же обычно 0, по той же самой причине.

Высота: количество строк нашего диапазона (здесь будет формула, см. ниже).

Ширина: количество столбцов в нашем диапазоне (минимум 1).

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

Типичная формула динамического диапазона, которую можно найти на просторах интернета, использует СЧЁТ (для числовых данных) или СЧЁТЗ (для текста). Обе эти функции подсчитывают количество непустых ячеек. Если мы подсчитаем количество непустых ячеек в одном столбце и введём это число в параметр Высота, то мы получим диапазон от начальной ячейки до последней ячейки с записью. Но это в теории.

В действительности, если вы внимательно читали, мы получим диапазон высотой в то количество строк, которое мы получили от СЧЁТЗ. Если в диапазоне присутствуют пустые ячейки, то выходной диапазон окажется короче, чем необходимо, и последние ячейки потеряются. Помните об этой тонкости.

Наиболее часто динамический диапазон используется в следующих случаях:

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

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

Сначала мы создаём динамический именованный диапазон с помощью Диспетчера имён, после чего используем в нашей формуле.

Формулы, которые мы использовали:

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

Вот начальные данные:

А вот после добавки новых строк (не забываем нажать Обновить):

Третий вариант использования динамического диапазона — определение исходного диапазона для диаграмм. Здесь выводится график, диапазон исходных данных которого регулируется параметром.

В данном примере использованы два динамических диапазона.

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

После добавления новых строк:

Сама формула динамического диапазона тривиальна:

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

Рассмотрим формулу получения динамического диапазона:

Дело в том, что ИНДЕКС возвращает ссылку, если стоит завершающим выражением диапазона (стоит после двоеточия). Чтобы получить адрес последней ячейки нам надо вычислить номер строки ячейки в диапазоне. Его мы получаем через функцию ПОИСКПОЗ. Она ищет определённое значение в указанном диапазоне. С помощью параметра Тип_сопоставления мы указываем функции искать ближайшее меньшее значение. При этом указываем заведомо большее значение для поиска. В результате функция обязательно дойдёт до последней ячейки.Если поиск идёт по числам, то можно использовать 9E+307. Для текста применим функцию ПОВТОР.

Конструкция ПОВТОР(“я”;255) создаёт строку из 255 литер “я”. При сравнении практически любой текст окажется «меньше», и функция найдёт последнюю строку. Ещё одним плюсом применения функции ИНДЕКС является нечувствительность к пустым ячейкам.

Источник: needfordata.ru

Exceltip

Блог о программе Microsoft 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.

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

Вам также могут быть интересны следующие статьи

2 комментария

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

Здравствуйте, Ренат! Очень интересная диаграмма, даже при том, что и не классический тримап. Скажите, а можно построить подобную диаграмму так, чтобы её составляющие были положительные и отрицательные. Чтобы их размер зависел, насколько далеко их значение от 0, а располагались они справа и слева от оси, в зависимости от знака? Была бы очень интересная диаграмма весов.

Источник: exceltip.ru

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

Для чего вообще нужны именованные диапазоны? Обращение к именованному диапазону гораздо удобнее, чем прописывание адреса в формулах и VBA:

  • Предположим, что в формуле мы ссылаемся на диапазон A1:C10 (возможно даже не один раз). Для примера возьмем простую функцию СУММ(суммирует значения указанных ячеек):
    =СУММ( A1:C10 ; F1:K10 )
    Затем нам стало необходимо суммировать другие данные(скажем вместо диапазона A1:C10 в диапазоне D2:F11 ). В случае с обычным указанием диапазона нам придется искать все свои формулы и менять там адрес диапазона на новый. Но если назначить своему диапазону A1:C10 имя(к примеру ДиапазонСумм ), то в формуле ничего менять не придется – достаточно будет просто изменить ссылку на ячейки в самом имени один раз. Я привел пример с одной формулой – а что, если таких формул 10? 30?
    Примерно такая же ситуация и с использованием в кодах: указав имя диапазона один раз не придется каждый раз при изменении и перемещении этого диапазона прописывать его заново в коде.
  • Именованный диапазон не просто так называется именованным. Если взять пример выше – то отображение в формуле названия ДиапазонСумм куда нагляднее, чем A1:C10 . В сложных формулах куда проще будет ориентироваться по именам, чем по адресам. Почему удобнее: если сменить стиль отображения ссылок (подробнее про стиль), то диапазон A1:C10 будет выглядеть как-то вроде этого: R1C1:R10C3 . А если назначить имя – то оно как было ДиапазонСумм , так им и останется.
  • При вводе формулы/функции в ячейку, можно не искать нужный диапазон, а начать вводить лишь первые буквы его имени и Excel предложит его ко вводу:

    Данный метод доступен лишь в версиях Excel 2007 и выше

Как обратиться к именованному диапазону
Обращение к именованному диапазону из VBA

MsgBox Range(“ДиапазонСумм”).Address MsgBox [ДиапазонСумм].Address

Обращение к именованному диапазону в формулах/функциях

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

Ограничения, накладываемые на создание имен

  • В качестве имени диапазона не могут быть использованы словосочетания, содержащие пробел. Вместо него лучше использовать нижнее подчеркивание _ или точку: Name_1, Name.1
  • Первым символом имени должна быть буква, знак подчеркивания (_) или обратная косая черта (). Остальные символы имени могут быть буквами, цифрами, точками и знаками подчеркивания
  • Нельзя в качестве имени использовать зарезервированные в Excel константы – R, C и RC(как прописные, так и строчные). Связано с тем, что данные буквы используются самим Excel для адресации ячеек при использовании стиля ссылок R1C1 (читать подробнее про стили ссылок)
  • Нельзя давать именам названия, совпадающие с адресацией ячеек: B$100, D2(для стиля ссылок А1) или R1C1, R7(для стиля R1C1). И хотя при включенном стиле ссылок R1C1 допускается дать имени название вроде A1 или D130 – это не рекомендуется делать, т.к. если впоследствии стиль отображения ссылок для книги будет изменен – то Excel не примет такие имена и предложит их изменить. И придется изменять названия всех подобных имен. Если очень хочется – можно просто добавить нижнее подчеркивание к имени: _A1
  • Длина имени не может превышать 255 символов

Создание именованного диапазона
Способ первый
обычно при создании простого именованного диапазона я использую именно его. Выделяем ячейку или группу ячеек, имя которым хотим присвоить -щелкаем левой кнопкой мыши в окне адреса и вписываем имя, которое хотим присвоить. Жмем Enter:

Способ второй
Выделяем ячейку или группу ячеек. Жмем правую кнопку мыши для вызова контекстного меню ячеек. Выбираем пункт:

  • Excel 2007: Имя диапазона (Range Name)
  • Excel 2010: Присвоить имя (Define Name)


либо:
Жмем Ctrl + F3
либо:

  • 2007-2016 Excel : вкладка Формулы (Formulas)Диспетчер имен (Name Manager)Создать (New) (либо на той же вкладке сразу – Присвоить имя (Define Name) )
  • 2003 Excel : ВставкаИмяПрисвоить

Появляется окно создания имени

Имя (Name) – указывается имя диапазона. Необходимо учитывать ограничения для имен, которые я описывал в начале статьи.
Область (Scope) – указывается область действия создаваемого диапазона – Книга , либо Лист1 :

  • Лист1 (Sheet1) – созданный именованный диапазон будет доступен только из указанного листа. Это позволяет указать разные диапазоны для разных листов, но указав одно и тоже имя диапазона
  • Книга (Workbook) – созданный диапазон можно будет использовать из любого листа данной книги

Примечание (Comment) – здесь можно записать пометку о созданном диапазоне, например для каких целей планируется его использовать. Позже эту информацию можно будет увидеть из диспетчера имен ( Ctrl + F3 )
Диапазон (Refers to) – при данном способе создания в этом поле автоматически проставляется адрес выделенного ранее диапазона. Его можно при необходимости тут же изменить.

Изменение диапазона
Чтобы изменить имя Именованного диапазона, либо ссылку на него необходимо всего лишь вызывать диспетчер имен( Ctrl + F3 ), выбрать нужное имя и нажать кнопку Изменить (Edit. ) .
Изменить можно имя диапазона (Name) , ссылку (RefersTo) и Примечание (Comment) . Область действия (Scope) изменить нельзя, для этого придется удалить текущее имя и создать новое, с новой областью действия.

Удаление диапазона
Чтобы удалить Именованный диапазон необходимо вызывать диспетчер имен( Ctrl + F3 ), выбрать нужное имя и нажать кнопку Удалить (Delete. ) .

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

Статья помогла? Поделись ссылкой с друзьями!

Источник: www.excel-vba.ru