Un cours résumé pour maîtriser le tableur Excel pour les étudiants qui préparent le DCG (Diplôme de Comptabilité et Gestion) . Le tableur est un outil généraliste de productivité personnelle dont l’usage s’est généralisé pour organiser des feuilles de calcul permettant de traiter des données, de créer des modèles réutilisables et d’effectuer des simulations. Il offre également des fonctions de présentation graphique des données. C’est pourquoi il est devenu un outil indispensable pour de nombreux professionnels, en particulier ceux de la gestion.
Présentation générale du tableur Excel
Le tableur est un outil logiciel qui permet d’organiser, d’analyser et de présenter des données à travers des opérations, des tris, des classements et des graphiques. Les données sont stockées et manipulées à l’intérieur d’une feuille de calcul, qui se présente comme un quadrillage en lignes et en colonnes qui délimite des cellules. Un classeur comporte plusieurs feuilles de calcul. Il prend l’extension de fichier xlsx pour les classeurs Excel (Microsoft), ods pour Calc (OpenOffice).
Un couple de valeurs (colonne, ligne) constitue une adresse ou référence de cellule. Une plage de cellules est un ensemble de cellules contiguës qu’il est possible de repérer en utilisant les références des cellules extrêmes ou en les nommant. Le tableur permet de mettre en forme le contenu des cellules (choix des polices de caractères, taille, couleur, gras, italique…) ; définir un format de données (nombre, date…) ; masquer / afficher des lignes et des colonnes ; etc.
Utilisation d’une feuille de calcul
Contenu des cellules
Trois types d’informations peuvent être saisies dans une cellule d’une feuille de calcul :
- un libellé, texte explicatif pour faciliter la compréhension ;
- une donnée, c’est-à-dire une information élémentaire (un nom, un nombre, une date…) ;
- un résultat obtenu par une formule de calcul.
Formules de calcul et fonctions
Une formule de calcul est une expression qui fournit une valeur. Elle est construite à l’aide des opérateurs arithmétiques (+ – * /) et fait intervenir des constantes, le contenu des cellules (référencées par leur adresse ou leur nom), et des fonctions. Le tableur possède de nombreuses fonctions intégrées. Ces fonctions effectuent un ensemble d’opérations prédéfinies utilisant une ou plusieurs valeurs (arguments de la fonction) et renvoyant une ou plusieurs valeurs (résultats de la fonction).
Recopie des formules
Une fois la formule saisie dans une cellule, l’opérateur peut faire glisser l’expression saisie sur les cellules contiguës (à droite, à gauche, au-dessus ou en dessous).
Cependant, il peut être nécessaire de figer certaines références :
Adressage relatif : la référence (lettre de colonne / numéro de ligne) est modifiée lorsque la formule est recopiée (ou glissée). C’est l’adressage par défaut.
Adressage absolu : la référence correspond à un emplacement fixe de la feuille de calcul et ne sera pas modifiée lors d’une recopie si elle est soit nommée, soit « figée » par des $ (ex. : $B$27).
Adressage mixte : il est possible de ne bloquer que la référence de colonne ($ devant la lettre de la colonne), ou de ligne ($ devant le numéro de la ligne, ex. : $B27 ou B$27).
Cellules situées sur des feuilles de calcul différentes
Il est possible de travailler avec des cellules appartenant à des feuilles de calcul différentes. Pour les distinguer, on fait précéder la référence de la cellule par le nom de la feuille de calcul suivi d’un point d’exclamation (sur Excel) ou d’un point (sur Calc).
Ex. : La référence Feuil2!B27 renvoie à la cellule B27 de la feuille de calcul appelée « Feuil2 ».
Fonctions Excel de base : Fonctions arithmétiques et statistiques
Fonction SOMME
La fonction SOMME permet de faire le total des valeurs contenues dans plusieurs cellules. La fonction ajoute les cellules contenant des valeurs numériques et ignore les autres. La zone concernée peut être continue (références séparées par : (deux points)) ou discontinue (plusieurs plages séparées par ; (point-virgule)).
Ex. : SOMME(B2:B11;D2:D11) renverra la somme des valeurs situées dans les cellules B2 à B11 et D2 à D11 sans tenir compte des cellules intermédiaires.
De même qu’il est possible de nommer une cellule, il est possible de nommer une plage de cellules : Sélectionner la cellule ou plage de cellules, puis Insertion/Nom/ Définir ou bien saisir le nom dans la zone d’affichage de la sélection, à gauche de la barre de saisie. Attribuer des noms à des cellules ou plages de cellules permet d’y faire référence sans avoir à préciser la feuille, à condition que le nom soit défini pour l’ensemble du classeur.
Gestion des arrondis
Les fonctions ARRONDI, ARRONDI.INF et ARRONDI.SUP permettent d’afficher la valeur arrondie la plus proche, directement inférieure ou supérieure. Les arguments de la fonction sont une valeur et la précision de l’arrondi. 0 est un arrondi à l’unité, – 1 est un arrondi à la dizaine, – 2 à la centaine etc. alors que 1 sera un arrondi au dixième, 2 au centième…
Ex. : =ARRONDI(15/4;0) donne la valeur 3 et non 3,75 La fonction ENT permet de n’afficher que la partie entière d’une valeur. Ainsi, =ENT(21/2) renverra la valeur 10, au lieu de 10,50. ENT(ref) est équivalent à ARRONDI.INF(ref ; 0).
Il ne faut pas confondre la définition du nombre de décimales à afficher en cliquant sur le bouton correspondant de la barre d’outils et l’arrondi. Dans le premier cas, seul l’affichage est modifié. Dans le second cas, c’est la valeur contenue dans la cellule qui change.
Dénombrement
Les fonctions NBVAL et NB permettent respectivement de renvoyer le nombre de cellules contenant des valeurs (quelles qu’elles soient), et le nombre de cellules contenant des valeurs numériques.
La fonction NB.SI renvoie le nombre de cellules dont la valeur correspond à la valeur spécifiée ou répond à la condition spécifiée.
Ex. : NB.SI(B2:B20 ;JAJ) renvoie le nombre de cellules de la plage B2 : B20 dont la valeur est JAJ.
NB.SI(C2:C20;>=100) renvoie le nombre de cellules dont la valeur est > ou = à 100.
La fonction NB.VIDE renvoie le nombre de cellules d’une plage de cellules, qui ne contiennent ni nombre, ni texte, ni formule de calcul.
Valeurs significatives d’une série
Les fonctions MOYENNE et MEDIANE calculent la moyenne ou la médiane des valeurs numériques contenues dans une plage de cellules.
Les fonctions MIN et MAX renvoient respectivement les valeurs minimales et maximales trouvées sur les plages de cellules de référence.
Les fonctions GRANDE.VALEUR et PETITE.VALEUR renvoient la n-ième valeur d’une série à partir de la plus grande ou de la plus petite.
Ex. :
Fonctions Excel logiques
Fonction conditionnelle
La fonction SI respecte la syntaxe suivante : SI (Condition ; valeur si vrai ; valeur si faux).
Si la condition est vérifiée, la fonction renvoie les valeurs spécifiées par ’valeur si vrai’, sinon celles spécifiées par ’valeur si faux’.
Imbrication de plusieurs fonctions SI
Il est possible de démultiplier le nombre de cas testés en ayant recours à l’imbrication de plusieurs fonctions SI.
Ex. : la formule =SI(B12>=1 000 ; 3,5 % ; SI(B12>=500 ; 2 % ; 0)) envisage trois possibilités : – si la cellule B12 est supérieure ou égale à 1 000, alors la fonction va renvoyer 3,5 %
– sinon, si la cellule B12 est supérieure ou égale à 500, alors la fonction renvoie 2 %,
– sinon la formule renvoie 0.
Fonctions logiques
La fonction SI peut intégrer les fonctions OU et ET dans l’écriture de la condition testée :
= OU(Condition 1;Condition 2;Condition 3 …) . Il suffit que l’une des conditions soit vérifiée pour que la fonction renvoie VRAI
= ET(Condition 1;Condition 2;Condition 3 …). Il faut que toutes les conditions listées soient vérifiées pour que la fonction renvoie VRAI
Recherche et références
Fonctions de recherche
La fonction RECHERCHEV recherche verticalement une valeur spécifiée sur la 1re colonne d’une plage de cellules, puis renvoie la valeur qui correspond à celle-ci dans une autre colonne. Elle comprend quatre arguments : le 1er indique la valeur recherchée ; le 2e indique la plage dans laquelle la recherche va être menée ; le 3e argument indique le numéro de la colonne dans laquelle se trouve la valeur à retourner (1 étant le numéro de la 1re colonne, dans laquelle s’effectue la recherche). Enfin le 4e argument correspond à valeur approchée et prendra la valeur VRAI (la fonction doit rechercher une valeur proche) ou FAUX (la fonction doit trouver une correspondance exacte).
La fonction RECHERCHEH est similaire à RECHERCHEV mais la recherche se fait de façon horizontale. La recherche s’effectue sur la 1re ligne de la plage, et renvoie la valeur correspondant sur la n-ième ligne de la plage.
La formule dans la cellule A6 affiche le libellé correspondant au code saisi dans la cellule A5.
A6 = RECHERCHEH(A5;A2:D3;2;FAUX)
Fonctions de recherche et argument VRAI
L’argument VRAI, s’il est choisi pour 4e argument des fonctions RECHERCHEV ou RECHERCHEH renvoie la valeur directement inférieure à celle recherchée. Ex. :
La formule en B2 est =RECHERCHEV(A2;D$2:F$4;3;VRAI). Les $ permettent de la recopier vers le bas. Avec l’argument faux, la formule ne donnerait rien car la valeur exacte des montants de la colonne A n’existe pas dans le tableau des taux de remise (colonne D).
Si l’argument VRAI/FAUX est omis, la valeur par défaut est VRAI.
Fonctions de date et heure
La fonction AUJOURDHUI() renvoie la date du jour. Les parenthèses sont nécessaires bien qu’il n’y ait pas d’argument à indiquer pour cette fonction. La fonction MAINTENANT() renvoie la date du jour et l’heure en minutes et secondes.
Les fonctions ANNEE, MOIS et JOUR extraient d’une cellule qui contient une date respectivement l’année, le numéro de mois et la date du jour. JOURSEM renvoie le numéro de la journée (lundi = 1, mardi = 2 etc.) Exemple:
D2 = ENT((AUJOURDHUI()-E2)/365,25)
AUJOURDHUI()-E2 donne l’ancienneté en nombre de jours.
/365,25 transforme le nombre de jours en nombre d’années (en tenant compte approximativement des années bissextiles) ENT ne retient que le nombre entier d’années
Remarques : annee(aujourdhui()) – annee(C2) donne un nombre d’années indépendant du fait que les dates considérées soient en début ou en fin d’année.