Tooprogram.ru

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

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

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

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

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

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

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

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

Читать еще:  Макрос для сохранения файла excel

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

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

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

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

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

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

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

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

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

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

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

Именованные диапазоны 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)

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

Читать еще:  Майкрософт excel онлайн

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Данный метод доступен лишь в версиях Excel 2007 и выше
Читать еще:  Удаление таблицы excel

Как обратиться к именованному диапазону
Обращение к именованному диапазону из 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. ) .

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

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

Ссылка на основную публикацию
Adblock
detector