Les 37 fonctions formules dynamiques d'Excel
Les formules dynamiques d'Excel 365 renvoient des tableaux de résultats qui se déversent automatiquement dans les cellules adjacentes. Elles remplacent les filtres manuels, les tris, les extractions de doublons et les formules matricielles complexes par une syntaxe directe et lisible.
Tu filtres tes données en appliquant des filtres automatiques, tu tries tes listes à la main, tu relies tes tableaux croisés dynamiques chaque lundi matin ? Les formules dynamiques font tout ça en une seule formule, et le résultat se met à jour seul quand tes données changent.
Le principe est simple. Avec les anciennes versions d'Excel, extraire les clients d'une région donnée demandait un filtre ou une formule matricielle entrée avec Ctrl+Maj+Entrée. Avec FILTRE, tu écris =FILTRE(A:C; B:B="Nord") et c'est fait. Le tableau de résultats apparaît directement sous la formule : c'est ce qu'Excel appelle le déversement, ou spill.
La vraie force de ces formules, c'est de pouvoir les combiner. Tu peux imbriquer TRIER dans FILTRE pour obtenir une liste filtrée et triée en une seule expression, ou entourer le tout avec UNIQUE pour n'en garder que les valeurs distinctes. Maîtrise les quatre fonctions clés, et tu diviseras par dix le temps passé sur tes analyses.
Déversement automatique
Une formule remplit autant de cellules que nécessaire, sans recopie
Syntaxe simplifiée
Fini le Ctrl+Maj+Entrée des formules matricielles classiques
Formules combinables
FILTRE, TRIER et UNIQUE s'imbriquent pour des analyses complexes en une ligne
Les fonctions essentielles
Ces six fonctions couvrent la grande majorité des besoins en manipulation de tableaux. Commence par FILTRE et UNIQUE, qui résolvent à eux seuls une part importante des tâches répétitives du quotidien.
- 1
FILTRE extrait les lignes d'un tableau qui correspondent à un ou plusieurs critères.
- 2
TRIER trie une plage par une colonne au choix, en ordre croissant ou décroissant.
- 3
UNIQUE renvoie la liste des valeurs distinctes d'une plage, sans doublons.
- 4
SEQUENCE génère une séquence de nombres consécutifs sur autant de lignes et colonnes que tu veux.
- 5
LET définit des variables nommées dans une formule pour la rendre plus lisible et éviter les répétitions.
- 6
LAMBDA crée une fonction personnalisée réutilisable sans VBA, directement dans la barre de formule.
Cas d'usage courants
Reporting automatisé. Alimente un tableau de synthèse avec FILTRE en combinant plusieurs critères pour que la vue se rafraîchisse dès que les données sources changent, sans aucune manipulation manuelle.
Listes déroulantes dynamiques. Génère la source d'une liste de validation de données avec UNIQUE pour qu'elle affiche uniquement les valeurs réellement présentes dans ta colonne, sans jamais laisser de doublons.
Top N résultats. Affiche les dix meilleures ventes ou les cinq tickets les plus élevés en imbriquant TRIER et PRENDRE, sans tableau croisé ni mise à jour manuelle.
Numérotation automatique. Crée une numérotation de lignes, une grille de référence ou une table de multiplication avec SEQUENCE en quelques secondes, sans recopier la moindre cellule.
Formules nommées réutilisables. Encapsule une logique de calcul complexe dans LAMBDA et donne-lui un nom dans le Gestionnaire de noms pour l'appeler comme une fonction native sur tout ton classeur.
Consolidation de tableaux. Empile plusieurs plages verticalement avec EMPILER ou côte à côte avec ASSEMB.H pour consolider des exports issus de plusieurs feuilles en un seul tableau unifié.
Toutes les fonctions formules dynamiques (37)
Questions fréquentes
Le déversement (spill) désigne le comportement d'une formule dynamique qui renvoie plusieurs valeurs. Excel les place automatiquement dans les cellules adjacentes sans qu'on ait à saisir quoi que ce soit. Si une cellule est occupée dans la zone de déversement, Excel affiche une erreur #DÉVERSEMENT! jusqu'à ce que l'espace soit libéré.
Non. FILTRE, TRIER, UNIQUE, SEQUENCE, LET et LAMBDA sont exclusives à Excel 365 et Excel 2021. Sur Excel 2019 ou antérieur, ces formules renvoient une erreur. Pour des classeurs partagés avec des utilisateurs sur des versions anciennes, il faut prévoir des alternatives comme les formules matricielles classiques.
Il suffit d'imbriquer les fonctions. Par exemple =TRIER(FILTRE(A2:C100; B2:B100="Oui"); 3; -1) filtre d'abord les lignes où la colonne B vaut « Oui », puis trie le résultat par la troisième colonne en ordre décroissant. Le tout en une seule cellule.
LET sert à nommer des calculs intermédiaires dans une formule unique pour éviter de répéter les mêmes expressions et gagner en lisibilité. LAMBDA va plus loin en permettant de créer une fonction réutilisable que tu peux appeler partout dans le classeur. LET est interne à une formule, LAMBDA est pensé pour être nommé et partagé.
Les formules dynamiques peuvent lire des tableaux structurés avec la notation @[Colonne]. En revanche, une formule à déversement ne peut pas elle-même résider à l'intérieur d'un tableau structuré, car les tableaux ne supportent pas les plages de déversement. La solution courante est de placer la formule hors du tableau et de référencer le tableau structuré comme source.
Bloqué sur une formule Excel ?
Pose ta question à notre assistant Excel IA, il te sort la bonne formule en quelques secondes.
Essayer l'assistant IAGratuit · 10 questions par mois