Excel: использовать таблицу как раскрывающийся список для проверки данных

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

Создание таблицы в MS Excel может быть очень неприятным, если вы повторяете один и тот же ввод снова и снова.

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

Давайте посмотрим, как использовать «правило проверки», функцию, которая делает это возможным.

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

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

Решение 1. Создайте список путем прямого ввода

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

Выберите «Проверка данных» на вкладке «Данные» в Excel, он может отображаться только в виде небольшого значка.

На вкладке настроек отобразите раскрывающийся список разрешить и выберите параметр Список. Это позволит вам создать или выбрать список разрешенных значений для выбранных ячеек данных.

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

Просто подтвердите, нажав OK, и ваш раскрывающийся список проверки будет создан для выбранных ячеек.

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

Решение 2. Выберите диапазон существующих значений

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

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

После создания списка допустимых значений в нашем примере на том же листе, чтобы сделать его визуально более доступным, выберите ячейки, в которых должна выполняться проверка данных.

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

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

На выбранные таким образом значения будут ссылаться в вашем списке проверки данных, и вы можете просто добавить значения для проверки данных или удалить их, обновив этот список.

Установить записи для проверки данных на другом листе

Станьте Excel Pro: Присоединяйтесь к нашему курсу!

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

Зарегистрируйтесь здесь

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

Поместите туда свои данные, выберите таблицу и используйте таблицы параметров: таблица на вкладке вставки, чтобы преобразовать ваш список значений в таблицу, которая будет автоматически преобразовываться при добавлении или удалении значений - как и ваша проверка данных.

Затем щелкните в любом месте стилизованной таблицы и введите имя таблицы на вкладке дизайна таблицы.

Это позволит вам ссылаться на эту таблицу где угодно, в том числе в списке проверки данных, просто введя имя таблицы вместо диапазона!

Как разрешить пустые ячейки в списке проверки данных

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

Убедитесь, что установлен флажок «Игнорировать пустое поле», и нажмите «применить эти изменения ко всем другим ячейкам с такими же настройками», прежде чем нажать «ОК».

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

Как отключить пустые значения в списке проверки данных

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

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

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

Как удалить раскрывающийся список проверки данных

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

Затем откройте меню проверки данных на вкладке данных - см. Выше в первом разделе, как это сделать - и убедитесь, что установлен флажок «применить эти изменения ко всем другим ячейкам с такими же настройками», иначе вы удалите только данные. проверка выбранных в данный момент ячеек.

Затем нажмите «Очистить все», и выбранная проверка данных будет удалена из книги MS Excel!

VBA: добавить список проверки

Добавить список проверки с помощью VBA так же просто, как использовать функцию ниже, где A1 - это ячейка, в которой данные будут проверяться по списку проверки данных, хранящемуся в ячейках от B1 до B5.

Диапазон ("a1"). Проверка _ .Modify xlValidateList, xlValidAlertStop, "= $ B $ 1: $ B $ 5"

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