Bugsp.ru

Ваша компьютерная помощь
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Именованный диапазон в 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 ИД с соответствующими именами:

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

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

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

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

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

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

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

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

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

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

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

dynamic_range1.png

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

Читайте так же:
Как работает Яндекс Диск

dynamic_range3.png

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

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

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

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

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

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

dynamic_range4.png

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

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

dynamic_range5.png

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

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

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

dynamic_range6.png

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

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

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

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

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

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

dynamic_range7.png

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

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

dynamic_range8.png

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

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

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

Она выдает содержимое ячейки из диапазона по номеру строки и столбца, т.е. например функция =ИНДЕКС(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) :

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

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

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

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

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

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

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

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

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

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

Имя диапазона строке имен в Microsoft Excel

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

Имя диапазону не присвоено в Microsoft Excel

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

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

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

Присвоение имени диапазону через поле имен в Microsoft Excel

Наименование диапазону через поле имен присвоено в Microsoft Excel

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

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

Переход к присвоению имени диапазону ячеек через контекстное меню в Microsoft Excel

Окно создания имени в Microsoft Excel

Наименование диапазону через контекстное меню присвоено в Microsoft Excel

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

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

Переход к присвоению имени диапазону через кнопку на ленте в Microsoft Excel

Окно создания имени в программе Microsoft Excel

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

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

Переход в Диспетчер имен в Microsoft Excel

Переход к созданию имени в Диспетчере имён в Microsoft Excel

Закрытие окна Диспетчера имён в Microsoft Excel

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

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

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

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

Читайте так же:
Гугл приложения для браузера

Наименование диапазону моделей присвоено в Microsoft Excel

Переход в окно проверки данных в Microsoft Excel

Окно проверки вводимых значений в Microsoft Excel

Выпадающий список в Microsoft Excel

Значение из выпадающего списка выбрано в Microsoft Excel

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

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

Таблица выручки по филиалам предприятия в Microsoft Excel

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

Имя диапазону Филиал 1 присвоено в Microsoft Excel

Имя всем диапазонам таблицы пррисвоено в Microsoft Excel

Переход в Мастер функций в Microsoft Excel

Переход в окно аргументов функции СУММ в Microsoft Excel

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

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

Окно аргументов функции СУММ в Microsoft Excel

Результат вычисления функции СУММ в Microsoft Excel

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

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

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

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

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

Переход в Диспетчер имен в программе Microsoft Excel

  • Имена на листе;
  • в книге;
  • с ошибками;
  • без ошибок;
  • Определенные имена;
  • Имена таблиц.

Фильтрация в Диспетчере имён в Microsoft Excel

Переход к изменению именованного диапазона через Диспетчер имен в Microsoft Excel

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

Окно изменения имени именнованного диапазона в Microsoft Excel

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

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

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

Удаление именованного диапазона через Диспетчер имен в Microsoft Excel

Подтверждение удаления имени в Microsoft Excel

Закрытие окна Диспетчера имен в Microsoft Excel

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

ЗакрытьМы рады, что смогли помочь Вам в решении проблемы.

Помимо этой статьи, на сайте еще 12307 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Отблагодарите автора, поделитесь статьей в социальных сетях.

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

Читайте так же:
Скачать AdwCleaner бесплатно последнюю версию

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Именованные диапазоны в 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, а располагались они справа и слева от оси, в зависимости от знака? Была бы очень интересная диаграмма весов.

голоса
Рейтинг статьи
Ссылка на основную публикацию