Как сделать циклическую формулу в excel?

Как удалить или разрешить циклическую ссылку

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

Формула =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.

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

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Excelling at Excel вып.2: Циклы в Excel без VBA

Немного теории. Циклом называется конструкция, которая некоторое (определяемое) количество раз выполняет заданные действия. Например, Вам нужно перебрать некий массив данных и выделить в нем пустые поля. В программировании это реализуется при помощи циклов. В VBA наиболее частым вариантом является конструкция For i = 0 to n … Next i.

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

Читать еще:  Как сделать из 1с выгрузку в excel?

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

Задача: свести это в одну таблицу для последующей обработки через ту же сводную таблицу. То есть требовалось получить вот такое представление:

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

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

В нашем примере получалось три цикла (в порядке от младшего к старшему): тип исполнителя (цикл 1), статья затрат (цикл 2), проект (цикл 3). Алгоритм выглядит примерно так:

Цикл 1 (тип исполнителя)

Так бы примерно выглядела бы и структура кода VBA для реализации этих трех циклов, но в самом Excel так сделать нельзя. Что же делать?

Давайте еще раз обратимся к сути цикла: это повторение какого либо действия определенное количество раз. Теперь рассмотрим это на примере одного цикла – цикла 1 (тип исполнителя).

Допустим, у нас 4 возможных типа исполнителя. Они у нас на отдельном листе «Тип исполнителя». Соответственно, нам надо перебрать все эти четыре значения по одному. Как? Во-первых, мы должны определить, что их именно 4. Для этого воспользуемся функцией COUNTA (СЧЁТА).

ВАЖНО! Не забудем вычесть заголовок.

Во-вторых, нам надо оформить перебор значений от 1 до 4. Вернее, до значения полученного из COUNTA (СЧЁТА). Это именно столько «шагов» должен сделать наш цикл.

Увы, без вспомогательных столбцов здесь не обойтись. Добавляем их слева от результирующей таблицы и в первой строке в ячейке А2 смело ставим 1. В ячейке А3 и ниже мы пропишем следующую формулу:

Получаем бесконечное повторение от 1 до 4. Теперь нам остается получить значение на каждому «шагу» цикла. Это можно сделать при помощи функции OFFSET (СМЕЩ), в которой значения столбца А мы будем использовать в качестве второго параметра (смещение по строкам).

Теперь добавим второй цикл – цикл 2 (статья). Подход такой же за исключением одного «НО»: переключать значение мы будем не сразу после предыдущего как в цикле 1, а по достижении максимального значения в цикле 1 (тип исполнителя). Для этого нам нужно формула, описывающая такую логику:

«Если значение типа исполнителя равно количество типов, то

если предыдущее значение статьи равно количеству статей, то 1,

если не равно, то предыдущее значение + 1,

если не равно, то предыдущее значение».

Вот так это выглядит в экселе:

Цикл 3 (проект) оформляется схожим образом с циклом 2 (статья). Но «триггером» для переключения на новое значение будет уже два условия одновременно: максимальное значение количества статей и максимальное количество типов исполнителей. В формуле выполнение этих двух условий мы оформим через функцию AND (И) равную TRUE (ИСТИНА).

Осталось только добавить формулы СМЕЩ в ячейки с данными.

Необходимо не забыть «остановить» цикл. В противном случае вы получите то, что ниже:

Чтобы этого избежать в формуле в столбце А мы специально вставили в одном из возможных исходов значение «» (пусто), чтобы этим самым «остановить» бесконечный цикл. Теперь при протягивании формулы будут выводиться пустые ячейки. В формул остальных ячеек (в т.ч. со СМЕЩ) следует добавить:

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

Я когда ваял в экселе производство растворителей, такие советы были для меня бесценными.

Но я их находил, в основном, на пленетеэксель (не сочтите за рекламу).

А на пикабу это как то специфично выглядит, тем более второй пост подряд.

Плюсану, конечно, но жду таки котиков и сисек, ну или байку какую нибудь )))

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

И увы, никаких котиков и сисек тут. Только эксель, только хардкор:)

Думаю, тебе поможет фильтр

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

А суммирование рабочих дней и часов делается через СЧЁТЕСЛИ и СУММЕСЛИ, соответственно:

COUNTIF(N25:AC25;»Я») — на массив где Я и В

SUMIF(N25:AC25;»Я»;N26:AC26) — массив где Я и В и массив с часами

Суммесли не подходит?

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

Раб.дни в помощь. Там есть параметр Праздники. Делаете ссылку на массив с праздниками, приходящимися на будни, и будет Вам счастие.

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

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

Интересная статья, но без острой потребности вникать лень.

Внезапно для решения рабочих вопросов потребовалось резко изучить VBA. Можешь подсказать книги/курсы/видео для поверхностного изучения синтаксиса и общей логики языка?

изучал сам, без учебников. просто решал конкретную задачу. плюс был опыт программирования на php и javascript. поэтому логика там схожая.

В интернете полно ресурсов, где можно почитать.

а так синтаксис довольно-таки простой. Через точку пишете адрес объекта, к которому обращаетесь или к свойству этого объекта. Например, ячейки: Лист.Адрес.Свойство:

Каждая манипуляция записывается на отдельной строке

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

Из конструкций обязательно надо изучить IF . Then и циклы.

Пишем прототип бизнес-игры (экономическая стратегия для 24 пользователей по сети)

. на VBA?? сетевая? браузерная? тогда уже сразу учите PHP. По-хорошему, туда же еще mySQL для работы с БД и javascript для красивой реализации на стороне пользователя. Ну и конечно html с CSS для верстки страниц. сам так запилил своему прошлому работодателю мини-ERP

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

Читать еще:  Как из excel сделать vcf?

офигеть. несколько раз прочитал пост. это же надо было так заморочиться и сделать игру на самом для этого не подходящем движке. создателю респект. главное в программирование, на мой взгляд, — это правильное построение алгоритмов. Если они выстроены правильно, то работать будет на чем угодно. Делал в свое время таск-менеджер и на удаленном сервере на php и mysql. Потом легко перенес это на VBA, т.к. логика и взаимосвязь таблиц данных одна и та же.

В любом случае успехов! Но мой совет — не занимайтесь больше такими извращениями!

И растягиваем вниз

Во-первых, @ArtemTabolin , спасибо — делаешь хорошее дело!

Во-вторых — сначала я зашел в этот пост, а потом уже заглянул в первый, т.к. первый пропустил, т.к. подумал, что это очередной рекламный пост — меняй название))

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

А где пример файла что бы вы живую поглядеть формулы?

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

А не легче это все будет делать функциями SUMIFS , COUNTIFS и подобными (сорри, не в курсе как они на русском). Или версия екселя только старая доступна?

Уточните, пожалуйста, как Вы хотите использовать COUNTIFS (СЧЕТЕСЛИМН)?

COUNTIFS отлично подходит для проверки правильности подсчета. Всегда ставлю во вторую (можно скрытую колонку).

Если честно, то меня эксел бесит 🙂

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

Я прям благоговею перед их создателями, считаю их просто монстрами. И одновременно мне их жалко, жалко их потраченный впустую труд. Что люди не делают, лишь бы не изучать VBA и SQL 🙂

И я показываю, им как все эти ужасные формулы на несколько строк переписываются маленьким скриптиком в VBA редакторе.

А если еще на компьютере есть MS Access то, тут вообще возможна истинная магия.

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

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

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

Проведите эксперимент: объясните человеку формулу СУММЕСЛИ и код VBA с суммированием по условию через цикл. Для меня лично результат очевиден

Я могу использовать СУММЕСЛИ и в VBA, если знаю о её существовании.

В данном случае в VBA можно использовать функцию SumIf и объяснять человеку придется ровно тоже самое.

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

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

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

Уже тройное вложенное ЕСЛИ вызывает стойкое желание разбить монитор высчитывая эти скобочки и точку с запятой..

А если нужно 10 вложений. ничего кроме желания грязно выругаться это не вызывает. В VBA коде, хоть 15 вложений сделайте простым ctrl+C , ctrl+V

и всё это будет легко читаемо и прозрачно.

Собственно, я ни на чём не настаиваю, это чисто моё мнение. Но когда меня просят, сделать что-нибудь этакое, я по возможности стараюсь это запихнуть в VBA. Подход в стиле : «Нажми на кнопку, получишь результат»

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

Циклические ссылки в 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 с применением циклических ссылок.

На тему методов распределения затрат в ближайшее время появиться отдельная статья.

Пока же нас интересует сама возможность таких вычислений.

Итеративные вычисления

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

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

Включить итеративные вычисления можно через вкладку Файл → раздел Параметры → пункт Формулы. Устанавливаем флажок «Включить итеративные вычисления».

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

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

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

Решение сходится, что означает получение надежного конечного результата.

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

Решение колеблется между двумя значениями, например, после первой итерации получается значение 1, после второй — значение 10, после третьей — снова 1 и т. д.

Циклические ссылки в Excel: поиск и исправление

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

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

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

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

Нахождение циклических ссылок

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

Следовательно, ломать голову над тем, если ли в книге циклическая ссылка (ссылки) или нет, не нужно, так как это понятно в момент его открытия. Остается только определить, где именно она находится.

Метод 1. Визуальный поиск циклической ссылки

Данный способ самый простой, однако, удобен лишь при работе с небольшими таблицами.

  1. Для начала нам нужно закрыть всплывшее диалоговое окно, предупреждающее нас о том, что в книге присутствует циклическая ссылка.
  2. После этого в таблице отобразится так называемая стрелка трассировки, наглядно демонстрирующая связь между ячейками, которая вызывает ошибку.
  3. Переходим в ту ячейку, куда указывает стрелка и исправляем формулу, чтобы избавиться от цикличности. В нашем случае лишним элементом является ячейка F13, поэтому убираем ее и формулы.
  4. Все готово. Теперь в нашей таблице нет циклических ссылок.

Метод 2. Использование инструментов на Ленте

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

  1. Начнем с того, что закроем информационное окно о наличии циклической ссылки.
  2. Теперь переключаемся во вкладку “Формулы”. Обращаем внимание на раздел “Зависимости формул”. Здесь нас интересует кнопка “Проверка ошибок” (в некоторых случаях, когда размеры окна сжаты по горизонтали, отображается только значок кнопки в виде восклицательного знака). Щелкаем по небольшому треугольнику, направленному вниз, справа от кнопки. Откроется перечень команд, среди которых выбираем пункт “Циклические ссылки”, после чего откроется список всех ячеек, содержащих эти самые ссылки.
  3. Если мы щелкнем на адрес ячейки, программа сразу же выделит ее, независимо от того, в какой ячейке мы находились до того, как решили воспользоваться данной функцией.
  4. Нам остается только разобраться с формулой и исправить допущенные в ней ошибки. В нашем случае в диапазон суммируемых ячеек была включена и ячейка, куда записана сама формула, что конечно же, неверно.
  5. Корректируем координаты диапазона в формуле, чтобы избавиться от цикличности.
  6. Чтобы удостовериться в том, что теперь все в порядке, снова раскрываем перечень команд рядом с кнопкой “Проверка ошибок”. На этот раз пункт “Циклические ссылки” неактивен, что свидетельствует о том, что ошибки устранены.

Заключение

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

Ссылка на основную публикацию
Adblock
detector