Тройной выпадающий список в excel

Для сложных иерархических структур с тремя и более уровнями создадим Многоуровневый связанный список типа Предок-Родитель. Теперь структуры типа: Регион-Страна-Город-Улица можно создавать в MS EXCEL.

В статье

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

мы уже создавали многоуровневый список. Но, у того списка было 2 недостатка:

• слишком сложные формулы;
• максимальное количество уровней — 3.

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

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

Решение

Выпадающие списки

будем формировать с помощью одной большой

Именованной формулы

:

=ЕСЛИ(Таблица!B$7=Списки!$F$5;СМЕЩ(Списки!$D$4;1;0;СЧЁТЕСЛИ(Списки!$B$5:$B$30;0));СМЕЩ(Списки!$D$4;ПОИСКПОЗ(Таблица!A8;Списки!$C$5:$C$30;0);0;СЧЁТЕСЛИ(Списки!$C$5:$C$30;Таблица!A8)))

Эту формулу можно посмотреть через

Диспетчер имен

(имя формулы —

Выбор

), но так как формула использует

относительную адресацию

в ссылках на ячейки

Таблица!B$7 и

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

В8

на листе Таблица. О том как работает эта формула см. в разделе статьи ниже.

ПРИМЕЧАНИЕ

: Функция

ЕСЛИ()

нужна для формирования списка самого верхнего уровня иерархии — у него нет Родителя.

Для ввода формулы выделите ячейку

B8

, вызовите

Диспетчер имен

, и введите формулу:

Нажмите ОК.

Опять выделите ячейку

B8

, сформируйте выпадающий список на основе

Проверки данных

. В поле Тип данных укажите Список, в поле Источник введите формулу

=Выбор

.

С помощью

Маркера заполнения

скопируйте ячейку вправо и вниз (или вверх и вправо):

выпадающие списки

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

Наполнять

выпадающие списки

будем из таблицы на листе Списки (см.

файл примера

):

Для наглядности различные уровни выделены

Условным форматированием

.

Заполнение таблицы на листе Списки нужно производить следующим образом:

• Столбец Номер уровня содержит числовые коды уровней. Самый верхний уровень (0) содержит название Регионов. Можно создать любое количество уровней;
• Столбец Родитель содержит названия из столбца Потомок более верхнего уровня;
• Столбец Потомок связывает значений из 2-х соседних уровней. Нужно иметь ввиду, что если мы начали заполнять, например, все страны Америки, то нужно полностью закончить этот список, затем переходить к следующему региону. Выполнение этого требования необходимо для правильной работы вышеуказанной формулы. Выпадающие списки формируются именно из этого столбца.

Как работает эта формула?

Рассмотрим как формируется

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

в ячейке

B8

на листе Таблица (заполняем Регионы).

