Как сделать сообщение об ошибке в excel?

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

Ошибка #ИМЯ?

Ошибка #ИМЯ появляется, когда имя, которое используется в формуле, было удалено или не было ранее определено.

Причины возникновения ошибки #ИМЯ?:

1. Если в формуле используется имя, которое было удалено или не определено.

Ошибки в Excel – Использование имени в формуле

Устранение ошибки: определите имя. Как это сделать описано в этой статье.

1. Ошибка в написании имени функции:

Ошибки в Excel – Ошибка в написании функции ПОИСКПОЗ

Устранение ошибки: проверьте правильность написания функции.

1. В ссылке на диапазон ячеек пропущен знак двоеточия (:).

Ошибки в Excel – Ошибка в написании диапазона ячеек

Устранение ошибки: исправьте формулу. В вышеприведенном примере это =СУММ(A1:A3).

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

Ошибки в Excel – Ошибка в объединении текста с числом

Устранение ошибки: заключите текст формулы в двойные кавычки.

Ошибки в Excel – Правильное объединение текста

Ошибка #ЧИСЛО!

Ошибка #ЧИСЛО! в Excel выводится, если в формуле содержится некорректное число. Например:

1. Используете отрицательное число, когда требуется положительное значение.

Ошибки в Excel – Ошибка в формуле, отрицательное значение аргумента в функции КОРЕНЬ

Устранение ошибки: проверьте корректность введенных аргументов в функции.

1. Формула возвращает число, которое слишком велико или слишком мало, чтобы его можно было представить в Excel.

Ошибки в Excel – Ошибка в формуле из-за слишком большого значения

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

Ошибка #ЗНАЧ!

Данная ошибка Excel возникает в том случае, когда в формуле введён аргумент недопустимого значения.

Причины ошибки #ЗНАЧ!:

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

Ошибки в Excel – Суммирование числовых и текстовых значений

Устранение ошибки: проверьте правильно ли заданы типы аргументов в формуле.

1. В аргументе функции введен диапазон, а функция предполагается ввод одного значения.

Ошибки в Excel – В функции ВПР в качестве аргумента используется диапазон, вместо одного значения

Устранение ошибки: укажите в функции правильные аргументы.

1. При использовании формулы массива нажимается клавиша Enter и Excel выводит ошибку, так как воспринимает ее как обычную формулу.

Устранение ошибки: для завершения ввода формулы используйте комбинацию клавиш Ctrl+Shift+Enter.

Ошибки в Excel – Использование формулы массива

Ошибка #ССЫЛКА

В случае если формула содержит ссылку на ячейку, которая не существует или удалена, то Excel выдает ошибку #ССЫЛКА.

Ошибки в Excel – Ошибка в формуле, из-за удаленного столбца А

Устранение ошибки: измените формулу.

Ошибка #ДЕЛ/0!

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

Ошибки в Excel – Ошибка #ДЕЛ/0!

Устранение ошибки: исправьте формулу.

Ошибка #Н/Д

Ошибка #Н/Д в Excel означает, что в формуле используется недоступное значение.

Причины ошибки #Н/Д:

1. При использовании функции ВПР, ГПР, ПРОСМОТР, ПОИСКПОЗ используется неверный аргумент искомое_значение:

Ошибки в Excel – Искомого значения нет в просматриваемом массиве

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

1. Ошибки в использовании функций ВПР или ГПР.

Устранение ошибки: см. раздел посвященный ошибкам функции ВПР

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

Ошибки в Excel – Ошибки в формуле массива

Устранение ошибки: откорректируйте диапазон ссылок формулы с соответствием строк и столбцов или введите формулу массива в недостающие ячейки.

1. В функции не заданы один или несколько обязательных аргументов.

Ошибки в Excel – Ошибки в формуле, нет обязательного аргумента

Устранение ошибки: введите все необходимые аргументы функции.

Ошибка #ПУСТО!

Ошибка #ПУСТО! в Excel возникает когда, в формуле используются непересекающиеся диапазоны.

Ошибки в Excel – Использование в формуле СУММ непересекающиеся диапазоны

Устранение ошибки: проверьте правильность написания формулы.

Ошибка ####

Причины возникновения ошибки

1. Ширины столбца недостаточно, чтобы отобразить содержимое ячейки.

Ошибки в Excel – Увеличение ширины столбца для отображения значения в ячейке

Устранение ошибки: увеличение ширины столбца/столбцов.

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

Ошибки в Excel – Разница дат и часов не должна быть отрицательной

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

Источник: https://naprimerax.org/posts/92/oshibki-v-excel

9 ошибок Excel, которые вас достали

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

Как исправить ошибки Excel?

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

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

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

Несколько полезных приемов в Excel

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

• Начинайте каждую формулу со знака «=» равенства.
• Используйте символ * для умножения чисел, а не X.
• Сопоставьте все открывающие и закрывающие скобки «()», чтобы они были в парах.
• Используйте кавычки вокруг текста в формулах.

9 распространенных ошибок Excel, которые вы бы хотели исправить

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

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

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

Реклама

Спонсором сегодняшнего выпуска является компания Arenda-it.ru. Минимизируйте затраты на IT с облачным сервисом https://arenda-it.ru/1s-oblako. 1С Облако предоставляет доступ к 1С через интернет. Выполняйте свою непосредственную работу, остальное оставьте сотрудникам компании: обновление программного обеспечения 1С, настройку и сопровождение, решение технических вопросов.

1. Excel пишет #ЗНАЧ!

#ЗНАЧ! в ячейке что это

Ошибка #ЗНАЧ! появляется когда в формуле присутствуют пробелы, символы либо текст, где должно стоять число. Разные типы данных. Например, формула =A15+G14, где ячейка A15 содержит «число», а ячейка G14 — «слово».

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

Как исправить #ЗНАЧ! в Excel

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

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

2. Ошибка Excel #ИМЯ?

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

Почему в ячейке стоит #ИМЯ?

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

Нет, Эксель не ищет ваше имя в этом случае. Ошибка #ИМЯ? появляется в ячейке, когда он не может прочитать определенные элементы формулы, которую вы пытаетесь запустить.

Например, если вы пытаетесь использовать формулу =A15+C18 и вместо «A» латинской напечатали «А» русскую, после ввода значения и нажатия Enter, Excel вернет #ИМЯ?.

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

Как исправить #ИМЯ? в Экселе?

Чтобы исправить ошибку #ИМЯ?, проверьте правильность написания формулы. Если написана правильно, а ваша электронная таблица все еще возвращает ошибку, Excel, вероятно, запутался из-за одной из ваших записей в этой формуле. Простой способ исправить это — попросить Эксель вставить формулу.

• Выделите ячейку, в которой вы хотите запустить формулу,
• Перейдите на вкладку «Формулы» в верхней части навигации.
• Выберите «Вставить функцию«. Если вы используете Microsoft Excel 2007, этот параметр будет находиться слева от панели навигации «Формулы».

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

3. Excel отображает ##### в ячейке

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

Как в Excel убрать решетки из ячейки?

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

Совет для профессионалов

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

4. #ДЕЛ/0! в Excel

В случае с #ДЕЛ/0!, вы просите Excel разделить формулу на ноль или пустую ячейку. Точно так же, как эта задача не будет работать вручную или на калькуляторе, она не будет работать и в Экселе.

Как устранить #ДЕЛ/0!

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

5. #ССЫЛКА! в ячейке

Иногда это может немного сложно понять, но Excel обычно отображает #ССЫЛКА! в тех случаях, когда формула ссылается на недопустимую ячейку. Вот краткое изложение того, откуда обычно возникает эта ошибка:

Что такое ошибка #ССЫЛКА! в Excel?

#ССЫЛКА! появляется, если вы используете формулу, которая ссылается на несуществующую ячейку. Если вы удалите из таблицы ячейку, столбец или строку, и создадите формулу, включающую имя ячейки, которая была удалена, Excel вернет ошибку #ССЫЛКА! в той ячейке, которая содержит эту формулу.

Теперь, что на самом деле означает эта ошибка? Вы могли случайно удалить или вставить данные поверх ячейки, используемой формулой. Например, ячейка B16 содержит формулу =A14/F16/F17.

Если удалить строку 17, как это часто случается у пользователей (не именно 17-ю строку, но… вы меня понимаете!) мы увидим эту ошибку.

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

Как исправить #ССЫЛКА! в Excel?

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

Совет для профессионалов

Если вы случайно удалили несколько ячеек, вы можете восстановить их нажатием кнопки «Отменить» на панели быстрого доступа. Сочетание клавиш CTRL + Z для Windows и Command + Z для Mac, также позволяет отменить последние внесенные изменения.

6. #ПУСТО! в Excel

Ошибка #ПУСТО! возникает, когда вы указываете пересечение двух областей, которые фактически не пересекаются, или когда используется неправильный оператор диапазона.

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

• Оператор диапазона (точка с запятой): определяет ссылки на диапазон ячеек.
• Оператор объединения (запятая): объединяет две ссылки в одну ссылку.
• Оператор пересечения (пробел): возвращает ссылку на пересечение двух диапазонов.

Как устранить ошибку #ПУСТО!?

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

7. #Н/Д

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

Как устранить эту ошибку

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

8. Ячейка Excel выдает ошибку #ЧИСЛО!

Если ваша формула содержит недопустимые числовые значения, появится ошибка #ЧИСЛО!. Это часто происходит, когда вы вводите числовое значение, которое отличается от других аргументов, используемых в формуле.

И еще, при вводе формулы, исключите такие значения, как $ 1000, в формате валюты. Вместо этого введите 1000, а затем отформатируйте ячейку с валютой и запятыми после вычисления формулы. Просто число, без знака $ (доллар).

Как устранить эту ошибку

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

Заключение

Напишите в х, а что вы думаете по этому поводу. Хотите узнать больше советов по Excel? Обязательно поделитесь этой статьей с друзьями.

Источник: https://winternete.ru/9-oshibok-excel-kotorye-vas-dostali.html

Как добавить сообщения об ошибках в Excel 2019

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

Мы хотим сделать ошибку, когда кто-то пытается ввести данные против проверки.

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

Добавить сообщения об ошибках в Excel

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

Сначала выберите ячейку ( E6 в нашем примере), которую вы хотите установить для ограничения. Перейдите на вкладку Данные и в разделе Инструменты данных нажмите Проверка данных.

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

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

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

Поскольку мы выбрали «между», нам нужно указать минимальный и максимальный диапазоны. Мы можем выбрать диапазон ячеек или ввести значения напрямую. В нашем случае введите «1» в Минимум и `10` в Максимум .

Теперь перейдите на вкладку Вводное сообщение .

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

Укажите Заголовок и Сообщение для отображения, когда ячейка выбрана. Заголовок отображается жирным шрифтом, и сообщение отображается как обычный текст под заголовком.

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

Рекомендуется: Советы Microsoft Excel по экономии времени и работе быстрее

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

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

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

Это самый простой способ создания сообщений об ошибках в Excel.

Источник: https://ru.joecomp.com/how-to-add-error-messages-in-microsoft-excel

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

Проверка данных является неплохим инструментом Excel для контроля за вносимыми на листах изменениями, не прибегая к помощи VBA. С её помощью можно ограничить ввод в ячейку, разрешив вводить только даты либо время, либо только числа. Да к тому же еще и задать диапазон дат либо предел чисел(к примеру от 1 до 10).

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

Разберем поподробней.

Сей чудесный инструмент находится: Данные(Data)—Проверка данных(Data Validation). Должно появиться окно:

Для начала надо выделить ячейку(или диапазон ячеек) для которой необходимо установить проверку. Затем идем в Данные(Data)—Проверка данных(Data Validation).

Первая вкладка — Параметры(Settings)

В поле Тип данных(Allow) — выбирается непосредственно тип данных, который должен быть записан в ячейке. Всего доступно 8 типов: Любое значение, Целое число, Действительное, Список, Дата, Время, Длина текста, Другой(Any Value, Whole number, Decimal, List, Date, Time, Text lenght, Custom).

Пункт Любое значение(Any Value) установлен по умолчанию, проверка не осуществляется. Подробно про пункт Списки(List) и как их создавать можно посмотреть и почитать в статье Выпадающие списки.

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

Остальные типы данных:

• Целое число(Whole number) — говорит само за себя. После установки такой проверки в ячейку можно будет внести только целое число. Т.е. число, не имеющее дробного остатка(9,1 например уже нельзя будет ввести). Так же нельзя будет ввести произвольный текст. Чаще всего подобная проверка применяется в полях для записи кол-ва штук товара и т.п. Т.е. там, где не может быть дробных значений.
• Действительное(Decimal) — тоже, что и в предыдущем пункте, но ввести можно любое число — хоть целое, хоть дробное, но невозможно будет ввести текст

Примечание: применив данные типы проверок, внести в ячейку можно только число. Занести текст Excel уже не разрешит. Однако стоит помнить, что даты и время Excel воспринимает и хранит именно как ЧИСЛОВЫЕ значения, поэтому ввод даты и времени тоже будет разрешен, если он не противоречит остальным условиям проверки(см.ниже). Но для проверки Целое число разрешен будет ввод только даты, т.к. время Excel хранит как дробное значение(кроме 24:00:00 и 00:00:00, которые Excel воспринимает как 1 и 0 соответственно). Более подробно о восприятии Excel-ем данных читайте в статье Как Excel воспринимает данные?.

• Дата(Date) — В ячейке может содержаться только значение даты. Дата может быть записана в любом формате, допустимом в текущей локализации Windows. Здесь тот же нюанс, что и проверкой на числа, только в обратную сторону — любая дата это число, поэтому по сути можно будет ввести любое целое число, которое может быть переведено Excel-м в дату.
• Время(Time) — можно записывать значения в формате времени для текущей локализации и так же как с датой в ячейку можно будет вводить не только время, но и любые числа: целые или дробные
• Длина текста(Text lenght) — позволяет ограничить количество вводимых в ячейку символов. Данные могут являться и числом и текстом и временем. Чем угодно, только должны соответствовать остальным условиям проверки. Часто применяется для контроля ввода данных по ИНН, КПП, БИК, артикулам и т.п.

Если посмотреть на картинку в начале статьи, то можно увидеть еще три поля, помимо тех, которые я перечислил: Значение(Data), Минимум(Minimum) и Максимум(Maximum). Поле Значение содержит несколько вариантов проверки: Между, Вне, Равно, Больше, Меньше, Больше или равно, Меньше или равно(between, not between, equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to) и доступно оно только при выборе Типа данных Целое число, Действительное, Дата, Время, Длина текста. Для других типов поле Значение недоступно. В зависимости от того, какой пункт выбран в поле Значение появляются дополнительные поля: Минимум и Максимум. При этом может быть только одно поле и название может быть иным. Например, для варианта Равно будет показано только одно поле и называться оно будет Значение(Value). Но здесь нет никаких подводных камней и разобраться с этими полями можно не напрягаясь и без поллитры 🙂
По сути все пункты довольно красноречивы и пояснять подробно каждый, думаю, смысла нет. Например, Между(between) — указывается интервал дат или чисел, в который должно входить условие(например целое число от 1 до 12: Минимум = 1, Максимум = 12). Если пользователь попытается ввести число за пределами указанного интервала(скажем число 0, -3 или 14), то Excel выдаст сообщение об ошибке(см.ниже). Вариант Больше или равно(greater than or equal to) позволит вносить только положительные значения больше нуля, если указать в поле Минимум значение 1.
Чуть большее внимание следует уделить варианту проверки Вне(not between). При установленном значении Вне, в ячейку можно будет внести только данные, которые не входят в диапазон, указанный в полях Минимум и Максимум. Т.е. при тех же условиях от 1 до 12 ввести можно будет и 0 и -3 и 14, но нельзя будет ввести 1 или 10.
При этом в полях для ввода значений допускается указать ссылку на ячейку. Например, в ячейке A1 записана начальная дата приема заявок, в B1 — конечная дата. И надо установить в ячейках A2:A50 проверку на интервал дат, указанных как раз в A1 и B1. Для этого выделяем ячейки A2:A50 и создаем в них проверку данных: Тип данных: Дата, Значение: Между, Начальная дата: =A1, Конечная дата: =B1. Теперь можно регулировать интервал без изменения самой проверки данных — просто изменяя значения ячеек A1 и B1.

