Как сделать проверку данных в Excel?

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

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

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

В появившемся окошке «Проверка вводимых значений» настраивается проверка. На вкладке «Параметры» выбирается из списка тип данных, которые будут проверяться и вводиться в выделенный нами диапазон ячеек. Для примера выберем «Целое число».

После выбора типа данных становится возможным выбор условия соответствия вводимого значения в графе «Значение:». Выберем для примера «Между»

• Далее в графах «Минимум:» и «Максимум:» необходимо указать значения, или указать на ячейки с данными значениями, нажав на соответствующий значок справа от каждой графы ввода.
• На следующей вкладке «Сообщение для ввода» можно указать заголовок и само сообщение подсказку, которое будет высвечиваться при активации ячейки с проверкой вводимых данных.

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

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

«Сообщение» просто проинформирует о недопустимом введенном значении.

1. 
2. Сообщение подсказка.
3. Вид «Останов».
4. Вид «Предупреждение».
5. Вид «Сообщение».

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

Источник: http://officeprogs.ru/excel/proverka-danny-h-v-excel.html

Руководство по проверке данных Excel

Проверка данных — это функция в Excel, используемая для контроля того, что пользователь может ввести в ячейку. Например, вы можете использовать проверку данных, чтобы убедиться, что:

• значение является числом от 1 до 6
• дата произойдет в следующие 30 дней
• текстовая запись содержит менее 25 символов

Проверка данных может просто отображать сообщение пользователю с информацией, что разрешено, как показано ниже:

Сообщение отображается автоматически при выборе ячейки

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

Пример сообщения об ошибке

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

Пример раскрывающегося меню проверки данных

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

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

Проверка данных осуществляется с помощью правил, определенных в пользовательском интерфейсе Excel на вкладке «Данные» на ленте.

Элементы управления проверкой данных на вкладке ДАННЫЕ

Важное ограничение

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

Определение правил проверки данных

Проверка данных определяется в окне с 3 вкладками: Параметры, Сообщение для ввода и Сообщение об ошибке:

Окно проверки данных имеет три основные вкладки

На вкладке Параметры вы можете ввести критерии проверки. Существует ряд встроенных правил проверки с различными параметрами, также можно выбрать «Другой» и использовать собственную формулу для проверки ввода, как показано ниже:

Пример вкладки настроек проверки данных

Вкладка «Сообщение для ввода» определяет сообщение, отображаемое при выборе ячейки с правилами проверки. Оно не является обязательным.

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

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

Вкладка настройки сообщения проверки данных

Вкладка «Сообщение об ошибке» определяет, как выполняется проверка. Например, когда вид установлен на «Останов», неверные данные вызывают окно с сообщением, и ввод не разрешен.

Вкладка предупреждения об ошибке проверки данных

Пользователь видит сообщение, подобное этому:

Пример сообщения об ошибке проверки данных

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

Не позволяет пользователям вводить недопустимые данные в ячейку. Пользователи могут повторить попытку, но должны ввести значение, которое проходит проверку данных. В окне предупреждения «Останов» есть три опции: «Повторить», «Отмена» и «Справка».

Предупреждает пользователей о том, что данные неверны. Предупреждение ничего не делает, чтобы остановить ввод неверных данных. В окне «Предупреждение» есть три параметра: «Да» (для принятия недействительных данных), «Нет» (для редактирования недействительных данных), «Отмена» (для удаления недействительных данных) и «Справка».

Вид: Сообщение

Сообщает пользователям, что данные являются недействительными. Это сообщение не делает ничего, чтобы остановить ввод неверных данных. Информационное окно имеет 3 кнопки: «ОК», чтобы принять недействительные данные, «Отмена», чтобы удалить их и «Справка».

Параметры проверки данных

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

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

Целое число — разрешены только целые числа. Как только опция целого числа выбрана, другие опции становятся доступными для дальнейшего ограничения ввода. Например, вам может потребоваться целое число от 1 до 10.

Действительное — работает как опция целого числа, но допускает десятичные значения. Например, если для параметра «Действительное» задано значение от 0 до 3, допустимы все значения, такие как 0,5 и 2,5.

Список — разрешены только значения из предварительно определенного списка. Значения представляются пользователю как выпадающее меню. Допустимые значения могут быть жестко заданы непосредственно на вкладке «Параметры» или указаны в виде диапазона на рабочем листе.

Дата — разрешены только даты. Например, вам может потребоваться дата между 1 января 2018 года и 31 декабря 2021 года или дата после 1 июня 2018 года.

Время — разрешено только время. Например, вы можете указать время между 9:00 и 17:00 или разрешить время только после 12:00.

Длина текста — проверяет ввод на основе количества символов или цифр. Например, вам может потребоваться код из 5 цифр.

Другой — проверяет ввод с использованием пользовательской формулы. Другими словами, вы можете написать собственную формулу для проверки ввода. Пользовательские формулы значительно расширяют возможности проверки данных. Например, вы можете использовать формулу, чтобы обеспечить значение в верхнем регистре, или значение, которое содержит «АБВ».

На вкладке параметров также есть два флажка:

Игнорировать пустые ячейки — говорит Excel не проверять ячейки, которые не содержат значений. На практике этот параметр влияет только на команду «Обвести неверные данные». Когда эта опция включена, пустые ячейки не обведены, даже если они не прошли проверку.

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

Простое выпадающее меню

Вы можете предоставить пользователю раскрывающееся меню опций, жестко закодировав значения в поле настроек или выбрав диапазон на листе. Например, чтобы ограничить записи действиями «ПРИНЯТ», «В ОБРАБОТКЕ» или «ОТГРУЖЕН», вы можете ввести эти значения через точку с запятой:

Раскрывающееся меню проверки данных с жестко заданными значениями

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

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

Другой способ ввода значений в раскрывающееся меню — использование ссылки на рабочий лист. Например, с размерами (то есть маленький, средний и т.д.) в диапазоне F4:F6. Вы можете указать этот диапазон непосредственно в окне настроек проверки данных:

Значения выпадающего меню проверки данных со ссылкой на диапазон

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

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

Вы также можете использовать именованные диапазоны для указания значений. Например, с именованным диапазоном под названием «размер» для F4:F6, вы можете ввести имя непосредственно в окне, начиная со знака равенства:

Значения выпадающего меню проверки данных с именованным диапазоном

Именованные диапазоны автоматически являются абсолютными, поэтому они не изменятся.

Вы также можете создавать зависимые выпадающие списки с пользовательской формулой.Совет.

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

Проверка данных с помощью пользовательской формулы

Формулы проверки данных должны быть логическими формулами, которые возвращают ИСТИНА, если ввод действителен, и ЛОЖЬ, если ввод недействителен. Например, чтобы разрешить ввод любого числа в ячейку A1, вы можете использовать функцию ЕЧИСЛО (ISNUMBER) в формуле, подобной этой:

= ЕЧИСЛО (А1)

Если пользователь вводит значение 10 в A1, ЕЧИСЛО (ISNUMBER) возвращает ИСТИНА, и проверка данных завершается успешно. Если вводится значение типа «яблоко» в A1, ЕЧИСЛО (ISNUMBER) возвращает ЛОЖЬ, и проверка данных завершается неудачно.

Чтобы включить проверку данных с помощью формулы, выберите «Другой» на вкладке «Параметры», затем введите формулу, начиная со знака равенства (=), как обычно.

Формулы устранения неполадок

Excel игнорирует формулы проверки данных, которые возвращают ошибки.

Если формула не работает, и вы не можете понять, почему, настройте фиктивные формулы, чтобы убедиться, что формула работает так, как вы ожидаете.

Фиктивные формулы — это просто формулы проверки данных, введенные непосредственно на листе, чтобы вы могли легко увидеть, что они возвращают. На приведенном ниже экране показан пример:

Проверка достоверности данныхс помощью фиктивных формул

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

Примеры формул проверки данных

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

Чтобы разрешить только 5 символьных значений, начинающихся с «z», вы можете использовать:

= И (ЛЕВСИМВ (А1) = «z»; ДЛСТР (A1) = 5)

Эта формула возвращает ИСТИНА только тогда, когда код длиной 5 цифр и начинается с «z». Два значения в примере выше возвращают ЛОЖЬ с этой формулой.

Чтобы разрешить ввод даты в течение 30 дней с сегодняшнего дня:

