Какая ссылка на ячейку в Microsoft Excel неверна

Обновлено: 03.07.2024

Исправление ссылок на ячейки (иначе называемое абсолютными ссылками) — это то, что, кажется, сбивает с толку многих людей — возможность изменить ссылку на ячейку в формуле на фиксированную ячейку, строку или столбец…

Причина, по которой вам следует подумать об этом, заключается в том, что часть вашей формулы всегда должна ссылаться на одну и ту же строку или столбец; в простейшем случае это может быть создание «Таблицы умножения» или счета-фактуры, к которому вы хотите применить ту же ставку НДС. Для этого мы используем знак доллара ($) в нашей формуле Excel.

Относительные ссылки на ячейки

"Нормальное" поведение Excel заключается в том, что при копировании и вставке формулы Excel из одной ячейки в другую ссылки на ячейки изменяются относительно новой позиции, поэтому, например,

Если у нас есть очень простая формула «=A1» в ячейке B1, она изменится следующим образом при копировании и вставке:

При вставке в B2 он становится "=A2"
При вставке в C2 он становится "=B2"
При вставке в формат A2 возвращается ошибка!

В каждом случае изменяется ссылка, чтобы ссылаться на ячейку слева в той же строке, что и ячейка, в которой находится формула, т. е. в той же относительной позиции, что и A1 по отношению к исходной формуле. Причина, по которой возвращается ошибка при вставке в столбец A, заключается в том, что слева от столбца A нет столбцов. Такое поведение очень полезно и позволяет копировать сумму по странице или вниз и автоматически ссылаться на нее. новый столбец или строка, в которой он находится; как я уже упоминал, это поведение Excel по умолчанию.

Однако, как отмечалось выше, могут возникнуть ситуации, когда вы хотите, чтобы некоторые или все ссылки оставались фиксированными, когда они копируются в другое место. Здесь используется знак доллара ($).

Абсолютные ссылки на ячейки

Если вы создаете счет-фактуру, вам потребуется ввести ставку НДС только один раз, но вы можете указать сумму НДС для каждой позиции. В этом случае вы должны исправить ссылку на ячейку, содержащую ставку НДС, путем преобразования ссылки на ячейку. Итак, если, например, ставка НДС находится в ячейке E11, каждая ячейка, которая ссылается на нее, будет использовать, например, «=$E$11»…

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 или получить поддержку в сообществе ответов.

Что такое ошибка недопустимой ссылки на ячейку в функции ВПР?

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

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

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

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

Почему функция ВПР выдает ошибку ссылки?

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

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

Другие возможные причины, ответственные за это:

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

Возможно, вы использовали связывание объекта и внедрение ссылки (OLE) с программой, которая больше не работает.

Как найти проблему со ссылкой на формулу в Excel?

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

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

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

Если средство проверки ошибок включено, нажмите кнопку, отображающую следующую ошибку, и щелкните Показать этапы расчета, если она появится. После этого перейдите на декларацию, подходящую по вашим данным.
Теперь измените формулу или восстановите ячейки на листах, нажав кнопку "Отменить" на панели быстрого доступа сразу после удаления или закрепления ячеек.
Запустите программу, известную как внедрение и связывание объектов (OLE).
Кроме того, убедитесь, что вы правильно используете тему динамического обмена данными (DDE).

Автоматическое решение: средство восстановления MS Excel

Используйте рекомендованный профессионалами инструмент восстановления MS Excel, который лучше всего подходит для исправления поврежденных, испорченных или недоступных файлов Excel. Он может восстановить все поврежденные файлы Excel и помогает восстановить все поврежденные файлы Excel, а также может восстановить все, что включено в диаграммы, комментарии к ячейкам, свойства рабочего листа и другие данные. С помощью этого инструмента пользователи могут восстанавливать файлы .xlsx и .xls.

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

Шаги по использованию инструмента восстановления MS Excel:

Вывод:

Итак, это все об ошибке ссылки на недействительную ячейку VLookup в Excel

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

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

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

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

Если у вас есть какие-либо дополнительные вопросы относительно представленных, сообщите нам об этом в разделе комментариев ниже или посетите наш блог «Восстановление MS Excel».

Удачи.

Маргрет

Маргрет Артур – предприниматель и эксперт по контент-маркетингу. Она ведет технические блоги и делится знаниями по MS Office, Excel и другим техническим дисциплинам. Ее самобытное искусство представления технической информации простым для понимания языком очень впечатляет. Когда она не пишет, она любит незапланированные путешествия.

Блог, ориентированный в основном на Microsoft Excel, PowerPoint и Word, со статьями, призванными вывести ваши навыки анализа данных и работы с электронными таблицами на новый уровень. Научитесь всему: от создания информационных панелей до автоматизации задач с помощью кода VBA!

Как устранить это постоянное сообщение об ошибке

Если вы читаете эту статью, вы, вероятно, были разочарованы постоянным сообщением об ошибке, которое постоянно показывает вам Excel:

“ Excel обнаружил проблему с одной или несколькими ссылками на формулы на этом листе. Убедитесь, что ссылки на ячейки, имена диапазонов, определенные имена и ссылки на другие книги в формулах указаны правильно.

Что ж, спасибо, Excel, за то, что вы предоставили нам общее, всеобъемлющее заявление, которое, по сути, говорит нам: "Я обнаружил проблему с вашей электронной таблицей, я не собираюсь говорить вам, где она или даже какой тип объект вызывает это. но наслаждайтесь просмотром всего файла! О, и я чуть не забыл, я буду напоминать вам об этой ошибке, пока она не будет исправлена."

Поэтому после десятикратного нажатия кнопки "ОК" вы, вероятно, потратили больше времени на то, чтобы выяснить, почему это сообщение продолжает появляться. Что ж, я был на вашем месте, чувствуя полное разочарование, и, надеюсь, я смогу провести вас через различные части вашей электронной таблицы, которые могут содержать эту недействительную ссылку на формулу. Удачи!

Изолировать конкретную вкладку электронной таблицы

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

Проверить внешние ссылки

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

Проверьте диспетчер имен

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