Comment construire un moteur de tableau de bord « pro »

Ne perdez plus des heures à trier, dédupliquer et filtrer manuellement vos données dans Excel. Au lieu de cela, combinez FILTER, UNIQUE et SORTBY pour créer un moteur de données autonettoyant qui effectue tout le travail à partir d'une seule cellule et n'a jamais besoin d'être mis à jour.

Comment fonctionne le trio FILTER-UNIQUE-SORTBY

Pour créer une liste dans Excel filtrée, dédupliquée et classée par ordre alphabétique à la fois, vous devez empiler ces trois fonctions :

=SORTBY(
UNIQUE(FILTER(array,include,(if_empty))),
UNIQUE(FILTER(array,include,(if_empty))),
(sort_order)
)

Les formules imbriquées peuvent rapidement devenir un mur de texte. Pour créer le saut de ligne illustré ci-dessus, appuyez sur Alt+Entrée dans la barre de formule. Cela ne change pas le fonctionnement de la formule, mais cela facilite grandement la saisie et l'audit.

À première vue, vous remarquerez peut-être qu’une partie de la formule se répète. Ce n'est pas une erreur. Pour que ce moteur fonctionne sans erreurs, vous devez refléter la logique. J'expliquerai exactement pourquoi nous devons faire cela à l'étape 4 ci-dessous.

Étape 1. Le noyau : FILTRE

Tout commence avec FILTER, la salle des machines qui identifie les données brutes que vous souhaitez extraire :

FILTER(array,include,(if_empty))

• tableau est la plage de cellules ou le tableau que vous souhaitez filtrer.
• inclure est le critère qui indique à Excel ce qu'il faut conserver dans le filtre.
• (si_vide) (facultatif) est l'endroit où vous spécifiez ce qu'Excel doit afficher si aucune correspondance n'est trouvée.

Au lieu de regarder un tableau volumineux et désordonné, FILTER identifie uniquement les lignes qui répondent à vos critères. Il ignore le bruit et transmet uniquement les données pertinentes à l'étape suivante. De plus, contrairement au bouton de filtre standard, cela ne masque pas les lignes de votre table principale : il les extrait vers un nouvel emplacement.

Étape 2. La couche intermédiaire : UNIQUE

Ensuite, UNIQUE agit comme un « videur », supprimant tous les doublons du tableau filtré qu'il vient de recevoir :

UNIQUE(FILTER(...))

Dans ce trio, le résultat complet de la fonction FILTER sert de seul argument dont UNIQUE a besoin. Au moment où cette couche est terminée, vous disposez d’une liste vierge dans laquelle chaque élément apparaît exactement une fois. C'est mieux que d'utiliser l'outil Supprimer les doublons, car cela n'affecte pas votre table d'origine et ne nécessite pas d'actualisation manuelle si votre source change.

Étape 3. La coque extérieure : SORTBY

La dernière étape est l'emballage. Bien que UNIQUE soit idéal pour supprimer les doublons, il conserve les données dans leur ordre d'origine. C'est là que SORTBY s'avère utile :

=SORTBY(array,sort_array,(sort_order))

• tableau est le résultat des deux étapes précédentes.
• tableau_de_tri est la logique qu'Excel utilise pour trier la liste (voir l'étape 4 ci-dessous).
• (ordre_tri) (facultatif) indique à Excel le sens du tri. Utilisez 1 pour ascendant (AZ) ou -1 pour descendant (ZA). Par contre, laissez-le vide pour déclencher la valeur par défaut (ascendante).

Bien que SORT fonctionne bien pour les listes de base, SORTBY est le choix le plus performant pour deux raisons. Premièrement, lors de l'empilement de fonctions telles que UNIQUE et FILTER, SORTBY gère mieux les résultats dispersés sans perdre la trace de la structure des données. Deuxièmement, SORT vous permet uniquement de trier par colonnes dans votre résultat final, tandis que SORTBY vous permet de trier votre liste en fonction d'une colonne complètement différente qui ne figure même pas dans votre résultat.

Étape 4. La logique du miroir

Excel a une règle stricte : les données que vous triez et les critères selon lesquels vous triez doivent avoir exactement la même hauteur. Si votre liste unique filtrée comporte cinq lignes, vos instructions de tri doivent également comporter cinq lignes. Ainsi, si vous essayiez de trier ces cinq noms à l'aide de votre table source d'origine, les hauteurs ne correspondraient pas et la formule serait brisée.

C'est pourquoi vous devez refléter la logique en répétant la chaîne UNIQUE(FILTER(…)) comme deuxième argument : vous vous assurez que les dimensions correspondent parfaitement à chaque fois.

=SORTBY(
UNIQUE(FILTER(array,include,(if_empty))), <-- the data to sort 
UNIQUE(FILTER(array,include,(if_empty))), <-- the instructions to sort by 
(sort_order)
)

Le trio magique en action : La liste des clients

Maintenant que je vous ai montré le plan, voyons le moteur en action. Imaginez que vous disposez d'un tableau Excel nommé T_Sales et que vous souhaitez une liste alphabétique dynamique des clients uniques pour la région sélectionnée dans la cellule G2.

Lorsque vous tapez cette formule dans la cellule G4, la formule transmet ses résultats aux cellules ci-dessous :

=SORTBY(
UNIQUE(FILTER(T_Sales(Company),T_Sales(Region)=G2,"No Matches")),
UNIQUE(FILTER(T_Sales(Company),T_Sales(Region)=G2,"No Matches")),
1
)

Faire référence à une cellule plutôt que de coder en dur le filtre dans la formule signifie que vous pouvez facilement passer à une autre région. Cela rend également votre moteur plus facile à comprendre pour les autres : ils n'ont pas besoin de savoir comment fonctionne SORTBY ; ils ont juste besoin de savoir saisir ou choisir une région dans une liste déroulante dans la cellule G2.

Étant donné que l'ensemble de données d'origine se trouve dans un tableau Excel, le moteur de données est évolutif. Si vous ajoutez de nouvelles lignes de données de ventes au bas du tableau, la formule détecte automatiquement la plage étendue. C'est l'un des avantages de l'utilisation de fonctions qui diffusent des tableaux dynamiques à partir de la cellule dans laquelle vous avez tapé la formule. De plus, vous pouvez référencer l'intégralité du résultat ailleurs en ajoutant simplement un hachage (#), également appelé opérateur de plage renversée, à la référence :

G4#

Derniers pièges à éviter

Pour que votre nouveau moteur de données fonctionne correctement, gardez ces trois règles à l'esprit :

• Dégager le chemin : Les formules de tableaux dynamiques ont besoin d'espace vide pour diffuser leurs résultats vers le bas. Si un morceau de texte égaré ou un autre tableau gêne, Excel lancera un #SPILL ! erreur.
• Aucune formule dans les tableaux : Bien que vous deviez utiliser un tableau comme données source, votre formule doit se trouver dans une cellule normale. En effet, les tableaux Excel ne prennent pas en charge le débordement dynamique.
• Laissez un tampon d'une colonne : Lorsque vous analysez des données en dehors d'un tableau Excel, laissez toujours un espace d'une colonne entre le tableau et l'endroit où vous tapez. Sinon, Excel pense que vous ajoutez plus de données à votre tableau, il « récupérera » donc votre analyse.

---

En vous éloignant des outils Excel manuels et en adoptant des tableaux dynamiques, vous créez des systèmes qui évoluent parallèlement à vos données. Ce trio est le point de départ idéal pour explorer d'autres combinaisons de fonctions Excel, telles que INDEX avec XMATCH, IF avec AND et OR et EOMONTH avec SEQUENCE, qui peuvent automatiser davantage vos tâches quotidiennes.