Как обновить выпадающий список в excel
Обновлено: 05.07.2024
Когда в таблицу Excel приходится вносить одни и те же значения, поможет выпадающий список. Для чего он нужен:
- Во-первых, облегчение занесения данных. Значение, которое необходимо занести может быть сложным и длинным >> выбор из представленного списка ускорит процесс;
- Во-вторых, снижение вероятности ошибки. Никто не застрахован от опечатки >> представленные варианты не позволят этого сделать;
- В-третьих, это некоторая защита работоспособности общего файла >> все мы встречаем тех или иных персон, которым приходится работать в Excel, но они категорически отказываются его понять. Такие зачастую осложняют жизнь остальных тем, что могут занести данные в область таблицы, предназначенную для другого - настроенный выпадающий список позволит ограничить значения ячейки только теми данными, которые для нее разрешены.
Итак, предположим, что я работаю в компании Х, представленной на рынке 10 крупнейших городов РФ. Регулярно в отчетах я указываю название города. Чтобы исключить вероятность опечатки, хочу заранее сформировать выпадающий список с правильными вариантами. Приступим:
- Сформируем диапазон значений для будущего выпадающего списка. Пусть это будет 10 строк в столбце А, наполненные названиями городов (диапазон A1:A10)
- Даем имя этой области. Пусть будет имя Город . Для этого выделяем диапазон A1:A10 и в поле с адресом ячейки пишем "Город" и нажимаем Enter.
Это не обязательная процедура, но она значительно упрощает жизнь в будущем, т.к. к именованному диапазону можно обращаться по имени (использовать слово "Город" внутри формул)
Теперь переходим непосредственно в наш отчет. У меня это будет небольшая таблица, в одном из столбцов которой необходимо указывать город. Шаги следующие:
- Выделяем столбец таблицы (без заголовка), переходим на вкладку "Данные" (на рисунке 1) >> раздел "Работа с данными" >> кнопка "Проверка данных"
- В открывшемся окне выбираем тип данных " Список ", а в качестве источника данных указываем имя нашего исходного диапазона ( =Город ). Здесь важно ссылаться на диапазон через знак "=" - в противном случае Excel решит, что вы вносите в источник значения руками и будет выдавать только слово "город". Нажимаем ОК.
Теперь при обращении к любой ячейке в столбце город, мы будем автоматически получать варианты заполнения:
После небольшой практики вся процедура будет занимать у вас считанные секунды.
Если статья оказалась для вас полезной, ставьте лайк, и задавайте любые вопросы по Excel - во всём разберемся.
Чтобы упростить работу пользователей с листом, добавьте в ячейки раскрывающиеся списки. Раскрывающиеся списки позволяют пользователям выбирать элементы из созданного вами списка.
На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel. Если это не так, список можно быстро преобразовать в таблицу, выделив любую ячейку диапазона и нажав клавиши CTRL+T.
Почему данные следует поместить в таблицу? Потому что в этом случае при добавлении и удалении элементов все раскрывающиеся списки, созданные на основе этой таблицы, будут обновляться автоматически. Дополнительные действия не требуются.
Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.
На ленте откройте вкладку Данные и нажмите кнопку Проверка данных.
Примечание: Если кнопка Проверка данных недоступна, возможно, лист защищен или является общим. Разблокируйте определенные области защищенной книги или отмените общий доступ к листу, а затем повторите шаг 3.
На вкладке Параметры в поле Тип данных выберите пункт Список.
Щелкните поле Источник и выделите диапазон списка. В примере данные находятся на листе "Города" в диапазоне A2:A9. Обратите внимание на то, что строка заголовков отсутствует в диапазоне, так как она не является одним из вариантов, доступных для выбора.
Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки.
Установите флажок Список допустимых значений
Не знаете, какой параметр выбрать в поле Вид?
Чтобы заблокировать пользователям ввод данных, которые не содержатся в раскрывающемся списке, выберите вариант Остановка.
После создания раскрывающегося списка убедитесь, что он работает правильно. Например, рекомендуется проверить, изменяется ли ширина столбцов и высота строк при отображении всех ваших записей.
Если список элементов для раскрывающегося списка находится на другом листе и вы хотите запретить пользователям его просмотр и изменение, скройте и защитите этот лист. Подробнее о защите листов см. в статье Блокировка ячеек.
Если вы решили изменить элементы раскрывающегося списка, см. статью Добавление и удаление элементов раскрывающегося списка.
Чтобы удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.
Ввод данных станет быстрее и точнее, если ограничить значения в ячейке вариантами из раскрывающегося списка.
Сначала создайте на листе список допустимых элементов, а затем выполните сортировку или расположите их в нужном порядке. В дальнейшем эти элементы могут служить источником для раскрывающегося списка данных. Если список небольшой, на него можно легко ссылаться и вводить элементы прямо в средстве проверки данных.
Создайте список допустимых элементов для раскрывающегося списка. Для этого введите элементы на листе в одном столбце или строке без пустых ячеек.
Выделите ячейки, для которых нужно ограничить ввод данных.
На вкладке Данные в группе Инструменты нажмите кнопку Проверка данных или Проверить.
Примечание: Если команда проверки недоступна, возможно, лист защищен или книга является общей. Если книга является общей или лист защищен, изменить параметры проверки данных невозможно. Дополнительные сведения о защите книги см. в статье Защита книги.
Откройте вкладку Параметры и во всплывающем меню Разрешить выберите пункт Список.
Щелкните поле Источник и выделите на листе список допустимых элементов.
Диалоговое окно свернется, чтобы было видно весь лист.
Нажмите клавишу ВВОД или кнопку Развернуть , чтобы развернуть диалоговое окно, а затем нажмите кнопку ОК.
Значения также можно ввести непосредственно в поле Источник через запятую.
Чтобы изменить список допустимых элементов, просто измените значения в списке-источнике или диапазон в поле Источник.
См. также
На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel.
Почему данные следует поместить в таблицу? Потому что в этом случае при добавлении и удалении элементов все раскрывающиеся списки, созданные на основе этой таблицы, будут обновляться автоматически. Дополнительные действия не требуются.
Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.
На ленте откройте вкладку Данные и нажмите кнопку Проверка данных.
На вкладке Параметры в поле Разрешить выберите пункт Список.
Если вы уже создали таблицу с элементами раскрывающегося списка, щелкните поле Источник и выделите ячейки, содержащие эти элементы. Однако не включайте в него ячейку заголовка. Добавьте только ячейки, которые должны отображаться в раскрывающемся списке. Список элементов также можно ввести непосредственно в поле Источник через запятую. Например:
Фрукты;Овощи;Зерновые культуры;Молочные продукты;Перекусы
Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки.
Установите флажок Список допустимых значений
После создания раскрывающегося списка убедитесь, что он работает правильно. Например, рекомендуется проверить, изменяется ли ширина столбцов и высота строк при отображении всех ваших записей. Если вы решили изменить элементы раскрывающегося списка, см. статью Добавление и удаление элементов раскрывающегося списка. Чтобы удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Раскрывающийся список автообновлений
Раскрывающийся список автообновлений
1. Выберите ячейку, в которую хотите поместить раскрывающийся список, и нажмите Данные > проверка достоверности данных > проверка достоверности данных. Смотрите скриншот:
2. в проверка достоверности данных диалоговом окне, щелкните вкладку Настройка и выберите Список от Разрешить список, затем введите = СМЕЩЕНИЕ (2,0,0 $ A $; COUNTA (A: A) -1) в текстовое поле Источник. Смотрите скриншот:
Чаевые: В приведенной выше формуле A2 - это первая ячейка диапазона данных, с которым вы хотите создать раскрывающийся список, а A: A - местоположение исходных данных столбца.
3. Нажмите OK. Теперь создается автоматически обновляемый раскрывающийся список. А когда вы добавляете новые данные в исходный диапазон данных, раскрывающийся список тем временем обновляется.
В прошлых статьях я рассказал, как сделать выпадающий список в Excel: первая , вторая и видео:
Вернемся к примеру из прошлых статей - мы готовим таблицу для отдела кадров, в которой перечислены все сотрудники, должности и отделы, и хотим, чтобы таблицу было заполнять легко, в то же время, чтобы избежать зоопарка различных названий, типа: "дир.", "директор", "дир-р", "деректор" и тому подобного - всегда удобно работать с чистыми данными.
Все достаточно просто - выбираем диапазон, где хотим сделать выпадющий список и жмем кнопку Проверка данных, указываем список значений - и готово.
Все бы хорошо, но каждый раз, когда нужно будет добавить новые должности, отделы, или что-то поменять - нужно будет редактировать настройки проверки данных, и не всегда отдел кадров сможет это сделать быстро и без ошибок.
Можно ли как-то автоматизировать добавление новых отделов не используя макросы? Да! Но сначала нужна небольшая подготовка. Воспользуемся методом, описанным в статье " Только при помощи формул: список уникальных значений и сортировка ", и скопируем на лист "Списки" уникальные значения из колонки "Отделы". Чтобы узнать, как это сделать, пройдите по ссылке выше. Там же вы найдете готовую таблицу Excel для примера.
Мы ранее протягивали формулу массива на несколько десятков строк, чтобы наверняка хватило: с ячейки B3 до ячейки B33 (конечно, можно сделать и меньше, и больше). Этот диапазон можно указать в настройках проверки данных, но тогда в выпадающем списке будет достаточно много пустых строк. Неаккуратненько.
Чтобы у нашего файлы был профессиональный и законченный вид, попробуем сосчитать сколько ячеек в уникальном списке заполнены. Введем в ячейку B1 на листе "Списки" формулу:
=COUNTA($B$3:$B$33)-COUNTBLANK(B3:B33)
COUNTA (СЧЁТЗ) укажет количество ячеек в указаном диапазоне, COUNTBLANK (СЧИТАТЬПУСТОТЫ) укажет количество путых ячеек.
Таким образом мы узнали, сколько уникальных значений в списке. Теперь можно немного модифицировать формулу:
Функция TEXT (ТЕКСТ) преображает числовое значение в текстовый вид и позволяет сцепить его с текстом. И еще немножко доделаем формулу, добавив проверку случая, если наш список пуст.
Таким образом, у нас в ячейке B1 получился в текстовом виде указатель на диапазон, в котором размещен список уникальных значений:
Читайте также: