Sdscompany.ru

Компьютерный журнал
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как найти ошибку в формуле

Ошибки в Excel: как их найти, понять и исправить

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

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

Типы ошибок

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

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

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

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

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

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

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

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

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

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

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

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

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

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Ошибки в Excel — поиск и обработка ошибок в Excel

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

Ошибки в формулах делятся на несколько категорий:

Синтаксические ошибки: Возникают при неправильном синтаксисе формулы. Например, формула имеет несоответствующие скобки, или функция имеет не корректное количество аргументов.

Логические ошибки: В этом случает формула не возвращает ошибку, но имеет логический изъян, что является причиной неправильного результата расчета.

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

Семантические ошибки: Например, название функции написано неправильно, в этом случае Excel вернет ошибку #ИМЯ?

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

Ошибки в формулах массивов: Когда вы вводите формулу массива, по окончании ввода необходимо нажать Ctrl + Sift + Enter. Если вы не сделали этого, Excel не поймет, что это формула массива, и вернет ошибку или некорректный результат.

Ошибки неполных расчётов: В этом случае формулы рассчитываются не полностью. Чтобы удостовериться, что се формулы пересчитаны, наберите Ctrl + Alt + Shift + F9.

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

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

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

Так как Excel воспринимает пустую ячейку как ноль, то при делении на пустую ячейку тоже будет возвращена ошибка. Эта проблема часто встречается при создании формулы для данных, которые еще не были введены. Формула ячейки D4 была протянута на весь диапазон (=C4/B4).

Эта формула возвращает отношение значений колонок C к B. Так как не все данные по дням были занесены, формула вернула ошибку #ДЕЛ/0!

Чтобы избежать ошибки, вы можете воспользоваться формулой ЕСЛИ, для проверки, являются ли ячейки колонки B пустыми или нет:

Эта формула вернет пустое значение, если ячейка B4 будет пустой или содержать 0, в противном случае вы увидите посчитанное значение.

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

Ошибка #Н/Д

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

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

Чтобы перехватить ошибку и отобразить пустую ячейку, воспользуйтесь функцией =ЕСНД().

Обратите внимание, что функция ЕСНД является новой функцией в Excel 2013. Для совместимости с предыдущими версиями воспользуйтесь аналогом этой функции:

Ошибка #ИМЯ?

Excel может вернуть ошибку #ИМЯ? в следующих случаях:

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

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

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

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

Ошибка #ЧИСЛО! будет возвращена в следующих случаях:

  • В числовом аргументе формулы введено нечисловое значение (например, $1,000 вместо 1000)
  • В формуле введен недопустимый аргумент (например, =КОРЕНЬ(-12))
  • Функция, использующая итерацию, не может рассчитать результат. Примеры функций, использующих итерацию: ВСД(), СТАВКА()
  • Формула возвращает значение, которое слишком большое или слишком маленькое. Excel поддерживает значения между -1E-307 и 1E-307.
Читать еще:  Ошибка сервера 1

Ошибка #ССЫЛКА!

Ошибка #ССЫЛКА! возникает в случаях, когда формула использует недействительную ссылку на ячейку. Ошибка возникает в следующих ситуациях:

  • Вы удалили колонку или строку, на которую ссылалась ячейка формулы. Например, следующая формула вернёт ошибку, если первая строка или столбцы A или B были удалены:
  • Вы удалили рабочий лист, на которую ссылалась ячейка формулы. Например, следующая формула вернёт ошибку, если Лист1 был удален:
  • Вы скопировали формулу в расположение, где относительная ссылка становится недействительной. Например, при копировании формулы из ячейки A2 в ячейку A1, формула вернет ошибку #ССЫЛКА!, так как она пытается обратиться к несуществующей ячейке.
  • Вы вырезаете ячейку и затем вставляете ее в ячейку, на которую ссылается формула. В этом случае будет возвращена ошибка #ССЫЛКА!

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

Ошибка #ЗНАЧ! является самой распространенной ошибкой и возникает в следующих ситуациях:

  • Аргумент функции имеет неверный тип данных или формула пытается выполнить операцию, используя неверные данные. Например, при попытке сложения числового значения с текстовым, формула вернет ошибку
  • Аргумент функции является диапазоном, когда он должен быть одним значением
  • Пользовательские функции листа не рассчитываются. Для принудительного пересчета нажмите Ctrl + Alt + F9
  • Пользовательская функция листа пытается выполнить операцию, которая не является допустимой. Например, пользовательская функция не может изменить среду Excel или сделать изменения в других ячейках
  • Вы забыли нажать Ctrl + Shift + Enter при вводе формулы массива

Вам также могут быть интересны следующие статьи

5 комментариев

Ренат, прошу у вас помощи,как у спеца… Создала книгу с кучей страниц и ссылок на разные страницы. Потом необходимо построить диаграммы, а у меня мастер диаграмм почему-то стал неактивен, и никак не могу сделать ни через «Вставку», никак. Может я что-то в настройках сбила? помогите, пожалуйста, что можно проверить.

Людмила, ни разу не сталкивался с проблемой неактивности мастера диаграмм по причине большого количества связей. В любом случае необходимо взглянуть на книгу. Мой ящик — admin@exceltip.ru

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

Ошибки в Excel

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

Ошибка #ИМЯ?

Ошибка #ИМЯ появляется, когда имя, которое используется в формуле, было удалено или не было ранее определено.

Причины возникновения ошибки #ИМЯ?:

  1. Если в формуле используется имя, которое было удалено или не определено.

Ошибки в Excel – Использование имени в формуле

Устранение ошибки: определите имя. Как это сделать описано в этой статье.

  1. Ошибка в написании имени функции:

Ошибки в Excel – Ошибка в написании функции ПОИСКПОЗ

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

  1. В ссылке на диапазон ячеек пропущен знак двоеточия (:).

Ошибки в Excel – Ошибка в написании диапазона ячеек

Устранение ошибки: исправьте формулу. В вышеприведенном примере это =СУММ(A1:A3).

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

Ошибки в Excel – Ошибка в объединении текста с числом

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

Ошибки в Excel – Правильное объединение текста

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

Ошибка #ЧИСЛО! в Excel выводится, если в формуле содержится некорректное число. Например:

  1. Используете отрицательное число, когда требуется положительное значение.

Ошибки в Excel – Ошибка в формуле, отрицательное значение аргумента в функции КОРЕНЬ

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

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

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

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

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

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

Причины ошибки #ЗНАЧ!:

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

Ошибки в Excel – Суммирование числовых и текстовых значений

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

  1. В аргументе функции введен диапазон, а функция предполагается ввод одного значения.

Ошибки в Excel – В функции ВПР в качестве аргумента используется диапазон, вместо одного значения

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

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

Устранение ошибки: для завершения ввода формулы используйте комбинацию клавиш Ctrl+Shift+Enter .

Ошибки в Excel – Использование формулы массива

Ошибка #ССЫЛКА

В случае если формула содержит ссылку на ячейку, которая не существует или удалена, то Excel выдает ошибку #ССЫЛКА.

Ошибки в Excel – Ошибка в формуле, из-за удаленного столбца А

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

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

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

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

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

Ошибка #Н/Д

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ошибка ####

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

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

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

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

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

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

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

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

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

Читать еще:  Ошибка предварительного просмотра

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В Excel для Windows щелкните файл _гт_ Параметры _гт_ формулыили
в Excel для Mac откройте меню Excel _Гт_ параметры _Гт_ Поиск ошибок.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Код ошибки 30015 25 1006

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

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

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

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

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

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

Если вы ранее не проигнорировали какие-либо ошибки, их можно снова проверить, выполнив указанные ниже действия. Щелкните файл _гт_ Параметры _гт_ формулы. В Excel для Mac откройте меню Excel _Гт_ параметры _Гт_ Поиск ошибок.

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

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

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

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

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

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

Нажмите появившуюся рядом с ячейкой кнопку Поиск ошибок и выберите нужный пункт. Доступные команды зависят от типа ошибки. Первый пункт содержит описание ошибки.

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

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

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

