Excel Avancé
Introduction
Ons LEJRI Master MP RSO ISCAE 2019/2020 1
Rappels
Ons LEJRI Master MP RSO 2ISCAE 2019/2020
Qu’est ce qu’Excel?
• EXCEL est un gestionnaire de feuilles de calcul ou tableur. Il
permet d’organiser, manipuler et analyser les données.
• Excel intègre des fonctions de calcul numérique,
de représentation graphique, d'analyse de données et
de programmation
• Chaque fichier correspond à un classeur, lequel contient
des feuilles de calculs organisées. Chaque feuille correspond
à un tableau de lignes et de colonnes pouvant contenir des
valeurs (numériques ou non) ainsi que des formules
permettant les calculs
Ons LEJRI Master MP RSO ISCAE 2019/2020 3
Formule et syntaxe
Opérateur d’affectation Fonction Plage de cellules
Référence de la cellule
Formule de calcul
Cellule
Résultat
Ons LEJRI Master MP RSO ISCAE 2019/2020 4
Formule et syntaxe
Pour Ecrire une formule, on peut utiliser de simples
opérateurs (+,‐,*,/,…) ou des fonctions prédéfinies dans Excel.
En précédant toute formule par un « = »
Pour insérer une fonction, d’écrire le nom de la fonction
directement dans la cellule ou bien d’aller à l’onglet Formules
et de la sélectionner dans la bibliothèque de fonctions
On peut aussi utiliser le raccourci
Ons LEJRI Master MP RSO ISCAE 2019/2020 5
Référence relative et référence absolue
• Référence relative : C'est le mode de référence par défaut de
Excel. Si l'on fait un copier/coller d'une formule contenant
cette référence (Par exemple A1) alors la référence de la
formule s'ajustera en fonction du nouvel emplacement.
• Référence absolue : La référence est entièrement absolue. Si
l'on fait un copier/coller d'une formule contenant cette
référence (Par exemple $A$1) alors la référence de la
formule ne changera pas. Le symbole dollar "$" permet de
transformer les références relatives en références absolues.
Ons LEJRI Master MP RSO ISCAE 2019/2020 6
Référence relative et référence absolue
• Référence mixte : Une référence mixte est une référence qui
n'est fixée que sur une partie de la référence : soit la ligne,
soit la colonne. Exemples: A$2 est une référence relative sur
la colonne et absolue sur le ligne, $C1 est une référence
absolue sur la colonne et relative sur la ligne
Bloque la colonne Bloque la ligne
Ons LEJRI Master MP RSO ISCAE 2019/2020 7
Référence nominale
• C'est un troisième type de référence qui est en fait une
référence absolue. Mais au lieu de désigner la cellule par son
nom générique ($C$7), on lui donne un nom particulier
(souvent en rapport avec son contenu)
• Pour associer un nom à une cellule, il suffit de sectionner la
cellule, puis aller à l’onglet Formules et choisir Définir un
nom
Ons LEJRI Master MP RSO ISCAE 2019/2020 8
Exercice1
• On se propose de réaliser le tableau de multiplication de 1 à 9:
• Utiliser la recopie automatique ou glissement pour remplir la
première ligne et la première colonne
• Dans la cellule B2, écrire la formule et la recopier dans le
reste du tableau
Ons LEJRI Master MP RSO ISCAE 2019/2020 9
Les fonctions de base
Ons LEJRI Master MP RSO ISCAE 2019/2020 10
Les fonctions de somme
• SOMME: Renvoie la somme des valeurs stockées dans ses
arguments (nombres décimaux). Il peut s’agir d'un ensemble
de nombres (séparés par des;)d’une plage unique (A1:A6 ou
D:D) ou de plusieurs plages (A1:A6;B5:B12)
• SOMME.SI: SOMME.SI(plage;critère;[somme_plage])
permet de faire la somme des valeurs d’une plage en
omettant celles qui ne remplissent pas un certain critère
Ons LEJRI Master MP RSO ISCAE 2019/2020 11
Les fonctions de somme
• SOMME.SI.ENS: SOMME.SI.ENS(somme_plage; plage_critère1;
critère1, [plage_critère2; critère2], ...)
additionne les valeurs d'une plage de cellules donnée en
paramètre lorsqu'une ou plusieurs conditions sont remplies.
• SOMMEPROD: SOMMEPROD(matrice1; [matrice2]; [matrice3;,
...)
Permet d’effectuer la somme de produits d’éléments de matrices
les uns avec les autres.
Pour effectuer d’autres opérations arithmétiques (à la place du
produit), il suffit de remplacer les séparateurs d’arguments ; par
les opérateurs arithmétiques souhaités (/, ^, …)
Ons LEJRI Master MP RSO ISCAE 2019/2020 12
Les fonctions NB
• NB: NB(valeur1 ; [valeur2] ; ...)
Permet de compter le nombre de cellules contenant des
nombres
• NBVAL: NBVAL(valeur1 ; [valeur2] ; ...)
Compte les cellules non vides et ceci quelque soient leurs
contenus : nombres, textes, valeurs logiques (Vrai ou Faux),…
• NB.VIDE: NB.VIDE(plage)
Permet de compter le nombre de cellules vides
Ons LEJRI Master MP RSO ISCAE 2019/2020 13
Les fonctions NB
• NB.SI: NB.SI(plage ; critère)
Permet de compter le nombre de cellules qui répondent à un
critère
• NB.SI.ENS: NB.SI.ENS(plage_critère1;critère1;[plage_critère2;
critère2]…)
Applique les critères aux cellules de plusieurs plages et
compte le nombre de fois où tous les critères sont remplis.
Ons LEJRI Master MP RSO ISCAE 2019/2020 14
La fonction SI
• SI :SI(condition, traitement1 si vrai, traitement2 sinon)
Permet d’évaluer une valeur selon une condition et de
renvoyer une valeur différente selon si la condition est remplie
ou pas.
Ons LEJRI Master MP RSO ISCAE 2019/2020 15
Exercice2
1. Ouvrir le fichier RessourceExercice2.xlsx
2. Mettre les titres des colonnes en gras et les centrer
3. Encadrer le tableau: contour tableau et bordures verticales;
contour titres colonne et ligne TOTAL
4. Changer la police (Broadway) et la taille de la police (15) du
titre « FACTURE FOURNITURES SCOLAIRES »
5. Fusionner les cellules pour centrer le titre au dessus du
tableau et l'entourer
6. Dans la cellule L1, insérer la date et l’heure système
Ons LEJRI Master MP RSO ISCAE 2019/2020 16
Exercice2
7. Dans la cellule E4, calculer le prix total HT. Puis recopier la
formule dans la colonne E
8. Dans la cellule F4, calculer le prix total TTC. Puis recopier la
formule dans la colonne F
9. Dans la cellule E14, utiliser la fonction SOMMEPROD pour
calculer le prix total HT
10.Dans la cellule F14, utiliser la fonction SOMMEPROD pour
calculer le prix total TTC
Ons LEJRI Master MP RSO ISCAE 2019/2020 17
Exercice2
11. Dans la cellule B19, écrire la formule donnant le nombre
d’articles
12. Dans la cellule B20, écrire la formule donnant le nombre
d’articles dont la quantité est >3
13. Dans la cellule B21, écrire la formule donnant le nombre
d’articles dont la quantité est >3 et le prix unitaire est < à 1DT
14.Remplir les cellules F19, F20 et F21
15.Dans la colonne A et pour chaque article afficher « Cher » ou
« Pas cher » selon si le prix HT est > ou non au prix moyen HT
Ons LEJRI Master MP RSO ISCAE 2019/2020 18
