Fonctions dynamiques : FILTRE, TRIER, UNIQUE, LET et plages à déversement

Jusqu’ici, pour extraire les factures impayées d’un client, il fallait un filtre manuel, une macro ou des formules matricielles réservées aux spécialistes. Les fonctions dynamiques changent la donne : une seule formule renvoie une liste entière qui se met à jour seule. Pour les comptables, c’est un gain considérable sur les états récurrents : liste des clients, impayés, écritures d’un compte, top 10 des fournisseurs.

Ce que vous saurez faire à la fin de l’épisode

• dresser une liste sans doublon avec UNIQUE ;
• extraire des lignes avec FILTRE selon un ou plusieurs critères ;
• trier un résultat avec TRIER ;
• référencer une plage déversée avec l’opérateur # ;
• alléger une formule avec LET.

Version d’Excel. FILTRE, TRIER, UNIQUE et LET sont disponibles dans Microsoft 365 et dans les versions récentes d’Excel (2021 et suivantes). Avec une version plus ancienne, ces formules ne fonctionneront pas : privilégiez alors les tableaux croisés dynamiques (épisode 25) ou les formules classiques.

Le cas : quatorze factures

Atlas Négoce suit ses factures de vente du premier trimestre : date, client, montant hors taxes et statut.

Figure 1 : les factures. Repère 1 : une facture impayée.

Total facturé : 190 600 HT, dont 80 400 impayés.

UNIQUE : la liste des clients

=UNIQUE(Factures!B2:B15)

On saisit la formule une seule fois, dans A2. Excel écrit les cinq clients dans A2:A6 : c’est le déversement (le résultat « se déverse » vers le bas). Plus besoin de dédoublonner à la main : si un nouveau client apparaît dans la source, la liste s’allonge, à condition que la plage source suive (voir plus loin).

L’opérateur # : une formule qui suit la liste

Pour calculer le total par client, on écrit en B2 :

=SOMME.SI.ENS(Factures!C2:C15;Factures!B2:B15;A2#)

A2# désigne toute la plage déversée par la formule de A2. Comme le critère est une liste, SOMME.SI.ENS renvoie un total par client et se déverse à son tour :

Figure 2 : clients et totaux. Repère 1 : la formule UNIQUE ; repère 2 : les totaux déversés.

La somme des cinq totaux est bien 190 600. Si la liste de clients passe de cinq à six, les totaux passent de cinq à six, sans toucher à la formule : c’est la différence avec un tableau où il faut recopier la formule vers le bas.

FILTRE : extraire les lignes

On veut les factures impayées de plus de 10 000 :

=FILTRE(Factures!A2:D15;(Factures!D2:D15="Impayée")*(Factures!C2:C15>10000);"Aucune")

Le deuxième argument est un test sur chaque ligne ; la multiplication de deux tests joue le rôle d’un « ET » (les deux conditions doivent être vraies ; pour un « OU », on additionne). Le troisième argument est le texte affiché si rien ne correspond. En enveloppant FILTRE dans TRIER, on classe le résultat par montant décroissant (colonne 3, ordre −1) :

=TRIER(FILTRE(Factures!A2:D15;(Factures!D2:D15="Impayée")*(Factures!C2:C15>10000);"Aucune");3;-1)

Figure 3 : les impayés importants. Repère 1 : la zone déversée ; repère 2 : le tri décroissant par montant.

Ces quatre lignes totalisent 64 400 sur les 80 400 d’impayés : près de 80 % de l’encours impayé tient en quatre factures, dont deux d’un même client, Atlas Export, qui pèse 40 500 d’impayés. C’est exactement la liste que l’on voudrait pour une relance ciblée (voir la balance âgée).

LET : donner un nom aux calculs

Le taux d’impayés est impayés ÷ total. Sans LET, la formule répète les calculs ; avec LET, on nomme les étapes :

=LET(total;SOMME(Factures!C2:C15);
     impaye;SOMME.SI(Factures!D2:D15;"Impayée";Factures!C2:C15);
     impaye/total)

LET prend des paires (nom ; valeur), puis le calcul final qui les utilise. Résultat : 80 400 ÷ 190 600 = 42,2 %.

Figure 4 : le taux d’impayés. Repère 1 : la formule LET, lisible dans la barre de formule.

Avantages de LET : la formule se lit comme une phrase (« total, impayé, impayé sur total »), un calcul coûteux n’est fait qu’une fois, et une modification (changer la plage source) ne se fait qu’à un endroit.

Les pièges

1. Une zone de déversement bloquée. Si une cellule voisine contient déjà quelque chose, Excel affiche une erreur de déversement : videz les cellules concernées.
2. Des plages fixes. B2:B15 ne s’agrandit pas quand on ajoute une facture. Transformez les données en tableau (Ctrl+T, épisode 8) et utilisez les références structurées (Factures[Client]) : les formules suivent le tableau.
3. Le partage du classeur. Une version d’Excel plus ancienne ne comprend pas ces fonctions : prévenez les destinataires ou collez les résultats en valeurs.
4. L’ET et le OU. Multiplier (ET) et additionner (OU) les tests : une inversion donne des listes fausses mais plausibles.
5. Ne pas contrôler le résultat. Vérifiez toujours un total : ici, la somme des totaux par client doit être égale au total des factures.

À vous de jouer

Dans une feuille vierge du classeur d’exercice (les onglets de résultats sont des valeurs de référence) :

1. Saisissez les formules UNIQUE, SOMME.SI.ENS avec #, et TRIER(FILTRE(…)). Comparez avec les résultats attendus.
2. Abaissez le seuil de 10 000 à 8 000 : combien de factures sont extraites ?
3. Transformez la source en tableau et ajoutez une facture : vos formules s’actualisent-elles ?
4. Modifiez la formule LET pour calculer le taux d’impayés du seul client Atlas Export.

Correction. (2) Avec un seuil de 8 000, la facture de 9 200 (Sud Négoce, 13/02) s’ajoute : cinq lignes, 24 000, 16 500, 12 500, 11 400 puis 9 200. (3) Oui, si les formules utilisent les références structurées du tableau : les plages suivent l’ajout, et la liste des clients, les totaux et les impayés se recalculent. Avec des plages fixes comme B2:B15, la nouvelle ligne est ignorée. (4) Atlas Export : 70 500 facturés dont 40 500 impayés (24 000 + 16 500), soit 57,4 %. En LET, on ajoute un paramètre client et l’on remplace SOMME et SOMME.SI par SOMME.SI.ENS avec le critère du client.

À retenir

• UNIQUE, FILTRE et TRIER renvoient des listes qui se déversent dans les cellules voisines.
• A2# désigne toute la plage déversée : les formules suivent la liste.
• Multipliez les tests pour un ET, additionnez-les pour un OU.
• LET nomme les calculs intermédiaires pour des formules plus lisibles.
• Vérifiez la version d’Excel des destinataires, et contrôlez les totaux.

Épisode 49 : fiabiliser un classeur, avec protection, audit des formules et erreurs classiques.