Проверка данных в Excel – для тех, кто ценит свое время
Привет всем. Как часто Вы сталкиваетесь с таблицами общего пользования, которые заполняют несколько сотрудников? Например, Вы формируете базу данных в Эксель, и всю информацию вносит несколько человек. Ошибок не избежать, некорректные данные часто проскакивают в таблицы. Вот если бы Excel мог проверить их перед внесением и указать на ошибку…
А он может! В программу встроен мощный инструмент под названием «Проверка данных», который минимизирует ошибки внесения информации.
Как сделать проверку данных в Excel
Вы можете предусмотреть проверку данных еще на этапе проектировки документа, чтобы пользователь не мог внести ошибочные данные, или добавить проверку к уже существующим массивам.
Чтобы подключить инструмент «Проверка данных», выделите проверяемые ячейки и кликните на ленте « Данные – Работа с данными – Проверка данных ». Откроется окно настройки «модератора»:
На вкладке «Параметры» нужно задать условие для проверки в поле «Тип данных». Есть 8 типов условий для проверки:
- Любое значение – проверка данных отключена
- Целое число – можно ввести лишь целое число с дополнительным критерием;
- Действительное число – любое действительное число с дополнительным критерием
- Список – выбрать значение из заранее заданного списка
- Дата – ввести только дату, удовлетворяющую дополнительному критерию
- Время – ввести время с дополнительным условием
- Длина текста – ввести текст определенной длины
- Другой – ввести значение, которое удовлетворяет записанному вручную логическому выражению
В этом окне можно установить еще две галки:
- Игнорировать пустые ячейки – не применять условие к пустым ячейкам
- Распространить изменения на другие ячейки с тем же условием – найти ячейки с аналогичными условиями и применить к ним такие же изменения
На вкладке «Сообщение для ввода» введите подсказку о том, какие данные нужно внести в ячейку.
На вкладке «Сообщение об ошибке выберите событие, которое произойдет при введении неправильных данных. Сначала сделаем выбор в списке «Вид»:
- Останов – отменить внесение ошибочных данных, вывести заданное сообщение
- Предупреждение – программа сообщит, что значение не соответствует требованиям и уточнит, вносить ли его в ячейку
- Сообщение – примерно то же, что и предупреждение, но выглядит менее устрашающе
После этого запишем заголовок и основной текст для окна-предупреждения.
А теперь поговорим детальнее о каждом из видов условий.
Условие на целое число в Excel
Если в ячейки можно вносить только целые числа – выберите этот тип данных. Станет активным список с дополнительным параметром, в котором нужно задать ограничения. Сделайте выбор:
- Между минимальным и максимальным значением
- Вне заданного промежутка значений
- Равно определенному значению
- Не равно определенному значению
- Больше заданного значения
- Меньше заданного значения
- Больше или равно значению
- Меньше или равно значению
Условие на действительное значение
Действительные числа – все положительные, отрицательные числа, а так же, ноль. От предыдущего пункта это правило отличается тем, что можно вводить и дробные числа. Все остальные настройки – аналогичны описанным выше.
Список
Как я уже говорил, применение этого правила означает, что пользователю будет предложено выбрать вариант для заполнения из заранее составленного списка. Например, Вы продаете несколько марок автомобилей, можно ограничить оператору возможность ввода марки только списком из Вашего ассортимента. Когда пользователь выделит ячейку для заполнения, в ней появится раскрывающийся список с возможными вариантами. Останется только выбрать мышью или клавиатурой нужный.
Чтобы задать список, опишите его в поле источник. Это можно сделать несколькими способами:
- Записать перечень в ячейках на листе книги, а в поле «Источник» указать ссылку на этот массив
- Создать именованный массив, в поле «Источник» указать его имя
- Перечислить пункты прямо в поле «Источник» через точку с запятой
Пользователь может написать и свое значение, отличное от тех, что в списке. Но, если выбран тип проверки «Останов», программа не позволит внести его в ячейку. При других способах проверки, значение будет записано в ячейку, но все равно будет считаться неверным.
Этот фильтр позволит внести в ячейку только дату. Напомню, что дата в Эксель дата – это тоже число, поэтому такое правило перекликается с условием на действительное значение. Тем не менее, ввести можно только дату, с указанием параметров, таких же, как и в первых двух пунктах.
Время
Проверка на время – то же, что и в предыдущем пункте, только задается конкретное время без привязки к дате. Так же, вводится дополнительный параметр для ограничения выбора.
Длина текста
С этой проверкой Вы можете ввести и число и текст, но предлагается задать условие на его длину (в символах). Список параметров все тот же: длина между заданными значениями, вне промежутка, равна какой-то величине и т.п.
Другой
Этот способ проверки самый гибкий. В строке «Формула» Можно задать свою логическую формулу для проверки, если ни один из предыдущих способов проверки Вас не устраивает. Например, чтобы в ячейку А1 вносили только коды, начинающиеся с символов “SUV”, и содержащие 12 символов, внесем в поле формулу: =И(ЛЕВСИМВ(A1;3)=»SUV»;ДЛСТР(A1)=12) . Лишь при выполнении этого правила значение будет считаться верным.
Напомню, функция ЛЕВСИМВ возвращает заданное количество символов слева направо, а ДЛСТР – считает количество символов в ячейке.
Как проверить ячейки Excel на соблюдение условий
Если Вы применили «Проверку данных» к таблице, в которой уже есть какие-то данные, можно проверить их на соблюдение заданных правил и, при необходимости, вручную исправить. Для этого кликните на стрелке вниз возле ленточной команды «Проверка данных», в выпадающем меню выберите «Обвести неверные данные». Excel обведет ячейки с ошибками красным кругом. Осталось только исправить ошибки, круги сами будут исчезать.
Чтобы убрать выделения без исправления, в этом же меню выберите «Удалить обводку неверных данных».
И так, в этом уроке мы рассмотрели инструмент «Проверка данных», который позволяет найти ошибки в заполнении таблиц информацией, а так же, избежать появления новых. Вы знаете, как важно, чтобы данные были «чистыми», ведь их проще обрабатывать, а результаты расчетов будут точнее отображать исследуемые процессы. Так что, лучше взять этот замечательный инструмент на вооружение. Я, таким образом, иногда подстраховываю и себя самого, когда создаю таблицы данных. Думаю, «Проверка данных» будет и для Вас полезным помощником!
Понравилась статья? Считаете ее полезной? Поделитесь с другом – кликните на кнопках социальных сетей, или просто перешлите ссылку!
Я же отправляюсь писать новый важный пост, на этот раз про создание структуры таблицы. Не пропустите, особенно если Ваши таблицы большие и сложные. До встречи!
Извлечение информации о ячейке функцией ЯЧЕЙКА (CELL)
Иногда бывает необходимо с помощью формул узнать о какой-либо ячейке подробную информацию и параметры, чтобы использовать это в расчетах. Например, выяснить число или текст в ячейке или какой числовой формат в ней установлен. Сделать это можно, используя функцию ЯЧЕЙКА (CELL) .
Синтаксис у функции следующий:
=ЯЧЕЙКА( Параметр ; Адрес )
где Адрес — это, понятное дело, ссылка на нужную ячейку, а вот Параметр — это кодовое слово, определяющее какую именно информацию о ячейке мы хотим получить. У этого аргумента несколько возможных значений:
Давайте рассмотрим пару трюков с применением этой функции на практике.
Например, можно получить имя текущего листа формулой, используя функцию ЯЧЕЙКА с параметром "имяфайла" и извлекающей все символы правее закрывающей квадратной скобки:
Также можно проверить тип данных в ячейке (параметр "тип") и выводить сообщение об ошибке вместо вычислений, если введен текст или ячейка пуста:
Или подсветить условным форматированием все незащищенные ячейки в заданном диапазоне: