Если Excel не удается устранить формулу, которую вы пытаетесь создать, может появиться следующее сообщение об ошибке:
К сожалению, это означает, что Excel не может понять, что вы пытаетесь сделать, поэтому вам потребуется обновить формулу или убедиться, что вы правильно используете функцию.
Совет
Есть несколько распространенных функций, с которыми могут возникнуть проблемы. Чтобы узнать больше, ознакомьтесь с COUNTIF, SUMIF, VLOOKUP или IF. Список функций также можно просмотреть здесь.
Вернитесь в ячейку с неработающей формулой, которая будет находиться в режиме редактирования, и Excel выделит место, где возникла проблема. Если вы по-прежнему не знаете, что делать, и хотите начать заново, вы можете снова нажать клавишу ESC или нажать кнопку Отмена в строке формул, что приведет к выходу из режима редактирования.
Если вы хотите двигаться вперед, в следующем контрольном списке приведены шаги по устранению неполадок, которые помогут вам выяснить, что могло пойти не так. Выберите заголовки, чтобы узнать больше.
Примечание
Если вы используете Microsoft 365 для Интернета, вы можете увидеть не те же ошибки или решения могут не применяться.
Используете ли вы в формуле правильные разделители списков?
В формулах с более чем одним аргументом для разделения аргументов используются разделители списков. Используемый разделитель может зависеть от языкового стандарта ОС и параметров Excel. Наиболее распространенными разделителями списков являются запятая "," и точка с запятой ";".
Формула не будет работать, если какая-либо из ее функций использует неправильные разделители.
Дополнительные сведения см. в статье Ошибки формул, когда разделитель списка задан неправильно.
Вы видите ошибку фунта (#) в формулах Excel?
Excel выдает различные ошибки фунта (#), такие как #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME?, и #NULL! , чтобы указать, что что-то в формуле работает неправильно. Например, ошибка #ЗНАЧ! вызывается неправильным форматированием или неподдерживаемыми типами данных в аргументах. Ошибка #ССЫЛКА! происходит, когда формула ссылается на ячейки, которые были удалены или заменены другими данными. Инструкции по устранению проблемы будут разными для каждой ошибки.
Примечание
не является ошибкой, связанной с формулой. а означает, что столбец недостаточно широк для отображения содержимого ячеек. Просто перетащите столбец, чтобы расширить его, или перейдите в раздел Начальная > страница Формат > автоподбора ширины столбца.
См. любой из следующих разделов, соответствующих ошибке фунта, который вы видите:
- Исправление ошибки #ЧИСЛО! ошибка
- Исправление ошибки #ЗНАЧ! ошибка
- Исправление ошибки #Н/Д
- Исправление ошибки #ДЕЛ/0! ошибка
- Исправление ошибки #ССЫЛКА! ошибка
- Исправление ошибки #ИМЯ?
- Исправление ошибки #ПУСТО!
Исправление неработающих ссылок в формулах Excel
Каждый раз при открытии электронной таблицы с формулами, которые ссылаются на значения в других таблицах, вам предлагается обновить ссылки или отставить их как есть.
Excel отображает приведенное выше диалоговое окно, чтобы убедиться, что формулы в текущей электронной таблице всегда указывают на наиболее обновленное значение в случае изменения ссылочного значения. Вы можете обновить ссылки или пропустить этот шаг. Даже если вы решили не обновлять ссылки, вы можете в любой момент сделать это вручную непосредственно в электронной таблице.
Вы также можете отключить показ этого диалогового окна при открытии файла. Для этого перейдите в раздел Параметры > файлов > Дополнительные > общие и снимите флажок Запрашивать обновление автоматических ссылок.
Важно
Если раньше вам не приходилось сталкиваться с недействительными ссылками в формулах, вы хотите обновить в памяти сведения о них или не знаете, нужно ли обновлять ссылки, см. статью Управление обновлением внешних ссылок (связей).
Формула отображает синтаксис вместо значения в Excel
Если формула не выдает значение, воспользуйтесь приведенными ниже инструкциями.
Убедитесь, что в Excel настроен показ формул в электронных таблицах. Для этого на вкладке Формулы в группе Зависимости формул выберите Показать формулы.
Совет
Вы также можете нажать CTRL+` (эта клавиша расположена над клавишей TAB). При этом столбцы будут автоматически расширены, чтобы отобразить формулы, но не беспокойтесь, когда вы снова переключаетесь в обычное представление, ваши столбцы будут изменяться.
Если решить проблему с помощью описанного выше действия не удалось, возможно, ячейка имеет текстовый формат. Вы можете щелкнуть ячейку правой кнопкой мыши и выбрать команду Формат ячеек > общие (или CTRL+1), а затем нажать клавишу F2 > ВВОД , чтобы изменить формат.
Если у вас есть столбец с большим диапазоном ячеек, отформатированных в виде текста, можно выбрать диапазон, применить выбранный числовой формат и перейти к разделу Текст данных > к завершению столбца>. Формат будет применен ко всем выделенным ячейкам.
Включение автоматического вычисления книги, если формула не вычисляется в Excel
Если формула не вычисляется, необходимо проверить, включена ли в Excel функция автоматического вычисления. Формулы не вычисляются, если включено ручное вычисление. Чтобы проверить, включено ли автоматическое вычисление, выполните указанные ниже действия.
Перейдите на вкладку Файл, нажмите Параметры и выберите категорию Формулы.
В разделе Параметры вычисления в разделе Вычисление книги убедитесь, что выбран параметр Автоматически .
Дополнительные сведения о вычислениях см. в статье Изменение пересчета, итерации или точности формулы.
Формула содержит одну или несколько циклических ссылок
Циклическая ссылка возникает, когда формула ссылается на ячейку, в которой она расположена. Чтобы исправить эту ошибку, перенесите формулу в другую ячейку или исправьте синтаксис таким образом, чтобы циклической ссылки не было. Однако иногда циклические ссылки необходимы, потому что они заставляют функции выполнять итерации, то есть повторять вычисления до тех пор, пока не будет выполнено заданное числовое условие. В таких случаях потребуется удалить или разрешить циклическую ссылку.
Дополнительные сведения см. в статье Удаление или разрешение циклической ссылки.
Начинается ли функция со знака равенства (=)?
Если запись не начинается со знака равенства, она не является формулой и не будет вычисляться — распространенная ошибка.
Если ввести СУММ(A1:A10), Excel отобразит текстовую строку СУММ(A1:A10) вместо результата формулы. Если ввести 11/2, отобразится дата (например, "11.фев" или "11.02.2009") вместо результата деления 11 на 2.
Чтобы избежать подобных неожиданных результатов, всегда начинайте формулу со знака равенства. Например, введите : =SUM(A1:A10) и =11/2.
Соблюдается ли соответствие открывающих и закрывающих скобок?
Если в формуле используется функция, для ее правильной работы важно, чтобы у каждой открывающей скобки была закрывающая. Убедитесь, что у каждой скобки есть соответствующая пара. Например, формула =IF(B5<0),"Not valid",B5*1.05) не будет работать, так как есть две закрывающие скобки, но только одна открывающая скобка. Правильная формула будет выглядеть следующим образом: =IF(B5<0;"Not valid";B5*1.05)).
Включает ли синтаксис все обязательные аргументы?
Функции в Excel имеют аргументы — значения, которые необходимо указать, чтобы функция работала. Без аргументов работает лишь небольшое количество функций (например, ПИ или СЕГОДНЯ). Проверьте синтаксис формулы, который отображается, когда вы начинаете вводить функцию, чтобы убедиться в том, что указаны все обязательные аргументы.
Например, функция ПРОПИСН принимает в качестве аргумента только одну текстовую строку или ссылку на ячейку: =ПРОПИСН("привет") или =ПРОПИСН(C2).
Примечание
Аргументы функции отображаются на панели ссылок с плавающей функцией под формулой при ее вводе.
Кроме того, для некоторых функций, таких как СУММ, требуются только числовые аргументы, а другим функциям, таким как REPLACE, требуется текстовое значение по крайней мере для одного из аргументов. Если использовать неправильный тип данных, некоторые функции могут вернуть неожиданные результаты или ошибку #ЗНАЧ!.
Если вам нужно быстро просмотреть синтаксис определенной функции, см. список функций Excel (по категориям).
Обработка неформатированных чисел в формулах Excel
Не вводите в формулы числа, отформатированные знаками доллара ($) или десятичными разделителями (,), так как знаки доллара указывают на абсолютные ссылки , а запятые являются разделителями аргументов. Вместо $1,000 в формуле необходимо ввести 1000.
При использовании форматированных чисел в аргументах вы получите непредвиденные результаты вычислений, но также может появиться ошибка #NUM! . Например, если ввести формулу =ABS(-2,134) для получения абсолютной величины числа -2134, Excel выведет ошибку #ЧИСЛО!, так как функция ABS принимает только один аргумент, но воспринимает -2, и 134 как отдельные аргументы.
Примечание
Результат формулы можно отформатировать с использованием десятичных разделителей и обозначений денежных единиц после ввода формулы с неформатированными числами (константами). Как правило, не рекомендуется помещать константы в формулы, так как их может быть трудно найти, если вам потребуется обновиться позже, и они более склонны к неправильному типу. Гораздо лучше поместить константы в ячейки, где они открыты и легко ссылаются.
Имеют ли ячейки, на которые указывают ссылки, правильный тип данных?
Формула может не возвращать ожидаемые результаты, если тип данных ячейки не может использоваться в вычислениях. Например, если ввести простую формулу =2+3 в ячейку, отформатированную как текст, Excel не сможет вычислить введенные данные. В ячейке будет отображаться строка =2+3. Чтобы устранить эту проблему, измените тип данных ячейки с Text на Общие , как показано ниже:
- Выделите ячейку.
- Выберите Главная и нажмите стрелку, чтобы развернуть группу Числовой или Числовой формат (или нажмите клавиши CTRL+1). Затем выберите Общий.
- Нажмите клавишу F2, чтобы перейти в режим правки, а затем — клавишу ВВОД, чтобы подтвердить формулу.
Дата, введенная в ячейку с типом данных Number , может отображаться в виде числового значения даты, а не даты. Чтобы это число отображалось в виде даты, в коллекции Числовой формат выберите формат Дата.
Пытаетесь ли вы выполнить умножение, не используя символ *?
В качестве оператора умножения в формуле часто используют крестик (x), однако в этих целях в Excel необходимо использовать звездочку (*). Если в формуле использовать знак "x", появится сообщение об ошибке и будет предложено исправить формулу, заменив x на "*".
Однако при использовании ссылок на ячейки Excel вернет #NAME? Ошибка.
Отсутствуют ли кавычки вокруг текста в формулах?
Если в формуле содержится текст, его нужно заключить в кавычки.
Например, формула ="Сегодня " & ТЕКСТ(СЕГОДНЯ();"дддд, дд.ММ") объединяет текстовую строку "Сегодня " с результатами функций ТЕКСТ и СЕГОДНЯ и возвращает результат наподобие следующего: Сегодня понедельник, 30.05.
В формуле строка "Сегодня " содержит пробел перед закрывающей кавычкой, который соответствует пробелу между словами "Сегодня" и "понедельник, 30 мая". Если бы текст не был заключен в кавычки, формула могла бы вернуть ошибку #ИМЯ?.
Включает ли формула больше 64 функций?
Формула может содержать не более 64 уровней вложенности функций.
Например, формула =IF(SQRT(PI()<2,"Меньше двух!", "Больше двух!") имеет 3 уровня функций; Функция PI вложена в функцию SQRT, которая, в свою очередь, вложена в функцию IF.
Заключены ли имена листов в апострофы?
При вводе ссылки на значения или ячейки на других листах, имя которых содержит небуквенные символы (например, пробел), заключайте его в апострофы (').
Например, чтобы возвратить значение ячейки D3 листа "Данные за квартал" в книге, введите ='Данные за квартал'!D3. Без кавычек вокруг имени листа формула отображает ошибку #NAME?.
Вы также можете выбрать значения или ячейки на другом листе, чтобы добавить ссылку на них в формулу. Excel автоматически заключит имена листов в кавычки.
Исправление путей к внешней книге в формулах Excel
При вводе ссылки на значения или ячейки в другой книге включайте ее имя в квадратных скобках ([]), за которым следует имя листа со значениями или ячейками.
Например, чтобы сослаться на ячейки от A1 до A8 на листе Продажи в книге Операций в 2 квартале, открытой в Excel, введите : =[Q2 Operations.xlsx]Sales! A1:A8. Без квадратных скобок в формуле отображается ошибка #REF!.
Если книга не открыта в Excel, введите полный путь к файлу.
Например =ЧСТРОК('C:\Мои документы\[Операции за II квартал.xlsx]Продажи'!A1:A8).
Примечание
Если полный путь содержит пробелы, необходимо заключить его в апострофы (в начале пути и после имени книги перед восклицательным знаком).
Совет
Чтобы получить путь к другой книге, проще всего открыть ее, ввести в исходной книге знак равенства (=), а затем с помощью клавиш ALT+TAB перейти во вторую книгу. После выбора любой ячейки на необходимом листе нужно закрыть исходную книгу. Формула автоматически обновится, и в ней отобразится полный путь к имени листа с правильным синтаксисом. При необходимости этот путь можно копировать и вставлять.
Пытаетесь ли вы делить числовые значения на нуль?
При делении одной ячейки на другую, содержащую нуль (0) или пустое значение, возвращается ошибка #ДЕЛ/0!.
Чтобы устранить эту ошибку, можно просто проверить, существует ли знаменатель. Вы можете использовать:
=ЕСЛИ(B1;A1/B1;0)
Смысл это формулы таков: ЕСЛИ B1 существует, вернуть результат деления A1 на B1, в противном случае вернуть 0.
Ссылается ли формула на удаленные данные?
Прежде чем удалять данные в ячейках, диапазонах, определенных именах, листах и книгах, всегда проверяйте, нет есть ли у вас формул, которые ссылаются на них. Вы сможете заменить формулы их результатами перед удалением данных, на которые имеется ссылка.
Если вы не можете заменить формулы их результатами, просмотрите следующие сведения об ошибках и возможных решениях:
- Если формула ссылается на ячейки, которые были удалены или заменены другими данными, и если она возвращает ошибку #REF!, выделите ячейку с #REF! . В строке формул выберите текст #ССЫЛКА! и удалите его. Затем повторно укажите диапазон для формулы.
- Если отсутствует определенное имя, а формула, зависящая от него, возвращает ошибку #ИМЯ?, определите новое имя для нужного диапазона или вставьте в формулу непосредственную ссылку на диапазон ячеек (например, A2:D8).
- Если лист отсутствует, а формула, которая ссылается на него, возвращает ошибку #ССЫЛКА!, Ошибка. К сожалению, исправить эту проблему невозможно— удаленный лист не может быть восстановлен.
- Если отсутствует книга, это не влияет на формулу, которая ссылается на нее, пока не обновить формулу.
Например, если используется формула =[Книга1.xlsx]Лист1'!A1, а такой книги больше нет, значения, ссылающиеся на нее, будут доступны. Но если изменить и сохранить формулу, которая ссылается на эту книгу, появится диалоговое окно Обновить значения с предложением ввести имя файла. Выберите Отмена, а затем убедитесь, что эти данные не потеряны, заменив формулы, ссылающиеся на отсутствующие книги, результатами формул.
Выполняли ли вы копирование и вставку ячеек, связанных с формулой, в таблице?
Иногда при копировании содержимого ячейки требуется вставить только значение, а не базовую формулу, отображаемую в строке формул.
Например, вам может потребоваться скопировать итоговое значение формулы в ячейку на другом листе. Или вам может быть необходимо удалить значения, использовавшиеся в формуле, после копирования итогового значения в другую ячейку на листе. Оба этих действия вызывают ошибку недопустимой ссылки на ячейку (#REF!) в конечной ячейке, так как на ячейки, содержащие значения, используемые в формуле, больше нельзя ссылаться.
Чтобы избежать этой ошибки, вставьте итоговые значения формул в конечные ячейки без самих формул.
На листе выделите ячейки с итоговыми значениями формулы, которые требуется скопировать.
На вкладке Главная в группе Буфер обмена выберите Копировать
.
Сочетание клавиш: CTRL+C.Выделите левую верхнюю ячейку области вставки.
Совет
Чтобы переместить или скопировать выделенный фрагмент на другой лист или в другую книгу, щелкните ярлычок другого листа или выберите другую книгу и выделите левую верхнюю ячейку области вставки.
На вкладке Главная в группе Буфер обмена выберите Вставить
, а затем выберите Вставить значения или нажмите клавишу ALT > E > S > V > ВВОД для Windows или Option > Command > V > V > Ввод на компьютере Mac.
Если имеется вложенная формула, вычисляйте ее по шагам
Чтобы понять, как сложная или вложенная формула получает конечный результат, можно вычислить ее пошагово.
Выделите формулу, которую вы хотите вычислить.
Выберите Формулы Вычисление>формулы.
Нажмите Вычислить, чтобы проверить значение подчеркнутой ссылки. Результат вычисления отображается курсивом.
Если подчеркнутая часть формулы является ссылкой на другую формулу, нажмите Шаг с заходом, чтобы вывести другую формулу в поле Вычисление. Нажмите Шаг с выходом, чтобы вернуться к предыдущей ячейке и формуле.
Кнопка Шаг в недоступна при втором отображении ссылки в формуле или если формула ссылается на ячейку в другой книге.Продолжайте этот процесс, пока не будут вычислены все части формулы.
Инструмент "Вычисление формулы" вряд ли сможет рассказать вам, почему формула не работает, но поможет найти проблемное место. Этот инструмент может оказаться очень полезным в больших формулах, когда очень трудно найти проблему иным способом.Примечание
- Некоторые части функций IF и CHOOSE не будут оцениваться, и ошибка #N/A может появиться в поле Оценка .
- Пустые ссылки отображаются как нулевые значения (0) в поле Вычисление.
- Некоторые функции пересчитываются при каждом изменении листа. Такие функции, в том числе СЛЧИС, ОБЛАСТИ, ИНДЕКС, СМЕЩ, ЯЧЕЙКА, ДВССЫЛ, ЧСТРОК, ЧИСЛСТОЛБ, ТДАТА, СЕГОДНЯ и СЛУЧМЕЖДУ, могут приводить к отображению в диалоговом окне Вычисление формулы результатов, отличающихся от фактических результатов в ячейке на листе.
Дополнительные сведения
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществах.
Совет
Если вы являетесь владельцем малого бизнеса, который ищет дополнительные сведения о том, как настроить Microsoft 365, посетите страницу Справка для малого бизнеса & обучения.