Какая формула excel содержит ошибку - Исправление ошибок и поиск оптимальных решений проблем

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше

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

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

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

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

=3+1

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

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

Функции: это специальные формулы Excel, которые выполняют определенные вычисления. Например, функция ПИ() возвращает значение числа Пи: 3,142…
Ссылки: это ссылки на отдельные ячейки или диапазоны. Например, A2 возвращает значение ячейки A2.
Константы. Числа или текстовые значения, введенные непосредственно в формулу, например 2.
Операторы: оператор * (звездочка) служит для умножения чисел, а оператор ^ (крышка) — для возведения числа в степень. С помощью + и – можно складывать и вычитать значения, а с помощью / — делить их.

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

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

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

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

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

Рекомендация	Дополнительные сведения
Начинайте каждую формулу со знака равенства (=)	Если опустить знак равенства, введите текст или дату. Например, если ввести SUM(A1:A10),Excel отображает текстовую строку SUM(A1:A10) и не выполняет вычисление. Если ввести 11/2, Excel будет отображаться дата со 2 по нов (при условии, что формат ячейки — » Общие») вместо деления 11 на 2.
Следите за соответствием открывающих и закрывающих скобок	Все скобки должны быть парными (открывающая и закрывающая). Если в формуле используется функция, для ее правильной работы важно, чтобы все скобки стояли в правильных местах. Например, формула *=ЕСЛИ(B5<0);»Недопустимо»;B51,05) не будет работать, поскольку в ней две закрывающие скобки и только одна открывающая (требуется одна открывающая и одна закрывающая). Правильный вариант этой формулы выглядит так: =ЕСЛИ(B5<0;»Недопустимо»;B51,05)*.
Для указания диапазона используйте двоеточие	Указывая диапазон ячеек, разделяйте с помощью двоеточия (:) ссылку на первую ячейку в диапазоне и ссылку на последнюю ячейку в диапазоне. Например, =СУММ(A1:A5), а не =СУММ(A1 A5)), который возвращает #NULL! Ошибка.
Вводите все обязательные аргументы	У некоторых функций есть обязательные аргументы. Старайтесь также не вводить слишком много аргументов.
Вводите аргументы правильного типа	В некоторых функциях, например СУММ, необходимо использовать числовые аргументы. В других функциях, например ЗАМЕНИТЬ, требуется, чтобы хотя бы один аргумент имел текстовое значение. Если использовать в качестве аргумента данные неправильного типа, Excel может возвращать непредвиденные результаты или ошибку.
Число уровней вложения функций не должно превышать 64	В функцию можно вводить (или вкладывать) не более 64 уровней вложенных функций.
Имена других листов должны быть заключены в одинарные кавычки	Если формула содержит ссылки на значения или ячейки на других листах или в других книгах, а имя другой книги или листа содержит пробелы или другие небуквенные символы, его необходимо заключить в одиночные кавычки (‘), например: =’Данные за квартал’!D3 или =‘123’!A1.
Указывайте после имени листа восклицательный знак (!), когда ссылаетесь на него в формуле	Например, чтобы возвратить значение ячейки D3 листа «Данные за квартал» в той же книге, воспользуйтесь формулой =’Данные за квартал’!D3.
Указывайте путь к внешним книгам	Убедитесь, что каждая внешняя ссылка содержит имя книги и путь к ней. Ссылка на книгу содержит имя книги и должна быть заключена в квадратные скобки ([Имякниги.xlsx]). В ссылке также должно быть указано имя листа в книге. В формулу также можно включить ссылку на книгу, не открытую в Excel. Для этого необходимо указать полный путь к соответствующему файлу, например: =ЧСТРОК(‘C:My Documents[Показатели за 2-й квартал.xlsx]Продажи’!A1:A8). Эта формула возвращает количество строк в диапазоне ячеек с A1 по A8 в другой книге (8). Примечание: Если полный путь содержит пробелы, как в приведенном выше примере, необходимо заключить его в одиночные кавычки (в начале пути и после имени книги перед восклицательным знаком).
Числа нужно вводить без форматирования	Не форматируйте числа, которые вводите в формулу. Например, если нужно ввести в формулу значение 1 000 рублей, введите 1000. Если вы введете какой-нибудь символ в числе, Excel будет считать его разделителем. Если вам нужно, чтобы числа отображались с разделителями тысяч или символами валюты, отформатируйте ячейки после ввода чисел. Например, если для прибавления 3100 к значению в ячейке A3 используется формула =СУММ(3 100;A3), Excel не складывает 3100 и значение в ячейке A3 (как было бы при использовании формулы =СУММ(3100;A3)), а суммирует числа 3 и 100, после чего прибавляет полученный результат к значению в ячейке A3. Другой пример: если ввести =ABS(-2 134), Excel выведет ошибку, так как функция ABS принимает только один аргумент: =ABS(-2134).

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

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

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

Для Excel в Windows щелкните файл >параметры >формул или
Для Excel на Mac выберите меню Excel > параметры > проверки ошибок.

В Excel 2007 нажмите кнопку Microsoft Office и выберите Параметры Excel > Формулы.
В разделе Поиск ошибок установите флажок Включить фоновый поиск ошибок. Все найденные ошибки помечаются треугольником в левом верхнем углу ячейки.
Чтобы изменить цвет треугольника, которым помечаются ошибки, выберите нужный цвет в поле Цвет индикаторов ошибок.
В разделе Правила поиска ошибок установите или снимите флажок для любого из следующих правил:
- Ячейки, содержащие формулы, которые могут привести к ошибке: формула не использует ожидаемый синтаксис, аргументы или типы данных. Значения ошибок: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! и #VALUE!. Каждое из этих значений ошибок имеет разные причины и разрешается различными способами.
  
  Примечание: Если ввести значение ошибки прямо в ячейку, оно сохраняется как значение ошибки, но не помечается как ошибка. Но если на эту ячейку ссылается формула из другой ячейки, эта формула возвращает значение ошибки из ячейки.
- Несогласованная формула вычисляемого столбца в таблицах: вычисляемый столбец может содержать отдельные формулы, которые отличаются от формулы главного столбца, что создает исключение. Исключения вычисляемого столбца возникают при указанных ниже действиях.
  - Ввод данных, не являющихся формулой, в ячейку вычисляемого столбца.
  - Введите формулу в ячейку вычисляемого столбца, а затем нажмите клавиши CTRL +Z или нажмите кнопку «Отменить на панели быстрого доступа.
  - Ввод новой формулы в вычисляемый столбец, который уже содержит одно или несколько исключений.
  - Копирование в вычисляемый столбец данных, не соответствующих формуле столбца. Если копируемые данные содержат формулу, эта формула перезапишет данные в вычисляемом столбце.
  - Перемещение или удаление ячейки из другой области листа, если на эту ячейку ссылалась одна из строк в вычисляемом столбце.
