Aperçu des sections

  • D. Les formules et les fonctions

    Les formules ou les fonctions sont des types de données particulières. Elles prennent le plus souvent en compte les valeurs enregistrées dans d'autres cellules de la feuille de travail. La position de ces cellules est indiquée par leurs références.


    a) LES FORMULES

    Une formule permet d'effectuer des calculs et d'afficher le résultat dans une cellule et ce, à partir de nombres et/ou d'autres cellules.


    *       les cellules intervenant dans le calcul sont définies par leurs références (lettre de colonne et N° de ligne)

    *       des opérateurs les mettent en relation : + (plus), - (moins), * (multiplié), / (divisé).

    *       Les formules peuvent être arithmétiques, logiques ou texte selon les opérateurs


    <clic g> dans la cellule où doit s'afficher le résultat du calcul
    taper = (signe égal) -
    l'indicateur "entrer" s'inscrit dans la barre d'état à gauche
    <clic g>
     sur la première cellule
    saisir l'opérateur
    <clic g>
     sur la seconde cellule
    valider par    ou <entrée>

     

    Lors de la saisie, utiliser le pavé numérique à droite du clavier sur lequel vous avez les chiffres de 1 à 9, les opérateurs et une touche <entrée>
    l'idéal est d'utiliser la souris dans la main droite pour désigner les cellules ou les plages et d'avoir la main gauche sur le pavé numérique pour les opérateurs (+ - / *)



    SYNTAXE

    *       opérande
    données ou références de cellules contenant les données faisant l'objet du calcul

    *       opérateurs
    symboles indiquant l'opération à effectuer

    *       séparateurs
    symboles permettant de combiner les différentes opérations (parenthèses)


    Ne pas oublier de faire précéder les références de la cellule de = (égal)


    LISTE DES OPÉRATEURS

     ARITHMÉTIQUES

    +                   plus
    -                   
    moins
    *                   
    multiplié
    /                    divisé
    %                  pourcentage
    ^                   puissance

    LOGIQUES
                   =                        égal
                   <                       
    inférieur à
                   >                       
    supérieur à
                   <>                     
    différent
                   <=                     
    inférieur ou égal
                   >=                     
    supérieur ou égal
                   &
                           concaténation
                   #
    non#              non logique
                   #
    et#                  et logique
                   #
    ou#                 ou logique


    (1) LA FORMULE ARITHMÉTIQUE

    C'est la plus courante ; elle permet de manipuler des chiffres.


    =a5+b5     additionne les cellules a5 et b5

    =a5-1        soustrait 1 à la valeur de la cellule a5

    =a5*3        multiplie la valeur de la cellule a5 par 3

    =a5/2        divise la valeur de la cellule a5 par 2



    Dans le classeur "commande de fleurs", effacer les données de la colonne "valeur"
    Faire le total des quantités avec l'opérateur arithmétique "+"
    Calculer la colonne "valeur" pour chaque type de fleur en multipliant quantité par prix
    Enregistrer et fermer le classeur "commande de fleurs"



    (2) LA FORMULE LOGIQUE

    La formule logique est un type particulier de formule qui effectue un test et donne le résultat vrai/faux.


    LA FORMULE LOGIQUE

    +a5=10          donne vrai si a5 est égal à 10 et faux autrement

    +a5>1             donne vrai si a5 est supérieur à 1 et faux autrement


    Dans un classeur vierge, reproduire les 2 exemples ci-dessus

     

    La formule logique, associée à un format conditionnel est un excellent moyen d'introduire une formule de vérification dans un tableau


    (3) LA FORMULE TEXTE

    La formule texte manipule les chaines de caractères au lieu de chiffres.


    LA FORMULE TEXTE

    +"tic"&"tac"            affiche tictac

    +B2 & B3      affiche tictac si le contenu de B2 est "tic" et celui de B3 "tac"
    B2 et B3 sont remplacés par leur contenu


    Dans le même classeur, reproduire l'exemple ci-dessus
    Fermer le classeur sans l'enregistrer


    (4) LA FORMULE MATRICIELLE

    La formule matricielle est un type particulier de formule à employer lorsque la même formule est répétée dans une plage de cellules adjacentes. Elle permet de ne saisir qu'une seule fois la formule pour l'ensemble des cellules, cette dernière faisant référence à l'ensemble des cellules concernées.


     

    sélectionner la plage de cellules
    saisir la formule en incluant toutes les plages de référence
    (pas d'accolades)
    <ctrl><maj><entrée>

     

    Pour la modifier, il suffit de modifier une cellule de la plage puis de nouveau <ctrl><maj><entrée>

     

    EXCEL convertit automatiquement les lettres minuscules d'une formule (ou d'une fonction) en majuscules. S'il ne le fait pas, c'est que celle-ci n'est pas valide ou n'est pas reconnue comme telle. Vérifier la saisie.
    Si la formule est reconnue comme valeur mais n'est pas acceptée, "ERR" s'affiche - Vérifier alors la syntaxe

     

    Créer un nouveau classeur
    Se rendre en "feuille 3"
    Saisir le tableau suivant
    (les formules et fonction sont affichées en clair pour la compréhension du tableau mais doivent être saisies comme des formules et non des données)
    Enregistrer le classeur sous le nom "bilan" et le fermer


    b) LES FONCTIONS

    Les fonctions sont des formules intégrées et prédéfinies suivies d'un ou plusieurs arguments et obéissant à une syntaxe précise. Dans un tableau, elles s'utilisent souvent en combinaison avec des formules et sont précédées du signe = (égal).


    Une fonction s'exprime toujours sous la forme :

    =FONCTION(arg1;arg2;arg3;argn)

    =                          égal    indique à Excel qu'une fonction suit

    (...)                       parenthèses       les parenthèses encadrent les arguments de la fonction

    arg1 arg2..argn définissent les différentes variables dont la valeur est à
                                renseigner (emplacement, valeur, chaîne, condition)

    ;                           point virgule      caractère séparateur d'arguments indispensable

    "  "                       guillemets encadrent un argument texte


    (1) SOMME

    C'est la fonction la plus utilisée et la plus utile.


     onglet accueil
     groupe "édition"
    (dernier bloc)

    <clic g> sur la cellule ou doit s'inscrire la somme
    <clic g>
    sur
     

     

    La fonction utilisée par Excel pour faire la somme est normalement la fonction "somme" mais en fonction du contexte (format automatique de tableau), ce peut être aussi la fonction "sous.total" : les références peuvent en être des adresses de cellules mais aussi une formulation du type "cette ligne de telle colonne à telle colonne" 

     

     

    Pour une autre fonction que "somme" (moyenne, nombre), <clic g>  sur  de  et sélectionner la fonction adéquate ( propose toujours la fonction "somme" même si une autre fonction a été utilisée précédemment) ou sur  de la barre de formule

     

    Dans un tableau de valeurs, pour le 3ème total de ligne, Excel fera un total vertical et non horizontal - le rectifier en faisant glisser la souris sur la plage adéquate.

     

    Ouvrir le classeur "commande de fleurs"
    Remplacer dans le total des quantités les opérateurs arithmétiques par des sommes comme ci-dessous puis totaliser la colonne "valeur"
    Enregistrer puis fermer


    (2) LA SOMME D'UN CARRÉ

    Lorsque le tableau que l'on crée est un tableau carré (somme des lignes = somme des colonnes), la somme du tableau peut être effectuée en un clic de souris.


    souris gauche.gifbouton gauche

    sélectionner tout le tableau plus 1 ligne et une colonne vides pour les totaux
    <clic g> sur

     

    Ouvrir le classeur "somme" feuille "exercice"
    Faire les totaux des tableaux en un minimum d'opérations
    Effacer ces totaux en une seule opération
    Effectuer le total global des lignes des tableaux (somme de toutes les activités de toutes les régions) - le garder ouvert


    Saisir le tableau suivant sur la feuille 1 d'un nouveau classeur
    Faire les totaux en une opération
    Enregistrer le classeur sous le nom "transport" puis le fermer



    (3) LES FONCTIONS COURANTES

    Elles sont disponibles par le biais de l'assistant fonction ou en direct.


     barre formule

    <clic g> sur   de la barre de formule 
    sélectionner la catégorie puis la fonction (
    ou poser une question)
    l'assistant fonction rentre en action
    définir les arguments
     pour valider


     onglet accueil
     groupe "édition"
    (dernier bloc)

    <clic g> dans la cellule de saisie
    <clic g> sur  de   <clic g>  sur la fonction
    modifier éventuellement la plage
    <entrée> pour valider



     

    Ouvrir le classeur "commande de fleurs"
    Effacer les formules des lignes de total
    Indiquer sur cette ligne la moyenne de chaque colonne
    Remettre des sommes hormis pour la colonne "prix"
    Enregistrer et fermer le classeur



    Pour remplacer dans une cellule une fonction par une autre, il faut cliquer dans la barre de formule puis sur le  de la zone nom pour choisir la nouvelle fonction



    c) LE CALCUL AUTOMATIQUE

    La barre d'état de Excel affiche une zone dans laquelle figurent un certain nombre d'informations relatives à la sélection en cours et calculées en temps réel.


     barre d'état

    effectuer la sélection
    consulter la barre d'état (
    en bas à droite)
    <clic d> pour cocher les fonctions à afficher

     

    Les procédures standard de sélection (monozone ou multizones) peuvent être appliquées
                - <faire glisser>
                - <ctrl> pour ajouter (touche )
                - <maj> pour étendre (touche )


     


    Dans le classeur "somme"
    Effectuer une sélection de valeur (ex : de B2 à E5)
    Afficher les fonctions "somme" et "moyenne" et visualiser les résultats dans la barre d'état
    Tester les autres fonctions puis fermer le classeur


    d) LES RÉFÉRENCES RELATIVES / ABSOLUES

    Les références définissent la position de la cellule par rapport aux lignes et par rapport aux colonnes (comme dans un jeu de bataille navale). Elles peuvent aussi être définies par rapport à la feuille de travail pour une plage 3D (en 3 dimensions).


    Les références de la cellule peuvent être absolues, relatives ou mixtes

    *                               :                références relatives

    *                            :                références absolues

    *         ou         :                références mixtes


                           :                  plage 3D avec noms de feuille par défaut
        
    :                  plage 3D avec noms de feuille spécifiques


    PRINCIPES :

    *       références relatives : référence par défaut
    la position de la plage ou de la cellule dans le repère (orthonormé) est définie par rapport à la position de la cellule devant recevoir l'entrée.

    *       références absolues : toutes les coordonnées sont précédées du signe $.
    la position est définie par rapport à l'origine des axes et reste invariable.

    *       références mixtes : une des coordonnées de la cellule est précédée du signe $. Sa position est définie par rapport à l'origine de l'un des axes et reste invariable par rapport à celui-ci. L'autre coordonnée varie.


    la modification de la structure de la feuille de travail (insertion ou suppression de lignes ou de colonnes) est dans tous les cas prise en compte mais c'est au niveau de la copie ou de la saisie de données que les résultats peuvent être très différents selon le type de référence utilisé

     

     
    effectuer la saisie
    appuyer sur la touche  <f4> une 1ère fois
    appuyer sur la touche  <f4> une 2ème fois
    appuyer sur la touche  <f4> une 3ème fois
    au sein de la barre de formule en cours de saisie ou de modification pour faire varier la nature des références

     

    Pensez à utiliser les références absolues pour le dénominateur lorsque vous effectuez un calcul de pourcentage ou lorsque vous multipliez les valeurs d'une colonne par un coefficient

     

    L'indication des références d'une cellule ou d'une plage doit s'effectuer en désignant cette cellule ou cette plage avec la souris, et non en saisissant manuellement ces références

     

    Ouvrir le classeur "transport"
    En colonne G, ajouter un libellé "pourcentage"
    Calculer le pourcentage de la 1ère activité par rapport au total des activités
    Mettre le dénominateur en référence absolue ($F$6)
    Copier en glissant le résultat sur les cellules du dessous
    Enregistrer


     


    L'indication des références d'une cellule ou d'une plage s'effectue en désignant cette cellule ou cette plage avec la souris, et non en saisissant manuellement ces références


    e) LES RÉFÉRENCES 3D

    Un calcul concernant plusieurs feuilles s'effectue aussi naturellement qu'un calcul interne à une feuille.


    souris gauche.gifbouton gauche

    saisir la formule ou la fonction
    <clic g> sur l'onglet de 1ère feuille
    sélectionner la plage en faisant glisser la souris
    <maj> enfoncé
    <clic g> sur l'onglet de dernière feuille
    valider
    la syntaxe d'Excel est la suivante : feuille!plage
    des parenthèses encadrent les références dans la plupart des formules

     

    Pour faire dans la feuille 5 la somme de la cellule B2 à partir de la feuille 1 jusqu'à la feuille 4, il faut effectuer les opération suivantes :
    <clic g> sur la cellule B2 de la feuille 5 (celle devant recueillir la somme)

    <clic g>  sur  somme (ne pas se préoccuper de ce qu'Excel inscrit entre parenthèses)
    <clic g> sur l'onglet de la feuille 1  -  <clic g> en B2
    <maj enfoncé>   -  <clic g> sur l'onglet de la feuille 4
    <clic g> sur
    Excel inscrit :  en B2 de la feuille 5
    soit somme de la cellule b2 à partir de la feuille 1 jusqu'à la feuille 4

     

    Les procédures standard de sélection (monozone ou multizones) peuvent être appliquées
                - <faire glisser>
                - <ctrl> pour ajouter (touche )
                - <maj> pour étendre (touche )



    Dans le classeur transport, saisir le tableau suivant sur la feuille 2

    Saisir le tableau suivant sur la feuille 3

    Saisir le tableau suivant sur la feuille 4

    Effectuer le total de chacun des tableaux et un calcul de % comme sur la 1ère feuille
    Sur la feuille 5 , recopier l'un des tableaux, effacer les données spécifiques et
    Effectuer une consolidation (un total) des 4 premières feuilles avec des formule de type  , enregistrer puis fermer


    Ouvrir le classeur bilan
    Sur la feuille 3, effacer les valeurs des lignes chiffre d'affaires et charges (comme ci-dessous)

    Saisir le tableau suivant sur la feuille 1 :

    Saisir le tableau suivant sur la feuille 2 :

    Sur la feuille 3, remplacer les valeurs des lignes chiffre d'affaires et charges par les références aux cellules de total de chacune des feuilles saisies (comme ci-dessous)

    Enregistrer les modifications puis fermer le classeur

    • B. La colonne

      L'insertion, comme la suppression de colonnes, fait varier les lettres des colonnes suivantes, en plus ou en moins, du nombre de colonnes insérées ou supprimées. Les formules, que leurs références soient relatives ou absolues, sont automatiquement adaptées à la nouvelle structure de la feuille.

      1.         LA SÉLECTION DES COLONNES

      La sélection d'une ou plusieurs colonnes s'effectue de préférence avec la souris.

      <clic g> sur la lettre d'une colonne la sélectionne
      ou
      <faire glisser> le pointeur sur les lettres de colonne pour sélectionner

      <ctrl><espace> sélectionne la colonne a laquelle appartient la ou les cellules sélectionnée

       

       

      Comme dans la plupart des sélections, utiliser <ctrl> pour ajouter une sélection et <maj> pour étendre une sélection

       

      Dans le classeur "commande de fleurs"
      Sélectionner la colonne B
      Sélectionner les colonnes B et D
      Sélectionner les colonnes B à D

      2.         L'INSERTION DES COLONNES

      Les colonnes insérées viennent s'ajouter à gauche de la première colonne sélectionnée.

      sélectionner autant de colonnes que de colonnes à ajouter
      <clic g>
       sur


      sélectionner autant colonnes que de colonnes à ajouter
      <clic d> dans la sélection
      il y a autant de colonnes vierges d'insérées que de colonnes sélectionnées

       

      Si les données sur lesquelles vous travaillez ont été reconnues en tant que tableau Excel (mettre sous forme de tableau), Excel insérera automatiquement une ligne si une seule cellule (ou plusieurs cellules mais sur la même ligne) est sélectionnée, une colonne si plusieurs cellules de lignes différentes sont sélectionnées

       

      Plutôt que de faire une sélection élaborée, il est aussi possible de ne sélectionner que des cellules et de préciser la commande avec  de  

       

      Si Excel ne comprend pas, il demande de préciser ce qui est à insérer (cellules, lignes, colonnes)

       

      Dans le classeur "commande de fleurs"
      Insérer deux colonnes devant la colonne B
      Les appeler "nom scientifique" et description"
      Renseigner ces colonnes

      3.         LA SUPPRESSION DES COLONNES

      La suppression de colonnes se fait selon les mêmes principes que l'insertion.

      sélectionner autant de colonnes que de colonnes à supprimer
      <clic g>
       sur


      sélectionner autant de colonnes que de colonnes à supprimer
      <clic d> dans la sélection
      les colonnes sélectionnées sont supprimées

       

      Si les données sur lesquelles vous travaillez ont été reconnues en tant que tableau Excel (mettre sous forme de tableau), Excel supprimera automatiquement une ligne si une seule cellule (ou plusieurs cellules mais sur la même ligne) est sélectionnée, une colonne si plusieurs cellules de lignes différentes sont sélectionnées

       

      Plutôt que de faire une sélection élaborée, il est aussi possible de ne sélectionner que des cellules et de préciser la commande  avec  de

       

      Si Excel ne comprend pas, il demande de préciser ce qui est à insérer (cellules, lignes, colonnes)

       

      Dans le classeur "commande de fleurs"
      Supprimer les deux colonnes créées devant la colonne B
      Annuler la suppression

      4.         LA LARGEUR DES COLONNES

      La largeur des colonnes dans Excel est par défaut de 10,71 caractères (80 pixels) ; cette largeur peut être modifiée et adaptée aux données saisies.

       

      sélectionner les colonnes à modifier
      pointeur sur le bord droit de la case de lettre d'une colonne (
      forme du pointeur )
      étirer la colonne vers la droite ou vers la gauche pour adapter sa largeur
      si plusieurs colonnes sont sélectionnées, toutes sont modifiées

       

      sélectionner les colonnes
      <clic g>
       sur


      sélectionner les colonnes
      <clic d> dans la sélection

       

       

      saisir la largeur
       pour valider

       

      Il est possible d'adapter la largeur des colonnes sélectionnées à la taille de la plus longue donnée contenue dans une des cellules de chacune des colonnes ou de leur rendre leur taille d'origine.

      sélectionner les colonnes concernées
      pointeur sur le bord droit de la case de lettre d'une colonne
      (idem largeur)
      <double clic>

      sélectionner les colonnes
      <clic g>
       sur  

       

      Dans le classeur "commande de fleurs"
      Mettre la largeur de la colonne de libelles à 13,55
      Ajuster les autres colonnes

      5.         LE MASQUAGE DES COLONNES

      Il peut être utile de ne pas afficher ou de ne pas imprimer certaines données ; masquer les colonnes contenant ces données permet d'atteindre cet objectif.

       

      sélectionner les colonnes concernées
      pointeur sur le bord droit de la case de lettre de la dernière colonne de la sélection
       étirer le bord droit de la case jusqu'à la case de la colonne précédente (
      pointeur :)
      les colonnes ne sont plus affichées

       

      sélectionner les colonnes (ou clic g>  dedans)
      <clic g>
       sur
       


      sélectionner
      les colonnes à masquer
      <clic d> dans la sélection

       

       

       

      Même si les colonnes ne sont plus visibles, les lettres de colonne manquantes indiquent à l'utilisateur que des colonnes sont masquées

      6.         L'AFFICHAGE DES COLONNES MASQUÉES

      Le processus de réaffichage des colonnes masquées est sensiblement le même.

       

       

      pointeur sur la limite ou les numéros de colonne manquent
      faire glisser jusqu'à la largeur de colonne  désirée (
      forme du pointeur )
      <double clic> affiche les colonnes en adaptant leur largeur à celle des données les plus longues

       

      sélectionner les colonnes masquées
      + 1 colonne avant et 1 colonne après <clic g>
       sur


      sélectionner les colonnes masquées
      + 1 colonne avant et 1 colonne après <clic d>  dans la sélection

       

       

      Dans le classeur "commande de fleurs"
      Masquer les colonnes "nom scientifique" et "description"
      Réafficher les colonnes