Informatique

Excel Perfectionnement

60 Rue du Kadous Tixeraine -BIRKHADEM– ALGER.

Tél : 021 48 24 65
Fax : 021 59 15 78
 Excel Perfectionnement

I. Saisir des données et des formules de calcul

1. Présentation du tableur
Le fichier Excel se nomme classeur, il se compose de feuilles (par défaut 3 feuilles). Une feuille est
constituée d’un ensemble de lignes (1 048 576) et colonnes (16 384). L’intersection d’une ligne avec une
colonne forme une cellule et elle porte comme référence le nom de sa colonne et le N° de sa ligne.

Par exemple la cellule D5 est l’intersection de la colonne D avec la ligne 5.

2. Les règles de conception Excel :

2.1. La sélection :
En cliquant sur une cellule, on dit qu’elle est sélectionnée (bien qu’elle reste de couleur
blanche). Partant de ce principe, si on sélectionne plusieurs cellules, la première reste toujours de fond
blanc (bien qu’elle est sélectionnée).

Si on clique sur un numéro de ligne, cette dernière est entièrement sélectionnée. Par exemple, on

sélectionne la ligne 3:

Support de cours 2
 Excel Perfectionnement

La sélection de la colonne se fait par simple clique sur le nom de cette dernière (A, B, C, …).

La sélection de la feuille entière se fait en cliquant sur le coin supérieur gauche de la feuille:

2.2. La saisie de chiffres :

La saisie dans une cellule se fait en pointant cette cellule (1 clique de souris). Une cellule peut
contenir un maximum de 254 caractères. Si la largeur de la cellule ne suffit pas pour afficher le nombre,
Excel affiche des dièses (###). Pour afficher le nombre en entier, vous devez augmenter la largeur de la
colonne.

 Soit manuellement par un cliquer glisser

 Soit par double clique pour un ajustement automatiquement de la largeur de la colonne par
rapport au contenu le plus long de ses cellules.

2.3. La saisie de texte :

La saisie du texte en dehors du tableau est différente à celle dans Word :

 Saisir la phrase et valider par « Entrée »

 Sélectionner la cellule de saisie et les cellules adjacentes (dans la même ligne)

 Cliquer sur le bouton fusionner et centrer , ainsi les cellules sélectionnées se regroupent et
deviennent une seule.

Remarque :

Le retour à la ligne dans la même cellule se fait par la combinaison : Alt + Entrée. Ou bien en utilisant le

bouton : (Ruban Accueil).

Support de cours 3
 Excel Perfectionnement

2.4. La modification :
A la différence de Microsoft Word : pour toute modification, on clique à l’endroit exacte et on
réalise une modification (ajout/ suppression). Dans Microsoft Excel, pour réaliser une modification on
doit

 Double cliquer sur cette cellule;

 Ou bien on utilise la touche F2;
 Ou bien on clique sur la barre de formule :

5.5. Les formules :

Les formules permettent d’effectuer des opérations telles que les additions, les multiplications …
etc. sur les valeurs d’une feuille de calcul. Ceci afin d’analyser vos données. Une formule commence
(débute) toujours par le signe égal (=) et elle fait référence à des cellules contenant les données.

Exemple :

Pour calculer la somme de deux cellules, exécutez les étapes suivantes :

 Sélectionnez la cellule où doit figurer le résultat ;

 Appuyez sur la touche égal (=)
 Cliquez sur la première cellule (12,45)
 Appuyez sur la touche (+)
 Cliquez sur la deuxième cellule (14,56)

 Appuyez sur la touche ENTREE pour avoir le résultat.

Remarques :

1. On remarque qu’au fur et à mesure qu’on clique sur une cellule, sa référence apparaît dans la
formule.
2. Tout changement sur les données (dans l’exemple, les cellules : E10 ou E11), fait changer
automatiquement le résultat (cellule E12).

Support de cours 4
 Excel Perfectionnement

2.6. La recopie de formules et la somme automatique :

Excel permet de recopier une formule qui se répète sans avoir à la réécrire plusieurs fois. Soit le
devis suivant à élaborer avec EXCEL :

Le montant HT de chaque article est égal à la quantité multipliée par le prix. Donc la cellule du montant
est égale à la cellule de gauche multipliée par la deuxième cellule de gauche :

Après validation (avec ENTREE), Excel affiche le résultat de la formule. Maintenant pour ne pas répéter
la formule dans les cellules de bas, il suffit de copier la formule, en cliquant sur le coin inférieur droit de
la cellule contenant le 1er montant (D2) et glisser vers le bas :

Aussi, le total est demandé en cliquant sur le bouton « Somme Automatique » :

Remarque :

Pour arranger les chiffres, on dispose des 4 boutons suivants

: Style de virgule (groupes de 3 et 2 chiffres après la virgule)

: ajouter une décimale

: réduire une décimale

Support de cours 5
 Excel Perfectionnement

: ajouter la monnaie

Pour la 1ère ligne du tableau, on a utilisé les deux boutons centrer: (horizontalement et
verticalement respectivement).

La recopie de formule se fait verticalement et se fait horizontalement de la même façon pour une
formule ou une fonction.

3. Les fonctions usuelles : Somme, Max, Min, Moyenne, Nombre, Si

Elles sont utilisées comme suit :

 Cliquer sur la case de résultat ;

 Cliquer sur la flèche du bouton puis cliquer sur la
fonction ;
 Sélectionner les cellules de travail:

 Valider avec ENTREE.

Remarque :

La touche CTRL enfoncée, permet après sélection du 1 er bloc, de sélectionner les autres blocs de
données pas forcément adjacents.

4. La fonction Si
C’est la fonction à deux alternatives : selon le résultat d’un test, il y a deux possibilités test vrai, test
faux). Par exemple à la colonne « Montant » de l’exemple s’ajoute la colonne « Observation » avec deux
valeurs possibles :
« Montant important » si la valeur du montant >100 000 DA et « Montant simple » sinon :

Support de cours 6
 Excel Perfectionnement

Après validation et recopie de formule, le résultat peut ressembler à :

5. Contrôler les données avec la mise en forme conditionnelle

C’est une commande d’octroi de couleur selon une condition appliquée sur la colonne elle-même. Dans
l’exemple précédent, on peut donner une couleur (mise en forme) rouge par exemple pour tout
montant dépassant les 100 000 DA (la commande existe dans le ruban « Accueil ») :

On précise le seuil comme suit :

Support de cours 7
 Excel Perfectionnement

Et le résultat sur la colonne peut ressembler à :

Remarque : sur la même colonne, on peut appliquer plusieurs règles de mise en forme conditionnelle de
la même façon. Par exemple on peut donner une couleur verte si le montant est compris entre 50 000 et
100 000 DA :

Et le résultat sur la colonne :

6. Créer un tableau modèle

Au travail quotidien, on a l’habitude de communiquer des états « tableaux » périodiquement et donc il
faut qu’ils soient de même forme. On crée alors un tableau « modèle » dans la feuille1, ensuite copier-
coller ne permet de garder la forme du tableau. Plutôt :
Bouton droit de souris sur l’onglet de la feuille1 et on clique sur la commande « déplacer ou copier » :

Support de cours 8
 Excel Perfectionnement

Il faut cocher « créer une copie » :

Excel crée donc une deuxième feuille double de la feuiile1 (même contenu, même

forme) :
7. Lier les tableaux avec des formules ou bien le collage spécial
Une formule Excel peut faire référence à des données ou des résultats dans la même feuille ou une
autre feuille ou bien même un autre classeur. La barre de formule nous permet de suivre l’évolution de

la formule, par exemple :

Cette formule fait référence à deux autres cellules D5 de la feuile1 et E7 de la feuille2.

Une autre alternative est d’utiliser le collage spécial :

Support de cours 9
 Excel Perfectionnement

II. Gérer ses documents

1. Maitriser la mise en page :
La mise en page est réalisable avec les commandes du ruban "Mise en page":

 Orientation : portrait ou paysage;

 Taille: sélectionner la taille du papier à utiliser (A3, A4 …);
 Marges: régler avec précision les marges et éventuellement centrer le tableau sur la page s'il
s'agit d'un petit tableau:

Support de cours 10
 Excel Perfectionnement

Éventuellement, centrer le tableau sur la page (surtout horizontalement)

 Mettre à l'échelle : réduire cette valeur jusqu'à régler le problème de débordement du tableau
si c'est le cas;
 Quadrillage: l'afficher ou le masquer à l'écran (à l'impression);
 Imprimer les titres: permet de répéter la ligne des intitulés du tableau en haut de chaque page
lors de l'impression, ce qui facilitera énormément l'exploitation du tableau:

 Zone d'impression: permet de cibler une partie sélectionnée du tableau pour l'impression:

Le résultat peut être le suivant (aperçu) pour les deux dernières lignes du tableau:

Support de cours 11
 Excel Perfectionnement

Cette zone doit être annulée par la commande "Annuler" du menu précédent.

 Sauts de page: permet de décomposer le tableau en plusieurs pages à l'impression. Pour tout
endroit désiré du tableau, on insère un saut:

 Le mode mise en page: bouton du milieu (en bas à droite d'Excel). On peut ajouter l'en-
tête:

Et les numéros de pages en pied de page, avec les commandes:

Remarque :

Pour une meilleure consultation et saisie dans un long tableau, on peut figer les volets: fixer la partie
contenant les intitulés et garder le reste du tableau libre:

Il faut cliquer juste après la partie à figer (en lignes ou en colonnes), ensuite utiliser la commande
suivante de l’onglet « Affichage »:

Par exemple, dans le tableau suivant on peut cliquer dans la cellule A2 et exécuter cette commander
pour figer la 1ère ligne (des intitulés) du tableau:

Support de cours 12
 Excel Perfectionnement

2. Imprimer
A tout moment, la combinaison CTRL+F2, permet d'accéder à l'aperçu avant impression :

Il faut donc sélectionner le nombre d’exemplaires, l’imprimante, les pages à imprimer et cliquer sur le

bouton « ».

III. Générer des graphiques

1. Développer des graphiques à partir des données du tableau :
Un graphique est la représentation visuelle des données d’un tableau. Les graphiques facilitent
l’interprétation de données complexes. Ils convertissent les données figurant dans le tableau en format
graphique (camembert, histogramme, ...), pouvant être lu et rapidement interprété. Cet outil permet
de comparer, analyser et décider efficacement.

Support de cours 13
 Excel Perfectionnement

Exemple :

Si on veut représenter le premier tableau sous forme de graphique, en particulier on veut comparer
entre les formations en se basant sur leurs montants associés.

 D’abord il faut sélectionner la 1ère colonne (les formations), maintenir la touche CTRL enfoncée
et sélectionner la 2 ème colonne (les montants), puis utiliser l'un des styles de la partie
"Graphiques" du ruban "Insertion":

Par exemple, on choisit le type secteurs (2D ou mieux 3D);

 On sélectionne une disposition parmi celles proposées :

Et éventuellement un style.

 Déplacer le graphique vers une nouvelle feuille ou le garder dans la même feuille
du tableau:

Support de cours 14
 Excel Perfectionnement

Ce qui donne :

Remarques :

1. Un graphique peut se baser sur plus de 2 colonnes (2 variables), on utilise alors le type :
Histogramme 3D.
Soit le tableau récapitulatif suivant des budgets de formation par thème :

Si on veut réaliser un graphique pour analyser les 3 dernières années, on doit utiliser Histogramme
(colonnes), car le type « Secteur » se base sur les deux premières colonnes sélectionnées et ignore les
autres colonnes sélectionnées :

Support de cours 15
 Excel Perfectionnement

On peut donc analyser chaque thème dans le temps et entre les thèmes aussi.

2. Un graphique peut se baser sur des lignes et on utilise pour cela la même méthode utilisée pour
les graphiques basés sur les colonnes et plutôt on se base sur les courbes. Par exemple on
s’intéresse à la formation « Management » dans le temps :

2. Les Sparkline
Un graphique Sparkline, est un graphique minuscule dans une cellule de la feuille de calcul qui propose
une représentation visuelle des données. Ce type de graphique vous permet d’afficher les tendances
d’une série de valeurs comme les augmentations ou réductions.

Par exemple, en observant un phénomène dans l’entreprise (vente, consommations, dépenses, …), on a
collecté les données suivantes :

Le besoin est d’exprimer l’évolution dans la cellule correspondante mais sous forme d’un petit
graphique. On cliquer sur cette cellule et on utilise la commande « Sparkline » du ruban « Insertion » :

Support de cours 16
 Excel Perfectionnement

On sélectionne les cellules de données à sa gauche et le résultat sera :

Dont on peut utiliser les commandes du ruban associé pour personnaliser le Sparkline (Type, couleur,
…).

Remarque : ce type de graphique est encore très représentatif lorsque le nombre de phénomènes à
surveiller (dans un tableau de bord) est important, car un graphique ordinaire devient illisible.

IV. Utiliser les outils de synthèse et commandes avancées

1. Classer avec la commande tri :
Excel nous permet de faire un classement (croissant/ décroissant) du tableau avec plusieurs niveaux
(classement puis sous classement).

1.1. Le tri à un seul niveau :

Consiste à classer le tableau par rapport à une seule colonne. Il suffit de cliquer dans cette colonne
(n’importe où), ensuite cliquer sur le bouton :

: Pour un classement par ordre croissant.

: Pour un classement par ordre décroissant.

Support de cours 17
 Excel Perfectionnement

1.2. Le tri à plusieurs niveaux :

Consiste à classer le tableau par rapport à deux colonnes ou plus. On utilisa plutôt le bouton:

Exemple : trier le tableau par « Région », puis par « Modèle » :

Ce qui permettra une recherche ciblée des données après impression du tableau.

2. Interroger les tableaux via les filtres de données :

Les filtres permettent de prendre une sous liste d’une liste globale (un sous tableau depuis le tableau
global), satisfaisant un critère donné. C’est la commande permettant d’interroger le tableau.

On utilise le ruban "Données":

Un filtre s’applique à la base d’une colonne donnée. Il peut s'appliquer sur le résultat d'un autre filtre.

Exemple : Suivi des ventes des modèles pour 3 agents :

Support de cours 18
 Excel Perfectionnement

2.1. Filtre par classement :

Dans ce cas on cherche par exemple les 10 premiers, les 5 premiers, les 20 derniers (les 10 meilleures
ventes, les 5 mauvaises ventes, ..).

On clique sur la flèche du Montant HT :

On aura le message suivant:

Haut : les 3 meilleures ventes (premiers)

Support de cours 19
 Excel Perfectionnement

Bas : les 3 mauvaises ventes (derniers)

Le tableau filtré (les 3 meilleures ventes) sera par exemple :

Remarque: la commande suivante permet d'annuler le filtre et retrouver la forme initiale du tableau:

2.2. Filtre numérique :

Dans ce cas, on exprime un critère numérique. Par exemple, on veut la liste des ventes moyennes :
Montant HT entre 150 000 et 300 000 :

On utilise alors un filtre numérique:

Et on tape les valeurs de l'intervalle recherché:

Support de cours 20
 Excel Perfectionnement

Le clique sur OK, donne le tableau ci-dessous :

2.3. Filtre textuel

Si la colonne contient des intitulés, le filtre se nomme : un filtre textuel. Dans ce genre de filtres, il faut
cocher les intitulés ciblés et décocher les autres. Par exemple les ventes des régions : "Centre" et "Est":

Ce qui donne par exemple :

Support de cours 21
 Excel Perfectionnement

2.4. Filtre chronologique

Il s'agit d'opérer des filtres sur une colonne contenant des dates. Par exemple les opérations de ventes
réalisées entre le 12 mars et le 12 avril 2013.

Il faut compléter la boite de dialogue qui s'affiche:

Support de cours 22
 Excel Perfectionnement

2.5. Filtre avancé

Dans cette partie, on peut exprimer plusieurs critères sur plusieurs colonnes d’un seul coup sans avoir
besoin à faire plusieurs critères simples imbriqués. Pour cela on doit créer une zone de critères : se
composant de la ligne des en-têtes de colonnes et chaque critère est exprimé en bas de la colonne
associée, par exemple :

Les critères : les ventes des Régions « Est » ou bien « Ouest » dont la quantité est
supérieure à 5 quelque soit le modèle.

On utilise la commande « Avancé » du ruban « Données »:

La fenêtre suivante s’affiche :

Support de cours 23
 Excel Perfectionnement

- La plage représente le tableau de données

- La zone de critères : il faut sélectionner entièrement cette zone contenant les critères

- Copier dans : cliquer à l’endroit désiré pour recevoir le résultat (tableau trouvé après le filtrage)

Le résultat peut être :

Remarque :

Les critères exprimés sur la même ligne représentent des « Et » logiques et ceux exprimés sur des lignes
différentes représentent des « Ou » logiques.

3. Réaliser des synthèses avec les Sous totaux :

Dans le tableau des ventes, si on veut réaliser des sous totaux pour chaque modèle et réaliser la somme
des montants, cela nécessite beaucoup de filtres (si on a 30 modèles, il nous faut 30 filtres) ; une autre
solution serait de demander à Excel de faire cette synthèse en utilisant la commande du ruban

"Données" :

Support de cours 24
 Excel Perfectionnement

Dont on doit préciser la colonne de synthèse (Modèle), la fonction de synthèse (Max, Min, Moyenne,
somme..) et le champ qu'il faut cumuler (montant):

Le
résultat peut ressembler à :

C’est une synthèse basée sur une seule colonne.

Remarque :

Support de cours 25
 Excel Perfectionnement

Avant d’utiliser cette commander il faut trier le tableau par rapport à la colonne de synthèse. Dans
l’exemple le tableau est classé par modèle.

4. Créer les tableaux et graphiques croisés dynamiques :

4.1. Bilan instantané avec les tableaux croisés dynamiques

Un tableau croisé dynamique est une composition rapide d’un tableau de synthèse, d’un grand tableau
de données. Cette synthèse est basée sur 2 colonnes (variables) et plus, ce tableau est dynamique. Cela
veut dire qu’il est possible d’ajouter, de retirer et de modifier la présentation du tableau.

On utilise le même tableau des ventes précèdent comme exemple. On fait appel à la commande du
ruban « Insertion », après avoir cliqué dans le tableau.

Ce qui donne:

Support de cours 26
 Excel Perfectionnement

Excel crée automatiquement une nouvelle feuille. Reste à placer, par un cliquer-glisser les étiquettes de
lignes (région) et de colonnes (modèle) le champ de données (Montant):

Le cliquer-glisser se fait de la partie supérieure vers la zone correspondante de la partie inférieure.

Le tableau de synthèse est crée automatiquement:

Remarques :

Support de cours 27
 Excel Perfectionnement

1. L’aspect dynamique du tableau est du fait qu’il est possible d’inverser les colonnes en lignes,
ainsi qu’on peut avoir des sous synthèses, par exemple la synthèse des ventes des modèles
Produit1 et Produit2 (il faut décocher les autres modèles).
2. Après toute modification de données dans le tableau source et pour prendre en charge ces
changements, il faut cliquer avec le bouton droit de souris sur le tableau croisé dynamique et
l’actualiser:

3. Pour avoir le graphique croisé dynamique correspondant, il suffit de cliquer avec dedans et
utiliser la touche F11 du clavier.
4. La zone « Champ de page », permet de recevoir les autres variables de synthèse (3ème, 4ème, …),
exemple : "Énergie", "Motorisation", "Nombre de places"… si on rajoute la colonne "Énergie" au
tableau, puis en réalisant un tableau croisé dynamique, le résultat peut être:

Dans ce cas par exemple, on s’intéresse à l'énergie "Essence".

5. Double cliquer sur la fonction de synthèse (coin supérieur gauche du tableau croisé
dynamique), pour la changer :

Support de cours 28
 Excel Perfectionnement

Par exemple si le tableau de données permet de suivre les dossiers (courrier, missions, interventions, ..),
on utilise la fonction de synthèse « Nombre de dossier » au lieu de « Somme de dossier ».

6. Le double clique sur un total général, par exemple « 147000 » du modèle « Produit1 », créera
une nouvelle feuille avec toutes les ventes ayant donné naissance à ce total général :

4.2. Les segments (Slicer)

Dans les versions antérieures de Microsoft Excel, on peut utiliser des filtres de rapport pour filtrer les
données d’un rapport de tableau croisé dynamique (zone qui reçoit la 3ème, 4ème variable utilisée dans la
synthèse du tableau de données)

Dans Microsoft Excel 2010, on a le choix d’utiliser les slicers pour filtrer les données. Les slicers
disposent de boutons que vous pouvez activer pour filtrer les données de tableau croisé dynamique.
Outre le filtrage rapide, les slicers indiquent également l’état de filtrage actuel, ce qui permet de mieux
comprendre ce qui s’affiche dans un rapport de tableau croisé dynamique.

Soit le tableau « suivi des paiements » suivant :

Support de cours 29
 Excel Perfectionnement

On peut demander le bilan des paiements par client, par mode de paiement et par banque :

On remarque que la variable « Banque » est dans la zone filtre du rapport. On crée à sa place un
segment en utilisant la commande du ruban « Insertion » :

Ce qui donne :

Support de cours 30
 Excel Perfectionnement

Et l’utilisation est très intuitive.

5. La validation de données
Pour certaines colonnes de saisie, l’intervalle des valeurs permises est bien connu, et par conséquent,
toute saisie dans les cellules de cette colonne ne doit pas prendre une valeur non permise.

5.1. Validation d'une colonne à saisir

Excel dispose d’une commande pour contrôler la valeur saisie : c’est la commande « Validation des
données » du ruban « Données ».

Par exemple : la colonne « Expérience » peut recevoir des valeurs entre : 1 et 40 :

Avant de saisir, on sélectionne cette colonne, puis on demande la commande citée ci-dessus :

Support de cours 31
 Excel Perfectionnement

Pour informer l’utilisateur en cas d’erreur, on clique sur l’onglet « Alerte d’erreur » et on crée un
message d’erreur :

Dorénavant, dés qu’on essaye de saisir une valeur non permise, le message suivant s’affiche et Excel
interrompe la saisie :

5.2. Validation d'une colonne remplie:

La manœuvre précédente se fait pour une colonne à saisir, mais si elle est déjà saisie, on n'aura pas le
message d'erreur. Par contre, on peut utiliser une autre commande la complétant, permettant
d'entourer avec un cercle rouge les valeurs non valide dans cette colonne tapée:

Ce qui fait par exemple:

Support de cours 32
 Excel Perfectionnement

Il faut noter qu’il s’agit ici d’un effet visuel (apparait à l’écran et non pas à l’impression)

5.3. Liste de choix

Certaine colonnes contiennent des valeurs appartenant à un ensemble déterminé, c'est à dire
l'ensemble des valeurs possible est connu à l'avance. Pour réduire les erreurs de saisie et accélérer la
saisie, on peut programmer ces valeurs possibles et l'utilisateur arrivant à une cellule de cette colonne
choisira une valeur parmi une liste proposée en utilisant la souris. Par exemple:

La colonne "Direction" aura la forme:

Remarque : Cette liste peut exister dans une autre feuille et portant un nom, par exemple dans la
feuille2 :

Support de cours 33
 Excel Perfectionnement

Et on utilisera alors ce nom pour faire référence à cet ensemble de valeurs :

6. Supprimer les doublons dans un tableau

Cette commande propre à cette nouvelle version d’Excel, permet de supprimer les lignes en double dans
un tableau et donc éliminer les saisies en double. Le double peut être par rapport à une seule colonne
ou bien par rapport à plusieurs colonnes.

On utilise la commande suivante du ruban « Données » :

Exemple : Soit le tableau suivant :

Support de cours 34
 Excel Perfectionnement

En cliquant sur la commande, la boite de dialogue suivante s’affiche :

Et donc Excel ne trouve pas de doublons (inexistence de deux lignes identiques). Par contre si on ne
laisse coché que le N° Bon :

Le message suivant s’affiche :

Et le tableau prend la forme :

La 2ème occurrence du N° Bon est supprimée du tableau (la ligne entière).

Support de cours 35
 Excel Perfectionnement

7. Faire des prévisions avec la commande « Valeur cible »

Cette commande permet de répondre à des questions du genre :

Combien on doit encore vendre de « Produit2 » au centre pour que le chiffre d’affaire total atteint
35 000 KDA au lieu de : 29 400 KDA actuellement ?

On utilise alors la commande « Valeur cible » du ruban « Données » :

La quantité 5 de « Produit2 » au centre est à la cellule D et le total actuel de toutes les ventes des
différentes régions à la cellule G6:

Excel conclu que la quantité doit passer de la valeur actuelle : 5 à la valeur 9 pour atteindre exactement
l’objectif global ciblé :

Support de cours 36
 Excel Perfectionnement

8. Gestion des scénarios

Les scénarios font partie d'une série de commandes aussi appelées outils d'analyses de simulation. Un
scénario est un ensemble de valeurs que Microsoft Office Excel enregistre et peut remplacer
automatiquement dans la feuille de calcul. Vous pouvez utiliser des scénarios pour prévoir différents
résultats et conclure par la suite sur l’un d’entre eux. Excel permet de créer des scénarios ensuite
permet d’afficher un rapport de synthèse comportant tout les scénarios crées.

Exemple : Soit le tableau suivant :

Le 1er scénario est d’utiliser la remise à 5%. On utilise la commande suivante du ruban « Données » :

Ce qui donne :

On ajoute alors ce 1er scénario :

Support de cours 37
 Excel Perfectionnement

Ensuite il faut taper les valeurs du scénario :

De la même façon, on peut ajouter deux autres scénarios à 10% et 12%. Le retour vers la commande
« Gestionnaire de scénarios » affiche la fenêtre suivante :

Et le clique sur le bouton « Synthèse », affichera dan une nouvelle feuille « synthèse des scénarios » :

Support de cours 38
 Excel Perfectionnement

9. Protéger le classeur et les feuilles :

Excel nous permet de créer des modèles de tableaux ; dans chaque modèle crée certaines colonnes sont
fixes et d’autres sont variables. La protection de la feuille consiste à interdire la saisie dans les colonnes
fixes et laisser le champ libre de saisie dans les colonnes variables, aussi pour ôter cette protection, il
faut connaître le mot de passe. En plus, pour des raisons de confidentialité éventuelle, on peut masquer
toutes les formules de calcul.

9.1. Masquer les formules d’une feuille de données :

Une fois le modèle de tableaux est crée, on peut masquer toutes les formules de la feuille comme suit :
il faut sélectionner toute la, puis bouton droit de souris sur la sélection et demander la commande
« Format de cellule», sur l’onglet « Protection », il faut cocher la case « Masquée » et confirmer « Ok » :

A remarquer que si la feuille n’est pas protégée, cette commande n’a aucun effet.

9.2. Protection de la feuille :

Dans la boite de dialogue affichée dans la section précédente, on remarque que toutes les cellules de la
feuille sont prêtes à être verrouillées (la mention verrouillée est cochée) ; alors si on protège la feuille,
Excel interdira toute saisie dans toutes la feuille. Cependant, si on veut que la colonne de saisie de
données reste non verrouillée, il faut la sélectionner (ex : Quantité) et décocher la mention verrouillée
dans l’onglet « protection » de la commande précédente:

Support de cours 39
 Excel Perfectionnement

Puis passer à la protection, en utilisant le bouton droit de souris sur l'onglet de cette feuille:

On termine avec la saisie et la confirmation du mot de passe:

Support de cours 40
 Excel Perfectionnement

Le résultat de toutes ces opérations est :

 Toutes les formules de calcul sont masquées ;

 Tout le contenu de la feuille est non modifiable à part la colonne « Quantité ».

9.3. Protéger le classeur

On vient de protéger la feuille de calcul, si on veut protéger tout le classeur, il faut demander l’option
« Options générales » dans le menu « Outils » au moment de l’enregistrement :

Ensuite, Saisir et confirmer le mot de passe dans la fenêtre qui s’affiche :

10. Revoir la structure du tableau (Convertir les colonnes) :

Cette commande permet d’éclater une colonne contenant plusieurs informations groupées et de mettre
chacune dans une colonne à part.

10.1. Utiliser un séparateur

Exemple : on veut éclater la colonne « Nom Prénom » du tableau

Il faut d’abord insérer une colonne vide juste après la colonne à éclater. Ensuite, sélectionner la colonne
à éclater (ex : "Nom prénom") et utiliser la commande « Convertir » du ruban « Données »:

Support de cours 41
 Excel Perfectionnement

Dont on précise qu’il s’agit du séparateur "Espace" comme délimiteur :

Le résultat sera comme suit :

Remarque :

Le ‘&’ permet de concaténer les chaînes de caractères (regroupement de colonnes en une seule), c'est-
à-dire réaliser l’opération inverse.

Ex : =Nom&" "&Prénom

Support de cours 42
 Excel Perfectionnement

10.2. Utiliser une longueur fixe

La colonne peut contenir des informations groupées. Chaque information est d’une longueur fixe. Par
exemple la colonne « Matricule » contient quatre informations groupées : le N° de série, le type de
véhicule, l’année de mise en circulation et la wilaya de résidence du propriétaire :

On veut extraire chaque information dans une colonne. On utilise la même commande précédente
après sélection de la colonne :

On prend donc le 2ème choix et on passe au suivant :

Support de cours 43
 Excel Perfectionnement

Il faut faire un clique de souris pour chaque séparation désirée. Une fois terminé, le tableau aura la
forme suivante :

11. Consolider les données

1. Dans chaque feuille de calcul qui contient les données que vous voulez consolider,
définissez les données en procédant comme suit :

 Chaque plage de données est au format liste : la première ligne de chaque

colonne contient une étiquette, chaque colonne contient des faits similaires et sans vide.

 Placez chaque plage dans une feuille de calcul distincte, la feuille de

consolidation est vide et elles ont toutes la même disposition.

2. On clique sur la 1ère cellule de la plage de consolidation

3. Sous l'onglet Données, dans le groupe Outils de données, on clique sur Consolider.

4. Dans la zone Fonction, choisir la fonction somme par exemple,

5. Si la feuille de calcul qui contient les données à consolider est dans un autre classeur, on
clique sur Parcourir pour localiser le classeur,

6. Si la feuille de calcul contenant les données que vous souhaitez consolider se trouve dans le
classeur actif:

1. Dans la zone Référence, cliquez sur le bouton Réduire la boîte de dialogue pour
sélectionner les données dans la feuille de calcul.

Support de cours 44
 Excel Perfectionnement

2. Dans la boîte de dialogue Consolider, cliquez sur Ajouter, puis on répète pour ajouter les
autres tableaux à consolider (d’autres feuilles ou d’autres classeurs) :

Si l’un des tableaux possède des valeurs négatives et l’autre des valeurs positives, la
consolidation permet de donner les écarts entre les deux tableaux, par exemple :

V. Fonctions avancées
1. Réviser les tableaux

1.1. Vérification
Ce groupe de commandes permet de parcourir tout le contenu de a feuille et corriger les erreurs
d’orthographe, retrouver les synonymes et traduire vers une autre langue.

1.2. Les commentaires

Un commentaire permet de marquer une description ou remarque et l’associer à une cellule. Il est- juste
visuel (ne s’imprime pas), mais donne une grande lisibilité au travail pour l’utilisateur qui va l’exploiter.

Support de cours 45
 Excel Perfectionnement

On utilise la commande suivante du ruban « Révision » :

Exemple

Le commentaire s’affichera dés que la souris se positionne sur le petit triangle rouge au coin supérieur
droit de la cellule.

En cliquant sur la cellule du commentaire, on peut modifier ou supprimer ce dernier :

Ou bien se déplacer vers le commentaire suivant / précédent.

1.3. Classeur partagé :

Plusieurs utilisateurs travaillent sur le même fichier et au même moment. On utilise la commande
suivante du ruban « Révision » :

La boite de dialogue suivante s’affiche :

Support de cours 46
 Excel Perfectionnement

Il faut cocher : Permettre une modification multi utilisateur et dans l’onglet « Avancé » : mise à jour des
modifications, automatiquement toues les : 10 minutes par exemple pour une collecte automatique des
informations tapées par les différents utilisateurs.

2. Auditer les Formules

2.1. Utiliser les espions

Lorsque des cellules ne sont pas visibles dans une feuille de calcul, vous pouvez les visualiser avec leurs
formules dans la barre d'outils Fenêtre Espion.

La fenêtre Espion permet d'inspecter, de contrôler ou de confirmer les calculs de formules et leurs
résultats dans des feuilles de calcul volumineuses. Grâce à la fenêtre Espion, il n'est plus nécessaire de
faire défiler, ni d'accéder manuellement aux différentes parties de la feuille de calcul.

On utilise la commande « Fenêtre espion » du ruban « Formules » :

Ce qui affiche :

Support de cours 47
 Excel Perfectionnement

Qui est une barre d’outils qu’on peut la placer n’importe où à l’écran. On clique alors sur cette cellule
puis on lui ajoute un espion :

Et il devient visible :

Pour tout changement de données faisant référence à cet espion la valeur est affichée et change
automatiquement :

A tout moment, on peut supprimer l’espion en utilisant cette même fenêtre.

Support de cours 48
 Excel Perfectionnement

2.2. Audit des formules

Pour un éventuel contrôle sur le calcul d’une formule, on peut cliquer sur cette dernière et utiliser les
commandes « Audit de formules » du ruban « Formules » :

Où :

« Repérer les antécédents » permet de placer une flèche bleu vers toute cellule utilisée pour calculer la
présente cellule.

Par exemple :

Le 1er montant dépend de deux cellules : 1er prix unitaire et la ,1ère quantité.

Ce montant participe aussi dans le calcul du total (somme des montants). La commande « Repérer les
dépendants », affiche une flèche vers toutes les autres cellules utilisant notre cellule dans leurs calculs :

2.3. Définition de noms

Pour faciliter la manipulation des cellules de référence, ainsi que certaines plages de données et pouvoir
les utiliser aisément dans tout le classeur, on leur associe un nom.

On doit sélectionner cette plage, puis utiliser la commande suivante du ruban « Formules » :

La fenêtre suivante s’affiche, dont il faut saisir le nom choisi (espace on autorisé) :

Support de cours 49
 Excel Perfectionnement

On peut lui faire référence dans n’importe quelle formule ou fonction. On utilise alors la commande :

La liste des noms s’affiche :

On sélectionne le nom « les_ventes », on confirme :

et on continu la formule.

Le gestionnaire de noms permet de modifier la plage associée à un nom défini ou même supprimer
cette plage nommée :

Support de cours 50
 Excel Perfectionnement

3. La bibliothèque de fonctions
Excel possède un nombre important de fonctions dans sa bibliothèque, regroupées par famille :

3.1. Fonctions de date et heure

Cette famille regroupe des fonctions pour

manipuler la date ou l’heure. Par exemple :

 ANNEE : extrait l’année à partir d’une cellule contenant

une date :

Où F5 contient une date.

 AUJOURDHUI : affiche la date du jour (date système) se
mettant à jour automatiquement :

 MAINTENANT : affiche la date et l’heure actuelle :

Support de cours 51
 Excel Perfectionnement

 JOURSEM : renvoie une valeur entre 1 et 7 représentant du 1 er jour (dimanche) au

7ème jour (samedi) de la semaine pour une date donnée :

 MOIS : extrait le mois d’une date donnée

 JOUR : extrait le jour d’une date

3.2. Fonctions de recherche et de référence

Fonction Decaler
Renvoie une référence à une plage qui correspond à un nombre
déterminé de lignes et de colonnes d'une cellule ou plage de
cellules. La référence qui est renvoyée peut être une cellule unique
ou une plage de cellules. Vous pouvez spécifier le nombre de lignes
et de colonnes à renvoyer.

Syntaxe : DECALER(réf;lignes;colonnes;hauteur;largeur)

Réf : est la référence par rapport à laquelle le décalage doit être

opéré. L'argument réf doit être une référence à une cellule ou à
une plage de cellules.

Lignes : est le nombre de lignes vers le haut ou vers le bas dont la

cellule supérieure gauche de la référence renvoyée doit être
décalée. Si l'argument lignes est égal à 5, la cellule supérieure
gauche de la référence est décalée de cinq lignes en dessous de la
référence. L'argument lignes peut être positif (c'est-à-dire en
dessous de la référence de départ) ou négatif (c'est-à-dire au-
dessus de la référence de départ).

Support de cours 52
 Excel Perfectionnement

Colonnes : est le nombre de colonnes vers la droite ou vers la gauche dont la cellule supérieure gauche
de la référence renvoyée doit être décalée. Si l'argument colonnes est égal à 5, la cellule supérieure
gauche de la référence est décalée de cinq colonnes vers la droite par rapport à la référence. L'argument
colonnes peut être positif (c'est-à-dire à droite de la référence de départ) ou négatif (c'est-à-dire à
gauche de la référence de départ).

Hauteur : est la hauteur, exprimée en nombre de lignes que la référence renvoyée doit avoir.
L'argument hauteur doit être un nombre positif.

Largeur : est la largeur, exprimée en nombre de colonnes que la référence renvoyée doit avoir.
L'argument largeur doit être un nombre positif.

Notes

Si les arguments lignes et colonnes décalent la référence au-delà du bord de la feuille de

calcul, la fonction DECALER renvoie la valeur d'erreur #REF!.
Si les arguments hauteur ou largeur sont omis, les valeurs par défaut des arguments
hauteur et largeur sont celles de l'argument réf.
La fonction DECALER n'a pas pour rôle de décaler physiquement les cellules dans la feuille ni
de modifier la sélection ; elle renvoie simplement une référence. La fonction DECALER peut
être utilisée avec toutes les fonctions exigeant une référence comme argument.
Exemple : liste de choix variable

Soit la liste de valeurs suivante :

On définit un nom variable « Type_intervention » :

Support de cours 53
 Excel Perfectionnement

On crée une liste de choix :

Le résultat sera :

En ajoutant un autre type d’intervention (Électromécanique), il s’ajoute automatiquement à la liste :

Support de cours 54
 Excel Perfectionnement

Idem pour une modification ou suppression d’un type donné.

Fonctions de recherche : Recherche, RecherchH, RecherchV

Permettent le parcours d’une base de données en se basant sur une valeur d’un champ de
recherche pour retrouver la valeur d’un autre champ du même enregistrement ciblé.

Exemple :

Dans la base de données « Suivi des Paiements » au niveau de la comptabilité :

Remarquer le nom défini pour la base de données.

De cette base de données, on veut consulter et créer une fiche de paiement en affichant tous les
détails d’une opération de paiement, en saisissant juste son N° :

Support de cours 55
 Excel Perfectionnement

D’abord on utilise RechercheV pour trouver le « Paiement » :

Valeur cherchée : le N°3 (cellule E4)

Table_matrice : base de données

No_index_col : une fois l’enregistrement trouvé, récupérer la valeur de la colonne2.

Valeur_proche : si l’enregistrement n’existe pas, on ne veut pas avoir une valeur Proche (faux).

En appliquant la même manœuvre aux autres champs, le résultat sera :

Support de cours 56
 Excel Perfectionnement

En changeant le N°, la fiche se remplie automatiquement par une série de recherches dans la base
de données.

Remarque :

La fonction RechercheV, effectue une recherche verticale basée toujours sur la 1 ère colonne de la
base de données (champ clé). Dans certains cas particuliers on peut faire appel à la fonction
Recherche :

Où :

Valeur _ cherchée : valeur qui servira à la recherche

Vecteur _ recherche : la colonne où se fera la recherche (pas forcément la 1 ère colonne)

Valeur _ résultat: la colonne ciblée qui contient l'information à récupérer.

Support de cours 57
 Excel Perfectionnement

Fonction EQUIV
On utilise la fonction EQUIV plutôt que la fonction RECHERCHE lorsque on a besoin de la position
d'un élément dans une plage et non pas de l'élément en tant que tel.

Syntaxe EQUIV (valeur_cherchée;matrice_recherche;type)

Valeur_cherchée : est la valeur utilisée pour trouver la valeur souhaitée dans une matrice.

Matrice_recherche : est une plage de cellules adjacentes contenant les valeurs d'équivalence possibles.
L'argument matrice_recherche peut être une matrice ou une référence matricielle.

Type : est le nombre -1, 0 ou 1. On utilise 0 pour trouver la première valeur exactement équivalente à
celle de l'argument valeur_cherchée.

Exemple : rechercher la position du client « Eurl MSBN » dans la colonne B de la base de données
précédente :

Fonction ESTNA
Cette fonction permet de tester la valeur de la cellule : fait-elle référence à la valeur d'erreur #N/A
(valeur non affichée). Le résultat sera : vrai ou faux.

Exemple : la cellule observation recevra une valeur d’une taxe ; Si la fonction RECHERCHEV ne trouve
pas le paiement par « Chèque/virement », donc c’est un paiement en espèce et alors il y a une taxe de
0.5 % (sinon la taxe est nulle). La fonction peut être:

Support de cours 58
 Excel Perfectionnement

4. Personnaliser le ruban d’Excel

La version 2010 d’Excel permet de personnaliser le ruban et donc permet à l’utilisateur de tracer sa
propre interface du logiciel. Ceci permet de regrouper les commandes riches d’Excel par utilisation. Par
exemple un onglet « Conception du tableau », « outils de synthèse », … chaque onglet peut recevoir des
groupes, dont chacun contiendra les commandes voulues.

La commande « options » de l’onglet « Fichier » :

La commande « personnaliser le ruban » et sur le volet droit on peut créer les onglets et leurs groupes :

Ensuite choisir le groupe et du volet gauche rechercher puis ajouter les commandes désirées, le nouvle
onglet avec ses groupes et commandes peut ressembler par exemple à :

5. La correction automatique
Dans les options Excel, on trouve essentiellement la commande « Options de correction automatique »
du groupe « Vérification »:

Support de cours 59
 Excel Perfectionnement

Elle permet de définir une liste d’erreurs fréquentes commises et de leur associer la correction. On peut
utiliser cette fonctionnalité pour introduire les abréviations des mots (ou phrases) fréquentes, pour que
dorénavant en saisissant l’abréviation, Excel la remplace automatiquement par son équivalent
clairement ajouté au dictionnaire

Exemple : ‘rc' peut désigner ‘Relation Clientèle’ :

VI. Macros sans programmation

1. Automatiser les tâches en créant des macros :
Une macro (macro commande), est un ensemble de tâches successives à réaliser ; ces tâches sont
souvent demandées dans le même ordre (par exemple : créer et tracer un modèle de tableaux). Définir
une macro, consiste à enregistrer toutes ces tâches répétitives dans un petit programme appelé macro.
Cette dernière est exécutée dorénavant par simple raccourci (ex : CTRL + m), et donc toutes ses
opérations élémentaires appropriées s’exécutent instantanément sur le fichier actuel.

Le travail se fait en quatre étapes :

1. Demander la macro :

Support de cours 60
 Excel Perfectionnement

On utilise la commande suivante du ruban « Affichage » :

Dans ce qui suit, on peut nommer la macro et surtout lui associer une touche de raccourci, telle que :
CTRL + m

2. Saisir les opérations élémentaires (réaliser le tableau avec son en-tête, données, fonctions et
formules)

3. Arrêter l’enregistrement : la même commande précédente se transforme en :

4. Utiliser la macro par simple combinaison : CTRL + m dans n’importe quelle autre feuille ou classeur.

Remarques

1. Si le niveau de sécurité est élevé, les macros créées ne pourront pas s’exécuter. Il faut donc
réduire le niveau de sécurité à la prochaine ouverture du classeur en utilisant le bouton
« options » du message affiché au-dessus de la barre de formules :

Support de cours 61
 Excel Perfectionnement

Il faut donc activer les macros contenues dans le classeur :

2. Lors de la création de la macro, si on clique d’abord sur le lien :

Alors elle s’exécutera non pas dans le même emplacement dans la feuille, mais plutôt relativement à
partir de la position actuelle du curseur. Et donc on peut lui faire appel plusieurs fois dans la même
feuille.

2. Ajouter et utiliser des Macros complémentaires

Ce sont des macros déjà faites, reste à les ajouter comme nouvelles fonctions Excel. Dans les options
d’Excel, on s’intéressera au groupe « Compléments » :

Support de cours 62
 Excel Perfectionnement

Le bouton « Atteindre » permet d’afficher la boite de dialogue suivante :

Le bouton « parcourir » permet de rechercher et ajouter le fichier contenant la macro complémentaire.

Exemple : on ajoute la macro complémentaire "Abib" pour convertir un montant en lettres :

Support de cours 63
 Excel Perfectionnement

Elle se rajoute donc à la liste des macros complémentaires :

Et donc on peut l’utiliser dans n’importe quel fichier Excel, comme suit :

Support de cours 64
 Excel Perfectionnement

Où la cellule C3 contient le montant à convertir en lettres. Le résultat sera comme suit :

3. Présenter mieux avec les liens Hypertextes

Créer un lien (accès) vers un autre texte qui suit, représentant les détails du 1 er texte (généralement un
thème). Ce lien peut être :

 Vers un emplacement dans le même ficher

 Vers un autre fichier externe.
Ex : la 1ère feuille contient la liste des états mensuels réalisés (Sommaire) et les autres feuilles
contiennent les états en détails dans des emplacements connus :

Support de cours 65
 Excel Perfectionnement

Utiliser la commande suivante du ruban « Insertion » :

Le travail consiste en la création de liaison (lien) entre chaque titre de la feuille « Sommaire ». Exemple :
créer le lien pour « Janvier » vers son détail existant dans la feuille2 à partir de la cellule A1 :

Et préciser clairement la feuille et la cellule.

Remarque

Le lien peut faire référence à tout autre fichier externe (Excel, Word, Photo, …)

Support de cours 66
 Excel Perfectionnement

Sommaire
I. Saisir des données et des formules de calcul 1
1. Présentation du tableur 2
2. Les règles de conception Excel : 2
2.1. La sélection : ................................................................................................................................ 2
2.2. La saisie de chiffres : ......................................................................................................................... 3
2.3. La saisie de texte : ............................................................................................................................ 3
2.4. La modification : ............................................................................................................................... 4
5.5. Les formules : ................................................................................................................................... 4
2.6. La recopie de formules et la somme automatique : ......................................................................... 5
3. Les fonctions usuelles : Somme, Max, Min, Moyenne, Nombre, Si 6
4. La fonction Si 6
5. Contrôler les données avec la mise en forme conditionnelle 7
6. Créer un tableau modèle 8
7. Lier les tableaux avec des formules ou bien le collage spécial 9

II. Gérer ses documents 10

1. Maitriser la mise en page : 10
2. Imprimer13

III. Générer des graphiques 13

1. Développer des graphiques à partir des données du tableau : 13
2. Les Sparkline 16

IV. Utiliser les outils de synthèse et commandes avancées 17

1. Classer avec la commande tri : 17
1.1. Le tri à un seul niveau : ................................................................................................................... 17
1.2. Le tri à plusieurs niveaux : .............................................................................................................. 18
2. Interroger les tableaux via les filtres de données : 18
2.1. Filtre par classement : .................................................................................................................... 19
2.2. Filtre numérique : ........................................................................................................................... 20
2.3. Filtre textuel ................................................................................................................................... 21

Support de cours 67
 Excel Perfectionnement

2.4. Filtre chronologique ....................................................................................................................... 22

2.5. Filtre avancé ................................................................................................................................... 23
3. Réaliser des synthèses avec les Sous totaux : 24
4. Créer les tableaux et graphiques croisés dynamiques : 26
4.1. Bilan instantané avec les tableaux croisés dynamiques ................................................................. 26
4.2. Les segments (Slicer) ...................................................................................................................... 29
5. La validation de données 31
5.1. Validation d'une colonne à saisir .................................................................................................... 31
5.2. Validation d'une colonne remplie: ................................................................................................. 32
5.3. Liste de choix .................................................................................................................................. 33
6. Supprimer les doublons dans un tableau 34
7. Faire des prévisions avec la commande « Valeur cible » 36
8. Gestion des scénarios 37
9. Protéger le classeur et les feuilles : 39
9.1. Masquer les formules d’une feuille de données :........................................................................... 39
9.2. Protection de la feuille : ................................................................................................................. 39
9.3. Protéger le classeur ........................................................................................................................ 41
10. Revoir la structure du tableau (Convertir les colonnes) : 41
10.1. Utiliser un séparateur ................................................................................................................... 41
10.2. Utiliser une longueur fixe ............................................................................................................. 43
11. Consolider les données 44

V. Fonctions avancées 45
1. Réviser les tableaux 45
1.1. Vérification ..................................................................................................................................... 45
1.2. Les commentaires .......................................................................................................................... 45
1.3. Classeur partagé : ........................................................................................................................... 46
2. Auditer les Formules 47
2.1. Utiliser les espions .......................................................................................................................... 47
2.2. Audit des formules ......................................................................................................................... 49
2.3. Définition de noms ......................................................................................................................... 49
3. La bibliothèque de fonctions 51
3.1. Fonctions de date et heure............................................................................................................. 51

Support de cours 68
 Excel Perfectionnement

3.2. Fonctions de recherche et de référence ......................................................................................... 52

4. Personnaliser le ruban d’Excel 59
5. La correction automatique 59

VI. Macros sans programmation 60

1. Automatiser les tâches en créant des macros : 60
2. Ajouter et utiliser des Macros complémentaires 62
3. Présenter mieux avec les liens Hypertextes 65

Support de cours 69