Trouver des écarts dans Excel - 5 techniques simples expliquées

Lorsque vous travaillez avec de grands ensembles de données dans Microsoft Excel, il est inévitable de rencontrer des écarts dans les données. L'identification et la correction de ces incohérences sont essentielles pour garantir la précision des rapports et des analyses.

La bonne nouvelle? Excel est livré avec une variété d'outils et de techniques pour vous aider à trouver et résoudre ces problèmes. Dans cet article, vous découvrirez certaines des méthodes les plus efficaces. Cela inclut le filtrage, la mise en forme conditionnelle et l'utilisation de formules avancées.

En intégrant ces techniques dans votre flux de travail quotidien, vous gagnerez en efficacité dans la gestion et l'analyse de vos données. En fin de compte, cela conduit à une meilleure prise de décision et à de meilleurs résultats pour vos projets.

Table des matières

• Comprendre les écarts dans Excel
• 5 façons de trouver des écarts dans Excel
• #1 - Trouver des divergences via la révision manuelle dans Excel
• #2 - Utiliser le filtre dans Excel pour détecter et corriger les données incohérentes
• 3 # - Mettre en évidence les écarts avec le formatage conditionnel
• #4 – Trouver des divergences avec les fonctions avancées d'Excel
  • Fonctions IF et IS
  • RECHERCHEV, RECHERCHEH ET RECHERCHEX
  • CORRESPONDRE
• #5 - Utilisation des compléments Excel pour la détection des écarts
• Comment pouvez-vous créer des règles de validation des données pour éviter les écarts ?
• Notre dernier mot

Comprendre les écarts dans Excel

Les écarts dans les lignes et les colonnes peuvent résulter d'erreurs de saisie humaines, d'entrées en double ou d'irrégularités dans le formatage des données.

Jetez un oeil aux données suivantes. Vous remarquerez que le sexe de Walter White et Wayne Cena est mal orthographié - Mael au lieu de Male. En effet, il est simple de remarquer et de corriger les erreurs dans ce scénario. Mais ce n'est pas le cas avec les feuilles de calcul contenant des milliers de lignes et de colonnes.

Pour rechercher et gérer ces écarts, vous pouvez utiliser les fonctionnalités Excel suivantes :

• Filtres : appliquez des filtres à des colonnes spécifiques pour afficher des valeurs uniques et repérer rapidement les données incohérentes.
• Mise en forme conditionnelle : utilisez des règles de mise en forme pour mettre en surbrillance les cellules qui répondent à des critères spécifiques ou des données non concordantes entre les colonnes.
• Fonctions Excel avancées : utilisez des fonctions telles que MATCH et VLOOKUP pour comparer des ensembles de données et identifier les écarts entre eux.

En comprenant comment utiliser ces fonctionnalités dans Excel, vous pouvez détecter et corriger efficacement les écarts dans vos ensembles de données.

5 façons de trouver des écarts dans Excel

Nous aimons aborder les écarts dans Excel de 5 manières principales :

1. Examen manuel
2. Filtration
3. Mise en forme conditionnelle
4. Fonctions Excel avancées
5. Compléments Excel

Passons en revue chacun d'eux afin que vous puissiez trouver la meilleure façon qui vous convient.

#1 - Trouver des divergences via la révision manuelle dans Excel

Lorsque vous travaillez avec Excel , une révision manuelle peut être une méthode utile pour identifier les écarts dans vos données. Ce processus nécessite un examen attentif des ensembles de données pour repérer toute incohérence ou erreur.

Voici quelques étapes que vous pouvez suivre pour effectuer un examen manuel :

• Commencez par bien comprendre les ensembles de données avec lesquels vous travaillez. Familiarisez-vous avec les lignes, les colonnes et la mise en forme.
• Faites défiler les données et portez une attention particulière aux informations présentées dans chaque cellule. Vous pouvez utiliser des outils tels que la mise en surbrillance des cellules avec une couleur de remplissage, la mise en forme du texte ou la mise en forme conditionnelle pour faciliter la lecture des données.
• Comparez une ligne à une autre ou une colonne à une autre. Recherchez les informations non concordantes, les entrées en double, etc. qui peuvent entraîner des inexactitudes dans vos feuilles Excel.
• Lorsque vous identifiez des écarts, créez une liste ou utilisez des commentaires pour les documenter. Plus tard, ceux-ci peuvent servir de références importantes pour corriger les problèmes.

Lors de l'examen manuel des données pour les écarts dans une feuille de calcul Excel, la patience est la clé. Ce processus prend du temps. Mais c'est un bon point de départ pour identifier les erreurs potentielles.

#2 - Utiliser le filtre dans Excel pour détecter et corriger les données incohérentes

L'un des moyens les plus simples de détecter des données incohérentes consiste à utiliser le filtre d'Excel .

Avec Filter, nous isolerons les données incorrectes dans la colonne "Sexe" de l'ensemble de données suivant :

