Всем привет, друзья!
Если вы работаете с формулами в Excel , рано или поздно вы столкнетесь с проблемой, когда формулы Excel бьются и не срабатывают (или дают неверный результат).
К сожалению, слишком много вещей может пойти не так. Но поскольку мы живем в мире, который следует принципу Парето, то проверив некоторые общие проблемы, это, вероятно, решит 80% проблемы (или, может быть, даже 90% или 95% проблем).
Закон Парето (принцип Парето, принцип 80/20) — эмпирическое правило, названное в честь экономиста и социолога Вильфредо Парето, в наиболее общем виде формулируется как «20 % усилий дают 80 % результата, а остальные 80 % усилий — лишь 20 % результата». https://ru.wikipedia.org/wiki/Закон_Парето
В этой статье я хочу выделить те распространенные проблемы, которые, вероятно, являются причиной того, что ваши формулы Excel не работают. Изучите их внимательно, и тогда вы быстро и легко сможете понять где вы совершили ошибку.
Итак, приступим!
1. Неправильный синтаксис функции
Начнем с очевидного.
Каждая функция в Excel имеет определенный синтаксис, например количество или тип аргументов, которые она может принимать. И во многих случаях причиной того, что формулы Excel не работают или дают неправильный результат, может быть неправильный аргумент (или отсутствующие аргументы).
Например, функция ВПР принимает три обязательных аргумента и один необязательный аргумент. Если вы укажете неправильный аргумент или не укажете необязательный аргумент (где он нужен для работы формулы), он даст вам неправильный результат.
Например, предположим, что у вас есть набор данных, показанный ниже, в котором вам нужно знать оценку по экзамену 2 (в ячейке F2).
Если я использую приведенную ниже формулу, я получу неправильный результат, потому что я использую неправильное значение в третьем аргументе (тот, который запрашивает номер индекса столбца).
=ВПР(E2;A2:C6;2;0)
В этом случае формула вычисляет (поскольку возвращает значение), но результат неверен (вместо оценки за экзамен 2 он дает оценку за экзамен 1). Правильная формула должна выглядеть так:
=ВПР(E2;A2:C6;3;0)
2. Лишние пробелы, приводящие к неожиданным результатам
Начальные и конечные пробелы трудно найти, и они могут вызвать проблемы при использовании ячейки, в которой они есть в формулах.
Например, в приведенном ниже примере, если я пытаюсь использовать ВПР для получения оценки для Ивана, это дает мне ошибку # Н/Д (нет данных).
Хотя видно, что формула верна и имя явно есть в списке, трудно заметить, что в ячейке с именем (в ячейке Е2) есть конечный пробел, а он есть...
Excel не считает содержимое этих двух ячеек одинаковым и поэтому считает его несоответствием при получении значения с помощью ВПР (или это может быть любая другая формула поиска).
Чтобы решить эту проблему, вам необходимо удалить эти лишние пробелы . Вы можете сделать это, используя любой из следующих методов:
- Очистите ячейку и удалите все начальные / конечные пробелы, прежде чем использовать ее в формулах.
- Используйте функцию =СЖПРОБЕЛЫ() в формуле, чтобы игнорировать любые начальные / конечные / двойные пробелы.
См. также: Трюки и хитрости в Excel № 3 - перенос строки в ячейке комбинацией <Alt+Enter>
3. Удаление строк / столбцов / ячеек, ведущих к ошибке - # ССЫЛКА!
Одна из вещей, которые могут иметь разрушительный эффект на ваши существующие формулы в Excel, - это удаление любой строки / столбца, которые использовались в вычислениях. Когда это происходит, иногда Excel сам корректирует ссылку и проверяет правильность работы формул.
А иногда… не корректирует .
К счастью, одно четкое указание, которое вы получаете, когда формулы ломаются при удалении ячеек / строк / столбцов, - это ошибка # ССЫЛКА!Это ошибка, которая говорит о том, что есть проблема с ссылками в формуле.
Ниже я использовал формулу СУММ (костыльный метод), чтобы получить итог ячеек от A2 до A8.
Теперь, если я удалю любую из этих ячеек / строк, формула СУММ покажет ошибку в вычислении. Это происходит потому, что когда я удалил 2 строки формула не знает, на что теперь ссылаться.
Вы можете видеть, что третий четвертый аргумент в формуле стал # ССЫЛКА! (который ранее относился к ячейкам, которые мы удалили).
4. Неправильное использование абсолютных / относительных ссылок на ячейки
Когда вы копируете и вставляете формулы в Excel, он автоматически корректирует ссылки. Иногда это именно то, что вам нужно (в основном, когда вы копируете формулы вниз по столбцу).
Абсолютная ссылка - это когда вы закрепляете ссылку на ячейку (или ссылку на диапазон), чтобы она не менялась при копировании и вставке формул, а относительная ссылка - это ссылка, которая изменяется.
Вы можете получить неверный результат, если забудете изменить ссылку на абсолютную (или наоборот). Это то, что часто случается со мной, когда я использую формулы поиска. Вот пример:
Ниже у меня есть набор данных, в котором я хочу получить оценку в экзамене 1 для имен в столбце E (простой вариант использования ВПР)
Ниже приведена формула, которую я использую в ячейке F2, а затем копирую во все ячейки под ней:
=ВПР(E2;A2:C6;3;0)
Как видите, эта формула в некоторых случаях дает ошибку. Это происходит потому, что я не заблокировал аргумент массива таблицы - это A2: С6 в ячейке F2, тогда как это должно было быть $A$2:$С$6
Вставляя эти знаки доллара, я заставляю Excel сохранять эти ссылки на ячейки фиксированными. Таким образом, даже когда я копирую эту формулу вниз, массив таблицы будет продолжать ссылаться на A2: С6.
Чтобы преобразовать относительную ссылку в абсолютную, выберите ссылку в ячейке и нажмите клавишу F4. Вы заметите, что он меняется, добавляя знаки доллара. Вы можете продолжать нажимать F4, пока не получите нужную ссылку.
См. также: Формулы в Excel - Как перевести число в прописной текст
5. Ячейки отформатированы как текст
Если вы окажетесь в такой ситуации - как только вы вводите формулу при нажатии Enter, и вы видите формулу вместо значения , это явный случай, когда ячейка форматируется как текст.
Когда ячейка форматируется как текст, она рассматривает формулу как текстовую строку и показывает ее как есть. Поэтому формула не может рассчитывать и показать результат. ЧТо делать?
- Измените формат на «Общий» с «Текст» (он находится на вкладке «Главная» в группе «Числа»).
- Перейдите к ячейке с формулой, войдите в режим редактирования (используйте F2 или дважды щелкните ячейку) и нажмите Enter.
Если описанные выше шаги не решают проблему, нужно еще проверить, есть ли в начале ячейки апостроф. Многие люди добавляют апостроф для преобразования формул и чисел в текст.
6. Текст, автоматически преобразующийся в даты
У Excel есть дурная привычка преобразовывать то, что выглядит как дата, в настоящую дату. Например, если вы введете 1/1, Excel преобразует его в 01 января текущего года.
В некоторых случаях это может быть именно то, что вы хотите, а в некоторых случаях это может работать против вас. А поскольку Excel сохраняет значения даты и времени в виде чисел, как только вы вводите 1/1, он преобразует их в число, представляющее 1 января текущего года.
Это может испортить ваши формулы, если вы используете эти ячейки в качестве аргумента в формуле. Как это исправить?
- Выберите ячейки / диапазон, в котором вы хотите изменить формат
- Перейдите на вкладку "Главная"Щелкните вкладку "Главная"
- В группе "Число" щелкните раскрывающийся список "Формат".
- Нажмите на текст Выберите текст в качестве форматирования
Теперь всякий раз, когда вы вводите что-либо в выбранные ячейки, это будет считаться текстом и не изменится автоматически.
7. Неправильная ссылка на имена листов / книг.
Когда вы ссылаетесь на другие листы или книги в формуле, вам необходимо следовать определенному формату. А в случае неправильного формата вы получите ошибку.
Например, если я хочу сослаться на ячейку A1 в Листе 2, ссылка будет = Лист2! A1. Формула с ссылками на несколько листов будет выглядеть так:
=СУММ(Лист2!A1;Лист3!A1;Лист4!A1)
И если вы удалите один из них, например третий, формула сломается и выдаст ошибку.
Чтобы этого избежать, внимательно следите за тем, что вы удаляете.
См. также: Урок Excel № 20 - Удаление и замена данных.
8. Ошибка # ИМЯ
Относиться к категории ошибки в написании функций. Недопустимое имя:
# ИМЯ – значит, что Excel не распознал текста написанного в формуле (название функции =СУМ() ему неизвестно, оно написано с ошибкой). Это результат ошибки синтаксиса при написании имени функции. Например:
На этом у меня всё. Если вам понравился сегодняшний урок, ставьте лайки 👍 👍 👍 и подписывайтесь на канал чтобы не пропустить еще более интересные материалы. Если хотите посмотреть еще уроки загляните в СОДЕРЖАНИЕ 👈 , обязательно еще что-нибудь присмотрите )) Спасибо!