ENSAM -Casablanca
Formation Microsoft Office Excel
Mme S. EL HOUSSAINI
Tableur EXCEL
Excel est un logiciel de Microsoft permettant la création, la manipulation et l’édition de données organisées sous forme de tableaux.
Un tableur Excel est pour réaliser des tableaux de tous types et graphiques associés. Vous pourrez les insérer dans vos rapports, mémoires ou présentations.
Présentation EXCEL Mme S. EL HOUSSAINI
2
18/03/2020
Les principaux chapitres
Présentation EXCEL Mme S. EL HOUSSAINI
3
18/03/2020
1-Les bases Excel
Présentation EXCEL Mme S. EL HOUSSAINI
4
18/03/2020
Excel : Classeur, feuille, cellule
Présentation EXCEL Mme S. EL HOUSSAINI
5
18/03/2020
• Démarrer Excel avec le bouton ‘démarrer’ ou les raccourcis créés
• Une feuille de calcul apparaît sur l’écran
• Le fichier Excel se nomme un classeur
• Le fichiers Excel ont une extension en .xlsx
• Le classeur est composé de feuilles de calcul
• Les feuilles de calcul sont composées de cellules
La fenêtre Excel, notions importantes
Présentation EXCEL Mme S. EL HOUSSAINI
6
18/03/2020
Une cellule: intersection d’une colonne et d’une ligne, ici B8
La cellule active B10
Zone de nom et l’adresse de la zone active
Barre de formule
Onglets des feuilles
Barre d’état: affiche informations sur les commandes sélectionnées
Les sélections de cellules
Présentation EXCEL Mme S. EL HOUSSAINI
7
18/03/2020
• Cliquez sur une cellule pour la sélectionner
• Faire glisser le pointeur jusqu’à la dernière cellule de la plage pour sélectionner une plage
̶ La cellule supérieure gauche est active
̶ Les autres cellules de la plage sont en surbrillance
̶ La plage prend le nom indiqué en zone nom que vous pouvez modifier
• Par un appui sur ‘Ctrl’ et clic, vous pouvez sélectionner plages et cellules discontinues
• Une sélection de colonnes ou lignes se réalise par un clic sur les en-têtes colonnes ou lignes
Se déplacer dans une feuille
Présentation EXCEL Mme S. EL HOUSSAINI
8
18/03/2020
• Utiliser les touches de direction
• Utiliser les barres de défilement
•‘Entrée’ vous fait passer à la cellule dessous la cellule active
•‘Tab’ vous fait passer à la cellule à droite de la cellule active
Les feuilles et leurs onglets
Présentation EXCEL Mme S. EL HOUSSAINI
9
18/03/2020
En cliquant droit sur l’onglet, vous faites apparaître le menu contextuel: Vous pouvez insérer, supprimer, renommer, déplacer la feuille et l’onglet associé. Une couleur particulière peut être attribuée à l’onglet
La saisie des données dans Excel
Présentation EXCEL Mme S. EL HOUSSAINI
10
18/03/2020
• Deux catégories de données peuvent être saisies dans les cellules
̶ Données numériques comme 325
̶ Données alphanumériques comme Dupont_1
• Les données numériques se placent à droite, les autres à gauche
Saisir des données
Présentation EXCEL Mme S. EL HOUSSAINI
11
18/03/2020
Se positionner dans la cellule et valider la saisie
Pour modifier la saisie, modifiez son contenu dans la barre de formule
Pour supprimer le contenu d’une cellule, sélectionner et touche ‘Suppr’
La saisie semi-automatique
Présentation EXCEL Mme S. EL HOUSSAINI
12
18/03/2020
• Reconnaissance au fil de la frappe, des chaînes de caractères déjà saisies dans la colonne, accepter ou non la proposition d'Excel
• Utiliser une liste de choix avec les valeurs déjà saisies, clic droit sur la cellule à remplir
Précisions sur les longueurs de saisie
Présentation EXCEL Mme S. EL HOUSSAINI
13
18/03/2020
Saisir 'date de naissance' en B2
Saisir '24/04/1974' en C2 (penser à utiliser la bonne touche pour aller de B2 en C2)
Affichage tronqué lorsque vous avez saisi des données dans C2 lorsque la colonne n'est plus assez large
Modifier largeur des colonnes et hauteur des lignes
Présentation EXCEL Mme S. EL HOUSSAINI
14
18/03/2020
• Méthode 1
̶ Clic sur bouton titre de la colonne ou ligne
̶ Vous pouvez sélectionner plusieurs boutons
̶ Clic droit et choisir largeur colonne ou hauteur de ligne
̶ Taper valeur en points, valider
• Méthode 2
̶ Placer pointeur sur bord droit du bouton de titre, le pointeur se transforme en
̶ Enfoncer bouton souris et glisser
Figer des colonnes et des lignes
Présentation EXCEL Mme S. EL HOUSSAINI
15
18/03/2020
• Pour que les lignes et colonnes des étiquettes restent visibles à l’écran quelle que soit la taille du tableau, figer les colonnes et lignes
•‘Affichage/Fenêtre/Figer les volets’
• Pour libérer, ‘Affichage/Fenêtre/Libérer les volets’
Masquer des lignes ou colonnes
Présentation EXCEL Mme S. EL HOUSSAINI
16
18/03/2020
• Sélectionner la ligne ou la colonne à masquer
•‘Format/Colonne/Masquer’ ou ‘Format/Ligne/Masquer’
• Pour démasquer se positionner sur les lignes ou colonnes adjacentes à la ligne ou colonne masquée et ‘Format/Ligne (colonne)/Afficher’
• Vous pouvez aussi masquer des feuilles par Format/Feuille/Masquer (le réaffichage s’obtient par Format/Feuille/Afficher)
Insérer, supprimer des cellules
Présentation EXCEL Mme S. EL HOUSSAINI
17
18/03/2020
• Pour insérer des cellules, sélectionner le ou les cellules à l’emplacement choisi
• Accueil/Insertion/Cellule ou clic droit Insérer
• Choisir le type de décalage (vers le bas, à droite,…)
• Supprimer : Accueil/Supprimer ou clic droit Supprimer
• Attention Effacer le contenu ne fait qu’effacer le contenu des cellules, clic droit et effacer contenu
Insérer, supprimer colonnes ou lignes
Présentation EXCEL Mme S. EL HOUSSAINI
18
18/03/2020
• Clic sur le bouton du titre (A, B.. ou 1,2..)
• Clic droit et insérer (insère une colonne à gauche ou une ligne au-dessus) ou Insertion/Insérer/Colonne ou Ligne
• Une sélection de n lignes ou colonnes créera n lignes ou n colonnes
• Pour supprimer, clic sur le bouton du titre puis Insertion/Supprimer ou clic droit supprimer
Déplacer des cellules
Présentation EXCEL Mme S. EL HOUSSAINI
19
18/03/2020
• Sélectionner la plage
• Placer le pointeur sur le contour, le pointeur devient
• Cliquez et glissez la sélection à l'emplacement voulu
• Si vous voulez la déplacer vers une autre feuille, maintenez la touche Alt enfoncée et déplacez-vous vers l'onglet de la feuille souhaitée
• Si vous voulez la déplacer vers un autre classeur, ouvrez les deux classeurs et utilisez les mêmes techniques
Copier/Coller
Présentation EXCEL Mme S. EL HOUSSAINI
20
18/03/2020
Clic droit et ‘copier’ ou Ctrl+C
Clic droit et ‘Collage spécial’
Ce que vous pouvez copier dans la nouvelle cellule
Les boutons copier/coller de la barre standard
Des fonctions de collage spécial
Présentation EXCEL Mme S. EL HOUSSAINI
21
18/03/2020
Sauf 'largeur de colonne'
Opérations par rapport au contenu des cellules de destination
Colle une référence à la cellule source (absolue)
Inverse colonne et ligne
Utiliser le volet Office Presse-papiers
Présentation EXCEL Mme S. EL HOUSSAINI
22
18/03/2020
• Le volet office Presse-papiers garde en mémoire les 24 derniers éléments copiés
• Afficher le volet par Accueil/Presse-papiers
• Dans le volet, les dernières copies apparaissent, vous pouvez les coller, les supprimer
Les recopies incrémentées
Présentation EXCEL Mme S. EL HOUSSAINI
23
18/03/2020
• Excel permet des recopies intelligentes
• Sélectionner ‘Lundi’, recopier par poignée de recopie vers la droite, Mardi etc, s’inscrivent automatiquement
• Sélectionner ‘2’ et ‘4’, par poignée recopier vers le bas, Excel incrémente de 2 à chaque cellule
• Vous pouvez définir le type de recopie en cliquant sur le carré ‘options de recopie’ qui apparaît à droite de la sélection
2 - Mise en forme� Mise en page, impression
Présentation EXCEL Mme S. EL HOUSSAINI
24
18/03/2020
La mise en forme
Présentation EXCEL Mme S. EL HOUSSAINI
25
18/03/2020
• Des mises en forme peuvent être appliquées à une cellule ou une plage de cellule
• En général, le menu Format/Cellule permet d’appliquer une mise en forme après sélection des cellules concernées
• Le clic droit ‘Format de cellule’ permet aussi d’appliquer des mises en forme
• La duplication de format peut se réaliser par le bouton représentant un pinceau dans Accueil.
Format de cellule: Nombre
Présentation EXCEL Mme S. EL HOUSSAINI
26
18/03/2020
standard: par défaut, pas de mise en forme
nombre: choisir le nombre de chiffres après la virgule
monétaire: nb décimales, séparateur de milliers, symbole devise, montant négatif
Comptabilité : aligne les symboles monétaires et les décimaux dans une colonne.
Date : attribué automatiquement à une cellule de type jj/mm/AA ou de même type, choisir le sous type souhaité
Heure: format à appliquer à des saisies de type HH:MM:SS
Pourcentage : 00,00%
Spécial : comme code postal, numéro téléphone
Format de cellule: Alignement
Présentation EXCEL Mme S. EL HOUSSAINI
27
18/03/2020
Alignement du texte: centrer, aligner à droite, à gauche, gérer des retraits
Renvoyer à la ligne: si saisie plus longue que largeur colonne, renvoie à la ligne
Ajuster: Ajuste largeur colonne à la saisie
Fusionner : Fusionne des cellules pour des titres par exemple
Vous pouvez orienter le texte
Format de cellule: Police et Bordure
Présentation EXCEL Mme S. EL HOUSSAINI
28
18/03/2020
Choisir Police , Style, Taille et Couleur des caractères (peut être appliqué à une partie du texte seulement)
Choisir les bordures des cellules ou plages
Sélectionner Style et Couleur de la ligne à gauche de la fenêtre
Format de cellule: Remplissage et Protection
Présentation EXCEL Mme S. EL HOUSSAINI
29
18/03/2020
Vous pouvez choisir motif et couleur de fond
Le verrouillage s’opère si la feuille est protégée par
Onglet Révision, groupe Modification, Bouton Protéger la feuille
La barre mise en forme
Présentation EXCEL Mme S. EL HOUSSAINI
30
18/03/2020
Police
Alignement
Formats : �Pourcentage�Espace milliers�Symbole euro�Nb décimales en plus ou en moins
Retraits
Bordures
Couleur de fond
Couleur de police
Police plus grande ou plus petite
La mise en forme conditionnelle
Présentation EXCEL Mme S. EL HOUSSAINI
31
18/03/2020
Sélectionner la plage concernée
Activer Accueil/Mise en forme conditionnelle
La boîte mise en forme conditionnelle
Le résultat des conditions
La mise en page: Page
Présentation EXCEL Mme S. EL HOUSSAINI
32
18/03/2020
Mise en page/Marges/Marges personnalisées
Sous l'onglet page, il y a les options pour la présentation du classeur sur papier: l'orientation des pages à imprimer, à quelle échelle imprimer votre classeur…
La mise en page: Marges
Présentation EXCEL Mme S. EL HOUSSAINI
33
18/03/2020
Sous l'onglet Marges, vous pouvez déterminer les marges pour le classeur ainsi que ceux pour l'en-tête et le pied de page, choisir de centrer horizontalement et verticalement votre feuille de calcul sur la page, …
La mise en page: En-tête et pieds de page
Présentation EXCEL Mme S. EL HOUSSAINI
34
18/03/2020
En-têtes et pieds de page standard
En-têtes et pieds de page personnalisés. S'aider des boutons:�Police, Numéro de page, Nombre de page, Date, Heure, Chemin accès fichier, Nom fichier, Nom feuille, insérer image, Format image
La mise en page: Feuille
Présentation EXCEL Mme S. EL HOUSSAINI
35
18/03/2020
Vous pouvez redéfinir la zone d'impression
Sous l'onglet feuille, vous pouvez déterminer quel sera le bloc qui sera imprimé dans la case de zone d'impression. Au lieu d'imprimer tout le contenu d'une feuille de calcul, vous pouvez choisir d'en imprimer seulement une partie.
La mise en page: Zone d’impression et Sauts de page
Présentation EXCEL Mme S. EL HOUSSAINI
36
18/03/2020
• Définir la zone d'impression
̶ Par défaut, est égale à la zone active de la feuille (entre A1 et la dernière zone active)
̶ Sinon, Mise en page/Zone d'impression/Définir, la zone apparaît en pointillé
̶ Pour annuler la zone, Mise en page/Zone d'impression/Annuler
• Utiliser les sauts de page
̶ Sélectionner la ligne (colonne) au-dessus (à côté) de laquelle le saut de page doit être inséré
̶ Mise en page/Sauts de page/Insérer un saut de page
3 – Les formules�
Présentation EXCEL Mme S. EL HOUSSAINI
37
18/03/2020
Le principe des formules
Présentation EXCEL Mme S. EL HOUSSAINI
38
18/03/2020
• Une formule est une équation qui analyse des valeurs et renvoie un résultat
• Les formules commencent par le signe = suivi d’arguments, valeurs ou références à d’autres cellules reliées par des opérateurs arithmétiques.
Créer une formule
Présentation EXCEL Mme S. EL HOUSSAINI
39
18/03/2020
• Clic sur la cellule
• Taper = pour annoncer la formule
• Entrer 1er argument (nombre ou référence à une autre cellule)
• Entrer un opérateur arithmétique (+,-,*,/)
• Entrer un 2ème argument etc…
• Valider, le résultat s’affiche dans la cellule
Créer une formule
Présentation EXCEL Mme S. EL HOUSSAINI
40
18/03/2020
Formule = addition des deux cellules à gauche
Inscrire =C3+D3
Inscrire =�sélectionner la cellule C3 avec la souris
+
Sélectionner la cellule D3 et valider
Modifier une formule
Présentation EXCEL Mme S. EL HOUSSAINI
41
18/03/2020
Double-clic dans la cellule, la formule s'affiche. Modifiez la.
Clic dans la barre des formules et modifier
Copier une formule
Présentation EXCEL Mme S. EL HOUSSAINI
42
18/03/2020
Pointeur sur poignée. Le pointeur prend la forme d'un +
Tirer la poignée jusqu'à la cellule E5. Les autres totaux sont calculés pour Durand et Bernard
Copier les formules avec des références absolues et des références relatives
Présentation EXCEL Mme S. EL HOUSSAINI
43
18/03/2020
Sélectionner la poignée
Tirer la poignée vers le bas en maintenant le bouton de la souris enfoncé. Lâcher le bouton.
Des copier/coller du menu Accueil ou du menu contextuel peuvent aussi être utilisés
Formule =D2*(1+$B$7/100)
Références absolues et relatives
Présentation EXCEL Mme S. EL HOUSSAINI
44
18/03/2020
• Examinons les formules des cellules de la colonne E du tableau de la diapositive précédente
• E2=D2*(1+$B$7/100)
• E3=D3*(1+$B$7/100)
• En copiant E2 vers E3, D2 est devenue D3, $B$7 est restée fixe
• Les cellules de type D2 sont des cellules relatives et parcourent le même chemin que la cellule copiée
• Les cellules de type $C$7 sont des cellules absolues, elles ne changent pas lors d’une copie
• Le $ indique que la valeur qui suit ne doit pas être modifiée lors d’une copie, nous pouvons faire référence à des cellules mixtes comme C$7 colonne relative et ligne fixe (ou l’inverse $C7 colonne fixe et ligne relative )
Nommer des cellules et des plages de cellules
Présentation EXCEL Mme S. EL HOUSSAINI
45
18/03/2020
• Les noms pour une cellule ou une plage de cellules peuvent être utilisés dans les formules. Ils apportent de la clarté.
• Les noms définis peuvent être utilisés dans tout le classeur ce qui signifie qu'un nom est défini pour tout le classeur.
• Il ne peut exister qu'une seule cellule ou plage de cellules associée à un nom.
Les cellules nommées, référence par nom
Présentation EXCEL Mme S. EL HOUSSAINI
46
18/03/2020
Au lieu de désigner une cellule par des coordonnées, on peut utiliser un nom en tapant le nom dans la fenêtre d'édition des noms.
L'utilisation de la référence par nom procure deux avantages:
• Les formules deviennent plus lisibles
• La référence absolue de la cellule devient transparente.
Les cellules nommées, référence par nom
Présentation EXCEL Mme S. EL HOUSSAINI
47
18/03/2020
Je nomme la cellule C7 TVA
Je crée la formule pour la zone F3 en faisant référence à la cellule TVA
Je copie F3 vers les deux cellules du bas
TVA est une cellule absolue (vérifier les formules des cellules F4 et F5)
les erreurs…
Présentation EXCEL Mme S. EL HOUSSAINI
48
18/03/2020
Si la formule saisie est incorrecte, un message d’erreur est affiché dans la cellule et commence par ‘#’
Avez-vous deviné l’erreur?
La barre Vérification des erreurs par Formules/audit de formules peut vous aider
Les significations des valeurs d'erreur�
Présentation EXCEL Mme S. EL HOUSSAINI
49
18/03/2020
Valeur erreur | Signification |
#VALEUR | Type d'argument qui ne convient pas (dans fonction) |
#DIV/0 | Lorsqu'un nombre est divisé par 0 |
#NOM? | Ne reconnaît pas une saisie sous forme de texte, nom inexistant par exemple |
#N/A | Une valeur n'est pas disponible pour une valeur ou un fonction |
#REF! | Une référence de cellule n'est pas valide (suite à suppression par exemple) |
#NOMBRE! | Une formule ou fonction contient des valeurs numériques non valides |
#NULL! | Spécification de l'intersection de 2 zones qui, en réalité, ne se coupent pas |
Les références circulaires
Présentation EXCEL Mme S. EL HOUSSAINI
50
18/03/2020
• La référence circulaire apparaît lorsqu’une formule fait référence à sa propre cellule ou encore que deux cellules contiennent chacune une formule faisant référence à l’autre
• En A1, saisir la formule =A1
• Cliquer sur OK la boîte référence circulaire apparaît et vous permet de repérer les cellules en cause
4 - Les fonctions�
Présentation EXCEL Mme S. EL HOUSSAINI
51
18/03/2020
Fonctions mathématiques
Fonctions de texte
Fonctions logiques (SI, ET, OU)
Autres
Calculer avec des fonctions
Présentation EXCEL Mme S. EL HOUSSAINI
52
18/03/2020
• Les fonctions d’Excel sont des mots réservés que l’on peut taper dans une formule pour obtenir facilement un résultat.
• Toutes les fonctions d’Excel utilisent des parenthèses
• Entre ces parenthèses, on précise les contraintes du calcul : arguments
• Les arguments sont séparés par le signe point-virgule
Calculer avec des fonctions
Présentation EXCEL Mme S. EL HOUSSAINI
53
18/03/2020
• Cliquez sur la cellule
• Taper =, le nom de la fonction, l’argument ou la plage à insérer dans la fonction
• Après la frappe de la 1ère parenthèse, une aide sur la saisie des arguments de la fonction est affichée
Insérer une fonction
Présentation EXCEL Mme S. EL HOUSSAINI
54
18/03/2020
Formules/ ou
Boîte 'Insérer une fonction'
Rechercher une fonction ici ou sélectionner une catégorie de fonction ou 1er caractères de la fonction dans la zone
Description de la fonction choisie, plus d'explications sont possibles en cliquant sur le lien, valider
Saisir les arguments de la fonction, valider
Autre méthode d'insertion de fonctions
Présentation EXCEL Mme S. EL HOUSSAINI
55
18/03/2020
Bouton somme.�La petite flèche permet de choisir d'autres fonctions
Utilisation de fonctions
Présentation EXCEL Mme S. EL HOUSSAINI
56
18/03/2020
=SOMME(E3:E5)
=SOMME(plage_A) ou =SOMME(B12:D16)
=MOYENNE(plage_A)
=MIN(plage_A)
Calcul automatique de plage
Présentation EXCEL Mme S. EL HOUSSAINI
57
18/03/2020
Plage sélectionnée
Calcul automatique dans la barre d’état
Type de calcul à choisir par clic droit sur la zone
Fonctions courantes d’Excel
Présentation EXCEL Mme S. EL HOUSSAINI
58
18/03/2020
Fonction | Description | Exemple |
SOMME | Somme des arguments | =SOMME(arg) |
MOYENNE | Valeur moyenne des arg | =MOYENNE(arg) |
NB | Nombre de valeurs dans arg | =NB(arg) |
MAX | Plus grande valeur dans arg | =MAX(arg) |
MIN | Plus petite valeur dans arg | =MIN(arg) |
SI | Fonction Conditionnelle | =SI(arg) |
Fonctions majeures d’Excel
Présentation EXCEL Mme S. EL HOUSSAINI
59
18/03/2020
• Les fonctions mathématiques
• Les fonctions de dates et d’heures
• Les fonctions de texte
• Les fonctions logiques
• Les fonctions de recherche
• Les fonctions statistiques
• Les fonctions financières
Fonctions mathématiques
Présentation EXCEL Mme S. EL HOUSSAINI
60
18/03/2020
• =SOMME()
Additionne des cellules contiguës
• =SOMME.SI(plage;critère;somme_plage)
Additionne selon critère sur une plage
• =SOMMEPROD()
Additionne puis fait le produit (utile avec conditions, utiliser plutôt les tableaux croisés)
• =MOYENNE()
• =ARRONDI()
• =MAX()
• =MIN()
Fonctions dates et heures
Présentation EXCEL Mme S. EL HOUSSAINI
61
18/03/2020
• =AUJOURDHUI()
Renvoie la date du jour
• =JOUR(),=MOIS(),=ANNEE()
Renvoie jour, mois, année de la date indiquée entre parenthèses
• =DATE(année;mois;jour)
Calcul d’une date à partir d’une autre
• =DATEDIF(date1;date2; "y") ou"m" ou "j"
• =JOURSEM(date;2),NO.SEMAINE(date;2)
Renvoie numéro du jour ou de la semaine
• NB.JOURS.OUVRES(x;y;z),SERIE.JOUR.OUVRE(x;y;z)
Calcul de jours ouvrés tenant compte des jours fériés
x date départ, y date fin ou nb jours, z jours fériés
• FIN.MOIS(date_départ;nb mois)
Donne dernier jour d’un mois à partir d’une date
Fonctions de dates, calculs sur les dates
Présentation EXCEL Mme S. EL HOUSSAINI
62
18/03/2020
• Afficher la date du jour dans un texte
="Nous sommes le "&TEXTE(AUJOURDHUI();"jjjj jj mmmm aaaa")
• Ecrire le mois en lettres (cellule contient 1 à 12)
=TEXTE("1/"&A1;"mmmm")
• Les fonctions 'plafond', 'plancher' sont intéressantes pour calculer des durées, convertir des décimales en heures, minutes…
• Une date est reconnue si la saisie est de type jj/mm/aa ou jj-mm-aa ou de type 23 juillet 2006 ou jj/mm
• Les heures sont reconnues si la saisie est de type HH:MM ou HH: ou HH:MM:SS…
Remarques :
• Pour le tableur, une date est une valeur numérique. Il est donc possible de l’inclure dans une formule.
• Excel offre la possibilité d’afficher une date sous plusieurs aspects.
• Dans le menu Format/Cellule vous pouvez choisir les formats prédéfinis de dates.
Fonctions de texte
Présentation EXCEL Mme S. EL HOUSSAINI
63
18/03/2020
• MAJUSCULE(),MINUSCULE()
Convertit en majuscules, minuscules la cellule ou texte indiqué dans les parenthèses
• NOMPROPRE()
Met en majuscule la 1ère lettre du texte de la cellule ou du texte indiqué
• CNUM()
Transforme une cellule d’un format texte en un format nombre
Attention une seule cellule entre parenthèses.
Les expressions texte sont construites à l'aide de l'opérateur & qui permet de concaténer (mettre bout à bout) deux chaînes de caractères.
Fonctions logiques
Présentation EXCEL Mme S. EL HOUSSAINI
64
18/03/2020
• =SI(test, alors, sinon)
Comparaison à effectuer, action à faire si le résultat est positif, action à faire si le résultat est négatif
• ET(cond1;cond2…)
Toutes les conditions sont satisfaites (champ test du SI())
• OU(cond1;cond2…)
Au moins une des condition est satisfaite
Fonction SI
Présentation EXCEL Mme S. EL HOUSSAINI
65
18/03/2020
La fonction SI
̶ SI(Test; expression si Test=vrai; expression si Test=faux)
̶ Opérateurs logiques dans test
̶ Numériques, chaînes de caractères, calculs dans expression
"chaîne de caractères" | " " |
Opérateur de concaténation | & |
(formule de calcul) | (C4-B4) |
= | Egal à |
> | Supérieur à |
< | Inférieur à |
>= | Supérieur ou égal à |
<= | Inférieur ou égal à |
<> | Différent de |
Test sur date | Utiliser la fonction DATE(année;mois;jour) |
Test sur chaîne caractères | Encadré par des ", comparaison ordre alphabétique |
Exemple d'utilisation des fonctions logiques
Présentation EXCEL Mme S. EL HOUSSAINI
66
18/03/2020
Je veux mettre un commentaire conditionnel du montant restant à payé ou facture réglée
=SI((D6-E6)>0;"attention reste à payer "&(D6-E6)&" €";"Facture réglée")
Pour les factures non réglées entièrement dont le numéro commence par F uniquement, je veux surveiller celles dont l'échéance est inférieure ou égale au 11/05/2007
=SI(ET(C6<=DATE(2007;11;5);D6-E6>0;GAUCHE(B6;1)="F");"à surveiller";"RAS ou facture de type D")
Fonctions de recherche
Présentation EXCEL Mme S. EL HOUSSAINI
67
18/03/2020
• =RECHERCHEV(),=RECHERCHEH()
̶ (valeur_cherchée,table_matrice,n°index_colonne(ou ligne),0 ou 1)
• =EQUIV()
̶ Renvoie la position d’une valeur dans la matrice
• =INDEX()
̶ Va chercher une valeur dans une matrice (matrice,n°_ligne,n°_colonne)
• =CHOISIR()
̶ (Cellule,si cellule=1 alors valeur=x, si cellule=2 alors valeur=y etc…)
Fonctions statistiques
Présentation EXCEL Mme S. EL HOUSSAINI
68
18/03/2020
• =NB.SI(plage,critère)
̶ Compte le nombre de cellules non vides répondant au critère mentionné
• =NB.VAL(plage)
̶ Compte le nombre de cellules non vides de la plage mentionnée
• =BDSOMME(base;champ;critère),
• =BDMOYENNE(),=BDMAX(),=BDMIN()
̶ Base de donnée, champ et critère sont des plages (peuvent être nommées), un champ peut être une colonne nommée par son étiquette entourée de double côtes
Fonctions statistiques
Présentation EXCEL Mme S. EL HOUSSAINI
69
18/03/2020
• =NB.SI(plage,critère)
̶ Compte le nombre de cellules non vides répondant au critère mentionné
• =NB.VAL(plage)
̶ Compte le nombre de cellules non vides de la plage mentionnée
• =BDSOMME(base;champ;critère),
• =BDMOYENNE(),=BDMAX(),=BDMIN()
̶ Base de donnée, champ et critère sont des plages (peuvent être nommées), un champ peut être une colonne nommée par son étiquette entourée de double côtes
Fonctions financières
Présentation EXCEL Mme S. EL HOUSSAINI
70
18/03/2020
• =AMORLIN(Coût;Valeur_res;durée)
̶ Amortissement linéaire d’un bien pour une annuité complète
• =DB(Valeur achat;valeur résiduelle;durée en année;année du calcul;mois de la période)
̶ Amortissement dégressif à taux fixe
• =VC(Taux d'épargne;nombre d'épargne;montant déposé par périodes;montant au départ)
̶ Epargne
• =VPM(taux remboursement;Nombre de remboursements;Valeur de départ;montant final après le dernier remboursement;type de remboursement)
̶ Remboursement d’emprunt
5 - Bases de données dans Excel �
Présentation EXCEL Mme S. EL HOUSSAINI
71
18/03/2020
- Les listes de données
- Les tris
- Les sous-totaux
- Les tableaux croisés dynamiques
Bases de données dans Excel
Présentation EXCEL Mme S. EL HOUSSAINI
72
18/03/2020
• Dans toutes les entreprises, il y a des bases de données de type Oracle, SQL Server, Sybase, Access, Excel, etc.
• Les fonctionnalités d’Excel sont parmi les plus simples et les plus puissants outils pour analyser des données saisies manuellement ou importées dans Excel à partir des différentes bases de données.
• Pour avoir accès aux différentes fonctionnalités de base de données d’Excel, Excel doit reconnaitre votre ensemble de données comme une base de données. Des conditions doivent être respectées comme exemple: une seule ligne de cellules titres.
Bases de données dans Excel
Présentation EXCEL Mme S. EL HOUSSAINI
73
18/03/2020
À partir du moment où Excel reconnait votre base de données, on peut:
Les listes de données
Présentation EXCEL Mme S. EL HOUSSAINI
74
18/03/2020
Les listes sont des ensembles d’enregistrements qui ont les mêmes types de données appelées ‘champ’
- Ex : Liste des patients avec champs ‘nom’ ‘prénom’ ‘adresse’… ou médicaments avec champs ‘libellé’ ‘catégorie’ ‘prix unitaire HT’…
Les listes de données
Présentation EXCEL Mme S. EL HOUSSAINI
75
18/03/2020
Ligne des étiquettes (nom de champ)
Une ligne par enregistrement, remplir chaque champ
Une liste personnalisée
Présentation EXCEL Mme S. EL HOUSSAINI
76
18/03/2020
Vous disposez de deux options pour créer une liste personnalisée.
- S'il s'agit d'une liste courte, vous pouvez taper les valeurs directement dans la boîte de dialogue.
- Si votre liste est longue, vous pouvez l'importer à partir d'une plage de cellules.
Créer une liste personnalisée en tapant �des valeurs
Présentation EXCEL Mme S. EL HOUSSAINI
77
18/03/2020
• Cliquez sur le bouton Microsoft Office , puis sur Options Excel.
• Cliquez sur la catégorie Standard, puis sous Meilleures options pour travailler avec Excel, cliquez sur Modifier les listes personnalisées.
• Dans la zone Listes personnalisées, cliquez sur Nouvelle liste, puis tapez les entrées dans la zone Entrées de la liste, en commençant par la première entrée.
• Appuyez sur ENTRÉE après chaque entrée.
• Lorsque vous avez achevé votre liste, cliquez sur Ajouter.
• Les éléments de la liste sélectionnée sont ajoutés à la zone Listes personnalisées.
• Cliquez deux fois sur OK.
Créer une liste personnalisée en tapant �des valeurs
Présentation EXCEL Mme S. EL HOUSSAINI
78
18/03/2020
Créer une liste personnalisée à partir �d'une plage de cellules
Présentation EXCEL Mme S. EL HOUSSAINI
79
18/03/2020
• Dans une plage de cellules, entrez les valeurs en fonction desquelles vous voulez effectuer le tri ou le remplissage, dans l'ordre souhaité, de haut en bas.
Sélectionnez la plage que vous venez de taper. Par exemple vous sélectionnez les cellules A1:A3.
• Cliquez sur le Bouton Microsoft Office, cliquez sur Options Excel, sur la catégorie Standard, puis sous Meilleures options pour travailler avec Excel, cliquez sur Modifier les listes personnalisées.
• Dans la boîte de dialogue Listes personnalisées, vérifiez que la référence de cellule de la liste d'éléments sélectionnée est affichée dans la zone Importer la liste des cellules, puis cliquez sur Importer.
• Les éléments de la liste sélectionnée sont ajoutés à la zone Listes personnalisées.
• Cliquez deux fois sur OK.
Supprimer une liste personnalisée�
Présentation EXCEL Mme S. EL HOUSSAINI
80
18/03/2020
• Cliquez sur le bouton Microsoft Office , puis sur Options Excel.
• Cliquez sur la catégorie Standard, puis sous Meilleures options pour travailler avec Excel, cliquez sur Modifier les listes personnalisées.
• Dans la zone Listes personnalisées, sélectionnez la liste à supprimer, puis cliquez sur Supprimer.
Le tri d'une liste de données
Présentation EXCEL Mme S. EL HOUSSAINI
81
18/03/2020
• Dans le menu Données, sélectionnons la commande TRIER.
• La première question est Ligne de Titre (oui ou non). Cette notion est importante puisqu’en cas de ligne de titre, la première ligne ne sera pas trier.
• Le tri peut se faire suivant 3 critères
• Excel ne tient pas compte des colonnes après une colonne vide pour effectuer le tri. Il faudra sélectionner manuellement l’ensemble des colonnes pour que le tri tienne compte des colonnes supplémentaires.
Le tri d'une liste de données
Présentation EXCEL Mme S. EL HOUSSAINI
82
18/03/2020
• Dans le menu Données, sélectionnons la commande TRIER.
• La première question est Ligne de Titre (oui ou non). Cette notion est importante puisqu’en cas de ligne de titre, la première ligne ne sera pas trier.
• Le tri peut se faire suivant 3 critères
• Excel ne tient pas compte des colonnes après une colonne vide pour effectuer le tri. Il faudra sélectionner manuellement l’ensemble des colonnes pour que le tri tienne compte des colonnes supplémentaires.
Le filtre automatique
Présentation EXCEL Mme S. EL HOUSSAINI
83
18/03/2020
• Filtrer des données, c’est isoler et ne voir que les enregistrement (lignes) qui nous intéressent.
• Les filtres sont un des outils d’analyse de données les plus simples et les plus puissants.
• Dans Excel, on trouve des filtres automatiques et des filtres élaborés.
• Pour filtre automatique, de petites flèches apparaissent à la droite de chacune des cellules titres.
• Vous pouvez utiliser les différentes valeurs uniques dans les listes déroulantes pour filtrer les données.
• Vous pouvez filtrer la base de données en fonction d’une seule valeur dans un seul champ en sélectionnant cette valeur dans la liste déroulante de son champ.
• Vous pouvez filtrer les enregistrement vides ou les enregistrements non vides.
Le filtre automatique
Présentation EXCEL Mme S. EL HOUSSAINI
84
18/03/2020
Cliquer sur une des flèches et choisissez un élément, seuls ces éléments apparaîtront
Positionner curseur sur une étiquette
Données/Trier et filtrer/Filtrer
Des flèches de liste déroulante apparaissent
Le filtre automatique personnalisé
Présentation EXCEL Mme S. EL HOUSSAINI
85
18/03/2020
Cliquer flèche sur Prix
Sélectionner ‘Filtres numériques’ / ‘Filtre Personnalisé’
Sélectionner les critères
Et : Vrai si deux conditions vraies
Ou : Vrai si une des deux conditions vraies
Ici seuls les enr. dont les prix se situent entre 13 et 25 s’afficheront
Le filtre élaboré (Avancé)
Présentation EXCEL Mme S. EL HOUSSAINI
86
18/03/2020
• Si on doit appliquer trois conditions à une colonne, utiliser des valeurs calculées comme critères ou copier des enregistrements vers un autre emplacement, on doit utiliser des filtres élaborés.
• Plusieurs critères placés sur une même ligne utilisent l’opérateur relationnel ET. Des critères placés sur des lignes différentes utilisent l’opérateur relationnel OU.
Le filtre élaboré (Avancé)
Présentation EXCEL Mme S. EL HOUSSAINI
87
18/03/2020
Créer une zone de filtre constituée de la ligne des étiquettes et des critères de filtre.
Cette zone peut être créée sur une autre feuille du classeur
Ex : je veux la liste des calculettes dont le prix > 20 et des stylos <15
Données/Trier et filtrer/Avancé
Positionner curseur dans la plage du tableau principal
Remplir plage et zone de critères (possible par sélection des zones concernées)
Validation des données
Présentation EXCEL Mme S. EL HOUSSAINI
88
18/03/2020
• La validation est très pratique lorsque vous préparez un modèle pour d’autres utilisateurs afin de réduire les erreurs.
• La validation fournie aussi un message pour guider les utilisateurs au moment de l’entrée de données.
Validation des données
Présentation EXCEL Mme S. EL HOUSSAINI
89
18/03/2020
• Vous pouvez ne permettre que l’insertion d’un certain type de données dans des plages (numériques de 1 à 1000)
• Sélectionner la plage
• Données/Validation des données
• Inscrire les critères
Toutes saisies dans la plage autre que des entiers entre 0 et 1000 seront interdites. Une alerte d’erreur sera émise (3ème onglet)�Un message d’aide de saisie peut être initié (2ème onglet)
Un formulaire de données
Présentation EXCEL Mme S. EL HOUSSAINI
90
18/03/2020
• C’est bien vrai. Vous pouvez créer de superbes formulaires dans Microsoft Excel. En utilisant les formulaires et les nombreux contrôles et objets que vous pouvez y ajouter.
• Types de formulaires dans Excel : formulaires de données, feuilles de calcul contenant des contrôles de formulaire.
Un formulaire de données
Présentation EXCEL Mme S. EL HOUSSAINI
91
18/03/2020
• Un formulaire de données propose un moyen pratique d’entrer ou d’afficher une ligne complète d’informations dans une plage ou une table sans faire défiler horizontalement. Vous pourrez vous rendre compte que l’utilisation d’un formulaire de données peut s’avérer plus simple que de vous déplacer d’une colonne à l’autre quand le nombre de colonnes de données est trop important pour qu’elles soient toutes affichées à l’écran.
• Le formulaire de données affiche tous les en-têtes de colonnes sous forme d’étiquettes dans une boîte de dialogue unique.
Un formulaire de données
Présentation EXCEL Mme S. EL HOUSSAINI
92
18/03/2020
Aller dans les options d’Excel : Barre d’outils Accès rapide (en haut à gauche d’Excel), puis choisir Options Excel (en bas à droite), puis dans Personnaliser, sélectionner Commandes non présentes sur le ruban et finalement, formulaire…
Feuille de calcul avec contrôles de formulaire
Présentation EXCEL Mme S. EL HOUSSAINI
93
18/03/2020
Les contrôles sont des objets qui affichent des données ou qui permettent aux utilisateurs d’entrer ou de modifier des données, d’exécuter une action ou de faire une sélection plus facilement. En règle générale, les contrôles rendent l’utilisation du formulaire plus facile. Les exemples de contrôles courants comprennent notamment les zones de liste, les cases d’option et les boutons de commande.
Feuille de calcul avec contrôles de formulaire
Présentation EXCEL Mme S. EL HOUSSAINI
94
18/03/2020
Créer un plan
Présentation EXCEL Mme S. EL HOUSSAINI
95
18/03/2020
• Le mode plan permet de regrouper des données ensemble et d'analyser les résultats.
• Un plan permet de visualiser aisément les titres, et d'accéder d'un clic aux données détaillées.
• Un plan est constitué de lignes de synthèse, chacune regroupant des lignes de détail, qui peuvent être affichées ou masquées. Une ligne de détail peut à son tour être ligne de synthèse.
Créer un plan
Présentation EXCEL Mme S. EL HOUSSAINI
96
18/03/2020
Sélectionner les lignes à grouper et 'Grouper’
Niveaux de Plan
Symboles du mode Plan
Les sous-totaux
Présentation EXCEL Mme S. EL HOUSSAINI
97
18/03/2020
• Les sous-totaux par catégories sont possibles (moyenne ou somme par exemple)
• Trier les données sur lesquelles les sous-totaux seront calculés
• Sélectionner une cellule de la plage
• Activer Données/Plan/Sous-totaux
Sous-totaux exemple
Présentation EXCEL Mme S. EL HOUSSAINI
98
18/03/2020
Trier par catégorie
Curseur dans plage
Données/Plan/Sous-totaux
Choisir type fonction et où positionner le sous-total, valider
Résultats avec possibilités de développer et réduire les niveaux
Tableaux croisés dynamiques
Présentation EXCEL Mme S. EL HOUSSAINI
99
18/03/2020
• Un tableau croisé dynamique est un analyseur de données. Ces données sont généralement issues d’une liste Excel mais peuvent également provenir de données externes.
• Il permet d’analyser en deux dimensions les données répétées sur de nombreuses lignes.
Tableaux croisés dynamiques
Présentation EXCEL Mme S. EL HOUSSAINI
100
18/03/2020
Ex:On vous demande le nombre d’entrées par département, détaillé par service
Tableaux croisés dynamiques
Présentation EXCEL Mme S. EL HOUSSAINI
101
18/03/2020
Rappel demande :�Nombre d’entrées par département détaillé par service
Nombre d’entrées = données
Département = lignes
Service = colonnes
Résultat après, ok et terminé
Tableaux croisés dynamiques
Présentation EXCEL Mme S. EL HOUSSAINI
102
18/03/2020
Tableaux croisés dynamiques
Présentation EXCEL Mme S. EL HOUSSAINI
103
18/03/2020
Un graphique croisé dynamique
Présentation EXCEL Mme S. EL HOUSSAINI
104
18/03/2020
Graphique croisé de notre exemple
Vous pouvez insérer des champs comme pour les tableaux
Vous pouvez modifier la présentation du graphique comme un graphique normal
6 – Les graphiques�
Présentation EXCEL Mme S. EL HOUSSAINI
105
18/03/2020
- Créer un graphique
- Affiner la présentation du graphique
- Courbe tendance
Graphique Excel
Présentation EXCEL Mme S. EL HOUSSAINI
106
18/03/2020
• La création d’un graphique Excel permet une visualisation de l’évolution de chiffres.
• Sélectionnez l’ensemble du tableau avec la souris et sélectionnez Groupe Graphiques dans l’onglet Insertion.
Graphique Excel
Présentation EXCEL Mme S. EL HOUSSAINI
107
18/03/2020
Pour notre exemple vous allez construire un graphique en colonnes (histogramme)
Sélectionnez la plage de cellule A3:D7 de votre tableau. Cliquez sur l’outil ‘Colonne’
Graphique Excel
Présentation EXCEL Mme S. EL HOUSSAINI
108
18/03/2020
Votre graphique apparait immédiatement sur votre feuille.
Vous pouvez à présent en améliorer la présentation
Graphique Excel
Présentation EXCEL Mme S. EL HOUSSAINI
109
18/03/2020
Mettre en page le graphique dans la feuille de calcul
Présentation EXCEL Mme S. EL HOUSSAINI
110
18/03/2020
• Déplacer le graphique
- Sélectionnez le graphique à déplacer en cliquant dessus.
- Amener le pointeur de la souris sur le graphique. Le pointeur se transforme en flèche.
- Faites glisser le graphique en maintenant le bouton gauche de la souris enfoncé.
• Modifier la taille de l’objet graphique
- Sélectionnez le graphique en cliquant dessus.
- Amener le pointeur de la souris sur un des carrés entourant le graphique. Le pointeur se transforme en double flèche
- Faites glisser le carré en maintenant le bouton gauche de la souris enfoncé.
• Supprimer le graphique
- Sélectionnez le graphique à supprimer en cliquant dessus.
- Utilisez le menu Edition – effacer- tous. (ou touche Suppr.)
Les courbes de tendance
Présentation EXCEL Mme S. EL HOUSSAINI
111
18/03/2020
Utiliser les courbes de tendances pour représenter la progression (ou la baisse) moyenne de vos données
Clic droit sur la série et 'ajouter une courbe de tendance'
Plusieurs types de courbes de tendance peuvent être choisies