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

Пакет Microsoft Эксель позволяет проводить различные виды расчетов для статистики, экономики, финансового моделирования и других сфер. В некоторых случаях может потребоваться выполнение итераций для определения значения какой-либо величины. Принцип построения таких формул часто сводится к циклическим ссылкам.Если циклическая ссылка организована не корректно, то система будет выдавать ошибку. Визуальная проверкаПростым примером такой ситуации является следующий вариант:- ячейка C3 ссылается на B6- ячейка B6 ссылается на D6- ячейка D6 ссылается на C3Тут найти проблему просто. Кроме визуального осмотра если ссылка одна на лист, то в левом нижнем углу окна программы будет выведено сообщение с указанием адреса ячейки.Но в других расчетах может потребоваться гораздо больше усилий для поиска.

Выделение группы ячеек

Такой способ аудита в Excel, как выделения группы ячеек по заданным условиям, устанавливает ограничения для области поиска.Эта функция расположена на вкладке «Home» в группе «Найти и выделить» — «Выделение группы ячеек».Строки и столбцы с формулами, а так же сами ячейки будут подсвечены.

Отслеживание связей ячейки

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

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

— Обозначив активную ячейку, нажать сочетание клавиш Ctrl+[ — будут отмечены все задействованные ячейки- Аналогичный вариант — сочетание клавиш Ctrl+Shift+[ — в этом случае на активном листе будут отмечены и прямо, и косвенно влияющие ячейки- Выделение группы ячеек по формулам (как описано выше).

— Функция «Влияющие ячейки» на вкладке «Формула» показывает все задействованные в вычислениях ячейки стрелочками.

Проверка на ошибки

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

Фоновый поиск ошибок

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

Разрешение цикличности

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

Заключение

Источник: https://user-life.com/programs/excel/2872-kak-nayti-ciklicheskuyu-ssylku-v-excel.html

Циклическая ссылка в Excel — Microsoft Excel для начинающих

Перейти к контенту

Формула в ячейке, которая прямо или косвенно ссылается на эту же самую ячейку, называется циклической ссылкой. Такого быть не может!

1. Например, формула в ячейке A3 на рисунке ниже ссылается на ячейку A3 (т.е. на саму себя), чего быть не может.
   

Примечание: Если вы создадите такую циклическую ссылку, Excel вернёт “0”.

1. Ещё один пример. Формула в ячейке С2 на следующем рисунке ссылается на ячейку C1.
   
2. Формула в ячейке C3 ссылается на ячейку С2.
   
3. Формула в ячейке C4 ссылается на ячейку С3.
   
4. Пока все нормально. Теперь измените значение в ячейке C1 на следующую формулу:
   

   Пояснение:

   • Ячейка С1 ссылается на ячейку C4.
   • С4 ссылается на ячейку C3.
   • С3 ссылается на ячейку C2.
   • С2 ссылается на ячейку C1.
   • Другими словами, формула в ячейке C1 косвенно ссылается на саму себя, чего быть не должно.

Примечание: Если вы создадите такую циклическую ссылку, Excel вернёт “0”.

1. Чтобы найти циклические ссылки в документе, на вкладке Formulas (Формулы) кликните по стрелке вниз рядом с иконкой Error Checking (Проверка наличия ошибок) и нажмите Circular References (Циклические ссылки).

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

Источник: https://office-guru.ru/excel/ciklicheskaja-ssylka-v-excel-350.html

Как удалить или разрешить циклическую ссылку. Формулы для excel

ГлавнаяРазноеФормулы для excel

Примечание:  Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке.

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

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

При необходимости ссылаться на переменные вместо констант, можно использовать значения ячеек, например, = A1 + A2.

Если вы работаете с длинные столбцы данных или данных, который хранится в разных частях листа или на другом листе, вы можете использовать диапазон — к примеру, =SUM(A1:A100)/SUM(B1:B100), представляющий деления суммы первого сотен числа в столбце A на сумму эти числа в столбце B. Когда ссылается формула в другие ячейки, при каждом изменении данных во всех ячейках Excel пересчитывает результаты автоматически.

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

Операторы определяют тип вычисления, которое выполняет формулу. Например ^ («крышка») оператор возведения числа в степень и * (звездочка) оператор вычисляет произведение чисел. 

Ввод формулы, ссылающейся на значения в других ячейках

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

2. Введите знак равенства (=).

3. Щелкните первую ячейку, которую требуется включить в вычисление.

   

4. Введите оператор. Оператор представляет математическую операцию, выполняемую формулой. Например, оператор * (звездочка) перемножает числа. В этом примере используйте оператор / (косая черта), чтобы разделить числа. На этом этапе формула должна выглядеть так:

   

5. Щелкните следующую ячейку, которую нужно включить в вычисление. Теперь формула должна выглядеть так:

   

6. Нажмите клавишу RETURN.

   В ячейке отобразится результат вычисления.

   

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

Ввод формулы, содержащей функцию

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

2. Введите знак равенства (=) и функцию, например =МИН. Функция МИН находит наименьшее число в диапазоне ячеек.

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

4. Нажмите клавишу RETURN.

   В этом примере функция МИН возвращает 11 — наименьшее число в ячейках от A1 до C4.

Советы

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

• Чтобы проверить формулу, нажмите . Если ошибок нет, в ячейке будет выведен результат формулы. Если же ошибки есть, появится значок . Наведите на него указатель, чтобы просмотреть описание проблемы, или щелкните стрелку вниз, чтобы получить дополнительную помощь в устранении неполадки.
• Чтобы вернуться к предыдущей формуле, нажмите .
• 1. Чтобы выбрать функцию, используйте список функций.
  2. При выборе функции открывается построитель формул с дополнительной информацией о функции.

См. также

• Создание простой формулы
• Добавление списка чисел в столбце
• Операторы вычислений и порядок операций
• Список всех функций (по категориям)
• Переключение между относительных и абсолютных ссылок

support.office.com

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

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

Формула =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 отобразит в ячейке либо нулевое значение, либо последнее вычисленное значение. В некоторых случаях формула может успешно работать до тех пор, пока она не попытается вычислить себя.

Например, формула, использующая функцию «ЕСЛИ» может работать до тех пор, пока пользователь не введет аргумент (данные, необходимые для нормальной работы формулы), который заставит формулу попытаться вычислить саму себя.

Если это произойдет, Excel оставит в ячейке последнее успешно вычисленное значение.

Если есть подозрение, что циклическая ссылка содержится в ячейке, которая не возвращает значение 0, попробуйте такое решение:

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

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

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

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

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

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

1. Если вы работаете в Excel 2010 или более поздней версии, последовательно выберите элементы Файл > Параметры > Формулы. Если вы работаете в Excel для Mac, откройте меню Excel, выберите пункт Настройки и щелкните элемент Вычисление.

   Если у вас Excel 2007, нажмите кнопку Microsoft Office  , щелкните Параметры Excel и выберите категорию Формулы.

2. В разделе Параметры вычислений установите флажок Включить итеративные вычисления. На компьютере Mac щелкните Использовать итеративное вычисление.

3. В поле Предельное число итераций введите количество итераций для выполнения при обработке формул. Чем больше предельное число итераций, тем больше времени потребуется для пересчета листа.

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

Итеративное вычисление может иметь три исход:

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

К началу страницы

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

1. Полные сведения о формулах в Excel
2. Рекомендации, позволяющие избежать появления неработающих формул
3. Поиск ошибок в формулах
4. Сочетания клавиш и горячие клавиши в Excel
5. Функции Excel (по алфавиту)
6. Функции Excel (по категориям)

support.office.com

Создание формулы с помощью функции

Примечание:  Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке.

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

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

1. Щелкните ячейку, в которую нужно ввести формулу.

2. Чтобы начать формулу с функции, щелкните в строке или начать вводить формулу в ячейку формулы.

3. Завершив ввод аргументов, нажмите клавишу ВВОД, чтобы увидеть результат формулы в ячейке.

Совет:  Если функции используются часто, вы можете вводить формулы прямо на листе. Введя знак равенства (=) и имя функции, вы увидите информацию о синтаксисе формулы и аргументах функции.

Примеры

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

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

Данные
5	4
2	6
3	8
7	1
Формула	Описание	Результат
'=СУММ(A:A)	Сумма всех чисел в столбце A	=СУММ(A:A)
'=СРЗНАЧ(A1:B4)	Среднее арифметическое всех значений в диапазоне A1:B4	=СРЗНАЧ(A1:B4)

support.office.com

Изменение пересчета, итерации или точности формулы

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

По умолчанию в вычислениях используются хранимые, а не отображаемые значения   

Отображаемые и печатаемые значения зависят от выбора формата и отображения хранимых значений. Например, ячейка, в которой отображается дата «22.06.2008», также содержит порядковый номер, который является хранимым значением даты в этой ячейке. Можно изменить отображение даты на другой формат (например, «22-июнь-2008»), но хранимое в ячейке значение не изменится.

С осторожностью изменяйте точность вычислений   

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

Например, если в двух ячейках содержится значение 10,005, а формат ячеек предполагает отображение значений в виде денежных единиц, в каждой из ячеек будет отображаться значение 10,01 ₽.

При сложении этих значений результат будет равен 20,01 ₽, поскольку Excel складывает хранимые значения 10,005 и 10,005, а не отображаемые значения.

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

1. На вкладке Файл нажмите кнопку Параметры и выберите категорию Дополнительно.

   В Excel 2007 нажмите Кнопку Microsoft Office , выберите пункт Параметры Excel и выберите категорию Дополнительно

2. В разделе При пересчете этой книги выберите нужную книгу и установите флажок Задать указанную точность.

support.office.com

Источник: https://dilios.ru/raznoe/formuly-dlya-excel.html

Поиск и удалени циклических ссылок в Excel (Эксель)

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

Как найти циклическую ссылку в Excel?

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

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

• 
• Также сообщение о наличие циклических ссылок в документе находится на нижней панели.
• 

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

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

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

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

Не оставляйте таблицу в таком состоянии – сразу меняйте формулу и удаляйте ссылку.

Выводы

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

1. Еще много полезных статей о редакторе Excel:
2. Как импортировать данные из Excel в Word.
3. Excel Online — всё то же, только в браузере.
4. Функция ВПР в EXCEL: описание и синтаксис.
5. Создание календаря в Excel.

Источник: https://public-pc.com/kak-najti-i-ubrat-tsiklicheskie-ssylki-v-excel/

Циклические ссылки

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

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

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

• Чтобы выявить циклическую ссылку:
• Нажмите вкладку Формулы
• Нажмите стрелку кнопки Проверка наличия ошибок в группе Зависимости формул, выберите Циклические ссылки, а затем щелкните первую ячейку в списке подменю.
• Просмотрите формулу ячейки.
• Если вы не можете понять, что вызвало появление циклической ссылки, щелкните следующую ячейку в подменю.
• Продолжайте просматривать и исправлять циклические ссылки до тех пор, пока со строки состояния не исчезнет сообщение Циклические ссылки».

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

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

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

1. Чтобы ввести функцию:
2. Щелкните ячейку, куда вы хотите ввести функцию.
3. Введите знак равенства (=), введите имя функции, а затем откройте скобку.
4. При вводе имени прокрутите список авто завершения формул, чтобы выбрать нужную функцию, а затем нажмите клавишу Tab.
5. Введите аргумент или выделите ячейку или диапазон, которые вы хотите вставить в функцию, а затем закройте скобку, чтобы завершить ввод функции.
6. Нажмите кнопку Ввод в строке формул или нажмите клавишу Enter
7. Excel автоматически закроет скобку, чтобы завершить ввод функции.

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

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

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

Чтобы ввести функцию при помощи команды Вставить функцию:

Щелкните ячейку, в. которую вы хотите ввести функцию.

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

Источник: http://www.teachexcel.ru/funkcii-i-formuli/ciklicheskie-ssilki.html

Циклические ссылки в Excel

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

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

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

Но в дальнейшем при вычислениях в таблицах можно столкнуться с ошибками. Давайте рассмотрим что такое циклическая ссылка и как её найти в Excel.

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

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

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

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

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

Источник: https://www.softo-mir.ru/ciklicheskie-ssylki-v-excel/

Финансы в Excel

Подробности Создано 10 Март 2013

Вложения:

crosscosts.xls

[Перекрестная себестоимость]

30 kB

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

Пример задачи

На крупном производственном предприятии имеется 2 цеха вспомогательного производства:

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

Особенностями этих цехов является то, что они формируют составляющие себестоимости продукции, а именно, потребленное электричество и тепло.

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

Итак, получается, что энергоцех для расчета себестоимости кВт/ч должен учитывать цену потребленного тепла, а теплоцех, в свою очередь, для расчета стоимости мДж должен знать цену кВт/ч. Типичный случай «перекрестной ссылки». Задача, как ни странно, часто встречается на практике именно в такой формулировке.

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

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

Упрощенная форма задачи представлена в файле примера к статье:

Формула в ячейке В9 (=B8+B7*E10) определяет, что итоговые затраты энергоцеха складываются из бюджета внутренних расходов (в примере представлен одним числом) и стоимостью потребления тепла (объем, умноженный на цену).

Ячейка B10 вычисляет себестоимость кВт/ч электроэнергии (=B9/B6).

Если попытаться скопировать формулу расчета себестоимости единицы электроэнергии в формулу расчета единицы теплоэнергии (B10 в E10), то логично получим сообщение о циклической ссылке:

Какие же варианты решения имеются и используются на практике?

Преобразование математической модели

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

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

Но здесь останавливаться на нем не будем, так как цель статьи все-таки показать возможности Excel, а не устроить теоретическую дискуссию.

Назначенная себестоимость

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

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

То есть, себестоимость выработанной продукции не совпадет с затратами всего предприятия.

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

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

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

Подбор значения

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

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

На практике можно воспользоваться интерфейсным средством Excel «Подбор параметра«:

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

Но забывать об этом варианте тоже не стоит – в некоторых случаях он даже удобнее рассматриваемого ниже варианта итерационных расчетов.

Итерационный расчет

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

Для включения итерационного типа вычислений включите одноименный параметр Excel. После этого можно копировать формулу из ячейки B10 в E10. В результате получим верный результат и отсутствие сообщения о циклической ссылке:

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

Заключение

Итерационные вычисления – это мощный встроенный механизм Excel для решения специфических задач с перекрестными взаимосвязями в формулах.

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

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

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

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

У продвинутых пользователей Excel очень популярен вопрос о возможности объединения диапазона ячеек, содержащих текст, в одну строку при…
При ведении финансового учета в электронных таблицах обычно ограничиваются, так называемым «котловым» методом расчета задолженности…
Одной из самых сложных задач экономического менеджмента является составление календарного плана выпуска продукции в условиях…
Пример решения головоломки «Судоку 9х9» без программирования. К экономике отношения не имеет, зато отлично показывает возможности Excel в…
При решении сложных алгоритмических задач с использование электронных таблиц иногда приходится сталкиваться с ошибками из-за…

Источник: https://www.excelfin.ru/index.php/component/content/article/76-articles/projects/179-practice-crosscosts

Шаг #2. Расчеты в Эксель (изучаем Excel с нуля)

Давайте рассмотрим следующий пример.

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

Ежемесячная капитализация — это присоединение начисленных за месяц процентов к сумме вклада. Соответсвенно, в следующем месяце начисление процентов происходит уже к новой сумме и так далее.

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

Сделаем активной ячейку А1, с клавиатуры введем слово «Месяц», затем выберем ячейку B1 и введем в нее «Размер вклада». Нажмем клавишу Enter.

Ввод данных в ячейки таблицы

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

Давайте введем в ячейку С1 «День недели»:

Отображение значения ячейки в строке формул

Оно просто не отображается полностью, но если мы обратим внимание на строку формул (2), то обнаружим там содержимое активной ячейки.

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

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

Изменение ширины ячейки в таблице Эксель

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

Подгонка ширины ячейки (столбца) под ее содержимое

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

Теперь нам нужно установить табличный курсор в ячейку A2 и ввести название месяца. Например, мы сделали вклад в ноябре и поэтому введем название месяца в ячейку А2 и нажимаем клавишу Enter. Далее можем продолжить ввод названий месяцев — декабрь, январь, февраль и так далее… Но давайте воспользуемся функцией автозаполнения, чтобы автоматизировать этот процесс.

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

Маркер заполнения

Автозаполнение месяцев в Excel

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

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

Удаление данных из ячеек таблицы

Давайте вернемся к нашей таблице и рассчитаем доход от вклада за первый месяц. Для этого в ячейку В2 введем расчетную формулу:

Введение формулы в ячейку

Формула означает, что сумма нашего вклада в размере 100000 будет умножена на 11%, а поскольку процентная ставка годовая, то делим все на 12 месяцев.

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

После нажатия клавиши Enter обнаружим в ячейке В2 вычисленное значение (1, см. рис.

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

Отображение формулы в строке формул

• Дело в том, что мы получили размер дохода от вклада за месяц, а нам нужно видеть всю сумму целиком, поэтому добавим в формулу размер вклада. Формула будет выглядеть так: =100000*11%/12+100000
• Далее нам нужно определить размер дохода в следующем месяце.
• Получаем формулу: =B2*11%/12.

То есть мы сумму вклада не вводим вручную, а берем готовое значение из ячейки B2, указав ее адрес. При этом обратите внимание на то, что адрес ячейки В2 в формуле имеет цвет и сама ячейка обрамляется рамкой такого же цвета.

Использование адреса ячейки в формуле

Ну а чтобы увидеть, какова у нас будет сумма вклада во втором месяце, изменим формулу, добавив значение размера вклада за прошлый месяц. То есть к уже введенной формуле необходимо прибавить значение, находящееся в ячейке В2. Мы можем ввести адрес ячейки вручную с клавиатуры или укажем ячейку мышью:

Вставка адреса ячейки в формулу с помощью мыши

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

Ну а теперь нам нужно повторить тоже самое для следующего месяца. Формула будет такая: =B3*11%/12+В3

Отображение формул в электронной таблице

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

Что нам это дает? А дело в том, что мы снова можем воспользоваться автозаполнением данных. Да-да, тоже самое, что мы делали с месяцами!

Точно также захватываем за край табличный курсор на ячейке В3 и перемещаем его до ячейки В13.

Автозаполнение в формулах

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

Итог автозаполнения формул

Отличается только формула в ячейке В2, в которой находится сумма вклада — 100000 рублей.

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

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

Перемещение выделенного диапазона ячеек

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

Адреса ячеек в формулах автоматически изменились

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

Пока же давайте создадим отдельную графу для суммы вклада — в ячейку А1 введем название всей нашей таблицы — «Расчет дохода от вклада», а в ячейку А3 — «Вклад».

Далее в ячейку В3 вынесем сумму вклада — 100 000 рублей:

Внесение дополнительных граф в таблицу

Теперь нужно отредактировать формулу в ячейке В7 — заменим значение вклада на значение из ячейки В3. Для этого в строке формулы выделим сумму двойным щелчком мыши на ней и укажу ячейку В3:

Изменение суммы вклада на значение ячейки B3

Можем выйти из режима отображения формул (отключим инструмент Показать формулы на вкладке Формулы) и проверим работу таблицы, изменив сумму вклада:

Расчет при сумме вклада в 100 000 и 150 000 рублей

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

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

Если вам удобнее воспринимать информацию в видео-формате, то посмотрите ролик:

Если вам интересен Эксель, то подписывайтесь на этот Дзен-канал или мой видео канал на YouTube.

Источник: https://zen.yandex.ru/media/id/5c4d8a530aaeef00ac928ac3/5c52f58b84869700acac6a89