Excel Utilisation possible d un tableur Tenir ses

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 A 13 ou B 45. • Un classeur est composé de plusieurs feuilles de calcul. – La casse A 6 de la feuille 2 est référencée par : Feuil 2!A 6

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.

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

Copier/coller des formules : adressage relatif • Ce que mémorise Excel, c'est le déplacement à faire pour aller de la case C 9 : – à la case C 8 : une case à gauche – à la case C 5 : une case à gauche et 3 cases vers le haut • Recopiée dans la cellule D 9, on obtient : – une case à gauche : C 9 – une case à gauche et 3 cases vers le haut : C 6 • La formule devient donc =C 9*(1+C 6) qui n'est pas celle que nous souhaitions. – laisser de la place pour ajouter des articles. 9

Structuration d'une feuille de calcul (3) La c 5 s’est elle aussi déplacée 10

Adressage absolu : • Formule souhaitée : =C 9*(1+C 5) • Il faut donc indiquer à Excel que : – C 8 est bien une référence relative (un déplacement). Ainsi, C 8 deviendra C 9 lors du copier/coller; – C 5 désigne la case C 5, pas la case située 1 case à gauche et 3 cases vers le haut. Ainsi, C 5 sera inchangée lors du copier/coller. • Solution : préfixer lignes et colonnes par $. On parle alors d'adressage absolu • Notre formule devient : =C 8*(1+$C$5) 11

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.

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

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

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 18

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. 19

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 macro au 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(A 1: C 12; « >10 » ) • Fonction NB. SI : Exemple Complet – On d₫sire compter le nombre de jours de présence de chaque membre d'une équipe :

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(texte 1; texte 2; . . . ) – Exemple : – Attention aux espaces

Les opérations sur les chaînes (2) • Minuscule / Majuscule – Impose la casse d'un texte – Syntaxe : =minuscule(Texte) – Exemple : • Nom. Propre – Met en majuscule la 1 re 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 : Recherche. V • 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 : =Recherche. V(valeur; zone; numéro_colonne)

Fonctions de recherche : Recherche. H • 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 : =Recherche. H(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 Critères : et Le résultat de votre filtre

Les filtres élaborés Critère : ou Le résultat de votre filtre

Les erreurs de saisie En cas d ’erreur de saisie : Vous pouvez écrire un message d ’alerte ou d ’erreur. Sur cette exemple : Les notes sont >0 et <=20

Les grilles Insertion/Nom/Etiquette

Grille Message de Micro Excel

Grille

Boutons Affichage/barred’outils/Formulaires Bouton droit Zone de liste modifiable

boutons

boutons Renvoie la position de l ’é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 • Classeur 3 fait référence à UE 1 et UE 2

Référence Liée • 1 - sélectionner dans le fichier UE 1 puis coller dans Module 3 • 2 - ou, écrire le chemin ='C: Mes documentsDupontLicence administrationNotes20022003Nouveau dossier[UE 2. xls]notes'!$F$2

Un petit dictionnaire