Excel

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

Named Ranges Excel

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





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

Фактически, я постоянно использую именованные диапазоны при тестировании и создании прототипов формул. Они помогают мне быстрее работать с формулами. Я также использую именованные диапазоны, потому что я ленив и не люблю вводить сложные ссылки :)





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

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

Именованный диапазон - это просто удобочитаемое имя диапазона ячеек в Excel. Например, если я назову диапазон A1: A100 «данные», я могу использовать MAX, чтобы получить максимальное значение с помощью простой формулы:

 
 = MAX (data) // max value

Простой именованный диапазон, называемый



Красота именованных диапазонов заключается в том, что вы можете использовать значимые имена в своих формулах, не думая о ссылках на ячейки. Если у вас есть именованный диапазон, просто используйте его как ссылку на ячейку. Все эти формулы действительны для именованного диапазона data:

 
= MAX (data) // max value = MIN (data) // min value = COUNT (data) // total values = AVERAGE (data) // min value

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

Создать именованный диапазон легко

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

Быстрое создание именованного диапазона с полем имени

Чтобы быстро протестировать новый диапазон, выберите новое имя в раскрывающемся списке рядом с полем имени. Excel выберет диапазон на листе.

Excel может создавать имена автоматически (ctrl + shift + F3)

Если у вас есть хорошо структурированные данные с метками, вы можете попросить Excel создать для вас именованные диапазоны. Просто выберите данные вместе с метками и используйте команду «Создать из выделенного» на вкладке «Формулы» на ленте:

Создание имен из команды выбора на ленте

Вы также можете использовать сочетание клавиш control + shift + F3.

Используя эту функцию, мы можем создать именованные диапазоны для населения 12 штатов за один шаг:

есть ли способ найти дубликаты в Excel

Создание имен из выбранных с выбранными данными и метками

Когда вы нажимаете ОК, имена создаются. Вы найдете все вновь созданные имена в раскрывающемся меню рядом с полем имени:

Новые имена также отображаются в раскрывающемся меню поля имени.

Создав имена, вы можете использовать их в таких формулах

 
= SUM (MN,WI,MI)

Обновить именованные диапазоны в диспетчере имен (Control + F3)

После создания именованного диапазона используйте Имя Менеджер (Control + F3) для обновления по мере необходимости. Выберите имя, с которым хотите работать, затем измените ссылку напрямую (т. Е. Редактировать «относится к») или нажмите кнопку справа и выберите новый диапазон.

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

Нет необходимости нажимать кнопку «Изменить», чтобы обновить ссылку. Когда вы нажмете «Закрыть», название диапазона будет обновлено.

Примечание: если вы выберете весь именованный диапазон на листе, вы можете перетащить его в новое место, и ссылка будет обновлена ​​автоматически. Однако я не знаю, как настроить ссылки на диапазон, щелкнув и перетащив прямо на лист. Если вы знаете, как это сделать, сообщите об этом ниже!

Просмотреть все именованные диапазоны (Ctrl + F3)

Чтобы быстро просмотреть все именованные диапазоны в книге, используйте раскрывающееся меню рядом с полем имени.

Если вы хотите увидеть более подробную информацию, откройте Диспетчер имен (Control + F3), в котором перечислены все имена со ссылками, а также есть фильтр:

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

Примечание: на Mac нет диспетчера имен, поэтому вместо него вы увидите диалоговое окно «Определить имя».

Скопируйте и вставьте все именованные диапазоны (F3)

Если вам нужна более постоянная запись именованных диапазонов в книге, вы можете вставить полный список имен в любом месте. Перейдите в меню «Формулы»> «Использовать в формуле» (или воспользуйтесь сочетанием клавиш F3), затем выберите «Вставить имена»> «Вставить список»:

Диалоговое окно 'Вставить имена

Когда вы нажмете кнопку «Вставить список», вы увидите имена и ссылки, вставленные в рабочий лист:

После вставки именованных диапазонов в рабочий лист

Смотрите имена прямо на листе

Если вы установите уровень масштабирования менее 40%, Excel отобразит имена диапазонов прямо на листе:

При уровне масштабирования <40% Excel будет отображать имена диапазонов.

