ifempty

    Урок 14. Инструмент Проверка данных

    Оглавление

    Введение

    Контроль ввода чисел

    Контроль ввода даты и времени

    Создание простых выпадающих списков

    Создание динамических выпадающих списков

    Файлы для скачивания

     

    Введение

    Инструмент Проверка данных позволяет контролировать ввод данных в ячейки. Кнопка вызова инструмента находится на вкладке Данные - Проверка данных.

      проверка данных рис1

    Рисунок 1. Расположение кнопки вызова инструмента Проверка данных

    Окно инструмента состоит из трех вкладок:

    1. Параметры - основная вкладка, на которой устанавливаются настройки;
    2. Подсказка по вводу - на данной вкладке можно установить всплывающую подсказку для ячейки;
    3. Сообщение об ошибке - на данной вкладке можно установить вывод сообщения, в случае если пользователь ввел неправильные данные.

     проверка данных рис2

    Рисунок 2. Форма окна инструмента Проверка данных

     

    Контроль ввода чисел

    Скачайте и отктройте файл Проверка данных - Приложение1 по ссылке выше. Откройте лист Задача без решения. Если у вас стоит защищенный просмотр, то нажмите на кнопку Разрешить редактирование.  

    На листе дан простой расчет суммы двух чисел (ячейка С2) - А2+В2. 

    Задача: 

    Нужно сделать так, чтобы в ячейки А2 и В2 можно было вводить только целые числа от 10 до 20. Также сделать подсказки и сообщение об ошибке.

    Решение:

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

     проверка данных рис3

    Рисунок 3. Вызов инструмента Проверка данных

    Далее в окне Проверка вводимых значений нужно заполнить поля (см. рисунок 4):

    1. Тип данных - указать Целое число;
    2. Значение  - между;
    3. Минимум - установить 10;
    4. Максимум - установить 20.

      проверка данных рис4

    Рисунок 4. Заполнение вкладки Параметры - инструмент Проверка данных

    После этого переходим на вкладку Подсказка по вводу  и заполняем поля, как показано на рисунке 5:

     проверка данных рис5

    Рисунок 5. Заполнение вкладки Подсказка по вводу - инструмент Проверка данных

    И в конце переходим на вкладку Сообщение об ошибке и заполняем поля, как показано на рисунке 6:

     проверка данных рис6

    Рисунок 6. Заполнение вкладки Сообщение об ошибке - инструмент Проверка данных

    После этого нажимаем на кнопку ОК. 

    Теперь, если вы выделите ячейки А2 или В2, то у вас появится подсказка как на рисунке 7:

     проверка данных рис7

    Рисунок 7. Отображение всплывающей подсказки -инструмент Проверка данных

    Если вы попытаетесь ввести числа меньше 10 или больше 20, то Excel выдаст ошибку (см. рисунок 8):

     проверка данных рис8

    Рисунок 8. Сообщение об ошибке - инструмент Проверка данных

     

    Контроль ввода даты и времени

    Скачайте и откройте файл Проверка данных - Приложение 2 по ссылке выше. Откройте лист Без решения. На листе даны две таблицы  - Таблица 1 и Таблица 2

    Задача: 

    Нужно сделать так, чтобы в Таблицу 1 в ячейки А3 и В3 можно было вводить только даты 2019 года. А в Таблицу 2 в ячейки А7 и В7 можно было вводить только время от 09:00 утра до 18:00 вечера. Также нужно сделать подсказки и сообщения об ошибке, в случае если пользователь введет неверные данные.

    Решение:

    Для начла нужно выделить ячейки А3 и В3 в Таблице 1, затем перейти на вкладку Данные и открыть инструмент Проверка данных. Сделайте настройки, как показано на рисунке 9:

     проверка данных рис9

    Рисунок 9. Настройки трех вкладок инструмента Проверка данных

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

      проверка данных рис10

    Рисунок 10. Результат применения инструмента Проверка данных

    Теперь нужно настроить проверку данных для Таблицы 2. Для этого выделяем ячейки А7 и В7 в Таблице 2, затем открываем инструмент Проверка данных. 

     проверка данных рис11

    Рисунок 11. Настройки трех вкладок инструмента Проверка данных

    В результате у вас появится выпадающая подсказка, и в случае ввода неверных данных, например: 08:00, система выдаст сообщение об ошибке (см. рисунок 12).

      проверка данных рис12

    Рисунок 12. Результат применения инструмента Проверка данных

     

    Создание простых выпадающих списков

    Скачайте файл Проверка данных - Приложение 3 по ссылке выше. Откройте лист Задача без решения.  

    Задача:

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

    Решение:

    Нужно выделить ячейку Е1 и вызвать инструмент Проверка данных.

    Настройки нужно установить как на рисунках 13 и 14.

     проверка данных рис13

    Рисунок 13. Настройка выпадающего списка - инструмент Проверка данных

     проверка данных рис14

    Рисунок 14. Настройка выпадающего списка - инструмент Проверка данных

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

     проверка данных рис15

    Рисунок 15. Результат применения инструмента Проверка данных

     

    Создание динамических выпадающих списков

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

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

     

    Скачайте файл Проверка данных - Приложение 4 по ссылке выше. Откройте лист Задача без решения.  

    Сначала нужно таблицу со списком городов сделать умной. Как сделать таблицу умной смотрите в уроке Работа с умными таблицами 

    Далее выделяем ячейку Е1 и открываем инструмент Проверка данных. Настройки проверки данных показаны на рисунке 16.

     проверка данных рис16

    Рисунок 16. Настройка динамического выпадающего списка - инструмент Проверка данных

    Обратите внимание на поле Источник -  в нем указана формула:

    =ДВССЫЛ("Таблица1[Город]")

    В данном случае, нам потребовалась функция ДВССЫЛ, чтобы имя таблицы и наименование столбца, Excel смог воспринимать как ссылки. Имя умной таблицы вы можете увидеть на вкладке Конструктор. Обратите внимание, что в некоторых ситуациях кавычки нужно будет вводить в английской раскладке. 

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

     проверка данных рис17

    Рисунок 17. Результат применения инструмента Проверка данных

     

    Файлы для скачивания:

     

     

    Зарегистрируйтесь и получите пробный однодневный доступ к полным материалам курса!  В него входит: учебник, видеоурок, практика, тестирование. После входа в систему вам будет доступен личный кабинет с курсами.

    Войти с помощью: 


    Пробная подписка 1 День

    Пробная подписка на 1 день

    Вы можете активировать только 1 раз.


    MS Excel Basic Навсегда


    MS Excel Intermediate Навсегда

    Курс Basic получаете в подарок


    MS Excel Advanced Навсегда

    Курсы Basic и Intermediate получаете в подарок.