- Ячейки, содержащие годы, представленные в виде двух цифр: ячейка содержит текстовую дату, которую можно неправильно представить как неправильное время при использовании в формулах. Например, дата в формуле =ГОД(«1.1.31») может относиться как к 1931, так и к 2031 году. Используйте это правило для выявления дат в текстовом формате, допускающих двоякое толкование.
- Числа, отформатированные как текст или предшествуют апострофу: ячейка содержит числа, хранящиеся в виде текста. Обычно это является следствием импорта данных из других источников. Числа, хранящиеся как текст, могут стать причиной неправильной сортировки, поэтому лучше преобразовать их в числовой формат. =СУММ(A1:A10) отображается как текст.
- Формулы, несогласованные с другими формулами в регионе: формула не соответствует шаблону других формул рядом с ней. Во многих случаях формулы, смежные с другими формулами, отличаются только используемыми ссылками. В следующем примере четырех смежных формул Excel отображает ошибку рядом с формулой =СУММ(A10:C10) в ячейке D4, так как смежные формулы приращены на одну строку, а одна увеличивается на 8 строк . Excel ожидает формулу =СУММ(A4:C4).
  
  Если используемые в формуле ссылки не соответствуют ссылкам в смежных формулах, приложение Microsoft Excel сообщит об ошибке.
- Формулы, которые пропускают ячейки в области: формула не может автоматически включать ссылки на данные, вставленные между исходным диапазоном данных и ячейкой, содержащую формулу. Это правило позволяет сравнить ссылку в формуле с фактическим диапазоном ячеек, смежных с ячейкой, содержащей формулу. Если смежные ячейки содержат дополнительные значения и не являются пустыми, Excel отображает рядом с формулой ошибку.
  
  Например, при использовании этого правила Excel отображает ошибку для формулы =СУММ(D2:D4), поскольку ячейки D5, D6 и D7, смежные с ячейками, на которые ссылается формула, и ячейкой с формулой (D8), содержат данные, на которые должна ссылаться формула.
- Незаблокированные ячейки, содержащие формулы: формула не заблокирована для защиты. По умолчанию все ячейки на листе блокируются, поэтому их нельзя изменить при защите листа. Это поможет избежать случайных ошибок, таких как случайное удаление или изменение формул. Эта ошибка указывает, что ячейка была разблокирована, но лист не был защищен. Убедитесь, что ячейка не заблокирована.
- Формулы, ссылаемые на пустые ячейки: формула содержит ссылку на пустую ячейку. Это может привести к неверным результатам, как показано в приведенном далее примере.
  
  Предположим, требуется найти среднее значение чисел в приведенном ниже столбце ячеек. Если третья ячейка пуста, она не используется в расчете, поэтому результатом будет значение 22,75. Если эта ячейка содержит значение 0, результат будет равен 18,2.
- Недопустимые данные, введенные в таблицу: в таблице есть ошибка проверки. Проверьте параметр проверки для ячейки, перейдя на вкладку «Данные» > data Tools > проверки данных.

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

Если диалоговое окно Поиск ошибок не отображается, щелкните вкладку Формулы, выберите Зависимости формул и нажмите кнопку Поиск ошибок.
Чтобы повторно проверить пропущенные ранее ошибки, щелкните Файл > Параметры > Формулы. Для Excel на Mac выберите меню Excel > параметры > проверки ошибок.

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

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

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

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

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

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

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

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

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

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

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

Примечание: Для каждой ячейки может быть только одно контрольное значение.

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

Выделите ячейки, которые хотите просмотреть.

Чтобы выделить все ячейки с формулами, на вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить (вы также можете нажать клавиши CTRL+G или CONTROL+G на компьютере Mac). Затем выберите Выделить группу ячеек и Формулы.
На вкладке Формулы в группе Зависимости формул нажмите кнопку Окно контрольного значения.
Нажмите кнопку Добавить контрольное значение.
Убедитесь, что вы выделили все ячейки, которые хотите отследить, и нажмите кнопку Добавить.
Чтобы изменить ширину столбца, перетащите правую границу его заголовка.
Чтобы открыть ячейку, ссылка на которую содержится в записи панели инструментов «Окно контрольного значения», дважды щелкните запись.

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

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

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

Чтобы выделить несколько ячеек, щелкните их, удерживая нажатой клавишу CTRL.
Нажмите кнопку Удалить контрольное значение.

Иногда трудно понять, как вложенная формула вычисляет конечный результат, поскольку в ней выполняется несколько промежуточных вычислений и логических проверок. Но с помощью диалогового окна Вычисление формулы вы можете увидеть, как разные части вложенной формулы вычисляются в заданном порядке. Например, формулу =ЕСЛИ(СРЗНАЧ(D2:D5)>50;СУММ(E2:E5);0) будет легче понять, если вы увидите промежуточные результаты:

В диалоговом окне «Вычисление формулы»	Описание
=ЕСЛИ(СРЗНАЧ(D2:D5)>50;СУММ(E2:E5);0)	Сначала выводится вложенная формула. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ. Диапазон ячеек D2:D5 содержит значения 55, 35, 45 и 25, поэтому функция СРЗНАЧ(D2:D5) возвращает результат 40.
=ЕСЛИ(40>50;СУММ(E2:E5);0)	Диапазон ячеек D2:D5 содержит значения 55, 35, 45 и 25, поэтому функция СРЗНАЧ(D2:D5) возвращает результат 40.
=ЕСЛИ(ЛОЖЬ;СУММ(E2:E5);0)	Поскольку 40 не больше 50, выражение в первом аргументе функции ЕСЛИ (аргумент лог_выражение) имеет значение ЛОЖЬ. Функция ЕСЛИ возвращает значение третьего аргумента (аргумент значение_если_ложь). Функция СУММ не вычисляется, поскольку она является вторым аргументом функции ЕСЛИ (аргумент значение_если_истина) и возвращается только тогда, когда выражение имеет значение ИСТИНА.

Выделите ячейку, которую нужно вычислить. За один раз можно вычислить только одну ячейку.
Откройте вкладку Формулы и выберите Зависимости формул > Вычислить формулу.
Нажмите кнопку Вычислить, чтобы проверить значение подчеркнутой ссылки. Результат вычисления отображается курсивом.

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

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

Примечания:

Некоторые части формул, в которых используются функции ЕСЛИ и ВЫБОР, не вычисляются. В таких случаях в поле Вычисление отображается значение #Н/Д.
Если ссылка пуста, в поле Вычисление отображается нулевое значение (0).
Некоторые функции вычисляются заново при каждом изменении листа, так что результаты в диалоговом окне Вычисление формулы могут отличаться от тех, которые отображаются в ячейке. Это функции СЛЧИС, ОБЛАСТИ, ИНДЕКС, СМЕЩ, ЯЧЕЙКА, ДВССЫЛ, ЧСТРОК, ЧИСЛСТОЛБ, ТДАТА, СЕГОДНЯ, СЛУЧМЕЖДУ.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

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

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

Нужна дополнительная помощь?

Источник

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

Ошибка #ИМЯ?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ошибка #Н/Д

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ошибка ####

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

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

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

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

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

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

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

Источник

Функция ЕСЛИОШИБКА – примеры формул

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

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

Итак, вот о чем мы поговорим:

Что означает функция Excel ЕСЛИОШИБКА
Если ошибка, то пусто
Если ошибка, то показать сообщение
5 фактов, которые нужно знать о функции ЕСЛИОШИБКА в Excel
ЕСЛИОШИБКА с функцией ВПР
Вложенные функции ЕСЛИОШИБКА для выполнения последовательных ВПР
ЕСЛИОШИБКА в формулах массива
ЕСЛИОШИБКА или ЕСЛИ + ЕОШИБКА?
ЕСЛИОШИБКА против ЕСНД
Рекомендации по использованию ЕСЛИОШИБКА в Excel

Что означает функция Excel ЕСЛИОШИБКА

Функция ЕСЛИОШИБКА (IFERROR по-английски) предназначена для обнаружения и устранения ошибок в формулах и вычислениях. Это значит, что функция ЕСЛИОШИБКА должна выполнить определенные действия, если видит какую-либо ошибку. Более конкретно, она проверяет формулу и, если вычисление дает ошибку, то она возвращает какое-то другое значение, которое вы ей укажете. Если же всё хорошо, то просто возвращает результат формулы.

Синтаксис функции Excel ЕСЛИОШИБКА следующий:

ЕСЛИОШИБКА(значение; значение_если_ошибка)

Где:

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

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

Рассмотрим простой пример:

Чтобы этого не произошло, используйте формулу ЕСЛИОШИБКА, чтобы перехватывать и обрабатывать их нужным вам образом.

Если ошибка, то пусто

Укажите пустую строку (“”) в аргументе значение_если_ошибка, чтобы вернуть пустую ячейку, если обнаружена ошибка:

=ЕСЛИОШИБКА(A4/B4; «»)

Вернемся к нашему примеру и используем ЕСЛИОШИБКА:

Как видите по сравнению с первым скриншотом, вместо стандартных сообщений мы видим просто пустые ячейки.

Если ошибка, то показать сообщение

Вы также можете отобразить собственное сообщение вместо стандартного обозначения ошибок Excel:

=ЕСЛИОШИБКА(A4/B4; «Ошибка в вычислениях»)

Перед вами – третий вариант нашей небольшой таблицы.

5 фактов, которые нужно знать о функции ЕСЛИОШИБКА в Excel

ЕСЛИОШИБКА в Excel обрабатывает все типы ошибок, включая #ДЕЛ/0!, #Н/Д, #ИМЯ?, #NULL!, #ЧИСЛО!, #ССЫЛКА! и #ЗНАЧ!.
В зависимости от содержимого аргумента значение_если_ошибка функция может заменить ошибки вашим текстовым сообщением, числом, датой или логическим значением, результатом другой формулы или пустой строкой (пустой ячейкой).
Если аргумент значение является пустой ячейкой, он обрабатывается как пустая строка (»’), но не как ошибка.
ЕСЛИОШИБКА появилась в Excel 2007 и доступна во всех последующих версиях Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021 и Excel 365.
Чтобы перехватывать ошибки в Excel 2003 и более ранних версиях, используйте функцию ЕОШИБКА в сочетании с функцией ЕСЛИ, например как показано ниже:

=ЕСЛИ(ЕОШИБКА(A4/B4);»Ошибка в вычислениях»;A4/B4)

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

ЕСЛИОШИБКА с функцией ВПР

Часто встречающаяся задача в Excel – поиск нужного значения в таблице в соответствии с определёнными критериями. И не всегда этот поиск бывает успешным. Одним из наиболее распространенных применений функции ЕСЛИОШИБКА является сообщение пользователям, что искомое значение не найдено в базе данных. Для этого вы заключаете формулу ВПР в функцию ЕСЛИОШИБКА примерно следующим образом:

ЕСЛИОШИБКА(ВПР( … );»Не найдено»)

Если искомое значение отсутствует в таблице, которую вы просматриваете, обычная формула ВПР вернет ошибку #Н/Д:

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

=ЕСЛИОШИБКА(ВПР(D3; $A$3:$B$5; 2;ЛОЖЬ); «Не найдено»)

На скриншоте ниже показан пример ЕСЛИОШИБКА вместе с ВПР в Excel:

Если вы хотите перехватывать только #Н/Д, но не все подряд ошибки, используйте функцию ЕНД вместо ЕСЛИОШИБКА. Она просто возвращает ИСТИНА или ЛОЖЬ в зависимости от появления ошибки #Н/Д. Поэтому нам здесь еще понадобится функция ЕСЛИ, чтобы обработать эти логические значения:

=ЕСЛИ(ЕНД(ВПР(D3; $A$3:$B$5; 2;ЛОЖЬ)); «Не найдено»;ВПР(D3; $A$3:$B$5; 2;ЛОЖЬ))

Дополнительные примеры формул Excel ЕСЛИОШИБКА ВПР можно также найти в нашей статье Как убрать сообщение #Н/Д в ВПР?

Вложенные функции ЕСЛИОШИБКА для выполнения последовательных ВПР

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

Предположим, у вас есть несколько отчетов о продажах из региональных отделений вашей компании, и вы хотите получить сумму по определенному идентификатору заказа. С ячейкой В9 в качестве критерия поиска (номер заказа) и тремя небольшими таблицами поиска (таблица 1, 2 и 3), формула выглядит следующим образом:

=ЕСЛИОШИБКА(ВПР(B9;A3:B6;2;0);ЕСЛИОШИБКА(ВПР(B9;D3:E6;2;0);ЕСЛИОШИБКА(ВПР(B9;G3:H6;2;0);»Не найден»)))

Результат будет выглядеть примерно так, как на рисунке ниже:

То есть, если поиск завершился неудачей (то есть, ошибкой) первой таблице, начинаем искать во второй, и так далее. Если нигде ничего не нашли, получим сообщение «Не найден».

ЕСЛИОШИБКА в формулах массива

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

Допустим, у вас есть Сумма в столбце B и Цена в столбце C, и вы хотите вычислить Количество. Это можно сделать с помощью следующей формулы массива, которая делит каждую ячейку в диапазоне B2:B4 на соответствующую ячейку в диапазоне C2:C4, а затем суммирует результаты:

=СУММ(($B$2:$B$4/$C$2:$C$4))

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

Чтобы исправить эту ситуацию, просто вложите деление внутрь формулы ЕСЛИОШИБКА:

=СУММ(ЕСЛИОШИБКА($B$2:$B$4/$C$2:$C$4;0))

Что делает эта формула? Делит значение в столбце B на значение в столбце C в каждой строке (3500/100, 2000/50 и 0/0) и возвращает массив результатов {35; 40; #ДЕЛ/0!}. Функция ЕСЛИОШИБКА перехватывает все ошибки #ДЕЛ/0! и заменяет их нулями. Затем функция СУММ суммирует значения в итоговом массиве {35; 40; 0} и выводит окончательный результат (35+40=75).

Примечание. Помните, что ввод формулы массива должен быть завершен нажатием комбинации Ctrl + Shift + Enter (если у вас не Office365 или Excel2021 – они понимают формулы массива без дополнительных телодвижений).

ЕСЛИОШИБКА или ЕСЛИ + ЕОШИБКА?

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

В старые недобрые времена Excel 2003 и более ранних версий, когда ЕСЛИОШИБКА не существовало, совместное использование ЕСЛИ и ЕОШИБКА было единственным возможным способом перехвата ошибок. Это просто немного более сложный способ достижения того же результата.

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

В Excel 2007 — Excel 2016:

ЕСЛИОШИБКА(ВПР( … ); «Не найдено»)

Во всех версиях Excel:

ЕСЛИ(ЕОШИБКА(ВПР(…)); «Не найдено»; ВПР(…))

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

А вот простой пример формулы Excel ЕСЛИ ЕОШИБКА ВПР:

=ЕСЛИ(ЕОШИБКА(ВПР(D2; A2:B5;2;ЛОЖЬ)); «Не найдено»; ВПР(D2; A2:B5;2;ЛОЖЬ ))

ЕСЛИОШИБКА против ЕСНД

Представленная в Excel 2013, ЕСНД (IFNA в английской версии) — это еще одна функция для проверки формулы на наличие ошибок. Его синтаксис похож на синтаксис ЕСЛИОШИБКА:

ЕСНД(значение; значение_если_НД)

Чем ЕСНД отличается от ЕСЛИОШИБКА? Функция ЕСНД перехватывает только ошибки #Н/Д, тогда как ЕСЛИОШИБКА обрабатывает все типы ошибок.

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

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

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

=ЕСНД(ВПР(F3; $A$3:$D$6; 4;ЛОЖЬ); «Не найдено»)

Или подойдет комбинация ЕСЛИ ЕНД для старых версий Excel:

=ЕСЛИ(ЕНД(ВПР(F3; $A$3:$D$6; 4;ЛОЖЬ));»Не найдено»; ВПР(F3; $A$3:$D$6; 4;ЛОЖЬ))

Как видите, формула ЕСНД с ВПР возвращает «Не найдено» только для товара, которого нет в таблице поиска (Сливы). Для персиков она показывает #ДЕЛ/0! что указывает на то, что наша таблица поиска содержит ошибку деления на ноль.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник.

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

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

Источник

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

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

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

Ячейка
с ошибкой в формуле

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

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

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

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

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

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

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

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

Ячейки, которые содержат
годы, представленные 2 цифрами. Ячейка
содержит дату в текстовом формате,
которая в случае использования в
формулах может быть отнесена к
неправильному веку. Например, дата в
формуле =ГОД(«1.1.31») может относиться
как к 1931-му, так и к 2031-му году. Это
правило служит для выявления неоднозначных
дат в текстовом формате.
Числа,
отформатированные как текст или с
предшествующим апострофом. Ячейка
содержит числа, хранящиеся в виде
текста. Обычно это является следствием
импорта данных из других источников.
Числа, хранящиеся в виде текста, могут
стать причиной неправильной сортировки,
поэтому лучше преобразовать их в
числовой формат.
Формулы,
несогласованные с остальными формулами
в области. Формула
не соответствует шаблону других смежных
формул. В большинстве случаев формулы,
расположенные в соседних ячейках,
отличаются только используемыми
ссылками. В приведенном, далее примере,
состоящем из четырех смежных формул,
приложение MicrosoftExcel
показывает ошибку в формуле =СУММ(A10:F10),
поскольку значения в смежных формулах
изменились на одну строку, а в формуле
=СУММ(A10:F10)
— на 8 строк. В данном случае, ожидаемой
формулой является =СУММ(A3:F3).
Если используемые в формуле
ссылки не соответствуют ссылкам в
смежных формулах, приложение
MicrosoftExcelвыводит
ошибку.
Формулы, не
охватывающие смежные ячейки. Формула
может не включать ссылки на данные,
вставленные между исходным диапазоном
и ячейкой с формулой. Это правило
позволяет сравнить ссылку в формуле
с фактическим диапазоном ячеек, смежных
с ячейкой формулы. Если смежные ячейки
содержат дополнительные значения и
не являются пустыми, MicrosoftExcelвыведет
рядом с формулой ошибку.

Например, в случае
применения этого правила приложение
MicrosoftExcel
выведет ошибку рядом с формулой
=СУММ(A2:A4),
поскольку между указанным в формуле
диапазоном ячеек и ячейкой с формулой
(A8) находятся заполненные ячейки A5, A6 и
A7, на которые также должна быть ссылка
в формуле.

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

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

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

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

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

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

В случае обнаружения
ошибок открывается диалоговое окно
Контроль
ошибок.

Чтобы повторно
проверить пропущенные ранее ошибки,
выполните указанные ниже действия.
1. Нажмите кнопку
  Параметры.
2. В разделе Контроль
  ошибок
  нажмите кнопку Сброс
  пропущенных ошибок.
3. Нажмите кнопку
  ОК.
4. Нажмите
  кнопкуПродолжить.

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

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

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

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

Нажмите кнопкуДалее.
Выполняйте эти
действия, пока проверка ошибок не будет
завершена.

К
началу страницы

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

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

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

Повторите два
предыдущих действия.

Исправление
значения ошибки

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

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

Ссылка на статью с подробным описанием	Описание
Исправление ошибки #####	Эта ошибка появляется в том случае, если ширины столбца недостаточно, чтобы показать все знаки в ячейке, или если ячейка содержит отрицательные значения даты или времени. Так, при вычислении формулы, которая вычитает более позднюю дату из более ранней, например =15.06.2008-01.07.2008 , получится отрицательное значение даты.
Исправление ошибки #ДЕЛ/0!	Эта ошибка появляется в том случае, если число делится на ноль (0) или на ячейку, в которой нет значения.
Исправление ошибки #ЗНАЧ!	Эта ошибка появляется в том случае, если формула содержит ячейки с разными типами данных. Если включена проверка ошибок в формулах, при наведении указателя мыши на смарт-тег появится всплывающая подсказка «Значение, используемое в формуле, имеет неправильный тип данных». Обычно для исправления этой ошибки достаточно внести в формулу небольшие изменения.
Исправление ошибки #ИМЯ?	Эта ошибка появляется в том случае, если текст в формуле не удается распознать. Например, имя диапазона или функции может иметь неверное написание.
Исправление ошибки #Н/Д	Эта ошибка появляется в том случае, если значение для функции или формулы недоступно.
Исправление ошибки #ПУСТО!	Эта ошибка появляется в том случае, если указано пересечение двух областей, которые на самом деле не пересекаются. Оператором пересечения служит пробел, который разделяет ссылки в формуле. Например, области A1:A2 и C3:C5 не пересекаются, и если ввести формулу =СУММ(A1:A2 C3:C5) , будет показана ошибка #ПУСТО!.
Исправление ошибки #ССЫЛКА!	Эта ошибка появляется в том случае, если ссылка на ячейку является неверной. Например, вы могли удалить ячейки, на которые ссылались другие формулы, или вставить перемещенные ячейки на место тех ячеек, на которые ссылались другие формулы.
Исправление ошибки #ЧИСЛО!	Эта ошибка появляется в том случае, если формула или функция содержит недопустимые числовые значения.

Вычисления
в таблицах

Ознакомление
с правилами ввода простых формул

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

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

Функции.
Функция ПИ() возвращает значение числа
Пи: 3,142…

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

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

Операторы.
Оператор ^ («крышка») возводит число
в степень, а оператор * («звездочка»)
перемножает два или более числа.

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

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

Убедитесь, что…	Дополнительные сведения
Каждая формула начинается со знака равенства (=)	Если знак равенства опустить, введенные значения могут быть отображены как текст или дата. Например, если ввести СУММ(A1:A10), MicrosoftExcel отобразит текстовую строку СУММ(A1:A10) и не станет вычислять значение формулы. Если ввести 11/2, Excel отобразит дату (например, «2 ноября» или «02.11.2009») вместо того, чтобы разделить 11 на 2.
Все открывающие и закрывающие скобки согласованы	Убедитесь, что у каждой скобки имеется соответствующая ей пара. Чтобы функция в формуле работала правильно, важно, чтобы каждая скобка стояла на своем месте. Например, формула *=ЕСЛИ(B5<0);»Неверно»;B51,05) работать не будет, потому что в ней две закрывающие и только одна открывающая скобка. Правильная формула будет выглядеть так: =ЕСЛИ(B5<0;»Неверно»;B51,05)*.
Для указания диапазона используется двоеточие	При указании ссылки на диапазон ячеек используйте двоеточие (:) в качестве разделителя между первой и последней ячейками диапазона. Например: A1:A5.
Введены все необходимые аргументы	Некоторым функциям листа требуются аргументы (Аргумент. Значения, используемые функцией для выполнения операций или вычислений. Тип аргумента, используемого функцией, зависит от конкретной функции. Обычно аргументы, используемые функциями, являются числами, текстом, ссылками на ячейки и именами.), тогда как другие функции (например, ПИ) аргументов не принимают. Кроме того, убедитесь, что вы не ввели слишком много аргументов. Например, функция ПРОПИСН принимает в качестве аргумента только одну строку текста.
Введены аргументы правильного типа	Для некоторый функций листа, например СУММ, требуются числовые аргументы. Другие функции, напримерЗАМЕНИТЬ, требуют текстового значения по меньшей мере для одного из аргументов. Прииспользования в качестве аргумента данных неверного типа можно получить неправильные результаты или ошибку.
Количество вложенных функций не превышает 64	Внутри одной функции можно ввести (или вложить) не более 64 уровней функций. Например, формула =ЕСЛИ(КОРЕНЬ(ПИ())<2;»Меньше двух!»;»Больше двух!») содержит три функции: функция ПИ вложена в функцию КОРЕНЬ, которая, в свою очередь, вложена в функцию ЕСЛИ.
Имена других листов заключены в одинарные кавычки	Если в формуле имеются ссылки на значения или ячейки других листов или книг, имена которых содержат небуквенные знаки, такие имена необходимо заключить в одинарные кавычки ( ‘ ).
Ссылаясь на лист в формуле, следует поместить после его имени восклицательный знак (!).	Например, чтобы вернуть значение ячейки D3 листа с именем «Данные за квартал» в той же книге, воспользуйтесь формулой =’Данные за квартал’!D3.
Включен путь к внешним книгам	Убедитесь, что каждая внешняя ссылка (Внешняя ссылка. Ссылка на ячейку или диапазон ячеек в другой книге Microsoft Excel или ссылка на имя, определенное в другой книге.) содержит имя книги и путь к ней. Ссылка на книгу включает в себя имя этой книги и должна быть заключена в квадратные скобки ([]). Кроме того, в ссылке должно быть указано имя листа в книге. Например, формула со ссылкой на ячейки с A1 по A8 на листе с именем «Продажи» в книге (в настоящий момент открытой в Excel) с именем Кв2 Операции.xlsx, будет выглядеть примерно так: =[Кв2 Операции.xlsx]Продажи!A1:A8. Если книга, на которую требуется сослаться, не открыта в Excel, ссылку на нее все же можно включить в формулу. Нужно указать полный путь к файлу, как в следующем примере: =ЧСТРОК(‘C:Мои документы[Кв2 Операции.xlsx]Продажи’!A1:A8). Эта формула возвращает число строк в диапазоне, включающем ячейки с A1 по A8 в другой книге (а именно, 8). Примечание Если полный путь содержит пробелы, как в предыдущем примере, его необходимо заключить в одинарные кавычки (в начале пути и после имени листа, перед восклицательным знаком).
Числа введены без форматирования	Не форматируйте числа, которые вводите в формулах. Например, если вам нужно ввести значение $1,000, введите в формулу просто 1000. Если ввести запятую как часть числа, Excel будет трактовать ее как разделительный знак. Если требуется, чтобы числа отображались с разделителями тысяч и миллионов или с символами валюты, отформатируйте ячейки после ввода чисел. Например, если нужно добавить 3100 к значению в ячейке A3, и вы ввели формулу =СУММ(3,100;A3), приложение Excel сложит числа 3 и 100, а затем добавит результат к значению ячейки A3, вместо того чтобы добавить 3100 к значению A3. А если введена формула =ABS(-2,134), будет показана ошибка, поскольку функция ABS принимает только один аргумент.
Избегайте деления на ноль	При попытке разделить значение в ячейке на значение в другой ячейке, содержащей ноль или не содержащей никакого значения, будет показана ошибка #ДЕЛ/0!.

Формулы

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

Применение
операторов в формулах

Оператор. Знак или
символ, задающий тип вычисления в
выражении. Существуют математические,
логические операторы, операторы сравнения
и ссылок

Арифметические
операторы.

Арифметическийоператор	Значение	Пример
+ (знак плюс)	Сложение	23+3
– (знак минус)	Вычитание / Унарный минус	35–166 / –1
* (звездочка)	Умножение	23*(-321)
/ (косая черта)	Деление	3/3
% (знак процента)	Процент	20%
^ (крышка)	Возведение в степень	3^2 (аналогично 3*3)

Операторы
сравнения.

Используются для
сравнения двух значений. Результатом
сравнения является логическое значение:
либо ИСТИНА, либо ЛОЖЬ.

Операторсравнения	Значение	Пример
= (знак равенства)	Равно	A1=B1
> (знак больше)	Больше	A1>B1
< (знак меньше)	Меньше	A1<B1
>= (знак больше и знак равенства)	Больше или равно	A1>=B1
<= (знак меньше и знак равенства)	Меньше или равно	A1<=B1
<> (знак «не равно»)	Не равно	A1<>B1

Текстовый оператор
конкатенации.
Операция (&) используется для объединения
нескольких текстовых строк в одну
строку.

Текстовый
оператор

Значение

Пример

&
(амперсанд)

Объединение
последовательностей символов в одну
последовательность.

Выражение

«Северный
» & » ветер» эквивалентно
строке

«Северный
ветер».

Оператор
ссылки.
Для описания ссылок на диапазоны ячеек
используются следующие операторы.

Оператор ссылки	Значение	Пример
: (двоеточие)	Оператор диапазона используется для ссылки на все ячейки между крайними ячейками диапазона, включая эти ячейки.	B5:B15
, (запятая)	Оператор объединения объединяет несколько ссылок в одну ссылку.	СУММ(B5:B15,D5:D15)
(пробел)	Оператор пересечения задает пересечение двух диапазонов.	A6:D6