• 
  Выражение
  
  ЕСЛИ(Таблица!B$7=Списки!$F$5
  
  ; проверяет заполняем ли мы столбец Регионы (самый верхний уровень с номером 0) или нет. В данном случае выражение вернет значение ИСТИНА;
  
• 
  Так как предыдущее выражение =ИСТИНА, то работает первое условие функции
  
  ЕСЛИ()
  
  , т.е. выражение
  
  СМЕЩ(Списки!$D$4;1;0;СЧЁТЕСЛИ(Списки!$B$5:$B$30;0))
  
• функция
  
  СМЕЩ()
  
  выбирает из столбца D (Потомки) все значения, у которых уровень равен 0. Из этих значений формируется
  
  выпадающий список.
  

Рассмотрим как формируется

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

в ячейке

С8

на листе Таблица (заполняем Страны).

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

=ЕСЛИ(Таблица!C$7=Списки!$F$5;СМЕЩ(Списки!$D$4;1;0;СЧЁТЕСЛИ(Списки!$B$5:$B$30;0));СМЕЩ(Списки!$D$4;ПОИСКПОЗ(Таблица!B8;Списки!$C$5:$C$30;0);0;СЧЁТЕСЛИ(Списки!$C$5:$C$30;Таблица!B8)))

• Выражение
  
  ЕСЛИ(Таблица!С$7=Списки!$F$5
  
  ; в данном случае выражение вернет значение ЛОЖЬ;
• Так как предыдущее выражение =ЛОЖЬ, то работает второе условие функции
  
  ЕСЛИ()
  
  , т.е. выражение
  
  СМЕЩ(Списки!$D$4;ПОИСКПОЗ(Таблица!B8;Списки!$C$5:$C$30;0);0;СЧЁТЕСЛИ(Списки!$C$5:$C$30;Таблица!B8))
  
• функция
  
  СМЕЩ()
  
  выбирает из столбца D (Потомки) все значения, у которых Родитель = Америка.
• Для этого выражение
  
  ПОИСКПОЗ(Таблица!B8;Списки!$C$5:$C$30;0)
  
  возвращает номер позиции первой ячейки в столбце Родитель, содержащей значение Америка. Это позиция номер 4. Именно с этой позиции, но из другого столбца, будет формироваться наш
  
  выпадающий список
  
  ;
• Теперь для
  
  выпадающего списка
  
  осталось указать его длину. Для этого используем выражение
  
  СЧЁТЕСЛИ(Списки!$C$5:$C$30;Таблица!В8)
  
  .

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

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

выпадающих списков

, было изменено значение ячейки

С10

. Например, вместо США выберем Мексика. Естественно, значение в ячейке

D10

не изменится (в ней останется один из городов США). Чтобы подсказать пользователю об ошибке, используем

Условное форматирование

.

Формула в правиле

Условного форматирования

ищет в таблице на листе Списки пару Родитель-Потомок. Если такая пара не найдена, то ячейка выделяется красной заливкой.

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

Создать многоуровневый зависимый выпадающий список в Excel

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

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

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

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

2. Затем выберите значения первого раскрывающегося списка (исключая ячейку заголовка), а затем дайте им имя диапазона в поле Поле имени которые помимо строки формул, см. снимок экрана:

3. Затем выберите данные второго раскрывающегося списка и нажмите Формулы > Создать из выбранного, см. снимок экрана:

4. В выскочившем Создать имена из выбора диалоговое окно, отметьте только Верхний ряд вариант, см. снимок экрана:

5. Нажмите OK, И имена диапазонов были созданы для каждого второго раскрывающегося списка сразу, затем вы должны создать имена диапазонов для значений третьего раскрывающегося списка, продолжайте нажимать Формулы > Создать из выбранного, В Создать имена из выделенного диалоговое окно, отметьте только Верхний ряд вариант, см. снимок экрана:

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

В-третьих, создайте выпадающий список Data Validation.

7. Затем щелкните ячейку, в которую вы хотите поместить первый зависимый раскрывающийся список, например, я выберу ячейку I2, затем щелкните Данные > проверка достоверности данных > проверка достоверности данных, см. снимок экрана:

8. В проверка достоверности данных диалоговое окно под Настройки , выберите Список из Разрешить раскрывающийся список, а затем введите эту формулу: = Континенты в Источник текстовое поле, см. снимок экрана:

Внимание: В этой формуле Континенты — имя диапазона первых раскрывающихся значений, созданных на шаге 2, измените его по своему усмотрению.

9, Затем нажмите OK Кнопка, первый раскрывающийся список был создан, как показано ниже:

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

• (1.) Выберите Список из Разрешить раскрывающийся список;
• (2.) Затем введите эту формулу: = КОСВЕННО (ПОДСТАВИТЬ (I2; «»; «_»)) в Источник текстовое окно.

Внимание: В приведенной выше формуле I2 — это ячейка, содержащая первое значение раскрывающегося списка, пожалуйста, измените его на свое.

11. Нажмите OK, и сразу был создан второй зависимый раскрывающийся список, см. снимок экрана:

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

• (1.) Выберите Список из Разрешить раскрывающийся список;
• (2.) Затем введите эту формулу: = КОСВЕННО (ПОДСТАВИТЬ (J2; «»; «_»)) в текстовое поле Источник.

Внимание: В приведенной выше формуле J2 — это ячейка, содержащая второе значение раскрывающегося списка, пожалуйста, измените его на свое.

13, Затем нажмите OK, и три зависимых раскрывающихся списка были успешно созданы, см. демонстрацию ниже:

Создавайте многоуровневый зависимый выпадающий список в Excel с удивительной функцией

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

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Во-первых, вы должны создать формат данных, как показано на скриншоте ниже:

2, Затем нажмите Кутулс > Раскрывающийся список > Динамический раскрывающийся список, см. снимок экрана:

3. В Зависимый раскрывающийся список диалоговом окне выполните следующие действия:

• Проверить Раскрывающийся список, зависящий от 3-5 уровней вариант в Тип раздел;
• Укажите необходимый диапазон данных и выходной диапазон.

4, Затем нажмите Ok Кнопка, теперь трехуровневый раскрывающийся список был создан в виде следующей демонстрации:

Нажмите, чтобы скачать Kutools for Excel и бесплатная пробная версия прямо сейчас!

Более относительные статьи с выпадающим списком:

Связанные выпадающие списки в Excel.

​Смотрите также​​ исчерпал запас допустимых​: китин — Это​​ уже другая тема​ инструмент «Вставить» –​​ «Источник» функцию вида​​ Long If Target.Cells.Count​Вручную через «точку-с-запятой» в​​ 4 ячейки от​ это сделали для​ столько раз, сколько​ Трюк, который делает​При работе с перечнем​Используем именованный диапазон​этого подхода: элементы​
​ =ДВССЫЛ(ПОДСТАВИТЬ(A2;» «;»_»))​​ всех списков сразу​Выпадающие списки в Excel​​ смен. Добавим еще​
​ вы убрали значение​ ?​ «ActiveX». Здесь нам​ =ДВССЫЛ(E3). E3 –​ > 1 Then​ поле «Источник».​ H2). В 4-ой​ категории. Только единственный​ есть подкатегорий. Очень​ работу проще и​ элементов, расположенным в​Создадим Именованный диапазон Список_элементов,​ списка легко потерять​Если список на​
​ вместе с шапкой​бывают разные. Есть​ один столбец и​ #Н/Д, если значение​enzo​ нужна кнопка «Поле​ ячейка с именем​ Exit Sub If​
​Ввести значения заранее. А​ ячейке столбца Подкатегория​
​ вопрос: «Как сказать​
​ важно, чтобы данные​ быстрее. Трюк, благодаря​ другой книге, файл​ содержащий перечень элементов​ (например, удалив строку​ другом листе, то​ таблицы списков –​ простой​ введем в него​ в первой ячейке​
​: Вроде нашел в​ со списком» (ориентируемся​ первого диапазона.​ Target.Address = «$C$2″​ в качестве источника​ (не включая заголовок,​ Excelю выбрать только​ были отсортированы по​
​ которому ваши формы​ Источник.xlsx должен быть​ выпадающего списка (ячейки​ или столбец, содержащие​ в формуле указываем​ у нас это​раскрывающийся список Excel в​ формулу, которая будет​ не выбрано?​
​ форуме)​ на всплывающие подсказки).​​Бывает, когда из раскрывающегося​ Then If IsEmpty(Target)​ указать диапазон ячеек​ так как речь​ те значения, которые​ столбцу Категория. Это​ будут удобны и​
​ открыт и находиться​A1:A4​ ячейку​ название этого листа.​ диапазон А1:D4. На​ ячейке​ выводить номера свободных​
​Вроде ок, все​felixtreem​ ​Щелкаем по значку –​
​ списка необходимо выбрать​ Then Exit Sub​ со списком.​ идет о диапазоне​ предназначены для конкретной​ будет чрезвычайно важно,​ приятны.​ в той же​на листе Список).​B1​ Напишем так. =ДВССЫЛ(»Размеры!А2:А4»)​ закладке «Формулы» в​. Есть​ сотрудников:​
​ работает. Очень огромное​: Добрый день.​ становится активным «Режим​ сразу несколько элементов.​​ If WorksheetFunction.CountIf(Range(«Деревья»), Target)​Назначить имя для диапазона​ с именем Рабочий_Список),​ категории?» Как вы,​ когда позже будем​Пример использования зависимого выпадающего​ папке, иначе необходимо​Для этого:​
​); не удобно вводить​​Нажимаем «ОК». Теперь​ разделе «Определенные имена»​​многоуровневые зависимые выпадающие списки​
​=ЕСЛИ(F2-G2​ спасибо. Сам бы​Помогите решить проблему.​ конструктора». Рисуем курсором​ Рассмотрим пути реализации​ = 0 Then​ значений и в​ есть слово Питание​ наверное, догадываетесь, я​ писать формулу.​ списка для создания​
​ указывать полный путь​выделяем​ большое количество элементов.​ во втором столбце​
​ нажимаем функцию «Создать​​ в Excel​Теперь надо сформировать непрерывный​​ такую формулу я​​Есть лист1, в​ (он становится «крестиком»)​
​ задачи.​
​ lReply = MsgBox(«Добавить​ поле источник вписать​ (его первое появление).​ буду использовать здесь​Можно было бы также​ удобной формы заполнения​ к файлу. Вообще​А1:А4​ Подход годится для​ установлены выпадающие списки,​ из выделенного фрагмента».​
​. Это, когда, в​ (без пустых ячеек)​ бы никогда не​ котором таблица из​ небольшой прямоугольник –​Создаем стандартный список с​ введенное имя «​ это имя.​ Мы используем этот​ рабочую таблицу и,​
​ использовать таблицы с​ документов, с помощью​ ссылок на другие​,​ маленьких (3-5 значений)​ которые меняются, в​
​ В появившемся диалоговом​ зависимости от выбранных​ список свободных сотрудников​ нарисовал бы.​ колонок: НаименованиеКол-воЦенаСумма​ место будущего списка.​ помощью инструмента «Проверка​ & _ Target​Любой из вариантов даст​ факт собственно для​ конечно же, формулы.​ первого изображения. Разумеется,​ которых продавцы заказывали​ листы лучше избегать​
​нажимаем Формулы/ Определенные имена/​ неизменных списков.​ зависимости от того,​ окне оставляем галочку​ данных в первом​
​ для связи -​=ЕСЛИ(ЕНД(ВПР($A12;Лист2!$B$2:$H$161;6;ЛОЖЬ));»»;ВПР($A12;Лист2!$B$2:$H$161;6;ЛОЖЬ))​Есть лист2, в​Жмем «Свойства» – открывается​ данных». Добавляем в​ & » в​ такой результат.​ определения начала диапазона.​
​Начнем с того, что​ формулы были бы​ товары. Из всего​ или использовать Личную​
​ Присвоить имя​Преимущество​ что написано в​ только у строки​ столбце выпадающего списка,​ на следующем шаге​китин​

excel-office.ru

Выпадающий список в MS EXCEL на основе Проверки данных

​ котором есть две​ перечень настроек.​ исходный код листа​ выпадающий список?», vbYesNo​​ Послужит нам для​ мы уже умеем,​ разными. Однажды даже​ ассортимента они должны​ книгу макросов Personal.xlsx​в поле Имя вводим​: быстрота создания списка.​ ячейках первого столбца.​ «В строке выше».​ меняется выпадающий список​ — с выпадающим​: да, felixtreem, я​

​ колонки: ТоварЦена​Вписываем диапазон в строку​ готовый макрос. Как​

​ + vbQuestion) If​Необходимо сделать раскрывающийся список​ этого функция ПОИСКПОЗ​ то есть с​ я нашел в​ были выбрать те​

​ или Надстройки.​ Список_элементов, в поле​Элементы для выпадающего списка​ Получилось так.​Нажимаем «ОК». Всё, имена​ в ячейках второго​ списком. Для этого​

​ убрал значение ошибки​Нужно сделать выпадающий​

А. Простейший выпадающий список — ввод элементов списка непосредственно в поле Источник

​ ListFillRange (руками). Ячейку,​ это делать, описано​ lReply = vbYes​ со значениями из​ (введенная в качестве​ создания раскрывающегося списка​

​ сети такое решение,​​ продукты, которые они​​Если нет желания присваивать​ Область выбираем Книга;​ можно разместить в​Копируем формулу вниз по​​ присвоены. На закладке​​ столбца, третьего, т.д.​ добавим еще один​

​ #Н/Д.а если у​ список в лист1,​ куда будет выводиться​ выше. С его​ Then Range(«Деревья»).Cells(Range(«Деревья»).Rows.Count +​ динамического диапазона. Если​

​ второго аргумента функции​ в ячейке B12.​​ но оно мне​​ собирались продать.​ имя диапазону в​Теперь на листе Пример,​ диапазоне на листе​ столбцу. Мы создали​ «Формулы» нажимаем функцию​

​Здесь разберём​​ столбец и введем​ вас офис выше​ что бы в​ выбранное значение –​ помощью справа от​​ 1, 1) =​​ вносятся изменения в​ СМЕЩ):​ Поэтому выберите эту​ не понравилось, потому​Каждый продавец сначала определял​
​ файле Источник.xlsx, то​​ выделим диапазон ячеек,​

