7 Points à maitriser pour être à votre aise sur Excel

Par Elauïne BERNARD

Microsoft Excel

Si on choisit au hasard une personne qui peut utiliser un ordinateur et qu’on lui demande quels logiciels elle maitrise, il y a aurait plus de 50% de chance qu’Excel figure dans ses réponses. Et pourtant, près de la moitié de ces personnes semble paniquer dès qu’elles se trouvent derrière un classeur Excel. Or Excel est un logiciel très utilisé en entreprise. Alors, pouvez-vous imaginer le nombre de personnes qui subissent de longues journées stressantes et interminables « du fait qu’elles ne maitrisent pas Excel » ? Faites-vous partie de ces personnes-là ? Tout au moins connaissez-vous quelqu’un qui en fasse partie ? Probablement, vous cherchez ou vos proches cherchent à surmonter cela en surfant sur le web. Mais il y a tellement de choses à maitriser que l’on peut se décourager en cours de route.

 Alors, à travers cet article,  Moi & Technologie veut vous accompagner dans votre quête. 7 points à maitriser pour être à votre aise sur Excel s’avère être un guide à travers lequel nous vous communiquons des astuces qui vont vous aider d’une part à maitriser les éléments de bases sur Excel et d’autre part à appréhender plus facilement les éléments avancés sur Excel. Ces points concernent la saisie de données, la mise en forme de données, l’utilisation des conditions Excel, la gestion de date, la gestion de données textuelles, la recherche de valeur et l’utilisation de plusieurs feuilles Excel en même temps.

 1) La saisie de données

Saisie de Données

Voulez-vous préparer un espace pour saisir vos données rapidement tout en minimisant les risques d’erreurs de saisie ? Alors, dans cette section, nous allons vous partager le processus à suivre en 3 étapes.

  • Etape 1 : Créer des listes déroulantes

Au lieu de saisir les valeurs  au clavier,  vous pouvez choisir directement la valeur dans une liste. Sélectionner la colonne en question (sauf la cellule contenant le nom de la colonne) puis suivre le chemin suivant : DataData ValidationSetting–    – ListSource (entrer les valeurs possibles de la colonne en les séparant par une virgule) –OK.

On répète le processus pour chaque colonne dont on peut prévoir toutes les valeurs possibles.

  • Etape 2 : Imposer des valeurs de validations pour les données à saisir

Pour les colonnes dont on ne peut pas prévoir les valeurs possibles on peut empêcher la saisie de valeurs indésirables (comme par exemple avoir une lettre comme âge). Pour cela, il suffit d’imposer les valeurs saisies qu’Excel doit laisser passer. Pour s’y faire, la démarche à suivre est la suivante Sélectionner la colonne en question (sauf la cellule contenant le nom de la colonne) – Data Data ValidationSettingwhole number (Si la colonne doit recevoir un nombre) – on peut définir les valeurs minimum et maximum de ce nombre tout comme on peut choisir d’autres critères     selon ses besoins. Ensuite Input Messageécrire le message qui doit apparaitre quand l’utilisateur saisie une valeur qu’on a demandé à Excel de ne pas valider. Finalement Error Alertchoisir le type d’alerte qui empêchera à l’utilisateur de passer tant qu’il ne saisit pas la valeur correcte.

  • Étape 3 : Figer la ligne contenant les noms des colonnes

Lors de la saisie sur Excel, dès que l’on se rapproche de la ligne 30, les premières lignes de la feuille commencent à disparaitre des champs de vision. Et c’est généralement elles qui contiennent les noms des variables. Alors, pour éviter que la valeur d’une variable soit rentrée à la place d’un autre, on fige la ligne contenant les noms des variables. Ainsi, peu importe où l’on se trouve dans la feuille les noms des colonnes sont visibles.

La marche à suivre est la suivante: View- Freeze Panes -Freeze Top Row

2) Mise en forme des données

 Généralement, la base de données que vous avez obtenue après la saisie n’est pas immédiatement exploitable. C’est pratiquement le même cas de figure pour des bases de données que vous avez importées. La base nécessite au préalable certains traitements. Dans cette partie, nous allons vous partager quelques types de traitement très fréquents que l’on effectue sur les données avant de les utiliser.

  • Mettre les données sous format tableau

En mettant les données sous format tableau on passe directement en mode base de données. Et en ce sens on bénéficie des ajustements automatiques au niveau des graphiques et des tableaux croisés dynamiques. De plus, la majorité des solutions modernes de Excel impose ce format.

Pour mettre ses données sous format tableau : Sélectionner la plage des données, ensuite cliquer sur Insert -Table – Ok.

  • Trier les données

Travailler avec des données triées suivant un critère pertinent pour le travail permet de se repérer assez facilement. Supposons par exemple que vous ayez besoin de trouver rapidement la moyenne d’une élève donnée. Il suffit de trier les données par ordre alphabétique, repérer le nom de l’élève et se rendre à la colonne correspondante aux moyennes.

Pour trier les données suivant un ou plusieurs critère(s) il suffit de : Sélectionner la plage de donnéesHomeEditingSort & FilterCustom Sort – Sort Byon choisit la colonne correspondante au(x) critère(s) d’intérêt. Et dans Order on choisit ou crée l’ordre désiré.

  • Eliminer des espaces dans une cellule de donnée

Supposons que lors de la saisie certains nombres ont été saisis avec des espaces. En ce sens, Excel va les considérer comme des chaines de caractères. Pour enlever ces espaces,  il suffit d’utiliser la fonction SUBSTITUTE de Excel. C’est comme remplacer « espace » par « non espace »

Exemple

=SUBSTITUTE(A2, » « , » »)
 AB
11 23123
  • Repérer et supprimer des doublons

Certaines fois, lors des saisies, on peut rentrer les données correspondantes à un même individu dans plusieurs lignes différentes. Ce qui crée des doublons. On peut les repérer tout comme on peut les supprimer.

a) Pour repérer les doublons, il suffit de suivre les étapes suivantes : Sélectionner la plage des donnéesHome- Conditionnal Formatting- Highlight Cell Rules- Duplicate Values

b) Pour supprimer les doublons directement, voilà la marche à suivre :   Sélectionner l’ensemble des donnéesData-Remove DuplicateChoix colonne contenant critère de duplicationOk

  • Remplacer une valeur par une autre

Après la saisie, on peut remarquer qu’une valeur n’a pas bien été saisie par exemple on a rentré serveur ou lieu de livreur. Il faut donc effectuer un remplacement.

Pour faire le remplacement, on n’à suivre la démarche suivante : CTRL+F n’importe où sur la feuille- dans Find on écrit la valeur à remplacer- dans Replace on écrit la valeur qui remplace.

3) L’utilisation des conditions

Une fois que la base de données est traitée, vous pouvez commencer à exécuter les tâches qui répondront à vos besoins. Ces tâches consistent, pour la plupart, à calculer, repérer, extraire des éléments. Certaines fois, l’on doit prendre en compte certains critères pour les exécuter. Et c’est là qu’interviennent les conditions pour aller plus vite et pour minimiser les risques d’erreur. L’élément central des conditions est la fonction IF (). Nous allons passer en revue des éléments de bases permettant de se familiariser avec l’utilisation des conditions sur Excel.

  • Condition simple

La syntaxe de la condition Excel la plus simple s’écrit : IF (critère, valeurs si vrai, valeur si faux).

Supposons, par exemple, que vous voulez afficher les clients éligibles pour un rabais. Le rabais concerne uniquement ceux qui achètent du maïs.

   =IF(A1=”maïs”,”Eligible”,”Pas_Eligible ’’)  
 AB
1 maïsEligible
2rizPas_Eligible
3maïsEligible
  • Conditions complexes

Fort souvent, il nous arrive d’être dans l’obligation de prendre en compte plusieurs critères en même temps. Et pour cela, il est très commode d’utiliser des connecteurs comme AND et OR.

Reprenons l’exemple précédent. Supposons que l’on veut dans un premier temps que ce rabais soit accordé à des clients qui achètent plus de 300 livres de maïs.

Dans ce cas-ci on combine AND avec IF et la syntaxe s’écrit :

=IF(AND(critere1,critere2),valeur_si_vrai, valeur_si_faux)

       =IF(AND(A1=”Mais”,B1>500),”Eligible”,”Pas_Eligible”)
 ABC
1Mais250Pas_Eligible
2Riz490Pas_Eligible
3Sucre650Pas_Eligible
4Mais520Eligible

Et maintenant, supposons que les clients qui achètent du maïs ou qui achètent plus de 500 livres de n’importe quel produit soit éligible pour le rabais.

Dans ce cas, on combine OR avec IF et la syntaxe s’écrit : =IF(OR(critere1,critere2),valeur_si_vrai, vleur_si_faux)

      =IF(OR(A1=”Mais”,B1>500),”Eligible”,”Pas_Eligible”)  
 ABC
1Mais250Eligible
2Riz490Pas_Eligible
3Sucre650Eligible
4Mais520Eligible

Certaines fois, vous vous trouvez dans des situations où vous devez utiliser plusieurs critères et ces critères ne sont pas proprement liés. Vous ne pouvez donc utiliser ni AND ni OR. Dans ce dernier cas, vous allez faire appel à plusieurs IF à l’intérieur d’autres IF. On parle dans ce cas de IF imbriqués. Et la syntaxe s’écrit :

IF(critere1,action_si_vrai,IF(critere2,action_si_vrai,…)..)

Supposons par exemple que vous devez appliquer un taux de taxation relatif au montant d’achat. Le taux est de 0% pour tout achat ne dépassant pas 1250, 5% pour ceux qui ne dépassent pas 1950, 10% pour ceux qui ne dépassent pas 4500 et 20% au-delà de 4500. Et Vous voulez trouver le montant de taxation des achats automatiquement.

   =IF(A1<1250,0*A1,IF(A1<1950,0.05*A1,IF(A1<4500,0.1*A1,IF(A1>4500,0.2*A1))))  
 AB
17850
250001000
32102210.2
44000400
5180090
612400
  • Quelques fonctions conditionnelles

Certaines fois, on utilise aussi les conditions pour faire des calculs précis plus rapidement. Dans ces cas, on utilise des fonctions comme SUMIF, COUNTIF, AVERAGEIF, IFERROR.

SUMIF : elle est utilisée pour faire la somme des éléments d’une plage de données suivant un critère précis.

Supposons que vous vouliez connaitre la quantité d’argent tirée de la vente de maïs au cours de la semaine. On peut utiliser la fonction SUMIF dont la syntaxe s’écrit : =SUMIF(Plage des critères, critère considéré, plage des valeurs à partir desquels on va faire la somme)

   =SUMIF(A1:A5, »Mais »,B1:B5)  
 AB
1Maïs250
2Riz490
3Sucre650
4Maïs520
5Sucre385
6Somme Maïs770

COUNTIF :

Elle peut être utilisé pour compter les éléments selon un critère. La syntaxe s’écrit : =COUNTIF(Plage de tous critères, critère considéré)

Supposons que vous vouliez compter rapidement le nombre de femmes dans votre base de données.

 =COUNTIF(A1:A5,”F”)  
 A
1F
2M
3M
4F
5F
63

IFERROR

Cette fonction peut être utilisée pour éviter a Excel de nous renvoyer N/A, VALEUR! , #DIV/0 lorsqu’une action ne peut être exécutée. On imposera à Excel de nous renvoyer de préférence une valeur de nos choix.

Il vous suffit d’utiliser la fonction IFERROR. La syntaxe s’écrit : =IFERROR(Action_a_executer, Valeut_si_erreur)

   =IFERROR(A1*B1,0)      
 ABC
