Ошибка при вычислении функции в excel

Ошибка #ЗНАЧ! в Excel означает: «Формула вводится неправильно. Или что-то не так с ячейками, на которые указывают ссылки». Эта ошибка возникает в самых разных случаях, и найти ее точную причину может быть сложно. Сведения на этой странице включают распространенные проблемы и решения ошибки.

Используйте раскрывающийся список ниже или перейдите к одной из других областей:

  • Проблемы с вычитанием

  • Проблемы с пробелами и текстом

  • Другие решения

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

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

Женщина за столом с ноутбуком

Проблемы с вычитанием

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

Вычтите одну ссылку на ячейку из другой

Ячейка D2 со значением 2000,00 ₽, ячейка E2 со значением 1500,00 ₽, ячейка F2 с формулой =D2-E2 и результатом 500,00 ₽

Введите два значения в двух отдельных ячейках. В третьей ячейке вычтите одну ссылку на ячейку из другой. В этом примере ячейка D2 содержит плановую сумму, а ячейка E2 — фактическую. F2 содержит формулу =D2-E2.

Или используйте функцию СУММ с положительными и отрицательными числами

Ячейка D6 со значением 2000,00 ₽, ячейка E6 со значением 1500,00 ₽, ячейка F6 с формулой =СУММ(D6;E6) и результатом 500,00 ₽

Введите положительное значение в одной ячейке и отрицательное — в другой. В третьей ячейке используйте функцию СУММ, чтобы сложить две ячейки. В этом примере ячейка D6 содержит плановую сумму, а ячейка E6 — фактическую как негативное число. F6 содержит формулу =СУММ(D6;E6).

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

  1. Для начала выполните быструю проверку. В новой книге введите 2 в ячейке A1. Введите 4 в ячейке B1. Затем введите формулу =B1-A1 в ячейке C1. Если возникнет ошибка #ЗНАЧ! перейдите к следующему шагу. Если сообщение об ошибке не появилось, попробуйте другие решения на этой странице.

  2. В Windows откройте панель управления «Региональные стандарты».

    • Windows 10. Встроке поиска введите регион и выберите панель управления Региональные стандарты.

    • Windows 8. На начальном экране введите регион, щелкните Параметры и выберите пункт Региональные стандарты.

    • Windows 7. Нажмите кнопку Пуск, введите регион и выберите пункт Язык и региональные стандарты.

  3. На вкладке Форматы нажмите кнопку Дополнительные параметры.

  4. Найдите пункт Разделитель элементов списка. Если в поле разделителя элементов списка указан знак «минус», замените его на что-то другое. Например, разделителем нередко выступает запятая. Также часто используется точка с запятой. Однако для вашего конкретного региона может подходить другой разделитель элементов списка.

  5. Нажмите кнопку ОК.

  6. Откройте книгу. Если ячейка содержит ошибку #VALUE!, дважды щелкните ее для редактирования.

  7. Если там, где для вычитания должны быть знаки «минус», стоят запятые, замените их на знаки «минус».

  8. Нажмите клавишу ВВОД.

  9. Повторите эти действия для других ячеек, в которых возникает ошибка.

Вычтите одну ссылку на ячейку из другой

Ячейка D10 со значением 01.01.2016, ячейка E10 со значением 24.04.2016, ячейка F10 с формулой =E10-D10 и результатом 114

Введите две даты в двух отдельных ячейках. В третьей ячейке вычтите одну ссылку на ячейку из другой. В этом примере ячейка D10 содержит дату начала, а ячейка E10 — дату окончания. F10 содержит формулу =E10-D10.

Или используйте функцию РАЗНДАТ

Ячейка D15 со значением 01.01.2016, ячейка E15 со значением 24.04.2016, ячейка F15 с формулой =РАЗНДАТ(D15;E15;"d") и результатом 114

Введите две даты в двух отдельных ячейках. В третьей ячейке используйте функцию РАЗНДАТ, чтобы найти разницу дат. Дополнительные сведения о функции РАЗНДАТ см. в статье Вычисление разницы двух дат.

Растяните столбец по ширине. Если значение выравнивается по правому краю — это дата. Но если оно выравнивается по левому краю, это значит, что в ячейке на самом деле не дата. Это текст. И Excel не распознает текст как дату. Ниже приведены некоторые решения, которые помогут решить эту проблему.

Проверка наличия начальных пробелов

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

  2. Разместите курсор в начале и посмотрите, можно ли выбрать один или несколько пробелов. Вот как выглядит выбранный пробел в начале ячейки: Ячейка, в которой выделен пробел перед значением 01.01.2016

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

  3. Выделите столбец, содержащий дату, щелкнув его заголовок.

  4. Выберите Данные > Текст по столбцам.

  5. Дважды нажмите кнопку Далее.

  6. На шаге 3 из 3 в мастере в разделе Формат данных столбца установите переключатель дата.

  7. Выберите формат даты и нажмите кнопку Готово.

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

Проверка параметров даты на компьютере

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

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

Существует два решения этой проблемы: Вы можете изменить систему дат, которая используется на компьютере, чтобы она соответствовала системе дат, которая нужна в Excel. Или в Excel можно создать новый столбец и использовать функцию ДАТА, чтобы создать настоящую дату на основе даты в текстовом формате. Вот как это сделать, если система дат вашего компьютера — дд.мм.гггг, а в ячейке A1 записан текст 12/31/2017.

  1. Создайте такую формулу: =ДАТА(ПРАВСИМВ(A1;4);ЛЕВСИМВ(A1;2);ПСТР(A1;4;2))

  2. Результат будет 31.12.2017.

  3. Чтобы использовать формат дд.мм.гг, нажмите клавиши CTRL+1 (или Изображение значка кнопки команд в Mac + 1 на Mac).

  4. Выберите другой языковой стандарт, в котором используется формат дд.мм.гг, например Немецкий (Германия). После применения формата результат будет 31.12.2017, причем это будет настоящая дата, а не ее текстовая запись.

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

Проблемы с пробелами и текстом

Часто ошибка #ЗНАЧ! возникает, потому что формула ссылается на другие ячейки, содержащие пробелы или (что еще сложнее) скрытые пробелы. Из-за этих пробелов ячейка может выглядеть пустой, хотя на самом деле таковой не является.

1. Выберите ячейки, на которые указывают ссылки

Выделен столбец

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

2. Найдите и замените

Вкладка "Главная" > Найти и выделить > Заменить

На вкладке Главная щелкните Найти и выделить > Заменить.

3. Удалите пробелы

Поле "Найти" с пробелом, поле "Заменить на" с пустым значением

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

4. Замените одно или все вхождения

Кнопка "Заменить все"

Если вы уверены, что следует удалить все пробелы в столбце, нажмите кнопку Заменить все. Если вы хотите просмотреть и удалить пробелы по отдельности, можно сначала нажать кнопку Найти далее, а затем — Заменить, когда вы будете уверены, что пробел не нужен. После этого ошибка #ЗНАЧ! должна исчезнуть. Если нет — перейдите к следующему шагу.

5. Включите фильтр

Главная > Сортировка и фильтр > Фильтр

Иногда из-за скрытых символов (а не просто пробелов) ячейка может выглядеть пустой, хотя на самом деле таковой не является. Например, это может происходить из-за одинарных кавычек в ячейке. Чтобы убрать эти символы из столбца, включите фильтр, последовательно выбрав Главная > Сортировка и фильтр > Фильтр.

6. Установите фильтр

Меню "Фильтр", в котором снят флажок "Выбрать все" и установлен флажок "(Пустые)"

Щелкните стрелку фильтра Стрелка фильтра и снимите флажок Выделить все. Затем установите флажок Пустые.

7. Установите все флажки без названия

Установлен флажок без названия

Установите все флажки, напротив которых ничего не указано, как на этом рисунке.

8. Выделите пустые ячейки и удалите их

Выделены отфильтрованные пустые ячейки

Если Excel вернет пустые ячейки, выделите их. Затем нажмите клавишу DELETE. Все скрытые символы в ячейках будут удалены.

9. Очистите фильтр

Меню "Фильтр", команда "Удалить фильтр с"

Щелкните стрелку фильтра Стрелка фильтра и выберите команду Удалить фильтр из… для отображения всех ячеек.

10. Результат

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

Если причиной появления ошибки #ЗНАЧ! были пробелы, вместо ошибки отобразится результат формулы, как показано в нашем примере. Если нет — повторите эти действия для других ячеек, на которые ссылается формула. Или попробуйте другие решения на этой странице.

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

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

Пример с ошибкой #ЗНАЧ!

Ячейка H4 с формулой =E2+E3+E4+E5 и результатом #ЗНАЧ!

Вот пример формулы с ошибкой #ЗНАЧ! . Ошибка, скорее всего, возникает из-за ячейки E2. Здесь есть специальный знак, который выглядит как небольшой прямоугольник «00». Или, как показано на следующем рисунке, можно использовать функцию ЕТЕКСТ в отдельном столбце для поиска текста.

Этот же пример с функцией ЕТЕКСТ

Ячейка F2 с формулой =ЕТЕКСТ(F2) и результатом ИСТИНА

Здесь в столбец F добавлена функция ЕТЕКСТ. Все ячейки в порядке, кроме одной со значением ИСТИНА. Это значит, что ячейка E2 содержит текст. Чтобы решить эту проблему, можно просто удалить содержимое ячейки и еще раз ввести число 1865,00. Вы также можете использовать функцию ПЕЧСИМВ, чтобы убрать символы, или функцию ЗАМЕНИТЬ, чтобы заменить специальные знаки на другие значения.

Использовав функцию ПЕЧСИМВ или ЗАМЕНИТЬ, вы можете скопировать результат в буфер обмена, а затем выбрать Главная > Вставить > Специальная вставка > Значения. Кроме того, может потребоваться преобразовать числа из текстового формата в числовой.

Формулам с математическими операторами (такими как + и *) не всегда удается вычислить ячейки, содержащие текст или пробелы. В таком случае попробуйте использовать вместо них функцию. Функции часто пропускают текстовые значения и определяют все значения как числовые, избегая ошибки #ЗНАЧ! . Например, вместо =A2+B2+C2 введите =СУММ(A2:C2). Или вместо =A2*B2 введите =ПРОИЗВЕД(A2,B2).

Другие решения

Выберите ошибку

Ячейка H4 с формулой =E2+E3+E4+E5 и результатом #ЗНАЧ!

Сначала выделите ячейку с ошибкой #ЗНАЧ! .

Щелкните «Формулы» > «Вычислить формулу»

Диалоговое окно "Вычисление формулы" с формулой " "+E3+E4+E5

Щелкните Формулы > Вычислить формулу > Вычислить. Excel обработает каждую часть формулы по отдельности. В данном случае формула =E2+E3+E4+E5 выдает ошибку из-за скрытого пробела в ячейке E2. Пробела не видно, если смотреть на ячейку E2. Но его можно увидеть здесь. Он отображается как » «.

Иногда вам может быть нужно вместо ошибки #ЗНАЧ! выводить что-то свое, например собственный текст, ноль или пустую ячейку. В этом случае можно добавить в формулу функцию ЕСЛИОШИБКА. Она проверяет, есть ли ошибка, и если да, заменяет ее другим значением по вашему выбору. Если ошибки нет, вычисляется исходная формула. Функция ЕСЛИОШИБКА работает только в Excel 2007 и более поздних версиях. В предыдущих версиях можно использовать функцию ЕСЛИ(ЕОШИБКА()).

Предупреждение: Функция ЕСЛИОШИБКА скроет все ошибки, а не только ошибку #ЗНАЧ! . Ошибки не рекомендуется скрывать, так как они часто указывают на то, что какое-то значение нужно исправить, а не просто скрыть. Используйте эту функцию, только если вы абсолютно уверены, что формула работает правильно.

Ячейка с ошибкой #ЗНАЧ!

Ячейка H4 с формулой =E2+E3+E4+E5 и результатом #ЗНАЧ!

Вот пример формулы, в которой ошибка #ЗНАЧ! вызвана скрытым пробелом в ячейке E2.

Ошибка, скрытая функцией ЕСЛИОШИБКА

Ячейка H4 с формулой =ЕСЛИОШИБКА(E2+E3+E4+E5;"--")

А вот эта же формула с добавленной функцией ЕСЛИОШИБКА. Ее можно прочитать как «Вычислить формулу, но если возникнет какая-либо ошибка, заменить ее двумя дефисами.» Помните, что также можно использовать «», чтобы ничего не отображать вместо двух дефисов. Или вы можете подставить свой текст, например: «Ошибка суммирования».

К сожалению, как вы видите, функция ЕСЛИОШИБКА не устраняет ошибку, а только скрывает ее. Так что используйте ее, если точно уверены, что ошибку лучше скрыть, чем исправить.

В какой-то момент подключения к данным могут стать недоступны. Чтобы исправить ошибку, восстановите подключение или, если это возможно, импортируйте данные. Если у вас нет доступа к подключению, попросите автора книги создать для вас новый файл. В идеале в нем будут только значения (без подключений). Для этого можно скопировать все ячейки и вставить только значения, щелкнув Главная > Вставить > Специальная вставка > Значения. При этом будут удалены все формулы и подключения, а ошибки #ЗНАЧ! исчезнут.

См. также

Полные сведения о формулах в Excel

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

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

Ошибка #ИМЯ?

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

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

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

