Excel. Utilisation possible d un tableur Tenir ses comptes personnels : salaires, impôts, dépenses...

Excel

Utilisation possible d ’un tableur

• Tenir ses comptes personnels : salaires, impôts, dépenses et recettes diverses...• Tenir les comptes d'une (petite) association.• Éditer divers types de plannings (classes, associations, petites entreprises...). Calculer

automatiquement des états de services.• Pour un enseignant :

– Saisir des notes – calculer automatiquement des moyennes, le rang des élèves etc. – Éditer des relevé₫s de notes personnalisées + PV de jury.

Feuille de calcul : les cellules

• Une feuille de calcul est composée de cellules.• Les cellules sont organisées en tableau.• Une cellule a des coordonnées dans la feuille. Par exemple A13 ou

B45.• Un classeur est composé de plusieurs feuilles de calcul.

– La casse A6 de la feuille 2 est référencée par : Feuil2!A6

Le classeur

Le classeur est composéde plusieurs feuilles que vous pouvez renommer, déplacer ...

Le classeur est composéde plusieurs feuilles que vous pouvez renommer, déplacer ...

Le contenu des cellules

• Chaque cellule peut contenir divers objets :– Des nombres (entiers, ou décimaux).– Du texte. Par exemple :

• Des noms de personnes.• Des noms de produits...

– Des dates.– Des formules qui peuvent faire référence :

• à des cellules de la feuille et/ou• à des cellules d'une autre feuille.

Les formats des cellules

• On peut spécifier le format d'une cellule (menu Format). Ex. :

– Nombre simple, mon₫taire, etc...

– Ces formats sont disponibles sous plusieurs variantes.

• Détermine le « type » du contenu mais ne change pas la valeur.

Exemple de formats de cellules :

Nombre : 189898Monétaire : 10 000,00 FPourcentage : 18.6%Date : sam 18 déc 99

Le format date et ses variantes :

10/12/1998jeudi 10 décembre 199810 décembre 199810 déc 1998

...

B C D E F G5 Date facture : 13/09/200167 Objet Prix HT Prix TTC8 Vélo 3 000,00 F 3 618,00 F "- - - - >9 Balles 100,00 F 120,60 F "- - - - >

10 Chaus. 345,00 F 416,07 F1112 Total HT Total TTC13 3 445,00 F 4 154,67 F1415 Cout TVA : 709,67 F1617 Le magasin « Sportez- vous bien !» vous souhaite une bonne journée.

"=C8*(1+19.6/100)"=C9*(1+19.6/100)

Structuration d'une feuille de calcul (1) : facture

Critiques et améliorations possibles

• Il est important :– d'avoir des feuilles extensibles– de regrouper les constantes dans un tableau de bord de la feuille (la TVA dans notre cas)

pour faciliter la compréhension de la feuille et les modifications• on va :

– mettre le taux de TVA dans une cellule– utiliser la valeur de cette cellule dans les formules– corriger le taux de TVA

9

Copier/coller des formules : adressage relatif

• Ce que mémorise Excel, c'est le déplacement à faire pour aller de la case C9 : – à la case C8 : une case à gauche– à la case C5 : une case à gauche et 3 cases vers le haut

• Recopiée dans la cellule D9, on obtient : – une case à gauche : C9– une case à gauche et 3 cases vers le haut : C6

• La formule devient donc =C9*(1+C6) qui n'est pas celle que nous souhaitions.– laisser de la place pour ajouter des articles.

B C D E F G5 Taux TVA 19,60% Date facture : 13/09/200167 Objet Prix HT Prix TTC8 Vélo 3 000,00 F 3 588,00 F "- - - - > "=C8*(1+C5)9 Balles 100,00 F 100,00 F "- - - - > "<= Pb!

10 Chaus. 345,00 F 345,00 F "- - - - > "=C10*(1+C7) "<= Pb!1112131415 Total HT Total TTC16 3 445,00 F 4 033,00 F1718 Cout TVA : 588,00 F1920 Le magasin « Sportez- vous bien !» vous souhaite une bonne journée.

"=C9*(1+C6)

10

La c5 s’est elle aussidéplacée

Structuration d'une feuille de calcul (3)

11

Adressage absolu :

• Formule souhaitée : =C9*(1+C5)• Il faut donc indiquer à Excel que :

– C8 est bien une référence relative (un déplacement). Ainsi, C8 deviendra C9 lors du copier/coller;

– C5 désigne la case C5, pas la case située 1 case à gauche et 3 cases vers le haut. Ainsi, C5 sera inchangée lors du copier/coller.

• Solution : préfixer lignes et colonnes par $. On parle alors d'adressage absolu• Notre formule devient : =C8*(1+$C$5)

B C D E F G5 Taux TVA 19,60% Date facture : 13/09/200167 Objet Prix HT Prix TTC8 Vélo 3 000,00 F 3 588,00 F "- - - - > "=C8*(1+$C$5)9 Balles 100,00 F 119,60 F "- - - - >

10 Chaus. 345,00 F 412,62 F "- - - - > "=C10*(1+$C$5)1112131415 Total HT Total TTC16 3 445,00 F 4 120,22 F1718 Cout TVA : 675,22 F1920 Le magasin « Sportez- vous bien !» vous souhaite une bonne journée.

"=C9*(1+$C$5)

Structuration d'une feuille de calcul : version définitive

Manières de désigner une case dans une formule :

• En désignant sa position par rapport à la case courante. On indique le déplacement qu'il faudrait faire pour rejoindre la case. On parle d'adressage relatif. C'est le mode d'adressage par d₫faut.

• En désignant la case par ses coordonnées. Cette désignation est indépendante de la cellule courante. On parle alors d'adressage absolu. Il faut utiliser le symbole $ pour cela.

• Il est possible de traiter distinctement lignes et colonnes et d'avoir la colonne en adressage relatif et la ligne en adressage absolu.

Adressage absolu vs adresse relatif : intérêt

• Permet de contrôler le comportement d'Excel lors des copier/coller de formule.

• Ne sert donc que lors de copier/coller.

• Dans de grands tableaux, c'est une fonctionnalité indispensable.

Exemple : calcul du coût de reprographie (1)

• On souhaite calculer le coût trimestriel des travaux de reprographie.• Les travaux peuvent être de 3 sortes: photocopie NB, photocopies

couleur ou reliures.• Pour chaque trimestre et chaque type de travail, on souhaite avoir : le

prix HT et TTC.• pour chaque trimestre, on souhaite avoir le prix total HT et TTC.

TVA 19,00%

totalnom Prix unitaire NB HT TTC NB HT TTCphoto nb 0,25 1700 425,00 505,75 10 2,50 2,98proto 12,00 15 180,00 214,20 1 12,00 14,28reliures 10,00 100 1000,00 1190,00 10 100,00 119,00

Exemple :calcul du coût de reprographie (2)

TVA 0,19

totalnom Prix unitaireNB HT TTC NB HT TTCphoto nb0,25 1700 =D8*$C8 =E8*(1+$C$4) 10 =G8*$C8 =H8*(1+$C$4)proto 12 15 =D9*$C9 =E9*(1+$C$4) 1 =G9*$C9 =H9*(1+$C$4)reliures 10 100 =D10*$C10 =E10*(1+$C$4) 10 =G10*$C10 =H10*(1+$C$4)

Exemple : calcul du coût de reprographie (3) : formules

18

Les macros (1)

• Une macro permet d'automatiser les tâches de routine.• Permet d'enregistrer et de rejouer une série d'actions.• Est désignée par son nom• Peut être affectée à une séquence de touches ou à l'entrée d'un menu• Une macro peut être définie dans le classeur courant ou dans Excel (accessible

dans tous les classeurs).– Rem : si le bouton n ’est pas visible faire Affichage/Barre d ’outils/personnaliser/arrêt de l ’enregistrement

19

Les macros : utilisation

• L'utilisation est très 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.

Les macros : mode relatif ou absolu

• Lorsque les positions des cellules modifiées doivent être mémorisées par rapport à la cellule courante : références relatives.

• Lorsque les positions des cellules sont mémorisées indépendamment de la position de la cellule courante : références absolues.

• On passe d'un mode à l'autre à volonté à l'aide d'un bouton visible lors de l'enregistrement des macros.

Macros Excel : programmation en VBA

• Une macro est une suite de commandes. • Excel mémorise cette suite de commandes sous la forme d'instructions du

langage de programmation Visual Basic• On peut créer ou modifier une macro directement en tapant un programme

visual basic• C'est un travail de programmation qui sort du domaine de l'utilisation de base

d'un outil bureautique

Les macros: exemple d'utilisation

Les macros

Insérer un bouton

Les macros

Affecter une macroau boutonAffecter une macroau bouton

Excel : Fonctions Avancées Fonction NB.SI

• Objectif : compter le nombre de cellules à l'intérieur d'une zone répondant à un critère.

• Syntaxe : =NB.SI(zone rectangulaire, « critère »)

• Exemple : =NB.SI(A1:C12; « >10 »)• Fonction NB.SI : Exemple Complet

– On d₫sire compter le nombre de jours de présence de chaque membre d'une équipe :

Nombre 123456Chaîne 123456

Les chaînes de caractères

• Rappels – Affichage à gauche (par défaut) : chaîne de caractères– Affichage à droite (par défaut) : valeur numérique

• Exemple

• Les opérations ne sont pas les mêmes :– Nombres : toutes les opérations numériques (+ - * / ...)– Chaînes : - concaténation,

- extraction de sous-chaînes,

- mise en majuscules...

Les opérations sur les chaînes de caractères (1)

• Quelques fonctions sur les chaînes de caractères :• CONCATENER :

– Met bout à bout plusieurs chaînes– Syntaxe : =CONCATENER(texte1;texte2;...)– Exemple :

– Attention aux espaces

Les opérations sur les chaînes (2)

• Minuscule / Majuscule– Impose la casse d'un texte– Syntaxe : =minuscule(Texte)– Exemple :

• NomPropre– Met en majuscule la 1re lettre de chaque mot– Syntaxe : =nompropre(Texte)– Exemple :

Les opérations sur les chaînes (3)

• NBCAR

– Compte le nombre de caractères contenus dans une chaîne (un espace est un caractère comme un autre)

– Syntaxe : =NBCAR(Texte)

– Exemple :

Les opérations sur les chaînes (4)

• EXACT– Teste si deux chaînes sont ₫gales– Syntaxe : =EXACT(chaîne 1; chaîne 2) – Exemple :

– Remarque :• Sensibilité à la casse• 2 chaînes vides sont égales

Les opérations sur les chaînes (5)

• SUPPRESPACE

– Supprime les espaces inutiles dans un texte

– Syntaxe : =SUPPRESPACE(Texte)

– Exemple :

Les opérations

• SUBSTITUE : recherche une chaîne dans un texte et la remplace par une autre

– Syntaxe : =substitue(texte;chaîne;nouvelle chaîne)

• TROUVE : renvoie la position d'une sous-chaîne dans une chaîne

– Syntaxte : = TROUVE(chaîne ; sous-chaîne)

Trier des données (1)

• 2 icônes : – Par défaut : trie par ligne

– Plusieurs colonnes : Sélectionner les différentes colonnes

Fonctions de recherche : RechercheV

• recherche une valeur (valeur numérique ou chaîne de caractères) dans la 1₩re colonne d'un tableau, et renvoie la valeur correspondante dans la colonne spécifiée

• Syntaxe : =RechercheV(valeur;zone;numéro_colonne)

Fonctions de recherche : RechercheH

• Recherche une valeur (valeur numérique ou chaîne de caractères) dans la 1ére ligne d'un tableau, et renvoie la valeur correspondante dans la ligne spécifiée

• Syntaxe : =RechercheH(valeur; zone; numéro_ligne)

Fonctions de recherche : Recherche

• Recherche une valeur (valeur numérique ou chaîne de caractères) dans une zone, et renvoie la valeur correspondante dans une autre zone

• Syntaxe : =Recherche(valeur;zone_recherche;zone_sortie)

Les filtres élaborés

Les filtres élaborés

Le résultat de votre filtreLe résultat de votre filtre

Critères : etCritères : et

Les filtres élaborés

Critère : ouCritère : ou

Le résultat de votre filtreLe résultat de votre filtre

Les erreurs de saisie

En cas d ’erreur de saisie :Vous pouvez écrire un message d ’alerteou d ’erreur.

En cas d ’erreur de saisie :Vous pouvez écrire un message d ’alerteou d ’erreur.

Sur cette exemple : Les notes sont >0 et <=20Sur cette exemple : Les notes sont >0 et <=20

Les grilles

Insertion/Nom/EtiquetteInsertion/Nom/Etiquette

Grille

Message de Micro ExcelMessage de Micro Excel

Grille

Boutons

Affichage/barred’outils/FormulairesAffichage/barred’outils/Formulaires

Zone de liste modifiableZone de liste modifiable

Bouton droitBouton droit

boutons

boutons

Renvoie la position del ’élément sélectionné

Renvoie la position del ’élément sélectionné

Modèles

Affichage

• Rendre la feuille sans quadrillage

Affichage

• Outil I Options I Affichage

Faire référence à des données d’un autre fichier

• Classeur3 fait référence à UE1 et UE2

Référence Liée

• 1- sélectionner dans le fichier UE1 puis coller dans Module3

• 2- ou, écrire le chemin

='C:\Mes documents\Dupont\Licence administration\Notes\2002-2003\Nouveau dossier\[UE2.xls]notes'!$F$2

Un petit dictionnaire