Excel SVERWEIS – comment ça marche

Avec SVerweis, Excel offre une fonction polyvalente qui recherche la première colonne d’une zone de recherche.

Excel SVERWEIS – utilisation ciblée, travail propre

La fonction SVERWEIS dans Excel est l’une des fonctions les plus utilisées lorsqu’il s’agit d’extraire des valeurs individuelles de tableaux volumineux. Pour que cela fonctionne de manière fiable, il faut plus que la bonne formule : la structure des données et un œil attentif aux sources d’erreurs sont également importants.

• La matrice doit commencer à gauche : la fonction RECHERCHEV ne recherche que dans la première colonne de la zone sélectionnée. Si, par exemple, le numéro client se trouve dans la colonne D, la zone de recherche doit également commencer à cet endroit, c’est-à-dire « D:F » et non « A:F ». La première colonne est toujours utilisée comme base de comparaison. Si vous ignorez cette règle, vous obtiendrez des résultats erronés, voire aucun résultat.

• Travailler de manière plus stable avec les objets table : dès que vous formatez vos données en tant que « tableau » (raccourci clavier : Ctrl + T), Excel réagit automatiquement aux extensions ultérieures. La formule RECHERCHEV reste valide même si vous ajoutez de nouvelles lignes. La formule =RECHERCHEV(A2;Tableau1;3;FAUX) accède alors correctement aux données mises à jour, ce qui est idéal pour les listes qui s’allongent.

• FAUX au lieu de VRAI – contrôler le comportement de recherche de manière ciblée : sans correspondance exacte, vous risquez d’obtenir des résultats inexacts. Le quatrième élément de la formule, appelé référence_plage, doit presque toujours être défini sur FAUX. Ce n’est qu’alors qu’Excel vérifie la correspondance exacte. VRAI ne convient qu’aux données triées, ce qui est rarement garanti dans la pratique.

• Vérifier et uniformiser les formats de données : un nombre au format texte ou un nombre au format numérique – les deux se ressemblent, mais se comportent différemment. Si la fonction RECHERCHEV ne renvoie aucun résultat alors que la valeur figure dans la liste, il y a presque toujours un conflit de format. Avec =ISTTEXT(A2) ou =ISTZAHL(A2), vous identifiez rapidement où se situe le problème. Des outils tels que =GLÄTTEN() ou =WERT() nettoient automatiquement.

• Utiliser de manière ciblée les références de cellules absolues et relatives : lorsque vous copiez des formules, les références de cellules s’adaptent également. Cela peut avoir des conséquences fatales pour les données matricielles. Utilisez donc des références de cellules absolues avec $ (par exemple $A$2:$D$100) pour copier les formules de manière fiable de haut en bas ou à travers le tableau, sans que la zone ne « glisse ».

• Intercepter les erreurs avec élégance : Personne ne souhaite voir « NV » dans des lettres types ou des tableaux de bord. Combinez la fonction RECHERCHEV avec SI.ERREUR pour générer un résultat alternatif : =SI.ERREUR(RECHERCHEV(A2;Table1;3;FAUX); « Non trouvé »)

• Ce n’est pas une question d’esthétique, mais de fonctionnalité, par exemple pour les exportations ou les processus de données automatisés.

• Connaître les limites de SVERWEIS : dès que des colonnes sont déplacées ou que la structure du tableau change, SVERWEIS fournit des résultats erronés. Les recherches inversées, c’est-à-dire de droite à gauche, ne sont pas non plus possibles. Dans de tels cas, il est recommandé d’utiliser XVERWEIS ou INDEX/VERGLEICH.

XVERWEIS, INDEX & Co. – Des alternatives offrant plus de contrôle

Depuis Microsoft 365 et Excel 2021, XVERWEIS offre une variante plus robuste qui pallie de nombreuses faiblesses de SVERWEIS. La combinaison INDEX/VERGLEICH est également considérée comme flexible, en particulier lorsque les versions Excel ou les exigences changent.

• XVERWEIS : recherche inversée sans détours : la plus grande différence : XVERWEIS permet également de renvoyer des résultats provenant de colonnes situées à gauche du terme recherché. Exemple : =XVERWEIS(B2;Table1[Nom];Table1[Numéro client];« Aucun résultat »). Cela permet également de rechercher dans des tableaux dont la structure n’est pas optimale. SVERWEIS échouerait ici.

• L’index de colonne disparaît, ce qui réduit les risques d’erreur : avec XVERWEIS, vous indiquez deux plages de cellules concrètes : la plage de recherche et la plage de retour. Vous n’avez pas besoin de compter comme avec SVERWEIS (3e colonne, 4e colonne, etc.). Cela réduit les erreurs et améliore la clarté.

• Contrôler les messages d’erreur de manière ciblée : le quatrième argument « si_non_trouvé » remplace dans XVERWEIS le détour par SI_ERREUR. Lisibilité accrue, moins de fautes de frappe, accès direct : la fonction réagit de manière plus fluide aux cellules vides ou aux valeurs incorrectes.

• Contrôler le sens de la recherche et le comportement de comparaison : la fonction XVERWEIS offre deux paramètres supplémentaires : « Mode de comparaison » (0, -1, 1) et « Mode de recherche » (1 depuis le haut, -1 depuis le bas). Cela permet, par exemple, d’effectuer une recherche ciblée dans des barèmes de prix ou des entrées en double, ce qui est impossible avec SVERWEIS.

• XVERWEIS sur les appareils mobiles et sur le Web : dans Microsoft Excel Mobile, XVERWEIS fonctionne en principe, mais les formules dynamiques complexes sont limitées. Dans Excel Online, SVERWEIS et XVERWEIS fonctionnent de manière stable tant que les formats de cellule sont corrects. Sur les appareils mobiles, il est recommandé d’utiliser des structures simples, car certains éléments de contrôle font défaut.

• INDEX/COMPARER : polyvalent et indépendant de la version : INDEX récupère la valeur, COMPARER recherche l’emplacement correspondant. Exemple de formule : =INDEX(D2:D100;COMPARER(A2;B2:B100;0))

Contrairement à SVERWEIS, cette combinaison ne se rompt pas lorsque vous réorganisez les colonnes. Elle est adaptée aux calculs complexes ou lorsque XVERWEIS n’est pas disponible (par exemple dans Excel 2016).
Performances avec de grandes quantités de données : si vous travaillez avec des milliers d’enregistrements, INDEX/VERGLEICH ou XVERWEIS vous permettront d’atteindre votre objectif plus rapidement qu’avec SVERWEIS. Ces deux fonctions traitent plus efficacement les volumes de données importants, en particulier dans les tableaux de bord dynamiques ou lors de l’exportation depuis des systèmes tiers.

Exemple pratique – RECHERCHEV étape par étape

Beaucoup utilisent Excel quotidiennement, mais la fonction RECHERCHEV prête souvent à confusion, surtout si l’on ne travaille pas régulièrement avec des formules. L’exemple suivant montre comment créer une fonction RECHERCHEV fonctionnelle en quelques étapes simples et avec une structure claire. L’objectif est d’extraire automatiquement le numéro de téléphone correspondant à partir d’une liste de clients.

• Structurez le tableau de départ : créez un tableau à trois colonnes : « Numéro client », « Nom » et « Numéro de téléphone ». Chaque ligne contient exactement un enregistrement. Important : la colonne contenant la valeur recherchée, dans ce cas le numéro client, se trouve tout à gauche. La fonction RECHERCHEV ne compare que la première colonne de la zone de recherche. Exemple : « 1002 Langer 0157 9876543 »

• Configurer le deuxième tableau pour la requête : à droite, ou dans une autre feuille de calcul, créez une petite liste avec deux colonnes : « Numéro de client » et « Numéro de téléphone ». Dans la colonne « Numéro de client », saisissez la valeur à rechercher, par exemple « 1002 ». La deuxième colonne reste vide pour l’instant et sera remplie automatiquement ultérieurement.

• Construisez correctement la fonction RECHERCHEV : cliquez dans la cellule où le résultat doit apparaître, par exemple la cellule F2. Saisissez maintenant la formule suivante : =RECHERCHEV(E2;A2:C100;3;FAUX)

• Cette formule signifie : recherchez la valeur de la cellule E2 (ici « 1002 ») dans la première colonne de la plage A2 à C100. Renvoyez ensuite la valeur de la troisième colonne (C) de la même ligne. Le « FAUX » à la fin garantit que seules les correspondances exactes sont acceptées.

• Testez la formule et vérifiez le résultat : dès que vous appuyez sur Entrée, Excel affiche automatiquement « 0157 9876543 » dans la cellule F2, soit le numéro de téléphone du client recherché. Si vous modifiez la valeur dans E2 (par exemple en « 1003 »), le résultat dans F2 change automatiquement. La formule s’adapte dynamiquement à la valeur saisie.

• Éviter les sources d’erreurs courantes : vérifiez que les chiffres sont bien formatés en tant que chiffres dans la zone de recherche. Souvent, une erreur « NV » est due au fait que la valeur dans E2 est formatée en tant que chiffre, mais en tant que texte dans le tableau source. Cela n’est guère visible, mais a des conséquences techniques. Vous pouvez le vérifier avec « =ISTTEXT(A2) ». Si nécessaire, « =WERT() » permet de corriger le contenu de la cellule.

• Définir les références de cellules lorsque plusieurs lignes sont utilisées : Faites glisser la formule vers le bas pour interroger d’autres clients, puis fixez la zone à l’aide du signe dollar : =SVERWEIS(E2;$A$2:$C$100;3;FALSCH)

La zone de recherche reste ainsi inchangée, même si vous copiez ou faites glisser la formule vers le bas.
Intercepter les messages d’erreur, améliorer l’affichage

Pour afficher un message compréhensible tel que « Client introuvable » à la place d’un « NV », complétez la formule comme suit : =WENNFEHLER(SVERWEIS(E2;$A$2:$C$100;3;FALSCH); « Client introuvable »)

Le tableau semble ainsi plus soigné et l’utilisateur voit immédiatement lorsqu’il n’y a pas de résultat.