Utiliser des formules et fonctions simples dans un tableau Excel

Titre Pro Assistant de Direction

guidE EXCEL (version 2019)

EXPOSE 2022/11-22

Utiliser des formules simples pour faire des calculs dans un tableau Excel

expose 2022/11-22

UTILISER des formules simples pour faire des calculs dans un tableau Excel

SOMMAIRE

6-introduction fonctions

1-iNTRODUCtion formules

7-fonctionS STATISTIQUES

2-formule en ECRITURE DIRECTE

8-fonctionS MATHEMATIQUES

3-références de cellules

9-FONCTIONS LOGIQUES

4-formule liée aux références

Fin

5-RECOpier des formules

UTILISER des formules simples pour faire des calculs dans un tableau Excel

expose 2022/11-22

INTRODUCtion formules

utilisation des formules

Durée

4h

Grâce aux formules, vous pouvez effectuer des calculs et analyser les données d'une feuille de calcul. Une formule commence toujours par le signe égal = Les formules peuvent être écrites en direct ou alors liées aux références.

Modalité

Présentiel

Pré-requis

connaissance environement Excel

Langue

Français

exemples

UTILISER des formules simples pour faire des calculs dans un tableau Excel

expose 2022/11-22

les notions de mathématiques (formule directe)

Par exemple dans une cellule si on écrit: =50+15 le résultat affiché sera : 65=(50+15)^2/5 le résultat affiché sera : 845

Donc, pour écrire une formule, on sélectionne une cellule dans la feuille (à l’endroit où on désire notre résultat) et on commence par un = , on ajoute ensuite un nombre, puis un opérateur (par exemple + ), puis un autre nombre, et ainsi de suite. On ajoute des parenthèses ( ) lorsque c’est nécessaire... comme en mathématiques.

Voici la liste des opérateurs utiles pour vos calculs :

Pour rappel:l'opération 'Exposant' est identique à l'opération 'puissance'. exemple: 2*2*2*2 = 2^4 = 16 lire '2 exposant 4' ou '2 puissance 4' 3*3 = 3^2 = 9 lire '3 puissance 2' ou '3 au carré'

UTILISER des formules simples pour faire des calculs dans un tableau Excel

expose 2022/11-22

les notions de mathématiques (formule directe)

Ordre des calculs

Par exemple si on écrit dans une cellule : =10+20+30*40 le résultat affiché sera : 1230 =10+(20+30)*40 le résultat affiché sera : 2010 =10*(30-20)+40 le résultat affiché sera : 140 =30/(10+20)+40 le résultat affiché sera : 41

Excel effectue les opérations dans l’ordre suivant : 1. Exposant ^ 2. Multiplication * et Division / 3. Addition + et Soustraction - Pour changer l’ordre dans lequel Excel effectue les calculs, on utilisera les parenthèses. Excel commencera par les calculs se trouvant dans les parenthèses.

UTILISER des formules simples pour faire des calculs dans un tableau Excel

expose 2022/11-22

références de cellules

1. Repérage par une lettre B - pour identifier la colonne
2. Puis repérage par un nombre 2 - pour identifier la ligne
3. On identifie la cellule B2, telle est sa référence de cellule

UTILISER des formules simples pour faire des calculs dans un tableau Excel

expose 2022/11-22

écriture formule liée aux références

On sait maintenant comment écrire une formule en entrant directement les nombres dans la formule. Mais dans la plupart des cas, on devra entrer les références aux cellules contenant ces nombres au lieu d’entrer directement les nombres. On va prendre un exemple concret pour comprendre l’utilité d’entrer des références aux cellules au lieu des nombres.

Dans la formule affichée, les nombres ont été entrés directement dans la formule =200*80% Le problème avec cette méthode est que si on change maintenant les valeurs des cellules A2 ou B2, il faudra penser à modifier la formule à chaque changement, ce qui n'est vraiment pas pratique.

Si au contraire, on entre les références aux cellules au lieu d'entrer les nombres dans la formule, le problème ne se pose plus. La formule devient alors =A2*B2 :

En faisant cela, lorsque l’on modifiera à nouveau les cellules A2 ou B2, le résultat de la formule sera automatiquement recalculé :

UTILISER des formules simples pour faire des calculs dans un tableau Excel

expose 2022/11-22

écriture formule liée aux références

Lorsqu’on change un nombre utilisé dans une formule liée aux références, Excel recalcule automatiquement à notre place.

Pour ajouter une référence à une cellule, on clique sur la cellule concernée (après avoir entré = ) ou on tape directement sa référence dans la formule.

Exemples de formule liée aux références:= A1 * ( B3 - A4 ) + C5 = A1 + 2 * B3 / C4

UTILISER des formules simples pour faire des calculs dans un tableau Excel

expose 2022/11-22

recopier des formules

La recopie fonctionne très bien avec les formules liée aux références.

La formule de départ (à la ligne 2) est =A2*B2 et après recopie, la formule à la ligne 6 est =A6*B6 :

Les références aux cellules ont bien été décalées en respectant le sens de la recopie, mais dans certains cas, il faudra faire référence à une cellule fixe ...Pour illustrer ceci, le montant de la cellule B9 doit maintenant être divisé en 3 parts. La formule devient =A2*B2/B9 et celle-ci est ensuite recopiée :

Le problème qui se pose ici est que (on peut le voir en double-cliquant sur la cellule C6) la cellule B9 a été décalée lors de la recopie

Contrairement aux apparences, la recopie a fonctionné tout à fait normalement -->

UTILISER des formules simples pour faire des calculs dans un tableau Excel

expose 2022/11-22

recopier des formules

Pour éviter cela, il faut fixer la référence en ajoutant des $ à la référence.

La formule =A2*B2/B9 devient alors =A2*B2/$B$9 et lors de la recopie toutes les formules font maintenant référence à la cellule B9:

Le premier $ de $B$9 fixe la colonne lors de la recopie et le second $ la ligne. Dans cet exemple, il est donc possible de fixer uniquement la ligne avec B$9 puisqu’il n’y a aucun décalage de colonne lors de la recopie.

UTILISER des formules simples pour faire des calculs dans un tableau Excel

expose 2022/11-22

introduction aux fonctions

utilisation des fonctions

Grâce aux fonctions, vous pouvez effectuer des calculs et analyser les données d'une feuille de calcul. Une fonction est une formule prête à l'emploi qu'on peut utiliser pour effectuer un calcul avec les données de la feuille Une fonction commence toujours par le signe égal = Les données utilisées par Excel pour calculer la fonction se trouvent entre parenthèses ( )

UTILISER des formules simples pour faire des calculs dans un tableau Excel

expose 2022/11-22

La fonction STATISTIQUE : MOYENNE

la fonction MOYENNE

Renvoie la moyenne (arithmètique) des arguments, qui peuvent être des nombres, des noms, des matrices, ou des références contenant des nombres.

=MOYENNE(nombre1;nombre2;...)=MOYENNE(tableau)=MOYENNE(A12:C23) ...

UTILISER des formules simples pour faire des calculs dans un tableau Excel

expose 2022/11-22

LES fonctionS STATISTIQUES : MIN et MAX

la fonction MIN pour minimum

la fonction MAX pour maximum

Donne la valeur la plus petite parmi une liste de valeurs. Ignore les valeurs logiques et le texte.

Donne la valeur la plus grande parmi une liste de valeurs. Ignore les valeurs logiques et le texte.

=MIN(nombre1;nombre2;...)=MIN(tableau) =MIN(A12:C23) ...

=MAX(nombre1;nombre2;...)=MAX(tableau) =MAX(A12:C23) ...

UTILISER des formules simples pour faire des calculs dans un tableau Excel

expose 2022/11-22

LES fonctionS STATISTIQUES : NB

la fonction NB

Détermine le nombre de cellules d'une plage contenant des nombres.

=NB(nombre1;nombre2;...)=NB(tableau)=NB(A12:C23) ...

UTILISER des formules simples pour faire des calculs dans un tableau Excel

expose 2022/11-22

LES fonctionS STATISTIQUES : NBVAL

la fonction NB.VIDE

la fonction NBVAL

Compte le nombre de cellules vides à l'intérieur d'une plage spécifique.

Détermine le nombre de cellules d'une plage qui ne sont pas vides: nombres, textes, ...

=NB.VIDE(tableau)=NB.VIDE(A12:C23) ...

=NBVAL(nombre1;nombre2;...)=NBVAL(tableau)=NBVAL(A12:C23) ...

UTILISER des formules simples pour faire des calculs dans un tableau Excel

expose 2022/11-22

LES fonctionS MATHEMATIQUES: SOMME

la fonction SOMME

Calcule la somme automatique des nombres dans une plage de cellules.

=SOMME(C2;C3;C4;...)=SOMME(tableau)=SOMME(C2:C10) ...

UTILISER des formules simples pour faire des calculs dans un tableau Excel

expose 2022/11-22

LES fonctionS LOGIQUES : ET, OU

la fonction ET

la fonction OU

Vérifie si tous les arguments sont VRAI et renvoie VRAI si tous les arguments sont VRAI.

Vérifie si un argument est VRAI et renvoie VRAI. Renvoie FAUX uniquement si tous les arguments sont FAUX

=OU(valeur logique1;valeur logique 2;...)

=ET(valeur logique1;valeur logique 2;...)

UTILISER des formules simples pour faire des calculs dans un tableau Excel

expose 2022/11-22

LES fonctionS LOGIQUES : SI

la fonction SI

Vérifie si la condition est respectée et renvoie une valeur si le résultat d'une condition spécifiée est VRAI, et une autre valeur si le résultat est FAUX.

=SI(condition;si_cond_vraie;si_cond_fausse)

UTILISER des formules simples pour faire des calculs dans un tableau Excel

expose 2022/11-22

LES fonctionS LOGIQUES : SI ... DERIVEES

la fonction MOYENNE.SI

la fonction SOMME.SI

la fonction NB.SI

Permet de faire la moyenne des données en fonction d'une condition ou certain critère.

Permet de d'additionner des cellules spécifiées selon un certain critère.

Permet de de compter le nombre de cellules d'une plage de données qui répondent à un critère.

=MOYENNE.SI(plage;critère;[plage_moyenne])

=SOMME.SI(plage;critère;[plage_somme])

=NB.SI(plage;critère)

UTILISER des formules simples pour faire des calculs dans un tableau Excel

expose 2022/11-22

Merci

et maintenant, au travail...