Формулы в excel перестали работать: Excel не считает формулы в ячейках. Не производит вычислений

Проблемы совместимости формул в Excel

Средством проверки совместимости обнаружены проблемы совместимости формул с более ранними версиями Microsoft Excel.

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

Важно: 


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


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

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

В этой статье



  • Проблемы, которые приводят к существенной потере функциональности


  • Проблемы, которые приводят к небольшой потере точности

Проблемы, которые приводят к существенной потере функциональности


















Проблема


Решение

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


Что это означает.    В Excel 2007 г. и более поздних книгах массивы, ссылались на другие книги, ограничены только доступной памятью, но в Excel 97–2003 книги могут содержать не более 65 472 массивов книг, которые ссылаются на другие. Массивы книг после максимального предела будут преобразованы в и отобразить #VALUE! исчезнут.


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

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


Что это означает.    В Excel 2010 и более поздних годах можно использовать формулы массива, содержащие элементы для более чем 256 столбцов и 65 536 строк. В Excel 2007 г. это превышает ограничение для элементов массива и может возвращать другие результаты.


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

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


Что это означает.     В Excel 2007 и более поздних гг. максимальная длина содержимого формулы составляет 8 192 символа, а максимальная внутренняя длина формулы — 16 384байта. В Excel 97–2003 максимальная длина содержимого формулы составляет 1024 символа, а внутренняя — 1800 bytes. Если сочетание аргументов формулы (включая значения, ссылки и/или имена) превышает предельное Excel 97–2003, результатом формул будет #VALUE! при сохранение книги в формате более ранней Excel файла.


Действия    В окне средства проверки совместимости нажмите кнопку Найти, чтобы найти ячейки, содержащие формулы, длина которых превышает предельное значение в Excel 97–2003, и внести необходимые изменения, чтобы избежать #VALUE! исчезнут.

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


Что это означает.    В Excel 2007 и более поздних годах формула может содержать до 64 уровней вложенности, а в Excel 97–2003 — всего 7.


Действия    В окте проверки совместимости нажмите кнопку Найти, чтобы найти ячейки, содержащие формулы с более чем 7 уровнями вложенности, и внести необходимые изменения, чтобы избежать #VALUE! исчезнут.

Некоторые формулы содержат функции, у которых больше аргументов, чем поддерживается выбранным форматом файла. Формулы, которые имеют более 30 аргументов на одну функцию, не будут сохранены и будут преобразованы в #VALUE! исчезнут.


Что это означает.    В Excel 2007 и более поздних годах формула может содержать до 255 аргументов, но в Excel 97–2003 число аргументов в формуле не может быть больше 30.


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

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


Что это означает.    В Excel 2007 г. и более поздних гг. число операндов, которые можно использовать в формулах, составляет 1024, а в Excel 97–2003 — всего 40.


Действия    В окте проверки совместимости нажмите кнопку Найти, чтобы найти ячейки, содержащие формулы с более чем 40 операндами, и внести необходимые изменения, чтобы избежать #VALUE! исчезнут.

Некоторые формулы содержат функции с большим размером аргументов, чем поддерживается выбранным форматом файла. Формулы с более чем 29 аргументами функции не будут сохранены и будут преобразованы в #VALUE! исчезнут.


Что это означает.    В Excel 2007 и более поздних User-Defined функция User-Defined(UDF), создаемая с помощью Visual Basic для приложений (VBA), может содержать до 60 аргументов, но в Excel 97–2003 число аргументов в UDF ограничено VBA только 29.


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

Одна или несколько функций в этой книге недоступны в более ранних версиях Excel.  При пересчете в более ранних версиях эти функции возвращают #NAME? вместо текущих результатов.


Что это означает.    Excel 2007 и более поздних лет предоставляют новые и переименованные функции. Так как эти функции недоступны в Excel 97–2003, они возвращают #NAME? вместо ожидаемых результатов, если книга открыта в более ранней версии Excel.)

В некоторых случаях к формуле добавляется префикс _xlfn, например: =_xlfn.ЕСЛИОШИБКА (1;2).


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

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

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

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


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


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

Некоторые формулы содержат ссылки на таблицы в других книгах, которые сейчас не открыты в текущем экземпляре Excel. При сохранении в формате Excel 97–2003 эти ссылки будут преобразованы в значения #ССЫЛКА!, так как их невозможно преобразовать в адреса на текущем листе.


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


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

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


Что это означает.    В Excel 2010 г. и более поздних гг. правила проверки данных могут ссылаться на более чем 8192 невимех ячеек. В Excel 97–2007 этот тип правила проверки данных не поддерживается и будет не доступен.


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

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


Что это означает.    В Excel 2010 г. и более поздних можно использовать правила проверки данных, которые ссылаются на значения на других таблицах. В Excel 97–2007 этот тип проверки данных не поддерживается и не отображается на этом сайте.

Однако все правила проверки данных остаются доступными в книге и применяются при повторном ее повторном Excel 2010 г. или более поздней, если они не были изменены в Excel 97–2007.


Что необходимо сделать.    В средстве проверки совместимости нажмите кнопку Найти, чтобы найти ячейки, содержащие правила проверки данных, в которых есть ссылки на значения на других листах, и внесите необходимые изменения на вкладке Параметры диалогового окна Проверка данных (вкладка Данные, группа Работа с данными).

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


Что это означает.    В Excel 2007 и более поздних годах диапазоны консолидации данных могут содержать формулы, которые ссылаются на данные за пределами диапазона строк и столбцов выбранного формата файла. В Excel 97–2003 г. размер всего 256 столбцов на 65 536 строк. Формулы, ссылающиеся на данные в ячейках за пределами этого диапазона строк и столбцов, корректируются и могут отображаться неправильно.


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

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


Что это означает.    В Excel 2010 г. и более поздних Visual Basic для приложений для создания функций User-Defined (UDFs) с настраиваемой описанием. Настраиваемые описания не поддерживаются в Excel 97–2007 и будут удалены.


Что необходимо сделать.    Выполнять какие-либо действия не требуется, поскольку все настраиваемые описания будут удалены.


К началу страницы


Проблемы, которые приводят к небольшой потере точности








Проблема


Решение

Некоторые формулы массива в этой книге ссылаются на весь столбец. В более ранних версиях Excel эти формулы могут быть преобразованы в #NUM! при пересчете.


Что это означает.    Формулы массива, ссылайтесь на весь столбец в Excel 2007 г. и более поздних #NUM! при пересчете в Excel 97–2003.


Что необходимо сделать.    В средстве проверки совместимости нажмите кнопку Найти, чтобы найти формулы массива, ссылающиеся на целый столбец, и внесите необходимые изменения для предотвращения ошибок #ЧИСЛО!.

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


Что это означает.     Если именованные диапазоны в формулах превышают предел в 255 знаков, поддерживаемый в Excel 97–2003, формула работает правильно, но усекается в диалоговом окне Имя и недоступна для редактирования.


Что необходимо сделать.    В средстве проверки совместимости нажмите кнопку Найти, чтобы найти ячейки, содержащие именованные диапазоны в формулах, и внесите необходимые изменения, чтобы пользователи могли редактировать эти формулы в Excel 97–2003.

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


Что это означает.    Если формулы в одной книге связаны с другими книгами, которые закрыты, при пересчете в Excel 97–2003 эти формулы могут отобразить не более 255 знаков. Результаты формулы могут быть усечены.


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

Формула для проверки данных содержит более 255 символов.


Что это означает.    Если формула проверки данных превышает заданный в Excel 97–2003 предел в 255 знаков, формула будет работать правильно, но будет усечена и недоступна для редактирования.


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


К началу страницы


Excel не считает формулу, Эксель не рассчитывает функцию

Средняя арифметическая в Excel. Функция СРЗНАЧ

Функция СМЕЩ в Excel. Как автоматически расширить диапазон?

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

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

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

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

Содержание

  • Excel не считает формулу в ячейке
  • Excel считает формулу, но выдает ошибку
  • Функция ЗНАЧЕН()
  • Похожие статьи
Excel не считает формулу в ячейке

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

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

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

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

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

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

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

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

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

 

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

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

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

 

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

 

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

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

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

 

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

 

 

Средняя арифметическая в Excel. Функция СРЗНАЧ

Функция СМЕЩ в Excel. Как автоматически расширить диапазон?

формул Excel перестали работать? Вот несколько быстрых решений!

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

  • Формулы Excel возвращают неправильное значение
  • Формулы Excel извлекают старые значения даже после обновления новых значений в ячейках
  • Формулы Excel не выполняют вычисления

Случай 1# Формулы Excel возвращают неправильное значение

Некоторые возможные причины, по которым Excel возвращает неверные значения:

1) Нащупывание круглых скобок

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

2) Без объявления обязательных аргументов

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

При меньшем количестве аргументов: будет отображаться сообщение «Вы ввели слишком мало аргументов для этой функции».

При превышении аргументов: будет отображаться сообщение «Вы ввели слишком много аргументов для этой функции».

3) Вложение слишком большого количества функций в формулу

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

Для пользователей Excel 2016, 2013, 2010 и 2007 можно использовать 64 вложенные функции. С другой стороны, вы можете использовать только 7 вложенных функций, если работаете с Excel 2003.

Убедитесь, что вы понимаете эти ограничения, чтобы выполнять вычисления без каких-либо сообщений об ошибках.

4) Чрезмерное использование двойных кавычек

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

Если вы используете числовое значение в любой формуле, такой как СУММПРОИЗВ ({0,0,1,0,1}, {75″,100″,125″,125″,150})

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

5) Объявление форматированных чисел

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

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

    Рисунок Неверная ссылка

    Рисунок Правильная ссылка

  2. Неправильный выбор формата
    Неправильный выбор формата может привести к неправильным результатам. В примере, показанном под столбцом, C показывает значения в формате валюты $, поэтому в конце вы можете получить правильную сумму. Напротив, столбец D отображает числовые значения, но не показывает их общее количество. Причиной здесь является форматирование ячейки, которое было установлено как текст.

    Неправильный формат рисунка

    Примечание. Вы можете либо использовать Ctrl+I, либо щелкнуть правой кнопкой мыши нужную ячейку, чтобы получить параметр «Формат ячеек» и определить правильную категорию значений, которые вы ввели или хотите ввести.

6) Неправильное разделение аргументов функций

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

If(C4>0, 1)

Один и тот же аргумент функции может быть передан двумя способами

  1. If(C4>0, 1) // другие страны.
  2. If(C4>0; 1)// Точка с запятой — разделитель, обычно используемый в европейских странах.

Если вы когда-нибудь столкнетесь с сообщением об ошибке «Мы обнаружили проблему с этой формулой…», вам просто нужно изменить региональные настройки, выбрав Панель управления > Регион и язык > Дополнительные настройки для выбора правильного символа в качестве Разделителя списка для продолжения бесперебойной работы.

7) Неправильные ссылки на рабочую книгу и рабочий лист

Довольно часто вам требуются определенные значения из другого рабочего листа, рабочей книги или закрытой рабочей книги. Правило для предоставления таких ссылок заключается в предоставлении имени ссылочного объекта в одинарных кавычках («XYZ»)

  1. Для ссылки на другой лист: =СУММ («Продажи»17! B2:B10)
  2. Для ссылки на другую рабочую книгу: =SUM(‘[2015 Sales.xlsx] Sales’17’! B2:B10)
  3. Для ссылки на закрытую книгу: =SUM(‘D:\Excel Reports\[Sales. xlsx] Sales’17’! B2:B10)

