Recalculer des formules dans Power Pivot

Lorsque vous travaillez avec des données dans Power Pivot, vous devrez peut-être actualiser les données à partir de la source, recalculer les formules que vous avez créées dans des colonnes calculées ou vérifier que les données présentées dans un tableau croisé dynamique sont à jour.

Cette rubrique explique la différence entre l’actualisation des données et le recalcul des données, fournit une vue d’ensemble de la façon dont le recalcul est déclenché et décrit vos options de contrôle du recalcul.

Présentation de l’actualisation des données et du recalcul

Power Pivot utilise à la fois l’actualisation et le recalcul des données :

L’actualisation des données consiste à obtenir des données à jour à partir de sources de données externes. Power Pivot ne détecte pas automatiquement les modifications apportées aux sources de données externes, mais les données peuvent être actualisées manuellement à partir de la fenêtre Power Pivot ou automatiquement si le classeur est partagé sur SharePoint.

Le recalcul consiste à mettre à jour toutes les colonnes, tables, graphiques et tableaux croisés dynamiques de votre classeur qui contiennent des formules. Étant donné que le recalcul d’une formule entraîne un coût de performance, il est important de comprendre les dépendances associées à chaque calcul.

Important : Vous ne devez pas enregistrer ou publier le classeur tant que les formules qu’il contient n’ont pas été recalculées.

Recalcul manuel et automatique

Par défaut, Power Pivot recalcule automatiquement en fonction des besoins tout en optimisant le temps nécessaire au traitement. Bien que le recalcul puisse prendre du temps, il s’agit d’une tâche importante, car pendant le recalcul, les dépendances de colonne sont vérifiées et vous serez averti si une colonne a changé, si les données ne sont pas valides ou si une erreur s’est produite dans une formule qui fonctionnait auparavant. Toutefois, vous pouvez choisir de renoncer à la validation et de mettre à jour uniquement les calculs manuellement, en particulier si vous utilisez des formules complexes ou des jeux de données très volumineux et que vous souhaitez contrôler le minutage des mises à jour.

Les modes manuel et automatique présentent des avantages; toutefois, nous vous recommandons vivement d’utiliser le mode de recalcul automatique. Ce mode permet de synchroniser les métadonnées Power Pivot et d’éviter les problèmes causés par la suppression de données, les modifications apportées aux noms ou aux types de données ou les dépendances manquantes.

Utilisation du recalcul automatique

Lorsque vous utilisez le mode de recalcul automatique, toutes les modifications apportées aux données qui entraîneraient la modification du résultat d’une formule déclenchent le recalcul de la colonne entière qui contient une formule. Les modifications suivantes nécessitent toujours un recalcul des formules :

Les valeurs d’une source de données externe ont été actualisées.
La définition de la formule a changé.
Les noms des tables ou des colonnes référencées dans une formule ont été modifiés.
Les relations entre les tables ont été ajoutées, modifiées ou supprimées.
De nouvelles mesures ou colonnes calculées ont été ajoutées.
Des modifications ont été apportées à d’autres formules dans le classeur. Par conséquent, les colonnes ou les calculs qui dépendent de ce calcul doivent être actualisés.
Des lignes ont été insérées ou supprimées.
Vous avez appliqué un filtre qui nécessite l’exécution d’une requête pour mettre à jour le jeu de données. Le filtre aurait pu être appliqué dans une formule ou dans le cadre d’un tableau croisé dynamique ou d’un graphique croisé dynamique.

Utilisation du recalcul manuel

Vous pouvez utiliser le recalcul manuel pour éviter d’entraîner le coût des résultats de formule de calcul jusqu’à ce que vous soyez prêt. Le mode manuel est particulièrement utile dans les situations suivantes :

Vous concevez une formule à l’aide d’un modèle et souhaitez modifier les noms des colonnes et des tables utilisées dans la formule avant de la valider.
Vous savez que certaines données du classeur ont changé, mais que vous travaillez avec une autre colonne qui n’a pas changé. Vous souhaitez donc reporter un recalcul.
Vous travaillez dans un classeur qui a de nombreuses dépendances et souhaitez différer le recalcul jusqu’à ce que vous soyez sûr que toutes les modifications nécessaires ont été apportées.

Notez que, tant que le classeur est défini sur le mode de calcul manuel, Power Pivot dans Microsoft Excel 2013 n’effectue aucune validation ou vérification des formules, avec les résultats suivants :

