Excel не читает формулы

Обновлено: 21.11.2024

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

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

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

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

1. Параметры расчета установлены вручную

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

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

  1. Откройте таблицу, с которой у вас возникли проблемы.
  2. Затем на ленте перейдите на вкладку "Формулы" и выберите "Расчет".
  3. Выберите «Параметры расчета» и выберите «Автоматически» в раскрывающемся списке.

Кроме того, вы можете настроить параметры расчета в параметрах Excel.

Нажмите кнопку Office в верхнем левом углу > Параметры Excel > Формулы > Расчет в книге > Автоматически.

2. Ячейка отформатирована как текст

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

Лучший способ проверить форматирование — щелкнуть ячейку и проверить группу "Число" на вкладке "Главная". Если он отображает «Текст», щелкните его и выберите «Общие». Чтобы пересчитать формулу, дважды щелкните ячейку и нажмите клавишу ВВОД на клавиатуре.

3. Кнопка «Показать формулы» включена

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

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

Перейдите на вкладку "Формула" > "Группа аудита формул" и нажмите кнопку "Показать формулы".

4. Пробел перед знаком равенства

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

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

5. Заставить Excel пересчитать

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

Чтобы пересчитать всю электронную таблицу, нажмите клавишу F9 на клавиатуре или выберите "Рассчитать сейчас" на вкладке "Формула". Кроме того, вы можете пересчитать активный лист, нажав Shift + F9 на клавиатуре или выбрав «Рассчитать лист» в группе «Расчет» на вкладке «Формула».

Вы также можете пересчитать все формулы на всех листах, нажав комбинацию клавиш Ctrl + Alt + F9. Кроме того, если вы предпочитаете пересчитывать только одну формулу с листа, вы можете выбрать ячейку и нажать Enter.

Решите проблемы с Excel с помощью этих исправлений

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

Хотя Excel может быть вашим предпочтительным приложением, существует множество других подобных приложений, таких как Google Таблицы, и описанные выше методы будут работать со всеми ними.

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

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

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

Этот блог также доступен на YouTube.

Возможная причина 1: ячейки отформатированы как текст

Описание: ячейка содержит что-то похожее на формулу, но формула не вычисляет.Вместо этого текст самой формулы появляется непосредственно в ячейке. См. пример ниже.

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

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

Способ 1. Перейти в затронутую ячейку (щелчок мышью или F2), а затем сразу же снова выйти. Это быстрее для одной формулы, но если формул много, это займет много времени, и вместо этого вам следует использовать метод 2.

Способ 2. Используйте функцию «Найти и заменить» (Ctrl + F). Выберите замену = на =, и это заставит Excel обновить формулы и начать вычисления в обычном режиме.

​Возможная причина 2: рабочая книга настроена на ручной расчет

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

Причина. Рабочая книга была переведена в режим «Ручной расчет». Это будет означать, что Excel не обновляет автоматически все формулы всякий раз, когда в книгу вносятся изменения, и его необходимо принудительно принудительно сделать это вручную. Это также означает, что если вы скопируете и вставите формулу (например, формулу сложения, показанную справа), результат будет скопирован вместо правильного ответа до тех пор, пока не будет использован запрос на перерасчет вручную.

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

Способ 1. Вы можете принудительно выполнить перерасчет вручную, нажав кнопку "Рассчитать" в левом нижнем углу Excel или нажав клавишу F9.

Способ 2. Вы можете снова переключить книгу на автоматический расчет, выбрав Формулы => Параметры расчета.

Эти два являются причиной 99 % проблем такого типа.

Последняя проверка: декабрь 2021 г.

Сообщество Excel

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

Быть впереди в эпоху цифровых технологий

Финансы ICAEW в цифровом мире – это набор модулей онлайн-обучения, помогающих участникам повышать осведомленность и лучше понимать цифровые технологии и их влияние на финансы.

Время от времени Excel может вести себя странно или неожиданно. Одним из примеров является случайная активация функции блокировки прокрутки. Другой пример — когда одна или несколько формул внезапно перестают работать. Вместо результата вы видите только формулу. Что?

Вы ввели формулу, но Excel не вычисляет результат?

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

Формула ВПР верна, почему нет результата?

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

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

  1. Вы случайно включили отображение формул.
  2. Excel считает, что ваша формула является текстом

Я рассмотрю каждый случай с несколькими примерами.

Показать формулы включено

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

Показывать формулы отключено (обычный режим)

Показать формулы включено

Чтобы проверить, включен ли параметр "Показать формулы", перейдите на вкладку "Формула" на ленте и нажмите кнопку "Показать формулы":

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

