Définir et créer une formule

Table des matières:

Définir et créer une formule
Définir et créer une formule

Vidéo: Définir et créer une formule

Vidéo: Définir et créer une formule
Vidéo: Comment allumer un iPhone sans bouton power (marche/arrêt) ? - YouTube 2024, Mars
Anonim
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, nous en donnons donc plusieurs exemples et les expliquons en détail. Les sujets que nous couvrirons comprennent:
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, nous en donnons donc plusieurs exemples et les expliquons en détail. Les sujets que nous couvrirons comprennent:

NAVIGATION SCOLAIRE

  1. Pourquoi avez-vous besoin de formules et de fonctions?
  2. Définir et créer une formule
  3. Référence relative et absolue des cellules et formatage
  4. Fonctions utiles à connaître
  5. Recherches, graphiques, statistiques et tableaux croisés dynamiques
  • rangées 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
  • en utilisant une fonction "chaîne" ("chaîne" est un raccourci pour "chaîne de texte") à l'intérieur d'une formule et 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 vous 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, c'est-à-dire la colonne A, rangée 1. Les colonnes sont libellées A-Z. Lorsque l'alphabet est épuisé, Excel place une autre lettre devant lui: AA, AB, AC… AZ, BA, BC, BC, etc.
Les colonnes sont étiquetées par des lettres; lignes par nombres. La première cellule de la feuille de calcul est A1, c'est-à-dire la colonne A, rangée 1. Les colonnes sont libellées A-Z. Lorsque l'alphabet est épuisé, Excel place une autre lettre devant lui: 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.

Image
Image

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 ().
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.
Pour notre première fonction, utilisons SUM (), qui ajoute une liste de nombres.

Supposons que cette feuille de calcul contienne des plans pour la 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 ().
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 nombres).

Si le curseur se trouve 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.
Si le curseur se trouve 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.
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 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 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!
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:
#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
  • orthographe nom de fonction 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 taille de la zone dessinée par Excel lorsque vous déplacez la souris ou cliquez dans une autre cellule.

Nous avons cliqué en 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.
Appuyez sur «enter» pour confirmer les résultats.

Opérateurs de calcul

Il existe deux types d'opérateurs: mathématiques 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 24 = 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
< inférieur à, par exemple, 2 <4 ou "a" <"b"
>= supérieur ou égal à - une autre façon de penser est> = signifie non plus > ou =.
<= inférieur ou égal à.
non égal à, par exemple, 4 <> 6

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

Notez que si vous entrez = "a"> "b" dans une cellule, il dira "FAUX" car "a" n'est pas plus grand que "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 passons à la plongée.

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 πr2, qui est identique à π * r * r. Il est ne pas (πr)2.

Vous devez donc comprendre l'ordre de priorité lorsque vous écrivez une formule.

Généralement, vous pouvez dire ceci:

  1. Excel évalue d’abord les éléments entre parenthèses.
  2. Il utilise ensuite les règles de priorité d'ordre des mathématiques.
  3. 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, signifie multiplier 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 avec 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. Ainsi, 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 va vous accorder 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?

Nous voyons dans les règles du tableau ci-dessus que * et / ont la même priorité. Ainsi, Excel fonctionne de gauche à droite, 4 * 3 = 12 en premier, 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 souhaitez modifier l'ordre de priorité, vous devez écrire = (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.

Il y a donc quelques exemples d’ordre et de préséance mathématiques; nous espérons que cela aidera à clarifier quelques points concernant 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 (dans le chapitre suivant, nous verrons comment formater les nombres en détail). Par exemple, pour formater le taux d'intérêt, cliquez avec le bouton droit 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:
Maintenant nous avons:
Ajoutez la fonction SUM () au «total» des dépenses mensuelles.
Ajoutez la fonction SUM () au «total» des dépenses mensuelles.
Image
Image

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 que E3 inclut 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:
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»., «Nper» (durée du prêt), «Pv» («valeur actuelle» ou 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.
Utilisez le curseur pour sélectionner le «taux»., «Nper» (durée du prêt), «Pv» («valeur actuelle» ou 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.

Image
Image

Maintenant, le tableur ressemble à ceci:

Vos dépenses mensuelles sont de 1 863 $ - Aïe!
Vos dépenses mensuelles sont de 1 863 $ - Aïe!

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 résoudre ce problème, vous devez utiliser un algorithme, c’est-à-dire une procédure étape par étape.

Par exemple, regardez «Washington, George». La procédure pour diviser cela en deux mots serait la suivante:

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

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

  1. Calculez la longueur de la chaîne avec la fonction = LEN (A3) - le résultat est 18.
  2. Recherchez maintenant la position de la virgule en entrant cette fonction = FIND (“,”, A3”) - le résultat est 11.
  3. Maintenant, prenez 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). Notez que nous devons soustraire 1 de la longueur car FIND donne la position de la virgule.

Voici à quoi ça ressemble quand 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», nous devons maintenant avoir «George». Comment faisons-nous cela?
Donc, nous avons «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.

  1. Rappelez-vous la position de la virgule ou calculez-la à nouveau.
  2. Calculez la longueur de la chaîne.
  3. Comptez 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.

  1. D'en haut, c'est = FIND (“,”, A3”)
  2. La longueur de la chaîne est = LEN (A3)
  3. Vous devrez utiliser des calculs pour trouver le nombre de caractères à prendre: = LEN (A3) - FIND (“,”, A3) - 1
  4. 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.
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.

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

Conseillé: