Почему не работает формула в excel
Перейти к содержимому

Почему не работает формула в excel

  • автор:

Исправление ошибки #ИМЯ?

Обычно ошибка #ИМЯ? Ошибка отображается в формуле из-за опечатки в имени формулы. Рассмотрим пример:

Ошибка #ИМЯ?, вызванная опечаткой

Важно: Ошибка #ИМЯ? означает, что нужно исправить синтаксис, поэтому если вы видите ее в формуле, устраните ее. Не скрывайте ее с помощью функций обработки ошибок, например функции ЕСЛИОШИБКА.

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

Синтаксис формулы во всплывающей подсказке

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

Кнопка

Вставить функцию.

Excel автоматически загружает мастер.

Пример диалогового окна мастера функций

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

Ниже приведены другие причины #NAME? .

Формула ссылается на несуществующее имя

Если в формуле есть ссылка на имя, не определенное в Excel, вы увидите #NAME? ошибку #ЗНАЧ!.

См. следующий пример функции SUM, ссылающейся на Profit, которая является неопределенным именем в книге.

Ошибка #ИМЯ?, вызванная ссылкой на неопределенное имя

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

Щелкните

  1. Если у вас уже есть данные в электронной таблице и вы хотите назначить имя определенным ячейкам или диапазону ячеек, сначала выделите ячейки в электронной таблице. Если вы хотите создать диапазон, можно пропустить этот шаг.
  2. Перейдите в раздел Формулы >Определенные имена , > выберите Определить имя, а затем — Определить имя.
  3. Введите уникальное имя.
  4. В поле Область выберите, если нужно, чтобы имя было доступно только на листе или во всей книге.
  5. При желании введите примечание.
  6. Нажмите кнопку ОК. Далее необходимо добавить имя в формулу.
  7. Курсор должен быть в том месте формулы, куда вы хотите добавить созданное имя.
  8. Перейдите в раздел Формулы >Определенные имена > выберите Использовать в формуле, а затем выберите определенное имя, которое нужно добавить.

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

Определенное имя указано неправильно

Если синтаксис неправильно ссылается на определенное имя, вы увидите #NAME? ошибку #ЗНАЧ!.

В предыдущем примере в электронной таблице было создано определенное имя profit . В приведенном ниже примере имя указано неправильно, поэтому функция по-прежнему выдает ошибку #ИМЯ? .

Ошибка #ИМЯ?, вызванная опечаткой в определенном имени

Решение. Исправьте опечатку и повторите попытку.

Совет: Вместо того чтобы вручную вводить определенные имена в формулах, предоставьте это Excel. Перейдите в раздел Формулы > Определенные имена , > выберите Использовать в формуле, а затем выберите определенное имя, которое нужно добавить. Excel добавляет имя в формулу.

Текстовые значения не заключены в двойные кавычки

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

Ошибка #ИМЯ?, вызванная отсутствием кавычек вокруг текстовых значений

В синтаксисе в этом примере отсутствуют двойные кавычки для has; который объясняет ошибку.

Решение. Проверьте, нет ли в формуле текстовых значений без кавычек.

В ссылке на диапазон пропущено двоеточие

Если вы пропустите двоеточие в ссылке на диапазон, формула отображает #NAME? .

В приведенном ниже примере формула ИНДЕКС выдает ошибку #ИМЯ?, так как в диапазоне B2 — B12 нет двоеточия.

Ошибка #ИМЯ?, вызванная отсутствием двоеточия в ссылке на диапазон

Решение. Убедитесь, что все ссылки на диапазон включают двоеточие.

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

Некоторые функции Excel работают только тогда, когда включена соответствующая надстройка. Использование этих функций в противном случае приводит к #NAME? ошибку #ЗНАЧ!. Например, чтобы использовать функцию EUROCONVERT , необходимо включить надстройку Euro Currency Tools . Если вы используете пользовательские функции или макросы, для которых требуется инструмент анализа, убедитесь, что включена надстройка Analysis ToolPak .

Чтобы включить надстройку в Excel:

  1. Перейдите в раздел Параметры > файлов .
  2. Выберите Надстройки.
  3. В списке Управление выберите Надстройки Excel, а затем нажмите кнопку Go.
  4. Установите соответствующий флажок и нажмите кнопку ОК.

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

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Исправление ошибки #Н/Д

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2013 Excel для iPad Excel Web App Excel для iPhone Excel для планшетов с Android Excel для телефонов с Android Excel для Windows Phone 10 Excel Mobile Еще. Меньше

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

Лучшее решение

Чаще всего появление ошибки #Н/Д обусловлено тем, что формула не может найти значение, на которое ссылается функция ПРОСМОТРX, ВПР, ГПР, ПРОСМОТР или ПОИСКПОЗ. Например, искомого значения нет в исходных данных.

