Главная › Услуги › Коды ошибок эксель. Excel: "Ошибка при направлении команды приложению". Советы сисадмина: как исправить ошибку. Ввод и обработка данных в 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}}

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

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

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

Диагностика ошибок в формулах Excel

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

Сообщения об ошибках в Excel могут принадлежать к одному из 8 типов:

######

#ЗНАЧ!

#ДЕЛ/0!

#ИМЯ?

#ЧИСЛО!

#ПУСТО!

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

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

Ошибка #ДЕЛ/0 появляется, когда в формуле делается попытка деления на ноль.

Сообщение об ошибке типа #ИМЯ? появляется, когда Excel не может найти имя, используемое в формуле. Например, такая ситуация возникнет, если:

o при наборе имени произошла опечатка;

o текст ошибочно не был заключен в двойные кавычки;

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

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

Ввод и обработка данных в Excel

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

Рабочий лист в Excel 2007 состоит из 13384 столбцов и 1048576 строк.

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

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

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

Форматирование и защита рабочих листов

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

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

Форматирование в Excel включает в себя решение следующих вопросов:

изменение шрифта, размеров, начертания и цвета символов;

выравнивание и изменение ориентации текста и чисел в ячейках;

форматирование чисел, дат и времени;

форматирование строк и столбцов;

создание и использование пользовательских форматов;

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

защита ячеек, листов и рабочих книг;

использование стилей при форматировании;

применение автоформатов.

Работа с электронными таблицами

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

Рабочий лист электронной книги состоит из ячеек, каждая из которых имеет свой адрес: сочетание имени столбца и строки.Столбцы идентифицируются буквами латинского алфавита (А, B, C, D…), астроки

– арабскими цифрами (1,2,3…). Ячейка, в которой находится курсор, считается активной, то есть предназначенной для ввода данных. Например, адрес F10 говорит о том, что вводимая информация при активной ячейке F10 попадет именно в эту ячейку, а содержимое этой ячейки отразится в строке формул.

Многие команды Excel позволяют работать с блоками ячеек. Блок ячеек - это прямоугольник, задаваемый координатами противоположных углов, обычно, верхней левой и нижней правой ячеек. Имена ячеек в блоках разделяются двоеточием(:) . Например, блок А1:В4 включает в себя ячейки А1, А2, А3, А4, В1, В2, В3 и В4. Выделение блока ячеек осуществляется протаскиванием курсора мыши на нужный диапазон. При этом ячейка, начиная с которой выделяется блок, остается белого цвета, а остальные ячейки затемняются. Но блок ячеек не обязательно может включать смежные ячейки. Для выделения таких ячеек в блоке следует использовать клавишу .

Ячейкам и блокам для удобства работы можно давать имена.

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

При создании сложных формул (да и просто невнимательности) в 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 стал в большинстве случаев автоматически увеличивать ширину ячейки под число. Если же вы видите «решетки», проще всего избавиться от них увеличив ширину ячейки вручную.

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

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

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

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

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

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

Дата: 24 декабря 2015 Категория:

Ошибки в Экселе – непременный спутник всех, кто . Когда выражение в ячейке рассчитать невозможно, программа отображает в ячейке сообщение об ошибке. Оно начинается со знака «#», после которого записывается имя ошибки. Этого не нужно пугаться, если вы ориентируетесь в функциях Эксель и умеете следовать простейшей логике математических операций – легко найдёте и исправите ошибку.

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

Типы ошибок

Если ошибка всё же произошла, помочь в её исправлении поможет расшифровка:

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

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

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

Отслеживание ошибки через этапы вычисления

Циклические ссылки в Excel

Еще один тип ошибки – это циклическая ссылка. Она возникает, когда вы ссылаетесь на ячейку, значение которой зависит от той, в которой вы пишете формулу. Например, в клетке А1 записана формула =А2+1 , а в А2 написать =A1 , возникнет циклическая ссылка, которая будет пересчитываться бесконечно. В этом случае, программа предупреждает о появлении циклической ссылки, останавливает расчет «зацикленных формул». В левой части ячеек появляется двунаправленная стрелка. Придётся исправить возникшую ошибку и повторить расчет.

Иногда случается сложное «зацикливание», когда циклическая ссылка образуется с несколькими промежуточными формулами.

Чтобы отследить такие ошибки, выполните Формулы – Зависимости формул – Проверка наличия ошибок – Циклические ссылки . В выпадающем списке программа выводит адреса ячеек, создающих бесконечный цикл. Остаётся только исправить формулы в этих клетках.

Отслеживание циклических ссылок

В Excel можно попытаться рассчитать результат зацикленных формул. Для этого установите галочку Файл – Параметры – Формулы – Включить итеративные вычисления . В этом же блоке можно задать максимальное количество итераций (просчётов) для нахождения баланса и допустимую погрешность. В большинстве случаев этого делать не нужно, поэтому я рекомендую эту галку не устанавливать. Тем не менее, когда Вы знаете, что зацикленные формулы верны и их расчет приведет к устойчивому результату — почему бы это не сделать?

Вот и всё про типы ошибок в Эксель. В этой короткой статье вы получили достаточно информации, чтобы справиться с самыми распространенными ошибками в Excel путем анализа возвращаемого значения. А вот расширенный перечень ошибок читайте в ! Готов ответить на ваши вопросы – пишите в комментариях.

В следующей статье я расскажу . Стоит ли говорить, что функции Эксель — «наше всё»?
Думаю, нет. Потому, переходите и читайте, это будет первый шаг в мир сложных формул с правильными результатами!

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

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

Ошибка #####

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

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

Ошибка #Н/Д

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

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

Ошибка #ДЕЛ/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 - наше условие.

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

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

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

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