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

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

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

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

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

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

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


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

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

    Источник: office-guru.ru

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

    Способ 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) знак равно и имя нашего диапазона, т.е. =Модели

    Источник: www.planetaexcel.ru

    Читайте также:  Впр и впр excel

    Иллюстрированный самоучитель по Microsoft Excel 2003

    Как присваивать имена ячейкам и диапазонам ячеек

    При ссылках на диапазоны ячеек до сих пор вы пользовались обозначениями наподобие В6:С7, под которым подразумевались ячейки В6, В7, С6 и С7. Это краткое обозначение может оказаться чрезвычайно удобным, особенно в тех случаях, когда необходимо сослаться на большие группы ячеек. Можно также присваивать имена отдельным ячейкам или их диапазонам. Использование имен может облегчить ввод и чтение ячеек.

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

    Щелкните на поле Имя – первом слева в строке формул. Введите имя диапазона и нажмите Enter. Имя может содержать до 255 символов и должно начинаться с буквы или знака подчеркивания (лучше использовать короткие и значимые имена). Имена могут состоять из букв, чисел, целых фраз и символов подчеркивания, но не должны содержать пробелов и других знаков пунктуации или специальных символов, за исключением обратной косой черты () и знака вопроса.

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

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

    Совет
    Если нужно удалить существующие имена, воспользуйтесь командой Имя из меню Вставка и командой Присвоить из появившегося вложенного меню. В результате появится диалоговое окно Присвоение имени, в котором можно удалять имена, выделяя их и щелкая затем на кнопке Удалить
    .

    Совет
    Чтобы изменить имя, в меню Вставка выберите команду Имя › Присвоить. Щелкните на имени, которое нужно изменить. Затем щелкните в строке Имя, исправьте имя и щелкните на кнопке Добавить. В списке имен появится еще одно имя для того же диапазона. Удалите исходное имя
    .

    Источник: samoychiteli.ru

    Имена в формулах

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

    Пример без имени

    Пример с именем

    Дополнительно об использовании имен

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

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

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

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

    Если вы определили имя, например Budget_FY08, а его область — Sheet1, это имя, если оно не определено, распознается только на листе Sheet1, но не на других листах.

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

    Если вы определили имя, например Sales_Dept_Goals, а его областью является книга, это имя будет распознается на всех листах книги, но не в других книгах.

    Имя должно быть уникальным в пределах своей области действия. Excel не позволяет определить имя, уже существующее в области. При этом можно использовать одинаковые имена в разных областях действия. Например, можно определить имя «ВаловойДоход» в областях действия «Лист1», «Лист2» и «Лист3» в одной и той же книге. Несмотря на одинаковость, каждое имя уникально в пределах своей области действия. Это можно сделать, чтобы гарантировать, что формула, использующая имя (Гросспрофит), всегда будет ссылаться на те же ячейки на локальном уровне листа.

    Можно даже определить это же имя «ВаловойДоход» на глобальном уровне книги, но область действия опять будет уникальной. Однако в этом случае может возникнуть конфликт имен. Чтобы устранить этот конфликт, по умолчанию в Excel используется имя, определенное на листе, поскольку локальный уровень листа имеет приоритет над глобальным уровнем книги. Если вы хотите переопределить приоритет и использовать имя книги, вы можете устранить неоднозначность имени, указав префикс имени книги. Пример.

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

    Имя определяется с помощью:

    Поле «определенные имена» в строке формул Этот способ лучше всего использовать для создания имени на уровне книги для выделенного диапазона.

    Задать имя из выделенного фрагмента Можно удобно создавать имена из существующих имен строк и столбцов с помощью выделения ячеек на листе.

    Диалоговое окно «Создание имени». Это лучше всего использовать, если нужна большая гибкость при создании имен, например, задание области определения на локальном уровне листа или создание примечания к имени.

    Примечание. по умолчанию в именах используются абсолютные ссылки на ячейки.

    Имя можно ввести указанными ниже способами.

    Клавиш Введите имя, например, в качестве аргумента формулы.

    Автозавершение формул . Используйте раскрывающийся список автозавершения формул, в котором автоматически выводятся допустимые имена.

    Выбором из команды «Использовать в формуле». Выберите определенное имя на вкладке Формулы в группе Определенные имена из списка Использовать в формуле.

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

    Ниже приведен список правил синтаксиса для создания и редактирования имен.

    Допустимые символы. Первым символом имени должна быть буква, знак подчеркивания (_) или обратная косая черта (). Остальные символы имени могут быть буквами, цифрами, точками и знаками подчеркивания.

    Совет. нельзя использовать прописные и строчные буквы “C”, “c”, “R” или “r” в качестве определенного имени, так как они используются в качестве краткости для выбора строки или столбца для выбранной ячейки при их вводе в имени или переходе к текстовому полю.

    Имена в виде ссылок на ячейки запрещены. Имена не могут быть такими же, как ссылки на ячейки, например, Z$100 или R1C1.

    Недопустимые пробелы Использовать в имени пробелы нельзя. Используйте символ подчеркивания (_) и точку (.). как разделители Word, например Sales_Tax или First. Quarter.

    Длина имени. Имя может содержать до 255 символов.

    Учет регистра . Имя может состоять из строчных и прописных букв. Excel не различает строчные и прописные буквы в именах. Например, если вы создали имя Sales, а затем еще одно название SALES в той же книге, приложение Excel предложит выбрать уникальное имя.

    Читайте также:  Как искать в excel

    Создание на листе имени для ячейки или диапазона ячеек

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

    Щелкните поле Имя у левого края строка формул.

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

    Нажмите клавишу ВВОД.

    Примечание: При изменении содержимого ячейки ей нельзя присвоить имя.

    Можно преобразовать в имена существующие заголовки строк и столбцов.

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

    На вкладке Формулы в группе Определенные имена нажмите кнопку Создать из выделенного фрагмента.

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

    На вкладке Формулы в группе Определенные имена выберите пункт Присвоить имя.

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

    Примечание. имена могут иметь длину до 255 знаков.

    Для задания области действия имени в поле со списком Область выберите элемент Книга или имя листа в книге.

    Также в поле Примечание можно ввести описание длиной до 255 знаков.

    В поле Диапазон выполните одно из указанных ниже действий.

    Чтобы указать ссылку на ячейки, введите ссылку на ячейки.

    Совет: текущий выделенный фрагмент по умолчанию введен. Чтобы ввести другие ссылки на ячейки в качестве аргумента, нажмите кнопку Свернуть диалоговое окно (которое временно сжимает диалоговое окно), выделите ячейки на листе и нажмите кнопку Развернуть диалоговое окно .

    Чтобы указать константу, введите = (знак равенства), а затем значение константы.

    Чтобы указать формулу, введите =, а затем формулу.

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

    Совет: Чтобы расширить или удлинить диалоговое окно Создание имени, щелкните и перетащите маркер захвата, расположенный внизу.

    Управление именами с помощью диалогового окна диспетчера имен

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

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

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

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

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

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

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

    “это моя строковая константа”

    Текущая ссылка для имени. Вот типичные примеры.

    Источник: support.microsoft.com

    Именованные диапазоны

    Для чего вообще нужны именованные диапазоны? Обращение к именованному диапазону гораздо удобнее, чем прописывание адреса в формулах и VBA:

    • Предположим, что в формуле мы ссылаемся на диапазон A1:C10 (возможно даже не один раз). Для примера возьмем простую функцию СУММ(суммирует значения указанных ячеек):
      =СУММ( A1:C10 ; F1:K10 )
      Затем нам стало необходимо суммировать другие данные(скажем вместо диапазона A1:C10 в диапазоне D2:F11 ). В случае с обычным указанием диапазона нам придется искать все свои формулы и менять там адрес диапазона на новый. Но если назначить своему диапазону A1:C10 имя(к примеру ДиапазонСумм ), то в формуле ничего менять не придется – достаточно будет просто изменить ссылку на ячейки в самом имени один раз. Я привел пример с одной формулой – а что, если таких формул 10? 30?
      Примерно такая же ситуация и с использованием в кодах: указав имя диапазона один раз не придется каждый раз при изменении и перемещении этого диапазона прописывать его заново в коде.
    • Именованный диапазон не просто так называется именованным. Если взять пример выше – то отображение в формуле названия ДиапазонСумм куда нагляднее, чем A1:C10 . В сложных формулах куда проще будет ориентироваться по именам, чем по адресам. Почему удобнее: если сменить стиль отображения ссылок (подробнее про стиль), то диапазон A1:C10 будет выглядеть как-то вроде этого: R1C1:R10C3 . А если назначить имя – то оно как было ДиапазонСумм , так им и останется.
    • При вводе формулы/функции в ячейку, можно не искать нужный диапазон, а начать вводить лишь первые буквы его имени и Excel предложит его ко вводу:

      Данный метод доступен лишь в версиях Excel 2007 и выше

    Как обратиться к именованному диапазону
    Обращение к именованному диапазону из VBA

    MsgBox Range(“ДиапазонСумм”).Address MsgBox [ДиапазонСумм].Address

    Обращение к именованному диапазону в формулах/функциях

    Если при указании диапазона в формуле выделить именованный диапазон, то его имя автоматически подставится в формулу вместо фактического адреса ячеек:

    Ограничения, накладываемые на создание имен

    • В качестве имени диапазона не могут быть использованы словосочетания, содержащие пробел. Вместо него лучше использовать нижнее подчеркивание _ или точку: Name_1, Name.1
    • Первым символом имени должна быть буква, знак подчеркивания (_) или обратная косая черта (). Остальные символы имени могут быть буквами, цифрами, точками и знаками подчеркивания
    • Нельзя в качестве имени использовать зарезервированные в Excel константы – R, C и RC(как прописные, так и строчные). Связано с тем, что данные буквы используются самим Excel для адресации ячеек при использовании стиля ссылок R1C1 (читать подробнее про стили ссылок)
    • Нельзя давать именам названия, совпадающие с адресацией ячеек: B$100, D2(для стиля ссылок А1) или R1C1, R7(для стиля R1C1). И хотя при включенном стиле ссылок R1C1 допускается дать имени название вроде A1 или D130 – это не рекомендуется делать, т.к. если впоследствии стиль отображения ссылок для книги будет изменен – то Excel не примет такие имена и предложит их изменить. И придется изменять названия всех подобных имен. Если очень хочется – можно просто добавить нижнее подчеркивание к имени: _A1
    • Длина имени не может превышать 255 символов

    Создание именованного диапазона
    Способ первый
    обычно при создании простого именованного диапазона я использую именно его. Выделяем ячейку или группу ячеек, имя которым хотим присвоить -щелкаем левой кнопкой мыши в окне адреса и вписываем имя, которое хотим присвоить. Жмем Enter:

    Способ второй
    Выделяем ячейку или группу ячеек. Жмем правую кнопку мыши для вызова контекстного меню ячеек. Выбираем пункт:

    • Excel 2007: Имя диапазона (Range Name)
    • Excel 2010: Присвоить имя (Define Name)


    либо:
    Жмем Ctrl + F3
    либо:

    • 2007-2016 Excel : вкладка Формулы (Formulas)Диспетчер имен (Name Manager)Создать (New) (либо на той же вкладке сразу – Присвоить имя (Define Name) )
    • 2003 Excel : ВставкаИмяПрисвоить

    Появляется окно создания имени

    Имя (Name) – указывается имя диапазона. Необходимо учитывать ограничения для имен, которые я описывал в начале статьи.
    Область (Scope) – указывается область действия создаваемого диапазона – Книга , либо Лист1 :

    • Лист1 (Sheet1) – созданный именованный диапазон будет доступен только из указанного листа. Это позволяет указать разные диапазоны для разных листов, но указав одно и тоже имя диапазона
    • Книга (Workbook) – созданный диапазон можно будет использовать из любого листа данной книги
    Читайте также:  Как в excel отсортировать данные в таблице excel

    Примечание (Comment) – здесь можно записать пометку о созданном диапазоне, например для каких целей планируется его использовать. Позже эту информацию можно будет увидеть из диспетчера имен ( Ctrl + F3 )
    Диапазон (Refers to) – при данном способе создания в этом поле автоматически проставляется адрес выделенного ранее диапазона. Его можно при необходимости тут же изменить.

    Изменение диапазона
    Чтобы изменить имя Именованного диапазона, либо ссылку на него необходимо всего лишь вызывать диспетчер имен( Ctrl + F3 ), выбрать нужное имя и нажать кнопку Изменить (Edit. ) .
    Изменить можно имя диапазона (Name) , ссылку (RefersTo) и Примечание (Comment) . Область действия (Scope) изменить нельзя, для этого придется удалить текущее имя и создать новое, с новой областью действия.

    Удаление диапазона
    Чтобы удалить Именованный диапазон необходимо вызывать диспетчер имен( Ctrl + F3 ), выбрать нужное имя и нажать кнопку Удалить (Delete. ) .

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

    Статья помогла? Поделись ссылкой с друзьями!

    Источник: www.excel-vba.ru

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

    Способ 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) знак равно и имя нашего диапазона, т.е. =Модели

    Источник: www.planetaexcel.ru