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

Обновлено: 05.07.2024

Именованные диапазоны — одна из самых полезных функций Excel. Они облегчают чтение и понимание ваших формул; они автоматически дают вам абсолютные ссылки и уменьшают количество ошибок.

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

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

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

То же самое я проделаю со столбцом "Цена". Теперь я могу ссылаться на цены в других формулах, используя это имя.

Вы также можете именовать диапазоны более формально, используя элементы управления, которые отображаются на вкладке "Формулы" на ленте.

Например, я могу назвать столбец "Кровати", сначала выбрав данные, а затем нажав "Определить имя". Затем Excel откроет диалоговое окно «Новое имя», где я могу дать диапазону имя, установить область действия, предоставить описание и, наконец, выбрать ячейки, к которым должно относиться имя.

Excel также может одновременно называть несколько диапазонов с помощью кнопки "Создать из выделенного".

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

Когда я нажимаю "ОК", Excel создает три имени.

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

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

Если вы работаете с электронными таблицами Excel, это может означать большую экономию времени и эффективность.

В этом руководстве вы узнаете, как создавать именованные диапазоны в Excel и как использовать их для экономии времени.

Это руководство охватывает:

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

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

Аналогично в Excel вы можете дать имя ячейке или диапазону ячеек.

Теперь вместо ссылки на ячейку (например, A1 или A1:A10) вы можете просто использовать присвоенное ей имя.

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

В этом наборе данных, если вам нужно обратиться к диапазону с датой, вам придется использовать A2:A11 в формулах. Точно так же для торговых представителей и продаж вам придется использовать B2:B11 и C2:C11.

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

Именованные диапазоны Excel упрощают обращение к наборам данных в Excel.

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

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

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

Вот преимущества использования именованных диапазонов в Excel.

Используйте имена вместо ссылок на ячейки

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

Например, вы можете использовать =СУММ(ПРОДАЖИ) вместо =СУММ(C2:C11) для приведенного выше набора данных.

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

