Support de cours Informatique S4

Management

Préparé par : H.Mounadel

Ce document est complété par des travaux pratiques

FACULTÉ DES SCIENCES JURIDIQUES,

ÉCONOMIQUES ET SOCIALES SALÉ
Route Outa Hssain, BP 5295 SALA ALJADIDA
 Microsoft Excel

TABLE DES MATIERES

Introduction .......................................................................................... 2
I- LES FORMULES ............................................................................ 2
1. Définition d’une formule ............................................................................................................................ 2
2. Syntaxe d’une formule ............................................................................................................................... 2
3. Les erreurs de calcul ................................................................................................................................... 3

II- Les fonctions avancées ................................................................... 3

1. Formules conditionnelles ........................................................................................................................... 3
Fonction SI ............................................................................................................................................. 3
=ET() et =OU() .................................................................................................................................... 3
2. Les fonctions d'arrondi ............................................................................................................................... 4
3. Fonctions Statistiques ................................................................................................................................. 5
4. Fonctions s'appliquant à du texte ............................................................................................................... 5
5. Fonctions dates et heures............................................................................................................................ 6

III- Valeur cible ..................................................................................... 7

IV- Les graphiques élaborés ............................................................... 8
1. Ajouter un second axe à un graphique Excel ............................................................................................. 8
2. Créer une pyramide des âges ou une pyramide démographique .............................................................. 10

V- Le mode plan ................................................................................. 15

Grouper.................................................................................................................................................. 16
Dissocier ................................................................................................................................................ 16
Sous-total ............................................................................................................................................. 16
Afficher les détails ............................................................................................................................. 16

VI- Les tableaux croisés dynamiques .............................................. 17

Etape 1: Sélection de la plage de données ............................................................................. 17
Etape 2: construction du TCD....................................................................................................... 17

VII- Gestionnaire de scénarios ......................................................... 19

VIII- Le solveur .................................................................................. 20
Page 1 sur 22
 Microsoft Excel

Introduction
Excel fait partie des logiciels appelés : tableurs. Il permet de réaliser facilement des
calculs sur un nombre important de données et faciliter l’analyse et l’interprétation.
Bref, Excel constitue un outil d’aide à la prise de décision.
La version2007d’Excel marque une rupture avec les versions précédentes en ce qui
concerne l’ergonomie. L’interface, qui reposait sur des menus et des barres d’outils, a
été remplacée par un Ruban constitué d’onglets organisés en fonction de l’action à
accomplir.

I- LES FORMULES
1. Définition d’une formule

2. Syntaxe d’une formule

Page 2 sur 22
 Microsoft Excel

3. Les erreurs de calcul

 ##### : la largeur de la colonne insuffisante
 #DIV/0 : Diviser une valeur par la valeur d’une
cellule vide (ou par 0) !
 #NOM : Identificateur inconnu
 #NOMBRE : dépassement de capacité ou argument non numérique
 #VALEUR : lorsqu'un type d'argument ou d'opérande inapproprié est
utilisé.

II- Les fonctions avancées

1. Formules conditionnelles

Fonction SI

Renvoie une valeur si la condition que vous spécifiez est VRAI et une autre valeur si
cette valeur est FAUX.

Utilisez la fonction SI pour effectuer un test conditionnel sur des valeurs et des
formules.

Syntaxe

SI(test logique;valeur_si_vrai;valeur_si_faux)

test_logique représente toute valeur ou expression qui peut prendre la valeur VRAI
ou FAUX. Cet argument peut utiliser n'importe quel opérateur de calcul par
comparaison.

valeur_si_vrai est la valeur qui est renvoyée si le test logique est VRAI.

valeur_si_faux est la valeur qui est renvoyée si le test logique est FAUX.

Il est possible d'imbriquer des fonctions SI comme arguments valeur_si_vrai et

valeur_si_faux pour élaborer des tests plus complexes.

=SI(Test1;Val_Si_Vrai;SI(Test2;Val_Si_Vrai;SI(Test3;Val_Si_Vrai; …)..))

=ET() et =OU()
La fonction =Si() peut être améliorée lorsqu'elle est utilisée avec d'autres fonctions
telles que =ET() et =OU(). La partie qui suit démontre le fonctionnement de ces deux
fonctions suivi de comment elles peuvent être utilisé avec =Si().

Page 3 sur 22
 Microsoft Excel

