Lost your password?


Хитрости »
Основные понятия (27)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (23)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (68)
Разное (43)
Баги и глюки Excel (5)

Проверка данных

Проверка данных является неплохим инструментом Excel для контроля за вносимыми на листах изменениями, не прибегая к помощи VBA. С её помощью можно ограничить ввод в ячейку, разрешив вводить только определенные даты либо числа. При этом можно запретить вводить текст в тех ячейках, в которых должны быть исключительно числа, а так же задать диапазон чисел(например, только от 1 до 1000).
Для чего это вообще может быть нужно? Например, есть некий шаблон с формулами и для корректной работы формул требуются правильно введенные исходные данные. С помощью Проверки данных можно разрешить пользователю вводить только тот тип и интервал данных, который может обработать формула, не возвращая значение ошибки или некорректный результат.
Иными словами, проверка данных предназначена для контроля вводимых в ячейки данных. При правильном использовании очень мощная и удобная вещь в различных шаблонах, которые должны заполняться разными людьми.

Чтобы использовать проверку вводимых данных необходимо выделить ячейку или несколько ячеек, ввод данных в которые надо отслеживать и ограничить. Далее переходим на вкладку Данные(Data) -Проверка данных(Data Validation). Появится окно проверки данных:
Проверка данных



 
вкладка Параметры(Settings)
Это основная вкладка, в которой задаются основные параметры проверки данных.
В поле Тип данных(Allow) - выбирается непосредственно тип данных, который должен быть записан в ячейке. Всего доступно 8 типов: Любое значение, Целое число, Действительное, Список, Дата, Время, Длина текста, Другой(Any Value, Whole number, Decimal, List, Date, Time, Text lenght, Custom). Пункт Любое значение(Any Value) установлен по умолчанию, проверка не осуществляется. Подробно про пункт Списки(List) и как их создавать можно посмотреть и почитать в статье Выпадающие списки. Там все подробно и с нюансами расписано про списки в проверке данных, поэтому в данной статье рассмотрим оставшиеся 6 типов. Если кратко, то при выборе пункта Список в ячейке появляется выпадающий список допустимых значений. И ввести можно только то значение, которое присутствует в списке
Список в ячейке
Остальные типы данных:

  • Целое число(Whole number) - говорит само за себя. После установки такой проверки в ячейку можно будет внести только целое число. Т.е. число, не имеющее дробного остатка(9,1 например уже нельзя будет ввести). Так же нельзя будет ввести произвольный текст. Чаще всего подобная проверка применяется в полях для записи кол-ва штук товара и т.п. Т.е. там, где не может быть дробных значений.
  • Действительное(Decimal) - тоже, что и в предыдущем пункте, но ввести можно любое число - хоть целое, хоть дробное, но невозможно будет ввести текст
  • Примечание: применив данные типы проверок, внести в ячейку можно только число. Занести текст Excel уже не разрешит. Однако стоит помнить, что дата и время Excel воспринимает и хранит именно как ЧИСЛОВЫЕ значения, поэтому ввод даты и времени тоже будет разрешен, если он не противоречит остальным условиям проверки(см.ниже). Но для проверки Целое число разрешен будет ввод только даты, т.к. время Excel хранит как дробное значение(кроме 24:00:00 и 00:00:00, которые Excel воспринимает как 1 и 0 соответственно). Более подробно о восприятии Excel-ем данных читайте в статье Как Excel воспринимает данные?.

  • Дата(Date) - В ячейке может содержаться только значение даты. Дата может быть записана в любом формате, допустимом в текущей локализации Windows. Здесь тот же нюанс, что и проверкой на числа, только в обратную сторону - любая дата это число, поэтому по сути можно будет ввести любое целое число, которое может быть переведено Excel-м в дату.
  • Время(Time) - можно записывать значения в формате времени для текущей локализации и так же как с датой в ячейку можно будет вводить не только время, но и любые числа: целые или дробные
  • Длина текста(Text lenght) - позволяет ограничить количество вводимых в ячейку символов. Данные могут являться и числом и текстом и временем. Чем угодно, только должны соответствовать остальным условиям проверки. Часто применяется для контроля ввода данных по ИНН, КПП, БИК, артикулам и т.п.
  • Если посмотреть на картинку в начале статьи, то можно увидеть еще три поля, помимо тех, которые я перечислил: Значение(Data), Минимум(Minimum) и Максимум(Maximum). Поле Значение содержит несколько вариантов проверки: Между, Вне, Равно, Больше, Меньше, Больше или равно, Меньше или равно(between, not between, equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to) и доступно оно только при выборе Типа данных Целое число, Действительное, Дата, Время, Длина текста. Для других типов поле Значение недоступно. В зависимости от того, какой пункт выбран в поле Значение появляются дополнительные поля: Минимум и Максимум. При этом может быть только одно поле и название может быть иным. Например, для варианта Равно будет показано только одно поле и называться оно будет Значение(Value). Но здесь нет никаких подводных камней и разобраться с этими полями можно не напрягаясь и без поллитры :)

    По сути все пункты довольно красноречивы и пояснять подробно каждый, думаю, смысла нет. Например, Между(between) - указывается интервал дат или чисел, в который должно входить условие(например целое число от 1 до 12: Минимум = 1, Максимум = 12). Если пользователь попытается ввести число за пределами указанного интервала(скажем число 0, -3 или 14), то Excel выдаст сообщение об ошибке(см.ниже). Вариант Больше или равно(greater than or equal to) позволит вносить только положительные значения больше нуля, если указать в поле Минимум значение 1.
    Чуть большее внимание следует уделить варианту проверки Вне(not between). При установленном значении Вне, в ячейку можно будет внести только данные, которые не входят в диапазон, указанный в полях Минимум и Максимум. Т.е. при тех же условиях от 1 до 12 ввести можно будет и 0 и -3 и 14, но нельзя будет ввести 1 или 10.
    При этом в полях для ввода значений допускается указать ссылку на ячейку. Например, в ячейке A1 записана начальная дата приема заявок, в B1 - конечная дата. И надо установить в ячейках A2:A50 проверку на интервал дат, указанных как раз в A1 и B1. Для этого выделяем ячейки A2:A50 и создаем в них проверку данных: Тип данных: Дата, Значение: Между, Начальная дата: =A1, Конечная дата: =B1. Теперь можно регулировать интервал без изменения самой проверки данных - просто изменяя значения ячеек A1 и B1.

  • Другой(Custom) - на мой взгляд самый интересный и самый мощный тип проверки. Здесь нам предоставляется возможность более широко контролировать ввод данных. Для заполнения есть только одно поле - Формула(Formula). В него необходимо записать формулу и при каждом вводе значений в ячейку, Excel проверит введенное выражение на ИСТИНУ. Если быть точнее то Excel сначала вычислит формулу в этом поле ориентируясь на введенное в ячейку с проверкой данных значение, а потом проверит - возвращает ли формула значение ИСТИНА(TRUE). Если результатом будет ИСТИНА(TRUE), то введенное значение будет сохранено в ячейке, а если ЛОЖЬ(FALSE), то будет выдано окно с сообщением об ошибке:
    Сообщение об ошибке

    Приведу простой пример. В ячейку А1 введем число 1. Выделяем ячейку В1 - назначаем проверку данных-Другой. В поле Формула вписываем: =B1=A1. Теперь в ячейку В1 можно вписать только значение, которое полностью идентично значению в ячейке А1.
    Обращаю особое внимание на то, что в большинстве случаев в вводимой формуле должно происходить некое сравнение с той ячейкой, в которой создается проверка данных. Например, если мы хотим, чтобы в В1 можно было ввести только значение текущей даты, то мы должны записать формулу так:
    =B1=СЕГОДНЯ()
    =B1=TODAY()

    Т.е. мы сравниваем введенное в В1 значение с выражением, записанным правее от знака равенства. А правее у нас функция СЕГОДНЯ(TODAY), которая возвращает текущую дату.

    Важно: если проверка данных в дальнейшем будет скопирована в другие ячейки, то ссылки будут смещены так, будто это простая формула. Поэтому при создании формул в проверке данных необходимо учитывать возможность смещения и при необходимости закреплять ссылки(знак доллара - $).



  •  
    Вкладка Сообщение для ввода (Input Message)

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

     
    Вкладка Сообщение об ошибке(Error Alert)
    Здесь указывается отображать сообщение об ошибочном вводе или нет, и сам тип выводимого сообщения об ошибке.

    • Выводить сообщение об ошибке(Show error alert after invalid data is entered) - в большинстве случаев необходимо установить галочку. Если галочка снята, то проверка вводимых в ячейку данных не будет осуществляться.
    • Вид(Style)
      • Останов, Сообщение(Stop, Information) - можно ввести только разрешенные проверкой значения. Различается только вид сообщения(пример сообщения Останов приведен на рисунке выше).
      • Предупреждение(Warning) - в ячейку можно ввести любое значение, но при вводе значения, противоречащего проверке, появиться предупреждающее сообщение с подтверждением ввода данных.
    • Заголовок(Title) - текст, который будет показан в заголовке сообщения об ошибке. Если не указан, то в заголовке будет написано Microsoft Excel.
    • Сообщение(Error message) - непосредственно текст самого сообщения об ошибке. Если не указан, то будет показан текст примерно следующего содержания:
      Это значение не соответствует ограничениям по проверке данных, установленным для этой ячейки
      (This value doesn't match the data validation restrictions defined for this cell)


     
    Как скопировать проверку данных на другие ячейки
    Все очень просто - копируем ячейку с нужной проверкой данных -выделяем ячейки для создания в них такого же условия -Правая кнопка мыши -Специальная вставка(Paste Special) -в окне выбираем Условия на значения(Validation) -Ок:
    Распространить проверку данных



     
    Как удалить проверку данных из ячеек
    Выделяем необходимые ячейки -вкладка Данные(Data)-Проверка данных(Data Validation). В поле Тип данных(Allow) устанавливаем Любое значение(Any Value) -Ок.



     
    Маленькая хитрость использования проверки данных
    Если проверка данных на ячейки была установлена уже после того, как данные были внесены, то очень неплохо понять, все ли данные соответствуют условиям проверки. Сделать это несложно. После того, как ячейкам была назначена проверка данных переходим на вкладку Данные(Data)-Проверка данных(Data Validation) -раскрываем меню и выбираем Обвести неверные данные(Circle Invalid Data). Все ячейки, данные в которых не соответствуют условиям проверки данных будут обведены красной линией:
    Найти ошибочные данные
    Это можно использовать не только когда необходима проверка данных, но и просто для определения ошибочных значений. Например, есть список товара на складе и есть лист, в который этот товар вносился сотрудниками вручную. Необходимо определить какие наименования товара не соответствуют тем, которые заведены на складе. Создаем проверку данных Список(Выпадающие списки), указывая в качестве источника список товара на складе. Далее распространяем эту проверку на ячейки с занесенными сотрудниками данными. И теперь применяем функцию Обвести неверные данные.

    Сразу после исправления неверных значений на те, которые есть в списке обводка исчезает. Если не все значения надо исправлять, а обводку тем не менее надо удалить, то после всех нужных правок просто переходим на вкладку Данные(Data) -Проверка данных(Data Validation) -раскрываем меню и выбираем Удалить обводку неверных данных(Clear Validation Circles)



     
    Как обойти проверку данных или маленькая ложка дегтя
    Необходимо всегда помнить, что какое бы условие на проверку не было установлено - значение в ячейке можно удалить, нажав кнопку Del. Либо просто скопировать ячейку из другой книги или листа и вставить на место проверки данных - проверка исчезнет, т.к. вся ячейка будет заменена скопированной. И от этого никак не уйти - такой проверку сделали разработчики. И даже защита листа здесь не поможет, т.к. для работы проверки данных ячейка не должна быть защищена.


    Статья помогла? Поделись ссылкой с друзьями!
      Плейлист   Видеоуроки

    Поиск по меткам

    Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика
Обсуждение: 13 комментариев
  1. Роман:

    здравствуйте! я так и не понял, какие выбрать условия, чтобы ограничить длину текста шестью знаками? на пример - 02А02 и 25К31 можно написать, а 2А2 и 2А02 нельзя?

  2. Роман, а Вы хоть попробовали? Вы пишете 6 знаков, а для примера приводите значения из 5 :-)
    Выбираете Тип данных: Длина текста.
    Поле Значение: Равно.
    в поле Длина вводите цифру 6.
    Все.

  3. Роман:

    спасибо!
    точно, про шесть знаков я описАлся, конечно пять!
    я уже сам допетрил (экспериментальным путем), но Вы ответили быстрее! :)
    мне больше подходит вариант такой - значение БОЛЬШЕ ИЛИ РАВНО МИНИМУМ 5.
    т.е. меньше пяти знаков чтоб не могли записать в ячейку.

    и еще - огромное Вам спасибо за гениально простой способ убивать * в ячейках, который Вы описали в другом посте!

  4. Михаил:

    Подскажите пожалуйста возможно ли в Excel проверять данные таким образом как я хочу.
    Пример: Я хочу для всего листа задать такое условие, что если в одну ячейку была вписана например 1Б34 то в другую ячейку я не могу задать 1Б34 и выходила бы соответственно ошибка! То есть мне нужно создать проверку по водимых символов(не по длине текста), чтобы они не повторялись !!!
    Буду очень благодарен если Вы мне подскажите, жду ответа на E-mail [удалено администратором]
    С уважением Михаил.

  5. Чтобы статья стала еще более информативной, можно дополнить статью информацией о том, что несмотря на установку на ограничение ввода в ячейку через "Данные"-Проверка данных, в нее некоторые нерадивые пользователи (изобретательные, скажем так) все же умудряются внести неправильные данные ...
    причем особо не напрягаясь... они просто копируют, а потом вставляют данные в ту ячейку, где до этого существовало условие на проверку данных :-)

  6. Анастасия:

    как сделать проверку введенных чисел в таблицу для всей таблицы, а не для каждого числа?

    • Анастасия, что значит "для каждого числа"? В статье написано: "Для начала надо выделить ячейку(или диапазон ячеек)".
      Т.е. если Вы планируете использовать одно правило проверки данных для всех ячеек - выделяете сначала все нужные ячейки, а затем вызываете окно проверки данных. Проверка данных будет назначена всем выделенным ячейкам.
      Если имелось ввиду нечто другое - поясните подробнее.
      Спасибо.

  7. Анастасия:

    В строке таблицы введены данные 1 5 3 7, под каждым числом необходимо поставить условие что "можно вводить только это число". Значений в таблице много и листов с аналогичным условием тоже. Вручную "для каждого числа" вводить условие очень долго. Думала с помощью макросов сделать проверку вводимых значений?

    • Макросом можно. Может и стандартной проверкой можно с использованием формул. Но не видя в файле что и как там у Вас записано - сказать ничего конкретного нельзя.
      Обратитесь в форум.

  8. Добрый день!
    Подскажите, а как организовать проверку значения в ячейке. Если в ячейке значение "ВЕРНО", то сообщение об ошибки не всплывает, а если "НЕВЕРНО", то не всплывает!
    Заранее спасибо

  9. Артур:

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

Поделитесь своим мнением

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


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2024 Excel для всех   Войти