Причина, по которой функция "Показать формулы" может быть включена случайно, заключается в том, что у нее есть сочетание клавиш (Control `), которое пользователь может ввести по незнанию. Попробуйте Control ` на листе, чтобы увидеть, как это работает. Вы увидите, что можете быстро включать и выключать все формулы.

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

Excel считает, что ваша формула является текстом

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

Нет знака равенства

Во-первых, возможно, вы забыли знак равенства. Все формулы в Excel должны начинаться со знака равенства (=). Если вы опустите это, Excel будет просто обрабатывать формулу как текст:

Пример неполной формулы – без знака равенства (=)

Пробел перед знаком равенства

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

Формула в кавычках

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

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

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

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

Для справки, вот рабочая формула:

Текстовый формат ячейки

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

Если это так, установите формат «Общий» или другой подходящий числовой формат. Возможно, вам потребуется войти в режим редактирования ячейки (щелкните в строке формул или нажмите F2, а затем введите), чтобы Excel распознал изменение формата. Затем Excel должен оценить как формулу.

Совет. Сохраните текущую формулу в виде текста

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

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

Уровень навыков: начинающий

Посмотреть обучающее видео

Загрузить файл Excel

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

График погашения кредита – Пример расчета вручную.xlsx

Почему мои формулы не работают?

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

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

И это происходило только на его домашнем компьютере. Его рабочий компьютер работал нормально. Это сводило его с ума и отнимало много времени.

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

Чтобы проверить, в каком режиме расчета находится Excel, перейдите на вкладку "Формулы" и нажмите "Параметры расчета". Это вызовет меню с тремя вариантами. Текущий режим будет отмечен галочкой. На изображении ниже видно, что Excel находится в режиме ручного расчета.

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

Настройки расчета сбивают с толку!

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

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

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

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

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

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

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

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

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

3 варианта расчета

В Excel есть три варианта расчета.

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

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

Автоматически, кроме таблиц данных, означает, что Excel будет автоматически пересчитывать все ячейки, кроме тех, которые используются в таблицах данных. Это не относится к обычным таблицам Excel, с которыми вы можете часто работать. Это относится к инструменту анализа сценариев, которым пользуются немногие. Вы найдете его на вкладке «Данные» под кнопкой «Что, если сценарии».Поэтому, если вы не работаете с этими таблицами данных, маловероятно, что вы когда-либо намеренно измените настройку на этот параметр.

Помимо параметра "Расчет" на вкладке "Данные", его также можно найти в меню "Параметры Excel". Перейдите в «Файл», затем «Параметры», затем «Формулы», чтобы увидеть те же параметры настройки в окне «Параметры Excel».

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

Нажмите, чтобы увеличить

Зачем использовать режим ручного расчета?

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

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

Сочетание клавиш для функции "Рассчитать сейчас" — F9, и она рассчитает всю книгу. Если вы хотите рассчитать только текущий рабочий лист, вы можете нажать кнопку под ним: Рассчитать лист. Сочетание клавиш для этого выбора — Shift + F9 .

Вот список всех сочетаний клавиш для пересчета:

ЯрлыкОписание
F9Пересчитать формулы, которые имеют измененные с момента последнего вычисления, и формулы, зависящие от них, во всех открытых книгах. Если рабочая книга настроена на автоматический пересчет, вам не нужно нажимать F9 для пересчета.
Shift+F9 Пересчитать формулы, которые изменились с момента последнее вычисление и формулы, зависящие от них, на активном листе.
Ctrl+Alt+F9 Пересчитать все формулы во всех открытых рабочих книгах, независимо от изменились ли они с момента последнего пересчета.
Ctrl+Shift+Alt+F9Проверить зависимые формулы, а затем пересчитать все формулы во всех открытых книгах , независимо от того, изменились ли они с момента последнего пересчета.
Источник: Microsoft: статья о пересчете формул изменений

Макрос переходит в режим ручного расчета

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

Теперь я хочу предварить это, сказав, что проблема вызвана НЕ всеми макросами. Это определенная строка кода, которую разработчик может использовать для ускорения работы макроса.

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

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

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

Однако проблема возникает, когда макрос не завершается — возможно, из-за ошибки, сбоя программы или неожиданной системной проблемы. Макрос изменяет настройку на Вручную, и она не возвращается обратно.

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

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

Я также рекомендую НЕ изменять свойство Calculation с помощью кода, если в этом нет крайней необходимости. Это поможет предотвратить разочарование и ошибки пользователей ваших макросов.

Заключение

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

Может ли кто-нибудь представить себе использование Microsoft 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 долларов, введите просто 50 000 и используйте диалоговое окно Формат ячеек ( Ctrl + 1 ), чтобы отформатировать вывод по своему вкусу.

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

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

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

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

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

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

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

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

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

Если маленькие зеленые треугольники не отображаются в ячейках по какой-либо другой причине, посмотрите на поле Формат числа на вкладке Главная в группе Число. Если отображается Текст, попробуйте очистить все форматирование для проблемных ячеек и установить формат ячеек на Число или Общий. Если это не сработает, возможно, вам придется создать новый столбец, ввести данные вручную (например, скопировать текстовые числа в Блокнот, а затем вернуться в новый столбец) и удалить неработающий столбец.

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

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

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

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

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

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

8. Заключите имена книг и листов в одинарные кавычки

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

Ссылка на другой лист:
=SUM('Jan Sales'!B2:B10)

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

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

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

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

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

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

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

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

Кроме того, вы можете изменить этот параметр в параметрах Excel:

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

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

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

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

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

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

  • Нажмите Shift + F9 или
  • Нажмите «Вычислить лист» на вкладке Формулы >группа «Расчет».

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

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

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

Проблема: в ячейке отображается формула, а не результат.

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

1. Режим "Показать формулы" включен

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

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

  • Нажатие сочетания клавиш Ctrl + `или
  • Нажав кнопку "Показать формулы" на вкладке Формулы в группе Аудит формул.

2. Формула вводится как текст

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

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

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

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

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

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