Editions ENI Livres | Vidéos | e-Formations
Calculs
Découvrir le principe des formules de calcul
Les formules de calcul effectuent des calculs sur les valeurs contenues dans une feuille de calcul.
Une formule commence par un signe égal (=).
Une formule peut contenir les éléments suivants :
-
des références de cellules,
-
des opérateurs :
les opérateurs mathématiques :
+ pour l’addition
- pour la soustraction
/ pour la division
* pour la multiplication
% pour le calcul d’un pourcentage (ex : =5% donne le résultat 0,05)
ˆ pour l’élévation à la puissance (ex : =2ˆ3 donne le résultat 8)
les opérateurs de comparaison ; le résultat est une valeur logique : VRAI ou FAUX :
= égal à (ex : =30=40 donne le résultat FAUX)
< inférieur à (ex : =28<35 donne le résultat VRAI)
<= inférieur ou égal à
> supérieur à
>= supérieur ou égal à
<> différent de
un opérateur de concaténation de texte : & concatène des chaînes de caractères
(ex : ="Ouest"&" et "&"Nord" donne le résultat "Ouest et Nord").
les opérateurs de référence permettent de combiner des plages de cellules :
: (deux-points) ex : B1:C4...
Créer une formule de calcul simple
Il s’agit d’élaborer un calcul intégrant des références de cellules, des opérateurs de calcul et/ou des constantes.
Cliquez dans la cellule où vous souhaitez afficher le résultat de la formule.
Saisissez le signe égal =
Construisez la formule en tenant compte des informations suivantes :
-
pour intégrer le contenu d’une cellule, cliquez sur la cellule en question ou saisissez sa référence (par exemple, C4) ;
-
pour intégrer un opérateur de calcul ou une constante, saisissez la donnée correspondante (par exemple * pour multiplier) ;
-
si vous utilisez plusieurs opérateurs, définissez, si besoin, des priorités afin de regrouper des valeurs en utilisant les parenthèses.
Vous suivez l’évolution de la formule dans la barre de formule ; la formule ci-dessus permet de calculer le montant TTC d’un article.
Validez la formule en appuyant sur la touche Entrée ou
en cliquant sur l’outil 
de la barre de formule.
Rendre absolue une référence de cellule dans une formule
Cette technique permet de fixer la référence d’une cellule dans une formule de façon à ce que cette référence ne se modifie pas lors de la recopie de la formule.
Commencez à saisir la formule et arrêtez-vous lorsque la cellule concernée par la référence absolue est sélectionnée.
Appuyez sur la touche F4.
La référence de la cellule présente maintenant le caractère $ devant la lettre de colonne et devant le numéro de ligne.
Lorsque vous appuyez sur la touche F4, vous obtenez une référence absolue de cellule ; si vous appuyez sur F4 une seconde fois, seule la référence de la ligne devient absolue ; si vous appuyez sur F4 une troisième fois, c’est alors la référence de la colonne qui devient absolue.
Appuyez sur la touche F4 autant de fois que nécessaire afin de rendre absolu l’élément souhaité.
Au besoin, terminez la saisie de la formule puis validez.
Dans notre exemple, nous avons recopié la cellule E4 sur les cellules E5 à E10 ; notez que la référence absolue (B1) est restée fixe dans les différentes formules contrairement aux autres cellules. Sur cet exemple, seule la référence...
Saisir une formule multifeuille
Cette technique permet d’insérer dans une feuille une formule (appelée formule 3D) faisant référence à des cellules d’une ou plusieurs autres feuilles.
Activez la cellule où vous souhaitez afficher le résultat.
Saisissez le signe =
Commencez la formule et, au moment désiré, cliquez sur l’onglet de la feuille concernée puis sélectionnez la ou les cellules de votre choix, terminez la formule.
Validez.
Ici, la cellule E3 additionne le contenu des cellules E3 des feuilles "Semestre 1" et "Semestre 2".
Vous pouvez également effectuer des formules multiclasseurs ; dans ce cas, tous les classeurs intervenant dans le calcul doivent être ouverts. Pour atteindre une cellule d’une feuille d’un autre classeur, utilisez le bouton Changer de fenêtre de l’onglet Affichage (ou l’icône du fichier dans la barre des tâches), afin d’activer le classeur correspondant.
Utiliser les fonctions de calcul
Activez la cellule où vous souhaitez afficher le résultat.
Cliquez sur l’outil Insérer une fonction 
situé dans la barre de formule ou dans l’onglet Formules, ou faites ShiftF3.
Dans la boîte de dialogue Insérer une fonction, utilisez la liste déroulante Ou sélectionnez une catégorie si vous souhaitez afficher une catégorie particulière de fonctions.
La catégorie Les dernières utilisées affiche la liste des dernières fonctions que vous avez utilisées ainsi que les plus courantes. La catégorie Toutes affiche toutes les fonctions disponibles.
Pour rechercher une fonction particulière, vous pouvez saisir dans la zone Recherchez une fonction, soit le nom exact de la fonction, soit une description de l’utilisation que vous souhaitez en faire ; validez ensuite la recherche par le bouton Rechercher ou par la touche Entrée.
Cliquez sur la fonction recherchée dans le cadre Sélectionnez une fonction pour la sélectionner.
Lorsqu’une fonction est sélectionnée, la syntaxe de la fonction ainsi qu’une description s’affichent en dessous du cadre.
Si nécessaire, cliquez sur le lien Aide sur cette fonction pour afficher l’aide de Excel sur la fonction sélectionnée....
Utiliser la saisie semi-automatique de fonction
Cette manipulation vous permet de saisir vous-même une fonction, sans passer par l’Assistant Fonction, tout en étant aidé par Excel afin de limiter les erreurs de syntaxe et les fautes de frappe.
Activez la cellule dans laquelle vous souhaitez afficher le résultat de la formule.
Saisissez le signe = (égal) et les premières lettres de la fonction.
Dès la saisie de la première lettre, Excel affiche la liste des fonctions commençant par cette lettre.
Continuez de saisir le nom de la fonction ou faites un double clic sur le nom qui apparaît dans la liste puis indiquez les arguments.
Au fur et à mesure de la saisie, des info-bulles vous guident dans l’élaboration de la formule.
N’oubliez pas de terminer la formule par la saisie du signe ) puis validez en appuyant sur la touche Entrée.
Additionner un ensemble de cellules
Activez la cellule où vous souhaitez afficher la somme des valeurs.
Cliquez sur l’outil Somme automatique 
du groupe Édition de
l’onglet Accueil ou
utilisez le raccourci Alt=.
Vous retrouvez ce bouton dans l’onglet Formules - groupe Bibliothèque de fonctions.
Excel affiche une fonction intégrée appelée SOMME() et propose d’additionner par défaut le groupe de cellules situé au-dessus ou à gauche de la cellule du résultat (ici E3 à E11).
Si la sélection des cellules n’est pas satisfaisante, modifiez-la à l’aide d’un cliqué-glissé sur les cellules concernées.
Validez par la touche Entrée ou
cliquez sur l’outil Entrer .
Lorsque vous sélectionnez une plage de cellules contenant des valeurs numériques, Excel affiche, entre autres, la somme de ces valeurs dans la barre d’état.
Utiliser les fonctions statistiques simples
Activez la cellule où vous souhaitez afficher le résultat statistique.
Ouvrez la liste de l’outil Somme automatique du groupe Édition de
l’onglet Accueil ;
vous pouvez aussi ouvrir la liste associée au bouton Somme automatique du groupe Bibliothèque de fonctions de
l’onglet Formules.
Cliquez sur la fonction désirée :
|
Moyenne |
Calcule la moyenne d’un ensemble de cellules contenant des valeurs numériques. |
|
NB |
Calcule le nombre de cellules contenant des valeurs numériques dans un ensemble de cellules. |
|
Max. |
Extrait la valeur maximale d’un ensemble de cellules contenant des valeurs numériques. |
|
Min. |
Extrait la valeur minimale d’un ensemble de cellules contenant des valeurs numériques. |
Excel affiche la fonction correspondant à votre choix et sélectionne un groupe de cellules adjacentes.
Si la sélection des cellules n’est pas satisfaisante, modifiez-la en cliquant sur une cellule pour la sélectionner ou en effectuant un cliqué-glissé pour sélectionner une plage de cellules.
Appuyez sur la touche Entrée ou
cliquez sur l’outil pour valider la formule de calcul.
Lorsque vous sélectionnez une plage de cellules contenant des valeurs numériques, vous visualisez sur la barre d’état, outre leur somme...
Créer une formule conditionnelle simple
Ce type de formule permet d’afficher une valeur ou d’effectuer un calcul selon une ou plusieurs conditions.
Activez la cellule où vous souhaitez afficher le résultat.
Utilisez la fonction SI pour effectuer un test logique (VRAI ou FAUX) sur la valeur d’une cellule ou sur le résultat d’une autre formule ; en fonction du résultat de ce test, la fonction SI effectue une action si le résultat est Vrai, ou une autre action si le résultat est Faux.
La syntaxe de la fonction SI est la suivante :
=SI(test_logique;valeur si VRAI;valeur si FAUX)
La formule saisie en I4 a été recopiée en I5, I6, I7 et I8. Dans cet exemple, nous testons la valeur de la cellule Stock final (H4) : =SI(H4<=1000;"A commander";"En attente"). Si le contenu de la cellule H4 est inférieur ou égal à 1000, le texte "A commander" s’affiche dans la cellule de résultat, sinon le texte "En attente" s’affiche.
Notez qu’en inversant le test, le résultat reste identique si vous inversez aussi la valeur si VRAI et la valeur si FAUX : =SI(H4>1000;"En attente";"A commander").
Pour afficher un résultat uniquement si la condition est VRAI, vous pouvez ne pas renseigner l’argument FAUX, par exemple : =SI(H4<1000;"A...
Créer une formule conditionnelle imbriquée
Lorsqu’un résultat de condition de test implique plus de deux possibilités, vous pouvez imbriquer des formules conditionnelles.
Dans ce cas, la formule s’écrit : =SI(test_logique 1;valeur si VRAI;SI(test_logique 2;valeur si VRAI;valeur si FAUX)). La formule conditionnelle n°2 est bien "imbriquée" dans la première.
Vous pouvez ainsi imbriquer plusieurs conditions les unes dans les autres.
Dans cet exemple deux conditions ont été imbriquées dans une troisième : si le Stock final (H4) est inférieur à 100, alors le texte "Commande urgente" est affiché ; si le Stock final est compris entre 100 et 500, le texte "Passer commande" est affiché ; s’il est compris entre 500 et 1000, le texte "Stock compris entre 500 et 1000" est affiché ; si aucune de ces conditions n’est vérifiée, le texte "Stock > à 1000" est affiché.
Notez qu’en fin de formule, il y a une parenthèse pour fermer chaque condition.
Pour faciliter l’écriture de la formule, vous pouvez scinder les arguments en insérant des sauts de ligne à l’aide des touches AltEntrée.
L’insertion de sauts de ligne dans une formule n’a aucune incidence sur le résultat.
Pour...
Combiner l’opérateur OU ou ET dans une formule conditionnelle
Pour poser plusieurs conditions, utilisez l’opérateur OU ou ET selon le cas :
-
si plusieurs conditions doivent être vérifiées en même temps :
=SI(ET(cond1;cond2;…;condn);action à réaliser si les n conditions sont satisfaites;action à réaliser si au moins une des conditions n’est pas satisfaite)
-
si au moins une des conditions doit être vraie :
=SI(OU(cond1;cond2;…;condn);action à réaliser si au moins une condition est satisfaite;action à réaliser si aucune condition n’est satisfaite)
(lettre e de
la police Webdings) est affiché dans la cellule de la colonne Voiture, dans le cas contraire, la
cellule est vide :
Utiliser la fonction SI.CONDITIONS
Similaire aux fonctions SI imbriquées, la fonction SI.CONDITIONS, apparue en 2019, permet de tester jusqu’à 127 conditions.
La syntaxe est la suivante :
=SI.CONDITIONS(Test_logique1;Valeur_si_vrai1;[Test_logique2];[Valeur_si_vrai2]...)
Sur cet exemple, si l’âge de l’enfant est inférieur à 10 ans, la catégorie Poussin s’affiche ; si l’âge est inférieur à 15, c’est la catégorie Junior qui s’affiche et si l’âge est supérieur ou égal à 15, la catégorie Junior Ado s’affiche :
Utiliser la fonction SI.MULTIPLE
La fonction SI.MULTIPLE, apparue en 2019, permet d’évaluer une valeur et d’afficher le résultat correspondant à la valeur recherchée. Vous pouvez ainsi évaluer jusqu’à 126 conditions.
La syntaxe est la suivante :
=SI.MULTIPLE(Expression;Valeur1,Résultat1;[Valeur2;Résultat2];[Défaut ou Valeur3;Résultat3]...)
|
Expression |
correspond à la valeur à évaluer. |
|
Valeur |
correspond à la valeur recherchée. |
|
Résultat |
correspond à la valeur à renvoyer en cas de correspondance. |
|
Défaut |
correspond à la valeur à renvoyer en cas de non-correspondance. |
Sur cet exemple, si la valeur, dans la colonne Choix, est 1, le texte de la cellule E2 sera affiché (Natation) ; si la valeur correspond à 2, c’est le texte de la cellule E3 qui sera affiché (Équitation) ; en cas de non correspondance, c’est la valeur de la cellule E4 (Course) qui sera affichée :
Compter les cellules répondant à un critère spécifique (NB.SI)
La fonction NB.SI dont la syntaxe est =NB.SI(plage_de_cellules;critères), permet de compter le nombre de cellules qui répondent à un ou plusieurs critères.
|
Plage_de_cellules |
Correspond à la plage de cellules contenant le critère recherche. |
|
Critères |
Correspond au critère recherché pour le comptage des cellules correspondantes. |
Pour illustrer cette fonction, nous avons calculé le nombre de jours dont la quantité de pluie est supérieure à 5 mm.
Cliquez sur la cellule dans laquelle vous souhaitez afficher le résultat.
Commencez à saisir le début de la formule =NB.SI(
Puis à l’aide d’un cliqué-glissé, sélectionnez la plage de cellules sur laquelle porte le calcul.
Vous pouvez aussi bien sûr, saisir la référence de la plage de cellules ou son nom si elle a été nommée.
Saisissez un point-virgule (;) pour indiquer le changement d’argument.
Puis, saisissez le critère entre guillemets; celui-ci peut être composé :
-
d’un nombre : saisir alors la valeur directement ; exemple : =NB.SI((C2:C18);5) pour rechercher uniquement la quantité de pluie égale à 5.
-
d’une référence de cellule ;...
Calculer la somme d’une plage répondant à un critère (SOMME.SI)
La fonction SOMME.SI, dont le principe est sensiblement identique à celui de la fonction NB.SI (cf. Compter les cellules répondant à un critère spécifique (NB.SI)), permet d’additionner les cellules d’une plage répondant à un critère donné.
La syntaxe de la fonction est : =SOMME.SI(plage_de_cellules;critère;plage_à_additionner).
|
Plage_de_cellules |
Correspond à la plage de cellules dans laquelle se trouve le critère à rechercher. |
|
Critère |
Correspond au critère recherché pour additionner les cellules répondant à ce critère. |
|
Plage_à_additionner |
Cet argument peut être omis si l’argument Plage_de_cellules contient les valeurs à additionner ; dans le cas contraire, cet argument correspond à la page de cellules à additionner si les cellules correspondent au critère. |
Pour illustrer cette fonction, nous avons calculé en F17 la somme de la quantité d’eau uniquement si la valeur journalière est supérieure à 5 mm en utilisant seulement les deux premiers arguments de la fonction car la plage de cellules contenant le critère et celle contenant les valeurs à calculer sont les mêmes (C2 à C18). En F18, nous avons calculé...
Utiliser les fonctions MAX.SI.ENS et MIN.SI.ENS
La fonction MAX.SI.ENS permet d’obtenir la plus grande valeur d’une plage de cellules répondant à un ou plusieurs critères.
La fonction MIN.SI.ENS permet d’obtenir la plus petite valeur d’une plage de cellules répondant à un ou plusieurs critères.
Leur syntaxe est la suivante :
=MAX.SI.ENS(Plage_max;Plage_critères1;Critères1;[Plage_critères2];[Critères2]...)
=MIN.SI.ENS(Plage_min;Plage_critères1;Critères1;[Plage_critères2];[Critères2]...)
|
Plage_max/min |
Correspond à la plage de cellules contenant les valeurs recherchées. |
|
Plage_critères |
Correspond à la plage de cellules contenant le critère. |
|
Critères |
Saisissez le critère recherché entre guillemets. |
Sur cet exemple, en G3, la fonction MAX.SI.ENS affiche le nombre maximal d’enfants des femmes mariées ; en G7, la fonction MIN.SI.ENS affiche le nombre minimal d’enfants des femmes mariées :
Utiliser des zones nommées dans une formule
Cette fonctionnalité permet de remplacer dans une formule, une référence de plages de cellules par la zone nommée correspondante. Nous vous rappelons que les cellules ou plages de cellules nommées sont gérées comme des références absolues lors de la copie des formules.
Commencez la formule et arrêtez-vous lorsque vous avez besoin d’insérer le nom.
Cliquez sur le bouton Dans une formule du groupe Noms définis de l’onglet Formules.
La liste des zones préalablement nommées s’affiche (cf. chapitre Zones nommées - Nommer des plages de cellules) :
Cliquez sur le nom correspondant à la plage de cellules à insérer dans la formule.
Continuez et terminez la formule.
Vous pouvez également saisir le nom directement dans la formule, à la place des références de cellules.
Insérer des lignes de statistiques
Il s’agit d’ajouter des lignes de sous-totaux.
Triez le tableau en fonction de la colonne qui contiendra les groupes devant faire l’objet d’un sous-total.
Sélectionnez une cellule du tableau concerné par les lignes de statistiques.
Activez l’onglet Données et cliquez sur le bouton Sous-total du groupe Plan.
Dans la liste À chaque changement de, sélectionnez la colonne qui contient les groupes devant faire l’objet d’un calcul statistique.
Choisissez ensuite dans la liste Utiliser la fonction le calcul à effectuer.
|
Somme |
Calcule la somme. |
|
Nombre |
Calcule le nombre d’éléments. |
|
Moyenne |
Calcule la moyenne. |
|
Max |
Repère la valeur maximale. |
|
Min |
Repère la valeur minimale. |
|
Produit |
Multiplie les valeurs. |
|
Chiffres |
Détermine le nombre de valeurs comprises dans la liste des arguments (Nbval). |
|
Écartype |
(D’une série de nombres) représente la dispersion des valeurs autour de la moyenne. |
|
Écartypep |
Calcule l’écart type d’une population à partir de la population entière. |
|
Var |
Calcule la variance qui est égale au carré de l’écart type, sur la base d’un échantillon. |
|
Varp |
Calcule la variance sur la base de l’ensemble de la population. |
Enfin, cochez les colonnes qui contiennent les valeurs...