Support Ms Excel Filière TSDI 09

Introduction : Pour Composer ce cour , c’a ma pris

un effort donc j’espère que
que mon effort ne partira pas sans
rien et que tous mon groupe Techniques Développement
Informatique 09 ou autre groupe étudiant a l’ISTA ou pas ,
pourront bien acquérir des notions pour bien s’adapter a
l’interface EXCEL .

Bonne Chance a tous

Ali Kayouty

Présentation De L'interface D'Excel

Une fois lancé, Excel crée par défaut un "classeur" nommé « classeur1.xls ».

Rappelez vous que les fichiers créés sous Excel sont identifiés par l'extension ".xls" et
sont reconnaissables grâce à l'icone suivante :

Par défaut, un classeur contient trois feuilles (Feuille1, Feuille2 et Feuille3) auxquelles
vous pouvez accéder en bas à gauche de la fenêtre.

L'interface d'Excel contient un ensemble d'éléments standard utilisés dans la plupart

des logiciels Microsoft :

- la barre d'outils,
- la barre de dessin,
- le menu.
Mais aussi des éléments spécifiques à Excel.

L'ensemble de l'interface d'Excel est détaillé dans l'illustration suivante :

Comme vous pouvez le remarquer, l'interface d'Excel est basée sur un grand tableau
représentant la zone de saisie. Ce tableau est constitué d'un ensemble de colonnes et de
lignes qui se croisent pour former des cellules.

Faire Un Tableau Avec Excel

L'élément de base pour le travail sur Excel est le tableau.

Un tableau est un ensemble de lignes et de colonnes qui se rencontrent pour former

des cellules et permettent une (re)présentation synthétique des données.

Une cellule est identifiée par sa colonne est sa ligne d'appartenance. Ainsi la cellule
« D17 » se trouve à l'intersection de la colonne « D » et de la ligne « 17 ».

Pour insérer des données (Texte, nombre, date.) dans une cellule, il suffit de cliquer
dessus avec la souris et de saisir les données grâce au clavier. Alors que vous saisissez ces
éléments, les modifications apparaissent simultanément dans la cellule et dans la zone de
formule située au dessus de la colonne B (entourée en rouge).
D'ailleurs, à chaque fois que vous sélectionnez une cellule, son contenu apparaît dans la
zone de formule vous permettant d'y apporter d'éventuelles modifications.

Une fois que toutes vos données sont saisies, il ne vous restera plus qu'à mettre en forme
le tableau.
 Mettre En Forme Les Cellules
Une fois entrées, les données apparaissent à l'écran sous un formatage (présentation visuelle)
standard peu esthétique. Pour en adapter la présentation à vos goûts et améliorer la lisibilité
des chiffres, commencez, par exemple, par sélectionner, au moyen de la souris, les trois
premières cellules des colonnes B, C et D appelées « en-tête ». Cliquez sur la sélection avec le
bouton droit de la souris et choisissez dans le menu contextuel qui apparaît à l'écran « format
de cellule »

Une fenêtre apparaît alors à l'écran et vous offre une multitude d'options pour mettre en forme
les cellules sélectionnées.

Comme vous pouvez le voir, la fenêtre comporte 6 onglets. Les 5 premiers seront détaillés un
à un dans ce qui suit.

Onglet Nombre :

Ces options permettent de définir le type de données que contient une cellule. Cette
manipulation est généralement inutile car Excel reconnaît automatiquement le type de
données saisies. Toutefois, il arrive que l'on veuille changer un type « Nombre » en type
« Monétaire » ou en « Pourcentage ». Excel offre un large choix s'agissant des types de
données traités qui couvre tous les cas de figure auxquels vous pourriez être confrontés.

A chaque fois que vous sélectionnez un type de données, des options vous seront proposées
comme cela est par exemple le cas du type « monétaire » :
En général et s'agissant d'une donnée de type monétaire, on cherchera à en augmenter la
précision en passant de 2 décimales à 3 ou 4, à changer de monnaie en remplaçant le Franc
par l'Euro par exemple.

Dans notre cas, aucun traitement ne sera effectué sur le type de données.

Onglet Alignement :

Cet onglet vous permet d'agir sur la disposition dans l'espace des données stockées dans une
cellule. Les options les plus importantes sont celles qui permettent de modifier l'alignement
horizontal de votre texte (aligner à droite, à gauche, centrer le texte.), celles qui permettent de
modifier l'alignement Vertical du texte pour le mettre en haut de la cellule par exemple. Enfin,
il peut s'avérer utile de prévoir un retrait pour une bonne mise en forme du texte.
D'autres options plus avancées sont disponibles, libre à vous de les explorer une à une.

Dans notre cas, nous allons choisir de centrer le texte horizontalement.

Onglet Police :

Dans cet onglet vous pourrez définir la police de caractère à utiliser pour les cellules
sélectionnées, son style ainsi que sa taille.

D'autres options comme la couleur et le soulignement sont disponibles. Un aperçu vous

permet de rapidement voir l'effet des modifications que vous venez d'effectuer.
Dans notre exemple, on se contentera de mettre le texte en gras et de valider en cliquant sur le
bouton OK.

Onglet Bordure :

Pour comprendre l'utilisation de cet onglet, sélectionnez l'ensemble des cellules du tableau et
choisissez dans le menu contextuel « format de cellule ».

L'onglet bordure permet de définir les bordures du tableau. Par défaut, les tableaux dans Excel
n'ont pas de bordure, mais il est souvent plus simple de lire un tableau avec bordures d'où
l'intérêt d'en ajouter systématiquement.

