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