Использование Диспетчера имен в Excel
Диалоговое окно Диспетчер имен используется для работы со всеми определенными именами и именами таблиц в книге. Например, может потребоваться найти имена с ошибками, подтвердить значение и ссылку на имя, просмотреть или изменить описательные комментарии или определить область действия. Вы также можете сортировать и фильтровать список имен, а также легко добавлять, изменять и удалять имена из одного расположения.
Чтобы открыть диалоговое окно Диспетчер имен, на вкладке Формулы в группе Определенные имена нажмите кнопку Диспетчер имен.
В диалоговом окне Диспетчер имен отображаются следующие сведения о каждом имени в списке.
Одно из следующих значений:
определенное имя, которое обозначается значком определенного имени; 
имя таблицы, которое обозначается значком имени таблицы. 
Примечание: Имя таблицы — это имя таблицы Excel, которая является набором данных по определенной теме, сохраненной в записях (строках) и полях (столбцах). Excel создает таблицу Excel «Таблица1», «Таблица2» и так далее при каждой вставке Excel таблицы. Вы можете изменить имя таблицы, чтобы сделать ее более осмысленной. Дополнительные сведения о Excel таблицах см. в этой Excel таблицах.
Текущее значение имени, такое как результаты формулы, строковая константа, диапазон ячеек, ошибка, массив значений или знаки-заполнители, если формулу не удается вычислить. Вот типичные примеры.
Как посмотреть диапазоны в excel
Как найти и найти именованные диапазоны в Excel?
Возможно, мы раньше создавали много именованных диапазонов в книге. Иногда вам нужно найти и найти именованные диапазоны для применения формул или для других целей. В этой статье будут представлены несколько способов быстрого поиска, поиска и выделения именованных диапазонов в Excel.
Найдите и найдите именованный диапазон в поле «Имя» на панели формул
Найдите и найдите именованный диапазон в поле «Имя» на панели формул
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Если вам нужно найти и найти определенный именованный диапазон в активной книге, вы можете щелкнуть стрелку 
в Поле имени на панели формул и выберите определенный именованный диапазон из раскрывающегося списка.
И затем определенный именованный диапазон найден и расположен, как показано на скриншоте ниже:
Найдите и найдите именованный диапазон с помощью Kutools for Excel
Если у вас установлен Kutools for Excel, вы можете переключиться на Имя менеджера панель, щелкнув 
в Область переходов, а затем щелкните определенный именованный диапазон, чтобы найти и найти его. См. Снимок экрана ниже:
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
Внимание: По умолчанию панель навигации открывается слева от листов при первой установке Kutools for Excel. Если вы не можете его просмотреть, включите его, нажав Kutools > Навигация.
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Автоматически находите и выделяйте все именованные диапазоны с помощью Kutools for Excel
Оба вышеупомянутых метода могут находить и определять местонахождение только одного именованного диапазона за раз. Здесь я представлю Kutools для Excel Выделить имена утилита для быстрого поиска и выделения всех именованных диапазонов в активной книге.
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
Нажмите Предприятие > Дизайн рабочего листа для Дизайн и нажмите Выделить имена Кнопка на Дизайн таб. Смотрите скриншот:
Теперь все именованные диапазоны в активной книге выделяются сразу.
Внимание: Это метод условного форматирования. Если вы создадите новый именованный диапазон позже, этот новый именованный диапазон также будет выделен автоматически.
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Диапазон Excel
Имя ячейки
Начнем с простого – присвоим имя ячейке. Для этого просто выделяем ее (1) и в поле имени (2) вместо адреса ячейки указываем произвольное название, которое легко запомнить.
Длина имени ограничена 255 символами, что более чем достаточно. Также в имени не должно быть пробелов, поэтому если оно состоит из нескольких слов, то их можно разделять знаком подчеркивания.
Если теперь на других листах книги нам нужно будет вывести данное значение или использовать его в дальнейших расчетах, то не обязательно переключаться на первый лист и указывать ячейку вручную. Достаточно просто ввести имя ячейки и ее значение будет подставлено.
Ячейки, строки, столбцы
Давайте начнем с выбора ячеек, строк и столбцов.
Примеры диапазона
Диапазон представляет собой набор из двух и более ячеек.
Заполнение диапазона
Чтобы заполнить диапазон, следуйте инструкции ниже:
Именованный диапазон
Аналогичным образом можно задать имя и для диапазона ячеек, то есть выделим диапазон (1) и в поле имени укажем его название (2):
Далее это название можно использовать в формулах, например, при вычислении суммы:
Также создать именованный диапазон можно с помощью вкладки Формулы, выбрав инструмент Задать имя.
Появится диалоговое окно, в котором нужно указать имя диапазона, выбрать область, на которую имя будет распространяться (то есть на всю книгу целиком или на отдельные ее листы), при необходимости заполнить примечание, а далее выбрать соответствующий диапазон на листе.
Для работы с существующими диапазонами на вкладке Формулы есть Диспетчер имен.
С его помощью можно удалять, изменять или добавлять новые имена ячейкам или диапазонам.
При этом важно понимать, что если вы используете именованные диапазоны в формулах, то удаление имени такого диапазона приведет к ошибкам.
Задача
Имеется таблица продаж по месяцам некоторых товаров (см. Файл примера ):
Для создания динамического диапазона:
ВНИМАНИЕ! При использовании функции СЧЕТЗ() необходимо убедиться в отсутствии пустых ячеек! Т.е. нужно заполнять перечень месяцев без пропусков.
Теперь создадим еще один динамический диапазон для суммирования продаж.
Для создания динамического диапазона :
Функция ПОИСКПОЗ() ищет в строке 5 (перечень месяцев) выбранный пользователем месяц (ячейка С1 с выпадающим списком) и возвращает соответствующий номер позиции в диапазоне поиска (названия месяцев должны быть уникальны, т.е. этот пример не годится для нескольких лет). На это число столбцов смещается левый верхний угол нашего динамического диапазона (от ячейки А6 ), высота диапазона не меняется и всегда равна 12 (при желании ее также можно сделать также динамической – зависящей от количества товаров в диапазоне).
И наконец, записав в ячейке С2 формулу = СУММ(Продажи_за_месяц) получим сумму продаж в выбранном месяце.
Или, например, в апреле.
Формула подсчитывает количество элементов в строке 5 (функция СЧЁТЗ() ) и определяет ссылку на последний элемент в строке (функция ИНДЕКС() ), тем самым возвращает ссылку на диапазон B5:E5 .
Визуальное отображение динамического диапазона
Как посчитать количество ссылок на столбцы таблицы Excel
Пример 2. Определить количество столбцов в таблице и записать это значение в ячейку A16.
Используем формулу ОБЛАСТИ, поочередно выделяя каждый столбец ячейки в качестве параметра. Перед выбором последующего столбца нажимаем и удерживаем кнопку Ctrl. Если добавить символ «)» и нажать Enter, появится диалоговое окно с сообщением о том, что было введено слишком много аргументов. Добавим дополнительные открывающую и закрывающую скобки.
Определение принадлежности ячейки к диапазону таблицы
Пример 3. Определить, принадлежит ли ячейка заданному диапазону ячеек.
Рассматриваемая функция также позволяет определить, принадлежит ли ячейка выделенной области. Выполним следующие действия:
Данная ошибка означает, что ячейка не принадлежит выделенной области.
Если выделить несколько ячеек внутри диапазона, функция ОБЛАСТИ вернет количество выделенных ячеек:
Описанные особенности работы данной функции могут быть полезны при работе с большим количеством таблиц данных.
Перемещение и копирование ячеек и их содержимого
Смотрите также =ЕСЛИ(ЕЧИСЛО(A2);A2;B2) протягиваете копируете соответственно макрос “Фильтра” хамом станешь…Оно кому только в таблице.: Думаю возможно если столбец. Т.е. получается, ВСЕ”, затем произведите и нажатьВставить более сложная процедура,Нажмите иконку Вставить на указанных ниже действий.Вставить, можно выбрать временно отобразить данные, выделенный фрагмент наПримечание: вставляете как значения выводит таблицу с надо?
The_Prist копировать выделенный диапазон что выделяем для описанные выше действияCtrl+Space. Только имейте в чем хотелось бы. вкладке Главная илиЧтобы переместить ячейки, нажмите. параметры вставки, которые которые не нужно другой лист или Мы стараемся как можно
grablik заданными параметрами дат.Юрий М: В примере все по одной ячейке.
копирования не Range(“7:7″ с формой.(Пробел). Таким способом виду, что здесь На самом деле, нажмите Ctrl+V на кнопкуСочетание клавиш
следует применить к
будут выделены только существует несколько особенностей, это один из
Вырезанные ячейки переместятся на. клавиш CTRL+V.При копировании значения последовательно ячеек с данными, листа или выберите
Диспетчер имен в 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 .
