Excel 2010 - formules de calcul et fonctions
Après avoir étudié les bases du tableur Excel et la validation des données, nous proposons dans ce tutoriel de décrypter l’utilisation des formules de calcul et la mise en place de fonctions. Seules les formules couramment utilisées seront expliquées, Excel propose en effet un nombre important de fonctions et toutes ne peuvent être détaillées.
Consignes à respecter
Toute formule commence par le signe égal =
Excel ne distingue pas la casse (majuscule ou minuscule)
Après validation, le résultat d’une formule apparaît dans la cellule. La formule s’affiche dans la barre de formule.
Une formule est indépendante lorsqu’elle n’utilise pas la valeur d’une autre cellule (ex : =8+5). Une formule est dépendante dans le cas contraire (ex : = A1+B3)
Une formule peut contenir une ou plusieurs fonctions.
Les opérateurs
Opérateurs de calcul : ils sont classés par ordre de priorité : puissance, multiplication et division, addition et soustraction. On utilise des parenthèses pour préciser la priorité des calculs (ex : =(25+3)*4).
Opérateur alphanumérique : l’esperluette & permet de concaténer (lier) des chaines de caractères.
Référence relative
Une formule faisant appel à une cellule avec sa référence relative mémorise sa position par rapport à celle dans laquelle la formule est saisie.
Les références relatives sont fréquemment utilisées lors de copie d’une formule sur plusieurs cellules.
Référence absolue
Lors d’une recopie, la valeur reste celle de la cellule d’origine. Le symbole dollars $ fige la colonne ou la ligne qui le suit.
Référence à une autre feuille ou un autre classeur
Pour effectuer une référence à une cellule d’une autre feuille de calcul, dans la cellule ou l’on souhaite voir apparaître les données, saisissez = puis cliquez sur la cellule de la feuille souhaitée pour en afficher le contenu (ex =Feuil2!B3)
Attribution d’un nom
Un nom peut être attribué à une cellule, une plage de cellules, un ensemble de plages de cellules.
Il constitue une référence absolue et est utilisable dans tout le classeur.
Pour attribuer un nom, sélectionnez les cellules à nommer puis saisissez le nom dans la zone Nom située à gauche de la barre de formule et validez par Entrée.
Les noms peuvent être créés, modifiés ou supprimés depuis l’onglet Formules > groupe Noms définis > Gestionnaire de noms.
Généralités sur les fonctions
Excel dispose d’environ 300 fonctions classées par catégories. Un assistant fonction vous guide dans leur application.
Les fonctions sont accessibles via l’onglet Formules > groupe Bibliothèque de Fonctions.
Pour insérer une fonction, placez-vous dans la cellule dans laquelle vous souhaitez voir apparaître le résultat, insérer la fonction, sélectionnez les arguments et valider par Entrée.
Utilisation de l’esperluette &
Le contenu de deux (ou plus) cellules peut être concaténé dans une même et seule cellule grâce à ce symbole &.
=A1&« habite à »&A2 renvoi la phrase A1 habite à A2.
Les parenthèses permettent d’insérer un contenu texte dans cette formule.
Par exemple, nous cherchons à associer le nom et prénom d’une personne en C2 depuis 2 cellules distinctes :
Fonctions date et heure
Ces fonctions n’ont pas besoin d’arguments.
=AUJOURDHUI() renvoi la date courante.
=MAINTENANT() renvoi l’heure courante.
Par exemple, nous cherchons en H2 à connaître l’âge en fonction de la date de naissance en G2 :
Fonction SOMME automatique
Lors de l’insertion de cette fonction, Excel créé automatiquement la formule en sélectionnant les arguments les plus proches de la cellule du résultat.
Bouton Somme automatique > Somme.
=SOMME(A1:B2) effectue l’addition de A1 + A2 + B1 + B2.
Les deux points : dans une formule signifient que la sélection va de la cellule A1 jusqu’à B2.
Fonctions MIN et MAX
=MIN(A1:B2) renvoi le chiffre le plus petit entre A1, A2, B1, B2.
=MAX(A1:B2) renvoi le chiffre le plus élevé entre A1, A2, B1, B2.
Par exemple en H26, nous déterminons la quantité de stock disponible la moins élevée :
Fonction MOYENNE
Bouton Somme automatique > Moyenne
=MOYENNE(A1:B2) effectue la moyenne soit (A1 + A2 + B1 + B2)/4.
Fonctions NB et NBVAL
=NB(A1:B2) permet de compter seulement le nombre de chiffres (données numériques) dans la liste.
=NBVAL(A1:B2) compte aussi le nombre d’éléments non numériques.
Fonction SOMME.SI
Cette fonction permet de faire des additions seulement sur une partie des données si elles correspondent à un critère défini.
=SOMME.SI(plage des données à retenir ; critère ; somme de la plage des données retenues)
Par exemple, dans la cellule D26, nous cherchons à calculer la quantité de fruits achetés en fonction du pays sélectionné en D24 :
Fonction NB.SI
Cette fonction permet de compter seulement une partie des données d’un tableau si elles correspondent à un critère défini.
=NB.SI(plage des données à retenir ; critère)
Par exemple, nous cherchons combien de fois est cité un même pays dans la cellule D28 :
Fonction SI
Une formule contant cette fonction est appelée formule conditionnelle. Elle renvoie un résultat qui diffère selon qu’une expression est vraie ou fausse. L’expression contient nécessairement un opérateur logique (égal = , supérieur > , inférieur < …)
=SI(expression ; alors résultat si vrai ; sinon résultat si faux)
Par exemple, dans la cellule J2, nous créons un message d’avertissement si le stock disponible se situe en dessous de 200kg, le message sera « stock insuffisant » sinon « stock acceptable » :
Opérateurs ET et OU
Ces opérateurs permettent de créer des tests plus nuancés ou restrictifs.
L’opérateur OU permet de nuancer une expression en tenant compte de conditions qui peuvent intervenir sans s’exclure mutuellement.
L’opérateur ET permet de restreindre le test logique à des conditions qui doivent être remplie impérativement.
=SI(ET(1ère condition;2ème condition);Valeur si vrai;Valeur si faux)
=SI(OU(1ère condition;2ème condition);Valeur si vrai;Valeur si faux)
Par exemple, dans la cellule K2, nous créons un message d’avertissement « produit à recommander » si le stock de produit disponible est compris entre 0kg et 50kg :
Insérer des sous-totaux
Cette fonction permet de calculer automatiquement des sous-totaux généraux d’une colonne ou d’une ligne. Chaque colonne d’une plage de données doit comporter une étiquette dans la première ligne et contenir des informations similaires. Aucune ligne ou colonne de la plage ne doit être vide.
Sélectionnez une cellule du tableau.
Dans l’onglet Données, cliquez sur Triez de A à Z (ou l’inverse) puis sur Sous-total.
Dans la fenêtre qui s’affiche, la zone A chaque changement de permet de sélectionner la colonne dont vous voulez calculer le sous-total.
La zone Utiliser la fonction permet de choisir la formule à utiliser pour les sous-totaux (somme, moyenne, min, max…)
La zone Ajouter un sous-total à permet de déterminer la colonne contenant les valeurs à calculer.
Afficher uniquement les sous-totaux à l’écran
Une fois les sous-totaux créés, il est possible d’afficher à l’écran uniquement les résultats en les regroupant par niveaux (numéros présent en haut à droite de la feuille de calcul)
Par exemple dans la colonne H, nous créons les sous-totaux de la colonne chiffre d’affaires réalisé TTC par pays
Supprimer des sous-totaux
Onglet Données puis Sous-total, cliquez sur le bouton Supprimer tout.