Количество продаж со значением более 500: =СЧЁТЕСЛИ(Продажи,">500″)
Сумма всех продаж, совершенных Томом: =СУММЕСЛИ(СУММЕСЛИ(Представитель по продажам, "Том",Продажи)
Комиссия, полученная Джо (продажи Джо, умноженные на комиссионный процент):
=СУММЕСЛИ(SUMIF(SalesRep»,Joe»,Sales)*Commission

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

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

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

Вы можете просто ввести пару букв этого именованного диапазона, и Excel покажет соответствующие именованные диапазоны (как показано ниже):

Именованные диапазоны делают формулы динамичными

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

Например, в случае комиссии с продаж вместо значения 2,5 % можно использовать именованный диапазон.

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

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

Вот три способа создания именованных диапазонов в Excel:

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

Это создаст именованный диапазон SALESREP.

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

Например, в приведенном ниже наборе данных, если вы хотите быстро создать три именованных диапазона (Date, Sales_Rep и Sales), вы можете использовать метод, показанный ниже.

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

Это создаст три именованных диапазона — Date, Sales_Rep и Sales.

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

Соглашение об именах для именованных диапазонов в Excel

Существуют определенные правила именования, которые необходимо знать при создании именованных диапазонов в Excel:

Первым символом именованного диапазона должна быть буква и символ подчеркивания (_) или обратная косая черта (\). Если это что-то другое, он покажет ошибку. Остальные символы могут быть буквами, цифрами, специальными символами, точкой или символом подчеркивания.
Вы не можете использовать имена, которые также представляют ссылки на ячейки в Excel. Например, вы не можете использовать AB1, так как это также ссылка на ячейку.
Вы не можете использовать пробелы при создании именованных диапазонов. Например, у вас не может быть торгового представителя в качестве именованного диапазона. Если вы хотите объединить два слова и создать именованный диапазон, используйте для его создания символ подчеркивания, точку или символы верхнего регистра. Например, у вас может быть Sales_Rep, SalesRep или SalesRep.
- При создании именованных диапазонов Excel одинаково обрабатывает прописные и строчные буквы. Например, если вы создадите именованный диапазон ПРОДАЖИ, вы не сможете создать другой именованный диапазон, такой как «продажи» или «Продажи».
Слишком много именованных диапазонов в Excel? Не волнуйтесь

Иногда при работе с большими наборами данных и сложными моделями в Excel может создаваться множество именованных диапазонов.

Что делать, если вы не помните название созданного вами именованного диапазона?

Не волнуйтесь — вот несколько полезных советов.

Получение имен всех именованных диапазонов

Вот как получить список всех созданных вами именованных диапазонов:

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

Отображение совпадающих именованных диапазонов

Если вы уже создали именованный диапазон, вы можете изменить его, выполнив следующие действия:

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

Вот несколько полезных сочетаний клавиш, которые пригодятся при работе с именованными диапазонами в Excel:
- Чтобы получить список всех именованных диапазонов и вставить его в формулу: F3.
- Чтобы создать новое имя с помощью диалогового окна диспетчера имен, нажмите Control + F3.
- Чтобы создать именованные диапазоны из выделенного: Control + Shift + F3.
Создание динамических именованных диапазонов в Excel

До сих пор в этом руководстве мы создавали статические именованные диапазоны.

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

Например, если A1:A10 называется "Продажи", это всегда будет относиться к A1:A10.

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

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

Чтобы решить эту проблему, мы можем создать динамические именованные диапазоны в Excel, которые будут автоматически учитывать дополнительные данные и включать их в существующий именованный диапазон.

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

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

Давайте посмотрим, как создавать динамические именованные диапазоны в Excel.

Предположим, у нас есть данные о продажах в ячейке A2:A11.

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

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

Как работают динамические именованные диапазоны?

Чтобы объяснить, как это работает, вам нужно немного больше узнать о функции ИНДЕКС в Excel.

Большинство людей используют ИНДЕКС для возврата значения из списка на основе номера строки и столбца.

Но у функции ИНДЕКС есть и другая сторона.

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

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

INDEX($A$2:$A$100,COUNTIF($A$2:$A$100,"<>"&"") –> Ожидается, что эта часть формулы вернет значение (которое будет 10-м значение из списка, учитывая, что элементов десять).

Однако при использовании перед ссылкой (= $A$2: ИНДЕКС($A$2:$A$100,СЧЁТЕСЛИ($A$2:$A$100,"<>"&"")) возвращается ссылка на ячейку вместо значения.

Следовательно, здесь возвращается =$A$2:$A$11

Если мы добавим два дополнительных значения в столбец продаж, он вернет =$A$2:$A$13

При добавлении новых данных в список функция Excel СЧЁТЕСЛИ возвращает количество непустых ячеек в данных. Этот номер используется функцией ИНДЕКС для извлечения ссылки на ячейку последнего элемента в списке.

Примечание:
- Это будет работать, только если в данных нет пустых ячеек.
- В приведенном выше примере я назначил большое количество ячеек (A2:A100) для формулы именованного диапазона. Вы можете изменить это в зависимости от вашего набора данных.
Вы также можете использовать функцию OFFSET для создания динамических именованных диапазонов в Excel, однако, поскольку функция OFFSET нестабильна, это может привести к медленной работе книги Excel. ИНДЕКС, с другой стороны, является полузависимым, что делает его лучшим выбором для создания динамических именованных диапазонов в Excel.

Создайте именованный диапазон или именованную константу и используйте эти имена в формулах Excel. Так вы сможете упростить понимание своих формул.

Чтобы создать именованный диапазон, выполните следующие действия.
<р>1. Выберите диапазон A1:A4.

<р>2. На вкладке "Формулы" в группе "Определенные имена" нажмите "Определить имя".

<р>3. Введите имя и нажмите OK.

Есть еще более быстрый способ сделать это.
<р>4. Выберите диапазон, введите имя в поле Имя и нажмите Enter.

<р>5. Теперь вы можете использовать этот именованный диапазон в своих формулах. Например, сумма цен.

Чтобы создать именованную константу, выполните следующие шаги.
<р>6. На вкладке "Формулы" в группе "Определенные имена" нажмите "Определить имя".

<р>7. Введите имя, введите значение и нажмите OK.

<р>8. Теперь вы можете использовать эту именованную константу в своих формулах.

Если налоговая ставка изменится, используйте диспетчер имен, чтобы изменить значение, и Excel автоматически обновит все формулы, в которых используется TaxRate.
<р>9. На вкладке "Формулы" в группе "Определенные имена" нажмите "Диспетчер имен".

<р>10. Выберите TaxRate и нажмите «Изменить».

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

Если ваши данные имеют метки, вы можете быстро создать именованные диапазоны в Excel.
<р>11. Например, выберите диапазон A1:D13.

<р>12. На вкладке "Формулы" в группе "Определенные имена" нажмите "Создать из выбранного".

<р>13. Установите флажок Верхний ряд и Левый столбец и нажмите ОК.

<р>14. Excel создал 12 + 3 = 15 именованных диапазонов! Просто выберите диапазон и посмотрите на поле Имя.

<р>15. Используйте оператор пересечения (пробел), чтобы вернуть пересечение двух именованных диапазонов.

Примечание: попробуйте сами. Загрузите файл Excel, быстро и легко создайте именованные диапазоны (начните с шага 11) и найдите любое значение в этом двумерном диапазоне.

В этом руководстве по Excel объясняется, как определить именованный диапазон в Excel 2016 (со снимками экрана и пошаговыми инструкциями).

См. решение в других версиях Excel:

Если вы хотите следовать этому руководству, загрузите пример электронной таблицы.

Вопрос: как в Microsoft Excel 2016 настроить именованный диапазон, чтобы использовать его в формуле?

Ответ. Именованный диапазон — это описательное имя набора ячеек или диапазона на листе. Чтобы добавить именованный диапазон, выберите диапазон ячеек, которым вы хотите присвоить имя. В этом примере мы выбрали все ячейки в столбце A.

Затем выберите вкладку "Формулы" на панели инструментов в верхней части экрана и нажмите кнопку "Определить имя" в группе "Определенные имена".

Когда появится окно "Новое имя", введите описательное имя для диапазона. Имя может содержать до 255 символов. В этом примере мы ввели Order_ID в качестве названия диапазона.

Затем в поле "Относится к" введите диапазон ячеек, к которым относится это имя. В этом примере автоматически устанавливается диапазон =Лист1!$A:$A, поскольку это диапазон ячеек, которые мы ранее выделили.

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

Теперь, когда вы вернетесь к электронной таблице, вы увидите, что имя Order_ID появилось в поле Имя (обведено красным на изображении ниже). Поле Имя можно найти в левом конце поля формулы. Теперь всякий раз, когда вы выбираете столбец A, вы увидите, что это имя диапазона появляется в поле Имя.

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

Эта формула SUM суммирует все значения идентификатора заказа в столбце A листа 1.

Читайте также: