Définir et créer une formule

Dans cette leçon, nous vous présentons les règles de base pour la création de formules et l'utilisation de fonctions. Nous pensons que l’un des meilleurs moyens d’apprendre est la pratique, c’est pourquoi nous donnons plusieurs exemples et les expliquons en détail. Les sujets que nous couvrirons comprennent:

NAVIGATION SCOLAIRE

Pourquoi avez-vous besoin de formules et de fonctions?
Définir et créer une formule
Référence relative et absolue des cellules et formatage
Fonctions utiles à connaître
Recherches, graphiques, statistiques et tableaux croisés dynamiques

lignes et colonnes
exemple de fonction mathématique: SUM ()
les opérateurs
priorité des opérateurs
exemple de fonction financière: PMT (), paiement du prêt
utiliser une fonction “chaîne” (“chaîne” est un raccourci pour “chaîne de texte”) dans une formule et des fonctions imbriquées

Les formules sont un mélange de «fonctions», «opérateurs» et «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..

Rangées et Colonnes

Pour comprendre comment écrire des formules et des fonctions, vous devez connaître les lignes et les colonnes..

Les lignes sont horizontales et les colonnes verticales. Pour se rappeler lequel est lequel, pensez à une colonne tenant un toit - les colonnes vont de haut en bas et ainsi les rangées vont de gauche à droite.

Les colonnes sont étiquetées par des lettres; lignes par nombres. La première cellule de la feuille de calcul est A1, ce qui correspond à la colonne A, ligne 1. Les colonnes sont libellées A-Z. Lorsque l'alphabet est épuisé, Excel place une autre lettre devant: AA, AB, AC… AZ, BA, BC, BC, etc..

Exemple: Fonction Sum ()

Maintenant, montrons comment utiliser une fonction.

Vous utilisez des fonctions en les saisissant directement ou en utilisant l’assistant de fonction. L'assistant de fonction s'ouvre lorsque vous sélectionnez une fonction dans le menu «Formules» de la «Bibliothèque de fonctions». Sinon, vous pouvez taper = dans une cellule et un menu déroulant vous permettra de choisir une fonction..

L'assistant vous indique les arguments à fournir pour chaque fonction. Il fournit également un lien vers les 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 SUM..

Lorsque vous tapez une fonction, l’assistant est en ligne ou est juste au bout de vos doigts. Lorsque vous sélectionnez une fonction dans le menu «Formules», l’assistant est une boîte contextuelle. Voici l'assistant contextuel pour la fonction SUM ().

Pour notre première fonction, utilisons SUM (), qui ajoute une liste de nombres.

Supposons que nous ayons cette feuille de calcul contenant les plans de budgétisation des vacances de votre famille:

Pour calculer les coûts totaux, vous pouvez é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 rechercher le bouton Somme automatique (les mathématiciens utilisent la lettre grecque pour ajouter une série de chiffres)..

Si le curseur est en dessous des chiffres du budget familial, Excel est suffisamment intelligent pour savoir que vous souhaitez additionner la liste des nombres situés au-dessus de l'endroit où vous avez placé le curseur, afin de mettre les chiffres en surbrillance..

Appuyez sur «enter» pour accepter la plage sélectionnée par Excel ou utilisez le curseur pour changer les cellules sélectionnées..

Si vous regardez ce que 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 que nous avons laissé un peu de place sous la rangée 5 afin d’ajouter au budget de vacances en famille. Le coût augmentera certainement à 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. Par conséquent, si vous insérez des lettres dans la colonne, le résultat s'affiche sous la forme «#NAME?», Comme indiqué ci-dessous..

#PRÉNOM? indique qu'il y a une sorte d'erreur. Cela pourrait être n'importe quel nombre de choses, y compris:

mauvaise référence de cellule
utiliser des lettres dans les fonctions mathématiques
omettre les arguments requis
nom de fonction orthographique faux
opérations mathématiques illégales comme la division par 0

Le moyen le plus simple de sélectionner les arguments dans un calcul consiste à utiliser la souris. Vous pouvez ajouter ou supprimer de la liste des arguments de la fonction en agrandissant ou en réduisant la zone dessinée par Excel lorsque vous déplacez la souris ou cliquez dans une autre cellule..

Nous avons cliqué sur le haut de la case dessinée par Excel pour supprimer les «billets d'avion» du budget. Vous pouvez voir le symbole en forme de croix que vous pouvez dessiner pour agrandir ou réduire la plage sélectionnée..

Appuyez sur "enter" pour confirmer les résultats.

Opérateurs de calcul

Il y a deux types d'opérateurs: math et comparaison.

Opérateur mathématique	Définition
+	une addition
-	soustraction ou négation, par exemple, 6 * -1 = -6
*	multiplication
/	division
%	pour cent
^	exposant, par exemple 2⁴= 2 ^ 4 = 2 * 2 * 2 * 2 = 16

Il existe d'autres opérateurs non liés aux mathématiques, tels que «&» qui signifie concaténer (joindre de bout en bout) deux chaînes. Par exemple, = "Excel" & "is Fun" équivaut à "Excel is Fun".

