Formules matricielles Excel : les fonctions dynamiques (365)

La propagation, ou spill : le principe à comprendre d'abord

Avant Excel 365, une formule occupait une cellule et n'en renvoyait qu'une valeur. Pour obtenir un résultat sur plusieurs cellules, il fallait écrire une formule matricielle classique et la valider avec la combinaison Ctrl+Maj+Entrée, ce qui restait réservé aux utilisateurs avancés. Le moteur de calcul d'Excel a changé en 2020 : une formule peut désormais renvoyer un ensemble de valeurs qui se déversent automatiquement dans les cellules voisines. Ce comportement porte un nom, la propagation, traduit de l'anglais spill.

Prenons un exemple minimal. Si vous saisissez la formule suivante dans une cellule vide :

=A1:A5*2

et que la plage A1:A5 contient les nombres 1, 2, 3, 4 et 5, Excel affiche les cinq résultats 2, 4, 6, 8 et 10, répartis sur cinq cellules. Vous n'avez tapé qu'une seule formule, dans une seule cellule, et le tableur a rempli les autres tout seul. La cellule où vous avez écrit la formule s'appelle la cellule source ; la zone remplie automatiquement est la plage de propagation.

Cette mécanique a plusieurs avantages concrets. Vous n'avez plus à utiliser Ctrl+Maj+Entrée, vos formules deviennent plus lisibles, et surtout les résultats s'adaptent : si votre source s'agrandit, la zone propagée suit. En contrepartie, une règle est à retenir : la zone de propagation doit être libre. Si une cellule sur laquelle la formule veut se déverser contient déjà quelque chose, Excel affiche l'erreur #PROPAGATION! (ou #DEVERSEMENT! selon la version) et ne calcule rien tant que vous n'avez pas libéré la place.

Ce changement, en apparence technique, modifie en profondeur la manière de construire un classeur. Auparavant, on recopiait une même formule vers le bas sur des centaines de lignes, au risque d'oublier une cellule ou de figer une référence. Désormais, une formule unique pilote toute une colonne de résultats. La maintenance s'en trouve simplifiée : pour modifier le calcul, vous corrigez une seule cellule, la cellule source, et l'ensemble se met à jour. C'est aussi pour cela que ces formules sont qualifiées de dynamiques : elles ne décrivent pas un résultat figé, mais une règle qui s'applique en continu aux données présentes.

Les quatre fonctions dynamiques essentielles

La propagation prend tout son sens avec un jeu de nouvelles fonctions conçues pour renvoyer plusieurs valeurs. Quatre d'entre elles couvrent l'immense majorité des besoins : FILTRE, TRIER, UNIQUE et SEQUENCE. Le tableau ci-dessous les présente en un coup d'œil, avant que nous les détaillions une à une.

FILTRE : extraire selon des conditions

FILTRE est sans doute la plus utilisée. Elle renvoie uniquement les lignes d'un tableau qui satisfont une condition, et le résultat se déverse automatiquement sous la formule. Dans sa forme la plus simple :

=FILTRE(A2:B100; C2:C100="France")

Cette formule retourne toutes les lignes des colonnes A et B pour lesquelles la colonne C vaut « France ». Pour combiner deux conditions avec un ET logique, on multiplie les critères entre eux ; pour un OU, on les additionne :

=FILTRE(A2:B100; (C2:C100="France")*(D2:D100>1000))   → France ET montant > 1000
=FILTRE(A2:B100; (C2:C100="France")+(C2:C100="Espagne"))  → France OU Espagne

Un troisième argument facultatif vous permet d'afficher un message lorsque aucune ligne ne correspond, plutôt qu'une erreur : =FILTRE(A2:B100; C2:C100="France"; "Aucun résultat"). Pensez-y systématiquement dans un tableau de bord partagé, pour éviter qu'une cellule en erreur ne déroute le lecteur.

Le principe à retenir, c'est que les conditions s'écrivent comme des opérations entre plages. Multiplier deux critères revient à un ET, car le résultat ne vaut 1 que si les deux sont vrais ; les additionner revient à un OU. Cette logique ouvre des combinaisons fines : pour extraire les commandes françaises supérieures à 1 000 euros mais hors du mois de décembre, vous écririez par exemple =FILTRE(A2:D100; (C2:C100="France")*(D2:D100>1000)*(MOIS(B2:B100)<>12)). FILTRE remplace ainsi avantageusement les filtres manuels et les tableaux croisés pour bon nombre d'extractions, avec l'avantage décisif d'être recalculée en temps réel.

TRIER et TRIER.PAR : ordonner sans toucher à la source

TRIER renvoie une copie triée d'un tableau, sans modifier l'original. =TRIER(A2:C100) trie selon la première colonne en ordre croissant. Pour choisir la colonne et le sens, on précise un index et un ordre, le -1 indiquant un tri décroissant :

=TRIER(A2:C100; 3; -1)   → tri par la 3e colonne, du plus grand au plus petit

Sa variante TRIER.PAR trie un tableau d'après les valeurs d'un autre tableau. C'est l'outil idéal pour produire un classement : =TRIER.PAR(Noms; Scores; -1) classe la liste des noms selon les scores décroissants. L'intérêt est que la liste source reste intacte ; vous obtenez le classement dans une zone séparée, recalculé dès qu'un score change.

Vous pouvez aussi trier sur plusieurs niveaux en imbriquant deux appels, ou en passant plusieurs couples de critères à TRIER.PAR. Pour ordonner d'abord par région croissante puis, à région égale, par chiffre d'affaires décroissant, l'écriture reste lisible et tient sur une seule formule. Ces tris dynamiques sont précieux pour les classements vivants, comme un top des meilleurs vendeurs affiché en permanence sur un tableau de bord, sans qu'il faille relancer un tri manuel à chaque nouvelle saisie.

UNIQUE : isoler les valeurs distinctes

UNIQUE supprime les doublons d'une plage et renvoie la liste des valeurs distinctes. Si la colonne A contient Pomme, Banane, Pomme, Cerise, Banane, alors =UNIQUE(A2:A100) renvoie Pomme, Banane, Cerise. Couplée à NBVAL, elle compte le nombre de valeurs différentes : =NBVAL(UNIQUE(A2:A100)) vous dit combien d'éléments distincts figurent dans la plage. Un troisième argument permet même de ne conserver que les valeurs n'apparaissant qu'une seule fois, ce qui est commode pour repérer les enregistrements réellement uniques par opposition à ceux qui se répètent.

UNIQUE rend de grands services en amont d'autres calculs. Une liste de valeurs distinctes constitue souvent la colonne de référence d'un tableau de synthèse : vous la générez une fois avec UNIQUE, puis vous calculez à côté un total ou une moyenne par élément. Et comme la liste est dynamique, elle s'allonge ou se raccourcit toute seule lorsque de nouvelles catégories apparaissent dans la source, sans intervention de votre part.

SEQUENCE : générer une suite de nombres

SEQUENCE produit une suite de nombres répartie sur les lignes et colonnes que vous demandez. Quelques exemples parlants :

=SEQUENCE(10)              → 1, 2, 3, ... jusqu'à 10
=SEQUENCE(5; 3)            → un tableau 5 lignes sur 3 colonnes, de 1 à 15
=SEQUENCE(10; 1; 100; 5)   → 100, 105, 110, ... avec un pas de 5

Elle sert à numéroter automatiquement une liste, à générer une série de dates espacées régulièrement, ou à créer des jeux de test. Associée à la fonction TEXTE, elle produit par exemple les douze mois d'une année pour l'axe d'un graphique : =TEXTE(SEQUENCE(12; 1; "01/01/2026"; 30); "mmm") renvoie janv, févr, mars, et ainsi de suite. Une autre fonction proche, COMPLETER.AUTO, va plus loin en remplissant une plage à partir d'un motif détecté, mais SEQUENCE reste la plus simple et la plus prévisible pour des suites régulières.

RECHERCHEX : la recherche moderne, matricielle elle aussi

Aux côtés de ces quatre fonctions, RECHERCHEX a remplacé l'ancienne RECHERCHEV en apportant une recherche plus souple, capable de remonter une plage entière en une seule formule. Si vous débutez sur les fonctions de recherche, commencez par notre tutoriel RECHERCHEV pour bien comprendre la logique de base, puis passez à RECHERCHEX une fois ces fondations posées.

La particularité matricielle de RECHERCHEX apparaît quand vous lui demandez de renvoyer plusieurs colonnes à la fois. La formule =RECHERCHEX(A2; Noms; Salaires:Bonus) retrouve une personne et renvoie d'un coup ses deux colonnes, salaire et bonus, qui se déversent à droite de la formule. Là où l'ancienne RECHERCHEV exigeait une formule par colonne, RECHERCHEX traite l'ensemble en une fois grâce à la propagation.

Combiner les fonctions pour des tableaux de bord dynamiques

La vraie puissance de ces fonctions vient de leur imbrication. Comme chacune renvoie un tableau, le résultat de l'une peut servir d'entrée à l'autre. Quelques combinaisons reviennent souvent dans la pratique.

Pour obtenir la liste alphabétique des produits français, on imbrique TRIER, UNIQUE et FILTRE : =TRIER(UNIQUE(FILTRE(Produits; Pays="France"))). La lecture se fait de l'intérieur vers l'extérieur : FILTRE isole les produits français, UNIQUE en retire les doublons, TRIER les ordonne. Sur le même principe, pour compter le nombre de clients distincts en France, vous écrivez =NBVAL(UNIQUE(FILTRE(Noms; Pays="France"))).

