Recalculer des formules dans Power Pivot

Lorsque vous travaillez avec des données dans Power Pivot, vous devrez parfois 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 par rapport au 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, tableaux, 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 en termes de performances, il est important de comprendre les dépendances associées à chaque calcul.

Recalcul manuel ou automatique

Par défaut, Power Pivot recalcule automatiquement selon les besoins, tout en optimisant le temps requis pour le 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 renoncer à la validation et mettre à jour uniquement les calculs manuellement, en particulier si vous travaillez avec des formules complexes ou des jeux de données très volumineux et que vous souhaitez contrôler le calendrier 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 synchronise les métadonnées Power Pivot et empêche les problèmes causés par la suppression de données, les changements 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, toute modification de données susceptible d’entraîner la modification du résultat d’une formule déclenche le recalcul de la colonne entière contenant une formule. Les modifications suivantes nécessitent toujours un nouveau calcul 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.
• Des relations entre 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.
• 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 peut avoir été appliqué dans une formule ou dans le cadre d’un tableau ou d’un graphique croisé dynamique.

Utilisation du recalcul manuel

Vous pouvez recalculer manuellement pour éviter d’engendrer le coût du 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 été modifiées, mais vous travaillez avec une autre colonne qui n’a pas changé, de sorte que vous souhaitez reporter un nouveau calcul.
• Vous utilisez un classeur qui comporte de nombreuses dépendances et vous 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 seront marquées comme contenant une erreur.
• Aucun résultat n’apparaîtra dans les nouvelles colonnes calculées.

Configuration du classeur en vue d’un recalcul manuel

1. Dans Power Pivot, cliquez sur Mode decalcul manuel desoptions de>calcul des calculs> de conception>.
2. Pour recalculer toutes les tables, cliquez sur Options >de calculCalculer maintenant.
   Les formules du classeur sont vérifiées pour les erreurs et les tableaux sont mis à 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.

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 d’une manière ou d’une autre, 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 Excel 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, Ventes, qui est liée aux tables, Produit et Catégorie de produit ; et les formules de la table Ventes dépendent des deux autres tables. Toute modification apportée aux tables Produit ou Catégorie produit entraînera le recalcul de toutes les colonnes calculées dans la table Ventes . C’est logique si l’on considère que les formules 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 un 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 anodines, telles que la modification du nom d’une colonne, peuvent également déclencher un recalcul. En effet, les noms des colonnes sont utilisés comme identificateurs dans les formules.

Dans certains cas, Power Pivot peut déterminer que les colonnes peuvent être exclues du recalcul. Par exemple, si vous avez une formule qui recherche une valeur telle que [Couleur produit] dans la table Produits et que la colonne modifiée est [Quantité] dans la table Ventes , la formule n’a pas besoin d’être recalculée même si les tables Ventes et Produits sont liés. Toutefois, si vous avez des formules basées sur Ventes[Quantité], un nouveau calcul est nécessaire.

Séquence de recalcul pour les colonnes dépendantes

Les dépendances sont calculées avant tout nouveau calcul. 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 le bon ordre à la vitesse maximale.

Transactions

Les opérations qui recalculent ou actualisent les 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 annulées. Cela permet de s’assurer que les données ne sont pas laissées dans un état de traitement partiel. 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 MAINTENANT, ALEA ou AUJOURDHUI, 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 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 MAINTENANT, ALEA ou AUJOURDHUI seront toujours recalculées si la fonction est utilisée dans la définition d’un champ calculé.