Ошибка при впр недопустимая ссылка

Кто-нибудь знает, отчего может вылезать следующая ошибка?  

  При использовании функции ВПР вылезает сообщение:  

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

  В формуле стоят ссылки на столбец В одной книги и G другой. Формулой пользовался неоднократно, все было в порядке, а тут вдруг вылезло…  

  На всякий случай формула:  

  =ВПР(B11;НАЗВАНИЕ ДРУГОЙ КНИГИ;$F:$G;2;ЛОЖЬ)

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

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

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

В этом сравнительно небольшом руководстве вы найдете простой способ, как обойти такие ошибки, как #Н/Д, #ИМЯ, #ЗНАЧ!, которые часто появляются при работе с этой функцией, а также ознакомитесь с самыми частыми ситуациями, когда эти ошибки возникают.

Несколько причин, по которым возникает ошибка #Н/Д

В формуле =ВПР эта ошибка расшифровывается, как «Нет данных». Простыми словами, у электронной таблицы не получается отыскать значение, которое необходимо пользователю. Есть множество причин, по которым эта проблема может появляться.

Неправильный ввод искомого значения

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

Если ищется приближенное соответствие

Если пользователь применяет range_lookup (то есть, интервальный просмотр) в качестве аргумента функции, в конечном итоге возможно появление ошибки #Н/Д. Это может случиться при возникновении одного из следующих условий:

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

При поиске точного соответствия введенному запросу

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

На данный момент столбец не является крайним левым

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

Преодоление этой трудности заключается в следующем: если по какой-то причине не получается переместить столбец влево, необходимо использовать сразу две функции Excel: ИНДЕКС(), ПОИСКПОЗ(). 

Числовые значения отформатированы, как текстовые

Это также часто встречаемый источник проблем с формулой ВПР(). Нередко пользователь может не замечать, что числовые значения отформатированы как текстовые. Часто такая проблема может встретиться, если информация копируется из других источников.

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

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

Чтобы исправить эту проблему, достаточно просто нажать на параметр «Конвертировать в число» в контекстном меню.

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

Если стоит пробел в начале или в конце содержимого

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

Решение номер 1: Если пробелы находятся в таблице, к которой применяется функция ВПР

Если в основной таблице обнаружены пробелы, можно применить функцию СЖПРОБЕЛЫ в аргументе «Искомое значение». Значительно проще это продемонстрировать на примере.

=ВПР(СЖПРОБЕЛЫ($F2),$A$2:$C$10,3,FALSE)

Решение номер 2: Если лишние пробелы находятся в столбце или таблице поиска

В таком случае не получится легко предотвратить ошибку. Здесь необходимо использовать несколько функций: ИНДЕКС(), ПОИСКПОЗ(), СЖПРОБЕЛЫ().

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

В качестве альтернативного способа решения этой проблемы можно воспользоваться дополнением «Trim Spaces for Excel», которое позволяет убрать ненужные пробелы в формулах как в главной таблице, так и в таблице поиска. Это бесплатный инструмент, который можно скачать по .

Ошибка #ЗНАЧ! в формуле ВПР

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

Причина 1: значение, которое ищется, содержит больше 255 знаков

Это еще одно ограничение этой функции. Нельзя ее применять к ячейкам, содержимое которых длиннее 255 знаков. Если этот предел превышается, в результате может появиться сообщение #ЗНАЧ!.

Чтобы решить эту проблему, необходимо использовать связку формул ИНДЕКС()+ПОИСКПОЗ(). Вот пример формулы, демонстрирующий на практике реализацию этой задачи:

=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))

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

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

Вот как эта формула выглядит:

=ВПР(искомое_значение;'[имя_книги]имя_листа’!таблица;номер_столбца;ЛОЖЬ)

А вот пример использования этой формулы на практике:

=ВПР($A$3;'[New Price.xls]Sheet2′!$B:$C;3;ЛОЖЬ)

Здесь программа будет искать содержимое ячейки A3 столбца c листа Sheet2 файла «NewPrice». Данные формула будет извлекать из столбца C.

Если программа не может получить доступ к файлу, функция будет выдавать ошибку #ЗНАЧ. Причем ошибка будет выдаваться, даже если этот файл открыт в данный момент параллельно с файлом, где прописывается формула.

Причина 3: пользователь ввел значение меньше 1 в аргумент «Номер столбца»

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

Таким образом, если аргумент «Номер столбца» будет меньше единицы, функция также вернет ошибку #ЗНАЧ!

Если же аргумент «Номер столбца» больше, чем общее количество столбцов в этом наборе данных, то функция ВПР() выдаст ошибку #ССЫЛ!.

Ошибка #ИМЯ?

Наиболее простой проблемой является ошибка #ИМЯ? Означает она, что имя функции было введено неправильно. Поэтому, чтобы ее исправить, необходимо просто найти опечатку.

Почему еще может не работать функция ВПР?

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

Нечувствительность к регистру

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

Чтобы решить эту проблему, можно использовать другую функцию Excel, которая может искать нужное значение в столбце (ИНДЕКС(), ПОИСКПОЗ(), ПРОСМОТР() и другие) вместе с СОВПАД(). Последняя функция может различать регистр.

Возвращение первого найденного значения

Кроме использования другой функции для поиска значения с учетом регистра, можно использовать другую формулу, если точно известно, какое по очередности значение нужно найти. Для этого необходимо использовать функции ИНДЕКС(), НАИМЕНЬШИЙ() и СТРОКА(). Так можно будет выбирать 2, 3, 4 или любое другое требуемое значение.

Новая колонка была вставлена в таблицу или убрана из таблицы

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

Здесь также нужно использовать функции ИНДЕКС() и ПОИСКПОЗ(). Они позволяют указать не массив всего диапазона, а лишь требуемые столбцы, и поэтому можно редактировать все остальные без необходимости обновлять связанные с ними формулы.

Искажение ссылок на ячейки при копировании функции

Вам сразу стало понятно, в чем проблема, верно? Решить ее просто: используя абсолютные ссылки на те ячейки, которые нужно оставить стабильными при копировании функции. Для этого необходимо перед названием столбца или строки поставить значок доллара ($). Например, прописать диапазон таким образом: $A$2:$C$100. Более простой вариант: $A:$C. С помощью клавиши F4 можно оперативно изменять тип адреса ячейки.

Обработка ошибок при использовании функции ВПР

Если нет необходимости демонстрировать пользователям код ошибки (например, #Н/Д), можно использовать ВПР() совместно с функцией ЕСЛИОШИБКА() в Excel последних версий вплоть до 2007. Также можно использовать две функции ЕСЛИ()+ЕОШИБКА(), если версия более старая.

Использование функции ЕСЛИОШИБКА

Для этой формулы характерен простой синтаксис:

ЕСЛИОШИБКА(значение; значение_если_ошибка)

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

Вот пример использования функции на практике:

=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);»»)

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

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

Использование функции ЕОШИБКА

Описанная выше функция впервые появилась в Excel 2007 версии, поэтому, если используются более старые версии программы (например, если компьютер имеет небольшую производительность, но на нем нужно обрабатывать большие объемы данных), нужно использовать такую формулу:

=ЕСЛИ(ЕОШИБКА(ВПР формула);»Ваше сообщение при ошибке»;ВПР формула)

На практике формула будет выглядеть следующим образом:

=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));»»;ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))

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

Оцените качество статьи. Нам важно ваше мнение:

Как работает ВПР в Excel читайте в посте “Функция ВПР – чудо экономии времени”. Здесь же вы узнаете, что делать, если ВПР не работает. Так бывает. Скорее всего вы допустили ошибку. Есть четыре наиболее распространенных варианта ошибок:

1. Ошибка Н/Д в каждой ячейке.
2. Ошибка Н/Д преимущественно в нижней части таблицы.
3. Ошибка Н/Д в отдельных ячейках.
4. Ошибка #ССЫЛКА.

А теперь разберем каждый вариант подробнее.

Вариант 1. ВПР вернула ошибку #Н/Д во всех ячейках

Причины:

• Вы неправильно выделили диапазон поиска – начали не с того столбца:

Все будет в порядке, как только вы исправите ошибку:

=впр(B3;'[прайс-лист.xlsx]Лист1′!$B$3:$E$7;3;ложь) на

=впр(B3;'[прайс-лист.xlsx]Лист1′!$C$3:$E$7;3;ложь)

• Не совпадает текст, по которому ВПР ищет данные.

Например, в одном файле текст “Компьютер AF”, в другом файле “Компьютер—AF”. Если не заметили, разница всего лишь в наличии дефиса во втором случае. Тем не менее, ВПР не считает такое расхождение мелочью и, следовательно, не сработает.

Единственное исключение – допускаются разные регистры. Например, “Компьютер AF” и “компьютер af” не составит проблемы для ВПР.

Идентичность текста удобно проверять функцией =ЯЧЕЙКА=ЯЧЕЙКА. Смотрите пример на рисунке ниже и описание под ним:

1. Введите в ячейку D2 знак “=”
2. Выделите ячейку B2 с первым значением Планшет DC.
3. Опять введите “=”.
4. Выделите ячейку C2 со вторым значением.
5. Кликните Enter и протяните формулу вниз.

В результате в ячейках отобразится либо “ИСТИНА” (значения совпадают), либо “ЛОЖЬ” (значения не совпадают).

Как видите, не совпадают значения в строке 4: “Компьютер А” и “Компьютер F” (в ячейке D4 видим слово “ЛОЖЬ”).

Использовать функцию можно как в одном, так и в разных файлах.

Что можно предпринять, если текст не совпадает?

1. Если вы регулярно работаете с этими файлами, поменяйте текст вручную. Единожды потратив на это время, вы значительно сэкономите его в будущем. Если смена текста невозможна, создайте дополнительный столбец, куда введете текст из второго файла.
2. Иногда текст не совпадает из-за каких-то наборов знаков, которые повторяются в каждой ячейке. К примеру, в одном из файлов везде добавлен артикул: “010-01583 Компьютер AF”, а в другом файле этого артикула нет “Компьютер AF”. В этом случае текст можно заменить автоматически. Об этом читайте в следующих постах.

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

Как быть? Воспользуйтесь формулой =СЖПРОБЕЛЫ(ЯЧЕЙКА). Эта формула убирает все лишние пробелы. Подробнее – в посте о работе с текстом.

• В одном файле числа сохранены в виде текста, в другом – в виде чисел:

Для устранения проблемы сделайте следующее:

1. Выделите весь столбец с числами в виде текста (на рисунке выше диапазон B2:В5).
2. Подведите курсор к значку “!” и кликните по появившейся стрелочке.
3. Выберите из выпадающего списка “Преобразовать в число” – см. рисунок ниже.

Вариант 2. ВПР подтянула значения только в верхней части таблицы, в нижней — #Н/Д

Причина:

• Смещение диапазона. Чаще всего это происходит, если данные подтягиваются из одного и того же файла. Например, из Листа 1 на Лист 2 или даже из одного и того же листа. Что происходит? Мы тянем формулу вниз, а вместе с ней тянется и диапазон. Вот так:

Как исправить? Надо закрепить диапазон:

1. Кликните дважды по ячейке с формулой (С5) – отобразится формула.
2. В отобразившейся формуле поместите курсор на В12 и нажмите клавишу F4 на клавиатуре.
3. Переместите курсор на С14 в этой же формуле и опять нажмите F4.
4. Кликните Enter и формула преобразится, из а) в б)

а) =ВПР(В5;В12:С14;2;ложь)

б) =ВПР(В5;$B$12:$С$14;2;ложь)

Значок “$” указывает на то, что диапазон закреплен. Теперь при протягивании формулы вниз, он не будет смещаться.

Вариант 3. ВПР подтягивает только часть значений, остальные  – #Н/Д

Причина:

• В некоторых ячейках текст не совпадает на 100%.

Если таких ячеек немного, то проще всего исправить это вручную, копируя из одного файл в другой.

Вариант 4. ВПР возвращает ошибку #ССЫЛКА

Причина:

• Вы неверно указали номер столбца. ВПР всегда считает столбцы, начиная с первого столбца выделенного диапазона. Поэтому на рисунке ниже красные номера неверные, зеленые – правильные:

И еще несколько советов по функции ВПР в Excel:

1. При вводе формулы используете точку с запятой, а не запятые.
2. Если протягиваете формулу вправо, не забывайте закреплять ссылку в начале формулы, чтобы формула имела вид: =ВПР($В5;$B$12:$С$14;2;ложь). Для этого наведите курсор на В5 (прямо в формуле) и трижды нажмите клавишу F4 на клавиатуре.
3. Не используйте аргумент “ИСТИНА” в ВПР, если работаете с текстом. Это приблизительный поиск, в котором будет много ошибок.

Не работает ВПР в Excel н д? Причина в том, что формула не может найти необходимое значение, к примеру, из-за отсутствия искомого параметра в файле. Убедитесь, что такой показатель имеется в первоначальных данных, проверьте тип значений, удалите лишние пробелы, используйте способы точного / ориентировочного совпадения, задействуйте правильные аргументы и т. д. Ниже рассмотрим, в чем могут быть причины, и как действовать для восстановления работоспособности Эксель.

Причины и пути решения в Excel

Существует много причин, почему не работает ВПР в Excel и появляется надпись Н / Д. В каждой из ситуаций необходимо индивидуально подходить к решению вопроса с учетом возникшей неисправности.

Наиболее эффективный метод

В ситуации, когда не работает функция ВПР в Excel, проверьте наличие элемента на листе или задействуйте в формуле функцию обработки ошибок, к примеру, =ЕСЛИОШИБКА(ФОРМУЛА();0). В таком случае при появлении сбоев в расчете показывается ноль, а в ином случае — результат формулы. Можно дополнить запись “”, чтобы ничего не показывалось, или внести в скобки какую-либо запись.

Ошибка в типе параметров

Характерная причина, почему не работает ВПР в Excel — нахождение исходных / искомых данных к различным типам. К примеру, если вы используете ВПР в виде числа, а исходные данные сохраняются в качестве текста. Для решения вопроса убедитесь, что типы информации идентично. Для проверки формата сделайте следующее:

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

• Измените формат.

Для принудительного внесения изменений нужно изменить формат для всего столбца. Для начала примените требуемое форматирование, а после выберите «Данные», «Текст по столбцам» и «Готово». После этого проверьте, появляется Н Д или нет.

Лишние пробелы

Распространенная причина, почему не работает формула ВПР в Excel, состоит в наличии пробелов. Для их удаления используйте функцию СЖПРОБЕЛЫ.

Ошибки метода поиска совпадения

Следующее объяснение, почему не срабатывает ВПР в Excel  и возвращается Н Д — ошибки в применении метода совпадения. По умолчанию у опции ВПР имеется аргумент «интервальный просмотр», который дает команду на поиск точного совпадения даже при отсутствии сортировки данных в таблице.

Для поиска точного совпадения введите для аргумента «интервальный_просмотр» показатель ЛОЖЬ.

При этом учтите, что ИСТИНА, которое дает возможность поиска приблизительного параметра, может вернуть ошибку Н / Д. При использовании опции ПОИСКПОЗ попробуйте поменять параметр аргумента «тип_сопоставления» для указания порядке сортировки таблицы.

Не соответствие числа строк / столбцов заданному диапазону

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

В ячейке введена надпись Н / Д или Н Д

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

Другие ошибки

Дополнительно стоит выделить и ряд других ситуаций, когда ВПР в Excel по какой-то причине не работает:

1. В используемой формуле нет одного или более аргументов. Для исправления проблемы введите все необходимые документы и проверьте, работает опция или нет. Для контроля можно использовать Visual Basic.
2. Пользовательская опция недоступна. Для исправления проблемы убедитесь, что документ Excel с пользовательской функцией открыт, а опция работает корректно.
3. Макрос имеет функцию, которая возвращает Н Д. Если ВПР не работает по этой причине, для исправления ошибки убедитесь в правдивости аргументов и их нахождении в нужных местах.
4. Изменение защищенного файл с опцией ЯЧЕЙКА. Для исправления ситуации, когда ВПР в Excel не работает, жмите на комбинацию Ctrl+Alt+F9.
5. Столбец не является первым слева дли поискового диапазона. Для решения проблемы нужно ввести соответствующий параметр и проверить, появляется ли Н Д. Как вариант, можно использовать функции ИНДЕКС и ПОИСКПОК в качестве гибкой альтернативы для ВПР.
6. Неправильное форматирование числа. Бывают ситуации, когда цифры указаны в текстовом формате. Это часто происходит при импортировании сведений из внешней базы данных или при вводе апострофа перед числом для сохранения нуля в начале. Для решения проблемы жмите по ошибке и укажите Convert to Number. При появлении Н Д для многих чисел выделите их и жмите правой кнопкой мышки, а после выберите Format Cells и вкладку Число и Числовой.

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

Что за функция

