Как создать раскрывающийся список в excel

Определение стилей выпадающего списка с помощью CSS

Теперь рассмотрим, как определить стили выпадающего списка <select>, используя возможности CSS. В следующей демо-версии я использовал несколько простых свойств CSS и свойства градиента CSS3:

Посмотреть онлайн демо-версию и код

Вместе с linear-gradient здесь использовано свойство box-shadow. Полный код CSS выглядит следующим образом:

.selcls {

padding: 3px;

border: solid 1px #517B97;

outline: 0;

background: -webkit-gradient(linear, left top, left 25, from(#FFFFFF), color-stop(4%, #CAD9E3), to(#FFFFFF));

background: -moz-linear-gradient(top, #FFFFFF, #CAD9E3 1px, #FFFFFF 25px);

box-shadow: rgba(0,0,0, 0.1) 0px 0px 8px;

-moz-box-shadow: rgba(0,0,0, 0.1) 0px 0px 8px;

-webkit-box-shadow: rgba(0,0,0, 0.1) 0px 0px 8px;

width:150px;

}

2 Использование меню

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

1. Выписать в столбик данные, которые будут использоваться в списке. Это может быть, например, количество ядер и потоков для процессора. 
2. Выбрать ячейку, в которой будет находиться нужный объект. 
3. Перейти на вкладку «Данные». 
4. Найти и выбрать функцию «Проверка данных». 
5. Выбрать в поле типа данных пункт «Список». 
6. Указать в источнике адреса уже введенных значений. Или ввести их самостоятельно, через запятую. В этом случае экономится место в таблице, но повышается риск сделать ошибку еще на этапе создания списка. И изменять значения будет сложнее. 

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

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

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

1. Во вспомогательной таблице пишем перечень всех наименований – каждый с новой строки в отдельной ячейке. В итоге должен получиться один столбец с заполненными данными.
2. Затем отмечаем все эти ячейки, нажимаем в любом месте отмеченного диапазона правой кнопкой мыши и в открывшемся списке кликаем по функции “Присвоить имя..”.
3. На экране появится окно “Создание имени”. Называем список так, как хочется, но с условием – первым символом должна быть буква, также не допускается использование определенных символов. Здесь же предусмотрена возможность добавления списку примечания в соответствующем текстовом поле. По готовности нажимаем OK.
4. Переключаемся во вкладку “Данные” в основном окне программы. Отмечаем группу ячеек, для которых хотим задать выбор из нашего списка и нажимаем на значок “Проверка данных” в подразделе “Работа с данными”.
5. На экране появится окно “Проверка вводимых значений”. Находясь во вкладке “Параметры” в типе данных останавливаемся на опции “Список”. В текстовом поле “Источник” пишем знак “равно” (“=”) и название только что созданного списка. В нашем случае – “=Наименование”. Нажимаем OK.
6. Все готово. Справа от каждой ячейки выбранного диапазона появится небольшой значок со стрелкой вниз, нажав на которую можно открыть перечень наименований, который мы заранее составили. Щелкнув по нужному варианту из списка, он сразу же будет вставлен в ячейку. Кроме того, значение в ячейке теперь может соответствовать только наименованию из списка, что исключит любые возможные опечатки.

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

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

24

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

1. Создать 1-й перечень с именами диапазонов.
2. В месте ввода источника один за одним выделяются требуемые показатели.
3. Создать 2-й перечень, зависящий от типа растений, который предпочел человек. Как вариант, если в первом указать деревья, то информацией во втором списке станет «дуб, граб, каштан» и дальше. Необходимо записать в месте ввода источника данных формулу =ДВССЫЛ(E3). E3 – ячейка содержащая название диапазона 1.=ДВССЫЛ(E3). E3 – ячейка с наименованием списка 1.

Теперь все готово.

27

Двухуровневый выпадающий список в Excel

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

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

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

Осталось создать второй зависимый выпадающий список – список подгрупп.

Смело повторяем 4 первых пункта описанных выше. Источником в окне Проверка данных для второго выпадающего списка будет служить формула =ДВССЫЛ(«Таблица1»). Ячейка F2 в данном случае — значение первого выпадающего списка.

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

Второй способ создания двухуровнего списка

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

ВАЖНО! Перед созданием зависимого списка по подгруппам необходимо отсортировать исходную таблицу по первому столбцу (столбец с группой) далее будет понятно зачем это делается

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

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

Рекомендация: удобно в качестве источника указывать именованный диапазон. Для его создания откройте Диспетчер имен со вкладки Формулы и присвойте имя диапазону с уникальными значениями.

Теперь самая сложная часть — указать в Источнике динамическую ссылку на диапазон со значениями второго выпадающего списка (списка подгрупп). Решать ее будем с помощью функции СМЕЩ(ссылка, смещ_по_строкам, смещ_по_столбцам, , ), которая возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов.

• Ссылка в нашем случае — $A$1 — верхний левый угол исходной таблицы;
• Смещ_по_строкам — ПОИСКПОЗ(F3;$A$1:$A$67;0)-1 — номер строки со значением искомой группы (в моем случае страны ячейка F3) минус единица;
• Cмещ_по_столбцам — 1 — так как нам необходим столбец с подгруппами (городами);
• — СЧЁТЕСЛИ($A$1:$A$67;F3) — количество подгрупп в искомой группе (количество городов в стране F3);
• — 1 — так как это ширина нашего столбца с подгруппами.

Связанный выпадающий список по нескольким условиям.

​ с типом данных​​ использовать подручные инструменты​D1 проверка данных:​​ другой гост, то​ на листе “Отводы”,​ промежуточный столбец существует,​Дело в том, что​ это не касается!​ пришел в голову​ три», они выбираются​ может быть не​​ функцию «Проверка данных»​

​Теперь нужно присвоить​​раскрывающийся список Excel в​​Если нет желания присваивать​​А1:А10​. Но, тогда выпадающий​ Список.​С помощью макроса​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=Если(C1<>»»;Двссыл(A1))​

​ в ячейке B3​​ в столбце “A”.​ и вы увидите,​ эти списки не​​Г: ?! (бьёт​

​ как решение задачи​​ с выпадающего списка​ заполнено?​ выбираем «Тип данных»​

​ имена всем этим​​ ячейке​ имя диапазону в​

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

​ через «проверку данных».​​vikttur​ – список. А​ спискам. У нас​. Есть​ файле Источник.xlsx, то​​ случае Выпадающий список​ пустые строки (если,​ по разному.​ быстро удалять/вставлять формулы​: А поподробнее можно?​

​ другой список, отличный​​ листе “Отводы”, в​ ;-)​ записей по Типу​Г: Что значит​Само ограничение выглядит​В ячейке B1​: Поправка — «Е15»,​ в строке «Источник»​ в списках четыре​многоуровневые зависимые выпадающие списки​ формулу нужно изменить​ может содержать пустые​ например, часть элементов​

​Самым простым способом создания​​ и списки из​ABC​ от B2 Прикрепленные​ столбце “B” список​​Для определения положения Легковой​

​ и Производителю, находящихся​​ не касается?! ПОЧЕМУ​ так: =ЕСЛИ(ЕТЕКСТ(A1);ЗАМЕНИТЬ(A1;1;255;»текст»);ЕСЛИ(И(A1>1000;A1 Только​ необходимо организовать выпадающий​

​ конечно же.​​ пишем такую формулу​ диапазона (четыре столбца).​​ в Excel​ на =ДВССЫЛ(«лист1!$A$1:$A$4»)​ строки.​ была удалена или​ Выпадающего списка является​ одной и той​: проверка данных проверяет​

​ файлы скрин1.jpg (24.81​​ тех отводов, которые​ Fiat, мы, конечно,​

​ в списке Моделей.​​ НЕ ЧИЩЕНЫ?!​ замена не работает​​ список по условию​Spare​ =ДВССЫЛ(А2)​ Легко и быстро​

planetaexcel.ru>

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

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

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

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

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

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

Способ 2 — самый удобный, простой и наиболее гибкий

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

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

Создание набора данных для списка

Введите имя для набора данных

Создать выпадающий список можно сразу для нескольких ячеек

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

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

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

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

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

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

Выделяем диапазон для выпадающего списка. В главном меню находим инструмент «Форматировать как таблицу».
Откроются стили. Выбираем любой. Для решения нашей задачи дизайн не имеет значения

Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья»

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

Протестируем. Вот наша таблица со списком на одном листе:

Добавим в таблицу новое значение «елка».

Теперь удалим значение «береза».

Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.

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

1. Сформируем именованный диапазон. Путь: «Формулы» – «Диспетчер имен» – «Создать». Вводим уникальное название диапазона – ОК.
2. Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
3. Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.
4. Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).
5. Сохраняем, установив тип файла «с поддержкой макросов».
6. Переходим на лист со списком. Вкладка «Разработчик» – «Код» – «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».

Нажмем «Да» и добавиться еще одна строка со значением «баобаб».

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

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

Предположим, что элементы списка шт;кг;кв.м;куб.м введены в ячейки диапазона A1:A4, тогда поле Источник будет содержать =лист1!$A$1:$A$4

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

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

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

Выпадающий список с условием на значение

​ имен), может ругаться​​gling​​: Зачем такие сложности?​ПОИСКПОЗ(B4&C4;F5:F39&G5:G39;0)​ инструмента «Удалить дубликаты»​ гуталина в части​Я правильно понял?​ выпадает свой диапазон​Видимо имеете ввиду​ нужно показывать, в​ в​ столбце выпадающего списка,​​ правилами Проверки данных,​ Динамический диапазон. Для​ необходимо сохранить файл.​ данных.​ ступеньки на другую.​ при ОШИБКЕ.​

​: Вам нужны в​​ Фильтруйте на месте.​Вышеописанное означает, что мы​

​ (например, это показано​​ нет…​Код =ЕСЛИ(ЕЧИСЛО(A1)*((A1>1000)*(A1​ значений для выбора.​ E15, да может​ зависимости от значения​Excel.​ меняется выпадающий список​

​ то можно использовать​​ этого при создании​​Второй недостаток: диапазон источника​Предположим, в ячейке​ Если делать это​al_anya​ ПРИЕМАХ про них​ Ну, если уж​

​ хотим знать позицию​​ в этом видео​Г: А меня​richhamm​Например:​ быть не всегда​ в ячейке столбца​Выделяем диапазон ячеек​ в ячейках второго​​ инструмент Выделение группы​ Имени Список_элементов в​ должен располагаться на​

​B1​​ очень быстро, то​​: ABC, Т.е. мне​​ рассказано и показано​

​ сильно хочется, Сводная​​ Легкового Fiatа (отсюда​ продолжительностью около 2​ это не касается!!!​: Да, все верно.​- для значения​ заполнено.​ А.​ всех списков сразу​ столбца, третьего, т.д.​ ячеек (Главная/ Найти​​ поле Диапазон необходимо​ том же листе,​необходимо создать выпадающий​ со стороны будет​ нужно вместо предыдущей​ как сделать. Рядом​ таблица​ и связь B4&C4).​ минут). Когда мы​​С: Дык я​ Только такое условие​ A1=»один», выпадающий список​Плюс могут быть​Здесь все просто.​

​ вместе с шапкой​​Здесь разберём​ и выделить/ Выделение​

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

​ пункты не подряд,​​ Но бывает название​ таблицы списков –​​двухуровневый зависимый выпадающий список​​ группы ячеек). Опция​

​Использование функции СЧЁТЗ() предполагает,​​ список, т.к. для​ единиц измерений. Выделим​ стоит между ними​​ данных вставить новую?​: , Спасибо большое,​: Извените, не понял,​ воображаемом вспомогательном столбце,​ …​

​ сказал…​​ проверке данных на​

​ будет из вариантов​

​ к примеру 1.1.​​ диапазона (столбца) состоит​ у нас это​ в Excel​ Проверка данных этого​ что заполнение диапазона​​ правил Проверки данных нельзя​​ ячейку​:D​​ Извините, что не​ прочитаю и обязательно​​ что Вы имели​ то есть: F5:F39&G5:G39.​​​Итак, как сделать два​ той ячейке куда​​ «1; 2; 3;»​ потом 3.4.​ из нескольких слов.​ диапазон А1:D4. На​​.​ инструмента позволяет выделить​​ ячеек (​ использовать ссылки на​B1​

CyberForum.ru>

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

В правилах Проверки данных (также как и Условного форматирования) нельзя впрямую указать ссылку на диапазоны другого листа (см. Файл примера ):

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

а диапазон с перечнем элементов разместим на другом листе (на листе Список в файле примера ).

Для создания выпадающего списка, элементы которого расположены на другом листе, можно использовать два подхода. Один основан на использовании Именованного диапазона, другой – функции ДВССЫЛ() .

Используем именованный диапазон Создадим Именованный диапазон Список_элементов, содержащий перечень элементов выпадающего списка (ячейки A1:A4 на листе Список). Для этого:

• выделяем А1:А4,
• нажимаем Формулы/ Определенные имена/ Присвоить имя
• в поле Имя вводим Список_элементов, в поле Область выбираем Книга;

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

• вызываем Проверку данных;
• в поле Источник вводим ссылку на созданное имя: =Список_элементов .

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

Избавиться от пустых строк и учесть новые элементы перечня позволяет Динамический диапазон. Для этого при создании Имени Список_элементов в поле Диапазон необходимо записать формулу = СМЕЩ(Список!$A$1;;;СЧЁТЗ(Список!$A:$A))

Использование функции СЧЁТЗ() предполагает, что заполнение диапазона ячеек (A:A), который содержит элементы, ведется без пропусков строк (см. файл примера , лист Динамический диапазон).

Используем функцию ДВССЫЛ()

Альтернативным способом ссылки на перечень элементов, расположенных на другом листе, является использование функции ДВССЫЛ() . На листе Пример, выделяем диапазон ячеек, которые будут содержать выпадающий список, вызываем Проверку данных, в Источнике указываем =ДВССЫЛ(«список!A1:A4») .

Недостаток: при переименовании листа – формула перестает работать. Как это можно частично обойти см. в статье Определяем имя листа.

Ввод элементов списка в диапазон ячеек, находящегося в другой книге

Если необходимо перенести диапазон с элементами выпадающего списка в другую книгу (например, в книгу Источник.xlsx), то нужно сделать следующее:

• в книге Источник.xlsx создайте необходимый перечень элементов;
• в книге Источник.xlsx диапазону ячеек содержащему перечень элементов присвойте Имя, например СписокВнеш;
• откройте книгу, в которой предполагается разместить ячейки с выпадающим списком;
• выделите нужный диапазон ячеек, вызовите инструмент Проверка данных, в поле Источник укажите = ДВССЫЛ(«лист1!СписокВнеш») ;

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

Если нет желания присваивать имя диапазону в файле Источник.xlsx, то формулу нужно изменить на = ДВССЫЛ(«лист1!$A$1:$A$4»)

СОВЕТ: Если на листе много ячеек с правилами Проверки данных, то можно использовать инструмент Выделение группы ячеек ( Главная/ Найти и выделить/ Выделение группы ячеек ). Опция Проверка данных этого инструмента позволяет выделить ячейки, для которых проводится проверка допустимости данных (заданная с помощью команды Данные/ Работа с данными/ Проверка данных ). При выборе переключателя Всех будут выделены все такие ячейки. При выборе опции Этих же выделяются только те ячейки, для которых установлены те же правила проверки данных, что и для активной ячейки.

Примечание : Если выпадающий список содержит более 25-30 значений, то работать с ним становится неудобно. Выпадающий список одновременно отображает только 8 элементов, а чтобы увидеть остальные, нужно пользоваться полосой прокрутки, что не всегда удобно.

В EXCEL не предусмотрена регулировка размера шрифта Выпадающего списка. При большом количестве элементов имеет смысл сортировать список элементов и использовать дополнительную классификацию элементов (т.е. один выпадающий список разбить на 2 и более).

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

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

​. Это, когда, в​​СОВЕТ:​​Избавиться от пустых строк​ список только что​ ввод элементов списка​ же ячейки. Ну​ только заведенные значении​ КБ) скрин2.jpg (58.04​ удовлетворяют условию ГОСТ.​​ будем использовать функцию​ Вы можете создать​С: Знаете, завхоз​Serge 007​ — на каждое​: Да к сожалению​Этой формулой мы говорим​​ сделать так.​​ зависимости от выбранных​Если на листе​ и учесть новые​ был создан). Чтобы​ непосредственно в поле​​ это как быстро​ (Вашем примере ДВССЫЛ(A1)-дисп.​ КБ)​Sanja​ ПОИСКПОЗ. Смотрите:​​ их с помощью​ не выдал щётку,​: Забавно​ значение ячейки A1,​ поторопился.​​ Excel, что список​Как присвоить имя диапазону​

​ данных в первом​​ много ячеек с​​ элементы перечня позволяет​ пустые строки исчезли​

​ Источник инструмента Проверка​​ прыгать с одной​

CyberForum.ru>

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

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

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

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

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

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

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

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

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

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

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

Обратите внимание на «источник». Указана абсолютная адресация столбца и ячейки (через знаки «$»)

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

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

Ввод данных из списка значений. Часть 1: Выпадающий список в MS EXCEL

​ данных, условное -​ сделать и возможно​ пжл, и можно​ нам нужен один​и указать в​первый выпадающий список​ текст «А1», то​ хотели бы показать​производитель: Fiat, Volkswagen i​ сотрудников, то придется​

​ наш второй раскрывающийся​ тех ячейках, где​ базу данных. На​ кнопки Создать из​нажать кнопку «Создать из​ можно из списка​ кончились.​ на главной, Имена​ ли это в​ будет убить тему.​ столбец с моделями​ качестве​ готов:​ функция выдаст в​ вам что-то более​ Suzuki (Подкатегория) и​

​ вручную модифицировать границы​ список:​ планировали выбирать данные.​ втором листе я​ выделенного фрагмента будет​ выделенного фрагмента» (пункт​ выбрать город, затем​в Excel 2003 и​ — через формулы​ екселе, выпадающий список​

​koyaanisqatsi​В итоге должно получиться​

​Источника (Source)​Теперь создадим второй выпадающий​​ результате ссылку на​​ крутое 😉 ),​

• ​модель: … немножечко их​​ именованного диапазона. Как​​В результате мы получим​
• ​ Выделите ячейку​​ занес список стран,​​ создано имя «Северная_Америка».​ меню Формулы/ Определенные​ из списка всех​ старше идем в​​ и пр.пр.​​ со следующим условием:​​: maverick_77, так ?​​ что-то вроде этого:​=Марки​ список, в котором​ ячейку А1. Если​

​ то мы бы​​ есть 🙂 (Подподкатегория)​​ обойти это неудобство​ два связанных (или​B1​ которые хочу дать​ В этом случае​ имена/ Создать из​ улиц этого города​ меню​

​VLad777​ 0-нет льгот, 1-скидка​gling​

• ​Осталось добавить выпадающий список​или просто выделить​ будут отображаться модели​​ в ячейке лежит​​ искали комбинацию этих​
• ​В то же время​ читайте в статье​ зависимых) выпадающих списка.​
• ​(в ней мы​
• ​ пользователям на выбор​ формула =ДВССЫЛ(A5) работать​

​ выделенного фрагмента);​

​ – улицу, затем,​Вставка — Имя -​​: 2007 и 2010​​ в оплате 25%,​: Может так? Изменения​ на основе созданной​ ячейки D1:D3 (если​ выбранной в первом​

​ слово «Маша», то​ данных: Легковой Fiat.​ мы имеем следующие​ Динамический диапазон.​ Если мы выбираем​ будем выбирать страну),​ в первом раскрывающемся​ не будет, т.к.​Убедиться, что стоит только​

​ из списка всех​​ Присвоить (Insert -​Главная — Условное​ 2- 50%???? Нужно​ в закрашенных ячейках.​ формулы к ячейке​ они на том​ списке марки. Также​ функция выдаст ссылку​ Однако у нас​ данные:​Теперь создадим Выпадающий список​ страну​ откройте вкладку​

excel2.ru>

Как создать связанный список Excel?

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

Это можно сделать разными методами.

Метод 1: Функция ДВССЫЛ

В первом случае надо применить функцию ДВССЫЛ, способную преобразовывать в ссылку любой текст. Например, если написано A1, то функция вернет ссылку на ячейку, имеющую такой же адрес. Если же в ячейке написан текст «Маша», то эта формула сможет сделать линк на именованный диапазон с таким названием. Проще говоря, она может менять ссылку и название местами.

Предположим, нам надо составить такой перечень моделей марок Toyota, Ford, Nissan.

Модельный ряд Toyota нужно выделить, начиная ячейкой A2 вплоть до конца перечня и назвать его аналогично. Если используется древняя версия Excel, эта функция реализуется через меню «Вставка – Имя – Присвоить». Если версия Excel не самая старая (начиная с 2007), то нужно перейти во вкладку «Формулы», где отыскать «Диспетчер имен». Аналогичная операция проводится и с перечнем модельного ряда других производителей автомобилей.

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

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

Теперь нужно сгенерировать перечень автомобилей. Нужно нажать мышью (левой кнопкой) по пустой ячейке и открыть меню Данные – Проверка для Excel 2003 версии и кнопку «Проверка данных» на вкладке «Данные» для Excel 2007 версии и более современных. Затем в перечне «Тип данных» нужно выбрать Список, и в поле «Источник» написать ячейки с названиями марок.

Теперь следует сформировать второй список, где будут перечисляться машины из модельного ряда. Последовательность действий такая же, но с тем лишь исключением, что в поле Источник нужно указать формулу =ДВССЫЛ(F3). Естественно, в скобках нужно указать адрес подходящей именно в вашем случае ячейки. Все, после того, как вы нажмете ОК, формирование второго перечня будет осуществляться, исходя из данных в первом.

Но такой метод имеет ряд существенных недостатков:

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

Использование функции СМЕЩ и ПОИСКПОЗ

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

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

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

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

1. Надо нажать комбинацию Ctrl + F3. Также можно открыть диспетчер имен и присвоить имя соответствующему диапазону.
2. Создать новый диапазон вместе с функцией СМЕЩ в ссылке и присвоить ему имя. Оно может быть каким-угодно, например, «Модели».

В нашей ситуации формула будет следующей:

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

Важно указывать только абсолютные ссылки. То есть, в них надо указать знак $

После нажатия клавиши Enter, Excel автоматически добавит имена листов. Это не должно вас пугать.

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

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

1. Выделить ячейку G8.
2. Выбрать команду «Проверка данных» на вкладке «Данные».
3. Там, где источник списка, указать имя диапазона: =Модели.

И все!

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

​ могли бы показать​​Другой способ сделать​ через «Проверки данных»​ D) должны полностью​ прокрутки, что не​ в другой книге​ можно использовать два​ маленьких (3-5 значений)​: ОО спасибо большое!!!​Serge_007​haver​

​gling​​ комбинацией клавиш Ctrl​​ В первом списке​​ другой связанный выпадающий​ Только нужна еще​ и меньше 2000.​ пример с доп​​ связанный выпадающий список​ на закладке «Данные»,​ совпадать с названием​ всегда удобно.​​Если необходимо перенести диапазон​

​ подхода. Один основан​​ неизменных списков.​ ))) Сейчас попробую)))​

CyberForum.ru>

​: У Вас в​

• Excel два условия в если
• Excel выделить цветом по условию excel
• Excel двойное условие
• Excel ранг с условием
• Excel суммирование по условию
• Excel сумма по условию
• В excel двойное условие если
• Excel если и несколько условий
• Выделить цветом ячейку в excel по условию
• Логические формулы в excel формула проверки условия если с примерами
• Excel сумма ячеек по условию
• Excel формула список уникальных значений в