Le petit Excel illustré PDF

LE PETIT EXCEL ILLUSTRÉ Rev. 25/06/2023 Un e-book réalisé par Noël Galtiau - https://kitcreanet.fr 2 3 INTRODUCTION Bonjour ! Je suis Noël Galtiau du site Kitcreanet.fr. Je suis ravi de t’accompagner dans la lecture de cet ouvrage. Le petit Excel illustré est un recueil des fondamentaux nécessaires pour être à l’aise avec un tableur (que ce soit Excel de Microsoft ou un autre). Il s’adresse aux personnes qui découvrent le logiciel et qui ont besoin d’être rapidement opérationnel. Il n’en reste pas moins que lorsque tu auras absorbé les trois grandes sections de ce document (Basique, Avancé, Expert) tu en sauras plus que la plupart des personnes que tu croiseras en entreprise ou ailleurs. Ce n’est pas un manuel exhaustif (il faudrait des milliers de pages) mais les principales notions sont abordées. Cette œuvre est mise à disposition selon les termes de la Licence Creative Commons : ND Tu peux librement copier, partager et distribuer ce document tant que tu ne modifies pas le contenu et que tu laisses les informations permettant d’identifier son auteur. Aucune utilisation commerciale n’est autorisée sans mon accord. N’hésite pas à me faire part de tes questions commentaires en m’écrivant à [email protected]. Noël GALTIAU 4 PRÉREQUIS Cet e-book se veut accessible au plus grand nombre, mais un minimum de prérequis est nécessaire : avoir un ordinateur (Mac ou Pc), avec Excel (ou autre tableur) accessible, ou un compte microsoft pour utiliser Excel Online, savoir faire les opérations de base suivantes : Savoir ouvrir et fermer le logiciel, Savoir créer, sauver, ouvrir et fermer un fichier, Savoir copier / couper / coller (à la souris ou au clavier). COMMENT BIEN LIRE CET E-BOOK L’accent est mis sur les explications simples et claires avec pour objectif une prise en main rapide. J’ai parfois pris quelques raccourcis et j’omets parfois volontairement des détails non essentiels. Toutes les captures d’écran utilisée dans ce livre utilisent le même code couleur : les cellules en blanc contiennent les données. Les cellules en vert clair contiennent des formules de calcul. Les cellules en jaune pâle affichent la formule utilisée. 5 LE SUPPORT DE COURS Tous les exemples de ce livre sont regroupés dans un classeur Excel disponible au téléchargement à l’adresse suivante : https://kitcreanet.fr/support-de-cours-Excel-illustre/ Pour toutes questions concernant ce fichier, vous pouvez me contacter par email : [email protected] 6 7 PARTIE 1 | LES BASES C’est quoi microsoft Excel ? SOMM 12 La fonction MOYENNE() La fonction MAX() 43 44 C’est quoi un fichier Excel ? 13 La fonction MIN() 45 La feuille de calcul 14 L’OPÉRATEUR & 46 Texte ou chiffre, Excel fait la différence 15 LES FONCTIONS DE DATE 48 C’est quoi la mise en forme ? 15 c’est quoi une date dans Excel ? 50 C’est quoi un calcul ? 16 La fonction AUJOURDHUI() 51 C’est quoi une référence ? 17 La fonction JOUR() 52 C’est quoi une série ? 18 La fonction JOURSEM() 53 La poignée de recopie 19 La fonction MOIS() 54 Références relatives 20 La fonction FIN.MOIS() 55 Références Absolues 21 La fonction ANNEE() 56 Références mixtes 22 La fonction DATE() 57 Donner un nom à des cellules 23 LES FONCTIONS LOGIQUES 58 Ordre des opérations 24 La fonction SI() 60 Copier coller 25 La fonction ET() 61 Déplacer une cellule ou une plage 26 La fonction OU() 62 PARTIE 2 | AVANCÉ La fonction SIERREUR() 63 FORMULES & FONCTIONS 34 La fonction SOMME.SI() 64 C’est quoi une formule ? 36 La fonction NB.SI() 65 C’est quoi une fonction ? 37 LES FONCTIONS TEXTE 66 C’est quoi un paramètre ? 38 La fonction GAUCHE() 68 C’est quoi une plage ? 39 La fonction DROITE() 69 LES FONCTIONS DE BASE 40 La fonction NBCAR() 70 La fonction SOMME() 42 La fonction TROUVE() 71 8 MAIRE La fonction MAJUSCULE() La fonction MINUSCULE() 72 73 La fonction INDEX() La fonction EQUIV() 121 122 La fonction NOMPROPRE() 74 Le couple INDEX/EQUIV 123 La fonction TEXTE() 75 La fonction RECHERCHEX() 125 C’est quoi une fonction imbriquée ? 76 La fonction UNIQUE() 127 PARTIE 3 | EXPERT La fonction ALEA() 130 LA FEUILLE À OUTILS 80 La fonction ALEA.ENTRE.BORNES() 131 LES FORMATS DE CELLULE 82 LES TABLEAUX CROISÉS DYNAMIQUES 132 C’est quoi un format de cellule ? 84 C’est quoi un TCD ? 134 C’est quoi un format personnalisé ? 85 LES GRAPHIQUES 140 Créer son format personnalisé 86 À quoi servent les graphiques ? 142 TABLES & TABLEAUX 90 Comment créer un graphique ? 143 Différence entre plage et tableau 92 Bien choisir son type de graphique 144 VALIDATION DE DONNÉES 98 Modifier un graphique 145 C’est quoi la validation des données ? 100 PARTIE 4 | ANNEXES Mettre en place une validation 101 Obtenir Excel gratuitement 148 Messages de saisie personnalisés 103 Les messages d’erreur dans Excel 149 Valider avec une liste 104 AU REVOIR ! 152 LA MISE EN FORME CONDITIONNELLE 108 C’est quoi la MFC ? 110 Détecter les doublons 112 Utiliser des formules pour la MFC 113 LES FONCTIONS DE RECHERCHE 116 La fonction RECHERCHEV() 118 QUEL PROGRAMME ! La fonction RECHERCHEH() 120 9 PARTIE 1 LES BASES 10 11 C’EST QUOI MICROSOFT EXCEL ? Microsoft Excel est un programme de la suite logicielle Microsoft Office. C’est un TABLEUR. Un tableur est un logiciel permettant de traiter des informations stockées dans des feuilles de calcul. Une méga calculatrice quoi, mais pas que. Excel permet d’automatiser des calculs qui peuvent aller de la simple addition à des équations extrêmement complexes. Excel permet aussi la représentation des données sous forme de graphiques. Il existe de très nombreux tableurs sur le marché : Excel de Microsoft, Numbers développé par Apple, Calc de la suite Libre Office, Google Sheets, disponible en ligne via votre compte Google... La liste est longue. Ce puissant outil de travail deviendra vite un jeu avec lequel tu t’amuseras à manipuler tes données pour produire de l’information utile et efficace et des graphiques didactiques professionnels. Calc (Libre Office) Numbers (Apple) Excel (Microsoft) Sheets (Google) 12 C’EST QUOI UN FICHIER EXCEL ? Un fichier Excel s’appelle un CLASSEUR. Un classeur se présente da la façon suivante : Tout en haut de la fenêtre se trouve le RUBAN. C’est ta boîte à outils. Tu trouveras tout ce dont tu as besoin à l’intérieur. Juste en dessous se trouve LA BARRE DE FORMULE. C’est ici que tu écriras toutes tes formules de calcul. À gauche de cette barre de formule, il y a la ZONE DE NOM. C’est ici que tu pourras s’avoir comment se nomme la cellule sur laquelle tu te trouves. Dans un Classeur, on trouve plusieurs FEUILLES DE CALCUL. La FEUILLE DE CALCUL est l’endroit ou tu travailles. Chaque feuille de calcul possède un Onglet. Sous la feuille de calcul se trouvent les ONGLETS. Les onglets te permettent de passer d’une feuille de calcul à une autre d’un simple clic. 13 LA FEUILLE DE CALCUL Une feuille de calcul est composée de LIGNES et de COLONNES. À l’intersection d’une ligne et d’une colonne se trouve une CELLULE. Les cellules ont un NOM (on dit aussi une référence), composé à l’aide du nom de colonne et du numéro de ligne. Par exemple, dans l’image ci-dessous, la cellule à l’intersection de la Colonne F et de la Ligne 4 porte le nom F4. Pour passer d’une feuille à une autre, on utilise les ONGLETS. Chaque onglet possède un nom personnalisable. Dans une CELLULE, on ne trouve QU’UNE seule chose : soit une valeur (un nombre ou du texte) soit une suite de calculs. Un calcul peut se composer d’opérations simples ou bien faire appel à des FONCTIONS destinées à effectuer des traitements plus complexes. 14 TEXTE OU CHIFFRE, EXCEL FAIT LA DIFFÉRENCE En fonction de ce que tu écris dans une cellule, Excel ne réagira pas de la même façon. Si tu insères du texte, Excel applique un format Texte et utilisera cette cellule comme du texte. Si tu insères un chiffre, alors Excel applique un format Nombre et considèrera cette cellule comme un nombre. (Consulte la section dans laquelle je t’explique tout sur les formats de cellule pour en savoir plus !). ASTUCE Pour t’aider à reconnaître du premier coup d’œil une cellule au format Texte et une cellule au format Nombre, Excel présente les données de façon différente : Les chiffres sont alignés à droite, Les textes sont alignés à gauche. C’EST QUOI LA MISE EN FORME ? La mise en forme c’est l’ensemble des réglages « décoratifs » que tu appliques à une cellule. Couleur du texte, couleur de la cellule, choix de la police de caractères, modification de la taille de la police, alignement de la cellule, etc. Tous les outils de mise en forme simple sont disponibles dans le RUBAN aux sections Police et Alignement. Il existe des options de mise en forme plus élaborées, mais nous les verrons plus loin dans la partie EXPERT. 15 C’EST QUOI UN CALCUL ? Non ce n’est pas une question bête ! Tout calcul dans Excel commence par le signe ÉGAL (=). Dès que tu tapes le signe ÉGAL (=) dans une Cellule ou dans la Barre de formule, Excel sait que ce qui suit doit être calculé. On dit alors que tu tapes une FORMULE. Si tu ne tapes pas le signe ÉGAL (=) ça signifie que tu souhaites juste utiliser ta Cellule pour stocker une information (un texte ou un nombre). Pour faire un calcul tu dois utiliser les signes dont tu as l’habitude : Pour faire une addition, utilise le signe PLUS (+) Pour faire une soustraction, utilise le signe MOINS (-) Pour faire une division, tu dois utiliser le signe BARRE OBLIQUE (/) Pour faire une multiplication, tu dois utiliser l’ÉTOILE (*). Tous ces opérateurs sont disponibles sur ton pavé numérique si tu possèdes un clavier étendu. Si ce n’est pas le cas tu les retrouveras également sur ton clavier standard. Pour retrouver ces touches facilement, je t’ai dessiné mon clavier avec des zones de couleur pour chaque touche : ÉGAL (=) PLUS (+) MOINS (-) DIVISER (/) MULTIPLIER (*) 16 C’EST QUOI UNE RÉFÉRENCE ? Dans une Formule, tu peux indiquer une valeur ou bien la Référence de la cellule qui contient la valeur à utiliser. Par exemple, dans le tableau ci-dessous : dans la cellule B5, j’ai tapé la formule suivante : =5+2. Je demande donc à Excel de faire une addition et le lui donne les chiffres directement. Il m’affiche le résultat : 7. dans la cellule B7, je demande à Excel de faire une soustraction. Mais au lieu de taper les valeurs directement dans ma formule, je vais indiquer à Excel où sont mes valeurs (puisqu’elle existent déjà en B2 et C2). J’utilise alors la Référence des cellules plutôt que de taper les données elles-mêmes. QUELQUES EXEMPLES POUR MIEUX COMPRENDRE ? Dans les cellules B2, C2 et D2, des valeurs ont été saisies sans le signe ÉGAL (=). Ces Cellules sont donc destinées à stocker une information. La cellule C5 contient un calcul Direct, avec les données à calculer directement inscrites dans la formule. Ça fonctionne, mais c’est une mauvaise habitude. Les autres cellules (C7, C9, C11, C13 et C14) utilisent des Références vers des cellules qui contiennent de la donnée. C’est une meilleure pratique. Pourquoi ? Et bien l’explication est très simple : si je modifie mes données dans les cellules B2, C2 et D2, le résultat de mes formules sera toujours correct car le contenu de la cellule a changé mais pas sa Référence. Par contre, la formule de la cellule C5 ne se mettra pas à jour, je devrais le faire manuellement. Ici tu comprends maintenant toute la puissance de cette bonne habitude. Dans tes formules, à chaque fois que c’est possible, utilise des références de cellules qui contiennent tes données plutôt que la donnée elle-même. 17 C’EST QUOI UNE SÉRIE ? Excel est un logiciel intelligent qui possède plein de petits outils pratiques pour t’aider à travailler plus vite. Par exemple, lorsque tu as besoin d’écrire rapidement les jours de la semaine, pas besoin de tous les écrire. Écris juste Lundi dans une cellule, puis utilise la poignée de recopie ! Fais un clic gauche sur cette poignée et en laissant le bouton gauche de ta souris appuyé, fais glisser vers à droite (ou vers le bas) et observe ce qui se passe ! Excel complète la série automatiquement pour toi. Relâche la souris quand tu as obtenu la série souhaitée. Tu peux aussi essayer avec les mois de l’année ! 18 LA POIGNÉE DE RECOPIE La poignée de recopie est aussi un assistant précieux pour tes formules de calcul. Prenons l’exemple ci-dessous. Tu as deux colonnes de chiffres B et C. Dans la cellule D3, tu as écris la formule =B3 * C3. Mais pour avoir tous tes résultats, il faut écrire toutes les formules dans la colonne D et modifier le numéro de ligne pour chaque formule B4 * C4, B5* C5, etc. Mais non ! Rassures-toi, tu n’as pas besoin d’écrire la formule à la main dans toutes les cellules du dessous. Utilise la poignée de recopie vers le bas pour recopier automatiquement ta formule. ASTUCE : pour aller encore plus vite, tu peux faire un double-clic sur la poignée de recopie. Je te laisse découvrir le résultat de cette manipulation. Regarde attentivement le résultat dans le tableau ci-dessous. Toutes les formules sont correctes et utilisent bien les bonnes références. Excel a automatiquement adapté les formules de calcul aux positions relatives des cellules. Si la formule descend d’une ligne, alors les références suivent. Nous allons voir ça en détail page suivante. 19 RÉFÉRENCES RELATIVES Une formule standard utilise des références Relatives. Pour expliquer cette notion prenons l’exemple suivant : la cellule D3 contient une formule de calcul. Cette formule de calcul contient deux références : La référence à B3, une cellule qui est à 2 colonnes sur la gauche de D3, La référence à C3, une cellule qui est à 1 colonne sur la gauche de D3. Copie la cellule D3 , et colle-la en cellule D4. Tu peux constater que Excel, a automatiquement adapté la nouvelle formule. Comme la première référence (B3) faisait référence à une cellule située à deux colonnes sur la gauche, il a appliqué la même règle à la première référence de la formule que tu as collée. 2 colonnes sur la gauche par rapport à D4, c’est la cellule B4. Et il a fait pareil pour la seconde référence C3. C3 est une référence à une cellule qui se trouve à une colonne à gauche par rapport à D3, alors la nouvelle référence relative à D4, une colonne sur la gauche, c’est C4. Mais que se passe t-il si tu colles à nouveau ta formule en D5 ? La même logique est appliquée. Mais cette fois, les références relatives pointent sur des cellules qui sont vides. Notre résultat est donc de 0. 20 RÉFÉRENCES ABSOLUES Pour expliquer les références absolues, prenons un autre exemple. J’ai un tableau de note pour mes élèves et je veux multiplier toutes les notes par deux. Une règle importante dans une feuille de calcul : une donnée UNIQUE ne doit pas être présente à deux endroits différents. Dans la cellule D3, j’ai une formule qui multiplie la note par le coefficient qui se trouve en B2. Ce coefficient est le même pour toutes les notes, il est donc inutile et même interdit de recopier bêtement ce coefficient dans toutes les cellules du dessous. L’idéal est de placer ce coefficient à un seul endroit. Si je sélectionne la poignée de recopie et que je tire vers le bas, je vais avoir le même problème que dans la page précédente. Les formules vont être réécrites avec des références relatives et mon calcul ne trouveras plus la bonne valeur. Mais on peut empêcher Excel de modifier la référence d’une cellule lorsque l’on fait une copie ou qu’on utilise la poignée de recopie. Pour ça il suffit de placer le signe DOLLAR ($) devant les coordonnées de ligne et de colonne de la cellule. Ainsi B3 devra être écrit $B$3. En faisant ça tu viens de créer une référence absolue. Cette référence dans ta formule de calcul ne changera plus, même si tu copies la cellule ou que tu utilises la poignée de recopie. Pour placer le signe DOLLAR ($) et transformer une référence relative en référence absolue tu peux les écrire manuellement ou utiliser le raccourci clavier. Place ton curseur sur la référence dans la barre de formule puis : Sur Excel pour Windows, appuie sur la touche F4, Sur Excel pour Mac, utilise le raccourci clavier cmd ⌘ - T. 21 RÉFÉRENCES MIXTES Il est possible de mettre le signe $ seulement sur la référence de colonne ou seulement sur la référence de ligne. C’est alors une référence mixte. Super, mais à quoi est-ce que ça sert me diras-tu ? VOICI UN EXEMPLE Pour illustrer ça, on va retourner à l’école pour faire une magnifique table de multiplications ! Je vais te montrer comment la créer en moins de 10 secondes ! Tu te souviens, nous avons vu qu’en utilisant la poignée de recopie, les formules s’adaptent de façon relative à leur nouvelle position et modifient le nom des lignes et des colonnes. Mais parfois, on veut empêcher ça, comme dans l’exemple ci- dessous. Regarde attentivement la formule qui se trouve en B2 : =$A2*B$1. Pour faire la table de multiplications, il suffit de sélectionner la cellule B2 et d’utiliser la poignée de recopie vers la droite jusqu’en J2. Ensuite, il suffit de faire descendre la poignée de recopie jusqu’à J10. Magique ! OUI MAIS…. Ça fonctionne seulement parce-que j’ai utilisé des références mixtes ! Si ma formule était =A2*B1, en tirant la poignée de recopie vers la droite, la formule changerait pour devenir B2*C1, C2*D1, etc. Ce qui fausserait mes résultats. (Essaye de ton côté pour bien comprendre). Comment corriger ça ? Dans le premier terme de mon opération, la colonne ne doit jamais changer. Je dois toujours multiplier par les chiffres blancs sur fond vert de la colonne A. Je vais donc bloquer cette colonne dans ma formule en plaçant un dollar ($) devant le nom de la colonne. Dans le second terme de mon opération, la ligne ne doit jamais changer. Je dois toujours multiplier par les chiffres blancs sur fond vert de la ligne 1. Je vais donc bloquer cette ligne dans ma formule en plaçant un dollar ($) devant le nom de la ligne. 22 DONNER UN NOM À DES CELLULES Quand on a un classeur Excel avec beaucoup de données à l’intérieur, il n’est pas toujours évident de se souvenir de la cellule qui contient telle ou telle donnée. C’est pour ça qu’on peut donner un petit nom aux cellules ! Je t’ai expliqué au tout début que les cellules portent toutes un nom qui est composé du numéro de ligne et du nom de la colonne. Ce nom s’affiche en haut à gauche de ta feuille quand tu cliques sur une cellule dans la zone de nom. Ici la cellule qui contient le taux de TVA est la cellule B3, et son nom c’est B3 comme tu peux le voir dans la zone de nom. Dans mes formules de calcul, j’utilise donc une référence absolue $B$3 pour cibler cette cellule. Et si je te disais que tu peux lui donner le nom que tu veux à cette cellule ? Ce serait chouette n’est-ce pas ? Cliques dans la zone de nom et écris TVA. Valide en appuyant sur la touche Entrée de ton clavier. Regarde la différence si je clique en B3. Ce n’est plus le nom standard mais le nom que tu as choisi : TVA ! Encore plus fort ! Regarde les formules de calcul : oui ! Tu as bien vu. Quand tu donnes un nom à une cellule, tu peux utiliser ce nom dans tes formules ! Super efficace n’est-ce pas ? Et c’est pas fini ! Cerise sur le gâteau ! Lorsque tu as donné un nom à une cellule, cette cellule est utilisable de partout dans le classeur, depuis n’importe quelle feuille, sans préciser où elle se trouve exactement. Nous reverrons cette notion quand on parlera plus tard des bonnes pratiques et de ce que j’appelle mon onglet INDEX. Attention : pour nommer une cellule, tu dois respecter quelques règles : Il doit commencer par une lettre ou un trait de soulignement (_) ; Il ne doit pas contenir d’espace. 23 ORDRE DES OPÉRATIONS Dans un tableur, ce sont les règles standard des mathématiques qui s’appliquent pour l’ordre des opérations. La multiplication et la division sont prioritaires sur l'addition et la soustraction. Les multiplications et divisions sont effectuées de gauche à droite : Si une multiplication est à gauche d'une division, on effectue d'abord la multiplication. Si une division est à gauche d'une multiplication, on effectue d'abord la division. QUELQUES EXEMPLES POUR MIEUX COMPRENDRE ? Dans la cellule B4, la formule a été rédigée comme ceci : B2 + C2 * D2, mais les calculs sont faits dans l’ordre suivant : C2 * D2 + B2. La multiplication est prioritaire et elle est réalisée en premier : C2 * D2 = 20 + C2 = 25. Le résultat est 25. Dans la cellule B5, c’est exactement la même formule à la différence que les données B2 + C2 on été placées entre parenthèses ce qui change la priorité. Le calcul de B2 + C2 va donc se faire avant la multiplication, ce qui donne :. B2 + C2 = 7 * D2 = 70. Les résultats sont très différents. Dans la cellule B10, je fais une division puis une multiplication. Ces deux opérations sont de priorité équivalente, Excel va donc les faire dans leur ordre d’apparition, de la gauche vers la droite. Dans la cellule B11, c’est l’inverse. C’est une multiplication que j’ai écrite en premier. Dans ce cas également, Excel va les traiter dans leur ordre d’apparition, de la gauche vers la droite. Enfin, dans la cellule B12, j’ai mis la même opération que dans la cellule B10, mais cette fois j’ai utilisé des parenthèses pour forcer la priorité à la multiplication. Elle va donc être réalisée par Excel avant la division. Les résultats sont très différents. 24 COPIER COLLER Copier-coller, à priori tu sais faire ? Mais dans Excel, il y a quelques petites choses à savoir concernant le copier coller. Les options possibles sont assez nombreuses et je t’invite à les tester de ton côté. Dans ce manuel, je ne vais en aborder que quelques unes, parmi celles que je trouve les plus utiles. LE COPIER-COLLER STANDARD Dans ce tableau, si tu copies la cellule D3 et que tu colles en cellule D6, tu vas constater plusieurs choses. La mise en forme de la cellule a été copiée : alignement, police de caractères, couleur de police, couleur de fond et couleur de contour. Mais la formule de calcul qui se trouvait en D3 a été copiée également. Comme nous l’avons vu un peu plus tôt, Excel utilise des références relatives lors de la copie et notre formule s’est adaptée, pas besoin d’aller modifier la références des cellules. Nous sommes passés automatiquement de B3+C3 à B6+C6. Je n’ai copié qu’une seule cellule dans cet exemple mais tu peux copier une plage, une ligne entière, une colonne entière et même un tableau tout entier pour le placer où tu le souhaites sur ta feuille de calcul. 25 DÉPLACER UNE CELLULE OU UNE PLAGE Pour déplacer une cellule ou une plage de cellules, tu as deux possibilités : le Couper-Coller ou le Cliquer-Glisser. Si tu fais un Couper-Coller, tu effaces les cellules pour les coller ailleurs. Le Cliquer-glisser, cʼest la même chose, sauf que tu fais ça à la souris. Sélectionne la cellule ou la plage de cellules que tu veux déplacer (ici j’ai sélectionné la plage D2:E3), Positionne le curseur de ta souris sur la partie inférieure de ta sélection, le curseur change de forme (sur Mac, il devient une petite main, sur Windows, cʼest une croix), Clique sur le bord de ta sélection et sans relâcher le bouton, fait glisser tes cellules à leur nouvel emplacement. Comme tu peux le constater, tout se déplace en même temps : la mise en forme de la cellule, son format si tu as appliqué un format particulier, et bien entendu les formules qui pointent toujours vers les bonnes références. 26 LE COPIER-COLLER « VALEUR » Parfois tu auras besoin de copier-coller uniquement la valeur de la cellule et rien d’autre. Pour ça, tu vas devoir copier la cellule D3 comme précédemment, mais tu ne vas pas coller avec le raccourci clavier. Places-toi dans la cellule D6 et va dans le Ruban à la section Presse-papier. À côté du bouton Coller, il y a un petit menu déroulant. Clique dessus pour faire apparaître les options de collage disponibles. Comme je te l’ai dit, elles sont nombreuses. Choisis l’option Coller les valeurs. 27 Seule la valeur est collée dans la cellule D6. Ni la mise en forme, ni la formule de calcul n’ont été copiées (tu peux le vérifier en regardant dans la barre de formule qui est vide). DIS MOI. JE NE SUIS PAS CERTAIN DE BIEN COMPRENDRE À QUOI ÇA PEUT BIEN SERVIR ÇA ? Parfois, tu vas utiliser des formules de manière ponctuelles pour formater des nombres ou fusionner des données avec & par exemple. Une fois ce travail fait et que tes données sont prêtes, tu n’as plus besoin des formules mais seulement des données. C’est aussi très pratique pour donner une version « plate » (c’est à dire sans aucune formule, juste avec les chiffres) de ton fichier à un collègue. Tu verras, il y a de nombreuses raisons qui vont te pousser à faire ce copier-coller spécial Valeurs. REGARDE JE SUIS UNE VERSION PLATE ! … 28 LE COPIER-COLLER « TRANSPOSÉ » Le mieux c’est que je te montre un exemple… Prenons ce tableau dans lequel j’ai placé les mois sur la ligne 1 et des valeurs numériques sur la ligne 2. Mais je me suis trompé. En fait, je préfère mettre les mois en colonnes plutôt qu’en ligne comme ceci. Tu imagines le travail. Je vais devoir refaire toute la saisie. 29 Mais en fait… NON. Grâce au collage spécial Transposé. Pour ça il suffit de sélectionner la plage de cellule A1:B2, puis de la copier. Une fois que cette plage est copiée, clique dans une cellule vide (A4 par exemple) et rends-toi dans le bandeau, au niveau du presse-papier. Dans le petit menu déroulant, choisis la fonction Transposer. Et voilà ! Magique ! Tout le tableau a été transposé de l’horizontale vers la verticale. Quel temps gagné ! Cerise sur le gâteau, les formules de calculs ont été modifiées pour correspondre au nouvel emplacement des données ! 30 JE T’OFFRE UNE PAUSE, ÇA TE DIT ? PETITE ANECDOTE Le copier-coller, aujourd’hui, tout le monde sait ce que c’est, mais l’informatique a débuté sans. Ce n’est qu’en 1973 que cette technique a été inventée par Larry Tester, un ingénieur de chez Xerox. Source : https://fr.wikipedia.org/wiki/Copier-coller — Quand tu coupes ou que tu copies une information (texte, image, etc.), elle est temporairement stockée dans une zone de la mémoire de ton ordinateur qu’on appelle le presse-papier. Elle va y rester jusqu’a ce que tu colles cette information ailleurs ou que tu la remplaces par un nouveau copier ou un nouveau couper. Sur ordinateur, les raccourcis claviers pour effectuer une action de copier / couper / coller sont les suivants : Sur ordinateur Mac : apple COPIER : COUPER : COLLER : Sur ordinateur Windows : COPIER : COUPER : COLLER : 31 PARTIE 2 AVANCÉ 32 33 FORMULES & FONCTIONS 34 35 C’EST QUOI UNE FORMULE ? Une formule, c’est un ordre donné à Excel pour qu’il exécute un calcul. Toutes les formules démarrent obligatoirement par le signe ÉGAL (=). Dans une formule on peut utiliser une valeur fixe, des références à des cellules ou encore des fonctions. QUELQUES EXEMPLES POUR MIEUX COMPRENDRE ? Dans la cellule E3, je demande à Excel de multiplier le contenu de la cellule B3 par le contenu de la cellule C3, ce qui donne un résultat de 50. Dans la cellule E4, j’utilise une fonction SOMME() qui va additionner toutes les valeurs comprises entre B3 et B9. Résultat : 91. Dans la cellule E5, je multiplie le contenu de la cellule C3 par une fonction PI(). Le rôle de la fonction PI() est d’afficher le nombre Pi (3,14159265358979) avec une précision de 15 chiffres après la virgule. Ça t’évite de le taper à la main ! 5 * 3,14159265358979 est égal à 15,70796327. Dans la cellule E6, je dis à Excel que le contenu de cette cellule est égal au texte « Bonjour » (je pourrais faire de même en tapant « Bonjour » directement). Attention, quand tu veux afficher du texte après un signe ÉGAL (=), pense à le mettre entre guillemets sinon tu auras une erreur. Dans la cellule E7, je combine trois informations différentes pour en construire une nouvelle : le contenu de la cellule C5, un espace vide, le contenu de la cellule C6. & est un opérateur de concaténation que nous verrons un peu plus tard. Dans la cellule E8, je donne une valeur à cette cellule. En tapant =8, je dis à Excel que le contenu est égal à 8 (je pourrais faire de même en tapant 8 directement). En cellule E9, je dis à Excel, le contenu de cette cellule est égal au contenu de la cellule C3. Très pratique pour afficher et donc utiliser une données qui existe déjà dans la feuille de calcul. En cellule E10, je dis à Excel : Affiche moi le contenu de la cellule C3 … mais ce n’est pas 5 qui s’affiche n’est-ce pas ? C’est parce-que avant C3, j’ai indiqué DATE! Ce qui signifie la feuille de calcul qui s’appelle DATE. Si tu regardes le support de cours Excel, tu constateras qu’il y a bien un onglet qui s’appelle DATE et que dans cette feuille de calcul, à la référence de cellule C3 tu as bien la date du 01/01/1900. C’est une notion importante. Tu peux afficher des données en provenance de n’importe quelle feuille de ton classeur dans une formule. 36 C’EST QUOI UNE FONCTION ? Une fonction, c’est une sorte de petit programme indépendant. Les fonctions permettent d’aller plus vite et réalisent des actions parfois très complexes. Une fonction sert à effectuer un traitement de l’information. Excel propose plus de 400 fonctions classées en différentes familles : Statistiques, Mathématiques & Trigonométrie, Scientifique, Finance, Date et Heure et Base de données. Pour écrire une fonction, il faut respecter des règles précises : =NOM DE LA FONCTION ( paramètre 1 ; paramètre 2 ; etc.) Dans les fonctions, tu vas utiliser deux signes particuliers : Le point-virgule ( ; ) qui sert à séparer les paramètres, Les deux points ( : ) qui servent à définir une plage (une étendue). DIVISER POUR RÉGNER, UNIR POUR CONQUÉRIR Certaines fonctions pourront te paraître inutiles au premier coup d’œil, mais bien souvent, c’est en combinant plusieurs fonctions que toute la puissance d’une fonction que tu croyais inutile deviendra évidente. UN EXEMPLE POUR MIEUX COMPRENDRE ? Prenons une simple addition. Dans le tableau ci-dessous, je n’ai mis que 12 lignes, mais imagine qu’il y a un million de lignes. En C3, j’ai utilisé une formule et j’ai indiqué =B3+B4+B5, etc. Jusque B12. C’était long à faire et pourtant je n’ai que 10 références à indiquer. Si il y avait un million de lignes dans mon tableau, j’y aurais passé des heures et des heures. En C5, j’ai utilisé une fonction dont le rôle est de faire des additions. Il faut lui indiquer la première et la dernière cellule de la plage de cellules à additionner. Ainsi j’ai juste tapé =SOMME(B3:B12), c’est plus rapide ainsi n’est-ce pas ? Si j’avais eu un million de ligne dans mon tableau, j’aurais juste eu besoin de taper =SOMME(B3:B1000000). 37 C’EST QUOI UN PARAMÈTRE ? Un paramètre est une information que l’on donne à une fonction. Par exemple, si tu utilises une fonction pour faire une addition, il faut que tu dises à Excel ce qu’il doit additionner. Prenons un exemple simple avec la fonction SOMME() : =SOMME(A1;A2) Ici, on donne deux paramètres à la fonction SOMME(). le premier paramètre c’est A1, le second paramètre c’est A2. OBLIGATOIRE OU OPTIONNEL ? Il y a deux types de paramètres : Les paramètres obligatoires, (sans eux, la fonction ne pourra pas … fonctionner), Les paramètres optionnels, qui donnent des précisions ou des infos complémentaires à la fonction. Lorsque tu commences à composer une fonction dans Excel, un petit assistant apparaît et t’aide en affichant les éléments attendus pour la construction de cette fonction : les paramètres obligatoires, et les [paramètres optionnels] affichés entre crochets. Dans ce cours, pour être plus visuel, les paramètres obligatoires seront affichés en rouge, les paramètres optionnels en bleu. =FONCTION( paramètre obligatoire ; paramètre optionnel... ) 38 C’EST QUOI UNE PLAGE ? Une plage est une étendue rectangulaire de cellules contigües. Dans Excel, tu ne dis pas « toutes les cellules entre la cellule A1 et la cellule A25 ». Tu dois dire : « la plage A1:A25 ». Une plage peut être horizontale ou verticale et même s’étendre sur plusieurs lignes et colonnes ! VOICI QUELQUES EXEMPLES : Dans la cellule C4, j’utilise la fonction SOMME() pour additionner toutes les valeurs contenues dans la plage de cellule A2:A10. Dans la cellule C5, j’additionne les valeurs contenues dans une plage qui s’étend sur 9 lignes et deux colonnes ! A2:B10. Dans la cellule C6, j’additionne les valeurs contenues dans une plage horizontale de cellules : D1:G1. Il est possible d’utiliser plusieurs plages qui ne sont pas contigües, dans ce cas, tu dois séparer les différentes plages par le séparateur point-virgule ( ; ). Dans cet exemple, la colonne A et la colonne C ne sont pas contigües, la colonne B se trouve entre les deux. Je pourrais la supprimer, mais j’en ai besoin pour mon exemple ;-) - donc nous voilà avec deux plages non contigües… Dans la formule, tu peux voir trois plages : A2:A10, puis C2:C10 et enfin E1:H1. 39 LES FONCTIONS DE BASE 40 41 LA FONCTION SOMME() À QUOI SERT CETTE FONCTION ? Cette fonction permet de faire des additions. COMMENT ÉCRIT-ON CETTE FONCTION ? =SOMME( nombre 1 ; nombre 2 ; nombre 3; …) Chaque paramètre de la fonction SOMME() peut être un nombre, une référence à une cellule ou une plage de cellules. QUELQUES EXEMPLES POUR MIEUX COMPRENDRE ? Rappel : On sépare les paramètres d’une fonction avec le signe deux-points (:). Dans la cellule C3, je demande à Excel d’additionner le contenu de deux cellules. Pour ça je lui ai donné deux paramètres séparés par un point-virgule ( ; ) : la cellule B3 et la cellule B10. En B3 j’ai 10, en B10 j’ai 10 aussi, le résultat est bien 20. Rappel : Pour indiquer une plage, on utilise le signe deux-points ( : ). Dans la cellule C5, j’ai demandé à Excel d’additionner une plage de cellules : B3:B7, c’est à dire tous les nombres qui se trouve entre la cellule B3 et la cellule B7. Dans la cellule C7, j’additionne deux plages différentes. Chaque plage étant un paramètre, je les sépare avec le point-virgule ( ; ). Le premier paramètre est la plage B3:B7 (tous les nombres entre la cellule B3 et la cellule B7) et le second paramètre est la plage B10:B14 (tous les nombres entre la cellule B10 et la cellule B14). Dans le dernier exemple, en cellule C9, j’additionne le contenu de la cellule C3 à la plage de cellules B3:B7. 42 LA FONCTION MOYENNE() À QUOI SERT CETTE FONCTION ? Cette fonction calcule la moyenne des nombres fournis en paramètres. Pour calculer la moyenne, Excel additionne toutes les valeurs fournies en paramètres et divise par le nombre de valeurs Fournies. COMMENT ÉCRIT-ON CETTE FONCTION ? =MOYENNE( nombre 1 ; nombre 2 ; nombre 3; …) Chaque paramètre de la fonction MOYENNE() peut être un nombre, une référence à une cellule ou une plage de cellules. QUELQUES EXEMPLES POUR MIEUX COMPRENDRE ? Voici un bel exemple de temps gagné grâce à une fonction ! Tu ne me crois pas ? En Cellule C12, la fonction MOYENNE() exécute pas moins de 5 opérations différentes : 1. elle fait la somme des valeurs de la plage B3:B6 : 56, 2. elle fait la somme des valeurs de la plage B8:B11 : 55 3. elle additionne ces deux sommes pour avoir le total : 111 4. elle compte le nombre de valeurs présentes dans les deux plages, : 8 5. elle divise le total obtenu (111) par le nombre de valeurs (8), pour obtenir le résultat 13,875. 43 LA FONCTION MAX() À QUOI SERT CETTE FONCTION ? Cette fonction indique la plus grande valeur présente dans les données qui sont fournies en paramètres. COMMENT ÉCRIT-ON CETTE FONCTION ? =MAX( nombre 1 ; nombre 2 ; nombre 3; …) Chaque paramètre de la fonction MAX() peut être un nombre, une référence à une cellule ou une plage de cellules. QUELQUES EXEMPLES POUR MIEUX COMPRENDRE ? Ici, on cherche à afficher la note maximale obtenue par les filles et les garçons d’une classe d’école. En cellule C3, la fonction MAX() cherche dans toutes les notes de la plage B3:B6 pour les filles et trouve la plus haute note : 20. En cellule C8, MAX() fait la même recherche mais dans la plage B8:B11 pour les garçons. La plus haute note trouvée est 19. Ensuite, en cellule C13, on cherche la plus haute note garçons et filles confondus. Mais notez ici une petite subtilité dans la formule. Au lieu d’indiquer les deux plages successivement dans ma formule comme ceci : =MAX( B3 : B6 ; B8 : B11) la formule indique B3:B11. Mais dans cette plage, il y a du texte !!! La cellule B7 contient le texte « Notes Garçons ». Mon calcul va être faux ? Non. La fonction MAX() (comme la fonction MIN()) ne tient pas compte du texte ou des cellules vides qu’elle trouve en chemin. Malin Excel. Pas de risque d’erreur donc. 44 LA FONCTION MIN() À QUOI SERT CETTE FONCTION ? Et bien c’est tout l’inverse de la fonction précédente ;-) MIN() trouve la plus petite valeur présente dans les données qui sont fournies en paramètres. COMMENT ÉCRIT-ON CETTE FONCTION ? =MIN( nombre 1 ; nombre 2 ; nombre 3; …) Chaque paramètre de la fonction MIN() peut être un nombre, une référence à une cellule ou une plage de cellules. QUELQUES EXEMPLES POUR MIEUX COMPRENDRE ? Donc, ici, ce n’est pas la plus haute note que nous cherchons, mais la plus faible. Il suffit d’utiliser MIN() à la place de MAX(). NOUS SOMMES FONCTIONS JUMELLES.. MAX OU MINI C’EST NOTRE DUO… En général, la fonction MAX() n’est jamais très loin de la fonction MIN() dans une feuille de calcul. 45 L’OPÉRATEUR & HEU.. ARRÊTE MOI SI JE ME TROMPE, MAIS ON EST EN TRAIN DE PARLER DES FONCTIONS DE BASE LÀ NON ? QUE VIENT DONC FAIRE ICI CET OPÉRATEUR ? TU AS ABSOLUMENT RAISON ! SI JE PARLES ICI DE CET OPÉRATEUR, C’EST PARCE-QU’IL FAIT LE MÊME TRAVAIL QU’UNE FONCTION QUI S’APPELLE CONCATENER(). L’OPÉRATEUR & EST PLUS FLEXIBLE ET PERMET D’ÉCRIRE DES FORMULES PLUS SIMPLES ET PLUS COMPRÉHENSIBLES. À QUOI SERT CET OPÉRATEUR ? Vous aimez faire des collages ? Et bien c’est ce que vous allez pourvoir faire avec cette fonction. L’opérateur & permet d’assembler le contenu de plusieurs cellules entre-elles pour en faire un texte (le résultat est au format texte). COMMENT UTILISE T-ON CET OPÉRATEUR ? Il faut le placer entre chaque éléments que l’on veut assembler. Ces éléments peuvent être : du texte, des chiffres, des références à des cellules, des fonctions. QUELQUES EXEMPLES POUR MIEUX COMPRENDRE ? En colonne B, j’ai le prénom d’une personne et en colonne C j’ai son âge. Je veux écrire une phrase qui donne l’âge de chaque personne. Le prénom et l’âge sont fournis dans le tableau, mais les autres parties de la phrase n’existent pas. C’est du texte que nous allons associer avec le reste grâce à &. En cellule D3, je commence par afficher le prénom, j’indique à Excel d’afficher le contenu de la cellule B3, j’écris donc : =B3. Puis j’ajoute le symbole & pour indiquer à Excel de coller ce qui va suivre. Pour continuer ma phrase, j’ajoute un espace, la lettre a et un autre espace (tout ça entre guillemets bien sûr car c’est du texte). J’ajoute le symbole & à nouveau et j’indique à Excel de coller le contenu de la cellule C3 (l’âge). Puis j’ajoute à nouveau & pour indiquer à Excel de coller la suite. Pour terminer la phrase, j’indique un espace et le mot ans. (entre guillemets encore une fois car c’est du texte). En cellule B7, j’ai construit une autre phrase en combinant du texte avec une fonction pour afficher l’âge moyen des personnes. C’est le résultat de la fonction qui s’affiche, pas la fonction bien entendu. 46 JE T’OFFRE UNE PAUSE, ÇA TE DIT ? Tu as bien travaillé, il est temps de faire une petite pause culturelle ! Sais-tu comment est-ce qu’on appelle ce (très beau) signe typographique ? Il s’agit de l’esperluette ! On l’appelle aussi « et commercial ». C’est un mélange des lettres « e » et « t » et ça signifie tout simplement « et ». Ce n’est pas évident au premier regard, mais certaines polices de caractères permettent de voir ce détail plus facilement, comme par exemple la police Trebuchet : L’origine exacte de ce symbole n’est pas certaine, et on l’attribue soit à Tiron (le secrétaire de Cicéron), soit à un imprimeur nommé Alde Manuce, très célèbre pour ses contributions au domaine de l’imprimerie. Un truc fou, : pendant longtemps, & était la 27e lettre de l’alphabet et on l’enseignait aux enfants à l’école ! Source : https://fr.wikipedia.org/wiki/Esperluette 47 LES FONCTIONS DE DATE 48 49 C’EST QUOI UNE DATE DANS EXCEL ? Derrière cette question qui peut paraître un peu étrange, se cache en vérité une chose vraiment très importante à savoir pour travailler convenablement et sans devenir fou avec les dates. Dans Excel, une date est un nombre, les puristes disent un “numéro de série”. Une autre information importante : Excel compte les jours à partir du premier janvier 1900. Ainsi : le chiffre 1 correspond au 1/1/1900, (1er janvier 1900) ; le chiffre 2 correspond au 2/1/1900 (2 janvier 1900) ; Le chiffre 43508 correspond au 12 février 2019. Dans le tableau ci-dessous, La colonne B et la colonne C contiennent les 4 mêmes chiffres : 1, 2, 366 et 25704. Dans la colonne B, c’est un chiffre au format Standard. Dans la colonne C, j’ai appliqué un format Date aux cellules. Si tu es observateur, tu as remarqué que le 31 décembre 1900 est représenté par le chiffre 366 et non 365. Effectivement l’année 1900 est une année bisextile ! Ce n’est donc pas une erreur d’Excel ! Le chiffre 367 est donc le numéro de série qui correspond au premier janvier de l’année 1901. Dans le Tableau ci-dessous, 367 correspond bien à un jour de plus que le 31/12/1900, et donc nous avons comme résultat le 01/01/1901. Lorsque tu tapes une date dans une cellule, Excel applique automatiquement le format Date à cette cellule. Mais si tu veux voir quel numéro de série se cache 50derrière cette date, il suffit de passer la cellule au format Nombre. LA FONCTION AUJOURDHUI() À QUOI SERT CETTE FONCTION ? Cette fonction permet simplement d’afficher la date du jour. Elle est très utile dès que tu dois faire des calculs sur les dates. Elle ne nécessite aucun paramètre. COMMENT ÉCRIT-ON CETTE FONCTION ? =AUJOURDHUI() UN EXEMPLE POUR MIEUX COMPRENDRE ? Dans le tableau ci-dessous, j’ai cliqué dans la cellule B3, puis j’ai tapé la fonction : =AUJOURDHUI(). Excel affiche : 21/08/2022. Rappelle-toi, une date, c’est un nombre. Donc tu peux utiliser ce « nombre » dans des calculs. Dans la cellule B4, j’ajoute 1 à AUJOURDHUI(), j’obtiens la date de demain. Dans la cellule B5, j’ôte 1 à AUJOURDHUI(), j’obtiens la date d’hier. ASTUCE : Si tu utilises souvent le même fichier, glisse la fonction AUJOURDHUI() dans une cellule pour toujours avoir la date du jour à portée de main ! OUI, ALORS... BON... ON EST BIEN D’ACCORD QUE LE JOUR OÙ J’AI RÉDIGÉ CETTE PAGE, NOUS ÉTIONS LE 21 AOÛT 2022, CE QUI N’EST PAS LE CAS AUJOURD’HUI, AU MOMENT OÙ TOI TU TESTES CETTE FONCTION... JE DIS ÇA, JE DIS RIEN... DONC, IL Y A FORT À PARIER QUE TU N’AURAS PAS LE MÊME RÉSULTAT EN B3 ! 51 LA FONCTION JOUR() À QUOI SERT CETTE FONCTION ? La fonction JOUR() renvoie un chiffre de 1 à 31 qui correspond au numéro du jour dans une date. Attention : JOUR() renvoie un chiffre de 1 à 31, pas une Date. COMMENT ÉCRIT-ON CETTE FONCTION ? =JOUR ( une date ) La donnée transmise en paramètre peut être une date ou une référence de cellule qui contient une date. UN EXEMPLE POUR MIEUX COMPRENDRE ? Dans le tableau ci-dessous, la cellule B3 contient une date : le 14 février 2019. Dans la cellule D3, j’ai placé la formule =JOUR(B3). Excel renvoie donc 14. C’est correct. Attention, la cellule qui reçoit le résultat de la fonction JOUR() doit être au format Nombre standard, pas au format Date. Explication : si le format de la cellule est réglé sur Date, alors 14 indique le 14e jour après le 1/1/1900, donc le 14/01/1900. 52 LA FONCTION JOURSEM() À QUOI SERT CETTE FONCTION ? La fonction JOURSEM() renvoie le jour de la semaine d’une date donnée en paramètre. Le résultat est donné sous la forme d’un chiffre entier de 1 à 7. 1 pour le premier jour de la semaine, 7 pour le dernier jour. Cette fonction a besoin de deux paramètres : une date, et un chiffre qui indique le type de retour voulu. Attention : JOURSEM() renvoie un chiffre de 1 à 7, pas une Date. COMMENT ÉCRIT-ON CETTE FONCTION ? =JOURSEM( une date ; le type de retour attendu) La donnée transmise en paramètre peut être une date ou une référence de cellule qui contient une date. UN EXEMPLE POUR MIEUX COMPRENDRE ? Dans le tableau ci-dessous, la cellule B3 contient une date. Dans la cellule C3, j’ai placé la formule =JOURSEM(B3;2). Excel renvoie donc 3, ce qui pour la France correspond bien à un mercredi. COMMENT CONNAÎTRE LE « TYPE DE RETOUR » QUE JE DOIS UTILISER ? Excel est un logiciel américain et aux États-Unis, le premier jour de la semaine est un dimanche. En France, le premier jour de la semaine est un lundi. Il va falloir préciser ceci à Excel sinon, tu auras un décalage dans les jours ce qui est une erreur fréquente quand on utilise la fonction JOURSEM()). Si votre semaine démarre le dimanche (1) et se termine le samedi (7), utilisez 1 ; Si votre semaine démarre le lundi (1) et se termine le dimanche (7), utilisez 2 ; Si votre semaine démarre le lundi (0) et se termine le dimanche (6), utilisez 3. Attention, si tu ne précises pas le type de retour, Excel utilise le type 1 prévu pour les États-Unis. Il affichera donc une donnée fausse. 53 LA FONCTION MOIS() À QUOI SERT CETTE FONCTION ? La fonction MOIS() affiche le numéro du mois d’une date donnée en paramètre. Le mois qui est trouvé s’affiche sous la forme d’un chiffre entier de 1 à 12. 1 pour janvier …. 12 pour décembre. Attention : MOIS() renvoie un chiffre de 1 à 12, pas une Date. Cette fonction a besoin d’un seul paramètre : une date. COMMENT ÉCRIT-ON CETTE FONCTION ? =MOIS( une date ) La donnée transmise en paramètre peut être une date ou une référence de cellule qui contient une date. UN EXEMPLE POUR MIEUX COMPRENDRE ? Dans le tableau ci-dessous, la cellule B3 contient une date : le 19 août 2022. Dans la cellule C3, j’ai placé la formule =MOIS(B3). Excel affiche donc 8, ce qui correspond bien au huitième mois de l’année : AOÛT. 54 LA FONCTION FIN.MOIS() À QUOI SERT CETTE FONCTION ? La fonction FIN.MOIS() affiche une date correspondant au dernier jour du mois de la date donnée en paramètre. Le résultat s’affiche sous la forme d’une date. Cette fonction a besoin de deux paramètres : une date et le nombre de mois de décalage (pour afficher par exemple le dernier jour du mois dernier ou le dernier jour du mois prochain). C’est une fonction très utile dans la manipulation des dates, par exemple pour créer un calendrier. COMMENT ÉCRIT-ON CETTE FONCTION ? =FIN.MOIS( une date ; nombre de mois de décalage ) La date transmise en paramètre peut être une date ou une référence de cellule qui contient une date. Le second paramètre peut être : un 0 (mois en cours), un chiffre positif (décalage dans le futur), un nombre négatif (décalage dans le passé). UN EXEMPLE POUR MIEUX COMPRENDRE ? Dans le tableau ci-dessous, les cellules B3, B4 et B5 contiennent toutes la même date : le 16 mai 1970. Dans la cellule C3, j’ai placé la formule =FIN.MOIS(B3;0). Je ne demande pas de décalage, je veux le dernier jour du mois en cours (0). Excel affiche 31/05/1970, ce qui correspond bien au dernier jour du mois de MAI 1970. Dans la cellule C4, j’ai placé la formule =FIN.MOIS(B3;1). J’ai demandé un décalage d’un mois dans le futur (1). Excel affiche 31/06/1970, ce qui correspond bien au dernier jour du mois de JUIN 1970. Dans la cellule C5, j’ai placé la formule =FIN.MOIS(B3;-1). J’ai demandé un décalage d’un mois dans le passé (-1). Excel affiche 30/04/1970, ce qui correspond bien au dernier jour du mois d’AVRIL 1970 — avril ne comporte que 30 jours. 55 LA FONCTION ANNEE() À QUOI SERT CETTE FONCTION ? La fonction ANNEE() affiche l’année (au format Nombre) d’une date donnée en paramètre. Cette fonction a besoin d’un seul paramètre : une date. COMMENT ÉCRIT-ON CETTE FONCTION ? =ANNEE( une date ) La donnée transmise en paramètre peut être une date ou une référence de cellule qui contient une date. UN EXEMPLE POUR MIEUX COMPRENDRE ? Dans le tableau ci-dessous, les cellules B3, B4 et B5 contiennent une date. Dans la cellule C3, j’ai placé la fonction =ANNEE(B3), j’obtiens l’année 1970. Dans la cellule C4, j’ai placé la fonction =ANNEE(B4), j’obtiens l’année 1975. Attention, la cellule qui reçoit le résultat de la fonction ANNEE() doit être au format Nombre standard, pas au format Date. Explication : en C3 et C4, les cellules sont au format Nombre et affichent la bonne année. Mais en C5, le résultat attendu devrait être 1945. Rappelle-toi, dans Excel, une date c’est un nombre qui indique le nombre de jours depuis le 1/1/1900. Sur cette cellule C5, il y a un format Date appliqué sauf que ANNEE() ne renvoie pas une Date mais un Nombre. Ainsi 1945, c’est 1945 jours après le 1/1/1900. Et 1945 jours après le 1/1/1900 ça donne bien le 28 avril 1905… 56 LA FONCTION DATE() À QUOI SERT CETTE FONCTION ? La fonction DATE() permet de fabriquer une date valide en combinant trois valeurs au format Nombre. Cette fonction a besoin de trois paramètres : une valeur pour l’année, une valeur pour le mois et une valeur pour le jour. COMMENT ÉCRIT-ON CETTE FONCTION ? =DATE( année ; mois ; jour ) Les données transmises en paramètres peuvent être des valeurs saisies en dur ou bien une référence de cellule qui contient une valeur. QUELQUES EXEMPLES POUR MIEUX COMPRENDRE ? Dans la cellule B3, j’ai le Nombre 1970. Dans la cellule C3, j’ai le Nombre 5. Dans la cellule D3, j’ai le nombre 16. Dans la cellule E3, j’utilise la fonction DATE() avec comme paramètre pour l’Année la cellule B3, pour le Mois la cellule C3 et pour le Jour la cellule D3. J’obtiens une date valide, au format Date : 16/05/1970. Le traitement est identique pour les lignes 4 et 5. Ligne 8, il y a un problème ! Attention à bien saisir les années sur 4 chiffres ! 07 (dans la cellule B8) a été considéré comme 1907 et pas comme 2007. Ligne 9, on recule dans le temps. Ici, la valeur -3 indique à Excel qu’il doit reculer de trois mois dans l’année 2005. Décembre, Novembre, Octobre > nous voici arrivé en Septembre. Ligne 10, j’ai indiqué 45 jours en cellule D10. Il n’y a que 31 jours dans un mois au maximum. Pas de souci, on ne trompe pas Excel comme ça, il fait le calcul. Nous sommes en juillet, donc 31 jours. 45-31, ça fait donc 14 jours de trop. Nous voici arrivé au 14 août ! 57 LES FONCTIONS LOGIQUES 58 FOI DE DUPONT, TOUT CECI NE JE DIRAIS MÊME PLUS, TOUT ME PARAÎT PAS TRÈS LOGIQUE. CECI NE ME PARAIT PAS TRÈS LOGIQUE, FOI DE DUPOND. SALUT LES AMIS ! PERMETTEZ ! NOUS SOMMES HEU… VOUS FAITES EN SERVICE, ET EN SERVICE, QUOI DANS MON JE… NOUS NE CONNAISSONS PAS COURS ? D’AMIS ! BOTUS ET MOUCHE COUSUE, C’EST NOTRE DENISE 59 LA FONCTION SI() À QUOI SERT CETTE FONCTION ? La fonction SI() sert à faire un test et permet d’afficher un résultat différent selon que le résultat du test est VRAI ou FAUX. COMMENT ÉCRIT-ON CETTE FONCTION ? =SI( test logique ; valeur si vrai ; valeur si faux ) Le test fait appel aux opérateurs logiques (voir la page sur les opérateurs logiques) et les paramètres Valeur si vrai et Valeur si faux peuvent être soit du texte, soit des valeurs, soit des références à des cellules, soit des formules de calcul. QUELQUES EXEMPLES POUR MIEUX COMPRENDRE ? Ligne 3, on vérifie que les deux notes sont identiques. Le test vérifie que B3 = C3. Si c’est VRAI, alors j’affiche le mot « Identique », si c’est FAUX, j’affiche le mot « Différent ». En ligne 3, le résultat du test est VRAI, mais en ligne 4 le résultat du test est FAUX. Important : lorsque tu veux afficher du texte, pense à le placer entre des guillemets. Lignes 7 et 8 on demande à la fonction SI() d’afficher le contenu de la cellule B1 si le test est VRAI et le contenu de C1 si le résultat du test est FAUX. Le test vérifie que la note est supérieure à 5. Jacques réussi le test (smiley souriant) tandis que Pierre à échoué (smiley triste). Ligne 11 et 12, le test est le même mais on affiche du texte en fonction du résultat : « Beau travail » pour les notes supérieures à 5 et « Peux mieux faire » pour les notes qui ne passent pas ce cap. ET SI() TU ARRÊTAIS DE DIRES DES AVEC DES SI() ON ÂNERIES ? POURRAIT METTRE PARIS DANS UNE BOUTEILLE 60 LA FONCTION ET() À QUOI SERT CETTE FONCTION ? La fonction ET() analyse le résultat de plusieurs tests logiques (2 minimum). Si tous les tests logiques donnent VRAI, alors le résultat est VRAI. Si l’un des tests logiques donne FAUX, alors le résultat sera FAUX. COMMENT ÉCRIT-ON CETTE FONCTION ? =ET( test logique 1 ; test logique 2 ; test logique 3 ; … ) ET() comme SI() fait appel aux opérateurs logiques. QUELQUES EXEMPLES POUR MIEUX COMPRENDRE ? Sur la lignes 3 (et 4), j’ai besoin de savoir si l’élève a obtenu une note supérieure ou égale à 15 dans les deux matières. Si c’est VRAI, l’élève est reçu. Si c’est FAUX, l’élève est recalé. À la Ligne 3, la fonction ET() réalise deux tests : Est-ce que C3>=15 ? : 18 est supérieur ou égal à 15 ? : VRAI Est-ce que D3>=15 ? : 15 est supérieur ou égal à 15 ? : VRAI Les deux tests donnent un résultat VRAI, le résultat fourni par ET() est VRAI. À la Ligne 4, la fonction ET() réalise deux tests : Est-ce que C3>=15 ? : 15 est supérieur ou égal à 15 ? : VRAI Est-ce que D3>=15 ? : 12 est supérieur ou égal à 15 ? : FAUX Les deux tests donnent un résultat VRAI, le résultat fourni par ET() est VRAI. La fonction ET() renvoie VRAI ou FAUX. Si on veut afficher autre chose, par exemple « Reçu » ou « Recalé », on peut combiner avec la fonction SI(). =SI ( ET ( test 1 ; test 2 ; ) ; valeur si vrai ; valeur si faux ) =SI ( ET ( C7>=15 ; D7>=15 ) ; "Reçu" ; "Recalé") Voici notre première formule avec des fonctions imbriquées ! 61 LA FONCTION OU() À QUOI SERT CETTE FONCTION ? La fonction OU() analyse le résultat de plusieurs tests logiques (2 minimum) et affiche VRAI si un seul de ces tests a pour résultat VRAI. Si tous les tests sont FAUX, alors le résultat est FAUX. COMMENT ÉCRIT-ON CETTE FONCTION ? =OU( test logique 1 ; test logique 2 ; test logique 3 ; … ) OU() comme SI() fait appel aux opérateurs logiques. QUELQUES EXEMPLES POUR MIEUX COMPRENDRE ? Sur la lignes 3 (et 4 et 5), j’ai besoin de savoir si l’élève a obtenu une note supérieure ou égale à 15 dans l’une OU l’autre des deux matières. Si c’est VRAI, l’élève est reçu. Si c’est FAUX, l’élève est recalé. À la Ligne 3, la fonction OU() réalise deux tests : Est-ce que C3>=15 ? : 18 est supérieur ou égal à 15 ? : VRAI Est-ce que D3>=15 ? : 15 est supérieur ou égal à 15 ? : VRAI Les deux tests donnent un résultat VRAI, le résultat fourni par OU() est VRAI. À la Ligne 4, la fonction OU() réalise deux tests : Est-ce que C3>=15 ? : 15 est supérieur ou égal à 15 ? : VRAI Est-ce que D3>=15 ? : 12 est supérieur ou égal à 15 ? : FAUX L’un des deux test donne un résultat VRAI, le résultat fourni par OU() est VRAI. À la Ligne 5, la fonction OU() réalise deux tests : Est-ce que C3>=15 ? : 13 est supérieur ou égal à 15 ? : FAUX Est-ce que D3>=15 ? : 12 est supérieur ou égal à 15 ? : FAUX Malheureusement pour Paul, aucun des tests ne donne un résultat VRAI, le résultat fourni par OU() est FAUX. Paul est recalé. La fonction OU() renvoie VRAI ou FAUX. Comme pour la fonction ET() on peut combiner avec SI() pour afficher un texte plus « parlant ». 62 LA FONCTION SIERREUR() À QUOI SERT CETTE FONCTION ? La fonction SIERREUR() permet de renvoyer un résultat personnalisé lorsqu'une formule génère une erreur. S’il n’y a pas d’erreur, alors c’est le résultat de la formule qui est renvoyé. Les codes d’erreur interceptés sont les suivants : #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? ou #NULL!. (Voir la page sur les codes d’erreur). COMMENT ÉCRIT-ON CETTE FONCTION ? =SIERREUR( formule ; valeur si erreur ) QUELQUES EXEMPLES POUR MIEUX COMPRENDRE ? Lignes 3, 4 et 5, on ne gère pas les erreurs En D3, la formule est correcte, le résultat s’affiche. En D4, il y a une erreur dans la formule. Une division par zéro est impossible. Le Code d’erreur #DIV/0! s’affiche. En D5, le code #VALEUR! est causé par la présence d’une lettre en C5, alors que pour faire cette opération nous avons besoin d’un chiffre. Lignes 8, 9 et 10, on gère les erreurs avec SIERREUR() En D8, la formule est correcte, le résultat s’affiche. En D9, il y a une erreur dans la formule, la division par zéro. Grâce à SIERREUR(), j’affiche « hum hum … » au lieu du code #DIV/0!. En D10, le code #VALEUR est causé par la présence d’une lettre en C5. Grâce à SIERREUR() j’affiche « --- » au lieu du code #VALEUR!. En D1, j’essaie de faire la somme de toutes les valeurs entre la cellule B2 et la cellule B10. Mais j’ai oublié le séparateur de plage ( : ) dans ma formule. Excel devrait afficher un code d’erreur #NOM? Indiquant qu’il ne comprend pas. Grâce à SIERREUR() j’ai choisi de ne rien afficher (deux guillemets = rien). PAS D’INQUIÉTUDE MON CHER DUPONT, AVEC SIERREUR TOUT EST SOUS CONTRÔLE 63 LA FONCTION SOMME.SI() À QUOI SERT CETTE FONCTION ? Cette fonction permet de faire des additions en fonction d’un critère. On peut additionner les valeurs de la plage qui est évaluée, ou une autre plage. COMMENT ÉCRIT-ON CETTE FONCTION ? =SOMME.SI( plage évaluée ; critère recherché ; plage additionnée ) Le premier critère (plage évaluée) est l’étendue de cellule dans laquelle on va chercher le critère. Plage additionnée fait référence à la page qui contient les données que l’on va additionner. Si on ne précise pas ce critère optionnel, l’addition se fait dans la plage évaluée définie au premier critère. QUELQUES EXEMPLES POUR MIEUX COMPRENDRE ? En cellule D6, j’ai indiqué la plage C2:C5 comme plage dans laquelle rechercher le critère. Mon critère est tout ce qui est supérieur à 10 (>10). Je n’ai pas précisé la plage à additionner, c’est donc la même plage (la plage évaluée) qui va être utilisée pour effectuer l’addition des valeurs. Il y a 3 notes supérieures à 10 : 15, 12 et 15. Le résultat est donc 42. En cellule D7, je demande à la fonction d’additionner les points obtenus par les filles. Ma plage de recherche est B2:B5, c’est dans cette colonne que se trouvent les F que je recherche. En critère, j’indique donc F, puis j’indique quelle colonne additionner (C2:C5). Le résultat pour les filles est de 27 points. En cellule D8, je fais le même travail pour les garçons. Je remplace juste mon critère F par M. Le résultat est 24.. ÉCRASANTE VICTOIRE DES 3 PETITS POINTS. FILLES ! FRIMEUSE ! 64 LA FONCTION NB.SI() À QUOI SERT CETTE FONCTION ? Encore une fonction qui sert à compter ! Mais cette fois on va compter des cellules ! NB.SI() sert à compter combien de cellules d’une page répondent à un critère spécifié. COMMENT ÉCRIT-ON CETTE FONCTION ? =NB.SI( plage à compter ; critère ) QUELQUES EXEMPLES POUR MIEUX COMPRENDRE ? Reprenons notre liste de garçons et de filles. En cellule D7, je veux savoir combien d’élèves ont obtenus une note supérieure ou égale à 15. Pour ça j’indique en premier la plage qui contient les notes (C2:C6) puis le critère (>=15). Le résultat est 4 puisque 4 notes sont supérieures ou égales à 15. La cellule contenant le chiffre 9 n’a pas été comptée. En cellule D8, je veux savoir combien il y a de filles dans le groupe. Pour ça, j’indique la plage B2:B6 comme plage de recherche et le critère recherché F. Résultat : 3 filles. En D9, même travail pour rechercher les garçons. MAIS C’EST PÔ JUSTE ! Y’A PLUS DE FILLES QUE DE GARÇONS ! 65 LES FONCTIONS TEXTE 66 MON CHER DUPONT, SAVIEZ- EH OUI MON CHER DUPOND, VOUS QUE EXCEL NE FAIT PAS ET JE DIRAIS MÊME PLUS, QUE COMPTER ? NON SEULEMENT IL SAIT IL SAIT ÉGALEMENT TRÈS COMPTER MAIS IL EXCELLE BIEN JOUER AVEC LE TEXTE ! AUSSI AVEC LE TEXTE ! 67 LA FONCTION GAUCHE() À QUOI SERT CETTE FONCTION ? La fonction GAUCHE() permet d’extraire un certain nombre de caractères à gauche d’une chaîne de texte. COMMENT ÉCRIT-ON CETTE FONCTION ? =GAUCHE( texte ; Nombre de caractères ) Si le paramètre optionnel (nombre de caractères) n’est pas renseigné, alors par défaut sa valeur est de 1 caractère. QUELQUES EXEMPLES POUR MIEUX COMPRENDRE ? En B3, le mot Mercredi (8 caractères). Je n’ai pas précisé combien de caractères je veux extraire dans la formule GAUCHE() en C3, donc par défaut la fonction extrait un seul caractère (la lettre M). En C4, j’ai précisé à la fonction GAUCHE() que je veux extraire 3 caractères. Le résultat est donc MER. Attention, GAUCHE() prend en compte les espaces. En C5, j’ai demandé à GAUCHE() de sortir les 3 premiers caractères du texte présent en B5. Le résultat affiché est A, un espace, B. Donc 3 caractères. HÉ DUPONT, TU AS VU ? MA MAIN GAUCHE, C’EST CELLE OÙ J’AI LE POUCE À DROITE ! 68 LA FONCTION DROITE() À QUOI SERT CETTE FONCTION ? La fonction DROITE() permet d’extraire un certain nombre de caractères à droite d’une chaîne de texte. COMMENT ÉCRIT-ON CETTE FONCTION ? =DROITE( texte ; Nombre de caractères ) Si le paramètre optionnel (nombre de caractères) n’est pas renseigné, alors par défaut sa valeur est de 1 caractère. QUELQUES EXEMPLES POUR MIEUX COMPRENDRE ? En B3, le mot Mercredi (8 caractères). Je n’ai pas précisé combien de caractères je veux extraire dans la formule DROITE() en C3, donc par défaut la fonction extrait un seul caractère (la lettre I). En C4, j’ai précisé à la fonction DROITE() que je veux extraire 3 caractères. Le résultat est donc EDI. Attention, DROITE() prend en compte les espaces. En C5, j’ai demandé à DROITE() de sortir les 3 derniers caractères du texte présent en B5. Le résultat affiché est E, un espace, F. Donc 3 caractères. JE DIRAIS MÊME PLUS MON CHER DUPOND ! MA MAIN DROITE, C’EST CELLE OÙ J’AI LE POUCE À GAUCHE ! C’EST À N’Y RIEN COMPRENDRE… 69 LA FONCTION NBCAR() À QUOI SERT CETTE FONCTION ? La fonction NBCAR() permet de compter le nombre de caractères qui composent une chaine. Le plus souvent on s’en sert sur du texte mais on peut aussi l’utiliser avec des nombres. COMMENT ÉCRIT-ON CETTE FONCTION ? =NBCAR( texte ) La donnée transmise en paramètre peut être une chaine de texte, un nombre ou une référence de cellule qui contient la chaine à compter. QUELQUES EXEMPLES POUR MIEUX COMPRENDRE ? La cellule B3 contient le texte LUNDI. En C3, la formule NBCAR() compte le nombre de caractères et indique bien 5. Idem pour B4 avec MERCREDI qui donne un résultat de 8 caractères en C4. Attention, NBCAR() compte les espaces. En C5, la formule NBCAR() ne fait pas d’erreur car c’est bien 11 caractères qui se trouvent dans la cellule B5. La cellule B8 contient un nombre composé de 9 chiffres. NBCAR() affiche le résultat correct en cellule C8. En cellule B9, il semble y avoir bien plus que 5 caractères non ? Erreur. Dans cette cellule au format Date, en réalité se cache le chiffre 44793, soit, 5 caractères. En cellule B10 aussi il y a un piège. L’espace et le signe € n’existent pas. C’est un format monétaire qui permet de les afficher. En réalité, il y a seulement le chiffre décimal 123,45, soit 6 caractères. VOILÀ UNE AH AH ! DÉTROMPE TOI ! FONCTION QUI NE COMBINÉ À D’AUTRES ME PARAÎT PAS FONCTIONS DE TEXTE, TRÈS UTILE … NBCAR() EST UN OUTIL 70 INDISPENSABLE ! LA FONCTION TROUVE() À QUOI SERT CETTE FONCTION ? La fonction TROUVE(), permet de trouver la position de départ d’une chaîne de texte dans une autre chaîne de texte. COMMENT ÉCRIT-ON CETTE FONCTION ? =TROUVE( texte cherché ; texte dans lequel chercher ; n° de départ ) QUELQUES EXEMPLES POUR MIEUX COMPRENDRE ? Dans la cellule C3, la formule TROUVE() cherche le signe @ dans la cellule B3. Je n’ai pas précisé de position de départ donc la recherche démarre depuis le premier caractère. Le symbole @ est trouvé en 8e position. Attention, TROUVE() fait la différence entre les majuscules et les minuscules (pour utiliser un jargon technique, on dit que la « CASSE » est respectée). Dans la cellule C6, la fonction TROUVE() cherche un p minuscule. Le premier P majuscule est ignoré, et le résultat affiche donc 11, la position du premier p minuscule trouvé. À l’inverse, dans la cellule C7, on cherche un P majuscule, qui est en première position et le résultat afficher par TROUVE() est donc 1. Lorsque plusieurs caractères correspondants sont trouvés, TROUVE() n’affiche que le premier. Pour rechercher un espace comme en C11, il suffit de l’indiquer entre des guillemets « ». Le paramètre optionnel n° de départ permet de commencer la recherche à l’endroit désiré. Dans les cellules C14 et C15, la recherche démarre à partir du 4e caractère. On ne s’occupe pas des 3 premières lettres et de l’espace. Dans la cellule C14, TROUVE() découvre la lettre a en 8e position. En C15 la lettre a est trouvée en 14e position. 71 LA FONCTION MAJUSCULE() À QUOI SERT CETTE FONCTION ? Encore une fonction destinée à te faire gagner du temps. Je vais prendre un cas classique : tu as reçu une liste de personnes et tu as les noms et les prénoms écris de manière disparate. Un coup il y a des majuscules, un coup nom, bref, c’est un peu le bazar. La fonction MAJUSCULE() met le texte en majuscules ! COMMENT ÉCRIT-ON CETTE FONCTION ? =MAJUSCULE( texte ) QUELQUES EXEMPLES POUR MIEUX COMPRENDRE ? En colonne B j’ai des noms écris un peu n’importe comment. Je veux les passer en majuscules. En cellule C2 (et suivantes) j’ai placé la formule =MAJUSCULE(B2). Je me retrouve donc avec le nom SÉVERIN écris en capitales. HEU.. LES FILLES… TOUJOURS C’EST PAS NOUS PRÊTES À MAINTENANT.. SOMMES FAIRE UN Z’ÊTES GÊNANTES FONCTIONS BON MOT … LÀ …. JUMELLES… 72 LA FONCTION MINUSCULE() À QUOI SERT CETTE FONCTION ? La fonction MINUSCULE() sert à …. COMMENT ÉCRIT-ON CETTE FONCTION ? =MINUSCULE( texte ) QUELQUES EXEMPLES POUR MIEUX COMPRENDRE ? Rien de compliqué, j’ai repris exactement le même tableau et j'ai changé la formule MAJUSCULE() par MINUSCULE(). Comme tu peux le voir, le texte est maintenant en minuscule. C’EST BON LES FILLES ! VOUS POUVEZ Y ALLER ! PFFF… TROP TARD… OUAIS.. PLUS ENVIE Bon, ce que ces demoiselles voulaient te dire c’est que MAJUSCULES() et MINUSCULE() sont deux fonctions qui travaillent très souvent de concert. Mais, il y a un troisième membre à ce duo et je vais donc te présenter la formule NOMPROPRE(). 73 LA FONCTION NOMPROPRE() À QUOI SERT CETTE FONCTION ? Cette fonction s’utilise presque tout le temps avec MAJUSCULE() et MINUSCULE(). NOMPROPRE() permet de mettre une Majuscule au début de chaque mot d’un texte fourni en paramètre. COMMENT ÉCRIT-ON CETTE FONCTION ? =NOMPROPRE( texte ) QUELQUES EXEMPLES POUR MIEUX COMPRENDRE ? En colonne A j’ai des prénoms et en colonne B j’ai des noms. J’ai repris la même liste que dans l’exemple précédent donc tout est écris un peu n’importe comment. Ce que je veux c’est le prénom et le nom écrits en minuscules mais avec la première lettre en majuscule. Pour ça je suis passé par deux étapes : J’ai tout passé en minuscules (cellules C2 à C6), puis j’ai utilisé ces résultats avec la formule NOMPROPRE() dans les cellules C8 à C12. C’EST TOUT DE MÊME BEAUCOUP PLUS PROPRE AINSI. 74 LA FONCTION TEXTE() À QUOI SERT CETTE FONCTION ? La fonction TEXTE() permet de convertir un nombre en texte et de modifier la manière dont il est affiché en lui appliquant un format particulier. C’est utile pour afficher des nombres dans un format plus lisible ou si tu souhaites concaténer du texte et des nombres dans une même cellule par exemple. En réalité les cas d’usage sont très nombreux ! Pour bien comprendre comment fonctionne cette fonction, va lire le chapitre sur les formats de cellule. COMMENT ÉCRIT-ON CETTE FONCTION ? =TEXTE ( valeur ; format_texte ) QUELQUES EXEMPLES POUR MIEUX COMPRENDRE ? Dans les cellules B3 et B4, j’ai placé un numéro de téléphone. Il y a deux problèmes à régler : problème n°1, le 0 a disparu. Problème n°2 c’est difficile à lire avec les chiffres tout collés. En cellule C3, j’ai demandé à Excel de m’afficher B3 au format texte et je lui ai fourni un format d’affichage à respecter : 2 chiffres, un espace, 2 chiffres, un espace, etc. J’ai utilisé un 0 pour symboliser les chiffres, ce qui signifie que si il manque un chiffre à gauche, Excel affichera un 0 à la place. On voit bien le résultat : 06 01 02 01 01. En C4, la formule est identique mais j’ai utilisé le symbole # au lieu du 0 dans mon masque (c’est comme ça que ça s’appelle). Le # n’affiche pas de zéro s’il manque un chiffre. On voit donc un résultat différent : 6 01 02 01 01. En B7, j’ai écris une date. Grâce à la fonction TEXTE(), je vais afficher le jour et le mois dans les cellules C7 et C8. En C9, j’ai un problème : je veux écrire « Mois de mai », mais quand j’utilise l’opérateur &, le résultat me renvoie un nombre : Mois de 25704. Et oui, on ne peut pas utiliser du texte et des nombres dans la même formule ! Pour régler mon problème, je vais utiliser TEXTE() pour afficher le mois au format texte. Le résultat en C10, ça fonctionne ! 75 C’EST QUOI UNE FONCTION IMBRIQUÉE ? Comme nous l’avons vu, une fonction attend un ou plusieurs paramètres. Mais ces paramètres peuvent être des valeurs, des références de cellules, ou même une autre fonction ! UN EXEMPLE POUR MIEUX COMPRENDRE ? Dans l’exemple ci-dessous, je veux afficher Excellent si la moyenne de l’élève est supérieur à l’objectif indiqué en B2, et Correct dans le cas contraire. Nous allons devoir faire deux opérations : calculer la moyenne de l’élève, effectuer un test pour savoir si cette note est supérieure à l’objectif. En cellule C5, je calcule la moyenne avec la fonction MOYENNE(). J’obtiens 16,5. En cellule C6, je fais un test avec la fonction SI(). Je regarde si le résultat de ma fonction MOYENNE() en C5 est supérieur à l’objectif mentionné en B2. Comme c’est bien le cas, Excel affiche Excellent. Mais … en C5, nous avons une formule n’est-ce pas ? Alors pourquoi ne pas remplacer la référence C5 par cette formule ? C’est ce que j’ai fait en C8. Je fais exactement la même chose en une seule formule grâce à l’imbrication de fonctions. UNE FORMULE POUR LES LIER TOUTES … Attention - lorsque tu imbriques des fonctions, tu ne dois pas copier le signe =. Les occasions d’imbriquer des fonctions sont nombreuses et tu vas découvrir que certaines fonctions ne donnent leur plein potentiel que lorsqu’elles sont imbriquées entre-elles ! Un autre exemple : que dis-tu de ceci ? NOMPROPRE( MINUSCULE() & MINUSCULE() ) 76 ON SE FAIT UNE PAUSE ? Nous l’avons vu, certaines fonctions sont sensibles à la CASSE. Il est donc très intéressant de pouvoir modifier la manière dont est écrit notre texte avec les trois fonctions MAJUSCULE(), MINUSCULE() et NOMPROPRE(). Revenons sur ce mot : CASSE. C’est un mot qui veut dire Majuscule ou Minuscule. D’ailleurs peut-être que tu as déjà entendu un ami, un collègue, te dire qu’il a écrit en Bas de Casse (pour dire qu’il a écrit en minuscules) ou au contraire en Haut de Casse (pour indiquer qu’il a écrit en majuscules). Mais sais-tu d’où vient le mot CASSE ? Et bien ce mot remonte à l’époque de Gutenberg. Tu le sais sûrement, Gutenberg n’est pas l’inventeur de l’imprimerie, mais il a inventé les caractères mobiles d’imprimerie, ce qui a révolutionné en son temps la production des livres et a permit au savoir de se répandre plus facilement en Europe et dans le Monde. Merci à Gutenberg pour ça. Ces caractères mobiles en plomb étaient rangé dans un grand casier en bois appelé la CASSE. Dans ce grand casier on trouvait autant de petites cases que de lettres de l’alphabet (plus les caractères de ponctuation). Dans la partie haute du casier étaient rangés les caractères majuscules (Haut de Casse) et dans les cases du bas étaient rangées les minuscules (Bas de Casse). Et voilà ! Une petite info pour briller en société à ton prochain dîner entre amis ! Pour en savoir plus : https://fr.wikipedia.org/wiki/Casse_(typographie) https://fr.wikipedia.org/wiki/Gutenberg 77 PARTIE 3 EXPERT 78 79 LA FEUILLE À OUTILS 80 Moi, j’ai pris l’habitude de l’appeler INDEX, OUTILS ou BASE. C’est une feuille dans laquelle je place toutes les données uniques qui vont être utilisées dans d’autres feuilles. Souviens-toi, je t’ai dit qu’une valeur ne doit jamais être en double dans un classeur. Si tu est prof, peut-être auras-tu besoin d’avoir toujours sous la main ta liste d’élèves. Si tu travailles sur un calendrier, alors peut-être auras-tu besoin d’avoir des listes déroulantes avec les jours de la semaine, les mois. Si tu travailles sur des données comptables, peut-être auras tu besoin de stocker les différents Taux de TVA. Si tu gères un fichier client, peut-être auras tu besoin de conserver la liste des villes de France, la liste des pays dans le monde, etc. Tu l’as compris, l’objectif est de fournir des listes pré-remplies pour que les utilisateurs n’ait pas besoin de faire de saisie et d’éviter ainsi les erreurs de frappe ! En cas de mise à jour des données, il suffit de modifier la lise dans la feuille à outils pour que toutes les listes se mettent à jour partout dans le classeur. Pour savoir quoi mettre dans cette feuille à outils, la règle est simple : j’ai besoin de cette info à plusieurs endroits ? Alors je la place dans la feuille à outils. 81 LES FORMATS DE CELLULE 82 83 C’EST QUOI UN FORMAT DE CELLULE ? Pour faire simple, c’est la façon dont Excel va afficher une valeur dans une cellule. Selon le format utilisé, une même valeur peut être représentée de façon différente. Tu te souviens quand nous avons travaillé sur les dates ? Si tu écris le chiffre 1 dans une cellule, Excel utilise par défaut un format nombre. Mais si tu demande à Excel d’afficher cette cellule au format Date, il affichera 01/01/1900, pourtant c’est toujours le chiffre 1 qui est dans la cellule. Ce qui a changé c’est son format d’affichage. PRENONS QUELQUES EXEMPLES : Dans la cellule B1, j’ai tapé un nombre : 150,5. Dans les cellules B3 à B13, c’est le même nombre mais affiché avec des formats différents. En B4, j’ai utilisé le format monétaire. En B5, le format comptabilité. En B6, nous avons un format Date. 150 = le 150e jour après le 1/1/1900. En B7 le format spécial heure, je vais y revenir en détail. En B8, le format pourcentage. En B9, le format Fraction (150,5 = 150 et un demi), En B10, c’est la notation scientifique. En B11, le format Texte. (Note que le texte est bien aligné à gauche), En B12 un des formats spéciaux prédéfinis d’Excel (ici le téléphone), En B13, un format personnalisé, qui affiche le texte « Habitants » après la valeur. Pour modifier le format de cellule, il suffit de cliquer dans la cellule et de choisir le format que tu veux utiliser. Tous ces formats d’affichage sont disponibles soit en faisant un clic droit dans la cellule et en choisissant le menu Format de cellule, soit en utilisant le menu Format disponible dans le Ruban. 84 C’EST QUOI UN FORMAT PERSONNALISÉ ? Parfois il arrivera que tu veuilles modifier la façon dont s’affichent les données pour fournir une information plus claire. Dans l’exemple en dessous, les cellules B5 et B11 contiennent toutes les deux la somme des bonbons, c’est à dire 167. En B5, c’est le format par défaut qui est utilisé, dans la cellule B11, j’ai utilisé un format personnalisé. C’est toujours le même chiffre, mais j’ai demandé à Excel de l’afficher suivi du texte « Bonbons ». Pour créer ce format, fais un clic droit sur ta cellule, choisis Format de cellule et choisis la catégorie Personnalisée. Dans le champ Type, écris ceci : 0 « Bonbons » Pour ajouter du texte après la valeur, tu dois le mettre entre guillemets. Valide par Ok. 85 CRÉER SON FORMAT PERSONNALISÉ Dans le champ Type, tu peux créer tous les formats personnalisés que tu veux. Un format personnalisé peut être composé de 1 à 4 sections. Chaque section est séparée par un point- virgule ( ; ). Dans une cellule on peut trouver 4 choses : un chiffre positif, un chiffre négatif, un 0 ou du texte. Chiffres positifs ; Chiffres négatifs ; Zéro ; Texte Grâce au format personnalisé, on va pouvoir décider comment s’affichent ces différents types de valeurs et pour ça tu va devoir utiliser des caractères spéciaux dont voici la liste et le rôle : CARACTÈRES PRINCIPAUX 0 Affiche un chiffre, même non significatif. Si le chiffre est absent, Excel affiche 0. # Affiche un chiffre significatif (les zéros en début de chiffre ne sont pas affichés) ? Permet d’aligner les chiffres sur la décimale $ + - / ( ) : ! ^ & ' ~ { } < > = et Espace sont des caractères que tu peux utiliser sans les placer entre guillemets « ». Ils s’afficheront tel-quel. * Ce symbole répète le caractère suivant pour remplir la largeur de la colonne. _ Ce caractère simule la largeur d’un caractère. @ Affiche le texte de la cellule. CARACTÈRES POUR LES DATES: j Affiche le jour sous la forme d’un nombre de 1 à 31. jj Affiche le jour sous la forme d’un nombre de 01 à 31. jjj Affiche le jour en abrégé (lun pour lundi). jjjj Affiche le jour en entier. m Affiche le mois sous la forme d’un nombre de 1 à 12. mm Affiche le mois sous la forme d’un nombre de 01 à 12. mmm Affiche le mois en abrégé(Jan pour janvier) mmmm Affiche le nom du mois en entier. aa Affiche l’année avec deux chiffres (par exemple, 22 pour 2022). aaa Affiche les années avec 4 chiffres. 86 CARACTÈRES POUR LES HEURES h Affiche les heures sous la forme d’un nombre 0 à 23. hh Affiche les heures sous la forme d’un nombre 00 à 23. m Affiche les minutes sous la forme d’un nombre 0 à 59. mm Affiche les minutes sous la forme d’un nombre 00 à 59. s Affiche les secondes sous la forme d’un nombre 0 à 59. ss Affiche les secondes sous la forme d’un nombre 00 à 59. AM/PM Affiche le temps comme sur une horloge de 12 heures. LES CARACTÈRES CONDITIONNELS < Plus petit que > Plus grand que = égal >= Plus grand ou égal =MOYENNE($K$18:$K$23) Valide par OK. Automatiquement, les moyennes générales de chaque élève apparaissent en rouge si elles sont supérieures ou égales à la moyenne de la classe et on a 4 gagnants ! HEU.. WAHOU, ÇA BIEN ENTENDU ! MARCHE ! SUPER !.. PRÉPARE TOI À MAIS, HONNÊTEMENT, DÉCOUVRIR TOUTE LA FAUT QUE TU PUISSANCE DE LA MISE M’EXPLIQUES LÀ … EN FORME CONDITIONNELLE 114 La première chose à faire, c’est de sélectionner la plage sur laquelle tu veux que ta mise en forme conditionnelle s’applique. Ensuite, tout réside dans la formule de calcul utilisée. =K18>=MOYENNE($K$18:$K$23) J’ai tapé K18, c’est la première cellule de la plage. Ici quelque chose d’important. J’ai utilisé une référence relative. Puisque toutes les cellules de la colonne vont devoir être évaluées les unes après les autres.. Après K18, ce sera K19, K20, etc. jusqu’à K23. J’aurais pu écrire $K18 puisque la colonne ne vas pas changer. Mais comme nous n’avons qu’une seule colonne ici ce n’est pas utile. Ensuite j’ai tapé >= ce qui signifie Supérieur ou Égal, Puis j’ai utilisé la fonction MOYENNE($K$18:$K$23) qui effectue la moyenne de toutes les moyennes des élèves. Le résultat comme on l’a vu est 12,11. Cette fois-ci j’ai bloqué la plage ($K$18:$K$23) car dans ce calcul, toutes les cellules de la colonne K vont devoir être comparées à la moyenne issue de la même plage. K18=K18:K23, etc. La mise en forme conditionnelle va donc faire le test 6 fois de suite et à chaque fois que le test est VRAI, alors la mise en forme va s’appliquer : 1. K18 >= 12,17 = FAUX 2. K19 >= 12,17 = VRAI => je passe en rouge 3. K20 >= 12,17 = VRAI => je passe en rouge 4. K21 >= 12,17 = FAUX 5. K22 >= 12,17 = VRAI => je passe en rouge 6. K23 >= 12,17 = VRAI => je passe en rouge Un dernier exemple sous la forme d’une question : à ton avis, qu’est-ce que cette formule va provoquer comme mise en forme conditionnelle appliquée à la plage A30:D35 ? La solution est dans le classeur support de cours. 115 LES FONCTIONS DE RECHERCHE 116 117 LA FONCTION RECHERCHEV() À QUOI SERT CETTE FONCTION ? Cette fonction est un incontournable dès que tu dois chercher une information dans une grande quantité de données. La fonction RECHERCHEV() permet de chercher verticalement une valeur dans une plage, et une fois trouvée, elle permet d’afficher le contenu d’une cellule présente sur la même ligne sur la droite. COMMENT ÉCRIT-ON CETTE FONCTION ? =RECHERCHEV(ce que je cherche ; la plage dans laquelle rechercher ; le n° de colonne qui contient ce que je veux ; la précision du résultat). UN EXEMPLE POUR MIEUX COMPRENDRE ? J’ai ici une liste de produits, avec leurs références et leurs prix. En E5, je demande à RECHERCHEV() de : 1. trouver le contenu de E3 ($E$3), 2. dans la plage A1:C11 ($A$1:$C$11), 3. puis de m’afficher ce qui se trouve sur la même ligne mais dans la deuxième colonne (2, la référence de la colonne où se trouve ce que je veux afficher) 4. et je demande une valeur exacte (0). Je fais la même chose pour le prix en E7. La précision du résultat dépend du paramètre optionnel que tu as saisi ou pas. C’est soit 1 soit 0, ou en d’autre termes, soit VRAI, soit FAUX. Si tu mets 1 ou rien, Excel considère que c’est VRAI, et il va te renvoyer une valeur proche : la dernière valeur avant celle qui dépasse. Si tu indiques 0 (c’est ce qu’on utilise le plus souvent), Excel va chercher la valeur exacte, et s’il ne trouve pas, il affichera le code erreur #N/A!. Attention : RechercheV() ne sait chercher que de la gauche vers la droite. Et si tu ajoutes une colonne dans la plage de recherche, les résultats 118 seront faux. INDEX()/EQUIV() permettent de dépasser ces limitations. HEU ! DIS MOI J’AI EXCELLENTE PAS COMPRIS CETTE QUESTION ! ET BIEN OPTION DE PRÉCISION PARFOIS, TU NE DE RÉSULTAT. À QUOI POURRAS PAS ÇA SERT D’AVOIR UNE TROUVER UNE VALEUR VALEUR PROCHE ? EXACTE. JE VAIS TE MONTRER ! Imaginons que tu gères un tableau de remboursement de frais kilométriques pour les vendeurs de ta société. Selon le nombre de kilomètres effectués, le taux de remboursement change. En cellule D2, j’ai inséré le nombre de kilomètres réalisés par un vendeur pour savoir combien je vais devoir le rembourser. De 0 à 1000 kilomètres, je rembourse, 0,50 € du kilomètre, De 1000 à 2000 kilomètres, je rembourse 0,45 € du kilomètre, De 2000 à 3000 kilomètres, je rembourse 0,40 € du kilomètre, De 3000 à 4000 kilomètres, je rembourse 0,35 € du kilomètre, Etc. Ici, ma fonction RECHERCHEV() va rechercher la valeur 3095, mais ne la trouvera pas dans la colonne A. Je ne peux donc pas demander de trouver une valeur EXACTE. En indiquant VRAI (ou 1) en paramètre, je demande à RECHERCHEV() de m’afficher la valeur la plus proche avant d’atteindre une valeur qui dépasse la valeur cherchée. A2, A3 et A4 sont plus petits que ma valeur cherchée. En revanche A5 est plus grand. RECHERCHEV() utilise donc la dernière valeu

Le petit Excel illustré PDF

Document Details

Tags

Related

Summary

Full Transcript

Upgrade to continue