Поиск ошибок в формулах

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

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

Ввод простой формулы

Формулы — это выражения, с помощью которых выполняются вычисления со значениями на листе. Формула начинается со знака равенства (=). Например, следующая формула складывает числа 3 и 1:

=3+1

Формула также может содержать один или несколько из таких элементов: функции, ссылки, операторы и константы.

Части формулы

Части формулы

  1. Функции: это специальные формулы Excel, которые выполняют определенные вычисления. Например, функция ПИ() возвращает значение числа Пи: 3,142...

  2. Ссылки: это ссылки на отдельные ячейки или диапазоны. Например, A2 возвращает значение ячейки A2.

  3. Константы. Числа или текстовые значения, введенные непосредственно в формулу, например 2.

  4. Операторы: оператор * (звездочка) служит для умножения чисел, а оператор ^ (крышка) — для возведения числа в степень. С помощью + и – можно складывать и вычитать значения, а с помощью / — делить их.

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

Например, функция СУММ требует только один аргумент, но у нее может быть до 255 аргументов (включительно).

Функция СУММ

Пример одного аргумента: =СУММ(A1:A10).

Пример нескольких аргументов: =СУММ(A1:A10;C1:C10).

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

Рекомендация

Дополнительные сведения

Начинайте каждую формулу со знака равенства (=)

Если знак равных не завис, то введите его в виде текста или даты. Например, если ввести СУММ(A1:A10),Excel отобразит текстовую строку СУММ(A1:A10) и не вычислит ее. Если ввести 11/2,Excel отобразит дату 2–ноя (предполагается, что формат ячейки — общий),а не деление 11 на 2.

Следите за соответствием открывающих и закрывающих скобок

Все скобки должны быть парными (открывающая и закрывающая). Если в формуле используется функция, для ее правильной работы важно, чтобы все скобки стояли в правильных местах. Например, формула =ЕСЛИ(B5<0);"Недопустимо";B5*1,05) не будет работать, поскольку в ней две закрывающие скобки и только одна открывающая (требуется одна открывающая и одна закрывающая). Правильный вариант этой формулы выглядит так: =ЕСЛИ(B5<0;"Недопустимо";B5*1,05).

Для указания диапазона используйте двоеточие

Указывая диапазон ячеек, разделяйте с помощью двоеточия (:) ссылку на первую ячейку в диапазоне и ссылку на последнюю ячейку в диапазоне. Например, =СУММ(A1:A5)и не =СУММ(A1 A5)— возвращает #NULL! Ошибка.

Вводите все обязательные аргументы

У некоторых функций есть обязательные аргументы. Старайтесь также не вводить слишком много аргументов.

Вводите аргументы правильного типа

В некоторых функциях, например СУММ, необходимо использовать числовые аргументы. В других функциях, например ЗАМЕНИТЬ, требуется, чтобы хотя бы один аргумент имел текстовое значение. Если использовать в качестве аргумента данные неправильного типа, Excel может возвращать непредвиденные результаты или ошибку.

Число уровней вложения функций не должно превышать 64

В функцию можно вводить (или вкладывать) не более 64 уровней вложенных функций.

Имена других листов должны быть заключены в одинарные кавычки

