Не работают формулы Excel: возможные причины и как это исправить!

Если вы работаете с формулами в Excel, рано или поздно вы столкнетесь с проблемой, когда формулы Excel вообще не работают (или дают неверный результат).

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

Но поскольку мы живем в мире, который следует принципу Парето, если вы проверите некоторые общие проблемы, это, вероятно, решит 80% (или, возможно, даже 90% или 95% проблем, где формулы не работают в Excel).

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

Итак, приступим!

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

Позвольте мне начать с указания очевидного.

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

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

Например, функция ВПР принимает три обязательных аргумента и один необязательный аргумент.

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

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

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

= ВПР (A2; A2: C6,2; ЛОЖЬ)

В этом случае формула вычисляет (поскольку возвращает значение), но результат неверен (вместо оценки за экзамен 2 он дает оценку за экзамен 1).

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

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

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

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

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

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

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

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

Также прочтите: Выявление ошибок с помощью отладки формул Excel

Лишние пробелы, приводящие к неожиданным результатам

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

Например, в приведенном ниже примере, если я пытаюсь использовать ВПР для получения оценки для Марка, это дает мне ошибку # N / A (ошибка недоступности).

Хотя я вижу, что формула верна и имя «Отметка» явно есть в списке, я не вижу, что в ячейке с именем есть конечный пробел (в ячейке D2).

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

Чтобы решить эту проблему, вам необходимо удалить эти лишние пробелы.

Вы можете сделать это любым из следующих способов:

1. Очистите ячейку и удалите все начальные / конечные пробелы, прежде чем использовать ее в формулах.
2. Используйте функцию TRIM в формуле, чтобы игнорировать любые начальные / конечные / двойные пробелы.

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

= ВПР (ОБРЕЗАТЬ (D2); $ A $ 2: $ B $ 6,2,0)

Хотя я взял пример ВПР, это также обычная проблема при работе с функциями ТЕКСТА.

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

Убрать / Как исправить: Если возможно, очистите данные, удалив все начальные / конечные или двойные пробелы, прежде чем использовать их в формулах. Если вы не можете изменить исходные данные, используйте функцию TRIM в формулах, чтобы позаботиться об этом.

Использование ручного расчета вместо автоматического

Этот параметр может свести вас с ума (если вы не знаете, в чем причина всех проблем).

В Excel есть два режима расчета - Автоматический и Руководство по эксплуатации.

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

Это тот сеттинг, к которому мы все привыкли.

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

Но в некоторых случаях люди включают настройку ручного расчета.

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

Если вы включите ручное вычисление, Excel не будет вычислять, если вы его не заставите.

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

Все, что вам нужно сделать в этом случае, - либо вернуть расчет в автоматический режим, либо принудительно выполнить пересчет, нажав клавишу F9.

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

1. Перейдите на вкладку "Формула".
2. Нажмите на Параметры расчета
3. Выберите "Автоматически"

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

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

Удаление строк / столбцов / ячеек, ведущих к #REF! Ошибка

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

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

И иногда… это не может.

К счастью, одно четкое указание, которое вы получаете, когда формулы ломаются при удалении ячеек / строк / столбцов, - это #REF! ошибка в ячейках. Это справочная ошибка, указывающая на некоторую проблему со ссылками в формуле.

Позвольте мне показать вам, что я имею в виду, на примере.

Ниже я использовал формулу СУММ, чтобы добавить ячейки A2: A6.

Теперь, если я удалю любую из этих ячеек / строк, формула СУММ вернет # ССЫЛКА! ошибка. Это происходит потому, что когда я удалил строку, формула не знает, на что теперь ссылаться.

Вы можете видеть, что третий аргумент в формуле стал # ССЫЛКА! (который ранее относился к ячейке, которую мы удалили).

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

Неправильное размещение скобок (BODMAS)

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

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

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

Например, предположим, что у вас есть следующая формула:

=5+10*50

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

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

=(5+10)*50

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

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

Оператор	Описание	Порядок приоритета
: (двоеточие)	Диапазон	1
(одно пространство)	Пересечение	2
, (запятая)	союз	3
-	Отрицание (как в -1)	4
%	Процент	5
^	Возведение в степень	6
* и /	Умножение и деление	7
+ и -	Сложение и вычитание	8
&	Конкатненация	9
= =	Сравнение	10

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

Неправильное использование абсолютных / относительных ссылок на ячейки

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

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

Вы можете узнать больше об абсолютных, относительных и смешанных ссылках здесь.

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

Позвольте мне показать вам пример.

Ниже у меня есть набор данных, в котором я хочу получить оценку за экзамен 1 для имен в столбце E (простой вариант использования ВПР)

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

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

Как видите, эта формула в некоторых случаях дает ошибку.

Это происходит потому, что я не заблокировал аргумент массива таблицы - он A2: B6 в ячейке F2, в то время как это должно было быть 2 австралийских доллара: 6 миллиардных долларов

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

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

Неправильная ссылка на имена листов / книг

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

Например, если я хочу сослаться на ячейку A1 в Sheet2, ссылка будет = Лист2! A1 (где после названия листа стоит восклицательный знак)

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

Если вы имеете в виду внешнюю книгу (допустим, вы имеете в виду ячейку A1 в «Пример листа» в книге с именем «Пример книги»), ссылка будет такой, как показано ниже:

= '[Example Workbook.xlsx] Example Sheet'! $ A $ 1

И если вы закроете книгу, ссылка изменится, чтобы включить весь путь к книге (как показано ниже):

= 'C: \ Users \ sumit \ Desktop \ [Example Workbook.xlsx] Example Sheet'! $ A $ 1

Если вы в конечном итоге измените имя книги или рабочего листа, на который ссылается формула, вам будет выдан # ССЫЛКА! ошибка.

Также читайте: Как найти внешние ссылки и справочные материалы в Excel

Циркулярные ссылки

Циклическая ссылка - это когда вы ссылаетесь (прямо или косвенно) на ту же ячейку, в которой вычисляется формула.

Ниже приведен простой пример, в котором я использую формулу СУММ в ячейке A4 при использовании ее в самом вычислении.

= СУММ (A1: A4)

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

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

Для этого щелкните вкладку «Формула» и в группе «Аудит формул» щелкните значок раскрывающегося списка «Проверка ошибок» (маленькая стрелка, направленная вниз).

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

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

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

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

Он не заставляет его рассчитывать и давать результат.

И это легко исправить.

1. Измените формат на «Общий» с «Текст» (он находится на вкладке «Главная» в группе «Числа»).
2. Перейдите к ячейке с формулой, войдите в режим редактирования (используйте F2 или дважды щелкните ячейку) и нажмите Enter.

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

Если есть апостроф, его можно просто удалить.

Текст, автоматически преобразующийся в даты

У Excel есть дурная привычка преобразовывать то, что выглядит как дата, в настоящую дату. Например, если вы введете 1/1, Excel преобразует его в 01 января текущего года.

В некоторых случаях это может быть именно то, что вы хотите, а в некоторых случаях это может работать против вас.

А поскольку Excel сохраняет значения даты и времени в виде чисел, как только вы вводите 1/1, он преобразует их в число, представляющее 1 января текущего года. В моем случае, когда я это делаю, он преобразует его в число 43831 (на 01.01.2020).

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

Как это исправить?

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

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

1. Выберите ячейки / диапазон, в котором вы хотите изменить формат
2. Перейдите на вкладку "Главная"
3. В группе "Число" щелкните раскрывающийся список "Формат".
4. Нажмите на текст

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

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

Другой пример, когда это может сильно расстраивать, - ввод текста / числа с ведущими нулями. Excel автоматически удаляет эти ведущие нули, поскольку считает их бесполезными. Например, если вы введете 0001 в ячейку, Excel изменит его на 1. Если вы хотите сохранить эти ведущие нули, выполните действия, описанные выше.

Скрытые строки / столбцы могут дать неожиданные результаты

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

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

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

Все идет нормально!

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

И вот в чем проблема - формула в ячейке C12 по-прежнему показывает тот же результат - то есть сумму всех записей.

Как я уже сказал, формула не дает неправильного результата. На самом деле функция СУММ работает нормально.

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

Функция СУММ не может учитывать отфильтрованные данные и давать результат для всех ячеек (скрытых или видимых). Если вы хотите получить только сумму / количество / среднее количество видимых ячеек, используйте функции SUBTOTAL или AGGREGATE.

Ключевые вынос - Понимать правильное использование и ограничения функции в Excel.

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

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

Надеюсь, вы нашли этот урок полезным!