Les formules, comprendre leur fonctionnement
On va maintenant découvrir l'intérêt principal de l'utilisation d'Excel, qui réside dans la puissance de ses formules.
Place à la pratique, on va utiliser un unique classeur Excel pour ce tuto (cela nécessite donc de le sauvegarder si tout n'est pas fait en une fois).
Sommaire
- Récupérer un échantillon de données
- Mise en forme
- Interprétation des données
- Les calculs
- Les erreurs
Récupérer un échantillon de données
Il va falloir manipuler pour mieux comprendre le fonctionnement des formules.Cliquer sur le bouton ci-dessous pour copier le texte nécessaire pour la suite du tuto.
Coller ensuite ce qui a été copié dans un onglet Excel.
Voici ce que vous devez obtenir (seulement concernant le texte, car j'utilise un design personnalisé, voir ici pour créer le votre) :
Se positionner dans la cellule "A1" puis utiliser l'option "Coller" (ou Ctrl + V) pour récupérer l'ensemble des données.
Ce genre de manipulation reviendra assez souvent afin d'avoir des cas concrets directement dans Excel.
Mise en forme
Pour avoir un rendu plus lisible, il va falloir mettre en forme les données, comme ceci :
Pour obtenir ce résultat, sélectionner :
- A1:C8 → Accueil → Alignement → Centrer
- A1:C8 → Accueil → Police → Bordures → Toutes les bordures
- A1:C1 → Accueil → Alignement → Fusionner et Centrer
- A2:C2 → Accueil → Police → Gras
- C3:C8 → Accueil → Nombre → Format → Monétaire (ou Comptabilité)
Interprétation des données
Dans Excel, il est à la fois possible d'écrire du texte, des opérations, ou encore de faire des calculs. Pour que tout soit interprété comme on le souhaite, il y a des règles à respecter.On va tout de suite voir ça :
Coller ces nouvelles données dans la cellule "D1".
Après un peu de mise en forme, voici ce que l'on obtient :
Petite astuce, l'option "Reproduire la mise en forme" permet de copier une mise en forme existante d'une ou plusieurs cellules pour l'appliquer à d'autres (une vidéo de Microsoft est disponible ici).
Lorsque l'on utilise le copié collé, Excel n'applique aucun blocage. L'exercice ici, est de recopier le texte que l'on a en colonne "D" dans la colonne "E" (attention en D5 et D8 ce sont des formules à recopier, voir lesquelles depuis la barre de formule) :
On observe les différences suivantes :
- E4 et E7 → il n'y a plus d'apostrophe visible à l'écran, mais elle reste présente lorsque l'on regarde le contenu de la cellule via la barre de formule
- E6 → impossible de reproduire le texte en D6, un message d'erreur apparaît :
Il faut également savoir que le "=", le "+" et le "-" ont le même comportement :
- Pour écrire du texte, il suffit de ne pas commencer sa phrase par un =, un + ou un -.
- Pour écrire une opération qui débute par un =, un + ou un -, et la voir afficher comme du texte, on place une apostrophe ' devant.
- Pour faire des calculs et utiliser les formules dans Excel, il faut que le 1er caractère de la cellule soit un =, un + ou un -.
Les calculs
Maintenant on va pouvoir commencer à utiliser quelques formules de calculs simples :Coller ces nouvelles données dans la cellule "F1".
Après un peu de mise en forme, voici ce que l'on obtient :
Les résultats dans les colonnes G et I sont exactement les mêmes.
En comparant les formules utilisées dans les colonnes F et G, c'est là que l'on commence à comprendre tout l'intérêt du tableur Excel.
Explication des formules concernant les totaux :
- H10 → multiplication de chaque quantité par le prix
- H11 → multiplication de chaque quantité par le prix. C'est la traduction de la formule en H10 avec la formule SOMMEPROD (formule complexe). Cette formule prend 2 plages de données en compte B3:B8 et C3:C8, puis va multiplier chaque cellule de la plage B3:B8 avec la cellule correspondante de la plage C3:C8. Il n'est pas nécessaire de passer trop de temps à essayer de comprendre cette formule si ça ne vient pas, la suivante donne le même résultat plus simplement.
- H12 → cette formule nécessite un prérequis, il faut que le calcul de chaque ligne soit fait au préalable, comme c'est le cas ici dans la plage I3:I8. On additionne ensuite simplement l'ensemble des résultats.
- H13 → c'est la traduction de la formule en H12 (même prérequis) avec la formule SOMME. Cette formule prend 1 plage de données en compte I3:I8, puis va additionner chaque cellule de cette plage. C'est cette version qui est la plus utilisée, c'est également la plus rapide à écrire et la plus simple à comprendre.
P.-S. : La formule SOMMEPROD est utilisée ici simplement pour montrer qu'il y a toujours plusieurs manières d'arriver au même résultat. Il y a des manières complexes qui permettent d'arriver rapidement au résultat. Et des manières simples en décomposant au maximum le problème initial, ici avec la formule SOMME, en passant par le calcul du prix total de chaque ligne au préalable.
P.-P.-S. : Dans l'ensemble des tutos de ce site, on utilisera la manière simple en décomposant le problème au maximum. C'est, de mon point de vue, ce qui facilite le plus l'apprentissage.
Les erreurs
Lorsqu'on commence à utiliser les formules, on découvre également les erreurs.Ici, on va analyser les différents codes d'erreurs possibles pour savoir où chercher afin de les corriger.
Coller ces nouvelles données dans la cellule "J1" :
Explication des différents codes erreurs :
- #DIV/0! → assez explicite, division par 0
- #NUL! → correspond à une erreur de syntaxe dans la formule, pour corriger l'exemple, il faut écrire : "I3:I8"
- #VALEUR! → pointe une incohérence dans la formule. Dans l'exemple, I4 est monétaire avec la valeur 1,05€. On ne peut donc pas ajouter du texte à une valeur numérique.
- #REF! → problème de référence, lié à la suppression, le déplacement, ou le remplacement de cellule. Dans l'exemple, la formule demande de récupérer la valeur de la 3e colonne dans la plage G2:H8. C'est donc impossible car il n'y a que 2 colonnes dans cette plage. Cette erreur a pu se produire en supprimant la colonne I qui faisait partie de la plage au préalable.
- #NOM? → correspond à une erreur de syntaxe sur l'intitulé de la formule. Il manque le "E" de SOMME dans l'exemple.
- #NOMBRE! → erreur car le résultat est trop grand, ou trop petit. Peut également se produire si une formule attend un format numérique lorsque l'on utilise du texte.
- #N/A → apparaît lorsqu'il manque la valeur recherchée. La formule ne trouve donc aucun résultat à renvoyer.
Bravo ! On rentre dans le coeur du sujet avec ce tuto, on va maintenant pouvoir créer un tas de choses intéressantes, et découvrir d'autres formules très utiles.
Si ces informations sont nouvelles pour vous, vous avez bien mérité une pause afin de mieux assimiler toutes ces nouveautés !
