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

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

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

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

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

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

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

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

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

Сообщение для ввода

Сообщения для ввода возникают, когда юзер выделяет ячейку. Они указывают ему, что необходимо вводить. Перейдите на вкладку Сообщение для ввода (Input Message) и сделайте последующее:

1. Поставьте галочку напротив Показывать подсказку, если ячейка является текущей (Show input message when cell is selected).
2. Введите заголовок.
3. Введите само сообщение.

Сообщение о ошибке

Если юзеры игнорируют сообщение для ввода и вводят недопустимое число, вы сможете показать им извещение о ошибке. Перейдите на вкладку Сообщение о ошибке (Error Alert) и сделайте последующее:

1. Поставьте галочку напротив параметра Выводить сообщение о ошибке (Show error alert after invalid data is entered).
2. Введите заголовок.
3. Введите сообщение о ошибке.

Итог проверки данных

1. Выделите ячейку С2.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Перечень

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

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

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

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

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

Время

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

Длина текста

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

Иной

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

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

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

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

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

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

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

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

Анализ данных Excel — проверка данных

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

Для хоть какой ячейки на листе вы сможете

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

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

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

• Возможность
• Воздействие
• Категория риска
• Источник риска
• Статус

Столбец Risk Exposure будет иметь рассчитанные значения, и вы не можете ввести какие-либо данные. Даже в столбце S. No. установлены расчетные значения, которые корректируются, даже если вы удаляете строчку.

Сейчас вы узнаете, как настроить таковой лист.

Подготовьте структуру для рабочего листа

Чтоб приготовить структуру для рабочего листа —

