Проверка данных эксель: Проверка данных в EXCEL. Примеры и описание

Содержание

Проверка данных | Excel для всех

Хитрости » 1 Май 2011 Дмитрий 93874 просмотров
Проверка данных является неплохим инструментом 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: способы и особенности
Табличный редактор 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 года. Однако в более ранних версиях невозможно отобразить на листе проверку данных, которая проводилась этим способом.
Принцип работы проверки данных
Подпрограмма осуществляет свою работу при проверке введенных в ячейку таблицы данных. При этом срабатывает встроенный язык программирования, при корректном значении ошибки не возникнет. Однако в случае если значение не подходит по типу или величине, появится сообщение об ошибке, однако у этого есть свои ограничения.
При попытке ввести данные при помощи вставки из буфера обмена или при автоматическом заполнении проверка не сработает. Кроме того, в случае копирования можно «задеть» правила проверки и в некоторых случаях их удалить.
Как найти ячейку с проверкой данных
Чтобы найти на листе все ячейки, с которыми осуществляется работа и в которых заполнена проверка данных, можно использовать инструмент «Выделение группы ячеек». Он находится в разделе «Главная». Свойство проверки данных этого инструмента позволит выделить все нужные данные в ячейке.
Подсказки и проверки ввода данных в MS Excel
В MS Excel есть ряд очень полезных инструментов для удивительно простого создания по-настоящему сложных форм сбора информации. Однако, вот незадача — чаще всего камнем преткновения во взаимодействии с ними служат не возможности табличного редактора, а человеческая «косорукость». Пользователи стирают формулы из ячеек, вносят некорыстные данные, в общем, портят плоды наших трудов.
К счастью они делают это не со зла, и все что нужно нам — разработчикам, снабдить их подсказками и уже готовыми заранее ответами. Как это сделать, я расскажу в этой статье.
Вот и моя демонстрационная excel-таблица.
Как её заполнять?
Подсказки в MS Excel
Первый способ вставки подсказки
Первым делом разберемся c подсказками. Взгляните на мою табличку. Вроде бы все просто, однако даже при такой простоте можно нагородить солидный огород. К примеру, поле «номер документ» — это №1, 1, или 22.03-1?
Самый проcтой способ вставить примечание в лист MS Excel
Самое простое, это щелкнуть по нужной ячейке правой кнопкой мыши и выбрать пункт «Вставить примечание«. В появившемся поле достаточно написать требуемый текст, и если пользователь поднесет курсор мыши к этой ячейке, текст немедленно появится во всплывающей подсказке.
Примечания вставлено
Обратите внимание: ячейка с примечанием помечена особым значком — маленьким красным треугольником в правом верхнем углу. Сам же текст примечания может быть отформатирован как любой другой — вы можете сделать его жирным или курсивным, чтобы подчеркнуть особо важные моменты своего послания.

Удалить примечания можно только из контекстного меню правой кнопки мыши
Обратите внимание: нажатие кнопки «Delete» в ячейке не удалит примечание. Избавиться от него (или изменить его текст) можно повторно щелкнув в ячейке правой кнопкой мыши и выбрав пункт «Удалить примечание» или «Изменить примечание».
Второй способ вставки подсказки
Ещё один способ ввести подсказку интересен нам только с точки зрения того, о чем пойдет разговор дальше (фильтрации ввода), тем не менее знать о нем полезно.
Примечания в Excel с помощью проверки данных
Поставьте курсор мыши в выбранную ячейку, на панели «Данные» в группе «Работа с данным» откройте инструмент «
Проверка данных«, и заполните поля «заголовок» и «текст сообщения» на вкладке «Сообщение для ввода«. Нажмите «Ок».
Теперь, при щелчке на ячейке с подсказкой (обратите внимание — отмеченная таким способом ячейка не помечается красным треугольником, и выглядит так же как любая другая) также будет выведен его текст в желтом прямоугольнике. Удалить и изменить примечание введенное через «Проверку данных» с помощью правой кнопки мыши, нельзя.
Проверка по числу введенных символов
Подсказки — это хорошо, но что делать с самыми «прогрессивными» пользователями, которые подсказок не читают? Остается одно — бить по рукам… то есть использовать фильтрацию ввода, я хотел сказать.
Снова открываем инструмент «Проверка данных«, переходим на вкладку «Параметры» и смотрим на наши возможности по ограничению возможностей пользователя.
Проверка по числу введенных символов в MS Excel

Первое, что может оказаться полезным, это проверка по длине введенного текста. Выбираем пункт «Длина текста» и указываем любой диапазон ограничений. К примеру, я хочу чтобы текст в поле «Сообщение» был как можно более кратким, но в то же время ячейка не могла оставаться пустой. Значения от 3-х до 25 введенных символов, этого вполне хватит, чтоб вписать что-то вроде «Напоминание об оплате» (21 символ), а при попытке ввести более длинный текст, MS Excel выдаст окно-предупреждение и не даст завершить ввод.
Слишком длинный текст! Excel стоит на страже и не пропустит такой очевидной ошибки
Проверка по числу
Ещё один хороший способ фильтрации который пригодится без сомнения: фильтрация по числу. Установите фильтр подобный указанному на рисунке и навсегда избавьтесь от проблем с тем, что в поле цена появляются буквы, пробелы и лишние символы.

Проверка по числу в Excel
Фильтрация на ввод чисел работает ничуть не хуже
Выбор из имеющегося списка значений
Самая мощная и интересная проверка ввода в MS Excel — выборка из выпадающего списка. Она подойдет не для всех типов ячеек, но там где нужна точность, а диапазон значений не слишком велик, выборка по списку это то что надо.
А теперь, сделаем проверку по выпадающему списку в MS Excel. Данные введем вручную, через точку с запятой.
Выбираем в «Проверке данных» вкладку «Параметры«, и указываем «Тип данных» → «Список». Ввести «предустановленные» значения можно двумя способами:
Выбрать диапазон данных с помощью кнопки «Диапазон данных».
Вписать значения выпадающего списка вручную, через точку с запятой.
…или выберем из диапазона
Второй способ более интересен, так как данные «спрятаны» от пользователя, зато набить их больше десятка подряд — проблематично. Первый способ позволяет без проблем задать намного больший список для выбора данных, однако прежде придется этот список разместить где-то в документе (каждое значение на отдельной строке), например на отдельном «служебном» листе в той же книге MS Excel.

Выбор из списка на листе MS Excel. Других данных ввести не удастся.
Вот и готово — теперь заполнить ячейку можно лишь теми значениями, что мы указали в списке. При попытке ввести данные вручную, MS Excel выдаст нам уже знакомую ошибку.
Кстати, можно изменить и её текст — для этого перейдите на вкладку «Сообщение для вывода» в «Проверке данных», как мы уже делали при «втором способе» вставке подсказок, измените текст на требуемый.
4 техники анализа данных в Microsoft Excel
Юлия Перминова
Тренер Учебного центра Softline с 2008 года.

1. Сводные таблицы
Базовый инструмент для работы с огромным количеством неструктурированных данных, из которых можно быстро сделать выводы и не возиться с фильтрацией и сортировкой вручную. Сводные таблицы можно создать с помощью нескольких действий и быстро настроить в зависимости от того, как именно вы хотите отобразить результаты.
Полезное дополнение. Вы также можете создавать сводные диаграммы на основе сводных таблиц, которые будут автоматически обновляться при их изменении. Это полезно, если вам, например, нужно регулярно создавать отчёты по одним и тем же параметрам.
Как работать
Исходные данные могут быть любыми: данные по продажам, отгрузкам, доставкам и так далее.
Откройте файл с таблицей, данные которой надо проанализировать.
Выделите диапазон данных для анализа.
Перейдите на вкладку «Вставка» → «Таблица» → «Сводная таблица» (для macOS на вкладке «Данные» в группе «Анализ»).

