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

Обновлено: 04.07.2024

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

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

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

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

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

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

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

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

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

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

Табличный редактор 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 проверка данных в ячейке осуществляется более точно.

Некоторые способы ввода.

Для того чтобы ячейка содержала только текст без символов, можно определить для ввода исключительно текстовые значения. Так как в обычной проверке данных невозможно ограничить вводимые значения и в текстовое поле можно по ошибке ввести число.
Ограничить введение при условии, что в какой-либо из ячеек значение выходит за пределы заранее заданного диапазона.
Добавить проверку введенного значения с формулой "ЕСЛИ". В таком случае можно будет ввести только те значения, которые соответствуют истине в формуле. Таким образом, можно, например, не давать вводить ошибочный возраст или суммы денег.
Кроме того, можно в диапазоне ячеек вводить только определенные значения, которые не будут пересекаться.

При этом существуют ограничения на ввод формул. Поэтому лучше воспользоваться условным форматированием.

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

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

Использование ссылок на другие листы

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

Для того чтобы корректно выполнить проверку данных, которые находятся на другом листе, нужно для начала определить имя листа, а также наименование ячейки, затем при помощи ссылочного типа сослаться на полное имя листа и ячейки и корректно ввести данные. Проблема была устранена с обновлением распространяемого пакета Microsoft Office до 2010 года. Однако в более ранних версиях невозможно отобразить на листе проверку данных, которая проводилась этим способом.

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

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

Как найти ячейку с проверкой данных

Чтобы найти на листе все ячейки, с которыми осуществляется работа и в которых заполнена проверка данных, можно использовать инструмент "Выделение группы ячеек". Он находится в разделе "Главная". Свойство проверки данных этого инструмента позволит выделить все нужные данные в ячейке.

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

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

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

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

Определение критерия проверки

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

1. Выделите ячейку или диапазон ячеек.

2. Выберите вкладку Данные, область Работа с данными → Проверка данных. Excel отобразит диалоговое окно Проверка вводимых значений.

3. Щелкните на вкладке Параметры (рис. 2).

Рис. 2. Вкладка Параметры диалогового окна Проверка вводимых значений

4. Выберите одну из опций из раскрывающегося списка Тип данных. В зависимости от выбранной опции может измениться внешний вид вкладки Параметры путем добавления или удаления дополнительных элементов управления (для определения формулы нужно выбрать опцию Другой).

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

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

Типы проверяемых данных

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

Во вкладке Параметры диалогового окна Проверка вводимых значений содержатся две опции.

В Excel имеется команда Данные → Работа с данными → Проверка данных → Обвести неверные данные, после выбора которой все неверные значения будут обведены красным кружком (рис. 3).

Рис. 3. Ячейки с неверными значениями (значения которых больше 100) обведены кружками

Создание раскрывающегося списка

Возможно, проверка вводимых данных чаще всего используется для создания раскрывающегося списка значений. На рис. 4 приведен пример, в котором имена месяцев, содержащиеся в диапазоне А1:А12, используются для создания раскрывающегося списка.

Рис. 4. Список, созданный с помощью средства проверки данных

Чтобы создать такой список:

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

2. Выберите ячейку, которая должна содержать раскрывающийся список (в нашем примере – D3).

3. Во вкладке Параметры диалогового окна Проверка вводимых данных выберите тип данных Список и в поле Источник укажите диапазон, который содержит список значений (в нашем примере – $А$1:$А$12).

4. Удостоверьтесь, что установлен флажок Список допустимых значений.

5. Сделайте другие установки в диалоговом окне Проверка вводимых данных, как описано в предыдущем разделе.

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

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

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

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

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

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

Тип ссылок на ячейки в формулах для проверки данных

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

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

1. Выделите диапазон В2:В10 таким образом, чтобы ячейка В2 стала активизированной.

2. Выберите команду Данные → Работа с данными → Проверка данных, чтобы открыть диалоговое окно Проверка вводимых значений.

3. Перейдите на вкладку Параметры и в списке Тип данных выберите Другой.

4. Введите следующую формулу в поле Формула (рис. 5) =ЕНЕЧЁТ(В2). В этой формуле применена функция ЕНЕЧЁТ, которая возвращает значение ИСТИНА, если ее аргумент является нечетным числом.

6. Щелкните на кнопке ОК, чтобы закрыть диалоговое окно Проверка вводимых значений.

Рис. 5. Ввод формулы в диалоговое окно Проверка вводимых значений

Заметьте, что введенная формула содержит ссылку на верхнюю левую ячейку выделенного диапазона. Эта формула должна применяться ко всему диапазону ячеек, поэтому следует ожидать, что каждая ячейка этого диапазона содержит такую же формулу. Поскольку в формуле ссыпка на ячейку относительная, то эта формула изменяется для каждой отдельной ячейки диапазона В2:В10. Чтобы в этом удостовериться, поставьте курсор, например, в ячейку В5, и откройте диалоговое окно Проверка вводимых значений. В этом окне вы должны увидеть формулу =ЕНЕЧЁТ(В5)

В общем случае, когда вводится формула для проверки данных в диапазон ячеек, следует использовать относительную ссылку на активизированную ячейку, которой, как правило, является верхняя левая ячейка выделенного диапазона. Исключение составляют ситуации, когда надо сделать ссылку на некоторую конкретную ячейку. Например, вы хотите, чтобы в диапазон А1:В10 вводились только такие значения, которые превышают значение в ячейке С1. Для этого используется формула =А1>$С$1

В таком случае ссылка на ячейку С1 делается абсолютной и поэтому данная ссылка не меняется во всех ячейках выделенного диапазона.

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

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

Ввод только текста. Для того чтобы разрешить ввод только текста (и запретить ввод числовых значений) в ячейку или диапазон, используется следующая формула: =ЕТЕКСТ(А1). Здесь предполагается, что А1 является активизированной ячейкой выделенного диапазона.

Ввод значений, больших, чем в предыдущей ячейке. Следующая формула проверки данных позволяет ввести число в ячейку только в том случае, если оно больше, чем значение в предыдущей ячейке: =А2>А1. В формуле предполагается, что активизированной ячейкой выделенного диапазона является ячейка А2. Заметьте, что эту формулу нельзя использовать в первой строке рабочего листа.

Ввод только уникальных значений. Следующая формула проверки вводимых данных не позволит пользователю ввести в диапазоне А1:С20 повторяющиеся значения: =СЧЁТЕСЛИ($А$1:$С$20;А1)=1. Здесь предполагается, что А1 является активизированной ячейкой выделенного диапазона. Обратите внимание на то, что в качестве первого аргумента функции СЧЁТЕСЛИ ($А$1:$С$20) используется абсолютная ссылка. Вторым аргументом (А1) является относительная ссылка, которая меняется для каждой ячейки выделенного диапазона. На рис. 6 показано, как работает эта формула. Здесь сделана попытка ввести в ячейку А5 значение 2, которое уже есть в диапазоне А1:С20.

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

Ввод текста, начинающегося с буквы А. В следующей формуле используется прием, который позволяет проводить проверку по заданному символу. В данном случае формула вернет значение ИСТИНА, если ввести в ячейку строку, которая будет начинаться с буквы А (независимо от регистра): =ЛЕВСИМВ(А1)= " а " . В этой формуле предполагается, что активизированной ячейкой выделенного диапазона является ячейка А1.

Ниже приведена немного модифицированная формула проверки данных. С помощью этой формулы можно организовать ввод строки, которая состоит из пяти букв и начинается с буквы А:
=СЧЁТЕСЛИ (А1; " А. " ) =1

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

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

Читайте также: