#REF Пример ошибки Excel
Ниже приведен пример того, как вы можете случайно создать ошибку #REF Excel. Чтобы узнать больше, посмотрите бесплатный курс Excel по финансам и следуйте видео-инструкции.
На первом изображении показано сложение трех чисел (5, 54 и 16). В столбце D мы показываем формулу, складывающую ячейки D3, D4 и D5 вместе, чтобы получить 75.
На следующем изображении показано, что произойдет, если удалить строку 4. Когда вся строка удаляется, формула, ссылающаяся на ячейку D4, больше не может найти эту ячейку, и в электронной таблице появляется ошибка #REF.
Узнайте больше об ошибках #REF Excel в нашем бесплатном учебном курсе по Excel.
Как найти ошибки #REF Excel
Способ # 1
Когда появится меню «Перейти к специальному», выберите « Формулы» и установите только флажок «Ошибки». Нажмите OK, и вы автоматически перейдете к каждой ячейке с #REF! ошибка в этом.
Способ # 2
Как исправить ошибки #REF Excel
Как показано на скриншоте выше, после ввода сообщения об ошибке в поле «Найти что» оставьте поле «Заменить на» пустым и нажмите «Заменить все» в нижнем левом углу. Это приведет к удалению справочных ошибок из ваших формул.
Дополнительные ресурсы Excel от Финансов
Дополнительные финансовые руководства и ресурсы, которые могут оказаться полезными, включают:
Как исправить ошибку #REF! ошибка
Резюме
Объяснение
О #REF! ошибка
#REF! ошибка возникает, когда ссылка недействительна. Во многих случаях это происходит потому, что листы, строки или столбцы были удалены, или потому, что формула с относительными ссылками была скопирована в новое место, где ссылки недействительны. В показанном примере формула в C10 возвращает #REF! ошибка при копировании в ячейку E5:
Предотвращение ошибок #REF
Лучший способ предотвратить #REF! Ошибки должны в первую очередь предотвратить их появление. Перед удалением столбцов, строк или листов убедитесь, что на них не ссылаются формулы в книге. Если вы копируете и вставляете формулу в новое место, вам может потребоваться преобразовать некоторые ссылки на ячейки в абсолютную ссылку, чтобы предотвратить изменения во время операции копирования.
Если вы вызовете #REF! ошибка, лучше сразу исправить. Например, если вы удалите столбец и # ССЫЛКА! появляются ошибки, отмените это действие (вы можете использовать сочетание клавиш Control + Z). Когда вы отмените действие, #REF! ошибки исчезнут. Затем отредактируйте формулу (ы), чтобы исключить столбец, который вы хотите удалить, при необходимости переместив данные. Наконец, удалите столбец и убедитесь, что #REF нет! ошибки.
Удаление нескольких #REF! ошибки
Чтобы быстро удалить с листа многие ошибки #REF, вы можете использовать функцию «Найти и заменить». Используйте сочетание клавиш Control + H, чтобы открыть диалоговое окно «Найти и заменить». Введите #REF! в области ввода поиска и оставьте область ввода замены пустой:
Затем вы можете вносить индивидуальные изменения с помощью «Найти далее + заменить» или использовать «Заменить все» для замены всех ошибок #REF за один шаг.
Исправление ошибок #REF
Ошибки #REF довольно сложно исправить, потому что исходная ссылка на ячейку исчезла навсегда. Если вы знаете, какой должна быть ссылка, вы можете просто исправить ее вручную. Просто отредактируйте формулу и замените # ССЫЛКА! с действующей ссылкой. Если вы не знаете, какой должна быть ссылка на ячейку, вам, возможно, придется более подробно изучить рабочий лист, прежде чем вы сможете исправить формулу.
Примечание: вы не можете отменить удаление листа в Excel. Если вы удаляете вкладку рабочего листа и видите ошибки #REF, лучшим вариантом, вероятно, будет закрыть файл и повторно открыть последнюю сохраненную версию. По этой причине всегда сохраняйте книгу (или сохраняйте копию) перед удалением одного или нескольких листов.
# ССЫЛКА! ошибки с ВПР
Вы можете увидеть #REF! ошибка с функцией ВПР, когда столбец указан неправильно. На экране ниже ВПР возвращает # ССЫЛКА! потому что в диапазоне таблицы нет столбца 3, то есть B3: C7:
Если для индекса столбца установлено правильное значение 2, #REF! ошибка устранена, и функция ВПР работает правильно:
Примечание: вы также можете увидеть ошибку #REF с функцией INDEX, когда ссылка на строку или столбец недействительна.
Захват #REF! ошибка с ЕСЛИОШИБКА
Исправление ошибки #REF! #BUSY!
Ошибка #ССЫЛКА! указывает на то, что формула ссылается на недопустимую ячейку. Чаще всего это происходит потому, что формула ссылается на ячейки, которые были удалены или заменены другими данными.
Пример ошибки #ССЫЛКА! из-за удаления столбца
В следующем примере в столбце E используется формула =СУММ(B2;C2;D2).
Если вы случайно удалили строки или столбцы, вы можете немедленно нажать кнопку «Отменить» на панели быстрого доступа (или нажать клавиши CTRL+Z), чтобы восстановить их.
Измените формулу так, чтобы она ссылалась на диапазон, а не на отдельные ячейки, например =СУММ(B2:D2). Теперь можно удалить любой столбец в диапазоне суммирования, и Excel автоматически скорректирует формулу. Чтобы вычислить сумму значений в строках, также можно использовать формулу =СУММ(B2:B5).
Пример функции ВПР с неправильными ссылками на диапазоны
В следующем примере =ВРОТ(A8;A2:D5;5;ЛОЖЬ) возвращает #REF! из-за того, что она ищет значение из столбца 5, но диапазон ссылок — A:D, который составляет всего 4 столбца.
Расширьте диапазон или уменьшите значение столбца для поиска так, чтобы он попадал в указанный диапазон. Формулы =ВПР(A8;A2:E5;5;ЛОЖЬ) будет работать правильно, так же как и формула =ВПР(A8;A2:D5;4;ЛОЖЬ).
Пример функции ИНДЕКС с неправильной ссылкой на строку или столбец
В этом примере формула =ИНДЕКС(B2:E5;5;5) возвращает #REF! поскольку диапазон ИНДЕКС составляет 4 строки и 4 столбца, но формула запрашивает возврат данных в 5-й строке и 5-м столбце.
Измените ссылки на строки и столбцы так, чтобы они попадали в диапазон поиска функции ИНДЕКС. Формула =ИНДЕКС(B2:E5;4;4) вернет правильный результат.
Пример ссылки на закрытую книгу с использованием функции ДВССЫЛ
В следующем примере функция INDIRECT пытается создать ссылку на закрытую книгу, что приводит к #REF! ошибку «#ВЫЧИС!».
Откройте книгу, на которые ссылается ссылка. Эта же ошибка будет возникнуть при ссылке на закрытую книгу с динамической функцией массива.
Проблемы с OLE
Если вы использовали ссылку OLE, возвращая #REF! и запустите программу, в которую будет звонить ссылка.
Примечание. OLE — это технология, которая используется для обмена информацией между приложениями.
Проблемы dDE
Если вы использовали динамический Exchange DDE, возвращающий #REF! сначала убедитесь, что вы ссылаетесь на правильный раздел. Если вы по-прежнему получаете #REF! проверьте в центре управления Параметры на внешнее содержимое, как описано в Office документах.
Примечание. Динамические Exchange (DDE)— это протокол, который позволяет обмениваться данными между Windows программами Майкрософт.
Проблемы с макросами
Если макрос вводит на сайте функцию, которая ссылается на ячейку над функцией, а ячейка с этой функцией находится в строке 1, функция возвращает #REF! поскольку над строкой 1 нет ячеек. Проверьте функцию, не ссылается ли аргумент на не допустимую ячейку или диапазон ячеек. Для этого может потребоваться изменить макрос в редакторе Visual Basic (VBE).
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Функции SUMIF, COUNTIF и COUNTBLANK возвращают «#VALUE!» Ошибка
Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.
Симптомы
Формула, которая содержит функции SUMIF, SUMIFS, COUNTIFS, COUNTIFS или COUNTBLANK, может #VALUE! ошибка в Microsoft Excel.
Причина
Такое поведение происходит, когда формула, содержаная функцию, ссылается на ячейки в закрытой книге и вычисляется.
Если вы откроете ссылаемую книгу, формула работает правильно.
Обходной путь
Чтобы обойти это поведение, используйте сочетание функций SUM и IF вместе в формуле массива.
Примеры
Необходимо ввести каждую формулу в качестве формулы массива. Чтобы ввести формулу массива Microsoft Excel для Windows, нажмите кнопку CTRL+SHIFT+ENTER.
SUMIF
Вместо использования формулы, аналогичной следующей:
Используйте следующую формулу:
COUNTIF
Вместо использования формулы, аналогичной следующей:
использовать следующую формулу:
COUNTBLANK
Вместо использования формулы, аналогичной следующей:
использовать следующую формулу:
При использовании формулы массива SUM (IF()) используйте логические И или ИЛИ для замены функции SUMIFS или COUNTIFS.
Статус
Такое поведение является особенностью данного продукта.
Дополнительные сведения
Функция SUMIF использует следующий синтаксис:
Дополнительные сведения см. в #VALUE ошибке.
Справочные материалы
Дополнительные сведения о мастере, который может помочь вам создать эти функции, щелкните Microsoft Excel Справка в меню справки, введите суммы значений, которые соответствуют условиям, с помощью мастера условной суммы в помощнике Office или мастер ответа, а затем нажмите поиск, чтобы просмотреть тему.
Дополнительные сведения о формулах массива щелкните Microsoft Excel Справка в меню справки, введите сведения об использовании формул для вычисления значений на других таблицах и книгах в Office Помощнике или мастере ответа, а затем нажмите кнопку Поиск для просмотра темы.
Ошибки в формулах Excel
В этой статье мы расскажем о том, как справляться с некоторыми наиболее распространёнными ошибками формул в Excel.
Ошибка #####
Появление в ячейке такого кода ошибки означает, что столбец недостаточно широк, чтобы отобразить значение полностью.
Наведите указатель мыши на правую границу столбца рядом с его заголовком (в нашем примере это столбец A), чтобы указатель принял вид, как на рисунке ниже. Нажмите левую кнопку мыши и перетащите границу столбца до нужной ширины.
Совет: Если дважды кликнуть по границе столбца A рядом с его заголовком, то ширина столбца автоматически изменится и будет соответствовать самой широкой ячейке столбца.
Ошибка #ИМЯ?
Ошибка #ИМЯ? (#NAME?) возникает в том случае, когда Excel не может распознать текст в формуле (например, из-за опечатки).
Просто исправьте SU на SUM.
Ошибка #ЗНАЧ!
Excel показывает сообщение об ошибке #ЗНАЧ! (#VALUE!) в том случае, когда для формулы введён аргумент не подходящего типа.
a) Измените значение в ячейке A3.
b) Используйте функцию, которая игнорирует ячейки, содержащие текст.
Ошибка #ДЕЛ/0!
Сообщение об ошибке #ДЕЛ/0! (#DIV/0!) появляется при попытке деления числа на ноль или на пустую ячейку.
a) Изменить значение в ячейке A2 на любое число, не равное нулю.
b) Предотвратите возникновение ошибки при помощи логической функции ЕСЛИ (IF).
Пояснение: Если ячейка A2 будет равна нулю, то значением ячейки A3 будет пустая строка. Если нет – то в ячейке A3 будет вычислен результат формулы =A1/A2.
Ошибка #ССЫЛКА!
Сообщение об ошибке #ССЫЛКА! (#REF!) говорит о том, что формула ссылается на ячейку, которая не существует.
