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

Le déversement, une formule qui remplit plusieurs cellules

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.

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.

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.

Les quatre fonctions dynamiques en clair

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.

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.

Combiner FILTRE et TRIER pour la vraie puissance

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.

LET et LAMBDA, créer tes propres formules

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.