En cliquant sur les boutons « contour » et « intérieur » dans l'onglet bordure, vous verrez
s'ajouter des lignes noires à l'aperçu qui se trouve juste un peu plus bas. Vous pouvez modifier
le style des lignes grâce aux options de style (à droite de la fenêtre) et vérifier le résultat en
appuyant à nouveau sur les deux boutons « contour » et « intérieur »:
Validez votre travail en appuyant sur OK.

Onglet Motifs :

Excel offre aussi la possibilité de définir des motifs de remplissage des cellules. Dans cet
onglet vous pourrez ainsi choisir une couleur de remplissage pour certaines cellules, ou une
texture comme le montre l'image suivante :
Dans notre exemple nous ne mettrons aucun motif de remplissage. Quittez donc la fenêtre
sans valider votre travail.

Vous obtiendrez alors un tableau mis en forme comme celui-ci :

Mise en forme automatique

Comme vous avez pu le constater, la procédure précédente peut s'avérer lourde dans certaines
situations. Pour palier ce point, Excel a prévu une fonctionnalité appelée « Mise en forme
automatique » qui permet de faire le travail rapidement et en une seule étape.

Après avoir sélectionné l'ensemble du tableau, allez dans le menu « format » et choisissez
« Mise en forme automatique ». Dans le fenêtre qui s'affiche à l'écran, il suffit de choisir le
modèle qui vous convient (en cliquant dessus) et de valider par OK.

Vous pouvez aussi gagner du temps dans la mise en forme de vos tableaux en utilisant les
raccourcis de la barre d'outils qui permettent un accès plus rapide aux options de mise en
forme les plus utilisées.
 Créer Des Formules Simples
Si Excel possède des fonctions de mise en forme plus que correctes, elles ne sont en aucun cas
la vocation de ce logiciel. En effet, Excel se distingue surtout par ses fonctionnalités de
traitement des données, c'est d'ailleurs pour cela qu'on dit que c'est un Tableur.

La première fonctionnalité offerte est la possibilité de créer des formules de calcul. En effet,
Excel est basée sur une logique qui dit que seules les données de base doivent être fournies
par l'utilisateur, tous les calculs devant être pris en charge par Excel.

Dans notre tableau d'exemple, si vous vous placez sur la cellule C2, vous verrez s'afficher
dans la barre de formule « 2900 » c'est-à-dire le contenu de la cellule ni plus ni moins car
celle-ci est une donnée de base. Par contre, si nous nous plaçons sur la cellule D2 nous voyons
ce qui suit :

« =B2-C2 » est une formule. Une formule est reconnue sur Excel par un signe « = » en début
de la zone de formule. La formule « =B2-C2 » qui se trouve dans la cellule D2 indique à
Excel que pour connaître la valeur à afficher dans la cellule D2, il faut soustraire à la valeur
de la cellule B2, la valeur contenue dans la cellule C2. La valeur affichée dans D2 est donc
une information calculée par le logiciel et non pas introduite par l'utilisateur.

De même on aura les formules suivantes :

Dans D3 : =B3-C3

Dans D4 : =B4-C4

Dans le même esprit, on aura dans la cellule B5 la formule suivante « =B2+B3+B4 » car la
cellule B5 représente la somme des valeurs des trois cellules qui se trouvent dans la même
colonne « B ».

Il est bien évidemment possible d'utiliser d'autres opérateurs arithmétiques notamment :

« + » : Addition ; « - » : Soustraction

« * » : Multiplication ; « / » : Division

Pour vous familiariser par vous-même à l'utilisation des formules, nous vous proposons de télécharger
le fichier ayant servi d'exemple en cliquant sur ce lien. Pensez notamment à changer la valeur d'une
des cellules contenant les données de base et vous verrez que les cellules contenant des formules qui la
prennent en compte changeront de valeur automatiquement.
 Utiliser Les Fonctions
Outre les opérateurs arithmétiques, Excel offre la possibilité d'utiliser des fonctions. Pour
faire simple, une fonction renvoie une valeur sur la base de données d'entrée fournies par
l'utilisateur.

L'expression de la fonction prend généralement la forme suivante :

y = f (x), où « y » est la valeur renvoyée, « f » la fonction et « x » la variable contenant la
donnée d'entrée qui une fois transformée grâce à la fonction « f » donne « y ».

Dans Excel cette notion est reprise de manière identique à l'exception de l'expression écrite.
En effet, la valeur « y » est affichée dans la cellule contenant la formule de la fonction.

A titre d'exemple, considérons le tableau suivant reprenant les notes obtenues par trois
étudiants dans quatre matières :

Et 1 Et 2 Et 3
Mathématiques 17 12 5
Chimie 15 9 11
Français 13 18 12
Informatique 16 17 14
MOYENNE

Il s'agit donc de calculer la moyenne de chaque étudiant, pour cela on peut procéder de deux
manières différentes :

Utilisation des opérateurs

La moyenne est la somme des notes obtenues divisée par le nombre de matières. Ce qui peut
être traduit par les formules suivantes :

Dans B6 : = (B2+B3+B4+B5) / 4

Dans C6 : = (C2+C3+C4+C5) / 4

Dans D6 : = (D2+D3+D4+D5) / 4

Toutefois, cette méthode est fastidieuse et devient peu pratique lorsqu'il s'agit de faire la
moyenne d'une vingtaine de matières par exemple. Pour cela, nous conseillons de lui préférer
la méthode des fonctions.