1. Sélectionnez Trier et filtrer dans l' onglet Accueil .

2. Choisissez Filtre - assurez-vous que votre curseur se trouve dans la plage des données.

3. Cliquez sur la flèche de la liste déroulante sur la colonne contenant des données incohérentes.

4. Maintenant, désélectionnez toutes les données correspondantes correctes. Dans notre cas, c'est Male . Ensuite, cliquez sur OK pour enregistrer les modifications.

5. Votre feuille de calcul Excel n'affichera désormais que les lignes avec des valeurs incorrectes. Pour les corriger, cliquez sur Rechercher et sélectionner dans l' onglet Accueil .

6. Cliquez sur Remplacer lorsque la liste déroulante apparaît.

7. Tapez la valeur incorrecte dans Rechercher et la bonne dans Remplacer par . Cliquez ensuite sur Remplacer tout .

8. Une boîte de dialogue apparaîtra confirmant les modifications. Cliquez sur OK pour continuer.

3 # - Mettre en évidence les écarts avec le formatage conditionnel

Une autre façon d'identifier les différences entre les colonnes et les lignes consiste à utiliser la mise en forme conditionnelle.

Vous pouvez accéder à des règles telles que les cellules de surbrillance, le haut / le bas, les barres de données, les échelles de couleurs et les ensembles de fer. De plus, vous pouvez créer une nouvelle règle et gérer ou effacer les règles précédentes.

Nous allons utiliser un exemple de tableau pour vous montrer la puissance de la mise en forme conditionnelle :

1. Sélectionnez votre tableau ou vos lignes et colonnes préférées. Nous choisissons la colonne A (société) et la colonne B (pays). Ensuite, sélectionnez Mise en forme conditionnelle dans l' onglet Accueil .

2. Sélectionnez un type de règle, puis la règle. Nous sélectionnons les règles de cellule de surbrillance et les valeurs en double…

3. Maintenant, nous devons choisir de mettre en surbrillance les valeurs en double ou uniques et la couleur de remplissage et la police pour formater les cellules. Une fois terminé, cliquez sur OK .

4. Toutes les valeurs en double sont maintenant mises en surbrillance avec Light Red Fill with Dark Red Text.

#4 – Trouver des divergences avec les fonctions avancées d'Excel

Fonctions IF et IS

Pour comparer les cellules et identifier les différences, vous pouvez utiliser les fonctions IF et IS.

Par exemple, nous avons utilisé la formule =IF(A2=B2,"MATCH","NOT MATCHED")pour comparer les cellules A2 et B2. Et nous avons fait glisser la formule pour l'appliquer à d'autres cellules. Ainsi, des cellules identiques renvoient MATCH sinon NOT MATCHED .

Vous pouvez combiner IF avec des fonctions IS telles que ISNUMBER ou ISTEXT pour rechercher des types de données spécifiques. Par exemple, la formule =IF(ISNUMBER(B2), "Number", "NaN")renverra Nombre dans la colonne c si B2 est numérique et NaN sinon.

RECHERCHEV, RECHERCHEH ET RECHERCHEX

Les fonctions VLOOKUP (Vertical) et HLOOKUP (Horizontal) sont utiles lors de la comparaison de valeurs dans différentes colonnes ou lignes de deux tables. Pour ce faire, utilisez la formule suivante : =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) ou =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).

Remplacez lookup_value par la valeur que vous souhaitez trouver dans l'autre table, table_array par la plage de cellules de la deuxième table, col_index_num ou row_index_num par le numéro d'index de la colonne ou de la ligne à partir de laquelle vous souhaitez renvoyer une valeur, et [range_lookup] par FALSE pour les correspondances exactes ou TRUE pour une correspondance approximative.

Dans le didacticiel suivant, nous utilisons la fonction VLOOKUP sur la colonne E pour comparer les noms des employés dans la colonne A et la colonne D afin de retirer leurs salaires de la colonne B si une correspondance est trouvée.

XLOOKUP est une version actualisée de VLOOKUP et HLOOKUP disponible sur Excel 2021 et Excel 365. Pour la formule XLOOKUP est : =XLOOKUP(lookup_value, lookup_array, return_array,[if_not_found]).

Remplacez lookup_value par la valeur que vous souhaitez trouver dans l'autre table, lookup_array par la plage de cellules à rechercher dans la deuxième table, return_array par le tableau à partir duquel vous souhaitez renvoyer une valeur et [if_nout_found] par une valeur de texte si aucune correspondance les valeurs sont trouvées.

Voici un exemple rapide de XLOOKUP avec les mêmes tables :

CORRESPONDRE

La fonction MATCH peut également être utilisée pour comparer deux listes en cas de divergences.

La fonction MATCH, =MATCH(lookup_value, lookup_array, [match_type]), recherche un élément spécifié dans une plage et renvoie la position relative de l'élément dans cette plage.

