Module : Compétences digitales et Informatiques
Cours d’Excel Avancé
Pr. CHERRAT Loubna
[Link]@[Link]
Année universitaire: 2024/2025
Introduction
Compétences digitales et Informatiques
Les compétences digitales et informatiques sont essentielles dans le monde moderne, tant pour les
professionnels que pour les particuliers. Elles englobent une gamme variée de connaissances et d'aptitudes,
allant de la simple utilisation d'outils logiciels à des compétences techniques plus avancées. Voici un aperçu
des principales compétences dans ces deux domaines:
Compétences Informatiques: Compétences Digitales:
a. Connaissance des Systèmes d’Exploitation
a. Compétences en Communication Digitale
b. Utilisation des Logiciels Bureautiques
b. Compétences en Gestion de Projets Numériques
c. Gestion des Fichiers et des Données
c. Analyse et Visualisation de Données
d. Connaissance des Réseaux et d'Internet
d. Compétences en Développement Web et Programmation
e. Dépannage de Base
e. Compétences en Sécurité Informatique
f. Utilisation d’Outils de Collaboration en Ligne
g. Compétences en Cloud Computing
2
Introduction
Excel et les compétences digitales et informatiques?
La maîtrise d’Excel illustre plusieurs aspects des compétences digitales et informatiques.
Voici comment Excel se relie à ces compétences:
Au niveau compétence informatique: Au niveau compétence Digitales:
Utilisation des Logiciels Traitement de Données
Gestion des Fichiers Analyse et Visualisation de Données
Résolution de Problèmes Techniques Automatisation et Programmation
Collaboration en Ligne
3
Objectif
Amener l’apprenant à maitriser les notions avancées de l’environnement MS Excel.
Organiser des données et analyser une problématique professionnelle pour la
transposer systématiquement sous Excel.
Analyser des données et bien présenter ses résultats.
COURS+TD+TP La note du module est calculée sur la base de :
50% Examen final de fin de semestre
50% Contrôles continus
Plan
Plan du Cours
Chapitre 1: Rappel sur les fonctions de base de Microsoft Excel
Chapitre2: Gestion et protection des données (Validation des données , Filtres et tris avancés, mises en
forme conditionnelles, Gestionnaire de scénario, table de données, valeur cible, verrouillage de cellules, protection des feuilles
et classeurs)
Chapitre 3: Imbrications multiples et massives de formules multicritères: Étude de cas
Chapitre 4: Création des graphiques avancés de synthèse, superposition de
graphes, graphes multi-échelles...
Chapitre 5: Création et utilisation des Tableaux Croisés Dynamiques et des
Graphiques Croisés Dynamiques
Chapitre 6: Les Macros
Chapitre 7: Les tableaux de bords 5
Compétences digitales et informatiques: Excel
Chapitre1: Rappel sur les fonctions de base de Microsoft Excel
Généralités
Formules et Fonctions statistiques de base
Fonctions de texte
Fonctions de dates et heures
Fonctions de tests
Fonctions de recherches
Fonctions Matricielles
Fonctions financières
6
Rappel sur les fonctions de base de Microsoft Excel: Généralités
Microsoft Excel?
• Un tableur Excel est un logiciel développé par Microsoft qui permet de créer,
manipuler et analyser des tableaux de données. Il est principalement utilisé pour :
Gérer des données numériques
Effectuer des calculs
Créer des graphiques
Générer des rapports
Automatiser des tâches
Collaborer
7
Rappel sur les fonctions de base de Microsoft Excel: Généralités
Syntaxe d’une formule
Dans Excel, une formule commence toujours par le signe égal (=). À la suite du signe égal,
Excel calcule la formule de gauche à droite, selon un ordre spécifique pour chaque
opérateur de la formule.
8
Rappel sur les fonctions de base de Microsoft Excel: Généralités
Les operateurs de calcul(1)
une cellule est désignée par son numéro de colonne et de ligne, appelée aussi la référence ;
ainsi, A1 est la référence de la cellule A1 !
ou sa référence
Pour changer l'ordre de calcul, mettez entre parenthèses la partie de la formule qui doit
être calculée en premier.
9
Rappel sur les fonctions de base de Microsoft Excel: Généralités
Les operateurs de calcul(2)
10
10
Rappel sur les fonctions de base de Microsoft Excel: Généralités
Nommage d’une cellule
Pour nommer une cellule:
1. Sélectionner la cellule à nommer
2.
on clique sur l’onglet Formules Définir un nom ok
Ou
on tape directement le nom dans la zone nom. (liste déroulante à gauche de la barre formule), puis, on
valide par Entrée.
11
Rappel sur les fonctions de base de Microsoft Excel: Généralités
Références Relatives versus absolues (1)
Les références relatives sont présentées de cette manière : A1
Les références absolues sont présentées de cette manière : $A$1
Les références Mixtes : La colonne ou la ligne est fixe, tandis que l'autre est relative: A$1 ou $A1
Une référence relative est une cellule que l’on peut recopier sans problème
12
Rappel sur les fonctions de base de Microsoft Excel: Généralités
Références Relatives versus absolues (2)
Une référence absolue est une cellule fixe qui reste identique en cas de recopie
Problème ?
Solution: Transformer la cellule relative C6 en cellule absolue
13
Rappel sur les fonctions de base de Microsoft Excel: Généralités
Références Relatives versus absolues (3)
Solution1: Solution 2: transformer la référence
relative C6 en référence absolue $C$6 en
utilisant le symbole dollars « $ »
-- En appuyant 1 fois sur la touche F4 vous obtiendrez une référence absolue
– En appuyant 2 fois sur la touche F4 vous obtiendrez une ligne absolue
– En appuyant 3 fois sur la touche F4 vous obtiendrez une colonne absolue
14
Rappel sur les fonctions de base de Microsoft Excel: Généralités
Gestion des Erreurs
Erreur #DIV/0! : Division par zéro.
Erreur #N/A : Valeur non disponible.
Erreur #REF! : Référence de cellule invalide.
Erreur #VALUE! : Type de données incorrect.
######: Largeur de la colonne est insuffisante
ou la cellule contient une date ou une heure et que cette valeur est négative
exemple: Voir le Fichier Excel
15
Rappel sur les fonctions de base de Microsoft Excel
Formules et Fonctions statistiques de base
2. Pour d’autres Fonctions statistiques
1. Fonction SOMME : La Somme continue et discontinue
Exemple d’application
Budget mensuel pour le mois aôut
Charges Montant à payer
Loyer 700
Electircité 50
Essence 200
Alimentation 350
Téléphone 35
Divers 250
Chaufage 50
Crédit Mobilier 250
Calcul
16
Rappel sur les fonctions de base de Microsoft Excel
Formules et Fonctions statistiques de base
La fonction [Link] fait exactement le contraire de la fonction NBVAL. Elle compte toutes les cellules vides d’une
plage sélectionnée.
[Link]( Plage de cellules )
La fonction MODE renvoie le nombre qui se répète le plus souvent dans une série de nombre (minimum s'il y en a
plusieurs).
MODE(Plage de cellules )
La fonction PRODUIT renvoie le produit d’une série de nombres PRODUIT( nombre1;nombre2;nombre3,…)
La fonction QUOTIENT renvoie la partie entière du résultat d'une division
QUOTIENT( numérateur ; dénominateur )
À ne pas confondre avec la fonction MOD qui renvoie le reste de la division entière de deux nombres.
Exp:
MOD( Nombre ; Diviseur)
=B1/B2
17
Rappel sur les fonctions de base de Microsoft Excel
Formules et Fonctions statistiques de base
Exemple d’application ( QR Code pour l’exemple de la page 16)
Remplir les cellules demandées par les formules adéquates:
Donner les valeurs des fonctions suivantes:
=SOMME(B4:B10 B8:B11)
= [Link](4:4)
=NB(D:D)
18
Rappel sur les fonctions de base de Microsoft Excel
Formules et Fonctions statistiques de base
La fonction ABS renvoie la valeur absolue d’un nombre.
ABS(nombre)
=ABS(C1)
La fonction ARRONDI arrondit un nombre en fonction d’un type spécifié. On peut arrondir vers le bas, ou
vers le haut.
[Link]( nombre; type d’arrondi)
[Link]( nombre; type d’arrondi)
ARRONDI( nombre; type d’arrondi)
Syntaxe :
= [Link](B1;0) donne un arrondi supérieur à une unité près 325
= [Link](B1;0) donne un arrondi inférieur à une unité près 324
= [Link](F1;-2) donne un arrondi supérieur à la centaine près 400
= [Link](F1;-2) donne un arrondi inférieur à la centaine près300
= ARRONDI(23,2423;1) donne un arrondi à la dixième près23,2
= ARRONDI(23,2523;1) donne un arrondi à la dixième près23,3 19
Rappel sur les fonctions de base de Microsoft Excel
Formules et Fonctions statistiques de base
La fonction MEDIANE renvoie la valeur qui se trouve au centre d’un ensemble de nombres.
MEDIANE(nombre1;nombre2;nombre3;…)
La fonction [Link] renvoie la N-ième plus grande valeur d'une série de
données.
[Link](série de valeurs ; Nième grande valeur)
Cette fonction renvoie la N-ième plus petite valeur d'une série de données.
[Link]( série de valeurs ; Nième petite valeur)
20
Rappel sur les fonctions de base de Microsoft Excel
Formules et Fonctions statistiques de base
La fonction RANG renvoie le rang (la position) d'une valeur numérique dans une liste de valeurs.
RANG( valeur; série de valeurs; ordre)
avec : ordre = 0 pour obtenir le rang dans l’ordre décroissant
ordre = 1 pour obtenir le rang dans l’ordre croissant
Exemple d’application:
Nom Note Classement
• Donner le classement de =RANG(B2;$B$2:$B$10;0)
chaque étudiant selon sa Lina 12,65 5
note. Omar 10 7
Assia 11,7 6
Ahmed 18 1
Sami 7 9
Acheraf 15,25 3
Malak 17,5 2
Sofia 13,1 4
Amir 9 8 21
Rappel sur les fonctions de base de Microsoft Excel
Fonctions de texte
La fonction NOMPROPRE permet de modifier le texte en nom propre (1ère Lettre Des Mots En Majuscules):
NOMPROPRE(Texte) exp: =NOMPROPRE("ahmed") Ahmed
=NOMPROPRE(" MOHAMED amINE ") Mohamed Amine
La fonction SUPPRESPACE supprime uniquement les espaces inutiles entre chaque chaîne de caractères.
SUPPRESPACE (Texte)
22
Rappel sur les fonctions de base de Microsoft Excel
Fonctions de texte
La fonction CONCAT ( ou CONCATENER (Version <= 2010) permet de combiner le texte de plusieurs cellules dans une
seule cellule.
exp: =CONCATENER(A2;" a ";B2)
CONCAT(Texte1;[Texte2];… ) =A2&" a "&B2
Cette fonction permet de combiner le texte d’une plage de cellules dans une seule cellule (version 2016 plus).
[Link](séparateur; ignorer_vide ; Plage des cellules) Séparateur : Le caractère ou la
chaîne à utiliser entre les textes
exp: ignorer_vide : VRAI ou FAUX pour
ignorer ou non les cellules vides.
23
Rappel sur les fonctions de base de Microsoft Excel
Fonctions de texte
Les fonctions MAJUSCULE et MINUSCULE permettent de modifier le texte en majuscules et
en minuscules respectivement .
MAJUSCULE(Texte) MINUSCULE(Texte)
La fonction GAUCHE permet d’extraire les premiers caractères d’une chaîne de texte en fonction du
nombre spécifié de caractères à extraire et la fonction DROITE renvoie les derniers caractères d’une
chaîne de texte.
GAUCHE(Texte;[Nbr_car] ) DROITE(Texte;[Nbr_car] )
La fonction STXT permet d’extraire des caractères à l'intérieur d’une chaîne de texte en fonction
du nombre spécifié de caractères à extraire.
Texte: Texte contenant les caractères à extraire.
STXT (Texte ; No_départ ; Nbr_car) No_départ: Position du 1er caractère à extraire dans le Texte.
Nbr_car: Nombre de caractères à extraire.
Exp: =STXT("Bleu-ciel";6;4) ciel
24
Rappel sur les fonctions de base de Microsoft Excel
Fonctions de texte
Exercice d’application:
Convertissez les données suivantes en conservant uniquement les 2 premiers caractères de chaque
pays et en mettant le tout en majuscules (Ne pas inclure les espaces):
25
Rappel sur les fonctions de base de Microsoft Excel
Fonctions de texte
La fonction TROUVE permet de renvoyer la position d’une chaîne de texte dans une seconde chaîne
de texte en respectant la casse .
TROUVE(Texte_cherché ; Texte;[No_départ])
[No_départ]: Position du 1er caractère à partir duquel on veut commencer la recherche dans le Texte.
Si [No_départ] est omis, alors sa valeur par défaut est 1.
La fonction TROUVE est sensible à la casse. Si on ne veut pas respecter la casse, alors on peut utiliser la
fonction CHERCHE.
CHERCHE(Texte_cherché ; Texte;[No_départ])
La fonction NBCAR permet de compter le nombre de caractères contenus dans une chaîne de texte.
NBCAR(Texte)
26
Rappel sur les fonctions de base de Microsoft Excel
Fonctions de texte
exemple:
=NBCAR(A2) 27
Rappel sur les fonctions de base de Microsoft Excel
Fonctions de texte
La fonction SUBSTITUE permet de remplacer une chaîne spécifique de caractères par une autre.
SUBSTITUE(Texte; Ancien_texte ;Nouveau_texte ;[No position] )
exemple:
28
Rappel sur les fonctions de base de Microsoft Excel
Fonctions de dates et heures
Cette fonction renvoie la partie « année » d’une date donnée: ANNEE(date)
Cette fonction renvoie la partie « mois » d’une date donnée: MOIS(date)
Cette fonction renvoie la partie « jour » d’une date donnée: JOUR(date)
Ne pas la confondre avec la fonction JOURS(Date_fin; Date_début) qui permet de calculer le nombre de
jours compris entre deux dates.
JOURS360(date_début, date_fin) renvoie le nombre de jours compris entre deux dates sur la base d’une
année de 360 jours.
Cette fonction renvoie une date sous un format standard: DATE( année; mois ; jour)
29
Rappel sur les fonctions de base de Microsoft Excel
Fonctions de dates et heures
La fonction MAINTENANT () renvoie la date et l'heure actuelles du système.
La fonction AUJOURD'HUI () renvoie la date actuelle du système, sans l'heure.
Mise à Jour Dynamique : Les fonctions MAINTENANT et AUJOURD'HUI se mettent automatiquement à jour
chaque fois que la feuille de calcul est recalculée ou ouverte.
Cette fonction renvoie le nombre de jours ouvrables entre deux dates en comptant les jours de congés et
les week-ends:
[Link](date de début ; date de fin; jours de congés)
30
Rappel sur les fonctions de base de Microsoft Excel
Fonctions de dates et heures
Exemple :
EXEMPLE
Année de la date Date /Heure Nbr jours entre les Année en
Date Date d'aujoud'hui
"15/01/2030" d'aujoud'hui 2 dates cours
15/01/2030 2030 02/10/2024 2/10/24 11:06 AM 1931 2024
À VOUS DE TESTER !
Année de la date Date /Heure Nbr jours entre les Année en
Date Date d'aujoud'hui
"15/01/2030" d'aujoud'hui 2 dates cours
15/01/2030
Télécharger le fichier [Link] à partir de :[Link]
31