Что означают ошибки в экселе. Excel: "Ошибка при направлении команды приложению". Советы сисадмина: как исправить ошибку

Как в ячейке с формулой вместо ошибки показать 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" "если необходимо вместо нуля возвращать пусто "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..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}}

Нечасто, конечно, но иногда при открытии офисных документов (форматов XLS и XLSX) на экране появляется сообщения о сбое Excel - «ошибка при направлении команды приложению». Почему же программа не может открывать «родные» файлы? В этом нужно разобраться. Надо сказать, что Word, Excel и Access в равной мере подвержены появлению таких ошибок. Так что, некоторые предлагаемые решения по исправлению ситуации подойдут и для других приложений.

О чем свидетельствует ошибка направления команды приложению Excel?

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

В любой программе, в том числе в Excel, свидетельствует о том, что программу невозможно закрыть, если:

• она находится в активном состоянии;
• сам файл по каким-то причинам не распознается (например, при использовании разных версий офисного пакета 2003, 2007, 2010, 2013, 2016) или же отличающихся между собой модификаций операционных систем.

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

Основные методы устранения проблемы в офисных программах

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

Однако если говорить именно о проблеме направления команды, самыми действенными способами устранения проблемы, если речь идет именно об Excel, принято считать следующие:

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

Формат Excel: отключение игнорирования DDE-запросов

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

Суть решения состоит в том, чтобы через файловое меню войти в параметры программы, выбрать общие настройки и перейти к общей конфигурации. Здесь нужно просто установить флажок напротив строки игнорирования DDE-запросов. Что это такое, в данном случае не столь важно. Главное - что такая методика срабатывает практически всегда. Но это 99% случаев. Для 1%, когда данная методика не дает результата, придется применять другие способы, которые, кстати, при некоторых условиях являются не менее работоспособными.

Деактивация режима совместимости

Не секрет, что в операционных системах Windows при установке разных офисных пакетов или при использовании более свежих версий «Офиса» на устаревших ОС предусмотрена возможность запуска программ в режиме совместимости (например, с Windows XP).

Для исправления ситуации заходим в каталог «Офиса», который обычно располагается в директории Program Files (x86). Находим в нем исполняемый EXE-файл основной программы, через ПКМ вызываем строку свойств и снимаем галочки со строк старта в режиме совместимости и запуска от имени администратора, после чего перегружаем систему. Такой вариант подходит для всех офисных приложений, включая Word, Excel, Access, Power Point и т.д.

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

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

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

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

Установка программ по умолчанию

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

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

Восстановление и переустановка Office

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

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

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

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

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

Как вариант, можно зайти в «Центр обновления», задать ручной поиск при включенном задействовании установки апдейтов для других программных продуктов Microsoft, а все, что будет найдено, интегрировать в систему.

Что в итоге?

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

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

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

Преобразование ошибки в нулевое значение и использование формата для скрытия значения

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

Создание образца ошибки

Откройте чистый лист или создайте новый.

Введите 3 в ячейку B1, 0 - в ячейку C1 и формулу =B1/C1 - в ячейку A1.
В ячейке A1 отобразится значение ошибки #ДЕЛ/0!.

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

После знака равенства (=) введите ЕСЛИОШИБКА с открывающей круглой скобкой:
ЕСЛИОШИБКА(

Переместите курсор в конец формулы.

Введите ,0) , т. е. запятую с нулем и закрывающей круглой скобкой.
Формула =B1/C1 примет вид =ЕСЛИОШИБКА(B1/C1,0) .

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

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

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

Выберите команду Создать правило .

В диалоговом окне выберите параметр .

Убедитесь, что в разделе в первом списке выбран пункт Значение ячейки , а во втором - равно . Затем в текстовом поле справа введите значение 0.

Нажмите кнопку Формат .

На вкладке Число в списке Числовые форматы выберите пункт (все форматы) .

В поле Тип введите ;;; (три точки с запятой) и нажмите кнопку ОК . Нажмите кнопку ОК еще раз.
Значение 0 в ячейке исчезнет. Это связано с тем, что пользовательский формат ;;; предписывает скрывать любые числа в ячейке. Однако фактическое значение (0) по-прежнему хранится в ячейке.

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

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

Выделите диапазон ячеек, содержащих значение ошибки.

На вкладке Главная в группе Стили Условное форматирование и выберите пункт Управление правилами .
Появится диалоговое окно Диспетчер правил условного форматирования .

Выберите команду Создать правило .
Откроется диалоговое окно Создание правила форматирования .

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

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

Нажмите кнопку Формат и откройте вкладку Шрифт .

Щелкните стрелку, чтобы открыть список Цвет , и в разделе Цвета темы выберите белый цвет.

Отображение тире, #Н/Д или НД вместо значения ошибки

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

Описание функции

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

НД Эта функция возвращает в ячейке строку "#Н/Д". Синтаксис функции: =НД() .

Скрытие значений ошибок в отчете сводной таблицы

Щелкните отчет сводной таблицы.
Появится вкладка Работа со сводными таблицами .

Excel 2016 и Excel 2013: на вкладке Анализ в группе Сводная таблица щелкните стрелку рядом с командой Параметры и выберите пункт Параметры .

Excel 2010 и Excel 2007: на вкладке Параметры в группе Сводная таблица щелкните стрелку рядом с командой Параметры и выберите пункт Параметры .

Откройте вкладку Разметка и формат и выполните одно или оба указанных ниже действия.

• Измените способ отображения ошибок. В группе Формат установите флажок Для ошибок отображать . Введите в поле значение, которое должно отображаться вместо ошибок. Чтобы в пустых полях отображались ошибки, удалите из поля весь текст.

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

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

1. #ДЕЛ/О! – «деление на 0», чаще всего возникает при попытке делить на ноль. То есть заложенная в ячейке формула, выполняя функцию деления, натыкается на ячейку, где с нулевым значением или стоит «Пусто». Чтобы устранить проблему, проверьте все участвующие в вычислениях ячейки и исправьте все недопустимые значения. Второе действие, приводящее к #ДЕЛ/О! – это ввод неверных значений в некоторые функции, такие как =СРЗНАЧ(), если при расчете в диапазоне значений стоит 0. Тот же результат спровоцируют незаполненные ячейки, к которым обращается формула, требующая для расчета конкретных данных.
2. #Н/Д – «нет данных». Так Excel помечает значения, непонятные формуле (функции). Введя неподходящие цифры в функцию, вы обязательно вызовете эту ошибку. При ее появлении проследите, все ли входные ячейки заполнены правильно, а особенно в тех из них, где светится такая же надпись. Часто встречается при использовании
3. #ИМЯ ? – «недопустимое имя», показатель некорректного имени формулы или какой-то ее части. Проблема исчезает, если проверить и подправить все названия и имена, сопровождающие алгоритм вычислений.
4. #ПУСТО! – «в диапазоне пустое значение», сигнал о том, что где-то в расчете прописаны непересекающиеся области или проставлен пробел между указанными диапазонами. Довольно редкая ошибка. Выглядеть ошибочная запись может так:

СУММ(G10:G12 I8:J8)

Excel не распознает такие команды.
5. #ЧИСЛО! – ошибку вызывает формула, содержащая число, не соответствующее рамкам обозначенного диапазона.
6. #ССЫЛКА! – предупреждает о том, что исчезли ячейки, связанные с этой формулой. Проверьте, скорее всего были удалены ячейки указанные в формуле.
7. #ЗНАЧ! – неверно выбран тип аргумента для работы функции.

8. Бонус, ошибка ##### — ширина ячейки недостаточна для отображения всего числа

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

Ошибки в Excel. Применение функции ЕОШИБКА () для Excel 2003

Хорошо помогает ликвидировать ошибки в Excel функция . Действует она путем нахождения ошибок в ячейки, если находит ошибку в формуле возвращает значение ИСТИНА и наоборот. В комбинации с =ЕСЛИ(), она позволят заменить значение если найдена ошибка.

Рабочая формула: =ЕСЛИ(ОШИБКА(выражение);ошибка;выражение).

ЕСЛИ(ЕОШИБКА (А1/А2);””;А1/А2)

Пояснение: если при выполнении А1/А2 найдена ошибка, будет возвращено пусто («»). Если все прошло корректно (т.е. ЕОШИБКА (А1/А2) = ЛОЖЬ), то рассчитывается А1/А2.

Ошибки в Excel. Применение ЕСЛИОШИБКА() для Excel 2007 и выше

Одна из причин, почему я быстро перешел на Excel 2007 была ЕСЛИОШИБКА() (самая главная причина — это )

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

Инструмент активируется следующим образом: =ЕСЛИОШИБКА(значение; значение при ошибке). Вместо «значение» ставится расчетное выражение/ссылка на ячейку, а вместо «значение при ошибке» — то, что следует вернуть при появлении неточности например, если при расчете А1/А2 выдается #ДЕЛ/О! то формула будет выглядеть следующим образом:

ЕСЛИОШИБКА(А1/А2;””)

» качайте здесь