Imaginez maintenant un tableau de ventes avec le pays en colonne A, la catégorie en B et le chiffre d'affaires en C, sur un millier de lignes. La liste des pays distincts tient dans =UNIQUE(A2:A1000), le total par pays s'obtient avec SOMME.SI, et le palmarès des dix premières ventes en France se construit en combinant FILTRE et TRIER : =TRIER(FILTRE(A2:C1000; A2:A1000="France"); 3; -1). L'ensemble se recalcule seul : ajoutez une ligne au tableau source, et tous vos indicateurs se mettent à jour.

Pour ne garder que les cinq premières lignes d'un classement, ajoutez la fonction PRENDRE autour du tri, par exemple =PRENDRE(TRIER(FILTRE(A2:C1000; A2:A1000="France"); 3; -1); 5). PRENDRE et sa cousine IGNORER, apparues en même temps que ces fonctions, découpent un tableau dynamique en conservant ou en écartant un nombre de lignes ou de colonnes. C'est ce qui permet d'afficher un top 5 ou un top 10 sans calcul intermédiaire. Construit ainsi, un tableau de bord devient entièrement vivant : la source alimente les listes, qui alimentent les classements, qui alimentent les graphiques, le tout sans une seule manipulation manuelle.

Une dernière combinaison utile concerne la concaténation. Pour assembler en une phrase la liste des villes françaises distinctes, vous imbriquez JOINDRE.TEXTE, UNIQUE et FILTRE : =JOINDRE.TEXTE("; "; VRAI; UNIQUE(FILTRE(Villes; Pays="France"))). Le résultat tient dans une seule cellule et se met à jour dès qu'une ville s'ajoute à la source. Ce genre d'écriture, impensable avant 2020 sans macro, illustre bien le saut qu'ont représenté ces fonctions pour la productivité au quotidien.

Les erreurs fréquentes et les pièges de compatibilité

Quelques maladresses reviennent régulièrement chez ceux qui découvrent ces fonctions. Les connaître vous évite des heures de tâtonnement.

L'erreur de propagation. C'est la plus courante : la zone où la formule veut se déverser contient déjà des données. Excel affiche alors #PROPAGATION!. La solution est simple : repérez les cellules occupées sous la formule et videz-les pour libérer le passage.

Vouloir utiliser ces fonctions sur une vieille version. FILTRE, TRIER, UNIQUE, SEQUENCE et RECHERCHEX exigent Microsoft 365 ou Excel 2021 au minimum. Sur Excel 2019 et antérieurs, elles renvoient une erreur #NOM?. Si un collègue ouvre votre fichier avec une version ancienne, vos formules dynamiques ne fonctionneront pas chez lui : prévoyez des fonctions compatibles avant tout partage avec un parc hétérogène.

Continuer à taper Ctrl+Maj+Entrée. Cette combinaison était indispensable pour les formules matricielles classiques. Avec les fonctions dynamiques, elle est inutile : une simple touche Entrée suffit, le caractère matriciel est natif.

Oublier le poids des grosses plages. FILTRE et UNIQUE sur des tableaux de plusieurs centaines de milliers de lignes peuvent ralentir le classeur. Restreignez la plage de recherche, ajoutez des critères plus sélectifs, et appuyez-vous sur des tableaux structurés pour garder de bonnes performances.

En résumé

Les formules matricielles dynamiques ont profondément renouvelé Excel depuis 2020. Le principe central, la propagation, libère vos formules : une seule écriture suffit à remplir plusieurs cellules, sans Ctrl+Maj+Entrée, et le résultat s'adapte aux données. Autour de ce mécanisme, quatre fonctions couvrent l'essentiel des besoins, FILTRE pour extraire, TRIER pour ordonner, UNIQUE pour dédoublonner et SEQUENCE pour générer, auxquelles s'ajoute RECHERCHEX pour la recherche. En les combinant, vous bâtissez des tableaux de bord qui se recalculent tout seuls. La seule vraie limite reste la compatibilité : ces fonctions ne sont disponibles que sur Microsoft 365 et Excel 2021.

Maîtriser ces outils marque le passage vers un usage avancé du tableur, mais s'y mettre seul prend du temps. Une formation structurée vous fait gagner des mois en vous donnant une méthode applicable immédiatement à vos propres fichiers. C'est l'objet de notre formation Excel : formules dynamiques, fonctions de recherche, tableaux croisés et automatisation, le tout 100 % à distance et en live, avec un formateur dédié. Inscription immédiate, démarrage rapide, paiement en 3× sans frais. Pour aller plus loin dès maintenant, parcourez notre tutoriel RECHERCHEV et nos autres guides Excel.