• Другой(Custom) — на мой взгляд самый интересный и самый мощный тип проверки. Здесь нам предоставляется возможность более широко контролировать ввод данных. Для заполнения есть только одно поле — Формула(Formula). В него необходимо записать формулу и при каждом вводе значений в ячейку, Excel проверит введенное выражение на ИСТИНУ. Если быть точнее то Excel сначала вычислит формулу в этом поле ориентируясь на введенное в ячейку с проверкой данных значение, а потом проверит — возвращает ли формула значение ИСТИНА(TRUE). Если результатом будет ИСТИНА(TRUE), то введенное значение будет сохранено в ячейке, а если ЛОЖЬ(FALSE), то будет выдано окно с сообщением об ошибке:
  
  Приведу простой пример. В ячейку А1 введем число 1. Выделяем ячейку В1 — назначаем проверку данных-Другой. В поле Формула вписываем: =B1=A1. Теперь в ячейку В1 можно вписать только значение, которое полностью идентично значению в ячейке А1.
  Важно: если проверка данных в дальнейшем будет скопирована в другие ячейки, то ссылки будут смещены так, будто это простая формула. Поэтому при создании формул в проверке данных необходимо учитывать возможность смещения и при необходимости закреплять ссылки(знак доллара — $).

Вкладка Сообщение для ввода (Input Message)

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

• Выводить сообщение об ошибке(Show error alert after invalid data is entered) — в большинстве случаев необходимо установить галочку. Если галочка снята, то проверка вводимых в ячейку данных не будет осуществляться.
• Вид(Style)
  • Останов, Сообщение(Stop, Information) — можно ввести только разрешенные проверкой значения. Различается только вид сообщения(пример сообщения Останов приведен на рисунке выше).
  • Предупреждение(Warning) — в ячейку можно ввести любое значение, но при вводе значения, противоречащего проверке, появиться предупреждающее сообщение с подтверждением ввода данных.
