Chapitre 3 · Avancé · Leçon 18 / 26
Formules dynamiques
Pendant des décennies, une formule Excel ne remplissait qu'une seule cellule. Pour traiter cent lignes, tu recopiais la formule cent fois. Les formules dynamiques renversent cette logique : une seule formule, écrite une fois, peut remplir un tableau entier qui grandit, rétrécit et se recalcule tout seul. C'est l'une des plus grandes évolutions d'Excel depuis l'arrivée des tableaux croisés.
Une formule dynamique renvoie plusieurs résultats à partir d'une seule cellule. Excel étale automatiquement ces résultats dans les cellules voisines : ce remplissage s'appelle le déversement (ou « spill » en anglais). Quatre fonctions phares en profitent : FILTRE extrait, TRIER classe, UNIQUE dédoublonne et SEQUENCE numérote. On les découvre une par une après avoir compris le mécanisme qu'elles partagent.
Gardons le même fil rouge dans toute la leçon : un petit tableau de ventes avec la région en colonne A, le vendeur en B et le montant en C. L'objectif : en extraire des sous-tableaux propres, triés et toujours à jour, sans filtre manuel ni copier-coller.
| A | B | C | |
|---|---|---|---|
| 1 | Région | Vendeur | Montant |
| 2 | Nord | Lina | 4 200 € |
| 3 | Sud | Karim | 3 100 € |
| 4 | Nord | Théo | 5 600 € |
| 5 | Est | Awa | 2 800 € |
À vérifier avant de commencer : ta version d'Excel
Ces fonctions demandent Excel 365, 2021 ou 2024. Sous Excel 2019 ou plus ancien, elles renvoient l'erreur #NOM?. Pour savoir si tu les as : tape =SEQUENCE(3) dans une cellule vide. Si trois nombres apparaissent, tu es bon.
Le déversement : une formule qui remplit plusieurs cellules
Avant de découvrir les fonctions une par une, il faut comprendre le mécanisme commun à toutes : le déversement. Une fois ce principe assimilé, chaque fonction dynamique devient évidente. Il se déroule en quatre temps.
Tu écris une seule formule
Dans une cellule vide, tu tapes par exemple =UNIQUE(A2:A100). Tu valides avec Entrée, dans cette unique cellule, comme une formule normale. Pas de poignée de recopie, pas de matrice à valider avec Ctrl+Maj+Entrée comme dans l'ancien Excel.
Excel déverse le résultat
La formule remplit automatiquement les cellules en dessous (ou à côté) avec tous ses résultats. Ce bloc rempli s'appelle la plage déversée. Une bordure bleue fine l'entoure quand tu cliques sur la première cellule, pour signaler qu'il s'agit d'un résultat dynamique.
Seule la première cellule contient la formule
Clique sur une cellule du bloc autre que la première : la formule y apparaît en grisé, non modifiable. La vraie formule vit dans la cellule du haut. Pour changer le résultat, tu modifies cette seule cellule, et tout le bloc suit.
Le bloc grandit et rétrécit tout seul
Ajoute une ligne à la source : la plage déversée s'étend d'une ligne. Supprime des données : elle se réduit. Tu n'as plus jamais à étendre une formule à la main quand le tableau gagne ou perd des lignes.
L'opérateur de plage déversée : le dièse
Quand une formule en E2 déverse une liste, tu peux désigner tout le bloc avec E2#. Le dièse signifie « toute la plage déversée par E2, quelle que soit sa taille ». Si le bloc grandit, E2# suit. Tu réutilises ainsi un résultat dynamique ailleurs (un compteur =NBVAL(E2#), par exemple) sans jamais figer une plage.
Les quatre fonctions dynamiques en clair
Le mécanisme du déversement en tête, les fonctions dynamiques deviennent limpides : chacune déverse son résultat, mais répond à un besoin différent. Voici l'idée et la syntaxe minimale de chacune, sur notre tableau de ventes ; le détail complet, avec tous les arguments, vit sur sa page dédiée.
FILTRE extrait les lignes qui répondent à un critère. Tu obtiens un sous-tableau qui se met à jour seul quand les données changent, le remplaçant moderne du filtre manuel et du copier-coller. Pour ne garder que les ventes du Nord :
=FILTRE(A2:C100;A2:A100="Nord")TRIER classe une plage sans toucher aux données d'origine. Tu obtiens un classement vivant qui se recalcule en direct. Sa variante TRIER.PAR trie un tableau selon une autre colonne, par exemple classer des vendeurs d'après leur montant. Pour trier la colonne des montants du plus grand au plus petit :
=TRIER(C2:C100;1;-1)UNIQUE renvoie les valeurs distinctes d'une plage, sans doublon. Idéal pour bâtir une liste de clients, de produits ou, ici, de régions à partir d'une colonne qui se répète. Pour lister les régions présentes une seule fois :
=UNIQUE(A2:A100)SEQUENCE génère une suite de nombres (1, 2, 3…) sur autant de lignes ou de colonnes que tu veux. Pratique pour numéroter, créer un calendrier, ou alimenter d'autres formules dynamiques avec une grille de valeurs prête à l'emploi. Pour numéroter de 1 à 12 :
=SEQUENCE(12)Combiner FILTRE et TRIER : la vraie puissance
Prises séparément, ces fonctions sont déjà utiles. Imbriquées, elles remplacent des manipulations qui prenaient des minutes. Le principe : le résultat d'une fonction devient l'entrée d'une autre, et l'ensemble se déverse en un seul bloc.
Extraire puis classer en une formule. Sur notre tableau de ventes, tu veux la liste des ventes de la région « Nord », classée du plus gros montant au plus petit :
=TRIER(FILTRE(A2:C100;A2:A100="Nord");3;-1)FILTRE extrait d'abord toutes les lignes « Nord ». TRIER reprend ce sous-tableau et le classe sur la 3e colonne (les montants) en ordre décroissant (le -1). Le résultat se déverse en un bloc, et il se recalcule tout seul dès qu'une vente change. Tout ça sans utiliser de tableau croisé dynamique, filtre manuel, ou copier-coller.
Une liste propre et classée. Toujours sur la colonne des régions, qui se répète, tu veux la liste des régions distinctes, par ordre alphabétique :
=TRIER(UNIQUE(A2:A100))UNIQUE retire les doublons, TRIER classe le reste. Une seule formule produit une liste propre qui se met à jour à chaque nouvelle ligne saisie. Parfait pour alimenter une liste déroulante ou un menu de filtres qui ne se périme jamais.
Le réflexe face à #DÉBORDEMENT!
C'est de loin l'erreur la plus fréquente quand on imbrique ces fonctions, et la plus simple à régler. Excel encadre en pointillé la zone qu'il voudrait remplir et te montre la cellule qui bloque. Vide-la, ou déplace ta formule vers une zone libre, et le déversement reprend instantanément.
Questions fréquentes sur les formules dynamiques
Une formule dynamique est une formule qui renvoie plusieurs résultats à partir d'une seule cellule. Au lieu d'écrire une formule par ligne, tu en écris une seule et Excel remplit automatiquement toutes les cellules nécessaires en dessous ou à côté. Ce remplissage automatique s'appelle le déversement, ou « spill » en anglais.
Les formules dynamiques (FILTRE, TRIER, UNIQUE, SEQUENCE) sont arrivées avec Excel 365 et sont présentes dans Excel 2021 et 2024. Excel 2019 et les versions plus anciennes ne les reconnaissent pas : la cellule affiche alors une erreur #NOM?. Google Sheets dispose d'équivalents proches.
L'erreur #DÉBORDEMENT! (spill en anglais) apparaît quand une formule dynamique veut déverser ses résultats mais qu'une cellule de la zone de destination est déjà occupée. Excel a besoin de cases vides pour étaler le résultat. Vide les cellules sous la formule et le tableau se remplit aussitôt.
Le dièse, écrit après une cellule comme E2#, désigne toute la plage déversée par la formule dynamique de cette cellule, quelle que soit sa taille. Si le résultat grandit ou rétrécit, la référence E2# suit toute seule. C'est l'opérateur de plage déversée, indispensable pour réutiliser un résultat dynamique ailleurs.
Oui, et c'est là que ces fonctions deviennent puissantes. Tu peux filtrer puis trier en imbriquant les fonctions : =TRIER(FILTRE(...)). Le résultat de FILTRE devient l'entrée de TRIER, et l'ensemble se déverse en un bloc. Tu obtiens un sous-tableau extrait, trié et recalculé en direct, sans tableau croisé ni copier-coller.
Non, justement. Une formule dynamique se suffit à elle-même : tu l'écris une fois dans la première cellule et elle remplit toute la zone. Inutile de tirer la poignée de recopie. Si tu recopies une formule dynamique sur plusieurs lignes, tu crées des doublons inutiles et souvent des erreurs #DÉBORDEMENT!.
Tu veux passer aux formules dynamiques pour de bon ?
Le Dojo Club te donne accès à des exercices guidés sur FILTRE, TRIER et UNIQUE, des lives experts chaque semaine et une communauté active pour transformer tes tableaux figés en tableaux qui se recalculent tout seuls.
Rejoindre le Dojo