Excel обработка ошибки знач - Исправление ошибок и поиск оптимальных решений проблем

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Еще…Меньше

ЕСЛИ — одна из самых универсальных и популярных функций в Excel и часто используется несколько раз в одной формуле, а также в сочетании с другими функциями. К сожалению, из-за сложности, с помощью которой можно встраить такие утверждения, довольно просто запустить #VALUE! ошибку «#ВЫЧИС!». Как правило, вы можете скрыть ошибку, добавив в формулу функции обработки ошибок, такие как ЕERROR, ЕERR или ЕСЛИERROR.

Проблема: аргумент ссылается на ошибочные значения.

Если в ячейке есть ссылка на значение ошибки, если отображается #VALUE! ошибку «#ВЫЧИС!».

Решение: используйте с функцией ЕСЛИ функции для обработки ошибок, такие как ЕОШИБКА, ЕОШ и ЕСЛИОШИБКА. В следующих разделах описывается, как использовать функции ЕСЛИ, ЕОШИБКА, ЕОШ и ЕСЛИОШИБКА в формуле, если аргумент ссылается на ошибочные значения.

Исправление ошибки #ЗНАЧ! в функции СЦЕПИТЬ
Исправление ошибки #ЗНАЧ! в функции СРЗНАЧ или СУММ

Примечания:

Если в Excel 2007, она гораздо предпочтительнее, чем ЕERROR или ЕERR, так как она не требует избыточной построении формулы. ЕERROR и ЕERR принудительно вычисляют формулу дважды, сначала чтобы увидеть, возвращает ли она ошибку, а затем снова возвращает ее результат. ЕСЛИERROR вычисляется только один раз.
Конструкция =ЕСЛИОШИБКА(Формула;0) гораздо лучше конструкции =ЕСЛИ(ЕОШИБКА(Формула;0;Формула)).

Проблема: неправильный синтаксис.

Если синтаксис функции построен неправильно, она может вернуть #VALUE! ошибку «#ВЫЧИС!».

Решение: проверьте правильность синтаксиса. Ниже приведен пример правильно составленной формулы, в которой функция ЕСЛИ вкладывается в другую функцию ЕСЛИ для расчета вычетов на основе уровня доходов.

=ЕСЛИ(E2<31500;E2*15%;ЕСЛИ(E2<72500;E2*25%;E2*28%))

Обычным языком это можно выразить так: ЕСЛИ значение в ячейке A5 меньше чем 31 500, значение умножается на 15 %. Но ЕСЛИ это не так, проверьте, меньше ли это значение, чем 72 500. ЕСЛИ это так, значение умножается на 25 %; в противном случае — на 28 %.

Чтобы использовать функцию ЕСЛИОШИБКА с уже имеющейся формулой, просто вложите готовую формулу в функцию ЕСЛИОШИБКА:

=ЕСЛИОШИБКА(ЕСЛИ(E2<31500;E2*15%;ЕСЛИ(E2<72500;E2*25%;E2*28%));0)

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

Примечание: Значения в вычислениях разделяются точкой с запятой. Если разделить два значения запятой, функция ЕСЛИ будет рассматривать их как одно дробное значение. После процентных множителей ставится символ %. Он сообщает Excel, что значение должно обрабатываться как процентное. В противном случае такие значения пришлось бы вводить как дробные множители, например «E2*0,25».

У вас есть вопрос об определенной функции?

Задать вопрос на форуме сообщества, посвященном Excel

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Исправление ошибки #ЗНАЧ! #BUSY!

Функция ЕСЛИ

Функция ЕСЛИОШИБКА

Функции Е

Функция ЕСЛИМН (Microsoft 365 или Excel 2016 или более поздней)

Функция ЕСЛИ — вложенные формулы и типовые ошибки

Видео: вложенные функции ЕСЛИ

Полные сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Обнаружение ошибок в формулах

Все функции Excel (по алфавиту)

Функции Excel (по категориям)

Нужна дополнительная помощь?

Источник

Ошибка #ЗНАЧ! в Excel означает: «Формула вводится неправильно. Или что-то не так с ячейками, на которые указывают ссылки». Эта ошибка возникает в самых разных случаях, и найти ее точную причину может быть сложно. Сведения на этой странице включают распространенные проблемы и решения ошибки. Возможно, понадобится попробовать одно или несколько решений, чтобы устранить конкретную ошибку.

Исправление ошибок определенных функций

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

Проблемы с вычитанием

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

Вычтите одну ссылку на ячейку из другой

Введите два значения в двух отдельных ячейках. В третьей ячейке вычтите одну ссылку на ячейку из другой. В этом примере ячейка D2 содержит плановую сумму, а ячейка E2 — фактическую. F2 содержит формулу =D2-E2.

Или используйте функцию СУММ с положительными и отрицательными числами

Введите положительное значение в одной ячейке и отрицательное — в другой. В третьей ячейке используйте функцию СУММ, чтобы сложить две ячейки. В этом примере ячейка D6 содержит плановую сумму, а ячейка E6 — фактическую как негативное число. F6 содержит формулу =СУММ(D6;E6).

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

Для начала выполните быструю проверку. В новой книге введите 2 в ячейке A1. Введите 4 в ячейке B1. Затем введите формулу =B1-A1 в ячейке C1. Если возникнет ошибка #ЗНАЧ! перейдите к следующему шагу. Если сообщение об ошибке не появилось, попробуйте другие решения на этой странице.
В Windows откройте панель управления «Региональные стандарты».
- Windows 10. Встроке поиска введите регион и выберите панель управления Региональные стандарты.
- Windows 8. На начальном экране введите регион, щелкните Параметры и выберите пункт Региональные стандарты.
- Windows 7. Нажмите кнопку Пуск, введите регион и выберите пункт Язык и региональные стандарты.
На вкладке Форматы нажмите кнопку Дополнительные параметры.
Найдите пункт Разделитель элементов списка. Если в поле разделителя элементов списка указан знак «минус», замените его на что-то другое. Например, разделителем нередко выступает запятая. Также часто используется точка с запятой. Однако для вашего конкретного региона может подходить другой разделитель элементов списка.
Нажмите кнопку ОК.
Откройте книгу. Если ячейка содержит ошибку #VALUE!, дважды щелкните ее для редактирования.
Если там, где для вычитания должны быть знаки «минус», стоят запятые, замените их на знаки «минус».
Нажмите клавишу ВВОД.
Повторите эти действия для других ячеек, в которых возникает ошибка.

