Интерактивные вычисления в excel что это

Циклическая ссылка Excel

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

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

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

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

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

• Критерий Манна-Уитни
• Подключение MySQL в Excel
• Подключение Excel к SQL Server

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.

Удаление или разрешение циклической ссылки

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

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

Формула =D1+D2+D3 не работает, поскольку она расположена в ячейке D3 и ссылается на саму себя. Чтобы устранить проблему, можно переместить формулу в другую ячейку. Нажмите клавиши CTRL+X , чтобы вырезать формулу, выделите другую ячейку и нажмите клавиши CTRL+V , чтобы вставить ее.

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

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

Вы также можете попробовать один из описанных ниже способов.

• Если вы только что ввели формулу, начните с этой ячейки и проверка, чтобы узнать, ссылаетесь ли вы на саму ячейку. Например, ячейка A3 может содержать формулу =(A1+A2)/A3. Такие формулы, как =A1+1 (в ячейке A1), также вызывают ошибки циклической ссылки.

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

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

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

Предупреждение о циклической ссылке

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

При закрытии сообщения Excel отображает нулевое или последнее вычисляемое значение в ячейке. И теперь вы, вероятно, говорите: «Повесьте, последнее вычисляемое значение?» Да. В некоторых случаях формула может успешно выполниться до того, как она попытается вычислить себя. Например, формула, использующая функцию IF , может работать до тех пор, пока пользователь не введет аргумент (фрагмент данных, который формула должна правильно выполнить), который приведет к вычислению самой формулы. В этом случае Excel сохраняет значение из последнего успешного вычисления.

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

• Выберите формулу в строке формул и нажмите клавишу ВВОД.

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

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

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

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

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

1. Выберите Параметры >файлов >Формулы. Если вы используете Excel для Mac, выберите меню Excel, а затем выберите Параметры >Вычисление.
2. В разделе Параметры вычислений установите флажок Включить итеративные вычисления. На компьютере Mac выберите Использовать итеративное вычисление.
3. В поле Предельное число итераций введите количество итераций для выполнения при обработке формул. Чем больше предельное число итераций, тем больше времени потребуется для пересчета листа.
4. В поле Относительная погрешность введите наименьшее значение, до достижения которого следует продолжать итерации. Это наименьшее приращение в любом вычисляемом значении. Чем меньше число, тем точнее результат и тем больше времени потребуется Excel для вычислений.

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

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

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

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Совет: Если вы владелец малого бизнеса и хотите получить дополнительные сведения о настройке Microsoft 365, посетите раздел Справка и обучение для малого бизнеса.

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

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

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

Рассмотрим ситуацию на простом примере с плановой калькуляцией доходов и расходов. Дано:

1. Сумма расходов по статьям 25 150 ₽
2. Плановая рентабельность продаж 20%.

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

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

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

Первая мысль, как найти прибыль показана на рисунке 1. Нужно расходы умножить на Рентабельность:

Прибыль = 25 150 ₽ • 20% = 5 030 ₽

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

Выручка = 25 150 ₽ + 5 030 ₽ = 30 180 ₽

Рисунок 1. Расходы на Рентабельность продаж умножать нельзя.

Вообще-то нет, не выполнена… Если проверить, какая получится Рентабельность продаж, увидим, что это не 20%.

Проверка рентабельности = 5 030 ₽ / 30 180 ₽ = 17%

Потому что нельзя просто так взять Рентабельность продаж и умножить на расходы. Так делают с наценкой. Чтобы расчет получился верным, нужно изменить формулу расчета прибыли. От 100% отнять Рентабельность 20%, взять обратную величину, так мы получим коэффициент Выручки относительно расходов. А чтобы получить коэффициент прибыли придется отнять еще 100%.

Доля прибыли относительно расходов = 1 / ( 100% — 20% ) — 100% = 25%

Умножаем на расходы:

Прибыль = 25 150 ₽ • 25% = 6 287 ₽

Выручка = 25 150 ₽ + 6 287 ₽ = 31 437 ₽

Проверка рентабельности = 6 287 ₽ / 31 437 ₽ = 20% – Результат правильный.

Но! Согласитесь, весь предыдущий абзац в целом непонятен и такие «упражнения» с коэффициентами на большой расчетной модели реализовать сложно, а иногда невозможно. Или они получатся такими, что из-за сложности потом их не поменяешь. Что делать?

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

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

Не проблема! Переходим Файл → Параметры → Формулы → Включить итеративные вычисления.

Рисунок 3. Включите итеративные вычисления.

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

Рисунок 4. Включите итеративные вычисления

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

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

Язык DAX — понятие, для чего, для кого, какие задачи решает

DAX (Data Analysis Expressions) — это язык функций и формул, который входит в состав нескольких приложений от компании Microsoft, а если быть точнее, то в приложение Power BI, в надстройку Excel (Powerpivot) и в SSAS Tabular (SQL Server).

MS DAX — это функциональный язык запросов, который целиком и полностью состоит только из встроенных в него функций. В DAX вы не сможете создать какие-либо свои функции, циклы или что-то еще, что можно создавать в языках программирования. Ведь DAX — это не язык программирования, это именно функциональный язык от Microsoft, при помощи которого, у вас имеется возможность на основе встроенных в него функций создавать свои формулы для расчета необходимых вычислений, обогащая вашу исходную модель данных. И на основе всего этого, в итоге, создавать те самые интерактивные отчеты в Power BI и Excel (Powerpivot).

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

Язык DAX и Excel (Powerpivot)
Тем из вас, кто ранее пользовался обычным Excel, а сейчас переходит к работе с языком DAX, DAX может показаться достаточно знакомым и похожим на Excel. И это действительно так. Дело в том, что язык DAX изначально зародился из под Excel, а конкретнее, в 2010 году его представили на свет в рамках языка функций и формул для надстройки Excel — Power Pivot. И создатели DAX сделали все возможное, чтобы пользователям стандартного Экселя было легко перейти к работе с DAX. В DAX достаточно много функций, которые так или иначе похожи, или чем-то напоминают соответствующие функции в Excel.

Язык DAX и Power BI
Также, есть небольшое различие в языках DAX используемых в разных приложениях, например таких, как Power BI и Excel (Powerpivot). А конкретно, в DAX в Power BI имеется возможность создавать физически и сохранять в модели данных вычисляемые таблицы, чего в Excel (Powerpivot) по крайней мере, сейчас, пока сделать не получится. В Excel можно создавать некие виртуальные таблицы в составе самих формул DAX, но создать физически вычисляемые таблицы и потом сохранить их в модели данных нельзя. Поэтому, если вы только начинаете изучать язык DAX, то лучше всего, по моему мнению, это делать в приложении Power BI: во-первых, в Power BI в DAX можно легко создавать в модели данных вычисляемые таблицы; а во-вторых, само удобство использования DAX в Power BI намного лучше, ведь Power BI — это следующий технологический уровень в BI аналитике, после Excel, и именно поэтому, там процесс работы намного удобнее.

Где нужен DAX
Формулы DAX включают функции, операторы и значения для выполнения расширенных вычислений и запросов к данным в связанных таблицах и столбцах в табличных моделях данных. Они используются в показателях, вычисляемых столбцах, вычисляемых таблицах и безопасности на уровне строк.
Рассмотрим основные атрибуты по порядку:

1. Меры
Меры — это формулы динамических вычислений, результаты которых меняются в зависимости от контекста.

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

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

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

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

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

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