Как сделать зависимый выпадающий список в Excel

Как сделать зависимый выпадающий список в Excel?

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

Вот примеры таких задач:

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

Выглядеть это может примерно так:

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

Начнем с более простого и стандартного подхода.

1. Именованные диапазоны + функция ДВССЫЛ.

Это может показаться сложным, но на самом деле это очень просто, и является отличным примером того, как можно применить ДВССЫЛ.

Рассмотрим небольшой пример. У нас есть перечень автомобилей различных марок. Расположим их каждый в отдельном столбце. В первой ячейке каждого столбца запишем производителя — Toyota, Ford, Nissan. Необходимо, чтобы после того, как первоначально мы выберем, например, Toyota, далее мы видели бы только модели этой марки, и ничего более. То есть, нам нужен двухуровневый связанный список.

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

Итак, у нас получилось 3 именованных диапазона — «toyota», «ford», «nissan». Делать их статическими (фиксированными) или динамически (автоматически пополняемыми) — решайте сами. О том, как создать автоматически пополняемый список, смотрите ссылку в конце этой статьи.

Далее в ячейке F3 создаем выпадающий список первого уровня с моделями автомашин так, как это показано в нашем примере на рисунке. Источник данных — первые ячейки каждого столбца. Обратите внимание, что инструмент проверки вводимых значений, при помощи которого мы создаем его, прекрасно работает как с вертикальными (по строкам), так и с горизонтальными (по столбцам) данными.

И далее выбираем того производителя, который нас интересует. К примеру, «Ford».

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

В этом нам поможет функция ДВССЫЛ. Функция ДВССЫЛ (INDIRECT в английском варианте) преобразует текст в стандартную ссылку Excel.

Если мы запишем

то это будет равнозначно тому, что мы записали в ячейке формулу

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

«Фишка» функции ДВССЫЛ (или INDIRECT) в том, что она позволяет использовать текст точно так же, как обычную ссылку на ячейку . Это обеспечивает нам два ключевых преимущества:

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

В примере на этой странице мы объединяем последнюю идею с именованными диапазонами для создания многоуровневого выпадающего списка. ДВССЫЛ преобразует обычный текст в имя, которое затем превращается в нормальную ссылку и источник данных для него.

Итак, в этом примере мы берем текстовые значения из А1:С1, выбираем из них какое-то одно. К примеру, «Ford». Поскольку такое же название у нас имеет один из именованных диапазонов, то и применяем ДВССЫЛ, чтобы преобразовать текст «Ford» в ссылку =ford. И вот уже ее мы употребляем как источник для связанного выпадающего списка.

Итак, в качестве источника значений применяем формулу

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

В результате функция возвращает в нашу таблицу Excel ссылку

Регистр символов в данном случае значения не имеет — все автоматически преобразуется в нижний регистр. И именно это и будет источником данных.

Изменяя значения в F3, мы автоматически изменяем и ссылку-источник для списка в F6. В результате источник данных для зависимого выпадающего списка в F6 динамически меняется в зависимости от того, что было выбрано в F3. Если выбираем Ford, то видим только каталог машин этой марки. Аналогично, если выбираем Toyota либо Nissan.

Многоуровневые выпадающие списки могут быть и более двух раз «вложены» друг в друга. В нашем примере можно добавить еще один, третий уровень. Он может содержать наименования каких-то запасных частей для выбранной ранее модели. Так формируются сложные перечни, в которых каждый следующий уровень зависит от предыдущего.

А как быть с пробелами?

Может случиться так, что название вашей группы товаров или категории будет содержать пробелы. А именованные диапазоны не позволяют, чтобы в их названии встречался пробел. Принято заменять их символом нижнего подчеркивания «_». Как же нам быть в этом случае? Ведь в таблице названия товарных категорий с символом нижнего подчеркивания будут смотреться несколько непривычно. Например, «Косметические_товары». С непривычки можно и просто забыть ввести нужный символ. И тогда наши формулы работать не будут.