Utilisation des fonctions

Excel propose une fonction appelée « Moyenne » qui a pour rôle de renvoyer la moyenne des
valeurs contenues dans les cellules entrées comme argument.
Dans notre exemple il suffira d'entrer la formule suivante « =MOYENNE(B2:B5) » dans la
cellule B6 pour obtenir le même résultat que précédemment. L'utilisation des « : » permet de
définir une « plage de cellules » qui s'étant de la cellule B2 à la cellule B5, chose qui est
impossible en utilisant les opérateurs. De plus, la fonction se charge de compter
automatiquement le nombre de matières et vous évite ainsi tout risque d'erreur. Enfin,
l'utilisation des fonctions permet d'avoir des formules plus courtes et donc plus lisibles.

Outre les fonctions de base comme la MOYENNE ou la SOMME, Excel propose une
multitude de fonctions plus élaborées accessibles via le menu Insertion > Fonction.

Pour insérer une fonction dans une cellule, il vous suffit de cliquer sur la cellule et d'aller dans
le menu Insertion > Fonction ce qui affichera la fenêtre suivante :

Cette fenêtre vous propose l'ensemble des fonctions supportées par Excel classées en
catégories accessibles par une liste déroulante. Une fois la catégorie choisie, les fonctions qui
y sont proposées sont affichées dans la liste qui se trouve un peu plus bas.

Cliquez alors sur la fonction souhaitée et vous verrez s'afficher en bas de la liste des fonctions
une brève description de la fonction et de son utilisation. Ceci est particulièrement pratique
quand vous utilisez une fonction pour la première fois.

Reprenons notre exemple précédent et utilisons cet assistant plutôt que la saisie directe de la
fonction. Après avoir cliqué sur la cellule B6 et avoir accédé à la fenêtre d'insertion des
fonctions, choisissez la catégorie « tous » pour afficher toutes les fonctions. Faites dérouler
jusqu'à arriver à la lettre M et choisissez la fonction MOYENNE.

En appuyant sur OK vous voyez s'afficher à l'écran la fenêtre suivante :

Comme vous pouvez le voir, Excel devine automatiquement la plage de cellules la plus
probable pour cette fonction. Toutefois, à des fins pédagogiques, il nous semble préférable de
vous montrer la méthode à suivre car il arrive qu'Excel n'affiche pas les bons arguments.

Appuyez d'abord sur le petit bouton qui se trouve en regard de chaque argument. Cela
affichera la fenêtre suivante :

Ensuite, il vous suffira de sélectionner au moyen de la souris les cellules souhaitées pour voir
se modifier le texte de la fenêtre en même temps. Une fois la bonne plage sélectionnée, il ne
vous reste qu'à enfoncer la touche Entrer du clavier pour valider votre travail. Vous
reviendrez alors à la fenêtre précédente et verrez une simulation du résultat de la fonction, si
vous êtes satisfait, appuyez sur OK et le tour sera joué.

Signalons que l'argument2 est facultatif et sert à introduire une seconde plage de cellules
quand cela est nécessaire.

Vous pouvez maintenant procéder de la même manière pour toutes les autres fonctions
d'Excel, nous traiterons toutefois en détails certaines fonctions dont l'utilisation s'avère
délicate.
 La Fonction "SI"
En théorie

Les structures conditionnelles (ou de choix) sont indéniablement la base de la programmation

informatique et donc aussi la base du fonctionnement des ordinateurs et des logiciels comme
EXCEL.

Exemple :

SI "condition 1 réalisée" ALORS "faire opération 1" SINON "faire opération 2"

La traduction des structures conditionnelles dans la version française d'Excel se fait grâce à la
fonction "SI". Voici ce que vous devez saisir dans la barre de formule pour convertir
l'exemple précédent en fonction compréhensible par EXCLE :

=Si (condition 1 réalisée; faire opération 1; faire opération 2)

Quelques remarques s'imposent:

- les mots "alors" et "Sinon" ne sont pas écrits explicitement mais sous entendus.
- les différentes parties de la structure conditionnelle sont séparées par des points virgules ";"
- l'ordre est TRES important car il remplace les mots, en effet, après le premier on retrouve
toujours l'action à faire ne cas de vérification de la condition et celle à faire sinon se trouve
toujours après le second point virgule.

En pratique

Prenons maintenant un petit exemple pratique. Supposons que vous disposez de la base de
données suivante sur Excel :

Employé Ventes Commission

Jean 700
Bernard 372
Pierre 440
Rachid 801
David 975

Pour motiver vos vendeurs, vous avez décidé de doubler la commission que touche chaque
vendeur pour toutes le ventes qui dépassent les 500 pièces. La vente est payée 7 Euros (une
fois doublée elle sera donc payée 14 Euros).

Vous ne pouvez pas utiliser une simple formule comme nous l'avions fait dans les pages
précédentes de ce cours. La solution consiste en l'utilisation d'une structure conditionnelle qui
permet de traiter différemment les vendeurs ayant réalisé moins de 500 et ceux ayant dépassé
ce chiffre.
Voici ce que vous devez saisir dans la première cellule de la colonne "commission" (cellule
C2) :

=Si (B2<=500;B2*7;500*7+ (B2-500)*14)

Cette formule indique à Excel que :

- dans le cas ou le vendeur a vendu 500 pièces ou moins, il doit calculer la commission en
multipliant le nombre de pièces vendues par 7

- dans le cas contraire, c'est à dire s'il vent plus de 500 pièces, Excel doit trouver la
commission en comptant 7 Euros pour les 500 premières pièces (3500 Euros) et y ajouter 14
Euros par pièce dépassant les 500 ce qui est obtenu en enlevant 500 du total des ventes du
vendeur.

A noter que cette formule n'est pas l'unique bonne formule dans ce cas, plusieurs autres
formules équivalentes peuvent être utilisées :

=Si (B2>500;500*7+ (B2-500)*14;B2*7)

=Si (B2<=500;B2*7;3500+ (B2-500)*14)

=Si (B2<=500;B2*7;(B2*14)-3500)

En définitive, selon le cas de figure, Excel utilisera l'une OU l'autre des formules pour
calculer la commission.

Il ne vous restera plus qu'à tirer la poignée de recopie pour appliquer la formule à tout le
tableau. Voici ce que vous devez obtenir :

Employé Ventes Commission

Jean 700 6300
Bernard 372 2604
Pierre 440 3080
Rachid 801 7714
David 975 10150

ATTENTION : la fonction SI est extrêmement utile est sa maitrise nécessaire.

 La Fonction Recherchev
Pour schématiser, on peut retenir deux cas où l'on a recours à l'utilisation de la fonction
RechercheV :

Structures conditionnelles dynamiques

La fonction recherchev est ici une évolution de la structure conditionnelle classique SI. Elle
vient répondre à deux limitations majeures de cette dernière :

• La fonction SI est limitée à un maximum de 7 SI imbriqués et ne peux donc pas traiter plus
de 8 cas,

• La nécessité de définir les tests en les écrivant dans la formule (en dur), elle n'est donc pas
dynamique.

Prenons le cas suivant : vous êtes chargé(e) d'effectuer un rapport sur l'activité commerciale
de vos vendeurs. On vous fournit le tableau suivant (dans la première feuille du classeur Excel)
que vous devez compléter :

CA
Vendeur Pays HT CA TTC Com
Legrand France 25000
Paoli Italie 49500
Chasseney France 22750
De la garde Italie 18000
Parlois Italie 120000
Legrand Espagne 84300
Paoli Espagne 21200
Aulin France 98700

On vous fourni aussi la grille suivante qui servira de base aux calculs (dans une seconde
feuille du classeur appelée "grille") :

Taux
Pays TVA Com
France 19,60% 5%
Espagne 21% 4%
Italie 15% 3,5%

Quelle formule faut-il insérer dans les cellules D2 et E2 pour calculer le CA TTC et les
commissions des vendeurs ?
Solution 1 : utiliser la fonction SI

Dans D2 :
=SI(B2="France";C2*1,196;SI(B2="Espagne";C2*1,21;SI(B2="Italie";C2*1,15;"?")))

Dans E2 :
=SI(B2="France";C2*0,05;SI(B2="Espagne";C2*0,04;SI(B2="Italie";C2*0,035;"?")))

Cette solution comporte les inconvénients suivants :

• en cas de changement des taux, les formules doivent être éditées manuellement

• s'il y a beaucoup de pays, la formule sera longue et fastidieuse

• à partir de 8 pays, cette méthode est inutilisable

Solution 2 : utiliser la fonction RechercheV

Etape 1 : Définir la source des données :

Sélectionnez la plage de cellules "$A$2:$C$4" dans la feuille nommée « grille » (il ne faut
pas sélectionner les entêtes des colonnes). Ensuite dans le menu "Insertion" allez sur "Nom"
puis "Définir" et donnez un nom à la zone sélectionnée (exemple : Source).

Etape 2 : Définir la fonction RechercheV

Placez vous sur D2 et allez dans Insertion > Fonction puis sélectionnez la fonction
« RechercheV » qui se trouve dans la catégorie Recherche & Matrices.

Apparaîtra alors l'assistant qui vous propose 4 champs à renseigner (les trois premiers sont
obligatoires) :
- Valeur_cherchée : c'est la valeur sur laquelle doit être effectué le test, pour la retrouver
facilement, gardez à l'esprit que c'est en général la seule qui est partagée entre les deux
tableaux elle assure donc le lien entre les données sources et tableau des résultats. Il s'agit
dans notre cas de la cellule B2 (Pays).

- Table_matrice : c'est la matrice ou le tableau source des données. Il doit nécessairement

comporter au moins 2 colonnes de manière à faire correspondre la valeur cherchée (toujours
en première colonne) à une ou plusieurs données (taux de TVA, taux de commission.) dans
les colonnes suivantes. Pour la définir, vous avez le choix entre l'insertion de la référence de
la plage de données (Grille!$A$2:$C$4) ou l'insertion d'un nom préalablement attribué à la
source de données (dans notre cas « Source »).

- No_index_col : c'est le numéro de la colonne dans le tableau source de données qui contient
les valeurs que vous souhaitez obtenir. Mettez 2 pour obtenir le taux de TVA et 3 pour le taux
de commission (le 1 étant toujours réservé à la valeur cherchée).

- Valeur_proche : ici vous décidez si Excel doit trouver la correspondance exacte ou la

correspondance la plus proche. Ceci est utile dans le cas des valeurs numériques. Dans notre
cas on mettra « Faux » pour obtenir uniquement des correspondances exactes.

Voici donc la formule à insérer dans la cellule D2 :

=C2*(1+RECHERCHEV(B2;Source;2;FAUX))

Pour résumer, cela dit à Excel de rechercher la valeur de B2 dans le tableau appelé Source et
de renvoyez la valeur qui lui correspond dans la deuxième colonne de ce tableau. Une fois
cette valeur récupérée, elle sera utilisée pour calculer le CA TTC.

De même on aura dans E2 la formule suivante :

=C2*RECHERCHEV(B2;Source;3)
Comme vous pouvez le deviner, cette fonction offre donc les avantages suivants :

• simplicité, car la formule est plus courte et plus simple à comprendre,

• dynamisme, car il suffit de modifier les données dans le tableau source pour que les
changements soient répercutés automatiquement sans avoir à modifier la formule,

• il n'y a pas de limite de nombre de pays.

Nota : Dans notre cas, on a eu recours à la fonction RechercheV car les données sont
organisées verticalement (les pays sont dans une même colonne et non pas sur une même
ligne). Si les pays avaient été organisés horizontalement, c'est la fonction RechercheH qui
aurait été utilisée.

Cas 2 : Aide à la saisie

Prenons un cas simple : Vous êtes chargé(e) d'organiser un cycle de formation pour les
commerciaux de l'entreprise. Le service Ressources Humaines vous donne la liste des
matricules, noms et prenons des participants, mais le formateur souhaiterait avoir plus
d'informations sur chacun d'eux pour mieux cibler son intervention. Vous disposez pour cela
d'un fichier Excel interne à votre service qui regroupe pour chaque commercial : age, région,
expérience, formation, segment de clientèle.

Comment compléter le tableau fourni par les RH avec les données issues de votre fichier
interne ?

Solution manuelle : pour chaque matricule vous faites une recherche dans le fichier et vous
copiez / collez les données dans le fichier à fournir au formateur. Cette méthode n'est toutefois
pas pratique si vous avez plus d'une vingtaine de participants.

Solution automatisée : vous utilisez la fonction Recherche V qui se charge de remplir

automatiquement et dynamiquement le fichier à votre place en quelques secondes quel que
soit le nombre de participants qu'il contient. Pour ce faire, il suffit de définir le tableau
contenant les données sur tous les commerciaux comme source de données (Table matrice), la
valeur cherchée est qui est le matricule et les numéros des colonnes dont vous souhaitez
obtenir les informations.
 E n plus des tableaux, Excel propose un second moyen de représenter les
données de manière synthétique : les graphiques.
Il existe plusieurs types de graphiques dont l'utilisation dépend des données
sources et de l'objectif de l'analyse.

Les Types De Graphiques

Excel offre un large choix de types de graphiques mais nous nous contenterons de présenter
les trois principales familles de graphiques qui nous semblent les plus à même de couvrir
l'essentiel de vos besoins.

Les courbes :

Les courbes sont utilisées pour étudier l'évolution d'une ou plusieurs variables, par rapport à
une autre. Par exemple, l'on est typiquement amené à rechercher l'évolution d'un ou plusieurs
indicateurs dans le temps.

Voici un exemple d'analyse uni variée (une seule courbe - à gauche) et d'une analyse bi variée
(deux courbes - à droite), une infinité de variables pouvant bien sûr être étudiées grâce aux
courbes :

Les courbes permettent entre autres :

- d'identifier les tendances : y a-t-il une hausse ? une baisse ?

- d'identifier les saisonnalités : y a-t-il des périodes plus propices que d'autres ?

- d'identifier les relations entre variables : y a-t-il une relation (correlation) entre les variables
étudiées ?

Les secteurs :

Les graphiques en secteurs permettent de représenter graphiquement la part de chaque valeur

d'une variable dans le total des valeurs de ladite variable. On étudiera par exemple la part de
chaque appareil dans la consommation électrique d'un foyer.
Le graphique suivant montre que la variable 1 représente plus de la moitier du total, que les
variables 2 et 3 ont des valeurs comparables et que la variable 4 ne représente qu'un tout petit
pourcentage du total :

Les histogrammes :

Les histogrammes sont à mi-chemin entre les courbes et les secteurs. Ils permettent d'étudier
simultanément l'évolution des variables par rapport à une autre, et de comparer les variables
étudiées entre elles.

Dans le graphique suivant, on peut voir l'évolution des trois variables dans le temps mais aussi
comparer les trois variables entre elles à chaque période :

Toutefois, cette polyvalence des histogrammes a pour contrepartie de limiter les

histogrammes à un petit nombre d'observations et de variables au delà desquelles le graphique
devient illisible et sans grande utilité.
 Créer Et Personnaliser Un Graphique Excel
Pour réaliser un graphique, il est bien évidemment indispensable de disposer d'un certain
nombre de données. Prenons donc comme exemple le tableau suivant :

Var 1 Var 2 Var 3

A 100 80
B 150 90
C 115 101
D 138 57

Supposons, dans un premier temps, que nous voulons comparer les variables 2 et 3 sur les
différentes occurrences de la variable 1. En terminologie Excel on dira que l'on souhaite
représenter deux séries (var 2 et var 3).

Pour ce faire, sélectionnez les deux colonnes en question sans sélectionner la première.
Cliquez ensuite sur l'icône de l'assistant graphique dans la barre d'outils ou encore allez dans
le menu Insertion > Graphique... Apparaitra alors l'assistant suivant :

Comme vous le voyez, vous pouvez choisir le type de graphique et sa mise en forme. Nous
choisirons "Histogramme" (comme cela est expliqué dans la page précédente) et un effet 3D.
Des options de mise en forme et des types de graphiques plus élaborés sont aussi accessibles
en cliquant sur le second onglet "types personnalisés".
Cliquez sur le bouton Suivant > et vous verrez un aperçu du graphique que vous allez obtenir.

