Module:

Informatique de gestion

Calculs Avancés
Tableur Excel

Pr Ettaibi CHARANI
[Link]@[Link]
PLAN

• Introduction
• Notion d’un tableur
(notion de classeur, feuille de calcul, cellules,
références)
• Création des classeurs et feuilles de calcul
(calcul avec référence absolu et relatif et la
mise en forme d ’une feuille de calcul)

• Formules (opérations de calcul simple)

PLAN

• Formules(Utilisation des fonctions de

base)(somme, moyenne, max, min, nombre..)

• Formules (Utilisation des fonctions conditionnelles

simples) (la fonction si(), [Link], [Link])

• Formules (Utilisation des fonctions conditionnelles

complexes avec et/ou)
(la fonction si() imbriquée, la fonction si() avec
et(), ou() …..etc)
PLAN

• Filtres automatiques et Avancés (Utilisation des

zones de critères)

• Fonctions de recherche Verticale et horizontale.

• Autres fonctions utiles(Date….)

Travail à faire :

Voir les Ateliers ou les travaux pratiques à réaliser

sous Excel
Atelier n°1: Calcul à l’aide des formules et des
opérations simples et utilisation des fonctions de
base(somme(), produit(), moyenne(), max(), min())
Atelier n°2: Calcul à l’aide des fonctions
conditionnelles Si(), [Link]() , [Link]() et(), ou()
[Link]())
Atelier n°3: Utilisation des filtres automatiques ou
Avancés (filtrage des données selon des critères)
Atelier n°4: Utilisation des fonctions de recherche
Verticale et Horizontale
 Module:

Informatique de Gestion
Calculs Avancés
Tableur Excel
Notions: Tableur, Classeur,
Feuille, Cellule, Référence et
Formule
Pr Ettaibi CHARANI
[Link]@[Link]
Introduction: Composants d’une machine
(Hard et Soft)
• Les composants matériels et logiciels
d’une machine(PC, PC portable, Tablettes,
MAC, Téléphones)
Introduction: Types logiciels et applications:

Dans ce qui suit, on va

s’intéresser à la notion Tableur
Excel
Notion de tableur

Notion de tableur?
• Un tableur est un logiciel utilisé pour réaliser des
calculs comptables, financiers ou administratifs,
etc.
• Exemple de tableur : Excel
est un tableur conçu par Microsoft . Il
existe plusieurs versions d'Excel (PC, MAC,…)
• Excel se base sur trois notions fondamentales:
– Classeurs
– Feuilles de calculs
– Représentations graphiques (histogrammes,
camemberts, courbes...)
Notion de classeur

Notion de classeur?
• Un classeur est l ’unité de travail d ’Excel.

• Un classeur est un ensemble de feuilles de calculs plus

éventuellement des représentations graphiques.
Notion de feuille de calcul

Notion de feuille de calcul?

• Une feuille de calcul est un tableau de plusieurs colonnes et
lignes, définissent à leurs intersections des Cellules.
• Les colonnes sont référencées par des lettres(A, B….)
• les lignes sont référencées par des chiffres(1, 2…..)
• Chaque feuille de Microsoft Excel est composée :
Le nom de la
Nombre des
Version d'Excel Nombre des lignes dernière
colonnes
colonne
16 8
A partir d’Excel 65.536 (= 2 ) 256 (= 2 ) IV
Excel 95
20 14
A partir d’Excel 1.048.576 (= 2 ) 16.384 (= 2 ) XFD
2007
Notion de cellule

Notion de cellule?
• Une cellule est l ’intersection des colonnes et lignes dans une
feuille de calcul.
• Les cellules sont référencées par des lettres(A, B….) suivis des
chiffres(1, 2…..)
• par exemple la première cellule d ’Excel est référencée par A1
la colonne A et la ligne 1
Notion de référence

Types de référence:
• Référence relative à une cellule:
spécifie l ’adresse d ’une cellule par rapport à la cellule
contenant la formule A1, B1, C1, A3, D4

• Référence absolue à une cellule :

spécifié l ’adresse exacte d ’une cellule quel que soit
l ’emplacement de la cellule contenant la formule ex:$A$1,
$D$4, …etc.
• Référence mixte à une cellule:
combinaison des deux références relatives et absolues.
Cette référence donne l ’adresse exacte par rapport à la
colonne ex: $A1, $D4 ou par rapport à la ligne ex:A$1,
D$3
Notion de plage de cellules

Plage de cellules:
Une plage de cellules est un ensemble de cellules
adjacentes ou non.