Обзор ошибок, возникающих в формулах Excel

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

Несоответствие открывающих и закрывающих скобок

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

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

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

Ячейка заполнена знаками решетки

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

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

    …или изменить числовой формат ячейки.

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

В данном случае увеличение ширины столбца уже не поможет.

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

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

Ошибка #Н/Д

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

  1. Функция поиска не находит соответствия. К примеру, функция ВПР при точном поиске вернет ошибку #Н/Д, если соответствий не найдено.
  2. Формула прямо или косвенно обращается к ячейке, в которой отображается значение #Н/Д.
  3. При работе с массивами в Excel, когда аргументы массива имеют меньший размер, чем результирующий массив. В этом случае в незадействованных ячейках итогового массива отобразятся значения #Н/Д.Например, на рисунке ниже видно, что результирующий массив C4:C11 больше, чем аргументы массива A4:A8 и B4:B8.

Нажав комбинацию клавиш Ctrl+Shift+Enter, получим следующий результат:

Ошибка #ИМЯ?

Ошибка #ИМЯ? возникает, когда в формуле присутствует имя, которое Excel не понимает.

  1. Например, используется текст не заключенный в двойные кавычки:
  2. Функция ссылается на имя диапазона, которое не существует или написано с опечаткой:

В данном примере имя диапазон не определено.

  1. Адрес указан без разделяющего двоеточия:
  2. В имени функции допущена опечатка:

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

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

  1. Например, =А1:А10 C5:E5 – это формула, использующая оператор пересечения, которая должна вернуть значение ячейки, находящейся на пересечении двух диапазонов. Поскольку диапазоны не имеют точек пересечения, формула вернет #ПУСТО!.
  2. Также данная ошибка возникнет, если случайно опустить один из операторов в формуле. К примеру, формулу =А1*А2*А3 записать как =А1*А2 A3.

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

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

  1. Например, задано отрицательное значение там, где должно быть положительное. Яркий пример – квадратный корень из отрицательного числа.
  2. К тому же, ошибка #ЧИСЛО! возникает, когда возвращается слишком большое или слишком малое значение. Например, формула =1000^1000 вернет как раз эту ошибку.

Не забывайте, что Excel поддерживает числовые величины от -1Е-307 до 1Е+307.

  1. Еще одним случаем возникновения ошибки #ЧИСЛО! является употребление функции, которая при вычислении использует метод итераций и не может вычислить результат. Ярким примером таких функций в Excel являются СТАВКА и ВСД.

Ошибка #ССЫЛКА!

Ошибка #ССЫЛКА! возникает в Excel, когда формула ссылается на ячейку, которая не существует или удалена.

    Например, на рисунке ниже представлена формула, которая суммирует значения двух ячеек.

Если удалить столбец B, формула вернет ошибку #ССЫЛКА!.


Еще пример. Формула в ячейке B2 ссылается на ячейку B1, т.е. на ячейку, расположенную выше на 1 строку.

Если мы скопируем данную формулу в любую ячейку 1-й строки (например, ячейку D1), формула вернет ошибку #ССЫЛКА!, т.к. в ней будет присутствовать ссылка на несуществующую ячейку.

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

Ошибка #ЗНАЧ! одна из самых распространенных ошибок, встречающихся в Excel. Она возникает, когда значение одного из аргументов формулы или функции содержит недопустимые значения. Самые распространенные случаи возникновения ошибки #ЗНАЧ!:

  1. Формула пытается применить стандартные математические операторы к тексту.
  2. В качестве аргументов функции используются данные несоответствующего типа. К примеру, номер столбца в функции ВПР задан числом меньше 1.
  3. Аргумент функции должен иметь единственное значение, а вместо этого ему присваивают целый диапазон. На рисунке ниже в качестве искомого значения функции ВПР используется диапазон A6:A8.

Вот и все! Мы разобрали типичные ситуации возникновения ошибок в Excel. Зная причину ошибки, гораздо проще исправить ее. Успехов Вам в изучении Excel!

Ссылка на основную публикацию
Adblock
detector