В завершение кратко рассмотрим, что это за опция ВПР в Excel, и как она работает. Простыми словами, это опция, позволяющая переставлять данные из одной таблицы в соответствующие параметры другой. Английское название опции звучит как VLOOKUP. Это очень полезная опция, позволяющая сэкономить время и одновременно обработать большое количество параметров.

К примеру, в вас есть две таблицы. Первая — цены и названиями, вторая — заказ на покупку продукции. Осуществлять поиск в первом документе и пытаться вписать цену в заказ трудно. Необходимо, чтобы работа проходила автоматически. Для этого достаточно найти нужное значение в 1-м столбце и вернуть его содержимое из столбца той же строки, где находится название.

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

Отличного Вам дня!

Microsoft Excel. Ошибка #ССЫЛКА!, как исправить?

Исправить ошибку #ССЫЛКА! в Excel можно только непосредственно после того как она была замечена, т.к., чаще всего, она возникает, если был удален диапазон (обычно через удаление строк или столбцов) на который ссылалась некая формула.

Пример: в диапазоне B4:B9 находятся числа, которые нам нужно просуммировать. Пишем формулу =СУММ(B4:B9) и получаем их сумму. Если же удалить (предварительно выделив) строки с 4-й по 9-ю, то удалятся все значения в этих строках, а также во всех формулах, в которых этот диапазон был использован полностью (именно с 4-й по 9-ю строку) появится ошибка #ССЫЛКА!. То есть, удалив строки — получим вместо СУММ(B4:B9) уже =СУММ(#ССЫЛКА!)

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

появилось значение ошибки #ССЫЛКА! сразу же отменяем удаление строк (или столбцов).

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

В некоторых случаях исправить эту ошибку невозможно даже сразу обнаружив ее. Если удалить лист, то также возникнет эта ошибка, только рядом будет еще идти адрес (ячейки или диапазона). Пример: =#ССЫЛКА!A2. Однако, так как удаление листа невозможно отменить, то, соответственно, исправить эту ошибку тоже не получится. (как вариант, можно закрыть файл без сохранения, а потом открыть его — тогда удаленный лист еще будет на своем месте и ошибка #ССЫЛКА! просто не возникнет)

Также ошибка #ССЫЛКА! может возникать, если мы пытаемся сослаться на ячейки, которых нет на листе Excel.

результат работы этой формулы — #ССЫЛКА! так как ячейки выше A1 в Excel не существует, а эта формула, как раз пытается сгенерировать ссылку на ячейку выше A1.

Исправление ошибки #REF! #BUSY!

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

Пример ошибки #ССЫЛКА! из-за удаления столбца

В следующем примере в столбце 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;ЛОЖЬ) возвращает #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.

Как исправить ошибку #ССЫЛКА! в Excel

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

Вариант 1. Исправление формул

Представим такую ситуацию. У нас есть файл с базой данных, где хранятся данные по продажам за январь — февраль (лист в файле назван «БД янв — фев») и файл со сводными данными, который суммирует выручку по месяцам. Мы открыли файл с базой данных, внесли в него данные по продажам за март и внимание(!) переименовали лист в «БД янв — март», после этого открываем файл со сводом и видим там вместо данных по выручке за январь, февраль, что мы делали раньше, ошибку #ССЫЛКА!. Это произошло из-за того, что функция ссылается на данные в листе «БД янв — фев», которого нет, так как мы его переименовали.

Что бы исправить эту ситуацию, закрываем файл со сводом, не сохраняя изменения (!), если вы сохраните изменения — то формулы будут навсегда испорчены и вам придется писать их заново. Далее в файле с базой данных переименовываете лист так, как он назывался ранее, т.е. «БД янв — фев» и снова открываете файл со сводом. Ошибка пропала, функции снова работают. Теперь при открытых двух файлах, вы можете переименовать лист в базе данных и ошибка не появится, так как Excel изменит ссылки в функциях (он умеет это делать только в открытых книгах).

Вариант 2. Функция ЕСЛИОШИБКА

При помощи функции ЕСЛИОШИБКА можно обработать ошибки, которые возникают при написании формул, в том числе ошибку #ССЫЛКА!.

Синтаксис функции ЕСЛИОШИБКА следующий: первым аргументом идет функция, которая может вызвать ошибку, вторым аргументом функционал, который будет вызван, если ошибка произошла.

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