Интернет. Железо. Программы. Обзоры. Операционные системы

Эксель ошибка знач при умножении. Ошибки в Excel: как их найти, понять и исправить

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

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

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

Например, на рисунке выше мы намеренно пропустили закрывающую скобку при вводе формулы. Если нажать клавишу Enter , Excel выдаст следующее предупреждение:

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

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

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



В данном случае увеличение ширины столбца уже не поможет.

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

Ошибка #ДЕЛ/0! возникает, когда в Excel происходит деление на ноль. Это может быть, как явное деление на ноль, так и деление на ячейку, которая содержит ноль или пуста.

Ошибка #Н/Д

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


Ошибка #ИМЯ?

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


В данном примере имя диапазон не определено.


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

Ошибка #ПУСТО! возникает, когда задано пересечение двух диапазонов, не имеющих общих точек.


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

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


Не забывайте, что Excel поддерживает числовые величины от -1Е-307 до 1Е+307.

  1. Еще одним случаем возникновения ошибки #ЧИСЛО! является употребление функции, которая при вычислении использует метод итераций и не может вычислить результат. Ярким примером таких функций в Excel являются СТАВКА и ВСД .

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


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

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

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

Ошибка #ЗНАЧ! (ошибка в значении)

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

Как и говорилось — попытка сложить число и текст ставит MS Excel в тупик

Ошибка #ССЫЛКА! (неправильная ссылка на ячейку)

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

Ошибка #ДЕЛ/0! (деление на ноль)

Со школьной скамьи мы помним простое правило: на ноль делить нельзя! Ошибка #ДЕЛ/0! — это предупреждение от MS Excel о том, что это базовое правило нарушено и вы все-таки пытаетесь разделить некое число на ноль. При этом сам «ноль» не обязателен — любая попытка разделить существующее число на «пустую» ячейку также вызовет эту ошибку.

Делить на ноль нельзя — пустая ячейка воспринимается MS Excel как тот же ноль

Ошибка #Н/Д (значение недоступно)

Ошибка #Н/Д возникает в том случае, если в функции пропущен какой-то аргумент, или одно из используемых в формуле значений становится недоступно. Увидел #Н/Д — первым делом ищи чего в твоих вычислениях не хватает.

Применяю функцию ВПР, знак разделения поставил, а вот указать к какой ячейке он относится — забыл

Ошибка #ИМЯ? (недопустимое имя)

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

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

Попытка суммировать несуществующий диапазон с названием Столбец

  • Текст внутри функции заключается в кавычки. Если этого не происходит (то есть вместо =»Вася» мы вводим =Вася), MS Excel приходит в полное недоумение.

Ещё одна простейшая ошибка — текст в функциях и формулах указывается в кавычках

  • В названии функции случайно допущена опечатка.

Ошибка #ПУСТО! (пустое множество)

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

Все бы хорошо, но забыл про второй знак «+»

Ошибка #ЧИСЛО! (неправильное число)

Ошибку #ЧИСЛО! ms Excel выдает в тех случаях, когда результат математических вычислений в формуле порождает какой-то совершенно нереальный результат. Результат в виде предельно большого или малого числа, попытка вычислить корень из отрицательного числа — все это приведет к возникновению ошибки #ЧИСЛО!

Вычислить корень из отрицательного числа? Вас бы не понял не только Excel

