Не работают формулы в Excel.

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

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

Включено отображение формул

В Экселе есть режим проверки вычислений. Когда он включен, вы видите на листе формулы. Как и зачем применять отображение формул, я рассказывал в . Проверьте, возможно он активирован, тогда отключите. На ленте есть кнопка Формулы – Зависимости формул – Показать формулы . Если она включена – кликните, по ней, чтобы отключить.

Часто показ формул включают случайно, нажав комбинацию клавиш Ctrl+` .

Формула воспринимается программой, как текст

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

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

Обещает быть очень интересной. Мы рассмотрим некоторые простые приемы быстрого написания формул. Если довести их до автоматизма, Ваша работа будет выполняться проще и быстрее. Подпишитесь на обновления, чтобы не пропустить!

Бывает, скопировали таблицу в Excel, а числа в ней не считаются, формулы не работают в Excel . Одной из причин может быть то, что значения ячеек в текстовом формате. Вспоминаем, в Excel есть два формата – формат ячеек и формат значений. Подробнее об этом читайте в статье «Преобразовать дату в текст Excel ».
Итак, рассмотрим, почему в Excel не работает формула .
Нужно сложить верхние числа в ячейках. В ячейках значения написаны в текстовом формате с установкой между строками непечатаемых знаков. Нам нужно преобразовать текстовый формат значений в числовой и выделить первое число в ячейке. У нас такая скопированная таблица из 1С.
Первый этап.
Преобразуем значение ячеек в числовой формат.
Выделяем ячейки. На закладке «Главная» в разделе «Редактирование» нажимаем на кнопку «Найти».
В появившемся диалоговом окне в строке «Найти» ставим запятую». В строке «Заменить» ничего не ставим.
Нажимаем кнопку «Заменить всё». Получится так.
Второй этап.
В этом же окне «Найти и заменить» (мы его не убирали и ячейки выделены остались), в строке «Найти» ставим точку. А в строке «Заменить» ставим запятую. Нажимаем «Выделить всё».

Получилось так.
Убираем окно «Найти и заменить».
Третий этап.
Как выделить первое число в ячейке Excel .
В ячейке В1 пишем такую формулу. =ЛЕВСИМВ(A1;НАЙТИ(СИМВОЛ(10);A1)-1)
Этой формулой мы выделили из ячейки первое число. Копируем формулу по столбцу. Получилось так.
В ячейке В4 мы написали формулу сложения. =B1+B2+B3
Автосумма не работает, но, формулы, написанные вручную работают.
В Excel можно установить в ячейку ссылку на другой лист, диапазон, ячейку, сайт, т.д. При нажатии на эту ссылку, Excel переходит по ней. Но, как выделить саму ячейку, не гиперссылку? Об этом читайте статью "

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

Ошибки в формуле Excel отображаемые в ячейках

В данном уроке будут описаны значения ошибок формул, которые могут содержать ячейки. Зная значение каждого кода (например: #ЗНАЧ!, #ДЕЛ/0!, #ЧИСЛО!, #Н/Д!, #ИМЯ!, #ПУСТО!, #ССЫЛКА!) можно легко разобраться, как найти ошибку в формуле и устранить ее.

Как убрать #ДЕЛ/0 в Excel

Как видно при делении на ячейку с пустым значением программа воспринимает как деление на 0. В результате выдает значение: #ДЕЛ/0! В этом можно убедиться и с помощью подсказки.

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

Результат ошибочного вычисления – #ЧИСЛО!

Неправильное число: #ЧИСЛО! – это ошибка невозможности выполнить вычисление в формуле.

Несколько практических примеров:

Ошибка: #ЧИСЛО! возникает, когда числовое значение слишком велико или же слишком маленькое. Так же данная ошибка может возникнуть при попытке получить корень с отрицательного числа. Например, =КОРЕНЬ(-25).

В ячейке А1 – слишком большое число (10^1000). Excel не может работать с такими большими числами.

В ячейке А2 – та же проблема с большими числами. Казалось бы, 1000 небольшое число, но при возвращении его факториала получается слишком большое числовое значение, с которым Excel не справиться.

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

Как убрать НД в Excel

Значение недоступно: #Н/Д! – значит, что значение является недоступным для формулы:

Записанная формула в B1: =ПОИСКПОЗ(„Максим”; A1:A4) ищет текстовое содержимое «Максим» в диапазоне ячеек A1:A4. Содержимое найдено во второй ячейке A2. Следовательно, функция возвращает результат 2. Вторая формула ищет текстовое содержимое «Андрей», то диапазон A1:A4 не содержит таких значений. Поэтому функция возвращает ошибку #Н/Д (нет данных).

Ошибка #ИМЯ! в Excel

Относиться к категории ошибки в написании функций. Недопустимое имя: #ИМЯ! – значит, что Excel не распознал текста написанного в формуле (название функции =СУМ() ему неизвестно, оно написано с ошибкой). Это результат ошибки синтаксиса при написании имени функции. Например:

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

Пустое множество: #ПУСТО! – это ошибки оператора пересечения множеств. В Excel существует такое понятие как пересечение множеств. Оно применяется для быстрого получения данных из больших таблиц по запросу точки пересечения вертикального и горизонтального диапазона ячеек. Если диапазоны не пересекаются, программа отображает ошибочное значение – #ПУСТО! Оператором пересечения множеств является одиночный пробел. Им разделяются вертикальные и горизонтальные диапазоны, заданные в аргументах функции.

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

Заданные аргументы в функции: =СУММ(B4:D4 B2:B3) – не образуют пересечение. Следовательно, функция дает значение с ошибкой – #ПУСТО!

#ССЫЛКА! – ошибка ссылок на ячейки Excel

В данном примере ошибка возникал при неправильном копировании формулы. У нас есть 3 диапазона ячеек: A1:A3, B1:B4, C1:C2.

Под первым диапазоном в ячейку A4 вводим суммирующую формулу: =СУММ(A1:A3). А дальше копируем эту же формулу под второй диапазон, в ячейку B5. Формула, как и прежде, суммирует только 3 ячейки B2:B4, минуя значение первой B1.

Когда та же формула была скопирована под третий диапазон, в ячейку C3 функция вернула ошибку #ССЫЛКА! Так как над ячейкой C3 может быть только 2 ячейки а не 3 (как того требовала исходная формула).

Примечание. В данном случае наиболее удобнее под каждым диапазоном перед началом ввода нажать комбинацию горячих клавиш ALT+=. Тогда вставиться функция суммирования и автоматически определит количество суммирующих ячеек.

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

#ЗНАЧ! – ошибка в значении. Если мы пытаемся сложить число и слово в Excel в результате мы получим ошибку #ЗНАЧ! Интересен тот факт, что если бы мы попытались сложить две ячейки, в которых значение первой число, а второй – текст с помощью функции =СУММ(), то ошибки не возникнет, а текст примет значение 0 при вычислении. Например:

Решетки в ячейке Excel

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

Так решетки (######) вместо значения ячеек можно увидеть при отрицательно дате. Например, мы пытаемся отнять от старой даты новую дату. А в результате вычисления установлен формат ячеек «Дата» (а не «Общий»).

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

Excel неправильно считает. Почему?

Часто при вычислении разницы двух ячеек в Excel можно видеть, что она не равна нулю, хотя числа одинаковые. Например, в ячейках A1 и B1 записано одно и тоже число 10,7 , а в C1 мы вычитаем из одного другое:

И самое странное то, что в итоге мы не получаем 0! Почему?

Причина очевидная - формат ячеек
Сначала самый очевидный ответ: если идет сравнение значений двух ячеек, то необходимо убедиться, что числа там действительно равны и не округлены форматом ячеек. Например, если взять те же числа из примера выше, то если выделить их -правая кнопка мыши -Формат ячеек (Format cells) -вкладка Число (Number) -выбираем формат Числовой и выставляем число десятичных разрядов равным 7:

Теперь все становится очевидным - числа отличаются и были просто округлены форматом ячеек. И естественно не могут быть равны. В данном случае оптимальным будет понять почему числа именно такие, а уже потом принимать решение. И если уверены, что числа надо реально округлять до десятых долей - то можно применить в формуле функцию ОКРУГЛ:
=ОКРУГЛ(B1 ;1)-ОКРУГЛ(A1 ;1)=0
=ROUND(B1,1)-ROUND(A1,1)=0
Так же есть более кардинальный метод:

• Excel 2007: Кнопка офис -Параметры Excel (Excel options) -Дополнительно (Advanced) -
• Excel 2010: Файл (File) -Параметры (Options) -Дополнительно (Advanced) -Задать точность как на экране (Set precision as displayed)
• Excel 2013 и выше: Файл (File) -Параметры (Options) -Дополнительно (Advanced) -Задать указанную точность (Set precision as displayed)

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

Причина программная
Но нередко в Excel можно наблюдать более интересный "феномен": разница двух дробных чисел, полученная формулой не равна точно такому же числу, записанному напрямую в ячейку. Для примера, запишите в ячейку такую формулу:
=10,8-10,7=0,1
по виду результатом должен быть ответ ИСТИНА (TRUE) . Но по факту будет ЛОЖЬ (FALSE) . И этот пример не единственный - такое поведение Excel далеко не редкость при вычислениях. Его можно встретить и в менее явной форме - когда вычисления основаны на значении других ячеек, которые тоже в свою очередь вычисляются формулами и т.д. Но причина во всех случаях одна.

Почему с виду одинаковые числа не равны?
Сначала разберемся почему Excel считает приведенное выше выражение ложным. Ведь если вычесть из 10,8 число 10,7 - в любом случае получится 0,1 . Значит где-то по пути что-то пошло не так. Запишем в отдельную ячейку левую часть выражения: =10,8-10,7 . В ячейке появится 0,1 . А теперь выделяем эту ячейку -правая кнопка мыши -Формат ячеек (Format cells) -вкладка Число (Number) -выбираем формат Числовой и выставляем число десятичных разрядов равным 15:


и теперь видно, что на самом деле в ячейке не ровно 0,1 , а 0,100000000000001 . Т.е. в 15 значащем разряде у нас появился "хвостик" в виде лишней единицы.
А теперь будем разбираться откуда этот "хвостик" появился, ведь и логически и математически его там быть не должно. Рассказать я постараюсь очень кратко и без лишних заумностей - их на эту тему при желании можно найти в интернете немало.
Все дело в том, что в те далекие времена(это примерно 1970-е годы), когда ПК был еще чем-то вроде экзотики, не было единого стандарта работы с числами с плавающей запятой(дробных, если по простому). Зачем вообще этот стандарт? Затем, что компьютерные программы видят числа по своему, а дробные так вообще со статусом "все сложно". И при этом одно и то же дробное число можно представить по-разному и обрабатывать операции с ним тоже. Поэтому в те времена одна и та же программа, при работе с числами, могла выдать различный результат на разных ПК. Учесть все возможные подводные камни каждого ПК задача не из простых, поэтому в один прекрасный момент началась разработка единого стандарта для работы с числами с плавающей запятой. Опуская различные подробности, нюансы и интересности самой истории скажу лишь, что в итоге все это вылилось в стандарт IEEE754 . А в соответствии с его спецификацией в десятичном представлении любого числа допускаются ошибки в 15-м значащем разряде. Что и приводит к неизбежным ошибкам в вычислениях. Чаще всего это можно наблюдать именно в операциях вычитания, т.к. именно вычитание близких между собой чисел ведет к потере значимых разрядов.
Подробнее про саму спецификацию так же можно узнать в статье Microsoft: Результаты арифметических операций с плавающей точкой в Excel могут быть неточными
Вот это как раз и является виной подобного поведения Excel. Хотя справедливости ради надо отметить, что не только Excel, а всех программ, основанных на данном стандарте. Конечно, напрашивается логичный вопрос: а зачем же приняли такой глючный стандарт? Я бы сказал, что был выбран компромисс между производительностью и функциональностью. Хотя возможно, были и другие причины.

Куда важнее другое: как с этим бороться?
По сути никак, т.к. это программная "ошибка". И в данном случае нет иного выхода, как использовать всякие заплатки вроде ОКРУГЛ и ей подобных функций. При этом ОКРУГЛ здесь надо применять не как в было продемонстрировано в самом начале, а чуть иначе:
=ОКРУГЛ(10,8 - 10,7 ;1)=0,1
=ROUND(10.8-10.7,1)=0,1
т.е. в ОКРУГЛ мы должны поместить само "глючное" выражение, а не каждый его аргумент отдельно. Если поместить каждый аргумент - то эффекта это не даст, ведь проблема не в самом числе, а в том, как его видит программа. И в данном случае 10,8 и 10,7 уже округлены до одного разряда и понятно, что округление отдельно каждого числа не даст вообще никакого эффекта. Можно, правда, выкрутиться и иначе. Умножить каждое число на некую величину(скажем на 1000, чтобы 100% убрать знаки после запятой) и после этого производить вычитание и сравнение:
=((10,8*1000)-(10,7*1000))/1000=0,1

Хочется верить, что хоть когда-нибудь описанную особенность стандарта IEEE754 Microsoft сможет победить или хотя бы сделать заплатку, которая будет производить простые вычисления не хуже 50-рублевого калькулятора:) Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}

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

Устраняем проблему

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

При протягивании не работают формулы

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

1. Нажмите кнопку F9, чтобы начался пересчет листа.
2. Измените формат ячейки на общий. Для этого нужно выделить ее и кликнуть правой кнопкой мыши. Откроется меню, где следует выбрать «Формат ячейки» и изменить на «Общий».
3. Откройте параметры вычисления формул и проверьте, чтобы отметки стояли, как на скрине.

Формула не считает

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

• Не верный формат ячейки. Для его изменения, нужно выделить нужный диапазон ячеек и выбрать «Формат Ячейки».

• Активирован режим «Показать формулы». Для его отключения нужно на панели сверху перейти в раздел «Формулы», там кликнуть на «Зависимости формул» и отключить «Показать формулы».

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

Это несколько основных причин, почему Эксель не считает формулы.

Программа не считает формулу, введённую в ячейку

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

Не происходит подсчет суммы

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

1. Формат ячеек является тестовым.
2. Деактивирован подсчет суммы. Нужно, используя правую кнопку мыши клацнуть на специальной панели и установить отметку на «Сумма».

Вот, почему не считается сумма.

Не верно суммируются данные ячеек

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

Отключен перерасчет

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

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

Проблемы с работой формул в Microsoft Excel

Теперь, если какое-либо значение будет изменено, произойдет пересчет результата.

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