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

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

См. следующие разделы, соответствующие ошибке фунта:

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

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

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

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

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

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

• Исправление #NULL! #BUSY!

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

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

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

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

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

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

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

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

  

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

1. На вкладке "Файл" выберите "Параметры",а затем категорию "Формулы".

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

   

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

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

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

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

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

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

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

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

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

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

Если вам нужно быстро просмотреть синтаксис определенной функции, см. список функций 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 уровней вложенности функций.

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

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

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

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

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

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

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

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

При деление одной ячейки на другую, которая имеет ноль (0) или значение не имеет значения, #DIV/0!.

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

=ЕСЛИ(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 > ENTER для Windows или option > Command > V > V > ENTER на компьютере Mac.

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

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

2. Выберите "Формулы" > "Высметь формулу".

   

3. Чтобы проверить значение этой ссылки, выберите "Оценить". Результат вычисления отображается курсивом.

   

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

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

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

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

   Примечания: 

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

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

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