1-oshibki-v-excel

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

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

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

2-oshibki-v-excel

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

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

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

3-oshibki-v-excel

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

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

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

4-oshibki-v-excel

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

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

5-oshibki-v-excel

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

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

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

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

6-oshibki-v-excel

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

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

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

7-oshibki-v-excel

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

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

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

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

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

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

8-oshibki-v-excel

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

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

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

9-oshibki-v-excel

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

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

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

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

10-oshibki-v-excel

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

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

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

11-oshibki-v-excel

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

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

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

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

12-oshibki-v-excel

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

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

Ошибка #Н/Д

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

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

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

13-oshibki-v-excel

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

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

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

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

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

14-oshibki-v-excel

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

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

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

15-oshibki-v-excel

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

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

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

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

16-oshibki-v-excel

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

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

Ошибка ####

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

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

17-oshibki-v-excel

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

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

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

18-oshibki-v-excel

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

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

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

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

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

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

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

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

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

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

Рисунок 1

Ошибки в Excel

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

Рисунок 2

Ошибки в Excel

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

Рисунок 3

image

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

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

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

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

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

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

Рисунок 4

ошибки в Excel

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

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

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

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

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

Рисунок 5

image

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

Рисунок 6

image

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

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

Рисунок 7

ошибки в Excel

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

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

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

Рисунок 8

image

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

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

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

Рисунок 9

image

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

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

Рисунок 10

image

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

Рисунок 11

image

Рисунок 12

image

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

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

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

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

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

А можно так

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

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

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

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

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

Содержание

  1. Несоответствие открывающих и закрывающих скобок
  2. Ячейка заполнена знаками решетки
  3. Ошибка #ДЕЛ/0!
  4. Ошибка #Н/Д
  5. Ошибка #ИМЯ?
  6. Ошибка #ПУСТО!
  7. Ошибка #ЧИСЛО!
  8. Ошибка #ССЫЛКА!
  9. Ошибка #ЗНАЧ!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ошибка #Н/Д

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

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

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

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

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

Ошибка #ИМЯ?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Содержание

  • Как исправить ошибку #ПЕРЕНОС! в Excel
  • Как исправить ошибку #ЗНАЧ! в Excel
  • Как исправить ошибку #ПУСТО! в Excel
  • Как исправить ошибку #ИМЯ? в Excel

Как исправить ошибку #ПЕРЕНОС! в Excel

Прежде чем рассмотреть ошибку #ПЕРЕНОС! (#SPILL), рассмотрим, что такое перенос. В Excel это означает, что формула возвращает несколько значений (массив), и они автоматически переносятся в соседние ячейки.

Диапазон переноса и значения внутри будут изменяться при обновлении источника данных.

Ошибка #ПЕРЕНОС! возникает, когда формула возвращает несколько значений, но Excel не может вывести один или несколько результатов.

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

  • Веб-версия: наведите мышь на зеленый треугольник в верхнем левом углу ячейки с ошибкой #ПЕРЕНОС!. Появится сообщение с описанием ошибки.
  • Десктопная версия: щелкните по ячейке с ошибкой #ПЕРЕНОС!. Нажмите на треугольник, который появится слева от ячейки. Причина ошибки будет указана в верхней части меню справки.

Рассмотрим решения, которые подойдут для наиболее распространенных вариаций этой ошибки.

Диапазон для переноса содержит одну или более ячеек с значениями

Решение: очистить диапазон для переноса.

распространенные ошибки в Excel, как исправить ошибку#ПЕРЕНОС! (#SPILL)

Диапазон для переноса находится внутри таблицы

Решение 1: преобразовать таблицу в диапазон данных.

Для этого выполните следующие шаги:

  1. нажмите на любую ячейку в таблице,
  2. в меню в верхней части окна выберите «Конструктор»,
  3. выберите команду «Преобразовать в диапазон».

распространенные ошибки в Excel, как исправить ошибку#ПЕРЕНОС! (#SPILL)

Диапазон для переноса находится внутри таблицы

Решение 1: преобразовать таблицу в диапазон данных.

Для этого выполните следующие шаги:

  1. нажмите на любую ячейку в таблице,
  2. в меню в верхней части окна выберите «Конструктор»,
  3. выберите команду «Преобразовать в диапазон».

распространенные ошибки в Excel, как исправить ошибку#ПЕРЕНОС! (#SPILL)

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

Решение 2: переместить формулу за границы таблицы.

Диапазон для переноса содержит объединенные ячейки

Решение: разделить ячейки внутри диапазона.

как разделить ячейки внутри диапазона, Excel