1 2500
22490980
336501950
  • Mise en forme conditionnelle des cellules

Il est également possible d’appliquer certaines conditions sans utiliser de formules. Il suffit de sélectionner sa plage de données, se rendre dans Home conditionnal formating pour choisir ensuite les options qui vous intéressent.

 4) La gestion des dates  

Certaines fois, les tâches que vous avez à exécuter impliquent des dates. Nous vous apportons ici quelques manipulations fréquentes des dates.

  • Calculer le nombre de jour/mois/année entre deux dates

Pour calculer nombre de jours entre deux dates :

=DATEDIF(A1,B1, ‘’d’’)
 ABC
13/25/199010/19/20003861

Pour calculer nombre de mois entre deux dates :

=DATEDIF(A1,B1, ‘’m’’)
 ABC
13/26/199010/20/2000126

Pour calculer nombre d’années entre deux dates :

=DATEDIF(A1,B1, ‘’y’’)
 ABC
13/27/199010/21/200010
  • Calcul du nombre de jour de travail

Ici, deux cas sont à considérer :

Cas 1 : Calcul du nombre de jour de travail sans les week-ends

=NETWORKDAYS(A1,B1)
 ABC
11/25/202010/19/2020191

Cas 2 : Calcul nombre de jour de travail sans les week-ends et d’autres jours bien spécifiques

Par exemple ici, en plus des week-ends, les dates en C sont des congés nationaux.

=NETWORDAYS.INTL(A1,B1,1,C1 ;C3)
 ABCD
11/25/202010/19/20202/14/2020188
2  3/19/2020 
3  9/14/2020 
  • Extraction d’une parie d’une date
 =DAY(A1) 
 AB
110/21/200021
Extraction JOUR
 =MONTH(A1) 
 AB
110/21/200010
Extraction MOIS
 =YEAR(A1) 
 AB
110/21/20002000
Extraction ANNEE
  • Trouver à quel jour de la semaine une date correspond
 =WEEKDAY(A1) 
 AB
110/21/20007

Donc, le 21 octobre 2000 était un samedi (7e jour de la semaine).

5) La gestion des données textuelles

En général, on ne fait pas trop attention à l’aspect texte quand on travail sur Excel. Toutefois, quand on importe des données il est assez fréquent de se retrouver avec des cas de données textuelles à gérer sur Excel. En voici certains :

  • Minuscule et majuscule

Passer de minuscule à MAJUSCULE : =UPPER (le_texte)

Exemple

 =UPPER(A1) 
 AB
1chatCHAT

Passer de MAJUSCULE à minuscule : =LOWER (le_texte)

Exemple

 =LOWER(A1) 
 AB
1FOOTBALLFootball

Mettre uniquement la première lettre en majuscule : =PROPER (le_texte)

Exemple

 =PROPER(A1) 
 AB
1marieMarie
  • Séparer les mots d’une chaine de caractères

Supposons que l’on ait saisie nom(s) et prénom(s) dans une seule cellule et que vous voulez les arranger par ordre alphabétique de prénom. Dans ce cas, on demande à Excel de séparer les noms et ensuite on applique le Tri par ordre alphabétique.

Pour séparer les noms et prénoms, il suffit de donner deux exemples à Excel et ensuite presser CTRL+E dans les cellules ou les mots séparés doivent se placer.

Exemple

 ABCD
1Jean Jeudy ThomasJeanJeudyThomas
2Louis Rene F.LouisReneF
3Marie J. LouisMarieJLouis
4Collin O. MariseCollinOMarise
5Antoinette Lapie PetitAntoinetteLapiePetit

Les cellules en bleu ont été remplies directement à la main. Ensuite, on a sélectionné la cellule B3 et pressé CTRL+E : les cellules B3 jusqu’à B5 sont remplies automatiquement suivant les deux exemples précédents. On répète le même procédé pour C3 et D3.

  • Extraire un nombre d’une chaine de caractères

