Comment faire une recherche VLOOKUP pour des nombres stockés en tant que texte dans Excel ?
Lors de l'utilisation de VLOOKUP dans Excel, rencontrer des formats incompatibles — spécifiquement lorsque la valeur recherchée est stockée en texte alors que la colonne de recherche contient des nombres, ou vice versa — peut entraîner des échecs de recherche ou des erreurs. Cette incohérence de formatage est un problème courant, surtout lorsque les données proviennent de sources externes, sont importées, ou lorsqu'on travaille avec de grands ensembles de données collaboratifs. Résoudre ces incohérences est essentiel pour garantir que VLOOKUP fonctionne comme prévu et vous aide à récupérer les bonnes informations. Ce guide étape par étape présente plusieurs solutions pratiques pour résoudre ces incohérences de formatage, assurant des recherches fiables et précises indépendamment de la manière dont les nombres sont stockés dans votre classeur.
Cet article démontrera des méthodes efficaces pour gérer de tels erreurs, y compris des ajustements de formules, des outils intégrés d'Excel, et l'automatisation VBA pour le traitement en masse ou automatisé. Nous discutons également des avantages et considérations de chaque méthode, vous aidant à choisir l'approche la plus adaptée à votre scénario.
- Rechercher des nombres stockés sous forme de texte avec des formules
- Corrigez rapidement les incohérences de format avec Kutools pour Excel
- Macro VBA : Standardiser les formats avant VLOOKUP
- Autres méthodes intégrées d'Excel : Utilisez 'Texte en colonnes' pour corriger les formats de données
Rechercher des nombres stockés sous forme de texte avec des formules
Si vos données de recherche incluent des nombres stockés sous forme de texte à un endroit et des nombres réels à un autre, VLOOKUP peut échouer à trouver des correspondances en raison de cette incohérence de format. L'une des solutions les plus directes consiste à utiliser des formules Excel qui convertissent la valeur recherchée ou la colonne de recherche vers un format cohérent à la volée. Cette approche fonctionne bien dans la plupart des opérations de feuille de calcul, est facile à appliquer et conserve vos données d'origine inchangées.
Par exemple, si votre valeur recherchée est stockée sous forme de texte, tandis que le champ correspondant dans le tableau est formaté en nombre, vous pouvez utiliser la fonction VALUE pour convertir le texte en nombre dans la formule VLOOKUP.
Entrez la formule suivante dans une cellule vide où vous souhaitez afficher le résultat :
=VLOOKUP(VALUE(G1),A2:D15,2,FALSE)Après avoir entré la formule, appuyez sur la touche Entrée pour récupérer la valeur correspondant à vos critères, comme illustré dans la capture d'écran ci-dessous :
Explication des paramètres et conseils :
- G1 : La cellule contenant la valeur que vous souhaitez rechercher (peut être du texte ou un nombre).
- A2:D15 : La plage de votre table de données qui inclut la colonne de recherche et les colonnes contenant les informations que vous souhaitez retourner.
- 2 : Le numéro de colonne (à partir de la colonne la plus à gauche de la plage du tableau) pour le résultat que vous souhaitez retourner.
Soyez attentif aux espaces avant/après dans les valeurs recherchées, car ceux-ci peuvent également entraîner des échecs de recherche. Envisagez de combiner la fonction TRIM si vos données peuvent contenir des espaces supplémentaires.
Si la valeur recherchée est un nombre réel (format numérique), mais que le champ correspondant dans le tableau est stocké sous forme de texte, vous devez convertir le nombre en texte avant d'effectuer la recherche. La fonction TEXT convient à ce scénario :
=VLOOKUP(TEXT(G1,0),A2:D15,2,FALSE)Entrez ceci dans votre cellule cible, appuyez sur Entrée, et le bon résultat sera retourné comme indiqué ci-dessous :
Ici, le code de format numérique « 0 » à l'intérieur de la fonction TEXT garantit que votre nombre est converti en une valeur de texte simple avant la correspondance.
Si vous n'êtes pas sûr des formats possibles de vos valeurs recherchées ou si vous prévoyez que du texte et des nombres apparaîtront dans votre colonne de recherche, vous pouvez imbriquer les deux approches en utilisant la fonction IFERROR pour gérer toutes les possibilités de manière transparente :
=IFERROR(VLOOKUP(VALUE(G1),A2:D15,2,0),VLOOKUP(TEXT(G1,0),A2:D15,2,0))Entrez cette formule dans votre cellule de résultat. Elle essaiera d'abord la recherche en convertissant votre valeur en nombre ; si cela échoue (par exemple, si la valeur ne peut pas être transformée en nombre), elle essaiera ensuite de convertir votre valeur en texte et effectuera une nouvelle recherche. Cela est particulièrement utile dans des ensembles de données avec des formats mixtes ou dans des fichiers partagés où les normes de saisie de données ne sont pas uniformes.
Après avoir entré l'une des formules ci-dessus, n'oubliez pas de copier la formule vers les cellules adjacentes si vous devez l'appliquer à plusieurs valeurs recherchées — sélectionnez simplement la cellule, faites glisser la poignée de remplissage vers le bas, ou utilisez Ctrl+C et Ctrl+V au besoin. Pour les grandes tables, l'utilisation de ces formules aide à garantir des correspondances fiables sans altérer votre base de données d'origine.
Cette méthode fournit une solution flexible et universellement applicable pour la plupart des recherches basées sur des feuilles de calcul. Cependant, pour des ensembles de données très volumineux ou lorsque vous devez traiter de nombreux enregistrements automatiquement, vous pourriez envisager d'utiliser des outils d'automatisation comme VBA pour encore plus d'efficacité.
Corrigez rapidement les incohérences de format avec Kutools pour Excel
Si vous préférez une solution plus rapide, sans formule, Kutools pour Excel propose un outil convivial appelé Convertir entre Texte et Nombre. Cette fonctionnalité vous permet de convertir des nombres stockés sous forme de texte en nombres réels — ou vice versa — en quelques clics seulement. C'est particulièrement utile pour résoudre des problèmes de format avant d'effectuer des recherches comme VLOOKUP ou MATCH.
Après avoir installé Kutools pour Excel, procédez comme suit.
- Sélectionnez la plage qui contient vos données problématiques (par exemple, des nombres stockés sous forme de texte).
- Allez dans « Kutools » > « Contenu » > « Convertir entre Texte et Nombre ».
- Dans la fenêtre contextuelle :
- Choisissez « Texte en nombre » si vous corrigez des échecs de recherche dus à des nombres formatés en texte. (Ou sélectionnez « Nombre en texte » si les valeurs recherchées sont stockées sous forme de texte.)
- Cliquez sur « OK » pour convertir immédiatement le format des données.
- Choisissez « Texte en nombre » si vous corrigez des échecs de recherche dus à des nombres formatés en texte.
Après avoir converti du texte en nombres, les cellules converties se comporteront comme des nombres réels et n'afficheront plus les indicateurs de triangle vert pour incohérence.
Cette approche élimine le besoin de colonnes d'aide, de formules ou de VBA — la rendant idéale pour un nettoyage rapide avant d'appliquer VLOOKUP.
Kutools pour Excel - Boostez Excel avec plus de 300 outils essentiels. Profitez de fonctionnalités IA gratuites en permanence ! Obtenez-le maintenant
Macro VBA : Standardiser les formats avant VLOOKUP
Pour les utilisateurs qui travaillent régulièrement avec de grands ensembles de données, reçoivent des fichiers provenant de sources externes, ou nécessitent une automatisation répétitive, utiliser une macro VBA simple peut standardiser par programmation le format des données dans la colonne de valeur recherchée et la colonne de la table de recherche. De cette façon, vous vous assurez que toutes les données sont converties en texte ou en nombre avant d'exécuter VLOOKUP, éliminant ainsi les erreurs de correspondance dues à des incohérences de format. VBA est particulièrement utile pour le traitement en masse, économisant des ajustements manuels et garantissant la cohérence des données grâce à l'automatisation.
Avantages : Automatisation du formatage pour de grandes plages de données ou des flux de travail fréquents ; minimise le risque de formatage manquant ou incohérent ; adapté aux tâches répétitives.
Inconvénients : Non adapté aux utilisateurs ayant des restrictions sur les macros ou ceux qui ne sont pas familiers avec l'utilisation des macros VBA.
Voici comment utiliser une macro pour standardiser les formats des cellules :
1. Allez dans l'onglet Développeur et cliquez sur Visual Basic pour ouvrir l'éditeur VBA. Dans la nouvelle fenêtre, cliquez sur Insérer > Module, puis copiez et collez le code suivant dans la zone du module :
Sub StandardizeLookupFormats()
' Ask the user to select the lookup column and choose a target format
Dim rng As Range
Dim userChoice As Integer
Dim xTitleId As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.InputBox("Select the range to standardize (lookup or data column):", xTitleId, Type:=8)
If rng Is Nothing Then Exit Sub
userChoice = MsgBox("Convert selected data to Number? (Click Yes to convert to Number, No to convert to Text)", vbYesNoCancel, xTitleId)
If userChoice = vbYes Then
For Each cell In rng
If IsNumeric(cell.Value) Then
cell.Value = Val(cell.Value)
cell.NumberFormat = "General"
End If
Next
ElseIf userChoice = vbNo Then
For Each cell In rng
If Not IsEmpty(cell.Value) Then
cell.Value = CStr(cell.Value)
cell.NumberFormat = "@"
End If
Next
Else
Exit Sub
End If
End Sub2. Fermez l'éditeur VBA. Pour exécuter la macro, revenez à Excel, appuyez sur Alt+F8, sélectionnez StandardizeLookupFormats, et cliquez sur Exécuter.
Détails d'opération et conseils :
- Cette macro vous demandera de sélectionner la colonne (soit votre plage LOOKUP soit votre plage TABLE) que vous souhaitez standardiser.
- Après la sélection, il vous demandera si vous souhaitez convertir la plage en nombres (cliquez sur Oui) ou en texte (cliquez sur Non). Sélectionnez le même format pour vos colonnes de recherche et de table pour vous assurer que VLOOKUP correspondra de manière fiable.
- Après avoir exécuté cette macro, vous devrez peut-être recalculer la feuille de calcul (appuyez sur F9) ou réappliquer vos formules VLOOKUP si les résultats n'apparaissent pas immédiatement.
- Si vous recevez une erreur indiquant que les macros sont désactivées, activez les macros dans vos paramètres Excel avant de continuer.
Cette solution est idéale pour les importations de données récurrentes, ou lorsque vous nettoyez des colonnes incohérentes dans de grands ensembles de données avant d'appliquer VLOOKUP ou d'autres opérations de recherche.
Autres méthodes intégrées d'Excel : Utilisez 'Texte en colonnes' pour corriger les formats de données
Une méthode rapide pour aligner les formats de nombres et de texte dans Excel est d'utiliser la fonctionnalité Texte en colonnes. Cet outil intégré est souvent utilisé pour diviser des données mais peut également forcer une conversion de format sans modifier les formules, ce qui est utile lorsque vous souhaitez une correction ponctuelle ou lorsque vous traitez des listes simples.
Avantages : Très facile, aucune formule ni code requis, préserve la structure des données d'origine ; Inconvénients : Convient pour des corrections ponctuelles, ne se met pas à jour automatiquement si les données changent.
Pour utiliser cette méthode afin de convertir des nombres stockés sous forme de texte (ou vice versa) dans une colonne :
- Sélectionnez la colonne avec le format suspecté incohérent (par exemple, votre colonne de recherche ou la colonne référencée par VLOOKUP).
- Dans l'onglet Données, cliquez sur Texte en colonnes.
- Dans l'assistant, choisissez Délimité, puis cliquez sur Suivant.
- Désactivez toutes les cases à cocher des délimiteurs (car vous ne divisez pas les données) ; cliquez sur Suivant.
- Dans Format des données de colonne, sélectionnez Général (pour forcer Excel à reconnaître les nombres comme des nombres) ou sélectionnez Texte (pour convertir les nombres en texte).
- Cliquez sur Terminer pour compléter le processus.
Une fois terminé, vos données auront leur format aligné de force en nombre ou en texte, résolvant les incohérences de VLOOKUP. Vérifiez toujours quelques cellules pour confirmer que la conversion a fonctionné comme prévu. Si nécessaire, répétez le processus pour votre colonne de recherche et vos valeurs recherchées pour maximiser la cohérence.
Rappels pratiques : « Texte en colonnes » modifie directement les données, donc il peut écraser le contenu des cellules si vous avez des données existantes immédiatement à droite. Envisagez de copier votre colonne dans une zone vierge d'abord si vous n'êtes pas sûr, et sauvegardez toujours une copie de votre fichier avant d'utiliser des outils de données en masse.
Meilleurs outils de productivité pour Office
Améliorez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité incomparable. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte l’interface par onglets à Office, simplifiant considérablement votre travail.
- Activez la modification et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de50 % et réduit des centaines de clics de souris chaque jour !
Tous les modules complémentaires Kutools. Une seule installation
La suite Kutools for Office regroupe les modules complémentaires pour Excel, Word, Outlook & PowerPoint ainsi qu’Office Tab Pro, idéal pour les équipes travaillant sur plusieurs applications Office.
- Suite tout-en-un — modules complémentaires Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un installateur, une licence — installation en quelques minutes (compatible MSI)
- Une synergie optimale — productivité accélérée sur l’ensemble des applications Office
- Essai complet30 jours — sans inscription, ni carte bancaire
- Meilleure valeur — économisez par rapport à l’achat d’add-ins individuels