27 Annexe : Complément Excel

Objectifs d’apprentissage

Cette section se concentre sur d’autres concepts qui peuvent être utiles en pratique. Les sujets abordés sont les suivants :

  • Fonctions
    • La fonction RECHERCHEX
    • La fonction TRANSPOSE
  • Validation de données
  • Gérer les doublons
    • Mise en forme conditionnelle
    • Supprimer les doublons
    • La fonction UNIQUE
  • L’assistant conversion (text to columns)
  • Travailler efficacement dans un chiffrier
    • Figer les volets
    • Comparer des données

Fonctions

La fonction RECHERCHEX

Syntaxe

=RECHERCHEX(valeur_cherchée; tableau_recherche; tableau_renvoyé; [si_non_trouvé]; [mode_correspondance]; [mode_recherche])

Description

La fonction RECHERCHEX vise à remplacer la fonction RECHERCHEV. Elle combine les possibilités de recherche à l’horizontale et à la verticale qui sont associés respectivement à la fonction RECHERCHEH et RECHERCHEV.

Afin de bien comprendre la fonction RECHERCHEX et ses différences avec les autres fonctions de recherche, voici quelques éléments pertinents à son fonctionnement :  

  • La valeur cherchée peut être dans n’importe quelle colonne.
  • La colonne de retour peut être à droite ou à gauche de la colonne renfermant la valeur cherchée.
  • On doit indiquer la référence (et non le numéro) de la colonne dans laquelle on veut effectuer une recherche. La fonction tolère donc la suppression ou l’ajout de colonnes.
  • La correspondance exacte est utilisée par défaut.

Par ailleurs, la fonction RECHERCHEX possède des arguments additionnels. Il est notamment possible de renvoyer une valeur de votre choix si la fonction ne trouve pas la valeur cherchée.

Marche à suivre

  • Entrez la fonction =RECHERCHEX dans une cellule.

