Почему Эксель не считает формулу: 5 решений проблемы

Устранение проблем с вычислением

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

Способ 1: изменение формата ячеек

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

1. Чтобы узнать, какой формат имеет конкретная ячейка или диапазон, перейдите на вкладку «Главная». На ленте в панели инструментов Number есть поле для отображения текущего макета. Если указан «Текст», формула не будет рассчитана точно.



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



3. Но выбор типов форматов через ленту не такой широкий, как через специальное окно. Поэтому лучше использовать второй вариант форматирования. Выберите диапазон назначения. Щелкните по нему правой кнопкой мыши. В контекстном меню выберите пункт «Форматировать ячейки». Либо после выбора диапазона нажмите комбинацию клавиш Ctrl + 1.



4. Откроется окно форматирования. Перейдите во вкладку «Число». В блоке «Числовые форматы» выбираем нужный нам формат. Также в правой части окна вы можете выбрать тип презентации определенного формата. Сделав свой выбор, нажмите кнопку «ОК» внизу.



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

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

Способ 2: отключение режима «Показать формулы»

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

1. Чтобы активировать отображение итогов, перейдите на вкладку «Формулы». На ленте в наборе инструментов «Зависимости формул», если кнопка «Показать формулы» активна, щелкните ее.



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

Способ 3: исправление ошибки в синтаксисе

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

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

Способ 4: включение пересчета формулы

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

1. Перейдите на вкладку «Файл». Пока вы в нем, вы должны нажать на запись «Параметры».



2. Откроется окно настроек. Вам необходимо перейти в раздел «Формулы». В блоке настроек «Параметры расчета», который находится в верхней части окна, если параметр «Расчеты в книге» не установлен в положение «Автоматически», результат расчетов не имеет значения. Переставляем переключатель в нужное положение. После завершения вышеуказанных настроек, чтобы сохранить их в нижней части окна, нажмите кнопку «ОК».

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

9 распространенных ошибок Excel, которые вы бы хотели исправить

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

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

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

Проблема: неправильный синтаксис.

Если синтаксис функции построен неправильно, она может вернуть #VALUE! Ошибка «# РАСЧЕТ!».

Решение: проверьте правильность синтаксиса. Ниже приведен пример хорошо сформированной формулы, в которой функция ЕСЛИ вложена в другую функцию ЕСЛИ для расчета вычетов на основе дохода.

= SE (E2 <31500; E2 * 15%; SE (E2 <72500; E2 * 25%; E2 * 28%))

Проще говоря, это можно выразить так: ЕСЛИ значение в ячейке A5 меньше 31 500, значение умножается на 15%. Но если это не так, проверьте, не меньше ли значение 72 500. ЕСЛИ это так, значение умножается на 25%; в противном случае — на 28 %.

Чтобы использовать функцию ЕСЛИ ОШИБКА с существующей формулой, просто вложите подготовленную формулу в функцию ЕСЛИ ОШИБКА:

= ЕСЛИОШИБКА (ЕСЛИ (E2 <31500; E2 * 15%; ЕСЛИ (E2 <72500; E2 * 25%; E2 * 28%)); 0)

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

Примечание. Значения в расчетах разделяются точкой с запятой. Если вы разделите два значения запятой, функция ЕСЛИ будет рассматривать их как одно дробное значение. Коэффициенты процентного соотношения сопровождаются символом%. Сообщает Excel, что значение следует рассматривать в процентах. В противном случае эти значения следует вводить как дробные множители, например «E2 * 0,25″.

1. Excel пишет #ЗНАЧ!

#ЗНАЧ! в ячейке что это

Ошибка # ЗНАЧ! появляется, когда формула содержит пробелы, символы или текст вместо числа. Различные типы данных. Например, формула = A15 + G14, где ячейка A15 содержит «число», а ячейка G14 — «слово».

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

Как исправить #ЗНАЧ! в Excel

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

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

9 ошибок Excel, которые вас достали

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

Ошибка #ПУСТО!

Ошибка № ПУСТО! в Excel возникает, когда в формуле используются неперекрывающиеся диапазоны.

Ошибка № N / A

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

Причины ошибки № N / A:

1. При использовании функций VLOOKUP, HORIZ LOOK, SEARCH, SEARCH используется недопустимый аргумент lookup_value:

Несколько полезных приемов в Excel

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

Каждую формулу начинайте со знака равенства «=». Используйте * для умножения чисел, а не X. Найдите все открывающие и закрывающие скобки «()», чтобы они были попарно. Используйте двойные кавычки вокруг текста в формулах.

4. #ДЕЛ/0! в Excel

В случае # DIV / 0 !, вы просите Excel разделить формулу на ноль или пустую ячейку. Подобно тому, как эта задача не будет работать вручную или на калькуляторе, она не будет работать и в Excel.

Как устранить #ДЕЛ/0!

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

Удаление пробелов, которые вызывают ошибку #ЗНАЧ!

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

1. Выберите ячейки, на которые указывают ссылки

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

2. Найдите и замените

Найдите и выберите> Заменить «loading =» lazy» />

На вкладке «Главная» нажмите «Найти и выбрать»> «Заменить.

3. Удалите пробелы

В поле «Найти» введите пробел. Затем в поле «Заменить» удалите все, что может быть там.

4. Замените одно или все вхождения

Если вы уверены, что вам нужно удалить все пробелы в столбце, нажмите «Заменить все». Если вы хотите просмотреть и удалить пробелы по отдельности, вы можете сначала нажать «Найти далее», а затем «Заменить», если вы уверены, что пространство вам не нужно. Впоследствии #VALUE! он должен исчезнуть. Если нет, переходите к следующему шагу.

5. Включите фильтр

Щелкните стрелку фильтра
и снимите флажок Выбрать все. Затем установите флажок «Пусто.

7. Установите все флажки без названия

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

8. Выделите пустые ячейки и удалите их

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

9. Очистите фильтр

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

Решение 3: активируем автоматический пересчет формул

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

1. Зайдите в меню «Файл”.
   
2. В списке слева выберите раздел «Параметры”.
   
3. В появившемся окне перейдите в подраздел «Формулы». В правой части окна в группе «Параметры расчета» поставьте галочку напротив опции «автоматический», если выбран другой вариант. Когда все будет готово, нажмите ОК.
   
4. Все готово, с этого момента все результаты по формулам будут автоматически пересчитаны.

Выполнение базового вычитания

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

Вычтите одну ссылку на ячейку из другой

Введите два значения в две отдельные ячейки. В третьей ячейке вычтите ссылку на одну ячейку из другой. В этом примере ячейка D2 содержит плановую сумму, а ячейка E2 — фактическую сумму. F2 содержит формулу = D2-E2.

Или используйте функцию СУММ с положительными и отрицательными числами

Введите положительное значение в одну ячейку и отрицательное значение в другую. В третьей ячейке используйте функцию СУММ, чтобы добавить две ячейки. В этом примере ячейка D6 содержит плановую сумму, а ячейка E6 содержит фактическую сумму в виде отрицательного числа. F6 содержит формулу = СУММ (D6; E6).

8. Ячейка Excel выдает ошибку #ЧИСЛО!

Если ваша формула содержит недопустимые числовые значения, отобразится ошибка # ЧИСЛО !. Это часто происходит при вводе числового значения, которое отличается от других аргументов, используемых в формуле.

Кроме того, при вводе формулы исключите такие значения, как 1000 долларов США в денежном формате. Вместо этого введите 1000, а затем отформатируйте ячейку с помощью валюты и запятых после вычисления формулы. Просто число без знака $ (доллар).

Решение 2: отключаем режим “Показать формулы”

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

1. Перейдите на вкладку «Формулы». В группе инструментов «Зависимость формул» нажмите кнопку «Показать формулы», если она активна.
   
2. В результате результаты расчета теперь будут отображаться в ячейках с формулами. Правда, из-за этого могут меняться границы столбцов, но это поправимо.
   

5. #ССЫЛКА! в ячейке

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

Что такое ошибка #ССЫЛКА! в Excel?

#СВЯЗЬ! появляется, если вы используете формулу, относящуюся к несуществующей ячейке. Если вы удалите ячейку, столбец или строку из таблицы и создадите формулу, включающую имя удаленной ячейки, Excel вернет #REF! в ячейке, содержащей эту формулу.

