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 leur source, recalculer les formules dans les colonnes calculées ou mettre à jour les tableaux croisés dynamiques qui utilisent le modèle de données.

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

Comprendre 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. Pour importer des données mises à jour, actualisez le modèle de données.

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 affecte les 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 nécessaire au traitement. Bien que le recalcul puisse prendre du temps, il s’agit d’une tâche importante. Pendant le recalcul, Power Pivot vérifie les dépendances de colonne et vous avertit si une colonne a changé, si les données ne sont pas valides ou si une erreur apparaît dans une formule qui fonctionnait auparavant. Toutefois, vous pouvez renoncer à la validation et mettre à jour les calculs manuellement, en particulier si vous travaillez avec des formules complexes ou des ensembles de données très volumineux et que vous souhaitez contrôler le calendrier des mises à jour.

Les modes manuel et automatique présentent tous deux des avantages. Toutefois, utilisez 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 des 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 provenant d’une source de données externe sont actualisées.
• La définition de la formule a changé.
• Les noms des tables ou des colonnes référencées dans une formule sont modifiés.
• Les relations entre les tables sont ajoutées, modifiées ou supprimées.
• De nouvelles mesures ou colonnes calculées sont ajoutées.
• Des modifications sont 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 sont 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 être appliqué dans une formule ou dans le cadre d’un tableau ou d’un graphique croisé dynamique.

Utilisation du recalcul manuel

Utilisez un recalcul manuel 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é. Vous souhaitez donc reporter un nouveau calcul.
• Vous travaillez dans 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.

Si vous définissez le classeur en mode de calcul manuel, Power Pivot dans Excel n’effectue aucune validation ou vérification des formules. Les résultats suivants se produisent :

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

Configuration du classeur en vue d’un recalcul manuel

1. Dans Power Pivot, sélectionnez Mode decalcul manuelOptions de>calcul des calculs> de conception>.
2. Pour recalculer toutes les tables, sélectionnez Options >de calculCalculer maintenant.
   Le classeur vérifie les formules pour la recherche d’erreurs et met à jour les tables avec les résultats. Selon la quantité de données et le nombre de calculs, le classeur peut ne plus répondre temporairement.

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, vous devrez peut-être recalculer toutes les colonnes associées. Chaque fois que vous apportez des modifications au classeur Power Pivot, Power Pivot dans Excel analyse les données Power Pivot existantes pour déterminer si un recalcul est nécessaire, et il 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îne le recalcul de toutes les colonnes calculées dans la table Ventes . Ce comportement est logique si l’on considère que les formules cumulent les ventes par catégorie ou par produit. Pour vous assurer que les résultats sont corrects, vous devez recalculer les formules en fonction des donné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 anodines, telles que la modification du nom d’une colonne, peuvent également déclencher un recalcul. Ce comportement se produit, car les noms des colonnes sont utilisés comme identificateurs dans les formules.

Dans certains cas, Power Pivot détermine qu’il peut exclure des colonnes du recalcul. Par exemple, si vous avez une formule qui recherche une valeur telle que [Couleur produit] dans le tableau Produits et que la colonne que vous avez modifiée est [Quantité] dans le tableau 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

Power Pivot calcule les dépendances avant de recalculer des colonnes. Si plusieurs colonnes dépendent les unes des autres, Power Pivot suit la séquence des dépendances. Cette séquence permet à Power Pivot de traiter les colonnes dans l’ordre correct et à la vitesse maximale.

Transactions

Les opérations qui recalculent ou actualisent les données ont lieu en tant que transaction. Si une partie de l’opération d’actualisation échoue, les opérations restantes sont annulées. Ce comportement garantit 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 et vous ne pouvez pas créer de points de contrôle.

Recalcul des fonctions volatiles

Certaines fonctions, telles que NOW, RANDou 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 la réévaluation de ces fonctions par Power Pivot si vous les utilisez dans une colonne calculée. Power Pivot recalcule uniquement les résultats de ces fonctions lorsqu’il recalcule la colonne entière. Ces situations incluent l’actualisation à partir d’une source de données externe ou la modification manuelle des données qui entraîne la réévaluation des formules qui contiennent ces fonctions. Toutefois, si vous utilisez des fonctions volatiles telles que NOW, RANDou TODAY dans la définition d’un champ calculé, Power Pivot les recalcule toujours.