Как добавить значение в выпадающий список Excel

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

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

Как с наименьшими затратами времени и сил изменить размер выпадающего списка Excel – достаточно распространенная проблема. Рассмотрим основные способы ее решения.

Добавить или удалить значения в настройках выпадающего списка

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

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

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

Изменить исходный диапазон ячеек

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

1. На рабочем листе измените исходный список элементов по своему усмотрению — измените, добавьте новые или удалите существующие. Например, запишем в A7 новое значение.
2. Выберите ячейку с выпадающим списком.
3. На вкладке Данные нажмите Проверка данных .
4. В окне Проверка вводимых значений измените ссылку на диапазон в поле Источник . Вы можете либо отредактировать ссылку вручную, либо выбрать подходящий диапазон с помощью мыши. В нашем случае заменим $A$2:$A$6 на $A$2:$A$7
5. Нажмите «ОК» , чтобы сохранить изменения и закрыть окно.

Быстро добавить или удалить значения в выпадающем списке

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

Продолжим работу с выпадающим списком, который мы рассматривали в предыдущем параграфе.

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

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

Результатом является добавленная пустая ячейка:

2. Во вставленной ячейке введите новое значение. 

Вот и все! В диалоговом окне «Проверка данных » ссылка на диапазон автоматически меняется с A2:A7 на A2:A8. И в выпадающем списке появляется новый элемент:

Совет. Чтобы удалить значение из выпадающего списка, не обновляя ссылку на диапазон, на шаге 1 нажмите Удалить > ячейки со сдвигом вверх.

Увеличить выпадающий список на основе именованного диапазона

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

1. Добавьте или удалите значения в именованном диапазоне на рабочем листе.
2. Обновите ссылку на именованный диапазон:
   • Откройте Диспетчер имен, нажав Ctrl+F3, или перейдите на вкладку Формулы > Диспетчер имен.
   • В окне Диспетчера имен выберите нужный именованный диапазон и нажмите Изменить.
   • Обновите ссылку в поле Диапазон и нажмите ОК.
   • Закройте диалоговое окно, нажав кнопку Закрыть.

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

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

Создать динамический выпадающий список в Excel

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

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

1. В пустом столбце, не содержащем других данных, введите элементы списка в отдельных ячейках.
2. Создайте именованную формулу . Для этого нажмите Ctrl+F3 чтобы открыть диалоговое окно Создание имени. Введите желаемое имя в поле Имя , а затем введите следующую формулу в поле Диапазон. 

=СМЕЩ(Лист1!$A$2; 0; 0; СЧЁТЗ(Лист1!$A:$A)-1; 1)

Где:

2. Лист1 — название листа
3. А — столбец, в котором расположены элементы выпадающего списка
4. $A$2 — ячейка, содержащая первый элемент

Также вы можете использовать формулу ДВССЫЛ:

=ДВССЫЛ("A2:A"&(ПОИСКПОЗ(ИСТИНА;Лист1!$A:$A="";0)-1))

Рекомендую для дополнительной информации эту инструкцию: Выпадающий список Excel при помощи ДВССЫЛ.

3. Определив имя формулы, создайте раскрывающийся список на основе именованного диапазона, как обычно.

Совет. Если эту формулу вы планируете использовать только один раз для одного выпадающего списка, то вы можете просто ввести её в поле Источник, не создавая имя.

Примечание. В этом примере ячейка A1 содержит заголовок, который не должен быть включен в динамический диапазон. Поэтому мы добавляем поправку -1 к функции СЧËТЗ, чтобы предотвратить добавление пустого значения в конце раскрывающегося списка. 

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

=СМЕЩ(Лист1!$A$1; 0; 0; СЧЁТЗ(Лист1!$A:$A); 1)

Динамический выпадающий список в Excel 365/2021

Последние версии Excel предлагают много инновационных функций, которые недоступны в старых версиях. Одна из этих новых функций под названиеv УНИК может помочь вам создать динамический раскрывающийся список с простой формулой.

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

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

=УНИК(ФИЛЬТР(A2:A100;A2:A100<>""))

Эта динамическая формула массива вводится только в одну ячейку (F1) и автоматически распространяется на столько ячеек, сколько необходимо для отображения всех уникальных элементов из диапазона A2:A100.

Далее вы настраиваете выпадающий список, используя ссылку на динамический диапазон извлеченных уникальных значений. Это будет адрес ячейки, за которым следует символ решетки. В нашем случае это =$F$1# или =Лист1!$F$1#.

Результатом является  динамический раскрывающийся список — функция УНИК автоматически извлекает новые элементы по мере их добавления в столбец, а ссылка на ячейку с динамической формулой заставляет Excel соответствующим образом обновлять выпадающий список.

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

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

Вот еще полезные статьи по этой теме: