Почему не работают формулы в Excel

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

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

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

В данной статье мы как раз-таки и рассмотрим эти стандартные ошибки.

Итак, начнём!

Формула вызвана неправильно

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

К примеру, возьмем функцию ВПР. У неё 3 аргумента, которые пропустить никак нельзя, а также 1 дополнительный, который можно и не прописывать.

Допустим, у нас есть такая табличка:

Вот банальный пример из-за которого может возникнуть ошибка(или мы получим неверный результат).

=ВПР(A2;A2:C6;2;FALSE)

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

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

Допустим, у нас есть такая табличка:

Используем ВПР таким образом:

=ВПР(E2;$A$2:$C$6;2)

Аргумент соответствия мы не указали и посмотрите что стало с расчетами! Мы получили 2 неверных значения, а во всех остальных случаях вообще ошибки.

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

Пробелы в значениях

Их довольно сложно заметить, но из-за них может быть куча ошибок.

Допустим, такой пример:

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

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

Если мы удалим пробел — все заработает как надо.

Чтобы не проверять вручную каждую ячейку, мы можем заменить все двойные пробелы на один. Или использовать функцию СЖПРОБЕЛЫ.

Комбинация функций для нашего примера:

=ВПР(СЖПРОБЕЛЫ(D2);$A$2:$B$6;2;0)

Самое лучшее, конечно, просто обработать свою табличку, чтобы двойных или тройных пробелов не было.

Ручной режим расчета в формулах

Вы можете не знать что такое режимы расчета. Я сейчас объясню:

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

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

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

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

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

Что делать? Либо нажать F9 и заставить Excel выполнить перерасчет, либо включить автоматический режим, как и должно быть.

Как это сделать?

Пошаговая инструкция:

• Щелкните «Формулы»;

• В опции «Параметры вычислений» укажите «Автоматически».

Ошибка #REF

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

Обычно Excel автоматически скорректирует вашу функцию, но так бывает не всегда.

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

Вот пример:

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

Исправить эту ошибку довольно просто — обновите ссылки в формуле.

Неправильный порядок вычислений

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

Например, у нас есть такой пример:

=5+10*50

Сначала выполняется умножение, а потом только сложение.

Но что если нужно сначала выполнить сложение? Тогда мы можем заключить сложение в скобки:

=(5+10)*50

Так вот, если вы неправильно использовали, например, эти скобки в формуле — результат будет неверен.

Неправильное указание ссылок

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

Читатели нашего сайта знаю, что есть 3 типа ссылок в Excel. Но для нас, сейчас, важны только 2 — относительный и абсолютный тип. Относительный тип — который будет адаптироваться под каждую новую ячейку, а абсолютный — который изменяться не будет.

Давайте сразу рассмотрим пример.

Нам нужно вывести количество баллов за Exam 1.

Используем такую формулу:

=ВПР(E2;A2:B6;2;0)

Вот и ошибка. Но в чем же она?

Мы не заключили область поиска в $, то есть не сделали ссылку абсолютной и Excel адаптировал её для каждой последующей ячейки. Тут и кроется ошибка.

Есть способ быстро сделать ссылку абсолютной — выделите ячейку (или диапазон) в формуле и нажмите F4.

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

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

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

=Лист1!A1

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

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

='[пример.xlsx]лист1'!$A$1

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

='C:\Users\Excel\Desktop\[пример.xlsx]лист1'!$A$1

Но если вы измените название файла или листа — получите ошибку.

Циклическая ссылка

Что это? Это когда вы указываете ссылку на ту ячейку, которая является источником формулы.

Например, мы прописываем эту формулу в A4

=СУММ(A1:A4)

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

К счастью, легко проверить где у вас есть циклические ссылки.

Щелкните на «Формулы» и «Проверка ошибок», там увидите параметр «Циклические ссылки», наведите на него и увидите ячейки, где Excel заметил циклические ссылки.

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

Если вы видите что-то подобное:

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

Как сделать так, чтобы формула снова выполнялась:

1. Поменяйте формат ячейки на «Общий»;
2. А затем пересохраните значение ячейки.

Если вам это не помогло — проверьте есть ли “‘“ перед формулой (это можно посмотреть только в режиме редактирования).

Преобразование чисел в дату

Excel внимательно следит за тем, что вы вводите. И если что-то из введенного похоже на дату — он немедленно преобразует число в дату.

Часто это помогает, но иногда, просто раздражает.

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

Как это сделать?

Пошаговая инструкция:

• Выделите элементы, которые нужно преобразовать;
• Щелкните на «Главная»;

• И укажите формат «Текст».

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

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

Скрытые элементы

Сразу рассмотрим пример.

У нас есть столбик С с значениями продаж:

Давайте найдем сумму всех продаж:

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

Допустим, включим отображение продаж принтеров:

Видим неожиданное — результат не поменялся.

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

Чтобы правильно считать сумму в таких ситуациях — используйте функцию СУММ в комбинации с ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

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

Надеюсь, эта статья оказалась полезна для вас!