Если формула содержит ссылки на значения или ячейки на других листах или в других книгах, а имя другой книги или листа содержит пробелы или другие небуквенные символы, его необходимо заключить в одиночные кавычки ('), например: ='Данные за квартал'!D3 или =‘123’!A1.

Указывайте после имени листа восклицательный знак (!), когда ссылаетесь на него в формуле

Например, чтобы возвратить значение ячейки D3 листа "Данные за квартал" в той же книге, воспользуйтесь формулой ='Данные за квартал'!D3.

Указывайте путь к внешним книгам

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

Ссылка на книгу содержит имя книги и должна быть заключена в квадратные скобки ([Имякниги.xlsx]). В ссылке также должно быть указано имя листа в книге.

В формулу также можно включить ссылку на книгу, не открытую в Excel. Для этого необходимо указать полный путь к соответствующему файлу, например: =ЧСТРОК('C:\My Documents\[Показатели за 2-й квартал.xlsx]Продажи'!A1:A8). Эта формула возвращает количество строк в диапазоне ячеек с A1 по A8 в другой книге (8).

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

Числа нужно вводить без форматирования

Не форматируйте числа, которые вводите в формулу. Например, если нужно ввести в формулу значение 1 000 рублей, введите 1000. Если вы введете какой-нибудь символ в числе, Excel будет считать его разделителем. Если вам нужно, чтобы числа отображались с разделителями тысяч или символами валюты, отформатируйте ячейки после ввода чисел.

Например, если для прибавления 3100 к значению в ячейке A3 используется формула =СУММ(3 100;A3), Excel не складывает 3100 и значение в ячейке A3 (как было бы при использовании формулы =СУММ(3100;A3)), а суммирует числа 3 и 100, после чего прибавляет полученный результат к значению в ячейке A3. Другой пример: если ввести =ABS(-2 134), Excel выведет ошибку, так как функция ABS принимает только один аргумент: =ABS(-2134).

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

Существуют два способа пометки и исправления ошибок: последовательно (как при проверке орфографии) или сразу при появлении ошибки во время ввода данных на листе.

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

  1. В Excel для Windows щелкните файл >параметры > формул или
    В Excel для Mac щелкните меню Excel > параметры > проверки ошибок. 

    В Excel 2007 нажмите кнопку Microsoft Office Изображение кнопки Office и выберите Параметры Excel > Формулы.

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

    Ячейка с неправильной формулой
  3. Чтобы изменить цвет треугольника, которым помечаются ошибки, выберите нужный цвет в поле Цвет индикаторов ошибок.

  4. В разделе Правила поиска ошибок установите или снимите флажок для любого из следующих правил:

    • Ячейки, содержащие формулы,которые могут привести к ошибке: Формула не использует ожидаемый синтаксис, аргументы или типы данных. Значения ошибок: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! и #VALUE!. Каждое из этих значений ошибок имеет разные причины и решается разными способами.

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

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

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

      • Введите формулу в ячейку вычисляемого столбца и нажмите CTRL+Z или кнопку "Отменить Кнопка "Отменить" на панели быстрого доступа.

      • Ввод новой формулы в вычисляемый столбец, который уже содержит одно или несколько исключений.

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

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

    • Ячейки, содержащие годы,представленные как 2 цифры: ячейка содержит дату в текстовом текстовом виде, которую можно интерпретовать как неправильное век при ее использовании в формулах. Например, дата в формуле =ГОД("1.1.31") может относиться как к 1931, так и к 2031 году. Используйте это правило для выявления дат в текстовом формате, допускающих двоякое толкование.

    • Числа, отформатированные как текст,или с предшествующим апострофом: ячейка содержит числа, сохраненные как текст. Обычно это является следствием импорта данных из других источников. Числа, хранящиеся как текст, могут стать причиной неправильной сортировки, поэтому лучше преобразовать их в числовой формат. '=СУММ(A1:A10) является текстом.

    • Формулы, несогласованныес другими формулами в области: они не соответствуют шаблону других формул рядом с ней. Во многих случаях формулы, смежные с другими формулами, различаются только ссылками. В следующем примере из четырех смежных формул Excel отображает ошибку рядом с формулой =СУММ(A10:C10) в ячейке D4, так как смежные формулы приращены на одну строку, а одна — на 8 строк — ожидается формула =СУММ(A4:C4).

      Excel сообщает об ошибке, если формула не похожа на смежные.

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

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

      Например, при использовании этого правила Excel отображает ошибку для формулы =СУММ(D2:D4), поскольку ячейки D5, D6 и D7, смежные с ячейками, на которые ссылается формула, и ячейкой с формулой (D8), содержат данные, на которые должна ссылаться формула.

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

    • Формулы, которые ссылаются на пустые ячейки:формула содержит ссылку на пустую ячейку. Это может привести к неверным результатам, как показано в приведенном далее примере.

      Предположим, требуется найти среднее значение чисел в приведенном ниже столбце ячеек. Если третья ячейка пуста, она не используется в расчете, поэтому результатом будет значение 22,75. Если эта ячейка содержит значение 0, результат будет равен 18,2.

      Excel сообщает об ошибке, если формула ссылается на пустые ячейки
    • В таблицу введены недопустимыеданные: в таблице есть ошибка проверки. Проверьте параметры проверки для ячейки, на вкладке "Данные" > "Средства работы с данными" > проверки данных.

  1. Выберите лист, на котором требуется проверить наличие ошибок.

  2. Если расчет листа выполнен вручную, нажмите клавишу F9, чтобы выполнить расчет повторно.

    Если диалоговое окно Поиск ошибок не отображается, щелкните вкладку Формулы, выберите Зависимости формул и нажмите кнопку Поиск ошибок.

  3. Чтобы повторно проверить пропущенные ранее ошибки, щелкните Файл > Параметры > Формулы. В Excel для Mac щелкните меню Excel > параметры > проверки ошибок.

    В разделе Поиск ошибок выберите Сброс пропущенных ошибок и нажмите кнопку ОК.

    Поиск ошибок

    Примечание: Сброс пропущенных ошибок применяется ко всем ошибкам, которые были пропущены на всех листах активной книги.

    Совет: Советуем расположить диалоговое окно Поиск ошибок непосредственно под строкой формул.

    Перетащите диалоговое окно "Поиск ошибок" под строку формул
  4. Нажмите одну из управляющих кнопок в правой части диалогового окна. Доступные действия зависят от типа ошибки.

  5. Нажмите кнопку Далее.

Примечание: Если нажать кнопку Пропустить ошибку, помеченная ошибка при последующих проверках будет пропускаться.

  1. Рядом с ячейкой нажмите кнопку "Проверка Значок "Проверка ошибок" " и выберите нужный параметр. Доступные команды для каждого типа ошибки различаются, и первый пункт описывает ошибку.

    Если нажать кнопку Пропустить ошибку, помеченная ошибка при последующих проверках будет пропускаться.

    Перетащите диалоговое окно "Поиск ошибок" под строку формул

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

Приведенная ниже таблица содержит ссылки на статьи, в которых подробно описаны эти ошибки, и краткое описание.

Статья

Описание

Исправление ошибки ####

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

Например, результатом формулы, вычитающей дату в будущем из даты в прошлом (=15.06.2008-01.07.2008), является отрицательное значение даты.

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

Ошибка с #

Исправление ошибки #ДЕЛ/0! #BUSY!

Эта ошибка отображается в Excel, если число делится на ноль (0) или на ячейку без значения.

Совет: Добавьте обработчик ошибок, как в примере ниже: =ЕСЛИ(C2;B2/C2;0).

Для скрытия ошибок можно использовать функцию обработки ошибок, например ЕСЛИ

Исправление ошибки #Н/Д

Эта ошибка отображается в Excel, если функции или формуле недоступно значение.

Если вы используете такую функцию, как ВПР, есть ли для искомого значения соответствие в диапазоне поиска? Скорее всего, нет.

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

=ЕСЛИОШИБКА(ВПР(D2;$D$6:$E$8;2;ИСТИНА);0)

Ошибка #Н/Д

Исправление ошибки #ИМЯ? #BUSY!

Эта ошибка отображается, если Excel не распознает текст в формуле. Например имя диапазона или имя функции написано неправильно.

Примечание: Если вы используете функцию, убедитесь, что ее имя написано неправильно. В данном случае слово СУММ введено с ошибкой. Удалите "а", и Excel исправит формулу.

Ошибка #ИМЯ? выводится, если в имени функции есть опечатка

Исправление #NULL! #BUSY!

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

Примечание: Убедитесь, что диапазоны правильно разделены: области C2:C3 и E4:E6 не пересекаются, поэтому при вводе формулы =СУММ(C2:C3 E4:E6) возвращается #NULL! ошибка "#ЗНАЧ!". Если ввести между диапазонами C и E запятую, она будет исправлять ошибку =СУММ(C2:C3;E4:E6)

#ПУСТО! #BUSY!

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

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

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

Исправление ошибки #ССЫЛКА! #BUSY!

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

Вы случайно удалили строку или столбец? Смотрите, что произошло после удаления столбца B в формуле =СУММ(A2;B2;C2).

Нажмите кнопку Отменить (или клавиши CTRL+Z), чтобы отменить удаление, измените формулу или используйте ссылку на непрерывный диапазон (=СУММ(A2:C2)), которая автоматически обновится при удалении столбца B.

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

Исправление ошибки #ЗНАЧ! #BUSY!

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

Вы используйте математические операторы (+, -, *, / ^) с разными типами данных? В таком случае попробуйте использовать вместо них функцию. В этом случае =СУММ(F2:F5) поможет устранить проблему.

Ошибки #BUSY!

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

Окно контрольного значения позволяет отслеживать формулы на листе

Эту панель инструментов можно перемещать и закреплять, как и любую другую. Например, можно закрепить ее в нижней части окна. На панели инструментов выводятся следующие свойства ячейки: 1) книга, 2) лист, 3) имя (если ячейка входит в именованный диапазон), 4) адрес ячейки 5) значение и 6) формула.

Примечание: Для каждой ячейки может быть только одно контрольное значение.

Добавление ячеек в окно контрольного значения

  1. Выделите ячейки, которые хотите просмотреть.

    Чтобы выделить все ячейки с формулами, на вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить (вы также можете нажать клавиши CTRL+G или CONTROL+G на компьютере Mac). Затем выберите Выделить группу ячеек и Формулы.

    Диалоговое окно "Специальная вставка"
  2. На вкладке Формулы в группе Зависимости формул нажмите кнопку Окно контрольного значения.

  3. Нажмите кнопку Добавить контрольное значение.

    Нажмите кнопку "Добавить контрольное значение", чтобы добавить контрольное значение на лист
  4. Убедитесь, что вы выделили все ячейки, которые хотите отследить, и нажмите кнопку Добавить.

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

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

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

Удаление ячеек из окна контрольного значения

  1. Если окно контрольного значения не отображается, на вкладке Формула в группе Зависимости формул нажмите кнопку Окно контрольного значения.

  2. Выделите ячейки, которые нужно удалить.

    Чтобы выделить несколько ячеек, щелкните их, удерживая нажатой клавишу CTRL.

  3. Нажмите кнопку Удалить контрольное значение.

    Удалить контрольное значение

Иногда трудно понять, как вложенная формула вычисляет конечный результат, поскольку в ней выполняется несколько промежуточных вычислений и логических проверок. Но с помощью диалогового окна Вычисление формулы вы можете увидеть, как разные части вложенной формулы вычисляются в заданном порядке. Например, формулу =ЕСЛИ(СРЗНАЧ(D2:D5)>50;СУММ(E2:E5);0) будет легче понять, если вы увидите промежуточные результаты:

