Excel – Utilisez VLOOKUP pour comparer deux listes

Étape 1: Il y a quelques règles, nous avons donc vérifié ces premières

Le client avait envoyé une feuille de calcul Excel très basique qui affichait une liste de leurs enregistrements de paiement par bon de commande, numéro de facture, montant et date.

L’administrateur (appelons-le Guy) a ensuite généré un rapport Excel à partir du système de comptes de son entreprise. Nous avons ensuite utilisé les deux feuilles de calcul Excel pour effectuer notre rapprochement.

Cependant, avant de pouvoir commencer à utiliser la fonction VLOOKUP, j’ai expliqué les subtilités de l’obtention de vos données dans un format qui garantira le fonctionnement de VLOOKUP.

Excel avait besoin des éléments suivants:

• Dans le classeur du client et le classeur du gars, il devait y avoir au moins une information correspondante pour chaque enregistrement. Par exemple, les deux classeurs comportaient le numéro de facture et le numéro de bon de commande pour chaque enregistrement de transaction. Nous avons décidé d’utiliser le numéro de facture dans le classeur de Guy pour trouver une correspondance pour le même numéro de facture dans le classeur du client. Cette information est appelée « Valeur de recherche « .
• La valeur de recherche doit être unique, c’est-à-dire qu’elle ne peut pas être répétée plusieurs fois dans la liste du client. Heureusement pour Guy, il n’y avait qu’un seul enregistrement pour chaque facture. Dans les factures professionnelles et les numéros de bon de commande sont presque toujours uniques à une seule transaction, ils sont donc excellents à utiliser comme valeurs de recherche. D’autres exemples sont les codes de produit, les numéros de client ou de compte, les numéros d’employé ou les codes de département. Il est toujours recommandé de vérifier qu’il n’y a pas de doublons avant d’effectuer votre VLOOKUP.
• La valeur de recherche devait être située dans la première colonne de la liste dans le classeur du client. Malheureusement, ce n’était pas le cas. Il était situé dans la 2ème colonne (colonne B), nous avons donc coupé et collé la colonne devant le numéro de P / O. colonne. C’est une règle importante lors de l’utilisation de la fonction VLOOKUP. Si la valeur de recherche ne figure pas dans la première colonne, la recherche ne fonctionnera pas.

Une fois que nous avions décidé de notre Valeur de recherche et réorganisé la liste dans le classeur du client, nous étions prêts à créer notre VLOOKUP.

Étape 2: Insertion de la fonction VLOOKUP

Le ‘V’ dans VLOOKUP signifie « vertical ». Une recherche verticale est utilisée pour rechercher des données spécifiques dans la première colonne d’une table de données. Une fois qu’il a trouvé la ligne qui contient les données que vous recherchez, il rebondit sur une autre colonne de la même table de données et renvoie des informations à partir de celle-ci.

Nous avons décidé d’insérer la fonction VLOOKUP dans le classeur de Guy. De cette façon, nous pourrions utiliser la fonction pour référencer le numéro de facture de sa liste et vérifier le même numéro de facture dans la première colonne de la liste du client. S’il trouvait une correspondance, il retournerait alors dans la cellule le montant que le client avait par rapport au numéro de facture. Nous nous retrouvions alors avec le montant de Guy et le montant du client dans la même feuille de calcul. Nous pourrions alors facilement comparer les deux et déterminer quelles transactions ne correspondaient pas.

Nous avons créé un nouvel en-tête de colonne appelé « Enregistrement du client », le plaçant à la fin de la liste existante de Guy. En plaçant le sélecteur de cellule sur la cellule E5, nous avons inséré la fonction VLOOKUP.

Pour insérer la fonction, nous avons cliqué sur l’onglet Formules, puis dans le groupe Bibliothèque de fonctions, nous avons cliqué sur le bouton Insérer une fonction.

Astuce: la fonction VLOOKUP peut également être insérée en cliquant sur le bouton Insérer une fonction dans la barre de formule.

Dans la zone Sélectionner une catégorie, nous avons sélectionné Recherche et référence. Dans la zone de liste Sélectionner une fonction, nous avons sélectionné VLOOKUP, puis cliqué sur OK.

À ce stade, nous devions entrer les valeurs appropriées dans les cases Arguments de la fonction. Je vais vous expliquer chacun des arguments.

• Lookup_value – Il s’agissait du numéro de facture détenu dans la cellule C5. Une fois que nous l’avons entré dans la zone de valeur de recherche, Excel l’a conservé en mémoire et a attendu que nous terminions les arguments restants.

• Table_array – Le tableau de table est la liste qui contient les détails de la transaction sur la feuille de calcul du client. VLOOKUP recherchera une correspondance pour la « Valeur de recherche » dans la première colonne du tableau de table. Guy a navigué vers la feuille de calcul du client, puis a cliqué et fait glisser sur toute la zone contenant tous les détails des transactions. Il a inclus la colonne « Date », mais A4: C26 aurait également été bien car la colonne « Montant » est à l’intérieur de cette plage. Veuillez noter que Guy a rendu la gamme absolue afin que la formule puisse facilement être copiée.

• Col_index_num – C’est la colonne à partir de laquelle Guy voulait parcourir le montant de la facture du client. Il s’agissait de la colonne 3 de la liste des transactions du client. Les colonnes sont numérotées de gauche à droite, la première colonne de la plage de tableaux étant la colonne 1. Veillez à ne pas mélanger les numéros de colonne et les lettres de colonne. Même si le montant de la facture est conservé dans la colonne « C », Excel ne souhaite que l’emplacement du numéro d’index de la colonne, pas la lettre de la colonne.

• Range_lookup – La recherche de plage contrôle si vous recherchez une correspondance exacte avec votre « Valeur de recherche ». J’ai expliqué à Guy que s’il entrait TRUE ou laissait la case vide, Excel rechercherait son numéro de facture, mais s’il ne pouvait pas trouver de correspondance, il renverrait la valeur la plus grande suivante qui est inférieure au numéro de facture. Par exemple, s’il ne pouvait pas trouver la facture 2466, il trouverait l’enregistrement pour 2465 et retournerait le montant pour cela à la place. Pour que cela fonctionne, la liste des clients devait d’abord être triée par numéro de facture. Cependant, si ‘FALSE’ est entré dans la boîte, Excel recherchera une correspondance exacte pour la valeur de recherche. Guy voulait une correspondance exacte avec son numéro de facture alors il a entré FAUX dans la boîte. J’ai également transmis la pointe que vous pouvez entrer 0 (zéro) à la place du mot « FAUX » et Excel fera la même chose.

Avec tous les arguments entrés, Guy a cliqué sur OK.

Excel a ensuite parcouru chacune des valeurs fournies dans la zone argument. La fonction utilisait le numéro de facture (Valeur de recherche) pour rechercher un numéro de facture correspondant dans la colonne la plus à gauche des enregistrements du client (tableau de tables). Une fois le numéro de facture trouvé, il a ensuite sauté sur le nombre spécifié de colonnes (numéro d’index de colonne) sur la même ligne pour renvoyer le montant de la facture.

Heureusement, comme vous pouvez le voir ci-dessous, le client avait la même valeur de facture que Guy.

Guy a ensuite copié la fonction VLOOKUP pour toutes les transactions de facture.
En quelques secondes, il pouvait voir trois erreurs #N / A (erreurs de valeur non disponibles). Cela a montré où le client n’avait pas réellement d’enregistrement de ces factures. Il a ensuite rapidement constaté les différences entre les montants de sa facture et ceux du client. Immédiatement, il a pu voir que la facture 2464 était courte de 100,00 $, très probablement une erreur de saisie.

Étape 3: la double vérification

Nous avions maintenant comparé avec succès nos enregistrements avec ceux du client. Cependant, en tant que double vérification, nous avons ensuite décidé de faire une comparaison à partir des enregistrements du client, juste au cas où nous aurions manqué quelque chose.

Cette fois, nous avons créé le VLOOKUP dans la feuille de calcul du client. La première chose que nous devions faire était de déplacer la colonne de facture pour qu’elle soit la première colonne de notre liste de transactions (Tableau de tables). Après cela, nous avons ensuite inséré la fonction VLOOKUP dans une nouvelle colonne appelée « Notre enregistrement ».

Guy a copié la fonction vers le bas, puis a créé la colonne « Comparaison ». Notre double vérification a confirmé que le client avait sous-payé 100 $ US sur la facture 2464. Notre vérification a également montré que le client avait tout à fait possible d’enregistrer le numéro de facture de la facture 2466 comme 2646.

Conclusion

Guy peut maintenant revenir vers le client pour discuter des informations qui ont pu être saisies par erreur et fournir des copies des factures que le client n’avait pas d’enregistrement de réception.

Inutile de dire que la fonction VLOOKUP a permis à Guy et à ses collègues de travail d’économiser énormément de temps. Le responsable de la formation et du développement m’a contacté après la formation pour me dire que l’amélioration de l’efficacité du département avait permis à leur entreprise d’économiser 10 à 1000 dollars.

Nous couvrons la fonction VLOOKUP dans nos cours Excel Stage 3. Si vous avez affaire à des données volumineuses, cette formation est définitivement pour vous.