Imaginez que celui qui saisit l’âge des enquêtés au lieu de rentrer un nombre, écrit une phrase contenant l’âge. Que faire ? Nous allons extraire l’âge dans le texte.

Pour cela, il suffit de donner deux exemples à Excel et ensuite de presser CTRL+E dans les cellules ou les nombres doivent se placer.

Exemple

 AB
1Jean a 5 ans5
2Fritz a 10 ans10
3Léon a 12 ans12
4Marie est âgée de 9 ans9
5Lucie n’a que 3 ans3
  • Extraire un mot d’une chaine de caractères

Supposons que l’on ait oublié de demander le département de naissance des enquêtés et en cherchant on a trouvé que leur carte d’accès contient un code avec 3 lettres indiquant le code de leur département de naissance. Nous allons, à partir du code de la carte, extraire le département de naissance. On peut utiliser Left ou Right pour faire cela.

 La syntaxe s’écrit : =LEFT(texte, nombre de lettre_a_extraire_a partir de la gauche)

Exemple

=LEFT(A1,3)
 AB
1PAP12345PAP             
2CAP13458CAP
3CAY18290CAY
4JCM15679JCM

6) La recherche des valeurs

Certaines tâches sur Excel peuvent se rapporter à trouver des valeurs spécifiques. Et suivant le cas qui se présente, certaines techniques sont mieux adaptées que d’autres. Nous allons  vous expliquer brièvement deux de ces techniques.

  • Technique 1 : Utilisation des filtres

Les filtres sont très pratiques pour trouver si une valeur est présente dans une colonne de données.

 Supposons que vous vouliez vérifier si le riz a été ajouté à la liste des produits. Une manière assez simple de procéder est de cliquer sur la flèche en tête de la colonne en question et vous verrez défiler toutes les valeurs enregistrées dans cette colonne. Alors, vous verrez le riz s’il a été enregistré.

  • Technique 2 : Utilisation de la fonction VLOOKUP

La fonction VLOOKUP de Excel permet de trouver une valeur inconnue à partir d’une valeur connue. La syntaxe de la fonction s’écrit :

 VLOOKUP(valeur connue, plage de données commençant à partir de la valeur connue, numéro colonne de la valeur recherchée dans la plage sélectionnée, FALSE)

Supposons que sur les feuilles d’examen d’une classe on inscrit les numéros d’ordre des élèves et le directeur veut savoir à quel élève correspond un numéro d’ordre en particulier. Et imaginez que dans la base de référence, il y a plus de 3000 élèves. Pour trouver rapidement le nom de cet élève, on n’a qu’à faire un VLOOKUP.

 ABCD
1IDSexeSectionNom
21213FAJudith
31518MDJean
42398MBLucien
51567FCGina
     

On veut trouver le nom de l’élève qui a le numéro d’ordre 1518.

    =VLOOKUP(E1,A2:D5,4,FALSE)
 EF
11518Jean

7) L’utilisation de plusieurs feuilles de données Excel

En général, dans la pratique, nous travaillons sur plusieurs feuilles Excel en même temps. En ce sens, il est important de pouvoir les manipuler en limitant les erreurs tout en allant le plus vite possible. Voici quelques éléments fréquents qui sont susceptibles de vous aider en ce sens.

  • Regrouper plusieurs feuilles de données Excel dans un seul classeur

Il serait intéressant de commencer par regrouper les différentes feuilles dont on a besoin dans un seul classeur, si ce n’est pas déjà le cas. Cela diminue le nombre de déplacements qui sont souvent sources d’erreur. Il suffit de sélectionner la première feuille, de maintenir le doigt sur CTRL et de sélectionner progressivement une à une les autres feuilles dont on a besoin. Une fois les sélections terminées, on fait clic droit, puis Move or Copy et on les place dans un nouveau classeur ou dans un classeur existant.

  • Créer un lien entre deux feuilles Excel

Après avoir regroupé les feuilles, il est encore possible de gagner en déplacement. On peut, en ce sens, créer des liens entre les feuilles. Il suffit pour cela de faire des boutons de liens hypertextes. Dans Insert, on va dans shape pour choisir la forme de son bouton. Ensuite, on personnalise le bouton avec nom et couleur. Puis, on fait un clic droit sur le bouton créé, et toujours dans insert on clique sur link, puis place in this document. Après, on choisit la feuille avec laquelle on veut créer le lien. Finalement on clique sur Ok. Un simple clic sur ce bouton et vous allez vous retrouver sur la feuille nouvellement liée. Vous pouvez reproduire le processus dans les différentes feuilles qui ont des relations directes.

  • Rechercher des correspondances dans deux feuilles Excel

La recherche de correspondance permet d’aller plus vite. On peut la faire en utilisant la fonction d’un VLOOKUP suivant cette synthaxe : 

=VLOOKUP(valeur de référence, table_de_référence, numéro_colonne,FALSE)

La valeur de référence correspond au nom du produit dont on veut connaitre le prix

La table de référence correspond à la table où se trouve la valeur de référence et la valeur que l’on cherche. La colonne de la valeur de référence doit toujours être la première colonne de la table de référence.

Le numéro de colonne correspond à la position de la colonne où se trouve la valeur que l’on cherche. On commence à compter à partir du numéro de la colonne de la valeur de référence qui est toujours.

FALSE c’est pour dire que l’on recherche une valeur exacte et non une valeur approximative.

Vlookup va parcourir toute la table pour trouver votre valeur. S’il n’y a pas correspondance, Vlook renvoie N/A.

Nous allons vous donner un exemple pour illustrer.

Supposons que vous ayez une entreprise de provision alimentaire. Dans une feuille, vous enregistrez le nom de tous les produits avec leur prix. Et dans une autre feuille vous rentrez les commandes des clients. Alors, on peut éviter les erreurs de calcul en utilisant la recherche de correspondance. Et comment ? En rentrant automatiquement le prix unitaire des produits dès que l’on rentre le nom du produit.

 AB
1ProduitPrix étiquette
2riz245
3pois270
4sucre125
5lait45
6Œuf23
Feuille1: Prix

                                     

=VLOOKUP(B2,Prix!$A$1:$B$6,2,FALSE) 
 ABCD
1ClientProduitQuantitePrix_Unitaire
2Jeanoeuf323
3Paulriz6245
4Louispois2270
Feuille 2: Commandes

Excel a rempli automatiquement la colonne « prix_unitaire ».

Cet article vous a présenté des points sur lesquels vous pouvez travailler pour devenir plus à l’aise sur Excel. Ces points concernent : la saisie de données, la mise en forme de données, l’utilisation des conditions Excel, la gestion de date, la gestion de données textuelles, la recherche de valeur et l’utilisation de plusieurs feuilles Excel en même temps. Ils englobent les éléments essentiels que vous devez maitriser pour renouer avec Excel ou pour faire disparaitre vos paniques vis-à-vis du logiciel Microsoft Excel. Alors, qu’attendez-vous pour vous mettre à votre aise sur Excel ?

Cet article vous a été utile ? N’hésitez pas de poser vos questions et de partager vos commentaires avec nous.

Vous pouvez vous abonner à Moi & Technologie pour rester connecter.

Par Elau

Je trouve que chaque jour est une opportunité pour créer et apprendre. Seul, nous pouvons avoir de grande vision. En équipe nous pouvons réaliser les grandes visions. Apprenons, Créons et Réalisons ensemble pour progresser jour après jour.

2 commentaires

Répondre à Elau Annuler la réponse.

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l’aide de votre compte WordPress.com. Déconnexion /  Changer )

Photo Google

Vous commentez à l’aide de votre compte Google. Déconnexion /  Changer )

Image Twitter

Vous commentez à l’aide de votre compte Twitter. Déconnexion /  Changer )

Photo Facebook

Vous commentez à l’aide de votre compte Facebook. Déconnexion /  Changer )

Connexion à %s

%d blogueurs aiment cette page :