Les formules dynamiques Excel : des tableaux qui se calculent tout seuls

Pour ne pas changer d'exemple à chaque section, on garde le même fil rouge d'un bout à l'autre de 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 reste toujours le même, en extraire des sous-tableaux propres, triés et toujours à jour, sans le moindre filtre manuel ni copier-coller.

Le savais-tu ?

FILTRE, TRIER, UNIQUE et SEQUENCE sont arrivées avec Excel 365, puis reprises dans Excel 2021 et 2024. Sous Excel 2019 ou plus ancien, elles n'existent pas et renvoient l'erreur #NOM?. Pour savoir tout de suite si ta version les gère, tape =SEQUENCE(3) dans une cellule vide : si trois nombres apparaissent l'un sous l'autre, tu es bon.

Avant de te jeter sur les fonctions une par une, il y a un mécanisme à comprendre, celui qu'elles ont toutes en commun, le déversement.
C'est le petit déclic qui change tout, parce qu'une fois ce principe assimilé chaque fonction dynamique te paraît évidente, comme une simple variante de la même idée. Concrètement, il se déroule en quatre temps que tu vas reconnaître à chaque formule.

Oublier Ctrl + Maj + Entrée et les anciennes formules matricielles

Calculer sur une plage entière n'est pas neuf, puisque les formules matricielles le faisaient déjà. Il fallait seulement les valider avec Ctrl + Maj + Entrée, et Excel les entourait alors d'accolades pour signaler leur nature. Une formule dynamique se passe entièrement de ce rituel, tu la valides avec Entrée comme n'importe quelle autre formule, et c'est Excel qui décide seul de la place à remplir.

Vrai ou faux ?

« Une formule qui remplit plusieurs cellules, il faut la valider avec Ctrl + Maj + Entrée. » Faux : c'était la règle des anciennes formules matricielles, mais une formule dynamique se valide avec un simple Entrée. C'est Excel qui étale le résultat tout seul, sans la moindre combinaison de touches.

Réutiliser une plage déversée avec l'opérateur dièse

Le bloc que tu viens de voir porte un nom, la plage déversée, et sa taille change dès que la source gagne ou perd une ligne. Pointer dessus avec une plage écrite à la main te ramènerait donc au problème de départ, puisqu'il faudrait la corriger sans arrêt. Excel te donne pour ça un opérateur d'un seul caractère.

Le conseil du pro

Quand une formule en E2 déverse une liste, tu peux désigner tout le bloc d'un seul geste avec E2# : le dièse veut dire « toute la plage déversée par E2, quelle que soit sa taille ». Si le bloc grandit, E2# suit tout seul. Tu réutilises ainsi un résultat dynamique ailleurs, par exemple avec un compteur =NBVAL(E2#), sans jamais avoir à figer une plage à la main.

Maintenant que le déversement n'a plus de secret pour toi, les quatre fonctions dynamiques deviennent limpides, puisque chacune déverse son résultat de la même façon tout en répondant à un besoin bien distinct.
On voit ici l'idée et la syntaxe minimale de chacune, toujours appliquée à notre tableau de ventes, sachant que le détail complet, avec tous les arguments optionnels, t'attend sur la page dédiée à chaque fonction.

FILTRE extrait les lignes qui répondent à un critère et déverse le sous-tableau obtenu dans ta feuille, si bien qu'il se recompose tout seul à chaque changement de données, et c'est exactement ce qui remplace aujourd'hui le filtre manuel et le copier-coller. Pour ne garder que les ventes du Nord, écris ceci.

TRIER classe une plage sans toucher aux données d'origine, ce qui te donne un classement vivant qui se recalcule en direct. Sa variante TRIERPAR va plus loin en triant un tableau d'après une autre colonne, par exemple pour classer des vendeurs selon leur montant. Pour ranger la colonne des montants du plus grand au plus petit, voici la formule.

UNIQUE renvoie les valeurs distinctes d'une plage, sans aucun doublon, ce qui en fait l'outil 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, il suffit de cette ligne.

SEQUENCE génère une suite de nombres (1, 2, 3…) sur autant de lignes ou de colonnes que tu veux, ce qui la rend précieuse pour numéroter des lignes, monter un calendrier ou alimenter d'autres formules dynamiques avec une grille de valeurs prête à l'emploi. Pour numéroter de 1 à 12, tape simplement ceci.

Choisir entre une formule dynamique, le filtre du ruban et le tableau croisé dynamique

Le filtre du ruban reste le bon réflexe pour regarder. Tu coches une valeur, tu lis ce qui reste à l'écran, puis tu décoches quand tu as fini. Rien n'est écrit nulle part, le tableau d'origine ne bouge pas, et c'est exactement ce qu'on attend d'un coup d'œil. La leçon Trier et filtrer montre comment l'activer et le cumuler sur plusieurs colonnes.

La formule dynamique prend le relais dès que la vue doit rester. Elle écrit son résultat à côté du tableau, le garde affiché pendant que tu travailles ailleurs, et le recalcule à chaque saisie. Deux extractions différentes tiennent ainsi côte à côte sur la même feuille, ce qu'un filtre du ruban ne sait pas faire puisqu'il ne répond qu'à une question à la fois.

Le tableau croisé dynamique entre en jeu quand tu veux agréger. Compter, totaliser ou faire une moyenne par région et par mois reste son terrain, et la leçon Tableaux croisés dynamiques lui est consacrée. Retiens la règle courte, FILTRE extrait des lignes et le croisé résume des colonnes, et tu sauras toujours lequel des deux ouvrir.

