Повторное вычисление формул в Power Pivot

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

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

Общие сведения об обновлении данных и пересчете

Power Pivot использует как обновление данных, так и пересчет:

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

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

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

Ручной и автоматический пересчет

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

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

Использование автоматического пересчета

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

Значения из внешнего источника данных обновлены.
Определение формулы изменилось.
Изменены имена таблиц или столбцов, на которые ссылается формула.
Связи между таблицами были добавлены, изменены или удалены.
Добавлены новые меры или вычисляемые столбцы.
Были внесены изменения в другие формулы в книге, поэтому столбцы или вычисления, зависящие от этого вычисления, следует обновить.
Строки вставлены или удалены.
Вы применили фильтр, который требует выполнения запроса для обновления набора данных. Фильтр мог применяться либо в формуле, либо в составе сводной таблицы или сводной диаграммы.

Использование пересчета вручную

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

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

Обратите внимание, что до тех пор, пока книга настроена в режим вычисления вручную, Power Pivot в Microsoft Excel 2013 не выполняет проверку или проверку формул со следующими результатами:

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

Настройка книги для пересчета вручную

В Power Pivotщелкните Конструктор> Вычисления> Параметры вычисления> режиме вычисления вручную.
Чтобы пересчитать все таблицы, щелкните Параметры вычисления> Вычислить сейчас.

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

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

Устранение неполадок с пересчетом

Зависимости

Если столбец зависит от другого столбца и содержимое этого другого столбца каким-либо образом изменяется, может потребоваться пересчитать все связанные столбцы. При внесении изменений в книгу Power PivotPower Pivot в Microsoft Excel 2013 выполняет анализ существующих Power Pivot данных, чтобы определить, требуется ли пересчет, и выполнить обновление наиболее эффективным способом.

Например, предположим, что у вас есть таблица Sales, связанная с таблицами Product и ProductCategory; и формулы в таблице Sales зависят от обеих других таблиц. Любое изменение таблиц Product или ProductCategory приведет к перерасчету всех вычисляемых столбцов в таблице Sales . Это имеет смысл, если учесть, что у вас могут быть формулы, которые сводили продажи по категориям или продуктам. Поэтому, чтобы убедиться в правильности результатов; формулы, основанные на данных, должны быть пересчитаны.

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

В некоторых случаях Power Pivot могут определять, что столбцы могут быть исключены из пересчета. Например, если у вас есть формула, которая ищет значение , например [Product Color] , из таблицы Products, а измененный столбец — [Quantity] в таблице Sales , формула не требуется пересчитывать, даже если таблицы Продажи и Продукты связаны. Однако если у вас есть какие-либо формулы, которые зависят от Sales[Quantity], требуется пересчет.

Последовательность пересчета для зависимых столбцов

Зависимости вычисляются перед пересчетом. Если есть несколько столбцов, которые зависят друг от друга, Power Pivot следует последовательности зависимостей. Это гарантирует, что столбцы обрабатываются в правильном порядке с максимальной скоростью.

Транзакции

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

Пересчет летучих функций

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