=ET(cond1;cond2;cond3 ...) Il faut au moins deux conditions. Mais vous pouvez en

ajouter d'autres. Toutes les conditions doivent être
remplies pour que la fonction affiche VRAI. Sinon, elle
affichera FAUX.

=OU(cond1;cond2; cond3...) Il faut au moins deux conditions. Vous pouvez en

ajouter d'autres. L'une des conditions doit être remplie
pour que la fonction affiche VRAI. Si aucune des
conditions n?est remplie, elle affichera FAUX.

2. Les fonctions d'arrondi

Les fonctions d’arrondis permettent d’arrondir un nombre ou un calcul selon des
modalités diverses :
=ENT(nombre)
Donne la partie entière du nombre

Exemple d’utilisation
=ENT(10/4) donne comme valeur 2

=ARRONDI(Nombre; nombre de décimales)

Arrondit le nombre à la valeur la plus proche en fonction du nombre de décimales
spécifié. Le nombre de décimales peut être négatif

Exemple d’utilisation
=ARRONDI(10,54;1) donne 10,5
=ARRONDI(10,55;1) donne 10,6
=ARRONDI(124,2;-1) donne 120
=ARRONDI(2551;-2) donne 2600

=ARRONDI.INF(Nombre; nombre de décimales)

Effectue un arrondi par défaut, on peut spécifier également un nombre de décimales
négatif

Exemple d’utilisation
=ARRONDI.INF(124,769;2) donne 124,76
=ARRONDI.INF(25987;-3) donne 25000

=ARRONDI.SUP(Nombre; nombre de décimales)

Effectue un arrondi par excès, on peut spécifier également un nombre de décimales
négatif

Exemple d’utilisation
=ARRONDI.SUP(124,12;0) donne 125
=ARRONDI.SUP(1251;-1) donne 1260

Page 4 sur 22
 Microsoft Excel

ATTENTION : L’usage de la commande Format de Cellule – Nombre n’agit que sur

l’affichage. Excel conserve en mémoire tous les chiffres figurant derrière la virgule
pour les calculs, cela peut conduire à des erreurs de centimes. L’usage de la fonction
ARRONDI évite ce désagrément.

3. Fonctions Statistiques
• Comptage
NB(Plage): Nombre de cellules contenant des nombres
NBVAL(Plage): Nombre de cellules non vides
NB.VIDE(Plage) : Nombre de cellules vides
NB.SI(plage;Critère) Nombre de cellules répondant à un critère
NB.SI.ENS(plage1;Critère1;Plage2;Critère2;…): Nombre de cellules répondant
à un ensemble de critères
4. Fonctions s'appliquant à du texte

Excel offre aussi des fonctions pour chercher de l'information sur du texte. En voici
quelques-unes.

=droite(texte;nombre de caractères)

Cette fonction va afficher les derniers caractères d'une cellule ayant du texte.

=gauche(texte;nombre de caractères)

Affiche le nombre de caractères que vous avez choisi du début du texte.

=concatener(première cellule;seconde cellule)

Cette fonction vous permet de regrouper le contenu de plusieurs cellules ensemble

même si le contenu des cellules est différent comme du texte, des chiffres, le résultat
d'une formule ou des dates.

=nbcar(cellule ou texte)

Affiche le nombre de caractères contenu dans une cellule.

=romain(chiffre)

Convertis un chiffre sous forme arabe en chiffre romain. Ex.: =romain(45) = XLV

=texte(chiffre;format texte)

Convertis un chiffre en format texte. Il prend la présentation comme le reste des

cellules ayant du texte. Ex.: =texte(45;0) = 45 mais collé à la bordure gauche de la
cellule.

Page 5 sur 22
 Microsoft Excel

5. Fonctions dates et heures

La fonction =AUJOURDHUI()
Cette fonction permet d'afficher automatiquement la date du système
La fonction =JOUR()
Cette fonction vous permet d'extraire le numéro du jour du mois à partir d'une date
saisie dans une cellule (ou dans la formule elle même). Le numéro extrait peut ainsi
être utilisé pour effectuer des calculs ou des test dans le cadre d'une fonction logique.
La fonction =MOIS()
Cette fonction vous permet d'extraire le numéro du mois à partir d'une date saisie
dans une cellule (ou dans la formule elle même). Le numéro extrait peut ainsi être
utilisé pour effectuer des calculs ou des test dans le cadre d'une fonction logique.
La fonction =ANNEE()
Cette fonction vous permet d'extraire le numéro de l'année à partir d'une date saisie
dans une cellule (ou dans la formule elle même). Le numéro extrait peut ainsi être
utilisé pour effectuer des calculs ou des test dans le cadre d'une fonction logique.
La fonction =DATE()
Cette fonction vous permet de calculer une date à partir d'une autre Vous pouvez
également utiliser cette fonction en combinaison avec une autre formule de calcul sur
les dates.
Voici sa syntaxe :=DATE(annee;mois;jour)
La fonction =JOURSEM()
Cette fonction vous permet de connaitre le numéro du jour de la semaine à partir
d'une date saisie dans une cellule (ou dans la formule elle même). Le numéro extrait
peut ainsi être utilisé pour effectuer des calculs ou des test dans le cadre d'une
fonction logique.
=JOURSEM(Numéro_de_serie;type_de_retour)
Numéro_de_serie est la date de référence
type_de_retour) est la codification des jours :
 code 1 : dimanche = 1 et samedi = 7
 code 2 : lundi = 1 et dimanche = 7
 code 3 : lundi = 0 et dimanche = 1
La fonction =NO.SEMAINE()
Cette fonction vous permet de connaitre le numéro de la semaine à partir d'une date
saisie dans une cellule (ou dans la formule elle même). Le numéro extrait peut ainsi
être utilisé pour effectuer des calculs ou des test dans le cadre d'une fonction logique.
=NO.SEMAINE(Numéro_de_serie;méthode)
Numéro_de_serie est la date de référence
méthode est la codification des jours :
 code 1 : dimanche est le premier jour de la semaine
 code 2 : lundi est le premier jour de la semaine
La formule =SERIE.JOUR.OUVRE()
Cette formule vous permet d'obtenir une date de fin en tenant compte de la date de
départ, du nombre de jours ouvrés qui devront s'écouler, et des jours fériés compris
entre ces 2 dates.

Page 6 sur 22
 Microsoft Excel

=SERIE.JOUR.OUVRE(date_départ;nb_jours;jours_fériés)
Les dates de départ et le nombre de jours à ajouter peuvent être saisies dans la
formule ou faire référence à des cellules où elles ont été saisies.
La formule =FIN.MOIS()
Cette formule vous permet d'obtenir le dernier jour d'un mois à partir d'une date. Utile
pour calculer une échéance à 30 jours fin de mois par exemple.
=FIN.MOIS(date_départ;Mois)
La formule = JOURS360()
Cette formule calcule le nombre de jours écoulés entre 2 dates sur la base d’une
année de 360 jours (12 mois de 30 jours)
=JOURS360(date_début ;date_fin ;méthode)
Méthode est la méthode de comptage retenue :
0 (zéro) ou omis, il s’agit de la méthode de comptage américaine (US (NASD)). Si la
date de début est le 31 du mois, la date de début devient le 30 du même mois. Si la
date de fin est le 31 du mois et que la date de début est avant le 30 du mois, la date
de fin devient le 1er du mois suivant ; sinon, la date de fin devient le 30 du même
mois.
1, il s’agit de la méthode européenne. Les dates de début ou de fin correspondant au
31 du mois deviennent le 30 du même mois.

III- Valeur cible

L'outil valeur cible permet de rechercher un résultat spécifique pour une cellule en
ajustant la valeur d'une autre cellule.
Exemple : calcul de TVA
Commenttrouverlavaleurd’unmontanthorstaxeavecunmontanttoutestaxes.

Seplacersurlacellule résultanted’uncalcul,puis,
OngletDonnées,Analysedescénarios,Valeur cible…

Indiquer la valeur à atteindre, puis, cellule à modifier et valider.

Page 7 sur 22
 Microsoft Excel

IV- Les graphiques élaborés

1. Ajouter un second axe à un graphique Excel

Cela est avantageux lorsqu'il y a une grande différence entre les valeurs des séries de
données. Utilisez un second axe est intéressant pour faire ressortir une série de
données.

Entrez les données supplémentaires pour le graphique.

Sélectionnez la nouvelle série de données (A5 à D5).
Appuyez sur les touches CTRL et C.
Cliquez sur le graphique.
Appuyez sur les touches CTRL et V.

Il est difficile de voir les valeurs 7%, 8%, 9% comparés à 30, 35 et 40.

Page 8 sur 22
 Microsoft Excel

De l’onglet Disposition ou Mise en forme, sélectionnez la série Parts du marché.

Appuyez sur le bouton Mise en forme de la sélection.
Activez l’option Axe secondaire.
Appuyez sur le bouton Fermer.

Un second axe de données

apparaît maintenant à la droite du
graphique. Il affiche des valeurs
en pourcentage ce qui est requis
pour représenter les parts de
marché. Cependant, cette
dernière masque les autres séries
de données. Il faut changer sa
présentation.

Page 9 sur 22
 Microsoft Excel

Sélectionnez la série de données

Part du marché.
De l’onglet Création, appuyez
sur le bouton Modifier le type de
graphique.
De la liste des types de
graphiques, sélectionnez le type
Courbes avec marques.
Appuyez sur le bouton OK.

La dernière série de données ne

masque plus les autres. Vous
pouvez décider en tout temps de
changer vers quel axe une série
de données est représentée
(principal ou secondaire).
Cependant, mentionnez à votre
public que telle série est
représentée par le second axe.
Peu de personnes ont vu des
graphiques avec deux axes.

2. Créer une pyramide des âges ou une pyramide démographique

Elle représente des proportions de la population selon des segments d’âge et de sexe.

Entrez les informations suivantes dans les cellules appropriées.

L’une des séries de données doit avoir des valeurs négatives. Elle sera sur la partie
gauche du graphique.

Page 10 sur 22
 Microsoft Excel

De l’onglet Insertion, sélectionnez les options Barres et Barres groupés.

Les données ne seront pas bien affichées à ce point. Il faut réaliser encore quelques
opérations pour que le graphique se rapproche d’un graphique lisible pour tous.

Sélectionnez l’une des séries de données.

De l’onglet Disposition ou Mise en forme, appuyez sur le bouton Mise en forme de la
sélection.
De la catégorie Options des séries, activez l’option Tracer la série avec Axe secondaire.
Appuyez sur le bouton Fermer.

Cette dernière opération est nécessaire pour aligner les données. En ce moment, les
données à la droite du graphique (homme) utilisent l’axe du haut. Les données des
dames utilisent l’axe du bas pour les représenter. La prochaine étape consiste à
regrouper toutes ces valeurs sous un même axe.

Page 11 sur 22
 Microsoft Excel

Sélectionnez le second axe au-dessus du graphique.

Appuyez sur la touche Supprimer.

Les données sont maintenant affichées selon un axe. Il faut cependant déplacer l’axe
des ordonnées vers la gauche du graphique.

Sélectionnez l’axe vertical (0-15, 16-30 …)

De l’onglet Disposition ou Mise en forme, appuyez sur le bouton Mise en
Page 12 sur 22
 Microsoft Excel

forme de la sélection.
Sélectionnez la catégorie Options Axe.
Pour l’option Étiquette des axes, sélectionnez Bas.

Le graphique s’approche de sa version finale. Il faut maintenant retirer

l’espacement entre les barres.

Sélectionnez l’une des séries de données.

De l’onglet Disposition ou Mise en forme, appuyez sur le bouton Mise en forme
de la sélection.
Sélectionnez la catégorie Options des séries.
Changez l’option Largeur de l’intervalle à 0%.
Appuyez sur le bouton Fermer.
Répétez ces dernières opérations à l’autre série de données.

Page 13 sur 22
 Microsoft Excel

Sélectionnez l’une des séries de données.

Sélectionnez l’onglet Mise en forme.

Appuyez sur le bouton Contour de forme.

Sélectionnez l’option Automatique.

Répétez ces opérations à l’autre série de données.

Page 14 sur 22
 Microsoft Excel

V- Le mode plan

Le mode plan permet de regrouper des données ensemble et d'analyser

les résultats.

Placez le pointeur dans la liste de données.

Si nécessaire, trier les champs (colonnes) sur les valeurs appropriées avant d'utiliser
sous-total.
Appuyez sur le bouton Sous-total.

Sélectionnez le champ sur lequel vous

désirez regrouper vos données et avoir un
sous-total.

Vous pouvez demander plusieurs types de

sous-total selon l'opération que vous désirez:
Somme, Nombre, Moyenne, Min, Max,
Produit, Chiffres, Écart Type, Écart type
population, Variance, Variance de la
population.

Vous pouvez aussi choisir à quel champ

(colonne) vous désirez avoir un sous-total.

Une fois que vous avez choisi toutes les

options, appuyez sur le bouton OK.

Page 15 sur 22
 Microsoft Excel

Grouper

Dissocier

Sous-total

Afficher les détails

Page 16 sur 22
 Microsoft Excel

VI- Les tableaux croisés dynamiques

Le Tableau Croisé Dynamique (TCD) est un outil très puissant permettant de
synthétiser et analyser une base de données très rapidement.

Etape 1: Sélection de la plage de données

Sélectionnez une cellule quelconque de la base de données, puis cliquez sur l'onglet
"Insertion"; dans le groupe "Tableaux" à gauche choisissez "Tableau croisé
dynamique":

La fenêtre suivante apparaît, Excel a déterminé tout seul la plage de données en

sélectionnant l'ensemble de notre base et propose par défaut de placer le TCD dans
une nouvelle feuille qui sera créée pour l'occasion:

Etape 2: construction du TCD

Vous êtes maintenant sur le nouvel onglet qu'Excel vient de créer, on remarque à
gauche le TCD vide, tel qu'il apparaît avant que les champs soient définis; à droite la
liste des champs, en haut deux onglets Options et Création offrant de nombreux outils
spécifiques aux TCD:

Page 17 sur 22
 Microsoft Excel

C'est à partir de la fenêtre Liste des champs que nous allons construire le TCD, en
sélectionnant et déplaçant les champs qui nous intéressent.

Page 18 sur 22
 Microsoft Excel

VII- Gestionnaire de scénarios

Le gestionnaire de scénarios vous permet de comparer rapidement plusieurs
hypothèses, ou scénarios pour utiliser le terme d'Excel, et de générer un tableau de
synthèse avec les résultats.
Allez à l’onglet Données.
Sous le bouton Analyse de scénarios,
sélectionnez l’option Gestionnaire de
scénarios.

De la fenêtre du gestionnaire de scénarios, appuyez sur le bouton Ajouter.

Les premières étapes de création d'un scénario consiste à donner un nom au scénario
et de déterminer quelles seront les cellules variables. Ce sont les cellules dont vous
désirez changer les valeurs. Pour le premier scénario, il s'agit des cellules B21 et B5.

Dans la case Nom du scénario, entrez le texte : Scénario 1.

Dans la case Cellules variables, entrez les cellules

Attention : Il faut toujours s’assurer que les cellules que vous sélectionnez pour les
variables du scénario soient des cellules ayant des chiffres; jamais des formules.
Sinon, le gestionnaire de scénarios va écraser vos formules lors de la préparation du
rapport de synthèse. Votre modèle ne sera plus valide.

Page 19 sur 22
 Microsoft Excel

VIII- Le solveur
Le solveur est un outil complémentaire que vous pouvez utiliser pour optimiser le
modèle que vous avez déjà préparé. Vous pouvez essayer de maximiser vos profits,
minimiser vos pertes ou atteindre une objectif avec le moindre de ressources
possibles. Il ne faut surtout pas oublier d’ajouter les contraintes qui limitent votre
modèle. Sinon, votre résultat ira vers l’infini !

Puisqu’il s’agit d’un modèle complémentaire, il n’est pas disponible dès l’ouverture
d’Excel. Suivez les instructions ci-dessous pour activer le Solveur.

Appuyez sur le bouton Office .

Appuyez sur le bouton Options Excel.
De la colonne de gauche, sélectionnez la catégorie Compléments.

De la zone Gérer, sélectionnez l’option

Compléments Excel et appuyez sur le
bouton Atteindre.
De la liste des macros complémentaires,
activez l’option Complément Solver.
Appuyez sur le bouton OK.

C’est à la fin de l’onglet Données que vous allez maintenant retrouver l’outil
Solveur.

Allez à l’onglet Données.

Appuyez sur le nouveau bouton Solveur.

Page 20 sur 22
 Microsoft Excel

La cellule à définir est

celle que vous désirez
optimiser. Veillez noter
que les cellules variables
doivent toujours être des
cellules ayant que des
chiffres; jamais de
formules. Il faut ensuite
entrer les contraintes.

Appuyez sur le bouton

Ajouter et ajoutez les
contraintes suivantes.

Page 21 sur 22