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

Par Elauïne BERNARD

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

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 : Data – Data Validation– Setting–    – List –Source (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 Validation– Setting–whole 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 Alert– choisir 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ées – Home– Editing – Sort & Filter – Custom Sort – Sort By– on 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, » « , » »)
	A	B
1	1 23	123

• 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ées– Home- Conditionnal Formatting- Highlight Cell Rules- Duplicate Values

b) Pour supprimer les doublons directement, voilà la marche à suivre :   Sélectionner l’ensemble des données–Data-Remove Duplicate– Choix colonne contenant critère de duplication– Ok

• 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 ’’)
	A	B
1	maïs	Eligible
2	riz	Pas_Eligible
3	maïs	Eligible

• 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”)
	A	B	C
1	Mais	250	Pas_Eligible
2	Riz	490	Pas_Eligible
3	Sucre	650	Pas_Eligible
4	Mais	520	Eligible

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”)
	A	B	C
1	Mais	250	Eligible
2	Riz	490	Pas_Eligible
3	Sucre	650	Eligible
4	Mais	520	Eligible

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))))
	A	B
1	785	0
2	5000	1000
3	2102	210.2
4	4000	400
5	1800	90
6	1240	0

• 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)
	A	B
1	Maïs	250
2	Riz	490
3	Sucre	650
4	Maïs	520
5	Sucre	385
6	Somme Maïs	770

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
1	F
2	M
3	M
4	F
5	F
6	3

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)
	A	B	C
1		250	0
2	2	490	980
3	3	650	1950

• 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’’)
	A	B	C
1	3/25/1990	10/19/2000	3861

Pour calculer nombre de mois entre deux dates :

=DATEDIF(A1,B1, ‘’m’’)
	A	B	C
1	3/26/1990	10/20/2000	126

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

=DATEDIF(A1,B1, ‘’y’’)
	A	B	C
1	3/27/1990	10/21/2000	10

• 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)
	A	B	C
1	1/25/2020	10/19/2020	191

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)
	A	B	C	D
1	1/25/2020	10/19/2020	2/14/2020	188
2			3/19/2020
3			9/14/2020

• Extraction d’une parie d’une date

	=DAY(A1)
	A	B
1	10/21/2000	21

	=MONTH(A1)
	A	B
1	10/21/2000	10

	=YEAR(A1)
	A	B
1	10/21/2000	2000

• Trouver à quel jour de la semaine une date correspond

	=WEEKDAY(A1)
	A	B
1	10/21/2000	7

Donc, le 21 octobre 2000 était un samedi (7^e 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)
	A	B
1	chat	CHAT

Passer de MAJUSCULE à minuscule : =LOWER (le_texte)

Exemple

	=LOWER(A1)
	A	B
1	FOOTBALL	Football

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

Exemple

	=PROPER(A1)
	A	B
1	marie	Marie

• 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

	A	B	C	D
1	Jean Jeudy Thomas	Jean	Jeudy	Thomas
2	Louis Rene F.	Louis	Rene	F
3	Marie J. Louis	Marie	J	Louis
4	Collin O. Marise	Collin	O	Marise
5	Antoinette Lapie Petit	Antoinette	Lapie	Petit

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

	A	B
1	Jean a 5 ans	5
2	Fritz a 10 ans	10
3	Léon a 12 ans	12
4	Marie est âgée de 9 ans	9
5	Lucie n’a que 3 ans	3

• 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)
	A	B
1	PAP12345	PAP
2	CAP13458	CAP
3	CAY18290	CAY
4	JCM15679	JCM

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.

	A	B	C	D
1	ID	Sexe	Section	Nom
2	1213	F	A	Judith
3	1518	M	D	Jean
4	2398	M	B	Lucien
5	1567	F	C	Gina

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

	=VLOOKUP(E1,A2:D5,4,FALSE)
	E	F
1	1518	Jean

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.

	A	B
1	Produit	Prix étiquette
2	riz	245
3	pois	270
4	sucre	125
5	lait	45
6	Œuf	23

                                     

=VLOOKUP(B2,Prix!$A$1:$B$6,2,FALSE)
	A	B	C	D
1	Client	Produit	Quantite	Prix_Unitaire
2	Jean	oeuf	3	23
3	Paul	riz	6	245
4	Louis	pois	2	270

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.