Fonctions dynamiques modernes

Définition

Les fonctions dynamiques renvoient un tableau de résultats qui se répand automatiquement dans les cellules voisines : TRIER, TRIERPAR, UNIQUE, FILTRE et TABLEAU.ALEA. Disponibles depuis Microsoft 365, elles remplacent les anciennes formules matricielles validées par Ctrl+Maj+Entrée et sont testées dans l'examen MO-211.

Le principe de propagation

Une formule dynamique saisie dans une seule cellule renvoie plusieurs valeurs qui se répandent vers le bas ou la droite. La zone occupée est entourée d'un cadre bleu et se référence par un dièse :

=FILTRE(A2:C20;B2:B20="Oui")
=SOMME(D2#)

La référence D2# désigne toute la zone propagée. Si une cellule de cette zone contient déjà une valeur, la formule affiche l'erreur #PROPAGATION (#SPILL en anglais). Il faut vider la cellule bloquante ou la déplacer.

Les fonctions dynamiques

Fonction	Syntaxe	Description
TRIER (SORT)	=TRIER(plage;[index_tri];[ordre])	Trie une plage par une ou plusieurs colonnes
TRIERPAR (SORTBY)	=TRIERPAR(plage;plage_tri1;[ordre1];...)	Trie selon une ou plusieurs plages externes
UNIQUE	=UNIQUE(plage;[par_colonne];[exactement_une_fois])	Renvoie les valeurs distinctes d'une plage
FILTRE (FILTER)	=FILTRE(plage;condition;[si_vide])	Renvoie les lignes répondant à une condition
TABLEAU.ALEA (RANDARRAY)	=TABLEAU.ALEA(lignes;[colonnes];[min];[max];[entier])	Génère un tableau de nombres aléatoires

FILTRE

FILTRE renvoie toutes les lignes d'une plage qui répondent à une condition portant sur une colonne de même taille. La condition se combine avec * pour le ET logique et + pour le OU logique :

=FILTRE(A2:C20;(A2:A20="Nord")*(C2:C20>500);"Aucune vente")

Le 3e argument affiche un message au lieu de l'erreur #CALC! quand aucun résultat ne correspond. Sans lui, la formule renvoie #CALC!.

TRIER, TRIERPAR, UNIQUE et TABLEAU.ALEA

TRIER trie une plage par colonne : =TRIER(A2:C20;3;-1) trie par la 3e colonne en ordre décroissant. TRIERPAR trie selon une plage externe, utile quand les données à trier et les critères ne sont pas dans la même plage : =TRIERPAR(A2:C20;C2:C20;-1). UNIQUE renvoie les valeurs distinctes ; le 3e argument VRAI ne garde que les valeurs présentes exactement une fois. TABLEAU.ALEA génère des nombres aléatoires : =TABLEAU.ALEA(10;1;1;100;VRAI) produit 10 entiers entre 1 et 100. C'est une fonction volatile, les valeurs changent à chaque recalcul.

Exemple concret

Un tableau des ventes en A2:C5 contient Région, Vendeur et Montant.

Région	Vendeur	Montant
Nord	Dupont	1200
Sud	Martin	800
Nord	Martin	950

=FILTRE(A2:C5;A2:A5="Nord")      renvoie les deux lignes Nord
=UNIQUE(A2:A5)                    renvoie Nord et Sud
=TRIER(A2:C5;3;-1)                classe par montant décroissant