Выход довольно прост. Создавайте именованные перечни, заменяя в их названиях пробелы символом нижнего подчеркивания. В самих же значениях, записанных в ячейках таблицы Excel, используйте обычные пробелы. А перед тем, как применять в формуле, мы их специальным образом обработаем при помощи функции ПОДСТАВИТЬ.

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

То есть, мы проведем предварительную обработку значений, чтобы они соответствовали правилам написания имён. Вместо =ДВССЫЛ($F$3) запишем

Кавычки здесь не нужны, поскольку ПОДСТАВИТЬ возвращает текстовую строку. Если же в нашем тексте нет пробелов и он состоит из одного слова, то он будет возвращен «как есть». Следите только за тем, чтобы в начале и в конце обрабатываемой текстовой переменной у вас случайно не оказались пробелы. Ведь они тоже будут заменены на нижнее подчеркивание. Ну а чтобы не заниматься этим ручным контролем, усложните еще немного свою формулу при помощи функции СЖПРОБЕЛЫ. Она автоматически уберет начальные и конечные пробелы из текста. В итоге получим:

Ну а теперь — еще один способ, как сделать многоуровневый зависимый выпадающий список в Excel.

2. Комбинация СМЕЩ + ПОИСКПОЗ

Итак, у нас снова есть перечень марок и моделей автомобилей. Только записан он немного по-другому.

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

Первое условие — исходные данные должны быть отсортированы по маркам, а внутри марок — по моделям. То есть, нужно отсортировать по столбцу А, а затем — по В.

Начнем с простого. В ячейке D1 создадим выпадающий список из марок автомобилей. Для этого в F1:F3 запишем их названия и затем употребим их в качестве источника. Напомню, что нужно нажать Меню — Данные — Проверка данных.

Далее нам нужно в D2 создать второй уровень, где будут только модели выбранной марки. В этот раз источник данных мы определим несколько иначе, чем ранее. Воспользуемся тем, что функция СМЕЩ может возвращать массив данных, который мы как раз и можем употребить в качестве наполнения нашего второго перечня. Но для этого ей нужно передать целых 5 параметров:

  • координаты верхней левой ячейки,
  • на сколько строк нужно сместиться вниз — A,
  • на сколько столбцов нужно перейти вправо — B,
  • высота массива (строк) — C,
  • ширина массива (столбцов) D.

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

Традиционно точкой отсчета для функции СМЕЩ возьмем ячейку A1. Теперь нам нужно решить, на сколько позиций вниз и вправо нужно перейти, чтобы указать левый верхний угол нового перечня с моделями. Предположим, первоначально мы выбрали Ford.

На сколько шагов сместиться вниз? Применим функцию ПОИСКПОЗ, которая возвратит нам номер позиции первого вхождения «Ford».

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

Третий параметр установим равным 1, так как нужно перейти на один шаг вправо из A в B. Мы находимся в начальной точке нашего диапазона. Теперь рассчитаем, на сколько ячеек вниз он будет продолжаться. Для этого подсчитаем, сколько раз «Ford» встречается в нашем перечне. Столько и будет значений вниз.

А теперь объединяем все это в СМЕЩ:

Последняя единичка означает, что массив состоит из одной колонки.

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

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

Источник:
http://mister-office.ru/formuly-excel/dependent-dropdown-lists-excel.html

Создание раскрывающегося списка

Microsoft 365 позволяет максимально эффективно использовать время

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

На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel. Если это не так, список можно быстро преобразовать в таблицу, выделив любую ячейку диапазона и нажав клавиши CTRL+T.

Читайте также  Создание спарклайнов - Excel для Mac

Почему данные следует поместить в таблицу? Потому что в этом случае при добавлении и удалении элементов все раскрывающиеся списки, созданные на основе этой таблицы, будут обновляться автоматически. Дополнительные действия не требуются.

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

На ленте откройте вкладку Данные и нажмите кнопку Проверка данных.

Примечание: Если кнопка Проверка данных недоступна, возможно, лист защищен или является общим. Разблокируйте определенные области защищенной книги или отмените общий доступ к листу, а затем повторите шаг 3.

На вкладке Параметры в поле Тип данных выберите пункт Список.

Щелкните поле Источник и выделите диапазон списка. В примере данные находятся на листе «Города» в диапазоне A2:A9. Обратите внимание на то, что строка заголовков отсутствует в диапазоне, так как она не является одним из вариантов, доступных для выбора.

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

Установите флажок Список допустимых значений

Откройте вкладку Подсказка по вводу.

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

Откройте вкладку Сообщение об ошибке.

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

Не знаете, какой параметр выбрать в поле Вид?

Чтобы отобразить сообщение, не препятствующее вводу данных, которые не содержатся в раскрывающемся списке, выберите вариант Сообщение или «Предупреждение». Если выбрать вариант «Сообщение», сообщение будет отображаться со значком , а если «Предупреждение» — со значком .

Чтобы заблокировать пользователям ввод данных, которые не содержатся в раскрывающемся списке, выберите вариант Остановка.

Примечание: Если вы не добавили заголовок и текст, по умолчанию выводится заголовок «Microsoft Excel» и сообщение «Введенное значение неверно. Набор значений, которые могут быть введены в ячейку, ограничен».

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

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

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

Чтобы удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.

Предлагаем скачать образец книги с несколькими примерами проверки данных, аналогичными примеру в этой статье. Вы можете воспользоваться ими или создать собственные сценарии проверки данных. Скачать примеры проверки данных Excel.

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

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

Создайте список допустимых элементов для раскрывающегося списка. Для этого введите элементы на листе в одном столбце или строке без пустых ячеек.

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

На вкладке Данные в группе Инструменты нажмите кнопку Проверка данных или Проверить.

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

Откройте вкладку Параметры и во всплывающем меню Разрешить выберите пункт Список.

Щелкните поле Источник и выделите на листе список допустимых элементов.

Диалоговое окно свернется, чтобы было видно весь лист.

Нажмите клавишу ВВОД или кнопку Развернуть , чтобы развернуть диалоговое окно, а затем нажмите кнопку ОК.

Значения также можно ввести непосредственно в поле Источник через запятую.

Чтобы изменить список допустимых элементов, просто измените значения в списке-источнике или диапазон в поле Источник.

Можно указать собственное сообщение об ошибке, которое будет отображаться при вводе недопустимых данных. На вкладке Данные нажмите кнопку Проверка данных или Проверить, а затем откройте вкладку Сообщение об ошибке.

На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel.

Почему данные следует поместить в таблицу? Потому что в этом случае при добавлении и удалении элементов все раскрывающиеся списки, созданные на основе этой таблицы, будут обновляться автоматически. Дополнительные действия не требуются.

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

На ленте откройте вкладку Данные и нажмите кнопку Проверка данных.

На вкладке Параметры в поле Разрешить выберите пункт Список.

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

Фрукты;Овощи;Зерновые культуры;Молочные продукты;Перекусы

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

Установите флажок Список допустимых значений

Откройте вкладку Сообщение для ввода.

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

Откройте вкладку Сообщение об ошибке.

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

Нажмите кнопку ОК.

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Источник:
http://support.microsoft.com/ru-ru/office/%D1%81%D0%BE%D0%B7%D0%B4%D0%B0%D0%BD%D0%B8%D0%B5-%D1%80%D0%B0%D1%81%D0%BA%D1%80%D1%8B%D0%B2%D0%B0%D1%8E%D1%89%D0%B5%D0%B3%D0%BE%D1%81%D1%8F-%D1%81%D0%BF%D0%B8%D1%81%D0%BA%D0%B0-7693307a-59ef-400a-b769-c5402dce407b

Многоуровневый связанный список в EXCEL

21 января 2013 г.

Для моделирования сложных иерархических данных создадим Многоуровневый связанный список.

Потребность в создании иерархических данных появляется при решении следующих задач:

  • ОтделСотрудники отдела . При выборе отдела из списка всех отделов компании, динамически должен формироваться список, содержащий всех сотрудников этого отдела (двухуровневая иерархия);
  • Город – Улица – Номер дома . При заполнении адреса проживания из списка городов нужно выбирать город , затем из списка всех улиц этого города – улицу , затем, из списка всех домов на этой улице – номер дома (трехуровневая иерархия).

В этой статье рассмотрен Многоуровневый связанный список. Двухуровневый связанный список или просто Связанный список рассмотрен в статьях Связанный список и Расширяемый Связанный список. Материал статьи один из самых сложных на сайте Excel2.ru , поэтому необходимо для начала ознакомиться с вышеуказанными статьями. Многоуровневый связанный список будем реализовывать с помощью инструмента Проверка данных ( Данные/ Работа с данными/ Проверка данных ) с условием проверки Список .Создание Многоуровневого связанного списка рассмотрим на конкретном примере.

Примечание : Рассмотренный в этой статье Многоуровневый связанный список на самом деле правильнее назвать Трехуровневым, т.к. создать четырехуровневый связанный список, используя рассмотренный здесь подход, очень проблематично. Для тех, кому требуется создать структуру с 4-мя и более уровнями, см. статью Многоуровневый связанный список типа Предок-Родитель .

Постановка задачи

Имеется перечень Регионов . Для каждого Региона имеется свой перечень Стран . Для каждой Страны имеется свой перечень Городов .

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

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

Сначала выберем, например, Регион «Америка» с помощью Выпадающего списка .

Затем выберем Страну «США» из Региона «Америка».

Причем перечень стран в выпадающем списке будет содержать только страны из выбранного на предыдущем шаге Региона «Америка».

И, наконец, выберем Город «Атланта» из Страны «США».

Причем перечень городов в выпадающем списке будет содержать только города из выбранной на предыдущем шаге Страны, т.е. из «США».

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

Список Регионов и перечни Стран разместим на листе Страны .

Обратите внимание, что названия Регионов (диапазон А2:А12 на листе Страны ) в точности должны совпадать с заголовками столбцов, содержащих названия соответствующих Стран ( В1: L 1 ).

Это требование обеспечивается формулой (см. статьи о Транспонировании ). =ДВССЫЛ(АДРЕС(СТРОКА($A$1)-СТОЛБЕЦ($A$1)+СТОЛБЕЦ();1))

с помощью которой формируются заголовки столбцов. Введем ее в диапазон ячеек В1: L 1 .

Список Стран и перечни Городов разместим на листе Города .

Откуда же возьмется перечень стран на листе Города ? Очевидно, что после заполнения листа Страны названиями стран, необходимо, что они каким-то чудесным образом переместились на лист Города . Это чудесное перемещение организуем формулами. Список Стран сформируем на листе Города в столбце А с помощью решения приведенного в статье Объединение списков . Значения для этого списка будем брать из Именованного диапазона Диап_Стран (его нужно предварительно создать через Диспетчер имен ) . Именованный диапазон Диап_Стран образуем формулой:

Читайте также  Функция ЕСЛИ - — вложенные формулы и типовые ошибки - Служба поддержки Office

Для формирования списка Стран нам также понадобится Именованная формула Строки_Столбцы_Стран

Окончательная формула в столбце А на листе Города выглядит так:

сформирует необходимый нам список Стран .

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

  • нажать кнопку меню « Присвоить имя » ( Формулы/ Определенные имена/ Присвоить имя );
  • в поле Имя ввести Регионы ;
  • в поле Диапазон ввести формулу

Формула подсчитывает количество элементов в столбце А на листе Страны (функция СЧЁТЗ() ) и определяет ссылку на последний элемент в столбце (функция ИНДЕКС() ), тем самым формируется диапазон, содержащий все значения Регионов . Пропуски в столбце А не допускаются.

Аналогичным образом создадим Динамический диапазон Список_Стран для формирования выпадающего списка содержащего названия стран:

