Как исправить #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 (по категориям)
Нужна дополнительная помощь?
При работе в Excel можно столкнуться с ошибкой #ССЫЛКА!. Эта ошибка возникает тогда, когда функция ссылается на ячейку, которой не существует. В этой статье мы разберем основные способы, как исправить эту ситуацию.
Вариант 1. Исправление формул
Представим такую ситуацию. У нас есть файл с базой данных, где хранятся данные по продажам за январь — февраль (лист в файле назван «БД янв — фев») и файл со сводными данными, который суммирует выручку по месяцам. Мы открыли файл с базой данных, внесли в него данные по продажам за март и внимание(!) переименовали лист в «БД янв — март», после этого открываем файл со сводом и видим там вместо данных по выручке за январь, февраль, что мы делали раньше, ошибку #ССЫЛКА!. Это произошло из-за того, что функция ссылается на данные в листе «БД янв — фев», которого нет, так как мы его переименовали.
Что бы исправить эту ситуацию, закрываем файл со сводом, не сохраняя изменения (!), если вы сохраните изменения — то формулы будут навсегда испорчены и вам придется писать их заново. Далее в файле с базой данных переименовываете лист так, как он назывался ранее, т.е. «БД янв — фев» и снова открываете файл со сводом. Ошибка пропала, функции снова работают. Теперь при открытых двух файлах, вы можете переименовать лист в базе данных и ошибка не появится, так как Excel изменит ссылки в функциях (он умеет это делать только в открытых книгах).
Вариант 2. Функция ЕСЛИОШИБКА
При помощи функции ЕСЛИОШИБКА можно обработать ошибки, которые возникают при написании формул, в том числе ошибку #ССЫЛКА!.
Синтаксис функции ЕСЛИОШИБКА следующий: первым аргументом идет функция, которая может вызвать ошибку, вторым аргументом функционал, который будет вызван, если ошибка произошла.
В данном случае мы оставили напоминание себе о том, что листы, при закрытой книге со сводом переименовывать нельзя. Это позволит быстро сориентироваться в ситуации и исправить формулы, как описано в варианте 1.
Спасибо, что прочитали статью. Надеюсь она была для вас полезной.
|
В ячейках стоят формулы, тянущие данные из других книг Excel, в некоторых случаях чтобы оставить формулы , но занулить значения , ссылка делается на пустую ячейку. Проблема в том , что после обновления файлов появляется ошибка #ссылка! , если перейти по ссылке то , после окртыия файлов ошибка исчезает. |
|
|
LVL Пользователь Сообщений: 903 |
вероянее всего после открытия книги Вы пытаетесь пересчитать формулы вручную, а книги на которые есть ссылки закрыты, соответственно ошибка. Поменяйте режим пересчёта на авто, тогда ссылки будут обновляться корректно, но только по запросу |
|
бобочек  Пользователь Сообщений: 14 |
Ошибка «ссылка!» появляется при копировании ячейки из одного листа в другой на одной книге (Excel2010). Активных надстроек нет, Chrome отключен. |
|
Bema  Пользователь Сообщений: 6759 |
Файл бы увидеть. Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл |
|
БМВ  Модератор Сообщений: 21380 Excel 2013, 2016 |
Не посомтрел что ТС иной Изменено: БМВ — 09.03.2018 14:38:16 По вопросам из тем форума, личку не читаю. |
|
бобочек Пользователь Сообщений: 14 |
Ячейка С15 =(C3*КОРЕНЬ(2)-C4/2)*C8/C29/K2 превращается в =(#ССЫЛКА!*КОРЕНЬ(2)-#ССЫЛКА!/2)*#ССЫЛКА!/C18/#ССЫЛКА! Ну и так со всеми остальными |
|
БМВ Модератор Сообщений: 21380 Excel 2013, 2016 |
бобочек, изучайте относительные и абсолютные ссылки и изменении адресов при копировании содержимого формул. P.S. Z. да согласен, сбила с толку 8 ка в числе. и 2013 со слепу за 2018 принял Изменено: БМВ — 09.03.2018 14:49:01 По вопросам из тем форума, личку не читаю. |
|
Z  Пользователь Сообщений: 6111 Win 10, MSO 2013 SP1 |
#8 09.03.2018 14:45:36
off Да и «возраст» темы почтительный — стоило ли тревожить?!. «Ctrl+S» — достойное завершение ваших гениальных мыслей!.. |
||
|
бобочек Пользователь Сообщений: 14 |
Как-то раньше не было таких проблем, сейчас нашел только то, что если ячейку С15 копирую на другой лист на тот же С15, то все нормально, после этого даже копия по другому адресу проходит, если же сразу копировать не на С15, то идут неустранимые ошибки ССЫЛКА! Если Вы о адресации с $, то я не знаю как это поможет, неужели перед копированием надо в исходной формуле вставлять $ а потом это чистить ? Изменено: бобочек — 09.03.2018 15:18:11 |
|
vikttur Пользователь Сообщений: 47199 |
Показать пример с проблемой — намного эффективнее, чем кричать «Караул!» |
|
бобочек Пользователь Сообщений: 14 |
Еще примеры : Исходная С16 =C2*КОРЕНЬ(2)*C8/C29/K2 перенос на другой лист на С16 =C2*КОРЕНЬ(2)*C8/C29/K2 |
|
БМВ Модератор Сообщений: 21380 Excel 2013, 2016 |
бобочек, 22-18=4, 4-4=0 адрес со строкой 0 не существует. Еще нужны пояснения? По вопросам из тем форума, личку не читаю. |
|
бобочек Пользователь Сообщений: 14 |
#13 09.03.2018 15:51:21 Кажется дошло, спасибо ! В первом примере G4 должен был стать D0, а это низя. Изменено: бобочек — 14.03.2018 16:10:47 |
|
вместо текста отображается ссылка |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
При вставке или написании в ячейку программы Excel ссылки на интернет-ресурс, URL адреса, а также адреса электронной почты, запись автоматически преобразуется в гиперссылку. Чаще всего это мешает и не нужно. Рассмотрим варианты решения – работы с такими записями.
1. Разово отменить создание автоматической гиперссылки
После ввода текста и автоматического создания гиперссылки, нажмите сочетание клавиш Ctrl+Z, либо кнопку «Отменить» в самом вверху. Сама ссылка исчезнет, а введённый текст останется таким же.
Ещё один вариант. Ставьте перед записью символ ‘ (апостроф) – в таком случае она будет считаться обычным текстом. После ввода всей записи, апостроф исчезнет.
Где находится обычный ‘ апостроф? Буква «Э» на клавиатуре. Для ввода данного символа, перейдите на раскладку английского языка.
Либо комбинация клавиш (Alt-0146): зажмите левый Alt и на блоке доп. клавиш (правая часть клавиатуры) наберите 0146.
2. Отключение автоматического создания гиперссылок
Можно навсегда отключить создание автоматических гиперссылок:
- Нажмите Файл > Параметры, для открытия окна с параметрами Excel
- В левой части выберите Правописание
- В правой части нажмите кнопку «Параметры автозамены»
4. Выберите вкладку «Автоформат при вводе» и уберите чекбокс с пункта «адреса Интернета и сетевые пути гиперссылками».
Может быть интересно:
✦ Рейтинг лучших кабелей для зарядки смартфона и планшета
✦ Лучшие зарядные устройства для телефона и планшета
3. Удаление уже созданных гиперссылок
Чтобы убрать в ячейке уже существующую гиперссылку, щёлкните правой кнопкой мыши на ячейки и выберите в контекстном меню команду Удалить гиперссылку. При этом содержимое ячейки сохранится.
В более новых версиях Excel вы можете удалять сразу несколько гиперссылок. Можно выбрать диапазон, либо в выборочном порядке ячейки, нажать правую кнопку мыши и выбрать Удалить гиперссылки. Команда удаления работает, даже если выбор диапазона включает в себя ячейки, которые не содержат гиперссылки.
Можно удалить все гиперссылки на листе, нажав сочетание клавиш Ctrl+A (выбор всех ячеек). Далее на любой ячейке нажмите правую кнопку мыши и Удалить гиперссылки.
Как исправить в экселе ошибку #ССЫЛКА!?
В программе эксель часто люди сталкиваются с ошибкой #ССЫЛКА!, и многие из них не знают, что в такой ситуации делать. Поэтому рассмотрим подробную инструкцию, как можно исправить ошибку #ССЫЛКА!.
Первый шаг. Перед нами таблица, в которой должен быть подсчитан финансовый результат, но вместо этого, мы наблюдаем ошибку #ССЫЛКА!.
Второй шаг. Чтобы исправить данную ошибку, нужно понять природу её появления. Ошибка #ССЫЛКА! появляется в ячейке, если там раньше стояла формулу, которая суммировала определенные ячейки с этого листа или другого. Кто-то удалил полностью одну из ячеек входящих в формулу, в результате появилась данная ошибка.
Следовательно, нужно найти сначала ячейку, в которой изначально появилась эта ошибка. Для этого нажмем на клавиатуре сочетание клавиш «CTRL+F», а в появившемся меню, в строку «Найти» введем текст: #ССЫЛКА! После нажмем на кнопку «Найти все».
Третий шаг. Программа эксель находит источник данной ошибки, он находится в ячейке «В4», в чем мы убеждаемся, выделив эту ячейку.
Четвертый шаг. На этом шаге, вы должны проверить логику построения формулы и понять, что за ячейка была удалена, нужная ли в ней была информация. Если нужная иформация, то попробуйте через клавишу «Отменить ввод» (CTRL+Z) вернуть эту ячейку. Если это невозможно, то придется заново восстановить эту ячейку и повторно прописать формулу.
В приведенном примере, данная ячейка была лишняя, поэтому я просто поменял формулу =B2-B3-#ССЫЛКА!, на формулу =B2-B3 и в итоге все оставшиеся ошибки исчезли и таблица стала полностью рабочей.
Как сделать ссылку активной в Microsoft Excel
Вставленные в таблицу Excel ссылки не всегда становятся активными, из-за чего переход по ним в одно нажатие попросту невозможен. В таких случаях ссылку необходимо активировать, чтобы дальнейшее взаимодействие с ней было более удобным. Для этого есть разные способы, каждый из которых подойдет в определенных ситуациях.
Вариант 1: Вставка ссылки из браузера
Excel работает таким образом, что при копировании ссылки из адресной строки веб-обозревателя она сразу же становится активной. Используйте этот метод, если нужно вставить новые строки в таблицу, а не редактировать уже существующие. Найдите требуемую страницу в браузере и скопируйте ее, выделив и используя комбинацию клавиш Ctrl + C.
Перейдите в Microsoft Excel, после чего вставьте ссылку в подходящую ячейку, подогнав ее по размеру. Выберите любую другую клетку для редактирования, чтобы заметить, как применяется изменение к этой. Теперь вы можете нажать ЛКМ по ссылке, чтобы сразу открыть ее в браузере по умолчанию.
Прочитайте текст на всплывающей подсказке, появляющейся при наведении курсора на саму ссылку: в ней есть информация о том, что переход происходит при одинарном нажатии, а для выделения необходимо кликнуть и удерживать кнопку мыши. Это поможет разобраться с редактированием, если ранее вы с ним не работали. 
Вариант 2: Самостоятельная активация ссылки
Разберу вариант, когда ссылка уже вставлена в таблицу, но не является активной. Этот же метод подойдет в тех случаях, когда нужно сделать слово или надпись с адресом в виде ссылки, по которой и будет осуществлен переход при нажатии.
Выделите ячейку со ссылкой или надписью, после чего нажмите по ней ПКМ.
Из появившегося контекстного меню выберите пункт «Ссылка», открыв соответствующее окно с настройками.
Если сейчас ссылка представлена в поле «Текст», ее необходимо скопировать и перенести в поле «Адрес».
Вместо ссылки в поле «Текст» вы можете вписать любую надпись, нажатие по которой отвечает за переход по ссылке, вставленной в поле «Адрес».
Примените изменения и вернитесь к таблице. На следующем скриншоте вы видите результат активации ссылки с заменой ее текста на более уместный.
Думаю, принцип использования окна «Ссылка» вам понятен, и проблем с переименованием возникнуть не должно. Используйте такой метод, когда нужно заменить громоздкую ссылку на лаконичную надпись, которая не будет нарушать внешний вид таблицы, но при этом останется функционально полезной.
Вариант 3: Использование макроса
Последний вариант самый необычный и подходит в редких случаях. Чаще всего он применим, когда в таблице уже есть перечень ссылок, находящихся в рамках одного столбца, но при этом они неактивны. Активировать каждую при помощи представленных выше инструкций можно, но это займет много времени. Проще сделать это при помощи специального макроса.
Для начала через главное окно перейдите в меню «Параметры».
Выберите раздел «Настроить ленту» и поставьте галочку возле основной вкладки «Разработчик». Так вы обеспечите ее отображение в верхней панели программы, что нам сейчас и пригодится.
Примените изменения, вернитесь к таблице, перейдите на соответствующую вкладку и запустите оснастку «Visual Basic».
Дважды щелкните по файлу книги, чтобы справа отобразилось окно редактирования кода.
Вставьте туда оставленный ниже код, заменив значения «a2» на номер ячейки, с которой начинается активация ссылок, а «а» – на букву столбца.
Строку для редактирования вы видите на следующем изображении. 
Сохраните результат, нажав по соответствующей кнопке на панели сверху или используя сочетание клавиш Ctrl + S.
Закройте редактор кода и запустите окно «Макрос». Дважды щелкните по строке с созданным макросом для активации ссылок.
Все изменения выполняются автоматически, а далее вам остается только убедиться в том, что вместо обычного текста появились активные ссылки, переход по которым возможен (если в их содержимом действительно находится веб-адрес).
Все перечисленные варианты работают в любых ситуациях, а выбор зависит исключительно от того, с какой задачей столкнулись лично вы. Конечно, для активации одной ссылки не захочется создавать макрос, а вот для массовой обработки он окажется как никогда кстати.
Относительные, абсолютные и смешанные ссылки в Excel
Одна из основных функций Эксель – работа с формулами, которые могут содержать как конкретные числовые значения, так и ссылки на другие ячейки таблицы. При этом ссылки могут быть как относительные, так и абсолютные. В этой статье мы разберемся, в чем их различия, и для чего они нужны.
Относительная ссылка – ссылка, в которой координаты ячейки меняются при копировании формулы в другую ячейку.
Абсолютная ссылка – ссылка, в которой координаты ячейки зафиксированы и не меняется при копировании.
Относительные ссылки
Чтобы было нагляднее, давайте разберем примеры, и начнем мы с относительных ссылок, как более распространенных.
Допустим, у нас есть таблица, в которой заполнены столбцы с ценой за единицу товара и количеством реализованной продукции. Наша задача – вычислить сумму по каждому наименованию в соответствующей колонке.
Вот что нам нужно сделать:
- Переходим в самую верхнюю ячейку результирующего столбца (не считая шапки таблицы), ставим знак “равно” (“=”) и пишем в ней формулу: = B2*C2 .
- Когда выражение готово, нажимаем клавишу Enter на клавиатуре, после чего получаем результат в ячейке с формулой.
Возможные ошибки при работе с относительными ссылками
Безусловно, благодаря относительным ссылкам существенно упрощаются многие расчеты в Эксель. Однако, они не всегда помогают решить поставленную задачу.
Допустим, нам нужно посчитать долю каждого наименования в общих продажах.
- Встаем в первую ячейку столбца для расчетов, где пишем формулу: =D2/D13 .
- Нажимаем Enter, чтобы получить результат. После того, как мы скопируем формулу на оставшиеся ячейки столбца, вместо результатов увидим следующую ошибку: #ДЕЛ/0! .
Дело в том, что из-за того, что все ссылки на ячейки в формуле, которую мы скопировали, относительные, координаты в последующих ячейках сдвинулись. Т.е. для ячейки E3 формула выглядит следующим образом: =D3/D14 . Но, как мы видим, ячейка D14 – пустая, из-за чего программа и выдает ошибку, информирующую о том, что делить на цифру 0 нельзя.
Следовательно, мы должны написать формулу таким образом, чтобы координаты ячейки с итоговой суммой (D13) оставались неизменными при копировании. В этом нам помогут абсолютные ссылки.
Абсолютные ссылки
Как мы уже выяснили ранее, абсолютные ссылки позволяют зафиксировать координаты ячейки. Давайте посмотрим, как это работает на нашем примере.
По умолчанию, все ссылки в формулах Эксель относительные, поэтому, чтобы сделать их абсолютными, выполняем следующие действия:
- Для начала пишем формулу в привычном виде в требуемой ячейке. В нашем случае она выглядит так: = D2/D13 .
- Когда формула готова, не спешим нажимать клавишу Enter. Теперь нам нужно зафиксировать координаты ячейки D13. Для этого перед названием столбца и порядковым номером строки печатаем символ “$”. Или же можно просто после ввода адреса сразу нажать клавишу F4 на клавиатуре (курсор может находиться до, после или внутри координат). В итоге формула должна выглядеть следующим образом: D2/$D$13 .
- Теперь можно нажать Enter, чтобы вывести результат в ячейку.
- Остается только скопировать формулу с помощью маркера заполнения на нижние строки. На этот раз, благодаря тому, что мы зафиксировали ячейку с итоговой суммой, результат появится и в других ячейках.
Смешанные ссылки
Помимо ссылок, рассмотренных выше, в Excel также предусмотрены смешанные ссылки – когда при копировании формулы меняется одна из координат ячейки (столбец или номер строки).
- Если мы напишем ссылку как “$G5”, это означает, что будет меняться строка, а столбец будет зафиксирован.
- Если мы укажем “G$5”, в этом случае, фиксироваться будет номер строки, в то время, как столбец будет меняться.
Т.е. по сути, решить задачу выше с определением доли каждого наименования в общих продажах можно будет путем фиксации лишь номера строки, так как столбец в любом случае, даже при относительной ссылке, не менялся.
Примечание: вместо ручного ввода символов “$” можно задать тип ссылок (абсолютные, относительные, смешанные) с помощью функциональной клавиши F4. При это курсор должен находится в пределах координат ячейки, в отношении которой мы хотим выполнить данное действие.
Заключение
Благодаря относительным, абсолютным и смешанным ссылкам в Эксель выполняется огромное количество различных расчетов. Поэтому для успешной работы в программе, следует тщательно разобраться в них, чтобы максимально эффективно и рационально выполнить очередную задачу.
В большинстве случаев циклические ссылки воспринимаются пользователями как ошибочные выражения. Это обуславливается тем, что сама программа перегружается от их наличия, предупреждая об этом специальным оповещением. Чтобы снять ненужную нагрузку с программных процессов и устранить конфликтные ситуации между ячейками, необходимо найти проблемные места и удалить их.
Содержание
- Что такое циклическая ссылка
- Визуальный поиск
- Использование инструментов программы
- Отключение блокировки и создание циклических ссылок
- Заключение
Что такое циклическая ссылка
Циклическая ссылка представляет собой выражение, которое через формулы, расположенные в других ячейках, ссылается на самое начало выражения. При этом в данной цепочке может быть огромное количество ссылок, из которых образуется замкнутый круг. Чаще всего это ошибочное выражение, которое перегружает систему, не дает программе работать корректно. Однако в некоторых ситуациях циклические ссылки пользователи добавляют осознанно, чтобы выполнить определенные расчетные операции.
Если циклическая ссылка – ошибка, которую пользователь допустил случайно при заполнении таблицы, введении определенных функций, формул, необходимо найти ее и удалить. На этот случай существует несколько эффективных способов. Стоит подробно рассмотреть 2 наиболее простых и проверенных на практике.
Важно! Раздумывать о том есть ли в таблице циклические ссылки или нет не нужно. Если подобные конфликтные ситуации присутствуют, современные версии Excel сразу уведомляют пользователя об этом предупреждающим окном с соответствующей информацией.
Визуальный поиск
Самый простой метод поиска, который подойдет при проверке небольших таблиц. Порядок действий:
- Когда появится предупреждающее окно, необходимо закрыть его нажатием кнопки «ОК».
- Программа автоматически обозначит те ячейки, между которыми возникла конфликтная ситуация. Они будет подсвечены специальной стрелкой трассировки.
- Чтобы убрать цикличность, необходимо зайти в обозначенную ячейку и исправить формулу. Для этого необходимо убрать координаты конфликтной клетки из общей формулы.
- Останется перевести курсор мыши на любую свободную ячейку таблицы, нажать ЛКМ. Циклическая ссылка будет удалена.
Использование инструментов программы
В тех случаях, когда стрелки трассировки не указывают на проблемные места в таблице, необходимо воспользоваться встроенными инструментами Excel для поиска и удаления циклических ссылок. Порядок действий:
- В первую очередь нужно закрыть окно с предупреждением.
- Перейти на вкладку «Формулы» на основной панели инструментов.
- Зайти в раздел «Зависимости формул».
- Найти кнопку «Проверка ошибок». Если окно программы находится в сжатом формате, данная кнопка будет обозначена восклицательным знаком. Рядом с ней должен находиться маленький треугольник, который направлен вниз. Нужно нажать на него, чтобы появился список команд.
- Из списка выбрать «Циклические ссылки».
- Выполнив все описанные выше действия, перед пользователем появится полный список с ячейками, которые содержат циклические ссылки. Для того чтобы понять, где точно находится данная клетка, нужно найти ее в списке, кликнуть по ней левой кнопкой мыши. Программа автоматически перенаправит пользователя в то место, где возник конфликт.
- Далее необходимо исправить ошибку для каждой проблемной ячейки, как описывалось в первом способе. Когда конфликтные координаты будут удалены из всех формул, которые есть в списке ошибок, необходимо выполнить заключительную проверку. Для этого возле кнопки «Проверка ошибок» нужно открыть список команд. Если пункт «Циклические ссылки» не будет показан как активный – ошибок нет.
Отключение блокировки и создание циклических ссылок
Разобравшись с тем, как находить и исправлять циклические ссылки в таблицах Excel, необходимо рассмотреть ситуации, когда данные выражения можно использовать с пользой. Однако перед этим нужно узнать, как отключить автоматическую блокировку подобных ссылок.
Чаще всего циклические ссылки осознанно применяются во время построения экономических моделей, для выполнения итеративных вычислений. Однако, даже если подобное выражение используется осознанно, программа все равно будет блокировать его в автоматическом режиме. Для запуска выражения в работу, необходимо отключить блокировку. Для этого нужно выполнить несколько действий:
- Зайти во вкладку «Файл» на главной панели.
- Выбрать пункт «Параметры».
- Перед пользователем должно появиться окно настройки Excel. Из меню в левой части выбрать вкладку «Формулы».
- Перейти к разделу «Параметры вычислений». Установить галочку напротив функции «Включить итеративные вычисления». Дополнительно к этому в свободных полях чуть ниже можно установить максимальное количество подобных вычислений, допустимую погрешность.
Важно! Без особой необходимости лучше не изменять максимальное количество итеративных вычислений. Если их будет слишком много, программа будет перегружена, могут появиться сбои с ее работой.
- Чтобы изменения вступили в силу, необходимо нажат на кнопку «ОК». После этого программа перестанет автоматически блокировать вычисления в ячейках, которые связаны циклическими ссылками.
Самый простой вариант создания циклической ссылки – выделить любую клетку таблицы, в нее вписать знак «=», сразу после которого добавить координаты этой же ячейки. Чтобы усложнить задачу, расширить циклическую ссылку на несколько ячеек, нужно выполнить следующий порядок действий:
- В клетку А1 добавить цифру «2».
- В ячейку В1 вписать значение «=С1».
- В клетку С1 добавить формулу «=А1».
- Останется вернуться в самую первую ячейку, через нее сослаться на клетку В1. После этого цепь из 3 ячеек замкнется.
Заключение
Найти циклические ссылки в таблице Excel достаточно просто. Значительно упрощает данную задачу автоматическое оповещение самой программы о наличии конфликтующих выражений. После этого останется всего лишь воспользоваться одним из двух описанных выше способов, чтобы избавиться от ошибок.
Оцените качество статьи. Нам важно ваше мнение:
Циклические ссылки в excel
25.10.2013 Григорий Цапко Полезные советы
Среди пользователей Excel широко распространено мнение, что циклическая ссылка в excel является разновидностью ошибки, и от нее нужно непременно избавляться.
Между тем, именно циклические ссылки в excel способны облегчить нам решение некоторых практических экономических задач и финансовом моделировании.
Эта заметка как раз и будет призвана дать ответ на вопрос: а всегда ли циклические ссылки – это плохо? И как с ними правильно работать, чтобы максимально использовать их вычислительный потенциал.
Для начала разберемся, что такое циклические ссылки в excel 2010.
Циклические ссылки возникают, когда формула, в какой либо ячейке через посредство других ячеек ссылается сама на себя.
Например, ячейка С4 = Е 7, Е7 = С11, С11 = С4. В итоге, С4 ссылается на С4.
Наглядно это выглядит так:
На практике, зачастую, не такая примитивная связь, а более сложная, когда результаты вычисления одной формулы, порою весьма замысловатые, влияют на результат вычисления другой формулы, которая в свою очередь влияет на результаты первой. Возникает циклическая ссылка.
Предупреждение о циклической ссылке
Появление циклических ссылок очень легко определить. При их возникновении или наличии в уже созданной книге excel сразу же появляется предупреждение о циклической ссылке, которое по большому счету и описывает суть явления.
При нажатии на кнопку ОК, сообщение будет закрыто, а в ячейке содержащей циклическую ссылку в большинстве случаев появиться 0.
Предупреждение, как правило, появляется при первоначальном создании циклической ссылки, или открытии книги содержащей циклические ссылки. Если предупреждение принято, то при дальнейшем возникновении циклических ссылок оно может не появляться.
Как найти циклическую ссылку
Циклические ссылки в excel могут создаваться преднамеренно, для решения тех или иных задач финансового моделирования, а могут возникать случайно, в виде технических ошибок и ошибок в логике построения модели.
В первом случае мы знаем об их наличии, так как сами их предварительно создали, и знаем, зачем они нам нужны.
Во втором случае, мы можем вообще не знать где они находятся, например, при открытии чужого файла и появлении сообщения о наличии циклических ссылок.
Найти циклическую ссылку можно несколькими способами. Например, чисто визуально формулы и ячейки участвующие в образовании циклических ссылок в excel отмечаются синими стрелками, как показано на первом рисунке.
Если циклическая ссылка одна на листе, то в строке состояния будет выведено сообщение о наличии циклических ссылок с адресом ячейки.
Если циклические ссылки есть еще на других листах кроме активного, то будет выведено сообщение без указания ячейки.
Если или на активном листе их более одной, то будет выведено сообщение с указанием ячейки, где циклическая ссылка появляется в первый раз, после ее удаления – ячейка, содержащая следующую циклическую ссылку и т.д.
Найти циклическую ссылку можно также при помощи инструмента поиска ошибок.
На вкладке Формулы в группе Зависимости формул выберите элемент Поиск ошибок и в раскрывающемся списке пункт Циклические ссылки.
Вы увидите адрес ячейки с первой встречающейся циклической ссылкой. После ее корректировки или удаления – со второй и т.д.
Теперь, после того как мы выяснили как найти и убрать циклическую ссылку, рассмотрим ситуации, когда делать этого не нужно. То есть когда циклическая ссылка в excel приносит нам определенную пользу.
Использование циклических ссылок
Рассмотрим пример использования циклических ссылок в финансовом моделировании. Он поможет нам понять общий механизм итеративных вычислений и дать толчок для дальнейшего творчества.
В финансовом моделировании не часто, но все же возникает ситуация, когда нам необходимо рассчитать уровень затрат по статье бюджета, зависящей от финансовый результата, на который эта статья влияет. Это может быть, например статья расходов на дополнительное премирование персонала отдела продаж, зависящая от прибыли от продаж.
После введения всех формул, у нас появляется циклическая ссылка:
Однако, ситуация не безнадежная. Нам достаточно изменить некоторые параметры Excel и расчет будет осуществлен корректно.
Еще одна ситуация когда могут быть востребованы циклические ссылки – это метод взаимных или обратных распределений косвенных затрат между непроизводственными подразделениями.
Подобные распределения делаются при помощи системы линейных уравнений, и могут быть реализованы в Excel с применением циклических ссылок.
На тему методов распределения затрат в ближайшее время появиться отдельная статья.
Пока же нас интересует сама возможность таких вычислений.
Итеративные вычисления
Для того чтобы корректный расчет был возможен, мы должны включить итеративные вычисления в параметрах Excel.
Итеративные вычисления – это вычисления повторяемые множество раз, пока не будет достигнут результат соответствующий заданным условиям (условию точности или условию количества осуществленных итераций).
Включить итеративные вычисления можно через вкладку Файл → раздел Параметры → пункт Формулы. Устанавливаем флажок «Включить итеративные вычисления».
Как правило, установленных по умолчанию предельного числа итераций и относительной погрешности достаточно для наших вычислительных целей.
Следует иметь ввиду, что слишком большое количество вычислений может существенно загружать систему и снижать производительность.
Также, говоря об итеративных вычислениях, следует отметить, что возможны три варианта развития событий.
Решение сходится, что означает получение надежного конечного результата.
Решение расходится, т. е. при каждой последующей итерации разность между текущим и предыдущим результатами увеличивается.
Решение колеблется между двумя значениями, например, после первой итерации получается значение 1, после второй — значение 10, после третьей — снова 1 и т. д.
Как удалить или разрешить циклическую ссылку
Вы ввели формулу, но она не работает. Вместо этого вы получаете это сообщение об ошибке «Циклическая ссылка». Миллионы людей имеют такую же проблему, и это происходит из-за того, что формула пытается подсчитаться самой себе, и у вас есть функция, которая называется итеративным вычислением. Вот как это выглядит:
Формула =D1+D2+D3 не работает, поскольку она расположена в ячейке D3 и ссылается на саму себя. Чтобы устранить эту проблему, можно переместить формулу в другую ячейку. Нажмите клавиши CTRL + X , чтобы вырезать формулу, выберите другую ячейку и нажмите клавиши CTRL + V , чтобы вставить ее.
Другая распространенная ошибка связана с использованием функций, которые включают ссылки на самих себя, например ячейка F3 может содержать формулу =СУММ(A3:F3). Пример:
Вы также можете попробовать один из описанных ниже способов.
Если вы только что ввели формулу, начните с этой ячейки и проверьте, не ссылается ли вы на саму ячейку. Например, ячейка A3 может содержать формулу =(A1+A2)/A3. Формулы, например = a1 + 1 (в ячейке a1), также вызывают ошибки циклических ссылок.
Проверьте наличие непрямых ссылок. Они возникают, когда формула, расположенная в ячейке А1, использует другую формулу в ячейке B1, которая снова ссылается на ячейку А1. Если это сбивает с толку вас, представьте, что происходит с Excel.
Если найти ошибку не удается, на вкладке Формулы щелкните стрелку рядом с кнопкой Проверка ошибок, выберите пункт Циклические ссылки и щелкните первую ячейку в подменю.
Проверьте формулу в ячейке. Если вам не удается определить, является ли эта ячейка причиной циклической ссылки, выберите в подменю Циклические ссылки следующую ячейку.
Продолжайте находить и исправлять циклические ссылки в книге, повторяя действия 1–3, пока из строки состояния не исчезнет сообщение «Циклические ссылки».
В строке состояния в левом нижнем углу отображается сообщение Циклические ссылки и адрес ячейки с одной из них.
При наличии циклических ссылок на других листах, кроме активного, в строке состояния выводится сообщение «Циклические ссылки» без адресов ячеек.
Вы можете перемещаться между ячейками в циклической ссылке, дважды щелкая стрелку трассировки. Стрелка указывает ячейку, которая влияет на значение выбранной в данный момент ячейки. Чтобы отобразить стрелку трассировки, выберите пункт формулы, а затем — влияющие ячейки или зависимыеячейки.
Предупреждение о циклической ссылке
Когда Excel впервые находит циклическую ссылку, отображается предупреждающее сообщение. Нажмите кнопку ОК или закройте окно сообщения.
Когда вы закроете сообщение, в ячейке будет отображено либо нулевое, либо последнее вычисленное значение. И теперь, наверное, говорят: «повесить последнее вычисленное значение?» Да. В некоторых случаях можно успешно выполнить формулу, прежде чем выполнять вычисление. Например, формула, использующая функцию если , может работать, пока пользователь не введет аргумент (часть данных, которая должна выполняться правильно), которая приводит к тому, что формула будет вычислять саму себя. В этом случае Excel сохраняет значение из последнего успешного вычисления.
Если есть подозрение, что циклическая ссылка содержится в ячейке, которая не возвращает значение 0, попробуйте такое решение:
Щелкните формулу в строке формулы и нажмите клавишу ВВОД.
Внимание! Во многих случаях при создании дополнительных формул с циклическими ссылками предупреждающее сообщение в приложении Excel больше не отображается. Ниже перечислены некоторые, но не все, ситуации, в которых предупреждение появится.
Пользователь создает первый экземпляр циклической ссылки в любой открытой книге.
Пользователь удаляет все циклические ссылки во всех открытых книгах, после чего создает новую циклическую ссылку.
Пользователь закрывает все книги, создает новую и вводит в нее формулу с циклической ссылкой.
Пользователь открывает книгу, содержащую циклическую ссылку.
При отсутствии других открытых книг пользователь открывает книгу и создает в ней циклическую ссылку.
Итеративные вычисления
Иногда вам может потребоваться использовать циклические ссылки, так как они приводят к итерации функций — повторяются до тех пор, пока не будет выполнено определенное числовое условие. Это может замедлить работу компьютера, поэтому итеративные вычисления обычно отключены в Excel.
Если вы не знакомы с итеративными вычислениями, вероятно, вы не захотите оставлять активных циклических ссылок. Если же они вам нужны, необходимо решить, сколько раз может повторяться вычисление формулы. Если включить итеративные вычисления, не изменив предельное число итераций и относительную погрешность, приложение Excel прекратит вычисление после 100 итераций либо после того, как изменение всех значений в циклической ссылке с каждой итерацией составит меньше 0,001 (в зависимости от того, какое из этих условий будет выполнено раньше). Тем не менее, вы можете сами задать предельное число итераций и относительную погрешность.
Если вы работаете в Excel 2010 или более поздней версии, последовательно выберите элементы Файл > Параметры > Формулы. Если вы работаете в Excel для Mac, откройте меню Excel, выберите пункт Настройки и щелкните элемент Вычисление.
Если вы используете Excel 2007, нажмите кнопку Microsoft Office 
, щелкните Параметры Excelи выберите категорию формулы .
В разделе Параметры вычислений установите флажок Включить итеративные вычисления. На компьютере Mac щелкните Использовать итеративное вычисление.
В поле Предельное число итераций введите количество итераций для выполнения при обработке формул. Чем больше предельное число итераций, тем больше времени потребуется для пересчета листа.
В поле Относительная погрешность введите наименьшее значение, до достижения которого следует продолжать итерации. Это наименьшее приращение в любом вычисляемом значении. Чем меньше число, тем точнее результат и тем больше времени потребуется Excel для вычислений.
Итеративное вычисление может иметь три исход:
Решение сходится, что означает получение надежного конечного результата. Это самый желательный исход.
Решение расходится, т. е. при каждой последующей итерации разность между текущим и предыдущим результатами увеличивается.
Решение переключается между двумя значениями. Например, после первой итерации результат равен 1, после следующей итерации результат — 10, после следующей итерации результат равен 1 и т. д.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.
Дополнительные сведения
Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).
Циклические ссылки в Excel: поиск и исправление
Прежде, чем мы приступим к поиску циклических ссылок в таблицах Эксель, давайте разберемся, что это вообще такое.
Циклические ссылки встречаются в формулах, в которых ячейка ссылается на другие ячейки, которые в свою очередь могут ссылаются на другие и т.д., и в итоге, получается так, что начальная ячейка (в которой формула) через цепочку ссылок на другие ячейки, ссылается на саму себя.
Иногда пользователи целенаправленно используют подобную схему для выполнения определенных вычислений. Однако, чаще всего, подобные ситуации – результат ошибки в формуле, вероятнее всего, сделанной пользователей случайно или по незнанию специфики работы в программе.
Потому очень важно уметь находить и устранять циклические ссылки, особенно, в документе, полученном от другого пользователя, который могу допустить такие ошибки.
Нахождение циклических ссылок
Когда в документе есть циклическая ссылка, при его открытии Excel проинформирует нас об этом в соответствующем окошке.
Следовательно, ломать голову над тем, если ли в книге циклическая ссылка (ссылки) или нет, не нужно, так как это понятно в момент его открытия. Остается только определить, где именно она находится.
Метод 1. Визуальный поиск циклической ссылки
Данный способ самый простой, однако, удобен лишь при работе с небольшими таблицами.
- Для начала нам нужно закрыть всплывшее диалоговое окно, предупреждающее нас о том, что в книге присутствует циклическая ссылка.
- После этого в таблице отобразится так называемая стрелка трассировки, наглядно демонстрирующая связь между ячейками, которая вызывает ошибку.
- Переходим в ту ячейку, куда указывает стрелка и исправляем формулу, чтобы избавиться от цикличности. В нашем случае лишним элементом является ячейка F13, поэтому убираем ее и формулы.
- Все готово. Теперь в нашей таблице нет циклических ссылок.
Метод 2. Использование инструментов на Ленте
Иногда бывает так, что стрелки, помогающие определить циклическую связь между ячейкам не отображаются в книге. В этом случае поступаем следующим образом:
- Начнем с того, что закроем информационное окно о наличии циклической ссылки.
- Теперь переключаемся во вкладку “Формулы”. Обращаем внимание на раздел “Зависимости формул”. Здесь нас интересует кнопка “Проверка ошибок” (в некоторых случаях, когда размеры окна сжаты по горизонтали, отображается только значок кнопки в виде восклицательного знака). Щелкаем по небольшому треугольнику, направленному вниз, справа от кнопки. Откроется перечень команд, среди которых выбираем пункт “Циклические ссылки”, после чего откроется список всех ячеек, содержащих эти самые ссылки.
- Если мы щелкнем на адрес ячейки, программа сразу же выделит ее, независимо от того, в какой ячейке мы находились до того, как решили воспользоваться данной функцией.
- Нам остается только разобраться с формулой и исправить допущенные в ней ошибки. В нашем случае в диапазон суммируемых ячеек была включена и ячейка, куда записана сама формула, что конечно же, неверно.
- Корректируем координаты диапазона в формуле, чтобы избавиться от цикличности.
- Чтобы удостовериться в том, что теперь все в порядке, снова раскрываем перечень команд рядом с кнопкой “Проверка ошибок”. На этот раз пункт “Циклические ссылки” неактивен, что свидетельствует о том, что ошибки устранены.
Заключение
Итак, как вы могли убедиться, в нахождении и удалении циклических ссылок в Excel нет никаких сложностей. При работе с небольшими таблицами достаточно визуального осмотра на наличие стрелок трассировки, указывающих на ссылки, которые мы ищем. Если документ достаточно объемный, либо не хочется терять время на поиск “проблемных” мест таблицы вручную, нас выручит инструмент под названием “Поиск ошибок”.
Excel. Использование циклических ссылок для решения уравнений итерационным способом
Ранее я описал, как найти и исправить циклическую ссылку. Напомню, что циклическая ссылка появляется, если в ячейку Excel введена формула, содержащая ссылку на саму эту ячейку (напрямую или через цепочку других ссылок). Например (рис. 1), в ячейке С2 находится формула, ссылающаяся на саму ячейку С2.
Рис. 1. Пример циклической ссылки
Но. Не всегда циклическая ссылка является бедствием. Циклическую ссылку можно использовать для решения уравнений итерационным способом. Для начала нужно позволить Excel вести вычисления, даже при наличии циклической ссылки. В обычном режиме Excel, обнаружив циклическую ссылку, выдаст сообщение об ошибке, и потребует ее устранения. В обычном режиме Excel не может провести вычисления, так как циклическая ссылка порождает бесконечный цикл вычислений. Можно, либо устранить циклическую ссылку, либо допустить вычисления по формуле с циклической ссылкой, но ограничив число повторений цикла. Для реализации второй возможности щелкните на кнопке «Office» (в левом верхнем углу), а затем на «Параметры Excel» (рис. 2).
Скачать заметку в формате Word, примеры в формате Excel
Рис. 2. Параметры Excel
В открывшемся окне «Параметры Excel» перейдите на вкладку Формулы и отметьте «Включить итеративные вычисления» (рис. 3). Помните, что эта опция включается для приложения Excel в целом (а не для одного файла), и будет действовать, пока вы ее не отключите.
Рис. 3. Включить итеративные вычисления
На этой же вкладе, можно выбрать, как будут вестись вычисления: автоматически или вручную. При автоматическом вычислении Excel сразу рассчитает конечный результат, при вычислениях, вручную, можно будет наблюдать результат каждой итерации (простым нажатием F9 запуская каждый новый цикл вычисления).
Решим уравнение третьей степени: х 3 – 4х 2 – 4х + 5 = 0 (рис. 4). Для решения этого уравнения (и любого другого уравнения совершенно произвольного вида) понадобится всего одна ячейка Excel.
Рис. 4. График функции f(x)
Для решения уравнения нам понадобится рекуррентная формула (то есть, формула, выражающая каждый член последовательности через один или несколько предыдущих членов):
(1) x = x – f(x)/f’(x), где
f(x) – функция, задающая уравнение, корни которого мы ищем; f(x) = х 3 – 4х 2 – 4х + 5
f’(x) – производная нашей функции f(x); f’(x) = 3х 2 – 8х – 4; производные основных элементарных функций можно посмотреть здесь.
Если вы заинтересовались, откуда взялась формула (1), можете почитать, например, здесь.
Итоговая рекуррентная формула имеет вид:
(2) х = x – (х 3 – 4х 2 – 4х + 5)/(3х 2 – 8х – 4)
Выберем любую ячейку на листе Excel (рис. 5; в нашем примере это ячейка G19), присвоим ей имя х, и введем в нее формулу:
Можно вместо х использовать адрес ячейки… но согласитесь, что имя х, смотрится привлекательнее; следующую формулу я ввел в ячейку G20:
Рис. 5. Рекуррентная формула: (а) для поименованной ячейки; (б) для обычного адреса ячейки
Как только мы введем формулу и нажмем Enter, в ячейке сразу же появится ответ – значение 0,77. Это значение соответствует одному из корней уравнения, а именно второму (см. график функции f(x) на рис. 4). Поскольку начальное приближение не задавалось, итерационный вычислительный процесс начинался со значения, по умолчанию хранимого в ячейке х и равного нулю. Как же получить остальные корни уравнения?
Для изменения стартового значения, с которого рекуррентная формула начинает свои итерации, предлагается использовать функцию ЕСЛИ: [1]
Здесь значение «-5» – начальное значение для рекуррентной формулы. Изменяя его, можно выйти на все корни уравнения:
Циклическая ссылка в Excel
Формула в ячейке, которая прямо или косвенно ссылается на эту же самую ячейку, называется циклической ссылкой. Такого быть не может!
-
Например, формула в ячейке A3 на рисунке ниже ссылается на ячейку A3 (т.е. на саму себя), чего быть не может.
Примечание: Если вы создадите такую циклическую ссылку, Excel вернёт “0”.
-
Ещё один пример. Формула в ячейке С2 на следующем рисунке ссылается на ячейку C1.
Формула в ячейке C3 ссылается на ячейку С2.
Формула в ячейке C4 ссылается на ячейку С3.
Пока все нормально. Теперь измените значение в ячейке C1 на следующую формулу:
Пояснение:
- Ячейка С1 ссылается на ячейку C4.
- С4 ссылается на ячейку C3.
- С3 ссылается на ячейку C2.
- С2 ссылается на ячейку C1.
- Другими словами, формула в ячейке C1 косвенно ссылается на саму себя, чего быть не должно.
Примечание: Если вы создадите такую циклическую ссылку, Excel вернёт “0”.
- Чтобы найти циклические ссылки в документе, на вкладке Formulas (Формулы) кликните по стрелке вниз рядом с иконкой Error Checking (Проверка наличия ошибок) и нажмите Circular References (Циклические ссылки).
