Fonctions

La fonction RECHERCHEX

Syntaxe

Description

Marche à suivre

• Entrez la fonction =RECHERCHEX dans une cellule.

• Sélectionnez la cellule qui contient la valeur cherchée.

• Entrez un point-virgule, puis sélectionnez la plage dans laquelle vous voulez que la fonction cherche la valeur_cherchée.

• Entrez un point-virgule, puis sélectionnez la plage de retour.

• Appuyez sur la touche d’entrée. Excel recherche, en correspondance exacte, la valeur en B10, dans la plage C2:C8, puis renvoie la valeur de la même ligne dans la plage A2:A8. Le résultat : Montréal.

Exercice

Téléchargez le fichier suivant : Exercice – RECHERCHEX (Excel). Utilisez la fonction RECHERCHEX pour déterminer le prénom et le nom de l’employé numéro 5002762. Utilisez à nouveau la fonction RECHERCHEX pour déterminer le numéro de l’employé qui a effectué 50 000 $ de ventes (si aucun employé n’a effectué exactement 50 000 $ de ventes, trouver l’employé qui a effectué le montant de vente immédiatement supérieur à 50 000 $).

La fonction TRANSPOSE

Syntaxe

Description

La fonction TRANSPOSE vous permet de transposer les données d’une ligne à une colonne et vice-versa.

Marche à suivre

• Entrez la fonction =TRANSPOSE dans une cellule.

• Sélectionnez les données que vous voulez transposer.

• Appuyez sur la touche d’entrée.

Validation de données

La validation de données permet d’assurer que les données saisies dans une cellule respectent certains critères préétablis. Ceci limite le nombre d’erreurs qui se produisent lors de la saisie des données et standardise le format de vos entrées. De plus, la validation de données facilite la manipulation de ces dernières en s’assurant qu’il n’y a pas de divergence dans l’orthographe des noms de produits, de vendeurs, de fournisseurs, etc.

Voici quelques façons d’utiliser la validation de données :

• Imposer que les données figurent entre deux valeurs
• Imposer que les données soient du format date
• Imposer que les données soient du format heure
• Imposer que les données soient numériques
• Imposer que les données proviennent d’une liste

Si un utilisateur tente d’entrer une donnée qui ne respecte pas le critère établi, la saisie sera refusée et un message d’avertissement apparaitra à l’écran.

Dans l’exemple de la marche à suivre ci-après, la validation de données se fait à partir d’une liste d’éléments consignés dans une plage. Cette façon de procéder permet de générer automatiquement une liste déroulante des options.

Marche à suivre

• Sélectionnez la plage de cellule à laquelle vous voulez appliquer la validation de données.

• Rendez-vous dans l’onglet « Données » du ruban et sélectionnez « Validation des données ».

• Choisissez l’option « Liste ».

• Indiquez la référence de votre plage source.

• Si vous voulez personnaliser votre message de saisie et votre alerte d’erreur, vous pouvez le faire en naviguant entre ces deux onglets dans la fenêtre contextuelle. Lorsque vous êtes satisfait, appuyez sur « Ok ».

• Le message de saisie apparaitra lorsque la cellule active correspond à une cellule pour laquelle la validation de données est appliquée.

• Si vous tentez d’entrer un nom qui ne figure pas dans la liste d’employés, Excel refusera la saisie.

• Appuyez sur la flèche pour révéler la liste déroulante des employés.

Exercice

Un groupe de bénévole est responsable d’effectuer la vente de billets pour une course qui aura lieu en juillet prochain. Les adhésions seront consignées dans un fichier Excel et vous voulez vous assurer que tous les bénévoles entreront les données de la même façon afin que vous puissiez éventuellement générer des rapports sur la vente de billets. Téléchargez le fichier Excel suivant : Exercice – Validation de données (Excel).

• Appliquez une validation de données à la colonne « Date d’inscription ».
• Appliquez une validation de données à la colonne « Distance ». Ajoutez le message d’erreur suivant : « La distance que vous avez inscrite ne respecte pas le format suggéré. »
• Entrez le nom de 10 bénévoles dans les cases prévues à cet effet.
• Appliquez une validation de données à la colonne « Bénévole ». Ajoutez le message de saisie suivant : « Entrez le nom du bénévole qui a traité l’inscription. »
• Le prix pour prendre part à la course fonctionne sur la base de dons. Le don minimal est de 20 $. Appliquez la validation appropriée à la colonne « Montant payé ».
• Tester vos validations en inscrivant 5 personnes à la course.

Gérer les doublons

En dépit de l’implantation de systèmes qui vise à réduire les incohérences et les répétitions entre les données, il est possible que des doublons se glissent dans votre chiffrier Excel. Un doublon est un élément redondant. À titre d’exemple, il pourrait s’agir d’une vente qui apparait à deux reprises.

Afin de gérer les doublons, nous allons aborder trois méthodes qui pourraient vous être utiles. Si vous visez à identifier les doublons, la mise en forme conditionnelle s’avère la méthode de choix. Si vous voulez supprimer les doublons, une telle fonction est disponible sous l’onglet « Données » du ruban. Si vous voulez plutôt générer une liste de tous les éléments uniques, alors la fonction UNIQUE est mieux adaptée à cette tâche.

Mise en garde : La qualité de vos données influera grandement sur l’efficacité de ces méthodes. Si l’orthographe est différente, Excel n’identifiera pas de doublons. Par exemple, dans la capture d’écran ci-après, la mise en forme conditionnelle a été appliquée sur la colonne A et aucune valeur en double n’a été identifiée. Cependant, nous pouvons conclure que le client à la ligne 3 et le client à la ligne 6 représentent la même personne. Dans le cas présent, une mise en forme conditionnelle des adresses courriel aurait révélé ceci.

Mise en forme conditionnelle

Marche à suivre :

• Sélectionnez la plage de cellule pour laquelle vous voulez identifier les doublons.

• Appuyez sur « Mise en forme conditionnelle ».

• Sélectionnez « Règles de surlignage des cellules » puis cliquez sur « Valeurs en double ».

• Appuyez sur « Ok ».

• Les doublons seront identifiés.

Supprimer les doublons

Marche à suivre :

• Sélectionnez la plage de cellule pour laquelle vous voulez supprimer les doublons.

• Appuyez sur « Supprimer les doublons ». (Cette option se retrouve sous l’onglet « Données » du ruban.)

• Une fenêtre contextuelle apparait, appuyez sur « Ok ».

• Excel vous indique le nombre de doublons repérés et supprime ces derniers. Appuyez sur « Ok ».

La fonction UNIQUE

Syntaxe

Marche à suivre

• Entrez la fonction =UNIQUE dans une cellule.

• Sélectionnez la plage de données pour laquelle vous voulez générer une liste des éléments uniques.

• Appuyez sur la touche d’entrée.

Exercice

Vous disposez d’une liste des ventes effectuées par un petit marché local au cours d’une journée donnée. Téléchargez le fichier suivant : Exercice – Doublons (Excel).

• Utilisez d’abord la mise en forme conditionnelle pour repérer les doublons.
• Indice : Appliquez la mise en forme conditionnelle à la colonne qui devrait renfermer des données uniques.
• Éliminez les doublons à l’aide de la fonctionnalité « Supprimer les doublons ».
• Utilisez la fonction UNIQUE pour obtenir une liste des aliments vendus.
• Que pouvez-vous conclure en regardant la liste des éléments uniques? Quelle mesure auriez-vous pu prendre afin de prévenir un tel résultat?

L’assistant conversion (text to columns)

L’assistant conversion (ou l’assistant texte) vous permet de diviser du texte entre plusieurs colonnes. Ceci améliore la présentation des données et facilite la manipulation de ces dernières. L’utilité de cette fonction apparait lorsque vous exportez des données vers Excel. En faisant ainsi, il est possible que plusieurs champs soient réunis dans une seule cellule et séparés par un signe de ponctuation telle qu’une virgule ou un point-virgule. Grâce à l’assistant conversion, vous n’aurez pas besoin de diviser manuellement chaque entrée.

Marche à suivre

Mise en contexte : Vous utilisez un système de gestion des données de vos employés. Vous exportez le registre des employés dans un fichier Excel. Cependant, lorsque vous ouvrez le chiffrier résultant de l’exportation, vous vous apercevez que tous les renseignements concernant un employé sont regroupés dans une cellule. Sachant que les données apparaissent dans l’ordre suivant : « Nom, Prénom, Numéro d’employé, Service » divisez ces renseignements en 4 colonnes.

• Sélectionnez vos données.

• Appuyez sur « Convertir » dans l’onglet « Données » du ruban.

• Si ce n’est pas déjà fait, sélectionnez « Délimité » puis appuyez sur « Suivant ».

• Sélectionnez le séparateur de vos données. Ici, il s’agit de virgules. Appuyez ensuite sur « Suivant ».

• La dernière étape vous permet de définir le format des données de chaque colonne. Lorsque vous êtes satisfait, appuyez sur « Fin ».

• Vos données se séparent en colonnes.

Nouveauté : Vous pouvez désormais accéder à la fonction « Convertir en colonnes » sur Excel pour le Web.

Exercice

Vous êtes bénévole pour un club universitaire. Le président vous demande de dresser une liste des membres du club et de calculer le nombre de membres inscrits dans chaque spécialisation du baccalauréat en sciences commerciales. Pour ce faire, vous vous rendez sur la plateforme en ligne où vous recevez les formulaires d’adhésion des membres ainsi que leur paiement. Vous songez à ouvrir un tableur Excel pour y saisir manuellement les renseignements des membres lorsque vous apercevez la possibilité d’exporter les données dans un fichier Excel. Vous exportez les données en pensant que votre tâche sera très simple. Cependant, lorsque vous ouvrez le classeur, vous vous apercevez que les renseignements associés à chaque membre ont été fusionnés. Téléchargez le chiffrier suivant : Exercice – Assistant conversion (Excel). Utilisez l’assistant conversion pour diviser les données en quatre colonnes intitulées respectivement : nom, prénom, année d’étude, programme d’étude et numéro d’étudiant. Déterminer ensuite le nombre d’étudiants inscrits dans chaque spécialisation.

Travailler efficacement dans un chiffrier

Lorsque vous apercevez un employé chevronné travailler dans un fichier Excel, vous remarquerez que ce dernier se déplace très rapidement parmi les données afin d’accomplir ses tâches. Outre ses années d’expérience qui explique son gain de productivité, ce dernier exploite fort probablement des outils et des raccourcis d’Excel qui rendent la navigation dans le chiffrier beaucoup plus simple. Dans cette section, nous visons à vous présenter quelques fonctions et techniques qui permettront de vous démarquer en matière d’efficience.

Figer les volets

Lorsque vous travaillez dans un fichier volumineux, il est facile de perdre de vue les en-têtes. Vous vous retrouvez alors constamment à défiler vers le haut ou la gauche de la page afin d’obtenir la signification des données renfermées dans une cellule. Heureusement, la fonction « Figer les volets » vous évite ces va-et-vient laborieux en figeant la ligne ou la colonne qui contient vos en-têtes.

Marche à suivre

Note : la fonction « Figer les volets » se retrouve dans l’onglet « Affichage » du ruban.

Figer la première ligne

• Appuyez sur « Figer la ligne supérieure ».

Figer plusieurs lignes

• Sélectionnez la ligne qui se retrouve immédiatement sous la ligne que vous voulez figer.

• Appuyez sur « Figer les volets ».

Figer la première colonne

• Appuyez sur « Figer la première colonne

Figer plusieurs colonnes

• Sélectionnez la colonne qui se retrouve immédiatement à droite de la colonne que vous voulez figer.

• Appuyez sur « Figer les volets ».

Figer des lignes et des colonnes

• Sélectionnez la cellule qui se retrouve immédiatement sous la ligne que vous voulez figer et immédiatement à droite de la colonne que vous voulez figer.

• Appuyez sur « Figer les volets ».

Libérer des lignes ou des colonnes

• Appuyez sur « Libérer les volets ».

Comparer des données

Pour comparer des données qui se retrouvent dans deux feuilles différentes d’un même classeur, vous pouvez ouvrir une nouvelle fenêtre puis réorganiser les fenêtres afin que chacune occupe la moitié de votre écran. Vous utilisez peut-être déjà une fonction similaire en classe lorsque vous prenez des notes dans un document qui prend la moitié de votre écran et que vous visionnez les diapositives du cours sur l’autre moitié de votre écran. Le concept est simple, vous obtenez deux fenêtres ouvertes côte à côte. Cependant, il revêt d’une grande utilité lorsque vous disposez par exemple des ventes de chaque mois dans des feuilles différentes et que vous voulez comparer rapidement les ventes de deux mois.

Pour comparer des données qui se retrouvent dans deux classeurs distincts, il existe l’application Comparer les feuilles de calcul de Microsoft. Cette dernière permet de générer un rapport sur les différences observées entre deux classeurs. Cela est utile lorsque vous disposez de plusieurs versions d’un même classeur. Par exemple, si vous exportez chaque mois la liste des courriels des clients pour des fins de marketing, vous allez vous retrouver avec plusieurs fichiers Excel. Pour repérer rapidement les nouveaux clients et ceux qui se sont retirés de la liste d’envoi, vous pouvez utiliser l’application Comparer les feuilles de calcul. Notez que cette application est seulement disponible pour les versions suivantes d’Excel : Office Professionnel Plus 2013, 2016 et 2019 ou Applications Microsoft 365 pour les entreprises.