Toutes les nouvelles formules que vous ajoutez au classeur seront marquées comme contenant une erreur.
Aucun résultat n’apparaîtra dans les nouvelles colonnes calculées.

Pour configurer le classeur pour le recalcul manuel

Dans Power Pivot, cliquez sur Concevoir> Calculs> options de calcul> mode de calcul manuel.
Pour recalculer toutes les tables, cliquez sur Options de calcul> Calculer maintenant.

Les formules du classeur sont vérifiées pour rechercher les erreurs, et les tables sont mises à jour avec des résultats, le cas échéant. Selon la quantité de données et le nombre de calculs, le classeur peut ne plus répondre pendant un certain temps.

Important : Avant de publier le classeur, vous devez toujours revenir au mode de calcul automatique. Cela permet d’éviter les problèmes lors de la conception de formules.

Résolution des problèmes de recalcul

Dépendances

Lorsqu’une colonne dépend d’une autre colonne et que le contenu de cette autre colonne change de quelque manière que ce soit, toutes les colonnes associées peuvent avoir besoin d’être recalculées. Chaque fois que des modifications sont apportées au classeur Power Pivot, Power Pivot dans Microsoft Excel 2013 effectue une analyse des données Power Pivot existantes pour déterminer si un recalcul est nécessaire et effectue la mise à jour de la manière la plus efficace possible.

Par exemple, supposons que vous avez une table, Sales, qui est liée aux tables, Product et ProductCategory ; et les formules de la table Sales dépendent des deux autres tables. Toute modification apportée aux tables Product ou ProductCategory entraîne le recalcul de toutes les colonnes calculées de la table Sales . Cela est logique lorsque vous considérez que vous pouvez avoir des formules qui cumulent les ventes par catégorie ou par produit. Par conséquent, pour être sûr que les résultats sont corrects; les formules basées sur les données doivent être recalculées.

Power Pivot effectue toujours un recalcul complet pour une table, car un recalcul complet est plus efficace que la recherche de valeurs modifiées. Les modifications qui déclenchent le recalcul peuvent inclure des modifications majeures telles que la suppression d’une colonne, la modification du type de données numériques d’une colonne ou l’ajout d’une nouvelle colonne. Toutefois, des modifications apparemment triviales, telles que la modification du nom d’une colonne, peuvent également déclencher un recalcul. Cela est dû au fait que les noms des colonnes sont utilisés comme identificateurs dans les formules.

Dans certains cas, Power Pivot pouvez déterminer que les colonnes peuvent être exclues du recalcul. Par exemple, si vous avez une formule qui recherche une valeur telle que [Product Color] à partir de la table Products et que la colonne modifiée est [Quantity] dans la table Sales , la formule n’a pas besoin d’être recalculée même si les tables Sales et Products sont liées. Toutefois, si vous avez des formules qui reposent sur Sales[Quantity], un recalcul est requis.

Séquence de recalcul pour les colonnes dépendantes

Les dépendances sont calculées avant tout recalcul. S’il existe plusieurs colonnes qui dépendent les unes des autres, Power Pivot suit la séquence de dépendances. Cela garantit que les colonnes sont traitées dans le bon ordre à la vitesse maximale.

Transactions

Les opérations qui recalculent ou actualisent des données ont lieu en tant que transaction. Cela signifie que si une partie de l’opération d’actualisation échoue, les opérations restantes sont restaurées. Cela permet de s’assurer que les données ne restent pas dans un état partiellement traité. Vous ne pouvez pas gérer les transactions comme vous le faites dans une base de données relationnelle ou créer des points de contrôle.

Recalcul des fonctions volatiles

Certaines fonctions telles que NOW, RAND ou TODAY n’ont pas de valeurs fixes. Pour éviter les problèmes de performances, l’exécution d’une requête ou d’un filtrage n’entraîne généralement pas une réévaluation de ces fonctions si elles sont utilisées dans une colonne calculée. Les résultats de ces fonctions sont recalculés uniquement lorsque la colonne entière est recalculée. Ces situations incluent l'actualisation à partir d'une source de données externe ou une modification manuelle des données qui provoque la réévaluation des formules qui contiennent ces fonctions. Toutefois, les fonctions volatiles telles que NOW, RAND ou TODAY sont toujours recalculées si la fonction est utilisée dans la définition d’un champ calculé.