Как исправить ошибку #ЗНАЧ! в Excel

Ошибка #ЗНАЧ! (#VALUE) возникает в следующих случаях:

  • что-то не так с ячейкой (ячейками), на которую ссылается формула,
  • что-то не так с самой формулой.

Иногда найти источник проблемы не просто. Ниже — о самых распространенных случаях и как и их исправить.

Математическая формула ссылается на текст

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

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

Например, на примере ниже функция =СУММ(B2,B11) будет игнорировать текст в ячейке B11.

Как исправить ошибку #ЗНАЧ! в Excel

Как исправить ошибку #ЗНАЧ! в Excel

Ошибка #ЗНАЧ! (#VALUE) возникает в следующих случаях:

  • что-то не так с ячейкой (ячейками), на которую ссылается формула,
  • что-то не так с самой формулой.

Иногда найти источник проблемы не просто. Ниже — о самых распространенных случаях и как и их исправить.

Математическая формула ссылается на текст

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

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

Например, на примере ниже функция =СУММ(B2,B11) будет игнорировать текст в ячейке B11.

Как исправить ошибку #ЗНАЧ! в Excel

Скриншот: Zapier.com

Стоит помнить, что эта функция не оповещает автоматически о пропусках ячеек, и ее результат может вводить в заблуждение.

Одна или более ячеек содержат пробелы

В этих случаях ячейка выглядит пустой.

Решение: найти и заменить пробелы. Вот как это сделать:

  1. выделите диапазон ячеек, к которому обращается формула;
  2. нажмите на иконку с биноклем, нажмите «Найти и выделить»;
  3. в окне «Найти и заменить» выберите «Заменить»;
  4. в поле «Найти» вставьте пробелы;
  5. поле «Заменить на» оставьте пустым;
  6. нажмите «Заменить все».

Как исправить ошибку #ПУСТО! в Excel

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

Ячейка, на которую ссылается формула, удалена

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

Решение 1: Если ячейки удалены случайно, отмените это действие.

Решение 2: Обновите формулу, чтобы она ссылалась на диапазон ячеек. В этом случае Excel сможет произвести вычисления, даже если одна из ячеек была удалена.

Формула содержит относительные ссылки

Относительная ссылка означает, что используемые данные привязаны к ячейке, в которую вставлена формула. Например, если формулу =СУММ(B2:E2) скопировать из ячейки G2 в G3, Excel предположит, что должен суммировать все ячейки из колонок B-E в ряду 3.

Относительные и абсолютные ссылки в Excel

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

Например, если формулу =СУММ(G2:G7) перенести в ячейку I4, Excel предположит, что пользователю требуется сложить шесть ячеек над клеткой I4. В данном случае это невозможно, так как доступны лишь три ячейки.

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

Например, формула =СУММ(G2:G7) с абсолютными ссылками будет выглядеть как: =СУММ($G$2:$G$7).

Как исправить ошибку #ИМЯ? в Excel

Ошибка #ИМЯ? (#NAME) возникает, если название формулы неверно написано. Рассмотрим основные решения проблемы.

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

Решение: обновить название формулы. Лучший способ избегать опечаток — использовать встроенный редактор формул Excel. Когда пользователь начинает печатать название формулы, программа автоматически предложит список названий, содержащих те же буквы.

Неверное название формулы

Иногда пользователи вводят название несуществующей формулы или формулы, которая называется иначе (например, ПЛЮС вместо реальной функции СУММ). В этом случае формула вернет ошибку #ИМЯ?.

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

Вот как это сделать:

  1. выберите в меню раздел «Формулы»;
  2. нажмите на «Вставить функцию» в левой части панели инструментов, в открывшемся окне «Вставка функции» просмотрите недавно использовавшиеся функции или полный список всех доступных функций;
  3. выберите и вставьте нужную функцию;
  4. в редакторе формул обновите ячейки, к которым будет отсылаться формула (поля зависят от выбранной функции).

Эти советы применимы и к ошибкам в Google Таблицах (за исключением #ПЕРЕНОС!, которая часто отображается как #REF).

Источник.

Фото на обложке: Aihr.com

Подписывайтесь на наш Telegram-канал, чтобы быть в курсе последних новостей и событий!

Понравилась статья? Поделить с друзьями:
  • Ошибка при выполнении функции sign
  • Ошибка при вычислении условия для точки останова в строке
  • Ошибка при выполнении фонового задания длительныеоперации выполнитьсконтекстомклиента
  • Ошибка при вычислении 7 букв
  • Ошибка при выполнении файловой операции формат файла не поддерживается