Editions ENI Livres | Vidéos | e-Formations
Calculs
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.
Effectuer des calculs sur des données de type date
Après avoir abordé le principe de calcul sur les dates utilisé par Excel, nous vous proposons de découvrir quelques fonctions spécifiques au traitement des dates à travers quelques exemples.
Principe de calcul sur des jours
Si le calcul porte sur des jours, procédez comme pour les autres calculs car Excel enregistre les dates sous la forme de nombres séquentiels appelés numéros de série. De ce fait, elles peuvent être ajoutées, soustraites et incluses dans d’autres calculs.
Par défaut, sous Windows, Excel utilise le calendrier depuis 1900 (Excel pour Macintosh, le calendrier depuis 1904). Le 1er janvier 1900 correspond donc (pour Excel sous Windows) au numéro de série 1 et le 1er janvier 2005 correspond au nombre 38 353, car 38 353 jours se sont écoulés depuis le 1er janvier 1900.
Pour utiliser une fonction spécifique de gestion de dates et d’heures, vous pouvez activer l’onglet Formules et cliquer sur le bouton DateHeure du groupe Bibliothèque de fonctions puis sur la fonction concernée afin d’utiliser l’Assistant.
ANNEE(numéro_de_série)
Renvoie l’année, un nombre entier entre 1900 et 9999.
Cette fonction permet d’isoler l’année d’une date ; exemple : la cellule A1 contient la valeur 12/12/2021, la fonction =ANNEE(A1) renvoie 2021.
AUJOURDHUI()
Renvoie la date du jour au format de date.
Cette fonction a la particularité de ne pas posséder d’arguments, il n’y a rien à écrire entre parenthèses, mais attention à ne pas oublier ces deux parenthèses.
DATE(année;mois;jour)
Renvoie un numéro de série.
La fonction DATE permet la reconstitution d’une date à partir de trois valeurs : supposons qu’en A1, B1 et C1 nous ayons respectivement l’année 2021, le mois 12, le jour 20 ; la formule =DATE(A1;B1;C1) renverra 20/12/2021.
DATEVAL(date_texte)
Convertir une date représentée sous forme de texte en numéro de série.
Sous forme de texte signifie que la date saisie 20/12/2021 n’est pas reconnue comme date par Excel mais comme donnée de type texte. Ceci arrive...
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...
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...
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é...
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...
Utiliser les fonctions de recherche
Fonctions RECHERCHEV
La fonction RECHERCHEV permet de chercher une valeur dans la première colonne d’un tableau (V = Verticale) puis de renvoyer la valeur contenue dans la cellule située sur la même ligne et dans la colonne spécifiée.
Créez un tableau permettant de regrouper les données que vous allez devoir récupérer par la suite lors de la recherche puis triez le tableau par ordre croissant, sur les données de la première colonne du tableau.
Nommez cette plage de cellules si vous ne souhaitez pas la sélectionner lors de la création de la formule de calcul.
Cliquez dans la cellule où doit être affichée la donnée recherchée du tableau.
Créez votre formule de calcul en respectant la syntaxe suivante :
=RECHERCHEV(valeur_cherchée;table_matrice;no_index_col;valeur_proche)
|
valeur_cherchée |
Est la valeur que la fonction cherchera dans la première colonne de la table matrice. |
|
table_matrice |
Est le tableau à partir duquel les données vont être récupérées. Cela peut être les références ou le nom d’une plage de cellules. |
|
no_index_col |
Est le numéro d’ordre de la colonne (de la table_matrice qui contient la valeur récupérée. La première colonne du tableau est la colonne 1. |
|
valeur_proche |
Est une valeur logique qui permet d’effectuer une recherche exacte ou voisine de celle recherchée. Si la valeur_proche est VRAI ou omise, une donnée égale ou immédiatement inférieure à la valeur cherchée est affichée. Si la valeur_proche... |
Quelques nouvelles fonctions de calcul
De nouvelles fonctions sont venues enrichir la bibliothèque de fonctions d’Excel. Certaines d’entre elles ont été ajoutées dès la version 2019, d’autres ne sont apparues que dans cette nouvelle version 2021 : CONCAT, JOINDRE.TEXTE, VALEUR.EN.TEXTE, TABLEAU.EN.TEXTE, SOMME.SI.ENS, MOYENNE.SI.ENS, MAX.SI.ENS, MIN.SI.ENS, NB.SI.ENS, SI.CONDITIONS, FILTRE, TRIER, TRIERPAR, UNIQUE, RECHERCHEX, EQUIVX, LET, TABLEAU.ALEAT, SEQUENCE.
Les fonctions SOMME.SI.ENS, MOYENNE.SI.ENS, MAX.SI.ENS, MIN.SI.ENS, NB.SI.ENS, SI.CONDITIONS sont expliquées en détail dans le chapitre Calculs. La fonction RECHERCHEX est détaillée dans la section Utiliser les fonctions de recherche.
Fonction FILTRE
Nouveauté 2021, cette fonction filtre une plage de données en fonction d’un ou plusieurs critères et renvoie plusieurs résultats.
Sa syntaxe est :
=FILTRE(plage_à_filtrer;plage_à_inclure_et_critère;[valeur_si_vide])
|
Plage_à_filtrer |
Correspond à la liste de données à filtrer |
|
Plage_à_inclure_et_critère |
Zone de définition du critère comportant la plage de cellules (où se trouve le critère recherché) accompagnée d’un comparateur (= > < <= >= <>) et du critère. |
|
[valeur_si_vide] |
Valeur à renvoyer si le filtre ne trouve aucun résultat. |
Dans l’exemple ci-dessus, le but est de récupérer les informations...
Consolider des données
Cette fonctionnalité permet de combiner (pour les cumuler, par exemple) des valeurs de plusieurs plages de données placées sur différentes feuilles de calcul.
Avant de lancer la consolidation, vérifiez les points suivants :
-
Chaque plage de données source doit être placée dans une feuille de calcul distincte ; aucune des plages source ne doit être placée dans la feuille de calcul sur laquelle vous allez placer la consolidation.
-
Assurez-vous que les tableaux à consolider aient la même structure (même nombre de lignes et de colonnes, même type de données dans les cellules) et qu’ils soient positionnés dans les mêmes cellules sur les différentes feuilles.
-
Nommez, si vous le souhaitez, chaque plage de données source (cf. Zones nommées - Nommer des plages de cellules).
Activez la cellule où vous souhaitez voir placer la première cellule du tableau consolidé.
Activez l’onglet Données et
cliquez sur l’outil Consolider 
du groupe Outils de données.
Choisissez la Fonction de synthèse à utiliser pour consolider les données ; pour cumuler les données des différents tableaux, choisissez Somme.
Si les données à consolider se situent dans un autre classeur, cliquez...