Vous remarquez que le graphique représente bien nos deux variables (nos séries sont bien en
colonnes, l'option par défaut) mais que l'axe des abscisses ne reprend pas les valeurs de la
variable Var 1. Ceci est dû au fait qu'Excel n'a d'informations que sur les séries et aucune sur
l'étiquette de l'axe des abscisses.

Pour remédier à cela, il suffit d'accéder à plus d'options à travers l'onglet "Série" qui offre la
possibilité de redéfinir les valeurs des séries (Valeurs), leurs étiquettes (Nom) ainsi que les
données à mettre en abscisses comme le montre l'écran suivant :
Nous avons désormais un graphique conforme à nos attentes. Prenez toutefois le temps de
tester différentes alternatives afin de mieux comprendre les différentes options car vous en
aurez besoin si vous envisagez de réaliser des graphiques plus complexes.

Cliquez ensuite sur le bouton Suivant > pour valider cette étape et passer aux options de
graphique. Dans le fenêtre qui s'affiche vous aurez un grand nombre de possibilités de
personnalisation du graphique. Nous vous invitons à les tester une par une car vous en verrez
l'effet en temps réel sur l'aperçu à droite de la fenêtre. Nous vous proposons cette présentation
à titre indicatif (essayez d'obtenir le même résultat cela vous fera un bon exercice) :
Une fois que le résultat est satisfaisant, vous pouvez cliquer sur le bouton Suivant et passer à
la dernière étape de l'assistant de création des graphiques.

On vous proposera de choisir entre deux possibilités :

• insérer le graphique dans une nouvelle feuille dédiée à cet effet

• insérer la graphique dans une feuille du classeur (option par défaut)

Cliquez enfin sur Terminer et votre graphique sera inséré dans la feuille.
 Trier Une Base De Données
Quel que soit le cas de figure, des données triées sont toujours plus faciles à exploiter et à
interpréter, d'où l'importance de la fonction de tri proposée par Excel.

Avant de commencer, vous pouvez télécharger la base données Excel utilisée dans la suite du
cours ici

Après avoir sélectionné une cellule de la plage contenant la liste des données (A1 par
exemple), allez dans le menu "Données" et choisissez "Trier". Si Excel ne parvient pas à
localiser la liste de données un message d'erreur s'affichera, sinon la boite de dialogue
suivante vous sera proposée :

Elle vous offre la possibilité de choisir jusqu'à trois critères de tri ainsi que l'ordre de tri :
croissant (du plus petit au plus grand) ou décroissant (du plus grand au plus petit).

Voici par exemple le résultat d'un tri simple par mois croissant :

Année Mois Ventes Vendeur Région

2002Aoû 1647jean Est
2003Aoû 1441Laurent Est
2003Aoû 4873jean Nord
2003Aoû 8447jean Est
2002Avr 450Laurent Est
2003Avr 9136jean Est
2002Déc 2733Laurent Ouest
2002Déc 9614Laurent Est
2002Déc 2211Laurent Ouest
2002Déc 7047dacosta Ouest
2003Déc 7686dacosta Nord
2003Déc 479jean Ouest
 2002Fév 4923Laurent Sud
2002Fév 8076Laurent Sud

On remarque que les données relatives à un mois sont regroupées jusqu'à expiration de ce
dernier, on passe ensuite au mois suivant.

Et voici par exemple le résultat d'un tri double par année puis par vendeur :

Année Mois Ventes Vendeur Région

2003Août 1441Laurent Est
2003Jan 2686Laurent Ouest
2003Jan 9662Laurent Nord
2003Jun 5575Laurent Est
2003Jun 1361Laurent Sud
2003Mai 9566Laurent Sud
2003Nov 5720Laurent Est
2003Oct 7191Laurent Nord
2003Sep 3947Laurent Sud
2003Sep 66Laurent Nord
2003Sep 8859Laurent Ouest
2003Aoû 4873jean Nord
2003Aoû 8447jean Est
2003Avr 9136jean Est
2003Déc 479jean Ouest
2003Fév 5010jean Nord
2003Fév 2666jean Ouest
2003Jan 5594jean Nord
2003Jan 29jean Est
2003Jul 797jean Nord
2003Jul 7612jean Sud
2003Mar 7113jean Ouest
2003Oct 8165jean Sud
2003Oct 6028jean Est
2003Déc 7686dacosta Nord
2003Fév 3106dacosta Sud
2003Jul 9082dacosta Sud
2003Oct 3338dacosta Ouest
2002Avr 450Laurent Est
2002Déc 2733Laurent Ouest

On notera ici que l'ordre des critères de tri (quand il y en a plus d'un) est important car le
premier est le critère principal et le second n'est qu'une organisation des données au sein d'une
occurrence du premier critère (le même vendeur apparait en 2003 puis en 2002) alors que les
années apparaissent en totalité avant de passer à l'année suivante. En inversant l'ordre des
critères, on obtiendra la liste suivante :

Année Mois Ventes Vendeur Région

2003Août 1441Laurent Est
2003Jan 2686Laurent Ouest
2003Jan 9662Laurent Nord
 2003Jun 5575Laurent Est
2003Jun 1361Laurent Sud
2003Mai 9566Laurent Sud
2003Nov 5720Laurent Est
2003Oct 7191Laurent Nord
2003Sep 3947Laurent Sud
2003Sep 66Laurent Nord
2003Sep 8859Laurent Ouest
2002Avr 450Laurent Est
2002Déc 2733Laurent Ouest
2003Aoû 4873jean Nord
2003Aoû 8447jean Est
2003Avr 9136jean Est
2003Déc 479jean Ouest
2003Fév 5010jean Nord
2003Fév 2666jean Ouest

Vous remarquerez que l'année varie alors que le vendeur "Laurent" apparait en totalité.

N'hésitez pas à faire plusieurs simulations pour mieux comprendre ce mécanisme.

Une fois que les données sont triées, nous pouvons effectuer des traitements complémentaires.

Filtrer Une Base De Données

Contrairement au tri que nous avons présenté à la page précédente, l'application d'un filtre à
une base de données (une liste) Excel ne nous retourne qu'une partie de celle-ci et non la
totalité de la liste (réorganisée) comme cela est le cas pour les tris.

Les filtres ont donc pour rôle de vous permettre d'étudier une partie (ou sous ensemble) des
données.

Supposons par exemple qu'on vous demande de remettre un rapport détaillé sur la région du
Nord, il n'y a aucun intérêt à s'encombrer des données concernant les autres régions. En
appliquant un filtre sur la région, vous pouvez limiter les données affichées à celles relatives à
la région du Nord qui vous intéresse. Voici comment vous pouvez procéder :

Après avoir sélectionné une cellule de la plage contenant la liste des données (A1 par
exemple), allez dans le menu "Données" et choisissez "Filtrer..." puis "Filtre Automatique". Si
Excel ne parvient pas à localiser la liste de données un message d'erreur s'affichera, sinon
vous verrez apparaitre tout en haut de la liste et pour chaque colonne des petits rectangles
avec une flèche au centre comme sur le graphique suivant :
En cliquant sur ce petit rectangle, vous ferez dérouler la liste des options applicables à cette
colonne :

1. Tri croissant / décroissant : équivaut à la technique des tris étudiée dans la page
précédente
2. (tous) : pour afficher toutes les données sans aucun tri, cette option est souvent utilisée
pour revenir à la liste initiale complète après avoir effectué des filtrages
3. (10 premiers) : pour ne faire apparaitre que les 10 premières valeurs de la colonne
(option rarement utile)
4. (personnalisé...) : vous donne accès à des options de tri plus avancées et
personnalisables
5. Enfin, et c'est le plus important, il est possible de sélectionner une des valeurs de la
colonne, ici "Nord", "Sud", "Est" et "Ouest". En sélectionnant "Nord" par exemple,
vous limiterez l'affichage des données aux lignes relatives à la région "Nord".

Il est aussi possible de faire des filtres croisés en appliquant simultanément deux filtres à une
même liste : Filtrer la région sur "Sud" et filtrer le vendeur sur "Jean", vous obtiendrez alors la
liste des ventes effectuées par le vendeur Jean dans la région du Sud.

Pour tout annuler et revenir à la liste initiale, il vous suffit de sélectionner (tous) dans toutes
les colonnes ayant subi un filtrage.

Sous Totaux
Après avoir appliqué des tris et/ou des filtres sur une base de données (voir pages
précédentes), il n'est pas rare de vouloir dégager des données chiffrées sur celle-ci (total,
moyenne, variance, nombre...). Pour ce faire, Excel offre plusieurs solutions dont :

• l'utilisation des formules et fonctions (pour les tableaux simples)

• l'utilisation des sous totaux (qui est privilégiée dans le cas des bases de données)

Reprenons notre base de données sur les ventes et supposons que l'on veuille faire un bilan
annuel des ventes (combien avons-nous vendu pour chacune des années ?).

Mettre en forme les données :

Avant de pouvoir utiliser les sous-totaux, Excel nécessite une mise en forme des données.

Etant donné que l'année est le critère retenu pour faire le bilan (et non le vendeur ou la région),
il est indispensable de commencer par trier les données par année (croissant ou decroissant).

Réaliser des statistiques :

Maintenant que les données sont triées, il ne reste plus qu'à ajouter des sous totaux. Pour cela,
voici la procédure à suivre :

Sélectionnez une cellule de la plage de données

Dans le menu "Données", choisissez "Sous totaux " pour faire apparaitre la fenêtre suivante :
Comme vous pouvez le constater, il y a 3 options importantes à renseigner :

• "Ajouter un sous-total à" : ici il s'agit de sélectionner la ou les colonnes contenant les
données objet du traitement statistique (dans notre exemple ce sont les ventes)
• "Utiliser la fonction" : c'est la fonction (traitement) à appliquer aux données de la
colonne sélectionnée (somme, moyenne, variance...)
• "A chaque changement de" : est le critère qui détermine l'application de la fonction sur
les données. Dans notre exemple, Excel doit faire la somme des ventes à chaque fois
que la valeur de l'année change pour obtenir des totaux annuels. Vous comprenez aussi
pourquoi il est indispensable de trier les données avant d'appliquer les sous totaux car
sans cela, Excel ferait des sous totaux plusieurs fois pour chaque année (faites un essai
pour voir le résultat).

En cliquant sur OK, Excel ajoute des sous totaux à la fin de l'année 2002 et 2003 et un total
global. Vous pouvez aussi remarquer qu'à gauche de la feuille de calcul, Excel a ajouté des
petits "-". Cliquez sur ces petits boutons et vous verrez disparaitre le détail des données pour
ne garder que les totaux. On obtient le résultat synthètique suivant :

Pour retrouver la totalité des données, il suffira d'utiliser les petits boutons "+".

Les sous totaux sont donc une fonction puissante offerte par Excel, toutefois leur maitrise est
délicate et nécessite une certaine pratique surtout que dans la réalité les traitements et les
critères utilisés sont bien plus complexes que dans cet exemple pédagogique.
De plus, je tiens à mettre l'accent sur le fait que l'appellation "sous totaux" peut prêter à
confusion car cet outil ne se limite pas aux totaux mais offre aussi des traitements statistiques
plus avancés comme nous avons pu le voir.

Pour vous exercer, vous pouvez appliquer des sous totaux par vendeur, région ou mois. Vous
pouvez aussi faire des moyennes par vendeur, année, mois et région.

Tableaux Croises Dynamiques

Les tableaux croisés dynamiques représentent l'une des fonctionnalités les plus
puissantes mais aussi les plus délicates à maitriser d'Excel. Une fois maitrisés, ils
vous permettront de réaliser des gains considérables aussi bien en productivité qu'en
qualité de travail.

Présentation générale
Un tableau est fait de lignes et de colonnes, il permet donc typiquement de représenter de
manière synthétiques un maximum de trois variables : V1 en lignes, V2 en colonnes et V3
dans les cases. Cette limitation est contournée artificiellement en ajoutant des lignes ou des
colonnes supplémentaires car en pratique, il n'est pas rare d'avoir plus de 3 variables à
analyser.

Les tableaux croisés dynamiques, sans réellement résoudre le problème car ils sont aussi des
tableaux, permettent de rendre cette procédure de contournement plus simple et plus puissante
à utiliser.

Création d'un tableau croise dynamique

Pour illustrer notre propos, nous allons reprendre notre base de données exemple qui a servi à
la présentation des autres fonctions de traitement des données (tris, filtres et sous totaux) et
l'exploiter en utilisant un tableau croisé dynamique.

Ouvrez ce fichier et sélectionnez aléatoirement une cellule de la liste de données. Dans le

menu "Données", choisissez "Rapport de tableau croisé dynamique". L'assistant de création
des tableaux croisés dynamiques apparaitre alors à l'écran pour vous aider.

La première étape, consiste à choisir la source des données et le type de rapport. Pour la
source des données, nous allons choisir la première car nous disposons d'une liste de données
Excel. Pour la seconde option, on optera pour un tableau et non le graphique :
Cliquez sur suivant pour faire apparaitre le second écran.

Ce dernier vous offre la possibilité de définir la plage de données à utiliser. En général, ayant
cliqué sur une des cellules de la liste avant de lancer l'assistant, Excel sera en mesure de
détecter les données automatiquement. Assurez-vous donc que tout est bon et cliquez sur
suivant pour passer à la dernière étage.

Ici vous avez le choix entre la création d'une nouvelle feuille dédiée au tableau croisé
dynamique et l'ajout dans la feuille actuelle. Nous vous recommandons de choisir la première
option comme sur l'image et de cliquer sur Terminer.
Apparaitre alors une nouvelle feuille avec un tableau vierge, une barre d'outils et une fenêtre
qui vous permettront d'utiliser le tableau croisé dynamique comme vous le souhaitez.

ASTUCE : Pour gagner encore plus de temps, vous n'avez pas besoin de passer par les trois
étapes précédentes car nous utilisons les options par défaut, vous pouvez donc cliquez
directement sur Terminer dans la toute première fenêtre et obtenir le même résultat.

Utilisation d'un tableau croisé dynamique

Pour utiliser un tableau croisé dynamique, il suffit d'ajouter des champs de page, de colonne,
de ligne et des champs de données à analyser.

Pour ce faire, il suffit de faire un "glisser - déplacer" depuis la fenêtre "Liste de champs de
tableau croisé dynamique" vers chacune des zones du tableau comme cela est présenté dans
l'animation suivante :

http://www.esnips.com/doc/be1a9fe0-998d-4560-ac72-5ee6ad7174b3/clip0002

Nous disposons maintenant d'un tableau représentant les ventes de chaque vendeur par mois
pour l'ensemble des années, ce qui n'est pas très informatif. Il faut donc réaliser quelques
traitements supplémentaires à savoir :

• choisir l'année souhaitée.

• choisir la fonction statistique à appliquer aux données.

En effet, on peut souhaiter ne voir que les données de l'année 2002 et se limiter aux vendeurs
Laurent et Jean, pour cela il suffit de faire comme dans l'animation suivante :

http://www.esnips.com/doc/f1b461bb-6d4c-4b3e-8b11-d4e9b530101b/clip3

Maintenant essayons d'aller un peu plus loin en essayant de définir pour chacune des années,
les moyennes des ventes mensuelles de l'ensemble des vendeurs !

Pour ce faire, il faut modifier l'ensemble du tableau et faire quelques manipulations

supplémentaires comme dans l'animation suivante :

http://www.esnips.com/doc/865dc5c5-039d-4d2b-9ece-172dbbebe77d/clip0004

Comme vous pouvez le remarquer, l'option "Paramètres de champs..." dans le menu

contextuel permet d'appliquer une fonction aux données étudiées (les ventes). On peut donc
faire la somme, compter le nombre, faire la moyenne, obtenir la valeur Max ou Min... et ainsi
de suite.

Voilà, l'essentiel a été dit concernant les tableaux croisés dynamiques. Toutefois, je vous
recommande vivement de vous exercer plusieurs fois car cet outil extraordinairement puissant
et utile est loin d'être simple à manipuler et à comprendre.