Sdscompany.ru

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

Ошибки в экселе

Microsoft Excel

трюки • приёмы • решения

Какие существуют ошибки в Excel и как их исправлять

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

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

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

Обработка ошибок с помощью функции ЕОШИБКА()

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

Рис. 1. Предупреждающее сообщение о неправильно расставленных скобках

Функция если вернет ошибку (например, сообщение), если при расчете появляется ошибка. Например, рассмотрим следующую формулу: =ЕСЛИ(ЕОШИБКА(А1/А2);»»; А1/А2) . При возникновении ошибки (деление на 0) формула возвращает пустую строку. Если же ошибки не возникает, возвращается само выражение А1/А2 .

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

Обзор ошибок, возникающих в формулах 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!

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.

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

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

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

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

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

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

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

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

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

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

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

Ошибки Excel 2007

Я ввожу формулу, но вместо результата я вижу текст #Н/Д или #####. Что это значит?

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

Ошибка #####

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

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

Ошибка #Н/Д

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

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

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

Ошибка #ДЕЛ/0! Означает, что в процессе вычисления происходит деление на ноль. Причины могут быть следующие:

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

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

Эта ошибка возникает, когда в условиях функции указано два пересекающихся диапазона, но на самом деле они не пересекаются.

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

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

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

Данная ошибка возникает, если в формуле или функции указаны некорректные числовые значения. Например:

  • В функции, которая использует числовой аргумент, в его качестве указан текст или ссылка на ячейку с нечисловым значением.
  • Функция, которая выполняет итерации (вычисление, которое повторяется до тех пор, пока не будет выполнено заданное условие, например ВСД или СТАВКА) не может вычислить результат (например, заданное условие не наступило).
  • Результатом вычисления стало число, слишком большое или слишком малое для использования в Excel. Напоминаем, что максимальное число в Excel не может превышать 10307 и быть меньше минус 10307.

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

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

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

Такая ошибка возникает при использовании неверного типа аргумента или операнда. Например, пользователь может ошибиться в следующем:

  • В формулу вместо числа или логического значения (ИСТИНА или ЛОЖЬ) введен текст.
  • После ввода или редактирования формулы массива нажимается клавиша ВВОД вместо комбинации CTRL+SHIFT+ВВОД.
  • Ссылка, формула или функция указана как массив.
  • В качестве условия функции, который требует единичное значение, указан диапазон.

Я понял, что означает ошибка. Но моя формула очень большая/работает с большим объемом данных. Как мне найти место, где допущена ошибка?

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

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

У меня ошибка #ИМЯ? и я не могу найти её причину.

  • Проверьте правильность написания имени (названия) функции. Для этого можно вызвать Мастер функций (группа Библиотека функций вкладки Формулы), и выбрать нужную функцию из предлагаемого списка.
  • В формулу был введен текст, не заключенный в двойные кавычки. Допустим, результатом формулы должен был быть текст с числом — например, Итого 200 р. Если при введении текста Итого в формулу этот текст не заключен в двойные кавычки, Excel интерпретирует такие данные как имя, что вызывает ошибку.

Примечание: Имя — это слово, словосочетание или другой набор знаков, заданные пользователем и обозначающие ячейку, диапазон ячеек, формулу или константу.

  • В формуле или функции используется имя, которое не было определено. Проверьте, что данное имя существует. На вкладке Формулы в группе Именованные ячейки выберите Диспетчер имен и выясните, присутствует ли данное имя в списке. Если данное имя отсутствует, его можно добавить его путем выбора команды Создание имени.
  • Была допущена ошибка в написании имени. Для проверки правильности указания имен в строке формул нужно нажать клавишу F3, щелкнуть требуемое имя, а затем нажать ОК.
  • В формуле или функции используется ссылка на другой лист, не заключенный в ординарные кавычки. Если имя другого листа или книги содержит небуквенные знаки или пробел, то это имя необходимо заключить в апострофы ( ‘ ).

У меня огромная база данных клиентов — покупателей. Каждый день в базе данных добавляется столбец, в котором указывается сумма выручки от каждого покупателя. Если покупатель ничего не купил, в ячейке ставится #Н/Д. Как мне просуммировать столбец, если там есть такие данные?

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

Но если основную таблицу изменить невозможно (по разным причинам), эту проблему можно попробовать решить с помощью функции БДСУММ.

Допустим, исходная таблица имеет следующий вид:

Применив функцию БДСУММ, мы сможем выбрать для суммирования те ячейки, которые не содержат значения #Н/Д. Функция БДСУММ имеет следующий синтаксис:

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

То есть, в функции нам нужно задать суммирование первого столбца нашей таблицы, при условии, что значение ячейки этого столбца не равно #Н/Д. Для этого сначала создадим условие — в любом удобном месте рабочего листа напишем условие <>#Н/Д и озаглавим эту ячейку так же как и столбец нужной нам базы данных.

Далее мы вводим следующую формулу:

  • C2:C18 — наша таблица,
  • E2:E3 — наше условие.

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

Наш результат указан под таблицей:

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

Этот способ также может использоваться для функций СЧЁТ, БСЧЁТА, ДМАКС, ДМИН, БДПРОИЗВЕД и им подобных.

В начало страницы

В начало страницы

Ошибки в 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 – Разница дат и часов не должна быть отрицательной

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

Читать еще:  Ошибка числа или значения
Ссылка на основную публикацию
Adblock
detector