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

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

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

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

Изображение диалогового окна "Проблема в этой формуле" в Excel

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

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

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

Изображение кнопки "Отмена" в строке формул

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

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

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

В Excel порождаются различные ошибки хеша (#), такие как #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? и #NULL!, чтобы указать, что какая-либо формула не работает должным образом. Например, #VALUE! ошибка вызвана неправильным форматированием или неподдерживаемыми типами данных в аргументах. Кроме того, вы увидите #REF! ошибка, если формула ссылается на ячейки, которые были удалены или заменены другими данными. Руководство по устранению неполадок будет различаться при каждой ошибке.

Примечание: #### не указывает на ошибку, связанную с формулой, а означает, что столбец недостаточно широк для отображения содержимого ячеек. Просто перетащите границу столбца, чтобы расширить его, или воспользуйтесь параметром Главная > Формат > Автоподбор ширины столбца.

"Главная" > "Формат" > "Автоподбор ширины столбца"

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

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

Диалоговое окно неработающих ссылок в Excel

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

Вы также можете отключить показ этого диалогового окна при открытии файла. Для этого перейдите в раздел Файл > Параметры > Дополнительно > Общие и снимите флажок Запрашивать об обновлении автоматических связей. В Excel 2007 нажмите кнопка Office  Кнопка Office 2007  > Параметры Excel.

Изображение флажка "Запрашивать об обновлении автоматических связей"

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

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

  • Убедитесь в том, что в Excel настроен на показ формул в электронных таблицах. Для этого на вкладке Формулы в группе Зависимости формул нажмите кнопку Показать формулу.

    Совет: Вы также можете использовать сочетание клавиш CTRL + ' (клавиша над клавишей TAB). В этом случае столбцы будут автоматически расширены для отображения формул, но не волнуйтесь, при возврате к обычному виду столбцы будут изменены.

  • Если описанные выше действия по-прежнему не устраняют проблему, то ячейка может быть отформатирована как текст. Можно щелкнуть правой кнопкой мыши ячейку и выбрать команду Формат ячеек _Гт_ общие (или CTRL + 1), а затем нажать клавишу F2 _гт_ ввод , чтобы изменить формат.

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

    Диалоговое окно: "Данные" > "Текст по столбцам"

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

  1. На вкладке Файл нажмите кнопку Параметры и выберите категорию Формулы.

  2. В разделе Параметры вычислений в группе Вычисления в книге выберите вариант автоматически.

    Изображение параметров для вычисления автоматически и вручную

Дополнительные сведения о вычислениях см. в статье Изменение пересчета, итерации или точности формулы.

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

Дополнительные сведения о циклических ссылках см. в разделе Удаление или разрешение циклической ссылки

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

Если ввести СУММ(A1:A10), Excel отобразит текстовую строку СУММ(A1:A10) вместо результата формулы. Если ввести 11/2, отобразится дата (например, "11.фев" или "11.02.2009") вместо результата деления 11 на 2.

Чтобы избежать подобных неожиданных результатов, всегда начинайте формулу со знака равенства. Например, введите =СУММ(A1:A10) и =11/2.

Если в формуле используется функция, для ее правильной работы важно, чтобы у каждой открывающей скобки была закрывающая, поэтому убедитесь, что каждой скобке соответствует парная. Например, формула =ЕСЛИ(B5<0);"Недопустимо";B5*1,05) не будет работать, так как в ней две закрывающие и только одна открывающая скобка. Правильный вариант этой формулы выглядит следующим образом: =ЕСЛИ(B5<0;"Недопустимо";B5*1,05).

Функции в Excel имеют аргументы — значения, которые необходимо указать, чтобы функция работала. Без аргументов работает лишь небольшое количество функций (например, ПИ или СЕГОДНЯ). Проверьте синтаксис формулы, который отображается, когда вы начинаете вводить функцию, чтобы убедиться в том, что указаны все обязательные аргументы.

Например, функция ПРОПИСН принимает в качестве аргумента только одну текстовую строку или ссылку на ячейку: =ПРОПИСН("привет") или =ПРОПИСН(C2).

Примечание: Аргументы функции перечислены на всплывающей справочной панели инструментов под формулой во время ее ввода.

Снимок экрана: панель инструментов "Ссылка на функцию"
Справочная панель инструментов формулы

Кроме того, некоторые функции, например Sum, требуют числовых аргументов только в том случае, если другие функции, такие как Замена, требуют текстового значения по крайней мере для одного из их аргументов. При использовании неправильного типа данных функции могут возвращать непредвиденные результаты или показывать #VALUE! Error.

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

Не вводите в формулах числа со знаком доллара ($) или разделителем (;), так как знаки доллара используются для обозначения абсолютных ссылок, а точка с запятой — в качестве разделителя аргументов. Вместо $1 000 в формуле необходимо ввести 1000.

Если в аргументе используются форматированные числа, результаты вычислений будут непредвиденными, но также может появиться ошибка #NUM! . Например, если ввести формулу = ABS (-2 134) , чтобы найти абсолютное значение-2134, в Excel появится #NUM! ошибка, так как Функция ABS принимает только один аргумент.

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

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

  1. Выделите ячейку.

  2. На вкладке Главная щелкните стрелку рядом со списком Числовой формат (или нажмите CTRL+1) и выберите пункт Общий.

  3. Нажмите клавишу F2, чтобы перейти в режим правки, а затем — клавишу ВВОД, чтобы подтвердить формулу.

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

