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

Понадобилось, в частности, проверять, что в ячейку введено целое число, значения которого попадают в указанный интервал (например, от 0 до 10). В принципе, проблемы могут быть следующие:

• введено вообще не число (проверка - стандартной функцией ЕЧИСЛО);
• введено не целое число (проверка - условием вида ЦЕЛОЕ(ЯЧЕЙКА)<>ЯЧЕЙКА);
• введено число не из диапазона допустимых значений (проверка - арифметическими условиями).

Однако, формулы с "линейной" логикой у меня не получилось - похоже, что Excel проверяет составные условия всегда полностью, так что условие вроде ИЛИ(ЕЧИСЛО(ЯЧЕЙКА)=ЛОЖЬ;ЦЕЛОЕ(ЯЧЕЙКА)<>ЯЧЕЙКА) всё равно породит стандартную ошибку #ЗНАЧ от функции ЦЕЛОЕ, при том, что правильность типа данных проверяется раньше... зато можно сделать через вложенные ЕСЛИ:

=ЕСЛИ(ЕЧИСЛО(ЯЧЕЙКА)=ЛОЖЬ;"-";ЕСЛИ(ИЛИ(ЯЧЕЙКА<НИЗ;ЯЧЕЙКА>ВЕРХ;ЦЕЛОЕ(ЯЧЕЙКА)<>ЯЧЕЙКА);"-";"+"))

Здесь ЯЧЕЙКА - ячейка с вводимой величиной, НИЗ - нижняя допустимая граница ввода (в данном случае целое число), ВЕРХ - верхняя допустимая граница ввода (в данном случае целое число).

Если пустая ячейка разрешена (в любых арифметических расчётах Excel будет интерпретировать её как ноль), формула станет лишь чуть-чуть сложнее:

=ЕСЛИ(ЯЧЕЙКА="";"+";ЕСЛИ(ЕЧИСЛО(ЯЧЕЙКА)=ЛОЖЬ;"-";ЕСЛИ(ИЛИ(ЯЧЕЙКА<НИЗ;ЯЧЕЙКА>ВЕРХ;ЦЕЛОЕ(ЯЧЕЙКА)<>ЯЧЕЙКА);"-";"+")))

На выходе обеих формул получается "+", если значение допустимо, иначе "-". Мне кажется, целесообразней вынести проверку в отдельную ячейку, которую можно потом скрыть или защитить, а основную формулу реализовать уже отдельным ЕСЛИ:

=ЕСЛИ(ЯЧЕЙКА1="-";"ОШИБКА";ФОРМУЛА)

где ЯЧЕЙКА1 - ячейка, содержащая предыдущую формулу, ОШИБКА - нужная строка с сообщеним об ошибке, например, ВВЕДИТЕ ЦЕЛОЕ ОТ 0 ДО 10 ВКЛЮЧИТЕЛЬНО, ФОРМУЛА - формула, по которой нужно выполнить расчёт над допустимыми данными.

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

=ЕСЛИ(ИЛИ(ЯЧЕЙКА1="-";ЯЧЕЙКА2="-";ЯЧЕЙКА3="-");"ОШИБКА";ФОРМУЛА)

для 3 ячеек с данными.

Не стоит забывать и о возможностях встроенной проверки данных. В Excel XP/2003 - меню Данные, пункт Проверка..., в Excel 2007/10 - вкладка Данные, группа Работа с данными, кнопка Проверка данных (верхняя справа в своей группе).

22.05.2013, 16:09 [12912 просмотров]