Случай 2# Формулы Excel извлекают старые значения даже после обновления

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

Для разных версий Excel необходимо использовать разные пути для применения нужных настроек:

  • В Excel 2003
    Выберите Инструменты > Параметры > Расчет > Расчет > Автоматически.
  • В Excel 2007
    Кнопка «Перейти в Office» > «Параметры Excel» > «Формулы» > «Расчет в книге» > «Автоматически».
  • В Excel 2010, Excel 2013, Excel 2016 и Excel 2019
    Выберите «Файл» > «Параметры» > «Формулы» и выберите «Автоматически» в разделе «Расчет рабочей книги». принудительный пересчет программой Excel:
  • Для активного листа используйте Shift+F9.
  • Для активной книги используйте F9.
  • Для всех активных листов во всех книгах используйте Ctrl+Alt+F9.
  • Для одной формулы используйте F2.

Случай 3# Формулы Excel не могут выполнять вычисления

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

Их соответствующие решения:

  1. Либо перейдите на вкладку «Показать формулы» в меню «Формулы», либо используйте сочетание клавиш Ctrl+~.
  2. Измените формат формулы с текстового на числовой.
  3. Удалите пробел или любой специальный символ, упомянутый перед знаком равенства.

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

Подведение итогов:

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

10 причин, по которым формулы Excel не работают [и как их исправить]

«Боже мой» формулы Excel не работают в моем отчете.

Звучит знакомо, верно?

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

Просто подумай об этом.

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

[формулы ЕСЛИ с 64 условиями – просто шучу ]

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

По сути, здесь могут произойти две вещи:

Одна вы можете сказать что-то громко [ Я воздержусь от использования этого слова ].

Второй , вы приложили дополнительные усилия и исправили все формулы.

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

И как их исправить.

Формулы Excel не работают: почему и как это исправить

Согласно статье Washingtonpost об ошибках Excel, : «Австралийские исследователи обнаружили, что примерно в 1 из 5 этих статей в их списках генов были ошибки, связанные с тем, что Excel автоматически преобразовывал имена генов в такие вещи, как календарные даты или случайные числа».

Первоначальное исследование, проведенное Journal Genome Biology, показало, что из года в год наблюдается тенденция к увеличению количества ошибок.

Полные материалы исследования можно загрузить с веб-сайта SourceForge (с открытым исходным кодом) для дальнейшего чтения.

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

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

Итак, вот наши 10 основных причин, по которым формулы не работают.

#1. ВАРИАНТЫ РАСЧЕТА [НАИБОЛЕЕ ОСНОВНАЯ ПРИЧИНА НЕОБНОВЛЕНИЯ ФОРМУЛ EXCEL]

Изменение параметра расчета на «ручной» — это лучшая практика, но вы должны быть очень осторожны при ее использовании.

По умолчанию для параметра Расчет будет установлено значение автоматический в Excel.

Но есть случаи, когда вы могли бы превратить это в ‘m anual ’, чтобы ускорить процесс.

Если вы включите эту опцию « ON », ваши формулы не будут обновляться автоматически, в результате этого скорость обработки электронных таблиц улучшится.

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

Как решить эту проблему:

Перейдите в раздел «Формулы» → «Расчет» → нажмите «Рассчитать сейчас/Рассчитать лист».

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

Вот наше видео-руководство по вариантам расчета в Excel.

Еще несколько советов:

  • Заведите привычку нажимать Ctrl+S [это вызовет расчет]
  • Не используйте этот параметр для каждой книги, если только у вас нет большого файла с большим количеством формул
  • Этот параметр зависит от рабочей книги, поэтому вам необходимо включить или отключить его в каждой рабочей книге, которую вы хотите использовать

№2.

ФОРМУЛА EXCEL НЕ ВЫЧИСЛЯЕТ, ТОЛЬКО ПОКАЗЫВАЕТ ФОРМУЛУ [ПОКАЗАТЬ ФОРМУЛЫ]

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