Partie d’un classeur dans Excel avec 3 colonnes intitulées Ville, Rue et Code postal. La colonne A contient les villes : Ottawa, Toronto, Vancouver, Montréal, Calgary, Moncton et Québec. La colonne B contient les rues : Rue Wellington, Av. Spadina, 15e Av., Av. Papineau, Crois. Evergreen, Rue Grant et Rue Saint-Louis. La colonne C contient les codes postaux : K1A 0A2, M5V 3Y9, V6R 3B3, H2K 4J7, T2Y 3R3, E1A 6Z3 et G1R 3Y8. Dans la cellule A10, on retrouve le texte Code postal puis dans la cellule B10 on retrouve le code postal H2K 4J7. Dans la cellule A11, on retrouve le texte Ville et dans la cellule B11 on retrouve la formule =RECHERCHEX(.

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

Partie d’un classeur dans Excel avec 3 colonnes (A, B et C) intitulées respectivement Ville, Rue et Code postal. Dans la cellule A10, on retrouve le texte Code postal puis dans la cellule B10 on retrouve le code postal H2K 4J7. Dans la cellule A11, on retrouve le texte Ville et dans la cellule B11 on retrouve la formule =RECHERCHEX(B10.

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

Partie d’un classeur dans Excel avec 3 colonnes (A, B et C) intitulées respectivement Ville, Rue et Code postal. Dans la cellule A10, on retrouve le texte Code postal puis dans la cellule B10 on retrouve le code postal H2K 4J7. Dans la cellule A11, on retrouve le texte Ville et dans la cellule B11 on retrouve la formule =RECHERCHEX(B10;C2:C8.

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

Partie d’un classeur dans Excel avec 3 colonnes (A, B et C) intitulées respectivement Ville, Rue et Code postal. Dans la cellule A10, on retrouve le texte Code postal puis dans la cellule B10 on retrouve le code postal H2K 4J7. Dans la cellule A11, on retrouve le texte Ville et dans la cellule B11 on retrouve la formule =RECHERCHEX(B10;C2:C8;A2:A8.

  • 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.

Partie d’un classeur dans Excel avec 3 colonnes intitulées Ville, Rue et Code postal. La colonne A contient les villes : Ottawa, Toronto, Vancouver, Montréal, Calgary, Moncton et Québec. La colonne B contient les rues : Rue Wellington, Av. Spadina, 15e Av., Av. Papineau, Crois. Evergreen, Rue Grant et Rue Saint-Louis. La colonne C contient les codes postaux : K1A 0A2, M5V 3Y9, V6R 3B3, H2K 4J7, T2Y 3R3, E1A 6Z3 et G1R 3Y8. Dans la cellule A10, on retrouve le texte Code postal puis dans la cellule B10 on retrouve le code postal H2K 4J7. Dans la cellule A11, on retrouve le texte Ville et dans la cellule B11 on retrouve la ville 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

= TRANSPOSE(tableau)

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.

Partie d’un classeur Excel. La cellule A1 contient le titre de la colonne produit. La plage A2:A5 contient les lettres A, B, C et D. La cellule B1 contient le titre de la colonne stock (unités). La plage B2:B5 contient les nombres 1000, 7000, 14000 et 16000. La cellule D1 contient la formule =TRANSPOSE(.

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

Partie d’un classeur Excel. La cellule A1 contient le titre de la colonne produit. La plage A2:A5 contient les lettres A, B, C et D. La cellule B1 contient le titre de la colonne stock (unités). La plage B2:B5 contient les nombres 1000, 7000, 14000 et 16000. La cellule D1 contient la formule =TRANSPOSE(A1:B5.

  • Appuyez sur la touche d’entrée.

Partie d’un classeur Excel. La cellule A1 contient le titre de la colonne produit. La plage A2:A5 contient les lettres A, B, C et D. La cellule B1 contient le titre de la colonne stock (unités). La plage B2:B5 contient les nombres 1000, 7000, 14000 et 16000. À droite, la cellule D1 contient le titre de la ligne produit. La plage E1:H1 contient les lettres A, B, C et D. La cellule D2 contient le titre de la ligne stock (unités). La plage E2:H2 contient les nombres 1000, 7000, 14000 et 16000.

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.

Partie d’un classeur Excel. Les titres des colonnes A à E sont respectivement Date, Employé, Heure d’arrivée, Heure de départ et Temps travaillé. Le titre de la colonne G est Liste d’employés. La plage G2:G10 contient des noms d’employés. La colonne B est sélectionnée.

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

Capture d’écran d’une partie du ruban d’Excel. L’icône Validation des données dans l’onglet Données est sélectionnée. Parmi les choix du menu, l’option Validation des données... est sélectionnée.

  • Choisissez l’option « Liste ».

Fenêtre intitulée Validation des données. L’onglet Options est sélectionné et l’on y retrouve les critères de validation. Le champ Autoriser est encadré en rouge et contient l’option Liste. Les cases Ignorer si vide et Liste déroulante dans la cellule sont cochées. Le champ Source est vide.

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

Fenêtre intitulée Validation des données. L’onglet Options est sélectionné et l’on y retrouve les critères de validation. Le champ Autoriser contient l’option Liste. Les cases Ignorer si vide et Liste déroulante dans la cellule sont cochées. Le champ Source contient la formule =$G$2:$G$10. On voit la partie du classeur qui contient la plage G2:G10. Il s’agit des noms des employés.

  • 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 ».

Fenêtre intitulée Validation des données. L’onglet Message de saisie est sélectionné. La case « Afficher ce message de saisie quand la cellule est sélectionnée » est cochée. On retrouve le texte « Message de saisie à afficher quand la cellule est sélectionnée : ». En dessous, le champ titre contient le mot Employé. Le champ message de saisie contient le texte : « Entrez le prénom et le nom de l’employé ».

Fenêtre intitulée Validation des données. L’onglet Alerte d’erreur est sélectionné. La case « Afficher un message d’erreur lorsque des données incorrectes sont entrées » est cochée. On retrouve le texte « Quand des données non valides sont saisies, afficher ce message d’erreur : ». En dessous, le champ Style contient l’option Stop. Le champ Titre contient le titre Employé introuvable. Le champ Message d’erreur contient le texte « Le nom d’employé que vous avez saisi ne figure pas dans la liste d’employés. »

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

Partie d’un classeur Excel. Il y a 3 titres de colonnes, soit Date (colonne A), Employé (colonne B) et Heure d’arrivée (colonne C). La cellule B2 est sélectionnée. À droite de cette cellule, il y a un carré avec un triangle qui pointe vers le bas. En bas de cette cellule, il y a un carré jaune pâle dans lequel il est écrit : « Employé. Entrez le prénom et le nom de l’employé. »

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

Partie d’un classeur Excel. Il y a un nom entré dans la première cellule de la colonne Employé. La liste des employés est à droite (et ne contient pas ce nom). Une fenêtre affiche le message : « Employé introuvable. Le nom d’employé que vous avez saisi ne figure pas dans la liste d’employés. »

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

Partie d’un classeur Excel. La première cellule en dessous du titre de colonne Employé est sélectionnée. Il y a une liste déroulante affichant les noms 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.

Partie d’un classeur Excel. La colonne A est intitulée Client et contient les prénoms et les noms de clients. La colonne B est intitulée Adresse courriel et contient les adresses courriel des clients. Le client à la ligne 3 est Marc Roy et possède l’adresse courriel m.roy@yahoo.ca. Le client à la ligne 6 est Marc S. Roy et possède l’adresse courriel m.roy@yahoo.ca.

Mise en forme conditionnelle

Marche à suivre :

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

Partie d’un classeur Excel. On y retrouve les dates de la vente (colonne B), les articles vendus (colonne C), les numéros de série (colonne D) et les montants ($) (colonne E). Les données se retrouvent dans la plage B3:E10. La plage D3:D10 est sélectionnée.

  • Appuyez sur « Mise en forme conditionnelle ».

Capture d’écran d’une partie du ruban d’Excel. L’icône Mise en forme conditionnelle est encadrée en rouge. Elle se retrouve à la droite du groupe concernant le format de cellule et à la gauche de l’icône Mettre sous forme de tableau.

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

Capture d’écran d’Excel. L'icône Mise en forme conditionnelle est sélectionnée. Ensuite, l’option Règles de surlignage des cellules est sélectionnée dans la première liste. Puis, dans la deuxième liste, l’option Valeurs en double... est sélectionnée.

  • Appuyez sur « Ok ».

Fenêtre intitulée Nouvelle règle de mise en forme. Le champ Style contient l’option Classique. Le prochain menu déroulant contient l’option « Appliquer une mise en forme uniquement aux valeurs uniques ou en double ». L’option valeur en double est sélectionnée. La mise en forme est faite avec le remplissage rouge clair avec texte rouge foncé. Le bouton OK est encadré en rouge.

  • Les doublons seront identifiés.

Partie d’un classeur Excel. On y retrouve les dates de la vente (colonne B), les articles vendus (colonne C), les numéros de série (colonne D) et les montants ($) (colonne E). Les numéros de série AJO349 (doublons) à la ligne 3 et 7 sont dans une cellule avec couleur de remplissage rouge clair et du texte rouge foncé.

Supprimer les doublons

Marche à suivre :

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

Partie d’un classeur Excel. On y retrouve les dates de la vente (colonne B), les articles vendus (colonne C), les numéros de série (colonne D) et les montants ($) (colonne E). Les données se retrouvent dans la plage B3:E10. Cette plage est sélectionnée.

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

Partie du ruban Excel. Un encadré rouge entoure l’icône Supprimer les doublons. Cette icône se retrouve entre les icônes Remplissage instantané et Validation des données.

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

Fenêtre intitulée Supprimer les doublons. La case Ma liste contient des en-têtes est cochée. Les cases Sélectionner tout, Colonne B, Colonne C, Colonne D et Colonne E sont cochées.

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

Partie d’un classeur Excel. On y retrouve les dates de la vente (colonne B), les articles vendus (colonne C), les numéros de série (colonne D) et les montants ($) (colonne E). Les données se retrouvent dans la plage B3:E9. Une fenêtre contient le texte « Alerte. 1 valeurs en double trouvées et supprimées. Il reste 7 valeurs uniques. »

La fonction UNIQUE

Syntaxe

=UNIQUE(matrice; [by_col]; [exactly_once])

Marche à suivre

  • Entrez la fonction =UNIQUE dans une cellule.

Partie d’un classeur Excel. La colonne A, intitulée Noms d’élèves, contient la première lettre du prénom des élèves suivis de leur dernier nom. La colonne B, intitulée Pays d’origine, contient le pays d’origine des élèves. La colonne D est intitulée Liste des pays d’origine des élèves. La cellule D2 contient la formule =UNIQUE(.

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

Partie d’un classeur Excel. La colonne A, intitulée Noms d’élèves, contient la première lettre du prénom des élèves suivis de leur dernier nom. La colonne B, intitulée Pays d’origine, contient le pays d’origine des élèves. La colonne D est intitulée Liste des pays d’origine des élèves. La cellule D2 contient la formule =UNIQUE(B2:B10).

  • Appuyez sur la touche d’entrée.

Partie d’un classeur Excel. La colonne A, intitulée Noms d’élèves, contient la première lettre du prénom des élèves suivis de leur dernier nom. La colonne B, intitulée Pays d’origine, contient le pays d’origine des élèves. La colonne D est intitulée Liste des pays d’origine des élèves. La plage D2:D5 contient les noms de pays suivants : Canada, France, Irlande et Russie.

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.

Partie d’un classeur Excel. Le dernier nom, prénom, numéro d’employé et service de chaque employé se retrouvent dans la colonne A et sont séparés par des virgules. Par exemple : Lappan,James,678904,Ressources humaines.

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

Capture d’écran d’une partie du ruban d’Excel. L’icône Convertir est encadrée en rouge. Elle se retrouve à droite du groupe concernant les tris et les filtres, et à gauche de l’icône Remplissage instantané.

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

Fenêtre intitulée Assistant Conversion - Étape 1 sur 3. On y trouve le texte : L’Assistant Texte a déterminé que vos données sont de type Délimité. Si ce choix vous convient, cliquez sur Suivant, sinon choisissez le type qui décrit le mieux vos données. Parmi les types Délimité et Largeur fixe, c’est le type Délimité qui est sélectionné. On retrouve au bas de la fenêtre un aperçu des données sélectionnées. Toutes les données concernant un employé sont dans la même cellule et séparés par des virgules.

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

Fenêtre intitulée Assistant Conversion - Étape 2 sur 3. On y trouve le texte : Cette étape vous permet de choisir les séparateurs contenus dans vos données. Parmi les délimiteurs Tabulation, Point-virgule, Virgule, Espace et Autre, c’est le délimiteur Virgule qui est coché. Il y a une case « Interpréter des séparateurs identiques consécutifs comme uniques » qui peut être cochée. On retrouve au bas de la fenêtre un aperçu des données sélectionnées. Les données concernant un employé sont divisées en quatre colonnes.

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

Fenêtre intitulée Assistant Conversion - Étape 3 sur 3. On y trouve le texte : Cette étape vous permet de sélectionner chaque colonne et de définir le format des données. Parmi les formats de données en colonne Général, Texte, Date et Colonne non distribuée, c’est le format Général qui est sélectionné. Le champ Destination contient une référence à la cellule $A$1. On retrouve au bas de la fenêtre un aperçu des données sélectionnées. La première colonne est noircie. Le mot Standard se retrouve au-dessus de chacune des quatre colonnes.

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

Partie d’un classeur Excel. Le dernier nom des employés se retrouve dans la colonne A, leur prénom se retrouve dans la colonne B, leur numéro d’employé se retrouve dans la colonne C et leur service se retrouve dans la colonne D.

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

Partie d’un classeur Excel. L’option Convertir en colonnes... dans le ruban est encadrée en rouge. Le dernier nom, prénom, numéro d’employé et service de chaque employé se retrouvent dans la colonne I et sont séparés par des virgules. Par exemple : Lappan,James,678904,Ressources humaines. La plage renfermant les données de tous les employés est sélectionnée. Il y a une fenêtre intitulée Convertir en colonnes... Le séparateur sélectionné est la virgule. Un aperçu au bas de la fenêtre montre les données divisées en quatre colonnes.

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.

Capture d’écran de quelques onglets du ruban d’Excel. L’onglet Affichage est sélectionné. Il se retrouve à droite de l’onglet Révision et à gauche de Dites-le-nous.

Figer la première ligne

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

Capture d’écran d’une partie du ruban d’Excel. L’icône Figer la ligne supérieure est encadrée en rouge.

Figer plusieurs lignes

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

Partie d’un classeur Excel. On y trouve le titre Registre des fournisseurs 2021-2022. Ceci occupe la plage A1:C2. Les titres des colonnes sont : Nom du fournisseur, Produit et Coût par kg. Ces titres de colonnes se retrouvent dans la plage A3:C3. La ligne 4 est sélectionnée.

  • Appuyez sur « Figer les volets ».

Capture d’écran d’une partie du ruban d’Excel. L’icône Figer les volets est encadrée en rouge.

Figer la première colonne

  • Appuyez sur « Figer la première colonne ».

Capture d’écran d’une partie du ruban d’Excel. L’icône Figer la première colonne est encadrée en rouge.

Figer plusieurs colonnes

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

Partie d’un classeur Excel. On y trouve le titre Ventes écrit à la verticale dans la colonne A. Les titres Produit A, Produit B et Produit C sont écrits à la verticale dans la colonne B. La colonne C contient les titres Date, Quantité et Ventes répétés trois fois, soit une fois pour chaque produit. Les colonnes D et E contiennent des données. La colonne D est sélectionnée.

  • Appuyez sur « Figer les volets ».

Capture d’écran d’une partie du ruban d’Excel. L’icône Figer les volets est encadrée en rouge.

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.

Partie d’un classeur Excel. La colonne A contient les titres Produit A, Produit B et Produit C écrit à la verticale. La ligne 1 contient le titre Mois, suivi des mois de janvier, février et mars. La colonne B, après le mot Mois, contient les titres Coûts, Ventes et Bénéfices répétés trois fois, soit une fois pour chaque produit. La plage C2:E10 contient des données. La cellule C2 est sélectionnée.

  • Appuyez sur « Figer les volets ».

Capture d’écran d’une partie du ruban d’Excel. L’icône Figer les volets est encadrée en rouge.

Libérer des lignes ou des colonnes

  • Appuyez sur « Libérer les volets ».


Capture d’écran d’une partie du ruban d’Excel. L’icône Libérer les volets est encadrée en rouge.

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.

Capture d’écran. Le même classeur Excel est ouvert deux fois. Une fenêtre prend la moitié gauche de l’écran alors que l’autre fenêtre prend la moitié droite de l’écran. La fenêtre de gauche présente la feuille de calcul Janvier alors que celle de droite présente la feuille de calcul Février. Les deux fenêtres contiennent le titre produit dans la cellule A1 et des noms de produit (ex. Produit A) dans la plage A2:A7. La cellule B1 contient le titre Ventes ($) et la plage B2:B7 contient des nombres. Il y a également un diagramme à bandes intitulé Ventes par produit – Janvier (ou Février) avec les produits à l’axe des x et les ventes ($) à l’axe des y.

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.

Licence

Symbole de License Creative Commons Attribution - Pas d’utilisation commerciale - Partage dans les mêmes conditions 4.0 International

Manuel ADM1770 - Application des technologies de l'information en gestion (version 2) Copyright © 2023 by Muriel Mignerat is licensed under a License Creative Commons Attribution - Pas d’utilisation commerciale - Partage dans les mêmes conditions 4.0 International, except where otherwise noted.

Partagez ce livre