Создадим Именованную формулу Позиция_региона для определения позиции, выбранного пользователем региона, в созданном выше диапазоне Регионы:

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

Аналогичным образом создадим именованную формулу для определения позиции, выбранной пользователем страны, в диапазоне Список_Стран =ПОИСКПОЗ(таблица!B5;Список_Стран;0) . Перед созданием формулы нужно сделать активной ячейку С5 на листе Таблица .

Создадим Именованные константы МаксСтран равную 20 и МаксГородов равную 30. Константы соответствует максимальному количеству стран в регионе и, соответственно, максимальному количеству городов в стране. Эти значения произвольны и их можно изменить.

Создадим именованный диапазон Выбранный_Регион для определения диапазона на листе Страны , содержащего страны выбранного региона:

Теперь, например, при выборе региона Америка функция СМЕЩ() вернет ссылку на диапазон страны!$B$2:$B$20

Создадим аналогичный диапазон Выбранная_Страна для определения диапазона на листе Города , содержащего города выбранного региона: =СМЕЩ(города!$A$2;;Позиция_страны;МаксГородов)

Создадим две последние именованные формулы Страны и Города : =СМЕЩ(страны!$A$2;;Позиция_региона;СЧЁТЗ(Выбранный_Регион)) =СМЕЩ(города!$A$2;;Позиция_страны;СЧЁТЗ(Выбранная_Страна))

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

  • выделяем диапазон B5:B22 налисте Таблица ;
  • вызываем инструмент Проверка данных ,
  • устанавливаем тип данных Список ,
  • в поле Источник вводим: =Страны .

Также создадим связанный выпадающий список для ячеек из столбца Город (диапазон С5:С22 , в поле Источник вводим: =Города )

На листе Таблица после выбора Региона и Страны теперь есть возможность выбора Города .

Для добавления новых Регионов и их Стран достаточно ввести новый Регион в столбец A (лист Страны ), в строке 1 автоматически отобразится соответствующий заголовок. Под появившимся заголовком в строке 1 введите страны нового Региона .Для добавления новых Городов, на листе Города в строке 1 найдите нужное название страны (оно автоматически появится там после добавления страны на листе Страны ). Под этим заголовком введите название города.

СОВЕТ: В этой статье города (и страны) размещены в нескольких столбцах. Обычно однотипные значения размещают в одном столбце (списке). В статье Многоуровневый связанный список в MS EXCEL на основе таблицы все исходные данные размещены на одном листе, а однотипные данные (названия городов) — в одном столбце. Это облегчает написание формул и позволяет создать списки с большим количеством уровней иерархии (4-6).

Источник:
http://excel2.ru/articles/mnogourovnevyy-svyazannyy-spisok-v-ms-excel

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

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

Источник:
http://www.planetaexcel.ru/techniques/1/38/

Как в Excel сделать выпадающий список (простой или зависимый) с выбором нескольких значений?

Доброго времени на fast-wolker.ru! В процессе рутинной работы с таблицами всегда возникает желание ускорить ввод значений. Особенно это касается наименований позиций. Зачастую мы имеем один и тот же список, с которыми постоянно приходится работать, а новые добавляются постепенно.

Каждый раз заносить в таблицу одно и то же название утомительно; к тому же всегда есть возможность ошибки — поставить лишнюю точку, пробел и так далее. Сегодня расскажу о способах, который позволяет в ряде случаев ускорить работу. В самых простых примерах, может что-то подойдет и Вам. Итак…

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

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

Ставим курсор на следующую пустую ячейку под списком кликаем правой кнопкой мыши; из контекстного меню выбираем команду. Или используем сочетание клавиш «Alt+ Стрелка вниз». Такой способ выбора из списка использовать приходиться не слишком часто. Наименования ведь редко повторяются.

Читайте также  Как сделать график в клетку в excel?

Лучше использовать следующий способ — хранение готового списка на другом листе нашей книги. А в нужной ячейке делать ссылку на данные этого листа.

