Comprendre la propagation des résultats

La plupart des formules que vous avez écrites renvoient une seule valeur dans une seule cellule. Les tableaux dynamiques changent cela. Une seule formule peut renvoyer toute une liste ou une grille de résultats, et la feuille de calcul place automatiquement chaque résultat dans sa propre cellule.

Cette insertion automatique s’appelle le déversement. Vous saisissez la formule une seule fois, appuyez sur Entrée, puis les résultats se déploient dans les cellules voisines comme de l’eau qui remplit un bac.

Supposons que A1:A5 contienne les nombres de 1 à 5. Dans la cellule C1, saisissez =A1:A5*10, puis appuyez sur Entrée.

Au lieu d’une seule réponse, vous en obtenez cinq : 10, 20, 30, 40 et 50, empilées dans C1:C5. Vous n’avez saisi la formule que dans C1, mais le résultat s’est déversé automatiquement dans les cellules situées en dessous.

Le bloc de cellules rempli par une formule s’appelle sa plage de déversement. Seule la cellule en haut à gauche, appelée l’ancre, contient réellement la formule. Les autres cellules sont remplies automatiquement et affichent une bordure bleue pâle lorsque vous sélectionnez l’ancre.

Vous ne pouvez pas modifier directement les cellules déversées. Si vous essayez, vous obtenez une erreur. Pour modifier le résultat, éditez la formule dans la cellule d’ancrage.

Comme un déversement peut s’agrandir ou rétrécir, vous faites référence à l’ensemble du résultat avec l’opérateur de déversement : un signe dièse placé après l’adresse de l’ancre.

Si une formule dans C1 se déverse jusqu’à C5, alors C1# représente toute la plage déversée. Utilisez-la ailleurs, par exemple avec =SUM(C1#), et le total inclura toujours chaque valeur déversée, même si le déversement s’agrandit par la suite.

La véritable puissance du déversement vient de son caractère dynamique. Si vous ajoutez un nouveau nombre à la plage source, la plage de déversement s’agrandit automatiquement pour l’inclure. Supprimez une valeur et le déversement rétrécit.

Vous n’avez plus jamais besoin de recopier une formule vers le bas. L’unique formule d’ancrage ajuste la taille de son résultat pour l’adapter aux données qu’elle lit.

De nombreuses fonctions modernes sont conçues pour renvoyer des tableaux et se déversent donc :

• FILTER renvoie les lignes correspondantes.
• UNIQUE renvoie une liste sans doublons.
• SORT et SORTBY réordonnent une plage.
• SEQUENCE génère une liste de nombres.

Vous découvrirez FILTER et UNIQUE dans ce cours. Chacune produit de nombreux résultats à partir d’une seule formule.

Imaginez que la colonne A répertorie les régions suivantes : Est, Ouest, Est, Nord, Ouest. Pour obtenir une liste claire des régions distinctes, saisissez une seule formule :

=UNIQUE(A2:A6)

Elle déverse trois résultats : Est, Ouest et Nord. Ajoutez une autre région à la source et la liste déversée s’agrandit d’elle-même. Une seule formule pour une liste toujours à jour.

Les déversements ne se limitent pas à une progression vers le bas. Une formule peut se déverser dans plusieurs colonnes, voire remplir une grille à deux dimensions.

Par exemple, =SEQUENCE(3,4) déverse un bloc de 3 lignes sur 4 colonnes, remplissant 12 cellules avec les nombres de 1 à 12. La direction et la forme dépendent entièrement du tableau produit par la formule.

Les tableaux dynamiques et le déversement fonctionnent dans Excel pour Microsoft 365 ainsi que dans Excel 2021 et les versions ultérieures. Google Sheets prend également en charge le déversement des tableaux depuis plusieurs années.

Les anciennes versions comme Excel 2016 et 2019 ne prennent pas en charge le déversement. Les formules matricielles devaient alors être validées avec Ctrl+Maj+Entrée et ne pouvaient pas s’agrandir automatiquement. Si vos formules refusent de se déverser, vérifiez d’abord votre version.

Avant les tableaux dynamiques, vous écriviez =B2*C2 dans une cellule, puis vous la faisiez glisser sur des centaines de lignes. Avec le déversement, vous ne l’écrivez qu’une seule fois.

La formule =B2:B100*C2:C100 déverse un résultat par ligne sur toute la plage. La maintenance devient très simple : il n’y a plus qu’une seule formule à lire et à corriger, au lieu de centaines de copies qui pourraient finir par ne plus être synchronisées.

Considérez une formule qui se déverse comme un petit programme qui produit une forme de données, et pas seulement un nombre. La feuille de calcul dispose ensuite cette forme dans la grille à partir de la cellule d’ancrage.

Votre rôle consiste à vérifier qu’il y a suffisamment d’espace vide pour accueillir cette forme. Les prochaines leçons montreront comment produire des formes utiles avec FILTER et UNIQUE, ainsi que faire lorsque l’emplacement d’arrivée est bloqué.

Vérifiez votre compréhension du déversement.

Vous avez appris qu’une formule de tableau dynamique peut renvoyer de nombreux résultats et que la feuille de calcul les dispose automatiquement dans une plage de déversement.

• Seule la cellule d’ancrage contient la formule.
• Faites référence à l’ensemble du déversement avec l’opérateur #, par exemple C1#.
• Les déversements s’agrandissent et rétrécissent automatiquement avec les données sources.
• Des fonctions comme FILTER, UNIQUE, SORT et SEQUENCE se déversent toutes.

Vous allez maintenant utiliser FILTER pour ne renvoyer que les lignes souhaitées.