Ребята, всем привет! 👋 Продолжаем изучать Excel.
Если вы работаете с формулами в Excel, рано или поздно вы столкнетесь с проблемой, когда формулы Excel вообще не работают (или дают неверный результат).
В прошлом уроке мы рассмотрели некоторые распространенные проблемы, которые, вероятно, являются причиной того, что ваши формулы Excel не работают.
В продолжение данной темы рассмотрим еще несколько типичных примеров ошибок и правильного использования и ограничений функции в Excel.
✍️ Если вы только начинаете осваивать Excel мы уверены, каждый может найти для себя что-то новое!
✨ А прежде, чем мы начнем 📣 напомню, теперь у нас на канале есть удобный рубрикатор 👉 Быстрый поиск решения. Путеводитель по Excel, а все видео 📽 предыдущих уроков доступны и на YouTube.
⚠ см. Причины №№1-5 в предыдущем уроке
⏩ Причина №6. Неправильное использование абсолютных / относительных ссылок на ячейки
Абсолютная ссылка - это когда вы исправляете ссылку на ячейку (или ссылку на диапазон), чтобы она не менялась при копировании и вставке формул, а относительная ссылка - это та, которая изменяется.
Узнать больше об абсолютных, относительных и смешанных ссылках
Когда вы копируете и вставляете формулы в Excel, ссылки автоматически корректируются. Иногда это именно то, что вам нужно (в основном, когда вы копируете формулы вниз по столбцу), а иногда вы не хотите, чтобы это происходило.
📢 Причина ошибки: Вы можете получить неверный результат, если забудете изменить ссылку на абсолютную (или наоборот). Это то, что случается со мной довольно часто, когда я использую формулы поиска.
🔔 Решение: Чтобы преобразовать относительную ссылку в абсолютную, выберите ссылку на ячейку внутри ячейки и нажмите клавишу F4. Вы бы заметили, что это меняется при добавлении знаков доллара. Вы можете продолжать нажимать клавишу F4, пока не получите нужную ссылку.
⏩ Причина №7. Неправильная ссылка на имена листов / Книг
Когда вы ссылаетесь в формуле на другие листы или рабочие книги, вам необходимо следовать определенному формату. И в случае неправильного формата вы получите сообщение об ошибке.
Например, если я хочу сослаться на ячейку A1 в Лист1, ссылка будет =Лист!A1 (где после названия листа стоит восклицательный знак)
В случае, если вы ссылаетесь на внешнюю рабочую книгу (допустим, вы ссылаетесь на ячейку A1 в книге с именем "Тест"), ссылка будет такой, как показано ниже:
И в случае, если вы закроете рабочую книгу, ссылка изменится, чтобы включить весь путь к рабочей книге:
📢 Причина ошибки: В случае, если вы в конечном итоге измените имя рабочей книги или рабочего листа, на который ссылается формула, вам будет присвоен #ССЫЛКА! ошибка:
🔔 Решение: Убедитесь в правильности ссылок
⏩ Причина №8. Циклические ссылки
Циклическая ссылка - это когда вы ссылаетесь (прямо или косвенно) на ту же ячейку, где вычисляется формула.
📢 Причина ошибки: Хотя Excel показывает вам подсказку, сообщающую о циклической ссылке, она не будет делать это для каждого экземпляра. И это может привести к неправильному результату (без какого-либо предупреждения).
В данном примере результат вычисления равен нулю:
🔔 Решение: Если вы подозреваете, что циклическая ссылка используется, вы можете проверить, в каких ячейках она есть.
Для этого перейдите на вкладку Формулы и в группе ‘Аудит формул’ щелкните значок раскрывающегося списка Проверка ошибок (маленькая стрелка, указывающая вниз). Наведите курсор на параметр Круговой ссылки, и он покажет вам ячейку, в которой есть проблема с круговой ссылкой:
Так же информация о наличии циклических ссылок отображается в левом нижнем углу листа:
⏩ Причина №9. Ячейки отформатированы как текст
Если вы оказались в ситуации, когда, как только вы вводите формулу, нажимая enter, вы видите формулу вместо значения, это явный случай форматирования ячейки как текста:
📢 Причина ошибки: Когда ячейка форматируется как текст, она рассматривает формулу как текстовую строку и отображает ее как есть.
Это не заставляет его вычислять и выдавать результат.
🔔 Решение: Данную ошибку легко исправить.
- Шаг 1: Измените формат с "Текст" на "Общий" (он находится на вкладке "Главная" в группе "Числа").
- Шаг 2: Перейдите в ячейку с формулой, войдите в режим редактирования (используйте клавишу F2 или дважды щелкните ячейку) и нажмите Ввод
⚠ В случае, если описанные выше шаги не решают проблему, еще одна вещь, которую нужно проверить, - это наличие апострофа в начале ячейки. Многие люди добавляют апостроф для преобразования формул и чисел в текст. Если есть апостроф, вы можете просто удалить его.
⏩ Причина №10. Текст автоматически преобразуется в даты
У Excel есть эта плохая привычка преобразовывать то, что выглядит как дата, в фактическую дату. Например, если вы введете 1/1, Excel преобразует его в 01 января текущего года;
В некоторых случаях это может быть именно то, чего вы хотите, а в некоторых случаях это может сработать против вас.
📢 Причина ошибки: поскольку Excel хранит значения даты и времени в виде чисел, как только вы вводите 1/1, он преобразует его в число, представляющее 1 января текущего года.
Это может повлиять на ваши формулы, если вы используете эти ячейки в качестве аргумента в формуле.
🔔 Решение: нужно четко указать формат самостоятельно.
Теперь всякий раз, когда вы вводите что-либо в выделенные ячейки, это будет рассматриваться как текст, а не изменяться автоматически.
⚠ Важно: вышеуказанные шаги будут работать только для данных, введенных после изменения форматирования. Это не изменит текст, который был преобразован в дату до внесения этого изменения в форматирование.
⏩ Причина №11. Скрытые строки / столбцы могут давать неожиданные результаты
Это не тот случай, когда формула дает неправильный результат, а использование неправильной формулы.
Например, предположим, что у вас есть набор данных, как показано ниже, и я хочу получить сумму всех видимых ячеек в столбце C:
Пока все хорошо!
Теперь я применяю фильтр к столбцу А, чтобы показывать только записи о конкретных товарах:
И теперь мы видим ошибку!!!
📢 Причина ошибки: формула в ячейке C6 по-прежнему показывает тот же результат, то есть сумму всех записей.
Как я уже сказала, формула не дает неправильного результата. На самом деле, функция SUM() работает просто отлично. Проблема в том, что мы использовали здесь неправильную формулу.
ВАЖНО! Функция SUM() не может учитывать отфильтрованные данные и выдавать результат для всех ячеек (скрытых или видимых).
🔔 Решение: Если вы хотите получить только сумму / количество / среднее значение видимых ячеек, используйте функции ПРОМЕЖУТОЧНОГО ИТОГА:
А на этом сегодня все. 👏 Теперь вы знаете почему ваши формулы Excel могут не работать или давать неожиданные или неправильные результаты. Я надеюсь, что вы нашли этот урок полезным.
Продолжение следует.., а поэтому подписывайтесь на канал, чтобы не пропустить новые уроки и полезные фишки Excel. Следите за нашими новостями и вы узнаете больше о VBA и Excel в частности.
Понравился урок!? не забываем оставлять комментарий 📝 Нам очень важна Ваша обратная связь.
💝 А если у Вас пока нет вопросов, но вы дочитали данную статью до конца оставьте в комментариях просто смайлик 😉 (автору будет приятно)
И конечно же, за лайк 👍 и репост 🔁 данного поста благодарочка 💖 и респект 🤝 каждому!
см. также Почему не работают формулы Excel? Причины и как это исправить! (Часть 1)
Если вы знаете какую-либо другую причину (возможно, что-то, что вас часто раздражает) дайте мне знать в разделе комментариев.