Порядок выполнения
действий в формулах

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

	Оператор	Описание
	: (двоеточие) (пробел) , (запятая)	Операторы ссылок
	–	Унарный минус (например –1)
	%	Процент
	^	Возведение в степень
	* и /	Умножение и деление
	+ и –	Сложение и вычитание
	&	Объединение двух текстовых строк в одну
	= <><= >= <>	Сравнение

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

Ссылка
— это адрес объекта (ячейки, строки,
столбца, диапазона),
используемый при записи формулы.

Способы
ввода формул: набрать
ее

вручную

с
помощью указания

в
строке формул или непосредственно в
ячейке.

Строка
формул

— отмена
последнего действия,

—
для подтверждения ввода данных или
изменения содержимого ячеек,

—
активизировать Мастер функций для
работы с функциями.

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

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

#
#
#
#
#
#
#
#
#
#
#
23.08.2019159.74 Кб2оп.doc

Источник

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

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

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

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

Данная формула должна выполняться в массиве, поэтом после ее ввода для подтверждения нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter. Если все сделано правильно в строке формул появятся фигурные скобки, как на рисунке.

Таблица с большим объемом данных содержит ошибки, первая из которых находится в диапазоне третей строки листа 3:3.

Как получить адрес ячейки с ошибкой

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

Данная формула так же должна выполняться в массиве, поэтом после ее ввода снова для подтверждения жмем комбинацию клавиш CTRL+SHIFT+Enter.

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

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

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

С помощью функции ЕОШИБКА проверяется каждая ячейка в диапазоне A3:Z3 на наличие ошибок. На основании полученных результатов в памяти программы создается массив логических значений ИСТИНА и ЛОЖЬ. Следующая функция СТОЛБЕЦ возвращает в память программы второй массив из номеров столбцов с количеством элементов соответствующему количеству столбцов в диапазоне A3:Z3.

Скачать пример поиска ошибок в формулах Excel

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

Источник

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

Содержание

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

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

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

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

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

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

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

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

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

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

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

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

Ошибка #Н/Д

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

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

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

Ошибка #ИМЯ?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

Exceltip

Блог о программе Microsoft 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

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

Перехват ошибок в формулах функцией ЕСЛИОШИБКА (IFERROR)

Ошибки случаются. Вдвойне обидно, когда они случаются не по твоей вине. Так в Microsoft Excel, некоторые функции и формулы могут выдавать ошибки не потому, что вы накосячили при вводе, а из-за временного отсутствия данных или копирования формул «с запасом» на избыточные ячейки. Классический пример — ошибка деления на ноль при вычислении среднего:

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

Для лечения подобных ситуаций в Microsoft Excel есть мегаполезная функция ЕСЛИОШИБКА (IFERROR), которая умеет проверять заданную формулу или ячейку и, в случае возникновения любой ошибки, выдавать вместо нее заданное значение: ноль, пустую текстовую строку «» или что-то еще.

Синтаксис функции следующий:

=ЕСЛИОШИБКА( Что_проверяем ; Что_выводить_вместо_ошибки )

Так, в нашем примере можно было бы все исправить так:

Все красиво и ошибок больше нет.

Обратите внимание, что эта функция появилась только с 2007 версии Microsoft Excel. В более ранних версиях приходилось использовать функции ЕОШ (ISERROR) и ЕНД (ISNA) . Эти функции похожи на ЕСЛИОШИБКА, но они только проверяют наличие ошибок и не умеют заменять их на что-то еще. Поэтому приходилось использовать их обязательно в связке с функцией проверки ЕСЛИ (IF) , создавая вложенные конструкции типа:

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

Какие существуют обозначения ошибок и способы их исправления?

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

##### — Что обозначает и как исправить?

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

проверить формулу, если вычисляется число дней между двумя датами;
если формула не содержит ошибок, то необходимо изменить формат ячейки и перейти, например, с формата «Дата и время» на «Общий» или «Числовой» формат.

#ЗНАЧ! — Что обозначает и как исправить?

Эти сообщение об использовании текста вместо числа или логического значения (ИСТИНА или ЛОЖЬ). То есть Excel такой плейбой и не может преобразовать данный текст в ячейке в правильный тип данных.
Необходимо убедиться, что формула или функция ссылается на те ячейки, которые содержат действительные значения.
Например, если в ячейке A2 содержится число, а в ячейке A3 содержится текст, то в ячейке А1 с формулой =A2+A3 будет отображаться #ЗНАЧ! .

#ДЕЛ/0! — Что обозначает и как исправить?

Эти сообщение, что в ячейке происходит деление числа на 0 (ноль) или же используются ссылки на пустую ячейку.

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

#ИМЯ? — Что обозначает и как исправить?

Эти символы сообщают, что в формуле используется несуществующее имя или неправильный оператор.

1 вариант

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

В окне открытого листа перейдите к вкладке «Формулы» и в группе «Определенные имена» щелкните по кнопке «Диспетчер имен».
В окне «Диспетчер имен» просмотрите, присутствует ли данное имя в списке.

Если данное имя отсутствует, то необходимо добавить его согласно инструкции «Как присвоить имя ячейке или диапазону ячеек?».

2 вариант

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

В окне открытого листа нажмите клавишу F3.
В окошке «Вставка имени» выберите в списке имя нужное имя и нажмите кнопку «ОК».
Внесите исправления (при необходимости) в формуле, которая отобразится в соответствующей ячейке.
Для закрепления нажмите клавишу Enter.

3 вариант

Если в формуле используется функция с ошибкой в написании.
Например, СУМ(A1:А10) вместо СУММ(A1:А10) .

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

4 вариант

Если в формулу введен текст, который не заключен в двойные кавычки, то необходимо проверить все текстовые записи в формуле и заключить их в двойные кавычки. Иначе Excel будет пытаться распознать данный текст как имя диапазона ячеек, хотя это и не предполагалось.
Например, СУММ(A1 А10) вместо СУММ(A1:А10) .

5 вариант

Если в ссылке на диапазон ячеек пропущено двоеточие, то для исправления необходимо в формуле во всех подобных ссылках проверить знак двоеточия и исправить по мере необходимости.
Например, СУММ(A1 А10) вместо СУММ(A1:А10) .

6 вариант

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

#Н/Д — Что обозначает и как исправить?

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

1 вариант

Если в формулу были введены недостающие данные, а также #Н/Д или НД() , то #Н/Д необходимо заменить новыми данными.

2 вариант

Если в функциях ГПР , ПРОСМОТР , ПОИСКПОЗ или ВПР указывается неверное значение аргумента «искомое_значение» (например, ссылка на диапазон ячеек, что недопустимо), то необходимо соответственно указать ссылку только на нужную ячейку.

3 вариант

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

4 вариант

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

5 вариант

Если для просмотра значений в несортированной таблице используются функции ВПР , ГПР или ПОИСКПОЗ , для которых по умолчанию сведения для просмотра таблиц должны располагаться в возрастающем порядке.
В функциях ВПР и ГПР содержится аргумент «интервальный _просмотр», позволяющий искать определенное значение и в несортированной таблице. Но при этом, чтобы отыскать определенное значение, аргумент «интервальный_просмотр» должен иметь значение ЛОЖЬ .
В функции ПОИСКПОЗ содержится аргумент «тип_сопоставления», позволяющий сортировать данные для поиска. Если же соответствующее значение отыскать невозможно, то рекомендуется задать аргумент «тип_сопоставления» равный 0.

