Как сделать динамический выпадающий список в excel?

Как сделать динамический выпадающий список в excel?

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

Функционал

Поддержка любого уровня вложенности

Обеспечение целостности данных. Вы не можете изменить уровень N, если уже определили уровень N+1 и выше. Иными словами, если вы выбрали фрукт яблоки , а затем сорт яблок — Антоновка , то, не очистив ячейку с Антоновкой , вы не измените яблоки на, скажем, груши .

Преимущества

Простота инфраструктуры решения

Удобное ведение справочников

Ни строчки VBA кода. Вы можете использовать файлы типа XLSX

Файл примера

Скачать пример

Скачать пустой шаблон

Обязательные условия для работы решения

Отдельная и единственная умная таблица для ведения списков. Каждый столбец содержит все элементы одного выпадающего списка. В нашем примере она имеет имя tblLists .

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

Единственный именованный диапазон ( SubList ), который и выполняет всю работу.

В качестве источника строк для выпадающего списка используется вышеупомянутый SubList . При чём во всех ячейках всех уровней! Это очень удобно.

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

Крайняя правая колонка после таблицы tblEntry не должна использоваться, так как формулы проверяют ячейку справа и по её заполненности делают вывод о возможности ввода значения (если заполнена, то список возвращается пустым).

Как, чёрт побери, это работает?!

Повторюсь, что все делает формула в ИД SubList . Формула, на первый взгляд, выглядит страшновато, но давайте попробуем разобраться. Кстати, если вы не чувствуете пока в себе сил вникать в нюансы работы таких формул, то и не надо. Решение в любом случае готово к употреблению.

Всё относительно

Первое, что вы должны знать об этой формуле, это то, что она относительная. Она содержит относительные ссылки, поэтому, если вы встанете на ячейку B3 листа Smart (так называется лист, содержащий таблицу tblEntry ) и посмотрите на формулу в ИД SubList , то она будет выглядеть так:

=ЕСЛИ( ЕПУСТО( Smart! C3 ); ЕСЛИ( tblEntry[#Заголовки] Smart! B:B = tblEntry[[#Заголовки];[Главная категория]]; СМЕЩ( tblLists[[#Все];[Основная]]; 1; ; СЧЁТЗ(tblLists[Основная])); СМЕЩ( tblLists; 0; ПОИСКПОЗ(Smart! A3 ; tblLists[#Заголовки];0)-1; СЧЁТЗ( СМЕЩ( tblLists; ; ПОИСКПОЗ( Smart! A3 ; tblLists[#Заголовки];0)-1; ; 1) ); 1) ) )

а, если перейдёте в C5 и ещё раз посмотрите, то увидите уже это:

=ЕСЛИ( ЕПУСТО( Smart! D5 ); ЕСЛИ( tblEntry[#Заголовки] Smart! C:C = tblEntry[[#Заголовки];[Главная категория]]; СМЕЩ( tblLists[[#Все];[Основная]]; 1; ; СЧЁТЗ(tblLists[Основная])); СМЕЩ( tblLists; 0; ПОИСКПОЗ(Smart! B5 ; tblLists[#Заголовки];0)-1; СЧЁТЗ( СМЕЩ( tblLists; ; ПОИСКПОЗ( Smart! B5 ; tblLists[#Заголовки];0)-1; ; 1) ); 1) ) )

Декомпозиция

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

Обратите внимание на первую, внешнюю формулу ЕСЛИ . У ней только 2 параметра: условие — блок 1 на рисунке, и параметр, возвращаемый в случае истинности блока 1 (большой блок 2 ), а вот параметра для ложного условия нет вообще.

Блок 1 при помощи функции ЕПУСТО проверяет ячейку справа от текущей и, если она не пустая, то работа формулы на этом прекращается. Она возвращает Null в качестве источника строк для функции Проверка данных и выпадающий список не срабатывает.

Если ячейка справа пуста, то значит необходимо определить, какой список необходимо показать, надо его найти в таблице tblLists и вернуть все его строки. За всё это отвечает блок 2 , также обёрнутый в формулу ЕСЛИ .

Параметр-условие формулы ЕСЛИ ( блок 3 ) определяет имя столбца таблицы tblEntry над активной ячейкой. Определяет через операцию пересечения диапазонов (символ пробела) при помощи следующей конструкции tblEntry [#Заголовки] Smart ! B:B . Результатом этой операции для B3 становится ячейка B2 . Далее идёт выяснение того, является ли этот заголовок заголовком корневого списка. Если это так (а для B3 это так), то выполняется блок 4 , а если нет, то блок 5 .

Блок 4 формирует диапазон — набор строк для корневого списка. Нам необходимо использовать СМЕЩ и СЧЁТЗ , так как в каждом столбце количество непустых строк может быть разное. Тут всё стандартно: первый параметр задаёт точку отсчёта (не надо пугаться, что точка отсчёта задана диапазоном, так как формула всё равно возьмёт первую ячейку), второй параметр — смещение по строкам (у нас это 1), третий — смещение по столбцам (опущен — у нас ширина массива 1 столбец), четвёртый параметр — число строк (их просто считаем через СЧЁТЗ ), пятый параметр — число столбцов (опущен).

Блок 5 формирует набор строк для случая промежуточного (не корневого) списка. Это означает, что надо взять значение ячейки слева от текущей и искать столбец с таким же именем, а потом действовать примерно так же, как в блоке 4 . Всё это присутствует в блоке 5 : мы видим опять СМЕЩ , первый параметр tblLists — это в качестве точки отсчёта берётся первая ячейка таблицы без заголовка (это Ref ! A2 ), смещение по строкам — ноль, так как мы уже стоим на области данных, смещение по столбцам определяется в блоке 6 , высота диапазона определяется в блоке 7, ширина диапазона равна 1.

Обратите внимание, что блок 6 и блок 8 одинаковые — ведь мы определяем столбец динамически.

Если для вас функции СМЕЩ и СЧЁТЗ не пустой звук, то прочтя 2-3 раза раздел про декомпозицию, вы должно быть поняли, как это всё работает. Если же нет, то не расстраивайтесь, — всему своё время. Почитайте про структурные формулы умных таблиц, описания функций СМЕЩ , СЧЁТЗ , ПОИСКПОЗ и через некоторое время, когда знания улягутся в голове, вернитесь к этому описанию снова.

Данный рецепт подсмотрен мною для вас на сайте известного индийского экселиста Chandoo (Purna Duggirala).

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

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

Читать еще:  Как сделать ячейку в excel по размеру текста?

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

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

Для этого, в ячейки А1:А7 вписываем данные, которые будут отображаться в списке. Теперь выделим ячейку, в которой создадим выпадающий список – В2 .

Переходим на вкладку «Данные» и кликаем по кнопочке «Проверка данных» .

На вкладке «Параметры» в поле «Тип данных» выбираем «Список» . В поле «Источник» можно ввести значения различными способами:

1 – вводим значения для списка вручную, через точку с запятой;

2 – указываем диапазон ячеек, в которые введены данные для выпадающего списка;

3 – выделяем ячейки с именами, кликаем по ним правой кнопкой мыши и выбираем из меню «Присвоить имя» .

Дальше впишите «Имя» для выделенных данных.

Выделяем ячейку В2 и в поле «Источник» ставим «=» , затем пишем созданное имя.

Таким образом, мы создали простой выпадающий список в Excel.

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

Добавляем значения в выпадающий список – динамический список

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

Выделяем диапазон ячеек – D1:D8 , затем на вкладке «Главная» нажимаем «Форматировать как таблицу» и выбираем любой стиль.

Подтверждаем расположение данных и ставим галочку в поле «Таблица с заголовками» .

Вверху пишем заголовок таблицы – «Сотрудники» , и заполняем ее данными.

Выделяем ячейку, в которой будет выпадающий список и кликаем по кнопочке «Проверка данных» . В следующем окне, в поле «Источник» , пишем следующее: =ДВССЫЛ(«Таблица1[Сотрудники]») . У меня одна таблица на листе, поэтому пишу «Таблица1» , если будет вторая – «Таблица2» , и так далее.

Теперь добавим новое имя сотрудника в наш список: Ира. В выпадающем списке оно появилось. Если мы удалим любое имя из таблицы, из списка оно тоже удалится.

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

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

На Листе 2, выделяем одну ячейку или диапазон ячеек, затем кликаем по кнопочке «Проверка данных» .

Переходим на Лист 1, ставим курсор в поле «Источник» и выделяем нужный диапазон ячеек.

Теперь можно дописывать имена на Листе 1, они будут добавляться в выпадающие списки на Листе 2.

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

Предположим, у нас есть три диапазона: имена, фамилии и отчества сотрудников. Для каждого, нужно присвоить имя. Выделяем ячейки оного диапазона, можно и пустые – в них со временем можно будет добавлять данные, которые будут появляться в выпадающем списке. Кликаем по ним правой кнопкой мыши и выбираем из списка «Присвоить имя» .

Первый называем «Имя» , второй – «Фамилия» , третий – «Отч» .

Сделаем еще один диапазон, в котором будут прописаны присвоенные имена. Назовем его «Сотрудники» .

Делаем первый выпадающий список, который будет состоять из названия диапазонов. Выделяем ячейку Е1 и на вкладке «Данные» выбираем «Проверка данных» .

В поле «Тип данных» выберите «Список» , в поле источник – или введите «=Сотрудники» , или выделите диапазон ячеек, которому присвоено имя.

Первый выпадающий список создан. Теперь в ячейке F2 создадим второй список, который должен зависеть от первого. Если в первом выберем «Имя» , во втором отобразится список имен, если выберем «Фамилия» – список фамилий.

Выделяем ячейку и кликаем по кнопочке «Проверка данных» . В поле «Тип данных» выбираем «Список» , в поле источник прописываем следующее: =ДВССЫЛ($Е$1) . Здесь Е1 – это ячейка с первым выпадающим списком.

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

Если в дальнейшем, нужно будет вписать значения в диапазон, которому задано имя, например, «Фамилия» . Перейдите на вкладку «Формулы» и кликните «Диспетчер имен» . Теперь в имени диапазона выбираем «Фамилия» , и внизу, вместо последней ячейки С3 , напишите С10 . Нажмите галочку. После этого диапазон увеличится, и в него можно будет дописывать данные, которые автоматически будут появляться в выпадающем списке.

Теперь Вы знаете, как сделать раскрывающийся список в Excel.

Динамические именованные диапазоны

Очень часто при использовании связки Выпадающий списокИменованный диапазон возникает проблема: при добавлении новых данных в диапазон они не отразятся сразу в списке — для этого необходимо будет изменить адрес именованного диапазона. Либо указать заранее расширенный диапазон. Но тогда появляются лишние пустые значения в списке, что тоже не очень-то красиво и совсем не удобно, особенно если список не маленький. И вот здесь помогут так называемые Динамические диапазоны. Создав вместо обычного именованного диапазона динамический, уже не придется каждый раз менять адрес диапазона для отображения в списке всех добавленных значений. И отображаться будут только значения, никаких пустых строк.
Вызываем Диспетчер создания имен(Ctrl+F3), задаем имя диапазона и в поле Диапазон (Refers to) пишем формулу:

Вариант с формулой СМЕЩ
=СМЕЩ(Лист2! $A$1 ;;;СЧЁТЗ(Лист2! $A$1:$A$1000 );)
=OFFSET(Лист2! $A$1 . COUNTA(Лист2! $A$1:$A$1000 ),)

  • Где Лист2! $A$1 — первая ячейка значений для выпадающего списка
  • а Лист2! $A$1:$A$1000 максимальный диапазон ячеек, в котором будут храниться значения для списка. Если значений может быть более 1000, то необходимо увеличить диапазон $A$1:$A$1000 на необходимое количество строк. Хотя мне лично страшно представить себе такой выпадающий список. Но динамический диапазон может ведь пригодиться и для других целей

В чем главный недостаток этого метода: если добавить в список значений пропуск между значениями в виде пустой строки — список отобразит не все значения списка, т.к. СЧЕТЗ считает количество непустых ячеек:

Вариант с формулой ИНДЕКС
=Лист2!$A$1:ИНДЕКС(Лист2! $A$1:$A$1000 ;ПРОСМОТР(2;1/(Лист2! $A$1:$A$1000 <>«»);СТРОКА(Лист2! $A$1:$A$1000 )))
=Лист2!$A$1:INDEX(Лист2! $A$1:$A$1000 ,LOOKUP(2,1/(Лист2! $A$1:$A$1000 <>«»),ROW(Лист2! $A$1:$A$1000 )))

Изменяемые ссылки для этой формулы такие же, как и в случае с вариантом через СМЕЩ:

  • Лист2! $A$1 — первая ячейка значений для выпадающего списка
  • Лист2! $A$1:$A$1000 максимальный диапазон ячеек, в котором будут храниться значения для списка

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

Читать еще:  Как сделать импорт в 1с из excel в?

Примечание: созданные таким образом диапазоны нельзя использовать в составе функции ДВССЫЛ (INDIRECT) для создания зависимых выпадающих списков — список просто не будет работать

Tips_Lists_Dinamic_Range.xls (37,5 KiB, 6 071 скачиваний)

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

Поиск по меткам

Есть вариант ещё интереснее.
Размещаем список в «Таблице» (ВставкаТаблица)
Создаём Диапазон ссылающийся на часть таблицы с данными например =Таблица1[Почта](если заголовок списка «Почта» в таблице «Таблица1»)
Далее всё как указано в основном описании по созданию списка в третьем варианте отсюда: http://www.excel-vba.ru/chto-umeet-excel/vypadayushhie-spiski/

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

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме — добро пожаловать на Форум

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

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

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

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

Для этого, в ячейки А1:А7 вписываем данные, которые будут отображаться в списке. В данный момент выделим ячейку, в которой создадим выпадающий список – В2.

Переходим во вкладку «Данные» и щёлкаем по кнопочке «Проверка данных».

На вкладке «Параметры» в поле «Тип данных» выбираем «Список». В поле «Источник» можно ввести значения разными способами:

1 – вводим значения для списка вручную, через точку с запятой;

2 – указываем диапазон ячеек, в которые введены данные для раскрывающегося списка;

3 – выделяем ячейки с именами, щёлкаем по ним правой клавишей мышки и выбираем из меню «Присвоить имя».

Дальше впишите «Имя» для выделенных данных.

Выделяем ячейку В2 и в поле «Источник» ставим «=», затем пишем созданное имя.

Тем самым, мы создали простой выпадающий список в Excel.

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

Добавляем значения в выпадающий список – динамический список

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

Выделяем диапазон ячеек – D1:D8, затем на вкладке «Главная» щёлкаем «Форматировать как таблицу» и выбираем любой стиль.

Подтверждаем расположение данных и ставим галочку в поле «Таблица с заголовками».

Вверху пишем заголовок таблицы – «Сотрудники», и заполняем ее данными.

Выделяем ячейку, в которой будет раскрывающийся список и щёлкаем по кнопочке «Проверка данных». В следующем окошке, в поле «Источник», пишем следующее: =ДВССЫЛ(«Таблица1[Сотрудники]»). У меня одна таблица на листе, так что пишу «Таблица1», если будет вторая – «Таблица2», и так далее.

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

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

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

На Листе 2, выделяем одну ячейку или диапазон ячеек, затем щёлкаем по кнопочке «Проверка данных».

Переходим на Лист 1, ставим курсор в поле «Источник» и выделяем нужный диапазон ячеек.

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

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

Допустим, у нас есть три диапазона: имена, фамилии и отчества сотрудников. Для каждого, надо присвоить имя. Выделяем ячейки оного диапазона, можно и пустые – в них со временем можно будет добавлять данные, которые будут появляться в выпадающем списке. Кликаем по ним правой клавишей мышки и выбираем из списка «Присвоить имя».

Первый называем «Имя», второй – «Фамилия», третий – «Отч».

Сделаем еще один диапазон, в котором будут прописаны присвоенные имена. Назовем его «Сотрудники».

Делаем первый выпадающий список, который будет состоять из названия диапазонов. Выделяем ячейку Е1 и на вкладке «Данные» выбираем «Проверка данных».

В поле «Тип данных» выберите «Список», в поле источник – или введите «=Сотрудники», или выделите диапазон ячеек, которому присвоено имя.

Первый выпадающий список создан. В данный момент в ячейке F2 создадим второй список, который должен зависеть от первого. Если в первом выберем «Имя», во втором отобразится список имен, если выберем «Фамилия» – список фамилий.

Выделяем ячейку и щёлкаем по кнопочке «Проверка данных». В поле «Тип данных» выбираем «Список», в поле источник прописываем следующее: =ДВССЫЛ($Е$1). Тут Е1 – это ячейка с первым выпадающим списком.

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

Если в будущем, надо будет вписать значения в диапазон, которому задано имя, к примеру, «Фамилия». Перейдите во вкладку «Формулы» и кликните «Диспетчер имен». Затем в имени диапазона выбираем «Фамилия», и внизу, вместо последней ячейки С3, напишите С10. Нажмите галочку. После чего диапазон увеличится, и в него можно будет дописывать данные, которые автоматически будут появляться в выпадающем списке.

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

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

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

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

Читать еще:  Как сделать несколько строчек в ячейке excel?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ссылка на основную публикацию
Adblock
detector