Не работают формулы в excel 2018: Почему в эксель не считает формула

Содержание

Проблемы совместимости формул в 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 и как исправить формулу, которая не вычисляет или не обновляет автоматически.

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

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

Формулы Excel не работают

Симптомы : Формула Excel работает неправильно, возвращает ошибку или неверный результат.

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

1. Сопоставьте все открывающие и закрывающие скобки в формуле

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

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

2. Введите все необходимые аргументы в функцию Excel.

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

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

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

3. Не вкладывать более 64 функций в формулу

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

  • В Excel 2016, Excel 2013, Excel 2010 и Excel 2007 можно использовать до 64 вложенных функций.
  • В Excel 2003 и более ранних версиях можно использовать не более 7 вложенных функций.
4. Не заключайте числа в двойные кавычки

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

Это означает, что если вы введете такую ​​формулу, как =IF(A1>0, "1") , Excel будет обрабатывать число 1 как текст, и поэтому вы не сможете использовать возвращенные единицы в других расчеты. Чтобы это исправить, просто удалите двойные кавычки вокруг «1»: =IF(A1>0, 1) .

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

5. Вводите числа без какого-либо форматирования

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

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

Например, вместо того, чтобы вводить в формулу 50 000 долларов, введите просто 50000 и используйте диалоговое окно Формат ячеек (Ctrl + 1), чтобы отформатировать вывод по своему вкусу.

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

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

Визуальные индикаторы текстовых чисел следующие:

  • Числа, отформатированные как текст, по умолчанию выравниваются по левому краю, тогда как обычные числа в ячейках выравниваются по правому краю.
  • В поле Числовой формат на вкладке Главная в группе Число отображается формат Текст .
  • Когда на листе выбрано несколько ячеек с текстовыми номерами, в строке состояния отображается только Count , в то время как обычно для чисел отображается Average , Count и SUM .
  • В строке формул может быть виден начальный апостроф, или в верхнем левом углу ячеек могут отображаться зеленые треугольники.

На приведенном ниже снимке экрана показано, что даже простая формула СУММА в Excel может не работать из-за чисел, отформатированных как текст:

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

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

На первый взгляд, следующая формула работает нормально:
=IF(A1="Хорошо", "1", "0")

Но проблема в том, что возвращаемые единицы и нули представляют собой текстовых значений. , а не цифры! И если вы ссылаетесь на какие-либо ячейки с приведенной выше формулой в других формулах, эти ячейки не будут включены в расчеты. Как только вы удалите «» вокруг 1 и 0 в приведенной выше формуле, Excel будет обрабатывать выходные данные как числа, и они будут рассчитаны правильно.

Если маленькие зеленые треугольники не появляются в ячейках по какой-либо другой причине, посмотрите на Числовой формат на вкладке Домашний в группе Номер . Если он отображает Text , попробуйте очистить все форматирование для проблемных ячеек и установить формат ячеек на Number или General . Если это не сработает, вам, возможно, придется создать новый столбец, ввести данные вручную (например, скопировать текстовые числа в Блокнот, а затем вернуться в новый столбец) и удалить неработающий столбец.

Другое возможное решение — умножить значения в проблемном столбце на 1, используя простую формулу, например 9.0057 =А1*1 . Затем скопируйте ячейки формулы и вставьте их как значения в тот же или любой другой столбец с помощью Специальная вставка > Значения .

7. Разделяйте аргументы функций соответствующим символом

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

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

Например, в Северной Америке вы должны написать =ЕСЛИ(A1>0, "ОК", "Не OK") , в то время как европейские пользователи Excel должны ввести ту же формулу, что и =ЕСЛИ(A1>0; " ОК"; "Не ОК") .

