Recalculer des formules
Lorsque vous travaillez avec des données dans un classeur PowerPivot pour Excel, vous êtes parfois amené à actualiser les données à partir de la source, à recalculer les formules que vous avez créées dans les colonnes calculées ou à vous assurer que les données présentées dans un tableau croisé dynamique sont à jour.
Cette rubrique explique la différence entre l'actualisation et le recalcul des données, fournit une vue d'ensemble du mode de déclenchement du recalcul et décrit les options disponibles pour le contrôle du recalcul.
Présentation de l'actualisation et du recalcul des données
PowerPivot utilise aussi bien l'actualisation que le recalcul des données :
L'actualisation des données correspond à l'obtention de données à jour à partir de sources de données externes. PowerPivot ne détecte pas automatiquement les modifications dans les sources de données externes, mais les données peuvent être actualisées manuellement à partir du classeur PowerPivot ou automatiquement si le classeur est partagé sur SharePoint. Pour plus d'informations, consultez Différents moyens de mettre à jour des données dans PowerPivot.
Le recalcul correspond à la mise à jour des colonnes, des tableaux, des graphiques et des tableaux croisés dynamiques de votre classeur qui contiennent des formules. Étant donné que le recalcul d'une formule a des conséquences sur les performances, il est important de comprendre les dépendances associées à chaque calcul.
Cette rubrique explique comment un recalcul fonctionne. Pour plus d'informations sur l'impact potentiel d'un recalcul sur les performances, consultez la section Résolution des problèmes liés aux recalculs, ci-dessous.
Important
Vous ne devez jamais enregistrer ni publier le classeur tant que les formules qu'il contient n'ont pas été recalculées.
Recalcul manuel et recalcul automatique
Par défaut, PowerPivot effectue automatiquement les recalculs comme requis tout en optimisant le temps nécessaire au traitement. Même si le recalcul peut prendre du temps, c'est une tâche importante car lors du recalcul, les dépendances des colonnes sont vérifiées et vous êtes notifié si une colonne a été modifiée, si les données ne sont pas valides ou si une erreur est survenue dans une formule utilisée. Toutefois, vous pouvez choisir de renoncer à la validation et de 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 voulez contrôler le minutage des mises à jour.
Les modes manuel et automatique ont tous deux des avantages ; toutefois, nous vous recommandons fortement d'utiliser le mode de recalcul automatique. Ce mode maintient la synchronisation des métadonnées PowerPivot et empêche les problèmes causés par la suppression de données, par des modifications de noms ou de types de données, ou par des dépendances manquantes.
Utilisation du recalcul automatique
Lorsque vous utilisez le mode de recalcul automatique, toute modification apportée aux données dans le classeur qui changerait le résultat d'une formule quelconque déclenche le recalcul de la colonne entière qui contient une formule. Les modifications suivantes requièrent toujours le recalcul des formules :
Les valeurs issues d'une source de données externe ont été actualisées.
La définition de la formule a changé.
Les noms de tables ou de colonnes référencés 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 PowerPivot, si bien que les colonnes et 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 requiert l'exécution d'une requête pour mettre à jour le jeu de données. Le filtre a 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'encourir le coût du calcul des résultats des formules tant que vous n'êtes pas 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 tables utilisés dans la formule avant de la valider.
Vous savez que certaines données du classeur ont changé, mais vous travaillez avec une colonne différente qui n'a pas été modifiée. De ce fait, vous souhaitez différer un recalcul.
Vous travaillez dans un classeur qui comporte de nombreuses dépendances et souhaitez différer le recalcul jusqu'à ce que vous soyez certain que toutes les modifications nécessaires ont été apportées.
Notez que, tant que le classeur est défini sur un mode de calcul manuel, PowerPivot pour Excel n'effectue aucune validation ni vérification des formules, avec les résultats suivants :
Toutes les nouvelles formules que vous ajoutez au classeur seront signalées comme contenant une erreur.
Aucun résultat ne s'affichera dans les nouvelles colonnes calculées.
Pour obtenir des instructions sur la modification du mode de calcul ou le déclenchement du calcul manuel de formules, consultez Recalculer manuellement des formules ci-dessous.
Recalculer manuellement des formules
Cette rubrique décrit comment modifier momentanément les paramètres d'un classeur PowerPivot afin que les résultats des formules ne soient plus mis à jour automatiquement. Nous vous recommandons d'utiliser l'option Automatique chaque fois que vous le pouvez ; toutefois, le recalcul manuel des formules peut se révéler nécessaire pour atténuer l'impact de la conception de formules sur les performances du classeur.
Après avoir modifié ce paramètre, vous devez déclencher manuellement une mise à jour de tous les calculs basés sur des formules.
Important
Avant de publier le classeur, vous devez toujours rétablir le mode de calcul automatique. Cela permettra d'éviter des problèmes lors de la conception de formules.
Configurer le recalcul manuel
Recalculer implique la mise à jour des résultats de toutes les formules qui utilisent les données qui ont changé. Vous devez effectuer un recalcul chaque fois qu'une formule change, que les données qui affectent les résultats des calculs ont changé ou que les données sont actualisées. Pour plus d'informations sur l'actualisation des données, consultez Différents moyens de mettre à jour des données dans PowerPivot.
Notes
Dans Windows Vista et Windows 7, les fonctionnalités fournies dans la fenêtre PowerPivot sont disponibles sur un ruban, présenté dans cette rubrique. Dans Windows XP, ces fonctionnalités sont disponibles dans un ensemble de menus. Si vous utilisez Windows XP et souhaitez voir les correspondances entre les commandes des menus et les commandes du ruban, consultez Interface utilisateur de PowerPivot dans Windows XP.
Pour configurer le recalcul manuel du classeur
Dans la fenêtre PowerPivot, cliquez sur l'onglet Conception, puis dans le groupe Calculs, cliquez sur Options de calcul.
Cliquez sur Mode de calcul manuel.
Pour recalculer toutes les tables, cliquez de nouveau sur Options de calcul, puis sur Calculer maintenant.
Des erreurs sont recherchées dans les formules du classeur 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 moment.
Résolution des problèmes liés aux recalculs
Cette section fournit des conseils techniques supplémentaires à prendre en compte lors de la planification du recalcul d'un classeur. Pour obtenir des informations générales sur le recalcul et l'actualisation des données dans votre classeur, consultez les rubriques suivantes :
Différents moyens de mettre à jour des données dans PowerPivot
Dépendances
Lorsqu'une colonne dépend d'une autre colonne, et que le contenu de cette autre colonne est modifié, il faut éventuellement recalculer toutes les colonnes associées. Chaque fois que des modifications sont apportées au classeur PowerPivot, PowerPivot pour Excel effectue une analyse des données PowerPivot existantes afin de déterminer si un recalcul est nécessaire, puis il procède à la mise à jour de la manière la plus efficace possible.
Supposons par exemple que vous ayez une table Sales associée aux tables Product et ProductCategory et que les formules de la table Sales dépendent des deux autres tables. Toute modification apportée à la table Product ou ProductCategory conduit au recalcul des colonnes calculées de la table Sales. Cela est pertinent lorsque vous considérez que vous pouvez avoir des formules qui cumulent les ventes par catégorie ou par produit. Par conséquent, pour vous assurer de l'exactitude des résultats, les formules reposant sur les données doivent être recalculées.
PowerPivot effectue toujours un recalcul complet sur une table, car un recalcul complet est plus efficace qu'une recherche de valeurs modifiées. Les modifications qui déclenchent le recalcul peuvent contenir 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 simples, telles que la modification du nom d'une colonne, peuvent également déclencher le recalcul. Cela est dû au fait que les noms des colonnes sont utilisés comme identificateurs dans les formules.
Dans certains cas, PowerPivot pour Excel peut déterminer l'exclusion possible de colonnes lors 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 bien que les tables Sales et Products soient associées. En revanche, si vous avez des formules qui reposent sur Sales[Quantity], le 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, PowerPivot suit la séquence des dépendances. Cela garantit que les colonnes sont traitées dans l'ordre correct à la vitesse maximale.
Transactions
Les opérations qui recalculent ou actualisent des données se présentent sous forme de transactions. Cela signifie que si une partie de l'opération d'actualisation échoue, les opérations restantes sont restaurées. Cela permet de garantir 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, ni créer des points de contrôle.
Recalcul de fonctions volatiles
Certaines fonctions, telles que NOW, RAND et TODAY, n'ont pas de valeurs fixes. Pour éviter des problèmes de performances, l'exécution d'une requête ou le filtrage ne conduisent généralement pas à la réévaluation de ces fonctions si elles sont utilisées dans une colonne calculée. Les résultats pour 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 et TODAY, sont toujours recalculées si la fonction est utilisée dans la définition d'une mesure.