Toutes les fonctions à tableau dynamique d'Excel sont recensées dans la catégorie Formules dynamiques du dictionnaire des formules, avec leurs cousines (LET, ETENDRE, ASSEMB.V), chacune accompagnée de sa syntaxe et d'un exemple.

Prises séparément, ces fonctions sont déjà bien utiles, mais c'est en les imbriquant qu'elles révèlent leur vraie force, parce qu'elles remplacent en une seule ligne des manipulations qui te prenaient plusieurs minutes.
Le principe est simple à retenir, le résultat d'une fonction devient tout bonnement l'entrée d'une autre, et l'ensemble se déverse d'un seul bloc dans ta feuille.

Extraire puis classer en une seule formuleSur notre tableau de ventes, tu veux la liste des ventes de la région « Nord », classée du plus gros montant au plus petit.

FILTRE extrait d'abord toutes les lignes « Nord », puis TRIER reprend ce sous-tableau et le classe sur la 3e colonne (les montants) en ordre décroissant (-1). Le résultat se déverse en un bloc et se recalcule tout seul dès qu'une vente change, sans tableau croisé dynamique, filtre manuel ni copier-coller.

Une liste propre et classée d'un coupToujours sur la colonne des régions, qui se répète d'une ligne à l'autre, tu veux cette fois la liste des régions distinctes rangées par ordre alphabétique.

UNIQUE retire les doublons tandis que TRIER classe le reste, si bien qu'une seule formule produit une liste propre qui se met à jour à chaque nouvelle ligne saisie, parfaite pour alimenter une liste déroulante ou un menu de filtres qui ne se périme jamais.

L’erreur fatale

L'erreur #PROPAGATION! est de loin la plus fréquente quand on imbrique ces fonctions, et c'est aussi la plus simple à régler. Excel encadre en pointillé la zone qu'il voudrait remplir et te montre la cellule qui bloque. Vide cette cellule, ou déplace ta formule vers une zone libre, et le déversement reprend instantanément.

FILTRE, TRIER, UNIQUE et SEQUENCE déversent des données, alors que LET et LAMBDA jouent à un tout autre niveau, puisqu'elles structurent la formule elle-même.
C'est ce qui te permet d'éliminer les répétitions et même de créer tes propres fonctions réutilisables, exactement comme tu le ferais dans un vrai langage de programmation. Rien d'effrayant pourtant, tu vas voir que les deux idées sont très naturelles.

Le conseil du pro

LET et LAMBDA demandent elles aussi Excel 365 ou Excel pour le web. Tu ne les trouveras pas dans Excel 2019 ni dans les versions antérieures, où elles renvoient l'erreur #NOM?.

LET, pour nommer tes calculs intermédiaires

LET te laisse donner un nom à des calculs intermédiaires au cœur même d'une formule, si bien qu'au lieu de répéter la même expression plusieurs fois, tu la calcules une seule fois, tu lui attribues un nom, puis tu réutilises ce nom partout ailleurs.
Le bénéfice est immédiat, ta formule redevient lisible d'un coup d'œil là où elle n'était qu'un enchevêtrement d'arguments identiques.

Sa syntaxe suit toujours le même moule, =LET(nom1; valeur1; nom2; valeur2; …; résultat_final), où le dernier argument est ce qu'Excel renvoie, tandis que les précédents définissent tes variables locales.

Prenons un cas concret, calculer le chiffre d'affaires net une fois la remise déduite, avec le CA brut en B2 et un taux de remise fixe de 10 %.

Ici ca n'est calculé qu'une seule fois, puis réutilisé pour obtenir remise, et c'est le résultat final ca-remise qui s'affiche dans la cellule. Sans LET, tu aurais dû écrire B2*C2-(B2*C2*0,1) en répétant le même calcul, source d'erreurs à la moindre modification.

LAMBDA, pour créer ta propre fonction réutilisable

LAMBDA pousse l'idée encore plus loin, puisqu'elle te laisse définir une fonction maison que tu enregistres dans le Gestionnaire de noms , et que tu appelles ensuite comme n'importe quelle fonction Excel native, sans jamais réécrire sa logique.

Sa syntaxe reste tout aussi simple, =LAMBDA(paramètre1; paramètre2; …; calcul), où les premiers arguments sont les paramètres que ta fonction recevra, et le dernier le calcul qu'elle effectue.

Imagine par exemple une fonction TTC qui convertit un montant hors taxes en montant TTC à 20 %.

Colle cette formule dans le Gestionnaire de noms (onglet Formules > Gestionnaire de noms) sous le nom TTC, et dès lors, dans n'importe quelle cellule du classeur, appelle-la simplement avec =TTC(B2).
Le vrai confort arrive le jour où le taux de TVA change, parce que tu corriges la définition une seule fois dans le Gestionnaire de noms et que toutes les cellules qui utilisent TTC se recalculent aussitôt, sans que tu touches à quoi que ce soit ailleurs.

Le combo gagnant

LET clarifie une formule complexe à un seul endroit, le temps d'une cellule, en chassant les répétitions. LAMBDA prend le relais dès que le même calcul revient à plusieurs endroits du classeur, puisque tu le définis une fois et tu l'appelles partout. Ce sont deux niveaux complémentaires d'une même logique, et tu jongleras vite de l'un à l'autre.

Tu sais désormais faire calculer et réorganiser tes données toutes seules, et l'étape d'après consiste à les faire travailler pour toi en sens inverse, en partant du résultat que tu vises pour retrouver l'entrée qui y mène.
C'est tout l'objet de la prochaine leçon, simuler des scénarios, où tu découvres la valeur cible, le solveur et les tables de données pour tester tes hypothèses sans tâtonner.