Ошибка в ячейке говорит о том что

Просмотров: 129

Ошибки в формулах Excel.Причины и решения.

Почему Excel выдает ошибку?

Во время работы с Excel часто приходится сталкиваться с вычислениями. При этом время от времени вместо определенного значения введенная функция или формула Excel выдает ошибку. Сегодня мы попробуем разобраться с значениями ошибок и их источниками. Что делать, если Excel выдает ошибку и как это исправить?

Ошибки, связанные с неверным вводом формулы.

. Источники ошибочных результатов могут быть самыми разными. Ошибки Excel могут быть связаны как с введенными данными, так и с другими условиями. В частности, к ошибкам в расчете могут приводить действия самого пользователя. Прежде всего, необходимо при вводе функций соблюдать тип вводимых параметров. Нельзя использовать в вложенных формулах число уровней более 64. Если в формуле используется ссылка на другой лист, то после названия листа обязательно должен быть восклицательный знак, а если в названии есть пробел или другой не буквенно-числовой символ, то имя должно быть заключено в одинарные кавычки (апострофы). Нельзя так же форматировать данные для вычислений во время их ввода.

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

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

Рисунок 1

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

Рисунок 2

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

Рисунок 3

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

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

Наиболее распространены следующие варианты таких ошибок.

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

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

#Н/Д! – Ошибка, связанная с недоступными данными. В примере ниже данная ошибка появилась, так как указан код, отсутствующий в таблице с данными.

Рисунок 4

#ИМЯ!  – а вот эту ошибку я называю ошибка зеваки. Ее появление говорит о том, что введено неверное название. Обычно она возникает при попытке ввода в формуле адреса ячеек русскими буквами – как вариант, А10 – буква здесь русская. К этой же ошибке приведет неверный ввод имени диапазона, таблицы, названия формулы и так далее. Проще говоря, она чаще всего возникает при элементарной опечатке во время ввода.

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

А) двоеточие. Используется для указания границ диапазона. Например, запись А1:А12 говорит о использовании ячеек в диапазоне от А1 до А12 включительно.

Б) точка с запятой. Указывает на перечисление диапазонов или отдельных ячеек.  В качестве примера можно привести запись А1:А12;С1:С12

В) пробел определяет пересечение диапазонов. Результатом будет новый диапазон, состоящий из общих ячеек исходных диапазонов. Например, запись =СУММ(B1:C5 A2:D3) идентична записи =СУММ(B2:C3) так как при пересечении диапазонов B1:C5 A2:D3 образуется диапазон B2:C3. Это наглядно видно на изображении ниже. Обратите внимание, что результат вычисления функции СУММ в обеих случаях одинаков.

Рисунок 5

#ЧИСЛО! Распространенный вариант ошибки, связанной с типом данных. Ошибка говорит о недопустимом вводе числового значения. Обычно означает о вводе числа в недопустимом формате, либо о получаемом в результате значении, выходящем за пределы числовых данных, используемых Excel. На примере ниже происходит попытка возвести число 1000000000 в степень с показателем 1000.

Рисунок 6

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

#ССЫЛКА! Так же довольно распространенный вариант. Возникает при попытке обратиться к несуществующему диапазону или ячейке. В следующем примере выводится ошибка именно такого типа при вставке функции ВПР. Дело в том, что в обрабатываемом диапазоне $D$2:$H$715 шестого столбца нет и в помине. Такая же ошибка может появится непосредственно в формуле, если диапазон, на который ссылалась формула или функция, был удален.

Рисунок 7

#ЗНАЧ!  – как и в случае с ошибкой #ЧИСЛО, эта ошибка говорит о неверных данных, но не только именнно о неверных числовых данных, а о любой ошибке при вводе, когда данные не совпадают с параметрами, указанными в синтаксисе функции. Например, в качестве исходных данных в формуле ячейки использован текст, а сама формула применяет стандартные математические действия для чисел. Ну нельзя перемножать буквы. Часто именно такой вариант возникает при попытке использовать для расчета данные, полученные из различных баз данных. Такое часто происходит с той же 1С при некорректной работе ее разработчиков. В таком случае данные выгружаются в excel по стандарту, а так как разделителем дробной части в них обычно выступает точка, то Excel закономерно в русском варианте считает, что это текст.

Как определить ячейки с ошибками и как их исправить?

Вот в этом месте вынужден своих читателей разочаровать. Если речь идет о неверном вводе пользователем в формуле, то автоматически никак. Только если проверять каждую формулу по очереди. В частности, на вкладке ФОРМУЛЫ есть кнопка «ВЫЧИСЛИТЬ ФОРМУЛУ».  С ее помощью можно по шагам проверить ход выполнения формулы и при возникновении ошибки    определить место ее возникновения.

Рисунок 8

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

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

Прежде всего с помощью команды «Найти и выделить» → «выделить группу ячеек», расположенной на вкладке «Главная». Этот же вариант можно вызвать, нажав сочетание F5 или Ctrl+G, а затем кнопку «выделить».

Рисунок 9

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