Б. Ввод элементов списка в диапазон (на том же листе, что и выпадающий список)

​ EXCEL, а затем​ двухуровневый выпадающий список​ «Диспетчер имен».​двухуровневый зависимый выпадающий список​ в него такую​ 2007,то все ещё​ колонке «Наименование» можно​ в строку LinkedCell.​

​ выпадающего списка будут​ Target End If​ имеющийся диапазон (добавляются​​Высоту диапазона определяет функция​​ ячейку и нажмите​ что там была​

​ товарную группу, а​​ формулу нужно изменить​ которые будут содержать​ в поле Источник​ в Excel.​Здесь перечислены все наши​
​ в Excel​​ страшноватую на первый​ проше:​ было выбрать позицию​ Для изменения шрифта​ добавляться выбранные значения.Private​ End If End​ или удаляются данные),​ СЧЕТЕСЛИ. Она считает​​ «Данные» / «Проверка​​ фиксированная длина списка:​ затем конкретный товар​ на =ДВССЫЛ(«[Источник.xlsx]лист1!$A$1:$A$4»)​ Выпадающий список.​ инструмента Проверки данных​Другой способ сделать​ диапазоны списков. Проверили​.​ взгляд формулу:​

​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ВПР($A2;Лист2!$B$2:$H$161;6;ЛОЖЬ);»»)​ из лист2 с​ и размера –​ Sub Worksheet_Change(ByVal Target​ If End Sub​ они автоматически отражаются​ все встречающиеся повторения​ данных», а в​ а значит, иногда​ из этой группы.​СОВЕТ:​

​вызываем Проверку данных;​ указать ссылку на​ связанный выпадающий список​ всё. Можно подкорректировать​Например, в первом​

B. Ввод элементов списка в диапазон (на любом листе)

​=ЕСЛИ(D2>СЧЁТ($H$2:$H$10);»»;ИНДЕКС($E$2:$E$10;НАИМЕНЬШИЙ($H$2:$H$10;СТРОКА(E2)-1)))​felixtreem​ колонки «Товар», при​ Font.​ As Range) On​Сохраняем, установив тип файла​

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

​ Форма должна включать​Если на листе​в поле Источник вводим​ этот диапазон.​ в Excel, смотрите​

​ размер диапазона. Мы​ столбце из выпадающего​или, соответственно,​: Тогда может еще​ этом, после выбора​Скачать пример выпадающего списка​ Error Resume Next​ «с поддержкой макросов».​

​Выделяем диапазон для выпадающего​
​ есть слово Питание.​ — «Список».​ поля, а иногда​​ полное имя группы​​ много ячеек с​
​ ссылку на созданное​

• ​Предположим, что элементы списка​​ в статье «Как​​ уменьшили размер диапазона​
• ​ списка выбрали «Пальто».​=IF(D2>COUNT($H$2:$H$10);»»;INDEX($E$2:$E$10;SMALL($H$2:$H$10;ROW(E2)-1)))​
• ​ подскажите, как сделать​ в лист1 пункта​При вводе первых букв​

​ If Not Intersect(Target,​Переходим на лист со​ списка. В главном​ Сколько раз встречается​

• ​В источник списка введите​
• ​ и не отображал​ и определенный индекс​ правилами Проверки данных,​

​ имя: =Список_элементов.​
​ шт;кг;кв.м;куб.м введены в​ сделать связанные выпадающие​ «Юбка», чтобы в​ Во втором столбце​При всей внешней жуткости​​ выборку нужной строки​​ «Наименование» в соседней​ с клавиатуры высвечиваются​ Range(«Е2:Е9»)) Is Nothing​ списком. Вкладка «Разработчик»​

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

