Synthétiser les premiers meilleurs résultats

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Synthèse des meilleurs résultats

Consolider les premiers meilleurs scores est une prouesse rendue possible par les calculs matriciels. C'est ce que nous proposons d'apprendre et de comprendre au travers d'un cas pratique précis.



Dans l'exemple finalisé illustré par la capture ci-dessus, nous travaillons sur une base de données d'articles. Ceux-ci ont généré des chiffres d'affaires respectifs. Ils appartiennent tous à une catégorie mentionnée en dernière colonne. Pour chaque catégorie, nous consolidons la somme des trois meilleurs chiffres d'affaires. Et pour plus de clarté, nous réalisons l'extraction des références concernées pour chacun des groupes.

Source et objectif
Pour réaliser cette étude, nous devons commencer par réceptionner cette base de données.

• Télécharger le classeur sommes-premieres-grandes-valeurs.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Sur la droite de la base de données, vous constatez donc la présence d'un petit tableau de synthèse. Il est vide à ce stade.



Dans la première colonne vide (Meilleurs), sont attendues les sommes des trois plus grands chiffres d'affaires par catégorie. Ces dernières sont énumérées dans la colonne voisine de gauche. Nous allons pouvoir exploiter ces références dans les formules. Puis, les trois dernières colonnes sur la droite doivent offrir le détail. Il s'agit de livrer dans l'ordre, et toujours par catégorie, les références des articles ayant généré ces meilleurs chiffres.

• En haut à gauche de la feuille Excel, cliquer sur la flèche de la zone Nom pour la déployer,

Vous notez que chaque colonne du tableau est identifiée par son titre. Nous exploiterons ces noms pour désigner les matrices et simplifier la syntaxe des formules.

Sommer les premiers meilleurs scores
Au même titre que la fonction Petite.Valeur, nous connaissons déjà la fonction Grande.Valeur :

=Grande.Valeur(Plage_de_cellules; Rang)

Sur une plage de cellules passée en premier argument, elle permet d'extraire l'une des plus grandes valeurs, en fonction du rang indiqué en second paramètre. Il peut s'agir de la première (1), de la deuxième (2) ou des suivantes. Dans notre cas, il s'agit d'extraire les trois premières grandes valeurs. C'est une des raisons pour laquelle nous allons engager un raisonnement matriciel. Ce dernier sera capable d'analyser toutes les données de la colonne en fonction des multiples rangs indiqués.

Cependant, ces premières plus grandes données doivent être localisées dans leurs catégories respectives. Un critère doit donc être satisfait sur la colonne concernée. De fait, nous exploiterons la fonction conditionnelle Si. Comme il s'agit d'additionner ces valeurs, l'ensemble de la syntaxe doit être englobée dans la fonction Somme. Et une fois encore, comme les lignes respectives des matrices doivent être analysées ensemble, nous transformerons cette formule en formule matricielle.

• Sélectionner le premier résultat attendu, soit la cellule G6,
• Taper le symbole égal (=) pour débuter la syntaxe,
• Saisir la fonction d'addition suivie d'une parenthèse, soit : Somme(,
• Saisir la fonction des meilleures données, suivie d'une parenthèse, soit : Grande.Valeur(,

Avant de définir la plage d'action, il est impératif de poser le critère matriciel sur la catégorie à respecter.

• Taper la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Désigner la colonne des catégories par son nom, soit : Catégorie,
• Taper le symbole égal (=) pour annoncer le critère à honorer,
• Désigner la première catégorie du tableau de bord avec ses coordonnées, soit : F6,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Désigner la plage des valeurs à sommer par son nom, soit : CA,

De cette manière, l'addition des chiffres d'affaires ne doit être faite que pour les montants de la catégorie stipulée dans le critère. Et comme cette contrainte est inscrite dans la fonction Grande.Valeur, nous allons bientôt pouvoir lui indiquer de ne retenir que les trois meilleures. Avant cela, nous devons terminer la syntaxe de la fonction Si.

• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir deux guillemets ('') pour ignorer le CA des enregistrements non concordants,
• Fermer la parenthèse de la fonction Si,
• Taper un point-virgule (;) pour passer dans l'argument du rang de la fonction Grande.Valeur,
• Saisir alors la syntaxe suivante : {1;2;3},

C'est une astuce que nous avons déjà commise dans des cas pratiques précédents. Les accolades annoncent l'énumération des rangs. Ici, nous stipulons les trois premiers pour obtenir les trois premières grandes valeurs à sommer. C'est la transformation ultérieure de la formule en calcul matriciel qui autorisera à les considérer tous dans l'enchaînement.

• Fermer la parenthèse de la fonction Grande.Valeur,
• Puis, fermer la parenthèse de la fonction Somme,
• Enfin, valider nécessairement la formule par le raccourci clavier CTRL + MAJ + Entrée,

Le premier résultat tombe. La syntaxe du calcul matriciel que nous avons construit est la suivante :

{=SOMME(GRANDE.VALEUR(SI(Catégorie = F6; CA; ''); {1;2;3}))}

Les accolades qui l'encadrent imposent à cette formule de raisonner sur des matrices et non chronologiquement, comme ce serait le cas pour un calcul classique.



Grâce à une mise en forme conditionnelle repérant les trois meilleurs chiffres d'affaires dans leur couleur, vous pouvez rapidement confirmer la parfaite cohérence du résultat fourni. Pour cela, il suffit de regrouper dans la même sélection les trois chiffres d'affaires en vert.



Dès lors, la barre d'état, en bas de la fenêtre Excel, livre des informations de synthèse dont la somme de la sélection. Et cette dernière recoupe parfaitement le résultat de notre calcul matriciel.

• Sélectionner l'une des cellules du tableau, par exemple le premier chiffre d'affaires en C4,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir l'option Gérer les règles,

Dans le gestionnaire qui apparaît, vous constatez la présence de 12 règles, soit 3 par catégories. Les techniques matricielles ne peuvent en effet être exploitées dans la syntaxe des règles de mise en forme conditionnelle.

• Cliquer sur l'une de ces règles pour la sélectionner,
• Puis, cliquer sur le bouton Modifier la règle en haut du gestionnaire,

Nous avons donc exploité la fonction Grande.Valeur en lui indiquant un rang différent à chaque occasion.



La syntaxe de la fonction Grande.Valeur est identique pour observer la contrainte sur la catégorie :

=GRANDE.VALEUR(SI(Catégorie=$F$9; CA; ''); 1)=$C4

Mais cette fois, seul l'un des rangs lui est passé en second paramètre. Et selon sa valeur, l'égalité finale doit être observée avec le chiffre d'affaire associé et extrait dans le tableau de bord.

• Cliquer sur les boutons Ok des boîtes de dialogue pour revenir sur la feuille Excel,
• Double cliquer sur la poignée du précédent résultat, soit de la cellule G6,

Nous répliquons ainsi la logique du calcul matriciel sur les autres catégories. Mais une anomalie surgit.



Seuls deux articles sont en effet référencés dans cette dernière catégorie repérée sur un fond gris. Vous le constatez en consultant la source de données. En conséquence, la somme matricielle ne peut être réalisée. Il en résulte une erreur. Dans ces conditions, nous devons la neutraliser grâce à la fonction SiErreur :

{=SIERREUR(SOMME(GRANDE.VALEUR(SI(Catégorie = F6; CA; ''); {1;2;3})); '')}

Il ne faut pas omettre de valider la formule d'origine par le raccourci clavier CTRL + MAJ + Entrée et de répliquer sa logique sur les lignes du dessous. Ainsi, la dernière case, en l'absence de données suffisantes, reste muette.

Extraire les articles des meilleurs CA
Pour rapatrier dans l'ordre les produits associés aux meilleurs chiffres d'affaires dans leurs catégories, nous devons exploiter les fonctions Index et Equiv, toujours dans un raisonnement matriciel. La fonction Index retourne l'information située au croisement d'une ligne et d'une colonne dans une source de données :

=Index(Table_de_recherche; Num_ligne; Num_colonne)

La colonne est connue. Nous souhaitons extraire la référence de l'article. Il s'agit donc de la première rangée dans le tableau de données. La ligne doit être trouvée. Pour cela, nous devons exploiter la fonction Equiv à la recherche des premières grandes valeurs, les unes après les autres.

=Equiv(Valeur_Cherchée; Colonne_de_recherche; Mode_de_recherche)

Pour respecter ces rangs dans l'ordre, nous allons exploiter les numéros inscrits en ligne 5 du tableau de bord. Enfin et en connaissance de cause, nous allons cette fois directement intégrer la fonction de gestion des anomalies, soit la fonction SiErreur.

• Sélectionner la cellule du premier article à extraire, soit H6,
• Taper le symbole égal (=) pour débuter la formule matricielle,
• Saisir la fonction de gestion d'anomalies, suivie d'une parenthèse, soit : SiErreur(,
• Saisir la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner la colonne concernée par son nom, soit : Article,
• Taper un point-virgule (;) pour passer dans l'argument du numéro de ligne à trouver,
• Saisir la fonction pour chercher cette position, suivie d'une parenthèse, soit : Equiv(,
• Saisir la fonction pour les grandes valeurs, suivie d'une parenthèse, soit : Grande.Valeur(,
• Saisir la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Désigner la colonne des catégories par son nom, soit : Catégorie,
• Taper le symbole égal (=) pour annoncer la condition à respecter,
• Sélectionner la première catégorie du tableau de bord, soit la cellule F6,
• Enfoncer trois fois de suite la touche F4 du clavier, ce qui donne : $F6,

Ainsi, nous la libérons en ligne et la conservons figée en colonne. En effet, toutes les catégories du dessous devront être utilisées pour les critères respectifs. Mais pour les articles situés sur la droite, la référence doit toujours être prise dans cette colonne qui ne doit donc pas bouger.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Désigner la colonne pour la grande valeur à trouver par son nom, soit : CA,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir deux guillemets ('') pour ignorer la valeur en cas de non correspondance,
• Fermer la parenthèse de la fonction Si,
• Taper un point-virgule (;) pour passer dans l'argument du rang de la fonction Grande.Valeur,
• Désigner le premier numéro du tableau de bord en cliquant sur sa cellule H5,
• Enfoncer deux fois de suite la touche F4 du clavier, ce qui donne : H$5,

Ainsi, nous la libérons en colonne et la conservons figée en ligne. Pour chaque catégorie, ce rang doit être observé et pioché sur sa ligne qui ne doit donc pas bouger. Mais pour chaque meilleur article, situé dans une colonne voisine, ce rang doit s'adapter donc suivre le déplacement.

• Fermer la parenthèse de la fonction Grande.Valeur,
• Taper un point-virgule (;) pour passer dans la colonne de recherche de la fonction Equiv,
• Désigner les chiffres d'affaires par le nom de la colonne, soit : CA,
• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de la fonction Index,
• Saisir le chiffre 1 pour spécifier l'unique rangée désignée pour l'extraction,
• Fermer la parenthèse de la fonction Index,
• Taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,
• Saisir deux guillemets pour garder la cellule vide lorsqu'aucune grande valeur ne correspond,
• Fermer la parenthèse de la fonction SiErreur,
• Enfin, valider nécessairement la formule par le raccourci clavier CTRL + MAJ + Entrée,

La première référence pour la première catégorie est parfaitement importée. Il vous suffit de consulter les couleurs imposées par la mise en forme conditionnelle pour le constater.

• Double cliquer sur la poignée du résultat pour répliquer la logique sur les autres lignes,

Nous obtenons ainsi l'extraction de chaque article dans sa catégorie, ayant généré le meilleur chiffre d'affaires, correspondant donc à la plus grande des valeurs, soumise à condition.

• Tirer la poignée de la sélection sur les deux colonnes de droite,

La réplication est parfaite. Tous les articles sont extraits dans l'ordre en respectant leur catégorie.



Et grâce à la fonction de gestion d'erreur, le troisième meilleur article n'existant pas pour la dernière catégorie, sa cellule est laissée vide.

La formule matricielle que nous avons bâtie pour extraire les plus grandes valeurs dans l'ordre, est la suivante :

{=SIERREUR(INDEX(Article; EQUIV(GRANDE.VALEUR(SI(Catégorie = $F6; CA; ''); H$5); CA;0); 1); '')}