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

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

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

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

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

Щелкните Формулы > Создать из выделенного.

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

Нажмите кнопку ОК.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

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

Именованные диапазоны помогут вам быстрее обрабатывать отдельные элементы таблицы, например я, часто ими пользуюсь.

Что такое именованные диапазоны в Excel?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вы начинаете вводить его имя и 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$100,СЧЁТЕСЛИ($A$2:$A$100,»<>»&»») -> выдаст нам значение последнего элемента столбика.

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

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

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

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

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

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 за 6 шагов (с советами)

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

Что такое именованный диапазон?

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

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

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

Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)

Простой поиск данных

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

Повышение эффективности

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

Упрощение формул

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

Например, в электронной таблице в ячейках с B2 по B12 указаны данные о доходах от продаж каждого члена команды за месяц. Выделив эти ячейки, можно создать именованный диапазон под названием «Продажи», используя формулу =СУММ(Продажи). И наоборот, без именованного диапазона формула для расчета общего дохода команды от продаж будет =СУММ(B2:B12).

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

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

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

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

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

Перейдите к «окну имени» в верхнем левом углу.

Создайте имя из одного слова для диапазона.

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

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

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

Имена не чувствительны к регистру, поэтому Excel рассматривает такие заголовки, как «имена» и «ИМЯ», как одно и то же имя.

Имя не может быть только C, c, R или r, потому что это ярлыки выбора в Excel.

Имя не может напоминать адрес ячейки, например A1B2.

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

Имя может быть длиной только в один мир и не может содержать пробелы.

Как редактировать именованные диапазоны в Excel

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

Перейдите на вкладку «Формулы».

Найдите «Диспетчер имен».

Выберите именованный диапазон, который вы хотите изменить.

Введите новое имя в строке «Имя» в поле «Редактировать имя».

Нажмите «Закрыть» в поле «Диспетчер имен».

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

Перейдите на вкладку «Формулы».

Выберите «Диспетчер имен».

Выберите именованный диапазон, который вы хотите изменить.

Обновите ссылки на ячейки в строке «Ссылается на» в поле «Редактировать имя».

Нажмите «Закрыть» в поле «Диспетчер имен».

Советы по работе с именованными диапазонами в Excel

Примите во внимание следующие советы, которые помогут вам успешно использовать именованные диапазоны:

Удалить неиспользуемые именованные диапазоны

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

Перейдите на вкладку «Формулы».

Найдите группу «Определенные имена».

Нажмите «Диспетчер имен».

Выберите именованный диапазон, который вы хотите удалить.

Попробуйте использовать ярлыки

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

F3: Нажатие «F3» позволяет получить список всех именованных диапазонов и вставить его в «Формулу».

Control + F3: нажатие «Control + F3» позволяет использовать «Диалоговое окно диспетчера имен» для создания нового имени.

Control + Shift + F3: нажатие «Control + Shift + F3» позволяет вам создать именованный диапазон из вашего текущего выделения.

Используйте «Диспетчер имен» осторожно

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

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