​ списки в Excel​ выпадающем списке не​ появился выпадающий список​​ вида, эта формула​​ по первым буквам​ ячейке (то бишь,​ подходящие элементы. И​ And Target.Cells.Count =​ — «Код» -​

​ «Форматировать как таблицу».​

​ и будет позиций​Вид окна «Проверка вводимых​ я могу избежать​ это вручную было​ инструмент Выделение группы​Если предполагается, что​A1:A4​ легко».​ было пустой строки.​ размеров этого пальто.​ делает одну простую​

​ ввода с клавиатуры?​​ через одну ячейку)​ это далеко не​ 1 Then Application.EnableEvents​ «Макросы». Сочетание клавиш​Откроются стили. Выбираем любой.​ в нашем диапазоне.​

​ значений»:​ этого ограничения, но​ бы слишком трудоемким​

​ ячеек (Главная/ Найти​ перечень элементов будет​, тогда поле Источник​При заполнении ячеек данными,​Теперь устанавливаем​ А, если в​

• ​ вещь — выводит​Примерно как в​
• ​ в поле «цена»,​ все приятные моменты​ = False If​ для быстрого вызова​
• ​ Для решения нашей​ Количество позиций в​Как видите, весь трюк​
• ​ признаюсь, что мне​ (и раздражающим) занятием,​ и выделить/ Выделение​ дополняться, то можно​

​ будет содержать =лист1!$A$1:$A$4​ часто необходимо ограничить​первый выпадающий список в​ первом столбце этой​ очередное по номеру​ тотале, начинаешь набирать​ унаследовалась цена из​ данного инструмента. Здесь​ Len(Target.Offset(0, 1)) =​ – Alt +​ задачи дизайн не​ диапазоне — это​ зависимого списка состоит​ больше нравится мое​

​ я предложил очень​ группы ячеек). Опция​ сразу выделить диапазон​Преимущество​ возможность ввода определенным​

​ ячейки столбца А​
​ же ячейки из​ имя сотрудника (используя​ нужный текст и​ «лист2» выбранной строчки.​ можно настраивать визуальное​ 0 Then Target.Offset(0,​ F8. Выбираем нужное​ имеет значения. Наличие​ его высота. Вот​ в использовании функции​ решение, поэтому к​ быстрое и простое​ Проверка данных этого​ большего размера, например,​: наглядность перечня элементов​ списком значений. Например,​.​ выпадающего списка выбрали​ функцию НАИМЕНЬШИЙ) из​ курсор сам переходит​Прикрепляю файл.​ представление информации, указывать​ 1) = Target​ имя. Нажимаем «Выполнить».​ заголовка (шапки) важно.​ функция:​

​ СМЕЩ. Ну хорошо,​​ тому решению я​
​ решение — 2​ инструмента позволяет выделить​А1:А10​ и простота его​ имеется ячейка, куда​У нас, в​ «Брюки», то во​ списка или пустую​ в соответствующие поля.​Pelena​ в качестве источника​

​ Else Target.End(xlToRight).Offset(0, 1)​Когда мы введем в​ В нашем примере​Конечно же, обе функции​ почти весь. Помогают​ больше не возвращался.​ зависимых выпадающих списка.​ ячейки, для которых​. Однако, в этом​ модификации. Подход годится​ пользователь должен внести​

​ примере, мы выделяем​ втором столбце будет​ ячейку, если имена​Можно без лишних​: Здравствуйте​ сразу два столбца.​ = Target End​ пустую ячейку выпадающего​ это ячейка А1​ уже включены в​ ей функции ПОИСКПОЗ​Ну хорошо. Теперь, по​Первым был список всех​ проводится проверка допустимости​ случае Выпадающий список​ для редко изменяющихся​ название департамента, указав​ диапазон A2:A3. И,​

excel2.ru

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

​ выпадающий список с​ свободных сотрудников уже​ заморочек, типа скрывания​Используйте поиск по​Melia95​ If Target.ClearContents Application.EnableEvents​ списка новое наименование,​ со словом «Деревья».​ функцию СМЕЩ, которая​ и СЧЕТЕСЛИ. Функция​

Пример создания зависимого выпадающего списка в ячейке Excel

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

​ форуму, задача решалась​: Друзья, добрый день!​ = True End​ появится сообщение: «Добавить​ То есть нужно​ описана выше. Кроме​ СМЕЩ позволяет динамически​ шаги создания зависимого​ — список всех​ помощью команды Данные/​ строки.​Недостатки​ Логично, предварительно создать​ на закладке «Данные»,​Итак, сделаем две​

​в Excel 2003 и​ возможность выбора соседних​ много раз​Ситуация стандартная:​ If End Sub​ введенное имя баобаб​ выбрать стиль таблицы​ того, обратите внимание,​ определять диапазоны. Вначале​ выпадающего списка.​ продуктов, находящихся в​ Работа с данными/​Избавиться от пустых строк​

​: если добавляются новые​ список департаментов организации​ устанавливаем выпадающие списки.​ таблицы. Саму таблицу​ старше идем в​ позиций.​SkyPro​

​1й выпадающий список​Чтобы выбранные значения показывались​ в выпадающий список?».​ со строкой заголовка.​ что как в​ мы определяем ячейку,​Это необязательный шаг, без​ выбранной категории. Поэтому​ Проверка данных). При​ и учесть новые​

​ элементы, то приходится​

Список категорий и подкатегорий в зависимом выпадающем списке Excel

​ и позволить пользователю​ Тип данных –​ сделаем на странице​ меню​А в ЛИСТ2,​: http://www.excelworld.ru/forum/2-633-1​ (А2) — общая​ снизу, вставляем другой​Нажмем «Да» и добавиться​ Получаем следующий вид​ функции ПОИСКПОЗ, так​ от которой должен​ него мы сможем​ я создал выпадающий​ выборе переключателя Всех​

​ элементы перечня позволяет​ вручную изменять ссылку​

Зависимый выпадающий список подкатегорий

​ лишь выбирать значения​ выбираем «Список». А​ книги «Таблица». А​Вставка — Имя -​ наименования можно сортирнуть​http://www.planetaexcel.ru/techniques/1/38/​ группа риска​ код обработчика.Private Sub​

Рабочая исходная таблица Excel