Команда "Вычислить формулу" позволяет увидеть, как вычисляются части вложенной формулы

В диалоговом окне "Вычисление формулы"

Описание

=ЕСЛИ(СРЗНАЧ(D2:D5)>50;СУММ(E2:E5);0)

Сначала выводится вложенная формула. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.

Диапазон ячеек D2:D5 содержит значения 55, 35, 45 и 25, поэтому функция СРЗНАЧ(D2:D5) возвращает результат 40.

=ЕСЛИ(40>50;СУММ(E2:E5);0)

Диапазон ячеек D2:D5 содержит значения 55, 35, 45 и 25, поэтому функция СРЗНАЧ(D2:D5) возвращает результат 40.

=ЕСЛИ(ЛОЖЬ;СУММ(E2:E5);0)

Поскольку 40 не больше 50, выражение в первом аргументе функции ЕСЛИ (аргумент лог_выражение) имеет значение ЛОЖЬ.

Функция ЕСЛИ возвращает значение третьего аргумента (аргумент значение_если_ложь). Функция СУММ не вычисляется, поскольку она является вторым аргументом функции ЕСЛИ (аргумент значение_если_истина) и возвращается только тогда, когда выражение имеет значение ИСТИНА.

  1. Выделите ячейку, которую нужно вычислить. За один раз можно вычислить только одну ячейку.

  2. Откройте вкладку Формулы и выберите Зависимости формул > Вычислить формулу.

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

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

    Кнопка Шаг с заходом недоступна для ссылки, если ссылка используется в формуле во второй раз или если формула ссылается на ячейку в отдельной книге.

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

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

  6. Чтобы закончить вычисление, нажмите кнопку Закрыть.

Примечания: 

  • Некоторые части формул, в которых используются функции ЕСЛИ и ВЫБОР, не вычисляются. В таких случаях в поле Вычисление отображается значение #Н/Д.

  • Если ссылка пуста, в поле Вычисление отображается нулевое значение (0).

  • Некоторые функции вычисляются заново при каждом изменении листа, так что результаты в диалоговом окне Вычисление формулы могут отличаться от тех, которые отображаются в ячейке. Это функции СЛЧИС, ОБЛАСТИ, ИНДЕКС, СМЕЩ, ЯЧЕЙКА, ДВССЫЛ, ЧСТРОК, ЧИСЛСТОЛБ, ТДАТА, СЕГОДНЯ, СЛУЧМЕЖДУ.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

Отображение связей между формулами и ячейками

Рекомендации, позволяющие избежать появления неработающих формул

Нужна дополнительная помощь?

Совершенствование навыков работы с Office
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

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

×