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

Создать раскрывающийся список | Разрешить другие записи | Добавить / удалить элементы | Динамический раскрывающийся список | Удалить раскрывающийся список | Зависимые раскрывающиеся списки | Настольная Магия

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

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

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

1. На втором листе введите элементы, которые должны появиться в раскрывающемся списке.

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

2. На первом листе выберите ячейку B1.

3. На вкладке «Данные» в группе «Работа с данными» щелкните «Проверка данных».

Excel поиск слова в ячейке

Откроется диалоговое окно «Проверка данных».

4. В поле Разрешить щелкните Список.

5. Щелкните в поле «Источник» и выберите диапазон A1: A3 на листе Sheet2.

6. Щелкните ОК.

Результат:

Примечание: чтобы скопировать / вставить раскрывающийся список, выберите ячейку с раскрывающимся списком и нажмите CTRL + c, выберите другую ячейку и нажмите CTRL + v.

7. Вы также можете вводить элементы непосредственно в поле «Источник» вместо использования ссылки на диапазон.

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

Разрешить другие записи

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

1. Во-первых, если вы введете значение, которого нет в списке, Excel покажет предупреждение об ошибке.

Чтобы разрешить другие записи, выполните следующие шаги.

2. На вкладке «Данные» в группе «Работа с данными» щелкните «Проверка данных».

Откроется диалоговое окно «Проверка данных».

3. На вкладке «Предупреждение об ошибке» снимите флажок «Показывать предупреждение об ошибке после ввода неверных данных».

4. Щелкните ОК.

5. Теперь вы можете ввести значение, которого нет в списке.

Добавить / удалить элементы

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

1. Чтобы добавить элемент в раскрывающийся список, перейдите к элементам и выберите элемент.

2. Щелкните правой кнопкой мыши и выберите 'Вставить'.

3. Выберите «Сдвинуть ячейки вниз» и нажмите «ОК».

Результат:

Примечание. Excel автоматически изменил ссылку на диапазон с Sheet2! $ A $ 1: $ A $ 3 на Sheet2! $ A $ 1: $ A $ 4. Вы можете проверить это, открыв диалоговое окно «Проверка данных».

4. Введите новый элемент.

Результат:

5. Чтобы удалить элемент из раскрывающегося списка, на шаге 2 нажмите «Удалить», выберите «Сдвинуть ячейки вверх» и нажмите «ОК».

Динамический раскрывающийся список

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

1. На первом листе выберите ячейку B1.

2. На вкладке «Данные» в группе «Работа с данными» щелкните «Проверка данных».

Откроется диалоговое окно «Проверка данных».

3. В поле «Разрешить» щелкните «Список».

4. Щелкните поле Источник и введите формулу:= СМЕЩЕНИЕ (Лист2! $ A $ 1,0,0; СЧЁТ (Лист2! $ A: $ A), 1)

Пояснение: Функция СМЕЩЕНИЕ принимает 5 аргументов. Ссылка: Sheet2! $ A $ 1, строки для смещения: 0, столбцы для смещения: 0, высота: COUNTA (Sheet2! $ A: $ A) и ширина: 1. COUNTA (Sheet2! $ A: $ A) подсчитывает число значений в столбце A на Листе 2, которые не являются пустыми. Когда вы добавляете элемент в список на Sheet2, COUNTA (Sheet2! $ A: $ A) увеличивается. В результате диапазон, возвращаемый функцией СМЕЩЕНИЕ, расширяется, и раскрывающийся список будет обновлен.

5. Щелкните ОК.

6. На втором листе просто добавьте новый элемент в конец списка.

Результат:

Удалить раскрывающийся список

Чтобы удалить раскрывающийся список в Excel, выполните следующие действия.

1. Выберите ячейку в раскрывающемся списке.

2. На вкладке «Данные» в группе «Работа с данными» щелкните «Проверка данных».

Откроется диалоговое окно «Проверка данных».

3. Щелкните Очистить все.

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

4. Щелкните ОК.

Зависимые раскрывающиеся списки

Хотите узнать больше о раскрывающихся списках в Excel? Узнайте, как создавать зависимые раскрывающиеся списки .

1. Например, если пользователь выбирает пиццу из первого раскрывающегося списка.

2. Второй раскрывающийся список содержит элементы пиццы.

3. Но если пользователь выбирает китайский язык из первого раскрывающегося списка, второй раскрывающийся список содержит китайские блюда.

Настольная Магия

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

1. На втором листе выберите элемент списка.

2. На вкладке Вставка в группе Таблицы щелкните Таблица.

3. Excel автоматически выбирает данные за вас. Щелкните ОК.

4. Если вы выберете список, Excel покажет структурированная ссылка .

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

Пояснение: КОСВЕННАЯ функция в Excel преобразует текстовую строку в действительную ссылку.

6. На втором листе просто добавьте новый элемент в конец списка.

Результат:

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

7. При использовании таблиц используйте функцию UNIQUE в Excel 365 для извлечения уникальных элементов списка.

Примечание: эта функция динамического массива, введенная в ячейку F1, заполняет несколько ячеек. Ух ты! Такое поведение в Excel 365 называется разливом.

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

Объяснение: всегда используйте первую ячейку (F1) и символ решетки для обозначения диапазона разлива.

Результат:

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