• Заголовок(Title) — текст, который будет показан в заголовке сообщения об ошибке. Если не указан, то в заголовке будет написано Microsoft Excel.
• Сообщение(Error message) — непосредственно текст самого сообщения об ошибке. Если не указан, то будет показан текст примерно следующего содержания:
  Это значение не соответствует ограничениям по проверке данных, установленным для этой ячейки
  (This value doesn't match the data validation restrictions defined for this cell)

Но так же необходимо помнить, что какое бы условие на проверку Вы не поставили — значение в ячейке можно удалить, нажав кнопку Del. Либо скопировав ячейку из другой книги или листа и вставив на место проверки данных — проверка исчезнет, т.к. вся ячейка будет заменена скопированной. И от этого никак не уйти — такой проверку сделали разработчики…

Как скопировать проверку данных на другие ячейки
Все очень просто — копируем ячейку с нужной проверкой данных -выделяем ячейки для создания в них такого же условия -Правая кнопка мыши —Специальная вставка(Paste Special) -в окне выбираем Условия на значения(Validation) —Ок:


Как удалить проверку данных из ячеек
Выделяем необходимые ячейки -вкладка Данные(Data)—Проверка данных(Data Validation). В поле Тип данных(Allow) устанавливаем Любое значение(Any Value) —Ок.

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

После того, как ячейкам была назначена проверка данных переходим на вкладку Данные(Data)—Проверка данных(Data Validation) -раскрываем меню и выбираем Обвести неверные данные(Circle Invalid Data).

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

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

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

Если не все значения надо исправлять, а обводку тем не менее надо удалить, то после всех нужных правок просто переходим на вкладку Данные(Data) —Проверка данных(Data Validation) -раскрываем меню и выбираем Удалить обводку неверных данных(Clear Validation Circles)

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

Источник: https://www.excel-vba.ru/chto-umeet-excel/proverka-dannyx/

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

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

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

У нас имеется лист номенклатуры товаров магазина:



1. Выделите столбец B и выберите инструмент «Данные»-«Проверка данных».



2. Заполняем значениями поля на вкладке «Параметры» как показано на рисунке:



3. На вкладке «Сообщение для ввода» создаем особое примечание, в котором при помещения курсора на ячейку будет отображаться условия для ее заполнения. И жмем ОК.



Теперь проверим. В ячейку B2 введите натуральное число, а в ячейку B3 отрицательное. Как видно в ячейке B3 действие оператора набора – заблокировано. Отображается сообщение об ошибке: «Введенное значение неверно».

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

Чтобы удалить проверку данных в Excel нужно: выделить соответствующий диапазон ячеек, выбрать инструмент и нажать на кнопку «Очистить все» (указано на втором рисунке).



Данным способом проверяются данные только в процессе ввода. Если данные уже введенные они будут не проверенные. Например, в столбце B нельзя ввести текст после установки условий заполнения в нем ячеек. Но заголовок в ячейке B1 «Цена» остался без предупреждения об ошибке.

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

Чтобы проверить соответствуют ли все введенные данные, определенным условиям в столбце и нет ли там ошибок, следует использовать другой инструмент: «Данные»-«Проверка данных»-«Обвести неверные данные».



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

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

Источник: https://exceltable.com/vozmojnosti-excel/proverka-vvoda-dannyh

Устранение неполадок, связанных с сбоем и не отвечает на запросы, в Excel

• 22.11.2019
• Время чтения: 6 мин
• Применяется к: Microsoft Office Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019
• При открытии или использовании Microsoft Excel приложение Excel перестает отвечать на запросы, зависает, зависает или перестает работать. Кроме того, может появиться одно из следующих сообщений об ошибке:
• Приложение Excel не отвечает.
• Приложение Excel перестало работать.

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

Разрешение

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

Способ 1: запуск Excel в безопасном режиме

Безопасный режим позволяет безопасно использовать Excel без запуска определенных программ. Чтобы открыть Excel в безопасном режиме, нажмите и удерживайте клавишу CTRL во время запуска программы или с помощью параметра/Safe (то есть, файл Excel.

exe/Safe) при запуске программы из командной строки. При запуске Excel в безопасном режиме обходится функция и параметры, такие как альтернативное расположение автозагрузки, измененные панели инструментов, Папка XLStart и надстройки Excel.

(тем не менее, надстройки COM исключены.)

1. Если проблему удалось устранить после запуска Excel в безопасном режиме, ознакомьтесь со следующими статьями базы знаний Майкрософт:
2. 291288 описание параметров запуска Excel
3. 280504 устранение неполадок при запуске Excel 2013
4. Если проблема не устранена после запуска Excel в безопасном режиме, перейдите к способу 2.
• Надстройки Excel, сохраненные в виде файлов XLA, xlam или XLL.
• Надстройки COM. Обычно необходимо установить такие надстройки, включив в них DLL и разделы реестра. Надстройки COM, созданные для одной версии Excel, часто несовместимы с более новой версией и могут привести к зависанию системы. Они не отключаются в безопасном режиме.

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

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

1. Выберите * * File * * > * * Options * * > надстройки.
2. В нижней части диалогового окна измените раскрывающееся меню Управление на надстройки COM и нажмите кнопку Перейти.
3. Снимите флажок для всех включенных надстроек COM. Нажмите кнопку ОК.
   Если отключение надстроек COM позволяет устранить проблему, попробуйте включить их по одному, чтобы проверить надстройку, которая не связана с надстройкой.

Если эти действия не устраняют проблему, переходите к способу 3.

Способ 3: изучение сведений о файле Excel и его содержимого

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

• Формулы, ссылающиеся на целые столбцы.
• Формулы массива, ссылающиеся на нечетное число элементов в аргументах.
• Сотни или, возможно, тысячи, скрытые и заменяющие объекты высоты и ширины 0.
• Слишком много стилей вызвано частым копированием и вставкой между книгами.
• Слишком много (а также недопустимых) имен.

Проанализировать и упростить файлы (если это возможно).

Если эти действия не устраняют проблему, переходите к способу 4.

Способ 4: Проверка того, создается ли ваш файл сторонним производителем

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

Если проблема не устранена после тестирования приложения стороннего производителя, перейдите к способу 5.

Способ 5: выполнение выборочного запуска, чтобы определить, конфликтуют ли программы, процессы или службы с Excel

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

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

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

Если проблема не устранена после чистой загрузки, перейдите к способу 6.

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

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

Поставщики антивирусного программного обеспечения для Windows

Как проверить, конфликтует ли антивирусное программное обеспечение с Excel

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

Важно!

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

Ответственность за использование этого обходного пути несет пользователь.

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

Если обновление антивирусного программного обеспечения или его исключение из интеграции с Excel не устраняет проблему, перейдите к способу 7. способ 7: Проверка или установка последних обновлений Windows и Office

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

Дополнительные сведения о последних обновлениях Office можно найти в статье Office Updates.

Если проблема не устранена после установки последних обновлений для Windows и Office, перейдите к разделу Дополнительные сведения об устранении неполадок в разделе Дополнительные сведения.

Расширенное устранение неполадок

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

Факторы окружающей среды

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

• Выполните основные действия по устранению неполадок.
• Протестируйте файлы в самой ясной среде.

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

Место хранения файла

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

• Папка «Мои документы» перенаправлена в расположение на сервере
• Файлы хранятся в автономном режиме
• Вы пытаетесь открыть файлы из SharePoint или из папки «папка»
• Вы используете удаленный рабочий стол или Citrix
• Используются сетевые устройства
• Работает в виртуализированной среде. Для получения дополнительных сведений о программном обеспечении Майкрософт, которое работает в виртуализированной среде, ознакомьтесь со следующей статьей базы знаний Майкрософт:
  Политика поддержки 897615 для программного обеспечения Майкрософт, работающего в программном обеспечении виртуализации оборудования сторонних производителей

Память

При добавлении форматирования текста и фигур файлы Excel могут увеличиваться достаточно большим объемом. Убедитесь, что в вашей системе достаточно оперативной памяти для запуска приложения. Требования к системе для наборов Microsoft Office приведены в следующей статье базы знаний Майкрософт:

Требования к системе для Office

Драйверы принтеров и видео

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

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

Тестирование файла с помощью различных принтеров, например драйвера для средства записи XPS-документов (Майкрософт) или видеодрайвера VGA, определяет, связана ли проблема с конкретным принтером или видеодрайвером.

Если в Excel по-прежнему возникают проблемы с зависанием или аварийным сбоем, обратитесь в службу поддержки Майкрософт за устранение неполадок.

Источник: https://docs.microsoft.com/ru-ru/office/troubleshoot/excel/crashing-issues-with-excel