Искомого значения не существует. Ячейка E2 содержит формулу =ВПР(D2;$D$6:$E$8;2;ЛОЖЬ). Значение

В данном случае в таблице подстановки нет элемента «Банан», поэтому функция ВПР возвращает ошибку #Н/Д.

Решение: Убедитесь, что искомое значение есть в исходных данных, или используйте в формуле обработчик ошибок, например функцию ЕСЛИОШИБКА. Например, формула =ЕСЛИОШИБКА(ФОРМУЛА();0) означает следующее:

  • =ЕСЛИ(при вычислении формулы получается ошибка, то показать 0, в противном случае показать результат формулы)

Вы можете указать «», чтобы не отображалось ничего, или подставить собственный текст: =ЕСЛИОШИБКА(ФОРМУЛА(),»Сообщение об ошибке»)

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

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

Ссылка на форум сообщества Excel

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

Неправильные типы значений

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

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

Решение: Убедитесь, что типы данных совпадают. Проверьте форматы ячеек. Для этого выделите диапазон ячеек, щелкните правой кнопкой мыши, выберите Формат ячеек > Число (или нажмите клавиши CTRL+1) и при необходимости измените числовой формат.

Диалоговое окно

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

В ячейках есть лишние пробелы

Начальные и конечные пробелы можно удалить с помощью функции СЖПРОБЕЛЫ. В приведенном ниже примере в функции ВПР используется вложенная функция СЖПРОБЕЛЫ для удаления начальных пробелов из имен в ячейках A2:A7 и возврата названия отдела.

Использование функции ВПР с вложенной функцией СЖПРОБЕЛЫ в формуле массива для удаления начальных и конечных пробелов. Ячейка E3 содержит формулу <=ВПР(D2;СЖПРОБЕЛЫ(A2:B7);2;ЛОЖЬ)></p>
<p>, для ввода которой нужно нажать клавиши CTRL+SHIFT+ВВОД.» /></p>
<p><b>Примечание:</b> 24 сентября 2018 г. — Формулы динамического массива — Если у вас есть текущая версия Microsoft 365 и вы находитесь на канале быстрого выпуска Insiders, вы можете ввести формулу в верхнюю левую ячейку выходного диапазона и нажать клавишу <b>Enter</b>, чтобы подтвердите формулу в виде формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши <b>CTRL+SHIFT+ВВОД</b> для подтверждения. Excel автоматически вставляет скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.</p>
<p>Использование метода приблизительного или точного совпадения (ИСТИНА/ЛОЖЬ)</p>
<p>По умолчанию функции, которые ищут данные в таблицах, должны использовать сортировку по возрастанию. Но у функций ВПР и ГПР есть аргумент <i>интервальный_просмотр</i>, который сообщает функции, что нужно искать точное совпадение, даже если таблица не отсортирована. Чтобы найти точное совпадение, укажите для аргумента <i>интервальный_просмотр</i> значение ЛОЖЬ. Помните, что значение ИСТИНА, сообщающее функции о том, что нужно искать приблизительное совпадение, может привести к возвращению не только ошибки #Н/Д, но и ошибочных результатов, как видно в следующем примере.</p>
<p><img decoding=

В этом примере возвращается не только ошибка #Н/Д для элемента «Банан», но и неправильная цена для элемента «Черешня». К такому результату приводит аргумент ИСТИНА, который сообщает функции ВПР, что нужно искать не точное, а приблизительное совпадение. Здесь нет близкого совпадения для элемента «Банан», а «Черешня» предшествует элементу «Персик». В этом случае при использовании функции ВПР с аргументом ЛОЖЬ будет отображаться правильная цена для элемента «Черешня», но для элемента «Банан» все равно будет указана ошибка #Н/Д, потому что в списке подстановок его нет.

Если вы используете функцию ПОИСКПОЗ, попробуйте изменить значение аргумента тип_сопоставления, чтобы указать порядок сортировки таблицы. Чтобы найти точное совпадение, задайте для аргумента тип_сопоставления значение 0 (ноль).

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

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

В данном примере ячейка E2 содержит ссылку на несовпадающие диапазоны:

Пример формулы массива со ссылками на несовпадающие диапазоны, из-за чего появляется ошибка #Н/Д. Ячейка E2 содержит формулу <=СУММА(ЕСЛИ(A2:A11=D2;B2:B5))></p><div class='code-block code-block-11' style='margin: 8px 0; clear: both;'>
<!-- 11joomlaumnik -->
<script src=

, для ввода которой нужно нажать клавиши CTRL+SHIFT+ВВОД.» />

Чтобы формула вычислялась правильно, необходимо изменить ее так, чтобы оба диапазона включали строки 2–11.

