Краткий ответ
Ошибки #REF!, #VALUE! и #N/A в Excel чаще всего возникают из-за удаленных ячеек, на которые ссылается формула, текста вместо числа или данных, отсутствующих в таблице поиска. Исправьте их, проследив ссылки в формуле, проверив типы данных и используя функции, такие как IFERROR и XLOOKUP, с понятным сообщением вместо ошибки.
Как проявляется проблема
- В ячейке отображается #REF! вместо вычисленного значения
- Формула возвращает #VALUE!, хотя все ячейки заполнены
- VLOOKUP показывает #N/A для данных, которые явно существуют
- Ошибка появляется только в некоторых строках, а в других нет
- Весь отчет или сводная таблица показывает ошибки после копирования
Частые причины
Удаленные строки или столбцы
Когда вы удаляете ячейку, строку или столбец, на которые ссылается формула, Excel больше не знает, откуда взять данные, и показывает #REF!.
Текст вместо числа
#VALUE! возникает, когда формула ожидает число, а в ячейке находится текст, пробел или число, сохраненное как текст.
Значение отсутствует в таблице поиска
#N/A означает, что функция поиска не нашла искомые данные, чаще всего из-за различий в пробелах, скрытых символах или типе данных.
Несогласованные ссылки после копирования
Копирование формулы без абсолютных ссылок смещает диапазон поиска и возвращает неверный результат или ошибку.
Пустые ячейки в математических операциях
Деление на ноль дает #DIV/0!, а пустая ячейка в некоторых функциях может вызвать #VALUE! или неверный результат.
Пошаговое решение
- 1
Определите, какая ошибка возникла
Щелкните ячейку с ошибкой и посмотрите на маленький треугольник в верхнем левом углу ячейки. При наведении на него Excel показывает краткое объяснение ошибки. Для получения дополнительных сведений перейдите на вкладку
Формулыи выберитеПроверка ошибок. - 2
Проследите ссылки в формуле
Дважды щелкните ячейку, чтобы войти в режим редактирования. Excel окрашивает ссылки в разные цвета, и вы сразу видите, какие ячейки использует формула. Если какая-то ссылка не окрашена, ячейка удалена, и там возникла ошибка #REF!.
- 3
Исправьте #REF!, восстановив ссылку
Замените часть формулы, содержащую
#REF!, правильным адресом ячейки или диапазона. Если вы не знаете, какая ячейка была указана, отмените последнее изменение с помощьюCtrl+Zи очистите содержимое ячейки вместо удаления всей строки или столбца. - 4
Проверьте тип данных при #VALUE!
Выделите ячейки, которые использует формула, и на вкладке
Главнаяпроверьте, имеет ли формат значениеОбщийилиЧисло. Числа, сохраненные как текст, можно распознать по зеленому треугольнику; выделите диапазон, затемДанные > Текст по столбцам > Готово, чтобы преобразовать их в числа. Перед преобразованием сделайте копию файла, так как могут быть потеряны ведущие нули. - 5
Удалите пробелы и скрытые символы
При #N/A сначала проверьте, есть ли в ячейках ведущие или замыкающие пробелы. Функция
=TRIM(A2)удаляет лишние пробелы, а=CLEAN(A2)скрытые символы. Сравните результат с таблицей поиска и попробуйте снова. - 6
Зафиксируйте диапазон поиска
Если вы используете VLOOKUP или XLOOKUP, нажмите
F4на ссылке таблицы поиска, чтобы получить абсолютную ссылку, например$A$2:$D$100. Так копирование формулы в другие строки не сместит диапазон и не вызовет #N/A. - 7
Скройте ошибки функцией IFERROR
Вместо необработанной ошибки введите
=IFERROR(A2/B2;"нет данных")или=IFERROR(XLOOKUP(...);"не найдено"). Таблица останется читаемой, и вы сразу увидите, где не хватает данных. Следите за тем, чтобы не скрыть реальную проблему, которую нужно исправить. - 8
Протестируйте формулу на небольшом образце
Скопируйте формулу во вспомогательный столбец и примените ее к нескольким строкам с известным результатом. Когда результаты совпадут, скопируйте ее на весь диапазон. Так вы избежите распространения ошибки сразу на сотни строк.
Когда ошибка остается и после исправления
Если ошибка возвращается и после исправления, проверьте, защищен ли лист или участвует ли в формуле внешний файл, который был перемещен. В общих книгах Microsoft 365 обратите внимание на то, не изменяет ли кто-то другой тот же диапазон одновременно. Для больших таблиц с несколькими тысячами строк используйте параметр фильтрации на вкладке Данные и фильтруйте по ошибкам, затем исправляйте пакетами. Если книга была создана путем объединения нескольких файлов, проверьте, не были ли формулы перенесены с неверными ссылками. Сохраните копию файла перед крупными изменениями, чтобы можно было вернуться к предыдущему состоянию.
Как избежать повторения
- Используйте абсолютные ссылки для таблиц поиска и именованные диапазоны
- Вместо удаления строк и столбцов очищайте только содержимое ячеек
- Импортируйте данные через `Данные > Из текста/CSV` вместо копирования из других программ
- Периодически проверяйте формулы с помощью `Формулы > Показать формулы` перед отправкой файла
Когда лучше обратиться к специалисту
Если ошибки затрагивают большое количество формул на нескольких листах или книга содержит макросы и внешние связи, обратитесь в ИТ-поддержку для проверки структуры файла перед крупными изменениями.
Обслуживание ИТ-инфраструктуры · Серверы, сети, резервное копирование, безопасность и helpdeskЧастые вопросы
Что означает #REF! в Excel?
#REF! означает, что формула ссылается на ячейку, строку или столбец, которые больше не существуют, чаще всего из-за удаления. Исправьте ссылку в формуле или отмените удаление с помощью `Ctrl+Z`.
Почему VLOOKUP возвращает #N/A, когда данные существуют?
Наиболее частые причины: различия в пробелах, скрытые символы, тип данных или неправильно заданный четвертый аргумент для точного совпадения. Очистите значения функциями `TRIM` и `CLEAN` и проверьте, что обе стороны имеют один тип.
Безопасно ли использовать IFERROR?
Да, но IFERROR скрывает ошибку и может замаскировать реальную проблему в данных. Используйте его для отображения понятного сообщения, а не как постоянное решение для неверных ссылок.