Cours 3 Microsoft Excel

1
Cours 3Microsoft Excel
Université dAngers

• Adrien Goëffon
• Adrien.Goeffon_at_univ-angers.fr
• Bureau G206

2
Vocabulaire

• Classeur fichier Excel. Un classeur peut
  contenir plusieurs feuilles de calcul.
• Feuille de calcul zone composée de petits
  rectangles appelés cellules.
• ? Leur nom apparaît au bas de chaque feuille.
  Elles sont nommées par défaut Feuil1, Feuil2,
• Cellule intersection dune ligne et dune
  colonne. Un des nombreux rectangles dune feuille
  de calcul.
• Ligne rangée horizontale de cellules. Chaque
  ligne est repérée par un nombre.
• Colonne rangée verticale de cellules. Chaque
  colonne est identifiée par défaut par une ou
  plusieurs lettres.

3
Vocabulaire

• Quadrillage délimite les cellules. Il constitue
  une aide pour se repérer.
• ? Pour imprimer le quadrillage
• Fichier / Mise en page / Feuille / Impression /
  Quadrillage
• ? Pour ne pas afficher le quadrillage
• Outils / Options / Affichage / Fenêtres /
  Quadrillage

4
Vocabulaire

• Adresse de cellule emplacement dune cellule
  par référence à la lettre de sa colonne et au
  numéro de sa ligne .
• ? La cellule située dans le coin supérieur
  gauche de la feuille de calcul a comme adresse la
  référence A1
• ? Ladresse de la cellule active apparaît dans
  la première zone de la barre de formules

5
La Fenêtre Excel
Répéter/Annuler la dernière opération
Zone de contenu de la cellule
Barre de menus
Barre de titre
Barre doutils standard
Barre doutils mise en forme
Adresse de la cellule active
Colonne
Ligne
Cellule
Quadrillage
Barre détat
Nom de feuille de calcul
Cellule sélectionnée
6
Principales applications

• Tableaux de chiffres et de textes, plannings,
• Réalisation de calculs
• ? Enregistrement de transactions financières
• ? Contrôles de la valeur de stocks

7
Principales applications

• Gestion de données
• ? Tris, Recherches automatiques
• Graphiques
• Bases de données
• ? Fichiers dadresses

8
Texte / Valeurs

• Si on entre dans une cellule une suite de
  chiffres sans autre caractère, Excel le traitera
  comme un nombre
• Excel considèrera par défaut comme du texte
• ? Des nombres contenant un trait dunion
• ? Les numéros de sécurité sociale (contenant des
  espaces)
• Toute entrée commençant par des guillemets sera
  considérée comme du texte
• Pour effectuer un renvoi à la ligne dans une
  cellule Alt ?

9
Lignes et colonnes
Cliquer sur le chiffre ou la lettre correspondant
à la colonne ou à la ligne
Clic droit Insertion, suppression
10
Format de cellules

• Remarque Pour sélectionner des cellules non
  adjacentes
• Maintener la touche Ctrl enfoncée
• Sélectionner les plages de cellules voulues
• Format des nombres (Format / Cellule / Nombre)
• Nombre (préciser le nombre de décimales)
• Monétaire (décimales, symbole)
• Date (transforme des numéros de série en format
  de date)
• Heure
• Pourcentage (décimales)
• Fraction
• Scientifique (puissances de 10)
• Texte (ex 015)

11
Format de cellules

• Alignement
• Par défaut, le texte est aligné à gauche et les
  nombres à droite
• Pour modifier lalignement horizontal et vertical
  
• Format / Cellule / Alignement
• Il est également possible de fusionner des
  cellules adjacentes.
• Une plage de cellules nen forme alors plus
  quune.
• Police, Bordures, Motifs
• Proche de Word

12
Format de cellules

• Mise en forme conditionnelle
• Modifier la police, la bordure ou le motif dune
  cellule selon la valeur quelle contient
• Format des Lignes et des Colonnes
• (Format / Ligne Format / Colonne)
• Déterminer la longueur / hauteur
• Ajustement automatique
• Masquer / Afficher

13
Feuilles de calcul

• Options de base
• Insertion / Feuilles de calcul
• Edition / Supprimer une feuille
• ou Clic droit ? Insérer, Supprimer, Renommer,
  Déplacer
• Groupe de travail
• ? Sélectionner plusieurs feuilles
• Groupe de travail apparaît dans la barre de
  titre
• ? Toutes les modifications apportées le seront
  sur toutes ces feuilles
• ? Pour terminer Clic droit ? Dissocier les
  feuilles
• Fonctions utiles
• Tri (Données / Trier), Volets (Fenêtre / Figer
  les volets)

14
Protection

• Protection des cellules
• Outils / Protection
• Protéger une feuille de calcul
• Protéger un classeur
• Protéger un fichier
• Fichier / Enregistrer sous puis Outils / Options
  générales

15
Formules

• Une formule commence toujours par le signe
• Elle peut-être composée de différents éléments
• Références de cellules variables
• Constantes
• Opérateurs arithmétiques / -
• Opérateurs de comparaison lt gt lt gt
  ltgt
• Fonctions

16
Adresses

• Les adresses sont des références à des cellules
• Exemples
• C2 est la référence au contenu de la cellule
  nommée C2 (qui est en 2e ligne et 3e colonne)
• B5D9 désigne 2 cellules B5 et D9
• A3A9 désigne toutes les cellules de la 1e
  colonne comprises entre la 3e et la 9e ligne
  (A3, A4, A5, A6, A7, A8, A9)
• D12G12 désigne toutes les cellules de la 12e
  ligne comprises entre la 4e et la 7e colonne
  (D12, E12, F12, G12)
• B5D8 désigne les cellules B5, B6, B7, B8, C5,
  C6, C7, C8, D5, D6, D7, D8
• Prix!D1D4 désigne les cellules D1 à D4 de la
  feuille de calcul "Prix"
• Prix!D1Prix!D4 désigne les cellules D1 et D4 de
  la feuille de calcul "Prix"

17
Fonctions

• A partir de données, une fonction produit un
  résultat dun type bien précis

• Syntaxe nomfonction(liste de paramètres)
• Les paramètres sont séparés par des

18
Fonctions

• Excel 2000 dispose de 329 fonctions classées en
  différentes catégories
• Finances
• Date Heures
• Math Trigo
• Statistiques
• Recherche Matrices
• Base de données
• Texte
• Logique
• Informations

19
Fonctions simples

• Fonction SOMME (Math Trigo)

20
Fonctions simples

• Autres fonctions sutilisant comme SOMME
• MOYENNE
• ECARTYPE
• MIN
• MAX
• MEDIANE
• Ces fonctions sont dans la catégorie Statistiques

21
Fonctions simples

• Fonctions mathématiques simples (0 à 2 arguments,
  Math Trigo)
• ABS(x), SIGNE(x), ENT(x)Valeur absolue, Signe
  (-1, 0 ou 1), Partie entière
• FACT(x), RACINE(x), EXP(x), LN(x), LOG10(x)
• PUISSANCE(x,y), LOG(x,y)
• COS(x), SIN(x), TAN(x), ACOS(x), ASIN(x), ATAN(x)
• COSH(x), SINH(x), TANH(x), ACOSH(x), ASINH(x),
  ATANH(x)
• DEGRES(x), RADIANS(x)
• MOD(x,y) x Modulo y
• COMBIN(x,y) Cxy
• ALEA() Nombre aléatoire entre 0 et 1
• PI()

22
Fonctions simples

• Date Heure
• MAINTENANT()
• DATE(a,m,j), TEMPS(h,m,s)
• ANNEE(num), MOIS(num), JOUR(num),
• HEURE(num), MINUTE(num), SECONDE(num)
• num représente le numéro de référence dune date

23
Fonctions logiques

• Fonctions booléennes
• VRAI() , FAUX()
• NON(b) est vrai si b est faux, et inversement
• ET(b1b2bn) est vrai si ?i?1,,n, bi vrai
• OU(b1b2bn) est vrai si ?i?1,,n, bi vrai
• Expression logique SI(conditionvaleur.si.vrai
  valeur.si.faux)
• Une condition (test logique) est une expression
  dont la valeur est soit VRAI soit FAUX (ex 33
  est VRAI, 6gt10 est FAUX)
• Tout nombre non nul est considéré comme ayant la
  valeur VRAI
• Si la condition est VRAI, alors le traitement
  réalisé est valeur.si.vrai
• sinon on traite valeur.si.faux (0 si le 3e
  argument est absent)
• Une valeur peut être du texte, un nombre, une
  fonction

24
Fonctions logiques

• Exemple

Pour se résultat, on entre en E7 la fonction
suivante SI(C7gtD7"Objectif atteint""Retard
sur lobjectif") 
25
Recopiage

• Pour déplacer des données
• Sélectionner la ou les cellules puis les faire
  glisser
• Pour copier des données (cas général)
• Copier / Coller depuis les menus ou les
  raccourcis clavier (CtrlC, CtrlV)
• Pour créer une suite ou recopier des données vers
  des cellules contiguës en bas ou à gauche
• Sélectionner la ou les cellules, se placer en bas
  à droite de la sélection (poignée de recopie) et
  étirer la en bas ou à droite
• X continue en X X X X X X
• 1 2 continue par défaut en 3 4 5 6 7 (avec le
  bouton droit options)
• Novembre continue en Novembre Décembre Janvier

26
Collage spécial

• Permet de conserver uniquement
• Soit les formules
• Soit les valeurs
• Soit les formats
• Possibilité de coller des cellules sur d'autres
  en effectuant une opération
• Possibilité de copier en
• Retirant les cellules vides
• Modifiant le sens du tableau à recopier
  (transposée)
• Le collage spécial est accessible via Edition /
  Colage spécial

27
Références relatives

• La maîtrise du copier / coller de formules sur
  Excel est indispensable
• Excel modifie les références des cellules dans
  une formule lors dun recopiage.
• Il mémorise le déplacement à faire pour aller
  dune cellule à une autre et met les références
  de la formule à jour
• Exemple
• En D5 est la formule C32D4, on veut la copier
  en B8
• Le déplacement à effectuer de D5 vers B8 est
• -2 colonnes 3 lignes
• La nouvelle formule en B8 sera A62B7
• Il faut effectuer le même déplacement pour aller
  de D5 vers B8 que pour aller de C3 vers A6 ou de
  D4 vers B7

28
Références absolues

• Pour fixer la référence dune cellule lors dun
  recopiage, il faut utiliser le signe .
• A1 est une référence absolue car on a fixé la
  ligne et la colonne
• Cela signifie que lors dun recopiage, la
  référence se fera par rapport à la cellule et non
  par rapport au déplacement
• Exemple
• En D5 est la formule C32D4, on veut la
  copier en B8
• Le déplacement à effectuer de D5 vers B8 est
  toujours
• -2 colonnes 3 lignes
• La nouvelle formule en B8 sera C32B7
• Uniquement la référence à D4 a été modifiée

29
Références mixtes

• La référence mixte fixe soit la ligne, soit la
  colonne
• A1 et A1 sont deux références mixtes, car la
  ligne ou la colonne est en référence absolue, et
  l'autre en référence relative
• Exemple
• En D5 est la formule C32D4, on veut la
  copier en B8
• Le déplacement à effectuer de D5 vers B8 est
  encore
• -2 colonnes 3 lignes
• La nouvelle formule en B8 sera C62B4
• Uniquement la référence à D4 a été modifiée
• Ne pas confondre le copier / coller d'une cellule
  avec le copier / coller du contenu de la cellule

30
Le mode L1C1

• Il existe 2 manières de nommer une cellule
• A1 les colonnes sont représentées par des
  lettres et les lignes par des chiffres
• L1C1 à la fois les lignes et les colonnes sont
  représentées par des chiffres
• Pour activer ce mode Outils / Options /
  Général / Paramètres
• puis cocher "Style de référence L1C1"
• Attention
• Dans ce mode on commence par la ligne pour nommer
  une cellule
• Pour faire une référence relative, on marque
  explicitement le déplacement à réaliser (ex
  L(1)C(-2) signifie que l'on considère la valeur
  de la celule située une ligne en dessous et 2
  colonnes à gauche)
• L3C12 est une référence absolue, tandis que
  L(3)C12 est mixte

31
Graphiques

• Pour réaliser un graphique
• sélectionner une plage de données (avec légendes
  si possible)
• faire Insertion / Graphique
• Lassistant graphique vous guide dans le
  processus
• (type de graphique, légendes, données) et crée
  un premier graphique
• Le modifier en faisant un clic droit sur les
  objets le composant
• (format du graphique, échelles, titres,
  étiquettes de données)

32
Fonctions avancées

• Statistique
• NB(val1val2)
• Renvoie le nombre d'arguments contenant des
  nombres
• NBVAL(val1val2)
• Renvoie le nombre de valeurs non vides dans la
  liste des arguments
• NB.SI(plagecritère)
• Renvoie le nombre de cellules, de la plage,
  répondants au critère
• Exemple NB.SI(A1C10"gt10")
• NB.VIDE(plage)
• Renvoie le nombre de cellules vides de la plage

33
Fonctions avancées

• Recherche et matrice
• RECHERCHEV(val.cherchée tableau index.col
  val.proche)
• Recherche la valeur "val.cherchée" dans la
  colonne à l'extrême gauche d'une plage "tableau",
  puis renvoie la valeur dans la même ligne à la
  ligne "index.col".
• Pour trouver la valeur exacte, "val.proche" doit
  être FAUX.

34
Fonctions avancées

• Recherche et matrice
• RECHERCHEH(val.cherchée tableau index.ligne
  val.proche)
• Recherche la valeur "val.cherchée" dans la ligne
  supérieure d'une plage "tableau", puis renvoie la
  valeur dans la même colonne à la ligne
  "index.ligne".
• Pour trouver la valeur exacte, "val.proche" doit
  être FAUX.

35
Fonctions avancées

• Recherche et matrice
• RECHERCHE(val.cherchée zone.recherche
  zone.sortie)
• Recherche une valeur (valeur numérique ou chaîne
  de caractères) dans une zone, et renvoie la
  valeur correspondante dans une autre

36
Fonctions avancées

• Texte
• CNUM(texte)
• Convertit un texte en un nombre.
• CTXT(nombre décimales no-séparateur)
• Convertit un nombre en texte en l'arrondissant
  au nombre de décimales spécifiées.
• Si "no-séparateur" est omis ou égal à FAUX, le
  texte comprend des espaces, s'il est égal à VRAI,
  il ne contient pas d'espace
• NBCAR(texte)
• Renvoit le nombre de caractères d'un texte, y
  compris les espaces

37
Fonctions avancées

• Texte
• DROITE(texte nbcar)
• Renvoie les "nbcar" derniers caractères du texte
• GAUCHE(texte nbcar)
• Renvoie les "nbcar" premiers caractères du texte
• MAJUSCULE(texte)
• Convertit un texte en majuscules
• MINUSCULE(texte)
• Convertit un texte en minuscules
• NOMPROPRE(texte)
• Met en majuscules la première lettre de chaque
  mot du texte

38
Fonctions avancées

• Texte
• SUPPRESPACE(texte)
• Supprime tous les espaces de texte à l'exception
  des espaces simples entre les mots
• TROUVE(texte_cherché texte no_départ)
• Trouve un texte dans un autre texte, en
  commençant au caractère dont le numéro est
  précisé, et renvoie sa position
• STXT(texte no_départ nbcar)
• Renvoie un nombre déterminé de caractères du
  texte à partir de la position indiquée
• CONCATENER(texte1 texte2 )
• Met à bout plusieurs chaînes de caractères

39
Fonctions avancées

• Information
• ESTERREUR(valeur)
• Renvoie VRAI si la cellule contient une valeur
  d'erreur
• ESTNUM(valeur)
• Renvoie VRAI si la cellule contient une valeur
  numérique
• ESTTEXTE(valeur)
• Renvoie VRAI si la cellule contient une chaîne
  de caractères
• ESTVIDE(valeur)
• Renvoie VRAI si la cellule est vide
• NB.VIDE(plage)
• Renvoie le nombre de cellules vides dans la
  plage

40
Les valeurs d'erreur

• La cellule contient un nombre plus large à
  afficher que la largeur de la colonne
• VALEUR! Un type d'argument de la cellule est
  inapproprié
• DIV/0! La formule effectue une division par zéro
• NOM Mauvaise syntaxe dans la formule
• N/A Une valeur n'est pas disponible pour une
  formule
• REF! Une référence de cellule n'est pas valide
• NOMBRE! Un problème s'est produit avec un nombre
  dans une formule
• NUM! On spécifie une intersection nulle de deux
  zones

41
Tableaux croisés dynamiques

• Un tableau croisé dynamique est un tableau
  interactif qui permet d'effectuer une synthèse
  rapide et croisée à partir d'une liste de
  données.
• Données /
• Rapport de tableau croisé dynamique

42
Tableaux croisés dynamiques
43
Tableaux croisés dynamiques
44
Tableaux croisés dynamiques
45
Tableaux croisés dynamiques
46
Tableaux croisés dynamiques
47
Tableaux croisés dynamiques
48
Tableaux croisés dynamiques
49
Tableaux croisés dynamiques
50
Tableaux croisés dynamiques
51
Les macros

• Une macro est une suite de commandes et de
  fonctions écrite dans le langage Visual Basic
  pour Application (VBA).
• ? Elle permet d'automatiser une série de
  commandes ou de tâches que l'utilisateur effectue
  fréquemment.
• ? Une macro est désignée par son nom et
  peut-être définie dans le classeur courant ou
  dans Excel (accessible dans tous les classeurs)
• Il est possible de définir des macros sans
  apprendre le langage VBA.

52
Les macros

• L'utilisation d'une macro est proche de celle
  d'un magnétophone.
• On peut
• Lancer la création d'une macro grâce au sous-menu
  macro du menu outils
• Il est possible de faire une pause dans
  l'enregistrement d'une macro
• On arrête l'enregistrement en appuyant sur le
  bouton stop de l'enregistreur.

53
Les macros

• L'utilisation d'une macro est proche de celle
  d'un magnétophone.
• On peut
• Lancer la création d'une macro grâce au sous-menu
  macro du menu outils
• Il est possible de faire une pause dans
  l'enregistrement d'une macro
• On arrête l'enregistrement en appuyant sur le
  bouton stop de l'enregistreur.

54
Création d'une macro

• Outils / Macro / Nouvelle macro. Une boîte de
  dialogue saffiche.
• Dans la zone Nom de la macro, taper un nom
  explicite.
• Ne jamais mettre d'espace dans le nom.
• Dans la liste Enregistrer la macro dans,
  sélectionner Ce classeur. Classeur personnel vous
  permet de mettre la macro à disposition de tous
  vos classeurs.
• Après avoir cliqué sur OK, Excel va enregistrer
  toutes les actions et les transcrire en code.
• Exécuter les différentes commandes que vous
  voulez enregistrer. Si le bouton Référence
  relative est enfoncé, Excel enregistre en
  utilisant des références aux cellules relatives
  (A1), sinon il enregistre en références absolues
  (A1)
• Cliquez sur le bouton Arrêter l'enregistrement

55
Utilisation d'une macro

• Première méthode
• ? Aller dans Outils / Macro / Macros
• ? Dans la boîte de dialogue Affecter une macro,
  sélectionner une macro dans la liste.
• ? Cliquer sur le bouton Exécuter.

56
Utilisation d'une macro

• Seconde méthode
• ? Aller dans Affichage / Barres d'outils /
  Formulaire
• ? Cliquer sur Bouton, puis dessiner un rectangle
  sur la feuille de calcul
• ? Dans la boîte de dialogue Affecter une macro,
  sélectionner une macro dans la liste puis cliquer
  sur Ok
• ? Sélectionner le texte du bouton et taper un
  texte plus explicite
• ? La macro s'exécutera à chaque clic sur le
  bouton

57
Copier un document Excel dans Word

• Dans Excel
• ? Sélectionner une feuille ou une plage de
  cellules puis Copier
• Dans Word
• ? Faire Edition / Collage spéciale
• ? Choisir Coller en tant que Feuille de calcul
  Microsoft Excel objet

58
Aide

• Aussi bien dans Word ou Excel, pensez à utiliser
  l'aide
• ? Disponible dans le menu '? ' ou bien par a
  touche F1
• ? Il suffit de saisir des mots-clés à
  l'assistant puis de naviguer dans l'aide

59
Open Office
http//www.openoffice.org Suite gratuite de
Bureautique
60
Cours 3Microsoft Excel
Université dAngers

• Adrien Goëffon
• Adrien.Goeffon_at_univ-angers.fr
• Bureau G206