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.