Exemple1: A1:A12
désigne une plage contenant toutes les cellules de la
colonne A de 1 à 12 (12 premières cellules de la colonne
A: A1, A2, ……A12)

Exemple2: A1:F1
désigne une plage contenant les 6 premières cellules de la
ligne 1: A1, B1, C1, D1, E1, F1
Notion de Nom d’une cellule ou plage de
cellules
Nom de Plage de cellules:
Sous Excel, vous pouvez nommer une plage de cellules.

Sélectionnez les cellules concernées et affecter un nom à

cette plage de de cellules.

Exemple1: la page A1:A12 peut être désignée par le nom

valeurs1

Pour sommer la plage A1:A12 , vous affectez la formule

=somme(A1:A12)
Vous pouvez remplacer cette plage par le nom valeurs1
La formule devient
= Somme(Valeurs1)
Notion de Formule
• Une formule est un ensemble d’opérations
permettant de réaliser un calcul précis :
Comme vous le voyez, vous pouvez mélanger des
références de cellules et des chiffres sans problème.
Ceci est valable pour les 4 opérations:
• + pour l'addition
• - pour la soustraction
• * pour la multiplication
• / pour la division.
A retenir:
les formules commencent toujours par =
• il ne faut pas laisser d'espaces entre les chiffres et les
lettres dans les références de cellules.
• La référence de cellule peut être tapée en minuscule ou
majuscule
Notion de référence: Entre deux feuilles de
calcul
• Référence à une cellule ou plage de cellules dans une autre
feuille de calcul :
la référence à une cellule ou plage de cellules dans une
autre feuille de calcul du même classeur se fait par: Nom de
feuille suivit par point ! et la référence de la cellule ou la
plage de cellules feuil1!A1 , feuil1!$A$1, feuil1!A1:A12
Notion de référence: Entre deux Classeurs

• Référence à une cellule ou plage de cellules dans un autre

classeur :
la référence à une cellule ou plage de cellules dans un autre
classeur se fait par: [Nom de classeur]Nom de la feuille
suivit par point ! et la référence de la cellule ou la plage de
cellules [classeur1]feuil2!A1 ou[classeur1]feuil1!A1:A12
 Module:

Informatique de gestion
Calculs Avancés
Tableur Excel
Notions: Fonctions(Somme,
Moyenne, Max, Min …etc)

Pr Ettaibi CHARANI
[Link]@[Link]
Notion de Fonction de base sous Excel

• Même si les calculs avec les 4 opérations sous Excel sont intéressants,
l'utilisation principale du tableur reprend surtout sur les fonctions.
• Une fonction est une formule permettant de faire un calcul précis
• La syntaxe générale d’une fonction est :
=Nom_fonction(liste_arguments)
OU
=Nom_fonction(nombre1;nombre2; …….; nombreN)
• Types de fonctions en Excel:
– Finances
– Date & heure
– Math & trigo
– Statistiques
– Recherche & matrices
– Texte
– Logique
– Etc…
Fonctions de base sous Excel
Pour Insérer une fonction
Utiliser le menu Insertion\Fonction
ou la barre d’outils:

Exemple: la fonction Somme()

Au lieu d’utiliser une formule trop longue
=A1+A2+……………………….+A12 Utilisez =SOMME(A1:A12)
Syntaxe générale:
=somme(une_plage_de_cellules)
Ou
=Somme(les_références_de_ cellules_séparés_par_point_virgule)
Exemple:
- La somme d’une plage de cellules
- =SOMME(A1:A12)
- La somme des cellules A1, B2 et C3
=Somme(A1; B2; C3)
- la somme de deux plages de cellules:
=Somme(A1:A12; C1:C12)
Ou =somme(A1:A12)+somme(C1:C12)
Fonctions Imbriquées sous Excel

• Souvent l'utilisation d'une fonction est insuffisante, il faut utiliser une

fonction sur un résultat d'une autre fonction ou d'un ensemble de fonctions.

• Donc, l'imbrication des fonctions est primordiale. Elle consiste donc à

remplacer des opérantes d'une fonction par d'autres fonctions.

• Exemple: utilisation de l’Arrondi de la moyenne

=ARRONDI(MOYENNE(E2:E21);2)
 Module:
Informatique de gestion
Travaux Pratiques Sous Excel
Corrigé de l’Atelier
N°1

Pr. Ettaibi CHARANI

[Link]@[Link]
 Module:

Informatique de gestion
Calculs Avancés
Tableur Excel
Notions: Fonctions(Si(), Et(),
Ou(), …etc)

Pr Ettaibi CHARANI
[Link]@[Link]
Fonctions Conditionnelles(Tests) : SI()

• Rôle de la fonction Si():

Parmi les fonctions qui s'utilisent régulièrement: La fonction SI qui permet

d'exécuter :
– une opération si la condition est remplie
– et une autre si cette condition n'est pas remplie.

• Syntaxe générale :

=SI(Condition; opération à effectuer si VRAI; Opération à

effectuer si FAUX)
Fonctions Conditionnelles(Tests) : SI()

• Une condition : est une comparaison de deux expressions

condition  Expression1 opérateur Expression2

• Les conditions utilisent les opérateurs de comparaison suivants :

– Supérieur >
– Supérieur ou égal >=
– Inférieur <
– Inférieur ou égal <=
– Egal =
– Différent <>

• Exemple :
Les conditions peuvent être de tous types. Voici quelques exemples:
– A1<>A2 ; A12>=20 ; G15 <= 0 … etc
Fonctions Conditionnelles(Tests) : SI()

• Exemple1 :

– La cellule B3 reçoit =SI(B1<>B2; B1+B2; B1*B2)

• Interprétation:

– si le contenu de B1 est différent de celui de B2, alors

• additionner les 2 nombres,
– sinon
• multiplier les 2 nombres
Fonctions Conditionnelles(Test): SI()

• Exemple2(CAS DU TP2):

– La cellule G10 reçoit =si(F10>=10 ; "ADMIS" ; "NON ADMIS")

• Interprétation:
– Si le résultat en F10(moyenne), est supérieur ou égal à 10, alors
• afficher ADMIS,
– sinon
• afficher NON ADMIS

• N.B. : On peut faire également :

– La cellule G10 reçoit =si(F10<10 ; " NON ADMIS" ; "ADMIS")
Fonctions SI() Imbriquées: Si(…Si()…)

• Exemple3:
Afficher trois observations : Non admis, rattrapage et Admis

– Si la moyenne de l'étudiant est inférieure à 5, alors

• étudiant non admis,
– Sinon
• Si la moyenne de l'étudiant à <10 (mais supérieur ou égal à 5
avec la première condition), alors
– étudiant en session de rattrapage,
• sinon
– étudiant admis,

• =Si(F10<5; "Non admis";

Si(F10<10; " Session de rattrapage";"Admis "))
Fonction ET():

• Rôle de la fonction ET():

– La fonction donne les valeurs VRAI ou FAUX.

– Elle est utilisée avec la fonction SI()
– Elle donne la valeur VRAI si toutes les conditions sont remplies

• Syntaxe :
– ET(condition1;condition2;………….)

– VRAI ET VRAI donne VRAI

– VRAI ET FAUX donne FAUX
– FAUX ET VRAI donne FAUX
– FAUX ET FAUX donne FAUX

• Exemple:
=Si(ET(F10<10; E10>=9,5; D9>=9,5; C9>=9,5); ‘’ admis ’’;……)
Fonction OU():

• Rôle de la fonction OU():

– La fonction donne les valeurs VRAI ou FAUX.

– Elle est utilisée avec la fonction SI()
– Elle donne la valeur VRAI si au moins une condition est remplie

• Syntaxe :
– OU(condition1;condition2;………….)

– VRAI OU VRAI donne VRAI

– VRAI OU FAUX donne VRAI
– FAUX OU VRAI donne VRAI
– FAUX OU FAUX donne FAUX

• Exemple:
=Si(F10>=10; Si(OU(E9<4; D9<4; C9<4); ‘’ non admis note
éliminatoire ’’,……)
 Module:

Informatique de gestion

Travaux Pratiques Sous Excel

Corrigé de l’Atelier
N°2
Pr Ettaibi CHARANI
[Link]@[Link]
 Module:

Informatique de gestion
Calculs Avancés
Tableur Excel
Notions: Fonctions([Link],
[Link], [Link],
[Link],…etc)
Pr Ettaibi CHARANI
[Link]@[Link]
Formules Conditionnelles:

Pour Sommer et compter selon des critères:

• [Link]()
• [Link]()
• [Link]()
• [Link]()
Fonction [Link]()

• Rôle de la fonction :

– Elle additionne des cellules spécifiées selon un certain critère.

• Syntaxe :

[Link](Plage; critère ;Somme_plage)

• Exemple1:

=[Link](E5:E9;"oui";G5:G9)

• Exemple2:

=[Link](D1:D12; ">=20";G1:G12)
Fonction [Link]()
EXEMPLE1
Fonction [Link]()

• Rôle de la fonction :

– Elle détermine le nombre des cellules non vides répondant à la

condition à l’intérieur d’une plage.

• Syntaxe :

[Link](Plage; critère)

Exemple1:

=[Link](G5:G10;"ajourné")
OU
=[Link](F5:F10;"<5")
Fonction [Link]()
Fonction [Link]()
• Rôle de la fonction :

– Elle additionne des cellules spécifiées selon plusieurs critères regroupés par
l’opérateur ET.

• Syntaxe :

[Link](somme_plage, plage_critères1, critère1, [plage_critères2, critère2],

...)

• Exemple:
si vous souhaitez additionner les nombres de la plage A1:A20 uniquement
si

 les nombres correspondants en B1:B20 sont supérieurs à zéro (0),

et
 les nombres correspondants en C1:C20 sont inférieurs à 10,

vous pouvez utiliser la formule suivante :

=[Link](A1:A20, B1:B20, ">0", C1:C20, "<10")
Fonction [Link]()

• Rôle de la fonction :

Applique les critères aux cellules de plusieurs plages et compte le nombre de fois où tous
les critères sont remplis. (les critères regroupés par l’opérateur et)

• Syntaxe :

[Link](plage_1, critère1, [plage_2, critère2], ...)

• Exemple:

Le nombre des étudiants qui ont un rattrapage

Moyenne>=5 et <10 (Ceci est appliqué pour les anciennes filières)

=[Link](F3:F8;">=5";F3:F8;"<10")
Autres Fonctions utiles :

Comme [Link]()
• [Link]()
• [Link]()
• [Link]()
Comme [Link]()
• [Link]()
• [Link]()
• [Link]()

Comme BDSOMME()
• BDMOYENNE()
• BDMAX()
• BDMIN()
• BDNB()
 Module:

Informatique de gestion

Travaux Pratiques Sous

Excel
Corrigé de l’Atelier
N°2(Suite)

Pr Ettaibi CHARANI
[Link]@[Link]
 Module:

Informatique de gestion
Calculs Avancés
Tableur Excel
Notions: Filtres Automatiques
et Avancés
Pr Ettaibi CHARANI
[Link]@[Link]
 Filtres

Filtres
Généralement, il y a deux types de filtres

•Filtre automatique
–Filtrer sur place dans la même zone que le tableau principal.
–Filtrer à l’aide des différents colonnes du tableau
–Impossible de faire le filtrage sur deux ou plusieurs colonnes
différentes

•Filtre Avancé ou Elaboré

–Filtrer dans une autre zone que le tableau principal.
–Filtrer à l’aide des différents colonnes du tableau
–Possibilité de faire le filtrage sur deux ou plusieurs colonnes
différentes
–Possibilité de filtrer à l’aide des critères qui comportent des
fonctions.
 Filtres

Filtre automatique
 Filtres

Filtre automatique personnalisé

•Filtre élaboré ou Avancé

–Il se base sur une zone de critère

–La zone de critère comporte les colonnes et au dessous les
critères.
–Cliquez sur le menu données/Filtre avancé
 Module:

Informatique de gestion

Travaux Pratiques Sous

Excel
Corrigé de l’Atelier
N°3

Pr Ettaibi CHARANI
[Link]@[Link]
 Module:

Informatique de gestion
Calculs Avancés
Tableur Excel
Notions: Autres fonctions
Utiles(BDSOMME,….)
Pr Ettaibi CHARANI
[Link]@[Link]
Fonction BDSOMME()
• Rôle de la fonction :

– Elle additionne des cellules spécifiées selon plusieurs critères regroupés par
l’opérateur ET ou OU.

• Syntaxe :

BDSOMME(base de données, champ, critères)

*base de données Obligatoire. Représente la plage de cellules qui

constitue la liste ou la base de données.
*champ Obligatoire. Indique la colonne utilisée dans la fonction

*critères Obligatoire. Représente la plage de cellules qui contient les

conditions que vous spécifiez

• Exemple: voir atelier n°3

=BDSOMME(B3:H8;"salaire" ;B64:C65)

Permet de sommer les salaires des employés selon plusieurs conditions

spécifiées dans la zone de critères B64:C65
Autres Fonctions utiles :

Comme [Link]()
• [Link]()
• [Link]()
• [Link]()
Comme [Link]()
• [Link]()
• [Link]()
• [Link]()
• [Link]()
Comme BDSOMME()
• BDMOYENNE()
• BDMAX()
• BDMIN()
• BDNB()
Autres Fonctions utiles: [Link]()
• Rôle de la fonction :
Renvoie la moyenne (arithmétique) de toutes les cellules d’une
plage qui répondent à des critères donnés.

• Syntaxe :

[Link](plage, critère, [plage_moyenne])

• Exemple:

[Link](C5:C20; "grade_A" ; D5:D20 )

Permet de calculer la moyenne arithmétique des salaires des employés de
grade A
Autres Fonctions utiles: [Link]()

• Rôle de la fonction :
Renvoie la moyenne (arithmétique) de toutes les cellules qui
répondent à plusieurs critères (ET).

• Syntaxe :

[Link](plage_moyenne, critère_plage1,
critère1, [critère_plage2, critère2], ...)
• Exemple:

[Link] (D5:D20 ;C5:C20; "grade_A" ;

C5:C20; "grade_B" )
Permet de calculer la moyenne arithmétique des salaires des employés de
grade A et B.
 Module:

Informatique de gestion
Calculs Avancés
Tableur Excel
Notions: Fonctions Recherche
RECHERCHEV() et RECHERCHEH()

Pr Ettaibi CHARANI
[Link]@[Link]
Fonction RECHERCHEV()
• Cherche une valeur donnée dans la colonne située à l'extrême gauche
d'une matrice et renvoie une valeur dans la même ligne d'une colonne que
vous spécifiez dans la matrice.

• Le « V » dans « RECHERCHEV » signifie « Vertical ».

• Syntaxe:

RECHERCHEV(valeur_cherchée;table_matrice;no_index_col)

• valeur_cherchée est la valeur à trouver dans la première colonne de la

matrice. L'argument valeur_cherchée peut être une valeur, une référence
ou une chaîne de texte.

• table_matrice est la table de données dans laquelle est exécutée la

recherche de la valeur. Utilisez une référence à une plage ou un nom de
plage, par exemple Base de données ou Liste.

• no_index_col est le numéro de la colonne de l'argument table_matrice

dont la valeur correspondante doit être renvoyée.
Fonction RECHERCHEH()
• Cherche une valeur donnée dans la colonne située à l'extrême gauche d'une matrice
et renvoie une valeur dans la même ligne d'une colonne que vous spécifiez dans la
matrice.

• Le « H » dans « RECHERCHEH » signifie « Horizontal ».

• Syntaxe:

RECHERCHEH(valeur_cherchée;table_matrice;no_index_ligne)

• valeur_cherchée est la valeur à trouver dans la première ligne de la matrice

L'argument valeur_cherchée peut être une valeur, une référence ou une chaîne de
texte.

• table_matrice est la table de données dans laquelle est exécutée la recherche de

la valeur. Utilisez une référence à une plage ou un nom de plage, par exemple Base
de données ou Liste.

• no_index_ligne est le numéro de la ligne de l'argument table_matrice dont la

valeur correspondante doit être renvoyée.
 Module:

Informatique de gestion

Travaux Pratiques Sous

Excel
Corrigé de l’Atelier
N°4

Pr Ettaibi CHARANI
[Link]@[Link]
 Module:

Informatique de gestion
Calculs Avancés
Tableur Excel
Notions: Fonctions Utiles sur
la manipulation de la Date

Pr Ettaibi CHARANI
[Link]@[Link]
Autres Fonctions utiles: AUJOURDHUI()

AUJOURDHUI()
• Rôle de la fonction :

Renvoie la date d’Aujourdhui c’est à dire la date système

• Syntaxe :

=AUJOURDHUI()
Autres Fonctions utiles: DATEDIF()

• Rôle de la fonction:
La fonction DATEDIF() permet de calculer la différence entre deux dates en
années, mois et jours.
• Notez bien que cette fonction n'est pas documentée dans l'aide Excel

• Elle est pratique pour effectuer des calculs sur des âges ou des dates
d'échéance ou pour calculer l’Ancienneté.

• Syntaxe :
=DATEDIF (Date1; Date2; Intervalle)
– Date 1 est la date de Départ
– Date 2 est la date de Fin
– Intervalle peut prendre différentes valeurs:
"y" : différence en années
"m" : différence en mois
"d" : différence en jours
Autres Fonctions utiles: DATEDIF()
• Syntaxe :
=DATEDIF (Date1; Date2; Intervalle)

Exemple:
 Module:

Informatique de gestion

Travaux Pratiques Sous

Excel
Corrigé de l’Atelier
N°5

Pr Ettaibi CHARANI
[Link]@[Link]
 Module:
Informatique de gestion
Calculs Avancés
Tableur Excel
Fin
Merci
Pr Ettaibi CHARANI
[Link]@[Link]