Примечание: 24 сентября 2018 г. — Формулы динамического массива — Если у вас есть текущая версия Microsoft 365 и вы находитесь на канале быстрого выпуска Insiders, вы можете ввести формулу в верхнюю левую ячейку выходного диапазона и нажать клавишу Enter, чтобы подтвердите формулу в виде формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

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

Пример введенного в ячейки значения #Н/Д, которое не позволяет формуле СУММ получить правильный результат

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

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

Чтобы исправить ошибку, проверьте синтаксис используемой функции и введите все обязательные аргументы, которые возвращают ошибку. Вероятно, для проверки функции вам потребуется использовать редактор Visual Basic. Открыть этот редактор можно на вкладке «Разработчик» или с помощью клавиш ALT+F11.

Пользовательская функция, которую вы ввели, недоступна

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

Выполняемый макрос использует функцию, которая возвращает значение «#Н/Д».

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

При изменении защищенного файла, который содержит такие функции, как ЯЧЕЙКА, в ячейках выводятся ошибки #Н/Д

Чтобы исправить ошибку, нажмите клавиши CTRL+ALT+F9 для пересчета листа.

Нужна помощь по аргументам функции?

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

Кнопка

Excel автоматически запустит мастер.

Пример диалогового окна мастера функций

Щелкните любой аргумент, и Excel покажет вам сведения о нем.

Использование #Н/Д в диаграммах

Значение #Н/Д может принести пользу. Значения #Н/Д часто используются в диаграммах с такими данными, как в приведенном ниже примере, поскольку эти значения не отображаются на диаграмме. В примерах ниже показано, как выглядит диаграмма со значениями 0 и #Н/Д.

Пример графика, на котором отображаются нулевые значения

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

Пример графика, на котором не отображаются значения #Н/Д

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

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Почему не работает формула в excel

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949
ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071

Excel works!

menu

Встречались с проблемой, что Excel не считает формулу? Если да, то наверно следует разделить эту проблему на две основных причины:

Excel не считает формулу

Первый вариант — вы ввели формулу, она отображается, но не считается.

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

Подробнее о каждом случае ниже:

Excel не считает формулу в ячейке

Формулу вы ввели, но расчет не происходит, в чем дело? Скорее всего формат ячейки скорее всего Текстовый (Правой кнопкой мыши — Формат Ячеек — вкладка Число — Текстовый)

Excel не считает формулу 1

Поставьте формат Общий или Числовой. Теперь необходимо зайти в ячейку и активировать формулу — т.е. выбрать ее, войти внутрь (F2) и нажать Enter.

«Хах», — скажете вы — «Так у меня таких строк тыща». Резонно. В случаях для первого типа проблемы, в левом верхнем углу ячейке будет зеленый треугольник, если Excel посчитает, что введенный текст в ячейке относится к числовому формату. Тогда при выборе такой ячейки появится окошко «Число сохранено как текст»:

Текстовый3

Теперь выделите все такие ячейки и нажмите на пункт меню — преобразовать в число. Готово. Этот же инструмент доступен в Формулы — Проверка наличия ошибок.

Excel не считает формулу 2

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

Просто умножьте каждое число на 1 в соседнем столбце /ячейке (например, =A1*1), получится число. Теперь у вас есть числовые значения, которые можно скопировать куда угодно 😉

Excel считает формулу, но выдает ошибку

Как быть здесь. Сперва предлагаю почитать об ошибках — здесь . В этой же статье предложено решение многих проблем. Чтобы разобраться со случаем, когда ошибка в расчетах, существует специальный инструмент — Вычислить формулу (Формулы — Раздел Зависимости формул — Вычислить формулу)

Excel не считает формулу 3

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

Ошибка7

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

Формула не считается. Прочие случаи:

Так же я встречался со случаями, когда формула не рассчитывается, потому что был случайно включен режим просмотра формул (Лента задач — Формулы — Раздел Зависимости формул — Показать формулы)

Текстовый4

Достаточно часто выдает ошибку

Персонал5

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

Функция ЗНАЧЕН()

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

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

Похожие статьи

  • 25.11.2015Сумма по нескольким листам ExcelPosted in Формулы
  • 10.08.2015Как удобно записать несколько формул в ExcelPosted in Excel
  • 13.11.2015Формула массива Excel. Как пользоваться?Posted in Формулы
  • 21.07.2015Как преобразовать текст в число в Excel?Posted in Формулы
  • 14.03.2015Как столбцы сделать строками? Транспонирование в ExcelPosted in Excel, Формулы
  • 30.12.2015Для чего в Excel формула СУММПРОИЗВ?Posted in Формулы
  • 06.02.2016Функция ЯЧЕЙКА в ExcelPosted in Формулы, Работа с ячейками
  • 03.06.2017Стиль ссылок R1C1 в Excel. Когда вместо букв в столбцах цифрыPosted in Формулы, Оформление

Добавить комментарий

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