Les formules qui ont tué les tableaux croisés dynamiques simples

Introduites dans Microsoft 365, les fonctions FILTRE, UNIQUE, TRIER et TRIERPAR s’appuient sur le moteur de tableaux dynamiques d’Excel. Elles renvoient des plages de taille variable qui se mettent à jour automatiquement. Résultat : pour 70 pour cent des analyses courantes, elles remplacent avantageusement tableaux croisés, filtres avancés et macros VBA.

FILTRE : sélectionner des lignes selon un critère

=FILTRE(Ventes; Ventes[Ville]="Dakar")

Renvoie toutes les lignes de la table Ventes où la ville est Dakar. La plage résultat s’agrandit ou se réduit selon les données.

Plusieurs critères

=FILTRE(Ventes; (Ventes[Ville]="Dakar")*(Ventes[Montant]>100000))

Le produit logique joue le rôle de ET, la somme joue le rôle de OU.

Critère OU

=FILTRE(Ventes; (Ventes[Ville]="Dakar")+(Ventes[Ville]="Thiès"))

UNIQUE : liste sans doublons dynamique

=UNIQUE(Clients[Ville])

Renvoie la liste des villes distinctes. Combinée à NBVAL, on obtient le décompte en une formule : =NBVAL(UNIQUE(Clients[Ville])).

TRIER et TRIERPAR

=TRIER(Ventes; 3; -1)

Trie la table Ventes par la 3ème colonne en ordre décroissant.

=TRIERPAR(Produits[Nom]; Produits[Ventes]; -1)

Trie la liste des noms de produits selon les ventes décroissantes (sans modifier la colonne source).

Cas pratique : dashboard commercial dynamique

Objectif : sur une feuille séparée, afficher automatiquement le top 10 des clients du mois avec leurs commandes. Formule :

=PRENDRE(TRIERPAR(UNIQUE(FILTRE(Ventes[Client]; MOIS(Ventes[Date])=MOIS(AUJOURDHUI()))); NB.SI(Ventes[Client]; UNIQUE(FILTRE(Ventes[Client]; MOIS(Ventes[Date])=MOIS(AUJOURDHUI())))); -1); 10)

Dense mais totalement dynamique : change automatiquement au fil des jours.

Combinaisons utiles

Extraire les doublons

=FILTRE(A:A; NB.SI(A:A; A:A)>1)

Top 5 avec montant

=PRENDRE(TRIER(Ventes; 3; -1); 5)

Cascade de listes déroulantes

Sur la feuille Validation, saisir en B1 le pays. En B2, liste des villes :

=UNIQUE(FILTRE(Base[Ville]; Base[Pays]=B1))

Puis dans Données > Validation des données > Liste > source =B2# (le # référence la plage spilled entière).

Pièges à éviter

Compatibilité : les formules spilled ne fonctionnent que sur Microsoft 365 et Excel 2021+. Fichiers partagés avec Excel 2019 affichent #IMPLICITE!. Espace nécessaire : une formule qui déborde (spill) sur une cellule occupée renvoie #PROPAGATION!. Référence structurée vs plage : préférez les tableaux (Ctrl+T) pour que les plages s’ajustent automatiquement.

Conclusion

FILTRE, UNIQUE, TRIER transforment Excel en mini SQL visuel. Toute analyse récurrente devient automatique. La combinaison avec LAMBDA et LET rend possibles des fonctions personnalisées d’analyse réutilisables, véritable pont vers la BI.

Voir aussi

Étape 1 : vérifier que votre version Excel supporte les tableaux dynamiques

Avant d’écrire une formule, il faut s’assurer que le moteur de calcul de votre Excel comprend les tableaux dynamiques. C’est une fonctionnalité introduite avec Microsoft 365 et Excel 2021. Sur Excel 2019 ou 2016, FILTRE, UNIQUE et TRIER n’existent tout simplement pas et renvoient #NOM?. Beaucoup de PME au Plateau à Abidjan tournent encore sur des licences Office 2019 piratées : c’est le premier blocage à lever.

Ouvrez Excel, allez dans Fichier puis Compte. Vous devez voir la mention Microsoft 365 ou Excel 2021. Pour tester rapidement la disponibilité, tapez dans une cellule vide la formule suivante.

=SEQUENCE(5)

Si Excel renvoie une colonne avec les chiffres 1 à 5, votre version supporte le calcul matriciel dynamique et les trois fonctions de ce tutoriel fonctionneront. Si vous obtenez #NOM?, il faut migrer vers Microsoft 365 Business Basic à environ 4 200 FCFA par utilisateur et par mois avant d’aller plus loin.

Étape 2 : isoler des lignes avec FILTRE

FILTRE remplace le couple INDEX + EQUIV + matriciel que les anciens utilisateurs Excel connaissent par cœur. Sa syntaxe officielle est FILTRE(tableau; inclure; [si_vide]). L’argument tableau est la plage à filtrer, inclure est une condition booléenne de la même hauteur, et si_vide est le texte renvoyé si aucune ligne ne correspond.

Imaginons un fichier de ventes A1:D200 avec les colonnes Date, Boutique, Produit, Montant. Pour extraire toutes les ventes de la boutique de Sandaga supérieures à 100 000 FCFA, écrivez en F2 la formule ci-dessous.

=FILTRE(A2:D200; (B2:B200="Sandaga")*(D2:D200>100000); "Aucune vente")

Excel renvoie un tableau qui se déverse automatiquement vers le bas et la droite à partir de F2. Si vous ajoutez une ligne dans le fichier source, le résultat se met à jour sans intervention. Le signe multiplication entre les deux conditions joue le rôle d’un ET logique : c’est la forme idiomatique recommandée par Microsoft pour combiner plusieurs filtres.

Étape 3 : extraire les valeurs distinctes avec UNIQUE

UNIQUE prend une plage et renvoie les valeurs sans doublon, dans l’ordre d’apparition. Sa syntaxe est UNIQUE(tableau; [par_col]; [exactement_une_fois]). Le troisième argument, peu connu, renvoie uniquement les valeurs qui n’apparaissent qu’une seule fois — pratique pour détecter des anomalies.

Pour lister les boutiques actives sans doublon dans notre fichier de ventes, placez en H2 la formule suivante.

=UNIQUE(B2:B200)

Vous obtenez la liste des boutiques uniques. Pour identifier les produits qui n’ont été vendus qu’une seule fois sur la période — utile pour traquer les références qui ne tournent pas dans un magasin de Yopougon — utilisez la variante ci-dessous.

=UNIQUE(C2:C200; FAUX; VRAI)

Le résultat est une colonne de produits à fréquence 1, sur laquelle vous pouvez ensuite enquêter avec votre équipe terrain.

Étape 4 : ordonner un résultat avec TRIER

TRIER renvoie un tableau classé selon une ou plusieurs clés. Sa syntaxe est TRIER(tableau; [index_tri]; [ordre_tri]; [par_col]). L’index commence à 1 et désigne la colonne sur laquelle trier ; l’ordre vaut 1 pour croissant et -1 pour décroissant.

Pour afficher les ventes de Sandaga triées du montant le plus élevé au plus bas, on imbrique TRIER autour de FILTRE. La cellule J2 reçoit la formule suivante.

=TRIER(FILTRE(A2:D200; B2:B200="Sandaga"); 4; -1)

Le 4 désigne la quatrième colonne de la plage filtrée, c’est-à-dire le Montant. Le -1 demande un tri décroissant. Vous obtenez un classement instantané qui se réactualise à chaque modification du fichier source : votre directeur commercial à Dakar voit toujours en haut de la liste les meilleures ventes du mois.

Étape 5 : combiner les trois fonctions pour un mini-tableau de bord

La vraie puissance vient de la composition. On peut empiler FILTRE, UNIQUE et TRIER pour produire un mini-tableau de bord en deux formules. Préparons un panneau qui affiche, pour chaque boutique, le chiffre d’affaires total trié du plus gros au plus petit.

En L2, listez les boutiques uniques, puis en M2, calculez le total et triez le tout dans la foulée.

=TRIER(UNIQUE(B2:B200))

=SOMME.SI(B2:B200; L2#; D2:D200)

Le caractère # après L2 est l’opérateur de plage déversée : il dit à Excel d’utiliser tout le résultat dynamique de UNIQUE. Si vous ajoutez une nouvelle boutique à Cotonou dans le fichier, elle apparaît automatiquement dans le tableau de bord avec son chiffre d’affaires calculé. Vous pouvez ensuite encapsuler le tout dans TRIER si vous voulez classer par CA décroissant. Lectures complémentaires sur le calcul matriciel, consultez notre guide Excel : gestion de stock multi-entrepôts avec alertes qui réutilise ces patterns. Voir aussi Excel : tableau de suivi SAV multi-agents pour un cas concret de pilotage opérationnel.

Étape 6 : gérer les erreurs et les déversements bloqués

Quand une formule dynamique ne peut pas déverser ses résultats parce qu’une cellule en aval contient déjà une valeur, Excel affiche #PROPAGATION!. Cliquez sur la cellule contenant la formule : Excel met en surbrillance la zone qui devrait recevoir le déversement et indique précisément la cellule bloquante. Videz-la et le résultat se reforme.

Pour gérer les filtres qui ne renvoient rien, utilisez systématiquement le troisième argument de FILTRE. Sans lui, FILTRE renvoie #CALC! quand la condition exclut toutes les lignes. Avec le texte de repli, vous évitez ce message technique inutile devant un client.

=FILTRE(A2:D200; B2:B200="Almadies"; "Aucune vente sur cette zone")

Le tableau dynamique affiche alors une cellule unique avec le message lisible, ce qui rend votre fichier exploitable même par un commercial non technique en agence à Bamako.

Aller plus loin avec TRIERPAR pour des tris multi-criteres

La fonction TRIER suffit pour un tri sur une seule colonne, mais des qu’il faut hierarchiser sur deux ou trois criteres (par exemple chiffre d’affaires decroissant puis nom de client croissant), la fonction TRIERPAR devient incontournable. Sa syntaxe accepte une plage source suivie de paires plage de tri / ordre, et la formule reste lisible meme sur des tableaux de 50 000 lignes. Pour une PME a Dakar qui suit ses ventes mensuelles, c’est l’outil ideal pour produire un classement commercial automatique sans VBA ni macro.

L’avantage decisif de TRIERPAR sur un tri statique en place est que la formule recalcule automatiquement des qu’une nouvelle ligne est ajoutee a la source. Vous pouvez ainsi alimenter le tableau brut au quotidien depuis la caisse ou un export du logiciel comptable, et le tableau de bord trie reste a jour sans intervention manuelle. Cette fluidite change radicalement la facon de travailler avec Excel a l’echelle d’un service entier.

Documentez chaque formule TRIERPAR critique dans une cellule voisine commentee, car la maintenance par un collegue qui n’a pas redige la formule devient sinon problematique six mois plus tard. La regle d’or : tout tableau de bord important merite une feuille notice expliquant les choix de formules en quelques lignes.

REGROUPERPAR et PIVOTER PAR : la nouvelle generation de tableaux croises

Microsoft a deploye en 2024 deux fonctions qui transforment l’experience des analyses pivot dans Excel pour Microsoft 365 : REGROUPERPAR et PIVOTER PAR. La premiere produit un regroupement vertical type SQL GROUP BY (par exemple chiffre d’affaires par commercial), la seconde une matrice croisee deux dimensions (chiffre d’affaires par commercial et par mois). Ces formules natives offrent enfin une alternative dynamique aux tableaux croises dynamiques classiques, avec l’avantage d’etre chainable dans une formule plus large et de se rafraichir instantanement.

Les arguments cles a maitriser sont la fonction d’agregation (SOMME, MOYENNE, NB) et le mode total (en haut, en bas, sans). Pour un controleur de gestion a Abidjan ou un responsable financier au Plateau, la combinaison REGROUPERPAR + TRIERPAR + FILTRE permet de batir des tableaux de pilotage sur mesure qui repondent a des questions metier specifiques sans alourdir le fichier de macros.

Verifiez la disponibilite de ces fonctions dans votre version d’Excel : elles sont reservees au canal courant Microsoft 365. Sur Excel 2021 ou 2019, restez sur les tableaux croises dynamiques classiques ou sur la combinaison SOMMEPROD + UNIQUE pour produire des resultats equivalents.

Optimiser la performance sur 100 000 lignes avec LET et LAMBDA

Quand un fichier Excel atteint 100 000 lignes et que les formules dynamiques se multiplient, la performance peut chuter brutalement. La fonction LET permet de stocker un calcul intermediaire dans une variable nommee a l’interieur d’une formule, ce qui evite que Excel ne recalcule cinq fois la meme expression. Sur un FILTRE imbrique avec une condition complexe, LET fait passer le temps de recalcul de plusieurs secondes a quelques dixiemes de secondes.

Pour les calculs reutilises dans plusieurs feuilles, encapsulez la logique dans une fonction LAMBDA nommee dans le gestionnaire de noms. Vous obtenez ainsi une bibliotheque de formules metier centralisee, plus facile a maintenir et a auditer qu’un copier-coller de formules complexes. Pour étoffer le tableau sur les fonctions de recherche modernes, consultez notre tutoriel RECHERCHEX Excel qui complete idealement ce panorama.

Eviter les pieges classiques sur les tableaux dynamiques

Les fonctions FILTRE, UNIQUE et TRIER renvoient des plages de cellules qui s’etendent automatiquement sur les cellules voisines. Cette propagation, qu’on appelle deversement, devient un piege si une autre cellule occupe l’emplacement cible : Excel renvoie alors l’erreur #DEVERSEMENT et la formule ne produit aucun resultat. La regle d’or consiste a reserver une zone vierge en dessous et a droite de la formule, equivalente a deux fois la taille initiale prevue, pour absorber les futures croissances de donnees.

Autre piege frequent : les tableaux dynamiques ne fonctionnent pas dans une feuille protegee si la zone de deversement n’est pas explicitement deverrouillee. Pour un classeur partage entre plusieurs collaborateurs a Bamako ou a Cotonou, prenez le temps de configurer la protection avant de distribuer le fichier, sinon vos formules tomberont silencieusement.

Enfin, evitez de melanger tableaux dynamiques et tableaux structures (mis sous forme de tableau via Ctrl+T) sur la meme feuille : la combinaison fonctionne mais demande des references qualifiees explicites pour ne pas casser au moindre tri. Tracez en debut de projet le choix d’organisation et tenez-le sur l’ensemble du classeur.

Documenter les choix techniques en fin de classeur

Reservez la derniere feuille de tout classeur sensible pour une notice technique : auteur, date, dependances aux versions Excel, formules critiques avec une explication d’une phrase, et procedure de mise a jour des sources de donnees. Cette discipline ajoute dix minutes au temps de production initial mais epargne des heures de retro-ingenierie a vos successeurs.