Efficacité Opérationnelle Excel
Aymen El Abayed
ABF
1 Excel
Plan
1. Logiciel Tableur 8. Filtre automatique
2. Notion de classeur 9. Trie
3. Mise en forme 10. Sous-totaux
A. Police
B. Nombre 11. filtre élaboré
C. Alignement 12. Tableau croisé dynamique
D. Bordure
4. Insertion des formules
A. Calcul
B. Fonction prédéfinies
5. Insertion des graphiques
6. Mise en forme conditionnelle
7. Fonctions prédéfinies avancées
A. Si
B. Nb
C. Nb.si
D. Somme.si
E. RECHERCHEV
F. RECHERCHEH
2 Excel
Découverte de l’écran
3 Excel
Notion des classeurs et des adresses
Cellule B5 adressage relatif
$B$5 adressage absolue
Les Feuilles
4 Excel
Saisie et enregistrement :
Saisie d’un tableau : la saisie dans tableur se fait directement
dans les cases
Lancer le logiciel tableur
Ouvrir un nouveau classeur
Saisir les données dans les cellules du tableur
Enregistrement d’un classeur : l’enregistrement permet de
sauvegarder le classeur pour le manipuler plus tard.
Cliquez sur le Bouton Office/fichier
Cliquez sur Enregistrer sous…
Sélectionnez l’emplacement de l’enregistrement dans la
fenêtre qui s’ouvre
Saisissez le nom du classeur
Cliquez sur le bouton enregistre pour valider
5 Excel
Relative versus absolue
= B2 + B2 * Taxe
Étape 2 : On tape la formule correspondante
au calcul.
Étape 3 : On étend la cellule sur les autres cellules.
6 Note : On aurait put utiliser la référence
Excel absolue $C$6
Mise en forme des cellules
La mise en forme du tableau permet de modifier l’apparence
des cellules et de leurs contenus: taille, police, alignement,
bordure, ….
Sélectionnez les cellules du tableau
Cliquez sur le bouton droit de la souris
Cliquez sur format de cellule
Police: Modification de Alignement : Position Bordure : traçage de Nombre modifier
format des caractères de texte dans la cellule tableau l’apparence des nombres
7 Excel
Mise en forme des cellules
Police : la mise en forme Alignement: la position de Nombre: Les Différentes
des caractères texte dans la cellule formes des chiffres
8 Excel
Police
9 Excel
Bordure traçage des tableaux
10 Excel
Nombre
11 Excel
Alignement
12 Excel
Insertion des formules : Le calcul
Dans la cellule D2, la formule qui permet de calculer le
Montant total sachant que : Montant total = Quantité *
Prix unitaire
Création d’une formule : =B2*C2
Cliquez sur la cellule dans laquelle vous
voulez entrer la formule.
Tapez = (un signe égal).
Entrez la formule.
Appuyez sur la touche ENTRÉE
13 Excel
Syntaxe d’une formule
Opérateur d’affectation
14 Excel
Syntaxe d’une formule
Opérandes (données à traiter)
Références aux cellules Plages cellules
Constantes
15 Excel
Syntaxe d’une formule
Opérateurs (instructions)
Opérateurs de calcul Fonctions
16 Excel
Relative versus absolue
Formule en référence relative
17 Excel
Relative versus absolue
On étend la cellule sur les autres cellules.
18 Excel
Relative versus absolue
Erreur !
La référence concernant la taxe est aussi
19 descendue
Excel de deux cellules.
Relative versus absolue
On renomme la cellule pour Étape 1 :
la rendre « absolue de C6 à $C$6»
20 Excel
Fonction: Max/Min/somme/moyenne
21 Excel
Fonction: Max/Min/somme/moyenne
=Somme(B3:B10)
On calcul la somme des valeurs de plage des cellules entre B3 et
B10
=Max(B3:B10)
On détermine la valeur maximal dans la plage des cellules entre
B3 et B10
=Min(B3:B10)
On détermine la valeur minimal dans la plage des cellules entre
B3 et B10
=Moyenne (B3:B10)
On calcul la moyenne des valeurs de plage des cellules entre B3
et B10
22 Excel
Insertion des graphiques
Un graphique est utilisé pour
Simplifier l’analyse d’une masse de
données
Pouvoir comparer et interpréter
les données
Pour créer un graphique :
1. Sélectionner la plage de cellules à
représenter
2. Activer le menu Insertion
3. Activer la commande graphique.
4. Choisir le type de graphique voulu.
5. Sélectionner un sous type de graphique
23 Excel
Mise en forme conditionnelle
La mise en forme conditionnelle permet de surveiller les
résultats de certaines formules ou d'autres valeurs de
cellule.
1. Sélectionnez la cellule où vous allez appliquer la mise en
forme
2. Activer le menu Accueil
3. Cliquez sur la commande Mise en forme conditionnelle
4. Choisir la fonction gérer les règles
5. Cliquer sur la commande Nouvelle règle
6. Choisir le type de règle voulu dans la liste
7. Saisir la formule ou les valeurs en appliquant la ou les
conditions
8. Choisir la mise en forme voulue
9. Valider par OK
24 Excel
Fonctions prédéfinies avancées
La fonction Si
La fonction SI renvoie la valeur de vérité d’un test
logique. La syntaxe est :
=SI (test logique; valeursiVrai; valeursiFaux)
1. Choisissez le menu Formule
2. Cliquez sur Insérer une Fonction
3. Sélectionnez la fonction SI
4. saisir le test logique de la fonction dans le
champ test logique
5. saisir la valeur si le test logique est vrai dans
le champ valeur si vrai
6. saisir la valeur si le test logique est faux dans
le champ valeur si faux
7. Cliquez sur OK
25 Excel
Fonctions prédéfinies avancées
La fonction Nb calcul le nombre des cellules numériques
existant dans une plage des cellule
=NB(C8:I18)
26 Excel
Fonctions prédéfinies avancées
La fonction Nb.si Calcul le nombre de cellules dans uneplage
satisfaisant un critère donner
=NB.SI(plage; ‘’critère ‘’) =NB.SI(B3:B15;’’Tunis’’)
27 Excel
Fonctions prédéfinies avancées
La fonction somme.si calcule la somme des valeurs d’une
plage des cellules satisfaisant un critere
Somme.Si(plage_critère;critère;plage_somme)
Somme.Si(B3:B10;’’Ben Arous’’;E3:E10)
28 Excel
Fonctions de Recherche (1)
Renvoie une valeur provenant d'une plage
Recherche d’une ligne : RECHERCHEV
Recherche d’une colonne : RECHERCHEH
Syntaxe :
RECHERCHEV(valeur;plage;num_colonne)
valeur : Valeur à chercher (Référence ou constante)
plage : la plage où s’effectue la recherche
Num_colonne : le numéro de la colonne dont la valeur
doit être renvoyée
29 Excel
Fonctions de Recherche (2)
Renvoie d’une valeur provenant d'une plage
Dans la plage A1:B6 :
Colonne 1 : nom-
Colonne 2 : note-
Recherche la ligne de Benoit dans cette plage et donne la note.
= RECHERCHEV ( ’’Benoit’’ ; A1:B6 ; 2 ; FAUX)
30 Excel
Fonctions de Recherche (3)
Si le tableau est trié
=RECHERCHEV( B1 ; D3:F6 ; 3 ; VRAI)
Colonne1 Colonne3
Excel
Filtres automatiques (1)
Dans une feuille (ou une plage de données) en tant que table, on peut
sélectionner (et ne pas faire apparaître les autres) certaines données.
Exemple d’une table en Excel:
Noms des colonnes
Cela correspond à la table Enseignant(Nom,Prénom,Statue)
32 Excel
Filtres automatiques (2)
Pour chaque colonne, on peut sélectionner quelles sont les lignes
qui nous intéresse
Comme, on peut moduler ces choix, on parle alors de tableau
dynamique
Exemple, sélectionner que les MCF :
On clique dans le menu DonnéesFiltreFiltre Automatique
On obtient :
Puis, on clique sur la colonne Statue, et on choisit MCF. On obtient alors
33 Excel
Filtres automatiques (4)
On peut aussi choisir des filtres plus élaborés
Pour cela, on clique sur une des colonnes puis on choix « personnalisé »
:
Il existe plusieurs variantes comme « commence par », « se termine
par » etc…
34 Excel
Sous-totaux (1)
Comme on peut sélectionner des lignes du tableux (filtre), il est
possible de faire des calculs sur les sous parties de ce même tableau
Prenons cet exemple :
Nous souhaitons les ventes totales
Pour chaque entreprise…
Notez qu’il faut que les données soient triées
Puis faisons les totaux des ventes de chaque entreprise.
Pour cela, il faut cliquez dans le menu « Donnée » puis sélectionner
« Sous-totaux »
35 Excel
Sous-totaux (2)
Nous pouvons alors choisir :
Le champ « A chaque changement de : » on sélectionne sur
quel champs nous souhaitons un sous total. Ici par entreprise.
« Utiliser la fonction » : plusieurs fonctions sont à notre
disposition mais celle que nous allons utiliser est bien
entendu « Somme »
« Ajouter un sous-total à : » ici, il nous faudra cocher
«Vente » pour totaliser les ventes par entreprise
« Remplacer les sous-totaux existants : il est préférable de
laisser cette case cochée. Si vous avez déjà utilisé la fonction
Sous-totaux pour obtenir d'autres résultats, ceux-ci seront
donc effacés au bénéfice des nouveaux calculs.
Saut de page entre les groupes : cette option permet
d'obtenir, automatiquement, une page par entreprise. Soyez
prudents dans son utilisation. Ici, nous n'avons que 9
entreprise (donc au minimum 9 pages), mais si nous en avions
eu 150, cela n'aurait pas été sans incidence !
Synthèse sous les données : les résultats sont groupés à
chaque changement d'entreprise
36 Excel
Filtres élaborés
la grande différence entre un filtre automatique et un filtre élaboré est que ce
dernier permet la sélection des champs de résultats .
1. Préparer deux zones: zone résultats et zone critère.
2. Activez la commande « Données - Filtre Avancée »
3. On peut activez « Copier vers un autre emplacement » si on désire conserver intact les
données d'origines dans le champ Plages, sélectionnez vos données
4. dans le champ Zone de critères, sélectionnez votre zone de critère. Attention : ne
sélectionnez que les lignes non vides de votre zone de critères. Ceci est extrêmement
important : si vous sélectionnez une ligne de critères vierge, Excel considérera que vous
souhaitez sélectionner l'intégralité de votre base ;
5. cochez Extraction sans doublon si vous ne souhaitez pas avoir plusieurs fois le même
enregistrement.
37 Excel
Filtres élaborés
Les filtres « manuels » sont des cellules contenant le filtre. On les appels
« zones de critères »
Il est souvent conseiller d’avoir une zone de critères (cellules content les
filtres) puis une zone de données (extraction)
Plusieurs critères sur une même ligne : ET
Plusieurs critères sur des lignes différentes : OU
Exemple :
38 Excel
Les tableaux croisés
dynamiques
39 Excel
Objectif
But par l’exemple :
A partir d'un tableau recensant par exemple toutes les commandes de
l'année, les tableaux croisés dynamiques permettent d'obtenir, des tableaux
statistiques.
Voici, à titre d'illustration, quelques exemples de résultats qu'il est possible
d'obtenir
le nombre ou le montant des commandes pour chaque client ou type de produits (en
valeur absolue ou bien même en %) ;
le montant ou le nombre de commandes traité par chaque employé ;
le montant généré par chaque produit vendu, soit dans l'année, soit par mois ou par
trimestre.
40 Excel
Création d’un tableau croisé
Pour débuter, vous devez donc disposer d'un fichier de données.
Exemple :
Ensuite, on sélection le menu « Données », « rapport de tableaux
croisés dynamiques »
Nous passons à la création du croisement (forme de d’auto-jointure)
41 Excel
Création d’un tableau croisé (2)
Il vous faut sélectionner votre fichier de données. Si la zone contenu
dans le champ Plage n'est pas la bonne, effacez son contenu et activez
votre feuille de calcul. Sélectionnez ensuite l'intégralité de votre
tableau ;
cliquez sur Suivant
42 Excel
Création d’un tableau croisé (3)
Exemple : nom des sociétés dans « lignes » et « PrixTotal » dans données
Par défaut nous avons « Somme » des « PrixTotal » mais nous pouvons aussi
avoir :
MOYENNE
NOMBRE
MIN ou MAX
43 Excel
Création d’un tableau croisé (5)
Dans notre cas :
Puis on accepte la création du tableau et on obtient :
44 Excel
Création d’un tableau croisé (6)
Si on double-clic sur le
champs
« NomProduit » :
Et on peut faire la
même chose pour
chacun des autres
champs…
Attention, ne pas
oublier de cliquer sur
le bouton ! si on
modifie les données
(actualiser…)
45 Excel
Création d’un tableau croisé (7)
Si on prend le tableau suivant :
On obtient alors :
46 Excel
47 Excel