В качестве оператора умножения в формуле часто используют крестик (x), однако в этих целях в Excel необходимо использовать звездочку (*). Если в формуле использовать знак "x", появится сообщение об ошибке и будет предложено исправить формулу, заменив x на "*".

Сообщение с предложением заменить x на * для умножения
Сообщение об ошибке, связанной с использованием знака "x" вместо "*" для умножения

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

Ошибка #ИМЯ? при использовании x вместо * со ссылками на ячейки
Ошибка #ИМЯ? при использовании знака "x" вместо "*" со ссылками на ячейки

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

Например, формула ="Сегодня " & ТЕКСТ(СЕГОДНЯ();"дддд, дд.ММ") объединяет текстовую строку "Сегодня " с результатами функций ТЕКСТ и СЕГОДНЯ и возвращает результат наподобие следующего: Сегодня понедельник, 30.05.

В формуле строка "Сегодня " содержит пробел перед закрывающей кавычкой, который соответствует пробелу между словами "Сегодня" и "понедельник, 30 мая". Если бы текст не был заключен в кавычки, формула могла бы вернуть ошибку #ИМЯ?.

Формула может содержать не более 64 уровней вложенности функций.

Например, формула =ЕСЛИ(КОРЕНЬ(ПИ())<2;"Меньше двух!";"Больше двух!") содержит три уровня вложенности функций: функция ПИ вложена в функцию КОРЕНЬ, которая, в свою очередь, вложена в функцию ЕСЛИ.

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

Например, чтобы возвратить значение ячейки D3 листа "Данные за квартал" в книге, введите ='Данные за квартал'!D3. Если не заключить имя листа в кавычки, будет выведена ошибка #ИМЯ?.

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

При вводе ссылки на значения или ячейки в другой книге включайте ее имя в квадратных скобках ([]), за которым следует имя листа со значениями или ячейками.

Например, для создания ссылки на ячейки с A1 по A8 на листе "Продажи" книги "Операции за II квартал", открытой в Excel, введите: =[Операции за II квартал.xlsx]Продажи!A1:A8. Если не добавить квадратные скобки, будет выведена ошибка #ССЫЛКА!.

Если книга не открыта в Excel, введите полный путь к файлу.

Например =ЧСТРОК('C:\Мои документы\[Операции за II квартал.xlsx]Продажи'!A1:A8).

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

Совет: Проще всего получить путь к другой книге, чтобы открыть другую книгу, а затем в исходной книге ввести =, а затем перейти к другой книге с помощью клавиш ALT + TAB , а затем выбрать любую ячейку на листе. Затем закройте исходную книгу. Формула будет автоматически обновляться, чтобы отображался полный путь к файлу и имя листа, а также требуемый синтаксис. Вы можете даже скопировать и вставить путь и использовать его везде, где вам нужно.

При делении одной ячейки на другую, содержащую нуль (0) или пустое значение, возвращается ошибка #ДЕЛ/0!.

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

=ЕСЛИ(B1;A1/B1;0)

Смысл это формулы таков: ЕСЛИ B1 существует, вернуть результат деления A1 на B1, в противном случае вернуть 0.

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

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

  • Если формула ссылается на ячейки, которые были удалены или заменены другими данными, и возвращает ошибку #ССЫЛКА!, выделите ячейку с этой ошибкой. В строке формул выберите текст #ССЫЛКА! и удалите его. Затем повторно укажите диапазон для формулы.

  • Если отсутствует определенное имя, а формула, зависящая от него, возвращает ошибку #ИМЯ?, определите новое имя для нужного диапазона или вставьте в формулу непосредственную ссылку на диапазон ячеек (например, A2:D8).

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

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

    Например, если используется формула =[Книга1.xlsx]Лист1'!A1, а такой книги больше нет, значения, ссылающиеся на нее, будут доступны. Но если изменить и сохранить формулу, которая ссылается на эту книгу, появится диалоговое окно Обновить значения с предложением ввести имя файла. Нажмите кнопку Отмена и обеспечьте сохранность данных, заменив формулу, которая ссылается на отсутствующую книгу, ее результатами.

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

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

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

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

  2. На вкладке Главная в группе Буфер обмена нажмите кнопку Копировать Изображение кнопки .

    Изображение ленты Excel

    Сочетание клавиш: CTRL+C.

  3. Выделите левую верхнюю ячейку область вставки.

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

  4. На вкладке Главная в группе Буфер обмена нажмите кнопку Вставить  Изображение кнопки и выберите пункт Вставить значения либо нажмите клавиши ALT > E > S > V > ВВОД (на компьютере с Windows) или OPTION > COMMAND > V > V > RETURN (на компьютере Mac).

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

  1. Выделите формулу, которую вы хотите вычислить.

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

    Группа ''Зависимости формул'' на вкладке ''Формулы''

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

    Диалоговое окно "Вычисление формулы"

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

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

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

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

    Примечания: 

    • Некоторые части функций ЕСЛИ и ВЫБОР не будут вычисляться, а в поле Вычисление может появиться ошибка #Н/Д.

    • Пустые ссылки отображаются как нулевые значения (0) в поле Вычисление.

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

Общие сведения о формулах в Excel

Обнаружение ошибок в формулах

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Совершенствование навыков работы с Office
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×