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

28 January
561 full read
1,5 min.
1,6k story viewsUnique page visitors
561 read the story to the endThat's 33% of the total page views
1,5 minute — average reading time

Всем привет, друзья!

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

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

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

Закон Парето (принцип Парето, принцип 80/20) — эмпирическое правило, названное в честь экономиста и социолога Вильфредо Парето, в наиболее общем виде формулируется как «20 % усилий дают 80 % результата, а остальные 80 % усилий — лишь 20 % результата». https://ru.wikipedia.org/wiki/Закон_Парето

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

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

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

Начнем с очевидного.

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

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

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

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

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

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

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

=ВПР(E2;A2:C6;3;0)

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

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

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

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

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

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

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

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

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

См. также: Трюки и хитрости в Excel № 3 - перенос строки в ячейке комбинацией <Alt+Enter>

3. Удаление строк / столбцов / ячеек, ведущих к ошибке - # ССЫЛКА!

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

А иногда… не корректирует .

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

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

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

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

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

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

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

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

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

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

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

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

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

=ВПР(E2;A2:C6;3;0)
Почему не работают формулы Excel: возможные причины и как это исправить!

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

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

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

См. также: Формулы в Excel - Как перевести число в прописной текст

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

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

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

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

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

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

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

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

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

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

  • Выберите ячейки / диапазон, в котором вы хотите изменить формат
  • Перейдите на вкладку "Главная"Щелкните вкладку "Главная"
  • В группе "Число" щелкните раскрывающийся список "Формат".
  • Нажмите на текст Выберите текст в качестве форматирования
Почему не работают формулы Excel: возможные причины и как это исправить!

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

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

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

Например, если я хочу сослаться на ячейку A1 в Листе 2, ссылка будет = Лист2! A1. Формула с ссылками на несколько листов будет выглядеть так:

=СУММ(Лист2!A1;Лист3!A1;Лист4!A1)

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

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

См. также: Урок Excel № 20 - Удаление и замена данных.

8. Ошибка # ИМЯ

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

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

На этом у меня всё. Если вам понравился сегодняшний урок, ставьте лайки 👍 👍 👍 и подписывайтесь на канал чтобы не пропустить еще более интересные материалы. Если хотите посмотреть еще уроки загляните в СОДЕРЖАНИЕ 👈 , обязательно еще что-нибудь присмотрите )) Спасибо!