6 вариант

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

7 вариант

Если не заданы один или несколько необходимых аргументов стандартной или созданной функции листа, необходимо проверить и задать все необходимые аргументы функций.

#ССЫЛКА! — Что обозначает и как исправить?

Эти символы сообщают о неверной ссылке на ячейку.

1 вариант

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

2 вариант

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

3 вариант

Если используется ссылка на недоступный объект DDE (Dynamic Data Exchange – динамический обмен данными), например «system», то необходимо проверить, что используется правильный раздел DDE.

4 вариант

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

#ЧИСЛО! — Что обозначает и как исправить?

Это сообщение об использовании неправильных числовых значений в формуле или функции.

1 вариант

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

2 вариант

Если в функции с итерацией (подбором параметров), например «ВСД» или «СТАВКА», невозможно найти результат, то необходимо попробовать иное начальное приближение или изменить число итераций.

3 вариант

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

#ПУСТО! — Что обозначает и как исправить?

Эти сообщение об отсутствии общих ячеек, когда задано пере
сечение двух областей.

1 вариант

Если используется ошибочный оператор диапазона, то необходимо внести исправления, а именно:

для обозначения ссылки на непрерывный диапазон ячеек используется двоеточие (:) в качестве разделителя между начальной и конечной ячейкой диапазона. Например, СУММ(С1:С20) .
для обозначения ссылки на два непересекающихся диапазона используется оператор объединения – точкой с запятой (;). Например, СУММ(С1:С20;D1:D20) .

2 вариант

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

Источник

#Руководства

18 янв 2023
0

Показали, как работать с логическими функциями Excel: ИСТИНА, ЛОЖЬ, И, ИЛИ, НЕ, ЕСЛИ, ЕСЛИОШИБКА, ЕОШИБКА, ЕПУСТО.

Иллюстрация: Merry Mary для Skillbox Media

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

Логические функции в Excel проверяют, выполняются ли заданные условия в выбранном диапазоне таблицы. Пользователь указывает критерии, которые нужно проверить, — функции проверяют эти критерии и выдают результат: ИСТИНА или ЛОЖЬ.

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

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

Функции ИСТИНА и ЛОЖЬ
Функции И и ИЛИ
Функция НЕ
Функция ЕСЛИ
Функция ЕСЛИОШИБКА
Функция ЕОШИБКА
Функция ЕПУСТО

В конце расскажем, как узнать больше о работе в Excel.

Функции ИСТИНА и ЛОЖЬ не используют самостоятельно — только внутри других логических функций. Они нужны, чтобы отмечать значения как истинные или ложные.

Функция ИСТИНА возвращает только истинные значения. Её синтаксис: =ИСТИНА ().

Функция ЛОЖЬ — возвращает только ложные значения. Её синтаксис: =ЛОЖЬ ().

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

Функция И. Её используют, чтобы показать, что указанные число или текст должны соответствовать одновременно всем критериям. В этом случае функция возвращает значение ИСТИНА. Если один из критериев не соблюдается, функция И возвращает значение ЛОЖЬ.

Синтаксис функции И такой: =И(логическое_значение1;логическое_значение2;…), где «логическое_значение» — критерии пользователя, которые функция будет проверять. Всего может быть до 255 критериев.

Пример работы функции И. Проверим, соблюдены ли два условия:

число 662 больше 300;
число 8626 больше 9000.

Для этого выберем любую ячейку и в строке формул введём: =И(A1>300;A2>9000),

где А1 — ячейка с числом 662, А2 — ячейка с числом 8626.

Нажмём Enter. Функция возвращает значение ЛОЖЬ — один из критериев не соблюдён (число 8626 < 9000).

Функция И вернула значение ЛОЖЬ, так как один из критериев не соблюдён
Скриншот: Excel / Skillbox Media

Проверим другие критерии:

число 662 меньше 666;
число 8626 больше 5000.

Снова выберем любую ячейку и в строке формул введём: =И(A1<666;A2>5000).

Функция возвращает значение ИСТИНА — оба критерия соблюдены.

Функция И вернула значение ИСТИНА, так как соблюдены оба критерия
Скриншот: Excel / Skillbox Media

Функция ИЛИ. Её используют, чтобы показать, что указанные число или текст должны соответствовать одному из критериев. Если хотя бы один критерий соблюдён, функция возвращает значение ИСТИНА. Если все критерии не соблюдены, функция ИЛИ возвращает значение ЛОЖЬ.

Синтаксис функции ИЛИ: =ИЛИ(логическое_значение1;логическое_значение2;…).

Максимальное количество логических значений (критериев) — тоже 255.

Пример работы функции ИЛИ. Проверим три критерия:

число 662 меньше 666;
число 8626 больше 5000;
число 567 больше 786.

В строке формул введём: =ИЛИ(A1<666; A2>5000;A3>786).

Функция возвращает значение ИСТИНА, несмотря на то, что один критерий не соблюдён (число 567 < 786).

Функция ИЛИ вернула значение ИСТИНА — соблюдены два критерия из трёх
Скриншот: Excel / Skillbox Media

Проверим другие критерии:

число 662 меньше 500;
число 8626 больше 9000;
число 567 больше 600.

В строке формул введём: =ИЛИ(A1<500;A2>9000;A3>600).

Функция возвращает значение ЛОЖЬ, так как ни один из критериев не соблюдён.

Функция ИЛИ вернула значение ЛОЖЬ — все критерии не соблюдены
Скриншот: Excel / Skillbox Media

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

Если в качестве параметра функции НЕ указать ложное значение — она вернёт значение ИСТИНА. Наоборот, если указать истинное значение, функция вернёт ЛОЖЬ.

Синтаксис функции НЕ: =НЕ(логическое_значение), где «логическое_значение» — выражение, которое нужно проверить на соответствие значениям ИСТИНА или ЛОЖЬ. В этой функции можно использовать только одно такое выражение.

Пример работы функции НЕ. Проверим выражение «662 меньше 500». Выберем любую ячейку и в строке формул введём: =НЕ(A1<500), где А1 — ячейка с числом 662.

Нажмём Enter.

Выражение «662 меньше 500» ложное. Но функция НЕ поменяла значение на противоположное и вернула значение ИСТИНА.

Функция НЕ поменяла ложное значение на противоположное и вернула значение ИСТИНА
Скриншот: Excel / Skillbox Media

Функцию ЕСЛИ используют, когда нужно сравнить данные таблицы с критериями пользователя.

У этой функции также два результата: ИСТИНА и ЛОЖЬ. Первый результат функция выдаёт, когда значение ячейки совпадает с заданным условием, второй — когда значение условию не соответствует.

Например, если нужно определить в таблице значения меньше 1000, то значение 700 будет отмечено функцией как истинное, а значение 3500 — как ложное.

