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

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

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

• Исправление ошибки #ЧИСЛО! ошибка

• Исправление ошибки #ЗНАЧ! ошибка

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

• Исправление ошибки #ДЕЛ/0! ошибка

• Исправление ошибки #ССЫЛКА! ошибка

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

• Исправление #NULL! ошибка

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

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

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

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

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

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

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

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

  

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

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

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

   

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

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

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

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

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

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

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

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

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

Кроме того, некоторые функции, такие как СУММ, позволяют использовать только числовые аргументы, а другие, например ЗАМЕНИТЬ, требуют, чтобы хотя бы один аргумент был текстовым. Если вы используете неверный тип данных, функции могут возвращать непредвиденные результаты или показывать #VALUE! Error.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Например, чтобы возвратить значение из ячейки D3 на листе с квартальными данными в книге, введите: = "квартальные данные"! D3. Без кавычек вокруг имени листа в формуле отображается #NAME? ошибка.

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

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

Например, для ссылки на ячейки a1 – A8 на листе Sales в книге операций Q2, открытой в Excel, введите: = [Q2 Operations. xlsx] Sales! A1: A8. При отсутствии квадратных скобок в формуле отображается #REF!.

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

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

Деление ячейки на другую ячейку, которая имеет нулевое значение (0) или отсутствие значения, приводит к #DIVу или 0! Error.

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

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

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

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

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

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

• Если определенное имя отсутствует, а формула, которая ссылается на это имя, возвращает #NAME? ошибкаОпределите новое имя, которое ссылается на нужный диапазон, или измените формулу таким образом, чтобы она ссылалась непосредственно на диапазон ячеек (например, a2: D8).

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

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

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

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

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

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

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

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

   

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

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

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

4. На вкладке Главная в группе буфер обмена нажмите кнопку Вставить , а затем выберите команду вставить значения, или нажмите клавиши ALT > E > S > V > ввод для Windows, или Option > Command > v > v , > ввод на компьютере Mac.

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

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

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

   

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

   

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

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

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

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

   Примечания: 

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

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

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