Как назвать диапазон ячеек в Excel
Обновлено: 21.11.2024
Создавайте имена Excel, которые ссылаются на ячейки, постоянное значение или формулу. Используйте имена в формулах или быстро выберите именованный диапазон.
Имена Excel — Введение
В Excel вы можете создавать имена, которые относятся к:
- Ячейки на листе
- Конкретное значение
- Формула
После того, как вы определите имена Excel, вы сможете:
- Используйте эти имена в формуле вместо постоянного значения или ссылки на ячейку.
- Введите имя, чтобы быстро перейти к именованному диапазону ячеек.
Приведенные ниже инструкции показывают, как создавать имена и использовать имена в файлах Excel. Получите образец рабочей книги Excel, чтобы следовать инструкциям.
ПРИМЕЧАНИЕ. Чтобы создать краткий список всех имен в книге, см. инструкции «Быстрый список имен — без макросов».
Как назвать ячейки
Посмотрите это короткое видео, чтобы узнать, как назвать группу ячеек. Затем перейдите к этой именованной группе ячеек или используйте имя в формуле. Письменная инструкция находится под видео. ТОП
Ячейки с именами — Поле с именами
Вы можете быстро назвать выбранные ячейки, введя текст в поле имени. ПРИМЕЧАНИЕ. Существует несколько правил для имен Excel
- Выберите ячейки, которым нужно присвоить имя.
- Нажмите в поле "Имя" слева от строки формул.
- Введите допустимое название списка, состоящее из одного слова, например, Список фруктов.
- Нажмите клавишу Enter.
Правила создания имен
На сайте Microsoft есть правила для имен Excel, и они кратко изложены ниже.
Для более подробного ознакомления с тем, какие символы разрешены, см. проект excel-names Мартина Труммера на GitHub — в нем есть письменные примеры и файл Excel для загрузки.
Следуйте этим правилам Microsoft при создании имени в Excel.
- Первый символ имени должен быть одним из следующих символов:
- письмо
- подчеркивание (_)
- обратная косая черта (\).
- буквы
- числа
- периоды
- символы подчеркивания
- Пробелы не допускаются в составе имени.
- Имена не могут выглядеть как адреса ячеек, например 35 австралийских долларов или R2D2.
- C, c, R, r — нельзя использовать в качестве имен — Excel использует их как ярлыки для выбора.
Посмотрите имена
ПРИМЕЧАНИЕ. Чтобы создать краткий список всех имен в книге, см. инструкции «Быстрый список имен — без макросов».
Чтобы увидеть некоторые из именованных диапазонов на листе, используйте этот прием:
- В правом нижнем углу окна Excel выберите параметр "Уровень масштаба".
- В диалоговом окне "Масштаб" выберите "Пользовательский".
- Введите 39 в процентное поле и нажмите "ОК".
Имена некоторых диапазонов будут отображаться на листе синим текстом, как MonthList на этом снимке экрана.
- Имена, созданные с помощью формулы, например YearList, отображаться не будут.
- Некоторые диапазоны могут быть слишком маленькими, чтобы отображать их названия.
Изменить именованный диапазон
После создания именованного диапазона может потребоваться изменить ячейки, на которые он ссылается. В этом коротком видео показаны шаги, а под видео есть письменные инструкции.
Выполните следующие действия, чтобы изменить ссылку на диапазон:
- На ленте откройте вкладку "Формулы".
- Нажмите "Диспетчер имен".
- В списке нажмите на имя, которое хотите изменить.
- В поле "Относится к" измените ссылку на диапазон или перетащите лист, чтобы выбрать новый диапазон.
- Нажмите на галочку, чтобы сохранить изменения
- Нажмите "Закрыть", чтобы закрыть ВЕРХНЮЮ часть диспетчера имен.
Создать имена из текста ячейки
Чтобы быстро назвать отдельные ячейки или отдельные диапазоны, можно использовать в качестве имен текст заголовка ячейки. Посмотрите это видео, чтобы увидеть шаги. Письменные инструкции находятся под видео.
Создать имена из текста ячейки
Быстрый способ создать имена – создать их на основе текста заголовков ячеек (меток листа). В показанном ниже примере ячейки в столбце E будут названы на основе меток в столбце D.
Чтобы назвать ячейки или диапазоны на основе меток рабочего листа:
-
Выберите метки и ячейки, которые должны быть названы. Метки могут располагаться выше, ниже, слева или справа от ячеек, которые нужно назвать. В этом примере метки находятся в столбце B слева от ячеек, которым будут присвоены имена.
Создать имя для значения
Большинство имен Excel относятся к диапазонам на листе, но имена также могут использоваться для хранения значений.
Часто используемые значения
Например, создайте имя для хранения процентной суммы, которую вы часто используете, например ставки розничного налога:
Затем используйте это имя в формулах вместо ввода значения
Специальные значения
Вы также можете создавать имена для хранения значений, которые трудно ввести. Например, в некоторых формулах используется это странное число. Согласно спецификациям Excel на сайте Microsoft, это максимальное положительное число, которое можно ввести в ячейку Excel.
Вместо того, чтобы вводить это число в свои формулы, вы можете определить имя, используя это значение (скопируйте число с этой страницы, прежде чем создавать имя):
Затем используйте имя XL_Max в формулах, например эту формулу ПРОСМОТР, которая находит последнее число в столбце.
=ПРОСМОТР(9.99999999999999E+307, Данные о весе[Вес])
Как использовать имена Excel
После создания имен вы можете их использовать:
Использовать имена для быстрой навигации
Если имя относится к диапазону, вы можете выбрать это имя в раскрывающемся списке Поле имени, чтобы выбрать именованный диапазон на листе.
ПРИМЕЧАНИЕ. Если имя не отображается в раскрывающемся списке, вместо него можно ввести имя
Использование имен в формулах
Вы также можете использовать имена в формулах. Например, у вас может быть группа ячеек с количеством проданных товаров. Назовите эти ячейки Количество, а затем используйте эту формулу для расчета общей суммы:
=СУММ(Количество)
Трюки с полем имени
Помимо использования поля имени для создания именованного диапазона или выбора именованного диапазона, есть еще несколько приемов с полем имени.
Изменить размер поля имени
В старых версиях Excel поле имени имело заданную ширину, и изменить ее было нельзя. Вот как можно настроить ширину окна имени в новых версиях:
- Наведите указатель мыши на кнопку с тремя точками справа от поля имени.
- Когда указатель примет вид двунаправленной стрелки, перетащите его влево или вправо, чтобы изменить ширину.
Выбрать ячейки
Еще один удобный прием заключается в том, что вы можете использовать поле имени для выбора безымянных ячеек. Вот несколько способов, которыми этот трюк может быть полезен: показать столбцы или заполнить большой диапазон ячеек.
Показать столбцы
Вот быстрый способ отобразить определенные столбцы и оставить другие скрытыми.
Заполнить ячейки
С помощью функции автозаполнения Excel вы можете очень быстро создать список дат, чисел или других последовательностей. Просто введите одно или два значения в качестве начальной последовательности, выберите эти ячейки и дважды щелкните маркер заполнения, чтобы заполнить последнюю строку данных.
Иногда в соседнем столбце нет данных, поэтому автозаполнение не работает при двойном щелчке. Вы можете перетащить маркер заполнения вниз, но это не очень эффективно, если вам нужно создать длинную серию.
Вот как создать список из 1000 чисел в столбце A:
- Нажмите в поле имени.
- Введите a1:a1000 в поле имени и нажмите Enter.
- Выделив ячейки, введите цифру 1 и нажмите Ctrl + Ввод.
- Далее выберите ячейку A1 и введите первое число в своей серии, например 5
- Выберите ячейку A2 и введите второе число в ряду, например 10
- Выберите ячейки A1 и A2 и дважды нажмите маркер заполнения, чтобы создать серию из 1000 чисел.
Создать динамический именованный диапазон
Если список, который вы хотите назвать, будет часто меняться, добавляя и удаляя элементы, вам следует создать динамический именованный диапазон. Размер динамического именованного диапазона будет автоматически изменяться при изменении списка. Вот два способа создания динамического именованного диапазона:
Использование именованной таблицы Excel
Самый простой способ создать динамический именованный диапазон — начать с создания именованной таблицы Excel. Затем определите диапазон на основе одного или нескольких столбцов в этой таблице.
В этом примере на листе есть список частей, а также будут созданы именованная таблица и динамические именованные диапазоны. Позже, если вы добавите в таблицу новые элементы, именованный диапазон автоматически расширится.
Сначала создайте таблицу:
- Выберите ячейку в списке деталей.
- На вкладке "Вставка" ленты нажмите "Таблица".
- Убедитесь, что выбран правильный диапазон, и установите флажок "Моя таблица имеет заголовки".
- Нажмите "ОК", чтобы создать таблицу.
Далее создайте динамический список идентификаторов деталей:
- Выберите ячейки A2:A9, которые содержат идентификаторы частей (но не заголовок)
- Нажмите на панели формул и введите имя диапазона, состоящее из одного слова: PartIDList
- Нажмите клавишу Enter, чтобы завершить ввод имени.
Чтобы увидеть определение имени, выполните следующие действия:
- Перейдите на вкладку "Формулы" ленты и выберите "Диспетчер имен".
- В списке есть два именованных элемента:
- таблица Parts с именем по умолчанию Table1 (или именем, которое вы дали таблице)
- PartIDList, основанный на поле PartID в таблице 1.
Текст динамического диапазона
Поскольку именованный диапазон PartIDList основан на именованной таблице, размер списка автоматически изменится, если вы добавите или удалите идентификаторы частей в списке.
- Добавить новый элемент в список идентификаторов деталей.
- В поле «Имя» выберите имя PartIDList.
- Именованный диапазон выбран и включает новый идентификатор детали. ТОП
Динамический именованный диапазон — формула
Когда вы создаете именованный диапазон в Excel, он не включает новые элементы автоматически. Если вы планируете добавлять новые элементы в список, вы можете использовать динамическую формулу для определения именованного диапазона Excel. Затем, когда в список добавляются новые элементы, именованный диапазон будет автоматически расширяться, чтобы включить их.
Письменные инструкции находятся под видео.
Динамический именованный диапазон на основе формулы
Если вы не хотите использовать именованную таблицу, вы можете использовать динамическую формулу для определения именованного диапазона. По мере добавления новых элементов диапазон будет автоматически расширяться.
Примечание. Динамические именованные диапазоны не отображаются в раскрывающемся списке Поле имени. Однако вы можете ввести имена в поле имени, чтобы выбрать этот диапазон на листе.
- На ленте откройте вкладку "Формулы".
- Нажмите "Определить имя".
- Введите имя диапазона, например Список имен
- Оставьте для параметра "Область" значение "Книга".
- Справочная ячейка: Sheet1!$A$1
- Строки для смещения: 0
- Колонки для смещения: 0
- Количество строк: COUNTA(Лист1!$A:$A)
- Количество столбцов: 1
- Примечание: для динамического количества столбцов замените 1 на:
COUNTA(Sheet1!$1:$1)
Получить образец файла
Чтобы следовать инструкциям на этой странице, загрузите образец файла имен Excel. Заархивированный файл имеет формат xlsx и не содержит макросов. ТОП
Если вы работаете с электронными таблицами 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.
Ссылки на ячейки могут сбивать с толку, особенно при работе с формулами. Однако имена ячеек и диапазонов могут помочь. Вы можете задать имя для ячейки, диапазона ячеек, несмежных ячеек, формулы, константы или таблицы.
Например, вы можете назвать диапазон ячеек B16:H16 "Общий объем продаж". Затем вместо суммирования продаж по формуле =СУММ(B16:H16) вы можете использовать определенное имя для создания более удобочитаемой формулы =СУММ(ВсегоПродаж).
Создайте имя
- Выберите ячейки, которым вы хотите присвоить имя.
- Нажмите поле "Имя" на панели формул.
- Введите имя ячейки или диапазона.
Имя ячейки или диапазона не может содержать пробелы. Вместо этого используйте точку или подчеркивание.
Теперь при создании формулы вместо этого используйте имя для ссылки на ячейку(и).
Использование имени ячейки или диапазона в формуле
- Нажмите на ячейку, в которую хотите добавить формулу.
- Введите =, а затем формулу, которую вы хотите использовать.
- Если вы хотите использовать имя, просто введите его.
- Когда закончите, нажмите «Ввод».
Вы также можете нажать вкладку "Формулы" на ленте, нажать кнопку "Использовать в формуле" и выбрать имя из списка.
Перейти к имени
Имена ячеек и диапазонов также упрощают навигацию по книге. Вы можете выбрать имя диапазона, чтобы перейти к нему в книге.
-
Щелкните стрелку списка в поле Имя.
Появится список всех именованных диапазонов в книге.
Excel перенаправит вас к именованной ячейке или диапазону ячеек.
Изменить и удалить имена ячеек или диапазонов
Если вам нужно изменить или удалить именованный диапазон, используйте Диспетчер имен.
- Перейдите на вкладку "Формулы".
- Нажмите кнопку "Диспетчер имен".
- Используйте "Правка", чтобы изменить имя или обновить ячейку или диапазон, к которому относится имя.
- Используйте Удалить, если вам больше не нужна именованная ячейка или диапазон.
БЕСПЛАТНЫЙ краткий справочник
Бесплатно для распространения с нашей благодарностью; мы надеемся, что вы рассмотрите наше платное обучение.
Если бы вы спросили специалистов по моделированию о достоинствах использования названий диапазонов, вы бы обнаружили, что мнения сильно разделились. В электронных таблицах при правильном и экономном использовании имена диапазонов могут иметь большое значение, поскольку они могут облегчить чтение формул. В макросах (здесь не обсуждается) они жизненно важны. С другой стороны, чрезмерное использование может привести к путанице у конечных пользователей.
Имена диапазонов — это имена, используемые для ссылки на ячейки, результаты формул или значения.Они часто используются, чтобы избежать появления жестко закодированных значений в формулах и сделать формулы более понятными в целом. Они хранятся в так называемом диспетчере имен в Excel.
Пользователи могут создавать эти имена с помощью раскрывающихся меню в поле «Имя» (обведено на скриншоте выше) и/или просто использовать сочетание клавиш Ctrl+F3 во всех версиях Excel, а затем нажать «Создать». в диалоговом окне "Диспетчер имен", как показано на снимке экрана ниже.
Нажатие кнопки «Создать» (показано на снимке экрана выше) приводит к появлению следующего диалогового окна:Обратите внимание на второй раздел (Область действия). Все имена имеют область действия либо для определенного рабочего листа (также называемого локальным уровнем рабочего листа), либо для всей рабочей книги (также называемого глобальным уровнем рабочей книги). Область действия имени — это место, в котором имя распознается без уточнения.
Например, если вы определили имя диапазона как «Прибыль» с областью действия «Лист1», а не «Рабочая книга», то оно будет распознаваться как «Прибыль» только на Листе 1 (т. е. без уточнения).
Чтобы использовать это локальное имя на другом рабочем листе, необходимо указать перед ним локализованное имя рабочего листа:
=Лист1!Прибыль
Если вы определили имя, например "Денежный поток", и его областью действия является рабочая книга, это имя распознается для всех рабочих листов в этой рабочей книге (но не для любой другой рабочей книги). Если бы областью действия был рабочий лист (скажем, Лист1), то имя диапазона вместо этого было бы «Лист1!Денежный поток». Таким образом, область рабочей книги делает имена диапазонов более понятными и позволяет избежать путаницы.
Имя всегда должно быть уникальным в пределах своей области. Excel не позволяет вам определить имя, которое не является уникальным в пределах его области. Однако вы можете использовать одно и то же имя с разными областями. Например, вы можете определить имя, такое как «Прибыль», которое ограничено Листом 1, Листом 2 и Листом 3 в одной и той же книге. Хотя все имена одинаковы, каждое имя уникально в пределах своей области. Это можно сделать, чтобы гарантировать, что формула, использующая, например, имя «Валовая прибыль», всегда ссылается на одни и те же ячейки на локальном уровне листа.
Вы даже можете определить одно и то же имя, например "Прибыль", для глобального уровня рабочей книги, но опять же, эта область уникальна. В этом случае может возникнуть конфликт имен. Чтобы разрешить этот конфликт, Excel использует имя, определенное для рабочего листа по умолчанию. Локальный уровень рабочего листа имеет приоритет над глобальным уровнем рабочей книги. Это можно обойти, добавив к имени префикс, например, переименовав его в «WorkbookFile_Profit».
Можно переопределить локальный уровень рабочего листа для всех рабочих листов в рабочей книге, кроме первого рабочего листа. Это всегда будет использовать локальное имя, если есть конфликт имен, и его нельзя переопределить.
Настоятельно рекомендуется всегда пытаться создавать имена диапазонов только на уровне области действия книги. Кроме того, по возможности избегайте использования имен диапазонов в (разделах) листов, которые будут скопированы на другие листы или книги. Вы запутаете Excel, конечного пользователя и, самое главное, себя!
Будьте осторожны с именами
Строка имени должна начинаться с буквы или символа подчеркивания. Остальные символы в имени могут быть буквами, цифрами, точками и символами подчеркивания. Пробелы не допускаются, но два слова можно соединить символом подчеркивания (_) или точкой (.). Например, для ввода имени «Денежный поток» следует ввести «Денежный_поток» или «Денежный поток».
Нельзя использовать имя, которое иначе можно спутать со ссылкой на ячейку; например, «День1», так как это уже ссылка на ячейку (многие пробовали!).
Похоже, что количество имен, которые вы можете определить, не ограничено, но имя может содержать не более 255 символов. Имена могут содержать прописные и строчные буквы, но Excel не различает прописные и строчные буквы в именах. Например, если вы создали глобальное имя «Прибыль», а затем создали другое глобальное имя с именем «ПРОФИТ» в той же книге, второе имя будет отклонено, поскольку имена должны быть уникальными, независимо от заглавных букв.
Это не проблема синтаксиса, но я настоятельно рекомендую подумать о добавлении префиксов к именам диапазонов.Постоянные читатели заметят, что имена диапазонов моих списков всегда начинаются с «LU_», где «LU» означает «Поиск». Точно так же я использую «BC_» для «Базовой ячейки» при работе с функцией OFFSET.
Используя эти префиксы, я понимаю назначение имени диапазона, а имена с общим назначением группируются в список. Это не означает, что все имена диапазонов должны содержать префикс. Например, «Tax_Rate» имеет смысл сам по себе, а добавление префикса только отвлечет внимание от данного имени и может запутать конечного пользователя.
Быстрое создание имен диапазонов
Существует отличный способ создания имен диапазонов с использованием существующих имен. Рассмотрим следующий список:
Представьте, что вы выделили ячейки N12:N18 в приведенном выше примере, а затем использовали сочетание клавиш Ctrl+Shift+F3 (в качестве альтернативы выберите «Создать из выделенного» в группе «Определенные имена» на вкладке «Формулы» на ленте). :
Установив флажок в первом поле (верхняя строка), при нажатии кнопки OK диапазон N13:N18 (не N12:N18) будет называться «Phonetic_Alphabet» (т. е. подчеркивание будет добавлено автоматически). Диапазоны между строками можно называть в секундах аналогичным образом, используя левый столбец.Причина, по которой в этом диалоговом окне используются флажки (а не переключатели), заключается в том, чтобы позволить пользователям выбирать более одного за раз. Например:
Выделив N31:R34 и снова используя сочетание клавиш Ctrl+Shift+F3, должно появиться диалоговое окно «Создать имена из выделенного», как показано выше, с отмеченными верхней строкой и левым столбцом. Это означает, что O32:O34 будет называться «Jan», O33:R33 будет называться «COGS» и так далее. Вручную это займет значительно больше времени.Этот пример также показывает, почему пробелы являются недопустимыми символами в именах диапазонов (и их также нельзя добавлять в формулы). Пробел — это оператор пересечения в Excel. Если бы вы вводили следующую формулу:
=Валовая_маржа, февраль
Excel вернет значение в ячейке P34 (пересечение двух диапазонов выше), т. е. 4183 доллара США. Это может быть мощный, но быстрый и простой аналитический инструмент для ключевых выходных данных — быстрее, чем ВПР или ПОИСКПОЗ ИНДЕКСА.
Быстрое использование имен диапазонов
Одна из причин, по которой мне нравится использовать сочетание клавиш Ctrl+F3, заключается в том, что оно является частью семейства сочетаний клавиш F3 Names. Мы только что увидели, насколько полезными могут быть сочетания клавиш Ctrl+Shift+F3, а также сама по себе клавиша F3.
Возможно, из-за того, что в Excel 2007 и более поздних версиях Excel теперь будет предлагать при вводе формул, в прошлом F3 была очень полезна в качестве ярлыка «Вставить имя». Например, когда вы вводите формулу, вы можете обратиться к имени диапазона, просто нажав F3, чтобы открыть диалоговое окно «Вставить имя», как показано на снимке экрана ниже.
Если вы выберете одно из имен и нажмете OK, будет вставлено имя диапазона.Однако присмотритесь к диалоговому окну. Кнопка «Вставить список» в левом нижнем углу, если она нажата, вставит список и его определения в предварительно выбранный диапазон ячеек (т. е. до использования сочетания клавиш F3) на листе Excel, что может быть неоценимо для аудита модели. цели.
Иногда формулы пишутся до создания имени диапазона. В некоторых случаях можно применить эти имена задним числом, используя команду «Применить имена» в группе «Определить имена» на вкладке «Формулы» (см. снимок экрана ниже).
Обратите внимание, что сочетание клавиш Alt+I+N+A будет работать во всех версиях Excel. При выборе требуемых имен диапазонов в появившемся диалоговом окне формулы на активных листах будут обновлены соответствующим образом.
Удаление имен диапазоновЕсли бы мне платили всего 1 доллар США за каждый раз, когда меня спрашивают, как удалить имена диапазонов, я бы, наверное, уже ушел на пенсию. В основном это было связано с нелогичным меню в Excel 2003 и более ранних версиях:
В появившемся диалоговом окне выберите имя диапазона (к сожалению, за раз можно выбрать только одно) и нажмите "Удалить", как показано на снимке экрана ниже.
Excel 2007 и более поздние версии значительно упрощают эту задачу. В этом случае пользователи с большей вероятностью перейдут к Диспетчеру имен, а не к запутанному раскрывающемуся меню «Вставка»:
Другое заметное улучшение заключается в том, что несколько имен могут быть удалены одновременно с помощью клавиш Ctrl или Shift для выбора нескольких вариантов, прежде чем нажимать кнопку "Удалить".Относительная ссылка
По умолчанию на имена диапазонов указываются абсолютные ссылки (т. е. они содержат знак $, чтобы ссылки оставались статичными). Однако представьте себе сценарий, в котором вы моделируете доход и хотите увеличить значение за предыдущий период за счет инфляции (уже задано имя диапазона, скажем, ячейка C3 на листе 1). Просто нажмите на любую ячейку (например, я буду использовать D17 произвольно), затем определите имя нового диапазона следующим образом:
Обратите внимание на запись Относится к:. Ячейка C17 (ячейка слева от D17) выбрана без знаков доллара. Это относительная ссылка. Как только мы нажмем «ОК», имя диапазона «Prior_Period» будет определено как ячейка непосредственно слева от активной ячейки. Затем мы можем легко увеличить значения, скопировав формулу=Prior_Period*(1+Инфляция)
Другие типы имен
Как я уже говорил, имена могут также относиться к функциям, датам и константам — последние могут быть полезны (например, «Months_in_Year» определяется как 12), чтобы избежать вставки жесткого кода в формулу.
Слово мудрым
В этой статье обсуждается только верхушка айсберга имен. Эксперименты могут принести большие дивиденды. Однако цель состоит в том, чтобы не переборщить, так как преобладание имен в книге может фактически затруднить использование формул и, следовательно, вашей модели.
Кроме того, будьте осторожны, называя диапазоны, которые затем удаляются. Имена диапазонов не будут удалены (даже если они больше не будут отображаться в поле «Имя»). Их нужно будет удалить, как описано выше, чтобы избежать потенциальных ошибок в формулах и т. д. Важно помнить об этом, так как это часто было причиной повреждения файла или сбоя в работе Excel.
Читайте также: