Выпадающий список в Excel с помощью инструментов или макросов
Под выпадающим списком понимается содержание в одной ячейке нескольких значений. Когда пользователь щелкает по стрелочке справа, появляется определенный перечень. Можно выбрать конкретное.
Очень удобный инструмент Excel для проверки введенных данных. Повысить комфорт работы с данными позволяют возможности выпадающих списков: подстановка данных, отображение данных другого листа или файла, наличие функции поиска и зависимости.
Создание раскрывающегося списка
Путь: меню «Данные» — инструмент «Проверка данных» — вкладка «Параметры». Тип данных – «Список».
Ввести значения, из которых будет складываться выпадающий список, можно разными способами:
- Вручную через «точку-с-запятой» в поле «Источник».
- Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком.
- Назначить имя для диапазона значений и в поле источник вписать это имя.
Любой из вариантов даст такой результат.
Выпадающий список в Excel с подстановкой данных
Необходимо сделать раскрывающийся список со значениями из динамического диапазона. Если вносятся изменения в имеющийся диапазон (добавляются или удаляются данные), они автоматически отражаются в раскрывающемся списке.
- Выделяем диапазон для выпадающего списка. В главном меню находим инструмент «Форматировать как таблицу».
- Откроются стили. Выбираем любой. Для решения нашей задачи дизайн не имеет значения. Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья». То есть нужно выбрать стиль таблицы со строкой заголовка. Получаем следующий вид диапазона:
- Ставим курсор в ячейку, где будет находиться выпадающий список. Открываем параметры инструмента «Проверка данных» (выше описан путь). В поле «Источник» прописываем такую функцию:
Протестируем. Вот наша таблица со списком на одном листе:
Добавим в таблицу новое значение «елка».
Теперь удалим значение «береза».
Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.
Теперь сделаем так, чтобы можно было вводить новые значения прямо в ячейку с этим списком. И данные автоматически добавлялись в диапазон.
- Сформируем именованный диапазон. Путь: «Формулы» — «Диспетчер имен» — «Создать». Вводим уникальное название диапазона – ОК.
- Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
- Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.
- Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).
Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Cells.Count > 1 Then Exit Sub If Target.Address = "$C$2" Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Range("Деревья"), Target) = 0 Then lReply = MsgBox("Добавить введенное имя " & _ Target & " в выпадающий список?", vbYesNo + vbQuestion) If lReply = vbYes Then Range("Деревья").Cells(Range("Деревья").Rows.Count + 1, 1) = Target End If End If End If End Sub
Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».
Нажмем «Да» и добавиться еще одна строка со значением «баобаб».
Выпадающий список в Excel с данными с другого листа/файла
Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.
- Делаем активной ячейку, куда хотим поместить раскрывающийся список.
- Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).
Имя файла, из которого берется информация для списка, заключено в квадратные скобки. Этот файл должен быть открыт. Если книга с нужными значениями находится в другой папке, нужно указывать путь полностью.
Как сделать зависимые выпадающие списки
Возьмем три именованных диапазона:
Это обязательное условие. Выше описано, как сделать обычный список именованным диапазоном (с помощью «Диспетчера имен»). Помним, что имя не может содержать пробелов и знаков препинания.
- Создадим первый выпадающий список, куда войдут названия диапазонов.
- Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.
- Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.
Выбор нескольких значений из выпадающего списка Excel
Бывает, когда из раскрывающегося списка необходимо выбрать сразу несколько элементов. Рассмотрим пути реализации задачи.
-
Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("Е2:Е9")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(0, 1)) = 0 Then Target.Offset(0, 1) = Target Else Target.End(xlToRight).Offset(0, 1) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("Н2:К2")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(1, 0)) = 0 Then Target.Offset(1, 0) = Target Else Target.End(xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub
Private Sub Worksheet_Change( ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range( «C2:C5» )) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
newVal = Target
Application.Undo
oldval = Target
If Len(oldval) <> 0 And oldval <> newVal Then
Target = Target & «,» & newVal
Else
Target = newVal
End If
If Len(newVal) = 0 Then Target.ClearContents
Application.EnableEvents = True
End If
End Sub
Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.
Выпадающий список с поиском
- На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).
- Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка.
- Жмем «Свойства» – открывается перечень настроек.
- Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.
При вводе первых букв с клавиатуры высвечиваются подходящие элементы. И это далеко не все приятные моменты данного инструмента. Здесь можно настраивать визуальное представление информации, указывать в качестве источника сразу два столбца.
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры
Как создать выпадающие списки в Excel
Выпадающие списки в Excel — это удобный способ организации данных и упрощения работы с таблицами. Они позволяют выбирать значения из заранее заданного списка, что помогает избежать ошибок при вводе информации. В этой статье мы рассмотрим, как создать выпадающие списки в Excel с помощью функции «Проверка данных».
Шаг 1: Откройте таблицу Excel, в которую вы хотите добавить выпадающий список. Выделите ячейку или диапазон ячеек, куда вы хотите поместить список.
Шаг 2: Перейдите на вкладку «Данные» в главном меню Excel, найдите группу «Инструменты данных» и выберите «Проверка данных». Откроется диалоговое окно.
Шаг 3: В диалоговом окне «Проверка данных» перейдите на вкладку «Список» и в поле «Источник» введите значения, которые вы хотите видеть в выпадающем списке. Значения могут быть введены через запятую или указаны в отдельных ячейках.
Шаг 4: Подтвердите настройки и закройте диалоговое окно. Теперь, когда вы щелкнете на выбранной ячейке, появится кнопка-дропдаун со списком доступных значений. Выберите нужное значение из списка, и оно автоматически будет вписано в ячейку.
Пример: предположим, у вас есть таблица с колонкой «Страны», и вы хотите сделать выпадающий список с доступными странами. В поле «Источник» введите список стран через запятую, например: Россия, США, Германия. Теперь вы сможете выбирать страну из списка в ячейке и избежать ошибок при вводе данных.
Создание выпадающих списков в Excel может быть полезным при работе с большими таблицами и упрощает процесс ввода данных. Вы также можете редактировать список значений в любое время, добавляя новые значения или удаляя старые. Это помогает сохранять таблицы актуальными и удобными для использования.
Как создать выпадающие списки в Excel
Чтобы создать выпадающий список в Excel, следуйте этим шагам:
- Выберите ячейку, в которой должен быть выпадающий список.
- Перейдите на вкладку «Данные» в верхней панели.
- Нажмите на кнопку «Проверка данных» в разделе «Инструменты данных».
- В открывшемся окне выберите вкладку «Основные».
- В поле «Исходные данные» введите значения, которые должны быть доступны в списке. Каждое значение должно быть в отдельной строке.
- Нажмите на кнопку «ОК».
После завершения этих шагов, ячейка, в которой был создан выпадающий список, будет отображать стрелку вниз. При нажатии на эту стрелку появится список, из которого можно выбрать одно из предоставленных значений.
Выпадающие списки особенно полезны при работе с большим количеством данных или при создании шаблонов для других пользователей. Они помогут предотвратить ошибки и облегчить процесс работы с данными в Excel.
Пример использования выпадающего списка:
Предположим, что вы создаете таблицу для учета расходов. В столбце «Категория» вы можете использовать выпадающий список, который будет содержать значения «Продукты», «Транспорт», «Развлечения» и т.д. При использовании выпадающего списка вы сможете быстро выбрать нужную категорию, а также быть уверенными в том, что данные будут введены правильно.
Внимание: Если вы хотите изменить или обновить список значений для выпадающего списка, повторите указанные выше шаги и внесите необходимые изменения. Новый список значений будет автоматически применяться ко всем ячейкам, где был создан выпадающий список.
Почему нужны выпадающие списки
Вот несколько причин, почему выпадающие списки могут быть очень полезными:
1. Улучшение точности данных: Выпадающий список ограничивает возможность ввода данных только предопределенными значениями, исключая возможность опечаток или ошибок. Это позволяет поддерживать высокую точность данных и снижает риск возникновения ошибок при анализе информации.
2. Ускорение ввода данных: Использование выпадающих списков упрощает и ускоряет ввод данных, так как пользователь может выбрать нужное значение из списка, вместо того чтобы вводить его вручную.
3. Согласованность данных: Выпадающие списки помогают обеспечить единообразие и согласованность данных, так как все пользователи будут использовать одни и те же предопределенные значения. Это особенно полезно при работе с командой или при обмене данными между разными программами.
4. Удобство фильтрации и сортировки: Использование выпадающих списков позволяет легко фильтровать или сортировать данные, так как система заранее знает все возможные значения и может проводить операции с ними более эффективно.
5. Улучшение визуальной представляемости: Выпадающие списки помогают улучшить визуальное представление данных и делают их более понятными для пользователей. Вместо того, чтобы видеть большое количество вариантов, пользователь видит только ограниченный список, что облегчает принятие решений и улучшает понимание данных.
Подготовка данных для списка
Прежде чем создавать выпадающие список в Excel, необходимо подготовить данные, которые будут отображаться в этом списке. Для этого следует использовать специальную таблицу, где в каждой ячейке будет находиться один элемент списка.
Одно из первых правил — поместите данные для списка в отдельный диапазон ячеек на листе Excel. Это позволит легко связать список со своими данными и изменять их при необходимости.
Чтобы создать список, вы можете разместить данные для него в одном столбце или строке. Важно при этом соблюдать следующие правила:
- В случае, если данные размещены в столбце, каждый элемент должен быть записан в отдельной ячейке.
- Если данные находятся в строке, каждый элемент также должен быть размещен в отдельной ячейке.
Путем дополнительного форматирования данных, можно улучшить внешний вид списка, добавив, например, разнообразную цветовую гамму, выделение шрифта или другие элементы декорации.
После подготовки данных, мы готовы приступить к созданию выпадающего списка в Excel.
Создание раскрывающегося списка
Чтобы упростить работу пользователей с листом, добавьте в ячейки раскрывающиеся списки. Раскрывающиеся списки позволяют пользователям выбирать элементы из созданного вами списка.
- На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel. В противном случае можно быстро преобразовать список в таблицу, выбрав любую ячейку в диапазоне и нажав клавиши CTRL+T.
- Почему данные следует поместить в таблицу? Потому что в этом случае при добавлении и удалении элементов все раскрывающиеся списки, созданные на основе этой таблицы, будут обновляться автоматически. Дополнительные действия не требуются.
- Теперь следует отсортировать данные в диапазоне или таблице в раскрывающемся списке.
Примечание: Если не удается выбрать пункт Проверка данных, лист может быть защищен или предоставлен к общему доступу. Разблокируйте определенные области защищенной книги или отмените общий доступ к листу, а затем повторите шаг 3.
- Если вы хотите, чтобы при выборе ячейки отображалось сообщение, проверка поле Показывать входное сообщение при выделении ячейки и введите заголовок и сообщение в полях (не более 225 символов). Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.
- Если вы хотите, чтобы при вводе сообщения, которого нет в списке, проверка поле Показывать оповещение об ошибке после ввода недопустимых данных, выберите параметр в поле Стиль и введите заголовок и сообщение. Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.
- Чтобы отобразить сообщение, которое не мешает пользователям вводить данные, отсутствуют в раскрывающемся списке, выберите Сведения или Предупреждение. Сведения покажут сообщение с этим значком
, а предупреждение — сообщение с этим значком 
. - Чтобы запретить пользователям вводить данные, которые отсутствуют в раскрывающемся списке, выберите Остановить.
Примечание: Если вы не добавили заголовок и текст, по умолчанию выводится заголовок «Microsoft Excel» и сообщение «Введенное значение неверно. Набор значений, которые могут быть введены в ячейку, ограничен».
Работа с раскрывающимся списком
После создания раскрывающегося списка убедитесь, что он работает правильно. Например, рекомендуется проверить, изменяется ли ширина столбцов и высота строк при отображении всех ваших записей.
Если список элементов для раскрывающегося списка находится на другом листе и вы хотите запретить пользователям его просмотр и изменение, скройте и защитите этот лист. Подробнее о защите листов см. в статье Блокировка ячеек.
Если вы решили изменить элементы раскрывающегося списка, см. статью Добавление и удаление элементов раскрывающегося списка.
Чтобы удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.
Зачем нужны выпадающие списки в excel
Работу с большими массивами информации можно сделать значительно удобнее, используя такой инструмент Microsoft Excel как выпадающий список.
Наша студентка курса «Excel бизнес-анализ и прогнозирование» написала в чат поддержки и попросила помочь ей разобраться с выпадающим списком. Эта сложность преследует многих. Давайте разберёмся вмес
Классический выпадающий список в ячейке Excel создается с помощью команды главного меню Данные – блок «Работа с данными» Проверка (Data — Validation). С помощью этого инструмента пользователь может сэкономить время на заполнение повторяющих значений выбирая уже готовый аргумент из подготовленного списка. Выпадающий список может быть неплохим помощником для фильтрации данных, если он состоит из небольшого количества позиций, каждая из которых представляет из себя слово или сочетание из двух слов. В случае если список большой или его элементами есть предложение или словосочетание из более чем 2 слов пользователь сталкивается с проблемой – выпадающий список в ячейке Microsoft Excel не дает возможности фильтровать по первым символам, то есть результатом отбора нельзя получить те строки, которые содержат заданные первые символы элемента выпадающего списка. Давайте попробуем обойти этот недостаток. В качестве примера возьмём перечень стран со столицами и континентам, на которых они находятся. Нашей задачей будет сделать так, чтобы в выпадающий список попали только те позиции, которые будут содержать в себе значение, указанное в ячейке E2:
1. Для начала давайте создадим выпадающий список, который будет отображаться в ячейке E2. Для этого в нужно выбрать в главному меню Microsoft Excel команду «Данные» — «Проверка» (Data — Validation) и в открывшемся окне на закладке «Параметры» выбрать тип данных – «Список» и в качестве «Источника» указать диапазон, который будет отображаться:
На закладке «Сообщение об ошибке» исключаем команду «Выводит сообщение об ошибках»:
2. Теперь давайте зададим в ячейку E2 значение, по которому будем отбирать данные из исходной таблицы. Для того, чтобы определить какие строки из исходной таблицы содержат значение указано в ячейке E2 используем функцию ПОИСК (SEARCH), которая ищет заданную подстроку в тексте и как результат выдает либо порядковый номер символа, где он был обнаружен, либо ошибку, если его там нет:
3. Теперь давайте используем результат предыдущего действия в качестве опции проверки ЕЧИСЛО (ISNUMBER), которая в ячейках, содержащих числовые значения выдает ИСТИНУ (TRUE), а в тех, что содержат ошибки или отличающиеся от цифр значения — ЛОЖЬ (FALSE):
С полученными данными необходимо осуществить еще такие действия: значение «ЛОЖЬ (FALSE)» превратим в 0, а вместо «ИСТИНА (TRUE)» укажем последовательность чисел (последнее значение последовательности укажет на количество позиций со значением «ИСТИНА (TRUE)»). Для этого нам помогут функции ЕСЛИ (IF) и МАКС (MAX). Наша конструкция из формул будет иметь следующий вид:
ЕСЛИ (результат функции ЕЧИСЛО (ISNUMBER)); — условие
МАХ () +1; — значение если истина
Таким образом когда условие функции исполняться, то она выводит максимальное значение из всех вышестоящих чисел + 1, в обратном случае то выводит 0:
5. Теперь создадим поле куда должны попасть отобранные позиции исходной таблицы (в нашем случае это будет таблица из двух колонок — порядковый номер значения и само значение) и с помощью функции ВПР (VLOOKUP) подтянем нужны нам поля.
После этого можно протестировать как работает наша формула для других критериях отбора, вводя в ячейку E2 разные слова чтобы увидеть будут ли меняться значения в поле отбора:
6. Далее нашей задачей будет сделать так, чтобы отобранные значения стали выпадающим списком, который будет обновляться сразу же после изменения аргумента ячейки E2. Для этого преобразуем поле отбора в именованный диапазон, который будет меняться в зависимости от количества отобранных значений.
В главному меню Microsoft Excel на вкладке «Формулы» команду «Диспетчер имен» — «Создать» (Formulas — Name Manager — Create) и в окне, что появиться указываем имя диапазона и ссылку на ячейки, из которых диапазон должен состоять:
Так как область отбора будет меняться в зависимости от значения ячейки E2, то для того, чтобы задать диапазон используем функцию СМЕЩ (OFFSET), которая имеет такие аргументы:
1. ячейка от которой будем делаться отсчет (в нашем случае используем ячейку H3 – первое значение полученного отбора);
2. количество ячеек, на которое нам нужно сдвинуться вниз (у нас это 0, так как сдвига вниз быть не должно);
3. количество ячеек, на которое нам нужно сдвинуться вправо (у нас это 0, так как сдвига вправо быть не должно);
4. высота (равно максимальному значению полученного в результате предыдущего использования функции ЕСЛИ ((IF));
5. ширина (у нас это 1 столбец).
6. И финальным этапом будет создание выпадающего списка. Выделим ячейку E2 и проделаем действия указание в пункте 1 нашего алгоритма, но в поле «Источник» Укажем название создано нами именованного диапазона со знаком равно перед ним:
Все, задание выполнено!
В связи с тем, что в арсенале Microsoft Excel появилась новая функция ФИЛЬТР (FILTER), которая может отобрать из нашей исходной таблицы только те сроки, которые содержат критерий из ячейки E2. Попросту говоря ФИЛЬТР (FILTER) заменяет пункты 2-5 описаны выше. При создании именуемого диапазона нам нужно будет в поле «Источник» указать ссылку на первый элемент отбора добавив к нему знак # чтобы захватить весь отобранный массив.
Это один из вариантов оптимизации своей работы в Excel. Каждый такой маленький шаг к автоматизации своей работы избавляет от рутины и делает работу проще. Глубоко изучить Excel поможет 27 часовой курс «Excel бизнес-анализ и прогнозирование»