Формула Ошибка в Excel и их решениях

Posted on: 2020年10月22日 2020年11月12日
Tags: excel-errors

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

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

Что такое ошибки формул Excel При применении формулы, которая приводит к определенным ошибкам Excel (#NA, #VALUE, #NAME и т. Д.), Называются ошибками формул Excel. Эти ошибки выявляются в Excel и печатаются на листах. Причинами этих ошибок могут быть недоступные значения, неправильный тип аргументов, деление на 0 и т. Д.

Их легко поймать и исправить.

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

Выявление ошибок формул Excel:

В Excel есть специальные функции для обнаружения и обработки ошибок определенного типа (например, функция ISNA). Но link: / excel-formula-and-function-iserror-function [ISERROR function] и link: / logic-formulas-excel-iferror-function [IFERROR function] — это две функции, которые могут отловить любой вид Excel Ошибка (кроме логической).

Ошибка Excel #NA Ошибка #NA возникает в Excel, когда значение не найдено. Это просто означает НЕ ДОСТУПНО. Ошибка #NA часто встречается с функцией ВПР в Excel.

На изображении выше мы получаем ошибку #NA, когда ищем «Divya» в столбце A. Это потому, что «Divya отсутствует в списке.

Решение ошибки #NA:

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

Во-вторых, вы можете сделать ссылку `link: / lookup-formulas-partial-match -with-vlookup-function [частичное совпадение с VLOOKUP] `или любая функция поиска. Если вы уверены, что какая-то часть текста должна совпадать, используйте это.

Если вы не уверены, что значение существует или нет, чем оно может использоваться для link: / lookup-formulas-using-a-vlookup-formula-to-check-if-a-value-exists [проверить, существует ли значение в списке]. На изображении выше мы можем сказать, что Дивьи нет в списке.

Если вы хотите поймать ошибку #NA и напечатать или сделать что-то еще вместо вывода ошибки #NA, вы можете использовать link: / logic-formulas-excel -isna-function [функция Excel ISNA] `. ISNA functi on возвращает TRUE, если функция возвращает ошибку #NA. Используя это, мы можем избежать ошибки #NA. ISNA прекрасно работает с функцией ВПР. Проверьте это `ссылка: / советы-как-использовать-если-исна-и-vlookup-function-in-excel [здесь].

Ошибка Excel #VALUE Ошибка #VALUE возникает, если предоставленный аргумент не поддерживает тип. Например, если вы попытаетесь добавить два текста с помощью арифметического оператора плюс (+), вы получите ошибку #VALUE. То же самое произойдет, если вы попытаетесь получить год в недопустимом формате даты с помощью функции ГОД.

Как исправить ошибку #VALUE?

Сначала подтвердите тип данных, о котором вы говорите. Если вашей функции требуется номер, убедитесь, что вы ссылаетесь на него. Если число сформировано как текст, используйте link: / excel-generals-excel-value-function [VALUE function], чтобы преобразовать их в число. Если функции требуется текст (например, функция DATEVALUE), и вы ссылаетесь на число или тип даты, преобразуйте их в текст.

Если вы ожидаете, что может быть ошибка #VALUE и хотите ее отловить, то вы можете использовать ISERR, link: / excel-formula-and-function-iserror-function [ISERROR]

или link: / logic-formulas-excel-iferror-function [ЕСЛИОШИБКА]

поймать и заняться чем-нибудь другим.

Excel #REF Error Ссылка в #REF означает ссылку. Эта ошибка возникает, когда формула ссылается на несуществующее местоположение. Это происходит, когда мы удаляем ячейки из диапазонов, к которым также относится формула.

На рисунке ниже формула суммы относится к A2 и B2. Когда я удаляю A2, формула превращается в ошибку #REF.

Решить ошибку Excel #REF:

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

Например, как только вы получите ошибку #REF, ваша формула будет выглядеть так.

=A2+#REF!

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

Нажмите CTRL + H, чтобы открыть функцию поиска и замены. В поле поиска напишите # ССЫЛКА. Оставьте поле замены пустым. Нажмите кнопку «Заменить все».

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

Ошибка Excel #NAME Ошибка #NAME возникает в Excel, когда не удается идентифицировать текст в формуле.

Например, если вы неправильно написали имя функции, excel покажет ошибку #NAME. Если формула ссылается на имя, которого нет на листе, будет отображаться ошибка # ИМЯ.

На изображении выше ячейка B2 имеет формулу = POWERS (A2,2). POWERS не является допустимой функцией в excel, поэтому возвращает ошибку #NAME.

В ячейке B3 мы имеем = СУММ (числа). СУММ является допустимой функцией excel, но «числа»

link: / excel-range-name-all-about-named-range-in-excel [named range] не существует на листе. Следовательно, excel возвращает #NAME? Ошибка.

Как избежать ошибки #NAME в excel?

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

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

Excel # DIV / 0! Ошибка Как следует из названия, эта ошибка возникает, когда формула приводит к делению на ноль. Эта ошибка также может возникать при удалении некоторого значения из ячейки, от которой зависит формула деления.

=IF(B2=0,A2,A2/B2)

Ошибка DIV / 0 будет только в том случае, если делитель равен 0 или пуст. Следовательно, мы проверяем делитель (B2), если он равен нулю, то выведите A2, иначе разделите A2 на B2. Это будет работать и для пустых ячеек.

Ошибка Excel #NUM Эта ошибка возникает, когда число не отображается на экране. Причина может заключаться в том, что число слишком маленькое или слишком большое для отображения.

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

На изображении выше в ячейке C2 мы пытаемся получить значение -16309. Значение настолько мало, что не может быть отображено.

В ячейке C3 мы пытаемся получить квадратный корень из значения -16. Поскольку не существует такого значения, как квадратный корень из отрицательного значения (кроме мнимых чисел), поэтому в Excel отображается # ЧИСЛО! Ошибка.

Как решить # ЧИСЛО! Ошибка?

Чтобы устранить ошибку #NUM, первое, что вы можете сделать, это проверить каждое значение, на которое вы ссылаетесь. Убедитесь, что это действительные числа, с которыми может работать ваша формула.

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

=SQRT(ABS(A3))

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

О решении ошибки #NUM у нас есть отдельная статья. Вы можете проверить это по ссылке: / tips-how-to-corre-a-num-error [здесь] `.

#NULL Ошибка в Excel Это редкий тип ошибки. Ошибка #NULL из-за неправильной ссылки на ячейку. Например, если вы хотите указать диапазон A2: A5 в функции SUM, но по ошибке набираете A2 A5. Это вызовет ошибку #NULL. Как вы можете видеть на изображении ниже, формула возвращает ошибку #NULL. Если вы замените пробел столбцом (:), ошибка #NULL исчезнет, и вы получите сумму A2: A5. Если вы замените пробел запятой (,), вы получите сумму A2 и A5.

Как решить ошибку #NULL?

Как мы знаем, ошибка #NULL вызвана опечаткой. Чтобы этого избежать, попробуйте выбирать диапазоны с помощью курсора, а не вводить его вручную.

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

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

Если у вас есть ошибка #NULL, проверьте ссылки. Скорее всего, вы пропустили столбец (:) или запятую (,) между двумя ссылками на ячейки. Замените их соответствующим символом, и все готово.

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

Фактически, эта ошибка возникает, когда мы пытаемся показать отрицательное значение времени (отрицательного времени не существует). Например, если мы попытаемся вычесть 1 из 12:21:00 PM, вернемся . В Excel первая дата — 01.01.1900 00:00. Если вы попытаетесь вычесть время, прошедшее до этого, excel покажет вам ошибку . Чем больше вы увеличите ширину, тем больше # вы получите. Я подробно изложил это в link: / excel-date-time-formulas-convert-date-and-time-from-gmt-greenwich-mean-time-to-cst-central-standard-time [this article].

Как избежать ошибки Excel?

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

Минимальное значение времени — 01.01.1900 00:00. У вас не может быть действительной даты до того, как это в Excel 1 будет равно 24 часам (1 дню) в Excel. Вычитая часы, минуты и секунды, преобразуйте их в эквивалентные значения. Например, чтобы вычесть 1 час из 12:21, вам нужно вычесть из него 1/24.

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

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

Я обсуждал link: / tips-how-to-trace-and-fix-formula-errors-in-excel [подробное описание трассировки ошибок в Excel].

Решение логических ошибок в формулах

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

Но только вы знаете, что там что-то не так. Например, получая процент от части от целого, вы должны разделить часть на общее (= (часть / общее) * 100). Он всегда должен быть равен или меньше 100%.

Когда вы получаете более 100%, вы знаете, что что-то не так.

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

Один из способов — оценить вашу формулу.

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

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

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

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

Статьи по теме:

`link: / tips-how-to-corre-a-num-error [Как исправить #NUM! Ошибка] `

link: / tips-how-to-create-custom-error-bars-in-microsoft-excel-2010 [Создание настраиваемых полос ошибок в Excel 2016]

link: / tips-value-error-and-how-to-fix-it-in-excel [Ошибка #VALUE и как ее исправить в Excel]

link: / tips-how-to-trace-and-fix-formula-errors-in-excel [Как отслеживать и исправлять ошибки формул в Excel]