Как в эксель сделать выпадающий список с другого листа?

Я таблицу уже создал . Теперь создадим новый лист в документе и назовем его «база». Лист потом этот можно скрыть, чтобы случайно не сломать документ. В произвольном месте этого нового листа сохраним наш будущий список, в этом примере его можно дополнить пустыми строками и время от времени добавлять в них новые позиции.

Выделяем нужную область и в верхней левом верхнем углу присваиваем ей имя «Список_Кондит». У нас есть теперь первый ИМЕНОВАННЫЙ диапазон в нашей книге эксель. Чтобы потом диапазонами управлять, нужно открыть вкладку «Формулы» и найти там «Диспетчер имен»:

Следующий шаг — идем обратно в наш лист где создана основная таблица. Выделяем в графе нужные строки. Идем во вкладку «Данные»- «Проверка данных». В строке «тип данных» выбираем «Список»….

А в строке «Источник» копируем адрес нашего диапазона из «диспетчера имен»…

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

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

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

Сначала сделаем в листе «база» наши будущие списки. У нас будет три категории товара — полуфабрикаты, рыба и хлебобулочные. И мы заполним ими три графы.

Сейчас в них немного позиций; ниже будет описано, как сделать, чтобы при добавлении в «базу» новых позиций они автоматом появлялись в списке при выборе. Первым сделаем выпадающий список категорий в точности так, как это было описано в предыдущем примере. Идем в «Данные -«Проверка данных». Укажем в качестве диапазона только строку с названиями категорий.

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

Кстати, получившийся результат можно копировать в нижние ячейки -как обычные формулы. Работаем далее. Сначала займемся доработкой наших трех списков на листе «База». Доработаем сначала графу «Полуфабрикаты». Сделаем это, чтобы позже можно было добавлять в «базу» новые позиции, а они автоматом отображались бы в списках.

Ставим курсор в строку из «Полуфабрикаты, затем открываем вкладку «Главная» и жмем на кнопку «Форматировать как таблицу». Дизайн выбираем любой. Задаем по столбцу нужный вертикальный диапазон, включая только строки с наименованиями; ничего кроме диапазона не меняем.

В процессе этих действий нам стал доступен «конструктор таблиц». Обязательно присваиваем нашей( как бы созданной) таблице имя (в левом верхнем углу) «Полуфабрикаты».

Повторяем действо для граф «Рыба» и «Хлебобулочные».

Завешающий этап. Встаем в нужную строку таблицы, как обычно идем в «Данные»-«Проверка данных», выбираем тип «Список» и в качестве диапазона указываем на соседнюю ячейку , т.е на ранее созданный список, но…

… с использованием в «Источнике» функции ДВССЫЛ. «=ДВССЫЛ($Е$6)». Эта функция возвращает ссылку на наши диапазоны т.е. предыдущие списки, а не содержимое ячейки. Мы работаем с данными, в функцию просто их подставили. Наименование вводится вручную.

Обратите внимание на «источник». Указана абсолютная адресация столбца и ячейки (через знаки «$»). Чтобы распространить в таком виде на остальные строки, нужно будет указывать для каждой строки свой источник данных. Это долго. Можно аккуратно убрать абсолютную адресацию руками из «источника» и после скопировать ячейку на остальные строки ниже. Тогда «формула» распространится корректно.

Попробуйте добавить в базу новые позиции; проверьте — появляются ли они теперь автоматически в списке после указании категорий? Должно получиться.

Делаем выпадающий список в ячейке эксель с выбором по первой букве

Быстро понимаешь, что хорошо бы иметь возможность выбора из списка по первой букве, тогда процесс идет гораздо быстрее. Следующий способ познакомит вас с разработкой в эксель. Сначала Включим в настройках Excel режим конструктора через «Параметры».

