Недопустимая ссылка в эксель как найти - Исправление недочетов и поиск решений вместе с Examum.ru

[Excel2003-RUS]
1) Простой алгоритм выглядит так: Меню «Сервис»—«Проверка наличия ошибок…» (дублируется кнопкой на панели «Зависимости»).
Далее исправлять ошибки по очереди.
В данном случае Excel будет вести себя механистически — просто будет искать ошибочные ячейки сверху вниз. Поэтому до «первоисточника» путь может оказаться неблизким.
2) Списка ВСЕХ ошибок с рекомендациями — нет, не видел (справедливости ради, и не искал, т.к. с «неприятностями» справляюсь по мере их возникновения, а не коплю знания впрок).
3) Общие рекомендации по устранению конкретной ошибки даются в сообщении об ошибке: «Проверьте правильность указания пути, книги, названия диапазона и ссылки на ячейки.»
(примеч.: сама формулировка сообщения «…содержит ОДНУ ИЛИ НЕСКОЛЬКО …» говорит о том, что списка сразу всех «ошибочных ячеек» Excel Вам представить, скорее всего, не может. Переводя на человеческий — «есть как минимум одна, а сколько всего — не знаю»).
4) Некоторый общий перечень ошибок (не уверен, что всех) содержится в разделе справки «Поиск и исправление ошибок в формулах» (приведены причины/рекомендации по устранению по ошибкам «#####», «#ЗНАЧ!», «#ДЕЛ/0!», «#ИМЯ?», «#Н/Д», «#ССЫЛКА!», «#ЧИСЛО!», «#ПУСТО!». Описано «Наблюдение за формулой и результатом ячейки» при помощи «Окна контрольного значения». Даны инструкции по «пошаговому вычислению сложных формул». Описан метод «Прослеживание связей между формулами и ячейками» (трассировка зависимостей). Есть и подраздел «Исправление общих ошибок в формулах», включающий, например, работу со списком проверяемых/игнорируемых ошибок).

Источник

Как исправить #REF! ошибка

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 Еще…Меньше

Ошибка #REF! отображается, когда формула ссылается на недопустимую ячейку. Чаще всего это происходит потому, что формула ссылается на ячейки, которые были удалены или заменены другими данными.

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

Удаление столбцов B, C или D приведет к #REF! . В этом случае мы удалим столбец C (Продажи 2007), а формула теперь будет читать =СУММ(B2,#REF!,C2). Если вы используете явные ссылки на ячейки, как это (когда вы ссылаетесь на каждую ячейку по отдельности, разделенные запятой) и удаляете строку или столбец, на которые ссылается ссылка, Excel не может разрешить их, поэтому он возвращает #REF! могут вызвать текст и специальные знаки в ячейке. Это основная причина, по которой не рекомендуется использовать явные ссылки на ячейки в функциях.

Решение

Если вы случайно удалили строки или столбцы, вы можете немедленно нажать кнопку «Отменить» на панели быстрого доступа (или нажать клавиши CTRL+Z), чтобы восстановить их.
Измените формулу так, чтобы она ссылалась на диапазон, а не на отдельные ячейки, например =СУММ(B2:D2). Теперь можно удалить любой столбец в диапазоне суммирования, и Excel автоматически скорректирует формулу. Чтобы вычислить сумму значений в строках, также можно использовать формулу =СУММ(B2:B5).

В следующем примере =ВПР(A8;A2:D5;5;FALSE) вернет #REF! ошибка, так как она ищет значение, возвращаемое из столбца 5, но диапазон ссылок — A:D, то есть только 4 столбца.

Решение

Расширьте диапазон или уменьшите значение столбца для поиска так, чтобы он попадал в указанный диапазон. Формулы =ВПР(A8;A2:E5;5;ЛОЖЬ) будет работать правильно, так же как и формула =ВПР(A8;A2:D5;4;ЛОЖЬ).

В этом примере формула =INDEX(B2:E5,5;5) возвращает #REF! Ошибка, так как диапазон INDEX состоит из 4 строк на 4 столбца, но формула запрашивает возврат того, что находится в 5-й и 5-й строках.

Решение

Измените ссылки на строки и столбцы так, чтобы они попадали в диапазон поиска функции ИНДЕКС. Формула =ИНДЕКС(B2:E5;4;4) вернет правильный результат.

В следующем примере функция INDIRECT пытается ссылаться на закрытую книгу, вызывая #REF! могут вызвать текст и специальные знаки в ячейке.

Решение

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

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

Вычисляемые ссылки на связанные книги не поддерживаются.

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

Если вы использовали ссылку OLE, которая возвращает #REF! ошибка, а затем запустите программу, которую вызывает ссылка.

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

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

Если макрос вводит на листе функцию, которая ссылается на ячейку над функцией, а ячейка, содержащая функцию, находится в строке 1, функция вернет #REF! поскольку нет ячеек над строкой 1. Проверьте функцию, чтобы узнать, ссылается ли аргумент на ячейку или диапазон ячеек, которые недопустимы. Для этого может потребоваться изменить макрос в редакторе Visual Basic (VBE), чтобы учесть эту ситуацию.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Полные сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Поиск ошибок в формулах

Функции Excel (по алфавиту)

Функции Excel (по категориям)

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

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

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

Источник

Проблему описал

gsheppar

в 2010 году на сайте mrexcel.com (по-английски). Оставлено без ответа.

http://www.mrexcel.com/forum/excel-questions/464779-run-time-error-1004-name-you-entered-not-valid.h…

Проблема «жива» и актуальна для версий Excel: 2010, 2013, 2016 (в 2007 — не проверял).

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

Штатные инструменты ошибок не видят (надстройка Name Manager, вроде, тоже). Указанные объекты выведены программно и проанализированы. Ошибок нет, но обнаружено, что использование в книге формул, введенных впервые в Excel 2007, приводят к создание имен с префиксом «_xlfn.» (своего рода, маркер):
Name RefersTo RefersToLocal
_xlfn.COUNTIFS =#NAME? =#ИМЯ?
_xlfn.IFERROR =#NAME? =#ИМЯ?

Разумеется, в моей книге использованы соответствующие формулы (значения аргументов в скобках здесь рассматривать не надо):
=СЧЁТЕСЛИМН(КР;B$25;НаимОц10;$A26)
=ЕСЛИОШИБКА(ПЕРСЕНТИЛЬ(ЕСЛИ((КР=B$4)*НЕ(ЕПУСТО(БаллОц10));БаллОц10);0,25);НД())

В Интернете пишут (врут), что это якобы случается, если открыть приложение, использующее новые формулы, в Excel 97-2003, но проверено, что это не так. Советуют также заменить новые формулы старыми решениями: например, вместо ЕСЛИОШИБКА (IFERROR) использовать комбинацию формул ЕСЛИ (IF) и ЕОШИБКА (ISERROR), а формулу СЧЁТЕСЛИМН (COUNTIFS) заменить на формулу СУММАПРОИЗВ (SUMPRODUCT) и т.д. и т.п. Увы, пробовал — заменил, но это не привело к желаемому результату: то самое сообщение продолжает выскакивать, как черт из табакерки: раздражает… Но всё работает. При этом, естественно, имена с префиксом «_xlfn.» исчезают (после сохранения книги и повторного открытия).
Очевидно, что эта проблема как была в Excel 2010, так и осталась в Excel 2016.
При открытии книги всё обстоит нормально, но стоит, например, изменить раз или другой раз, третий… размер таблицы (ListObject) на листе, и вдруг, совершенно неожиданно, появляется упомянутое сообщение и потом уже выходит постоянно при всяком изменении или сохранении файла, пока не перезапустишь Excel. В таблице, и на листе, где она размещена, вообще нет ни одной формулы (только исходные данные). Локализовать ошибку не удается.
Есть предположение, что это «косяк» Excel. Неужели надо воссоздать всё приложение заново, поэтапно, осторожно и с оглядкой? И тогда посмотреть. Или как?
Возможно, что никакой связи упомянутого сообщения с префисом имен «_xlfn.» нет вовсе. Кто знает, кто сталкивался с подобным?
Из-за этой ошибки приложение нельзя передать пользователям.

Источник

Excel

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

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

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

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

Используйте инструмент проверки ошибок

Один из самых быстрых способов найти ошибки в электронной таблице Excel — использовать Инструмент проверки ошибок. Если инструмент обнаружит какие-либо ошибки, вы можете исправить их и попробовать сохранить файл еще раз. Однако инструмент может не найти все типы ошибок.

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

Чтобы использовать инструмент проверки ошибок в Excel:

Выберите Формулы вкладка
Нажмите на Проверка ошибок инструмент.
При обнаружении ошибки в инструменте появится информация о причине ошибки.
Либо исправьте ошибку, либо удалите формулу, вызывающую проблему, если она не нужна.
в Проверка ошибок инструмент, нажмите Следующий чтобы увидеть следующую ошибку и повторить процесс.
Когда больше ошибок не будет найдено, вы увидите всплывающее окно, информирующее вас об этом факте.
Повторите процесс для каждого листа документа.
Попробуйте сохранить документ еще раз, чтобы проверить, исправлена ли ошибка ссылки на формулу.

Найдите проблемный лист

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

Например, если у вас есть документ с несколькими листами, вам нужно сузить проблему до одного листа (или нескольких листов), вызывающего проблему.

Чтобы найти лист, вызывающий ошибку ссылки на формулу:

Откройте первый лист в электронной таблице.
Нажмите Ctrl+А в Windows или Cmd+А на Mac, чтобы выбрать весь лист. Кроме того, вы можете нажать кнопку в самом верхнем левом углу таблицы.
Щелкните правой кнопкой мыши и выберите Копировать.
В качестве альтернативы используйте сочетание клавиш Ctrl+С в Windows или Cмд+С на Mac.
Далее откройте Файл меню.
Выбирать Пустая рабочая тетрадь.
Щелкните правой кнопкой мыши в ячейке А1 и выберите Вставить икона.
В качестве альтернативы используйте сочетание клавиш Ctrl+V в Windows или Ctrl+V на Mac.
Попробуйте сохранить новый файл. Если вы можете сохранить файл без ошибок, то этот конкретный лист не содержит источника вашей ошибки.
Повторите эти действия с каждым листом документа, пока не найдете лист или листы, вызывающие проблемы.

Поиск ошибок

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

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

Чтобы найти ошибки на листе Excel:

Откройте лист, который, как вы обнаружили, содержит потенциальные ошибки.
Нажмите Ctrl+F в Windows или Ctrl+F на Mac, чтобы открыть инструмент поиска.
Тип # в Найти то, что поле и убедитесь, что Заглянуть установлен на Ценности.
Нажмите Находить Все чтобы найти все экземпляры ячейки, содержащие хэштег.
Если какие-либо экземпляры будут найдены, они появятся в инструменте поиска. Вы можете нажать на каждую из них, чтобы перейти к этой ячейке и внести необходимые изменения, или просто удалить формулы, если они вам не нужны.
Повторите процесс для всех других листов, которые, по вашему мнению, могут содержать ошибки.
Попробуйте сохранить документ еще раз, чтобы узнать, устранена ли проблема.

Проверьте свои графики

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

Чтобы проверить исходные данные для ваших диаграмм Excel:

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

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

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

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

Чтобы проверить действительность внешних ссылок в Excel:

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

Проверьте свои сводные таблицы

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

Чтобы проверить свои сводные таблицы на наличие ошибок в Excel:

Откройте лист, содержащий вашу сводную таблицу.
Выберите сводную таблицу.
На ленте щелкните значок Анализ сводной таблицы появившееся меню.
На ленте щелкните Изменить источник данных.
Проверить Таблица/диапазон чтобы убедиться, что он относится к действительному диапазону ячеек.
Если это не так, отредактируйте значения таблицы/диапазона или, если вам это не нужно, удалите сводную таблицу.
Попробуйте сохранить документ еще раз.

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

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

Если Excel продолжает давать сбой, есть несколько полезных исправлений, которые вы можете попробовать. Если вы хотите игнорировать любые проблемы на листе, вы можете вместо этого игнорировать все ошибки в Excel.

Источник

Формула на этом листе содержит одну или несколько ошибок недопустимых ссылок

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

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

Как избавиться от этого окна сообщения? Я пробовал использовать

Но это не работает.

3 ответа

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

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

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

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

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

Я упростил формулы всех названных диапазонов, например:

Я поместил последнюю часть «8-CountBlank(Other named range)» для вычисления в Рабочий лист и заменил ее в формуле для диапазона с результатом. Если диаграмма была полностью пустой, всплывающие ошибки не отображались. Я надеюсь когда-нибудь помочь кому-нибудь с той же проблемой.

Формула на этом листе содержит одну или несколько ошибок недопустимых ссылок

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

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

Как избавиться от этого окна сообщений? Я попытался использовать

но это не сработает.

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

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

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

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

Я упростил все формулы названных диапазонов, например:

Я поместил последнюю часть «8-CountBlank(Other named range)» для вычисления в листе и заменил ее в формуле для диапазона с результатом. Если диаграмма не была полностью пустой, отображались больше ошибок всплывающих окон. Я надеюсь, что когда-нибудь кто-нибудь помог бы с той же проблемой.

Формула на этом листе содержит одну или несколько ошибок недопустимых ссылок

Как найти формулу, содержащую недопустимую ссылку, в Excel: Использование Microsoft Excel

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

Как избавиться от этого окна сообщения? Я пробовал использовать

но это не работает.

Быстрый способ найти все недопустимые ссылки: F5 -> Special, выберите «Формулы», затем отметьте только «Ошибки». Если он выбирает несколько ячеек, меняет их фон на красный, а затем исправляет их все по одной

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

Спасибо, @Jake. Я собираюсь попробовать это. Между тем, не могли бы вы помочь мне с другим вопросом bit.ly/1J9ndmu (ищу решение второй части — части легенды)

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

Я упростил формулы всех названных диапазонов, например:

Я поставил последнюю часть ‘8-CountBlank(Other named range)’ для вычисления на рабочем листе и замены этого в формуле для диапазона на результат. Если диаграмма была полностью пустой, всплывающие ошибки не отображались. Я надеюсь когда-нибудь помочь кому-нибудь с той же проблемой.

ссылки в диаграммах также могут создавать эту ошибку, вы просматривали данные и видели, где вы видите #REF

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

Источник