Проверка данных в MS EXCEL. Проверка данных Excel

16.08.2019

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

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

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

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

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

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

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

Сообщение подсказка.

Вид «Останов» .

Вид «Предупреждение» .

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

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

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

У нас имеется лист номенклатуры товаров магазина:

Теперь проверим. В ячейку B2 введите натуральное число, а в ячейку B3 отрицательное. Как видно в ячейке B3 действие оператора набора – заблокировано. Отображается сообщение об ошибке: «Введенное значение неверно».

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

Чтобы удалить проверку данных в Excel нужно: выделить соответствующий диапазон ячеек, выбрать инструмент и нажать на кнопку «Очистить все» (указано на втором рисунке).

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

Данным способом проверяются данные только в процессе ввода. Если данные уже введенные они будут не проверенные. Например, в столбце B нельзя ввести текст после установки условий заполнения в нем ячеек. Но заголовок в ячейке B1 «Цена» остался без предупреждения об ошибке.

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

Чтобы проверить соответствуют ли все введенные данные, определенным условиям в столбце и нет ли там ошибок, следует использовать другой инструмент: «Данные»-«Проверка данных»-«Обвести неверные данные».

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

Конечно, можно выполнить проверку данных в столбце с помощью логической функции Excel – «ЕСЛИ». Или условное форматирование. Но применение инструмента «Проверка данных» – более эффективно, удобно и продуктивно для данной задачи. Особенно если нам нужно одновременно выполнить проверку по нескольким столбцам. В таком случаи более заметна рациональность его использования.

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

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

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

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

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

На вкладке «Параметры» нужно задать условие для проверки в поле «Тип данных». Есть 8 типов условий для проверки:

Любое значение – проверка данных отключена
Целое число – можно ввести лишь целое число с дополнительным критерием;
Действительное число – любое действительное число с дополнительным критерием
Список – выбрать значение из заранее заданного списка
Дата – ввести только дату, удовлетворяющую дополнительному критерию
Время – ввести время с дополнительным условием
Длина текста – ввести текст определенной длины
Другой – ввести значение, которое удовлетворяет записанному вручную логическому выражению

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

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

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

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

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

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

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

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

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

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

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

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

Список

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

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

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

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

Дата

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

Время

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

Длина текста

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

Другой

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

Как проверить ячейки Excel на соблюдение условий

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

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

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

Я же отправляюсь писать новый важный пост, на этот раз про . Не пропустите, особенно если Ваши таблицы большие и сложные. До встречи!

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

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

Подсказки в MS Excel

Первый способ вставки подсказки

Первым делом разберемся c подсказками. Взгляните на мою табличку. Вроде бы все просто, однако даже при такой простоте можно нагородить солидный огород. К примеру, поле «номер документ» — это №1, 1, или 22.03-1?

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

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

Удалить примечания можно только из контекстного меню правой кнопки мыши

Обратите внимание: нажатие кнопки «Delete» в ячейке не удалит примечание. Избавиться от него (или изменить его текст) можно повторно щелкнув в ячейке правой кнопкой мыши и выбрав пункт «Удалить примечание» или «Изменить примечание».

Второй способ вставки подсказки

Ещё один способ ввести подсказку интересен нам только с точки зрения того, о чем пойдет разговор дальше (фильтрации ввода), тем не менее знать о нем полезно.

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

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

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

Подсказки — это хорошо, но что делать с самыми «прогрессивными» пользователями, которые подсказок не читают? Остается одно — бить по рукам… то есть использовать фильтрацию ввода, я хотел сказать.

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

Первое, что может оказаться полезным, это проверка по длине введенного текста . Выбираем пункт «Длина текста » и указываем любой диапазон ограничений. К примеру, я хочу чтобы текст в поле «Сообщение» был как можно более кратким, но в то же время ячейка не могла оставаться пустой. Значения от 3-х до 25 введенных символов, этого вполне хватит, чтоб вписать что-то вроде «Напоминание об оплате» (21 символ), а при попытке ввести более длинный текст, MS Excel выдаст окно-предупреждение и не даст завершить ввод.

Проверка по числу

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

Выбор из имеющегося списка значений

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

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

Выбираем в «Проверке данных » вкладку «Параметры «, и указываем «Тип данных» → «Список» . Ввести «предустановленные» значения можно двумя способами:

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

…или выберем из диапазона

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

Выбор из списка на листе MS Excel. Других данных ввести не удастся.

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

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

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

Выпадающий список можно создать с помощью

В этой статье создадим Выпадающий список с помощью () с типом данных Список .

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

А. Простейший выпадающий список - ввод элементов списка непосредственно в поле Источник

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

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

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

Недостатки этого подхода: элементы списка легко потерять (например, удалив строку или столбец, содержащие ячейку B 1 ); не удобно вводить большое количество элементов. Подход годится для маленьких (3-5 значений) неизменных списков.
Преимущество : быстрота создания списка.

Б. Ввод элементов списка в диапазон (на том же листе, что и выпадающий список)

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

Предположим, что элементы списка шт;кг;кв.м;куб.м введены в ячейки диапазона A 1: A 4 , тогда поле Источник будет содержать =лист1!$A$1:$A$4

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

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

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

B. Ввод элементов списка в диапазон (на любом листе)

Ввод элементов списка в диапазон ячеек, находящегося в другой книге

Если необходимо перенести диапазон с элементами выпадающего списка в другую книгу (например, в книгу Источник. xlsx ), то нужно сделать следующее:

в книге Источник. xlsx создайте необходимый перечень элементов;
в книге Источник.xlsx диапазону ячеек содержащему перечень элементов присвойте , например СписокВнеш;
откройте книгу, в которой предполагается разместить ячейки с выпадающим списком;
выделите нужный диапазон ячеек, вызовите инструмент , в поле Источник укажите =ДВССЫЛ("[Источник.xlsx]лист1!СписокВнеш") ;

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

Если нет желания присваивать имя диапазону в файле Источник.xlsx , то формулу нужно изменить на =ДВССЫЛ("[Источник.xlsx]лист1!$A$1:$A$4")

СОВЕТ:
Если на листе много ячеек с правилами Проверки данных , то можно использовать инструмент (Главная/ Найти и выделить/ Выделение группы ячеек ). Опция Проверка данных этого инструмента позволяет выделить ячейки, для которых проводится проверка допустимости данных (заданная с помощью команды Данные/ Работа с данными/ Проверка данных ). При выборе переключателя Всех будут выделены все такие ячейки. При выборе опции Этих же выделяются только те ячейки, для которых установлены те же правила проверки данных, что и для активной ячейки.

Примечание :
Если выпадающий список содержит более 25-30 значений, то работать с ним становится неудобно. Выпадающий список одновременно отображает только 8 элементов, а чтобы увидеть остальные, нужно пользоваться полосой прокрутки, что не всегда удобно.

В EXCEL не предусмотрена регулировка размера шрифта Выпадающего списка . При большом количестве элементов имеет смысл список элементов и использовать дополнительную классификацию элементов (т.е. один выпадающий список разбить на 2 и более).

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