Содержание
- Как сделать выпадающий список в Excel
- Создание списка посредством контекстного меню
- Использование инструментов от разработчика
- Выпадающий список в ячейке листа
- Выпадающий список в Excel с поиском
- Как создать выпадающий список в excel. Самый простой способ
- Выпадающие списки в Excel (быстро и надежно)
- Выпадающий список в ячейке листа
• для начала создается один, общий список для всех названий. Выделяются ячейки с наименованиями посредством контекстного меню;
Как сделать выпадающий список в Excel
Пользователи, которые ведут свою базу данных, нередко встречаются с необходимостью выбрать определенные ячейки из подготовленного списка. Если сделать выпадающий список в эксель, пользователь сможет создать несколько значений в одной ячейке, что позволяет организовать быструю проверку данных, а также создать комфортные условия работы с переменными данными, сделать отображение другого листа или включить поисковую функцию. Существует несколько способов, как решить поставленную задачу.
Создание списка посредством контекстного меню
Считается самым простым и понятным методом, когда нужно сделать выпадающий список в экселе в другом месте текущего документа: на новом листе, рядом с таблицами. Это позволяет структурировать информацию.
Инструкция проста и состоит из нескольких шагов:
• в дополнительной таблице фиксируем наименования (каждое с новой строчки в отдельной ячейке), чтобы получился один столбец;
• далее нужно выделить ячейки, кликнуть правой кнопкой мыши в любом выделенном месте и выбрать из выпавшего списка функцию «Присвоить имя»;
• должно появиться окно с созданием имени. Пользователь может назвать список по собственному желанию, но первый символ обязательно должна быть буква. Также не допускается использование определенных символов. При необходимости разрешается создать примечание к списку. Нажать ОК;
• переключиться на вкладку «Данные» в главном окне экселя. Отметить группу с ячейками, для которой потребуется создать список и кликнуть на кнопку «Проверка данных» и выбрать подпункт «работа с данными»;
• должно появиться окно «проверка вводимых значений». В параметрах найти подпункт список. В «источнике» поставить = «название списка». Нажать ОК;
• если все действия выполнены правильно, должен появиться значок со стрелкой вниз. Нажимая на значок, автоматически откроется созданный список. Кликая по любому наименованию, значение будет вставлено в ячейку.
Основным преимуществом метода является минимальная вероятность опечаток.
Использование инструментов от разработчика
Альтернативный вариант создания списка в экселе – это использование ActiveX. В сравнении с первым решением, инструкция немного сложнее, поскольку список опций несколько шире: разрешено задавать большое количество элементов, менять внешний вид списков.
Инструкция по использованию инструментов от разработчика следующая:
1. Активировать опции, поскольку они автоматически отключены. Для этого нужно перейти в меню «Файл».
2. В списке слева найти меню «Параметры» и кликнуть по нему.
3. Открыть раздел с настройками ленты и перейти к «основным вкладкам», поставить галочку напротив разработчика. Остается кликнуть на ОК, чтобы сохранить опции.
4. Должна появиться новая вкладка «Разработчик», через которую можно будет отслеживать рабочий процесс. Для начала создается столбик с элементами, которые выполняют функцию исходных данных для создания выпадающего списка.
5. Перейти в «Разработчик». В подпункте элементов управления кликнуть на кнопку вставки. В появившемся окне выбрать опцию «элементы ActiveX» и кликнуть на значок «Поле со списком».
6. Нажать на нужную ячейку, чтобы появилось окно со списком. По желанию можно настроить параметры и границы. При выделении списка, на панели инструментов появится «режим конструктора». Нажать на «Свойства», если требуется продолжить настройку.
7. В параметрах найти ListFillRange. В столбике поставить рядом «:» и описать координаты ячеек, чтобы создать определенный диапазон. Закрыть окно.
8. Правой кнопкой мышки кликнуть на список в окне, выбрать «объект ComboBox» и кликнуть на edit.
9. Должен появиться список с заранее заданным параметрами.
10. Если требуется сделать вставку списка в несколько ячеек, но понадобится навести курсор в правой части нижнем углу страницы. Далее зажать левую кнопку мыши и потянуть вниз до строки, где должен присутствовать список.
На этом миссию можно считать выполненной. Список будет отображаться в заранее заданных ячейках.
Как сделать связанный список
Данный вариант подойдет пользователям, которые нуждаются в создании сложных и зависимых списков. При выполнении ниже описываемых пунктов, список из одной ячейки будет зависеть от значения другого:
• минимальное количество столбиков – три. В первом указываются наименования, второй и третий – для единиц или количества. При необходимости можно задавать большее количество столбиков;
• для начала создается один, общий список для всех названий. Выделяются ячейки с наименованиями посредством контекстного меню;
• задать определенное имя;
• по аналогии формируются отдельные списки для каждого наименования с определением количества единиц;
• далее потребуется вставить общий список с наименованиями в верхней ячейке в первом столбике главной страницы;
• указать в качестве основного источника ранее заданную таблицу;
• кликнуть по верхней части столбика, где указаны единицы измерения, зайти в проверочное окно и в источнике указать «=ДВССЫЛ(A2)», вместо А2 может быть любая ячейка с наименованием продукта;
• список готов. Теперь можно растянуть по желанию строки и таблицы.
Создавая выпадающие списки, каждый пользователь может облегчить ведение отчетности в экселе. Каждая представленная инструкция имеет свои преимущества и не отнимет много времени, чтобы должным образом структурировать всю информацию.
Выпадающий список в ячейке листа
Один щелчок правой кнопкой мыши по пустой ячейке под столбцом с данными, команда контекстного меню Выбрать из раскрывающегося списка (Choose from drop-down list) или нажать сочетание клавиш ALT+стрелка вниз. Способ не работает, если ячейку и столбец с данными отделяет хотя бы одна пустая строка или вам нужен товар, который еще ни разу не вводился выше:
Выпадающий список в Excel с поиском
Способ 2. Стандартный
- Выделите ячейки с данными, которые должны попасть в выпадающий список (например, наименованиями товаров).
- Если у вас Excel 2003 или старше — выберите в меню Вставка — Имя — Присвоить(Insert — Name — Define), если Excel 2007 или новее — откройте вкладку Формулы (Formulas) и воспользуйтесь кнопкой Диспетчер имен (Name Manager), затем Создать. Введите имя (можно любое, но обязательно без пробелов и начать с буквы!) для выделенного диапазона (например Товары). Нажмите ОК.
- Выделите ячейки (можно сразу несколько), в которых хотите получить выпадающий список и выберите в меню (на вкладке) Данные — Проверка (Data — Validation). Из выпадающего списка Тип данных (Allow) выберите вариант Список (List) и введите в строчку Источник (Source) знак равенства и имя диапазона (т.е. =Товары).
Нажмите ОК.
Важный нюанс. В качестве источника данных для списка может выступать и динамический именованный диапазон, например прайс-лист. Тогда при дописывании новых товаров к прайсу, они будут автоматически добавляться к выпадающему списку. Еще одним часто используемым трюком для таких списков является создание связанных выпадающих списков (когда содержимое одного списка меняется в зависимости от выбора в другом).
Способ 3. Элемент управления
Этот способ представляет собой вставку на лист нового объекта — элемента управления «поле со списком» с последующей привязкой его к диапазонам на листе. Для этого:
- В Excel 2007/2010 откройте вкладку Разработчик (Developer). В более ранних версиях — панель инструментов Формы (Forms) через меню Вид — Панели инструментов — Формы (View — Toolbars — Forms). Если этой вкладки не видно, то нажмите кнопку Офис — Параметры Excel— флажок Отображать вкладку Разработчик на ленте (Office Button — Excel Options — Show Developer Tab in the Ribbon)
- Найдите значок выпадающего списка среди элементов управления форм (не ActiveX!). Ориентируйтесь по всплывающим подсказкам — Поле со списком:
- Формировать список по диапазону — выделите ячейки с наименованиями товаров, которые должны попасть в список
- Связь с ячейкой — укажите ячейку куда нужно выводить порядковый номер выбранного пользователем элемента.
- Количество строк списка — сколько строк показывать в выпадающем списке. По умолчанию — 8, но можно больше, чего не позволяет предыдущий способ.
- ListFillRange — диапазон ячеек, откуда берутся данные для списка. Выделить мышью диапазон он не даст, надо просто вписать его руками с клавиатуры (например, Лист2!A1:A5)
- LinkedCell — связанная ячейка, куда будет выводиться выбранный из списка элемент
- ListRows — количество отображаемых строк
- Font — шрифт, размер, начертание (курсив, подчеркивание и т.д. кроме цвета)
- ForeColor и BackColor — цвет текста и фона, соответственно
- Выпадающий список с данными из другого файла
- Создание зависимых выпадающих списков
- Автоматическое создание выпадающих списков надстройкой PLEX
- Выбор фото из выпадающего списка
- Автоматическое удаление уже использованных элементов из выпадающего списка
- Выпадающий список с автоматическим добавлением новых элементов
- Выделите ячейки с данными, которые должны попасть в выпадающий список (например, наименованиями товаров).
- Если у вас Excel 2003 или старше — выберите в меню Вставка — Имя — Присвоить(Insert — Name — Define), если Excel 2007 или новее — откройте вкладку Формулы (Formulas) и воспользуйтесь кнопкой Диспетчер имен (Name Manager), затем Создать. Введите имя (можно любое, но обязательно без пробелов и начать с буквы!) для выделенного диапазона (например Товары). Нажмите ОК.
- Выделите ячейки (можно сразу несколько), в которых хотите получить выпадающий список и выберите в меню (на вкладке) Данные — Проверка (Data — Validation). Из выпадающего списка Тип данных (Allow) выберите вариант Список (List) и введите в строчку Источник (Source) знак равенства и имя диапазона (т.е. =Товары).
- В Excel 2007/2010 откройте вкладку Разработчик (Developer). В более ранних версиях — панель инструментов Формы (Forms) через меню Вид — Панели инструментов — Формы (View — Toolbars — Forms). Если этой вкладки не видно, то нажмите кнопку Офис — Параметры Excel— флажок Отображать вкладку Разработчик на ленте (Office Button — Excel Options — Show Developer Tab in the Ribbon)
- Найдите значок выпадающего списка среди элементов управления форм (не ActiveX!). Ориентируйтесь по всплывающим подсказкам — Поле со списком:
- Формировать список по диапазону — выделите ячейки с наименованиями товаров, которые должны попасть в список
- Связь с ячейкой — укажите ячейку куда нужно выводить порядковый номер выбранного пользователем элемента.
- Количество строк списка — сколько строк показывать в выпадающем списке. По умолчанию — 8, но можно больше, чего не позволяет предыдущий способ.
- ListFillRange — диапазон ячеек, откуда берутся данные для списка. Выделить мышью диапазон он не даст, надо просто вписать его руками с клавиатуры (например, Лист2!A1:A5)
- LinkedCell — связанная ячейка, куда будет выводиться выбранный из списка элемент
- ListRows — количество отображаемых строк
- Font — шрифт, размер, начертание (курсив, подчеркивание и т.д. кроме цвета)
- ForeColor и BackColor — цвет текста и фона, соответственно
- Выпадающий список с данными из другого файла
- Создание зависимых выпадающих списков
- Автоматическое создание выпадающих списков надстройкой PLEX
- Выбор фото из выпадающего списка
- Автоматическое удаление уже использованных элементов из выпадающего списка
- Выпадающий список с автоматическим добавлением новых элементов
-
Excel список значений в ячейке
В ячейке D16 считаем скидку с помощью логической формулы ЕСЛИ. Примем за правило, что если заказ собран на сумму более 5 000 руб., то скидка будет 10%….
-
Excel выпадающий список в ячейке
СЧЁТЗ просто считает количество непустых ячеек в диапазоне. Что такое выпадающий список Выпадающий список в Excel (или раскрывающийся список) — это…
-
Excel как сделать выпадающий список в ячейке
После этого появится список всех введённых выше значений без повторов, а вам останется лишь выбрать желаемое и кликнуть по нему. Выпадающий список в…
После нажатия на ОК списком можно пользоваться.
Чтобы вместо порядкового номера элемента выводилось его название можно дополнительно использовать функцию ИНДЕКС (INDEX), которая умеет выводить содержимое нужной по счету ячейки из диапазона:
Способ 4. Элемент ActiveX
Этот способ частично напоминает предыдущий. Основное отличие в том, что на лист добавляется не элемент управления, а элемент ActiveX «Поле со списком» из раскрывающегося набора под кнопкой Вставить (Insert) с вкладки Разработчик (Developer):
Механизм добавления тот же — выбираем объект из списка и рисуем его на листе. А вот дальше начинаются серьезные отличия от предыдущего способа.
Во-первых, созданный выпадающий ActiveX список может находится в двух принципиально разных состояниях — режиме отладки, когда можно настраивать его параметры и свойства, двигать его по листу и менять размеры и — режиме ввода, когда единственное, что можно — выбирать из него данные. Переключение между этими режимами происходит с помощью кнопки Режим Конструктора (Design Mode) на вкладке Разработчик (Developer):
Если эта кнопка нажата, то мы можем настраивать параметры выпадающего списка, нажав соседнюю кнопку Свойства (Properties), которая откроет окно со списком всех возможных настроек для выделенного объекта:
Как создать выпадающий список в excel. Самый простой способ
Самые нужные и полезные свойства, которые можно и нужно настроить:
Большим и жирным плюсом этого способа является возможность быстрого перехода к нужному элементу в списке при вводе первых букв с клавиатуры(!), чего нет у всех остальных способов. Приятным моментом, также, является возможность настройки визуального представления (цветов, шрифтов и т.д.)
Выпадающие списки в Excel (быстро и надежно)
При использовании этого способа, также возможно указывать в качестве ListFillRange не только одномерные диапазоны. Можно, например задать диапазон из двух столбцов и нескольких строк, указав дополнительно, что выводить нужно два столбца (свойство ColumnCount=2). Тогда можно получить весьма привлекательные результаты, окупающие все потраченные на дополнительные настройки усилия:
Итоговая сравнительная таблица всех способов
| Способ 1. Примитивный | Способ 2. Стандартный | Способ 3. Элемент управления | Способ 4. Элемент ActiveX | |
| Сложность | низкая | средняя | высокая | высокая |
| Возможность настройки шрифта, цвета и т.д. | нет | нет | нет | да |
| Количество отображаемых строк | всегда 8 | всегда 8 | любое | любое |
| Быстрый поиск элемента по первым буквам | нет | нет | нет | да |
| Необходимость использования дополнительной функции ИНДЕКС | нет | нет | да | нет |
| Возможность создания связанных выпадающих списков | нет | да | нет | нет |
Ссылки по теме:
Выпадающий список в ячейке листа
Один щелчок правой кнопкой мыши по пустой ячейке под столбцом с данными, команда контекстного меню Выбрать из раскрывающегося списка (Choose from drop-down list) или нажать сочетание клавиш ALT+стрелка вниз. Способ не работает, если ячейку и столбец с данными отделяет хотя бы одна пустая строка или вам нужен товар, который еще ни разу не вводился выше:
Способ 2. Стандартный
Нажмите ОК.
Важный нюанс. В качестве источника данных для списка может выступать и динамический именованный диапазон, например прайс-лист. Тогда при дописывании новых товаров к прайсу, они будут автоматически добавляться к выпадающему списку. Еще одним часто используемым трюком для таких списков является создание связанных выпадающих списков (когда содержимое одного списка меняется в зависимости от выбора в другом).
Способ 3. Элемент управления
Этот способ представляет собой вставку на лист нового объекта — элемента управления «поле со списком» с последующей привязкой его к диапазонам на листе. Для этого:
После нажатия на ОК списком можно пользоваться.
Чтобы вместо порядкового номера элемента выводилось его название можно дополнительно использовать функцию ИНДЕКС (INDEX), которая умеет выводить содержимое нужной по счету ячейки из диапазона:
Способ 4. Элемент ActiveX
Этот способ частично напоминает предыдущий. Основное отличие в том, что на лист добавляется не элемент управления, а элемент ActiveX «Поле со списком» из раскрывающегося набора под кнопкой Вставить (Insert) с вкладки Разработчик (Developer):
Механизм добавления тот же — выбираем объект из списка и рисуем его на листе. А вот дальше начинаются серьезные отличия от предыдущего способа.
Во-первых, созданный выпадающий ActiveX список может находится в двух принципиально разных состояниях — режиме отладки, когда можно настраивать его параметры и свойства, двигать его по листу и менять размеры и — режиме ввода, когда единственное, что можно — выбирать из него данные. Переключение между этими режимами происходит с помощью кнопки Режим Конструктора (Design Mode) на вкладке Разработчик (Developer):
Если эта кнопка нажата, то мы можем настраивать параметры выпадающего списка, нажав соседнюю кнопку Свойства (Properties), которая откроет окно со списком всех возможных настроек для выделенного объекта:
Самые нужные и полезные свойства, которые можно и нужно настроить:
Большим и жирным плюсом этого способа является возможность быстрого перехода к нужному элементу в списке при вводе первых букв с клавиатуры(!), чего нет у всех остальных способов. Приятным моментом, также, является возможность настройки визуального представления (цветов, шрифтов и т.д.)
При использовании этого способа, также возможно указывать в качестве ListFillRange не только одномерные диапазоны. Можно, например задать диапазон из двух столбцов и нескольких строк, указав дополнительно, что выводить нужно два столбца (свойство ColumnCount=2). Тогда можно получить весьма привлекательные результаты, окупающие все потраченные на дополнительные настройки усилия:
Итоговая сравнительная таблица всех способов
| Способ 1. Примитивный | Способ 2. Стандартный | Способ 3. Элемент управления | Способ 4. Элемент ActiveX | |
| Сложность | низкая | средняя | высокая | высокая |
| Возможность настройки шрифта, цвета и т.д. | нет | нет | нет | да |
| Количество отображаемых строк | всегда 8 | всегда 8 | любое | любое |
| Быстрый поиск элемента по первым буквам | нет | нет | нет | да |
| Необходимость использования дополнительной функции ИНДЕКС | нет | нет | да | нет |
| Возможность создания связанных выпадающих списков | нет | да | нет | нет |