Так же можно и использовать условное форматирование или проверку данных, применив в них формулы для определения ячеек с ошибками. Можно использовать кнопки «влияющие ячейки» и «Зависимые ячейки» на вкладке «Формулы».

Рисунок 10

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

Рисунок 11

Рисунок 12

В таких случая можно попробовать сочетания клавиш Ctrl+[ или Ctrl+Shift+{ для перехода к влияющим ячейкам на другом листе. Более подробно о поиске неверных данных с помощью условного форматирования и проверки данных можно прочитать в наших материалах здесь.

Однако все эти инструменты смогут лишь определить данные, которые уже неправильны.

Чтобы избежать ошибок при вводе данных, необходимо соблюдать ряд простых правил.

1. При вводе информации не применять никаких форматирований. Формат внешнего вида применяется к ячейкам, но не к самим данным.
2. При вставке функций, как по отдельности, так и в составной формуле, соблюдайте ее синтаксис и назначение аргументов. Например, если в функции ИНДЕКС первым параметром вы зададите номер строки или столбца, то программа просто вас не поймет.
3. Внимательно следить за введенными данными. Именно из-за невнимательности появляются такие ошибки как #ЗНАЧ, #ЧИСЛО, #ССЫЛКА и другие.
4. Использовать вложенность формул только тогда, когда это действительно необходимо. Например, если необходимо проверить количество знаков в ячейке В1 и при необходимости довести их до 12, добавив недостающие нули в начале, можно написать так

=СЦЕПИТЬ(ПОВТОР(«0»;12-ДЛСТР(В1));В1)

А можно так

=ТЕКСТ(В1;«000000000000»).

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

1. Не изменять расположение файлов с исходными данными, листов и диапазонов с ними без крайней необходимости.
2. Если все же формула выдала ошибку, не надо ее удалять и писать заново. От того, что вы десятки раз напишите формулу с ошибкой, ошибка не исчезнет. Внимательно просмотрите все диапазоны и адреса, которые в формуле применяются, проверьте соответствие данных параметрам функций и так далее. На практике часто случаюсь, что люди первым параметром функции СУММЕСЛИ указывали диапазон для суммирования, при работе с ВПР для приблизительного поиска четвертым аргументом указывали ноль, а для точного поиска четвёртый параметр вообще оказывался не задан, случалось, что при расчете количества позиций пытались ввести в качестве исходного значения сразу несколько вариантов ну и так далее.

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

На чтение 2 мин Опубликовано 29.05.2016

В этой статье мы расскажем о том, как справляться с некоторыми наиболее распространёнными ошибками формул в Excel.

Содержание

1. Ошибка #####
2. Ошибка #ИМЯ?
3. Ошибка #ЗНАЧ!
4. Ошибка #ДЕЛ/0!
5. Ошибка #ССЫЛКА!

Ошибка #####

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

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

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

Ошибка #ИМЯ?

Ошибка #ИМЯ? (#NAME?) возникает в том случае, когда Excel не может распознать текст в формуле (например, из-за опечатки).

Просто исправьте SU на SUM.

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

Excel показывает сообщение об ошибке #ЗНАЧ! (#VALUE!) в том случае, когда для формулы введён аргумент не подходящего типа.

a) Измените значение в ячейке A3.

b) Используйте функцию, которая игнорирует ячейки, содержащие текст.

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

Сообщение об ошибке #ДЕЛ/0! (#DIV/0!) появляется при попытке деления числа на ноль или на пустую ячейку.

a) Изменить значение в ячейке A2 на любое число, не равное нулю.

b) Предотвратите возникновение ошибки при помощи логической функции ЕСЛИ (IF).

Пояснение: Если ячейка A2 будет равна нулю, то значением ячейки A3 будет пустая строка. Если нет – то в ячейке A3 будет вычислен результат формулы =A1/A2.

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

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

1. В ячейке C1 содержатся ссылки на ячейки A1 и B1.
2. Удаляем столбец B. Для этого кликаем по заголовку столбца правой кнопкой и в контекстном меню нажимаем Удалить (Delete).
3. Выделите ячейку B1. Ссылка на ячейку B1 в формуле превратилась в ссылку на несуществующую ячейку.
4. Чтобы исправить эту ошибку, нужно либо удалить несуществующую ссылку в формуле, либо отменить действие, кликнув по иконке Отменить (Undo) на панели быстрого доступа (или нажать Ctrl+Z).

Оцените качество статьи. Нам важно ваше мнение:

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

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

• ######

• #ЗНАЧ!

• #ДЕЛ/0!

• #ИМЯ?

• #Н/Д

• #ССЫЛКА!

• #ЧИСЛО!

• #ПУСТО!

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

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

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

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

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

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

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

Ошибка
#Н/Д
является сокращением термина
«Неопределенные Данные».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #

В ячейке таблицы Microsoft Excel появилась запись #ЗНАЧ! Что это значит? Формула подсчёта данных в этой ячейке не работает. Какой выход из создавшейся ситуации? Самая частая причина такой ошибки — несовпадение типов в операциях. Условно говоря, если у вас формула которая перемножает значения в двух ячейках, и хотя бы в одной из этих ячейках не число, а текст, то вы получите эту ошибку. Проверьте корректность всех данных. Кроме того, если вы наведете курсор на эту ячейку и немного подождете, то получите некоторую подсказку по ошибке. Кроме того, такая ошибка может быть из-за того, что результат выполнения не влезает в ячейку по размеру. автор вопроса выбрал этот ответ лучшим Спаан­ч Бооб [85.7K] 6 лет назад  Такой знак часто встречается в ячейках, когда в них прописаны формулы, и в том случае, если формула приписана не корректно или элементы уравнения имеют разные форматы, либо указаны с ошибкой, например текстовое значение в ячейке вместо цифрового, появляется такой символ. Ксарф­акс [156K] 6 лет назад  Такая ошибка, как #ЗНАЧ, в программе Microsoft Excel является довольно распространённой. Если вы ввели формулу, сделали ссылки на ячейки, а после этого вместо значения появилась подобная ошибка, то причин может быть несколько. Обычно такая проблема бывает, когда в ячейках, на которые вы ссылаетесь в формуле, находятся данные разных типов. 1) Если вы выполняете математические действия, а в одной из ячеек текстовое значение. 2) Частный случай — когда вы в ячейку внесли дробное число «0.1» вместо «0,1», то появится ошибка #ЗНАЧ. Вообще, имеется возможность посмотреть, на каком этапе вычисления произошла ошибка и после этого, обратить внимание именно на ту ячейку, при работе с которой возникла эта проблема. Иногда может потребоваться удалить лишние пробелы в ячейках с числами (например, если вы импортировали эти данные) или какие-то специальные символы. В случае с ошибкой при вычитании дат нужно привести их к одинаковому формату. Также, если в книге вы используете подключение к данным, но в данный момент не удаётся получить доступ, то тоже появится подобная ошибка. Maste­r key 111 [75.2K] 6 лет назад  Чаще всего запись #ЗНАЧ! в ячейке таблицы Microsoft Excel говорит об ошибке при вводе данных следует проверить совпадение типов в операциях, то есть все что вы ввели, скорее всего вы просто забыли поставить какой-то знак, или символ, а может ввели что-то лишнее, попробуйте внимательно найти ошибку и тогда запись #ЗНАЧ пропадет. galva­nna [52.7K] 6 лет назад  Программа Excel значительно облегчает многие трудоёмкие процессы в работе, поэтому желательно умело пользоваться ей. Неопытного пользователя может поставить в затруднительное положение неожиданно появившаяся запись в ячейке таблицы, вроде этой #ЗНАЧ!. Не стоит паниковать — это Excel сообщает нам о допущенной нами же ошибке. Есть и другие сообщения, указывающие на ошибки пользователя, например #ДЕЛ/0. Это сообщение появляется тогда, когда пользователь пытается разделить на 0,то есть в качестве делителя он пытается задействовать пустую ячейку или ячейку, содержащую значение равное 0. Одним словом, подобными сообщениями Excel указывает на не верные действия с нашей стороны. Как выглядят сообщения Excel об ошибке и, что следует предпринять для их устранения, можно узнать из этой статьи,например. Искат­ель прикл­ючени­й [102K] 6 лет назад  Добрый день. В программе «Excel» ошибка «#ЗНАЧ!» может встречается, когда вы пытаетесь сложить два значения, которые находятся в разных форматах. Например, в одной ячейки стоит число «5», а в другом тест «5 00»,тогда и выйдет такая ошибка, чтобы её избежать нужно привести все значения в один формат. Отформатировать ячейку, поставив везде формат «число», и проверить нет ли чисел с пробелами, тогда ошибка не должна возникать. morel­juba [62.5K] 6 лет назад  Наиболее распрастранён тот случай, когда такая запись вывскакивает при отсутсвии места для значения — то есть не влезает просто-напросто результат в ячейку, её нужно раздвинуть и всё будет нормально. Если дело не в этом, то некоректно были введены вами данные, нужно проверить точность формулы. А при наведении на эту надпись подсказка об ошибке будет перед вами. Татья­на171­8685 [2.7K] 9 лет назад  Найдите описание используемых функций и проверьте условия возврата значения #ЗНАЧ!.В Microsoft Excel операндами могут быть значения, ссылки на ячейки, имена, метки и функции… A5 содержит число, а ячейка A6 содержит текст «Недоступно», формула =A5+A6 возвратит значение ошибки #ЗНАЧ… Ksyus­ha26 [26.8K] 8 лет назад  Чаще всего такая запись выплывает когда вы не совсем корректно вводите данные. Возможно, что забыли знак какой-нибудь поставить, либо не то, что-то поставили. Также может всплыть такое значение, когда ячейка мала по размеру для результата. Для этого просто растяните ячейку ворчу­нов [106K] 6 лет назад  ![Допустим в программе Exel мы прописываем формулу вычисления. На простом примере умножаем число из клетки В3 на число из клетки С4. По неаккуратности к одному из чисел цепляем знак и букву. В результате программа нас не понимает и выдает надпись #ЗНАЧ! Знаете ответ?