Ici, nous trouvons la position des pastèques dans le tableau A2: A8 en utilisant MATCH. Nous utilisons match_type comme 0 pour trouver la première valeur qui est exactement égale à lookup_value .

#5 - Utilisation des compléments Excel pour la détection des écarts

Excel propose divers compléments et outils intégrés qui peuvent vous aider à détecter et à analyser les écarts plus efficacement. Dans cette section, nous aborderons certains de ces compléments utiles.

Tout d'abord, envisagez d'utiliser le complément Spreadsheet Inquire . Cela peut vous aider à comparer deux classeurs et à mettre en évidence les différences cellule par cellule. Pour activer le complément Spreadsheet Inquire :

1. Cliquez sur l' onglet Fichier .

2. Sélectionnez Options , puis cliquez sur la catégorie Compléments .

3. Dans la zone Gérer , sélectionnez Compléments COM et cliquez sur Accéder .

4. Cochez la case Demande de feuille de calcul et cliquez sur OK .

5. Une fois activé, accédez à l' onglet Inquire pour utiliser la commande Compare Files .

Un autre outil utile pour la détection des divergences est le Analysis ToolPak . Ce complément fournit des fonctions statistiques avancées et des outils d'analyse de données, qui peuvent être utiles lors de l'analyse des écarts.

Pour activer Analysis ToolPak, suivez les mêmes étapes que pour activer le complément Spreadsheet Inquire. Mais sélectionnez Compléments Excel dans la case Gérer et cochez la case Analysis ToolPak .

En résumé, Excel propose divers compléments pour vous aider à détecter et à gérer les écarts dans vos données. Vous pouvez améliorer la précision de vos données en vous familiarisant avec ces outils et formules.

Comment pouvez-vous créer des règles de validation des données pour éviter les écarts ?

Pour créer des règles de validation des données dans Excel qui peuvent aider à éviter les écarts, procédez comme suit :

1. Sélectionnez les cellules que vous souhaitez restreindre. Il peut s'agir d'une seule cellule, d'une plage de cellules ou d'une colonne entière.

2. Accédez à l' onglet Données de la barre d'outils et cliquez sur le bouton Validation des données (marqué par deux cases horizontales, une coche verte et un cercle barré de rouge).

3. Dans la fenêtre Validation des données, assurez-vous que vous êtes sur l' onglet Paramètres . Ici, vous aurez la possibilité de définir vos critères de validation en fonction de vos besoins.

Il existe différents types de critères de validation disponibles dans Excel, dont certains incluent :

• Nombre entier – autorise uniquement les nombres entiers dans une plage spécifiée.
• Décimal – autorise uniquement les nombres décimaux dans une plage spécifiée.
• Liste – limite l'entrée à une liste prédéfinie de valeurs acceptables.
• Date - nécessite des dates dans une plage de dates spécifique.
• Heure – n'accepte que les heures comprises dans une plage horaire particulière.
• Longueur du texte – applique des contraintes sur la longueur du texte saisi.
• Personnalisé – vous permet de créer des règles de validation personnalisées à l'aide de formules Excel.

Après avoir sélectionné les critères de validation appropriés, spécifiez les paramètres selon vos besoins. Par exemple, si vous choisissez Nombre entier, vous devrez définir le minimum et le maximum avec un validateur de plage de données comme entre, égal à, inférieur à, etc.

Outre les critères de validation, vous pouvez également configurer des messages d'erreur personnalisés et des conseils de saisie pour aider les utilisateurs à comprendre et à respecter vos règles de validation. Pour ce faire, passez aux onglets Message d'entrée et Alerte d'erreur pour saisir les messages souhaités.

En implémentant des règles de validation des données, vous réduisez considérablement la probabilité d'incohérences des données et améliorez la précision globale de vos feuilles de calcul Excel.

Notre dernier mot

Dans cet article, vous avez appris diverses méthodes pour rechercher et résoudre les écarts dans vos données Excel. Récapitulons ces stratégies clés :

• Utilisation des listes déroulantes Filtre dans les colonnes pour identifier les valeurs uniques et les données incohérentes.
• Utilisation de la mise en forme conditionnelle dans les cellules sélectionnées pour mettre en évidence les écarts à l'aide d'un ensemble de modèles de règles.
• Application de fonctions avancées d'Excel telles que MATCH , VLOOKUP , XLOOKUP , etc. pour vérifier les données dans deux listes ou tables.
• Utilisation de compléments Excel pour détecter les écarts.
• Configuration des règles de validation des données pour éviter les erreurs dans les données

Avec ces techniques dans votre ensemble de compétences Excel, vos données seront plus précises et plus fiables.

N'oubliez pas de pratiquer ces méthodes régulièrement pour devenir plus efficace dans la détection des écarts dans vos feuilles de calcul.

Continuez à perfectionner vos compétences en vous inscrivant à notre et continuez à explorer de nouvelles fonctionnalités pour devenir encore plus compétent dans Excel.