Maintenant, regardons les opérateurs de comparaison.

Opérateur de comparaison	Définition
=	est égal à, par exemple, 2 = 4 ou «b» = «b»
>	supérieur à 4> 2 ou «b»> «a», par exemple
<	moins que, par exemple, 2 < 4 or “a” < “b”
> =	supérieur ou égal à - une autre façon de penser est> = signifie non plus > ou =.
<=	inférieur ou égal à.
	pas égal à, par exemple, 46

Comme vous pouvez le voir ci-dessus, les opérateurs de comparaison travaillent avec des nombres et du texte.

Notez que si vous entrez = "a"> "b" dans une cellule, il indiquera "FAUX" car "a" n'est pas supérieur à "b." "B" vient après "a" dans l'alphabet, donc "a" > «B» ou "B"> "a."

Ordre de priorité des opérateurs

La priorité des commandes est une idée tirée des mathématiques. Excel doit suivre les mêmes règles que les mathématiques. Ce sujet est plus compliqué, alors reprenez votre souffle et plongons dans.

Ordre de priorité désigne l'ordre dans lequel l'ordinateur calcule la réponse. Comme nous l'avons expliqué à la leçon 1, l'aire d'un cercle est πr², qui est identique à π * r * r. Il est ne pas (πr)².

Vous devez donc comprendre l'ordre de priorité 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’intérieur.
Il utilise ensuite les règles de priorité d'ordre des mathématiques.
Lorsque deux éléments ont la même priorité, Excel fonctionne de gauche à droite.

La priorité des opérateurs mathématiques est indiquée ci-dessous, par ordre décroissant..

( et )	Lorsque des parenthèses sont utilisées, elles remplacent les règles de priorité normales. Cela signifie qu'Excel fera ce calcul en premier. Nous expliquons ceci plus bas.
-	Négation, par exemple -1. Cela revient à multiplier un nombre par -1. -4 = 4 * (-1)
%	Pourcentage, moyens multipliés par 100. Par exemple, 0,003 = 0,3%.
^	Exposant, par exemple, 10 ^ 2 = 100
* et /	Multipliez et divisez. Comment deux opérateurs peuvent-ils avoir la même priorité? Cela signifie simplement que si une formule a deux autres opérateurs ayant la même priorité, le calcul est effectué de gauche à droite..
+ et -	Addition et soustraction.

Il existe d'autres règles de priorité relatives aux chaînes et aux opérateurs de référence. Pour le moment, nous allons simplement nous en tenir à ce que nous venons de décrire. Maintenant, regardons quelques exemples.

Exemple: Calculer 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 précèdent la multiplication. Donc, l’ordinateur calcule d’abord le rayon ^ 2, puis multiplie ce résultat par Pi.

Exemple: calcul d'une augmentation de salaire

Disons que votre patron décide que vous faites un excellent travail et qu'il vous accordera une augmentation de 10%! Comment calculeriez-vous votre nouveau salaire?

Tout d'abord, rappelez-vous que la multiplication vient avant l'addition.

Est-ce = salaire + salaire * 10% ou est-ce = salaire + (salaire * 10%)?

Supposons que votre salaire est de 100 $. Avec une augmentation de 10%, votre nouveau salaire sera de:

= 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 explicite l’ordre de priorité en utilisant des parenthèses. Rappelez-vous que les parenthèses sont évaluées avant toute autre opération.

À propos, la façon la plus simple d’écrire ceci est = salaire * 110%

Les parenthèses peuvent être imbriquées les unes dans les autres. Ainsi, lorsque nous écrivons (3 + (4 * 2)) en travaillant de l’intérieur vers l’extérieur, calculons d’abord 4 * 2 = 8, puis ajoutons 3 + 8 pour obtenir 11..

Quelques exemples supplémentaires

Voici un autre exemple: = 4 * 3 / 2. Quelle est la réponse?

Les règles du tableau ci-dessus montrent que * et / ont la même priorité. Donc, Excel fonctionne de gauche à droite, 4 * 3 = 12 d’abord, puis divise le résultat par 2 pour obtenir 6.

Encore une fois, vous pouvez expliquer cela en écrivant = (4 * 3) / 2

Qu'en est-il = 4 + 3 * 2?

L'ordinateur voit à la fois les opérateurs * et +. Donc, en suivant les règles de priorité (la multiplication vient avant l’addition), il calcule 3 * 2 = 6 d’abord, puis ajoute 4 pour obtenir 10..

Si vous vouliez changer l'ordre de priorité, vous écririez = (4 + 3) * 2 = 14.

Qu'en est-il de celui-ci = -1 ^ 3?

Alors la réponse est -3 car l'ordinateur a calculé = (-1) ^ 3 = -1 * -1 * -1 = -1.

Rappelez-vous que les temps négatifs sont négatifs et les effets négatifs sont négatifs. Vous pouvez voir ceci comme ceci (-1 * -1) * -1 = 1 * -1 = -1.

Donc, il y a quelques exemples d'ordre et de priorité mathématiques, nous espérons que cela aide à clarifier quelques points sur la manière dont Excel effectue les calculs (et c'est probablement assez de maths pour durer toute une vie pour certains d'entre vous).

