Recherches et extractions approximatives 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 :


Extractions sur recherches approchantes avec Excel

Dans cette formation Excel, nous proposons de réaliser une étude sur la base de nombreux critères à recouper, en apportant une solution originale et efficace.

La feuille du classeur Excel illustrée par la capture ci-dessous, réalise le suivi des chiffres d'affaires produits par les commerciaux d'une entreprise. Selon le panneau de contrôle situé en haut à gauche de cette feuille, il s'agit d'attribuer des récompenses et des primes aux commerciaux, en fonction des ventes réalisées.



Ainsi au-delà de 100 000 Euros de chiffre, le commercial se voit offrir un voyage et une prime de 10%, à calculer sur le montant de ses ventes. Entre 50 000 et 100 000 Euros, il reçoit une télévision et une prime de 5%. Puis il se voit offrir seulement un Dîner pour la tranche restante, définie entre 5 000 et 50 000 Euros. Remarquez qu'une situation n'est pas prévue. Il s'agit du cas où le vendeur n'atteint pas les 5 000 Euros de chiffre d'affaires.

Source et présentation du cas à résoudre
Nous avions résolu ce cas pratique grâce aux fonctions conditionnelles SI. Nous avions ainsi prouvé leur puissance. Mais avec cette nouvelle solution, nous allons surtout prouver leur complexité et leur limitation, lorsque le nombre de cas à envisager augmente. Nous proposons donc de récupérer la source, afin de débuter les travaux de comparaison.

• Télécharger le classeur primes-par-recherches-proches.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer la modification du bandeau de sécurité,

Ce classeur est constitué d'une feuille unique nommée Primes-CA. Comme nous l'avons dit plus haut, elle effectue la synthèse des chiffres d'affaires développés par les commerciaux de la société. Vous notez la présence du tableau de référence pour les récompenses à attribuer. Il est situé entre les colonnes B et E, à partir de la ligne 2 jusqu'à la ligne 5.

Vous constatez la présence de l'ancien calcul, volontairement conservé en colonne G:

=SI(E10>$D$5; $B$5; SI(E10>$D$4; $B$4; SI(E10>$D$3; $B$3; '')))



Il traduit les cas que nous avons explicité pour répondre aux exigences du panneau de contrôle. Trois imbrications de fonctions Si, avec références absolues, sont nécessaires. La syntaxe obtenue est relativement lourde et complexe. De plus, ces résultats sont figés. La formule est incapable de considérer de nouvelles potentielles contraintes.

En colonne H, nous proposons d'atteindre les mêmes résultats mais par recherche approximative. Nous avions développé une méthode intéressante dans deux cas pratiques. Le premier consistait à calculer les primes d'ancienneté des salariés. Le second consistait à calculer des remises en fonction des quantités achetées. Mais ces recherches ne pouvaient trouver aucune correspondance exacte dans le tableau des critères, comme ici. Pour connaître la récompense du vendeur, son chiffre d'affaires ne peut a priori correspondre à aucune valeur de référence.

Nous avions donc exploité la fonction Excel RechercheV, avec le dernier argument booléen fixé à Vrai pour effectuer une recherche approchée. En d'autres termes, si la valeur cherchée n'est pas trouvée, la fonction se cale sur la donnée directement inférieure la plus proche. Et ainsi nous respectons le raisonnement par tranches.

Cependant, la fonction RechercheV impose une limitation de taille. La donnée cherchée doit nécessairement se situer en première colonne du tableau de recherche. Ce n'est pas le cas ici. La première colonne restitue les récompenses.

Recherche approximative
Cette limitation n'est pas imposée par les fonctions Index et Equiv. La première extrait une information située au croisement d'une ligne et d'une colonne dans un tableau de recherche. Sa syntaxe est la suivante :

=Index(Tableau_de_recherche; numéro_de_ligne ; numéro_de_colonne)

Le tableau de recherche est le panneau de contrôle des récompenses. La colonne est connue. Il s'agit de la première. En revanche, la ligne dépend du chiffre d'affaires du commercial. C'est la fonction Equiv qui peut renseigner sur cette variable tout en proposant une évaluation approximative, comme la rechercheV. Sa syntaxe est la suivante:

=Equiv(Valeur_cherchée; Colonne_de_recherche ; Mode_de_recherche)

Il s'agit de la solution ingénieuse que nous apportons. La valeur cherchée est le chiffre d'affaires du commercial. C'est lui qui doit permettre de déterminer sa récompense. La colonne de recherche correspond à celle des valeurs de critères. Il s'agit donc de la colonne D. Le dernier argument constitue le noeud du problème. Réglé à 1, il permet d'effectuer une recherche approximative. Si le chiffre d'affaires n'est pas trouvé, c'est la valeur de référence directement inférieure qui est considérée comme base pour l'extraction.

Nous proposons donc de débuter le calcul en colonne H. Ainsi, nous pourrons comparer facilement les résultats avec ceux fournis par l'imbrication des fonctions Si en colonne G.

• Sélectionner la première cellule du calcul, soit H10,
• Taper le symbole = pour commencer la formule,
• Saisir la fonction d'extraction suivie d'une parenthèse, soit Index(,
• Sélectionner la plage de cellules B3:D5 pour définir le tableau de recherche,
• Enfoncer la touche F4 du clavier pour figer ses bornes, ce qui donne : $B$3:$D$5,

Il s'agira ensuite de répliquer la logique de ce calcul sur la hauteur du tableau, afin d'extraire les récompenses pour chaque chiffre d'affaires réalisé. Les bornes du tableau de recherche ne doivent pas suivre ce déplacement. Donc nous les figeons.

• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne,
• Saisir la fonction indiquant la ligne d'une recherche suivie d'une parenthèse, soit Equiv(,
• Cliquer sur la cellule E10 pour indiquer le premier chiffre d'affaires à rechercher,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Sélectionner la plage D3:D5 pour préciser les valeurs de critères de correspondance,
• Enfoncer la touche F4 du clavier pour figer ses bornes, soit : $D$3:$D$5,
• Taper un point-virgule (;) pour passer dans l'argument du mode de recherche,
• Saisir le chiffre 1 pour trouver les valeurs proches,
• 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 la colonne des récompenses correspondantes,
• Fermer la parenthèse de la fonction Index,
• Valider la formule par le raccourci CTRL + Entrée pour conserver la cellule active,
• Double cliquer sur la poignée de la cellule pour répliquer la logique du calcul sur la hauteur du tableau,

Il peut être utile d'exploiter la balise active qui se déclenche en bas de la colonne, pour le calcul répliqué. Elle permet de corriger les travaux de mise en forme en indiquant de ne répliquer que les valeurs.

La formule que nous avons bâtie est la suivante :

=INDEX($B$3:$D$5; EQUIV(E10; $D$3:$D$5; 1); 1)

Sa syntaxe est largement simplifiée, à plus forte raison si le nombre de critères augmente. Tous les résultats obtenus sont identiques ou presque. Une erreur résiduelle s'est glissée en regard du commercial n'ayant pas atteint les 5 000 Euros de chiffre d'affaires. Cette tranche inférieure n'est effectivement pas référencée dans le tableau des critères. Plutôt que d'ajouter cette contrainte au tableau de bord, nous la déduirons par calcul. D'ailleurs, c'est ce que propose l'imbrication des fonctions SI en colonne G.



Comme vous le constatez, si le chiffre d'affaires dépasse la valeur plafond, ce sont les 100 000 Euros qui sont utilisés comme référence. Le commercial se voit offrir un voyage. Lorsque le chiffre est compris entre 50 000 et 100 000 Euros, c'est bien la valeur inférieure la plus proche qui est exploitée, soit 50 000. Une télévision lui est donc offerte. Lorsque le chiffre est situé dans la tranche basse, c'est toujours la valeur inférieure qui est exploitée, soit 5 000. Le commercial obtient un dîner.

Nous avons donc reproduit le raisonnement initié par les fonctions SI imbriquées. Mais encore une fois, la syntaxe est plus simple. Elle ne s'alourdira pas si des critères, soit des nouvelles tranches étaient ajoutées. Les fonctions SI en revanche, nécessiteraient de nouvelles imbrications.

L'erreur produite par notre calcul est #N/A. Il s'agit de l'abréviation de l'expression Not Availiable. Elle signifie littéralement : Non disponible. En effet, une fonction d'extraction, lorsqu'elle ne trouve aucune correspondance, retourne une erreur. Il s'agit d'une opportunité à exploiter grâce à la fonction Excel SiErreur. Sa syntaxe est la suivante :

=SiErreur(Formule_à_tester; Action_si_erreur)

La formule à tester est notre calcul réalisé par l'imbrication des fonctions Index et Equiv. S'il produit un résultat, ce dernier est affiché. Si une erreur survient, cela indique que le commercial n'a pas atteint la valeur plancher de la tranche inférieure. Dans ce cas, aucune récompense ne lui est proposée. Sa cellule doit donc rester vierge. Les deux guillemets sont l'astuce que nous exploitons à chaque reprise dans ce contexte.

• Depuis la barre de formule en cellule H10, adapter la syntaxe comme suit :

=SiErreur(INDEX($B$3:$D$5; EQUIV(E10; $D$3:$D$5; 1); 1);'')

• Valider les modifications par le raccourci clavier CTRL + Entrée,
• Puis, répliquer la formule de recherche en double cliquant sur la poignée de la cellule,

Encore une fois, il convient d'exploiter la balise active en bas du calcul répliqué, afin de corriger les potentiels défauts de mise en forme.



Comme vous le constatez, le message Excel disparaît. Le cas non prévu générant une erreur est intercepté par la fonction Excel SiErreur. Par déduction, elle se charge de l'action à réaliser. De fait, aucune récompense n'est effectivement attribuée. Ce calcul demeure bien dynamique. Si vous basculez le chiffre d'affaires du commercial à 6500 Euros par exemple, la correspondance approximative est réalisée. Plus aucune erreur n'est générée. De fait, une récompense est attribuée selon le barème du tableau de référence.

Le calcul suivant en colonne I est similaire. Il propose d'extraire le pourcentage de la prime, en fonction du chiffre d'affaires réalisé. Il s'agit donc d'effectuer une recherche approximative sur la plage de cellules D3:E5 cette fois. Les tranches pour les chiffres d'affaires sont toujours situées en colonne D pour la recherche. En revanche, la valeur correspondante à extraire est située en colonne E, soit dans la deuxième colonne de la plage. En connaissance de cause, nous prévoyons d'imbriquer la formule d'extraction dans une fonction Excel de gestion d'erreur.

Pour ne pas avoir à subir les désagréments de mise en forme, nous proposons de répliquer une méthode de calcul spécifique. Cette dernière entre autres, est enseignée dans le petit livre Excel pour débuter avec les calculs.

• Sélectionner toutes les cellules concernées par l'extraction du pourcentage de la prime, soit la plage I10:120,
• Saisir le calcul comme si seule la première cellule de la sélection était concernée :

=SiErreur(Index($D$3:$E$5; Equiv(E10; $D$3:$D$5; 1); 2); '')

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

La logique du calcul est effectivement répliquée sur l'ensemble des cellules présélectionnées. Mais cette fois, les bordures de la première cellule ne sont pas reproduites. Une fois encore, nous avons extrait toutes les valeurs correspondantes par recherche approximative. Cette méthode est définitivement précieuse pour éviter l'imbrication sans fin des fonctions SI.



Comme précédemment et comme l'illustre la capture ci-dessus, l'exception a parfaitement été gérée pour le commercial n'ayant pas atteint le premier seuil. Vous avez remarqué que le calcul de la prime de rendement s'est automatiquement déclenché. Il était déjà en place. Il consiste à multiplier le pourcentage de la prime allouée par le chiffre d'affaires.



Mais comme une donnée numérique ne peut pas être multipliée par un texte (celui de la cellule vide), c'est la fonction Si qui intervient cette fois pour gérer l'exception, en toute simplicité. Son exploitation n'est donc absolument pas remise en cause. Elle reste très précieuse et puissante dans bien des cas. Mais lorsqu'il s'agit d'analyser une grande quantité de conditions, les fonctions d'extraction s'avèrent plus puissantes et plus souples.