Можно задавать несколько условий одновременно. Например, найти значения меньше 300, но больше 200. В этом случае функция определит значение 100 как ложное, а 250 — как истинное. Так можно проверять не только числовые значения, но и текст.

Синтаксис функции ЕСЛИ: =ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь), где:

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

Пример работы функции ЕСЛИ. Предположим, из столбца с ценами нам нужно выбрать значения до 2 млн рублей.

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

Создаём отдельный столбец, куда функция ЕСЛИ принесёт результаты
Скриншот: Excel / Skillbox Media

В строке формул введём: =ЕСЛИ(A2<2000000;»Подходит»;»Не подходит»)

В строке формул вводим параметры функции ЕСЛИ
Скриншот: Excel / Skillbox Media

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

Так выглядит результат работы функции ЕСЛИ
Скриншот: Excel / Skillbox Media

Функция показала, какие значения соответствуют условию «меньше 2000000», и отметила их как «Подходит». Значения, которые не соответствуют этому условию, отмечены как «Не подходит».

В Skillbox Media есть статья, где подробно объясняли, как использовать функцию ЕСЛИ в Excel — в частности, как запустить функцию ЕСЛИ с несколькими условиями.

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

Синтаксис функции ЕСЛИОШИБКА: =ЕСЛИОШИБКА(значение;значение_если_ошибка), где:

значение — выражение, которое нужно проверить;
значение_если_ошибка — текст, число или формула, которые будут выводиться или выполняться в случае, если в результате проверки аргумента «значение» получен результат ЛОЖЬ.

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

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

Выделим первую ячейку столбца C и введём: =ЕСЛИОШИБКА(A1/B1;»Ошибка в расчёте»)

В строке формул вводим параметры функции ЕСЛИОШИБКА
Скриншот: Excel / Skillbox Media

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

Результат работы функции ЕСЛИОШИБКА
Скриншот: Excel / Skillbox Media

В первой строке функция не нашла ошибок в выражении (360/60), поэтому провела расчёт и показала результат (6).

Во второй строке функция тоже не нашла ошибок (деление 0 на 76) — и показала результат расчёта (0).

В третьей строке функция нашла ошибку — делить на 0 нельзя. Поэтому вместо результата расчёта показала второй аргумент функции: «Ошибка в расчёте».

Эта функция проверяет, не содержат ли заданные ячейки ошибочных значений:

#Н/Д
#ЗНАЧ
#ЧИСЛО!
#ДЕЛ/0!
#ССЫЛКА!
#ИМЯ?
#ПУСТО!

Синтаксис функции ЕОШИБКА: =ЕОШИБКА(значение), где «значение» — ячейка или диапазон ячеек, которые нужно проверить.

Если функция находит ошибочные значения — возвращает значение ИСТИНА. Если не находит — возвращает значение ЛОЖЬ.

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

Выберем любую ячейку, в которой функция должна будет вывести результат. В строке формул введём: =ЕОШИБКА(A1:A6), где A1:A6 — диапазон, который нужно проверить.

В строке формул вводим параметры функции ЕОШИБКА
Скриншот: Excel / Skillbox Media

Нажимаем Enter — функция возвращает значение ИСТИНА. Это значит, что она нашла ошибку в выделенном диапазоне.

Результат работы функции ЕОШИБКА
Скриншот: Excel / Skillbox Media

Дальше эту функцию используют для выполнения других действий.

Например, при возникновении ошибки можно использовать функцию ЕОШИБКА в сочетании с функцией ЕСЛИ: =ЕСЛИ( ЕОШИБКА(B1);»Произошла ошибка»;B1*6).

Эта формула проверит наличие ошибки в ячейке B1. При возникновении ошибки функция ЕСЛИ возвращает сообщение «Произошла ошибка». Если ошибки отсутствуют, функция ЕСЛИ вычисляет произведение B1*6.

Функция ЕПУСТО проверяет, есть ли в выбранных ячейках какие-либо значения или эти ячейки пустые. Если ячейка пустая, функция возвращает значение ИСТИНА, если в ячейке есть данные — ЛОЖЬ.

Синтаксис функции ЕПУСТО: =ЕПУСТО(значение), где значение — ячейка или диапазон ячеек, которые нужно проверить.

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

Выберем любую ячейку и в строке формул введём: =ЕПУСТО(A1:A6), где A1:A6 — диапазон, который нужно проверить.

В строке формул вводим параметры функции ЕПУСТО
Скриншот: Excel / Skillbox Media

Нажимаем Enter — функция возвращает значение ИСТИНА. Это значит, что она нашла пустую ячейку в выделенном диапазоне.

Результат работы функции ЕПУСТО
Скриншот: Excel / Skillbox Media

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

В Excel много функций, которые упрощают и ускоряют работу с таблицами. В этой подборке перечислили 15 статей и видео об инструментах Excel, необходимых в повседневной работе.
В Skillbox есть курс «Excel + Google Таблицы с нуля до PRO». Он подойдёт как новичкам, которые хотят научиться работать в Excel с нуля, так и уверенным пользователям, которые хотят улучшить свои навыки. На курсе учат быстро делать сложные расчёты, визуализировать данные, строить прогнозы, работать с внешними источниками данных, создавать макросы и скрипты.
Кроме того, Skillbox даёт бесплатный доступ к записи онлайн-интенсива «Экспресс-курс по Excel: осваиваем таблицы с нуля за 3 дня». Он подходит для начинающих пользователей. На нём можно научиться создавать и оформлять листы, вводить данные, использовать формулы и функции для базовых вычислений, настраивать пользовательские форматы и создавать формулы с абсолютными и относительными ссылками.

Другие материалы Skillbox Media по Excel

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

Участвовать

Научитесь: Excel + Google Таблицы с нуля до PRO
Узнать больше

Источник

Ввод данных

05.12.2013 67033

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

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

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

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

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

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

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

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

По теме

Новые публикации

Источник

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

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

См. также

Нужна дополнительная помощь?

Ошибка #ИМЯ?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ошибка #Н/Д

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

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

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

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

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

Ошибка ####

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

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

Что означает функция Excel ЕСЛИОШИБКА

Если ошибка, то пусто

Если ошибка, то показать сообщение

5 фактов, которые нужно знать о функции ЕСЛИОШИБКА в Excel

ЕСЛИОШИБКА с функцией ВПР

Вложенные функции ЕСЛИОШИБКА для выполнения последовательных ВПР

ЕСЛИОШИБКА в формулах массива

ЕСЛИОШИБКА или ЕСЛИ + ЕОШИБКА?

ЕСЛИОШИБКА против ЕСНД

Рекомендации по использованию ЕСЛИОШИБКА в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как получить адрес ячейки с ошибкой

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

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

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

Ошибка #Н/Д

Ошибка #ИМЯ?

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

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

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

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

Exceltip

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

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

Ошибка #Н/Д

Ошибка #ИМЯ?

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

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

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

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

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

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

Перехват ошибок в формулах функцией ЕСЛИОШИБКА (IFERROR)

Какие существуют обозначения ошибок и способы их исправления?

##### — Что обозначает и как исправить?

#ЗНАЧ! — Что обозначает и как исправить?

#ДЕЛ/0! — Что обозначает и как исправить?