4.3Formules dynamiques : FILTRE, TRIER, UNIQUE, LAMBDA
Depuis Excel 365, une formule peut renvoyer plusieurs résultats à la fois : c'est la « propagation » (spill). =UNIQUE(A2:A100) affiche d'un coup la liste des valeurs distinctes ; =FILTRE(Ventes;Ventes[Ville]="Paris") extrait toutes les lignes de Paris — des résultats vivants, qui se mettent à jour avec la source.
Vous apprendrez le quatuor FILTRE, TRIER, UNIQUE, SEQUENCE, la référence « # » qui désigne toute une plage propagée, et l'ovni LAMBDA : la possibilité de créer vos propres fonctions nommées, sans une ligne de VBA. Ces formules dynamiques remplacent des pans entiers de manipulations manuelles.
Vocabulaire de la section
- Propagation (spill)
- Une formule saisie dans UNE cellule déborde sur ses voisines pour afficher tous ses résultats, encadrés en bleu.
- FILTRE / TRIER / UNIQUE
- =FILTRE(plage;condition) extrait les lignes ; =TRIER(plage) ordonne ; =UNIQUE(plage) dédoublonne — combinables entre elles.
- Référence #
- A2# désigne toute la plage propagée issue de A2 : les formules en aval suivent la taille du résultat.
- #EPARS!
- L'erreur « ça déborde » : des cellules occupées bloquent la propagation. Libérez la zone sous/à droite de la formule.
- LAMBDA
- Créer sa propre fonction : =LAMBDA(x;y;x*y) puis nommée via le Gestionnaire de noms pour être appelée comme une fonction native.
Où définit-on une mesure DAX comme Total CA := SUM(Ventes[Montant]) ?
En pratique — Un rapport auto-construit en 3 formules
- Sur votre tableau Ventes, en F2 : =UNIQUE(Ventes[Ville]) → la liste des villes apparaît et se maintient seule.
- En G2 : =SOMME.SI.ENS(Ventes[Montant];Ventes[Ville];F2#) → le CA de CHAQUE ville, en une formule (notez le #).
- En I2 : =TRIER(FILTRE(Ventes;Ventes[Montant]>1000);4;-1) → les grosses ventes, triées par montant décroissant.
- Ajoutez une vente dans le tableau source : listes, CA et extraction se mettent à jour instantanément.
Points clés à retenir
- Une formule dynamique renvoie une plage entière de résultats, toujours synchronisée avec la source.
- FILTRE + TRIER + UNIQUE se combinent comme des briques : extraction → tri → dédoublonnage.
- A2# référence tout le résultat propagé : vos formules en aval s'adaptent à sa taille.
- #EPARS! = la zone de débordement est occupée : libérez les cellules qui bloquent.
Questions fréquentes
Ces fonctions marchent-elles sur toutes les versions d'Excel ?
Non : FILTRE, TRIER, UNIQUE, SEQUENCE et LAMBDA nécessitent Excel 365 ou Excel 2021+ (et fonctionnent dans Excel pour le web). Sur les versions antérieures, elles renvoient #NOM?.
À quoi sert concrètement LAMBDA ?
À nommer un calcul récurrent : créez une fonction TVA(montant;taux) utilisée partout dans le classeur. Plus lisible, un seul endroit à corriger, sans macro ni .xlsm.