Exemple: paiement de prêt de fonction (PMT)

Regardons un exemple pour calculer un paiement de prêt.

Commencez par créer une nouvelle feuille de calcul.

Formatez les nombres avec des signes en dollars et n'utilisez pas de décimales, car les centimes ne nous intéressent pas pour le moment, car ils importent peu lorsque vous parlez de dollars (le chapitre suivant explique comment formater des nombres en détail). Par exemple, pour formater le taux d'intérêt, cliquez avec le bouton droit de la souris sur la cellule, puis cliquez sur "Formater les cellules". Choisissez un pourcentage et utilisez deux décimales..

De même, formatez les autres cellules pour «devise» au lieu de pourcentage et sélectionnez «nombre» pour la durée du prêt..

Maintenant nous avons:

Ajouter la fonction SUM () au «total» des dépenses mensuelles.

Noter la hypothèque la cellule n'est pas incluse dans le total. Excel ne sait pas que vous souhaitez inclure ce nombre, car il n'y a aucune valeur. Veillez donc à étendre la fonction SUM () vers le haut en utilisant le curseur ou en tapant E2, où il est écrit E3 pour inclure l'hypothèque dans la somme..

Placez le curseur dans la cellule de paiement (B4).

Dans le menu Formules, sélectionnez le menu déroulant "Finances", puis sélectionnez la fonction PMT. L'assistant apparaît:

Utilisez le curseur pour sélectionner le «taux», le «nper» (durée du prêt), le «Pv» («valeur actuelle» ou le montant du prêt). Notez que vous devez diviser le taux d'intérêt par 12 car les intérêts sont calculés mensuellement. Aussi, vous devez 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 indiqué par un nombre négatif: -1013.37062. Pour le rendre positif et l'ajouter aux dépenses mensuelles, pointez sur la cellule d'hypothèque (E2). Tapez "= -" puis utilisez le curseur pour pointer sur le champ de paiement. La formule résultante est = -B4.

Maintenant, le tableur ressemble à ceci:

Vos dépenses mensuelles sont de 1 863 $ - Ouch!

Exemple: fonction texte

Nous montrons ici comment utiliser des fonctions dans une formule et des fonctions de texte.

Supposons que vous ayez une liste d’étudiants comme indiqué ci-dessous. Le prénom et le nom se trouvent dans un champ séparé par une virgule. Nous devons mettre le dernier nom et le nom de l'entreprise dans des cellules séparées. Comment faisons-nous cela?

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 la suivante:

Calculer la longueur de la chaîne.
Trouvez la position de la virgule (cela montre où un mot se termine et l'autre commence).
Copiez le côté gauche de la chaîne jusqu'à la virgule.
Copiez le côté droit de la chaîne de la virgule à la fin.

Discutons comment procéder avec «George Washington» étape par étape dans Excel.

Calculez la longueur de la chaîne avec la fonction = LEN (A3) - le résultat est 18.
Maintenant, trouvez la position de la virgule en entrant cette fonction = FIND (“,”, A3 ”) - le résultat est 11.
Prenez maintenant le côté gauche de la chaîne jusqu'à la virgule et créez cette formule imbriquée en utilisant le résultat de l'étape 1: = LEFT (A3, FIND (“,”, A3) -1). Remarque, il faut soustraire 1 de la longueur car FIND donne la position de la virgule.

Voici à quoi 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»..

Nous avons donc «Washington», nous devons maintenant avoir «George». Comment faisons-nous cela??

Notez que nous aurions pu enregistrer le résultat de l'étape 1 dans une cellule à elle seule, par exemple B6, puis écrire une formule plus simple = LEFT (A3, B6-1). Mais cela utilise une cellule pour l'étape intermittente.

Rappelez-vous la position de la virgule ou calculez-la à nouveau.
Calculer la longueur de la chaîne.
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-en un pour omettre la virgule et l'espace..

Faisons cela étape par étape.

D'en haut, c'est = FIND (“,”, A3 ”)
La longueur de la chaîne est = LEN (A3)
Vous devrez utiliser des calculs pour trouver le nombre de caractères à prendre: = LEN (A3) - FIND (“,”, A3) - 1
Le côté droit de la chaîne souhaitée est = RIGHT (A3, LEN (A3) - FIND (“,”, A3) - 1)

Votre feuille de calcul devrait maintenant ressembler à la capture d'écran ci-dessous. Nous avons copié les formules sous forme de texte au bas de la feuille de calcul pour faciliter la lecture et la visualisation..

Celui-ci était un peu difficile, mais vous n'avez besoin d'écrire ces formules qu'une seule fois.

À suivre…

Ceci conclut notre leçon pour aujourd'hui. Vous devriez maintenant avoir une compréhension assez ferme des formules et des fonctions, des lignes et des colonnes, et la façon dont tout cela peut être utilisé à travers plusieurs exemples précis..

Ensuite, dans la leçon 3, nous discuterons de la référence et du formatage des cellules, ainsi que du déplacement et de la copie de formules afin que vous n'ayez pas à réécrire chaque formule encore et encore.!