Что же на самом деле означает эта ошибка? Возможно, вы случайно удалили или вставили данные в ячейку, используемую формулой. Например, ячейка B16 содержит формулу = A14 / F16 / F17.

Если мы удалим 17-ю строку, как это часто бывает с пользователями (не совсем 17-я строка, но . вы меня понимаете!), Мы увидим эту ошибку.

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

Как исправить #ССЫЛКА! в Excel?

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

Ошибка #ЗНАЧ! при вычитании дат в текстовом формате

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

Контроль основных пространств

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

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

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

3. Выберите столбец, содержащий дату, щелкнув его заголовок.

4. Выберите Данные> Текст по столбцам.

5. Дважды щелкните Далее.

6. На шаге 3 из 3 мастера в разделе Формат данных столбца выберите переключатель Дата.

7. Выберите формат даты и нажмите Готово.

8. Повторите эти шаги для других столбцов, чтобы убедиться, что они не содержат пробелов перед датами.

Проверьте настройки даты на компьютере

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

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

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

1. Создайте такую ​​формулу: = DATE (RIGHT (A1; 4); LEFT (A1; 2); MID (A1; 4; 2))

2. Результат будет 31.12.2017.

3. Чтобы использовать формат дд.мм.гг, нажмите CTRL + 1 (или
   +1 на Mac).

4. Выберите другой язык, использующий формат дд.мм.гг, например немецкий (Германия). После применения формата результатом будет 31 декабря 2017 г., и это будет фактическая дата, а не ее текстовый ввод.

Примечание. Приведенная выше формула написана с использованием функций ДАТА, ВПРАВО, СРЕДНЕЕ и ВЛЕВО. Обратите внимание, что формула написана исходя из предположения, что в дате текста используются два символа для дней, два символа для месяцев и четыре символа для года. Возможно, вам потребуется изменить формулу, чтобы она соответствовала введенной дате.

2. Ошибка Excel #ИМЯ?

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

Почему в ячейке стоит #ИМЯ?

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

Нет, в этом случае Excel не ищет ваше имя. Ошибка # ИМЯ? появляется в ячейке, когда она не может прочитать определенные элементы формулы, которую вы пытаетесь выполнить.

Например, если вы попытаетесь использовать формулу = A15 + C18 и вместо латинской «A» вы наберете «A» на русском языке, после ввода значения и нажатия Enter Excel вернет #NAME?.

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

Как исправить #ИМЯ? в Экселе?

Чтобы исправить ошибку #NAME ?, проверьте правильность написания формулы. Если написано правильно, а ваша электронная таблица по-прежнему выдает ошибку, Excel, скорее всего, запутался в одной из ваших записей в этой формуле. Простой способ решить эту проблему — попросить Excel ввести формулу.

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

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

Ошибка ####

Причины ошибки

1. Столбец недостаточно широк для отображения содержимого ячейки.

Ошибка #ЗНАЧ! при базовом вычитании

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

1. Сначала сделайте быструю проверку. В новой книге введите 2 в ячейку A1. Введите 4 в ячейку B1. Затем введите формулу = B1-A1 в ячейку C1. Если #VALUE! перейти к следующему шагу. Если сообщение об ошибке не появляется, попробуйте другие решения на этой странице.

2. В Windows откройте панель управления региональными параметрами».

   • Windows 10. В поле поиска введите регион и выберите Региональные параметры панели управления.

   • Windows 8. На начальном экране введите регион, щелкните «Параметры», затем щелкните «Региональные стандарты.

   • Windows 7. Нажмите кнопку «Пуск», введите регион и щелкните «Язык и региональные стандарты.

3. На вкладке «Форматы» нажмите кнопку «Дополнительные параметры.

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

5. Щелкните ОК.

6. Открыть книгу. Если ячейка содержит ошибку #VALUE!, Дважды щелкните ее, чтобы отредактировать.

7. Если в местах вычитания знаков минус есть запятые, замените их знаками минус».

8. Нажмите Ввод.

9. Повторите эти шаги для других ячеек, в которых возникает ошибка.

3. Excel отображает ##### в ячейке

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

Как в Excel убрать решетки из ячейки?

Щелкните правую границу заголовка столбца и увеличьте ширину столбца.