Диспетчер имен в excel 2003

Использование диспетчера имен в Excel

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

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

Чтобы открыть диалоговое окно Диспетчер имен, на вкладке Формулы в группе Определенные имена нажмите кнопку Диспетчер имен.

В диалоговом окне Диспетчер имен отображаются следующие сведения о каждом имени в списке.

Одно из следующих значений:

определенное имя, которое обозначается значком определенного имени;

имя таблицы, которое обозначается значком имени таблицы.

Примечание: Имя таблицы — это имя таблицы Excel, которая является коллекцией данных для определенной темы, хранящейся в записях (строках) и полях (столбцов). Excel создаст таблицу Excel по умолчанию для Table1, Table2 и т. д. каждый раз при вставке таблицы Excel. Вы можете изменить имя таблицы, чтобы сделать ее более информативной. Дополнительные сведения о таблицах Excel можно найти в разделе использование структурированных ссылок в таблицах Excel.

Текущее значение имени, такое как результаты формулы, строковая константа, диапазон ячеек, ошибка, массив значений или знаки-заполнители, если формулу не удается вычислить. Вот типичные примеры.

Диспетчер имен в Excel – инструменты и возможности

Со временем в Вашей рабочей книге Excel будет появляться все больше новых имен и возникнет острая необходимость всем этим как-то управлять. К счастью Excel предлагает очень удобный способ управления именами – Диспетчер имен.

Как получить доступ к диспетчеру имен

1. Чтобы открыть диалоговое окно Диспетчер имен, перейдите на вкладку Формулы и щелкните по кнопке с одноименным названием.
2. Откроется диалоговое окно Диспетчер имен:

Какие же возможности предоставляет нам это окно?

1. Полные данные о каждом имени, которое имеется в книге Excel. Если часть данных не помещается в рамки диалогового окна, то вы всегда можете изменить его размеры.
2. Возможность создать новое имя. Для этого необходимо щелкнуть по кнопке Создать.Откроется диалоговое окно Создание имени. Это тоже самое окно, что мы разбирали в уроке Как присвоить имя ячейке или диапазону в Excel.

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

Возможность удалить любое имя из списка. Для этого выделите нужное имя и нажмите кнопку Удалить.

В случае, когда Ваша книга Excel содержит большое количество имен, Вы всегда можете воспользоваться встроенным в диалоговое окно фильтром. Для этого щелкните по кнопке Фильтр и из раскрывающегося списка выберите то, что Вам нужно.

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

Расширение диспетчера имен Excel

Работа с диспетчером имен

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

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

Для каждого именованного диапазона выводится его название, значение, ссылка на диапазон, видимость (скрытый или нет) и примечание при наличие.

Использование фильтра

В диспетчере можно выбрать один или несколько именованных диапазонов нажав левой кнопкой мыши по ним. Для удобства выбора можно пользоваться фильтром. С помощью фильтра можно быстро выбрать все имена или снять с них выделение. Также можно выбрать все скрытые или видимые имена.

Изменение видимости

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

Команда работает как переключатель видимый/скрытый

Удаление стилей

С помощью диспетчера можно удалить все имена из книги, в том числе и скрытые.

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

Вывод списка имён (Names) книги Excel на новый лист

Если вы хотите посмотреть, присутствуют ли в книге Excel назначенные имена,
сделать это просто — достаточно вызвать диспетчер имён нажатием комбинации клавиш Ctrl + F3:

В диспетчере имён можно создать новые имена, просмотреть ранее созданные, и, при желании, изменить их.

Одно но: в диспетчере имён отображаются только видимые имена,

а в книге Excel могут присутствовать и скрытые.

Чтобы узнать количество имён в книге, а также посмотреть их значения,
мы воспользуемся макросом:

Для этого в прикреплённом файле нажмём зеленую кнопку,
и увидим вообщение с информацией о количестве имен в книге:

Если в диаоговом окне мы нажмём «Да», то макрос создаст новую книгу, и сформирует в ней таблицу со списком всех имен книги:

Если же вам требуется вывести список видимых имён на лист Excel, то можно воспользоваться макросом из одной строки:

Того же эффекта можно добиться, нажав кнопку «Все имена» в диалоговом окне, вызываемом из меню Вставка — Имя — Вставить. (в Excel 2003):

Связанные (зависимые) выпадающие списки

Способ 1. Функция ДВССЫЛ (INDIRECT)

Этот фокус основан на применении функции ДВССЫЛ (INDIRECT), которая умеет делать одну простую вещь — преобразовывать содержимое любой указанной ячейки в адрес диапазона, который понимает Excel. То есть, если в ячейке лежит текст «А1», то функция выдаст в результате ссылку на ячейку А1. Если в ячейке лежит слово «Маша», то функция выдаст ссылку на именованный диапазон с именем Маша и т.д. Такой, своего рода, «перевод стрелок» 😉

Возьмем, например, вот такой список моделей автомобилей Toyota, Ford и Nissan:

Выделим весь список моделей Тойоты (с ячейки А2 и вниз до конца списка) и дадим этому диапазону имя Toyota. В Excel 2003 и старше — это можно сделать в меню Вставка — Имя — Присвоить (Insert — Name — Define). В Excel 2007 и новее — на вкладке Формулы (Formulas) с помощью Диспетчера имен (Name Manager). Затем повторим то же самое со списками Форд и Ниссан, задав соответственно имена диапазонам Ford и Nissan.

При задании имен помните о том, что имена диапазонов в Excel не должны содержать пробелов, знаков препинания и начинаться обязательно с буквы. Поэтому если бы в одной из марок автомобилей присутствовал бы пробел (например Ssang Yong), то его пришлось бы заменить в ячейке и в имени диапазона на нижнее подчеркивание (т.е. Ssang_Yong).

Теперь создадим первый выпадающий список для выбора марки автомобиля. Выделите пустую ячейку и откройте меню Данные — Проверка (Data — Validation) или нажмите кнопку Проверка данных (Data Validation) на вкладке Данные (Data) если у вас Excel 2007 или новее. Затем из выпадающего списка Тип данных (Allow) выберите вариант Список (List) и в поле Источник (Source) выделите ячейки с названиями марок (желтые ячейки в нашем примере). После нажатия на ОК первый выпадающий список готов:

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

где F3 — адрес ячейки с первым выпадающим списком (замените на свой).

Все. После нажатия на ОК содержимое второго списка будет выбираться по имени диапазона, выбранного в первом списке.

Минусы такого способа:

• В качестве вторичных (зависимых) диапазонов не могут выступать динамические диапазоны задаваемые формулами типа СМЕЩ (OFFSET). Для первичного (независимого) списка их использовать можно, а вот вторичный список должен быть определен жестко, без формул. Однако, это ограничение можно обойти, создав отсортированный список соответствий марка-модель (см. Способ 2).
• Имена вторичных диапазонов должны совпадать с элементами первичного выпадающего списка. Т.е. если в нем есть текст с пробелами, то придется их заменять на подчеркивания с помощью функции ПОДСТАВИТЬ (SUBSTITUTE), т.е. формула будет выглядеть как =ДВССЫЛ(ПОДСТАВИТЬ(F3;» «;»_»))
• Надо руками создавать много именованных диапазонов (если у нас много марок автомобилей).

Способ 2. Список соответствий и функции СМЕЩ (OFFSET) и ПОИСКПОЗ (MATCH)

Этот способ требует наличия отсортированного списка соответствий марка-модель вот такого вида:

Для создания первичного выпадающего списка можно марок можно воспользоваться обычным способом, описанным выше, т.е.

• дать имя диапазону D1:D3 (например Марки) с помощью Диспетчера имен (Name Manager) с вкладки Формулы (Formulas) или в старых версиях Excel — через меню Вставка — Имя — Присвоить (Insert — Name — Define)
• выбрать на вкладке Данные (Data) команду Проверка данных (Data validation)
• выбрать из выпадающего списка вариант проверки Список (List) и указать в качестве Источника (Source)=Марки или просто выделить ячейки D1:D3 (если они на том же листе, где список).

А вот для зависимого списка моделей придется создать именованный диапазон с функцией СМЕЩ (OFFSET), который будет динамически ссылаться только на ячейки моделей определенной марки. Для этого:

• Нажмите Ctrl+F3 или воспользуйтесь кнопкой Диспетчер имен (Name manager) на вкладке Формулы (Formulas). В версиях до 2003 это была команда меню Вставка — Имя — Присвоить (Insert — Name — Define)
• Создайте новый именованный диапазон с любым именем (например Модели) и в поле Ссылка (Reference) в нижней части окна введите руками следующую формулу:

Ссылки должны быть абсолютными (со знаками $). После нажатия Enter к формуле будут автоматически добавлены имена листов — не пугайтесь 🙂

Функция СМЕЩ (OFFSET) умеет выдавать ссылку на диапазон нужного размера, сдвинутый относительно исходной ячейки на заданное количество строк и столбцов. В более понятном варианте синтаксис этой функции таков:

=СМЕЩ(начальная_ячейка; сдвиг_вниз; сдвиг_вправо; размер_диапазона_в_строках; размер_диапазона_в_столбцах)

• начальная ячейка — берем первую ячейку нашего списка, т.е. А1
• сдвиг_вниз — нам считает функция ПОИСКПОЗ (MATCH), которая, попросту говоря, выдает порядковый номер ячейки с выбранной маркой (G7) в заданном диапазоне (столбце А)
• сдвиг_вправо = 1, т.к. мы хотим сослаться на модели в соседнем столбце (В)
• размер_диапазона_в_строках — вычисляем с помощью функции СЧЕТЕСЛИ (COUNTIF), которая умеет подсчитать количество встретившихся в списке (столбце А) нужных нам значений — марок авто (G7)
• размер_диапазона_в_столбцах = 1, т.к. нам нужен один столбец с моделями

В итоге должно получиться что-то вроде этого:

Осталось добавить выпадающий список на основе созданной формулы к ячейке G8. Для этого:

• выделяем ячейку G8
• выбираем на вкладке Данные (Data) команду Проверка данных (Data validation) или в меню Данные — Проверка (Data — Validation)
• из выпадающего списка выбираем вариант проверки Список (List) и вводим в качестве Источника (Source) знак равно и имя нашего диапазона, т.е. =Модели