4.3Formules dynamiques : FILTRE, TRIER, UNIQUE, LAMBDA
L'essentiel en 30 secondes
- Une formule, plusieurs résultats : FILTRE extrait, TRIER ordonne, UNIQUE déduplique — le résultat déborde (spill) sur les cellules voisines et se met à jour seul.
- Le # désigne la zone débordée : si D2 contient =UNIQUE(...), D2# référence toute la liste — et #PROPAGATION! veut dire « libérez l'espace ».
- Réflexe de Wassim : imbriquer (TRIER(UNIQUE(...))) pour des listes vivantes, et nommer une petite LAMBDA (ex. TVA à 19 %) au lieu de répéter le calcul.
Mini-cas filé — Wassim produit des listes vivantes pour la clôture
Wassim, comptable à Alger, prépare la clôture : il lui faut la liste des wilayas distinctes (triée, à jour) et l'extraction des ventes d'Alger, sans refaire de filtres manuels à chaque fois. Il écrit =TRIER(UNIQUE(Ventes[Région])) et =FILTRE(Ventes;Ventes[Région]="Alger") : les deux tableaux débordent et suivent les données. Il référence la liste via D2# dans ses synthèses et crée une LAMBDA TVA19 pour les montants TTC. Troisième jalon du fil rouge : des listes qui vivent avec les données.
Suite des cas 4.1-4.2 — les tables nettoyées et modélisées de Wassim s'exploitent en formules vivantes.
1. Une formule qui renvoie un tableau
Les formules dynamiques (Microsoft 365) renvoient plusieurs résultats qui « débordent » (spill) sur les cellules voisines, sans les recopier. FILTRE extrait les lignes qui remplissent une condition ; TRIER ordonne ; UNIQUE supprime les doublons. Le résultat se met à jour tout seul quand la source change — la liste des wilayas de Wassim reste juste sans intervention.
On les imbrique : =TRIER(UNIQUE(Ventes[Région])) donne en une formule une liste dédupliquée et triée. Une seule formule remplit plusieurs cellules et suit les données : c'est la fin des manipulations manuelles répétitives.
2. Le débordement (#)
La zone de débordement se référence avec le # : si D2 contient =UNIQUE(...), alors D2# désigne toute la liste résultante — y compris quand elle grandit. On construit donc les synthèses sur D2#, pas sur une plage figée.
Si une cellule bloque le débordement, Excel renvoie l'erreur #PROPAGATION! — il suffit de libérer l'espace sous et à droite du résultat. Et on ne recopie jamais une formule dynamique : elle remplit déjà sa zone toute seule.
3. LAMBDA : créer ses fonctions
LAMBDA permet de définir sa propre fonction réutilisable, nommée dans le gestionnaire de noms, sans VBA. Ex. une fonction TVA19(x) = x*1,19 pour la TVA algérienne à 19 %. Combinée à MAP/REDUCE/BYROW, elle ouvre des calculs élégants sans macro. C'est le sommet des formules modernes.
Attention à la compatibilité : FILTRE/TRIER/UNIQUE/LAMBDA exigent Microsoft 365 ou Excel 2021+. Sur des versions antérieures, elles n'existent pas : on retombe sur filtres, TCD ou formules matricielles anciennes.
🔎 Exemple concret — 🔎 Exemple concret — Extraire, trier, dédupliquer en une formule
Table Ventes de Wassim ; liste des régions et ventes d'Alger :
=TRIER(UNIQUE(Ventes[Région])) → liste dédupliquée et triée (déborde)
=FILTRE(Ventes; Ventes[Région]="Alger") → toutes les lignes d'Alger (déborde)
Référencer le résultat ailleurs : D2# (toute la zone débordée)
// LAMBDA nommée TVA19 (Gestionnaire de noms) :
=LAMBDA(montant; montant*1,19)
= TVA19([@Montant]) → TTC avec TVA 19 %Lecture : une seule formule remplit plusieurs cellules et suit les données.
Leçon : FILTRE/TRIER/UNIQUE remplacent des manipulations manuelles répétitives.
Vocabulaire de la section
- Formule dynamique
- Formule qui renvoie un tableau « débordant » (spill), sur Microsoft 365.
Exemple simple : Le FILTRE de Wassim remplit seul des dizaines de lignes.
Ce que ce n'est pas : une formule classique à recopier vers le bas.
Réflexe : résultat multi-lignes : écrire une formule, laisser déborder.
- FILTRE
- Extrait les lignes qui remplissent une condition donnée.
Exemple simple : Wassim extrait toutes les ventes d'Alger en une formule.
Ce que ce n'est pas : un filtre manuel à refaire à chaque clôture.
Réflexe : sous-ensemble vivant : penser FILTRE.
- TRIER / UNIQUE
- Ordonne une plage (TRIER) ou supprime ses doublons (UNIQUE).
Exemple simple : Wassim produit sa liste de wilayas avec TRIER(UNIQUE(...)).
Ce que ce n'est pas : un tri manuel et une suppression de doublons en clics.
Réflexe : liste de référence : imbriquer TRIER(UNIQUE(...)).
- LAMBDA
- Définit sa propre fonction réutilisable et nommée, sans VBA.
Exemple simple : Wassim nomme TVA19 pour appliquer la TVA à 19 % partout.
Ce que ce n'est pas : une macro ou un taux répété en dur dans chaque formule.
Réflexe : calcul répété : le nommer en LAMBDA.
En pratique — En pratique — Formules qui débordent
- Écrivez =UNIQUE(colonne) pour obtenir les valeurs distinctes.
- Enrobez avec =TRIER(UNIQUE(...)) pour trier la liste.
- Extrayez un sous-ensemble avec =FILTRE(table ; condition).
- Créez une petite fonction LAMBDA nommée (ex. TVA19) et utilisez-la.
Script prêt à dire — montrer le spill à un collègue (30 s)
« C'est Wassim : regarde, une seule formule remplit toute la liste des wilayas — et quand j'ajoute une vente, elle se met à jour seule. J'extrais Alger avec FILTRE, je référence via D2#, et la TVA à 19 % est une fonction nommée. Plus de filtres refaits à la main. »
À dire en clôture : tapez la formule devant eux et ajoutez une ligne pour montrer la mise à jour.
Modèle copier-coller — Le trio dynamique + TVA
=TRIER(UNIQUE(Ventes[Région]))
=FILTRE(Ventes; Ventes[Région]="Alger")
=TRIER(UNIQUE(FILTRE(Ventes[Ville]; Ventes[Région]="Alger")))
// Dans le Gestionnaire de noms, nommer TVA19 :
// =LAMBDA(montant; montant*1,19)Liste des commerciaux
Comme Wassim : =TRIER(UNIQUE(Ventes[Commercial])) pour une liste vivante des vendeurs.
Top des impayés
=FILTRE(Ventes;Ventes[Statut]="Impayé") : l'extraction suit les règlements sans refaire de filtre.
Référencer ailleurs
Construire les synthèses sur D2# : quand la liste grandit, tout suit automatiquement.
Version sans 365
FILTRE/TRIER/UNIQUE absents : retomber sur filtre automatique, TCD ou formules matricielles anciennes.
✓ Bonnes pratiques
- Laisser de l'espace pour le débordement.
- Référencer une zone débordée avec # (D2#).
- Combiner FILTRE/TRIER/UNIQUE par imbrication.
✗ Pièges fréquents
- Cellule occupée sous le résultat → #PROPAGATION!.
- Recopier une formule dynamique inutilement.
- Utiliser ces fonctions sur une version qui ne les a pas (avant 365/2021).
Points clés à retenir
- Les formules dynamiques renvoient un tableau qui déborde (spill).
- FILTRE (extraire), TRIER (ordonner), UNIQUE (dédupliquer).
- La zone débordée se référence avec # (ex. D2#).
- LAMBDA crée des fonctions réutilisables sans VBA.
Défi 48h — appliquez demain
Sur une vraie table : écrivez =TRIER(UNIQUE(colonne)), extrayez un sous-ensemble avec FILTRE, référencez via # et nommez une LAMBDA pour un calcul répété.
- Je sais extraire, trier et dédupliquer en formules dynamiques.
- Je sais référencer une zone débordée avec # et gérer #PROPAGATION!.
- Je sais nommer et utiliser une fonction LAMBDA simple.
Questions fréquentes
Pourquoi #PROPAGATION! ?
Une formule dynamique veut « déborder » mais une cellule de la zone cible est occupée. Libérez l'espace sous/à droite du résultat, et la formule remplira toute la zone.
Ces fonctions marchent-elles sur toutes les versions ?
FILTRE/TRIER/UNIQUE/LAMBDA sont dans Microsoft 365 et Excel 2021+. Sur des versions antérieures, elles n'existent pas : on retombe sur des filtres, des TCD ou des formules matricielles anciennes (Ctrl+Maj+Entrée).
Que fait la fonction UNIQUE ?