Итак, если ваши формулы Excel не работают из-за » Мы обнаружили проблему с этой формулой…» ошибка, перейдите к Региональные настройки ( Панель управления > Регион и язык > Дополнительные настройки ) и проверьте, какой символ установлен как Разделитель списка Затем используйте именно этот символ для разделения аргументов в формулах Excel. 0088 пробелов или неалфавитных символов в их именах, заключайте имена в «одинарные кавычки». Например,

Ссылка на другой лист:
=СУММ('Продажи за январь'!B2:B10)

Ссылка на другую книгу:
=СУММ('[Продажи за 2015 год.xlsx]Продажи за январь'!B2: B10)

Дополнительные сведения см. в разделе Как сделать ссылку на другой лист или книгу в Excel.

9. Включите полный путь к закрытой книге

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

=SUM('D:\Reports\[Sales.xlsx]Jan'!B2:B10)

Дополнительные сведения см. в разделе Создание ссылки на другую книгу.

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

Формулы Excel не обновляются

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

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

На ленте Excel перейдите на вкладку Формулы > группу Расчет , нажмите кнопку Параметры расчета и выберите Автоматически :

Альтернативно, вы можете изменить этот настройка в Options :

  • в Excel 2003 , клик Инструменты > Опции > Расчет > Расчет > Automatic .
  • В Excel 2007 нажмите кнопку Office > Параметры Excel > Формулы > Расчет рабочей книги > Автоматически .
  • В Excel 2010 , Excel 2013 и Excel 2016 , перейдите к Файл > Варианты > Формулы > Варианты расчета и выберите Automatic под Расчет рабочей книги .

Как принудительно пересчитать формулы Excel

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

Чтобы пересчитать всю книгу :

  • Нажмите F9 или
  • Нажмите кнопку Рассчитать сейчас на вкладке Формулы > Группа расчетов .

Чтобы пересчитать активный лист :

  • Нажмите Shift + F9 или
  • Нажмите Расчетный лист на вкладке Формулы > Группа расчета .

Чтобы пересчитать все листы во всех открытых книгах , нажмите Ctrl + Alt + F9.

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

Формулы Excel не вычисляют

Симптомы : В ячейке отображается формула, а не результат.

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

1. Включен режим «Показать формулы»

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

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

  • Нажав комбинацию клавиш Ctrl + `, или
  • Нажатие кнопки Показать формулы на вкладке Формулы > Формула Аудит группы.
2. Формула введена как текст

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

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

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

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

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

Вас также может заинтересовать

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

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

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

Важно:

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

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

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

В этой статье

  • Проблемы, вызывающие значительную потерю функциональности

  • Проблемы, вызывающие незначительную потерю точности

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

Выпуск

Решение

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Что это означает     Excel 2007 и более поздних версий предоставляют новые и переименованные функции. Поскольку эти функции недоступны в Excel 97-2003, они будут возвращать #ИМЯ? ошибка вместо ожидаемых результатов при открытии книги в более ранней версии Excel..

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

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

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

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

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

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

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

Некоторые формулы содержат ссылки на таблицы в других книгах, которые в данный момент не открыты в этом экземпляре Excel. Эти ссылки будут преобразованы в #REF при сохранении в Excel 9.7-2003, поскольку их нельзя преобразовать в ссылки на листы.

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

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

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

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

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

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

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

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

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

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

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

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

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

Что это означает     В Excel 2010 и более поздних версиях можно использовать Visual Basic для приложений (VBA) для создания определяемых пользователем функций (UDF) с пользовательскими описаниями. Пользовательские описания не поддерживаются в Excel 97-2007 и будут удалены.

Что делать     Действий не требуется, поскольку все пользовательские описания будут удалены.

Верх страницы

Проблемы, вызывающие незначительную потерю точности

Выпуск

Решение

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

Что это означает     Формулы массива, ссылающиеся на весь столбец в Excel 2007 и более поздних версиях, будут преобразованы и отображены как #ЧИСЛО! ошибки при их пересчете в Excel 97-2003.

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

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

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

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

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

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

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

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

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