Знаки «решетки» в ячейке Excel (#######)

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

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

Достаточно увеличить ширину столбца и проблема исчезнет

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

Нажмите на значок, чтобы получить помощь в исправлении ошибки

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

«Показать этапы вычисления…» — программу не обманешь, точно выводит фрагмент формулы где допущена ошибка

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

Доброго времени суток друзья!

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

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

Ну вот, собственно какие бывает ошибки в формулах Excel :

  1. Ошибка #####. Это одна из самых распространенных и простых ошибок в формулах Excel . Означает она только одно, что ширина столбца не имеет достаточной ширины, что бы полноценно отобразить ваши данные. Лечение этой проблемы очень простое, курсор мышки наведите на границу столбца, и при зажатой левой кнопки увеличите ячейку до тех пор пока данные не начнут отображаться ну или двойным кликом на границе столбца позволит по наиболее широкой ячейке в столбце.
  2. Ошибка #ИМЯ? . Эта ошибка (#NAME?) возникает в формулах Excel, только тогда, когда редактор не может распознать текст в формуле (к примеру, ошибка в названии функции в связи с опечаткой =СУМ(А1:А4). Для исправления этой ошибки в формулах Excel , вам нужно внимательно прочитать ее и исправить ошибку (А1:А4).
  3. Ошибка #ЗНАЧ! . Эта ошибка (#VALUE!) может у вас возникнуть в случае, когда в формуле присутствует аргумент, тип которого не подходит для ваших вычислений. Например, в вашу математическую или формулу затесалось текстовое значение =А1+В1+С1, где С1 – это текст. Лечение проблемы просто, используйте формулу, которая игнорирует ячейки, в которых присутствует текст или просто уберите данное значение с ячейки С1.
  4. Ошибка #ДЕЛО/0 . Как видно с ошибки возникшей в формуле, вы просто умножили свой аргумент на число 0, а это нельзя делать исходя из математических правил. Для исправления этой ошибки, вы должны изменить число, что бы оно не равнялось 0 или изменить формулу, к примеру, логической , что позволит избежать возникновению ошибки. =ЕСЛИ(A2=0;””;A1/A2)
  5. Ошибка #ССЫЛКА! . Это одна из самых распространенных и запутанных ошибок в функциях Excel. Когда вы видите эту ошибку, это означает что формула, ссылается на ячейку, которой больше нет. Особенно это проблемно, когда вы работаете с большими объемами данных в и большим количеством . Когда вы редактируете свои таблицы, такие ошибки в формулах Excel вас не должны пугать, их очень легко исправить, нужно всего лишь и всё вернется на свои места, ну или при необходимости вручную переписать формулу, исключив из нее ошибочный аргумент.

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

До встречи в новых статьях!

"Почему так устроен мир, что у людей, которые умеют жить в свое удовольствие, никогда нет денег, а те, у кого деньги водятся, понятия не имеют, что значит «прожигать жизнь»?
Д.Б. Шоу


Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (39)
Баги и глюки Excel (3)

Как в ячейке с формулой вместо ошибки показать 0

Случаются ситуации, когда в рабочей книге на листах создано много формул, выполняющих различные задачи. При этом формулы созданы когда-то давно, возможно даже на вами. И формулы возвращают ошибки. Например #ДЕЛ/0! (#DIV/0!) . Эта ошибка возникает, если внутри формулы происходит деление на ноль: = A1 / B1 , где в B1 ноль или пусто. Но могут быть и другие ошибки(#Н/Д, #ЗНАЧ! и т.д.). Можно изменить формулу, добавив проверку на ошибку:

=IF(ISERR(A1 / B1),0, A1 / B1)
аргументы:
=ЕСЛИ(ЕОШ(1 аргумент);2 аргумент; 1 аргумент)
Эти формулы будут работать в любой версии Excel. Правда, функция ЕОШ не обработает ошибку #Н/Д (#N/A) . Чтобы так же обработать и #Н/Д необходимо использовать функцию ЕОШИБКА:
=ЕСЛИ(ЕОШИБКА(A1 / B1);0; A1 / B1)
=IF(ISERROR(A1 / B1),0, A1 / B1)
Однако далее по тексту я буду применять ЕОШ(т.к. она короче) и к тому же не всегда надо "не видеть" ошибки #Н/Д.
Но для версий Excel 2007 и выше можно применить чуть более оптимизированную функцию ЕСЛИОШИБКА (IFERROR) :
=ЕСЛИОШИБКА(A1 / B1 ;0)
=IFERROR(A1 / B1 ,0)
аргументы:
=ЕСЛИОШИБКА(1 аргумент; 2 аргумент)

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

Почему ЕСЛИОШИБКА лучше и я называю её более оптимизированной? Разберем первую формулу подробнее:
=ЕСЛИ(ЕОШ(A1 / B1);0; A1 / B1)
Если вычислить пошагово, то увидим, что сначала происходит вычисление выражения A1 / B1 (т.е. деление). И если его результат ошибка – то ЕОШ вернет ИСТИНА (TRUE) , которое будет передано в ЕСЛИ (IF) . И тогда функцией ЕСЛИ(IF) будет возвращено значение из второго аргумента 0.
Но если результат не является ошибочным и ЕОШ (ISERR) возвращает ЛОЖЬ (FALSE) – то функция заново будет вычислять уже вычисленное ранее выражение: A1 / B1
С приведенной формулой это особой роли не играет. Но если применяется формула вроде ВПР (VLOOKUP) с просмотром на несколько тысяч строк – то вычисление два раза может значительно увеличить время пересчета формул.
Функция же ЕСЛИОШИБКА (IFERROR) один раз вычисляет выражение, запоминает его результат и если он ошибочен возвращает записанное вторым аргументом. Если же ошибки нет, то возвращает запомненный результат вычисления выражения из первого аргумента. Т.е. вычисление по факту происходит один раз, что практически не будет влиять на скорость общего пересчета формул.
Поэтому если у вас Excel 2007 и выше и файл не будет использоваться в более ранних версиях – то имеет смысл использовать именно ЕСЛИОШИБКА (IFERROR) .

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

Итак, есть на листе такие формулы, ошибки которых надо обработать. Если подобных формул для исправления одна-две(да даже 10-15) – то проблем почти нет заменить вручную. Но если таких формул несколько десятков, а то и сотен – проблема приобретает почти вселенские масштабы:-). Однако процесс можно упростить через написание относительно простого кода Visual Basic for Application.
Для всех версий Excel:

Sub IfIsErrNull() Const sToReturnVal As String = "0" , vbInformation, "www.сайт" Exit Sub End If For Each rc In rr If rc.HasFormula Then s = rc.Formula s = Mid(s, 2) ss = "=" & "IF(ISERR(" & s & ")," & sToReturnVal & "," & s & ")" If Left(s, 9) <> "IF(ISERR(" Then If rc.HasArray Then rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number Then ss = rc.Address rc.Select Exit For End If End If End If Next rc If Err.Number Then MsgBox "Формулы обработаны"

Sub IfIsErrNull() Const sToReturnVal As String = "0" "если необходимо вместо нуля возвращать пусто "Const sToReturnVal As String = """""" Dim rr As Range, rc As Range Dim s As String, ss As String On Error Resume Next Set rr = Intersect(Selection, ActiveSheet.UsedRange) If rr Is Nothing Then MsgBox "Выделенный диапазон не содержит данных", vbInformation, "www..HasFormula Then s = rc.Formula s = Mid(s, 2) ss = "=" & "IF(ISERR(" & s & ")," & sToReturnVal & "," & s & ")" If Left(s, 9) <> "IF(ISERR(" Then If rc.HasArray Then rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number Then ss = rc.Address rc.Select Exit For End If End If End If Next rc If Err.Number Then MsgBox "Невозможно преобразовать формулу в ячейке: " & ss & vbNewLine & _ Err.Description, vbInformation, "www..сайт" End If End Sub

Для версий 2007 и выше

Sub IfErrorNull() Const sToReturnVal As String = "0" "если необходимо вместо нуля возвращать пусто "Const sToReturnVal As String = """""" Dim rr As Range, rc As Range Dim s As String , ss As String On Error Resume Next Set rr = Intersect(Selection, ActiveSheet.UsedRange) If rr Is Nothing Then MsgBox "Выделенный диапазон не содержит данных" , vbInformation, "www.сайт" Exit Sub End If For Each rc In rr If rc.HasFormula Then s = rc.Formula s = Mid(s, 2) ss = "=" & "IFERROR(" & s & "," & sToReturnVal & ")" If Left(s, 8) <> "IFERROR(" Then If rc.HasArray Then rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number Then ss = rc.Address rc.Select Exit For End If End If End If Next rc If Err.Number Then MsgBox "Невозможно преобразовать формулу в ячейке: " & ss & vbNewLine & _ Err.Description, vbInformation, "www.сайт" Else MsgBox "Формулы обработаны" , vbInformation, "www.сайт" End If End Sub

Sub IfErrorNull() Const sToReturnVal As String = "0" "если необходимо вместо нуля возвращать пусто "Const sToReturnVal As String = """""" Dim rr As Range, rc As Range Dim s As String, ss As String On Error Resume Next Set rr = Intersect(Selection, ActiveSheet.UsedRange) If rr Is Nothing Then MsgBox "Выделенный диапазон не содержит данных", vbInformation, "www..HasFormula Then s = rc.Formula s = Mid(s, 2) ss = "=" & "IFERROR(" & s & "," & sToReturnVal & ")" If Left(s, 8) <> "IFERROR(" Then If rc.HasArray Then rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number Then ss = rc.Address rc.Select Exit For End If End If End If Next rc If Err.Number Then MsgBox "Невозможно преобразовать формулу в ячейке: " & ss & vbNewLine & _ Err.Description, vbInformation, "www..сайт" End If End Sub

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

Копируете приведенный код, переходите в редактор VBA(Alt +F11 ), создаете стандартный модуль (Insert -Module ) и просто вставляете в него этот код. Переходите в нужную книгу Excel и выделяете все ячейки, формулы в которых необходимо преобразовать таким образом, чтобы в случае ошибки они возвращали ноль. Жмете Alt +F8 , выбираете код IfIsErrNull (или IfErrorNull , в зависимости от того, какой именно скопировали) и жмете Выполнить .
Ко всем формулам в выделенных ячейках будет добавлена функция обработки ошибки. Приведенные коды учитывают так же:
-если в формуле уже применена функция ЕСЛИОШИБКА или ЕСЛИ(ЕОШ, то такая формула не обрабатывается;
-код корректно обработает так же функции массива;
-выделять можно несмежные ячейки(через Ctrl).
В чем недостаток: сложные и длинные формулы массива могут вызвать ошибку кода, в связи с особенностью данных формул и их обработкой из VBA. В таком случае код напишет о невозможности продолжить работу и выделит проблемную ячейку. Поэтому настоятельно рекомендую производить замены на копиях файлов.
Если значение ошибки надо заменить на пусто, а не на ноль, то надо строку

"Const sToReturnVal As String = """"""

Удалить апостроф ( " )

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

И небольшое дополнение: старайтесь применять код вдумчиво. Не всегда возврат ошибки мешает. Например, при использовании ВПР иногда полезно видеть какие значения не были найдены.
Так же хочу отметить, что применять надо к реально работающим формулам. Потому как если формула возвращает #ИМЯ!(#NAME!), то это означает, что в формуле неверно записан какой-то аргумент и это ошибка записи формулы, а не ошибка результата вычисления. Такие формулы лучше проанализировать и найти ошибку, чтобы избежать логических ошибок расчетов на листе.

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

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}

Поделиться с друзьями:
Похожие публикации