= И (А1> СЕГОДНЯ (), А1

Источник: https://excelpedia.ru/bez-rubriki/rukovodstvo-po-proverke-dannyx-excel

Проверка вводимых данных в ячейки Excel

Допустим, что в таблице прайс-листа с розничными ценами на товары, в одном из столбцов указана процентная ставка НДС. Как часто бывает в рутинной работе срабатывает человеческий фактор и по ошибке для одной из категорий товаров, вместо ставки НДС 20% была введена старая ставка 19%.

Эта небольшая разница в данных – 1% может создать большие проблемы для фирмы с всевозможными последствиями.

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

Пример прайс-листа с введенными ошибками в процентных ставках НДС:

Чтобы в Excel сделать проверку вводимых данных в ячейки следует выполнить ряд последовательных действий:

1. Выделите диапазон ячеек где вводиться формула с процентной ставкой для цен с НДС и выберите инструмент: «ДАННЫЕ»-«Работа с данными»-«Проверка данных».
2. В появившемся окне «Проверка вводимых значений» на вкладке «Параметры» из выпадающего списка «Тип данных:» выберите опцию «Список».
3. В полю ввода «Источник:» введите значения разных процентных ставок: 0%; 20%; освобождается.
4. Перейдите на закладку «Сообщение об ошибке» и заполните текстовое поле «Сообщение:» текстом который будет содержать сообщение при вводе других значений, которые отличаются от указанных значений в списке.

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

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

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

После заполнения всех параметров в окне «Проверка вводимых значений» нажмите на кнопку ОК.

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

Внимание! Инструмент «Проверка данных» срабатывает только при вводе значений в ячейку. Если же значения буду скопированы из других ячеек, то при вставке этот инструмент удалиться из этих ячеек и не сработает.

Источник: https://exceltable.com/vozmojnosti-excel/proverka-dannyh-v-yacheyke

Проверка данных в Excel — Microsoft Excel для начинающих

Используйте проверку данных в Excel, чтобы убедиться, что пользователи вводят в ячейки только определенные значения.

Пример проверки данных

В этом примере мы сделаем такое ограничение, при котором пользователи смогут вводить только целое число между 0 и 10.

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

Чтобы создать правило проверки данных, следуйте нашей инструкции:

1. Выделите ячейку С2.

На вкладке Данные (Data) нажмите кнопку Проверка данных (Data Validation).

На вкладке Параметры (Settings) диалогового окна Проверка вводимых значений (Data Validation) сделайте следующее:

• Из выпадающего списка Тип данных (Allow) выберите Целое число (Whole number).
• Из выпадающего списка Значение (Data) выберите Между (Between).
• Введите минимальное и максимальное значения.
  

Сообщение для ввода

Сообщения для ввода появляются, когда пользователь выделяет ячейку. Они указывают ему, что нужно вводить. Перейдите на вкладку Сообщение для ввода (Input Message) и сделайте следующее:

1. Поставьте галочку напротив Отображать подсказку, если ячейка является текущей (Show input message when cell is selected).
2. Введите заголовок.
3. Введите само сообщение.
   

Сообщение об ошибке

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

1. Поставьте галочку напротив параметра Выводить сообщение об ошибке (Show error alert after invalid data is entered).
2. Введите заголовок.
3. Введите сообщение об ошибке.
   
4. Нажмите ОК.

Результат проверки данных

1. Выделите ячейку С2.
   
2. Попробуйте ввести число больше, чем 10.Результат:

   

Примечание: Чтобы удалить проверку данных из ячейки, выделите её и на вкладке Данные (Data) нажмите кнопку Проверка данных (Data Validation). Затем кликните по Очистить все (Clear All). Чтобы быстро выбрать все ячейки с проверкой данных, используйте инструмент Выделение группы ячеек (Go To Special).

Источник: https://office-guru.ru/excel/proverka-dannyh-v-excel-381.html

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

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

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

Разберем поподробней.

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

Для начала надо выделить ячейку(или диапазон ячеек) для которой необходимо установить проверку. Затем идем в Данные(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.
  Важно: если проверка данных в дальнейшем будет скопирована в другие ячейки, то ссылки будут смещены так, будто это простая формула. Поэтому при создании формул в проверке данных необходимо учитывать возможность смещения и при необходимости закреплять ссылки(знак доллара — $).

Вкладка Сообщение для ввода (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)

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

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

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

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

После того, как ячейкам была назначена проверка данных переходим на вкладку Данные(Data)—Проверка данных(Data Validation) -раскрываем меню и выбираем Обвести неверные данные(Circle Invalid Data).

Все ячейки, данные в которых не соответствуют условиям проверки данных будут обведены красной линией:

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

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

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

Источник: https://www.excel-vba.ru/chto-umeet-excel/proverka-dannyx/

Как сделать проверку данных в Excel?

Привет всем. Как часто Вы сталкиваетесь с таблицами общего пользования, которые заполняют несколько сотрудников? Например, Вы формируете базу данных в Эксель, и всю информацию вносит несколько человек. Ошибок не избежать, некорректные данные часто проскакивают в таблицы. Вот если бы Excel мог проверить их перед внесением и указать на ошибку.

А он может! В программу встроен мощный инструмент под названием «Проверка данных», который минимизирует ошибки внесения информации.

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

Чтобы подключить инструмент «Проверка данных», выделите проверяемые ячейки и кликните на ленте «Данные – Работа с данными – Проверка данных». Откроется окно настройки «модератора»:

1. На вкладке «Параметры» нужно задать условие для проверки в поле «Тип данных». Есть 8 типов условий для проверки:
2. Любое значение – проверка данных отключена
3. Целое число – можно ввести лишь целое число с дополнительным критерием;
4. Действительное число – любое действительное число с дополнительным критерием
5. Список – выбрать значение из заранее заданного списка
6. Дата – ввести только дату, удовлетворяющую дополнительному критерию
7. Время – ввести время с дополнительным условием
8. Длина текста – ввести текст определенной длины
9. Другой – ввести значение, которое удовлетворяет записанному вручную логическому выражению

В этом окне можно установить еще две галки:

• Игнорировать пустые ячейки – не применять условие к пустым ячейкам
• Распространить изменения на другие ячейки с тем же условием – найти ячейки с аналогичными условиями и применить к ним такие же изменения

На вкладке «Сообщение для ввода» введите подсказку о том, какие данные нужно внести в ячейку.

На вкладке «Сообщение об ошибке выберите событие, которое произойдет при введении неправильных данных. Сначала сделаем выбор в списке «Вид»:

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

После этого запишем заголовок и основной текст для окна-предупреждения.

А теперь поговорим детальнее о каждом из видов условий.

Условие на целое число в Excel

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

• Между минимальным и максимальным значением
• Вне заданного промежутка значений
• Равно определенному значению
• Не равно определенному значению
• Больше заданного значения
• Меньше заданного значения
• Больше или равно значению
• Меньше или равно значению

Условие на действительное значение

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

Список

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

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

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

Чтобы задать список, опишите его в поле источник. Это можно сделать несколькими способами:

• Записать перечень в ячейках на листе книги, а в поле «Источник» указать ссылку на этот массив
• Создать именованный массив, в поле «Источник» указать его имя
• Перечислить пункты прямо в поле «Источник» через точку с запятой

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

Дата

Этот фильтр позволит внести в ячейку только дату. Напомню, что дата в Эксель дата – это тоже число, поэтому такое правило перекликается с условием на действительное значение.  Тем не менее, ввести можно только дату, с указанием параметров, таких же, как и в первых двух пунктах.

Время

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

Длина текста

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

Другой

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

Например, чтобы в ячейку А1 вносили только коды, начинающиеся с символов “SUV”, и содержащие 12 символов, внесем в поле формулу: =И(ЛЕВСИМВ(A1;3)=»SUV»;ДЛСТР(A1)=12).

Лишь при выполнении этого правила значение будет считаться верным.

Напомню, функция ЛЕВСИМВ возвращает заданное количество символов слева направо, а ДЛСТР – считает количество символов в ячейке.

Если Вы применили «Проверку данных» к таблице, в которой уже есть какие-то данные, можно проверить их на соблюдение заданных правил и, при необходимости, вручную исправить.

Для этого кликните на стрелке вниз возле ленточной команды «Проверка данных», в выпадающем меню выберите «Обвести неверные данные». Excel обведет ячейки с ошибками красным кругом.

Осталось только исправить ошибки, круги сами будут исчезать.

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

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

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

Я, таким образом, иногда подстраховываю и себя самого, когда создаю таблицы данных. Думаю, «Проверка данных» будет и для Вас полезным помощником!

Источник: https://officelegko.com/2017/01/30/proverka-dannyih-v-excel-dlya-teh-kto-tsenit-svoe-vremya/