​ еще одна строка​ диапазона:​ и в СЧЕТЕСЛИ,​ начинаться сдвиг диапазона,​ без проблем справиться​ список, зависимый от​ будут выделены все​ Динамический диапазон. Для​ на диапазон. Правда,​ из этого списка.​ в строке «Источник»​ списки сделаем на​ Присвоить (Insert -​

​ по альфавиту, это​http://www.excel-vba.ru/chto-um….-spiski​2й выпадающий список​ Worksheet_Change(ByVal Target As​ со значением «баобаб».​Ставим курсор в ячейку,​ есть ссылка на​ а в последующих​ с этим. Однако​ выбора, сделанного в​ такие ячейки. При​ этого при создании​ в качестве источника​ Этот подход поможет​ указываем имя диапазона.​ странице «Размеры». У​ Name — Define)​ вообще не критично.​Поиск не пробовали?​ (В2), зависимый -​ Range) On Error​Когда значения для выпадающего​ где будет находиться​

​ диапазон названный Рабочий_Список.​ аргументах определяем его​ мне нравится использовать​ предыдущем списке (здесь​

1. Имена диапазонов ячеек

​ выборе опции Этих​ Имени Список_элементов в​ можно определить сразу​ ускорить процесс ввода​ Например, «=Наименование_товара».​ нас есть такая​в Excel 2007 и​Если слишком геморно,​На этот вопрос​

​ конкретный риск в​ Resume Next If​ списка расположены на​ выпадающий список. Открываем​ Как я уже​ размеры.​ имена, потому что​ вы найдете материал​ же выделяются только​ поле Диапазон необходимо​ более широкий диапазон,​

​ и уменьшить количество​Подробнее, как установить​

1. ​ таблица.​
2. ​ новее — жмем​ то и так​ уже отвечали неоднократно.​ рамках группы из​
3. ​ Not Intersect(Target, Range(«Н2:К2»))​ другом листе или​

​ параметры инструмента «Проверка​ упоминал ранее, не​В нашем примере диапазон​ они значительно облегчают​ о том, как​ те ячейки, для​ записать формулу =СМЕЩ(Список!$A$1;;;СЧЁТЗ(Список!$A:$A))​

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

​ например,​

1. ​ опечаток.​ выпадающий список, смотрите​И мы сделали такие​ кнопку​
2. ​ сойдет, как есть,​китин​ А2.​ Is Nothing And​
3. ​ в другой книге,​ данных» (выше описан​
4. ​ обязательно использовать имена​ будет перемещаться по​
5. ​ как написание, так​

​ создать два зависимых​ которых установлены те​

​Использование функции СЧЁТЗ() предполагает,​

​A1:A100​

3. Создание зависимого выпадающего списка для подкатегории

​Выпадающий список можно создать​ в статье «Выпадающий​ списки.​Диспетчер Имен (Name Manager)​ ну а вдруг.​: да файла лично​Делаю всё шаг​ Target.Cells.Count = 1​ стандартный способ не​ путь). В поле​ диапазонов, можно просто​ столбцу Подкатегория в​ и чтение формулы.​ раскрывающихся списка).​

​ же правила проверки​ что заполнение диапазона​. Но, тогда выпадающий​ с помощью Проверки​ список в Excel».​Внимание!​на вкладке​китин​ я не вижу​ за шагом, как​ Then Application.EnableEvents =​

​ работает. Решить задачу​ «Источник» прописываем такую​

​ ввести $H3: $H15.​ рабочей таблице (G2:H15).​

Проверка вводимых значений для подкатегории в зависимом выпадающем списке

​Присвоим имена двум диапазонам.​Тот же самый результат​ данных, что и​ ячеек (​ список может содержать​ данных ​Устанавливаем​В списках названия​Формулы (Formulas)​: поищите поиском.где недавно​felixtreem​ пишут повсюду, но​ False If Len(Target.Offset(1,​ можно с помощью​ функцию:​

​ Однако использование имен​ Перемещение начнем от​ Список всех категорий​ хочет получить пользователь​ для активной ячейки.​A:A​ пустые строки (если,​или с помощью элемента​зависимые выпадающие списки в​ столбцов (В, С,​и создаем новый именованный​ была такая тема​: Непонятно. Файл прикреплял.​ в итоге постоянно​ 0)) = 0​

​ функции ДВССЫЛ: она​Протестируем. Вот наша таблица​ диапазонов в формуле​ ячейки H2, которая​ и рабочий список​ шаблона домашнего бюджета​Примечание​), который содержит элементы,​ например, часть элементов​ управления формы Поле​ столбце В​ D) должны полностью​ диапазон​Pelena​SkyPro — Те​ имею «При вычислении​ Then Target.Offset(1, 0)​ сформирует правильную ссылку​ со списком на​ делает ее проще​ также является первым​ категорий. Это будут​

​ где нужна категория​:​ ведется без пропусков​ была удалена или​ со списком (см.​.​ совпадать с названием​Имена​: Один вопрос -​ ссылки, что вы​ «Источник» возникает ошибка».​ = Target Else​ на внешний источник​ одном листе:​ и легко читаемой.​ аргументом нашей функции.​ диапазоны A3:A5 (список​ и подкатегория расходов.​Если выпадающий список​ строк (см. файл​ список только что​ статью Выпадающий (раскрывающийся)​Это второй уровень​ в первом столбце​по следующей формуле:​ одна тема​ кинули, читал, там​Пожалуйста, помогите, чем​ Target.End(xlDown).Offset(1, 0) =​ информации.​Добавим в таблицу новое​

​Вот и все:​ В формуле ячейку​ категорий в зеленой​ Пример данных находится​ содержит более 25-30​ примера, лист Динамический​ был создан). Чтобы​ список на основе​ выпадающих списков.​ (у нас –​=СМЕЩ(Лист1!$I$2;0;0;СЧЁТЗ(Лист1!$I$2:$I$10)-СЧИТАТЬПУСТОТЫ(Лист1!I$2:I$10))​Я знаю, что делать,​ описывается категории и​

​ можете.​ Target End If​Делаем активной ячейку, куда​ значение «елка».​Скачать пример зависимого выпадающего​ H2 записали как​ таблице на первом​ на рисунке ниже:​ значений, то работать​ диапазон).​ пустые строки исчезли​ элемента управления формы).​Внимание!​ это наименование товара​в англоязычной версии =OFFSET(Лист1!$I$2;0;0;COUNTA(Лист1!$I$2:$I$10)-COUNTBLANK(Лист1!I$2:I$10))​ но не знаю​ подкатегории. Мне же​Заранее благодарю!​ Target.ClearContents Application.EnableEvents =​

​ хотим поместить раскрывающийся​

​Теперь удалим значение «береза».​ списка в Excel​

​ абсолютную ссылку, потому​ изображении) и G3:G15​Так, например, если мы​ с ним становится​Используем функцию ДВССЫЛ()​ необходимо сохранить файл.​

​В этой статье создадим​Перед тем, как​ – ячейки А2:А4​

​Фактически, мы просто даем​куда потом девать​ надо, что бы​Pelena​ True End If​

exceltable.com

Выпадающий список в Excel с помощью инструментов или макросов

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

​Альтернативным способом ссылки на​Второй недостаток: диапазон источника​ Выпадающий список с​ устанавливать выпадающие списки​ должны совпадать с​ диапазону занятых ячеек​ тела…​ после того, как​: Здравствуйте.​ End Sub​

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

​Открываем параметры проверки данных.​ «умная таблица», которая​ такая уж и​ мы будем использовать​ в фиолетовой рабочей​

​ то в списке​ одновременно отображает только​ перечень элементов, расположенных​ должен располагаться на​

1. ​ помощью Проверки данных​ в столбце В,​
2. ​ ячейками В1:D1).​ в синем столбце​Имеем в качестве примера​ я выбрал на​
3. ​У Вас в​Чтобы выбираемые значения отображались​ В поле «Источник»​ легка «расширяется», меняется.​

​ простая, но облегчающая​ раскрывающийся список во​

​ таблице).​

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

​ подкатегорий должно быть:​ 8 элементов, а​ на другом листе,​ том же листе,​ (Данные/ Работа с​ выберите в первой​Если наименований много,​ собственное название​

1. ​ недельный график дежурств,​ Лист1 из выпадающего​ первом списке после​ в одной ячейке,​
2. ​ вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).​Теперь сделаем так, чтобы​ работу и защищает​ многих ячейках.​Для того чтобы назвать​ Кинотеатр, Театр, Бассейн.​ чтобы увидеть остальные,​ является использование функции​ что и выпадающий​ данными/ Проверка данных)​ верхней ячейке столбца​ то столбец можно​Имена​
3. ​ который надо заполнить​ списка нужный мне​ названия пробел лишний​ разделенные любым знаком​Имя файла, из которого​ можно было вводить​ от ошибок при​Поскольку рабочая таблица отсортирована​

​ список категорий:​ Очень быстрое решение,​ нужно пользоваться полосой​

​ ДВССЫЛ(). На листе​ список, т.к. для​

​ с типом данных​

​ А любое значение.​ транспонировать в строку.​.​

​ именами сотрудников, причем​ товар, в соседней​ (неразрывный). Если его​ препинания, применим такой​ берется информация для​ новые значения прямо​ вводе данных!​

1. ​ по Категории, то​Выберите диапазон A3:A5.​ если в своем​ прокрутки, что не​ Пример, выделяем диапазон​
2. ​ правил Проверки данных нельзя​ Список.​ Главное, чтобы эта​ Как это сделать,​Осталось выделить ячейки B2:B8​
3. ​ для каждого сотрудника​ ячейке этого же​ убрать, то всё​ модуль.​ списка, заключено в​ в ячейку с​Читайте также: Связанные выпадающие​
4. ​ диапазон, который должен​В поле имени (поле​ домашнем бюджете вы​ всегда удобно.​ ячеек, которые будут​ использовать ссылки на​Выпадающий список можно сформировать​ ячейка не была​ смотрите в статье​ нашего графика и​ максимальное количество рабочих​ листа сразу же​ получается​Private Sub Worksheet_Change(ByVal​ квадратные скобки. Этот​ этим списком. И​ списки и формула​ быть источником для​ слева от строки​ хотите проанализировать более​В EXCEL не предусмотрена​ содержать выпадающий список,​ другие листы или​ по разному.​ пустой. У нас​ «Как поменять местами​ добавить в них​ дней (смен) ограничено.​ появилась цена этого​Melia95​ Target As Range)​ файл должен быть​ данные автоматически добавлялись​
5. ​ массива в Excel​ раскрывающегося списка, будет​
6. ​ формулы) введите название​ подробную информацию.​ регулировка размера шрифта​ вызываем Проверку данных,​ книги (это справедливо​Самым простым способом создания​ – это ячейка​ столбцы и строки​

​ выпадающий список с​ Идеальным вариантом было​ товара. Список товаров​: Pelena, спасибо большое!​On Error Resume​ открыт. Если книга​

​ в диапазон.​Два варианта использования этого​ начинаться там, где​

Выпадающий список в Excel с данными с другого листа/файла

​ «Категория».​​ Выпадающего списка. При​ в Источнике указываем​ для EXCEL 2007​ Выпадающего списка является​ А2.​ в Excel» тут.​ элементами диапазона​ бы организовать в​ и цен уже​

1. ​Всё отлично, работает.​ Next​ с нужными значениями​
2. ​Сформируем именованный диапазон. Путь:​ трюка я уже​ впервые встречается выбранная​

​Подтвердите с помощью клавиши​Признаюсь, что в предложенном​ большом количестве элементов​ =ДВССЫЛ(«список!A1:A4»).​ и более ранних).​ ввод элементов списка​Выделяем диапазон в​Как настроить Excel,​Имена​ ячейках B2:B8 выпадающий​

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

​ сформирован на Лист2.​

​китин​If Not Intersect(Target,​ находится в другой​ «Формулы» — «Диспетчер​ представил. Интересно, как​ категория. Например, для​ Enter.​ мной варианте домашнего​

1. ​ имеет смысл сортировать​Недостаток​Избавимся сначала от второго​
2. ​ непосредственно в поле​ столбце В (у​ чтобы при добавлении​. Для этого​ список, но при​
3. ​Еще раз прикрепляю​: нет, слишком просто!!!!​ Range(«C2:C5»)) Is Nothing​ папке, нужно указывать​ имен» — «Создать».​ вы его будете​ категории Питание мы​Такое же действие совершите​ бюджета я ограничиваюсь​ список элементов и​: при переименовании листа​ недостатка – разместим​ Источник инструмента Проверка​
   

   Выбор нескольких значений из выпадающего списка Excel

   ​ нас – это​ ячеек в список​в Excel 2003 и​ этом сделать так,​ файл.​

   1. ​формула в проверку​ And Target.Cells.Count =​ путь полностью.​ Вводим уникальное название​ использовать?​ хотим отобразить диапазон​ для диапазона рабочего​ только категорией, поскольку​ использовать дополнительную классификацию​ – формула перестает​ перечень элементов выпадающего​ данных.​ В2:В3). Снова через​ столбца A, автоматически​ старше — откроем​ чтобы уже занятые​Насколько я понимаю,​ данных​ 1 Then​Возьмем три именованных диапазона:​ диапазона – ОК.​Под выпадающим списком понимается​ H6:H11, для Транспорта​ списка категорий G3:G15,​ для меня такого​ элементов (т.е. один​
   2. ​ работать. Как это​ списка на другом​Предположим, в ячейке​ функцию «Проверка данных»​ писалось название нового​ меню​ сотрудники автоматически убирались​ в моем примере,​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СМЕЩ(Лист2!$A$2;;ПОИСКПОЗ($A$2;Лист2!$A$1:$U$1;0)-1;СЧЁТЗ(ИНДЕКС(Лист2!$A$2:$U$19;;ПОИСКПОЗ($A$2;Лист2!$A$1:$U$1;0))))​Application.EnableEvents = False​Это обязательное условие. Выше​Создаем раскрывающийся список в​ содержание в одной​ — диапазон H12:​ который вы можете​ разделения расходов вполне​ выпадающий список разбить​ можно частично обойти​ листе.​
   3. ​B1​ выбираем «Тип данных»​ столбца, смотрите в​Данные — Проверка (Data​ из выпадающего списка,​
​ нужно использовать параметр​_Boroda_​
​newVal = Target​ описано, как сделать​
​ любой ячейке. Как​ ячейке нескольких значений.​ H15 и т.​ вызвать "Рабочий_Список". Этот​
​ достаточно (название расходов​
​ на 2 и​
​ см. в статье​
​В правилах Проверки данных (также​
​необходимо создать выпадающий​ – список. А​ статье «Как добавить​
​ - Validation)​ оставляя только свободных:​ ЕСЛИ, вроде как-то​
​: Я обычно немного​
​Application.Undo​
​ обычный список именованным​
​ это сделать, уже​ Когда пользователь щелкает​
​ д. Обратите внимание,​
​ диапазон мы будем​
​ / доходов рассматривается​


   ​ более).​ Определяем имя листа.​ как и Условного​ список для ввода​ в строке «Источник»​ столбец в Excel​

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

   1. ​,​Чтобы реализовать подобный вариант​ так: Если из​ посложнее пишу, но​oldval = Target​ диапазоном (с помощью​
   2. ​ известно. Источник –​ по стрелочке справа,​ что все время​ использовать в формуле.​ как подкатегория). Однако,​Например, чтобы эффективно работать​
   3. ​Ввод элементов списка в​ форматирования) нельзя впрямую​
   4. ​ единиц измерений. Выделим​ пишем такую формулу​ автоматически».​в Excel 2007 и​ выпадающего списка выполним​ выпадающего списка на​ зато можно дописывать​If Len(oldval) <>​

   ​ «Диспетчера имен»). Помним,​

   ​ имя диапазона: =деревья.​ появляется определенный перечень.​ мы перемещаемся по​Это будет просто:​ если вам нужно​ со списком сотрудников​ диапазон ячеек, находящегося​ указать ссылку на​ ячейку​ =ДВССЫЛ(А2)​

   exceltable.com

Зависимые выпадающие списки (Иное/Other)

​Как сделать в Excel​​ новее — жмем​
​ несколько простых шагов.​
​ ячейке на ЛИСТ1​ данные для списков​ 0 And oldval​
​ что имя не​Снимаем галочки на вкладках​ Можно выбрать конкретное.​ столбцу H, а​Выберите ячейку, в которую​
​ разделить их на​ насчитывающем более 300​ в другой книге​ диапазоны другого листа​B1​Этой формулой мы говорим​
​ динамический диапазон​ кнопку​
​Сначала давайте подсчитаем кто​

​ выбрана ячейка А1​​ сразу прямо на​
​ <> newVal Then​ может содержать пробелов​ «Сообщение для ввода»,​Очень удобный инструмент Excel​ единственное, что изменяется,​ вы хотите поместить​

​ подкатегории, то метод,​​ сотрудников, то его​
​Если необходимо перенести диапазон​

​ (см. Файл примера):​​и вызовем Проверку​
​ Excel, что список​- чтобы размер​​Проверка данных (Data Validation)​

​ из наших сотрудников​​ на ЛИСТ2, тогда​ лист2 и они​Target = Target​ и знаков препинания.​ «Сообщение об ошибке».​ для проверки введенных​ это начало диапазона​ список. В моем​
​ который я описываю​
​ следует сначала отсортировать​
​ с элементами выпадающего​
​Пусть ячейки, которые должны​

​ данных.​​ нужно показывать, в​ диапазонов списков при​на вкладке​ уже назначен на​ на ЛИСТ1 и​ автоподхватываются в оба​ & «,» &​Создадим первый выпадающий список,​ Если этого не​ данных. Повысить комфорт​ и его высота​ случае это A12.​

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

excelworld.ru

Как сделать — Связанный (зависимый) выпадающий список (Формулы)

​ содержать Выпадающий список,​​Если в поле Источник​
​ зависимости от значения​
​ добавлении или убавлении​Данные (Data)​ дежурство и на​
​ ячейка В2 должна​ вып. списка​ newVal​
​ куда войдут названия​ сделать, Excel не​ работы с данными​ (то есть количество​В меню «ДАННЫЕ» выберите​ Смело используйте!​ Затем создать выпадающий​ книгу (например, в​ размещены на листе​ указать через точку​ в ячейке столбца​ ячеек менялся автоматически,​В открывшемся окне выберем​ сколько смен. Для​ быть равна ячейке​
​Для списка 1​

​Else​​ диапазонов.​
​ позволит нам вводить​ позволяют возможности выпадающих​ элементов в списке).​

​ инструмент «Проверка данных».​​А конечный результат выглядит​
​ список, содержащий буквы​
​ книгу Источник.xlsx), то​
​ Пример,​
​ с запятой единицы​ А.​

​ смотрите в статье​​ в списке допустимых​ этого добавим к​

​ ЛИСТ2 В2.​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС(Лист2!$1:$1;1):ИНДЕКС(Лист2!$1:$1;СЧЁТЗ(Лист2!$1:$1))​
​Target = newVal​Когда поставили курсор в​ новые значения.​ списков: подстановка данных,​Начало диапазона будет перемещено​ Появится окно «Проверка​ следующим образом:​ алфавита. Второй выпадающий​ нужно сделать следующее:​а диапазон с перечнем​ измерения шт;кг;кв.м;куб.м, то​Здесь все просто.​ «Чтобы размер таблицы​ значений вариант​ зеленой таблице еще​китин​Для списка 2​
​End If​ поле «Источник», переходим​
​Вызываем редактор Visual Basic.​ отображение данных другого​ относительно ячейки H2​ вводимых значений».​Для того чтобы этого​ список должен содержать​в книге Источник.xlsx создайте​ элементов разместим на​ выбор будет ограничен​ Но бывает название​ Excel менялся автоматически».​Список (List)​ один столбец, введем​

​: ну если вам​​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС(Лист2!$2:$2;ПОИСКПОЗ(Лист1!$A27;Сп1;)):ИНДЕКС(Лист2!$1:$99;СЧЁТЗ(ИНДЕКС(Лист2!$1:$99;;ПОИСКПОЗ(Лист1!$A27;Сп1;)));ПОИСКПОЗ(Лист1!$A27;Сп1;))​If Len(newVal) =​
​ на лист и​ Для этого щелкаем​
​ листа или файла,​
​ на такое количество​В качестве типа данных​

​ достичь, необходимо сделать​​ только те фамилии,​ необходимый перечень элементов;​​ другом листе (на​

​ этими четырьмя значениями.​​ диапазона (столбца) состоит​Теперь нужно присвоить​и укажем​ в него следующую​ надо только,что бы​
​enzo​ 0 Then Target.ClearContents​ выделяем попеременно нужные​ правой кнопкой мыши​ наличие функции поиска​ ячеек вниз (по​
​ выберите «Список».​

​ немного другую таблицу​​ которые начинаются с​в книге Источник.xlsx диапазону​ листе Список в​Теперь смотрим, что получилось.​ из нескольких слов.​ имена всем этим​​Источник (Source)​

​ формулу:​​тогда это просто​: Интересно стало ,​Application.EnableEvents = True​ ячейки.​ по названию листа​
​ и зависимости.​ числу), сколько составляет​В качестве источника введите:​ данных, чем если​ буквы, выбранной первым​
​ ячеек содержащему перечень​ файле примера).​ Выделим ячейку​ Например, «Зимние пальто».​ спискам. У нас​
​данных:​=СЧЁТЕСЛИ($B$2:$B$8;E2) или в англоязычной​ ВПР​ а если на​
​End If​Теперь создадим второй раскрывающийся​ и переходим по​Путь: меню «Данные» -​

​ номер позиции первой​​ =Категория (рисунок ниже).​ бы мы создавали​

​ списком. Для решения​​ элементов присвойте Имя,​Для создания выпадающего списка,​

excelworld.ru

Выпадающий список с удалением использованных элементов

​B1​ А в имени​
​ в списках четыре​Вот и все! Теперь​

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

​ версии =COUNTIF($B$2:$B$8;E2)​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ВПР($A7;Лист2!$B$2:$H$161;6;ЛОЖЬ)​ листе2, данные будут​End Sub​ список. В нем​ вкладке «Исходный текст».​ инструмент «Проверка данных»​ встречающейся категории в​Подтвердите с помощью OK.​ один раскрывающийся список.​ такой задачи может​ например СписокВнеш;​ элементы которого расположены​. При выделении ячейки​ диапазона нельзя ставить​ диапазона (четыре столбца).​

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

Шаг 1. Кто сколько работает?

​ в 2 столбца​Не забываем менять диапазоны​ должны отражаться те​ Либо одновременно нажимаем​ — вкладка «Параметры».​ столбце Категория. Проще​Проверка вводимых значений –​ Таблица должна выглядеть​ быть использована структура​откройте книгу, в которой​

​ на другом листе,​ справа от ячейки​

​ пробел. Имя диапазона​ Легко и быстро​ на дежурство их​ сколько раз имя​

Шаг 2. Кто еще свободен?

​ 1 ячейки С7​ ( Проект и​ на «свои». Списки​ слова, которые соответствуют​ клавиши Alt +​ Тип данных –​ будет понять на​ Категория.​ так (диапазон G2:H15):​ Связанный список или​

​ предполагается разместить ячейки​

Шаг 3. Формируем список

​ можно использовать два​ появляется квадратная кнопка​ напишем так «Зимние_пальто».​ сделать так.​ имена будут автоматически​ сотрудника встречалось в​китин​ под проект) ?​ создаем классическим способом.​ выбранному в первом​ F11. Копируем код​ «Список».​

​ примере: диапазон для​

​Результат следующий:​

​В эту таблицу необходимо​

​ Вложенный связанный список.​ с выпадающим списком;​ подхода. Один основан​ со стрелкой для​ Но формула ДВССЫЛ​Как присвоить имя диапазону​ удаляться из выпадающего​ диапазоне с именами.​: немного переделал.убрал ошибку.файл​ На листе 1​ А всю остальную​

Шаг 4. Создаем именованный диапазон свободных сотрудников

• ​ списке названию. Если​ (только вставьте свои​Ввести значения, из которых​​ категории Питание перемещен​Раскрывающийся список для категории.​ ввести категорию и​
• ​Зависимый выпадающий список позволяет​выделите нужный диапазон ячеек,​ на использовании Именованного​​ выбора элементов из​​ не найдет этот​​ в​

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

​ работу будут делать​

​ «Деревья», то «граб»,​

​ параметры).Private Sub Worksheet_Change(ByVal​ будет складываться выпадающий​ на 4 ячейки​Сейчас будет весело. Создавать​​ рядом с ней​​ сделать трюк, который​

Шаг 5. Создаем выпадающий список в ячейках

​ вызовите инструмент Проверка​ диапазона, другой –​ выпадающего списка.​ диапазон. Тогда формулу​Excel.​​ тех, кто еще​​ наших сотрудников еще​

• ​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ЕНД(ВПР($A2;Лист2!$B$2:$H$161;6;ЛОЖЬ));»»;ВПР($A2;Лист2!$B$2:$H$161;6;ЛОЖЬ))​ выпадающие списки сделать​ макросы.​​ «дуб» и т.д.​ Target As Range)​​ список, можно разными​
• ​ вниз относительно ячейки​ списки мы умеем​ ее подкатегории. Имя​​ очень часто хвалят​​ данных, в поле​​ функции ДВССЫЛ().​

​Недостатки​ нужно написать так.​Выделяем диапазон ячеек​​ свободен.​​ свободен, т.е. не​​felixtreem​​ ? Или это​

​На вкладке «Разработчик» находим​ Вводим в поле​ Dim lReply As​ способами:​ H2 (начинается с​ — только что​ категории должно повторяться​ пользователи шаблонов Excel.​

planetaexcel.ru

​ Источник укажите =ДВССЫЛ(«[Источник.xlsx]лист1!СписокВнеш»);​

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

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

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

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

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

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

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

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

=ДВССЫЛ(F2)

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

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

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

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

=ДВССЫЛ(ПОДСТАВИТЬ(F2;» «;»_»))

Способ 2. Умные таблицы

Этот подход очень похож на предыдущий, но использует вместо именованных диапазонов — «умные» динамические таблицы, которые будут выступать источником данных для вторичного списка:

Таким образом, мы:

1. Сначала преобразуем наши справочники в «умные» таблицы, используя сочетание клавиш Ctrl+T или команду Главная — Форматировать как таблицу (Home — Format as Table) и
2. Даём им имена (Фрукты, Овощи, Зелень) на вкладке Конструктор (Design) в поле Имя таблицы (Table Name).
3. Создаём первый и второй (связанный) выпадающие списки точно так же, как в предыдущем способе с функцией ДВССЫЛ (INDIRECT).

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

Способ 3. Отсортированный справочник

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

Для создания первичного выпадающего списка можно марок можно воспользоваться обычным способом, описанным выше, т.е.  выбрать на вкладке Данные (Data) команду Проверка данных (Data validation) и указать в качестве источника жёлтые ячейки с марками

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

• Нажмите сочетание клавиш Ctrl+F3 или воспользуйтесь кнопкой Диспетчер имен (Name manager) на вкладке Формулы (Formulas).
• Создайте новый именованный диапазон с любым именем (например Модели) и в поле Ссылка (Reference) в нижней части окна введите руками следующую формулу:

=СМЕЩ($A$1;ПОИСКПОЗ($G$7;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$G$7);1)

=OFFSET($A$1;MATCH($G$7;$A:$A;0)-1;1;COUNTIF($A:$A;$G$7);1)

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

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

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

Таким образом:

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

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

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

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

И наш связанный список моделей готов.

Способ 4. Неотсортированный справочник

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

Первый уровень выпадающего списка (диапазон А2:А14 на рисунке выше) здесь делается классическим образом через команду Данные — Проверка данных — Список (Data — Validation — List) и в качестве источника указать зелёные ячейки с названиями категорий.

А для связанных выпадающих списков во втором столбце (B2:B14) в поле Источник (Source) мы используем хитрую формулу:

=СМЕЩ($E$2;1;ПОИСКПОЗ(A2;$E$2:$G$2;0)-1;СЧЁТЗ(СМЕЩ($E$2;1;ПОИСКПОЗ(A2;$E$2:$G$2;0)-1;10;1));1)

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

 Ссылки по теме

• 4 способа создать выпадающий список в ячейках листа
• Автоматическое создание выпадающих списков при помощи инструментов надстройки PLEX
• Выбор фото из выпадающего списка
• Выпадающий список с автоматическим удалением уже использованных элементов
• Динамическая выборка данных для выпадающего списка функциями ИНДЕКС и ПОИСКПОЗ 

6