Вычтите одну ссылку на ячейку из другой

Введите две даты в двух отдельных ячейках. В третьей ячейке вычтите одну ссылку на ячейку из другой. В этом примере ячейка D10 содержит дату начала, а ячейка E10 — дату окончания. F10 содержит формулу =E10-D10.

Или используйте функцию РАЗНДАТ

Введите две даты в двух отдельных ячейках. В третьей ячейке используйте функцию РАЗНДАТ, чтобы найти разницу дат. Дополнительные сведения о функции РАЗНДАТ см. в статье Вычисление разницы двух дат.

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

Проверка наличия начальных пробелов

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

Если в ячейке обнаружена эта проблема, перейдите к следующему шагу. Если вы не видите один или несколько пробелов, перейдите к следующему разделу и проверьте параметры даты на компьютере.
Выделите столбец, содержащий дату, щелкнув его заголовок.
Выберите Данные > Текст по столбцам.
Дважды нажмите кнопку Далее.
На шаге 3 из 3 в мастере в разделе Формат данных столбца установите переключатель дата.
Выберите формат даты и нажмите кнопку Готово.
Повторите эти действия для других столбцов, чтобы убедиться, что они не содержат пробелы перед датами.

Проверка параметров даты на компьютере

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

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

Существует два решения этой проблемы: Вы можете изменить систему дат, которая используется на компьютере, чтобы она соответствовала системе дат, которая нужна в Excel. Или в Excel можно создать новый столбец и использовать функцию ДАТА, чтобы создать настоящую дату на основе даты в текстовом формате. Вот как это сделать, если система дат вашего компьютера — дд.мм.гггг, а в ячейке A1 записан текст 12/31/2017.

Создайте такую формулу: =ДАТА(ПРАВСИМВ(A1;4);ЛЕВСИМВ(A1;2);ПСТР(A1;4;2))
Результат будет 31.12.2017.
Чтобы использовать формат дд.мм.гг, нажмите клавиши CTRL+1 (или + 1 на Mac).
Выберите другой языковой стандарт, в котором используется формат дд.мм.гг, например Немецкий (Германия). После применения формата результат будет 31.12.2017, причем это будет настоящая дата, а не ее текстовая запись.

Примечание: Формула выше написана с использованием функций ДАТА, ПРАВСИМВ, ПСТР и ЛЕВСИМВ. Обратите внимание, что формула записана с учетом того, что в текстовой дате используется два символа для дней, два символа для месяцев и четыре символа для года. Возможно, вам понадобится откорректировать формулу под свою запись даты.

Проблемы с пробелами и текстом

Часто ошибка #ЗНАЧ! возникает, потому что формула ссылается на другие ячейки, содержащие пробелы или (что еще сложнее) скрытые пробелы. Из-за этих пробелов ячейка может выглядеть пустой, хотя на самом деле таковой не является.

1. Выберите ячейки, на которые указывают ссылки

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

2. Найдите и замените

На вкладке Главная щелкните Найти и выделить > Заменить.

3. Удалите пробелы

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

4. Замените одно или все вхождения

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

5. Включите фильтр

Иногда из-за скрытых символов (а не просто пробелов) ячейка может выглядеть пустой, хотя на самом деле таковой не является. Например, это может происходить из-за одинарных кавычек в ячейке. Чтобы убрать эти символы из столбца, включите фильтр, последовательно выбрав Главная > Сортировка и фильтр > Фильтр.

6. Установите фильтр

Щелкните стрелку фильтра и снимите флажок Выделить все. Затем установите флажок Пустые.

7. Установите все флажки без названия

Установите все флажки, напротив которых ничего не указано, как на этом рисунке.

8. Выделите пустые ячейки и удалите их

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

9. Очистите фильтр

Щелкните стрелку фильтра и выберите команду Удалить фильтр из… для отображения всех ячеек.

10. Результат

Если причиной появления ошибки #ЗНАЧ! были пробелы, вместо ошибки отобразится результат формулы, как показано в нашем примере. Если нет — повторите эти действия для других ячеек, на которые ссылается формула. Или попробуйте другие решения на этой странице.

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

Ошибку #ЗНАЧ! могут вызвать текст и специальные знаки в ячейке. Но иногда сложно понять, в каких именно ячейках они присутствуют. Решение: используйте функцию ЕТЕКСТ для проверки ячеек. Обратите внимание, что функция ЕТЕКСТ не устраняет ошибку, она просто находит ячейки, которые могут ее вызывать.

Пример с ошибкой #ЗНАЧ!

Вот пример формулы с ошибкой #ЗНАЧ! . Ошибка, скорее всего, возникает из-за ячейки E2. Здесь есть специальный знак, который выглядит как небольшой прямоугольник «00». Или, как показано на следующем рисунке, можно использовать функцию ЕТЕКСТ в отдельном столбце для поиска текста.

Этот же пример с функцией ЕТЕКСТ

Здесь в столбец F добавлена функция ЕТЕКСТ. Все ячейки в порядке, кроме одной со значением ИСТИНА. Это значит, что ячейка E2 содержит текст. Чтобы решить эту проблему, можно просто удалить содержимое ячейки и еще раз ввести число 1865,00. Вы также можете использовать функцию ПЕЧСИМВ, чтобы убрать символы, или функцию ЗАМЕНИТЬ, чтобы заменить специальные знаки на другие значения.

Использовав функцию ПЕЧСИМВ или ЗАМЕНИТЬ, вы можете скопировать результат в буфер обмена, а затем выбрать Главная > Вставить > Специальная вставка > Значения. Кроме того, может потребоваться преобразовать числа из текстового формата в числовой.

Формулам с математическими операторами (такими как + и *) не всегда удается вычислить ячейки, содержащие текст или пробелы. В таком случае попробуйте использовать вместо них функцию. Функции часто пропускают текстовые значения и определяют все значения как числовые, избегая ошибки #ЗНАЧ! . Например, вместо =A2+B2+C2 введите =СУММ(A2:C2). Или вместо =A2*B2 введите =ПРОИЗВЕД(A2,B2).

Другие решения

Выберите ошибку

Сначала выделите ячейку с ошибкой #ЗНАЧ! .

Щелкните «Формулы» > «Вычислить формулу»

Щелкните Формулы > Вычислить формулу > Вычислить. Excel обработает каждую часть формулы по отдельности. В данном случае формула =E2+E3+E4+E5 выдает ошибку из-за скрытого пробела в ячейке E2. Пробела не видно, если смотреть на ячейку E2. Но его можно увидеть здесь. Он отображается как » «.

Иногда вам может быть нужно вместо ошибки #ЗНАЧ! выводить что-то свое, например собственный текст, ноль или пустую ячейку. В этом случае можно добавить в формулу функцию ЕСЛИОШИБКА. Она проверяет, есть ли ошибка, и если да, заменяет ее другим значением по вашему выбору. Если ошибки нет, вычисляется исходная формула. Функция ЕСЛИОШИБКА работает только в Excel 2007 и более поздних версиях. В предыдущих версиях можно использовать функцию ЕСЛИ(ЕОШИБКА()).

Предупреждение: Функция ЕСЛИОШИБКА скроет все ошибки, а не только ошибку #ЗНАЧ! . Ошибки не рекомендуется скрывать, так как они часто указывают на то, что какое-то значение нужно исправить, а не просто скрыть. Используйте эту функцию, только если вы абсолютно уверены, что формула работает правильно.

Ячейка с ошибкой #ЗНАЧ!

Вот пример формулы, в которой ошибка #ЗНАЧ! вызвана скрытым пробелом в ячейке E2.

Ошибка, скрытая функцией ЕСЛИОШИБКА

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

К сожалению, как вы видите, функция ЕСЛИОШИБКА не устраняет ошибку, а только скрывает ее. Так что используйте ее, если точно уверены, что ошибку лучше скрыть, чем исправить.

В какой-то момент подключения к данным могут стать недоступны. Чтобы исправить ошибку, восстановите подключение или, если это возможно, импортируйте данные. Если у вас нет доступа к подключению, попросите автора книги создать для вас новый файл. В идеале в нем будут только значения (без подключений). Для этого можно скопировать все ячейки и вставить только значения, щелкнув Главная > Вставить > Специальная вставка > Значения. При этом будут удалены все формулы и подключения, а ошибки #ЗНАЧ! исчезнут.

См. также

Полные сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Источник

Функция ЕСЛИОШИБКА – примеры формул

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

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

Итак, вот о чем мы поговорим:

Что означает функция Excel ЕСЛИОШИБКА
Если ошибка, то пусто
Если ошибка, то показать сообщение
5 фактов, которые нужно знать о функции ЕСЛИОШИБКА в Excel
ЕСЛИОШИБКА с функцией ВПР
Вложенные функции ЕСЛИОШИБКА для выполнения последовательных ВПР
ЕСЛИОШИБКА в формулах массива
ЕСЛИОШИБКА или ЕСЛИ + ЕОШИБКА?
ЕСЛИОШИБКА против ЕСНД
Рекомендации по использованию ЕСЛИОШИБКА в Excel

Что означает функция Excel ЕСЛИОШИБКА

Функция ЕСЛИОШИБКА (IFERROR по-английски) предназначена для обнаружения и устранения ошибок в формулах и вычислениях. Это значит, что функция ЕСЛИОШИБКА должна выполнить определенные действия, если видит какую-либо ошибку. Более конкретно, она проверяет формулу и, если вычисление дает ошибку, то она возвращает какое-то другое значение, которое вы ей укажете. Если же всё хорошо, то просто возвращает результат формулы.

Синтаксис функции Excel ЕСЛИОШИБКА следующий:

ЕСЛИОШИБКА(значение; значение_если_ошибка)

Где:

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

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

Рассмотрим простой пример:

Чтобы этого не произошло, используйте формулу ЕСЛИОШИБКА, чтобы перехватывать и обрабатывать их нужным вам образом.

Если ошибка, то пусто

Укажите пустую строку (“”) в аргументе значение_если_ошибка, чтобы вернуть пустую ячейку, если обнаружена ошибка:

=ЕСЛИОШИБКА(A4/B4; «»)

Вернемся к нашему примеру и используем ЕСЛИОШИБКА:

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

Если ошибка, то показать сообщение

Вы также можете отобразить собственное сообщение вместо стандартного обозначения ошибок Excel:

=ЕСЛИОШИБКА(A4/B4; «Ошибка в вычислениях»)

Перед вами – третий вариант нашей небольшой таблицы.

5 фактов, которые нужно знать о функции ЕСЛИОШИБКА в Excel

ЕСЛИОШИБКА в Excel обрабатывает все типы ошибок, включая #ДЕЛ/0!, #Н/Д, #ИМЯ?, #NULL!, #ЧИСЛО!, #ССЫЛКА! и #ЗНАЧ!.
В зависимости от содержимого аргумента значение_если_ошибка функция может заменить ошибки вашим текстовым сообщением, числом, датой или логическим значением, результатом другой формулы или пустой строкой (пустой ячейкой).
Если аргумент значение является пустой ячейкой, он обрабатывается как пустая строка (»’), но не как ошибка.
ЕСЛИОШИБКА появилась в Excel 2007 и доступна во всех последующих версиях Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021 и Excel 365.
Чтобы перехватывать ошибки в Excel 2003 и более ранних версиях, используйте функцию ЕОШИБКА в сочетании с функцией ЕСЛИ, например как показано ниже:

=ЕСЛИ(ЕОШИБКА(A4/B4);»Ошибка в вычислениях»;A4/B4)

Далее вы увидите, как можно использовать ЕСЛИОШИБКА в Excel в сочетании с другими функциями для выполнения более сложных задач.

ЕСЛИОШИБКА с функцией ВПР

Часто встречающаяся задача в Excel – поиск нужного значения в таблице в соответствии с определёнными критериями. И не всегда этот поиск бывает успешным. Одним из наиболее распространенных применений функции ЕСЛИОШИБКА является сообщение пользователям, что искомое значение не найдено в базе данных. Для этого вы заключаете формулу ВПР в функцию ЕСЛИОШИБКА примерно следующим образом:

ЕСЛИОШИБКА(ВПР( … );»Не найдено»)

Если искомое значение отсутствует в таблице, которую вы просматриваете, обычная формула ВПР вернет ошибку #Н/Д:

Для лучшего понимания таблицы и улучшения ее внешнего вида, заключите функцию ВПР в ЕСЛИОШИБКА и покажите более понятное для пользователя сообщение:

=ЕСЛИОШИБКА(ВПР(D3; $A$3:$B$5; 2;ЛОЖЬ); «Не найдено»)

На скриншоте ниже показан пример ЕСЛИОШИБКА вместе с ВПР в Excel:

Если вы хотите перехватывать только #Н/Д, но не все подряд ошибки, используйте функцию ЕНД вместо ЕСЛИОШИБКА. Она просто возвращает ИСТИНА или ЛОЖЬ в зависимости от появления ошибки #Н/Д. Поэтому нам здесь еще понадобится функция ЕСЛИ, чтобы обработать эти логические значения:

=ЕСЛИ(ЕНД(ВПР(D3; $A$3:$B$5; 2;ЛОЖЬ)); «Не найдено»;ВПР(D3; $A$3:$B$5; 2;ЛОЖЬ))

Дополнительные примеры формул Excel ЕСЛИОШИБКА ВПР можно также найти в нашей статье Как убрать сообщение #Н/Д в ВПР?

Вложенные функции ЕСЛИОШИБКА для выполнения последовательных ВПР

В ситуациях, когда вам нужно выполнить несколько операций ВПР в зависимости от того, была ли предыдущая ВПР успешной или неудачной, вы можете вложить две или более функции ЕСЛИОШИБКА одну в другую.

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

=ЕСЛИОШИБКА(ВПР(B9;A3:B6;2;0);ЕСЛИОШИБКА(ВПР(B9;D3:E6;2;0);ЕСЛИОШИБКА(ВПР(B9;G3:H6;2;0);»Не найден»)))

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

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

ЕСЛИОШИБКА в формулах массива

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

Допустим, у вас есть Сумма в столбце B и Цена в столбце C, и вы хотите вычислить Количество. Это можно сделать с помощью следующей формулы массива, которая делит каждую ячейку в диапазоне B2:B4 на соответствующую ячейку в диапазоне C2:C4, а затем суммирует результаты:

=СУММ(($B$2:$B$4/$C$2:$C$4))

Формула работает нормально, пока в диапазоне делителей нет нулей или пустых ячеек. Если есть хотя бы одно значение 0 или пустая строка, то возвращается ошибка: #ДЕЛ/0! Из-за одной некорректной позиции мы не можем получить итоговый результат.

Чтобы исправить эту ситуацию, просто вложите деление внутрь формулы ЕСЛИОШИБКА:

=СУММ(ЕСЛИОШИБКА($B$2:$B$4/$C$2:$C$4;0))

Что делает эта формула? Делит значение в столбце B на значение в столбце C в каждой строке (3500/100, 2000/50 и 0/0) и возвращает массив результатов {35; 40; #ДЕЛ/0!}. Функция ЕСЛИОШИБКА перехватывает все ошибки #ДЕЛ/0! и заменяет их нулями. Затем функция СУММ суммирует значения в итоговом массиве {35; 40; 0} и выводит окончательный результат (35+40=75).

Примечание. Помните, что ввод формулы массива должен быть завершен нажатием комбинации Ctrl + Shift + Enter (если у вас не Office365 или Excel2021 – они понимают формулы массива без дополнительных телодвижений).

ЕСЛИОШИБКА или ЕСЛИ + ЕОШИБКА?

Теперь, когда вы знаете, как использовать функцию ЕСЛИОШИБКА в Excel, вы можете удивиться, почему некоторые люди все еще склоняются к использованию комбинации ЕСЛИ + ЕОШИБКА. Есть ли у этого старого метода преимущества по сравнению с ЕСЛИОШИБКА?

В старые недобрые времена Excel 2003 и более ранних версий, когда ЕСЛИОШИБКА не существовало, совместное использование ЕСЛИ и ЕОШИБКА было единственным возможным способом перехвата ошибок. Это просто немного более сложный способ достижения того же результата.

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

В Excel 2007 — Excel 2016:

ЕСЛИОШИБКА(ВПР( … ); «Не найдено»)

Во всех версиях Excel:

ЕСЛИ(ЕОШИБКА(ВПР(…)); «Не найдено»; ВПР(…))

Обратите внимание, что в формуле ЕСЛИ ЕОШИБКА ВПР вам нужно дважды выполнить ВПР. Чтобы лучше понять, расшифруем: если ВПР приводит к ошибке, вернуть «Не найдено», в противном случае вывести результат ВПР.

А вот простой пример формулы Excel ЕСЛИ ЕОШИБКА ВПР:

=ЕСЛИ(ЕОШИБКА(ВПР(D2; A2:B5;2;ЛОЖЬ)); «Не найдено»; ВПР(D2; A2:B5;2;ЛОЖЬ ))

ЕСЛИОШИБКА против ЕСНД

Представленная в Excel 2013, ЕСНД (IFNA в английской версии) — это еще одна функция для проверки формулы на наличие ошибок. Его синтаксис похож на синтаксис ЕСЛИОШИБКА:

ЕСНД(значение; значение_если_НД)

Чем ЕСНД отличается от ЕСЛИОШИБКА? Функция ЕСНД перехватывает только ошибки #Н/Д, тогда как ЕСЛИОШИБКА обрабатывает все типы ошибок.

В каких ситуациях вы можете использовать ЕСНД? Когда нецелесообразно скрывать все ошибки. Например, при работе с важными данными вы можете захотеть получать предупреждения о возможных ошибках в вашем наборе данных (случайном делении на ноль и т.п.), а стандартные сообщения об ошибках Excel с символом «#» могут быть яркими визуальными индикаторами проблем.

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

Предположим, вы хотите получить Количество из таблицы поиска в таблицу с результатами, как показано на рисунке ниже. Проще всего было бы использовать ЕСЛИОШИБКА с ВПР. Таблица приобрела бы красивый вид, но при этом за надписью «Не найдено» были бы скрыты не только ошибки поиска, но и все другие ошибки. И мы не заметили бы, что в исходной таблице поиска у нас есть ошибка деления на ноль, так как не заполнена цена персиков. Поэтому более разумно использовать ЕСНД, чтобы с ее помощью обработать только ошибки поиска:

=ЕСНД(ВПР(F3; $A$3:$D$6; 4;ЛОЖЬ); «Не найдено»)

Или подойдет комбинация ЕСЛИ ЕНД для старых версий Excel:

=ЕСЛИ(ЕНД(ВПР(F3; $A$3:$D$6; 4;ЛОЖЬ));»Не найдено»; ВПР(F3; $A$3:$D$6; 4;ЛОЖЬ))

Как видите, формула ЕСНД с ВПР возвращает «Не найдено» только для товара, которого нет в таблице поиска (Сливы). Для персиков она показывает #ДЕЛ/0! что указывает на то, что наша таблица поиска содержит ошибку деления на ноль.

Обзор ошибок, возникающих в формулах Excel

Смотрите также за месяц. выложить его из «Результативность» открыть файл новых позиции, (которых»» Создать правило… );Скрыть значения ошибки можноРешение:.В этом разделе приводятся#ЗНАЧ! которая при вычислении не имеют точек.Например, на рисунке ниже на ноль. Это

Несоответствие открывающих и закрывающих скобок

исправить формулу самостоятельно.Ошибки в Excel возникаютCapMorgan дома после 19:00 АСАО ПАБ, то до этого никогда, вы тем самымв появившемся окне выберите используя пользовательский формат, УсловноеУдалите аргументЕсли функции не удается

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

: Господа, удалил все (мск). все формулы сразу не было) автоматически как-то влияте на Форматировать только ячейки, форматирование или значениенач_позиция найти искомый текст распространенных случаях возникновения распространенных ошибок, встречающихся и не может#ПУСТО! массив C4:C11 больше,

Ячейка заполнена знаками решетки

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

1. же рассчитываются корректно. попадали в первую тип данных в которые содержат; Пустой текст («»)., если он не
  в указанной строке, ошибки #ЗНАЧ! в

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

ячейках и типыв выпадающем списке выберитеПользовательский формат вводим через

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

нужен, или присвойте она выводит ошибку функциях НАЙТИ, НАЙТИБ, возникает, когда значение примером таких функцийТакже данная ошибка возникнет, A4:A8 и B4:B8. деление на ячейку, в коем случае вместо ожидаемого результата,JayBhagavan

Ошибка #Н/Д

ошибка возникает при можно уйти от также из 6-ой данных в ячейках Ошибки (см. рисунок диалоговое окно Формат ему правильное значение. #ЗНАЧ!. ПОИСК и ПОИСКБ.

одного из аргументов в Excel являются если случайно опуститьНажав комбинацию клавиш которая содержит ноль нельзя. Например, на которые начинались со: Предлагаю заменить СЧЁТЗ() разных форматах файлов
данной проблемы? Всегда колонки всех листов, становятся ниже); ячеек (см. файлНапример, рассмотрим следующую функцию:
Например, рассмотрим следующую функцию:Функции НАЙТИ и ПОИСК формулы или функцииСТАВКА один из операторовCtrl+Shift+Enter или пуста. следующем рисунке Excel знака для одной ячейки xls и xlsx ли необходимо открывать в колонку 2Текстовый

выберите пользовательский формат (белый примера).=НАЙТИ(«и»;»Функции и формулы»;25)

Ошибка #ИМЯ?

=НАЙТИ(«перчатки»;»Перчатки (детские)»;1) очень похожи. Они содержит недопустимые значения.и в формуле. К, получим следующий результат:

Ошибка предложил нам неправильное#
на, например: =—(ДЛСТР(‘[АСАО — проверьте файл АСАО? Можно листа «Итого». И

(хотя у вас цвет шрифта);

для вызова окна ФорматОна ищет букву «и»
Она выведет ошибку #ЗНАЧ!, работают одинаково: находят

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

Самые распространенные случаиВСД примеру, формулуОшибка#Н/Д решение.

. Это говорит о ПАБ цех №53(2).xlsx]Январь’!$E$5)>0)РаботаетJayBhagavan ли как-то этого при этом существующая установлен для ячеекТеперь ошибочные значения отображаются ячеек нажмите в строке «Функции так как в символ или текстовую возникновения ошибки
.=А1*А2*А3#ИМЯ?возникает, когда дляБывают случаи, когда ячейка том, что формула с закрытым файлом,: CapMorgan, почитайте . избежать, скажем, изменив

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

формула добавления количество, тип данных белым шрифтом иCTRL+1 и формулы» (

строке нет слова строку в другой#ЗНАЧ!Ошибказаписать каквозникает, когда в
формулы или функции в Excel полностью возвращает ошибку. Чтобы на который ссылается.ЗЫ На почту формулы или выполнив добавляла данное количество.Числовой не видны на

;просматриваемая_строка «перчатки», но есть текстовой строке. Различие

:#ССЫЛКА!=А1*А2 A3 формуле присутствует имя, недоступно какое-то значение. заполнена знаками решетки. избавиться от ошибки,JayBhagavan не надо. Спасибо. некие преобразования? Буду В примере, новые), а вычисления над стандартном белом фоне.выберите (все форматы).

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

) начиная с 25-го слово «Перчатки». Помните, между ними вФормула пытается применить стандартныевозникает в Excel,. которое Excel не

Приведем несколько случаев Это означает один Вы должны исправить: Для столбцов BW,
CapMorgan Вам очень признателен позиции во втором текстом производить нельзя.
Если фон поменять,в поле Тип введите символа ( что функция НАЙТИ том, что функция математические операторы к
когда формула ссылаетсяОшибка понимает. возникновения ошибки из двух вариантов: ее причину, а BX предлагаю использовать: Проверил, оба файла за помощь. Файлы

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

листе — откуда, Поэтому замените то ошибки станут формат [Черный]Основнойнач_позиция учитывает регистр, поэтому НАЙТИ учитывает регистр, тексту. на ячейку, которая#ЧИСЛО!Например, используется текст не#Н/ДСтолбец недостаточно широк для

они могут быть такую конструкцию: =—(‘[АСАО формата .xlsx.
прикрепить не удается и в листе»» снова видны. Даженажмите ОК), но возвращает ошибку значение аргумента а функция ПОИСК —
В качестве аргументов функции не существует иливозникает, когда проблема заключенный в двойные: отображения всего содержимого самыми разными. ПАБ цех №53(2).xlsx]Январь’!$I$2=BW$2)The_Prist

из-за их веса Итого — куда,на если просто выделитьцвет шрифта ячейки установите #ЗНАЧ!, так какискомый_текст нет. Поэтому если

используются данные несоответствующего

office-guru.ru

Исправление ошибки #ЗНАЧ! в функциях НАЙТИ, НАЙТИБ, ПОИСК и ПОИСКБ

удалена. в формуле связана кавычки:Функция поиска не находит ячейки. Для решенияСамым распространенным примером возникновения

Некоторые важные сведения о функциях НАЙТИ и ПОИСК

CapMorgan: Вы явно используете (архивом вестят 340 отмеченные желтым цветом.0 диапазон, то ошибки таким же как в строке всегодолжно иметь точное при поиске нужно типа. К примеру,Например, на рисунке ниже со значением.Функция ссылается на имя соответствия. К примеру,
проблемы достаточно увеличить ошибок в формулах: Бегло поупражнялся, «=—(‘[АСАО формулы, который не Кб). Могу отправить,CapMorgan. будут слегка видны. и ее фон 22 знака. совпадение в строке, сопоставлять символы без
номер столбца в представлена формула, котораяНапример, задано отрицательное значение диапазона, которое не функция ширину столбца, чтобы Excel является несоответствие ПАБ цех №53(2).xlsx]Январь’!$I$2=BW$2)» работают с закрытыми

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

скажите куда? Спасибо.: Уважаемые сограждане, доброгоКод =ЕСЛИ(ЕОШИБКА(C4+E4-F4);0;C4+E4-F4) вЗамечательным свойством значения Пустой (обычно белый)

Совет:

 указанной в аргументе

учета регистра, используйте функции суммирует значения двух там, где должно существует или написаноВПР все данные отобразились… открывающих и закрывающих сработала. Я правильно книгами. СУММЕСЛИ, СЧЁТЕСЛИ,JayBhagavan времени суток! 2003-м, текст является, то

На рисунке ниже пользовательский Чтобы определить общеепросматриваемый_текст функцию ПОИСК.ВПР

 ячеек.

 быть положительное. Яркий с опечаткой:при точном поиске

Проблема: значение аргумента нач_позиция равно нулю (0)

…или изменить числовой формат скобок. Когда пользователь предполагаю, что если ДВССЫЛ и т.п.: На файлообменник, например,Обращаюсь к Вамв 2007 Код что оно не формат применен к количество символов в

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

Проблема: длина значения нач_позиция превышает длину значения просматриваемый_текст

вернет ошибку

 ячейки.

вводит формулу, Excel мне по образу Если знать сами мэйл.ру. за помощью. Уверен, =ЕСЛИОШИБКА(C4+E4-F4);0)Огромное Вам спасибо, отображается в ячейке ячейкам текстовой строке, используйтеОднако функция ПОИСК вернет строку, содержащую указанное

 1. формула вернет ошибку корень из отрицательного диапазон не определено.#Н/Д

В ячейке содержится формула, автоматически проверяет ее и подобию сделать

У вас есть вопрос об определенной функции?

формулы и цельalexthegreat

Помогите нам улучшить Excel

у Вас есть оба варианта работают. (например, введите формулуА6B6 функцию ДЛСТР. в этом случае количество символов, используйте

support.office.com

Скрытие в MS EXCEL ошибки в ячейке

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

которая возвращает некорректное синтаксис и не для категории «Специалисты» их применения можно

Пользовательский формат

: Можно открыть оба чёткое понимание решенияВ данной таблице =»»). Записав, например,

. Как видно, наРешение: значение 1, так функцию ПСТР вместе
единственное значение, а
.К тому же, ошибка
двоеточия:
найдено. значение даты или даст закончить ввод, и «ТОП-менеджеры», то

заменить другими(например СУММЕСЛИ файла, (если Вам моей трудности. Дело я не учел в ячейке значения, которые неИсправьте начальную позицию как она не

с функцией НАЙТИ. вместо этого емуЕще пример. Формула в#ЧИСЛО!В имени функции допущенаФормула прямо или косвенно времени. Думаю, Вы

Условное форматирование

пока в ней будет считать корректно? и СЧЁТЕСЛИ можно

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

обращается к ячейке, знаете, что Excel присутствует синтаксическая ошибка.Спасибо, заменить на СУММПРОИЗВ). будут и связь 2 файла и это когда поступаютформулу =ЕСЛИОШИБКА(1/0;»») получим не повлияло.

Значение Пустой текст («»)

Задать вопрос на форуме=ПОИСК(«перчатки»;»Перчатки (детские)»;1) функций ПСТР и На рисунке ниже на ячейку B1, слишком большое илиОшибка в которой отображается не поддерживает датыНапример, на рисунке вышеJayBhagavanCapMorgan

excel2.ru

Как убрать #ЗНАЧ!

 не нужна) в в одном из
новые позиции, а пустую ячейкуПрименение вышеуказанного формата не

 сообщества, посвященном ExcelРешение: НАЙТИ в сочетании в качестве искомого т.е. на ячейку, слишком малое значение.#ПУСТО! значение до 1900 года. мы намеренно пропустили: CapMorgan, чего предполагать: Все верно, а зависимом файле выделить них (АСАО ПАБ заводить дважды вА14 влияет на вычисления.У вас есть предложенияИсправьте синтаксис необходимым друг с другом значения функции расположенную выше на Например, формулавозникает, когда задано

