Matrices dynamiques dans Excel : FILTRE, TRIER et UNIQUE expliqués

Pendant des années, extraire d’un tableau les seules ventes d’une région, les trier ou obtenir une liste sans doublons imposait des filtres manuels, des tableaux croisés dynamiques ou des formules interminables à recopier ligne par ligne. Avec les matrices dynamiques, une seule formule saisie dans une cellule renvoie toute une plage de résultats, mise à jour dès que les données changent.

Vous allez voir comment fonctionnent FILTRE, TRIER et UNIQUE, comment les combiner et que faire lorsqu’une erreur de propagation apparaît. Tous les exemples utilisent le même tableau, vous pouvez donc les reproduire en quelques minutes.

Ce que sont les matrices dynamiques et la version d’Excel nécessaire

Une matrice dynamique est le résultat d’une formule qui occupe plusieurs cellules. Vous saisissez la formule dans la première cellule et Excel « propage » les résultats vers le bas et vers la droite, sur autant de lignes et de colonnes que nécessaire. Si les données sources changent, la plage de résultats s’ajuste automatiquement.

Ces fonctions sont disponibles dans Excel pour Microsoft 365, Excel 2021 et versions ultérieures, ainsi que dans Excel pour le web. Elles n’existent pas dans Excel 2019 ou les versions antérieures : si vous y ouvrez un fichier qui les utilise, vous verrez l’erreur #NOM? ou des valeurs figées qui ne se recalculent plus.

Avant de commencer, retenez trois règles :

• La formule se saisit uniquement dans la première cellule. Le reste du résultat est entouré d’une bordure bleue lorsque vous le sélectionnez.
• Vous ne pouvez ni modifier ni effacer une cellule isolée de la plage propagée. Pour changer quelque chose, modifiez la formule d’origine.
• Pour faire référence à tout le résultat depuis une autre formule, on ajoute l’opérateur # après la première cellule, par exemple F2#.

Le tableau d’exemple

Copiez ces données dans une nouvelle feuille, à partir de la cellule A1. Les formules des sections suivantes portent sur la plage A2:D9.

FILTRE : extraire uniquement les lignes qui remplissent une condition

La syntaxe est FILTRE(tableau; inclure; [si_vide]). Le premier argument est la plage à renvoyer, le deuxième une condition évaluée ligne par ligne et le troisième, facultatif, ce qui s’affiche si aucune ligne ne remplit la condition.

Pour obtenir toutes les ventes de la région Nord :

=FILTRE(A2:D9;C2:C9="Nord";"Aucun résultat")

Le résultat comprend quatre lignes complètes (Client A, Client C, Client D et de nouveau Client C) avec leurs quatre colonnes.

Plusieurs conditions à la fois

Dans l’argument inclure, ET et OU ne fonctionnent pas, car ces fonctions renvoient une seule valeur et non une valeur par ligne. À la place, chaque condition s’écrit entre parenthèses et on les combine par des opérations :

• Pour que toutes soient remplies (logique ET), multipliez : (C2:C9="Nord")*(D2:D9>500).
• Pour qu’au moins une soit remplie (logique OU), additionnez : (C2:C9="Nord")+(C2:C9="Sud").

Ventes de la région Nord supérieures à 500 :

=FILTRE(A2:D9;(C2:C9="Nord")*(D2:D9>500);"Aucun résultat")

La formule renvoie deux lignes : Client D avec 1250 et Client C avec 870.

TRIER : trier les résultats sans toucher aux données d’origine

La syntaxe est TRIER(tableau; [index_tri]; [ordre_tri]; [par_col]). L’index indique la colonne du tableau servant au tri (1 pour la première) et l’ordre vaut 1 pour croissant, la valeur par défaut, ou -1 pour décroissant.

=TRIER(A2:D9;4;-1)

La formule renvoie le tableau complet trié par montant décroissant : d’abord Client D (1250), puis Client A (910), Client B (890) et ainsi de suite jusqu’à Client C avec 45. Le tableau d’origine garde son ordre.

Trier le résultat d’un filtre

Comme FILTRE renvoie une matrice, vous pouvez la transmettre directement à TRIER. Vous obtenez ainsi les ventes du Nord du montant le plus élevé au plus faible :

