Ошибки при вводе данных можно исправить использовав

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

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

Таблица
2

Стандартные математические функции

Математические функции	Обозначение в Excel	Примеры
Математическая запись	Ввод в Excel
	=КОРЕНЬ(X)		=КОРЕНЬ(234)Enter
	=EXP(X)		=EXP(-2,67)Enter
	=ABS(X)		=ABS(X-Y)
	=LN(X)		=LN(45,6)Enter
	=LOG(X,A)		=LOG(27,3)Enter
	=LOG10(X)		=LOG10(1000)Enter
	=SIN(X)		=SIN(56)Enter
	=COS(X)		*=COS(3,1460/180)Enter**
	=TAN(X)		*=TAN(3,1439/180)Enter**
	=1/TAN(X)		=1/TAN(85)Enter
	=ASIN(X)		=ASIN(0,56)Enter
	=ACOS(X)		=ACOS(0,934)Enter
	=ATAN(X)		=ATAN(5,8)Enter
	=X^(1/N)		=625,37^(1/5)Enter

Таблица 3

Стандартные статистические функции

Статистические функции	Обозначение в Excel	Примеры
Вычисление среднего значения среди чисел диапазона (списка)	=СРЗНАЧ(диапазон)	=СРЗНАЧ(20,G5:N9,A7)
Вычисление суммы всех значений в диапазоне (списке)	=СУММ(диапазон)	=СУММ(D8:J10) =СУММ(12,23,A3:F5)
Выбор максимального значения из диапазона (списка)	=МАКС(диапазон)	=МАКС(24,56,A3:D6) =МАКС(D5:G6)
Выбор минимального значения из диапазона (списка)	=МИН(диапазон)	=МИН(C5:H8) =МИН(H7:P9,J8,K6)
Подсчет количества чисел (ячеек) в диапазоне (списке)	=СЧЕТ(диапазон)	=СЧЕТ(A7:A20)

#ДЕЛ/0! Сообщение
о некорректной операции –деление
на ноль, т.е. в формуле, в знаменателе,
имеется ссылка на ячейку, содержимое
которой равно нулю. Проверьте и исправьте
содержимое соответствующей ячейки.
#ЗНАЧ! Сообщение
о недопустимом типе аргумента функции
или содержимом ячейки в формуле, на
которые осуществляется ссылка. Проверьте
и исправьте содержимое соответствующей
ячейки.
#ИМЯ? Сообщение
о неверном имени диапазона или функции.
Необходимо исправить имя диапазона
или имя функции. В случае затруднения
правильное имя функции можно выбрать
из списка функций, открывающегося при
помощи программыМастер функций.
#Н/Д. Сообщение
об использовании в расчетах неопределенных
данных, которые не были введены в ячейки.
Для устранения ошибки необходимо ввести
нужные данные в соответствующие ячейки.
#ССЫЛКА! Сообщение
о неверной ссылке на ячейку, т.е. в
формуле используется ссылка на
несуществующую ячейку. Проверьте и
исправьте ссылку на ячейку в формуле.
#ЧИСЛО! Сообщение
об ошибке в вычислениях появляется при
выполнении расчетов по формуле, в
которой используется недопустимое
число или числовой аргумент. Проверьте
и исправьте данные в ячейках.
Циклическая
ссылка. Такое сообщение
выдается в том случае, если в
ячейке записана формула со ссылкой на
эту же ячейку. Проверьте и отредактируйте
содержимое ячейки.

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

Источник

Ошибки пользователей при вводе данных – одна из самых распространенных проблем в Excel. В результате ошибочных данных ячейки с формулами на разных листах друг за другом могут заполниться ошибками #ЗНАЧ, #H/Д и т.п. Поиск ошибок может занимать длительное время, потому что непонятен первоначальный источник ошибки. С чего начинать? Но появление ячеек с ошибками это даже хорошо.

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

НЕДОСТАТКИ СТАНДАРТНЫХ ИНСТРУМЕНТОВ EXCEL

Как вы наверняка знаете, в Excel существует стандартный функционал для контроля вводимых в ячейки данных. Он устанавливается через меню Данные -> Проверка данных. В результате появляется диалоговое окно Проверка вводимых значений c тремя закладками.

На закладке Параметры в поле Тип данных можно выбрать тип данных. В зависимости от введенного типа становятся доступными для заполнения другие поля, например:

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

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

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

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

Например, если данные вводятся в ячейку с использованием команд Копировать->Вставить, Копировать->Специальная вставка-Значения или путем «растаскивания» от соседних ячеек, то проверка данных не осуществляется. Также значения в ячейках не контролируются, если они введены в ячейку с помощью макроса (если только контроль не осуществляется в самом макросе).

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

Часто это бывает, когда вы выгружаете данные в таблицы из других программ, например, 1С. Но особенно неприятно когда ошибки незаметны или малозаметны. Например:

Значение в ячейке выглядит как число и дата, а на самом деле оно является текстом. Например потому, что перед или после цифр есть пробел или другие непечатные (невидимые) символы.
Ячейка выглядит пустой, а на самом деле там есть непечатные символы.
В качестве разделителя целой и дробной части использована точка вместо запятой.
Введено значение, которое отсутствует в «привязанном» к ячейке выпадающем списке.

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

2. Стандартные способы проверки могут «тормозить»

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

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

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

3. Бесполезный инструмент «Обвести неверные данные»

Для проверки правильности введенных значений в Excel есть встроенный инструмент «Обвести неверные данные», который вызывается через пункты меню — Данные -> Проверка данных -> Обвести неверные данные.

Команда доступна только на незащищенном листе, а результатом ее работы будет обведение неверных по мнению Excel данных красным овалом как на рисунке выше.

На мой взгляд, это функция практически бесполезна по трем причинам:

Будут признаны ошибочными и обведены пустые ячейки с типом «Список допустимых значение», если для них при настройке была снята галочка в поле «игнорировать пустые ячейки» (она снята в большинстве случаев). Но согласитесь — неверные значения и пустые значения это разные вещи.

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

Она не всегда работает корректно. На вышеприведенном рисунке не были обведены значения в колонке ЦУ — Объект «Сколково» — хотя эти значения ошибочные, т.к. их нет в списке.

4. «Подсвечивание» ячеек с ошибочными данными

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

Я считаю это способ неэффективным. Ну «подсветили» вы некорректные данные в таблице, а что дальше?

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

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

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

КАК ПРАВИЛЬНО ОРГАНИЗОВАТЬ ПРОВЕРКУ ВВЕДЕННЫХ ДАННЫХ

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

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

Любую проверку вводимых значений можно делать и обычными функциями Excel, такими как ЕСЛИ, ЕТЕКСТ, ЕНЕТЕКСТ, ЕЧИСЛО, ПОИСКПОЗ. Поэтому в таблице можно создать служебную колонку в ячейках которой задать формулу, которая будет проверять корректность введенных данных по заданным условиям во всех ячейках строки, предназначенных для ввода и сообщать об этом пользователю. Расчет формулы сделать включаемым/ выключаемым в зависимости от положения «тумблера». Что такое тумблеры и зачем они, написано здесь.

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

Как это устроено проще всего понять это на примере (см. рисунок ниже)

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

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

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

Блоки могут быть такие:

Ячейки, в которых должна вводиться дата или число проверяется такой конструкцией

ЕСЛИ(ЕТЕКСТ(проверяемое_значение);номер_столбца&».»;»»)

Ячейки, в которых значение вводится из выпадающего списка проверяется такой конструкцией:

EСЛИ(ЕСЛИОШИБКА(ПОИСКПОЗ(искомое_значение; просматриваемый_массив;0);-1)>0;””;номер_столбца)

Если вам нужно проверять даты на вхождение в определенный период времени используйте обычные функции ЕСЛИ, >=,<= и т.д. Аналогично для чисел.

Не забудьте всю эту конструкцию поместить внутрь функции ЕСЛИ: =ЕСЛИ(НаТ1;ваша_цепочка_блоков;””), где НаТ1 это имя ячейки-тумблера, которая включает/выключает расчет формулы. Это позволит не вычислять формулу при всяком изменении в ячейках, а делать это по необходимости.

В результате в служебной колонке, в тех строках, где будут обнаружены ошибки, отобразятся номера колонок с некорректными данными, разделенные точками. В шапке таблицы можно подсчитать количество строк с ошибками следующей формулой:
=ЧСТРОК(A$5:A$677)-СЧЁТЕСЛИ(A$5:A$677;»») , где A$5:A$677 – диапазон служебного столбца в котором выводятся признаки ошибок.

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

Ваша программа будет еще удобнее если вы соберете данные о количестве ошибок на одном листе (в моих решениях это лист Настройки как на рисунке ниже) и там же разместите кнопку для запуска формул проверки ошибок. В этом случае вам не нужно метаться между листами рабочей книги. Достаточно нажать одну кнопку – макрос включит «тумблер» — все листы будут проверены на наличие ошибок, данные о количестве ошибок будут выведены в таблице. Если ошибок не обнаружено ячейка останется пустой. Если ошибки обнаружены вам нужно перейти на соответствующий лист и исправить их.

Все просто и удобно, никаких «тормозов» и экономия времени!

Cookie-файлы

Настройка cookie-файлов

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

Аналитические Cookie-файлы
Отключить все

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

Подробнее

Понятно

Источник

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

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

1. Перейдите в режим редактирования содержимого ячейки. Это можно сделать одним из следующих способов:

— Щелкнете левой клавишей мыши в строке формул.

— Дважды щелкните мышью на ячейке.

2. Текстовый курсор поставьте перед неверным символом, исправьте данные.

3. Нажмите или “зеленую галочку” на панели инструментов, чтобы выйти из режима редактирования.

Неверный формат ячейки может быть изменен только выбором другого формата в меню Формат / Ячейка.

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

Например, Вы хотели ввести число 410. Интерпретация некоторых возможных при вводе ошибок приводится в таблице 19.

410.

4.Окт.

410.

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

Если при вводе формул Вы забыли поставить знак “=”, то все, что было набрано, запишется в ячейку как текст. Если Вы поставили знак равенства, то компьютер распознал, что идет ввод формулы и не допустит записать формулу с ошибкой до тех пор, пока она не будет исправлена. Некоторые ошибки, которые могут быть допущены при вводе формулы, приведены в таблице 20.

При копировании материалов обратная ссылка обязательна

Источник

Ошибка при воде данных

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

Ошибки пользователей при вводе данных – одна из самых распространенных проблем в Excel . В результате ошибочных данных ячейки с формулами на разных листах друг за другом могут заполниться ошибками #ЗНАЧ , #H/Д и т.п. Поиск ошибок может занимать длительное время, потому что непонятен первоначальный источник ошибки. С чего начинать? Но появление ячеек с ошибками это даже хорошо.

НЕДОСТАТКИ СТАНДАРТНЫХ ИНСТРУМЕНТОВ EXCEL

Как вы наверняка знаете, в Excel существует стандартный функционал для контроля вводимых в ячейки данных. Он устанавливается через меню Данные -> Проверка данных . В результате появляется диалоговое окно Проверка вводимых значений c тремя закладками.

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

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

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

Часто это бывает, когда вы выгружаете данные в таблицы из других программ, например, 1С. Но о собенно неприятно когда ошибки незаметны или малозаметны. Например:

Значение в ячейке выглядит как число и дата, а на самом деле оно является текстом. Например потому, что перед или после цифр есть пробел или другие непечатные (невидимые) символы.
Ячейка выглядит пустой, а на самом деле там есть непечатные символы.
В качестве разделителя целой и дробной части использована точка вместо запятой.
Введено значение, которое отсутствует в «привязанном» к ячейке выпадающем списке.

2. Стандартные способы проверки могут «тормозить»

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

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

3. Бесполезный инструмент «Обвести неверные данные»

Для проверки правильности введенных значений в Excel есть встроенный инструмент «Обвести неверные данные» , который вызывается через пункты меню — Данные -> Проверка данных -> Обвести неверные данные.

Источник

Набор значений, которые могут быть введены в ячейку Excel, ограничен: решение проблемы

Иногда при работе с таблицами в редакторе Excel появляется уведомление, что «набор значений, которые могут быть введены в ячейку, ограничен». Самая главная проблема в том, что из-за этого вы не можете ввести нужную вам информацию. На самом деле ничего страшного тут нет. Это ситуацию можно исправить в несколько шагов. В этой статье мы подробно рассмотрим, из-за чего именно появляется это окно и что можно сделать в этой ситуации.

Условие проверки

В редакторе Excel существует возможность ограничить ввод информации по каким-нибудь критериям. Процесс происходит следующим образом.

Нажмите на любую клетку.
Перейдите на вкладку «Данные».
Кликните на выделенный инструмент.
Выберите пункт «Проверка данных».

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

Рассмотрим эти форматы более внимательно.

Любое значение

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

Целое число

Выбрав этот формат, вы сможете отредактировать дополнительную настройку во втором поле.

Например, если выбрать вариант «между», то вы увидите, что в этом окне появятся два дополнительных поля: «Минимум» и «Максимум».

Если выбрать «больше» какой-то величины, то вам нужно будет ввести минимальный допустимый порог. Именно поэтому вы увидите только одно поле – «Минимум», поскольку «Максимум» неограничен.

Действительное

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

Список

Этот формат наиболее интересный.

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

Заполните чем-нибудь несколько клеток. Неважно чем.

Нажмите на какую-нибудь клетку. Перейдите на знакомую вам вкладку. Кликните на иконку «Работа с данными». Выберите выделенный инструмент.

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

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

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

Время

Аналогично и тут. Только здесь указывается одно время (без даты).

Длина текста

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

Вариантов довольно много. Этот способ используется при заполнении каких-нибудь бланков или анкет.

Другой

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

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

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

Повторите описанные выше шаги по вызову окна «Проверка вводимых значений».
Выберите какой-нибудь тип данных. В качестве примера мы укажем, что необходимо вводить «Действительное» число, которое больше 10.

Теперь переходим на вкладку «Сообщение об ошибке».
Выбираем вид отображения:
- «Останов»;
- «Предупреждение»;
- «Сообщение».
Указываем заголовок и текст уведомления.
Для сохранения проверки ввода значений нажмите на «OK»

В качестве примера мы укажем следующие настройки.

Введите любое число. Например, что-нибудь меньше указанного значения. Нажмите на клавишу [knopka]Enter[/knopka]. И вы увидите уведомление о том, что была допущена ошибка ввода данных.

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

Если сделать тип «Останов», то ввести неверное значение не удастся.

А теперь попробуйте убрать введенные настройки и оставить пустые поля.

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

Но обратите внимание на то, что текст сообщения отличается!

Отличие версий Microsoft Excel

Описанная выше инструкция подходит для современных редакторов 2010, 2013 и 2016 годов. По сравнению со старыми программами существуют некоторые отличия.

Откройте этот же файл в Экселе 2007. Введите неправильное значение. И вы увидите эту ошибку. То есть в современных редакторах текст сообщения по умолчанию был изменён. Привычный всем вид существовал до 2007 года.

В старом Excel 2003 ошибка точно такая же.

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

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

Кликните на любую клетку, для которой вы хотите задать какие-нибудь правила.
Откройте в меню раздел «Данные».
Выберите инструмент «Работа с данными».
Нажмите на иконку «Проверка данных».

В появившемся окне перейдите на вкладку «Сообщение для ввода».
Укажите любой текст в указанных полях.
Для продолжения нажмите на «OK».

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

Как отключить эту ошибку

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

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

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

Теперь можно вносить любые данные, словно вы открыли пустой файл и никаких настроек там нет.

Примеры от компании Microsoft

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

Заключение

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

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

Видеоинструкция

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

Источник

Adblock
detector

Книжные новинки

Копилка

Рабочие программы

Проекты MS Office

Презентации

Открытые уроки

Экзаменационные билеты

Элективные курсы

Бесплатный soft

Инструкции по ТБ

Подготовка к олимпиадам по информатике

Методика подготовки

«Золотые» алгоритмы

Простые задачи для начинающих

Олимпиадные задачи с решениями

Книги

Среда программирования

Обучение программированию на С++

Справочник по языку Pascal

Обучение

Подготовка к ЕГЭ

Создание сайтов

Уроки FrontPage

Уроки Word 2003

Создание игр на Delphi

Печатаем вслепую

Источник

Добавлено 31 мая 2021 в 22:08

Большинство программ, имеющих какой-либо пользовательский интерфейс, должны обрабатывать вводимые пользователем данные. В программах, которые мы писали, мы использовали std::cin, чтобы попросить пользователя ввести текст. Поскольку ввод текста имеет произвольную форму (пользователь может вводить что угодно), пользователю очень легко ввести данные, которые не ожидаются.

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

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

`std::cin`, буферы и извлечение

Чтобы обсудить, как std::cin и operator>> могут давать сбой, сначала полезно немного узнать, как они работают.

Когда мы используем operator>> для получения пользовательского ввода и помещения его в переменную, это называется «извлечением». Соответственно, в этом контексте оператор >> называется оператором извлечения.

Когда пользователь вводит данные в ответ на операцию извлечения, эти данные помещаются в буфер внутри std::cin. Буфер (также называемый буфером данных) – это просто часть памяти, отведенная для временного хранения данных, пока они перемещаются из одного места в другое. В этом случае буфер используется для хранения пользовательских входных данных, пока они ожидают извлечения в переменные.

При использовании оператора извлечения происходит следующая процедура:

Если во входном буфере уже есть данные, то для извлечения используются они.
Если входной буфер не содержит данных, пользователя просят ввести данные для извлечения (так бывает в большинстве случаев). Когда пользователь нажимает Enter, во входной буфер помещается символ ‘n’.
operator>> извлекает столько данных из входного буфера, сколько может, в переменную (игнорируя любые начальные пробельные символы, такие как пробелы, табуляции или ‘n’).
Любые данные, которые не могут быть извлечены, остаются во входном буфере для следующего извлечения.

Извлечение завершается успешно, если из входного буфера извлечен хотя бы один символ. Любые неизвлеченные входные данные остаются во входном буфере для дальнейшего извлечения. Например:

int x{};
std::cin >> x;

Если пользователь вводит «5a», 5 будет извлечено, преобразовано в целое число и присвоено переменной x. А «an» останется во входном потоке для следующего извлечения.

Извлечение не выполняется, если входные данные не соответствуют типу переменной, в которую они извлекаются. Например:

int x{};
std::cin >> x;

Если бы пользователь ввел ‘b’, извлечение не удалось бы, потому что ‘b’ не может быть извлечено в переменную типа int.

Проверка ввода

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

Есть три основных способа проверки ввода:

встроенный (по мере печати пользователя):
- прежде всего, не позволить пользователю вводить недопустимые данные;
пост-запись (после печати пользователя):
- позволить пользователю ввести в строку всё, что он хочет, затем проверить правильность строки и, если она корректна, преобразовать строку в окончательный формат переменной;
- позволить пользователю вводить всё, что он хочет, позволить std::cin и operator>> попытаться извлечь данные и обработать случаи ошибок.

Некоторые графические пользовательские интерфейсы и расширенные текстовые интерфейсы позволяют проверять входные данные, когда пользователь их вводит (символ за символом). В общем случае, программист предоставляет функцию проверки, которая принимает входные данные, введенные пользователем, и возвращает true, если входные данные корректны, и false в противном случае. Эта функция вызывается каждый раз, когда пользователь нажимает клавишу. Если функция проверки возвращает истину, клавиша, которую только что нажал пользователь, принимается. Если функция проверки возвращает false, введенный пользователем символ отбрасывается (и не отображается на экране). Используя этот метод, вы можете гарантировать, что любые входные данные, вводимые пользователем, гарантированно будут корректными, потому что любые недопустимые нажатия клавиш обнаруживаются и немедленно отбрасываются. Но, к сожалению, std::cin не поддерживает этот стиль проверки.

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

Чаще всего мы позволяем std::cin и оператору извлечения выполнять эту тяжелую работу. В этом методе мы позволяем пользователю вводить всё, что он хочет, заставляем std::cin и operator>> попытаться извлечь данные и справиться с последствиями, если это не удастся. Это самый простой способ, о котором мы поговорим ниже.

Пример программы

Рассмотрим следующую программу-калькулятор, в которой нет обработки ошибок:

#include <iostream>
 
double getDouble()
{
    std::cout << "Enter a double value: ";
    double x{};
    std::cin >> x;
    return x;
}
 
char getOperator()
{
    std::cout << "Enter one of the following: +, -, *, or /: ";
    char op{};
    std::cin >> op;
    return op;
}
 
void printResult(double x, char operation, double y)
{
    switch (operation)
    {
    case '+':
        std::cout << x << " + " << y << " is " << x + y << 'n';
        break;
    case '-':
        std::cout << x << " - " << y << " is " << x - y << 'n';
        break;
    case '*':
        std::cout << x << " * " << y << " is " << x * y << 'n';
        break;
    case '/':
        std::cout << x << " / " << y << " is " << x / y << 'n';
        break;
    }
}
 
int main()
{
    double x{ getDouble() };
    char operation{ getOperator() };
    double y{ getDouble() };
 
    printResult(x, operation, y);
 
    return 0;
}

Эта простая программа просит пользователя ввести два числа и математический оператор.

Enter a double value: 5
Enter one of the following: +, -, *, or /: *
Enter a double value: 7
5 * 7 is 35

Теперь подумайте, где неверный ввод пользователя может нарушить работу этой программы.

Сначала мы просим пользователя ввести несколько чисел. Что, если он введет что-то, отличающееся от числа (например, ‘q’)? В этом случае извлечение не удастся.

Во-вторых, мы просим пользователя ввести один из четырех возможных символов. Что, если он введет символ, отличный от ожидаемых? Мы сможем извлечь входные данные, но пока не обрабатываем то, что происходит после.

В-третьих, что, если мы попросим пользователя ввести символ, а он введет строку типа «*q hello». Хотя мы можем извлечь нужный нам символ ‘*’, в буфере останутся дополнительные входные данные, которые могут вызвать проблемы в будущем.

Типы недопустимых входных текстовых данных

Обычно мы можем разделить ошибки ввода текста на четыре типа:

извлечение входных данных выполняется успешно, но входные данные не имеют смысла для программы (например, ввод ‘k’ в качестве математического оператора);
извлечение входных данных выполняется успешно, но пользователь вводит дополнительные данные (например, вводя «*q hello» в качестве математического оператора);
ошибка извлечения входных данных (например, попытка ввести ‘q’ при запросе ввода числа);
извлечение входных данных выполнено успешно, но пользователь выходит за пределы значения числа.

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

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

Случай ошибки 1: извлечение успешно, но входные данные не имеют смысла

Это самый простой случай. Рассмотрим следующий вариант выполнения приведенной выше программы:

Enter a double value: 5
Enter one of the following: +, -, *, or /: k
Enter a double value: 7

В этом случае мы попросили пользователя ввести один из четырех символов, но вместо этого он ввел ‘k’. ‘k’ – допустимый символ, поэтому std::cin успешно извлекает его в переменную op, и она возвращается в main. Но наша программа не ожидала этого, поэтому она не обрабатывает этот случай правильно (и, таким образом, ничего не выводит).

Решение здесь простое: выполните проверку ввода. Обычно она состоит из 3 шагов:

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

Вот обновленная функция getOperator(), которая выполняет проверку ввода.

char getOperator()
{
    while (true) // Цикл, пока пользователь не введет допустимые данные
    {
        std::cout << "Enter one of the following: +, -, *, or /: ";
        char operation{};
        std::cin >> operation;
 
        // Проверяем, ввел ли пользователь подходящие данные
        switch (operation)
        {
        case '+':
        case '-':
        case '*':
        case '/':
          return operation; // возвращаем символ вызывающей функции
        default: // в противном случае сообщаем пользователю, что пошло не так
            std::cout << "Oops, that input is invalid.  Please try again.n";
        }
    } // и попробуем еще раз
}

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

Случай ошибки 2: извлечение успешно, но с посторонними входными данными

Рассмотрим следующий вариант выполнения приведенной выше программы:

Enter a double value: 5*7

Как думаете, что будет дальше?

Enter a double value: 5*7
Enter one of the following: +, -, *, or /: Enter a double value: 5 * 7 is 35

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

Когда пользователь вводит «5*7» в качестве вводных данных, эти данные попадают в буфер. Затем оператор >> извлекает 5 в переменную x, оставляя в буфере «*7n». Затем программа напечатает «Enter one of the following: +, -, *, or /:». Однако когда был вызван оператор извлечения, он видит символы «*7n», ожидающие извлечения в буфере, поэтому он использует их вместо того, чтобы запрашивать у пользователя дополнительные данные. Следовательно, он извлекает символ ‘*’, оставляя в буфере «7n».

После запроса пользователя ввести другое значение double, из буфера извлекается 7 без ожидания ввода пользователя. Поскольку у пользователя не было возможности ввести дополнительные данные и нажать Enter (добавляя символ новой строки), все запросы в выводе идут вместе в одной строке, даже если вывод правильный.

Хотя программа работает, выполнение запутано. Было бы лучше, если бы любые введенные посторонние символы просто игнорировались. К счастью, символы игнорировать легко:

// очищаем до 100 символов из буфера или пока не будет удален символ 'n'
std::cin.ignore(100, 'n');

Этот вызов удалит до 100 символов, но если пользователь ввел более 100 символов, мы снова получим беспорядочный вывод. Чтобы игнорировать все символы до следующего символа ‘n’, мы можем передать std::numeric_limits<std::streamsize>::max() в std::cin.ignore(). std::numeric_limits<std::streamsize>::max() возвращает наибольшее значение, которое может быть сохранено в переменной типа std::streamsize. Передача этого значения в std::cin.ignore() приводит к отключению проверки счетчика.

Чтобы игнорировать всё, вплоть до следующего символа ‘n’, мы вызываем

std::cin.ignore(std::numeric_limits<std::streamsize>::max(), 'n');

Поскольку эта строка довольно длинная для того, что она делает, будет удобнее обернуть ее в функцию, которую можно вызвать вместо std::cin.ignore().

#include <limits> // для std::numeric_limits
 
void ignoreLine()
{
  std::cin.ignore(std::numeric_limits<std::streamsize>::max(), 'n');
}

Поскольку последний введенный пользователем символ должен быть ‘n’, мы можем указать std::cin игнорировать символы в буфере, пока не найдет символ новой строки (который также будет удален).

Давайте обновим нашу функцию getDouble(), чтобы игнорировать любой посторонний ввод:

double getDouble()
{
    std::cout << "Enter a double value: ";
    double x{};
    std::cin >> x;
    ignoreLine();
    return x;
}

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

Случай ошибки 3: сбой при извлечении

Теперь рассмотрим следующий вариант выполнения нашей программы калькулятора:

Enter a double value: a

Неудивительно, что программа работает не так, как ожидалось, но интересно, как она дает сбой:

Enter a double value: a
Enter one of the following: +, -, *, or /: Enter a double value:

и программа внезапно завершается.

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

Когда пользователь вводит ‘a’, этот символ помещается в буфер. Затем оператор >> пытается извлечь ‘a’ в переменную x, которая имеет тип double. Поскольку ‘a’ нельзя преобразовать в double, оператор >> не может выполнить извлечение. В этот момент происходят две вещи: ‘a’ остается в буфере, а std::cin переходит в «режим отказа».

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

К счастью, мы можем определить, завершилось ли извлечение сбоем, и исправить это:

if (std::cin.fail()) // предыдущее извлечение не удалось?
{
    // да, давайте разберемся с ошибкой
    std::cin.clear(); // возвращаем нас в "нормальный" режим работы
    ignoreLine();     // и удаляем неверные входные данные
}

Вот и всё!

Давайте, интегрируем это в нашу функцию getDouble():

double getDouble()
{
    while (true) // Цикл, пока пользователь не введет допустимые данные
    {
        std::cout << "Enter a double value: ";
        double x{};
        std::cin >> x;
 
        if (std::cin.fail()) // предыдущее извлечение не удалось?
        {
            // да, давайте разберемся с ошибкой
            std::cin.clear(); // возвращаем нас в "нормальный" режим работы
            ignoreLine();     // и удаляем неверные входные данные
        }
        else // иначе наше извлечение прошло успешно
        {
            ignoreLine();
            return x; // поэтому возвращаем извлеченное нами значение
        }
    }
}

Примечание. До C++11 неудачное извлечение не приводило к изменению извлекаемой переменной. Это означает, что если переменная была неинициализирована, она останется неинициализированной в случае неудачного извлечения. Однако, начиная с C++11, неудачное извлечение из-за недопустимого ввода приведет к тому, что переменная будет инициализирована нулем. Инициализация нулем означает, что для переменной установлено значение 0, 0.0, «» или любое другое значение, в которое 0 преобразуется для этого типа.

Случай ошибки 4: извлечение успешно, но пользователь выходит за пределы значения числа

Рассмотрим следующий простой пример:

#include <cstdint>
#include <iostream>
 
int main()
{
    std::int16_t x{}; // x - 16 бит, может быть от -32768 до 32767
    std::cout << "Enter a number between -32768 and 32767: ";
    std::cin >> x;
 
    std::int16_t y{}; // y - 16 бит, может быть от -32768 до 32767
    std::cout << "Enter another number between -32768 and 32767: ";
    std::cin >> y;
 
    std::cout << "The sum is: " << x + y << 'n';
    return 0;
}

Что произойдет, если пользователь введет слишком большое число (например, 40000)?

Enter a number between -32768 and 32767: 40000
Enter another number between -32768 and 32767: The sum is: 32767

В приведенном выше случае std::cin немедленно переходит в «режим отказа», но также присваивает переменной ближайшее значение в диапазоне. Следовательно, x остается с присвоенным значением 32767. Дополнительные входные данные пропускаются, оставляя y с инициализированным значением 0. Мы можем обрабатывать этот вид ошибки так же, как и неудачное извлечение.

Примечание. До C++11 неудачное извлечение не приводило к изменению извлекаемой переменной. Это означает, что если переменная была неинициализирована, в случае неудачного извлечения она останется неинициализированной. Однако, начиная с C++11, неудачное извлечение вне диапазона приведет к тому, что переменной будет присвоено ближайшее значение в диапазоне.

Собираем всё вместе

Вот наш пример калькулятора с полной проверкой ошибок:

#include <iostream>
#include <limits>
 
void ignoreLine()
{
  std::cin.ignore(std::numeric_limits<std::streamsize>::max(), 'n');
}
 
double getDouble()
{
    while (true) // Цикл, пока пользователь не введет допустимые данные
    {
        std::cout << "Enter a double value: ";
        double x{};
        std::cin >> x;
 
        // Проверяем на неудачное извлечение
        if (std::cin.fail()) // предыдущее извлечение не удалось?
        {
            // да, давайте разберемся с ошибкой
            std::cin.clear(); // возвращаем нас в "нормальный" режим работы
            ignoreLine();     // и удаляем неверные входные данные
            std::cout << "Oops, that input is invalid.  Please try again.n";
        }
        else
        {
            ignoreLine(); // удаляем любые посторонние входные данные
 
            // пользователь не может ввести бессмысленное значение double,
            // поэтому нам не нужно беспокоиться о его проверке
            return x;
        }
    }
}
 
char getOperator()
{
    while (true) // Цикл, пока пользователь не введет допустимые данные
    {
        std::cout << "Enter one of the following: +, -, *, or /: ";
        char operation{};
        std::cin >> operation;
        ignoreLine();
 
        // Проверяем, ввел ли пользователь осмысленные данные
        switch (operation)
        {
        case '+':
        case '-':
        case '*':
        case '/':
            return operation; // возвращаем символ вызывающей функции
        default: // в противном случае сообщаем пользователю, что пошло не так
            std::cout << "Oops, that input is invalid.  Please try again.n";
        }
    } // и попробуем еще раз
}
 
void printResult(double x, char operation, double y)
{
    switch (operation)
    {
    case '+':
        std::cout << x << " + " << y << " is " << x + y << 'n';
        break;
    case '-':
        std::cout << x << " - " << y << " is " << x - y << 'n';
        break;
    case '*':
        std::cout << x << " * " << y << " is " << x * y << 'n';
        break;
    case '/':
        std::cout << x << " / " << y << " is " << x / y << 'n';
        break;
    default: // Надежность означает также обработку неожиданных параметров,
             // даже если getOperator() гарантирует, что op в этой
             // конкретной программе корректен 
        std::cerr << "Something went wrong: printResult() got an invalid operator.n";
    }
}
 
int main()
{
    double x{ getDouble() };
    char operation{ getOperator() };
    double y{ getDouble() };
 
    printResult(x, operation, y);
 
    return 0;
}

Заключение

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

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

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

Следующий код очистит любые посторонние входные данные:

std::cin.ignore(std::numeric_limits<std::streamsize>::max(), 'n');

Следующий код будет проверять и исправлять неудачные извлечения или переполнение:

if (std::cin.fail()) // предыдущее извлечение не удалось или закончилось переполнением?
{
    // да, давайте разберемся с ошибкой
    std::cin.clear(); // возвращаем нас в "нормальный" режим работы
    ignoreLine();     // и удаляем неверные входные данные
}

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

Примечание автора

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

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

При настройке рабочей книги для пользователей часто может потребоваться контролировать ввод информации в определенные ячейки, чтобы убедиться, что все введенные данные точны и непротиворечивы. Кроме того, вы можете захотеть разрешить в ячейке только определенный тип данных, например числа или даты, или ограничить числа определенным диапазоном, а текст — заданной длиной. Возможно, вы даже захотите предоставить заранее определенный список допустимых значений, чтобы исключить возможные ошибки. Проверка данных Excel позволяет выполнять все эти действия во всех версиях Microsoft Excel 365, 2019, 2016, 20013, 2010 и более ранних версиях.

Что такое проверка данных в Excel?
Как сделать проверку данных в Excel
Как настроить ограничения проверки данных Excel
Проверка даты и времени в Excel
Разрешить только будни или выходные
Проверить даты на основе сегодняшней даты
Проверка времени на основе текущего времени
Проверка длины текста
Список проверки данных Excel (раскрывающийся список)
Разрешить только числа
Разрешить только текст
Разрешить текст, начинающийся с определенных символов
Формула проверки с логикой ИЛИ (несколько критериев)
Проверка ввода с учетом регистра
Разрешить только значения, содержащие определенный текст
Разрешить только уникальные записи и запретить дубликаты
Как отредактировать проверку данных в Excel
Как скопировать правило проверки данных Excel в другие ячейки
Как найти ячейки с проверкой данных в Excel
Как убрать проверку данных в Excel
Советы по проверке данных в Excel
Проверка данных на основе другой ячейки
Правила проверки на основе формул
Как найти неверные данные на листе
Как поделиться книгой с проверкой данных
Почему проверка данных Excel не работает?

Что такое проверка данных в Excel?

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

Вот лишь несколько примеров того, что может сделать проверка данных в Excel:

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

Например, вы можете настроить правило, которое ограничивает ввод данных 3-значными числами от 100 до 999. Если пользователь вводит что-то другое, Excel покажет предупреждение об ошибке, объясняющее, что было сделано неправильно:

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

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

1. Откройте диалоговое окно «Проверка данных».

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

2. Создайте правило проверки Excel.

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

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

В качестве примера создадим правило, разрешающее пользователям вводить только целое число от 100 до 999:

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

3. Подсказка по вводу (необязательно).

Если вы хотите отобразить сообщение, объясняющее пользователю, какие данные разрешены в данной ячейке, откройте соответствующую вкладку и выполните следующие действия:

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

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

4. Отображение предупреждения об ошибке (необязательно)

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

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

Чтобы настроить пользовательское сообщение об ошибке, перейдите на вкладку «Сообщение об ошибке» и задайте следующие параметры:

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

И теперь, если пользователь введет недопустимые значения, Excel отобразит специальное предупреждение с объяснением ошибки (как показано в начале этого руководства).

Примечание. Если вы не введете собственное сообщение, появится стандартное предупреждение Stop со следующим текстом: Это значение не соответствует ограничениям проверки данных, установленным для этой ячейки.

Как настроить ограничения проверки данных Excel

При добавлении правила проверки данных в Excel вы можете выбрать один из предопределенных параметров или указать новые критерии на основе собственной формулы. Ниже мы обсудим каждую из встроенных опций.

Как вы уже знаете, критерии проверки определяются на вкладке «Параметры» диалогового окна «Проверка данных» (вкладка «Данные» > «Проверка данных»).

В первую очередь нужно настроить проверку типа записываемых данных.

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

Равно или не равно указанному числу
Больше или меньше указанного числа
Между двумя числами или вне, чтобы исключить этот диапазон чисел

Например, вот как выглядят ограничения по проверке данных Excel, которые допускают любое целое число больше 100:

Проверка даты и времени в Excel

Чтобы проверить даты, выберите «Дата» в поле «Тип данных», а затем выберите соответствующий критерий в поле «Значение». Существует довольно много предопределенных параметров на выбор: разрешить только даты между двумя датами, равные, большие или меньшие определенной даты и т. д.

Точно так же, чтобы проверить время, выберите Время в поле Значение, а затем определите необходимые критерии.

Например, чтобы разрешить только даты между датой начала в B1 и датой окончания в B2, примените это правило проверки даты Excel:

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

Чтобы разрешить пользователю вводить даты только будних или выходных дней, настройте пользовательское правило проверки на основе функции ДЕНЬНЕД (WEEKDAY).

Если для второго аргумента установлено значение 2, функция возвращает целое число в диапазоне от 1 (понедельник) до 7 (воскресенье). Так, для будних дней (пн-пт) результат формулы должен быть меньше 6, а для выходных (сб и вс) — больше 5.

Таким образом, разрешить только рабочие дни:

=ДЕНЬНЕД( ячейка ; 2)<6

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

=ДЕНЬНЕД( ячейка ; 2)>5

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

=ДЕНЬНЕД(A2;2)<6

Проверить даты на основе сегодняшней даты

Во многих случаях может потребоваться использовать сегодняшнюю дату в качестве начальной даты допустимого диапазона дат. Чтобы получить текущую дату, используйте функцию СЕГОДНЯ , а затем добавьте к ней нужное количество дней, чтобы вычислить дату окончания временного периода.

Например, чтобы ограничить ввод данных через 6 дней (7 дней, включая сегодняшний день), мы можем использовать встроенное правило даты с критериями в виде формул:

Выберите Дата в поле Тип данных
Выберите в поле Значение – между
В поле Начальная дата введите выражение =СЕГОДНЯ()
В поле Конечная дата введите =СЕГОДНЯ() + 6

Аналогичным образом вы можете ограничить пользователей вводом дат до или после сегодняшней даты. Для этого выберите меньше или больше, чем в поле Значение, а затем введите =СЕГОДНЯ() в поле Начальная дата или Конечная дата соответственно.

Проверка времени на основе текущего времени

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

В поле Тип данных выберите Время .

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

В поле Время окончания или Время начала (в зависимости от того, какие критерии вы выбрали на предыдущем шаге) введите одну из следующих формул:

Чтобы проверить дату и время на основе текущей даты и времени:

=ТДАТА()

Чтобы проверить время на основе текущего времени, используйте выражение:

=ВРЕМЯ(ЧАС(ТДАТА());МИНУТЫ(ТДАТА());СЕКУНДЫ(ТДАТА()))

Проверка длины текста

Чтобы разрешить ввод данных определенной длины, выберите Длина текста в поле Тип данных и укажите критерии проверки в соответствии с вашей бизнес-логикой.

Например, чтобы ограничить ввод до 15 символов, создайте такое правило:

Примечание. Параметр «Длина текста» ограничивает количество символов, но не тип данных. Это означает, что приведенное выше правило разрешает как текст, так и числа до 15 символов или 15 цифр соответственно.

Список проверки данных Excel (раскрывающийся список)

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

Откройте диалоговое окно «Проверка данных» (вкладка «Данные» > «Проверка данных»).
На вкладке «Настройки» выберите «Список» в поле «Тип данных».
В поле Источник введите элементы списка проверки Excel, разделенные точкой с запятой. Например, чтобы ограничить пользовательский ввод тремя вариантами, введите Да; Нет; Н/Д.
Убедитесь, что выбрана опция Список допустимых значений, чтобы стрелка раскрывающегося списка отображалась рядом с ячейкой.
Нажмите ОК.

Выпадающий список проверки данных Excel будет выглядеть примерно так:

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

Другие способы создания списка проверки данных в Excel

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

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

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

Разрешить только числа

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

Удивительно, но ни одно из встроенных правил проверки данных Excel не подходит для очень типичной ситуации, когда вам нужно ограничить пользователей вводом только чисел в определенные ячейки. Но это можно легко сделать с помощью пользовательской формулы проверки данных, основанной на функции ЕЧИСЛО(), например:

=ЕЧИСЛО(C2)

Где C2 — самая верхняя ячейка диапазона, который вы хотите проверить.

Примечание. Функция ЕЧИСЛО допускает любые числовые значения в проверенных ячейках, включая целые числа, десятичные дроби, дроби, а также даты и время, которые также являются числами в Excel.

Разрешить только текст

Если вы ищете обратное — разрешить только текстовые записи в заданном диапазоне ячеек, то создайте собственное правило с функцией ЕТЕКСТ (ISTEXT), например:

=ЕТЕКСТ(B2)

Где B2 — самая верхняя ячейка выбранного диапазона.

Разрешить текст, начинающийся с определенных символов

Если все значения в определенном диапазоне должны начинаться с определенного символа или подстроки, выполните проверку данных Excel на основе функции СЧЁТЕСЛИ с подстановочным знаком:

=СЧЁТЕСЛИ(A2; » текст *»)

Например, чтобы убедиться, что все идентификаторы заказов в столбце A начинаются с префикса «AРТ-», «арт-», «Aрт-» или «aРт-» (без учета регистра), определите пользовательское правило с этой проверкой данных.

=СЧЁТЕСЛИ(A2;»АРТ-*»)

Формула проверки с логикой ИЛИ (несколько критериев)

В случае, если есть 2 или более допустимых префикса, добавьте несколько функций СЧЁТЕСЛИ, чтобы ваше правило проверки данных Excel работало с логикой ИЛИ:

=СЧЁТЕСЛИ(A2;»АРТ-*»)+СЧЁТЕСЛИ(A2;»АБВ-*»)

Проверка ввода с учетом регистра

Если регистр символов имеет значение, используйте СОВПАД (EXACT) в сочетании с функцией ЛЕВСИМВ, чтобы создать формулу проверки с учетом регистра для записей, начинающихся с определенного текста:

=СОВПАД(ЛЕВСИМВ(ячейка; число_символов); текст)

Например, чтобы разрешить только те коды заказов, которые начинаются с «AРТ-» (ни «арт-», ни «Арт-» не допускаются), используйте эту формулу:

=СОВПАД(ЛЕВСИМВ(A2;4);»АРТ-«)

В приведенной выше формуле функция ЛЕВСИМВ извлекает первые 4 символа из ячейки A2, а СОВПАД выполняет сравнение с учетом регистра с жестко заданной подстрокой (в данном примере «AРТ-«). Если две подстроки точно совпадают, формула возвращает ИСТИНА и проверка проходит успешно; в противном случае возвращается ЛОЖЬ и проверка завершается неудачно.

Разрешить только значения, содержащие определенный текст

Чтобы разрешить ввод значений, которые содержат определенный текст в любом месте ячейки (в начале, середине или конце), используйте функцию ЕЧИСЛО (ISNUMBER) в сочетании с НАЙТИ (FIND) или ПОИСК (SEARCH) в зависимости от того, хотите ли вы совпадение с учетом регистра или без учета регистра:

Проверка без учета регистра:

ЕЧИСЛО(ПОИСК( текст ; ячейка ))

Проверка с учетом регистра:

ЕЧИСЛО(НАЙТИ( текст ; ячейка ))

В нашем примере, чтобы разрешить только записи, содержащие текст «AР» в ячейках A2: A8, используйте одну из следующих формул, создав правило проверки в ячейке A2:

Без учета регистра:

=ЕЧИСЛО(ПОИСК(«ар»;A2))

С учетом регистра:

=ЕЧИСЛО(НАЙТИ(«АР»;A2))

Формулы работают по следующей логике:

Вы ищете подстроку «AР» в ячейке A2, используя НАЙТИ или ПОИСК, и оба возвращают позицию первого символа в подстроке. Если текст не найден, возвращается ошибка. Если поиск успешен и «АР» найден в ячейке, мы получаем номер позиции в тексте, где эта подстрока была найдена. Далее функция ЕЧИСЛО возвращает ИСТИНА, и проверка данных проходит успешно. В случае, если подстроку не удалось найти, результатом будет ошибка и ЕЧИСЛО возвращает ЛОЖЬ. Запись не будет разрешена в ячейке.

Разрешить только уникальные записи и запретить дубликаты

В ситуациях, когда определенный столбец или диапазон ячеек не должны содержать дубликатов, настройте пользовательское правило проверки данных, разрешающее только уникальные записи. Для этого мы можем использовать классическую формулу СЧЁТЕСЛИ для выявления дубликатов :

=СЧЁТЕСЛИ( диапазон ; самая верхняя_ячейка )<=1

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

=СЧЁТЕСЛИ($A$2:$A$8; A2)<=1

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

Обратите внимание, что мы фиксируем диапазон абсолютными ссылками на ячейки (A$2:$A$8) и используем относительную ссылку для верхней ячейки (A2), чтобы формула корректно изменялась для каждой ячейки в проверяемом диапазоне.

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

Чтобы изменить правило проверки Excel, выполните следующие действия:

Выберите любую из проверенных ячеек.
Откройте диалоговое окно «Проверка данных» (вкладка «Данные» > «Проверка данных»).
Внесите необходимые изменения.
Установите флажок Применить эти изменения ко всем другим ячейкам с теми же параметрами, чтобы скопировать внесенные вами изменения во все остальные ячейки с исходными критериями проверки.
Нажмите OK, чтобы сохранить изменения.

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

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

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

Чтобы скопировать правило проверки в Excel, выполните следующие 4 быстрых шага:

Выберите ячейку, к которой применяется правило проверки, и нажмите Ctrl + С , чтобы скопировать его.
Выберите другие ячейки, которые вы хотите проверить. Чтобы выделить несмежные ячейки, нажмите и удерживайте клавишу Ctrl при выборе ячеек.
Щелкните выделенный фрагмент правой кнопкой мыши, выберите «Специальная вставка» и выберите параметр «Условия на значения».

Либо используйте комбинацию клавиш Ctrl + Alt + V, и затем — Н.

Нажмите ОК.

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

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

Чтобы быстро найти все проверенные ячейки на текущем листе, перейдите на вкладку «Главная» > группа «Редактирование» и нажмите «Найти и выделить» > «Проверка данных» :

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

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

В целом, есть два способа удалить проверку в Excel: стандартный подход, разработанный Microsoft, и метод без мыши, разработанный фанатами Excel, которые никогда не отрывают руки от клавиатуры без крайней необходимости (например, чтобы выпить чашку кофе:)

Способ 1: Обычный способ отключить проверку данных

Обычно, чтобы удалить проверку данных на листах Excel, выполните следующие действия:

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

Советы:

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

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

Способ 2: Удалить правила проверки данных при помощи Специальной вставки

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

Выберите пустую ячейку без проверки данных и нажмите Ctrl + С, чтобы скопировать ее в буфер обмена.
Выберите ячейки, в которых вы хотите отключить проверку данных.
Нажмите Ctrl + Alt + V, и затем Н. Эта комбинация клавиш вызовет «Специальная вставка» > «Проверка данных».
Нажимаем ОК.

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

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

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

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

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

Например, чтобы разрешить любое целое число, находящееся между 100 и 999, выберите критерии «минимум» и «максимум» в вкладке «Параметры» и введите в них адреса ячеек с этими числами:

Рис18

Вы также можете ввести формулу в ячейку, на которую указывает ссылка, и Excel проверит ввод на основе этой формулы.

Например, чтобы запретить пользователям вводить даты после сегодняшней даты, введите формулу =СЕГОДНЯ() в какую-нибудь ячейку, скажем, B1, а затем настройте правило проверки даты на основе этой ячейки:

Или вы можете ввести формулу =СЕГОДНЯ() непосредственно в поле Конечная дата, что будет иметь тот же эффект.

Правила проверки на основе формул

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

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

=МИН($С$2:$С$10)

=МАКС($С$2:$С$10)

Обратите внимание, что мы фиксируем диапазон с помощью знака $ (абсолютная ссылка на ячейки), чтобы наше правило проверки Excel работало правильно для всех выбранных ячеек.

Как найти неверные данные на листе

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

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

Это выделит все ячейки, которые не соответствуют критериям проверки.

Как только вы исправите неверную запись, отметка автоматически исчезнет. Чтобы удалить все отметки, перейдите на вкладку «Данные» и нажмите «Проверка данных» > «Удалить обводку неверных данных».

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

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

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

Почему проверка данных Excel не работает?

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

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

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

Хотя нет способа отключить клавиши копирования/вставки (кроме использования VBA), вы можете, по крайней мере, предотвратить копирование данных путем перетаскивания ячеек. Для этого выберите «Файл» > «Параметры» > «Дополнительно» > «Параметры редактирования» и снимите флажок «Включить маркер заполнения и перетаскивания ячеек».

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

Кнопка «Проверка данных» не активна (выделена серым цветом), если вы вводите или изменяете данные в ячейке. Закончив редактирование ячейки, нажмите Enter или Esc, чтобы выйти из режима редактирования, а затем выполните проверку данных.

Проверка данных не может быть применена к защищенной или общей книге

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

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

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

Формула проверки не возвращает ошибок.
Формула не ссылается на пустые ячейки.
Используются правильные ссылки на ячейки.

Ручной пересчет формул включен

Если в Excel включен режим ручного расчета, невычисленные формулы могут помешать правильной проверке данных. Чтобы снова изменить параметр расчета Excel на автоматический, перейдите на вкладку «Формулы» > группу «Расчет», нажмите кнопку «Параметры расчета» и выберите «Автоматически».

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

Для начала скопируйте формулу проверки в какую-нибудь ячейку, чтобы убедиться, что она не возвращает ошибку, такую как #Н/Д, #ЗНАЧ или #ДЕЛ/0!.

Если вы создаете пользовательское правило , формула должна возвращать логические значения ИСТИНА и ЛОЖЬ или приравненные к ним значения 1 и 0 соответственно.

Если вы используете критерий на основе формулы во встроенном правиле (как мы делали для проверки времени на основе текущего времени), он также может возвращать неправильное числовое значение.

Формула проверки данных не должна ссылаться на пустую ячейку

Если вы активируете опцию «Игнорировать пустые ячейки» при определении правила (обычно выбрано по умолчанию), то следите, чтобы ваши формулы или условия не ссылались на пустую ячейку.

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

Абсолютные и относительные ссылки на ячейки в формулах проверки

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

Если вы создаете правило для нескольких ячеек и ваши критерии проверки содержат адреса конкретных ячеек , обязательно зафиксируйте ячейку в формуле при помощи абсолютной ссылки (со знаком $, например, $A$1), иначе ваше правило будет правильно работать только для первой ячейки.

Чтобы лучше проиллюстрировать это, рассмотрим следующий пример.

Предположим, вы хотите ограничить ввод данных в ячейках с A2 по A8 целыми числами от 100 (минимальное значение) до 999. Чтобы проще было при необходимости изменить критерии проверки, используем ссылки на ячейки с этими значениями, как показано на скриншоте ниже:

Это правило будет корректно выполняться только для первой ячейки диапазона – A2. Для ячейки A3 критерии максимума и минимума изменятся на E3 и F3. Поскольку эти ячейки пусты, то условие ввода теперь – любое число больше либо меньше нуля. Правда, символьные значения и дробные числа вы вводить не сможете, так как продолжает действовать ограничение на тип данных – целые числа.

Чтобы исправить формулу, просто введите «$» перед ссылками на столбцы и строки, чтобы зафиксировать их: =$E$2 и $F$2. Или используйте клавишу F4 для переключения между различными типами ссылок.

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

Источник

ВикиЧтение

С компьютером на ты. Самое необходимое
Егоров А. А.

4.5. Исправление ошибок и автозамена

Как я упоминал ранее, Word может исправлять ошибки в тексте. Слова, в которых допущены орфографические ошибки, подчеркиваются красной линией; слова и словосочетания, в которых имеются грамматические ошибки, — зеленой линией. Чтобы исправить ошибку, щелкните правой кнопкой мыши на слове, подчеркнутом красной линией — программа предложит правильный вариант (или несколько вариантов) слова (рис. 4.22). Если вариантов нет, то Word честно признается, что о таком слове он не «слышал».

Примечание

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

Меню грамматической ошибки выглядит немного по-другому (рис. 4.23) — в данном случае программа указывает на саму ошибку, которую вы можете исправить самостоятельно.

Также вы можете пропустить предложение или вызывать окно Грамматика (рис. 4.24), в котором ошибку можно исправить.

Правописание по всему тексту сразу можно проверить, выбрав опцию Правописание на вкладке Рецензирование или нажав клавишу <F7>.

Еще одна полезная функция Word — автозамена. Если вы, например, часто повторяете одно и то же слово в тексте, то можете настроить программу таким образом, что она будет заменять слова автоматически — например, если наберете слово «комп», то оно заменится на слово «компьютер». По умолчанию в Word задана автозамена наиболее известных символов, которых нет на клавиатуре, например последовательность символов (с) автоматически заменяется на значок копирайта ©, а ™ заменяется символом ™ (от Trade Mark, торговая марка). Полный список можно посмотреть на вкладке окна Автозамена (рис. 4.25), вызвав его из окна параметров Word[41]. Кроме специальных символов Word исправляет наиболее распространенные опечатки, например «ыбстро» заменяется на «быстро», «экарн» на «экран» и т. д.

Если вы хотите добавить слово, введите его в текстовом поле Заменить над списком. Слева пишется слово, которое должно заменяться, а справа — слово, на которое оно должно заменяться.

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

Ряд нужных параметров автозамены устанавливается на вкладке Автоформат (рис. 4.26) — здесь включается замена прямых кавычек парными, дроби — соответствующими знаками, двойной дефис меняется на тире, а адреса Интернета автоматически становятся гиперссылками. Советую ничего здесь не менять и все оставить по умолчанию.

Данный текст является ознакомительным фрагментом.

Условие проверки

Нажмите на любую клетку.
Перейдите на вкладку «Данные».
Кликните на выделенный инструмент.
Выберите пункт «Проверка данных».

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

Рассмотрим эти форматы более внимательно.

Любое значение

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

Целое число

Выбрав этот формат, вы сможете отредактировать дополнительную настройку во втором поле.

Действительное

Список

Этот формат наиболее интересный.

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

Заполните чем-нибудь несколько клеток. Неважно чем.

Нажмите на какую-нибудь клетку. Перейдите на знакомую вам вкладку. Кликните на иконку «Работа с данными». Выберите выделенный инструмент.

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

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

Время

Аналогично и тут. Только здесь указывается одно время (без даты).

Длина текста

Вариантов довольно много. Этот способ используется при заполнении каких-нибудь бланков или анкет.

Другой

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

Повторите описанные выше шаги по вызову окна «Проверка вводимых значений».
Выберите какой-нибудь тип данных. В качестве примера мы укажем, что необходимо вводить «Действительное» число, которое больше 10.

Теперь переходим на вкладку «Сообщение об ошибке».
Выбираем вид отображения:
«Останов»;
«Предупреждение»;
«Сообщение».
Указываем заголовок и текст уведомления.
Для сохранения проверки ввода значений нажмите на «OK»

В качестве примера мы укажем следующие настройки.

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

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

Если сделать тип «Останов», то ввести неверное значение не удастся.

А теперь попробуйте убрать введенные настройки и оставить пустые поля.

Но обратите внимание на то, что текст сообщения отличается!

Отличие версий Microsoft Excel

В старом Excel 2003 ошибка точно такая же.

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

Кликните на любую клетку, для которой вы хотите задать какие-нибудь правила.
Откройте в меню раздел «Данные».
Выберите инструмент «Работа с данными».
Нажмите на иконку «Проверка данных».

В появившемся окне перейдите на вкладку «Сообщение для ввода».
Укажите любой текст в указанных полях.
Для продолжения нажмите на «OK».

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

Как отключить эту ошибку

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

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

Теперь можно вносить любые данные, словно вы открыли пустой файл и никаких настроек там нет.

Примеры от компании Microsoft

Заключение

Видеоинструкция

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

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

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

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

В качестве примера укажем любое целое число не больше 100000.

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

• Останов — останавливает операцию, предотвращая ввод некорректных данных;
• Предупреждение — при вводе некорректных данных выдается предупреждение, но пользователю дается возможность продолжить операцию;
• Сообщение — пользователь уведомляется о том, что введенные данные недопустимы, однако ввод данных разрешен.

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

А вот так выглядит сообщение, выдаваемое при попытке ввода данных, не подходящих под заданное условие.

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

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

И если уж так случилось, что пользователям все таки удалось ″накосячить″, есть возможность выделить неправильно введенные данные. Сделать это можно, выбрав в меню «Проверка данных» пункт «Обвести неверные данные».

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

Источник

Ошибки при вводе данных можно исправить использовав

Стандартные математические функции

Стандартные статистические функции

Ошибка при воде данных

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

Набор значений, которые могут быть введены в ячейку Excel, ограничен: решение проблемы

Условие проверки

Любое значение

Целое число

Действительное

Список

Время

Длина текста

Другой

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

Отличие версий Microsoft Excel

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

Как отключить эту ошибку

Примеры от компании Microsoft

Заключение

Видеоинструкция

std::cin, буферы и извлечение

Проверка ввода

Пример программы

Типы недопустимых входных текстовых данных

Случай ошибки 1: извлечение успешно, но входные данные не имеют смысла

Случай ошибки 2: извлечение успешно, но с посторонними входными данными

Случай ошибки 3: сбой при извлечении

Случай ошибки 4: извлечение успешно, но пользователь выходит за пределы значения числа

Собираем всё вместе

Заключение

Теги

Что такое проверка данных в Excel?

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

Как настроить ограничения проверки данных Excel

Проверка даты и времени в Excel

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

Проверить даты на основе сегодняшней даты

Проверка времени на основе текущего времени

Проверка длины текста

Список проверки данных Excel (раскрывающийся список)

Другие способы создания списка проверки данных в Excel

Разрешить только числа

Разрешить только текст

Разрешить текст, начинающийся с определенных символов

Формула проверки с логикой ИЛИ (несколько критериев)

Проверка ввода с учетом регистра

Разрешить только значения, содержащие определенный текст

Разрешить только уникальные записи и запретить дубликаты

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

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

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

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

Способ 1: Обычный способ отключить проверку данных

Способ 2: Удалить правила проверки данных при помощи Специальной вставки

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

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

Правила проверки на основе формул

Как найти неверные данные на листе

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

Почему проверка данных Excel не работает?

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

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

Проверка данных не может быть применена к защищенной или общей книге

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

Ручной пересчет формул включен

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

Формула проверки данных не должна ссылаться на пустую ячейку

Абсолютные и относительные ссылки на ячейки в формулах проверки

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

Исправление ранее написанного кода

1.5. Создание текстовых документов (ввод, редактирование, исправление ошибок)

3. Исправление ошибок в русскоязычных текстах

Исправление коэффициента мощности

Исправление синтаксических ошибок

Автозамена

Автозамена как средство предотвращения ошибок при вводе

Орфография, стиль, автозамена

Исправление разрушенных страниц

5.8. Сервис. Автозамена. Колонтитулы

`std::cin`, буферы и извлечение