#Н/Д Поэтому, если результатом
закрывающую скобку при то — пробуйте. именно «СЧЁТЕСЛИ». Эту
весь лист - цех №53 Результативность) таблицу это будет. В Строке формул, по улучшению следующей образом. и примеры приведеныВПР 1 строку.=1000^1000
пересечение двух диапазонов,. формулы оказывается такая вводе формулы. Если Сделайте резервную копию формул применил, так Копировать — Спец. есть ссылки/связи на неудобно, так какvasilyev по-прежнему, будет отображаться версии Excel? ЕслиАргумент в разделе справкииспользуется диапазон A6:A8.Если мы скопируем даннуювернет как раз не имеющих общихПри работе с массивами дата, то Excel нажать клавишу исходного файла и как необходимо скалькулировать вставка — Вставить

CyberForum.ru

Исправление ошибки «ЗНАЧ#» при использовании связей файлов Excel

 другой файл (АСАО нужно еще понимать,: Доброе время суток)
формула, хотя в да, ознакомьтесь снач_позиция по функции НАЙТИ.Вот и все! Мы формулу в любую эту ошибку. точек. в Excel, когда возвращает подобный результат.Enter смело на нём сколько было проведено только значения. ПАБ цех №53). где новая позиция,Подскажите пожалуйста как ячейке ничего не темами на порталеявляется необязательным. ЕслиСинтаксис этих функций одинаков: разобрали типичные ситуации ячейку 1-й строкиНе забывайте, что ExcelНапример, аргументы массива имеютВ данном случае увеличение, Excel выдаст следующее экспериментируйте. аудитов работниками тойCapMorgan Проблема в том, а где старая. убрать в строках будет отображаться. пользовательских предложений для его опустить, используетсяискомый_текст; просматриваемый_текст; [нач_позиция] возникновения ошибок в (например, ячейку D1),

 поддерживает числовые величины=А1:А10 C5:E5 меньший размер, чем

 ширины столбца уже предупреждение:CapMorgan или иной категории: Спасибо. С рабочего что когда яКак сделать так #ЗНАЧ!Используя Условное форматирование, также Excel. значение по умолчанию,

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

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

 равное 1. Однако можно выразить так:
ошибки, гораздо проще#ССЫЛКА!

 1Е+307. использующая оператор пересечения, этом случае в

Ошибка предлагает свой вариант в Файл, все «Руководитель подразделения», то могу. Может быть формулы в столбцах листов (от 1: же результата. ячейке значения ошибки: если этот аргументчто нужно найти; где

 исправить ее. Успехов, т.к. в нейЕще одним случаем возникновения которая должна вернуть незадействованных ячейках итогового#ДЕЛ/0! исправления ошибки. Вы заработало! Огромное, большущее нужно в «Результативности» уместно, если я «BW», «BX», «BY», листа до 31vasilyevвыделите интересующий диапазон;

 #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, указан и его это нужно найти; Вам в изучении

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

 вернуть «1», а Вам направлю его возвращают «ЗНАЧ#». Если листа) с 5-ой, видимо вставляя пустую

в меню выберите Главная/ #ЧИСЛО!, #ИМЯ? Сделаем значение равно 0, с какой позиции Excel! на несуществующую ячейку.#ЧИСЛО! на пересечении двух#Н/Д Excel происходит деление
с Excel, либо

 помощь!!!!!! потом посчитать итого эл.почтой? Или могу при открытом файле колонки при добавлении строку Стили/ Условное форматирование/

 это несколькими способами. возникнет ошибка #ЗНАЧ!. следует начатьАвтор: Антон АндроновОшибкаявляется употребление функции,

planetaexcel.ru

диапазонов. Поскольку диапазоны

Источник

ЗНАЧ в Excel — как исправить? В зависимости от причины требуется исправление проблемы с вычитанием, пробелами и текстом. Может потребоваться определение источника ошибка, замена «ЗНАЧ» другим значением, проверка подключение данных или использование помощи представителей форума сообщества. Ниже рассмотрим, что значите появление такой ошибки, чем она может быть вызвана, и каким способом ее можно устранить.

Что значит и когда возникает

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

вычитание;
пробелы или текст;
появление ошибки ввода;
неправильные значения и т. д.

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

Как исправить

Теперь разберемся подробнее, как исправить в Эксель ЗНАЧ в формуле, когда не удается сделать вычисление в обычном режиме. Рассмотрим разные проблемы и методы решения.

Вычитание

Наиболее распространенная ситуация, когда ошибка «ЗНАЧ» в Excel возникает из-за трудностей с вычитанием. Чтобы исправить сбой, могут помочь рассмотренные ниже шаги.

Для начала проверьте правильность ввода формулы вычитания. Сделайте следующие шаги, чтобы исправить ситуацию в Excel:

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

Как вариант, используйте опцию «СУММ» с цифрами выше / ниже нуля. Для этого введите параметр выше нуля в одной, а ниже — в другой ячейке. В третьей применяется опция «СУММ» для складывания. Пример: =СУММ (D5;E5).

Еще один способ, как убрать ЗНАЧ в Excel — ввести правильную функцию вычитания. Алгоритм действий такой:

Сделайте проверку. Для этого в новом файле введите «3» в А1 и 6 в В1. Если при формуле =В1-А1 появляется ЗНАЧ, переходите к следующему шагу.
Войди на Виндовс в раздел «Региональные стандарты». В «десятке» в поисковой строке введите «регион», а после выберите панель управления «Региональные стандарты».
На вкладке «Форматы» кликните на «Дополнительные параметры» и найдите «Разделитель элементов списка». При наличии здесь знака «минус» поменяйте его на другой элемент и жмите ОК.
Войдите в книгу Excel. Если в ячейке имеется надпись #VALUE!, два раза жмите на нее для внесения правок. В случае, когда вместо «минуса» стоит запятые, замените их на «минус». Далее жмите «Ввод» и повторите эти шаги для других ячеек, для которых возникла ошибка.

Распространенная причина, почему в Экселе выходит ЗНАЧ — неправильные операции с датами. Для решения вопроса сделайте следующее:

Вычтите одну ссылку на ячейку из второй.
Используйте опцию РАЗНДАТ для 3-ей ячейке, что позволяет отыскать разницу в датах.

Следующий способ, как исправить ЗНАЧ в Excel — убедиться, что программа распознает текст, как дату для проведения проверки. Чтобы исправить сбой, сделайте следующее:

Два раза жмите на дату, используемую в формуле.
Разместите курсор на старте и убедитесь, можно ли выбрать один или более пробелов.
Выделите столбец с датой, нажав на заголовок.
Кликните на «Данные», а потом — «Текст по столбцам».

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

Пробелы и текст

Еще одно объяснение, что значит ЗНАЧ в Excel — проблемы с текстами / пробелом. В таком случае воспользуйтесь одним из рассмотренных ниже шагов.

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

Сделайте следующее в Excel:

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

Замените одно / все вхождения.
Если ЗНАЧ не удалось исправить, включите фильтр в разделе «Главная», а далее «Сортировка и фильтр» — «Фильтр».
Жмите стрелку внизу и удалите флажок с поля «Выделить все», а после установите отметку «Пустые».
Поставьте флажки в местах без названия.
Выделите незаполненные ячейки и удалите их.
Кликните на стрелку вниз и выберите «Удалить фильтр из…».

Если ошибка ЗНАЧ появлялась из-за пробелов, рассмотренным методом удается исправить ситуацию. В ином случае может потребоваться применение другого варианта.

Причиной, почему вы видите ЗНАЧ в Эксель, может быть текст / специальные знаки. Обратите внимание на ячейки и удалите лишнее. Может потребоваться применение функции «Заменить» или «ПЕЧСИМВ» или «ЗАМЕНИТЬ».

Еще один способ, как исправить, если в Excel вместо цифр ЗНАЧ — использовать формулы вместо операций. При наличии «+» или «*» рассчитать ячейки с текстом / пробелом не получится. Для решения проблемы может потребоваться применение специальных функций. К примеру, вместо сложной формулы =А2+В2+С2 укажите =СУММ (А2:С2).

Дополнительные шаги

Рассматривая пути, как исправить ошибку ЗНАЧ в Excel, можно использовать и другие пути решения проблемы:

Определите источник ошибки. Для начала выделите секцию, где имеется параметр ЗНАЧ, а после этого жмите на «Формулы», «Вычислить формулы», а после — «Вычислить». В этом случае Эксель обрабатывает каждую часть формулы отдельно. При этом можно увидеть, где проблема, а после исправить ее.

Замените ЗНАЧ другим параметром Excel, чтобы исправить проблему. Попробуйте добавить формулу ЕСЛИОШИБКА, которая проверят наличие сбоев и заменяет ЗНАЧ другим значением, к примеру, двумя дефисами.
Восстановите подключение. Попросите создателя файла Excel сделать новую книгу, чтобы в ней были только параметры без подключений. Для этого копируйте ячейки и вставьте только значения. Это происходит следующим образом — «Главная», а потом «Вставить» и «Специальная вставка» — «Значения».

Как вариант, можно зайти в сообщество Excel по ссылке go.microsoft.com/fwlink/?linkid=827514, где можно задать вопрос и исправить проблему с Excel.

Теперь вы знаете, почему в Экселе пишет ЗНАЧ и не считает формулу, как исправить ситуацию, и какие шаги необходимо осуществлять в первую очередь. Более подробные сведения можно найти на официальном сайте по ссылке support.microsoft.com/ru-ru/office/исправление-ошибки-знач-ошибка-15e1b616-fbf2-4147-9c0b-0a11a20e409e.

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

Отличного Вам дня!

Источник

Проблема: аргумент ссылается на ошибочные значения.

Проблема: неправильный синтаксис.

У вас есть вопрос об определенной функции?

Дополнительные сведения

См. также

Нужна дополнительная помощь?

Исправление ошибок определенных функций

Проблемы с вычитанием

Вычтите одну ссылку на ячейку из другой

Или используйте функцию СУММ с положительными и отрицательными числами

Вычтите одну ссылку на ячейку из другой

Или используйте функцию РАЗНДАТ

Проблемы с пробелами и текстом

1. Выберите ячейки, на которые указывают ссылки

2. Найдите и замените

3. Удалите пробелы

4. Замените одно или все вхождения

5. Включите фильтр

6. Установите фильтр

7. Установите все флажки без названия

8. Выделите пустые ячейки и удалите их

9. Очистите фильтр

10. Результат

Пример с ошибкой #ЗНАЧ!

Этот же пример с функцией ЕТЕКСТ

Другие решения

Выберите ошибку

Щелкните «Формулы» > «Вычислить формулу»

Ячейка с ошибкой #ЗНАЧ!

Ошибка, скрытая функцией ЕСЛИОШИБКА

См. также

Что означает функция Excel ЕСЛИОШИБКА

Если ошибка, то пусто

Если ошибка, то показать сообщение

5 фактов, которые нужно знать о функции ЕСЛИОШИБКА в Excel

ЕСЛИОШИБКА с функцией ВПР

Вложенные функции ЕСЛИОШИБКА для выполнения последовательных ВПР

ЕСЛИОШИБКА в формулах массива

ЕСЛИОШИБКА или ЕСЛИ + ЕОШИБКА?

ЕСЛИОШИБКА против ЕСНД

Рекомендации по использованию ЕСЛИОШИБКА в Excel

Обзор ошибок, возникающих в формулах Excel

Несоответствие открывающих и закрывающих скобок

Ячейка заполнена знаками решетки

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

Ошибка #Н/Д

Ошибка #ИМЯ?

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

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

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

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

Исправление ошибки #ЗНАЧ! в функциях НАЙТИ, НАЙТИБ, ПОИСК и ПОИСКБ

Некоторые важные сведения о функциях НАЙТИ и ПОИСК

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

Проблема: значение аргумента нач_позиция равно нулю (0)

Проблема: длина значения нач_позиция превышает длину значения просматриваемый_текст

У вас есть вопрос об определенной функции?

Помогите нам улучшить Excel

Скрытие в MS EXCEL ошибки в ячейке

Пользовательский формат

Условное форматирование

Значение Пустой текст («»)

Как убрать #ЗНАЧ!

Исправление ошибки «ЗНАЧ#» при использовании связей файлов Excel

Что значит и когда возникает

Как исправить

Вычитание

Пробелы и текст

Дополнительные шаги