Как назвать диапазон в excel
Определение и использование имен в формулах
Использование имен позволяет значительно упростить понимание и изменение формул. Вы можете задать имя для диапазона ячеек, функции, константы или таблицы. Начав использовать имена в книге, вы сможете с легкостью обновлять, проверять имена и управлять ими.
Присвоение имени ячейке
Введите имя в поле Имя.
Нажмите клавишу ВВОД.
Присвоение имен из выделенного диапазона
Выберите диапазон, которому вы хотите присвоить имя, включая заголовки строк и столбцов.
На вкладке Формулы нажмите кнопку Создать из выделенного
В диалоговом окне Создание имен из выделенного диапазона укажите место, содержащее заголовки, установив флажок в строке выше в столбце слева, в строке ниже или в столбце справа.
Excel присвоит ячейкам имена на основе заголовков из указанного диапазона.
Использование имен в формулах
Выделите ячейку и введите формулу.
Поместите курсор туда, где вы хотите использовать в формуле имя.
Введите первую букву имени и выберите его из появившегося списка.
Или выберите Формулы > Использовать в формуле и выберите имя, которое хотите использовать.
Нажмите клавишу Ввод.
Управление именами в книге с помощью Диспетчера имен
На ленте выберите команды Формулы > Определенные имена > Диспетчер имен. Здесь можно создавать, редактировать, удалять и искать все имена, используемые в книге.
Присвоение имени ячейке
Введите имя в поле Имя.
Нажмите клавишу ВВОД.
Присвоение имен из выделенного диапазона
Выберите диапазон, которому вы хотите присвоить имя, включая заголовки строк и столбцов.
На вкладке Формулы нажмите кнопку Создать из выделенного
В диалоговом окне Создание имен из выделенного диапазона укажите место, содержащее заголовки, установив флажок в строке выше в столбце слева, в строке ниже или в столбце справа.
Excel присвоит ячейкам имена на основе заголовков из указанного диапазона.
Использование имен в формулах
Выделите ячейку и введите формулу.
Поместите курсор туда, где вы хотите использовать в формуле имя.
Введите первую букву имени и выберите его из появившегося списка.
Или выберите Формулы > Использовать в формуле и выберите имя, которое хотите использовать.
Нажмите клавишу Ввод.
Управление именами в книге с помощью Диспетчера имен
На ленте выберите команды Формулы > Определенные имена > Диспетчер имен. Здесь можно создавать, редактировать, удалять и искать все имена, используемые в книге.
В Excel в Интернете, вы можете использовать именные диапазоны, определенные в Excel для Windows или Mac. Выберите имя в поле «Имя» для перехода к расположению диапазона или использования именованного диапазона в формуле.
В настоящее время создание именоваемого диапазона в Excel в Интернете недоступно.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Работа с именованным диапазоном в Microsoft Excel
Одним из инструментов, который упрощает работу с формулами и позволяет оптимизировать работу с массивами данных, является присвоение этим массивам наименования. Таким образом, если вы хотите сослаться на диапазон однородных данных, то не нужно будет записывать сложную ссылку, а достаточно указать простое название, которым вы сами ранее обозначили определенный массив. Давайте выясним основные нюансы и преимущества работы с именованными диапазонами.
Манипуляции с именованными областями
Именованный диапазон — это область ячеек, которой пользователем присвоено определенное название. При этом данное наименование расценивается Excel, как адрес указанной области. Оно может использоваться в составе формул и аргументов функций, а также в специализированных инструментах Excel, например, «Проверка вводимых значений».
Существуют обязательные требования к наименованию группы ячеек:
Наименование области ячеек можно увидеть при её выделении в поле имен, которое размещено слева от строки формул.
В случае, если наименование диапазону не присвоено, то в вышеуказанном поле при его выделении отображается адрес левой верхней ячейки массива.
Создание именованного диапазона
Прежде всего, узнаем, как создать именованный диапазон в Экселе.
Выше был назван самый быстрый вариант наделения наименованием массива, но он далеко не единственный. Эту процедуру можно произвести также через контекстное меню
Ещё один вариант выполнения указанной задачи предусматривает использование инструментов на ленте.
Последний вариант присвоения названия области ячеек, который мы рассмотрим, это использование Диспетчера имен.
Операции с именованными диапазонами
Как уже говорилось выше, именованные массивы могут использоваться во время выполнения различных операций в Экселе: формулы, функции, специальные инструменты. Давайте на конкретном примере рассмотрим, как это происходит.
На одном листе у нас перечень моделей компьютерной техники. У нас стоит задача на втором листе в таблице сделать выпадающий список из данного перечня.
Именованный диапазон также удобно использовать в качестве аргументов различных функций. Давайте взглянем, как это применяется на практике на конкретном примере.
Итак, мы имеем таблицу, в которой помесячно расписана выручка пяти филиалов предприятия. Нам нужно узнать общую выручку по Филиалу 1, Филиалу 3 и Филиалу 5 за весь период, указанный в таблице.
Как нетрудно понять, оператор суммирует все аргументы группы «Число». В виде аргументов могут применяться, как непосредственно сами числовые значения, так и ссылки на ячейки или диапазоны, где они расположены. В случае применения массивов в качестве аргументов используется сумма значений, которая содержится в их элементах, подсчитанная в фоновом режиме. Можно сказать, что мы «перескакиваем», через действие. Именно для решения нашей задачи и будет использоваться суммирование диапазонов.
Всего оператор СУММ может насчитывать от одного до 255 аргументов. Но в нашем случае понадобится всего три аргумента, так как мы будет производить сложение трёх диапазонов: «Филиал_1», «Филиал_3» и «Филиал_5».
Итак, устанавливаем курсор в поле «Число1». Так как мы дали названия диапазонам, которые требуется сложить, то не нужно ни вписывать координаты в поле, ни выделять соответствующие области на листе. Достаточно просто указать название массива, который подлежит сложению: «Филиал_1». В поля «Число2» и «Число3» соответственно вносим запись «Филиал_3» и «Филиал_5». После того, как вышеуказанные манипуляции были сделаны, клацаем по «OK».
Как видим, присвоение названия группам ячеек в данном случае позволило облегчить задачу сложения числовых значений, расположенных в них, в сравнении с тем, если бы мы оперировали адресами, а не наименованиями.
Конечно, эти два примера, которые мы привели выше, показывают далеко не все преимущества и возможности применения именованных диапазонов при использовании их в составе функций, формул и других инструментов Excel. Вариантов использования массивов, которым было присвоено название, неисчислимое множество. Тем не менее, указанные примеры все-таки позволяют понять основные преимущества присвоения наименования областям листа в сравнении с использованием их адресов.
Управление именованными диапазонами
Управлять созданными именованными диапазонами проще всего через Диспетчер имен. При помощи данного инструмента можно присваивать имена массивам и ячейкам, изменять существующие уже именованные области и ликвидировать их. О том, как присвоить имя с помощью Диспетчера мы уже говорили выше, а теперь узнаем, как производить в нем другие манипуляции.
Для того, чтобы вернутся к полному перечню наименований, достаточно выбрать вариант «Очистить фильтр».
В поле «Имя» можно сменить наименование области. В поле «Примечание» можно добавить или отредактировать существующее примечание. В поле «Диапазон» можно поменять адрес именованного массива. Существует возможность сделать, как применив ручное введение требуемых координат, так и установив курсор в поле и выделив соответствующий массив ячеек на листе. Его адрес тут же отобразится в поле. Единственное поле, значения в котором невозможно отредактировать – «Область».
После того, как редактирование данных окончено, жмем на кнопку «OK».
Также в Диспетчере при необходимости можно произвести процедуру удаления именованного диапазона. При этом, естественно, будет удаляться не сама область на листе, а присвоенное ей название. Таким образом, после завершения процедуры к указанному массиву можно будет обращаться только через его координаты.
Это очень важно, так как если вы уже применяли удаляемое наименование в какой-то формуле, то после удаления названия данная формула станет ошибочной.
Применение именованного диапазона способно облегчить работу с формулами, функциями и другими инструментами Excel. Самими именованными элементами можно управлять (изменять и удалять) при помощи специального встроенного Диспетчера.
Помимо этой статьи, на сайте еще 12528 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Ссылка на именованные диапазоны
Диапазоны легче идентифицировать по имени, чем с помощью нотации A1. Чтобы присвоить имя выбранному диапазону, щелкните поле имени с левой стороны строки формул, введите имя и нажмите клавишу ВВОД.
Примечание. Существует два типа именованных диапазонов: именованный диапазон книги и именованный диапазон определенного листа.
Именованный диапазон книги
Именованный диапазон книги относится к определенному диапазону в любом месте книги (применяется глобально).
Как создать именованный диапазон книги:
Как указано выше, обычно он создается путем ввода имени в поле «Имя» с левой стороны строки формул. Обратите внимание, что имя не может содержать пробелов.
Именованный диапазон определенного листа
Именованный диапазон определенного листа относится к диапазону конкретного листа и не является глобальным для всех листов в книге. Сослаться на такой именованный диапазон с этого же листа можно просто с помощью имени, но из другого листа потребуется использовать имя листа с добавлением «!» и имени диапазона (пример: диапазон «Имя» «= Лист1!Имя»).
Преимущество заключается в возможности использования кода VBA для создания новых листов с одинаковыми именами для одних и тех же диапазонов на этих листах без возникновения ошибки, сообщающей, что имя уже используется.
Как создать именованный диапазон определенного листа:
Пример именованного диапазона определенного листа: выделенный диапазон A1:A10 для присвоения имени.
Выбранное имя диапазона — «Имя». В пределах одного листа ссылайтесь на именованный диапазон, просто введя в ячейку «=Имя». Из другого листа ссылайтесь на диапазон определенного листа, указав в ячейке имя листа: «= Лист1!Имя».
Ссылка на именованный диапазон
В следующем примере выполняется ссылка на диапазон с именем MyRange в книге с именем MyBook.xls.
В следующем примере выполняется ссылка на диапазон определенного листа с именем Sheet1!Sales в книге с именем Report.xls.
Чтобы выбрать именованный диапазон, используйте метод GoTo, который активирует книгу и лист, а затем выбирает диапазон.
В следующем примере показано, как можно написать эту же процедуру для активной книги.
В этом примере в качестве формулы для проверки данных используется именованный диапазон. В этом примере данные проверки должны быть на листе 2 в диапазоне A2:A100. Они используются для проверки данных, введенных на листе 1 в диапазоне D2:D10.
Циклический переход по ячейкам в именованном диапазоне
Об участнике
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Excel. Имена диапазонов
Возможно, вам приходилось работать с листами, в которых использовалась, формула типа: =СУММ(А5000:А5078). Вы гадали, что же находится в ячейках А5000:А5078!? Если в ячейках А5000:А5078 содержатся объемы продаж по регионам, не кажется ли вам формула =СУММ(ПродажиРегионы) более понятной? В данной главе описываются способы присвоения имен отдельным ячейкам и диапазонам ячеек, а также способы вставки имен диапазонов в формулы. [1]
Как создать именованный диапазон?
Существуют три способа создания именованных диапазонов:
Рис. 1.1. Создание имени диапазона путем выбора диапазона ячеек и ввода имени в поле Имя
Скачать заметку в формате Word или pdf, примеры в формате Excel
При нажатии в поле Имя на стрелку раскрывающегося списка появятся имена диапазонов, определенные в текущей книге (рис. 1.2). При выборе в поле Имя имени диапазона все ячейки, соответствующие этому диапазону, отмечаются автоматически. Это позволяет убедиться в правильности выбора ячейки или диапазона ячеек для указанного имени. В именах диапазонов регистр не учитывается. Например, если выбрать имя Финиш, будет отмечена ячейка Е8 (рис. 1.3).
Рис. 1.2. Список имен диапазонов
Рис. 1.3. При выборе имени диапазона отмечаются все ячейки, соответствующие этому диапазону
При нажатии клавиши открывается диалоговое окно Вставка имени, в котором отображаются имена всех диапазонов.
Присвоение имени означает, что вместо любой ссылки Старт в формуле будет автоматически подставлено значение из ячейки В3.
Рис. 1.4. Присвоение диапазону A1:B5 имени Данные и нахождение среднего значения именованного диапазона
Рис. 1.5. Присвоение имени несмежному диапазону ячеек
Создание имен с помощью инструмента Создать из выделенного. На листе «Рис. 1.6» Excel-файла с примерами содержатся продажи за март для каждого из 50 штатов США (рис. 1.6). Требуется присвоить каждой ячейке в диапазоне B2:B51 сокращенное название штата. Выделите диапазон A2:B51 и на вкладке ФОРМУЛЫ в группе Определенные имена выберите инструмент Создать из выделенного, и затем в открывшемся диалоговом окне установите флажок в столбце слева.
Рис. 1.6. Создание имен с помощью инструмента Создать из выделенного
Теперь имена в первом столбце выделенного диапазона связаны с ячейками во втором столбце выделенного диапазона. Таким образом, ячейке B6 присвоено имя диапазона СА, ячейка B7 имеет имя СО и т.д. Создавать имена таких диапазонов с помощью поля Имя было бы невероятно утомительно! Нажмите на стрелку раскрывающегося списка в поле Имя и убедитесь, что все имена диапазонов созданы.
Создание имен диапазонов с помощью инструмента Присвоить имя. Если на вкладке ФОРМУЛЫ в группе Определенные имена выбрать инструмент Диспетчер имен (и затем нажать кнопку Создать) или инструмент Присвоить имя, откроется диалоговое окно Создание имени (рис. 1.7).
Предположим, требуется присвоить имя область1 диапазону ячеек A2:B7. Введите область1 в поле Имя, переместите курсор в поле Диапазон, и выделите диапазон на листе или введите с клавиатуры =A2:B7. Нажмите кнопку OK для завершения присваивания.
Рис. 1.7. Диалоговое окно Создание имени
При нажатии на стрелку раскрывающегося списка в поле Область можно выбрать строку Книга или любой лист в книге, указав тем самым область действия имени (рис. 1.8). К любым именам диапазонов можно добавить примечания. Очень полезная опция, если не очевидно, что подразумевает выбранное имя диапазона.
Рис. 1.8. Выбор области действия имени
Диспетчер имен
В Microsoft Excel 2013 существует простой способ изменения или удаления имен диапазонов. Перейдите на вкладку ФОРМУЛЫ, выберите группу Определенные имена и откройте Диспетчер имен. Появится список имен всех диапазонов (рис. 1.9).
Рис. 1.9. Диспетчер имен
Редактирование формул в диалоговых окнах
Когда Excel отображает диалоговое окно (например, как на рис. 1.7 или 1.9), в котором можно записать ссылку на диапазон, поле, содержащее такую ссылку, всегда находится в режиме указания. Если активизировать поле Диапазон и воспользоваться стрелками для редактирования ссылки на диапазон, то вы обнаружите, что при этом вы именно указываете на диапазон, а не редактируете текст ссылки. Если на рисунке ниже вы поместите курсор в поле Диапазон, то попытка двинуть курсор влево с помощью стрелки даст неожиданный результат. Вместо движения курсора произошло изменение ссылки (обратите внимание: актуальный режим указан в левой части статусной панели):
Что делать? Нажмите F2. [2] Клавиша F2 позволяет переключаться между режимом указания (ввод) и режимом редактирования (правка). В режиме редактирования стрелки действуют именно так, как при редактировании формулы. На рисунке ниже попытка двинуть курсор влево увенчалась успехом:
Несколько конкретных примеров использования имен диапазонов
1. Необходимо вычислить общий объем продаж в штатах Аризона, Калифорния, Монтана, Нью-Йорк и Нью-Джерси.
Если вы помните наизусть сокращенные наименования штатов, то можно использовать формулу =AZ+CA+MT+NY+NJ (рис. 1.10)
Рис. 1.10. Использование имен вычисления объема продаж в отдельных штатах
2. Необходимо определить среднюю доходность акций, казначейских векселей и облигаций.
Рис. 1.11. Исторические данные по инвестициям
Рис. 1.12. Подсказка при вводе в формуле имени диапазона
3. Использование имен столбца и строки
При использовании в формуле имени столбца (в формате A:A, C:C и т.д.) весь столбец обрабатывается в Excel как именованный диапазон. Например, по формуле =СРЗНАЧ(A:A) вычисляется среднее значение всех чисел в столбце А. Использование имени диапазона для целого столбца очень эффективно при частом вводе новых данных в столбец. Например, если столбец A содержит данные о ежемесячных продажах продукта, то новые данные добавляются каждый месяц, и по такой формуле вычисляется актуальное среднее значение ежемесячных продаж. Однако будьте осторожны: если ввести формулу =СРЗНАЧ(А:А) в столбец А, то появится сообщение о циклической ссылке, т.к. значение в ячейке, содержащей формулу расчета среднего, будет зависеть от ячейки, содержащей среднее значение. Способ разрешения циклических ссылок см. Excel. Как найти циклическую ссылку. Аналогично, по формуле =СРЗНАЧ(1:1) рассчитывается среднее значение всех чисел в строке 1.
4. Имена с областью действия книга и лист
При создании имен с помощью поля Имя областью действия имен по умолчанию становится Книга. Однако, можно присвоить одно и тоже имя на разных листах, выбрав область действия Лист. Например, создайте новую книгу Excel, содержащую три листа, и введите числа 4, 5, 6 в ячейки E4:E6 на листе Лист1 и 3, 4, 5 в ячейки E4:E6 на листе Лист2. Затем откройте окно Диспетчер имен, присвойте имя jam ячейкам E4:E6 на листе Лист1 и определите область действия для этого имени как Лист1. Далее перейдите на Лист2, откройте окно Диспетчер имен, присвойте имя jam ячейкам E4:E6 и определите область действия для этого имени как Лист2. Диалоговое окно Диспетчер имен показано на рис. 1.13.
Рис. 1.13. Имена на уровне Листа
Что произойдет, если ввести формулу =СУММ(jam) на каждом из трех листов? На листе Лист1 по формуле =СУММ(jam) будут просуммированы значения ячеек E4:E6 листа Лист1. Так как в этих ячейках содержатся числа 4, 5 и 6, в сумме получится 15. На листе Лист2 по формуле =СУММ(jam) будут просуммированы значения ячеек E4:E6 листа Лист2, что в сумме даст 3 + 4 + 5 = 12. Однако на листе Лист3 вычисление по формуле =СУММ(jam) приведет к появлению сообщения об ошибке #имя?, поскольку на этом листе отсутствует диапазон с именем jam. Если где-либо на листе Лист3 ввести формулу =СУММ(лист2!jam), Excel распознает имя на уровне листа, которое представляет диапазон ячеек E4:E6 листа Лист2, и в результате получится 3 + 4 + 5 = 12. Таким образом, указав перед именем диапазона соответствующее имя листа с восклицательным знаком (!), можно обратиться к диапазону на листе, отличном от того листа, где диапазон был определен.
5. Как добиться отображения недавно созданных имен диапазонов в ранее созданных формулах?
Рассмотрим небольшую таблицу, содержащую формулы (рис. 1.14).
Рис. 1.14. Новые имена диапазонов в старых формулах
Ячейка F3 содержит цену продукта, а ячейка F4 — потребность в продукте =10000–300*F3. В ячейки F5 и F6 введена себестоимость единицы продукции и постоянные затраты, соответственно. Прибыль вычисляется в ячейке F7 по формуле =F4*(F3–F5)–F6. Выделите диапазон E3:F7, затем для присвоения ячейке F3 имени цена, ячейке F4 имени потребность, ячейке F5 имени себестоимость, ячейке F6 имени затраты и ячейке F7 имени прибыль используйте вкладку ФОРМУЛЫ, инструмент Создать из выделенного и флажок в столбце слева. Теперь имена созданных диапазонов необходимо отобразить в формулах ячеек F4 и F7. Для применения имен сначала выделите диапазон, для которого они создаются (в данном случае F3:F7). Затем на вкладке ФОРМУЛЫ в группе Определенные имена нажмите стрелку раскрывающегося списка Присвоить имя и выберите инструмент Применить имена. Выделите в окне имена, которые требуется применить, и нажмите кнопку OK. Обратите внимание, что в ячейке F4 теперь находится формула =10000-300*цена, а в ячейке F7 формула =потребность*(цена–себестоимость)–затраты, что и требовалось. [3]
6. Можно ли вывести на лист Excel список всех имен диапазонов (и представляемых ими ячеек)?
Откройте окно Вставка имени с помощью клавиши и нажмите кнопку Все имена (рис. 1.15). На листе, начиная с текущей ячейки, появится список имен диапазонов и соответствующих им ячеек.
Рис. 1.15. Вывод на лист Excel список всех имен диапазонов (и представляемых ими ячеек)
7. Использование формул для определения диапазона
Пример 1. Предполагаемый годовой доход вычисляется как кратный прошлогоднему доходу (рис. 1.16). Воспользуемся формулу =(1+прирост)*предыдущий_год (имя диапазона не может содержать пробел). Требуется вычислить доходы за 2012–2018 гг. с приростом 10% в год, начиная с базового уровня 300 млн. долларов в 2011 г.
Сначала в поле Имя присвойте ячейке B3 имя прирост. Теперь самое интересное! Переместите курсор в ячейку B7 и на вкладке ФОРМУЛЫ в группе Определенные имена выберите инструмент Присвоить имя для открытия диалогового окна Создание имени. Введите данные, как показано на рис. 1.16. Поскольку активной является ячейка B7, Excel всегда будет интерпретировать имя диапазона как указывающее на ячейку, находящуюся над текущей ячейкой. Это не будет работать, если в ссылке на ячейку B6 останется знак доллара, поскольку он не позволит изменить ссылку на строку и указать строку непосредственно над активной ячейкой (подробнее см. Относительные, абсолютные и смешанные ссылки на ячейки в Excel. Если в ячейку B7 ввести формулу =предыдущий*(1+прирост) и скопировать ее в диапазон B8:B13, каждая ячейка будет содержать требуемую формулу, по которой содержимое ячейки непосредственно над активной ячейкой будет умножаться на 1,1.
Рис. 1.16. Для любой ячейки это имя указывает на ячейку, находящуюся над активной ячейкой
Пример 2. Для каждого дня недели дана почасовая оплата и количество отработанных часов (рис. 1.17). Вычислим зарплату за каждый день по формуле почасовая*часы.
Выберите строку 12 (щелкните слева на 12) и в поле Имя (рядом со строкой формул) введите имя почасовая. Выберите строку 13 и введите в поле Имя – часы. Если теперь в ячейку F14 ввести формулу =почасовая*часы и скопировать эту формулу в диапазон G14:L14, то в каждом столбце автоматически появится результат перемножения значений почасовой оплаты и отработанных часов.
Рис. 1.17. Расчет зарплаты по дням недели
Если вам интересно, предлагаю несколько более сложных примеров использования имен диапазонов: Создание пользовательских функций при помощи имен, Автоматическое обновление сводной таблицы.
Некоторые замечания:
Задания для самостоятельной работы
Исходные данные находятся в файле Имена диапазонов. Задания.xlsx
[2] При написании этого раздела использованы идеи книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 156.
[3] У меня не получилось воспользоваться указанным методом, поэтому пришлось перенабрать формулы после присвоения имен.