Microsoft a introduit l’opérateur d’intersection implicite dans le cadre d’une mise à niveau substantielle du langage de formule d’Excel pour prendre en charge les tableaux dynamiques. Les tableaux dynamiques apportent de nouvelles capacités et fonctionnalités de calcul significatives à Excel.
Langage de formule mis à niveau
Le langage de formule amélioré d’Excel est presque identique à l’ancien langage, sauf qu’il utilise l’opérateur @ pour indiquer où l’intersection implicite pourrait se produire, alors que l’ancien langage le faisait de façon silencieuse. Par conséquent, il est possible qu’un @ apparaisse dans certaines formules lorsque vous les ouvrez dans un tableau dynamique Excel. Vos formules continuent de fonctionner de la même manière.
Présentation des intersections implicites
La logique d’intersection implicite réduit de nombreuses valeurs à une seule valeur. Excel effectue cette opération pour forcer une formule à renvoyer une valeur unique, car une cellule ne peut contenir qu’une seule valeur. Si votre formule renvoyait une valeur unique, l’intersection implicite ne faisait rien (même si elle était techniquement effectuée en arrière-plan). La logique fonctionne comme suit :
- Si la valeur est un élément unique, renvoyez l’élément.
- Si la valeur est une plage, renvoyez la valeur de la cellule sur la même ligne ou la même colonne que la formule.
- Si la valeur est un tableau, choisissez la valeur en haut à gauche.
Avec l’arrivée des tableaux dynamiques, Excel n’est plus limité à renvoyer des valeurs uniques à partir de formules. Par conséquent l’intersection implicite silencieuse n’est plus nécessaire. Là où une ancienne formule pouvait déclencher de manière invisible une intersection implicite, le tableau dynamique activé Excel montre où cela se serait produit avec @.
Pourquoi le symbole @ ?
Le symbole @ est déjà utilisé dans les références de table pour indiquer une intersection implicite. Considérons la formule suivante dans un tableau =[@Column1]. Le @ indique que la formule utilise une intersection implicite pour récupérer la valeur sur la même ligne à partir de [Colonne1].
Pouvez-vous supprimer le @ ?
C’est souvent possible. Cela dépend de ce que renvoie la partie de la formule à droite du @ :
- Si elle renvoie une seule valeur (le cas le plus courant), il n’y a aucune modification lorsque vous supprimez le @.
- S’il renvoie une plage ou un tableau, la suppression du @ le fait qu’il se propage aux cellules adjacentes.
Si vous supprimez un @ ajouté automatiquement et rouvrez le classeur dans une version antérieure d’Excel, il apparaît sous la forme d’une ancienne formule de tableau (entourée d’accolades {}). Ce comportement garantit que l’ancienne version ne déclenche pas d’intersection implicite.
Quand ajoutons-nous le @ aux anciennes formules ?
En règle générale, les fonctions qui renvoient des plages ou des tableaux à plusieurs cellules ont un préfixe @ si vous les avez créées dans une ancienne version d’Excel. Il n’y a aucun changement dans la façon dont votre formule se comporte. Vous pouvez simplement désormais voir l’intersection implicite qui était auparavant invisible. Les fonctions courantes qui renvoient des plages multicellulaires incluent INDEX, DECALER et les fonctions définies par l’utilisateur (UDF). Il existe une exception courante : si les opérateurs sont encapsulés dans une fonction qui accepte un tableau ou une plage (par exemple, SOMME() ou MOYENNE()).
Consultez l’article Fonctions Excel qui renvoient des plages ou des tableaux pour obtenir plus d’informations.
Exemples
| Formule d’origine | Affichage dans le tableau dynamique Excel | Explication |
|---|---|---|
| =SOMME(A1:A10) | =SOMME(A1:A10) | Aucune modification : Aucune intersection implicite ne peut se produire, car la fonction SOMME attend des plages ou des tableaux. |
| =A1+A2 | =A1+A2 | Aucun changement : Aucune intersection implicite ne peut se produire. |
| =A1:A10 | =@A1:A10 | Une intersection implicite se produit et Excel renvoie la valeur associée à la ligne dans laquelle se trouve la formule. |
| =INDEX(A1:A10,B1) | =@INDEX(A1:A10,B1) | Une intersection implicite peut se produire. La fonction INDEX peut renvoyer un tableau ou une plage lorsque son deuxième ou troisième argument est 0. |
| =DECALER(A1:A2,1,1) | =@DECALER(A1:A2,1,1) | Une intersection implicite peut se produire. La fonction DECALER peut renvoyer une plage multicellulaire. Dans ce cas, il déclenche une intersection implicite. |
| =MAUDF() | =@MAUDF() | Une intersection implicite peut se produire. Créer des fonctions personnalisées dans Excel peut renvoyer des tableaux. Dans ce cas, la formule d’origine déclenche une intersection implicite. |
Utiliser l’opérateur @ dans les nouvelles formules
Si vous créez ou modifiez une formule dans un tableau dynamique Excel qui contient l’opérateur @, celle-ci peut apparaître sous la forme _xlfn. SINGLE() dans un tableau pré-dynamique Excel.
Ce comportement se produit lorsque vous validez une formule mixte. Une formule mixte est une formule qui repose à la fois sur le calcul de tableau et sur l’intersection implicite. Tableau pré-dynamique Excel ne prend pas en charge cette combinaison. Tableau pré-dynamique Excel prend uniquement en charge les formules qui font i) une intersection implicite ou ii) un calcul de tableau complet.
Lorsque le tableau dynamique activé Excel détecte la création d’une formule mixte, il propose une variation de la formule qui utilise l’intersection implicite partout. Par exemple, si vous entrez =A1:A10+@A1:A10, , la boîte de dialogue suivante s’affiche :
Si vous choisissez de rejeter la formule proposée par la boîte de dialogue, la formule =A1:A10+@A1:A10 mixte est validée. Si vous ouvrez ultérieurement cette formule dans un tableau pré-dynamique Excel, elle apparaît comme avec =A1:A10+_xlfn.SINGLE(A1:A10) les opérateurs @ dans la formule mixte apparaissant comme _xlfn.SINGLE(). Lorsque le tableau pré-dynamique Excel évalue cette formule, elle retourne un #NAME ! valeur d’erreur.
Vous avez besoin d’une aide supplémentaire ?
Vous pouvez toujours poser des questions à un expert de la Communauté technique Excel ou obtenir de l’aide dans les Communautés.
Voir aussi
Fonction FILTRE
Fonction TABLEAU.ALEAT
Fonction SEQUENCE
Fonction TRI
Fonction TRIERPAR
Fonction UNIQUE
Les tableaux dynamiques et le comportement de matrice dispersé