Institut Supérieur des Etudes Maritimes
Informatique 1
Initiation à Microsoft Excel
Année universitaire: 2017/2018
Initiation à Microsoft Excel
Introduction
Microsoft Excel est un logiciel tableur de la suite bureautique Microsoft
Office développé et distribué par l'éditeur Microsoft. La version la plus récente
est Excel 2016.
Il est destiné à fonctionner sur les plates-formes Microsoft Windows, Mac
OS X, Android ou Linux.
Le logiciel Excel intègre des fonctions de calcul numérique,
de représentation graphique, d'analyse de données (notamment de tableau croisé
dynamique) et de programmation, laquelle utilise les macros écrites dans le langage
VBA (Visual Basic for Applications) qui est commun aux autres logiciels de Microsoft
Office.
Les principaux formats de fichiers natifs portent
l'extension xls (1995→2003) et xlsx (2007→2016).
Chaque fichier correspond à un classeur, lequel contient des feuilles de
calculs organisées. Chaque feuille correspond à un tableau de lignes et de colonnes
pouvant contenir des valeurs (numériques ou non) ainsi que des formules permettant
les calculs.
Informatique 1 2
Initiation à Microsoft Excel
Environnement
Informatique 1 3
Initiation à Microsoft Excel
Environnement
Le bouton office: remplace le menu Fichier des versions antérieures.
La barre de titre: affiche le nom de votre application
et le nom du document en cours.
Les onglets: regroupent les différentes commandes et
fonctions de Word.
Les groupes et boutons de commandes : chaque groupe contient des
boutons de commandes qui exécutent des commandes dans les différents onglets.
Les boutons d’affichage: permettent de contrôler l’affichage de votre document.
Le zoomer: détermine la taille de l’affichage du document à l’écran.
La barre d’accès rapide: permet d’afficher à l’aide de menus déroulants
les différentes commandes disponibles.
Informatique 1 4
Initiation à Microsoft Excel
Environnement
La barre de formule: permet de voir et de modifier le contenu
des cellules .
L’indicateur de position affiche la position de la cellule active.
Les barres de défilement verticale et horizontale: qui vous permettent de
visualiser rapidement un document.
La barre d’état: permet de voir au bas de l’écran les options qui sont activées.
Les onglets: sont utilisés pour identifier chaque feuille.
Les colonnes: chaque feuille de calcul contient des colonnes nommées de A à ZZZ
Les lignes: chaque feuille de calcul contient des lignes numérotées de 1 à 1 048 576 lignes
Informatique 1 5
Initiation à Microsoft Excel
Environnement
Quelques définitions:
Un classeur est un ensemble de feuilles de calcul, c’est un ensemble de feuilles de
calcul stockées dans un même fichier. Chaque feuille est repérable par un onglet à son
nom.
Une feuille de calcul est un ensemble de cellules organisées en tableau.
Une cellule est l’intersection d'une ligne et d'une colonne. Une cellule active est une
cellule qui apparaît en surbrillance à l’écran.
Le ruban est constitué des composants suivants :
Des onglets pour chaque catégorie des tâches d’Excel rassemblant les
commandes les plus utilisées.
Des groupes rassemblant des boutons de commande.
Des boutons de commande dans chaque groupe que vous pouvez sélectionner
pour accomplir une action.
Des lanceurs de boîte de dialogue dans le coin inférieur droit de certains
groupes vous permettant d’ouvrir une boîte de dialogue contenant un
certain nombre d’options supplémentaires.
Informatique 1 6
Initiation à Microsoft Excel
Environnement
Les différents onglets du Ruban:
L’onglet Accueil:
Cet onglet contient les boutons de commande utilisés pour créer, mettre en forme et
modifier une feuille de calcul regroupés dans les groupes Presse-papiers, Police, Alignement,
Nombre, Style, cellules et Edition.
L’onglet Insertion :
Cet onglet contient les boutons de commande utilisés pour insérer des dessins, tableaux,
graphiques, en-têtes et pied de pages, etc…, regroupés dans les groupes Tableaux,
Illustrations, Graphiques, Liens et texte.
Informatique 1 7
Initiation à Microsoft Excel
Environnement
Les différents onglets du Ruban:
L’onglet Mise en page:
Cet onglet contient les boutons de commande utilisés pour mettre en page votre feuille de
calcul pour l’impression regroupés dans les groupes Thèmes, Mise en page, Mise à l’échelle,
Options de la feuille de calcul et Organiser.
L’onglet Formules :
Cet onglet contient les boutons de commande utilisés pour ajouter des formules et des
fonctions dans une feuille de calcul regroupés dans les groupes Bibliothèque de fonctions,
Noms définis, Audit de formules et calcul.
Informatique 1 8
Initiation à Microsoft Excel
Environnement
Les différents onglets du Ruban:
L’onglet Données :
Cet onglet contient les boutons de commande utilisés pour importer, faire des requêtes,
faire un sous total des données dans une feuille de calcul, trier et filtrer des données, etc…
regroupés dans les groupes Connexions, Trier et filtrer, outils de données et Plan.
L’onglet Révision :
Cet onglet contient les boutons de commande utilisés pour vérifier, protéger et procéder au
suivi des modifications d’une feuille de calcul, regroupés dans les groupes Vérification,
Commentaires et Modifications.
Informatique 1 9
Initiation à Microsoft Excel
Eléments de base
Bases et textes:
Informatique 1 10
Initiation à Microsoft Excel
Eléments de base
Mise en forme du texte:
Pour changer la taille des caractères:
1) Sélectionnez les cellules A5 à C5.
Informatique 1 11
Initiation à Microsoft Excel
Eléments de base
Mise en forme du texte:
Pour changer la taille des caractères:
2) Sélectionnez la taille 20 depuis la liste déroulante suivante :
Informatique 1 12
Initiation à Microsoft Excel
Eléments de base
Mise en forme du texte:
Pour modifier la largeur des colonnes:
1) Sélectionnez les colonnes A à C:
Informatique 1 13
Initiation à Microsoft Excel
Eléments de base
Mise en forme du texte:
Pour modifier la largeur des colonnes:
2) Placez ensuite le pointeur sur le trait entre 2 des colonnes
sélectionnées :
Informatique 1 14
Initiation à Microsoft Excel
Eléments de base
Mise en forme du texte:
Pour modifier la largeur des colonnes:
3) Cliquez sans relâcher, allez vers la droite pour élargir les colonnes, et
vers la gauche pour les rétrécir. Dans ce cas, allez vers la droite:
Toutes les colonnes sélectionnées subissent les mêmes changements.
Informatique 1 15
Initiation à Microsoft Excel
Eléments de base
Mise en forme du texte:
Texte en gras:
Sélectionnez ces 3 cellules puis cliquez sur G.
Informatique 1 16
Initiation à Microsoft Excel
Eléments de base
Mise en forme du texte:
Texte en italique:
Sélectionnez ces 4 cellules puis cliquez sur I.
Informatique 1 17
Initiation à Microsoft Excel
Eléments de base
Mise en forme du texte:
Souligner un texte:
Conservez la sélection et cliquez sur S.
Informatique 1 18
Initiation à Microsoft Excel
Eléments de base
Alignements, fusion, couleurs:
Alignements
1) Sélectionnez les cellules B11 à C14.
Informatique 1 19
Initiation à Microsoft Excel
Eléments de base
Alignements, fusion, couleurs:
Alignements
2) Pour aligner à gauche, cliquez sur « Aligner le texte à gauche » :
Informatique 1 20
Initiation à Microsoft Excel
Eléments de base
Alignements, fusion, couleurs:
Alignements
2) Pour centrer, cliquez sur « Centrer » :
Informatique 1 21
Initiation à Microsoft Excel
Eléments de base
Alignements, fusion, couleurs:
Alignements
2) Pour aligner à droite, cliquez sur « Aligner le texte à droite » :
Informatique 1 22
Initiation à Microsoft Excel
Eléments de base
Alignements, fusion, couleurs:
Alignements
Pour agrandir la hauteur de la ligne 5.
Informatique 1 23
Initiation à Microsoft Excel
Eléments de base
Alignements, fusion, couleurs:
Alignements
Pour modifier l'alignement vertical (zone rouge) des cellules de la ligne 5:
Informatique 1 24
Initiation à Microsoft Excel
Eléments de base
Alignements, fusion, couleurs:
Fusion
La fusion permet avec plusieurs cellules de n’en faire plus qu’une.
1) Sélectionnez les cellules de A6 à C7 :
Informatique 1 25
Initiation à Microsoft Excel
Eléments de base
Alignements, fusion, couleurs:
Fusion
2) Cliquez sur le bouton indiqué pour fusionner les cellules :
Informatique 1 26
Initiation à Microsoft Excel
Eléments de base
Alignements, fusion, couleurs:
Fusion
Attention : seule la valeur de la cellule supérieure gauche est conservée lorsque
plusieurs cellules contiennent des données !
Vous obtenez :
Informatique 1 27
Initiation à Microsoft Excel
Eléments de base
Alignements, fusion, couleurs:
Couleur de fond de la cellule
Cliquez sur une cellule, puis sur le bouton « Couleur de remplissage » et
choisissez une couleur :
Informatique 1 28
Initiation à Microsoft Excel
Eléments de base
Alignements, fusion, couleurs:
Couleur de police
Sélectionnez une cellule puis choisissez une couleur en cliquant sur le bouton
« Couleur de police ».
Informatique 1 29
Initiation à Microsoft Excel
Eléments de base
Déplacer/copier le contenu:
Déplacer une cellule
1) Sélectionnez une cellule.
Informatique 1 30
Initiation à Microsoft Excel
Eléments de base
Déplacer/copier le contenu:
Déplacer une cellule
2) Placez le pointeur sur le bord de la cellule sélectionnée, de façon à obtenir un
pointeur à 4 flèches, puis cliquez sans relâcher et déplacez la cellule.
Informatique 1 31
Initiation à Microsoft Excel
Eléments de base
Déplacer/copier le contenu:
Copier des cellules
1) Sélectionnez une ou plusieurs cellules, faites un clic droit sur une des
cellules sélectionnées et cliquez sur Copier.
Informatique 1 32
Initiation à Microsoft Excel
Eléments de base
Déplacer/copier le contenu:
Copier des cellules
2) Faites ensuite un clic droit à l’endroit ou vous souhaitez dupliquer les
cellules et cliquer sur Coller.
Informatique 1 33
Initiation à Microsoft Excel
Eléments de base
Déplacer/copier le contenu:
Reproduire une mise en forme
1) Cliquez sur la cellule contenant la mise en forme à reproduire puis sur le
Pinceau.
Informatique 1 34
Initiation à Microsoft Excel
Eléments de base
Déplacer/copier le contenu:
Reproduire une mise en forme
2) Sélectionnez ensuite simplement les cellules qui doivent recevoir cette mise
en forme.
Informatique 1 35
Initiation à Microsoft Excel
Eléments de base
Déplacer/copier le contenu:
Retour à la ligne
Pour ajouter un saut de ligne dans une cellule, appuyez sur Alt + Enter.
Informatique 1 36
Initiation à Microsoft Excel
Eléments de base
Exercice 1:
A partir des données suivantes:
Créer:
Informatique 1 37
Initiation à Microsoft Excel
Eléments de base
Tableaux:
Bordures
Pour créer un tableau, il existe différentes possibilités.
1ère méthode
Sélectionnez la zone à quadriller et cliquez à l’endroit
indiqué par l’image.
Informatique 1 38
Initiation à Microsoft Excel
Eléments de base
Tableaux:
Bordures
1ère méthode
Vous obtenez un premier quadrillage.
Informatique 1 39
Initiation à Microsoft Excel
Eléments de base
Tableaux:
Bordures
1ère méthode
Vous pouvez également sélectionner d'autres cellules et ajouter un autre
style de bordure
Informatique 1 40
Initiation à Microsoft Excel
Eléments de base
Tableaux:
Bordures
2e méthode
1) Sélectionnez les cellules de votre choix, faites un clic droit et cliquez sur
« Format de cellule ».
Informatique 1 41
Initiation à Microsoft Excel
Eléments de base
Tableaux:
Bordures
2e méthode
2) Cliquez sur l’onglet « Bordure » et modifiez-les à votre guise.
Informatique 1 42
Initiation à Microsoft Excel
Eléments de base
Tableaux:
Bordures
3e méthode
Cliquez à l’endroit indiqué par le pointeur
Informatique 1 43
Initiation à Microsoft Excel
Eléments de base
Tableaux:
Bordures
3e méthode
Cela vous permet de tracer des bordures à la main. C'est une solution intéressante
pour les tableaux aux formes inhabituelles.
Informatique 1 44
Initiation à Microsoft Excel
Eléments de base
Tableaux:
Couleur et style des traits
Vous pouvez sélectionner une couleur et un style de trait pour tracer vos bordures
(méthodes 1 et 3) depuis le bouton « Bordures ».
Informatique 1 45
Initiation à Microsoft Excel
Eléments de base
Tableaux:
Couleur et style des traits
Vous retrouverez les mêmes choix si vous tracez vos bordures depuis « Format de
cellule ».
Informatique 1 46
Initiation à Microsoft Excel
Eléments de base
Tableaux(couleurs, modèles):
Couleur de fond
Si vous voulez plus de choix (dégradé de couleurs/textures), rendez-vous dans
« Format de cellule », onglet « Remplissage ».
Informatique 1 47
Initiation à Microsoft Excel
Eléments de base
Tableaux(couleurs, modèles):
Couleur de fond
OU cliquez sur l'onglet « Mise en page » puis sur « Thèmes » pour choisir un
nouveau thème pour votre tableau (ou sur « Couleur » si vous ne voulez modifier que
les couleurs).
Informatique 1 48
Initiation à Microsoft Excel
Eléments de base
Tableaux(couleurs, modèles):
Les modèles de tableau
1) Sélectionnez les cellules du tableau.
Informatique 1 49
Initiation à Microsoft Excel
Eléments de base
Tableaux(couleurs, modèles):
Les modèles de tableau
2) Cliquez sur « Mettre sous forme de tableau » et choisissez un style.
Informatique 1 50
Initiation à Microsoft Excel
Eléments de base
Tableaux(couleurs, modèles):
Les modèles de tableau
Vous obtenez par exemple :
Informatique 1 51
Initiation à Microsoft Excel
Eléments de base
Exercice 2:
Reproduire le tableau suivant :
Informatique 1 52
Initiation à Microsoft Excel
Eléments de base
Format de cellule et mise en forme:
Sélectionnez une ou plusieurs cellules, faites un clic droit sur votre
sélection et choisissez « Format de cellule ».
Informatique 1 53
Initiation à Microsoft Excel
Eléments de base
Format de cellule et mise en forme:
Depuis cette fenêtre vous pourrez personnaliser l'affichage et la mise en
forme de vos cellules.
Informatique 1 54
Initiation à Microsoft Excel
Eléments de base
Format de cellule et mise en forme:
L’onglet « Nombre »
Pour obtenir un nombre avec des décimales fixes, choisissez « Nombre »
et définissez le nombre de décimales :
Informatique 1 55
Initiation à Microsoft Excel
Eléments de base
Format de cellule et mise en forme:
L’onglet « Nombre »
Pour ajouter le symbole monétaire, choisissez « Monétaire » ou
« Comptabilité » :
Informatique 1 56
Initiation à Microsoft Excel
Eléments de base
Format de cellule et mise en forme:
L’onglet « Nombre »
Si la cellule contient une date, vous pouvez formater cette date.
Depuis « Date » :
Informatique 1 57
Initiation à Microsoft Excel
Eléments de base
Format de cellule et mise en forme:
L’onglet « Alignement »
Vous pouvez modifier l’alignement vertical/horizontal du texte, son orientation,
activer le renvoi automatique à la ligne si nécessaire (pour le texte qui dépasse de
la cellule), etc.
Informatique 1 58
Initiation à Microsoft Excel
Eléments de base
Recopie incrémentée:
La recopie incrémentée permet souvent un gain de temps considérable, en vous
évitant de répéter maintes fois les mêmes opérations.
Pour ce faire:
1) Entrez le chiffre 1 en A1
2) Placez le pointeur sur l’extrémité inférieure droite de la sélection, cliquez sans
relâcher jusqu’à l’apparition du 20.
3) Choisissez ensuite « Incrémenter une série »
Informatique 1 59
Initiation à Microsoft Excel
Eléments de base
Recopie incrémentée:
La recopie incrémentée s’applique aussi aux jours, mois, dates, heures, etc.
Le résultat :
Informatique 1 60
Initiation à Microsoft Excel
Eléments de base
Recopie incrémentée:
Si vous désirez un intervalle différent par exemple:
Incrément A : 2 jours
Incrément B : 2 mois
Incrément C : les mois (au lieu des jours)
Incrément D : 2 heures
Incrément E : + 2
Incrément F : + 10
Le résultat :
Informatique 1 61
Initiation à Microsoft Excel
Eléments de base
Recopie incrémentée:
Recopier une mise en forme
Sélectionnez et recopiez simplement les lignes.
Vous obtenez :
Informatique 1 62
Initiation à Microsoft Excel
Eléments de base
Exercice 3:
Objectif : reproduire ce tableau:
Informatique 1 63
Initiation à Microsoft Excel
Fonctions
Opérateur mathématique:
Remarque:
Attention aux règles de priorité en mathématiques!
* et / vont avant + et -.
Ainsi en rentrant dans la barre de formules f=(3+2+5)*2; on obtiendra comme
résultat 20.
En rentrant dans la barre de formules f=3+2+5*2; on obtiendra comme résultat
15.
Informatique 1 64
Initiation à Microsoft Excel
Fonctions
Opérateur de comparaison:
Remarque:
Ces opérateurs de comparaison permettent, par exemple, de faire des sélections
sur une base de données.
Ces opérateurs sont utilisés souvent avec les fonctions statistiques et fonctions
logiques.
Informatique 1 65
Initiation à Microsoft Excel
Fonctions
Utilisation du $ dans les cellules:
Remarque:
Pour Éviter qu’une référence change quand vous copiez une formule à droite ou
vers le bas, vous pouvez insérer le signe dollar « $ » devant la référence de cellule
à bloquer. A force d’insérer le signe, vous rendez l’adresse de cellule absolue et
donc invariable.
Attention: le signe « $ » est à insérer devant la partie de l’adresse de cellule qui ne
peut pas changer à la copie.
Informatique 1 66
Initiation à Microsoft Excel
Fonctions
Exercice 4:
Réalisez ce tableau de calcul des intérêts du capital en utilisant les références
absolues.
Informatique 1 67
Initiation à Microsoft Excel
Fonctions
Fonctions mathématiques:
Informatique 1 68
Initiation à Microsoft Excel
Fonctions
Fonctions statistiques:
Informatique 1 69
Initiation à Microsoft Excel
Fonctions
Exercice 5:
1- Dans la colonne G, calculez la somme des heures de travail effectuées par
Maurice, José et Myriam. Sur la ligne 5, calculez la moyenne des heures effectuées
par mois.
2- Refaire le calcul de la somme pour les employés dont les heures de travail
dépassent 38h.
Informatique 1 70
Initiation à Microsoft Excel
Fonctions
Fonctions sur les dates:
Informatique 1 71
Initiation à Microsoft Excel
Fonctions
Exercice 6:
Calculez la date du jour dans la cellule E2, puis calculez l'âge en années dans la
colonne C (sans passer par l'âge en jours). Calculez ensuite dans les cellules C6 et
C7 l'âge du plus jeune et l'âge du plus vieux.
Informatique 1 72
Initiation à Microsoft Excel
Fonctions
Fonctions texte:
Informatique 1 73
Initiation à Microsoft Excel
Fonctions
Fonctions logique:
Informatique 1 74
Initiation à Microsoft Excel
Fonctions
Exercice 7:
Attribuez une allocation de 1000 frs aux personnes ayant soit un revenu inférieur à
40000 frs soit plus de 2 enfants. Attribuez une allocation de 500 frs dans le cas
contraire.
Informatique 1 75
Initiation à Microsoft Excel
Graphiques
Les graphiques sont utilisés pour afficher des séries de données numériques
sous forme graphique afin d’appréhender plus facilement d’importantes quantités
de données et les relations entre différentes séries de données.
Pour créer un graphique dans Excel, il faut commencer par entrer les données
dans une feuille de calcul et ensuite tracer ces données dans un graphique :
plusieurs formes de graphiques sont disponibles dans le groupe Graphiques de
l’onglet Insertion.
Informatique 1 76
Initiation à Microsoft Excel
Graphiques
Création de graph:
Pour créer un graph:
1) Créez votre base de données
2) Sélectionnez les cellules contenant les données que vous voulez utiliser pour
votre graphique.
Informatique 1 77
Initiation à Microsoft Excel
Graphiques
Création de graph:
Pour créer un graph:
3) choisissez un graphique dans l'onglet « Insertion ».
Informatique 1 78
Initiation à Microsoft Excel
Graphiques
Création de graph:
Le graphique est inséré sur la feuille.
Informatique 1 79
Initiation à Microsoft Excel
Graphiques
Modification de graph:
Lorsque vous sélectionnez le graphique des onglets supplémentaires sont affichés.
Pour changer (si nécessaire) de type de graphique,
1) cliquez sur « Modifier le type de graphique ».
Informatique 1 80
Initiation à Microsoft Excel
Graphiques
Modification de graph:
Lorsque vous sélectionnez le graphique des onglets supplémentaires sont affichés.
Pour changer (si nécessaire) de type de graphique,
2) Et choisissez un nouveau type.
Informatique 1 81
Initiation à Microsoft Excel
Graphiques
Personnalisation de graph:
Vous pouvez choisir une des dispositions de graphique prédéfinies.
Informatique 1 82
Initiation à Microsoft Excel
Graphiques
Personnalisation de graph:
Vous pouvez également choisir l'un des styles prédéfinis.
Informatique 1 83
Initiation à Microsoft Excel
Graphiques
Personnalisation de graph:
A partir de l'onglet « Dispositions », vous pouvez ajouter/retirer/personnaliser les
éléments suivants : titres, légendes, étiquettes, table de données, axe, quadrillage,
paroi, etc.
Informatique 1 84
Initiation à Microsoft Excel
Graphiques
Personnalisation de graph:
A partir de l'onglet « Dispositions », vous pouvez ajouter/retirer/personnaliser les
éléments suivants : titres, légendes, étiquettes, table de données, axe, quadrillage,
paroi, etc.
Informatique 1 85
Initiation à Microsoft Excel
Graphiques
Personnalisation de graph:
Il est également possible de personnaliser les différents éléments séparément (en
les sélectionnant sur le graphique).
Informatique 1 86
Initiation à Microsoft Excel
Graphiques
Modifier la source du graph:
Pour changer la plage de données à prendre en compte pour le graphique, cliquez
sur « Sélectionner des données... ».
Informatique 1 87
Initiation à Microsoft Excel
Mises en forme conditionnelles
Utilisez un format conditionnel peut vous aider à explorer et analyser des
données, à détecter des problèmes critiques et à identifier des modèles et des
tendances.
La mise en forme conditionnelle vous facilite la mise en évidence des cellules ou
des plages de cellules qui présentent un intérêt, en soulignant les valeurs
inhabituelles et en permettant de visualiser les données à l'aide de barres de
données, de nuances de couleurs et de jeux d'icônes.
Commençons avec quelques exemples de MFC "prêtes à l'emploi".
Informatique 1 88
Initiation à Microsoft Excel
Mises en forme conditionnelles
Règles de mise en surbrillance des cellules :
1) Sélectionnez les cellules cibles et choisissez la MFC « Supérieur à... » :
Informatique 1 89
Initiation à Microsoft Excel
Mises en forme conditionnelles
Règles de mise en surbrillance des cellules:
2) Entrez une valeur limite (ici, 50'000) et choisissez une mise en forme.
Informatique 1 90
Initiation à Microsoft Excel
Mises en forme conditionnelles
Règles des valeurs plus/moins élevées:
1) En choisissant la MFC « 10 valeurs les plus élevées... », la mise en forme sera
appliquée sur les X valeurs les plus élevées de la plage de cellules.
Informatique 1 91
Initiation à Microsoft Excel
Mises en forme conditionnelles
Règles des valeurs plus/moins élevées:
2) Vous pouvez ensuite définir le nombre de valeurs à mettre en évidence (ici, 5)
ainsi que la mise en forme.
Informatique 1 92
Initiation à Microsoft Excel
Mises en forme conditionnelles
Barres de données:
Exemple:
Informatique 1 93
Initiation à Microsoft Excel
Mises en forme conditionnelles
Nuances de couleurs:
Exemple:
Informatique 1 94
Initiation à Microsoft Excel
Mises en forme conditionnelles
Jeux d’icônes:
Exemple:
Informatique 1 95
Initiation à Microsoft Excel
Mises en forme conditionnelles
Mise en forme conditionnelle sur mesure:
Pour ce nouvel exemple, un mois a été entré dans la cellule B8.
L'objectif ici est de créer une MFC qui va colorer automatiquement la colonne du
tableau en fonction du mois en B8.
1) Sélectionnez les cellules du tableau et choisissez « Nouvelle règle... » :
Informatique 1 96
Initiation à Microsoft Excel
Mises en forme conditionnelles
Mise en forme conditionnelle sur mesure:
2) Sélectionnez le dernier type de règle pour entrer une formule:
Commencez votre formule avec un =, entrez ensuite la première cellule à tester (ici,
B1) et finissez avec le test =$B$8 (avec des $ pour "fixer" la cellule).
Informatique 1 97
Initiation à Microsoft Excel
Mises en forme conditionnelles
Mise en forme conditionnelle sur mesure:
Avec =B1=$B$8, la MFC est appliquée à chaque cellule contenant la valeur
recherchée (ici, Février).
La formule =B1=$B$8 est celle de la première cellule du tableau, dans les autres
cellules la formule est modifiée (comme lors d'une recopie incrémentée).
Informatique 1 98
Initiation à Microsoft Excel
Mises en forme conditionnelles
Mise en forme conditionnelle sur mesure:
Pour mettre en forme la colonne entière et pas seulement une cellule, il va falloir "fixer"
le n° de ligne avec un $.
Pour modifier la formule, cliquez sur « Gérer les règles... » puis « Modifier la règle... ».
Informatique 1 99
Initiation à Microsoft Excel
Mises en forme conditionnelles
Mise en forme conditionnelle sur mesure:
Pour mettre en forme la colonne entière et pas seulement une cellule, il va falloir "fixer"
le n° de ligne avec un $.
Pour modifier la formule:
1) Cliquez sur « Gérer les règles... » puis « Modifier la règle... ».
2) Ajoutez un $ devant le n° de ligne.
Informatique 1 100
Initiation à Microsoft Excel
Mises en forme conditionnelles
Mise en forme conditionnelle sur mesure:
Résultat :
Informatique 1 101
Initiation à Microsoft Excel
Mises en forme conditionnelles
Exercice 8:
Afficher les retards de paiement en fonction de la date du jour
Informatique 1 102
Initiation à Microsoft Excel
Listes déroulantes
1) Sélectionnez les cellules dans lesquelles vous souhaitez ajouter la liste déroulante
puis, dans l'onglet « Données », cliquez sur « Validation des données ».
Informatique 1 103
Initiation à Microsoft Excel
Listes déroulantes
2) Dans le premier onglet, choisissez « Liste » et sélectionnez la source (ici, les villes).
Informatique 1 104
Initiation à Microsoft Excel
Listes déroulantes
2) Dans le premier onglet, choisissez « Liste » et sélectionnez la source (ici, les villes).
Informatique 1 105
Initiation à Microsoft Excel
Listes déroulantes
Exercice
Soit le tableau employé/ville dans la feuille1 et le tableau des villes dans la feuille 2
Construire une liste déroulante au niveau de la colonne ville de la feuille 1, qui récupère
les données du tableau Villes de la feuille 2.
Informatique 1 106
Initiation à Microsoft Excel
Tri de données
Tri simple:
Excel vous permet de trier rapidement le contenu d'une liste de données pour vous
aider à retrouver rapidement ce que vous recherchez et d'organiser les données.
Remarque : vous trouvez la fonction trier et filtrer à deux endroits différents, dans
le groupe Edition de l’onglet Accueil et dans le groupe trier et filtrer de l’onglet
Données.
•Placez le pointeur sur n'importe quelle cellule de la liste de données.
•A partir de l’onglet Données, dans le groupe trier et filtrer, cliquez sur le
bouton si vous désirez un tri croissant
•et sur le bouton si vous désirez un tri décroissant.
Informatique 1 107
Initiation à Microsoft Excel
Tri de données
Tri multiple:
Vous pouvez faire plusieurs tris successifs en ordre croissant ou décroissant.
• sélectionnez l'option Trier
Dans la boîte de dialogue qui s’affiche, cliquez sur Ajouter un niveau
Pour le premier tri, sélectionnez le champ Nom - Conservez l'ordre croissant.
Pour le second tri, sélectionnez le champ Prénom. - Conservez l'ordre croissant.
Remarque : pour les enregistrements ayant le même nom, les enregistrements
seront triés selon les prénoms.
Informatique 1 108
Initiation à Microsoft Excel
Tri de données
Exercice 9:
Trier le tableau suivant par Age/Nom/Prénom.
Informatique 1 109
Initiation à Microsoft Excel
Filtre automatique
Les données filtrées n’affichent que les lignes qui répondent aux critères que vous
avez spécifiés et masquent celles que vous ne voulez pas afficher. Vous pouvez
également filtrer les données dans plusieurs colonnes. Les filtres sont additifs, à
savoir que chaque filtre supplémentaire est basé sur le filtre actif, ce qui réduit
encore davantage le sous ensemble de données.
• Placez le pointeur sur n'importe quelle cellule de la liste de
données.
• A partir de l’onglet Données, dans le groupe trier et filtrer,
cliquez sur le bouton Filtrer
Informatique 1 110
Initiation à Microsoft Excel
Filtre automatique
À la première ligne de la liste de données, vous trouvez des boîtes
de sélection.
• Cliquez sur la flèche à droite du nom du champ
• Décochez Sélectionnez tout
• Et sélectionnez le nom que vous désirez, Dubois par exemple
Pour annuler le filtre : A partir de l’onglet Données, cliquez sur Filtre
Informatique 1 111
Initiation à Microsoft Excel
Filtre automatique
Exercice 10:
Filtrer le tableau pour afficher les informations relatives aux Dupont.
Informatique 1 112
Initiation à Microsoft Excel
Sous-totaux d’une liste de données
Vous pouvez calculer automatiquement les sous-totaux et les totaux généraux
d'une colonne dans une liste.
• Créez votre base de données
• Vérifiez que la première ligne de chaque colonne contient une étiquette
• Triez la colonne qui constitue le groupe
• Placez votre curseur n’importe où dans la liste de données
• Cliquez sur l’onglet Données puis sur la commande « Sous-totaux »
Remarque: La liste de données ne doit contenir aucune ligne ou colonne vide.
Informatique 1 113
Initiation à Microsoft Excel
Sous-totaux d’une liste de données
Une boîte de dialogue apparaît:
• Dans la zone A chaque changement de, cliquez sur la colonne
pour laquelle vous voulez obtenir un sous total.
• Dans la zone Utiliser la fonction, cliquez sur la fonction de
synthèse à utiliser pour calculer les sous totaux
• Dans la zone Ajouter un sous-total à, activez la case à cocher
correspond à chaque colonne qui contient des valeurs pour
lesquelles vous souhaitez obtenir un sous total
Informatique 1 114
Initiation à Microsoft Excel
Tableau croisé dynamique
Un tableau croisé dynamique est un outil puissant capable de calculer, de
synthétiser et d’analyser des données, qui vous permet de voir des comparaisons,
des motifs et des tendances dans vos données.
Informatique 1 115
Initiation à Microsoft Excel
Tableau croisé dynamique
Création d’un tableau croisé dynamique:
1) Il faut vérifier les données de départ
Colonnes
Chaque colonne doit avoir un titre :
choisissez des titres significatifs,
les titres deviendront des identifiants dans le tableau croisé dynamique.
Vous ne devez pas avoir de colonnes vides.
Les données d'une colonne doivent être de même nature. Ne pas mélanger
dans une colonne des dates, des nombres, du texte…
Lignes
Ne laisser pas des lignes vides.
Informatique 1 116
Initiation à Microsoft Excel
Tableau croisé dynamique
Création d’un tableau croisé dynamique:
2) Sélectionner toutes les données (en incluant les titres) -> onglet Insertion ->
groupe tableau -> cliquer sur Tableau croisé dynamique
Informatique 1 117
Initiation à Microsoft Excel
Tableau croisé dynamique
Création d’un tableau croisé dynamique:
2) Sélectionner toutes les données (en incluant les titres) -> onglet Insertion ->
groupe tableau -> cliquer sur Tableau croisé dynamique
Vérifier que l'étendue sélectionnée est la bonne
Ex. Feuil1!$A$1:$H$8 : les cellules A1 jusqu'à H8 sur la feuille 1.
Le tableau peut s'afficher sur une nouvelle feuille (par défaut) ou vous pouvez
spécifier une feuille existante.
Cliquer sur OK pour confirmer
Informatique 1 118
Initiation à Microsoft Excel
Tableau croisé dynamique
Création d’un tableau croisé dynamique:
3) Paramétrer les zones
Informatique 1 119
Initiation à Microsoft Excel
Tableau croisé dynamique
Création d’un tableau croisé dynamique:
4) Analyser
Vous devez choisir ce que vous désirez afficher.
Débutons par un tableau simple, le nombre de personnes par sexe et âge :
Pour les lignes
Le sexe des personnes
Le total (généré automatiquement)
Pour les colonnes
L'âge des personnes
Le total (généré automatiquement)
Dans le cœur du tableau (appelé les "valeurs")
Le nombre de personnes
Informatique 1 120
Initiation à Microsoft Excel
Tableau croisé dynamique
Création d’un tableau croisé dynamique:
4) Analyser
Sélectionner un par un les champs désirés en haut de la fenêtre des paramètres et
glisser les dans la zone voulue (appuyer sur le bouton gauche de la souris sur un
champ et sans le relâcher glisser le champ dans la zone).
Pour le tableau du nombre de personnes par sexe et âge, vous devriez :
Informatique 1 121
Initiation à Microsoft Excel
Tableau croisé dynamique
Création d’un tableau croisé dynamique:
5) Filtre du rapport
Complexifions le rapport afin d'inclure de l'information par secteur. Combien y a-t'il
d'hommes et femmes par secteur? Il y a deux techniques possibles :
Ajouter le "Secteur" comme filtre de rapport
Ajouter le "Secteur" comme deuxième étiquette de ligne
Voici les résultats
Informatique 1 122
Initiation à Microsoft Excel
Tableau croisé dynamique
Création d’un tableau croisé dynamique:
5) Modifier les titres
Cliquer sur le titre, dans le tableau, et modifier le dans la barre de formule. Si le
titre se répète, il sera modifié partout.
Informatique 1 123
Initiation à Microsoft Excel
Tableau croisé dynamique
Création d’un tableau croisé dynamique:
5) Créer des groupes d'âges
Cliquer avec le bouton droit de la souris sur un des âges directement dans les titres
de colonne du tableau croisé dynamique. Choisissez l'option "Grouper". Dans la
fenêtre d'options, prendre les choix proposés ou modifier le nombre de début, de
fin et l'intervalle.
Informatique 1 124
Initiation à Microsoft Excel
Tableau croisé dynamique
Création d’un tableau croisé dynamique:
5) Afficher des pourcentages
Cliquer sur le champ dans la zone des champs de valeur (1) -> sélectionner le choix
"Paramètres des champs de valeur" (2).
Informatique 1 125
Initiation à Microsoft Excel
Tableau croisé dynamique
Création d’un tableau croisé dynamique:
5) Afficher des pourcentages
1. Choisir l'onglet "Synthèse par"
2. Choisir le type de calcul à effectuer dans la zone des valeurs (le cœur) du
tableau croisé dynamique.
a. Somme (ex. pour la somme des salaires)
b. Nombre (ex.: le nombre de personnes dans la liste)
c. Etc.
Informatique 1 126
Initiation à Microsoft Excel
Tableau croisé dynamique
Création d’un tableau croisé dynamique:
5) Afficher des pourcentages
4. Choisir le type d'affichage
a. % par ligne: la somme des valeurs des colonnes d'une ligne donnera 100%
au bout de la ligne.
b. % par colonne : la somme des valeurs des lignes d'une colonne donnera
100% à la fin d'une colonne..
c. % du total: la somme du total des colonnes ou du total des lignes donnera
100%. d. Etc.
5. On peut aussi choisir le format d'affichage des nombres (le nombre de décimal,
les montants…).
Informatique 1 127
