Dans cette leçon, nous vous présentons les règles de base pour créer des formules et utiliser des fonctions. Nous pensons que l’une des meilleures façons d’apprendre est la pratique, c’est pourquoi nous fournissons plusieurs exemples et les expliquons en détail. Les sujets que nous couvrirons comprennent :
- lignes et colonnes
- exemple de fonction mathématique : SUM()
- opérateurs
- précédence des opérateurs
- exemple de fonction financière : PMT(), paiement d’un prêt
- utilisation d’une fonction « string » (« string » est un raccourci pour « chaîne de texte ») à l’intérieur d’une formule et imbrication de fonctions
Les formules sont un mélange de « fonctions », d' »opérateurs » et d' »opérandes ». Avant d’écrire quelques formules, nous devons créer une fonction, mais avant de pouvoir créer une fonction, nous devons d’abord comprendre la notation des lignes et des colonnes.
Les lignes et les colonnes
Pour comprendre comment écrire des formules et des fonctions, vous devez connaître les lignes et les colonnes.
Les lignes se déroulent horizontalement et les colonnes verticalement. Pour vous rappeler lequel est lequel, pensez à une colonne qui soutient un toit – les colonnes vont de haut en bas et donc les rangées vont de gauche à droite.
Les colonnes sont étiquetées par des lettres ; les rangées par des chiffres. La première cellule de la feuille de calcul est A1 signifiant colonne A, ligne 1. Les colonnes sont étiquetées de A à Z. Lorsque l’alphabet est épuisé, Excel place une autre lettre devant : AA, AB, AC… AZ, BA, BC, BC, etc.
Exemple : Fonction Sum()
Montrons maintenant comment utiliser une fonction.
Vous utilisez les fonctions en les tapant directement ou en utilisant l’assistant de fonction. L’assistant de fonction s’ouvre lorsque vous choisissez une fonction dans le menu « Formules » ou dans la « Bibliothèque de fonctions ». Sinon, vous pouvez taper = dans une cellule et un menu déroulant pratique vous permettra de choisir une fonction.
L’assistant vous indique quels arguments vous devez fournir pour chaque fonction. Il fournit également un lien vers des instructions en ligne si vous avez besoin d’aide pour comprendre ce que fait la fonction et comment l’utiliser. Par exemple, si vous tapez =sum dans une cellule, l’assistant en ligne vous indique les arguments requis pour la fonction SOMME.
Lorsque vous tapez une fonction, l’assistant est en ligne ou à portée de vos doigts. Lorsque vous sélectionnez une fonction dans le menu « Formules », l’assistant est une boîte pop-up. Voici l’assistant contextuel pour la fonction SUM().
Pour notre première fonction, utilisons SUM(), qui additionne une liste de chiffres.
Supposons que nous ayons cette feuille de calcul contenant les plans pour budgétiser les vacances de votre famille :
Pour calculer les coûts totaux, vous pourriez écrire =b2+b3+b4+b5 mais il est plus facile d’utiliser la fonction SUM().
Dans Excel, recherchez le symbole Σ dans le coin supérieur gauche de l’écran Excel pour trouver le bouton AutoSum (les mathématiciens utilisent la lettre grecque Σ pour additionner une série de chiffres).
Si le curseur se trouve sous les chiffres du budget familial, Excel est suffisamment intelligent pour savoir que vous voulez additionner la liste des chiffres situés au-dessus de l’endroit où vous avez placé le curseur, il met donc les chiffres en surbrillance.
Appuyez sur « entrée » pour accepter la plage sélectionnée par Excel ou utilisez le curseur pour modifier les cellules sélectionnées.
Si vous regardez ce qu’Excel a mis dans la feuille de calcul, vous pouvez voir qu’il a écrit cette fonction :
Dans cette formule, Excel additionne les nombres de B2 à B9. Notez, nous avons laissé un peu de place sous la ligne 5 pour que vous puissiez ajouter au budget des vacances familiales – le coût va certainement augmenter au fur et à mesure que la liste des enfants de ce qu’ils veulent faire et où ils veulent aller s’allonge !
Les fonctions mathématiques ne fonctionnent pas avec les lettres, donc si vous mettez des lettres dans la colonne, le résultat s’affiche sous la forme « #NAME ? » comme indiqué ci-dessous.
#NAME ? indique qu’il y a une sorte d’erreur. Il peut s’agir de plusieurs choses, notamment :
- mauvaise référence de cellule
- utilisation de lettres dans les fonctions mathématiques
- omission des arguments requis
- mauvaise orthographe du nom de la fonction
- opérations mathématiques illégales comme la division par 0
La façon la plus simple de sélectionner les arguments d’un calcul est d’utiliser la souris. Vous pouvez ajouter ou retirer de la liste des arguments de la fonction en agrandissant ou en réduisant la case qu’Excel dessine lorsque vous déplacez la souris ou cliquez dans une autre cellule.
Nous avons cliqué sur le haut du carré dessiné par Excel pour retirer les « billets d’avion » du budget. Vous pouvez voir le symbole de la croix que vous pouvez dessiner pour agrandir ou réduire la plage sélectionnée.
Appuyez sur « entrée » pour confirmer les résultats.
Opérateurs de calcul
Il existe deux types d’opérateurs : mathématiques et comparaison.
Il existe d’autres opérateurs non liés aux mathématiques comme « & » qui signifie concaténer (joindre bout à bout) deux chaînes de caractères. Par exemple, = »Excel » & » is Fun » équivaut à « Excel is Fun ».
Maintenant, nous examinons les opérateurs de comparaison.
Comme vous pouvez le voir ci-dessus, les opérateurs de comparaison fonctionnent avec les nombres et le texte.
Note, si vous entrez = »a »> »b » dans une cellule, elle dira « FAUX » puisque « a » n’est pas supérieur à « b ». « b » vient après « a » dans l’alphabet, donc « a » > « b » ou « b » > « a. »
Précédence d’ordre des opérateurs
La précédence d’ordre est une idée issue des mathématiques. Excel doit suivre les mêmes règles que les mathématiques. Ce sujet est plus compliqué, alors respirez et plongeons dedans.
La préséance d’ordre signifie l’ordre dans lequel l’ordinateur calcule la réponse. Comme nous l’avons expliqué dans la leçon 1, l’aire d’un cercle est πr2, ce qui correspond à π * r * r. Ce n’est pas (πr)2.
Vous devez donc comprendre la préséance d’ordre lorsque vous écrivez une formule.
Généralement, vous pouvez dire ceci :
- Excel évalue d’abord les éléments entre parenthèses en travaillant à l’envers.
- Il utilise ensuite les règles de précédence d’ordre des mathématiques.
- Lorsque deux éléments ont la même précédence, Excel travaille de gauche à droite.
La précédence des opérateurs mathématiques est indiquée ci-dessous, par ordre décroissant.
Il existe d’autres règles de précédence liées aux chaînes de caractères et aux opérateurs de référence. Pour l’instant, nous nous en tiendrons à ce que nous venons de couvrir. Maintenant, regardons quelques exemples.
Exemple : Calcul de l’aire d’un cercle
L’aire d’un cercle est =PI() * rayon ^ 2.
En regardant le tableau ci-dessus, nous voyons que les exposants viennent avant la multiplication. Donc l’ordinateur calcule d’abord le rayon ^ 2 et ensuite il multiplie ce résultat par Pi.
Exemple : Calculer une augmentation de salaire
Disons que votre patron décide que vous faites un excellent travail et qu’il va vous accorder une augmentation de 10 % ! Comment calculeriez-vous votre nouveau salaire ?
D’abord, rappelez-vous que la multiplication passe avant l’addition.
S’agit-il de =salaire + salaire * 10 % ou de =salaire + (salaire * 10 %) ?
Supposons que votre salaire est de 100 $. Avec une augmentation de 10 %, votre nouveau salaire sera :
= 100 + 100 * 10 % = 100 + 10 = 110
Vous pouvez aussi l’écrire comme ceci :
=100 + (100 * 10 %) = 100 + 10 = 110
Dans le second cas, nous avons rendu l’ordre de préséance explicite en utilisant des parenthèses. Rappelez-vous que les parenthèses sont évaluées avant toute autre opération.
En passant, la façon la plus simple d’écrire ceci est = salaire * 110%
Quelques autres exemples
Voici un autre exemple : = 4 * 3 / 2. Quelle est la réponse ?
Nous voyons d’après les règles du tableau ci-dessus que * et / ont la même préséance. Donc Excel travaille de gauche à droite, 4 * 3 = 12 d’abord, puis divise cela par 2 pour obtenir 6.
De nouveau, vous pourriez rendre cela explicite en écrivant = (4 * 3) / 2
Qu’en est-il de = 4 + 3 * 2?
L’ordinateur voit les deux opérateurs * et +. Donc, en suivant les règles de précédence (la multiplication vient avant l’addition), il calcule d’abord 3 * 2 = 6, puis ajoute 4 pour obtenir 10.
Si vous vouliez changer l’ordre de précédence, vous écririez = (4 + 3) * 2 = 14.
Qu’en est-il de celui-ci = -1 ^ 3 ?
Alors la réponse est -3 parce que l’ordinateur a calculé = (-1) ^ 3 = -1 * -1 * -1 = -1.
Voilà donc quelques exemples d’ordre mathématique et de préséance, nous espérons que cela aide à éclaircir certaines choses sur la façon dont Excel effectue les calculs (et c’est probablement assez de mathématiques pour toute une vie pour certains d’entre vous).
Exemple : Fonction Paiement de prêt (PMT)
Regardons un exemple pour calculer un paiement de prêt.
Démarrez en créant une nouvelle feuille de calcul.
Formatez les nombres avec des signes de dollar et utilisez des décimales nulles puisque nous ne sommes pas intéressés par les centimes pour le moment car ils n’ont pas beaucoup d’importance lorsque vous parlez de dollars (dans le prochain chapitre, nous explorons comment formater les nombres en détail). Par exemple, pour formater le taux d’intérêt, faites un clic droit sur la cellule et cliquez sur « formater les cellules ». Choisissez le pourcentage et utilisez 2 décimales.
De même, formatez les autres cellules pour « devise » au lieu de pourcentage et choisissez « nombre » pour la durée du prêt.
Nous avons maintenant :
Ajouter la fonction SUM() pour « totaliser » les dépenses mensuelles.
Notez, la cellule hypothèque n’est pas incluse dans le total. Excel ne sait pas que vous voulez inclure ce nombre, puisqu’il n’y a pas de valeur à cet endroit. Veillez donc à étendre la fonction SUM() vers le haut, soit en utilisant le curseur, soit en tapant E2 là où il est indiqué E3 pour inclure l’hypothèque dans la somme.
Mettez le curseur dans la cellule de paiement (B4).
Sur le menu Formules, sélectionnez le menu déroulant « Financier », puis sélectionnez la fonction PMT. L’assistant s’affiche :
Utilisez le curseur pour sélectionner le « taux. », « nper » (durée du prêt), « Pv » (« valeur actuelle » ou montant du prêt). Remarquez que vous devez diviser le taux d’intérêt par 12 puisque l’intérêt est calculé mensuellement. Vous devez également multiplier la durée du prêt en années par 12 pour obtenir la durée du prêt en mois. Appuyez sur « OK » pour enregistrer le résultat dans la feuille de calcul.
Notez que le paiement est affiché comme un nombre négatif : -1013,37062. Pour le rendre positif et l’ajouter aux dépenses mensuelles, pointez sur la cellule de l’hypothèque (E2). Tapez « =- » puis utilisez le curseur pour pointer sur le champ du paiement. La formule résultante est =-B4.
Maintenant, la feuille de calcul ressemble à ceci:
Vos dépenses mensuelles sont de 1 863 $ – Aïe!
Exemple : Fonction texte
Nous démontrons ici comment utiliser les fonctions à l’intérieur d’une formule et les fonctions texte.
Supposons que vous ayez une liste d’étudiants comme indiqué ci-dessous. Le prénom et le nom sont dans un champ séparé par une virgule. Nous devons mettre le nom de famille et le nom ferme dans des cellules séparées. Comment faire ?
Pour résoudre ce problème, vous devez utiliser un algorithme – c’est-à-dire une procédure étape par étape pour le faire.
Par exemple, regardez « Washington, George ». La procédure pour diviser cela en deux mots serait :
- Calculer la longueur de la chaîne de caractères.
- Trouver la position de la virgule (cela montre où un mot se termine et l’autre commence).
- Copier la partie gauche de la chaîne jusqu’à la virgule.
- Copier la partie droite de la chaîne de la virgule à la fin.
Discutons comment faire cela avec « George Washington » étape par étape dans Excel.
- Calculer la longueur de la chaîne de caractères avec la fonction =LEN(A3) – le résultat est 18.
- Maintenant trouver la position de la virgule en entrant cette fonction =FIND(« , »,A3″) – le résultat est 11.
- Maintenant prendre le côté gauche de la chaîne de caractères jusqu’à la virgule et créer cette formule imbriquée en utilisant le résultat de l’étape 1 : =LEFT(A3,FIND(« , »,A3)-1). Notez, nous devons soustraire 1 de la longueur car FIND donne la position de la virgule.
Voici à quoi tout cela ressemble lorsque toutes les fonctions sont placées ensemble dans une formule. Dans la cellule B3, vous pouvez voir que cette formule prend toutes les informations de la cellule A3 et y introduit « Washington ».
Donc nous avons « Washington », maintenant nous devons obtenir « George ». Comment faire ?
Notez, que nous aurions pu enregistrer le résultat de l’étape 1 dans une cellule par elle-même, disons B6, puis écrire une formule plus simple =LEFT(A3,B6-1). Mais cela utilise une cellule pour l’étape intermittente.
- Rappeler la position de la virgule ou la calculer à nouveau.
- Calculer la longueur de la chaîne de caractères.
- Compter les caractères de la fin de la chaîne à la virgule.
Prenez le nombre de caractères de l’étape 3 et soustrayez un pour omettre la virgule et l’espace.
Faisons cela étape par étape.
Votre feuille de calcul devrait maintenant ressembler à la capture d’écran ci-dessous. Nous avons copié les formules sous forme de texte dans le bas de la feuille de calcul pour faciliter la lecture et la vue.
Ceci était un peu difficile mais vous n’avez besoin d’écrire ces formules qu’une seule fois.
Après …
Ceci conclut notre leçon d’aujourd’hui. Vous devriez avoir une compréhension assez ferme maintenant des formules et des fonctions, des lignes et des colonnes, et de la façon dont tout cela peut être employé à travers plusieurs exemples définis.
.