Спасибо за этот совет, Фелипе!

Имена имеют правила

При создании именованных диапазонов соблюдайте следующие правила:

  1. Имена должны начинаться с буквы, символа подчеркивания (_) или обратной косой черты ().
  2. Имена не могут содержать пробелы и большинство знаков препинания.
  3. Имена не могут конфликтовать со ссылками на ячейки - вы не можете назвать диапазон «A1» или «Z100».
  4. Отдельные буквы подходят для имен ('a', 'b', 'c' и т. Д.), Но буквы 'r' и 'c' зарезервированы.
  5. Имена не чувствительны к регистру - «home», «HOME» и «HoMe» одинаковы для Excel.

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

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

Например, допустим, вы назвали ячейку в своей книге «обновленной». Идея состоит в том, что вы можете поместить текущую дату в ячейку (Ctrl +) и указать дату в другом месте книги.

Использование именованного диапазона внутри текстовой формулы

Формула в B8 выглядит так:

 
='Updated: '&  TEXT (updated, 'ddd, mmmm d, yyyy')

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

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

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

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

Именованные диапазоны могут работать как константы

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

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

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

Использование именованного диапазона как константы в формуле

Именованные диапазоны по умолчанию являются абсолютными

По умолчанию именованные диапазоны действуют как абсолютные ссылки. Например, на этом листе формула для расчета топлива будет следующей:

 
=C5/$D

Стандартная формула с абсолютным адресом

Ссылка на D2 является абсолютной (заблокированной), поэтому формулу можно скопировать без изменения D2.

Если мы назовем D2 «MPG», формула будет иметь следующий вид:

 
=C5/MPG

Использование именованного диапазона как константы в формуле

Поскольку по умолчанию MPG является абсолютным, формулу можно скопировать в столбец D как есть.

Именованные диапазоны также могут быть относительными

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

Например, вы можете создать общий именованный диапазон CellAbove следующим образом:

  1. Выберите ячейку A2
  2. Control + F3, чтобы открыть диспетчер имен
  3. Перейдите в раздел 'Относится к' и введите: = A1.

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

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

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

Если у вас есть формулы, в которых не используются именованные диапазоны, вы можете попросить Excel применить именованные диапазоны в формулах за вас. Начните с выбора ячеек, содержащих формулы, которые вы хотите обновить. Затем запустите «Формулы»> «Определить имена»> «Применить имена».

Диалоговое окно 'Применить имена

Затем Excel заменит ссылки, имеющие соответствующий именованный диапазон, самим именем.

Вы также можете применять имена с помощью поиска и замены:

Применение диапазонов имен с помощью функции поиска и замены

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

Ключевые преимущества именованных диапазонов

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

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

 
= VLOOKUP ($H,$B:$E,2,0)

Без именованных диапазонов формулы могут быть загадочными

обратиться к другому листу в Excel

Однако с B3: E11 с именем «данные» и с H4 с именем «планета» мы можем написать такие формулы:

 
= VLOOKUP (planet,data,2,0) // position = VLOOKUP (planet,data,3,0) // diameter = VLOOKUP (planet,data,4,0) // satellites

С именованными диапазонами формулы могут быть простыми

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

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

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

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

 
= SUM (--( FREQUENCY (data,data)>0))

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

Совет: я рекомендую вам создать необходимые имена диапазонов * сначала * в целевой книге, а затем скопировать формулу только как текст (т. Е. Не копировать ячейку, содержащую формулу, на другой лист, просто скопируйте текст формулы ). Это мешает Excel создавать имена на лету и l позволяет вам полностью контролировать процесс создания имени. Чтобы скопировать только текст формулы, скопируйте текст из строки формул или скопируйте через другое приложение (например, браузер, текстовый редактор и т. Д.).

Именованные диапазоны можно использовать для навигации

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

Именованные диапазоны обеспечивают простую навигацию

Именованные диапазоны хорошо работают с гиперссылками

Именованные диапазоны упрощают создание гиперссылок. Например, если вы назовете A1 в Sheet1 «дом», вы можете создать гиперссылку в другом месте, которая вернет вас туда.

Создание гиперссылки на именованный диапазон

