Recalculer des formules dans Power Pivot

Lorsque vous utilisez 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 pour contrôler le 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 signifie 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 performances, 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 ou 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 est apparue 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 de noms ou de 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 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, de sorte que les colonnes ou les calculs qui dépendent de ce calcul doivent être actualisés.
Les 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 peut avoir été 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 de calcul des résultats de formule 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 vous travaillez avec une autre colonne qui n’a pas changé et 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 Excel n’effectue aucune validation ou vérification des formules, avec les résultats suivants :

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

Pour configurer le classeur pour le recalcul manuel

Dans Power Pivot, cliquez sur Conception> 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 et les tables sont mises à jour avec les 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 façon que ce soit, toutes les colonnes associées doivent être recalculées. Chaque fois que des modifications sont apportées au classeur Power Pivot, Power Pivot dans Excel effectue une analyse des données Power Pivot existantes pour déterminer si le recalcul est nécessaire et effectue la mise à jour de la manière la plus efficace possible.

Par exemple, supposons que vous disposez d’une table, Sales, qui est liée aux tables Product et ProductCategory ; les formules et 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 vérification des 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érique 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 peuvent déterminer que les colonnes peuvent être exclues du recalcul. Par exemple, si vous avez une formule qui recherche une valeur telle que [Couleur du produit] dans la table Products et que la colonne modifiée est [Quantité] 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 s’appuient sur Sales[Quantity], un recalcul est nécessaire.

Séquence de recalcul pour les colonnes dépendantes

Les dépendances sont calculées avant tout recalcul. Si plusieurs colonnes dépendent les unes des autres, Power Pivot suit la séquence des dépendances. Cela garantit que les colonnes sont traitées dans l’ordre approprié à 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 sont pas laissées 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 le filtrage n’entraîne généralement pas la réévaluation de ces fonctions si elles sont utilisées dans une colonne calculée. Les résultats de ces fonctions ne sont recalculés que 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é.