• Начните с незапятнанного листа.
• Расположите заголовок в строке 2.
• Расположите заглавия столбцов в строке 3.
• Для заголовков столбцов Возможность, Действие и Риск —
  • Щелкните правой клавишей мыши на ячейке.
  • Нажмите на Формат ячеек из выпадающего перечня.
  • В диалоговом окне «Формат ячеек» перейдите на вкладку «Сглаживание».
  • Введите 90 под ориентацией.

  Ваш рабочий лист будет смотреться последующим образом —

  

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

  В ячейках M5 — M13 введите последующие значения (M5 — заголовок, а M6 — M13 — значения)

  • Нажмите на первую ячейку под столбцом Категория риска (H6).
  • Нажмите вкладку ДАННЫЕ на ленте.
  • Нажмите Проверка данных в группе Инструменты данных.
  • Изберите Проверка данных … из раскрывающегося перечня.

  

  Раскроется диалоговое окно «Проверка данных».

  • Нажмите вкладку Опции.
  • В разделе «Аспекты проверки» в раскрывающемся перечне « Разрешить:» изберите « Перечень» .

  

  • Изберите спектр M6: M13 в показавшемся поле Source :.
  • Установите флажки Пропустить пустое поле и раскрывающийся перечень снутри ячейки.

  

  Установить входное сообщение для группы риска

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

  

  Установить оповещение о ошибке для группы риска

  Чтоб установить предупреждение о ошибке —

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

  

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

  Для избранной первой ячейки в группы риска

  • Аспекты проверки данных установлены
  • Входное сообщение установлено
  • Предупреждение о ошибке установлено

  Сейчас вы сможете проверить свои опции.

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

  

  Входное сообщение отображается верно.

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

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

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

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

  

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

  Изберите значение из выпадающего перечня. Возникает в клеточке.

  

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

  В конце концов, попытайтесь ввести неправильную запись и проверьте предупреждение о ошибке.

  Введите People в ячейку и нажмите Enter. Покажется сообщение о ошибке, установленное для ячейки.

  

  • Проверьте сообщение о ошибке.
  • У вас есть возможность или повторить попытку, или отменить. Проверьте оба варианта.

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

  Примечание. Весьма принципиально проверить корректность написания и грамматику ваших сообщений.

  Установите действительные аспекты для столбца группы риска

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

  Сейчас для вас необходимо уяснить две вещи —

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

  Вы не должны устанавливать аспекты для ненужного спектра ячеек либо для всего столбца. Это лишне наращивает размер файла. Это именуется лишним форматированием. Если вы получаете рабочий лист из наружного источника, для вас нужно удалить избыточное форматирование, которое вы узнаете в главе « Запрос» в этом руководстве.

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

  Вы не должны устанавливать аспекты для ненужного спектра ячеек либо для всего столбца. Это лишне наращивает размер файла. Это именуется лишним форматированием. Если вы получаете рабочий лист из наружного источника, для вас нужно удалить избыточное форматирование, которое вы узнаете в главе « Запрос» в этом руководстве.

  Следуйте инструкциям ниже

  • Установите аспекты проверки для 10 ячеек в группы риска.
  • Вы сможете просто создать это, нажав в правом нижнем углу первой ячейки.
  • Удерживайте показавшийся знак + и потяните его вниз.

  

  Проверка данных установлена ​​для всех избранных ячеек.

  Нажмите на крайний избранный столбец и подтвердите.

  

  Проверка данных для столбца Категория риска завершена.

  Установить значения проверки для источника риска

  В этом случае у нас есть лишь два значения — Внутреннее и Наружное.

  Проверка данных в Excel: способы и индивидуальности

  Табличный редактор Microsoft Excel во время работы просит ввода большого количества данных. В этом процессе просто можно допустить ошибку, а потому что программкой почти все пользуются для сотворения отчетности – могут показаться сопутствующие трудности. Чтоб не допустить таковых заморочек, разрабами внедрена особенная функция — проверка данных в Excel.

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

  

  Проверка на введенные значения

  Для вас будет любопытно: Свойство программного обеспечения: эталоны и оценка. Технологическое обеспечение свойства программного обеспечения

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

  Числовые проверки

  

  Для записывания в ячейку таблицы можно разрешить лишь один тип данных. К ним относятся:

  • Целочисленные значения – в ячейку можно будет ввести лишь числа без дробной части. При всем этом можно также ограничить их значение определенным спектром или запретить ввод отрицательных. Это отлично подступает для номеров столбцев и строк.
  • Действительные числа. В ячейку можно вводить лишь числовые значения, которые могут включать дробную часть (до 10-х). Но при всем этом нереально ввести хоть какой текст. Можно добавочно вводить дату.
  • При выбирании параметра «Дата» покажется возможность вводить числовые данные лишь в определенном формате. Для этого осуществляются проверка и поиск по введенному числу, и при неверном выборе формата покажется ошибка. Спектр значений: от первого января 1900 года до 31 декабря 9999.
  • Если избрать тип ограничений по времени – то, как и в случае с датой, можно будет ввести лишь временной просвет в определенном формате. Не считая того, можно ограничить введенные значения, к примеру, введя данные лишь опосля пополудни. Также существует возможность вводить данные в ячейку программки Microsoft Office Excel с помощью числового эквивалента. Так, 12:00 соответствует число 0.5. Это обуславливается методами хранения данных в Microsoft Office Excel. В приложении за дату и время отвечает дробное число. На примере пополудни: 12/24 = 0.5.

  Текстовая проверка

  Для вас будет любопытно: Как обновить Microsoft Office 2016? Три обычных метода

  

  Можно ограничить метод введения текста разными способами. Разглядим подробнее.

  • Проверка данных в Excel по длине введенного текста. Подступает для фамилий и наименований компаний. При всем этом разрешено вводить в определенную ячейку лишь заблаговременно данный размер знаков. Также можно вводить числа и даты. Но тут есть свои индивидуальности. Из-за хранения дат в памяти в виде десятичного числа не получится ввести дату позднее 13/10/2173, но лишь если ограничить продолжительность вводимого значения 5 знаками. То же самое относится и к формулам. Если итог формулы очень длиннющий, запись в ячейку не будет изготовлена.
  • Перечень ограничений. Проверка вводимых данных в Excel осуществляется с помощью заблаговременно данного перечня ограничений. При всем этом можно заблаговременно задать определенный перечень ограничений. Не считая того, можно задавать значения в перечне с помощью ссылки на ячейку или именованной формулы. Перечень можно заполнять разными методами.

  С помощью формулы

  

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

  Некие методы ввода.

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

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

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

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

  

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

  Вывод сообщения о ошибке

  Как создать проверку данных в Excel? Если юзер вводит неточное значение, то можно вывести сообщение о ошибке и предложить ввести значения поновой. По факту приложение стопроцентно соответствует функции MessageBox из встроенного языка программирования в Microsoft Excel Visual Basic Application.

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

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

  Внедрение ссылок на остальные листы

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

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

  Механизм работы проверки данных

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

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

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

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

  Интересно почитать:  Как в эксель проверить орфографию