=TRIER(FILTRE(A2:D9;C2:C9="Nord");4;-1)

UNIQUE : des listes sans doublons en une seule formule

La syntaxe est UNIQUE(tableau; [par_col]; [exactement_une_fois]). Dans sa forme la plus simple, elle renvoie chaque valeur distincte une seule fois, dans l’ordre de sa première apparition :

=UNIQUE(B2:B9)

Le résultat est Clavier, Écran, Souris et Portable.

Les valeurs qui apparaissent exactement une fois

Le troisième argument change le comportement : avec VRAI, seules les valeurs qui ne se répètent jamais sont renvoyées. Remarquez le double point-virgule, qui laisse le deuxième argument vide :

=UNIQUE(A2:A9;;VRAI)

La formule renvoie Client D et Client E, c’est-à-dire les clients ayant fait un seul achat.

Compter les valeurs distinctes

En imbriquant UNIQUE dans NBVAL, vous obtenez le nombre d’éléments différents. Cette formule renvoie 3, car il y a trois régions distinctes :

=NBVAL(UNIQUE(C2:C9))

Combiner les trois fonctions : un récapitulatif par région

Leur vraie puissance apparaît quand on les imbrique. Par exemple, pour voir quels produits ont été vendus dans la région Sud, sans doublons et par ordre alphabétique :

=TRIER(UNIQUE(FILTRE(B2:B9;C2:C9="Sud")))

Le résultat est Clavier et Écran. Avec la même logique, vous pouvez créer un petit récapitulatif des ventes par région qui s’agrandit tout seul :

1. En F1, saisissez l’en-tête Région et en G1 l’en-tête Total.
2. En F2, saisissez =TRIER(UNIQUE(C2:C9)). Est, Nord et Sud apparaissent.
3. En G2, saisissez =SOMME.SI(C2:C9;F2#;D2:D9). L’opérateur # calcule la somme pour chaque région de la liste propagée.
4. Vérifiez les totaux : Est 970, Nord 2485 et Sud 1200.

Si une nouvelle région apparaît dans les données, la liste de F2 s’allonge et la colonne G suit, sans recopier aucune formule.

Erreurs fréquentes et comment les résoudre

• #PROPAGATION! : la plage où doivent apparaître les résultats contient quelque chose (une valeur, une formule, voire un espace) ou des cellules fusionnées. Lorsque vous sélectionnez la formule, une bordure en pointillés indique la zone nécessaire. Videz-la ou déplacez la formule.
• #CALC! : FILTRE n’a trouvé aucune ligne et vous n’avez pas renseigné le troisième argument. Ajoutez un texte comme « Aucun résultat » ou des guillemets vides.
• #VALEUR! : le tableau et la condition n’ont pas le même nombre de lignes, par exemple A2:D9 face à C2:C10.
• Formule dans un tableau Excel : les formules qui se propagent ne fonctionnent pas à l’intérieur d’un tableau créé avec Ctrl+T. Placez-les à l’extérieur ; elles peuvent tout de même lire les données du tableau.

Quand il faut nettoyer le fichier, pas seulement le consulter

UNIQUE est parfait pour consulter des valeurs distinctes, mais il ne supprime pas les lignes répétées du fichier d’origine : le tableau source reste identique. Si vous devez remettre un fichier propre, ou si votre version d’Excel ne gère pas les matrices dynamiques, vous pouvez utiliser notre outil gratuit pour supprimer les doublons : vous chargez le fichier xlsx, xls, csv ou ods et téléchargez une copie sans lignes répétées. Et pour vérifier quelles lignes ont disparu, vous pouvez comparer le fichier d’origine avec le fichier nettoyé.

Conclusion

FILTRE, TRIER et UNIQUE remplacent de nombreuses tâches qui exigeaient auparavant des filtres manuels ou des formules recopiées ligne par ligne. Saisissez une formule, laissez de l’espace libre pour le résultat et utilisez l’opérateur # pour enchaîner les calculs. Commencez par les exemples de ce guide sur vos propres données et, en quelques jours, vous créerez des rapports qui se mettent à jour tout seuls.

Questions fréquentes