Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

EXCEL INTERMEDIAIRE MME AZZOUG Page 1 SUPPORT DE COURS EXCEL 2013 EXCEL INTERME

EXCEL INTERMEDIAIRE MME AZZOUG Page 1 SUPPORT DE COURS EXCEL 2013 EXCEL INTERMEDIAIRE MME AZZOUG Page 2 TABLE DE MATIÈRES I. LES REFERENCES DES CELLULES ................................................................................. 3 A. Quelques généralités .......................................................................................................................................................... 3 B. Règles de recopie par défaut d’une formule ...................................................................................................................... 3 Comment marche les références relatives ou absolues dans Excel ...... 5 1- Comment utiliser une référence relative Excel ? ........................................................................................................ 5 2- Comment utiliser une référence absolue Excel ?........................................................................................................ 7 3- Comment définir une ligne absolue dans Excel ? Dans ce cas, la référence est partiellement absolue, seule la ligne est figée. La colonne reste relative au nouvel emplacement. Une référence avec ligne absolue s’écrit de la façon suivante “A$1“ ....................................................................................................................... 8 4- Comment définir une colonne absolue dans Excel Dans ce cas la référence est partiellement absolue, seule la colonne est figée. La ligne reste relative au nouvel emplacement. Une référence avec colonne absolue s’écrit de la façon suivante “$A1“ ....................................................................................................................... 8 II. LE GROUPE DE TRAVAIL ........................................................................................... 9 A. Cas de feuilles contiguës ................................................................................................................................................... 9 B. Cas de feuilles éloignées les unes des autres ................................................................................................................... 10 III. LES FONCTIONS PRINCIPALES ................................................................................ 10 A. Les fonctions statistiques ................................................................................................................................................. 10 B. La fonction Si .................................................................................................................................................................. 10 E. Les fonctions de texte ...................................................................................................................................................... 14 F. La fonction de concaténation ........................................................................................................................................... 15 IV. LA GESTION DES NOMS ......................................................................................... 16 A. Nommer une cellule c’est lui conférer un pouvoir. ......................................................................................................... 16 B. Exemple ........................................................................................................................................................................... 17 V. LES FORMULES MATRICIELLES ................................................................................ 20 A. CREATION D’UNE FORMULE MATRICIELLE ........................................................................................................ 20 B. EXEMPLES ..................................................................................................................................................................... 21 C. CONTRAINTES PARTICULIERES .............................................................................................................................. 21 D. SAISIE D’UNE PLAGE DE NOMBRES ....................................................................................................................... 22 VII. LES GRAPHIQUES ................................................................................................. 23 A. CREATION ET MODIFICATIONS D’UN GRAPHIQUE ............................................................................................ 23 Les fonctions ‘NB.SI’ , SOMME.SI’ et NBVAL ............................................................... 24 les fonctions ‘NB.SI.ENS’ ‘SOMME.SI.ENS’ ‘MOYENNE.SI.ENS’ .................................... 26 EXCEL INTERMEDIAIRE MME AZZOUG Page 3 I. LES REFERENCES DES CELLULES A. Quelques généralités Une feuille Excel présente un repère dont l’axe horizontal est représenté par des noms de lettres pour les colonnes et dont l’axe vertical est constitué de numéros pour les lignes. Ce repère permet d’assigner à un emplacement élémentaire de la feuille nommé cellule un nom, une adresse que l’on appelle références de la cellule. Exemple : A1, C2 B. Règles de recopie par défaut d’une formule • Recopie verticale : ligne après ligne Lors de la recopie d’une formule à l’aide de la poignée de recopie, bouton qui se situe en bas à droite d’une sélection de cellules, et qui présente un pointeur en forme de croix noire, le logiciel adapte la formule au mouvement de l’utilisateur, si on recopie ligne après ligne, donc verticalement, la formule s’adapte en tout point aux données situées sur chaque nouvelle ligne rencontrée. • Recopie horizontale : colonne après colonne De même, si on recopie une formule colonne après colonne, donc horizontalement, la formule tient compte à nouveau du déplacement de l’utilisateur à l’écran et examine les données rencontrées sur chaque nouvelle colonne. Expl : EXCEL INTERMEDIAIRE MME AZZOUG Page 4 Ces règles de recopie d’une formule se font toujours par défaut, cependant il faudra parfois les rendre inactives dans une formule dès lors que celle‐ci analysera des cellules permanentes. Ce peut être le cas d’une formule qui tient compte d’une taux à appliquer à un montant. • L’utilisation du symbole dollar Expl : Si on examine le tableau ci‐dessous (faire Ctrl et touche guillemets ou dans l’onglet Formules, un clic sur Afficher les formules), on voit que lors de la recopie de la formule du calcul du montant de TVA, le logiciel applique les règles par défaut de recopie de formule donnant les résultats présentés dans le tableau précédent. On voudrait que notre formule applique toujours le taux de la cellule G2 aux différents montants HT. Hors du fait de la recopie par défaut, chaque nouvelle formule issue de la recopie se décale d’une ligne pour lire le taux. Il n’y a pas de taux en G3, et le résultat est donc 0, il y a du texte en G4 et le résultat rendu est #VALEUR. Pour que la formule examine toujours le taux en G2, on tient compte du sens de la recopie pour transformer la première formule et la rendre correcte. Ainsi, puique l’on recopie la formule verticalement, il y lieu d’empêcher le logiciel d’écrire 3 pour G3, puis 4 pour G4 à la place de 2 pour G2. EXCEL INTERMEDIAIRE MME AZZOUG Page 5 Pour ce faire, il suffit de se positionner dans la première formule, ici celle de la cellule G5 et d’insérer un symbole $ à gauche de l’élément qui ne doit pas être incrémenté. Le dollar bloque l’élément qui le suit, oblige la recopie exacte de cet élément. On peut maintenant recopier la formule. On constate qu’à chaque fois on pointe vers le taux de la cellule G2. Comment marche les références relatives ou absolues dans Excel Comment sont référencées les cellules Excel. Comprendre les références relatives et absolues. Utiliser le caractère $. Qu’est-ce-qu’une ligne relative ou une ligne absolue. Rappel : une cellule est désignée par son numéro de colonne et de ligne, appellée aussi la référence ; ainsi, A1 est la référence de la cellule A1 ! Pour utiliser efficacement les formules de calcul, il est important de bien comprendre comment sont référencées les cellules. Il existe 4 types de références : • Référence relative (ex : A1) • Référence absolue (ex : $A$1) • Ligne absolue (ex : A$1) • Colonne absolue (ex : $A1) Nous allons détaillé chacun de ces types de référence. Je vais volontairement expliquer dans le détail le fonctionnement du référencement des cellules car cette base est indispensable pour utiliser efficacement excel. 1- Comment utiliser une référence relative Excel ? La référence de la cellule est relative. Si l’on fait un copier/coller d’une formule contenant cette référence ( Par exemple A1 ) alors la référence de la formule s’ajustera en fonction du nouvel emplacement. EXEMPLE 1. Entrer dans la cellule A1 le texte « les chanteurs » comme indiquer ci-dessous : EXCEL INTERMEDIAIRE MME AZZOUG Page 6 2. Utiliser dans la cellule A2 la formule MAJUSCULE qui permet de transformer un texte minuscule (ici le contenu de la cellule A1 : les chanteurs) en texte en majuscule. La référence (relative) utilisée dans la formule majuscule doit être “A1“ Ensuite appuyez sur entrée. Le résultat obtenu doit être le suivant : LES CHANTEURS est affiché en majuscule dans la cellule A2. 3. Inscrivez dans la cellule A2 le texte suivant ‘les danseurs‘ : 4.Sélectionner et copier la cellule B1 EXCEL INTERMEDIAIRE MME AZZOUG Page 7 4.Coller la formule dans la cellule B2 Le résultat affiché dans la cellule B2 est le suivant : ‘LES DANSEURS” et non pas ‘LES CHANTEURS‘ Quelle remarques pouvons nous faire ? En faisant un copier/coller nous n’obtenons pas dans la cellule B2 le même contenu que celui de la cellule B1. Pourquoi ? –> Car seule la formule (=MAJUSCULE) a été copié telle quelle. La référence dans la formule, elle , a changé. On peut le constater en double cliquant dans la cellule B2, alors on s’apercevra que la référence (relative) n’est plus A1 mais A2. 2- Comment utiliser une référence absolue Excel ? Si l’on fait un copier/coller d’une formule contenant une référence absolue ( Par exemple $A$1 ) alors la référence de la formule ne changera pas. Le symbole dollars « $ » permet de transformer une référence relative en référence absolue. EXCEL INTERMEDIAIRE MME AZZOUG Page 8 Ainsi si on effectue un copier/coller dans la cellule B2 de la formule =MAJUSCULE($A1$) contenue en B1 comme dans l’exemple ci-dessus alors le résultat sera le suivant : On remarque que la référence $A$1 est la même dans la cellule B2 et B1. Dans les deux cas la formule « =MAJUSCULE($A$1) » utilise la référence “$A$1“ Astuce : Lorsque vous avez saisi le code suivant : « =MAJUSCULE(A1), en appuyant sur la touche F4 vous obtiendrez automatiquement le résultat suivant “=MAJUSCULE($A$1)“ 3- Comment définir une ligne absolue dans Excel ? Dans ce cas, la référence est partiellement absolue, seule la ligne est figée. La colonne reste relative au nouvel emplacement. Une référence avec ligne absolue s’écrit de la façon suivante “A$1“ 4- Comment définir une colonne absolue dans Excel Dans ce cas la référence est partiellement absolue, seule la colonne est figée. La ligne reste relative au nouvel emplacement. Une référence avec colonne absolue s’écrit de la façon suivante “$A1“ Note : Vous remarquerez que le signe dollars $ se place juste en avant ce qui doit resté figé. Avant le ‘1’ si l’on veut que la référence reste figé sur la ligne ‘1‘. Juste avant le ‘A‘ si l’on veut que la référence reste EXCEL INTERMEDIAIRE MME AZZOUG Page 9 figée sur la colonne ‘A‘. Et devant la référence de ligne et de colonne si l’on veut qu’il y est une référence absolue sur une cellule. Astuce : – En appuyant 1 fois sur la touche F4 vous obtiendrez une référence absolue – En appuyant 2 fois sur uploads/Philosophie/cours-excel-intermediaire.pdf