Pour de nombreux tableaux Excel sophistiqués, SVERWEIS() et WVERWEIS() sont indispensables. Lisez ici comment ces fonctions peuvent vous aider.
La fonction SVERWEIS est une fonction de référence. Elle sert donc à trouver des champs sur leur contenu à partir d’une plage de cellules donnée. SVERWEIS vérifie une série de cellules par rapport à une valeur de comparaison donnée et localise la première cellule qui a cette valeur de comparaison.
SVERWEIS a une autre fonction soeur appelée WVERWEIS. La recherche s’effectue toujours dans la première colonne (SVERWEIS) ou ligne (WVERWEIS) de la plage que vous indiquez. Les premières lettres du nom de la fonction indiquent le sens de recherche utilisé : « vertical » ou « horizontal ». Dans ce qui suit, nous ne nous attarderons pas sur la fonction RECHERCHE. Toutes les remarques faites pour SVERWEIS sont également valables ici, à la seule différence que cette fonction travaille horizontalement.
Les paramètres de la fonction SVERWEIS
La fonction a le schéma d’appel suivant :
SVERWEIS(Critère de recherche ; Matrice ; Index des colonnes ; Référence_de_plage)
La première valeur dans la liste des paramètres est la valeur de comparaison que vous voulez utiliser pour la recherche. Une cellule doit avoir cette valeur pour qu’Excel la trouve. Contrairement à ce que vous pourriez penser, une seule valeur est autorisée ici, mais pas une expression logique. Les fonctions de référence vérifient toujours « égal ou supérieur ».
En deuxième position se trouve la matrice, c’est-à-dire le domaine que vous souhaitez utiliser pour la recherche. SVERWEIS utilise pour la recherche la première colonne, c’est-à-dire la colonne la plus à gauche de la zone de recherche, WVERWEIS la ligne la plus haute.
Avec l’index de colonne, c’est-à-dire le troisième paramètre de la fonction, vous indiquez quelle cellule doit être renvoyée par rapport à la cellule contenant la valeur trouvée. Si la valeur de l’index est 1, il s’agit de la cellule trouvée elle-même. Si vous indiquez 2, il s’agit de la cellule à droite de l’emplacement trouvé (en dessous, si vous utilisez RÉFÉRENCE), et ainsi de suite.
Le dernier paramètre Domaine_Référence a une désignation malheureuse qui n’explique pas le sens du paramètre. Il décide si la recherche de la valeur doit être exacte (dans ce cas, vous devez mettre FAUX comme valeur du paramètre) ou si Excel se contente aussi d’une valeur supérieure au critère de recherche (référence_plage est VRAIE ou non spécifiée).
Attention Si vous ne spécifiez pas le dernier paramètre de SVERWEIS/WVERWEIS ou si vous le mettez à VRAI, vos valeurs doivent être triées par ordre croissant dans la plage recherchée ! Sinon, la fonction ne vous donnera pas le résultat attendu ! Vous trouverez l’explication à la fin de cette astuce.
Les deux variantes en fonction de la valeur du dernier paramètre diffèrent dans leur fonction. Examinez d’abord le cas le plus simple, celui où la recherche exacte est utilisée via Domaine_Référence = VRAI.
Pour utiliser SVERWEIS pour une recherche exacte, procédez comme suit
Un exemple courant est une liste de numéros de téléphone. Vous y avez un champ clé, par exemple le numéro personnel de tous les employés.
Si la liste est longue, il est difficile de la parcourir manuellement. Avec la fonction SVERWEIS, cela fonctionne beaucoup mieux.
Pour ce faire, vous saisissez dans une cellule le matricule de l’employé recherché. La cellule de droite doit ensuite afficher le numéro de téléphone et, en guise de contrôle, le nom du collaborateur apparaît encore une cellule plus loin à droite.
Pour obtenir ce résultat, saisissez dans la cellule B1 la formule SVERWEIS de la manière suivante. Si votre annuaire téléphonique, c’est-à-dire la partie marquée en rouge, a d’autres dimensions, vous devez adapter le deuxième paramètre (c’est-à-dire la partie marquée en rouge sur l’image) en conséquence.
Dans la cellule à droite, qui doit recevoir le nom du collaborateur recherché, copiez la formule que vous venez de taper, mais choisissez « 4 » comme index de colonne, ce qui correspond au champ de nom dans la liste.
La liste téléphonique ne doit pas être triée par ordre croissant pour le premier champ avec le numéro personnel. Comme on utilise ici SVERWEIS avec FALSCH comme dernier paramètre, une recherche exacte est effectuée, ce qui rend cela inutile.
Un exemple simple de SVERWEIS avec « recherche non exacte ».
Un commerçant fait dépendre les frais d’expédition de la valeur des achats : Jusqu’à 50 euros, les frais de port sont de 10 euros, de 50 à 100 euros, ils sont encore de 5 euros et à partir de 100 euros, le client ne doit pas payer de frais de port.
Vous pouvez représenter cette règle dans le tableau suivant, qui contient toujours dans la première colonne la valeur inférieure de l’intervalle correspondant (donc 0-50, 50-100, 100-) :
Pour que le tableau puisse être utilisé avec un nom parlant à l’intérieur de la formule, sélectionnez le tableau des frais d’envoi et donnez à ce domaine le nom « VersandTab ».
Dans l’exemple, nous sélectionnons la zone B6 à C8 et saisissons le nom dans le champ de nom en haut à gauche.
Tout en haut de la feuille de calcul se trouve le calcul pour une certaine commande. Tout en haut, on inscrit la valeur de la marchandise, en dessous dans B2, la fonction SVERWEIS doit calculer pour nous le forfait de frais de port correct et dans la cellule en dessous, nous additionnons les deux valeurs.
Pour calculer les frais de port, inscrivez pour cela la fonction suivante dans la cellule B2 :
=Référence(B1;TabExpédition;2)
Excel doit donc parcourir les cellules B6 à B8 l’une après l’autre et vérifier la valeur qui y est trouvée avec le critère de recherche dont la valeur se trouve dans B1. La recherche s’arrête sur la première valeur supérieure ou égale à la valeur saisie dans B1. La valeur renvoyée est alors celle de la cellule à droite, car l’index de la colonne est indiqué par 2.
Voici en détail comment fonctionne la recherche inexacte par SVERWEIS
L’explication initiale selon laquelle SVERWEIS recherche la première valeur qui entre dans le schéma est une simplification. Afin de comprendre pleinement le comportement, il convient de garder cela à l’esprit. Prenons l’exemple ci-dessus avec une valeur de marchandise saisie de 32 euros.
Excel prend d’abord en compte la première cellule et vérifie si sa valeur est inférieure ou égale à la valeur recherchée. Comme 32 est supérieur à 0, Excel passe à la cellule suivante. Ici, la condition (32 = 50) est remplie et la recherche est réussie. Excel fait maintenant un pas en arrière et utilise cette ligne comme lieu de recherche, il affiche donc 10 euros de frais de port.
Si, par exemple, tu saisis un euro comme valeur de départ dans la cellule B6 et que tu effectues ensuite un calcul de frais de port avec une valeur de marchandise de 0,99 euro, Excel te signale une erreur (#NV). En effet, Excel a tout de suite réussi dans la toute première comparaison, mais ne peut pas revenir en arrière d’une ligne.
Ainsi, les valeurs non triées provoquent une erreur
A cause de la logique d’Excel « trouver la première cellule plus grande que la valeur de recherche et revenir en arrière », il y a des résultats étranges si vous n’avez pas vos valeurs triées par ordre croissant et si vous travaillez avec la version « inexacte » de SVERWEIS, c’est-à-dire si vous mettez le dernier paramètre à VRAI ou si vous l’omettez complètement…
Examinez la liste suivante, avec laquelle une entreprise veut calculer la commission des commerciaux en fonction du chiffre d’affaires réalisé :
Si vous effectuez une recherche inexacte et que vous voulez utiliser SVERWEIS pour déterminer la commission pour un chiffre d’affaires de 12 000 euros, écrivez
=SVERWEIS(12000;A2:B4;2)
Ce que fait Excel dans ce cas :
Voir A2, la valeur est plus petite que la valeur de recherche, donc continuer
Regarder A3. La valeur est supérieure à 12.000, alors reculez d’un pas.
Sortir la valeur en B3