Пример гиперссылки именованного диапазона на листе

Чтобы использовать именованный диапазон внутри функции ГИПЕРССЫЛКА, добавьте символ фунта перед именованным диапазоном:

 
= HYPERLINK ('#home','take me home')

Примечание: как ни странно, вы не можете ссылаться на таблицу, как на обычное имя диапазона. Однако вы можете определить имя, равное таблице (например, = Table1), и гиперссылку на нее. Если кто-то знает способ напрямую связать таблицу, присоединяйтесь!

Именованные диапазоны для проверки данных

Диапазоны имен хорошо подходят для проверки данных, поскольку они позволяют использовать ссылку с логическим именем для проверки ввода с помощью раскрывающегося меню. Ниже диапазон G4: G8 назван 'statuslist', затем примените проверку данных со списком, связанным следующим образом:

Использование именованного диапазона для проверки данных со списком

Результатом является раскрывающееся меню в столбце E, которое допускает значения только в названном диапазоне:

Проверка данных с примером именованного диапазона

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

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

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

Таблица - это самый простой способ создать динамический именованный диапазон. Выберите любую ячейку в данных, затем используйте сочетание клавиш Control + T:

Создание таблицы Excel

Когда вы создаете таблицу Excel, имя создается автоматически (например, Table1), но вы можете переименовать таблицу по своему усмотрению. После того, как вы создали таблицу, она будет автоматически расширяться при добавлении данных.

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

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

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

  • Пример формулы динамического диапазона с ИНДЕКСОМ
  • Пример формулы динамического диапазона со смещением

Имена таблиц при проверке данных

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

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

Видео: Как использовать именованные диапазоны с таблицами

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

Примечание: Если у вас есть формулы, которые относятся к именованным диапазонам, вы можете сначала обновить формулы, прежде чем удалять имена. В противном случае вы увидите # ИМЯ? ошибки в формулах, которые по-прежнему относятся к удаленным именам. Всегда сохраняйте рабочий лист перед удалением именованных диапазонов на случай, если у вас возникнут проблемы и вам нужно вернуться к исходному состоянию.

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

Когда вы удаляете * часть * именованного диапазона или вставляете ячейки / строки / столбцы внутри именованного диапазона, ссылка на диапазон будет соответствующим образом скорректирована и останется действительной. Однако, если вы удалите все ячейки, которые охватывают именованный диапазон, именованный диапазон потеряет ссылку и отобразит ошибку #REF. Например, если я назову A1 «тест», а затем удалю столбец A, диспетчер имен покажет «относится к» как:

 
=Sheet1!#REF!

Удалить имена с помощью диспетчера имен

Чтобы удалить именованные диапазоны из книги вручную, откройте диспетчер имен, выберите диапазон и нажмите кнопку «Удалить». Если вы хотите удалить более одного имени одновременно, вы можете Shift + щелчок или Ctrl + щелчок, чтобы выбрать несколько имен, а затем удалить за один шаг.

Удалить имена с ошибками

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

Меню фильтров Менеджера имен

Затем нажмите Shift + щелкните, чтобы выбрать все имена и удалить.

Именованные диапазоны и область действия

Именованные диапазоны в Excel имеют так называемую «область», которая определяет, является ли именованный диапазон локальным для данного рабочего листа или глобальным для всей книги. Глобальные имена имеют область видимости «книга», а локальные имена имеют область действия, равную имени листа, на котором они существуют. Например, областью действия для локального имени может быть Sheet2.

Цель области

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

Локальный охват

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

Чтобы ссылаться на имя с локальной областью видимости, вы можете префикс имени листа перед именем диапазона:

 
Sheet1!total_revenue Sheet2!total_revenue Sheet3!total_revenue

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

 
Sheet3!my_new_name

Глобальный охват

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

 
=last_update

Глобальные имена в книге должны быть уникальными.

Локальный охват

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

Управление областью именованного диапазона

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

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

Ян Карел Питерс и Чарльз Уильямс разработали служебную программу под названием Name Manager, которая предоставляет множество полезных операций для именованных диапазонов. Вы можете загрузите утилиту Name Manager здесь .

Автор Дэйв Брунс


^