Параметры проверки данных Excel включают в себя создание раскрывающегося списка, который ограничивает данные, которые пользователи могут вводить в ячейку, предварительно заданным списком записей. Эта функция поддерживает согласованность данных и позволяет фильтровать столбцы по каждой записи в списке. Например, если вы используете электронную таблицу для отслеживания RSVP для события, вы можете отфильтровать этот столбец по Да, Нет и Пока не отвечено.
Эти инструкции относятся к Excel 2019, 2016, 2013, 2010 и Excel для Office 365.
Когда вы добавляете раскрывающийся список в ячейку, рядом с ней появляется стрелка. При нажатии на стрелку откроется список, и вы сможете выбрать один из элементов для входа в ячейку.
Данные, используемые в списке, могут быть расположены на том же листе, что и список, на другом листе в той же рабочей книге или в другой рабочей книге .
Ввод данных для создания выпадающего списка
В этом примере раскрывающийся список использует список записей, расположенных в другой книге. Преимущества использования списка записей в другой книге включают централизацию данных списка, если нескольким пользователям потребуется доступ к ним, и защиту данных от случайного или преднамеренного изменения.
Когда данные списка находятся в отдельной рабочей книге, они должны быть открыты для обновления списка.
Ниже приведены пошаговые инструкции по созданию, использованию и изменению раскрывающегося списка, аналогичного приведенному на рисунке ниже.
-
Откройте две пустые книги Excel.
-
Сохраните одну книгу с именем data-source.xlsx — эта книга будет содержать данные для раскрывающегося списка.
-
Сохраните вторую книгу с именем drop-down-list.xlsx — эта книга будет содержать выпадающий список.
-
Оставьте обе книги открытыми после сохранения.
-
Введите данные , как показано ниже , в клетках A1 к A4 из данных source.xlsx- книги , как показано на рисунке выше.
-
Сохраните книгу и оставьте ее открытой.
-
Введите данные , как показано ниже , в ячейке B1 в выпадающем list.xlsx книги.
-
Сохраните книгу и оставьте ее открытой.
Данные для ячеек А1-А4 в data-source.xlsx
- A1 — Пряник
- A2 — Лимон
- A3 — Овсяная Изюм
- A4 — шоколадный чип
Данные для ячейки B1 в раскрывающемся списке. Xlsx
- B1 — Тип печенья:
Создание двух именованных диапазонов
Именованный диапазон позволяет вам ссылаться на определенный диапазон ячеек в книге Excel. Именованные диапазоны имеют много применений в Excel, в том числе в формулах и при создании диаграмм. Во всех случаях вы должны использовать именованный диапазон вместо диапазона ссылок на ячейки, указывающих расположение данных на листе.
При использовании раскрывающегося списка из другой книги вам необходимо использовать два именованных диапазона. Один предназначен для элементов списка, а второй — в рабочей книге, где находится раскрывающийся список — этот именованный диапазон ссылается на тот, который находится в первой рабочей книге.
Первый Именованный Диапазон
-
Выделите ячейки A1 — A4 книги data-source.xlsx, чтобы выделить их.
-
Нажмите на название Box , расположенной выше столбец A .
-
Введите Cookies в поле Имя .
-
Нажмите клавишу ввода на клавиатуре.
-
Клетки А1 до А4 в данных-source.xlsx книги теперь имя диапазона Cookies .
-
Сохраните книгу.
Второй именованный диапазон
Второй именованный диапазон не использует ссылки на ячейки из книги drop-down-list.xlsx . Вместо этого он ссылается на имя диапазона Cookies в книге data-source.xlsx , что необходимо, поскольку Excel не будет принимать ссылки на ячейки из другой книги для именованного диапазона. Это будет, однако, за исключением другого имени диапазона.
Следовательно, создание второго именованного диапазона выполняется не с помощью поля « Имя», а с помощью параметра « Определить имя», расположенного на вкладке « Формулы » на ленте .
-
Нажмите на ячейку C1 в книге раскрывающегося списка list.xlsx .
-
Нажмите « Формулы»> «Определить имя» на ленте, чтобы открыть диалоговое окно « Определить имя ».
-
Нажмите на кнопку New , чтобы открыть диалоговое окно New Name .
-
Введите данные в названии линии.
-
В Относится к строке type = ‘data-source.xlsx’! Cookies
-
Нажмите кнопку ОК, чтобы завершить именованный диапазон и вернуться в диалоговое окно «Определение имени».
-
Нажмите кнопку « Закрыть», чтобы закрыть диалоговое окно « Определить имя ».
-
Сохраните книгу.
Использование списка для проверки данных
Все параметры проверки данных в Excel, включая раскрывающиеся списки, задаются с помощью диалогового окна проверки данных. Помимо добавления раскрывающихся списков на лист, проверка данных в Excel также может использоваться для контроля или ограничения типа данных, которые пользователи могут вводить в определенные ячейки на листе.
-
Нажмите на ячейки С1 в выпадающем list.xlsx книги , чтобы сделать его активную ячейку — это когда выпадающий список будет.
-
Нажмите на вкладку « Данные » в меню ленты над рабочим листом.
-
Нажмите на значок проверки данных на ленте, чтобы открыть раскрывающееся меню. Выберите опцию проверки данных .
-
Нажмите на вкладку « Настройки » в диалоговом окне « Проверка данных » .
-
Нажмите на стрелку вниз в конце строки Разрешить, чтобы открыть раскрывающееся меню.
-
Нажмите « Список», чтобы выбрать раскрывающийся список для проверки данных в ячейке C1 и активировать строку « Источник» в диалоговом окне.
-
Поскольку источник данных для раскрывающегося списка находится в другой книге, второй именованный диапазон помещается в строку « Источник» в диалоговом окне.
-
Нажмите на строку источника .
-
Тип = Данные в строке источника .
-
Нажмите OK , чтобы завершить выпадающий список и близко в Валидация данных диалогового окна.
-
Небольшая иконка со стрелкой вниз должна появиться с правой стороны ячейки C1. При нажатии на стрелку вниз , откроется выпадающий список , содержащий четыре названия куки , введенные в клетки A1 до A4 из данных-source.xlsx книги.
-
Нажав на одно из имен в раскрывающемся списке, вы должны ввести это имя в ячейку C1 .
Изменение выпадающего списка
Так как в этом примере используется именованный диапазон в качестве источника для списка наших деталей , а не фактических имен списка, изменение названия печенья в указанном диапазоне в ячейках А1 до А4 в данной-source.xlsx книги сразу меняют имена в раскрывающемся вниз список.
Если данные вводятся непосредственно в диалоговое окно, внесение изменений в список требует возврата в диалоговое окно и редактирования строки источника.
Выполните следующие действия, чтобы изменить Lemon на Shortbread в раскрывающемся списке, изменив данные в ячейке A2 именованного диапазона в книге data-source.xlsx .
-
Нажмите на ячейку A2 в книге data-source.xlsx, чтобы сделать ее активной ячейкой.
-
Введите Shortbread в ячейку A2 и нажмите клавишу Enter на клавиатуре.
-
Нажмите на стрелке вниз для раскрывающегося списка в ячейках С1 в выпадающем list.xlsx книги.
-
Пункт 2 в списке должен теперь читать Песочное печенье вместо Лимона .
Варианты защиты выпадающего списка
Поскольку данные в этом примере находятся не на раскрывающемся списке, а на другом листе, доступны следующие варианты защиты данных списка:
- Защита рабочего листа путем блокировки ячеек A1 до A4 на листе 2
- Требование пароля для изменения рабочей книги
- Если рабочая книга открыта только для чтения