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

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

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

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

=3+1

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

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

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

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

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

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

   Примечание

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

Например, функция СУММ требует только один аргумент, но у нее может быть до 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'! А1.
Указывайте после имени листа восклицательный знак (!), когда ссылаетесь на него в формуле	Например, чтобы возвратить значение ячейки D3 листа "Данные за квартал" в той же книге, воспользуйтесь формулой ='Данные за квартал'!D3.
Указывайте путь к внешним книгам	Убедитесь, что каждая внешняя ссылка содержит имя книги и путь к ней. Ссылка на книгу содержит имя книги и должна быть заключена в квадратные скобки ([Имякниги.xlsx]). В ссылке также должно быть указано имя листа в книге. В формулу также можно включить ссылку на книгу, не открытую в Excel. Для этого необходимо указать полный путь к соответствующему файлу, например: =ЧСТРОК('C:\My Documents\[Показатели за 2-й квартал.xlsx]Продажи'!A1:A8). Эта формула возвращает количество строк в диапазоне ячеек с A1 по A8 в другой книге (8). Примечание. Если полный путь содержит пробелы, как в предыдущем примере, его необходимо заключить в одинарные кавычки (в начале пути и после имени листа перед восклицательным знаком).
Числа нужно вводить без форматирования	Не форматируйте числа, которые вводите в формулу. Например, если нужно ввести в формулу значение 1 000 рублей, введите 1000. Если вы введете какой-нибудь символ в числе, Excel будет считать его разделителем. Если вам нужно, чтобы числа отображались с разделителями тысяч или символами валюты, отформатируйте ячейки после ввода чисел. Например, если к значению ячейки A3 нужно прибавить 3100, вводя формулу =СУММ(3100;A3), Excel сложит числа 3 и 100, а затем прибавит полученную сумму к значению из ячейки A3, а не 3100 в ячейке A3, т. е. = СУММ(3100;A3). Если ввести формулу =ABS(-2,134), появится сообщение об ошибке, так как функция ABS принимает только один аргумент: =ABS(-2134).

Исправление распространенных ошибок в формулах

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

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

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

Включение и отключение правил проверки ошибок

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

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

   

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

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

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

     Примечание

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

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

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

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

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

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

     

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

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

     

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

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

     

   • Недопустимые данные, введенные в таблицу: в таблице произошла ошибка проверки. Проверьте параметр проверки ячейки, перейдя на вкладку "Данные" >Группа "Работа с> данными" Проверка данных.

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

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

2. Если расчет листа выполнен вручную, нажмите клавишу F9, чтобы выполнить расчет повторно.
   Если диалоговое окно "Поиск ошибок" не отображается, выберите пункт "Формулы"Проверкаошибокаудита>> формул.

3. Если ранее вы пропустили ошибки, их можно снова проверить, выполнив следующие действия.Перейдите в раздел "Формулы параметров>файла>". В Excel для Mac выберите в меню Excel > пункт "Проверка ошибок" в параметрах>.
   В разделе "Проверка ошибок" выберите "ОК сбросить пропускаемые ошибки>".

   

   Примечание

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

   Совет

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

   

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

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

Исправление распространенных ошибок по одной

1. Нажмите значок проверки ошибок рядом с ячейкой , а затем выберите нужный вариант. Команды, доступные различны, для каждого типа ошибок, и первая запись описывает ошибку.
   Если вы выберете «Пропустить ошибку», ошибка будет помечена для игнорирования при каждой последующей проверке.

   

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

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

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

