Я ввожу формулу, но вместо результата я вижу текст #Н/Д или #####. Что это значит?

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

Ошибка #####

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

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

Ошибка #Н/Д

Данная ошибка возникает, если функции или формула содержит ссылку на значение (или само значение), которого нет. Другими словами, в функции или формуле не заполнено обязательное поле, или есть ссылка на пустую ячейку в то время, как в ячейке обязательно должно присутствовать значение. Наиболее частые причины:

• В функции не указаны один или несколько обязательных аргументов.
• В функциях ВПР, ГПР, ПРОСМОТР или ПОИСКПОЗ указано неправильное значение аргумента искомое_значение.
• Функции ВПР, ГПР или ПОИСКПОЗ применяются для поиска в несортированной таблице.
• В исходной таблице вместо пропущенных данных было введено значение #Н/Д или НД().

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

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

• В формуле явно задано деление на ноль (например, =10/0).
• В формуле или функции в качестве делителя стоит ссылка на пустую ячейку или на ячейку, которая содержит ноль.

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

Эта ошибка возникает, когда в условиях функции указано два пересекающихся диапазона, но на самом деле они не пересекаются.

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

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

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

Данная ошибка возникает, если в формуле или функции указаны некорректные числовые значения. Например:

• В функции, которая использует числовой аргумент, в его качестве указан текст или ссылка на ячейку с нечисловым значением.
• Функция, которая выполняет итерации (вычисление, которое повторяется до тех пор, пока не будет выполнено заданное условие, например ВСД или СТАВКА) не может вычислить результат (например, заданное условие не наступило).
• Результатом вычисления стало число, слишком большое или слишком малое для использования в Excel. Напоминаем, что максимальное число в Excel не может превышать 10307 и быть меньше минус 10307.

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

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

Такая ошибка возникает при использовании неверного типа аргумента или операнда. Например, пользователь может ошибиться в следующем:

• В формулу вместо числа или логического значения (ИСТИНА или ЛОЖЬ) введен текст.
• После ввода или редактирования формулы массива нажимается клавиша ВВОД вместо комбинации CTRL+SHIFT+ВВОД.
• Ссылка, формула или функция указана как массив.
• В качестве условия функции, который требует единичное значение, указан диапазон.

Я понял, что означает ошибка. Но моя формула очень большая/работает с большим объемом данных. Как мне найти место, где допущена ошибка?

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

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

У меня ошибка #ИМЯ? и я не могу найти её причину.

• Проверьте правильность написания имени (названия) функции. Для этого можно вызвать Мастер функций (группа Библиотека функций вкладки Формулы), и выбрать нужную функцию из предлагаемого списка.
• В формулу был введен текст, не заключенный в двойные кавычки. Допустим, результатом формулы должен был быть текст с числом - например, Итого 200 р. Если при введении текста Итого в формулу этот текст не заключен в двойные кавычки, Excel интерпретирует такие данные как имя, что вызывает ошибку.

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

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

У меня огромная база данных клиентов - покупателей. Каждый день в базе данных добавляется столбец, в котором указывается сумма выручки от каждого покупателя. Если покупатель ничего не купил, в ячейке ставится #Н/Д. Как мне просуммировать столбец, если там есть такие данные?

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

Но если основную таблицу изменить невозможно (по разным причинам), эту проблему можно попробовать решить с помощью функции БДСУММ.

Допустим, исходная таблица имеет следующий вид:

Применив функцию БДСУММ, мы сможем выбрать для суммирования те ячейки, которые не содержат значения #Н/Д. Функция БДСУММ имеет следующий синтаксис:

БДСУММ(база_данных, поле, условия) , где

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

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

БДСУММ(C2:C18;1;E2:E3) где

• C2:C18 - наша таблица,
• E2:E3 - наше условие.

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

Наш результат указан под таблицей:

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

Этот способ также может использоваться для функций СЧЁТ, БСЧЁТА, ДМАКС, ДМИН, БДПРОИЗВЕД и им подобных.

Списки и диапазоны (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}}

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

Ошибки в формуле Excel отображаемые в ячейках

В данном уроке будут описаны значения ошибок формул, которые могут содержать ячейки. Зная значение каждого кода (например: #ЗНАЧ!, #ДЕЛ/0!, #ЧИСЛО!, #Н/Д!, #ИМЯ!, #ПУСТО!, #ССЫЛКА!) можно легко разобраться, как найти ошибку в формуле и устранить ее.

Как убрать #ДЕЛ/0 в Excel

Как видно при делении на ячейку с пустым значением программа воспринимает как деление на 0. В результате выдает значение: #ДЕЛ/0! В этом можно убедиться и с помощью подсказки.

В других арифметических вычислениях (умножение, суммирование, вычитание) пустая ячейка также является нулевым значением.



Результат ошибочного вычисления – #ЧИСЛО!

Неправильное число: #ЧИСЛО! – это ошибка невозможности выполнить вычисление в формуле.

Несколько практических примеров:

Ошибка: #ЧИСЛО! возникает, когда числовое значение слишком велико или же слишком маленькое. Так же данная ошибка может возникнуть при попытке получить корень с отрицательного числа. Например, =КОРЕНЬ(-25).

В ячейке А1 – слишком большое число (10^1000). Excel не может работать с такими большими числами.

В ячейке А2 – та же проблема с большими числами. Казалось бы, 1000 небольшое число, но при возвращении его факториала получается слишком большое числовое значение, с которым Excel не справиться.

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

Как убрать НД в Excel

Значение недоступно: #Н/Д! – значит, что значение является недоступным для формулы:

Записанная формула в B1: =ПОИСКПОЗ(„Максим”; A1:A4) ищет текстовое содержимое «Максим» в диапазоне ячеек A1:A4. Содержимое найдено во второй ячейке A2. Следовательно, функция возвращает результат 2. Вторая формула ищет текстовое содержимое «Андрей», то диапазон A1:A4 не содержит таких значений. Поэтому функция возвращает ошибку #Н/Д (нет данных).

Ошибка #ИМЯ! в Excel

Относиться к категории ошибки в написании функций. Недопустимое имя: #ИМЯ! – значит, что Excel не распознал текста написанного в формуле (название функции =СУМ() ему неизвестно, оно написано с ошибкой). Это результат ошибки синтаксиса при написании имени функции. Например:

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

Пустое множество: #ПУСТО! – это ошибки оператора пересечения множеств. В Excel существует такое понятие как пересечение множеств. Оно применяется для быстрого получения данных из больших таблиц по запросу точки пересечения вертикального и горизонтального диапазона ячеек. Если диапазоны не пересекаются, программа отображает ошибочное значение – #ПУСТО! Оператором пересечения множеств является одиночный пробел. Им разделяются вертикальные и горизонтальные диапазоны, заданные в аргументах функции.

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

Заданные аргументы в функции: =СУММ(B4:D4 B2:B3) – не образуют пересечение. Следовательно, функция дает значение с ошибкой – #ПУСТО!

#ССЫЛКА! – ошибка ссылок на ячейки Excel

В данном примере ошибка возникал при неправильном копировании формулы. У нас есть 3 диапазона ячеек: A1:A3, B1:B4, C1:C2.

Под первым диапазоном в ячейку A4 вводим суммирующую формулу: =СУММ(A1:A3). А дальше копируем эту же формулу под второй диапазон, в ячейку B5. Формула, как и прежде, суммирует только 3 ячейки B2:B4, минуя значение первой B1.

Когда та же формула была скопирована под третий диапазон, в ячейку C3 функция вернула ошибку #ССЫЛКА! Так как над ячейкой C3 может быть только 2 ячейки а не 3 (как того требовала исходная формула).

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

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

#ЗНАЧ! – ошибка в значении. Если мы пытаемся сложить число и слово в Excel в результате мы получим ошибку #ЗНАЧ! Интересен тот факт, что если бы мы попытались сложить две ячейки, в которых значение первой число, а второй – текст с помощью функции =СУММ(), то ошибки не возникнет, а текст примет значение 0 при вычислении. Например:

Решетки в ячейке 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 и их исправлении, стала вам полезной, и вы узнали для себя что-то новое и интересное.

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

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

Если 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 - Ошибка в формуле, из-за удаленного столбца А

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

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

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

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

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

Ошибка #Н/Д

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ошибка ####

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

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

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

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

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

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

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