Etude des tableurs : Cas de Excel
On appelle tableur, tout logiciel capable de réaliser de façon automatique les tableaux et des
formules.
I- Présentation d’Excel
Le fichier Excel qui s’affiche après lancement du logiciel Excel se présente comme un classeur
composé de plusieurs feuilles de calcul, par défaut 3.
Une feuille de calcul est composée de plusieurs cellules, c’est-à-dire plusieurs intersections entre
colonnes et lignes. Une cellule est repérée par <N° colonne><N° ligne>.
Exemple : B19 ; F15
II- Les formules arithmétiques
Avant d’inscrire une formule dans une cellule, on tape sur le signe (=). Les opérateurs
mathématiques sont aussi utilisés.
Exemple : On donne la feuille de calcul suivante :
A B C D
1 PRODUIT PU Qté MontantHT
2 Scanner 80000 15
3 Imprimante 150000 20
4 Webcam 25000 60
5 Souris 3000 150
6 Ecran 8000 75
7 Clavier 3000 300
8 Onduleur 25000 20
9 Régulateur 15000 20
10 Total HT
11 Remise (5%)
12 Net à payer
TAF :
1- Présenter le dictionnaire des variables.
2- Elaborer le dictionnaire des formules
III- Les fonctions logiques
A- La fonction SI
Elle permet de faire un choix de calcul parmi plusieurs choix. Il s’agit en fait d’une alternative qui
peut s’exprimer sous sa forme simple ou composé.
1- Structure alternative simple
Synthaxe
= SI (Condition ; Action 1 ; Action 2)
- Le premier point virgule (;) représente ALORS et le deuxième représente SINON.
Exemple : Représente la formule de calcul de la remise en tenant compte des conditions
suivantes :
• Le taux de remise est de 5% lorsque le total HT est supérieur ou égal à 1000000F
Réponse : D11 = Si (D10<1000000 ; 0 ; D10*5%)
2- Structure alternative composée
Synthaxe
= SI (Condition1 ; Action1 ; SI (Condition2 ; Action2 ; SI (Condition n ; Action n ; Action n+1)))
Exemple : Reprendre la formule de calcul de la remise en tenant compte des conditions suivantes :
le taux de remise est de 5% lorsque le total HT est compris entre 100000 et 500000. Il est de 7%
lorsque le total HT est compris entre 500000 et 1000000. Au-delà de 1000000, il est de 8%.
Réponse : D11 = Si (D10 <= 100000 ; 0 ;Si (D10 <= 500000 ; D10*5% ; Si (D10<=1000000 ;
D10*7% ; D10*8%)))
B- La fonction ET et OU
La fonction ET renvoie la valeur Vraie si tous les arguments sont vrais et la valeur Faux si l’un au
moins des arguments est faux.
Synthaxe
Et (Valeur_logique1 ; Valeur_logique2 ; ………)
La fonction OU renvoie la valeur Vraie si l’un au moins des arguments est vrai et la valeur Faux si
tous les arguments sont faux.
Exemple :
A B C D
1 NOM SEXE MOYENNE PRIME
2 Hervé M 10.5
3 Charles M 12.3
4 Irenée M 14
5 Ivonne F 9.5
6 Raoul M 08
7 Eddy M 15
8 Fiacre M 10
9 Diane F 11
10 Jean M 14
Il s’agit d’une ONG qui a souhaité octroyer des primes aux étudiants. Les conditions sont les
suivantes :
- Une prime de 100000 est octroyée à toutes les filles ayant au moins 12 de moyenne.
- Toutes les filles ont d’office 50000 comme prime et les garçons ayant une moyenne
supérieure ou égale à 10 bénéficient aussi de 50000.
TAF : Ecris dans les deux cas, les formules permettant de remplir les plages D2 à D10.
Réponse : D2 = SI (ET (B2 = ‘’F’’ ; C2 >= 12) ; 100000 ; 0)
D3 à D10 = Recopier D2 vers le bas.
IV- Les fonctions de base
A- La fonction SOMME
Elle permet d’additionner le contenu des cellules sélectionnées.
Ex : SOMME (E2 : E9)
B- Les fonctions MIN et MAX
Elle renvoie respectivement le plus petit nombre et le plus grand nombre contenus dans une plage
de cellule.
A B
1 50 100
2 75 25
3 46 30
4 100 14
5 12 09
6 14 13
7 12 14
8 200 92
9 175 25
10 67 08
C- La fonction MOYENNE
Elle renvoie la moyenne arithmétique des contenus des cellules sélectionnées.
Exemple : MOYENNE (A1 : A10)
D- Les fonctions NB et NBVAL
La fonction NB donne le nombre de cellule numérique (nombre, date et heure) non vide. Tandis
que la fonction NBVAL compte le nombre de cellule quel que soit la nature du contenu (Non
vide).
A B C
1 BONJOUR 75.75 OK
2
3 PAPA
4 20/03/2013
5
6 15H59
7
8 HELLO
E- Les fonctions [Link], [Link], [Link] et [Link]
❖ La fonction [Link] compte le nombre de cellules qui répondent à un critère donné.
Syntaxe
[Link] (Plage_Critère ; ‘’Critère’’)
❖ La fonction [Link] fait la somme des nombres qui répondent à un critère.
Syntaxe
[Link] (Plage_Critère ; ‘’Critère’’ ; Plage_Somme)
Exemple :
A B
1 200 150
2 500 120
3 1000 1475
4 75 11
5 125 1717
6 3375 1080
7 4000 10670
Formules : = [Link] (A1 : A7 ; ‘’>=1000’’) = 3
= [Link] (A1 : A7 ; ‘’>=1000’’) = 1000 + 3375 + 4000
❖ La fonction [Link] retourne le cumul des valeurs de la table_somme se trouvant
sur la même ligne (ou colonne) que les cellules de la table_recherche qui vérifient les critères
spécifiques à chaque niveau.
Syntaxe
[Link] (<Table_somme> ; <Table_recherche1> ; <critère1> ; <Table_recherche2> ;
<critère2> ;…)
❖ La fonction [Link] compte le nombre de fois où tous les critères des tables respectives
sont remplis simultanément.
Syntaxe
[Link] (<Table_recherche1> ; <critère1> ; <Table_recherche2> ; <critère2> ;…)
V- Les fonctions DATE et HEURE
A- La fonction ANNEE
Elle permet de renvoyer l’année correspondant à une date.
Syntaxe : ANNEE (DATE)
Exemple= ANNEE (10/04/2013) = 2013
B- La fonction MOIS
Elle permet de renvoyer le mois correspondant à une date.
Syntaxe : MOIS (DATE)
Exemple= MOIS (10/04/2013) = 04
C- La fonction JOUR
Elle permet de renvoyer le jour correspondant à une date.
Syntaxe : JOUR (DATE)
Exemple= JOUR (10/04/2013) = 10
D- La fonction AUJOURD’HUI
Elle permet de renvoyer la date du jour.
Syntaxe : AUJOURD’HUI ()
E- La fonction MAINTENANT
Elle permet de renvoyer La date et l’heure en cours.
Syntaxe : MAINTENANT ()
VI- Les références absolues et les références externes
A- Les références absolues
Ce sont des cellules qui demeurent identiques dans plusieurs formules, c’est-à-dire que leur
contenu ne varie pas lorsque la formule est recopiée vers le bas ou vers la droite. Elle se présente
sous la forme suivante :
$ <N°Colonne > $ < N°Ligne >
B- Les références externes
Elle retient deux feuilles de calcul entre elles. C’est-à-dire prendre les informations sur une feuille
de calcul et les exploiter sur une autre.
< Nom_feuille > ! < reference_cellule >
VII- Les fonctions de recherche
Elles permettent de réaliser des opérations de recherche. Les fonctions de recherche ont pour but
de remplir des feuilles de calcul à partir d’autres feuilles de calcul en se basant sur des données
identifiants. Il existe deux types de recherche : la recherche vectorielle et la recherche
matricielle.
A- La recherche vectorielle
Syntaxe
RECHERCHE (<valeur_cherchée> ; <matrice_recherche> ; <matrice_résultat>)
B- La recherche matricielle
Il s’agit de la RECHERCHE V (Verticale) ou de la RECHERCHE H (Horizontale).
La recherche est verticale lorsque les données sur la feuille de recherche sont disposées
verticalement. Elle est horizontale lorsque les données sur la feuille de recherche sont disposées de
manière horizontale.
Syntaxe
RECHERCHE V (<valeur_cherchée> ; <matrice_recherche> ; <N°Colonne_résultat>)
RECHERCHE H (<valeur_cherchée> ; <matrice_recherche> ; <N°Ligne_résultat>)
APPLICATION1
Dans la société « MAISON POUR TOUS » spécialisée dans la location immobilière depuis une
trentaine d’années, une application a été conçue en Excel de Microsoft pour le calcul de la
commission et de la majoration à la fin de chaque mois. La majoration correspond à un supplément
de 20% de la commission et n’est obtenue que si le nombre de catégories d’appartements est
supérieur ou égal à 5. La commission d’une catégorie est fonction de son taux de commission et du
montant total perçu dans la catégorie.
Travail à faire
1. Présenter le dictionnaire des variables de la feuille COMMISSION (Annexe).
2. Elaborer le dictionnaire des formules en tenant compte des données de la feuille
COMMISSION.
ANNEXE :
Feuille 1 : APPARTEMENT
A B C D E
1 Attribution des taux de commission en fonction de la catégorie
2 Catégorie F1 F2 F3 F4
3 Prix 15 500 35 000 45 000 70 000
4 Taux de commission 1.5% 3% 3.5% 5%
Feuille 2 : COMMISSION
A B C D E
1 MAISON POUR TOUS S.A.R.L
2
3 POINT DES APPARTEMENTS BAILLES
4 Mois : JUIN
5 Date Nom Prénom Catégorie Prix
6 02/06/2012 ADOBO Louis F2 35 000
7 04/06/2010 KAYODE Emmanuel F1 15 500
8 05/06/2010 ASSODANOU Elvire F3 45 000
9 10/06/2010 AYOKOUNLE Eloïse F2 35 000
10 10/06/2010 KOBA Chabi F3 45 000
11 13/06/2010 KAYODE Achirou F1 15 500
12 16/06/2010 ABIODOUN Fatima F3 45 000
13 16/06/2010 SOULE Alabi F2 35 000
14 17/06/2010 BABADJERA Gafar F2 35 000
15 19/06/2010 MAKY Joseph F3 45 000
16 19/06/2010 SAMBA Diallo F1 15 500
17 25/06/2010 ASSANE Nazer F2 35 000
18 25/06/2010 ASSOGBA Jean F3 45000
19 27/06/2010 ADOKOUNJETE Kofi F1 15 000
20 Totaux 462 000
21
22 Catégorie Totaux Nombre
23 F2 175 000 5
24 F1 62 000 4
25 F3 225 000 5
26 CALCUL DE LA COMMISSION ET DE LA MAJORATION
27 Catégorie Commission Supplément
28 F2 5 250 6 300
29 F1 930 0
30 F3 7 875 9 450
31 Totaux 14 055 15 750
APPLICATION 2
De nos jours, les opérations comptables sont effectuées à l’aide des logiciels de gestion comptable.
En effet les documents (grand livre, balance, journaux, etc…) produits par la comptabilité sont
édités facilement après avoir procédé à la saisie des écritures. Toute écriture dans un journal achat
provient des commandes fournisseurs. Ces commandes sont réalisées à l’aide de Microsoft Excel,
dont celle de l’annexe.
Travail à faire
1. Présenter le dictionnaire des variables de la feuille Commande.
2. Elaborer dans un tableau, les formules à mettre en place pour réaliser la commande.
3.
Annexe : Classeur de Microsoft Excel
Feuille : Taux
A B C D
1 LISTE DES FOURNISSEURS
2 Code Nom Taux sur achat (en millions)
3 [10 ; 25] [25 ; ∞[
4 I Inter Telecom 2% 4%
5 E Emoul’s corporation 1% 2%
Feuille : Commande
A B C D E
1 Sté Amassyze
2 04 BP 1961 Cel 00(33)41164579 Commande 1402
amassyze@[Link] N°
3
4 Code Fournisseur : I
5 Inter Telecom
Libellé :
6
7 Code Libellé Produit Qté Prix Montant
8 A04 Serveur HP 4 2 500 000 10 000 000
9 A01 Pentium IV 2,7 GHz 10 500 000 5 000 000
10 A05 Imprimante à jet d’encre 3 120 000 360 000
11 Total brut 15 360 000
12 Remise 307 200
13 Net à payer 15 052 800
APPLICATION 3
Une société de bâtiments et de travaux publics souhaite automatiser la gestion des
rémunérations de ses agents par un tableur.
Le classeur permettant ces calculs comporte une feuille de calcul contenant les données
concernant les agents, une feuille de la grille tarifaire et une feuille par mois contenant le relevé
journalier des heures effectuées par les membres du personnel par projet (Annexe).
Le temps de travail est de 140 heures pour les salariés. Les heures supplémentaires sont payées
25% en plus jusqu’à 20 heures supplémentaires puis 50% au-delà de ces 20 heures.
Travail à faire
1. Elaborer le dictionnaire des variables de la feuille RECAPITULATIF.
2. Présenter le dictionnaire des formules.
Annexe : Classeur Excel
Feuille : Grille
A B C D
1 T A U X H O R A I R E S
2 Qualification Niveau
3 Code Désignation 1 2
4 1 Apprenti 2 250 3 140
5 2 Ouvrier d'exécution 4 100 4 700
6 3 Ouvrier 4 550 5 150
professionnel
7 4 Compagnon 5 400 5 800
professionnel
8 5 Maitre ouvrier 6 200 6 500
9 6 Chef d'équipe 7 000 7 750
Feuille : Salaries
A B C D E
1 L I S T E D E S S A L A R I E S
2 Matricule Nom Prénom Qualification Niveau
professionnelle (1 ou 2)
3 S01 BOUFFON Jean 5 1
4 S02 ABALO Luc 4 2
5 S03 PAPOU Jean 2 2
6 S04 AZALOU Cossi 1 2
……………………….
11 S20 ZANNOU Atalagba 4 1
12 S21 ALLAGBE Louis 3 2
Feuille : Recapitulatif
A B C D E F
1 R E L E V E D E S H E U R E S
2
3 Année : 2017 Mois : Mars
4
Jour Matricule Prénom & Nom Chantier Temps passé Montant
5 (heures)
6 S04 Cossi AZALOU C465 8 25 120
1
7 S20 Atalagba ZANNOU C465 6 32 400
1
8 S01 Jean BOUFFON C465 7 43 400
1
.
.
100 S01 Jean BOUFFON C658 5 31 000
2
101 S20 Atalagba ZANNOU C465 6 32 400
2
.
.
130 Totaux 1 000 500 000
131
132 R E C A P I T U L A T I F
Total des Heures sup. Heures
Salarié heures 25% sup. Chantier Coût
133 50%
134 S20 150 10 0 C465 200 000
135 S01 170 20 10 C658 150 000
136 S04 130 0 0
.
.
150 Totaux 1 000 210 85 Totaux 500 000
APPLICATION 4
Pour mieux suivre le travail des techniciens assurant l’assistance téléphonique, il a été décidé
de récapituler chaque semaine tous les appels traités (on considère qu’il n’y a jamais plus de
200 appels dans une semaine). La feuille APPEL pour la semaine est présentée en annexe.
Une feuille RECAP, présentée également en annexe va permettre de calculer à partir des
données de la feuille APPEL, deux indicateurs importants pour la mesure de l’activité des
techniciens : la durée moyenne en secondes des appels traités et le taux de résolution des
problèmes des clients. Ce taux obtenu par un technicien détermine ensuite le montant d’une
prime.
Travail à faire : En fonction des renseignements fournis dans l’annexe,
1. Donner la formule de la cellule C205 de la feuille APPEL.
2. Elaborer le dictionnaire des variables
3. Elaborer le dictionnaire des formules de la feuille RECAP.
ANNEXE : Classeur Excel
Feuille TECHNICIEN
A B C
1 LISTE DES TECHNICIENS
2 Numéro Nom Prénom
3 T1 TESSI Henri
4 T2 BIO Jean
5 T4 KOFFI Stéphane
6 T3 KOUASSI Oumar
Feuille APPEL
A B C D
1 Récapitulatif des appels traités de la semaine n° 23
2
3 N° appel N° Technicien Durée (secondes) Résultat
4 1 T4 710 1
5 2 T3 252 0
6 3 T2 197 1
7 4 T1 248 1
8 5 T4 440 0
9 6 T1 164 1
10 7 T4 161 1
11 8 T3 210 1
12 9 T1 125 1
13 10 T2 413 0
204
205 Durée moyenne d’appel 292
Feuille RECAP
A B C D E F G
1 Numéro Noms & Nombre Durée Performance Taux Prime
Prénoms d’appels moyenne résolution
2 T1 TESSI Henri 3 179 Satisfaisant 100% 50
3 T2 BIO Jean 2 305 A améliorer 50% 15
4 T3 KOUASSI 2 231 Satisfaisant 50% 15
Oumar
5 T4 KOFFI 3 437 A améliorer 67% 30
Stéphane
6
7
8 Critère d’attribution de prime
9 Taux de résolution Prime
10 0% 39,99% 0
11 40% 59,99% 15
12 60% 79,99% 30
13 80% Et plus 50
NB : Les données sur les feuilles sont à titre indicatif et les taux sont variables.
APPLICATION 5
En attendant que le logiciel spécialisé sollicité par le Directeur soit à bout de réalisation,
Microsoft Excel est utilisé pour résoudre certaines tâches dont la répartition des produits dans
les différents dépôts lors de la réception des produits commandés auprès d’un fournisseur. Un
produit à livrer en une quantité au-delà de 15 dans un dépôt situant à plus de 60 kilomètres voit
son prix unitaire réduit d’un taux de 10%.
Travail à faire :
1- Remplir la feuille LIVRAISON
2- Réaliser le dictionnaire des variables.
3- Réaliser le dictionnaire des formules.
Feuille : PRODUIT
A B C D
1 LISTE DES PRODUITS
2 Référence Libellé Prix Stock
3 P01 Lait 1 000 25
4 P02 Sucre 800 20
5 P03 Beurre 2 000 10
6 P04 Fromage 1 500 5
7 P05 Pain 200 60
Feuille : FOURNISSEUR
A B C
1 LISTE DES FOURNISSEURS
2 Ville Matricule Nom
3 Porto F1 COBOF Sarl
4 Abomey F2 ZIGO S.A
5 Porto F3 OLEY Sarl
6 Cotonou F4 ETS SOUROU
Feuille : DEPOT
A B C D
1 LISTE DES DEPOTS
2 Code D1 D2 D3
3 Désignation Alafia Dèdèwanou Akiéssi
4 Distance par rapport au magasin 50 70 30
central (en Km)
Feuille : LIVRAISON
A B C D E F G
1 REPARTITION D’ARRIVAGE DES PRODUITS DANS LES DEPOTS
2 Numéro : F3 Taux (%) : 5%
3 Fournisseu Nom :
4 r Ville :
5 Référence Désignation Dépôt Qté Prix Montant
6 P03 D3 20
7 P03 D2 15
8 P01 D1 8
9 P01 D3 10
10 P04 D1 12
11 P05 D2 25
12 P05 D3 30
13 P05 D1 10
14 P02 D2 10
15 P02 D1 20
16 POINT DES PRODUITS PAR DEPOT
17 Code Nom Nombre de produits reçus Total des montants
18 D3
19 D1
20 D2
APPLICATION 6
REPARAUTO est un garage automobile. Le relevé des heures d’interventions effectué dans
un classeur Excel permet de calculer les heures supplémentaires effectuées par chaque
technicien et de calculer le coût hebdomadaire à imposer à chaque véhicule sachant que le
taux horaire est fonction de la spécialité (1-junior et 2-sénior) du technicien et de son niveau
(1-mécanicien, 2-électricien, 3-electrotechnicien, 4-frigoriste).
La durée hebdomadaire réglementaire de travail est de 10 heures. Les heures supplémentaires
sont payées 20% en plus jusqu’à 4 heures supplémentaires puis 40% au-delà de ces 4 heures
(annexe)
Travail à faire
1. Présenter le dictionnaire des variables de la feuille COUT
2. Elaborer le dictionnaire des formules correspondant
Annexe : Classeur de Microsoft Excel
Feuille SALAIRE
A B C D E
1 LISTE DES TECHNICIENS
2 Matricule Nom Prénom Spécialité Niveau
3 T01 AGODDOU Lucien 2 1
4 T02 AMAYISSE Yaovi 4 1
5 T03 BAH Alidou 1 2
6 T04 FALANA Joseph 3 1
7 T05 LAGUIDE Jacob 1 1
8 T07 PÄDONOU Valère 2 2
9 T06 YEKINI Seydou 1 2
Feuille TAUX
A B C D
1 TAUX HORAIRES / REFERENCE
2 Spécialité Désignation Junior Sénior
3 1 Mécanicien 8 000 10 000
4 2 Electricien 6 000 8 000
5 3 Electrotechnicien 4 000 6 000
6 4 Frigoriste 3 000 3 000
Feuille COUT
A B C D E F G
1 RECAPITULATIF DES HEURES EFFECTUEES
2 Date : 02 au 06 juillet 2012
3
4 Date Matricule Nom Prénom Véhicule Temps Montant
passé
5 02 T04 FALANA Joseph U 1414 RB 2 8 000
6 02 T05 LAGUIDE Jacob K 2222 RB 5 40 000
7 03 T01 AGOSSOU Lucien A 3333 RB 6 36 000
8 04 T02 AMAYISSE Yaovi K 2222 RB 3 9 000
9 04 T05 LAGUIDE Jacob U 1414 RB 5 40 000
10 04 T06 YEKINI Seydou U 1414 RB 4 32 000
11 05 T01 AGOSSOU Lucien U 1414 RB 6 36 000
12 05 T05 LAGUIDE Jacob A 3333 RB 6 48 000
13 06 T07 PADONOU Valère U 1414 RB 1 8 000
14 06 T04 FALANA Joseph A 3333 RB 3 12 000
15 Totaux : 41 201 000
16
17 POINT DES HEURES DES SALARIES : COÛTS HEBDOMADAIRES
18 Salarié Heures Heures sup Heures DES VEHIVULES
(20%) sup (40%)
19 T01 12 2 0 Véhicule Temps Coût
20 T03 0 0 0 U 1414 RB 18 124 000
21 T02 3 0 0 A 3333 RB 15 96 000
22 T05 16 4 2 K 2222 RB 8 49 000
23 Totaux : 31 6 2 Totaux : 41 269 000