Эта опция очень полезна для поиска всех формул на листе.

Сказав это, мы должны быть немного осторожны при использовании th e показать формулы , в противном случае вы можете получить что-то вроде ниже.

Вы видите, что сегодняшняя формула отображается как формула, а не показывает фактическую дату?

То же самое и с формулой СЛУЧМЕЖДУ.

Как решить эту проблему:

Это очень просто:

Перейдите в Формулы → Под Аудит формул → Нажмите «Показать формулы» (При первом нажатии формулы будут отображаться, а при втором щелчке они скроются за ячейками и начнут работать)

[ Пожалуйста, обратитесь к приведенному выше скриншоту]

Также читайте: 51 лучший совет и прием Excel

Если вы извлекаете данные из систем ERP в формате CSV (значения, разделенные запятыми), то ваши данные в основном подвержены нежелательным пробелам.

Знаете ли вы, что лишние пробелы вызывают много проблем при использовании формулы ВПР?

Иногда бывает сложно идентифицировать эти пробелы.

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

Теперь, если я возьму длину каждого имени, я вижу, что первая строка имеет 12, а у второго 13.

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

Это не соответствует критериям точного соответствия в формуле ВПР, что приводит к ошибке #Н/Д .

Как решить эту проблему:

Проблему с лишними пробелами можно устранить с помощью формулы Trim .
В общем, формула Trim обнаружит более одного пробела между словами и удалит.

Другие советы:

  • Всегда полезно использовать Обрезать формулу перед выполнением ВПР в обеих таблицах (таблица поиска и данных)
  • Функция обрезки будет игнорировать пробелы между буквами (работает только со словами)

№4.

ИЗБАВЬТЕСЬ ОТ НЕПЕЧАТАЕМЫХ/СКРЫТЫХ СИМВОЛОВ

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

В случае, если у вас есть данные с этими символами, лучше их снять перед дальнейшей работой.

Как решить эту проблему:

Подобно обрезке, вы можете использовать формулу CLEAN для удаления этих символов.

Совет: Рекомендуется использовать обе формулы Trim& Clean вместе

#5. ФОРМАТИРОВАНИЕ EXCEL – НЕ ПЫТАЙТЕСЬ СРАВНИВАТЬ ЯБЛОКИ С АПЕЛЬСИНАМИ

Очень важно иметь правильное форматирование перед использованием таких формул, как Vlookup, Hlookup и Match& Index.

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

Хотя для нашего невооруженного глаза они выглядят как цифры, на самом деле это не так.

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

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

Это, в свою очередь, вызовет ошибку #Н/Д при добавлении формулы ВПР.

Хуже всего то, что его немного сложно идентифицировать.

Вот еще один пример, где числа отформатированы как текст с одинарными кавычками. Это очень распространено, если вы извлекаете данные из базы данных.

Опять же, в этом случае ваша формула ВПР не будет работать.

Как решить эту проблему:

Убедитесь, что все ваши числа отформатированы как числа, а не как текст. Перед использованием формулы ВПР стоит переформатировать столбец поиска.

Еще несколько советов:

  • Как правило, числа всегда будут выравниваться по правому краю ячейки (но в нашем примере выше это не так)
  • Иногда в левом верхнем углу можно увидеть небольшой зеленый значок, указывающий на то, что с этой ячейкой что-то не так (см. второе изображение выше)
  • Просто нажмите на числовую ячейку -> Нажмите на раскрывающийся список от символа ошибки -> Нажмите «Преобразовать в число»

#6. ЦИКЛИЧЕСКИЕ ССЫЛКИ (ОШИБКИ ИЗ-ЗА БЕСКОНЕЧНОГО КРУГА)

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

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

Лично я много раз боролся с такими ошибками.

Например, в нашей приведенной ниже таблице «Бюджет расходов» нам нужно добавить вилку (номер корректировки), чтобы соответствовать целям.