Должно появиться диалоговое окно «Создание сводной таблицы».
Настройте отображение данных, которые есть у вас в таблице.
Перед нами таблица с неструктурированными данными. Мы можем их систематизировать и настроить отображение тех данных, которые есть у нас в таблице. «Сумму заказов» отправляем в «Значения», а «Продавцов», «Дату продажи» — в «Строки». По данным разных продавцов за разные годы тут же посчитались суммы. При необходимости можно развернуть каждый год, квартал или месяц — получим более детальную информацию за конкретный период.
Набор опций будет зависеть от количества столбцов. Например, у нас пять столбцов. Их нужно просто правильно расположить и выбрать, что мы хотим показать. Скажем, сумму.
Можно её детализировать, например, по странам. Переносим «Страны».
Можно посмотреть результаты по продавцам. Меняем «Страну» на «Продавцов». По продавцам результаты будут такие.
2. 3D-карты
Этот способ визуализации данных с географической привязкой позволяет анализировать данные, находить закономерности, имеющие региональное происхождение.
Полезное дополнение. Координаты нигде прописывать не нужно — достаточно лишь корректно указать географическое название в таблице.
Как работать
Откройте файл с таблицей, данные которой нужно визуализировать. Например, с информацией по разным городам и странам.
Подготовьте данные для отображения на карте: «Главная» → «Форматировать как таблицу».
Выделите диапазон данных для анализа.
На вкладке «Вставка» есть кнопка 3D-карта.
Точки на карте — это наши города. Но просто города нам не очень интересны — интересно увидеть информацию, привязанную к этим городам. Например, суммы, которые можно отобразить через высоту столбика. При наведении курсора на столбик показывается сумма.
Также достаточно информативной является круговая диаграмма по годам. Размер круга задаётся суммой.
3. Лист прогнозов
Зачастую в бизнес-процессах наблюдаются сезонные закономерности, которые необходимо учитывать при планировании. Лист прогноза — наиболее точный инструмент для прогнозирования в Excel, чем все функции, которые были до этого и есть сейчас. Его можно использовать для планирования деятельности коммерческих, финансовых, маркетинговых и других служб.
Полезное дополнение. Для расчёта прогноза потребуются данные за более ранние периоды. Точность прогнозирования зависит от количества данных по периодам — лучше не меньше, чем за год. Вам требуются одинаковые интервалы между точками данных (например, месяц или равное количество дней).
Как работать
Откройте таблицу с данными за период и соответствующими ему показателями, например, от года.
Выделите два ряда данных.
На вкладке «Данные» в группе нажмите кнопку «Лист прогноза».
В окне «Создание листа прогноза» выберите график или гистограмму для визуального представления прогноза.
Выберите дату окончания прогноза.
В примере ниже у нас есть данные за 2011, 2012 и 2013 годы. Важно указывать не числа, а именно временные периоды (то есть не 5 марта 2013 года, а март 2013-го).
Для прогноза на 2014 год вам потребуются два ряда данных: даты и соответствующие им значения показателей. Выделяем оба ряда данных.
На вкладке «Данные» в группе «Прогноз» нажимаем на «Лист прогноза». В появившемся окне «Создание листа прогноза» выбираем формат представления прогноза — график или гистограмму. В поле «Завершение прогноза» выбираем дату окончания, а затем нажимаем кнопку «Создать». Оранжевая линия — это и есть прогноз.
4. Быстрый анализ
Эта функциональность, пожалуй, первый шаг к тому, что можно назвать бизнес-анализом. Приятно, что эта функциональность реализована наиболее дружественным по отношению к пользователю способом: желаемый результат достигается буквально в несколько кликов. Ничего не нужно считать, не надо записывать никаких формул. Достаточно выделить нужный диапазон и выбрать, какой результат вы хотите получить.
Полезное дополнение. Мгновенно можно создавать различные типы диаграмм или спарклайны (микрографики прямо в ячейке).
Как работать
Откройте таблицу с данными для анализа.
Выделите нужный для анализа диапазон.
При выделении диапазона внизу всегда появляется кнопка «Быстрый анализ». Она сразу предлагает совершить с данными несколько возможных действий. Например, найти итоги. Мы можем узнать суммы, они проставляются внизу.
В быстром анализе также есть несколько вариантов форматирования. Посмотреть, какие значения больше, а какие меньше, можно в самих ячейках гистограммы.
Также можно проставить в ячейках разноцветные значки: зелёные — наибольшие значения, красные — наименьшие.
Надеемся, что эти приёмы помогут ускорить работу с анализом данных в Microsoft Excel и быстрее покорить вершины этого сложного, но такого полезного с точки зрения работы с цифрами приложения.
Читайте также:
Как включить анализ данных в Excel: рабочая инструкция
Программа Excel – это не просто табличный редактор, но ещё и мощный инструмент для различных математических и статистических вычислений. В приложении имеется огромное число функций, предназначенных для этих задач. Правда, не все эти возможности по умолчанию активированы. Именно к таким скрытым функциям относится набор инструментов «Анализ данных». Давайте выясним, как его можно включить.
Включение блока инструментов
Чтобы воспользоваться возможностями, которые предоставляет функция «Анализ данных», нужно активировать группу инструментов «Пакет анализа», выполнив определенные действия в настройках Microsoft Excel. Алгоритм этих действий практически одинаков для версий программы 2010, 2013 и 2016 года, и имеет лишь незначительные отличия у версии 2007 года.
Активация
Перейдите во вкладку «Файл». Если вы используете версию Microsoft Excel 2007, то вместо кнопки «Файл» нажмите значок Microsoft Office в верхнем левом углу окна.
Кликаем по одному из пунктов, представленных в левой части открывшегося окна – «Параметры».
В открывшемся окне параметров Эксель переходим в подраздел «Надстройки» (предпоследний в списке в левой части экрана).
В этом подразделе нас будет интересовать нижняя часть окна. Там представлен параметр «Управление». Если в выпадающей форме, относящейся к нему, стоит значение отличное от «Надстройки Excel», то нужно изменить его на указанное. Если же установлен именно этот пункт, то просто кликаем на кнопку «Перейти…» справа от него.
Открывается небольшое окно доступных надстроек. Среди них нужно выбрать пункт «Пакет анализа» и поставить около него галочку. После этого, нажать на кнопку «OK», расположенную в самом верху правой части окошка.
После выполнения этих действий указанная функция будет активирована, а её инструментарий доступен на ленте Excel.
Запуск функций группы «Анализ данных»
Теперь мы можем запустить любой из инструментов группы «Анализ данных».
Переходим во вкладку «Данные».
В открывшейся вкладке на самом правом краю ленты располагается блок инструментов «Анализ». Кликаем по кнопке «Анализ данных», которая размещена в нём.
После этого запускается окошко с большим перечнем различных инструментов, которые предлагает функция «Анализ данных». Среди них можно выделить следующие возможности:
Корреляция;
Гистограмма;
Регрессия;
Выборка;
Экспоненциальное сглаживание;
Генератор случайных чисел;
Описательная статистика;
Анализ Фурье;
Различные виды дисперсионного анализа и др.
Выбираем ту функцию, которой хотим воспользоваться и жмем на кнопку «OK».
Работа в каждой функции имеет свой собственный алгоритм действий. Использование некоторых инструментов группы «Анализ данных» описаны в отдельных уроках.
Урок: Корреляционный анализ в Excel
Урок: Регрессионный анализ в Excel
Урок: Как сделать гистограмму в Excel
Как видим, хотя блок инструментов «Пакет анализа» и не активирован по умолчанию, процесс его включения довольно прост. В то же время, без знания четкого алгоритма действий вряд ли у пользователя получится быстро активировать эту очень полезную статистическую функцию.
Мы рады, что смогли помочь Вам в решении проблемы.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
ДА НЕТ
Excel. Проверка данных — PDF Free Download
Виды ошибок при задании формул
Виды ошибок при задании формул Формула в Microsoft Excel представляет собой синтаксическую конструкцию, начинающуюся со знака равенства (=) и предназначенную для обработки данных с последующим помещением
Подробнее PDF created with pdffactory Pro trial version
Практическая работа 3. 9. Обработка информации, структурированной в виде списков MS Excel Цель работы. Выполнив эту работу, Вы научитесь: выполнять контроль ввода данных в таблицу; использовать инструмент
Подробнее Содержание. Microsoft Excel 1
Содержание Содержание…1 Табличный процессор Microsoft Excel…2 Функции…3 Сообщения об ошибках…4 Формат данных…4 Числовой…4 Маски форматов…5 Примеры использования масок…6 Выравнивание содержимого
Подробнее Тема: Фильтрация данных
Тема: Фильтрация данных Отфильтровать список показать только те записи, которые удовлетворяют заданному критерию. Excel предоставляет две команды для фильтрации данных: 1. Автофильтр (для простых условий
Подробнее Вычисления в Microsoft Excel 2010/2007
Вычисления в Microsoft Excel 2010/2007 Вычисления в Microsoft Excel 2010/2007 План занятия Создание формул Копирование формул Относительные и абсолютные ссылки Использование в формулах, данных с других
Подробнее Подготовка к работе с Excel
ГЛАВА 1 Подготовка к работе с Excel Многие читатели в большей или меньшей степени знакомы с электронными таблицами Excel. Тем не менее необходимо дать определение терминов, наиболее часто встречающихся
Подробнее Поиск и замена данных Поиск данных
Поиск и замена данных Поиск данных Поиск данных можно производить на всем листе или в выделенной области листа, например, только в некоторых столбцах или строках, а также сразу во всей книге. 1. В группе
Подробнее Поля страницы. Автор: Автор :14
На вкладке Поля диалогового окна Параметры страницы в полях верхнее, нижнее, левое и правое устанавливаются отступы от края страниц до таблицы. От величины отступов зависит высота и ширина поля таблицы,
Подробнее Работа с базами данных в MS Excel
Работа с базами данных в MS Excel База данных (или в терминах MS Excel — список) представляет собой упорядоченную информацию, объединенную в единое целое. Строки в базе данных называются записями, а столбцы
Подробнее Создание формы. Практическая работа 3
Практическая работа 3 Создание формы Форма это объект базы данных, который можно использовать для ввода, изменения или отображения данных из таблицы или запроса. Формы могут применяться для управления
Подробнее Excel. Имена диапазонов
Excel. Имена диапазонов Возможно, вам приходилось работать с листами, в которых использовалась, формула типа: =СУММ(А5000:А5078). Вы гадали, что же находится в ячейках А5000:А5078!? Если в ячейках А5000:А5078
Подробнее 3.4. Работа с электронными таблицами
3.4. Работа с электронными таблицами 3.4.1. Пользовательский интерфейс программы Microsoft Excel. Создание и редактирование таблиц Документ в программе Microsoft Excel (MS Excel) называется рабочей книгой,
Подробнее Пользовательский формат числа в Excel
Пользовательский формат числа в Excel Excel предоставляет достаточно много встроенных числовых форматов, однако возможны ситуации, в которых ни один вам не подойдет. В таком случае попробуйте создать собственный
Подробнее Excel. Создание указателя листов в книге
Excel. Создание указателя листов в книге Один посетитель сайта (Валерий) недавно спросил: «Есть ли инструмент поиска листа в книге по его названию». Возможное решение просто выбрать лист из списка (рис.
Подробнее Лабораторная работа 3 Подбор параметров
1. Введение Лабораторная работа 3 Подбор параметров При решении различных задач часто приходится заниматься проблемой подбора одного значения путем изменения другого. Для этой цели весьма эффективно используется
Подробнее Знакомство с Microsoft Excel
Знакомство с Micrsft Excel 1. Структура таблицы 1. Электронная таблица состоит из ячеек, находящихся на пересечении строк и столбцов. Строки нумеруются числами, столбцы — латинскими буквами. Каждая ячейка
Подробнее Сервис «Задачи» Содержание:
Сервис «Задачи» Содержание: Введение… 2 1. Начало работы. 3 1.1 Запуск «Журнала задач» 3 1.2 Добавление задач 3 1.3 Редактирование-просмотр задач. 4 2. Задача тип «Напоминание». 6 3. Задача тип «Задача»
Подробнее Организация данных на листе
Стр. 1 из 24 Организация данных на листе Способы организации данных Существует два способа организации данных на листе: таблица и список. При организации данных в виде таблицы формируются строки и столбцы
Подробнее Работа с программой ABBYY FineReader Банк 7
Работа с программой ABBYY FineReader Банк 7 Руководство пользователя ABBYY 2013 год Обработка документов в программе ABBYY FineReader Банк состоит из четырех этапов: Загрузка Распознавание Проверка Выгрузка
Подробнее Сортировка данных сводной таблицы в Excel 2013
Сортировка данных сводной таблицы в Excel 2013 Сортировка данных, находящихся в области строк и столбцов сводной таблицы, по умолчанию выполняется в порядке возрастания (рис. 1а) либо с применением пользовательских
Подробнее Использование электронных таблиц. MS Excel.
Практикум: Использование электронных таблиц. MS Excel. Задание 1. Работа с листами, сохранение рабочей книги 1. Открыть MS Excel. 2. Щелкнуть правой кнопкой мыши по ярлычку «Лист1». 3. В появившемся контекстном
Подробнее Организация и работа с базой данных в Excel
Организация и работа с базой данных в Excel Совокупность строк определенной структуры, содержащих информацию о множестве однотипных объектов, является простейшей базой данных (БД). Она представляет собой
Подробнее ЛАБОРАТОРНАЯ РАБОТА 6
ЛАБОРАТОРНАЯ РАБОТА 6 1. ТЕМА: «Создание и форматирование таблиц в текстовом документе» 2. ОБЩАЯ ЦЕЛЬ РАБОТЫ: получить навыки создания, редактирования, форматирования таблиц и выполнения вычислений в документах
Подробнее Работа с таблицами в MS Word
Работа с таблицами в MS Word Способы создания таблиц 1. Вкладка Вставка Таблица — Вставить таблицу. Способы создания таблиц В диалоговом окне указать число строк и столбцов. 2. Вкладка Вставка Таблица
Подробнее Создание тестов с помощью Google-форм
Создание тестов с помощью Google-форм Цель: научиться создавать тесты c автоматизированным результатом проверки с использованием Google-форм. Задачи: Познакомиться с Google-формами для создания тестов
Подробнее Microsoft Excel Ayupov R.X.
Microsoft Excel 2010 Ayupov R.X. Знакомство с MS Excel Электронная таблица это таблица в электронном виде, в ячейках которой записаны данные различных типов: тексты, даты, формулы, числа. Для управления
Подробнее Создание и использование форм
Глава 8 Создание и использование форм Как уже отмечалось в главах 1 и 2 этой книги, такие объекты базы данных, как формы, предназначены в первую очередь для работы одновременно только с одной записью.
Подробнее Работа с табличным процессором Microsoft Excel
Работа с табличным процессором Microsoft Excel Краткие теоретические сведения Приложение Windows Excel позволяет формировать и выводить на печать документы, представленные в табличном виде, выполнять расчеты
Подробнее Тема 1 Построение графиков функций
Тема Построение графиков функций Теоретический материал по построению графика функции в MathCAD Прочитать теорию Раздел Построение графика функции. Построение графика функции в MathCAD График функции —
Подробнее Глава 5 Финансовые расчеты
Глава 5 Финансовые расчеты Данная глава посвящена приемам создания небольших электронных таблиц. Однако используемые при этом приемы рассматриваются здесь в несколько ином разрезе, нежели в предыдущей
Подробнее 1. Вставка и создание таблиц в Word 2007
1. Вставка и создание таблиц в Word 2007 Таблицы Word применяются для структурирования содержимого страницы. Кроме того, таблицы используются для вычислений. В Word используется технология вставки и создания
Подробнее Общие сведения о таблицах
Общие сведения о таблицах Таблицы в документах Word используют, большей частью, для упорядочивания представления данных. В таблицах можно производить вычисления, таблицы можно применять для создания бланков
Подробнее 3. Основы работы в MicrosoftWord 2010
Как открыть программу Microsoft Word 2010 3. Основы работы в MicrosoftWord 2010 MicrosoftWord это многофункциональная программа обработки (редактор) текстов настольная издательская система. В Word е мы
Подробнее MS Excel (цифровые таблицы)
Электронные таблицы Разнообразие электронных таблиц в приложениях MS Excel (цифровые таблицы) SulSim (вывод данных в Excel) HYSYS (собств. Электр. таблицы) Maple (символьные таблицы) Введение Область применения
Подробнее занятие 6 Иксанова Э.Б.
занятие 6 Иксанова Э.Б. Информатика и ИТвПД Тема 5.1.2. Проведение вычислений с применением функций. Оглавление АВТОЗАПОЛНЕНИЕ… 2 СТАНДАРТНЫЙ СПОСОБ АВТОЗАПОЛНЕНИЯ… 2 СПИСКИ ДЛЯ АВТОЗАПОЛНЕНИЯ… 3
Подробнее ЛАБОРАТОРНАЯ РАБОТА 4
ЛАБОРАТОРНАЯ РАБОТА 4 1. ТЕМА: «Настройки текстового процессора Microsoft Word» 2. ОБЩАЯ ЦЕЛЬ РАБОТЫ: получить навыки выполнения первичных настроек текстового редактора; ознакомления с элементами строки
Подробнее Руководство по проверке данных
Excel | Exceljet
Введение
Проверка данных — это функция Excel, используемая для управления тем, что пользователь может вводить в ячейку. Например, вы можете использовать проверку данных, чтобы убедиться, что значение представляет собой число от 1 до 6, убедитесь, что дата наступит в следующие 30 дней, или убедитесь, что текстовая запись содержит менее 25 символов.
Проверка данных может просто отображать сообщение пользователю о том, что ему разрешено, как показано ниже:
Проверка данных также может остановить недопустимый ввод данных пользователем.Например, если код продукта не прошел проверку, вы можете отобразить такое сообщение:
Кроме того, проверка данных может использоваться, чтобы предоставить пользователю предопределенный выбор в раскрывающемся меню:
Это может быть удобный способ дать пользователю точные значения, соответствующие требованиям.
Контроль достоверности данных
Проверка данных осуществляется с помощью правил, определенных в пользовательском интерфейсе Excel на вкладке «Данные» на ленте.
Важное ограничение
Важно понимать, что проверку данных легко обойти. Если пользователь копирует данные из ячейки без проверки в ячейку с проверкой данных, проверка уничтожается (или заменяется). Проверка данных — хороший способ сообщить пользователям, что разрешено или ожидается, но , а не — надежный способ гарантировать ввод.
Определение правил проверки данных
Проверка данных определяется в окне с 3 вкладками: Настройки, Входное сообщение и Предупреждение об ошибке:
На вкладке настроек вы вводите критерии проверки.Существует ряд встроенных правил проверки с различными параметрами, или вы можете выбрать Пользовательский и использовать свою собственную формулу для проверки ввода, как показано ниже:
Вкладка «Входное сообщение» определяет сообщение, которое будет отображаться при выборе ячейки с правилами проверки. Это входное сообщение является необязательным. Если входное сообщение не задано, сообщение не появляется, когда пользователь выбирает ячейку с примененной проверкой данных. Входное сообщение не влияет на то, что пользователь может ввести — оно просто отображает сообщение, чтобы пользователь знал, что разрешено или ожидается.
Вкладка «Предупреждение об ошибке» определяет, как выполняется проверка. Например, если для стиля установлено значение «Стоп», недопустимые данные вызывают окно с сообщением, и ввод не разрешен.
Пользователь видит такое сообщение:
Если для стиля задано значение «Информация» или «Предупреждение», отображается другой значок с настраиваемым сообщением, но пользователь может проигнорировать сообщение и ввести значения, не прошедшие проверку. В таблице ниже приводится сводка поведения для каждого варианта предупреждения об ошибке.
Стиль предупреждения Поведение
Стоп Запрещает пользователям вводить недопустимые данные в ячейку. Пользователи могут повторить попытку, но должны ввести значение, прошедшее проверку данных. В окне «Остановить предупреждение» есть два варианта: «Повторить» и «Отменить».
Предупреждение Предупреждает пользователей, что данные недействительны. Предупреждение ничего не делает, чтобы остановить недопустимые данные. В окне предупреждения есть три варианта: Да (принять недопустимые данные), Нет (изменить недопустимые данные) и Отменить (удалить недопустимые данные).
Информация Сообщает пользователям, что данные недействительны. Это сообщение ничего не делает для предотвращения неверных данных. В окне информационного предупреждения есть 2 варианта: ОК, чтобы принять недопустимые данные, и Отмена, чтобы удалить их.
Варианты проверки данных
При создании правила проверки данных доступно восемь опций для проверки ввода данных пользователем:
Любое значение — проверка не выполняется. Примечание: если проверка данных ранее применялась с заданным входным сообщением, сообщение все равно будет отображаться при выборе ячейки, даже если выбрано любое значение.
Whole Number — разрешены только целые числа. После выбора варианта целого числа становятся доступны другие параметры для дальнейшего ограничения ввода. Например, вы можете потребовать целое число от 1 до 10.
Decimal — работает как опция целых чисел, но допускает десятичные значения. Например, если параметр «Десятичный формат» настроен на разрешение значений от 0 до 3, допустимы все значения вроде 0,5, 2,5 и 3,1.
Список — разрешены только значения из предопределенного списка.Значения представлены пользователю в виде раскрывающегося меню. Допустимые значения могут быть жестко заданы непосредственно на вкладке «Настройки» или указаны в виде диапазона на листе.
Дата — разрешены только даты. Например, вы можете указать дату между 1 января 2018 г. и 31 декабря 2021 г. или дату после 1 июня 2018 г.
Время — разрешены только времена. Например, вы можете указать время с 9:00 до 17:00 или разрешить время только после 12:00.
Длина текста — проверяет ввод по количеству символов или цифр. Например, вам может потребоваться код, содержащий 5 цифр.
Custom — проверяет ввод данных пользователем с использованием настраиваемой формулы. Другими словами, вы можете написать свою собственную формулу для проверки ввода. Пользовательские формулы значительно расширяют возможности проверки данных. Например, вы можете использовать формулу, чтобы убедиться, что значение написано в верхнем регистре, значение содержит «xyz» или дата является днем недели в следующие 45 дней.
На вкладке настроек также есть два флажка:
Игнорировать пустое поле — указывает Excel не проверять ячейки, не содержащие значения. На практике кажется, что этот параметр влияет только на команду «обвести недопустимые данные». Если этот параметр включен, пустые ячейки не обведены кружком, даже если они не прошли проверку.
Применить эти изменения к другим ячейкам с такими же настройками. — этот параметр обновит проверку, применяемую к другим ячейкам, когда она совпадает с (исходной) проверкой редактируемых ячеек.
Примечание. Вы также можете вручную выбрать все ячейки с проверкой данных с помощью «Перейти к + специальное», как описано ниже.
Простое раскрывающееся меню
Вы можете предоставить раскрывающееся меню параметров, жестко закодировав значения в поле настроек или выбрав диапазон на листе. Например, чтобы ограничить ввод действиями «КУПИТЬ», «УДЕРЖАТЬ» или «ПРОДАТЬ», вы можете ввести эти значения, разделенные запятыми, как показано ниже:
При применении к ячейке на листе раскрывающееся меню работает следующим образом:
Еще один способ указать значения в раскрывающемся меню — использовать ссылку на лист.Например, для размеров (то есть малых, средних и т. Д.) В диапазоне F3: F6 вы можете указать этот диапазон непосредственно в окне настроек проверки данных:
Обратите внимание, что диапазон вводится как абсолютный адрес, чтобы предотвратить его изменение, когда проверка данных применяется к другим ячейкам.
Совет. Щелкните значок маленькой стрелки в дальнем правом углу поля источника, чтобы сделать выбор прямо на листе, чтобы не вводить диапазон вручную.
Вы также можете использовать именованные диапазоны для указания значений.Например, с именованным диапазоном, называемым «размеры» для F3: F7, вы можете ввести имя прямо в окне, начиная со знака равенства:
Именованные диапазоны автоматически являются абсолютными, поэтому они не изменятся, поскольку проверка данных применяется к разным ячейкам. Если именованные диапазоны новы для вас, на этой странице есть хороший обзор и ряд советов по теме.
Совет — если вы используете таблицу для значений раскрывающегося списка, Excel будет продолжать расширять или сжимать таблицу автоматически при добавлении или удалении значений раскрывающегося списка.Другими словами, Excel будет автоматически синхронизировать раскрывающийся список со значениями в таблице при изменении, добавлении или удалении значений. Если вы новичок в таблицах Excel, вы можете увидеть демонстрацию в этом видео о ярлыках таблиц.
Проверка данных с помощью специальной формулы
Формулы проверки данных должны быть логическими формулами, которые возвращают ИСТИНА, если ввод действителен, и ЛОЖЬ, если ввод недопустим. Например, чтобы разрешить ввод любого числа в ячейку A1, вы можете использовать функцию ISNUMBER в такой формуле:

Если пользователь вводит значение, подобное 10, в A1, ISNUMBER возвращает TRUE и проверка данных завершается успешно.Если они вводят значение типа «яблоко» в A1, ISNUMBER возвращает FALSE, и проверка данных не выполняется.
Чтобы включить проверку данных с помощью формулы, выберите «Пользовательский» на вкладке настроек, затем введите формулу в строке формул, как обычно, начиная со знака равенства (=).
Формулы поиска неисправностей
Excel игнорирует формулы проверки данных, которые возвращают ошибки. Если формула не работает, и вы не можете понять почему, настройте фиктивные формулы, чтобы убедиться, что формула работает так, как вы ожидаете.Фиктивные формулы — это просто формулы проверки данных, вводимые непосредственно на листе, чтобы вы могли легко увидеть, что они возвращают. На экране ниже показан пример:
Как только фиктивная формула будет работать так, как вы хотите, просто скопируйте ее и вставьте в область формулы проверки данных.
Если эта фиктивная формула вас смущает, посмотрите это видео, в котором показано, как использовать фиктивные формулы для улучшения формул условного форматирования. Концепция точно такая же.
Примеры формул проверки данных
Возможности пользовательских формул для проверки данных практически безграничны. Вот несколько примеров, которые вдохновят вас:
Чтобы разрешить только 5-символьные значения, начинающиеся с «z», вы можете использовать:

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

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

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

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

Найти ячейки с проверкой данных
Чтобы найти ячейки с примененной проверкой данных, воспользуйтесь диалоговым окном «Перейти к> Специальный». Введите сочетание клавиш Control + G, затем нажмите кнопку Special. Когда появится диалоговое окно, выберите «Проверка данных»:
.
Копировать данные проверки из одной ячейки в другую
Для копирования проверки из одной ячейки в другие ячейки.Скопируйте ячейки, которые обычно содержат нужные данные для проверки, затем используйте Специальная вставка + Проверка. Когда появится диалоговое окно, введите «n», чтобы выбрать проверку, или щелкните «Проверка» с помощью мыши.
Примечание: вы можете использовать сочетание клавиш Control + Alt + V, чтобы вызвать Специальную вставку без мыши.
Очистить проверку всех данных
Чтобы отменить все проверки данных для диапазона ячеек, сделайте выбор, затем нажмите кнопку «Проверка данных» на вкладке «Данные» на ленте.Затем нажмите кнопку «Очистить все»:
Чтобы очистить всю проверку данных на листе, выберите весь лист, затем выполните те же шаги, что и выше.
Как использовать проверку данных в Excel — настраиваемые правила и формулы проверки
В этом руководстве показано, как создавать собственные правила проверки данных в Excel. Вы найдете несколько примеров формул проверки данных E xcel, позволяющих разрешить только числа или текстовые значения в определенных ячейках или только текст, начинающийся с определенных символов, разрешить уникальные данные, предотвращающие дублирование, и многое другое.
Во вчерашнем учебном пособии мы начали изучать проверку данных Excel — каково ее предназначение, как она работает и как использовать встроенные правила для проверки данных в ваших таблицах. Сегодня мы собираемся сделать еще один шаг и поговорить о мельчайших деталях проверки пользовательских данных в Excel, а также поэкспериментировать с несколькими различными формулами проверки.
Как создать настраиваемое правило проверки на основе формулы
Microsoft Excel имеет несколько встроенных правил проверки данных для чисел, дат и текста, но они охватывают только самые простые сценарии.Если вы хотите проверять ячейки по своим критериям, создайте настраиваемое правило проверки на основе формулы. Вот как:
Выберите одну или несколько ячеек для проверки.
Откройте диалоговое окно «Проверка данных». Для этого нажмите кнопку Data Validation на вкладке Data в группе Data Tools или нажмите комбинацию клавиш Alt> D> L (каждую клавишу нужно нажимать отдельно).
На вкладке Настройки диалогового окна Проверка данных выберите Пользовательский в поле Разрешить и введите формулу проверки данных в поле Формула .
Щелкните ОК .
При желании вы можете добавить настраиваемое сообщение ввода и предупреждение об ошибке, которое будет отображаться, когда пользователь выбирает проверенную ячейку или вводит недопустимые данные соответственно.
Ниже вы найдете несколько примеров пользовательских правил проверки для разных типов данных.
Примечание. Все правила проверки данных Excel, встроенные и настраиваемые, проверяют только новые данные, введенные в ячейку после создания правила. Скопированные данные не проверяются, равно как и ввод данных в ячейку перед созданием правила.Чтобы закрепить существующие записи, которые не соответствуют вашим критериям проверки данных, используйте функцию Circle Invalid Data , как показано в разделе Как найти недопустимые данные в Excel.
Проверка данных Excel, разрешающая только числа
Удивительно, но ни одно из встроенных правил проверки данных Excel не обслуживает очень типичную ситуацию, когда вам нужно ограничить пользователей вводом только чисел в определенные ячейки. Но это легко сделать с помощью специальной формулы проверки данных, основанной на функции ЕЧИСЛО, например этой:
= НОМЕР (C2)
Где C2 — это самая верхняя ячейка диапазона, который вы хотите проверить.
Примечание. Функция ЕЧИСЛО позволяет использовать любые числовые значения в проверенных ячейках, включая целые числа, десятичные дроби, дроби, а также даты и время, которые также являются числами в терминах Excel.
Проверка данных Excel для разрешения только текста
Если вы ищете противоположное — разрешить ввод только текста в заданном диапазоне ячеек, создайте собственное правило с функцией ISTEXT, например:
= ISTEXT (D2)
Где D2 — самая верхняя ячейка выбранного диапазона.
Разрешить текст, начинающийся с определенного символа (ов)
Если все значения в определенном диапазоне должны начинаться с определенного символа или подстроки, выполните проверку данных Excel на основе функции СЧЁТЕСЛИ с подстановочным знаком:
СЧЁТЕСЛИ ( ячейка , « текст *»)
Например, чтобы гарантировать, что все идентификаторы заказов в столбце A начинаются с префикса «AA-», «aa-», «Aa-» или «aA-» (без учета регистра), определите настраиваемое правило с этими данными. формула проверки:
= СЧЁТЕСЛИ (A2; «aa- *»)
Формула проверки с логикой ИЛИ (несколько критериев)
Если имеется 2 или более действительных префикса, добавьте несколько функций СЧЁТЕСЛИ, чтобы ваше правило проверки данных Excel работало с логикой ИЛИ:
= СЧЁТЕСЛИ (A2; «аа - *») + СЧЁТЕСЛИ (A2; «bb- *»)
Формула проверки с учетом регистра
Если регистр символов имеет значение, используйте EXACT в сочетании с функцией LEFT, чтобы создать чувствительную к регистру формулу проверки для записей, начинающихся с определенного текста:
ТОЧНЫЙ (ЛЕВЫЙ ( ячейка , число_символов ), текст )
Например, чтобы разрешить только те идентификаторы заказов, которые начинаются с «AA-» (ни «aa-», ни «Aa-» не допускаются), используйте следующую формулу:
= ТОЧНЫЙ (ЛЕВЫЙ (A2,3), «AA-»)
В приведенной выше формуле функция LEFT извлекает первые 3 символа из ячейки A2, а EXACT выполняет сравнение с учетом регистра с жестко заданной подстрокой («AA-» в этом примере).Если две подстроки точно совпадают, формула возвращает ИСТИНА и проверка проходит; в противном случае возвращается FALSE и проверка не выполняется.
Разрешить записи, содержащие определенный текст
Чтобы разрешить записи, содержащие определенный текст в любом месте ячейки (в начале, середине или конце), используйте функцию ЕЧИСЛО в сочетании с НАЙТИ или ПОИСК, в зависимости от того, хотите ли вы совпадение с учетом регистра или без учета регистра:
Проверка без учета регистра:
ISNUMBER (ПОИСК ( текст , ячейка ))
Проверка с учетом регистра:
ISNUMBER (НАЙТИ ( текст , ячейка ))
В нашем примере набора данных, чтобы разрешить только записи, содержащие текст «AA» в ячейках A2: A6, используйте одну из следующих формул:
Без учета регистра: = ISNUMBER (ПОИСК ("AA", A2))
С учетом регистра: = ISNUMBER (FIND ("AA", A2))
Формулы работают по следующей логике:
Вы ищите подстроку «AA» в ячейке A2, используя НАЙТИ или ПОИСК, и оба возвращают позицию первого символа в подстроке.Если текст не найден, возвращается ошибка. Для любого числового значения, возвращаемого в результате поиска, функция ЕЧИСЛО возвращает ИСТИНА, и проверка данных проходит успешно. В случае ошибки ISNUMBER возвращает FALSE, и запись в ячейку запрещена.
Проверка данных для разрешения только уникальных записей и запрета дублирования
В ситуациях, когда определенный столбец или диапазон ячеек не должен содержать дубликатов, настройте пользовательское правило проверки данных, чтобы разрешить только уникальные записи.Для этого мы собираемся использовать классическую формулу СЧЁТЕСЛИ для выявления дубликатов:
= СЧЁТЕСЛИ ( диапазон , верхняя_ячейка ) <= 1
Например, чтобы убедиться, что в ячейки с A2 по A6 вводятся только уникальные идентификаторы заказов, создайте настраиваемое правило с этой формулой проверки данных:
= СЧЁТЕСЛИ ($ A $ 2: $ A $ 6; A2) <= 1
При вводе уникального значения формула возвращает ИСТИНА, и проверка завершается успешно. Если такое же значение уже существует в указанном диапазоне (счетчик больше 1), СЧЁТЕСЛИ возвращает ЛОЖЬ, и ввод не проходит проверку.
Обратите внимание, что мы блокируем диапазон с помощью абсолютных ссылок на ячейки (A $ 2: $ A $ 6) и используем относительную ссылку для верхней ячейки (A2), чтобы формула правильно настраивалась для каждой ячейки в проверенном диапазоне.
Примечание. Эта формула проверки данных нечувствительна к регистру , она не различает прописные и строчные буквы.
Формулы проверки даты и времени
Excel для проверки даты предоставляет довольно много предопределенных критериев, чтобы ограничить пользователей вводом дат только между двумя указанными вами датами, больше, меньше или равными заданной дате.
Если вам нужен больший контроль над проверкой данных на ваших листах, вы можете реплицировать встроенные функции с помощью настраиваемого правила или написать свою собственную формулу, которая выходит за рамки встроенных возможностей проверки данных Excel.
Разрешить даты между двумя датами
Чтобы ограничить запись датой в пределах указанного диапазона, можно использовать либо предопределенное правило даты с критериями «между», либо создать настраиваемое правило проверки с этой общей формулой:
И ( ячейка > = начальная_дата ), ячейка <= конечная_дата )
Где:
ячейка является самой верхней ячейкой в подтвержденном диапазоне, а
даты начала и конца являются действительными датами, предоставленными с помощью функции ДАТА или ссылок на ячейки, содержащие даты.
Например, чтобы разрешить только даты в июле 2017 года, используйте следующую формулу:
= И (C2> = ДАТА (2017,7,1), C2 <= ДАТА (2017,7,31))
Или введите дату начала и дату окончания в некоторые ячейки (F1 и F2 в этом примере) и укажите на эти ячейки в формуле:
= И (C2> = $ F $ 1, C2 <= $ F $ 2)
Обратите внимание, что даты границ заблокированы абсолютными ссылками на ячейки.
Разрешить только будние или выходные дни
Чтобы ограничить пользователя вводом данных только по будням или выходным, настройте пользовательское правило проверки на основе функции WEEKDAY.
Если для аргумента return_type установлено значение 2, WEEKDAY возвращает целое число от 1 (понедельник) до 7 (воскресенье). Итак, для будних дней (с понедельника по пятницу) результат формулы должен быть меньше 6, а для выходных (сб и вс) больше 5.
Разрешить только рабочих дня :
WEEKDAY ( ячейка , 2) <6
Разрешить только выходных :
WEEKDAY ( ячейка , 2)> 5
Например, чтобы разрешить ввод только рабочих дней в ячейки C2: C6, используйте эту формулу:
= ДЕНЬ НЕДЕЛИ (C2,2) <6
Проверить даты на основе сегодняшней даты
Во многих ситуациях вы можете использовать сегодняшнюю дату как дату начала допустимого диапазона дат.Чтобы получить текущую дату, используйте функцию СЕГОДНЯ, а затем добавьте к ней желаемое количество дней, чтобы вычислить дату окончания.
Например, чтобы ограничить ввод данных 6 днями с текущего момента (7 дней, включая сегодняшний), мы собираемся использовать встроенное правило даты с критериями на основе формулы:
Выберите Дата в Разрешить
Выберите между в Data
В поле Дата начала введите = СЕГОДНЯ ()
В поле Дата окончания введите = СЕГОДНЯ () + 6
Аналогичным образом вы можете ограничить пользователей вводом дат до или после сегодняшней даты.Для этого выберите меньше или больше в поле Данные , а затем введите = СЕГОДНЯ () в поле даты Конец или Начало даты соответственно.
Время подтверждения на основе текущего времени
Для проверки данных на основе текущего времени используйте предварительно определенное правило времени с собственной формулой проверки данных:
В поле Разрешить выберите Время .
В поле Data выберите либо меньше, чем , чтобы разрешить только времена до текущего времени, либо больше, чем , чтобы разрешить времена после текущего времени.
В поле Время окончания или Время начала (в зависимости от того, какие критерии вы выбрали на предыдущем шаге) введите одну из следующих формул:
Для проверки даты и времени на основе текущей даты и времени:
= СЕЙЧАС ()
Для проверки раз на основе текущего времени:
= ВРЕМЯ (ЧАС (СЕЙЧАС ()), МИНУТА (СЕЙЧАС ()), СЕКУНДА (СЕЙЧАС ()))
На снимке экрана ниже показано правило, которое допускает только время, превышающее текущее время:
Пользовательское правило проверки данных Excel не работает
Если ваше правило проверки данных на основе формул не работает должным образом, необходимо проверить 3 основных момента:
Формула проверки данных верна
Формула проверки не ссылается на пустую ячейку
Используются соответствующие ссылки на ячейки
Проверьте правильность формулы проверки данных Excel
Для начала скопируйте формулу проверки в какую-нибудь ячейку, чтобы убедиться, что она не возвращает ошибку, например # N / A, #VALUE или # DIV / 0 !.
Если вы создаете настраиваемое правило , формула должна возвращать логические значения ИСТИНА и ЛОЖЬ или значения 1 и 0, соответствующие им, соответственно.
Если вы используете критерий на основе формулы во встроенном правиле (как мы это делали для проверки времени на основе текущего времени), оно также может вернуть другое числовое значение.
Формула проверки данных Excel не должна ссылаться на пустую ячейку
Во многих ситуациях, если вы выбираете Игнорировать пустое поле при определении правила (обычно выбирается по умолчанию) и одна или несколько ячеек, на которые ссылается ваша формула, пусты, любое значение будет разрешено в проверенной ячейке.
Вот пример в самом простом виде:
Абсолютные и относительные ссылки на ячейки в формулах проверки данных
При настройке правила проверки Excel на основе формул имейте в виду, что все ссылки на ячейки в вашей формуле имеют значение относительно верхней левой ячейки в выбранном диапазоне.
Если вы создаете правило для нескольких ячеек и ваши критерии проверки зависят от конкретных ячеек , обязательно используйте абсолютные ссылки на ячейки (со знаком $, например, $ A $ 1), в противном случае ваше правило будет работать правильно только для первая ячейка.Чтобы лучше проиллюстрировать эту мысль, рассмотрим следующий пример.
Предположим, вы хотите ограничить ввод данных в ячейки от D2 до D5 целыми числами от 1 (минимальное значение) до результата деления A2 на B2. Итак, вы рассчитываете максимальное значение по этой простой формуле = A2 / B2 , как показано на скриншоте ниже:
Проблема в том, что эта, казалось бы, правильная формула не работает для ячеек с D3 по D5, потому что относительные ссылки меняются в зависимости от относительного положения строк и столбцов.Таким образом, для ячейки D3 формула изменится на = A3 / B3 , а для D4 она станет = A4 / B4 , что делает проверку данных неверной!
Чтобы исправить формулу, просто введите «$» перед ссылками на столбцы и строки, чтобы заблокировать их: = $ A $ 2 / $ B $ 2 . Или нажмите F4 для переключения между различными типами ссылок.
В ситуациях, когда вы хотите проверить каждую ячейку на основе ее собственных критериев, используйте относительные ссылки на ячейки без знака $, чтобы получить формулу для корректировки для каждой строки или / или столбца:
Как видите, «абсолютной истины» не существует, одна и та же формула может быть правильной или неправильной в зависимости от ситуации и вашей конкретной задачи.
Вот как использовать проверку данных в Excel с вашими собственными формулами. Чтобы получить больше информации, загрузите нашу книгу «Пример проверки данных Excel» и изучите настройки правил. Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Вас также может заинтересовать
Проверка данных Excel - проверка правильности формата введенных данных
Автор: Советы и рекомендации по Excel с сайта Pryor.com 23 марта 2017 г.
Категории: Excel® Теги: проверка данных
Excel позволяет настроить определенные ячейки для приема только определенного формата данных, который вы укажете.Вы можете предложить пользователю рекомендации по правильному вводу его данных. Вы даже можете указать, что данные должны попадать в определенный диапазон данных, если хотите. Если введенные данные не соответствуют вашим спецификациям, Excel отобразит предупреждение об ошибке, которое предложит пользователю повторить попытку с правильным форматом.
Укажите формат данных
Выберите ячейку или ячейки, которые вы хотите проверить во время ввода.
На вкладке Data в группе Data Tools щелкните Data Validation , чтобы открыть диалоговое окно Data Validation .
На вкладке Настройки укажите критерии, которым должны соответствовать введенные данные:
Выберите тип данных Время в раскрывающемся меню Разрешить .
Выберите между из раскрывающегося меню Данные: .
Установите допустимый диапазон времени начала и окончания .
На вкладке Входное сообщение вы можете отобразить всплывающую подсказку с инструкциями для пользователя, прежде чем он наберет .
Установите флажок Показывать входное сообщение при выборе ячейки , чтобы включить подсказки.
На вкладке Предупреждение об ошибке укажите, что происходит, если данные не соответствуют вашим критериям:
Установите флажок Показывать предупреждение об ошибке после ввода недопустимых данных , чтобы включить предупреждения.
Выберите желаемый тип оповещения:
Стоп - Запрещает Excel принимать недопустимые данные и позволяет пользователю только Повторить или Отменить .
Предупреждение - Пользователь предупрежден о том, что данные недействительны, но он может щелкнуть Да , чтобы ввести данные в любом случае, Нет, для редактирования записи или Отмена , чтобы удалить запись.
Информация - Пользователь предупрежден о том, что данные недействительны, но ему не запрещен их ввод. Пользователь может щелкнуть OK , чтобы ввести данные в любом случае, или Отмена , чтобы удалить их.
Нажмите ОК , чтобы применить проверку данных.
Удалить подтверждение данных
Выберите затронутую ячейку или ячейки и нажмите кнопку Проверка данных .
В диалоговом окне Проверка данных нажмите Очистить все , затем ОК .
Hot Tip : Чтобы найти недопустимые данные, которые уже содержат в вашей книге, настройте проверку, как описано выше, и выберите Circle Invalid Data [H] из раскрывающегося меню Data Validation .Excel выделит ячейки, не соответствующие указанным критериям. Щелкните Очистить круги проверки , чтобы удалить их.
Советы и рекомендации по работе с Excel от Pryor.com
Excel Tips & Tricks написано экспертами Microsoft® Excel® из Fred Pryor Seminars и CareerTrack. Известные своим обширным обучением в области Excel, мы предлагаем одни из лучших в отрасли. Не тратьте драгоценное время на попытки разобраться во всем самостоятельно.Посетите один из наших замечательных курсов по Excel и получите знания, необходимые для более эффективного и действенного использования Excel. Ознакомьтесь с курсом Excel, который будет проходить в ближайшем к вам месте, нажав здесь.
Подробнее о проверке данных - Excel
Вы можете использовать проверку данных, чтобы ограничить тип данных или значений, которые пользователи вводят в ячейки. Например, вы можете использовать проверку данных для вычисления максимально допустимого значения в ячейке на основе значения в другом месте книги.В следующем примере пользователь ввел abc, что не является допустимым значением в этой ячейке.
Когда полезна проверка данных?
Проверка данных неоценима, если вы хотите поделиться книгой с другими и хотите, чтобы введенные данные были точными и согласованными. Помимо прочего, вы можете использовать проверку данных для следующего:
Ограничить записи предопределенными элементами в списке - Например, вы можете ограничить выбор отдела пользователем бухгалтерией, расчетом заработной платы, кадрами, и это лишь некоторые из них.
Ограничить числа за пределами указанного диапазона - Например, вы можете указать максимальный процентный ввод для годового повышения заслуг сотрудника, скажем, 3%, или разрешить только целое число от 1 до 100.
Ограничение дат вне определенного временного интервала - Например, в запросе сотрудника на отгул вы можете запретить кому-либо выбирать дату, предшествующую сегодняшней дате.
Ограничить время вне определенного временного интервала - Например, вы можете указать расписание встреч с 8:00 до 17:00.
Ограничить количество текстовых символов - Например, вы можете ограничить разрешенный текст в ячейке 10 или менее символами.
Проверка данных на основе формул или значений в других ячейках - Например, вы можете использовать проверку данных, чтобы установить максимальный предел для комиссионных и бонусов на основе общего прогнозируемого значения заработной платы.Если пользователи вводят сумму, превышающую лимит, они видят сообщение об ошибке.
Ввод проверки данных и сообщения об ошибках
Вы можете выбрать отображение входного сообщения, когда пользователь выбирает ячейку. Сообщения ввода обычно используются, чтобы предложить пользователям рекомендации относительно типа данных, которые вы хотите ввести в ячейку. Сообщение такого типа появляется рядом с ячейкой. Вы можете переместить это сообщение, если хотите, и оно останется видимым до тех пор, пока вы не перейдете в другую ячейку или не нажмете Esc.
Вы настраиваете свое входное сообщение на второй вкладке проверки данных.
Как только ваши пользователи привыкнут к вашему входному сообщению, вы можете снять отметку с Показывать входное сообщение, когда выбрана ячейка .
Вы также можете показать предупреждение об ошибке , которое появляется только после того, как пользователи вводят недопустимые данные.
Вы можете выбрать один из трех типов предупреждений об ошибках:
Значок
Тип
Используйте до
Стоп
Запретить пользователям вводить недопустимые данные в ячейку.
Предупреждающее сообщение Stop имеет два варианта: Retry или Cancel .
Предупреждение
Предупреждать пользователей о том, что они ввели неверные данные, не препятствуя их вводу.
Когда появляется предупреждающее сообщение Предупреждение , пользователи могут щелкнуть Да, , чтобы принять недопустимую запись, Нет, , чтобы отредактировать недопустимую запись, или Отмена, , чтобы удалить недопустимую запись.
Информация
Сообщите пользователям, что введенные ими данные недействительны, не препятствуя их вводу. Этот тип предупреждений об ошибках является наиболее гибким.
Когда появляется предупреждающее сообщение Информация , пользователи могут щелкнуть OK , чтобы принять недопустимое значение, или Отмена , чтобы отклонить его.
Советы по работе с проверкой данных
Воспользуйтесь этими советами и приемами для работы с проверкой данных в Excel.
Примечание. Если вы хотите использовать проверку данных с книгами в службах Excel или в веб-приложении Excel, вам необходимо сначала создать проверку данных в настольной версии Excel.
Ширина раскрывающегося списка определяется шириной ячейки, в которой выполняется проверка данных.Вам может потребоваться отрегулировать ширину этой ячейки, чтобы предотвратить усечение допустимых записей, которые превышают ширину раскрывающегося списка.
Если вы планируете защитить рабочий лист или книгу, защитите их после того, как вы закончите указывать какие-либо параметры проверки. Убедитесь, что вы разблокировали все проверенные ячейки, прежде чем защищать лист. В противном случае пользователи не смогут вводить данные в ячейки. См. Раздел Защита рабочего листа.
Если вы планируете предоставить общий доступ к книге, предоставьте общий доступ к ней только после того, как вы закончите указывать параметры проверки и защиты данных. После того, как вы поделитесь книгой, вы не сможете изменить настройки проверки, пока не прекратите общий доступ.
Вы можете применить проверку данных к ячейкам, в которые уже были введены данные. Однако Excel не уведомляет автоматически о том, что существующие ячейки содержат недопустимые данные.В этом случае вы можете выделить недопустимые данные, указав Excel обвести их на листе. Как только вы определили неверные данные, вы можете снова скрыть круги. Если вы исправите неверную запись, кружок исчезнет автоматически.
Чтобы применить кружки, выберите ячейки, которые вы хотите оценить, и перейдите к Data > Data Tools > Data Validation > Circle Invalid Data .
Чтобы быстро удалить проверку данных для ячейки, выберите ее и перейдите к Data > Data Tools > Data Validation > Settings > Clear All .
Чтобы найти ячейки на листе с проверкой данных, на вкладке Главная в группе Редактирование щелкните Найти и выбрать , а затем щелкните Проверка данных . После того, как вы нашли ячейки, в которых есть проверка данных, вы можете изменить, скопировать или удалить параметры проверки.
При создании раскрывающегося списка вы можете использовать команду Define Name (вкладка Formulas, , группа Defined Names, ), чтобы определить имя диапазона, который содержит список.После создания списка на другом листе вы можете скрыть лист, содержащий список, а затем защитить книгу, чтобы пользователи не имели доступа к списку.
Если вы измените параметры проверки для ячейки, вы можете автоматически применить изменения ко всем другим ячейкам с такими же настройками. Для этого на вкладке Параметры установите флажок Применить эти изменения ко всем другим ячейкам с такими же параметрами .
Если проверка данных не работает, убедитесь, что:
Пользователи не копируют и не заполняют данные - Проверка данных предназначена для отображения сообщений и предотвращения недопустимых записей только тогда, когда пользователи вводят данные непосредственно в ячейку. Когда данные копируются или заполняются, сообщения не появляются. Чтобы запретить пользователям копировать и заполнять данные путем перетаскивания ячеек, перейдите к Файл > Параметры > Расширенный > Параметры редактирования > снимите флажок Включить дескриптор заполнения и перетаскивание ячейки , а затем защитите лист.
Ручной пересчет отключен. - Если ручной пересчет включен, невычисленные ячейки могут помешать правильной проверке данных. Чтобы отключить ручной пересчет, перейдите на вкладку Формулы > группа Расчет > Параметры расчета > щелкните Автоматический .
Формулы не содержат ошибок - Убедитесь, что формулы в проверенных ячейках не вызывают ошибок, например #REF! или # DIV / 0 !.Excel игнорирует проверку данных, пока вы не исправите ошибку.
Ячейки, указанные в формулах, верны. - Если указанная ячейка изменяется так, что формула в проверенной ячейке вычисляет недопустимый результат, сообщение проверки для ячейки не отображается.
Таблица Excel может быть связана с сайтом SharePoint. - Вы не можете добавить проверку данных в таблицу Excel, связанную с сайтом SharePoint.Чтобы добавить проверку данных, необходимо отключить таблицу Excel или преобразовать таблицу Excel в диапазон.
Возможно, вы сейчас вводите данные. - Команда «Проверка данных» недоступна, пока вы вводите данные в ячейку. Чтобы завершить ввод данных, нажмите Enter или ESC для выхода.
Рабочий лист может быть защищен или открыт для общего доступа. - Вы не можете изменить параметры проверки данных, если ваша книга является общей или защищенной.Сначала вам нужно отменить общий доступ к книге или снять защиту с нее.
Как обновить или удалить проверку данных в унаследованной книге
Если вы наследуете книгу с проверкой данных, вы можете изменить или удалить ее, если она не защищена. Если он защищен паролем, которого вы не знаете, вам следует попытаться связаться с предыдущим владельцем, чтобы помочь вам снять защиту с листа, так как Excel не может восстановить неизвестные или утерянные пароли.Вы также можете скопировать данные на другой лист, а затем удалить проверку данных.
Если вы видите предупреждение о проверке данных при попытке ввести или изменить данные в ячейке, и вы не знаете, что вы можете ввести, обратитесь к владельцу книги.
У вас есть конкретный вопрос по функциям?
Задайте вопрос на форуме сообщества Excel
Помогите нам улучшить Excel
У вас есть предложения о том, как мы можем улучшить следующую версию Excel? Если да, ознакомьтесь с темами на сайте Excel User Voice.
Применить проверку данных к ячейкам
Загрузите наши примеры
Загрузите образец книги со всеми примерами проверки данных в этой статье
Выберите ячейки, для которых нужно создать правило.
Выберите Данные> Проверка данных .
На вкладке Настройки в разделе Разрешить выберите вариант:
Целое число - чтобы ячейка могла принимать только целые числа.
Десятичное число - чтобы ячейка могла принимать только десятичные числа.
Список - для выбора данных из раскрывающегося списка.
Дата - чтобы ячейка могла принимать только дату.
Время - ограничить ячейку приемом только времени.
Длина текста - для ограничения длины текста.
Custom - для пользовательской формулы.
В разделе Данные выберите условие:
между
не между
равно
не равно
больше
менее
больше или равно
меньше или равно
На вкладке Настройки в разделе Разрешить выберите вариант:
Задайте другие требуемые значения в зависимости от того, что вы выбрали для Разрешить и Данные .Например, если вы выберете между , затем выберите значения Минимум: и Максимум: для ячеек.
Установите флажок Игнорировать пробелы , если вы хотите игнорировать пробелы.
Если вы хотите добавить заголовок и сообщение для своего правила, выберите вкладку Входное сообщение , а затем введите заголовок и введите сообщение.
Установите флажок Показывать входное сообщение при выборе ячейки , чтобы отображать сообщение, когда пользователь выбирает или наводит курсор на выбранную ячейку (я).
Выберите ОК .
Теперь, если пользователь пытается ввести недопустимое значение, появляется всплывающее окно с сообщением: «Это значение не соответствует ограничениям проверки данных для этой ячейки.”
Загрузите наши примеры
Загрузите образец книги со всеми примерами проверки данных в этой статье
Если вы создаете лист, который требует, чтобы пользователи вводили данные, вы можете ограничить ввод определенным диапазоном дат или чисел или убедиться, что вводятся только положительные целые числа. Excel может ограничить ввод данных определенными ячейками с помощью проверки данных, предлагать пользователям вводить допустимые данные при выборе ячейки и отображать сообщение об ошибке, когда пользователь вводит недопустимые данные.
Ограничить ввод данных
Выберите ячейки, в которых вы хотите ограничить ввод данных.
На вкладке Данные щелкните Проверка данных > Проверка данных .
Примечание. Если команда проверки недоступна, возможно, лист защищен или к книге открыт общий доступ.Вы не можете изменить настройки проверки данных, если ваша книга является общей или ваш лист защищен. Дополнительные сведения о защите книги см. В разделе Защита книги.
В поле Разрешить выберите тип данных, которые вы хотите разрешить, и введите ограничивающие критерии и значения.
Примечание: Поля, в которые вы вводите предельные значения, будут помечены на основе данных и критериев ограничения, которые вы выбрали.Например, если вы выберете «Дата» в качестве типа данных, вы сможете ввести предельные значения в поля минимального и максимального значений с метками Дата начала и Дата окончания .
Запрашивать у пользователей действительные записи
Когда пользователи щелкают ячейку, в которой есть требования к вводу данных, вы можете отобразить сообщение, объясняющее, какие данные являются действительными.
Выберите ячейки, в которых вы хотите запрашивать у пользователей допустимые записи данных.
На вкладке Данные щелкните Проверка данных > Проверка данных .
Примечание. Если команда проверки недоступна, возможно, лист защищен или к книге открыт общий доступ. Вы не можете изменить настройки проверки данных, если ваша книга является общей или ваш лист защищен.Дополнительные сведения о защите книги см. В разделе Защита книги.
На вкладке Входное сообщение установите флажок Показывать входное сообщение при выборе ячейки .
В поле Заголовок введите заголовок сообщения.
В поле Введите сообщение введите сообщение, которое вы хотите отобразить.
Отображать сообщение об ошибке при вводе неверных данных
Если у вас есть ограничения данных и пользователь вводит недопустимые данные в ячейку, вы можете отобразить сообщение, объясняющее ошибку.
Выберите ячейки, в которых вы хотите отобразить сообщение об ошибке.
На вкладке Данные щелкните Проверка данных > Проверка данных .
Примечание. Если команда проверки недоступна, возможно, лист защищен или к книге открыт общий доступ. Вы не можете изменить настройки проверки данных, если ваша книга является общей или ваш лист защищен. Дополнительные сведения о защите книги см. В разделе Защита книги.
На вкладке Предупреждение об ошибке в поле Заголовок введите заголовок сообщения.
В поле Сообщение об ошибке введите сообщение, которое вы хотите отобразить, если введены недопустимые данные.
Выполните одно из следующих действий:
К
на Стиль Во всплывающем меню выберите
Требовать от пользователей исправить ошибку перед продолжением
Стоп
Предупредить пользователей о том, что данные недействительны, и потребовать от них выбора Да или Нет , чтобы указать, хотят ли они продолжить
Предупреждение
Предупредить пользователей, что данные недействительны, но разрешить им продолжить после закрытия предупреждающего сообщения
Важно
Ограничить ввод данных
Выберите ячейки, в которых вы хотите ограничить ввод данных.
На вкладке Данные в разделе Инструменты щелкните Проверить .
Примечание: Если команда проверки недоступна, возможно, лист защищен или книга может использоваться совместно. Вы не можете изменить настройки проверки данных, если ваша книга является общей или ваш лист защищен.Дополнительные сведения о защите книги см. В разделе Защита книги.
Во всплывающем меню Разрешить выберите тип данных, которые вы хотите разрешить.
Во всплывающем меню Data выберите нужный тип ограничивающих критериев, а затем введите ограничивающие значения.
Примечание: Поля, в которые вы вводите предельные значения, будут помечены на основе данных и критериев ограничения, которые вы выбрали. Например, если вы выберете «Дата» в качестве типа данных, вы сможете ввести предельные значения в поля минимального и максимального значений с метками Дата начала и Дата окончания .
Запрашивать у пользователей действительные записи
Когда пользователи щелкают ячейку, в которой есть требования к вводу данных, вы можете отобразить сообщение, объясняющее, какие данные являются действительными.
Выберите ячейки, в которых вы хотите запрашивать у пользователей допустимые записи данных.
На вкладке Данные в разделе Инструменты щелкните Проверить .
Примечание: Если команда проверки недоступна, возможно, лист защищен или книга может использоваться совместно.Вы не можете изменить настройки проверки данных, если ваша книга является общей или ваш лист защищен. Дополнительные сведения о защите книги см. В разделе Защита книги.
На вкладке Входное сообщение установите флажок Показывать входное сообщение при выборе ячейки .
В поле Заголовок введите заголовок сообщения.
В поле Введите сообщение введите сообщение, которое вы хотите отобразить.
Отображать сообщение об ошибке при вводе неверных данных
Если у вас есть ограничения данных и пользователь вводит недопустимые данные в ячейку, вы можете отобразить сообщение, объясняющее ошибку.
Выберите ячейки, в которых вы хотите отобразить сообщение об ошибке.
На вкладке Данные в разделе Инструменты щелкните Проверить .
Примечание: Если команда проверки недоступна, возможно, лист защищен или книга может использоваться совместно. Вы не можете изменить настройки проверки данных, если ваша книга является общей или ваш лист защищен.Дополнительные сведения о защите книги см. В разделе Защита книги.
На вкладке Предупреждение об ошибке в поле Заголовок введите заголовок сообщения.
В поле Сообщение об ошибке введите сообщение, которое вы хотите отобразить, если введены недопустимые данные.
Выполните одно из следующих действий:
К
на Стиль Во всплывающем меню выберите
Требовать от пользователей исправить ошибку перед продолжением
Стоп
Предупредить пользователей о том, что данные недействительны, и потребовать от них выбора Да или Нет , чтобы указать, хотят ли они продолжить
Предупреждение
Предупредить пользователей, что данные недействительны, но разрешить им продолжить после закрытия предупреждающего сообщения
Важно
Применить проверку данных к ячейкам
Загрузите наши примеры
Загрузите образец книги со всеми примерами проверки данных в этой статье
Выберите ячейки, для которых нужно создать правило.
Выберите Данные> Проверка данных .
На вкладке Настройки в разделе Разрешить выберите вариант:
Целое число - чтобы ячейка могла принимать только целые числа.
Десятичное число - чтобы ячейка могла принимать только десятичные числа.
Список - для выбора данных из раскрывающегося списка.
Дата - чтобы ячейка могла принимать только дату.
Время - ограничить ячейку приемом только времени.
Длина текста - для ограничения длины текста.
Custom - для пользовательской формулы.
В разделе Данные выберите условие:
между
не между
равно
не равно
больше
менее
больше или равно
меньше или равно
На вкладке Настройки в разделе Разрешить выберите вариант:
Задайте другие требуемые значения в зависимости от того, что вы выбрали для Разрешить и Данные .Например, если вы выберете между , затем выберите значения Минимум: и Максимум: для ячеек.
Установите флажок Игнорировать пробелы , если вы хотите игнорировать пробелы.
Если вы хотите добавить заголовок и сообщение для своего правила, выберите вкладку Входное сообщение , а затем введите заголовок и введите сообщение.
Установите флажок Показывать входное сообщение при выборе ячейки , чтобы отображать сообщение, когда пользователь выбирает или наводит курсор на выбранную ячейку (я).
Выберите ОК .
Теперь, если пользователь пытается ввести недопустимое значение, появляется всплывающее окно с сообщением: «Это значение не соответствует ограничениям проверки данных для этой ячейки.”
Загрузите наши примеры
Загрузите образец книги со всеми примерами проверки данных в этой статье
Если вы создаете лист, который требует, чтобы пользователи вводили данные, вы можете ограничить ввод определенным диапазоном дат или чисел или убедиться, что вводятся только положительные целые числа. Excel может ограничить ввод данных определенными ячейками с помощью проверки данных, предлагать пользователям вводить допустимые данные при выборе ячейки и отображать сообщение об ошибке, когда пользователь вводит недопустимые данные.
Ограничить ввод данных
Выберите ячейки, в которых вы хотите ограничить ввод данных.
На вкладке Данные щелкните Проверка данных > Проверка данных .
Примечание. Если команда проверки недоступна, возможно, лист защищен или к книге открыт общий доступ.Вы не можете изменить настройки проверки данных, если ваша книга является общей или ваш лист защищен. Дополнительные сведения о защите книги см. В разделе Защита книги.
В поле Разрешить выберите тип данных, которые вы хотите разрешить, и введите ограничивающие критерии и значения.
Примечание: Поля, в которые вы вводите предельные значения, будут помечены на основе данных и критериев ограничения, которые вы выбрали.Например, если вы выберете «Дата» в качестве типа данных, вы сможете ввести предельные значения в поля минимального и максимального значений с метками Дата начала и Дата окончания .
Запрашивать у пользователей действительные записи
Когда пользователи щелкают ячейку, в которой есть требования к вводу данных, вы можете отобразить сообщение, объясняющее, какие данные являются действительными.
Выберите ячейки, в которых вы хотите запрашивать у пользователей допустимые записи данных.
На вкладке Данные щелкните Проверка данных > Проверка данных .
Примечание. Если команда проверки недоступна, возможно, лист защищен или к книге открыт общий доступ. Вы не можете изменить настройки проверки данных, если ваша книга является общей или ваш лист защищен.Дополнительные сведения о защите книги см. В разделе Защита книги.
На вкладке Входное сообщение установите флажок Показывать входное сообщение при выборе ячейки .
В поле Заголовок введите заголовок сообщения.
В поле Введите сообщение введите сообщение, которое вы хотите отобразить.
Отображать сообщение об ошибке при вводе неверных данных
Если у вас есть ограничения данных и пользователь вводит недопустимые данные в ячейку, вы можете отобразить сообщение, объясняющее ошибку.
Выберите ячейки, в которых вы хотите отобразить сообщение об ошибке.
На вкладке Данные щелкните Проверка данных > Проверка данных .
Примечание. Если команда проверки недоступна, возможно, лист защищен или к книге открыт общий доступ. Вы не можете изменить настройки проверки данных, если ваша книга является общей или ваш лист защищен. Дополнительные сведения о защите книги см. В разделе Защита книги.
На вкладке Предупреждение об ошибке в поле Заголовок введите заголовок сообщения.
В поле Сообщение об ошибке введите сообщение, которое вы хотите отобразить, если введены недопустимые данные.
Выполните одно из следующих действий:
К
на Стиль Во всплывающем меню выберите
Требовать от пользователей исправить ошибку перед продолжением
Стоп
Предупредить пользователей о том, что данные недействительны, и потребовать от них выбора Да или Нет , чтобы указать, хотят ли они продолжить
Предупреждение
Предупредить пользователей, что данные недействительны, но разрешить им продолжить после закрытия предупреждающего сообщения
Важно
Ограничить ввод данных
Выберите ячейки, в которых вы хотите ограничить ввод данных.
На вкладке Данные в разделе Инструменты щелкните Проверить .
Примечание: Если команда проверки недоступна, возможно, лист защищен или книга может использоваться совместно. Вы не можете изменить настройки проверки данных, если ваша книга является общей или ваш лист защищен.Дополнительные сведения о защите книги см. В разделе Защита книги.
Во всплывающем меню Разрешить выберите тип данных, которые вы хотите разрешить.
Во всплывающем меню Data выберите нужный тип ограничивающих критериев, а затем введите ограничивающие значения.
Примечание: Поля, в которые вы вводите предельные значения, будут помечены на основе данных и критериев ограничения, которые вы выбрали. Например, если вы выберете «Дата» в качестве типа данных, вы сможете ввести предельные значения в поля минимального и максимального значений с метками Дата начала и Дата окончания .
Запрашивать у пользователей действительные записи
Когда пользователи щелкают ячейку, в которой есть требования к вводу данных, вы можете отобразить сообщение, объясняющее, какие данные являются действительными.
Выберите ячейки, в которых вы хотите запрашивать у пользователей допустимые записи данных.
На вкладке Данные в разделе Инструменты щелкните Проверить .
Примечание: Если команда проверки недоступна, возможно, лист защищен или книга может использоваться совместно.Вы не можете изменить настройки проверки данных, если ваша книга является общей или ваш лист защищен. Дополнительные сведения о защите книги см. В разделе Защита книги.
На вкладке Входное сообщение установите флажок Показывать входное сообщение при выборе ячейки .
В поле Заголовок введите заголовок сообщения.
В поле Введите сообщение введите сообщение, которое вы хотите отобразить.
Отображать сообщение об ошибке при вводе неверных данных
Если у вас есть ограничения данных и пользователь вводит недопустимые данные в ячейку, вы можете отобразить сообщение, объясняющее ошибку.
Выберите ячейки, в которых вы хотите отобразить сообщение об ошибке.
На вкладке Данные в разделе Инструменты щелкните Проверить .
Примечание: Если команда проверки недоступна, возможно, лист защищен или книга может использоваться совместно. Вы не можете изменить настройки проверки данных, если ваша книга является общей или ваш лист защищен.Дополнительные сведения о защите книги см. В разделе Защита книги.
На вкладке Предупреждение об ошибке в поле Заголовок введите заголовок сообщения.
В поле Сообщение об ошибке введите сообщение, которое вы хотите отобразить, если введены недопустимые данные.
Выполните одно из следующих действий:
К
на Стиль Во всплывающем меню выберите
Требовать от пользователей исправить ошибку перед продолжением
Стоп
Предупредить пользователей о том, что данные недействительны, и потребовать от них выбора Да или Нет , чтобы указать, хотят ли они продолжить
Предупреждение
Предупредить пользователей, что данные недействительны, но разрешить им продолжить после закрытия предупреждающего сообщения
Важно
.

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

Значок	Тип	Используйте до
	Стоп	Запретить пользователям вводить недопустимые данные в ячейку. Предупреждающее сообщение Stop имеет два варианта: Retry или Cancel .
	Предупреждение	Предупреждать пользователей о том, что они ввели неверные данные, не препятствуя их вводу. Когда появляется предупреждающее сообщение Предупреждение , пользователи могут щелкнуть Да, , чтобы принять недопустимую запись, Нет, , чтобы отредактировать недопустимую запись, или Отмена, , чтобы удалить недопустимую запись.
	Информация	Сообщите пользователям, что введенные ими данные недействительны, не препятствуя их вводу. Этот тип предупреждений об ошибках является наиболее гибким. Когда появляется предупреждающее сообщение Информация , пользователи могут щелкнуть OK , чтобы принять недопустимое значение, или Отмена , чтобы отклонить его.

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