что такое имя в экселе
Использование Диспетчера имен в Excel
Диалоговое окно Диспетчер имен используется для работы со всеми определенными именами и именами таблиц в книге. Например, может потребоваться найти имена с ошибками, подтвердить значение и ссылку на имя, просмотреть или изменить описательные комментарии или определить область действия. Вы также можете сортировать и фильтровать список имен, а также легко добавлять, изменять и удалять имена из одного расположения.
Чтобы открыть диалоговое окно Диспетчер имен, на вкладке Формулы в группе Определенные имена нажмите кнопку Диспетчер имен.
В диалоговом окне Диспетчер имен отображаются следующие сведения о каждом имени в списке.
Одно из следующих значений:
определенное имя, которое обозначается значком определенного имени; 
имя таблицы, которое обозначается значком имени таблицы. 
Примечание: Имя таблицы — это имя таблицы Excel, которая является набором данных по определенной теме, сохраненной в записях (строках) и полях (столбцах). Excel создает таблицу Excel «Таблица1», «Таблица2» и так далее при каждой вставке Excel таблицы. Вы можете изменить имя таблицы, чтобы сделать ее более осмысленной. Дополнительные сведения о Excel таблицах см. в этой Excel таблицах.
Текущее значение имени, такое как результаты формулы, строковая константа, диапазон ячеек, ошибка, массив значений или знаки-заполнители, если формулу не удается вычислить. Вот типичные примеры.
Имена в EXCEL
history 18 ноября 2012 г.
Имя можно присвоить диапазону ячеек, формуле, константе или таблице. Использование имени позволяет упростить составление формул, снизить количество опечаток и неправильных ссылок, использовать трюки, которые затруднительно сделать другим образом.
Ниже приведены примеры имен.
Объект именования
Пример
Формула без использования имени
Формула с использованием имени
имя ПродажиЗа1Квартал присвоено диапазону ячеек C20:C30
имя НДС присвоено константе 0,18
имя УровеньЗапасов присвоено формуле ВПР(A1;$B$1:$F$20;5;ЛОЖЬ)
имя МаксПродажи2006 присвоено таблице, которая создана через меню Вставка/ Таблицы/ Таблица
имя Диапазон1 присвоено диапазону чисел 1, 2, 3
Для создания имени сначала необходимо определим объект, которому будем его присваивать.
Присваивание имен диапазону ячеек
1.Создание имени диапазона через команду Создать из выделенного фрагмента :
Проверить правильность имени можно через инструмент Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен )
2.Создание имени диапазона через команду Присвоить имя :
3.Создание имени в поле Имя:
4.Создание имени через контекстное меню:
ВНИМАНИЕ! По умолчанию при создании новых имен используются абсолютные ссылки на ячейки (абсолютная ссылка на ячейку имеет формат $A$1 ).
5. Быстрое создание нескольких имен
Пусть имеется таблица, в каждой строке которой содержится определенный массив значений.
Чтобы создать все имена сразу, нужно:
Получим в Диспетчере имен ( Формулы/ Определенные имена/ Диспетчер имен ) сразу все 9 имен!
Присваивание имен формулам и константам
Присваивать имена формулам и константам имеет смысл, если формула достаточно сложная или часто употребляется. Например, при использовании сложных констант, таких как 2*Ln(ПИ), лучше присвоить имя выражению =2*LN(КОРЕНЬ(ПИ())) Присвоить имя формуле или константе можно, например, через команду Присвоить имя (через меню Формулы/ Определенные имена/ Присвоить имя ):
Присваивание имен таблицам
В. СИНТАКСИЧЕСКИЕ ПРАВИЛА ДЛЯ ИМЕН
Ниже приведен список синтаксических правил, которым следует руководствоваться при создании и изменении имен.
В качестве имен не следует использовать следующие специальные имена:
Если Вы в качестве имени использовали, например, слово Критерии с областью действия Лист1, то оно будет удалено при задании критериев для Расширенного фильтра на этом листе (без оповещения).
С. ИСПОЛЬЗОВАНИЕ ИМЕН
Уже созданное имя можно ввести в ячейку (в формулу) следующим образом.
D. ПОИСК И ПРОВЕРКА ИМЕН ОПРЕДЕЛЕННЫХ В КНИГЕ
Диспетчер имен: Все имена можно видеть через Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен ), где доступна сортировка имен, отображение комментария и значения.
Е. ОБЛАСТЬ ДЕЙСТВИЯ ИМЕНИ
Все имена имеют область действия: это либо конкретный лист, либо вся книга. Область действия имени задается в диалоге Создание имени ( Формулы/ Определенные имена/ Присвоить имя ).
Блог Мачула Владимира
В предыдущей статье были перечислены виды имен, которые можно использовать в редакторе Excel и правила, которыми при их создании следует руководствоваться. Теперь рассмотрим самое интересное и полезное – варианты и инструменты создания имен, а также, как их применять.
Присвоение имени диапазону ячеек
Допустим, есть столбец таблицы с информацией о доходах организации за полугодие. Используем это и диапазону, который он занимает: D5:D60 присвоим имя Доходзаполугодие. Вот несколько вариантов создания имени для заданного диапазона:
1. При помощи команды Создать из выделенного фрагмента:
Правильно ли Вы создали имя и какому диапазону оно присвоено, можно проверить через окно Диспетчер имен, которое вызывается одноименной кнопкой из этой же области.
2. Создание имени через поле Имя:
Тут все очень просто. Надеюсь, вы помните, что поле Имя, это то поле, в котором отображается адрес ячейки диапазона, на которой установлен прямоугольник курсора и расположено оно слева от строки формул. Итак, делаем следующее:
3. Создание имени через команду Присвоить имя:
Для создания имени через кнопку Присвоить имя необходимо:
Вызвать окно Создание имени можно и через контекстное меню, которое вызывается для выделенного диапазона, которому нужно присвоить имя.
Присваивание имен формулам и константам
Присваивать имена константам или формулам имеет смысл, если они часто употребляются. Например, есть коэффициент трения сталь-сталь, имеющий значение 0,15. Использовать просто цифру не практично, можно скоро забыть, что это за 0,15 там встречается. Поэтому будем использовать более информационное имя Коэф_тр_стали.
Как создать имя для константы?
Делаем следующие шаги:
Для формулы имя создается точно таким же образом, только в поле Диапазон указывается адрес ячейки, в которой располагается формула, или же непосредственно сама формула.
Как удобнее подставлять имя в формулу Excel?
Допустим, имя Коэф_тр_стали нужно вставить в некое выражение =Коэф_тр_стали*5.
Тут порядок действий следующий:
Присваивание имен таблицам
Отдельно стоит поговорить об именах таблиц. Начиная с версии MS Office 2007 таблицы правильно создавать через команду Таблица, расположенную в области Таблицы вкладки Вставка. При этом Excel автоматически присваивает таблице имя: Таблица1 или Таблица2 и т.д., но его можно изменить через Конструктор таблиц, чтобы сделать более выразительными.
Пока существует таблица будет определено и ее имя, которое невозможно удалить. Рассмотрим пример нахождение суммы для столбца таблицы через ее имя. Для этого построим небольшую таблицу, присвоив ей имя Товар, которая содержит столбец Стоимость.
Немного в стороне от таблицы введем формулу =СУММ(Товар[Стоимость]). По мере ввода формулы редактор Excel даже предложит выбрать среди других имен формул и имя таблицы, и имя столбца. В итоге мы получим сумму по столбцу Стоимость.
Ссылки вида Имя_Таблицы[Имя_столбца] называются Структурированными ссылками.
Что очень важно, именуются таблицы не только для использования в формулах. Присваивая имя таблице, мы получаем то самое имя, по которому к этой таблице, а значит и к ее данным, можно обратиться извне. Точно так же, как можно обратиться к базе данным! Это позволяет строить запросы на выборку информации из этой таблицы, анализировать ее и обрабатывать, создавая новые таблицы с новой информацией. Это довольно большая тема, поэтому она будет рассмотрена в дальнейших статьях.
Диспетчер имен в Excel – инструменты и возможности
Требования к именам ячеек
Примечание: Если для ячейки (диапазона ячеек) задано какое-то имя, именно оно будет использоваться в качестве ссылки, например, в формулах.
Допустим, ячейке B2 присвоено имя “Продажа_1”.
Если она будет участвовать в формуле, то вместо B2 мы пишем “Продажа_1”.
Нажав клавишу Enter убеждаемся в том, что формула, действительно, рабочая.
Теперь перейдем, непосредственно, к самим методам, пользуясь которыми можно задавать имена.
Строка имен
Пожалуй самый простой способ присвоить имя ячейке или диапазону – ввести требуемое значение в строке имен, которое находится слева от строки формул.
Примечание: при присвоении названия любым из способов ниже, оно также будет показываться в строке имен.
Использование контекстного меню
Использование контекстного меню в Эксель позволяет выполнить популярные команды и функции. Присвоить имя ячейке также можно через этот инструмент.
Что такое именованный диапазон ячеек в Excel?
Именованный диапазон – это ячека, либо диапазон ячеек, которому присвоено имя. Имя – краткое, осмысленное обозначение. Имена ячеек и диапазонов могут использоваться при создании формул вместо адресов ячеек, а также при выделении нужных диапазонов. Присвоить имя диапазону можно различными способами, самый простой из которых – выделить нужную ячейку или диапазон и в адресном окошке строки формул написать имя для именованного диапазона.
По умолчанию имена диапазонов ячеек автоматически считаются абсолютными ссылками.
Для имен действует ряд ограничений:
– имя может содержать до 255 символов;
– первым символом в имени должна быть буква, знак подчеркивания (_) либо обратная косая черта (), остальные символы имени могутбыть буквами, цифрами, точками и знаками подчеркивания;
– имена не могут быть такими же, как ссылки на ячейки;
– пробелы в именах не допускаются;
– строчные и прописные буквы не различаются.
Управление существующими именованными диапазонами (создание, просмотр и изменение) можно осуществлять при помощи диспетчера имен. В Excel 2007 диспетчер находится на вкладке “Формулы”, в группе кнопок “Определенные имена”.
Выделение диапазонов
Способ первый: для выделения небольшого диапазона ячеек, находящихся рядом, можно просто провести по ним курсор мыши в виде широкого белого креста при нажатой левой кнопке мыши. Первая ячейка диапазона при этом останется неподсвеченной и готовой к вводу информации.
Способ второй: для выделения большого диапазона ячеек необходимо щелкнуть по первой ячейке диапазона, после чего при нажатой клавише Shift, щелкнуть по последней ячейке диапазона. При этом можно использовать горячие клавиши для перехода в начало или конец строки, а также для перехода в начало или конец рабочего листа.
Способ третий: для выделения диапазона можно просто написать адрес этого диапазона в адресном окошке строки формул, а если диапазон именованный, то достаточно в адресном окошке написать его имя.
Сравнение диапазонов
Сравнение диапазонов – это одна из классических задач в Excel, которую рано или поздно приходится решать любому пользователю Excel. Задача по сравнению диапазонов может быть поставлена по разному. Когда-то нужно найти различия или совпадения в диапазонах при построчном их сравнении, а когда-то необходимо узнать есть ли что-то общее в сравниваемых диапазонах вообще. В зависимости от поставленной задачи различаются и методики её решения.
Например, для построчного сравнения часто используется логическая функция “ЕСЛИ” и какой-либо из операторов сравнения (также можно использовать и другие функции, например “СЧЕТЕСЛИ” из категории статистические для проверки вхождения элементов одного списка в другой).
Также для поиска отличий по столбцам или по строкам используется стандартное средство Excel, которое находится на вкладке “Главная”, в группе кнопок “Редактирование”, в меню кнопки “Найти и выделить”. Если в этом меню выбрать пункт “Перейти” и далее нажать кнопку “Выделить”, то в диалоговом окне “Выделение группы ячеек” можно выбрать одну из опций “Отличия по строкам” или “Отличия по столбцам”.
Для поиска повторяющихся или уникальных значений в двух диапазонах можно использовать условное форматирование.
Задача
Имеется таблица продаж по месяцам некоторых товаров (см. Файл примера ):
Для создания динамического диапазона:
ВНИМАНИЕ! При использовании функции СЧЕТЗ() необходимо убедиться в отсутствии пустых ячеек! Т.е. нужно заполнять перечень месяцев без пропусков.
Теперь создадим еще один динамический диапазон для суммирования продаж.
Для создания динамического диапазона :
Функция ПОИСКПОЗ() ищет в строке 5 (перечень месяцев) выбранный пользователем месяц (ячейка С1 с выпадающим списком) и возвращает соответствующий номер позиции в диапазоне поиска (названия месяцев должны быть уникальны, т.е. этот пример не годится для нескольких лет). На это число столбцов смещается левый верхний угол нашего динамического диапазона (от ячейки А6 ), высота диапазона не меняется и всегда равна 12 (при желании ее также можно сделать также динамической – зависящей от количества товаров в диапазоне).
И наконец, записав в ячейке С2 формулу = СУММ(Продажи_за_месяц) получим сумму продаж в выбранном месяце.
Или, например, в апреле.
Формула подсчитывает количество элементов в строке 5 (функция СЧЁТЗ() ) и определяет ссылку на последний элемент в строке (функция ИНДЕКС() ), тем самым возвращает ссылку на диапазон B5:E5 .
Визуальное отображение динамического диапазона
Функция СМЕЩ в Excel
Разберем более детально функции, которые мы вводили в поле диапазон при создании динамического имени.
Функция =СМЕЩ определяет наш диапазон в зависимости от количества заполненных ячеек в столбце B. 5 параметров функции =СМЕЩ(начальная ячейка; смещение размера диапазона по строкам; смещение по столбцам; размер диапазона в высоту; размер диапазона в ширину):
Последние 2 параметра функции являются необязательными. Если их не заполнять, то диапазон будет состоять из 1-ой ячейки. Например: =СМЕЩ(A1;0;0) – это просто ячейка A1, а параметр =СМЕЩ(A1;2;0) ссылается на A3.
Теперь разберем функцию: =СЧЕТ, которую мы указывали в 4-ом параметре функции: =СМЕЩ.
Что определяет функция СЧЕТ
Функция =СЧЕТ($B:$B) автоматически считает количество заполненных ячеек в столбце B.
Таким образом, мы с помощью функции =СЧЕТ() и =СМЕЩ() автоматизируем процесс формирования диапазона для имени «доход», что делает его динамическим. Теперь еще раз посмотрим на нашу формулу, которой мы присвоили имя «доход»: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)
Читать данную формулу следует так: первый параметры указывает на то, что наш автоматически изменяемый диапазон начинается в ячейке B2. Следующие два параметра имеют значения 0;0 – это значит, что динамический диапазон не смещается относительно начальной ячейки B2. А увеличивается только его размер по вертикали, о чем свидетельствует 4-тый параметр. В нем находится функция СЧЕТ и она возвращает число равно количеству заполненных ячеек в столбце B. Соответственно количество ячеек по вертикали в диапазоне будет равно числу, которое нам даст функция СЧЕТ. А за ширину диапазона у нас отвечает последний 5-тый параметр, где находиться число 1.
Благодаря функции СЧЕТ мы рационально загружаем в память только заполненные ячейки из столбца B, а не весь столбец целиком. Данный факт исключает возможные ошибки связанные с памятью при работе с данным документом.
Манипуляции с именованными областями
Именованный диапазон — это область ячеек, которой пользователем присвоено определенное название. При этом данное наименование расценивается Excel, как адрес указанной области. Оно может использоваться в составе формул и аргументов функций, а также в специализированных инструментах Excel, например, «Проверка вводимых значений».
Существуют обязательные требования к наименованию группы ячеек:
Наименование области ячеек можно увидеть при её выделении в поле имен, которое размещено слева от строки формул.
В случае, если наименование диапазону не присвоено, то в вышеуказанном поле при его выделении отображается адрес левой верхней ячейки массива.
Создание именованного диапазона
Прежде всего, узнаем, как создать именованный диапазон в Экселе.
Выше был назван самый быстрый вариант наделения наименованием массива, но он далеко не единственный. Эту процедуру можно произвести также через контекстное меню
Ещё один вариант выполнения указанной задачи предусматривает использование инструментов на ленте.
Последний вариант присвоения названия области ячеек, который мы рассмотрим, это использование Диспетчера имен.
Примеры диапазона
Диапазон представляет собой набор из двух и более ячеек.
Заполнение диапазона
Чтобы заполнить диапазон, следуйте инструкции ниже:
Эта техника протаскивания очень важна, вы будете часто использовать её в Excel. Вот еще один пример:
Excel автоматически заполняет диапазон, основываясь на шаблоне из первых двух значений. Классно, не правда ли? Вот еще один пример:
Перемещение диапазона
Чтобы переместить диапазон, выполните следующие действия:
Именованный диапазон с абсолютной адресацией
Пусть необходимо найти объем продаж товаров:
Иногда выгодно использовать не абсолютную, а относительную ссылку, об этом ниже.
Именованный диапазон с относительной адресацией
Теперь найдем сумму продаж товаров в четырех сезонах. Данные о продажах находятся на листе 4сезона (см. файл примера ) в диапазонах: B2:B10 , C 2: C 10 , D 2: D 10 , E2:E10 . Формулы поместим соответственно в ячейках B11 , C 11 , D 11 , E 11 .
