Содержание

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

Проверка данных в Excel – для тех, кто ценит свое время

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Длина текста

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

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

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

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

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

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

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

Понравилась статья? Считаете ее полезной? Поделитесь с другом – кликните на кнопках социальных сетей, или просто перешлите ссылку!

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

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

Проверка данных является неплохим инструментом 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)

    Статья помогла? Поделись ссылкой с друзьями!

    Руководство по проверке данных 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 будет автоматически расширять или сокращать таблицу при добавлении или удалении значений. Другими словами, Excel будет автоматически синхронизировать раскрывающийся список со значениями в таблице при изменении, добавлении или удалении значений.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Чтобы разрешить только уникальные значения:

    = СЧЁТЕСЛИ (диапазон, А1)

    Разрешить только адрес электронной почты

    = ЕЧИСЛО (НАЙТИ ( «@», A1)

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

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

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

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

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

    Чтобы найти ячейки с примененной проверкой данных, используйте диалоговое окно Переход — Выделить. Введите сочетание клавиш Ctrl + G, затем нажмите кнопку «Выделить». Когда появится окно, выберите «Проверка данных»:

    Скопировать данные проверки из одной ячейки в другую

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

    Примечание: вы можете использовать сочетание клавиш Ctrl + Alt + V, чтобы вызвать Специальную вставку без мыши.

    Очистить все данные проверки

    Чтобы очистить все данные проверки из диапазона ячеек:

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

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

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

    Виды сообщений об ошибке:

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

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

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

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

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

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

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

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

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

    Читать еще:  Как сделать график уравнения в excel?
  • Ссылка на основную публикацию
    ×
    ×
    Adblock
    detector