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 représente le bloc de cellules B1 à C4.
; (point-virgule) ex : B1;C4 représente la cellule B1 et la cellule C4.
-
des constantes, c’est-à-dire des valeurs qui ne sont pas calculées et donc ne changent pas (par exemple, le nombre 1210 ou le texte "Totaux trimestriels" sont des constantes).
-
des fonctions de calcul sont des formules pré-écrites qui permettent d’effectuer une opération de calcul ; par exemple, la fonction MOYENNE...
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.
(ou F9) et pour calculer manuellement
la feuille active, utilisez l’outil Calculer la feuille
(ou ShiftF9)
de l’onglet Formules - groupe Calcul).Lorsque vous modifiez une formule de calcul, les références des cellules...
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 ($B$1) ; si vous appuyez sur F4 une seconde fois, seule la référence de la ligne devient absolue (B$1) ; si vous appuyez sur F4 une troisième fois, c’est alors la référence de la colonne qui devient absolue ($B1) et, si vous appuyez une quatrième fois, la référence redevient relative (B1).
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 de la ligne pourrait être fixée car la recopie se fait vers le bas.
de l’onglet Formules - groupe Vérification des formules).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 C4 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 Tous 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.
Cliquez sur le bouton OK pour activer la boîte de dialogue Arguments de la fonction.
Pour définir chaque argument de la fonction :
-
cliquez dans la zone de saisie correspondante puis cliquez sur le bouton
, -
sélectionnez dans la feuille de calcul, la ou les cellules correspondant à l’argument,
-
cliquez sur le bouton
pour afficher de nouveau la boîte de
dialogue.
Vous pouvez également saisir directement un argument.
La fonction SI permet de créer une formule...
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 d’une parenthèse ) 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 de la barre de formule.
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 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, la moyenne de ces valeurs ; vous visualisez aussi, pour n’importe quelle sélection, le nombre de cellules non vides ; pour visualiser d’autres résultats de fonction, cliquez avec le bouton droit de la souris sur la barre d’état et choisissez parmi les fonctions proposées (Nb (nombres), Minimum ou Maximum).
Utiliser les formules conditionnelles
Ce type de formules permet d’afficher une valeur ou d’effectuer un calcul selon une ou plusieurs conditions.
Fonction SI simple
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 commander") ; dans ce cas, si la condition n’est pas vérifiée, l’action n’étant pas définie dans la formule, Excel affiche la valeur FAUX.
Pour laisser le contenu d’une cellule vide d’un des résultats (VRAI ou FAUX) afin d’éviter l’affichage de VRAI ou FAUX, saisissez deux guillemets ; exemple : =SI(H4<=1000;"A commander";"").
La ou les cellules dont le test...
Combiner l’opérateur OU ou ET dans une formule conditionnelle
Dans la fonction SI, il n’est possible de poser qu’une seule question dans la partie Test_logique. Pour pouvoir poser plusieurs questions, vous pouvez utiliser les opérateurs OU/ET qui seront imbriqués dans la fonction SI.
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 vérifiée :
=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 :
Compter les cellules répondant à un ou plusieurs critères spécifiques
Fonction 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 ; 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 ; exemple : =NB.SI(C2:C18;C2) pour rechercher la quantité contenue dans la cellule C2.
-
d’une expression : saisir l’expression entre guillemets ; exemple : =NB.SI(C2:C18;">5") pour rechercher le nombre de jours pour lesquels la quantité de pluie est supérieure à 5.
-
d’une chaîne de texte : saisir le texte recherché entre guillemets ; exemple : =NB.SI(B2:B18;"lundi") pour rechercher...
Effectuer des statistiques conditionnelles avec un critère
Fonction 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 ou plusieurs critères spécifiques), 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é la somme de la quantité d’eau tombée le dimanche en utilisant les trois arguments de la fonction car la plage de cellules contenant le critère "dimanche" (B2 à B18) n’est pas la même que la plage de cellules contenant les valeurs à additionner (C2 à C18).
Cliquez sur la cellule dans laquelle vous souhaitez afficher le résultat.
Commencez par saisir le début de la formule =SOMME.SI(...
Effectuer des statistiques conditionnelles avec plusieurs critères
Fonction SOMME.SI.ENS
Le but de cette fonction est d’additionner des valeurs en fonction d’un ou plusieurs critères. Comme pour la fonction NB.SI.ENS vous pouvez utiliser plusieurs critères dans des plages différentes avec la fonction SOMME.SI.ENS.
La syntaxe est la suivante :
=SOMME.SI.ENS(plage_à_additionner;plage_critère1;critère1;plage_critère2;critère2…)
Vous noterez la position de la plage de cellules à additionner qui se trouve au début dans la syntaxe de la fonction SOMME.SI.ENS contrairement à la fonction SOMME.SI qui place cette plage en fin de fonction.
Fonction MOYENNE.SI.ENS
Cette fonction est similaire à la fonction SOMME.SI.ENS dans sa structure et son fonctionnement, elle permet de calculer la moyenne des valeurs répondant à un ou plusieurs critères.
La syntaxe est :
=MOYENNE.SI.ENS(plage_moyenne;plage_critère1;critère1;plage_critère2;critère2…)
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é. |
Sur cet exemple...
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. 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 sous-totaux dans une liste de données
Il s’agit d’ajouter des lignes de sous-totaux dans une liste contenant des valeurs numériques (par exemple, une liste de produits avec des prix, une liste de commandes avec des montants...).
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 sur lesquelles effectuer les calculs.
Dans cet exemple, après avoir trié les données en fonction de la Catégorie, nous souhaitons calculer la Somme de la colonne MONTANT pour chaque Catégorie de produit.
Laissez l’option Remplacer les sous-totaux existants cochée...