Итак, мы подсчитали сумму дифференциала в строке 13, и теперь нам просто нужно поместить их в строку 9.

Один простой способ — связать ячейки строки 13 в строке 9.

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

[см. ниже]

В общем, должно работать.

Но технически это будет ошибкой, потому что наши ссылки на ячейки формул находятся в цикле.

Как решить эту проблему:

Во-первых, нам нужно идентифицировать ошибочные ячейки.

Перейдите на вкладку Формулы → щелкните стрелку раскрывающегося списка Проверка ошибок → поместите курсор на Ссылки на ячейки → теперь вы увидите диапазон(ы) ошибок

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

#7. НЕПРАВИЛЬНОЕ ИСПОЛЬЗОВАНИЕ ДВОЙНЫХ КАвычек – ЧЕЛОВЕЧЕСКАЯ ОШИБКА

Мы часто используем двойные кавычки при написании формул.

Но очень важно понимать, когда использовать, а когда нет.

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

=1234&5678 = 12345678

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

=Джон&Смит: Неправильный способ использования [скорее всего см. ошибку #NAME]

=”Джон”&”Смит” = Это правильный способ.

То же самое и с условными формулами, такими как ЕСЛИ, И, ИЛИ. Очень важно указывать текст в двойных кавычках.

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

#8. BODMAS — ОСНОВНОЕ ПРАВИЛО ДЛЯ КАЖДОГО РАСЧЕТА

Знаете ли вы, что каждый расчет в Excel основан на правиле «BODMAS»?

Ой, подождите… что такое правило БОДМАСА?

Это математическое правило для определения порядка операций.

Хорошо, давайте упростим дальше.

Можете ли вы угадать, что ответ на ниже?

10+10*2 =?

Это 40, поздравляю, вы ошиблись.

Можете ли вы попробовать тот же расчет в Excel?

Скорее всего, вы увидите 30, это действительно правильный ответ… но как?

В соответствии с правилом BODMAS расчет будет осуществляться на основе последовательности, т. е. → B-скобки, O-порядок, D-деление, M-умножение, A-сложение и S-вычитание.

Итак, последовательность вышеприведенной формулы: → 10*2 = 20 → 20+10 =30.

Ваши формулы могут ошибаться, если вы не учитываете БОДМАС.

Как решить эту проблему:

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

=(30+40)*2

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

#9. НЕПРАВИЛЬНОЕ ИСПОЛЬЗОВАНИЕ «АБСОЛЮТНОЙ» ССЫЛКИ

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

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

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

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

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

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

Но мы ошиблись.

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

Это действительно привело к неправильным результатам.

Таким образом, правильно было бы использовать абсолютную ссылку только для таких строк, как эта =СУММ(D$3:D$6).

Как решить эту проблему:

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

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

Пожалуйста, дайте мне знать ваши обходные пути на данный момент.

Другие советы:

  • Не используйте горячую клавишу F4 для частичной заморозки
  • Попробуйте вручную включить знак доллара ($) для частичного диапазона

№10.

НЕПРАВИЛЬНЫЕ АРГУМЕНТЫ ФОРМУЛЫ

Неверные аргументы формулы могут отрицательно сказаться на результатах формулы.

Просто чтобы дать вам некоторое представление, давайте разберемся с приведенным ниже синтаксисом vlookup, , особенно с последним [просмотр диапазона].

Аргумент поиска диапазона имеет два варианта выбора.

  1. Верно – приблизительное совпадение
  2. Ложь — Точное совпадение

Лично я чувствую использование «Приблизительное совпадение» Вариант подобен самоубийству.

Потому что Приблизительное соответствие никогда не даст точных результатов.

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

Если вы внимательно посмотрите, сумма дохода 25 000 для идентификатора клиента 6378993 неверна. Вместо этого должно было быть 65k.

Что же пошло не так?

Как я уже говорил, я использовал «Приблизительное совпадение» под аргумент поиска диапазона .

Читайте также: