Différencier les égalités à l'extraction avec Excel

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


Sujets que vous pourriez aussi aimer :


Différencier les égalités à extraire

Avec les calculs matriciels, nous sommes déjà parvenus à extraire toute une série de données répondant à des critères. Pour l'énumération des résultats, nous avons souvent employé la fonction Grande.Valeur imbriquée dans les fonctions d'extraction Index et Equiv. Mais lorsque les données numériques attachées aux informations à importer sont identiques, l'extraction s'arrête sur la première concordance qu'elle livre en répétitions.



Ici, nous apportons la solution pour différencier ces égalités lors des phases d'extraction. Dans l'exemple finalisé illustré par la capture ci-dessus, nous importons les meilleurs chiffres d'affaires réalisés. Et bien que certains soient identiques, nous associons précisément les commerciaux les ayant atteints.

Source et présentation
Pour démarrer ces travaux, nous proposons de réceptionner des données.

• Télécharger le classeur differencier-egalites.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é,

Un petit tableau énumère les résultats des commerciaux entre les colonnes B et C. La synthèse doit être produite sur cinq lignes entre les colonnes E et F. Elle doit mettre en lumière les cinq meilleurs chiffres d'affaires. Et pour chacun d'eux, elle doit associer le nom du commercial ayant atteint ce score.



En déployant la zone Nom, en haut à gauche de la feuille Excel, vous notez que les deux colonnes du premier tableau sont identifiées par des intitulés. Nous exploiterons ces noms pour simplifier la construction des formules matricielles.

Extraire les cinq meilleurs chiffres
Dans un raisonnement matriciel, soit sur toutes les lignes simultanées du tableau, la fonction Grande.Valeur est la solution pour extraire les meilleurs résultats numériques. Nous devons la faire agir sur la plage des chiffres d'affaires. Et nous devons lui fournir une matrice de cinq unités incrémentées en guise de rangs. Chaque chiffre concordant avec son rang respectif sera ainsi extrait. Par exemple, le premier rang (1) correspond au meilleur score.

• Sélectionner les cellules des CA à extraire, soit la plage E6:E10,
• Taper le symbole égal (=) pour initier la formule matricielle,
• Inscrire la fonction des grandes valeurs suivie d'une parenthèse, soit : Grande.Valeur(,
• Désigner la plage des chiffres d'affaires par son nom, soit : CA,
• Taper un point-virgule (;) pour passer dans l'argument du rang,
• Inscrire la matrice suivante sans oublier les accolades : {1;2;3;4;5},

Ces accolades permettent en effet d'annoncer une matrice. Les points-virgules énumèrent les rangs dans l'ordre, du premier (1) pour le meilleur chiffre au cinquième pour le dernier à restituer.

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

Les cinq meilleurs chiffres d'affaires sont en effets extraits, qui plus est dans l'ordre décroissant. Et c'est une toute petite formule qui réalise cette prouesse :

{=GRANDE.VALEUR(CA;{1;2;3;4;5})}

Extraire les noms des meilleurs commerciaux
La fonction d'extraction est la fonction Index. Elle doit être combinée à la fonction Equiv qui, selon le précédent calcul, repère les positions des données à importer.

• Sélectionner et copier (CTRL + C) la syntaxe du précédent calcul, sans le symbole égal,
• Penser à sortir de la barre de formule par le raccourci CTRL + MAJ + Entrée,
• Sélectionner les cellules des commerciaux à extraire, soit la plage F6:F10,
• Taper le symbole égal (=) pour initier la formule matricielle,
• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner la matrice des commerciaux par son nom, soit : Commercial,
• Taper un point-virgule (;) pour passer dans l'argument des indices de ligne,
• Saisir la fonction permettant de trouver ces positions, suivie d'une parenthèse, soit : Equiv(,
• Coller la syntaxe (CTRL + V) précédemment copiée, soit : GRANDE.VALEUR(CA;{1;2;3;4;5}),
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Désigner la matrice des chiffres d'affaires par son nom, soit : CA,

Ainsi, nous réalisons la recherche matricielle pour lesquelles les positions des plus grands scores coïncident. Sachant que la matrice d'extraction est définie sur la colonne des commerciaux, nous devrions pouvoir extraire leurs noms.

• Taper un point-virgule suivi du chiffre zéro, soit : ;0; pour réaliser une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Puis, fermer la parenthèse de la fonction Index,

L'indice de colonne n'est en effet pas utile à renseigner. Comme la recherche est effectuée dans une rangée unique, la fonction Index pointera directement sur cette dernière.

• Enfin, valider nécessairement la formule matricielle par le raccourci CTRL + MAJ + Entrée,

Comme vous pouvez le voir, tous les commerciaux associés à ces chiffres sont parfaitement rapatriés. La syntaxe de la formule matricielle que nous avons construite est la suivante :

{=INDEX(Commercial; EQUIV(GRANDE.VALEUR(CA; {1;2;3;4;5}); CA; 0))}

Néanmoins, le contexte actuel est idéal. Aucun score n'est identique à un autre.

• Remplacer le chiffre 11 200 du vendeur Onette par 16 100,

A validation, la synthèse est beaucoup moins séduisante. Ce commercial associé à ce chiffre est repéré en premier. De fait, son nom est répliqué à deux reprises. Pourtant, c'est bien le Vendeur Mauve qui devrait être ensuite restitué.



Extraire sur des valeurs identiques
C'est une astuce tout à fait particulière qui permet d'extraire les données tout en effectuant la distinction des valeurs communes correspondantes.

• Sélectionner de nouveau la plage de cellules F6:F10,
• Dans la barre de formule, adapter la syntaxe du calcul comme suit :

{=INDEX(Commercial; EQUIV(GRANDE.VALEUR(CA-LIGNE(CA)/10^10; {1;2;3;4;5}); CA-LIGNE(CA)/10^10; 0))}

C'est cette syntaxe spécifique (-LIGNE(CA)/10^10) retranchée à la matrice utilisée pour l'analyse qui permet de différencier les égalités sur les extractions.

• Valider la formule matricielle par le raccourci clavier CTRL + MAJ + Entrée,

Cette fois, malgré l'égalité des meilleurs scores, ce sont bien les deux commerciaux respectifs qui sont extraits.