Проверка данных в Excel: методы и особенности
Табличный редактор Microsoft Excel во время работы требует ввода огромного количества данных. В этом процессе легко можно допустить ошибку, а так как программой многие пользуются для создания отчетности – могут появиться сопутствующие проблемы. Чтобы не допустить таких проблем, разработчиками внедрена особая функция - проверка данных в Excel.
Кроме проверки присутствуют и многие другие функции, которые позволяют осуществлять эту работу. Перечислим существующие типы проверок.
Проверка на введенные значения
Программа Microsoft Office Excel позволяет гибко настроить введенные значения. При этом правильность будет проверяться по одному либо нескольким параметрам. Благодаря этому можно настроить поиск и ячейку.
Числовые проверки
Для записывания в ячейку таблицы можно разрешить только один тип данных. К ним относятся:
- Целочисленные значения – в ячейку можно будет ввести только числа без дробной части. При этом можно также ограничить их значение определенным диапазоном либо запретить ввод отрицательных. Это хорошо подходит для номеров столбцев и строк.
- Действительные числа. В ячейку можно вводить только числовые значения, которые могут включать дробную часть (до десятых). Однако при этом невозможно ввести любой текст. Можно дополнительно вводить дату.
- При выборе параметра "Дата" появится возможность вводить числовые данные только в определенном формате. Для этого осуществляются проверка и поиск по введенному числу, и при неправильном выборе формата появится ошибка. Диапазон значений: от первого января 1900 года до 31 декабря 9999.
- Если выбрать тип ограничений по времени – то, как и в случае с датой, можно будет ввести только временной промежуток в определенном формате. Кроме того, можно ограничить введенные значения, например, введя данные только после полудня. Также существует возможность вводить данные в ячейку программы Microsoft Office Excel при помощи числового эквивалента. Так, 12:00 соответствует число 0.5. Это обуславливается способами хранения данных в Microsoft Office Excel. В приложении за дату и время отвечает дробное число. На примере полудня: 12/24 = 0.5.
Текстовая проверка
Можно ограничить способ введения текста различными методами. Рассмотрим подробнее.
- Проверка данных в Excel по длине введенного текста. Подходит для фамилий и наименований компаний. При этом разрешено вводить в определенную ячейку только заранее заданный объем символов. Также можно вводить числа и даты. Однако здесь существуют свои особенности. Из-за хранения дат в памяти в виде десятичного числа не получится ввести дату позже 13/10/2173, но только если ограничить длительность вводимого значения 5 символами. То же самое относится и к формулам. Если результат формулы слишком длинный, запись в ячейку не будет сделана.
- Список ограничений. Проверка вводимых данных в Excel осуществляется при помощи заранее заданного списка ограничений. При этом можно заранее задать определенный список ограничений. Кроме того, можно задавать значения в списке при помощи ссылки на ячейку либо именованной формулы. Список можно заполнять различными способами.
При помощи формулы
Существует отдельный параметр, если ни один из стандартных не соответствует нуждам пользователя. Проверка данных в Excel в таком случае осуществляет по формуле. В текстовое поле будет предложено ввести формулу, соответствующую желаемому типу значений, которые будут вводиться в ячейку. Таким образом, в Excel проверка данных в ячейке осуществляется более точно.
Некоторые способы ввода.
- Для того чтобы ячейка содержала только текст без символов, можно определить для ввода исключительно текстовые значения. Так как в обычной проверке данных невозможно ограничить вводимые значения и в текстовое поле можно по ошибке ввести число.
- Ограничить введение при условии, что в какой-либо из ячеек значение выходит за пределы заранее заданного диапазона.
- Добавить проверку введенного значения с формулой "ЕСЛИ". В таком случае можно будет ввести только те значения, которые соответствуют истине в формуле. Таким образом, можно, например, не давать вводить ошибочный возраст или суммы денег.
- Кроме того, можно в диапазоне ячеек вводить только определенные значения, которые не будут пересекаться.
При этом существуют ограничения на ввод формул. Поэтому лучше воспользоваться условным форматированием.
Вывод комментария в случае, если ячейка выбрана
Функция проверка данных в Excel имеет возможность добавлять различные комментарии при выборе текущей ячейки, в которой была произведена запись по проверке вводимых значений. Для того чтобы это сделать, нужно в окне проверки перейти в раздел сообщения для ввода и ввести в текстовые поля заголовка и сообщения произвольный текст.
Такой комментарий останется на экране, пока ячейка выделена, что может быть полезно при длительной работе.
Вывод сообщения об ошибке
Как сделать проверку данных в Excel? Если пользователь вводит некорректное значение, то можно вывести сообщение об ошибке и предложить ввести значения заново. По факту приложение полностью соответствует функции MessageBox из встроенного языка программирования в Microsoft Excel Visual Basic Application.
В окне выбора проверки нужно перейти в раздел «Сообщение об ошибке», затем поставить галочку на чекбоксе «Выводить сообщение об ошибке», выбрать тип сообщения, затем ввести заголовок и произвольное сообщение в текстовое поле. Проверка ввода данных в Excel в таком случае осуществляется по двум условиям.
Виды сообщений об ошибке:
- "Остановить" – отменить ввод неправильной информации, вывести заданное сообщение.
- "Предупреждение" – при выборе такого типа сообщения произойдет предупреждение о неправильности введенных данных, программа предложит исправить ошибку.
- "Сообщение" – примерно то же, что и предупреждение, но выглядит менее устрашающе и не предлагает изменить содержимое ячейки.
Использование ссылок на другие листы
В новых версиях программы можно проводить связывание нескольких несвязанных листов, как и в условном форматировании. Для того чтобы было возможно при заполнении условий проверять наличие информации на другом листе, можно воспользоваться функцией получения имен.
Для того чтобы корректно выполнить проверку данных, которые находятся на другом листе, нужно для начала определить имя листа, а также наименование ячейки, затем при помощи ссылочного типа сослаться на полное имя листа и ячейки и корректно ввести данные. Проблема была устранена с обновлением распространяемого пакета Microsoft Office до 2010 года. Однако в более ранних версиях невозможно отобразить на листе проверку данных, которая проводилась этим способом.
Принцип работы проверки данных
Подпрограмма осуществляет свою работу при проверке введенных в ячейку таблицы данных. При этом срабатывает встроенный язык программирования, при корректном значении ошибки не возникнет. Однако в случае если значение не подходит по типу или величине, появится сообщение об ошибке, однако у этого есть свои ограничения.
При попытке ввести данные при помощи вставки из буфера обмена или при автоматическом заполнении проверка не сработает. Кроме того, в случае копирования можно "задеть" правила проверки и в некоторых случаях их удалить.
Как найти ячейку с проверкой данных
Чтобы найти на листе все ячейки, с которыми осуществляется работа и в которых заполнена проверка данных, можно использовать инструмент "Выделение группы ячеек". Он находится в разделе "Главная". Свойство проверки данных этого инструмента позволит выделить все нужные данные в ячейке.