В режиме конструктора вставим в наш документ элемент управления Active X «Поле со списком«:

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

  • ListFillRange — сюда вставляем наш именованный диапазон из «диспетчера имен»;
  • LinkedCell — связанная ячейка, куда будет выводиться выбранный из списка элемент — нужно, если данные ее будут использоваться в других таблицах, формулах и т.д;
  • ListRows — количество отображаемых строк в списке;
  • Font — выбираем шрифт, размер, стиль;
  • ForeColor и BackColor — цвет текста и фона;
  • ListRow — количество возвращаемых строк в списке;
  • PrintObject — определяет — выводить элемент управления на печать или нет (истина или ложь)

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

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

Источник:
http://fast-wolker.ru/kak-v-eksel-sdelat-vypadayushhij-spisok-delaem-prostye-i-zavisimye-spiski.html

Выпадающий список в Excel

В программе Microsoft Excel есть возможность создать выпадающий список – то есть сделать так, чтобы одна ячейка содержала несколько значений, между которыми может переключаться пользователь. Это очень удобный инструмент для работы с таблицами, в частности, если речь идёт о проверке введённых данных. Поэтому если вы не знаете, как сделать в экселе выпадающий список, то вам помогут приведённые в статье инструкции.

Создание выпадающего списка

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

Шаг 1. Перейдите во вкладку «Данные», которая расположена на верхней панели, затем в блоке «Работа с данными» выберите инструмент проверки данных (на скриншоте показано, какой иконкой он изображен).

Шаг 2. Теперь откройте самую первую вкладку «Параметры», и установите «Список» в перечне типа данных.

Шаг 3. Теперь в поле ввода данных «Источник» вы можете указывать значения, которые будут составлять раскрывающийся список. Сделать это можно следующими способами:

    вручную. Для этого просто введите значения нужных ячеек, разделив их точкой с запятой;

На заметку! Чтобы не вводить диапазон ячеек вручную, достаточно левой кнопкой мышки выделить первую ячейку со значением, поставить двоеточие и выделить мышью последнюю ячейку со значением.

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

На заметку! Есть ещё один способ указать значение в источнике – написать в поле ввода имя диапазона. Этот способ самый быстрый, но прежде чем прибегать к нему, нужно создать именованный диапазон. О том, как это сделать, мы поговорим позже.

Видео — Создание выпадающих списков в Excel

Раскрывающийся список с подстановкой данных

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

    Выделите левой кнопкой мышки диапазон для списка (в данном примере это будет перечень деревьев), затем откройте вкладку «Главная» и выберите меню «Форматировать как таблицу».

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

На заметку! У этого способа есть принципиальное отличие от первого – в качестве списка используется готовая таблица, а не диапазон значений. Это значит, что любые изменения в таблице будут отображаться и в выпадающем списке.

Давайте протестируем это. Для начала добавим в нашу новую отформатированную таблицу новую ячейку «ёлка». Как видите, это же значение добавилось в список.

Если удалить ячейку (в данном случае мы удалили «берёза»), это тоже отобразится на содержании раскрывающегося списка.

На заметку! В этом способе мы имели дело с так называемой «умной таблицей». Она легко расширяется, и это её свойство полезно для многих манипуляций с таблицами Excel, в том числе и для создания выпадающего списка.

Зависимые раскрывающиеся списки

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

    Для начала вам нужно создать именованный диапазон. Перейдите во вкладку «Формулы», затем выберите «Диспетчер имён» и «Создать».

Теперь вам известны несколько способов, как создать выпадающее меню в Excel-таблице. Это может пригодиться вам в ряде случаев, а особенно – в проверке данных. Выбор правильного способа должен осуществляться в зависимости от того, с каким типом таблицы вы работаете. Если это «одноразовая» таблица, то подойдёт первый способ – он быстрый и лёгкий. Если же таблица требует постоянных изменений, то лучше всего будет воспользоваться тем способом, который включает в себя создание «умных таблиц» и именованных диапазонов.

Видео — Связанные выпадающие списки: легко и быстро

Понравилась статья?
Сохраните, чтобы не потерять!

Источник:
http://pc-consultant.ru/rabota-na-komp-yutere/vypadajushhij-spisok-v-excel/