Статья	Описание
Исправление ошибки ####	Эта ошибка отображается в Excel, если столбец недостаточно широк, чтобы показать все символы в ячейке, или ячейка содержит отрицательное значение даты или времени. Например, результатом формулы, вычитающей дату в будущем из даты в прошлом (=15.06.2008-01.07.2008), является отрицательное значение даты. Совет: Попробуйте автоматически подобрать размер ячейки, дважды щелкнув заголовки столбцов. Если ### отображается потому, что Excel не может отобразить все символы, это исправляется.
Исправление ошибки #ДЕЛ/0! ошибка	Эта ошибка отображается в Excel, если число делится на ноль (0) или на ячейку без значения. Совет: Добавьте обработчик ошибок, как в следующем примере, который =ЕСЛИ(C2;B2/C2;0)
Исправление ошибки #Н/Д	Эта ошибка отображается в Excel, если функции или формуле недоступно значение. Если вы используете такую функцию, как ВПР, совпадает ли то, что вы пытаетесь найти, в диапазоне поиска? Чаще всего это не так. Используйте функцию ЕСЛИОШИБКА для подавления ошибки #Н/Д. В этом случае можно ввести следующее: =ЕСЛИОШИБКА(ВПР(D2;$D$6:$E$8;2;ИСТИНА);0)
Исправление ошибки #ИМЯ?	Эта ошибка отображается, если Excel не распознает текст в формуле. Например, может быть неправильно написано имя диапазона или функции. Примечание. Если вы используете функцию, убедитесь, что имя функции написано правильно. В данном случае слово СУММ введено с ошибкой. Удалите букву "e", и Excel исправит ее.
Исправление ошибки #ПУСТО!	Эта ошибка отображается в Excel, когда вы указываете пересечение двух областей, которые не пересекаются. Оператором пересечения является пробел, разделяющий ссылки в формуле. Примечание. Убедитесь, что диапазоны разделены верно: области C2:C3 и E4:E6 не пересекаются, поэтому при вводе формулы =СУММ(C2:C3 E4:E6) возвращается #NULL! . Если поставить точку с запятой между диапазонами C и E, это исправит ошибку =СУММ(C2:C3,E4:E6)
Исправление ошибки #ЧИСЛО! ошибка	Эта ошибка отображается в Excel, если формула или функция содержит недопустимые числовые значения. Вы используете функцию, выполняющую итерацию, например ВСД или СТАВКА? Если да, то #NUM! возникает, вероятно, из-за того, что функция не может найти результат. Инструкции по разрешению см. в разделе справки.
Исправление ошибки #ССЫЛКА! ошибка	Эта ошибка отображается в Excel при наличии недопустимой ссылки на ячейку. Например, можно удалить ячейки, на которые ссылались другие формулы, или вставить ячейки, перемещенные поверх ячеек, на которые ссылались другие формулы. Вы случайно удалили строку или столбец? Смотрите, что произошло после удаления столбца B в формуле =СУММ(A2;B2;C2). Используйте команду "Отменить" (CTRL+Z), чтобы отменить удаление, перестроить формулу или использовать непрерывную ссылку на диапазон, например: =СУММ(A2:C2), которая автоматически обновлялась при удалении столбца Б.
Исправление ошибки #ЗНАЧ! ошибка	Эта ошибка отображается в Excel, если в формуле используются ячейки, содержащие данные не того типа. Вы используйте математические операторы (+, -, *, / ^) с разными типами данных? В таком случае попробуйте использовать вместо них функцию. В этом случае =СУММ(F2:F5) исправит проблему.

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

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

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

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

1. Выделите ячейки, которые хотите просмотреть.
   Чтобы выделить все ячейки на листе с формулами, перейдите на вкладку «Домашнее>редактирование> », нажмите «Найти & Выделить » (либо вы можете использовать клавиши CTRL+G или CONTROL+G на Mac)> Перейдите к специальным>формулам.

   

2. Перейдите в раздел "Формулы"Зависимости> формул, > выберите "Контрольное окно".

3. Выберите "Добавить часы".

   

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

   

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

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

   Примечание

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

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

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

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

3. Выберите "Удалить часы".

   

Вычисление вложенной формулы по шагам

Иногда трудно понять, как вложенная формула вычисляет конечный результат, поскольку в ней выполняется несколько промежуточных вычислений и логических проверок. Но с помощью команды "Вычислить формулу " в Excel для Windows вы можете увидеть, как разные части вложенной формулы вычисляются в заданном порядке. Например, формулу =ЕСЛИ(СРЗНАЧ(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. В Excel для Windows выделите ячейку, которую нужно вычислить. За один раз можно вычислить только одну ячейку.
2. Перейдите в раздел "Формулы", "Проверка формулы",> "Вычисление>формулы".
3. Нажмите Вычислить, чтобы проверить значение подчеркнутой ссылки. Результат вычисления отображается курсивом.
   Если подчеркнутая часть формулы является ссылкой на другую формулу, нажмите кнопку "Шаг с заходом ", чтобы отобразить другую формулу в поле "Вычисление ". Нажмите Шаг с выходом, чтобы вернуться к предыдущей ячейке и формуле.
   Кнопка Шаг с заходом недоступна для ссылки, если ссылка используется в формуле во второй раз или если формула ссылается на ячейку в отдельной книге.
4. Продолжайте нажимать "Вычислить ", пока не будут вычислены все части формулы.
5. Чтобы снова просмотреть оценку, выберите "Перезагрузить".
6. Чтобы завершить вычисление, нажмите кнопку "Закрыть".

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

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

См. также

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

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