Ошибка ссылки в Excel

Обновлено: 20.11.2024

Из этой статьи вы узнаете следующее:

Строка, столбец или лист удалены.
Формула содержит неправильную или недопустимую ссылку на ячейку.

Давайте рассмотрим несколько примеров, чтобы узнать, когда возникает эта ошибка и как ее исправить!

Пример 1:

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

Чтобы избавиться от этого сообщения об ошибке, нам нужно выбрать ячейку(и) с этой ошибкой, использовать диалоговое окно "Найти и заменить" и сделать следующее:

Заменить на: (оставьте это поле пустым)

Скачайте книгу и следуйте инструкциям:

ШАГ 1. Чтобы проверить ячейку, содержащую ячейку, просто щелкните ячейку и нажмите F2.

ШАГ 2. Выделите таблицу с ошибками.

ШАГ 3. Нажмите Ctrl + H, чтобы открыть диалоговое окно "Найти и заменить".

ШАГ 5. Нажмите «Заменить все».

Вот как будут выглядеть ваши замененные данные:

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

Пример 2:

В приведенной ниже таблице представлены данные о продажах для разных клиентов за 4 квартала и формула суммирования, используемая для расчета общего объема продаж. Для расчета общего объема продаж используется формула =СУММ(B4, C4, D4, E4).

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

Простое решение этой проблемы — использование диапазона вместо явной ссылки на ячейку. Давайте рассмотрим пошаговое руководство, чтобы узнать, как это сделать:

ШАГ 1. Используйте формулу =СУММ(B4:E4) в ячейке F4 и скопируйте приведенную ниже формулу в ячейки F5:F11.

ШАГ 2. Теперь удалите столбец E, чтобы получить общий объем продаж только за 3 квартала.

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

Давайте рассмотрим другой пример, когда ошибка возникла из-за того, что функция ВПР содержала недопустимую ссылку на ячейку.

Пример 3:

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

Формула, используемая для определения общего объема продаж для клиентов, указанных в ячейке H4, выглядит следующим образом: =VLOOKUP(H4,$A$4:$F$11,7,0).

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

Аргументы функции ВПР:

Искомое_значение = значение, которое нужно найти в первом столбце таблицы.
Table_array = таблица, из которой нужно извлечь данные.
Col_index_num = номер столбца в массиве таблиц, из которого должно быть возвращено соответствующее значение.
Range_lookup = значение должно быть равно 1, если требуется приблизительное соответствие, или 0, если требуется точное соответствие возвращаемого значения.

Excel возвращает ошибку в этой формуле, так как функция ВПР пытается вернуть значение из 7-го столбца, а ссылка $A$4:$F$11 содержит только 6 столбцов.

Чтобы исправить эту ошибку, используйте формулу =ВПР(H4,$A$4:$F$11,6,0).

Заключение

ПОЛЕЗНЫЙ РЕСУРС:

Вы можете узнать больше о том, как использовать Excel, просмотрев наш БЕСПЛАТНЫЙ веб-семинар по Excel, посвященный формулам, сводным таблицам, макросам и VBA!

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel для iPad Excel для iPhone Excel для планшетов Android Excel 2010 Excel 2007 Excel для Mac 2011 Excel для телефонов Android Excel для Windows Phone 10 Excel Mobile Excel Starter 2010 Еще. Меньше

В следующем примере используется формула =СУММ(B2,C2,D2) в столбце E.

Если вы случайно удалили строки или столбцы, вы можете сразу же нажать кнопку "Отменить" на панели быстрого доступа (или нажать CTRL+Z), чтобы восстановить их.

Настройте формулу так, чтобы в ней использовалась ссылка на диапазон вместо отдельных ячеек, например =СУММ(B2:D2). Теперь вы можете удалить любой столбец в диапазоне сумм, и Excel автоматически скорректирует формулу. Вы также можете использовать =SUM(B2:B5) для суммы строк.

Пример. Функция ВПР с неправильными ссылками на диапазон

Увеличьте диапазон или уменьшите значение поиска в столбце, чтобы оно соответствовало эталонному диапазону. =VLOOKUP(A8,A2:E5,5,FALSE) будет допустимым эталонным диапазоном, как и =VLOOKUP(A8,A2:D5,4,FALSE).

Пример: ИНДЕКС с неправильной ссылкой на строку или столбец

Настройте ссылки на строки или столбцы так, чтобы они находились в пределах диапазона поиска INDEX. =INDEX(B2:E5,4,4) вернет правильный результат.

Пример. ДВССЫЛНАЯ ссылка на закрытую книгу

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

Проблемы OLE

Примечание. OLE — это технология, которую можно использовать для обмена информацией между программами.

Проблемы DDE

Примечание. Динамический обмен данными (DDE) — это установленный протокол для обмена данными между программами на базе Microsoft Windows.

Проблемы с макросами

Нужна дополнительная помощь?

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

Это руководство охватывает:

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

Самая распространенная причина, по которой вы можете столкнуться с ошибкой ссылки, – это когда у вас есть формула, ссылающаяся на ячейку/строку/столбец, и вы удаляете эту ячейку/строку/столбец.

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

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

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

Итак, позвольте мне показать вам два способа быстрого поиска ячеек с ошибкой ссылки и некоторые возможные исправления.

Предположим, у вас есть набор данных, показанный ниже, в котором есть пара ошибок ссылки.

Ниже приведены действия по поиску и выбору всех ячеек с «ошибками ссылки»:

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

Давайте рассмотрим возможные способы обработки ошибки ссылки.

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

Удалите ошибку: просто нажмите клавишу удаления, и эта ошибка будет удалена, и вместо этого у вас будут пустые ячейки.
Заменить значением или текстом. Вы можете заменить ошибку на 0, дефис или любой другой значимый текст. Для этого просто введите то, на что вы хотите заменить ошибку, удерживайте клавишу Control, а затем нажмите клавишу Enter. Это введет введенный вами текст во все выбранные ячейки.
Выделите эти ячейки с помощью параметра цвета ячейки на вкладке "Главная" на ленте.

Поиск ошибок с помощью специальной опции «Перейти»

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

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

Ниже приведены инструкции по использованию параметра «Перейти к специальному», чтобы найти и выбрать все ошибки:

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

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

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

Как найти ошибки REF в Excel?

Если столбец будет удален, это вызовет ошибку REF. Если вы случайно удалили столбец, нажмите комбинацию клавиш Ctrl + Z, чтобы отменить ошибку.

2] ВПР с неправильными ссылками на диапазон

Чтобы решить эту проблему, поместите правильный столбец в диапазон.

3] ИНДЕКС с неправильной ссылкой на строку или столбец

Чтобы устранить эту проблему, используйте правильный столбец или строку в диапазоне.

Дата: 7 марта 2022 г. Метки: Excel

Как использовать объединение в Excel для улучшения форматирования данных

Где скачать Microsoft Word, Excel, PowerPoint для Windows 11/10

[электронная почта защищена]

Шантель учится в университете и получает степень бакалавра наук в области информационных технологий. Ее цель — стать администратором базы данных или системным администратором. Она любит читать и смотреть исторические документальные фильмы и драмы.

Читайте также: