Université Sultan Moulay Slimane

Faculté Polydisciplinaire
Département des Sciences Economiques et de Gestion
Béni Méllal

Support de Cours
M25 : Informatique de gestion

Filière : SEG – S4

Pr. Abdelmoula ABOUHILAL

Département Mathématiques et Informatique
[email protected]

Année Universitaire: 2018-2019

 Plan de cours

Introduction générale à
01 l’informatique

Environnement Windows et
02 Internet

Traitement du texte en Ms
03
Word

04 Tableur Ms Excel

05 Ms Power Point

2 Pr. A.ABOUHILAL
 Chapitre 4
Microsoft Excel 2010
1. Introduction

Excel est un tableur. C’est une application conçue pour créer et utiliser des tableaux, appelés ici «
feuilles de calcul ». Ces tableaux sont composés de cellules (intersection d’une ligne et d’une
colonne) pouvant contenir du texte, des données numériques ou une formule de calculs. Un classeur
est constitué de feuilles, ayant chacune un onglet. Cliquer sur un onglet permet d’afficher la feuille
correspondante.

EXCEL fonctionne avec le menu Ruban et utilise différents outils, dont :

 un classeur (comprenant des feuilles de calcul ou onglets) ;

 un assistant graphique ;
 un générateur de base de données (permettant de créer des filtres) ;
 un utilitaire permettant de créer des macro-commandes (avec comme langage Visual Basic
pour Applications) ;
 un gestionnaire de scénarios et solveur.
Si vous utilisez davantage le clavier que la souris, il devrait vous intéresser de savoir que le ruban
propose de nouveaux raccourcis.

Cette modification engendre deux avantages considérables par rapport aux versions précédentes
d'Excel :

 Chaque bouton est associé à un raccourci dans le ruban.

 Des raccourcis qui nécessitent souvent moins de touches.

3 Pr. A.ABOUHILAL
Les nouveaux raccourcis ont également un nouveau nom : les touches accélératrices.
Appuyez sur ALT pour faire apparaître les touches accélératrices.
Voici par exemple comment utiliser des touches accélératrices pour centrer du texte :
1. Appuyez sur ALT pour faire apparaître les touches accélératrices.
2. Appuyez sur L pour sélectionner l'onglet Accueil.
3. Appuyez sur A, puis sur C pour centrer le texte sélectionné.

Plus d'informations sur l'utilisation des nouveaux raccourcis :

En appuyant sur ALT, vous verrez que des touches accélératrices existent pour tous les onglets du
ruban, toutes les commandes de tous les onglets, la barre d'outils Accès rapide et le bouton Microsoft
Office (que la présentation couvrira plus tard).

Appuyez sur la touche correspondant à l'onglet que vous désirez afficher. Cette action a pour effet
d'afficher toutes les touches accélératrices de cet onglet. Ensuite, appuyez sur la touche
correspondant à la fonction souhaitée.

 AFFICHAGE D’UNE FEUILLE DE CALCUL

 Conserver l’affichage des intitulés des lignes et des colonnes

Quand la zone active de la feuille de calcul est très large, il n’est plus possible de voir ses colonnes de
titres

De même, quand la zone active de la feuille s’étale sur une grande hauteur

Les lignes et colonnes de titres sont appelées des [ volets ]

 Sélectionnez les cellules des titres

 Ruban ►Affichage ► Figer les volets

Pour supprimer les volets :

 Ruban ►Affichage ► Figer les volets ► Libérer les volets.

4 Pr. A.ABOUHILAL
  TRAVAILLER SUR PLUSIEURS CLASSEURS
Si on veut travailler sur plusieurs classeurs, il faut commencer par les ouvrir.
 Bouton Office ► Ouvrir
 Sélectionnez les fichiers [ Ctrl + Clic]
Une fois les classeurs ouverts, vous pouvez afficher la feuille d’un classeur en cliquant sur le nom de
ce classeur dans la barre des tâches.
-ou-
Affichage ► Changer de fenêtre ► affichez le classeur souhaité.
 Comparaison de feuilles de deux classeurs côte à côte
Si deux classeurs ont une structure similaire, il est intéressant de les mettre côte à côte, et de les faire
défiler simultanément afin de pouvoir comparer facilement leurs données.
 Ouvrez les deux classeurs.
 Activez le premier classeur.
 Affichage ► Afficher côte à côte.
Les deux classeurs apparaissent l’un en dessous de l’autre.
Pour revenir à l’affichage normal, désactivez le bouton [Afficher côte à côte ].

 SELECTION DE CELLULES

Avec le clavier et la souris

• Sélectionner : Une cellule

• Atteindre : (La cellule A1 Ctrl + (Home),La dernière cellule de la zone active Ctrl + Fin

• Des cellules faisant partie de la ligne ou de la colonne de la cellule active touche Maj + Clic

• Une plage de cellules

• Des cellules non adjacentes (isolées, en plages, en colonnes ou en lignes) touche Ctrl +c Clic

 SAISIE DANS LES CELLULES

Sélectionnez une cellule et saisissez par exemple un texte ou un nombre. Par défaut, un texte est
aligné à gauche, un nombre est aligné à droite.
Après Validation et passage à une autre cellule (bas, haut, gauche, droite ou bien cellule quelconque.
 Saisie rapide dans une plage sélectionnée (Colonne après colonne -ou- Ligne après ligne )
 Saisie d’une même valeur dans toutes les cellules d’une plage Sélectionnez la plage. Saisissez
la valeur. Validez avec Ctrl + Entrée.

5 Pr. A.ABOUHILAL
  SAISIE DANS LES CELLULES

 Saisie d’un nombre sous forme de texte

Exemple: ’16
Après validation, un triangle vert apparaît en haut à gauche de la cellule:

 Saisie semi-automatique de données de type texte

Lorsqu’on effectue des saisies de type texte dans une colonne, Excel les mémorise. Après avoir tapé
une ou plusieurs lettres dans une nouvelle cellule de la colonne, dès qu’Excel reconnaît une saisie
précédente, celle-ci est automatiquement proposée.

 Saisie d’une date ou d’une heure

Séparez le jour, le mois et l’année par les caractères / ou –
Seul le caractère : est admis pour séparer les heures et les minutes
Exemple : 9h28 s’écrit 9:28 et est affiché 09:28
La virgule, ainsi que pour séparer les minutes et les secondes.

 Saisie restreinte
Données ► Validation des données : permet de restreindre une saisie.
Commencez par sélectionner les cellules concernées par l’application d’une saisie restreinte.

6 Pr. A.ABOUHILAL
RECHERCHER, REMPLACER
Accueil ► Edition ► bouton « Rechercher/Remplacer ».
Une fenêtre peut rester ouverte lors de la saisie dans les cellules.
 Rechercher : raccourcis Ctrl + F
La recherche est à effectuer dans une plage de cellules de la feuille active sinon sur la zone active
Pensez à utiliser les deux caractères : * et ?
 Remplacer : Ctrl + H
• Ouvrez l’onglet « Remplacer ».
• Le bouton Remplacer permet de remplacer les valeurs une par une.

VERIFICATION ORTHOGRAPHIQUE
Révision ► Vérification ► Orthographe ». - Ou- touche F7.
Si Excel détecte un terme absent du dictionnaire, la fenêtre « Orthographe » est affichée. Elle contient
des suggestions de correction, ainsi que diverses options.

SERIES DE DONNEES
La procédure de création d’une série est plus rapide et plus fiable que la saisie individuelle de
chaque élément d’une série. On peut créer une série dans tous les sens.
Pour créer une série numérique, une série chronologique, ou une série alphanumérique,
Du cliqué-glissé sur la poignée ▪ d’une sélection de cellules, le pointeur revêt l’aspect d’une croix
noire + .
De la fenêtre « Série de données ». Pour l’afficher :
Accueil ► Edition ► bouton « Remplissage » ► Série.
 SERIE NUMERIQUE
Exemple : 5, 8, 11, 14, 17, 20.
4, 8, 16, 32, 64.
 SERIE CHRONOLOGIQUE
Exemple : 10/05/2011, 17/05/2011, 24/05/2011.
 SERIE ALPHANUMERIQUE
Exemple : couleur 1, couleur 2, couleur 3, couleur 4.
 SERIE CREEE A PARTIR D’UNE LISTE PERSONNALISEE
Avant de créer la série, il convient de créer la liste personnalisée.
bouton Office ► Options Excel ► Standard ► « Meilleures options pour travailler avec Excel », puis
cliquez sur le bouton « Modifier les listes personnalisées ».

7 Pr. A.ABOUHILAL
Formats d’affichage
Onze formats prédéfinis par Excel sont proposés, avec un aperçu de la cellule active.
Format Standard :alignement à droite d’un nombre ou d’une date, et en l’alignement à gauche d’un
texte.
Format Nombre : Exemple : 3528,10
Il s’affiche un nombre de ce format avec deux décimales et sans séparateur de milliers.
Bouton Office ► Options Excel ► Options avancées. Dans la rubrique « Options d’édition »
Format Monétaire : deux décimales, séparateur de milliers et devise. Exemple: 219,50 €
Changement du symbole monétaire par défaut : Démarrer ► Panneau de configuration.
Format Comptabilité:
Comme un nombre de format Monétaire mais avec:
- Une valeur nulle  tiret
- Montant négatif : le signe moins est aligné à gauche
- Le symbole de la devise est aligné avec un retrait d’un caractère du bord droit de la
cellule ;
Exemple :
Format Date et Heure:
Format Pourcentage : Exemple : 0,055,  5,5%
Format Fraction: une partie entière suivie d’une fraction.
Exemple: 3,5  3 ½
Format Scientifique : Exemple: 123456 = 1,23456 * 1051,235E05
Format Texte:
Format Spécial :
Le format Spécial regroupe divers formats particuliers, type Code postal, Sécurité Sociale ou
Téléphone. Ils dépendent du paramètre régional choisi.
Format personnalisé:
Si vous souhaitez définir un format Personnalisé
Exemple:
SAISIE FORMAT APPLIQUE AFFICHAGE

Jaune «La couleur est »@ La couleur est jaune

12 *+;[Rouge]«- »0 +++++++++++++++++

Alignement et orientation
L’alignement concerne la disposition du texte dans la cellule
Sur le ruban, le groupe Alignement de l’onglet Accueil ;
-Ou-
La fenêtre « Format de cellule », à l’onglet Alignement.
 Alignement horizontal ou vertical;
8 Pr. A.ABOUHILAL
  Orientation du texte;
 Texte et dimensions de la cellule
Renvoyer à la ligne automatiquement : Le texte ne déborde plus sur les cellules voisines;
Ajuster : la taille des caractères est réduite et La cellule garde les mêmes dimensions;
Fusionner les cellules.

MISE EN FORME CONDITIONNELLE

Mise en forme conditionnelle : la mise en forme est conditionnée par le contenu de la cellule.
Exemple :
Si la valeur d’une cellule est comprise entre 18 et 24, ses caractères sont verts et le style est italique.
Des mises en forme conditionnelles prédéfinies d’Excel sont disponibles.
On peut également créer des mises en forme conditionnelles personnalisées.
 Mises en forme conditionnelles prédéfinies
Il existe cinq types prédéfinis de règles.
 Règles de mise en surbrillance des cellules
 Règles des couleurs plus/moins élevées
 Barres de données
 Nuances de couleurs
 Jeux d’icônes
 Mises en forme conditionnelles personnalisées
Nouvelle règle de mise en forme ►bouton Mise en forme conditionnelle ► Nouvelle règle.
Six types de règles sont proposés. Dans le cadre supérieur, sélectionnez le type souhaité. Dans le
cadre inférieur, définissez la règle à appliquer à la sélection de cellules.
 Gérer les règles
On peut appliquer à une sélection de cellules plusieurs mises en forme conditionnelles.
Mise en forme conditionnelle ► Gérer les règles.
 Effacer les règles
Mise en forme conditionnelle ► Effacer les règles.
Il faut Préciser sur quelles cellules elles doivent être effacées.
Excel peut changer la présentation d’une cellule selon des règles que vous avez établies.
Le contenu peut changer de:
 Couleur de la cellule,
 La couleur,
 La taille et le style de la police de caractère,
 L'encadrement de la cellule
 Et plusieurs autres options de présentation pour vous avertir d’un changement
important qui nécessite votre attention.
Les mises en forme conditionnelles peuvent aussi être utilisées sous d’autres formes
Des options qui étaient seulement disponibles en utilisant des logiciels spécialisés.
9 Pr. A.ABOUHILAL
 La première colonne indique le nom du produit. La seconde colonne indique le minimum de
produit à conserver. Il faut en commander d’autres unités lorsque la quantité disponible, la troisième
colonne, est inférieur au minimum. La commande mise en forme conditionnelle sera utilisée pour
afficher en rouge lorsque la quantité disponible sera au niveau critique.
• Placez le curseur dans la cellule C4.
• Allez à l’onglet Accueil.
• Appuyez sur le bouton Mise en forme conditionnelle.
• Sélectionnez les options Règles de mise en surbrillance des cellules et Inférieur à …

On peut aussi changer les couleurs ou la présentation

• Sélectionnez les options Règles de mise en surbrillance des cellules et Entre …
• Dans la première case, entrez =B4.
• Dans la seconde case, entrez =B4+5.
• De la liste des styles de présentation, sélectionnez Remplissage jaune avec texte jaune foncé.
• Appuyez sur le bouton OK.

10 Pr. A.ABOUHILAL
 • Changez la valeur de la cellule C4 à 10.

Copier la mise en forme conditionnelle

Sous l’onglet Accueil, dans le groupe Presse-papier, sélectionnez la commande Reproduire la mise
en forme.

MISE EN FORME CONDITIONNELLE- Exercice

 Barre de données
Une nouvelle option de la mise en page conditionnelle consiste à représenter les valeurs sous forme
de barre horizontale. Il est donc possible de comparer le contenu de plusieurs cellules juste en
comparant leurs barres de données.

• Sélectionnez les cellules G3 à G7.

• De l’onglet Accueil, sélectionnez la commande Mise en forme conditionnelle et
l’option Barres de données.

• Sélectionnez la couleur des barres de votre choix

11 Pr. A.ABOUHILAL
  Nuances de couleurs
Une autre option qui a été ajoutée à partir de la version 2007 d'Excel est de représenter les chiffres
avec une palette de couleurs.
• Sélectionnez les cellules B3 à f3.

• Sélectionnez la série de couleurs de votre choix.

 Jeux d'icônes
Une autre façon de représenter les valeurs dans les cellules est en utilisant des icônes.
• Sélectionnez les cellules A2 à A9.
• Sélectionnez l’onglet Accueil.
• Appuyez sur le bouton Mise en forme conditionnelle.
• De la liste des options, sélectionnez Jeux d’icônes.

12 Pr. A.ABOUHILAL
TRI RAPIDE
Pour trier une colonne de cellules, indiquez d’abord cette colonne :
 Cliquez sur une cellule quelconque de la colonne : avec cette méthode, le tri s’arrêtera à la
première cellule vide trouvée.
 Ou bien sélectionnez la colonne de valeurs à trier. Dans un ordre croissant, les cellules vides
seront placées en dernier.
Accueil ► Groupe «Edition ► bouton Trier et filtrer ;
-Ou-
Données ► Groupe Trier et filtrer ► bouton «Trier de A à Z» ou «Trier de A à Z»
 Fenêtre « Tri »
Elle est principalement utilisée pour un tableau de données qui permet de classer les données
sur plusieurs niveaux de critères et de préciser si la colonne comporte un en-tête en cochant la case «
Mes données ont des en-têtes ».
Un en-tête ou un pied de page peuvent par exemple comprendre un nom, une adresse, la date, le numéro de
page, également un logo, une image, une photo.
Pour afficher l’onglet Création des « Outils des en-têtes et pieds de page »,On l’active
Insertion ►groupe Texte ► bouton « En-tête et pied de page »
On utilisera ici:
 En-têtes et pieds de page prédéfinis
 En-têtes et pieds de page personnalisés
Exemple :Le &[Date] à &[Heure] ► Le 08/06/2010 à 20:35.

LES FORMULES
Toute formule doit commencer par le signe égal =
Le signe égal indique qu’un résultat doit être donné, contrairement à une saisie simple, sans résultat
attendu.
Excel ne distingue pas la casse (majuscule ou minuscule)
Exemple : A6 = a6 , SOMME() = somme()
Pour valider on tape Entrée ou cliquez dans une autre cellule, le résultat d’une formule apparaît dans
la cellule.
Pour afficher et masquer toutes les formules de la feuille de calcul :
Formules ►Groupe «Audit de formules» ► bouton «Afficher les formules».
-Ou bien-
Appuyez sur : Ctrl + touche guillemets.
Si une formule est longue, pour que son affichage soit plus lisible, insérez un ou plusieurs sauts de
ligne : Alt + Entrée.
Indépendance ou dépendance
Une formule est indépendante lorsqu’elle n’utilise pas la valeur d’une autre cellule (exemple = 8 * 5 -
6).

13 Pr. A.ABOUHILAL
Dans le cas contraire, on dit qu’elle est dépendante (exemple = A3 + B1). On intègre communément
des contenus de cellules dans les formules. Les
 Fonctions
Une formule peut utiliser une ou plusieurs fonctions, relatives à divers domaines.
Exemples de domaines
• Mathématiques, fonction SOMME ();
• Texte, fonction NBCAR ();
• Date, fonction AUJOURDHUI();
• Information, fonction ESTVIDE();
 Opérateurs de calcul
Les opérateurs de calcul sont, par ordre de priorité : Puissance ^ Multiplication * Division /
Addition + et Soustraction –
Exemple : la saisie =3^2*4+1 affiche comme résultat 37 (est calculé d’abord 3^2, puis 9*4, puis 36+1).
On peut utiliser des parenthèses pour préciser la priorité des calculs.
 Opérateur alphanumérique
L’esperluette & est l’opérateur permettant de concaténer des chaînes de caractères.
Exemple :
Saisissez Amine dans A1, Casa dans B1, puis dans la cellule C1 la formule suivante : =A1&" habite à
"&B1. La cellule C1 affiche : Amine habite à Casa.

REFERENCES RELATIVES OU ABSOLUES

Dans une formule, on peut désigner une cellule soit par sa référence ColonneLigne (B7,A13, …) ou
l’attribution d’un nom.
Exemple : =6*quantité, si la cellule B7 a été nommée quantité.
Quand on saisit sa référence, le contour de la cellule change de couleur et s’entoure de quatre
poignées, ce qui permet de bien la distinguer.
Il existe deux méthodes pour saisir la référence d’une cellule dans une formule : soit avec la souris ou
avec le clavier
 Référence relative
Comme son nom l’indique, elle est relative. Une formule faisant appel à une cellule avec sa référence
relative.
On se sert fréquemment des références relatives en effectuant la copie d’une formule sur plusieurs
cellules d’affilée :
Exemple : C1=A1*B1

14 Pr. A.ABOUHILAL
  Référence absolue
il convient de le préciser sous la forme : $colonne$ligne (procédé mémo : la référence au dollar est
absolue…). Dans ce cas, même si la cellule est recopiée, la valeur reste celle de la cellule d’origine. Le
symbole $ fige la colonne ou la ligne qui le suit.
Exemple: D1=$B$1 * C1

 Référence mixte
Il est possible d’indiquer une colonne fixe ($ColonneLigne) et une ligne relative, ou vice versa
(Colonne$Ligne).
Exemples : $A8 et A$8.
Pour passer de l’écriture d’un type de référence à un autre, il est pratique d’utiliser la touche F4.
Exercice :
Pour bien concrétiser ce qui précède, on réalise cet exemple.
Donner les formules (directement ou par recopie).

Exercice :
On considère le classeur ci-dessous:

15 Pr. A.ABOUHILAL
Donner les valeurs de X, Y et Z dans chacun des cas ci-dessous en faisant la recopie de la formule au
sens de la flèche.

REFERENCES : AUTRE FEUILLE, AUTRE CLASSEUR

On saisit le nom de la feuille, suivi d’un point d’exclamation, puis de la référence de la cellule.
Exemple: =Feuil1!T9
Si le nom de la feuille contient un espace, il doit être entouré d’apostrophes.
Exemple :
=‘Feuille une’!T9
 Utilisation de plages de cellules ayant les mêmes références, situées sur des feuilles
différentes du même classeur
Exemple :
=somme(Feuil1:Feuil3!G2:H8)
 Référence à une cellule appartenant à la feuille d’un autre classeur
Le nom du classeur auquel appartient la feuille est placé entre crochets. S’il contient un espace, on
doit ajouter des apostrophes.
Exemple:
=’[Classeur un]Feuil5’!A2.

ATTRIBUTION D’UN NOM

Un nom peut être attribué à une cellule, à une plage de cellules, à un ensemble de plages de
cellules, également à une formule ou à une constante.
La liste de tous les noms attribués est affichée dans la fenêtre «Gestionnaire de noms ».
Pour l’afficher:
Formules ► Noms définis ► Gestionnaire de noms;
- Ou -
Ctrl + F3.
 Règles concernant le nom :
 Il doit être différent d’une référence ligne colonne.
 Le premier caractère doit être une lettre ou le caractère de soulignement.
 Ne pas utiliser d’espace. Le point et le caractère de soulignement peuvent le remplacer.
Deux méthodes d’attribution d’un nom :
Saisie dans la zone Nom  Fenêtre « Gestionnaire de noms »

16 Pr. A.ABOUHILAL
AUDIT DE FORMULES, ERREURS ET REFERENCES CIRCULAIRES
Sous l’onglet Formules, le groupe « Audit de formules » contient les commandes spécifiques à
l’audit de formules, à la vérification et à la recherche d’erreurs, ainsi qu’aux références circulaires.
Indépendamment de toute erreur, on peut souhaiter auditer une formule, et examiner :
 Les liaisons existantes avec d’autres cellules : antécédents, dépendants.
 Son évaluation, c’est-à-dire le processus de son calcul.
 Des valeurs de cellules.

Erreurs
 Messages d’erreurs
Après validation d’une saisie, lorsqu’Excel repère une erreur, la cellule affiche un bref message
commençant par un dièse # et indiquant sa nature.
Exemples de valeurs d’erreurs :
#VALEUR ! : Cette erreur survient lorsqu'un type d'argument ou d'opérande inapproprié est utilisé.
#NOM ? : Cette erreur survient lorsque l'application ne reconnaît pas le texte contenu dans une
formule.
#REF !: Cette erreur survient lorsque les coordonnées d'une cellule ne sont pas valides.
#N/A :v(valeur non disponible, Not Available).
#NUL ! : Erreur de valeur nulle.
#DIV/0 ! : Erreur de division par zéro.
 Référence circulaire
Lorsqu’une cellule fait référence à son propre résultat, on dit qu’il y a référence circulaire.
Pour afficher la liste des cellules contenant une référence circulaire, ouvrez le menu déroulant du
bouton « Vérification des erreurs» ►Références circulaires.

LES FONCTIONS
Excel 2007 propose plus de 300 fonctions, utilisables dans les formules.
L’onglet « Formules » contient le groupe « Bibliothèque de fonctions ».
Si la fonction est en début de formule, elle doit être précédée du signe égal.
Exemple : La formule =MAINTENANT()
 ARGUMENTS
Une fonction requièrent des données, appelées arguments, pour retourner un résultat. Et quand il y a
plusieurs arguments, ils doivent être séparés par un point-virgule.
 Fonction sans argument : =AUJOURDHUI() , =ALEA()
 Fonctions avec un argument : =MOYENNE(3:3) , =ENT(SOMME(E6:G8))
 Fonction avec plusieurs arguments : =SOMME(5;B1:B6;C3;plage1)

Pour utiliser les autres fonctions de calcul proposées par Excel, il est important de connaître le
principe des références (absolues ou relatives), ainsi que les possibilités de nommer des cellules ou

17 Pr. A.ABOUHILAL
des plages de cellules pour les utiliser dans les formules. On peut regrouper les fonctions selon les
catégories suivantes:

Pour utiliser les autres fonctions de calcul proposées par Excel, il est important de connaître le
principe des références (absolues ou relatives), ainsi que les possibilités de nommer des cellules ou
des plages de cellules pour les utiliser dans les formules.
On peut regrouper les fonctions selon les catégories suivantes:

 Mathématiques:
Somme(): Pour additionner entre elles plusieurs cellules contiguës, on peut
utiliser la fonction SOMME d’EXCEL.
La fonction renvoie l’addition des sommes sélectionnées.
Exemple:
En A5 on écrit la formule suivante :
=SOMME(A1:A4)
Pour calculer la somme
Des cellules non adjacentes
=Somme(B5;D5)

18 Pr. A.ABOUHILAL
 SOMME.SI(): Cette fonction vous permet d'additionner plusieurs cellules en fonction d'un
critère déterminé.
Sa syntaxe :
=SOMME.SI(plage;critère;somme_plage)
plage : est la plage de cellule contenant le critère à retenir
critère : est LE critère à retenir (chiffre ou texte)
somme_plage: est la plage de cellule où se trouvent les nombres à
additionner.
Exemple : Dans la colonne A on a écrit des nombres. En colonne B,
on a indiqué "garçon" ou "fille". On voudrait obtenir le total des
nombres de la colonne A auxquels correspondent le mot "fille" en
colonne B.
En A7 on écrit la formule suivante :
=SOMME.SI(B1:B6;"fille";A1:A6)

On dispose de classeur ci-dessous :

Donner la formule qui permet de calculer la quantité de papier dans la cellule H6

19 Pr. A.ABOUHILAL
 SOMME.SI.ENS(): Cette formule permet d'additionner plusieurs cellules en fonction de
plusieurs critères.
sa syntaxe :
=SOMME.SI.ENS(Plage_somme ;Plage_critères1;Critères1;[plage_critères2;critères2];….)
Exemple :

Plage_somme, la plage de cellule contenant les montant à additionner, (dans la formule D:D),
Première plage de critères ; la colonne du service, la colonne C (dans la formule C:C),
Premier critère, le service saisi en cellule G1,
Deuxième plage de critères, la colonne B (dans la formule B:B),
Deuxième critère, le sexe saisie en cellule F2,
=somme.si.ens(D:D;C:C;G1;B:B;F2)

SOMMEPROD()
Réalisation d'opérations conditionnelles comptant et additionnant en utilisant des conditions
multiples.
Les formules permettant de calculer le nombre de valeurs ou les sommes sont fondées sur
deux conditions ou plus.
La fonction que nous allons utiliser est SOMMEPROD ; celle-ci appartient à la catégorie Math
& Trigo.
Exemple : Compter le nombre de mois où un commercial fait une vente. Le tableau de départ
pourrait se présenter sous cette forme :
Exemple 1:
- Compter le nombre des ventes faites par Jules sur le mois de février.
20 Pr. A.ABOUHILAL
C'est une formule conditionnelle qui emploie deux conditions.
= SOMMEPROD((A2:A13="Jules")*(B2:B13="Février"))

Pour obtenir la moyenne de plusieurs cellules contiguës, on peut utiliser la

fonction MOYENNE d’EXCEL.
• Ecrire dans la cellule où doit apparaître le résultat : =moyenne(
• Sélectionner ensuite à la souris la plage de cellules désirée
• Valider avec la touche Entrée.
La fonction renvoie la moyenne des sommes sélectionnées.
Exemple : Dans la colonne A on a écrit des nombres. On voudrait obtenir la
moyenne des nombres de la colonne A.
En A7 on écrit la formule suivante :
=MOYENNE(A1:A6)

ARRONDI ():
Le résultat d’une division ou d’une multiplication peut donner un nombre comportant plusieurs
décimales. Dans le but de créer des formulaires destinés à des tiers (exemple facture) les nombres
doivent, en principe, se présenter avec simplement deux décimales. On peut, bien entendu, utiliser
le format des nombres. Mais Excel va conserver en mémoire le nombre initial avec plusieurs
décimales et l’utilisera pour les calculs ultérieurs. D’où des problèmes d’affichages.
Exemple:
Le résultat du calcul : 2 divisé par 3 donne le résultat 0.666666666.....
Si on additionne 0.66666666.... avec 0.6666666.... on obtient 1.33333333.....

Avec un format des nombres à deux décimales, Excel propose un arrondi de présentation qui
donnera : 0.67 + 0.67 = 1.33. Evidemment, cet arrondi de présentation n’est pas satisfaisant. Il faut
donc utiliser une formule d’EXCEL, la formule ARRONDI().

21 Pr. A.ABOUHILAL
Sa syntaxe :
=ARRONDI(nombre;No_chiffres)
Cette formule requiert deux arguments :
 Nombre: est le nombre à arrondir. Ce nombre peut également être remplacé par la référence à
une cellule contenant un nombre. Il peut également être une formule de calcul complexe.
 No_chiffres: représente le nombre de décimales que l’on souhaite obtenir.
On aura donc par exemple pour obtenir le résultat du calcul 2/3 arrondi à 2 décimales:
=ARRONDI(2/3;2)
Le résultat de cette formule sera 0.67. Ce nombre s’affichera directement dans la cellule sans que l’on
ait besoin de passer par le format des nombres.
L’argument "nombre de décimales" donne des arrondis différents selon le chiffre utilisé.
La fonction =MAX()
Pour obtenir le plus grand nombre contenu dans des cellules différentes, on peut utiliser la
fonction MAX() d’EXCEL.
Voici sa syntaxe :
=MAX(plage_de_cellules)
plage_de_cellules est la zone dans laquelle la valeur la plus élevée doit être cherchée,
La fonction =MIN()
Pour obtenir le plus petit nombre contenu dans des cellules différentes, on peut utiliser la
fonction MIN() d’EXCEL.
Voici sa syntaxe :
=MIN(plage_de_cellules)
plage_de_cellules est la zone dans laquelle la valeur la moins élevée doit être cherchée.

La fonction =GRANDE.VALEUR()
Pour obtenir le plus petit nombre contenu dans des cellules différentes, on peut utiliser la fonction
GRANDE.VALEUR() d’EXCEL.
Voici sa syntaxe :
=GRANDE.VALEUR(plage_de_cellules;rang à prendre en compte)

22 Pr. A.ABOUHILAL
plage_de_cellules est la zone dans laquelle la valeur doit être cherchée.
rang à prendre en compte est le nombre correspondant au rang de la valeur cherchée. Si vous
écrivez 3 la fonction cherchera la 3eme plus grande valeur de la plage_de_cellules.

La fonction =PETITE.VALEUR()
Pour obtenir le plus petit nombre contenu dans des cellules différentes, on peut utiliser la fonction
PETITE.VALEUR() d’EXCEL.
Voici sa syntaxe :
=PETITE.VALEUR(plage_de_cellules;rang à prendre en compte)
plage_de_cellules est la zone dans laquelle la valeur doit être cherchée.
rang à prendre en compte est le nombre correspondant au rang de la valeur cherchée. Si vous
écrivez 3 la fonction cherchera la 3eme plus petite valeur de la plage_de_cellules.

La fonction =PETITE.VALEUR()
Pour obtenir le plus petit nombre contenu dans des cellules différentes, on peut utiliser la fonction
PETITE.VALEUR() d’EXCEL.
Voici sa syntaxe :
=PETITE.VALEUR(plage_de_cellules;rang à prendre en compte)
plage_de_cellules est la zone dans laquelle la valeur doit être cherchée.
rang à prendre en compte est le nombre correspondant au rang de la valeur cherchée. Si vous
écrivez 3 la fonction cherchera la 3eme plus petite valeur de la plage_de_cellules.

La fonction =SI()
On peut demander à Excel de réagir différemment en fonction du résultat d’un calcul demandé.
Sa syntaxe:
=SI (Test; alors Résultat_si_Test_vrai; sinon Résultat_si_Test_faux)
Exemple :
Vous décidez d’élaborer une facture. Vous souhaitez faire bénéficier à votre client d’une remise sur le
prix de vente. Cette remise sera de 5% du total hors taxes de la facture si ce total inférieur à 1000 €. Si
le total hors taxes est supérieur ou égal à 1000 €, le taux de la remise sera de 10% sur la totalité du
montant.
Le document Excel se présentera ainsi :

23 Pr. A.ABOUHILAL
 La formule en D6, compare tout d’abord le résultat obtenu en D5 au nombre 1000.
Si le montant en D5 est plus petit que 1000, alors (symbolisé par le point-virgule) le résultat à
afficher en D6 sera celui du calcul : D5 multiplié par 5%.
Sinon (symbolisé par le deuxième point-virgule) c’est à dire si le montant en D5 est égal ou
plus grand que le nombre 1000, le résultat à afficher en D6 sera celui du calcul : D5 multiplié par 10 %

CAS PRATIQUE : MATCH DE FOOTBALL

CAS PRATIQUE : Mention

Essayez de modifier votre formule en E2 pour afficher

• Recalé quand la moyenne est inférieure à 10
• Accepté quand la moyenne est comprise entre 10 et 12
24 Pr. A.ABOUHILAL
 • Mention Assez Bien quand la moyenne est comprise entre 12 et 14
• Mention Bien quand la moyenne est comprise entre 14 et 16
• Mention Très bien quand la moyenne est à plus de 16

La fonction NB.SI() Compte le nombre de cellules à l’intérieur d’une plage qui répondent à un
critère donné.
Syntaxe : NB.SI(plage;critère)
Plage : Plage de cellules dans laquelle vous voulez compter les cellules.
Critère : Critère, exprimé sous forme de nombre, d’expression ou de texte,
qui détermine les cellules à compter.
Exemple:
=NB.SI(A2:A5;"?????es"), Compte le nombre de cellules se terminant par les lettres « es » et
ayant exactement 7 lettres dans les cellules A2 à A5.

25 Pr. A.ABOUHILAL
1- Dénombrer les ventes qui ont eu lieu au Luxembourg.
2- Dénombrer les données de janvier 2009.
3- Pour compter les valeurs comprises entre 20 et 40, bornes comprises, nous allons d'abord
dénombrer celles qui sont inférieures ou égales à 40.
4- Dénombrer les ventes des produits dont le nom contient 'o' en deuxième position.
5- Dénombrer les ventes des régions dont le nom commence par 'L‘.

NB.SI.ENS
La fonction NB.SI.ENS utilise deux paramètres par condition: la plage sur laquelle appliquer le
critère et le critère lui-même.
NB.SI.ENS renvoie le nombre de lignes qui correspondent à TOUS les critères.
Exemple:

RECHERCHE ET INSERTION D’UNE FONCTION

Pour utiliser une fonction dans une formule, deux cas se présentent :
 Il vous faut alors passer par la fenêtre « Insérer une fonction »;
 Soit vous connaissez le nom de la fonction. Vous pouvez alors directement le saisir, une
info-bulle vous aidera si nécessaire pour l’écriture des arguments.
 Pour rechercher, puis insérer une fonction, procédez ainsi :
-Soit vous saisissez dans la zone « Rechercher une fonction » ce que vous souhaitez faire
Exemple : Calculer le reste d’une division
 Soit vous sélectionnez dans le menu déroulant une catégorie de fonctions, parmi la dizaine de
catégories proposées. Selon la catégorie choisie, une liste de fonctions s’affiche dans la zone
centrale.
 Une nouvelle fenêtre « Arguments de la fonction » apparaît. Saisissez les arguments, ou
gardez ceux qui sont éventuellement proposés.
 Et validez.
26 Pr. A.ABOUHILAL
AUTRES FONCTIONS
La formule =MAJUSCULE()
Cette formule vous permet de convertir le texte d'une cellule en MAJUSCULE.
La syntaxe :
=MAJUSCULE(Texte)
Texte fait référence à UNE cellule où un texte apparaît pour tout ou partie en minuscules, et le
convertit en majuscules.
Exemple : en A1 vous avez le texte "bonjour".
• En B1 on écrit la formule suivante :
=MAJUSCULE(A1)
Le résultat donne : BONJOUR

La formule =MINUSCULE()
Cette formule vous permet de convertir le texte d'une cellule en MAJUSCULE.
La syntaxe :
=MINUSCULE (Texte)
Texte fait référence à UNE cellule où un texte apparaît pour tout ou partie en majuscule, et le
convertit en minuscule.
Exemple : en A1 vous avez le texte “SALUT".
• En B1 on écrit la formule suivante :
=MINUSCULE(A1)
Le résultat donne : salut

La formule =NOMPROPRE()
Cette formule vous permet de mettre en MAJUSCULE la première lettre de CHAQUE mot d'un
texte contenu dans une cellule.
La syntaxe :
=NOMPROPRE(Texte)
Texte fait référence à UNE cellule où un texte apparaît pour tout ou partie en minuscules, et le
convertit en minuscules en ajoutant une majuscule au début de chaque mot.
Exemple : en A1 vous avez le texte "le matin suivant".
• En B1 on écrit la formule suivante :
=NOMPROPRE(A1)
Le résultat donne : Le Matin Suivant

27 Pr. A.ABOUHILAL
 La formule =CNUM()
Cette formule vous permet de redonner un format de NOMBRE à une liste de chiffres importés à
partir d'une base de donnée de type ACCES. En effet, il arrive que les séries de nombres importés
soient au format TEXTE. Il est alors impossible de les utiliser dans Excel pour effectuer des calculs.
La syntaxe :
==CNUM(Texte)
Exemple : en A1 vous avez le nombre 128. En demandant un format d'alignement standard
• En B1 on écrit la formule suivante :
=CNUM(A1)
Le résultat donne : 128 au format de nombre utilisable

La fonction de calcul =RECHERCHEV()

Cette fonction requiert 4 arguments et se présente ainsi :
=RECHERCHEV(valeur_cherchée;table_matrice;no_index_col;valeur_proche)
La fonction va rechercher, dans une table_matrice, la valeur_cherchée que l’on va lui indiquer.
Quand elle aura trouvé cette valeur (nombre ou texte), elle va renvoyer le contenu de la cellule se
trouvant sur la même ligne que la valeur trouvée et dans la colonne désignée par no_index_col.

Exemple:
Pour Afficher le grade et l’observation, nous allons utiliser la fonction =RECHERCHEV().
Tout d’abord, nous construisons 2 tableaux, sur la même feuille du classeur.

28 Pr. A.ABOUHILAL
Création d’un graphique

Pour créer un graphique on suit les étapes suivantes :

29 Pr. A.ABOUHILAL
30 Pr. A.ABOUHILAL
Exercices :

Exercice 1 :
Compléter le tableau avec les formules demandées

Exercice 2 :

Donner la formule de C4 et D4.

31 Pr. A.ABOUHILAL
Exercice 3 :

Donner la formule à inscrire dans C3 afin de réaliser les calcul de C3 à F7.

Exercice 4 :

Dans la cellule C4, donner la formule afin d’avoir le quotient de la division, et celle à inscrire dans le
cellule B4 pour calculer le reste de la division.
Exercice 5 :
Donner la formule à inscrire dans la cellule :
Sachant que :
 La prime est donnée aux représentants qui ont soit un chiffre d’affaire supérieur à 150000 ou
bien une ancienneté de plus de 5 ans.
 La prime vaut le max entre la durée d’ancienneté * 180 et le chiffre d’affaire divisé par 1000

32 Pr. A.ABOUHILAL
Exercice 6 :

Donner les formules à inscrire dans B1, C6 et E6.

Exercice 6 :

Compléter le tableau suivant sachant que le NC = PrixHT-Remise.

33 Pr. A.ABOUHILAL