Editions ENI Livres | Vidéos | e-Formations
Calculs avancés
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 (sous Excel pour Macintosh, le calendrier commence en 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 2024, le mois 12, le jour 20 ; la formule =DATE(A1;B1;C1) renverra 20/12/2024.
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/2024 n’est pas reconnue comme date par Excel mais comme donnée de type texte. Ceci arrive généralement lorsque des données ont été importées.
FIN.MOIS(date_départ;mois)
Renvoie le numéro de série du dernier jour du mois situé dans un intervalle exprimé en nombre de mois dans le futur ou le passé.
Cette fonction est très utile pour calculer les dates d’échéances.
Exemple : pour déterminer la date d’échéance d’une facture payable à 30 jours fin de mois en supposant que la date de facturation saisie en B2 est le 15/01/2025, la fonction s’écrit =FIN.MOIS(B2;1) et renvoie 28/02/2025.
FRACTION.ANNEE(date_début;date_fin;[base])
Renvoie la fraction de l’année représentant le nombre de jours entre la date de début et la date de fin.
Cette fonction est pratique pour calculer le nombre d’années d’ancienneté, l’âge et, plus généralement, le nombre d’années écoulées entre deux dates. La valeur obtenue est souvent une valeur décimale du type 12,4568256 (12 années et plus…).
HEURE(numéro_de_série)
Renvoie le nombre d’heures : un nombre entier entre 0 et 23.
Numéro_série correspond à un temps écrit de la manière suivante hh:mm:ss (heures:minutes:secondes) exemple : 12:25:30 renverra 12.
JOUR(numéro_de_série)
Donne...
Effectuer des calculs sur des données de type heure
Après avoir abordé le principe de calcul sur les heures utilisé par Excel, nous vous proposons de découvrir des méthodes et formules spécifiques au traitement d’heures à travers quelques exemples.
Principe de calcul sur des heures
Lorsque vous saisissez une heure dans une cellule, Excel l’enregistre sous forme de nombre décimal allant de 0 à 1 (1 non compris) pour chaque période de 24 heures.
Pour que Excel puisse reconnaître l’information comme une heure et l’enregistrer sous la forme d’un nombre décimal, vous devez séparer les différentes parties de l’heure par le signe deux-points (:). Par exemple, 18h30 et 43 secondes doit être saisi suivant cette syntaxe 18:30:43. Pour ne pas inclure les secondes, saisissez 18:30.
|
Exemple d’heure saisie |
Valeur enregistrée par Excel |
|
00:00 (minuit) |
0 |
|
11:59 |
0,499305555555556 |
|
12:00 (midi) |
0,5 |
|
15:00 (15h) |
0,625 |
|
18:00 (18h) |
0,75 |
Ce concept d’heure permet d’appliquer des calculs arithmétiques aux heures.
Exemple : pour calculer la différence entre 18:00 (18 h) et 15:00 (15 h), Excel effectue ce calcul :
= 0,75 - 0,625 = 0,125
Excel met à votre disposition de nombreux formats d’heure prédéfinis.
Calculer la différence entre deux heures
Pour effectuer ce calcul et représenter le résultat au format horaire standard, c’est-à-dire heures:minutes:secondes, vous...
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 est FAUX, seule la valeur recherchée est prise en compte. Dans le cas où la valeur recherchée n’est pas trouvée, la fonction renvoie un message d’erreur #N/A. |
Sur cet exemple, la fonction RECHERCHEV cherche la référence exacte du produit (contenue en cellule A4) dans le tableau des codes produits (G2 à I7) et y trouve la désignation dans sa deuxième colonne et son prix dans la troisième colonne.
Validez la formule par la touche Entrée.
La plage de cellules G2:I7 est en référence absolue dans la formule (présence des symboles $) afin qu’elle reste toujours la même quand les formules seront recopiées sur les autres cellules du tableau. Pour faciliter la lecture de la formule (et conserver ses références absolues), vous pouvez nommer cette plage de cellules et faire référence au nom de cette plage de cellules. Cette technique s’avère intéressante si la table matrice se trouve sur une autre feuille par exemple ou dans un autre classeur.
D’une manière similaire, la fonction...
Utiliser des fonctions de texte
En 2024, trois nouvelles fonctions sont venues s’ajouter à celles déjà existantes : TEXTE.AVANT, TEXTE.APRES et FRACTIONNER.TEXTE.
Fonction TEXTE.AVANT
La fonction TEXTE.AVANT (nouveauté 2024) permet d’extraire le texte d’une chaîne de caractères se trouvant avant un certain caractère.
Sa syntaxe est la suivante :
=TEXTE.AVANT(Texte;Délimiteur;[Numéro_instance];[Mode_recherche];[Correspondance_fin];[Si_introuvable])
|
Texte |
Texte (habituellement un nom de cellule) à utiliser pour la recherche. Cet argument est obligatoire. |
|
Délimiteur |
Texte qui donne le point avant lequel le texte doit être extrait. Argument obligatoire. |
|
Numéro instance |
Le numéro d’occurrence du délimiteur après lequel vous souhaitez extraire le texte. Par défaut, c’est 1. |
|
Mode_recherche |
Détermine si la case doit être respectée. Par défaut, la valeur est 0 (respect de la casse) ; utilisez 1 pour ne pas respecter la casse. |
|
Correspondance_fin |
Lorsque cet argument est égal à 1, si la fonction ne trouve pas délimiteur, elle prendra la fin comme un délimiteur. Par défaut, la valeur de cet argument est de 0 donc la fin du texte n’est pas prise en compte dans la recherche. |
|
Si_introuvable |
Permet de préciser la valeur à renvoyer si aucune correspondance n’est trouvée. Par défaut, c’est #N/A. |
Sur cet exemple, la colonne B contient la description d’une série de produits et nous voulons extraire dans la colonne C l’information du Type de produit, qui correspond à l’information avant le premier tiret :
Fonction TEXTE.APRES
La fonction TEXTE.APRES (nouveauté 2024) est le pendant de la fonction TEXTE.AVANT qui permet d’extraire le texte d’une chaîne de caractères se trouvant après un certain caractère.
Sa syntaxe comporte les mêmes arguments que la fonction TEXTE.AVANT :
=TEXTE.APRES(Texte;Délimiteur;[Numéro_instance];[Mode_recherche];[Correspondance_fin];[Si_introuvable])
Créer des fonctions personnalisées
En 2024 a été introduite la fonction LAMBDA, qui permet de créer des fonctions personnalisées et réutilisables en leur donnant un nom facile à retenir, sans faire appel à VBA, des macros ou JavaScript. La fonction personnalisée est alors disponible dans tout le classeur et elle est appelée de la même manière que les fonctions Excel natives.
Sa syntaxe est la suivante :
=LAMBDA([paramètre1;paramètre2;…;]calcul)
|
Paramètres |
Ce sont des valeurs à transmettre à la fonction, comme des références de cellule, une chaîne de caractères ou un nombre. Ces arguments sont facultatifs et la fonction peut comporter jusqu’à 253 paramètres. |
|
Calcul |
La formule à exécuter et renvoyer comme résultat de la fonction. La formule doit arriver en dernier dans l’expression et renvoyer un résultat. |
Les paramètres de LAMBDA suivent les mêmes règles de syntaxe que les autres noms sur Excel avec une exception : il ne faut pas utiliser le point (.) dans le nom du paramètre.
Il convient de suivre une série d’étapes pour s’assurer que la fonction LAMBDA renvoie le résultat souhaité.
Testez d’abord la formule à automatiser avec LAMBDA pour vous assurer qu’elle...
Affecter un nom à un calcul intermédiaire
La fonction LET, introduite en 2021, est utilisée pour affecter un nom à un résultat de calcul afin de stocker des calculs intermédiaires ou de définir des noms à l’intérieur d’une formule.
Pour utiliser la fonction LET, vous devez définir des éléments composés d’un binôme : nom et valeur_associée (jusqu’à 126 binômes possibles) et d’un calcul qui utilise tous les éléments.
Sa syntaxe est la suivante :
=LET(nom_élément1;valeur_élément1; [nom_élément2;valeur_élément2];…;calcul)
|
Nom_élément1 |
Premier nom à définir, doit commencer obligatoirement par une lettre. |
|
Valeur_élément1 |
Valeur associée à Nom_élément1, cette valeur peut être une valeur saisie ou une valeur résultant d’un calcul. |
|
Nom_élément2 ; valeur_élément2 |
Nom et valeur du deuxième élément (facultatif). La fonction peut ne comporter qu’un seul élément ou aller jusqu’à 126 éléments. |
|
Calcul |
Le calcul utilise tous les noms et doit être le dernier argument de la fonction. |
Exemples d’utilisation :
Dans cet exemple, le CA 2024 est multiplié...
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...
Générer une table à double entrée
Pour illustrer l’utilisation d’une table à double entrée, nous souhaitons connaître les différentes valeurs de remboursement pour un capital emprunté fixe de 15 000 €, un nombre variable de mensualités et des taux d’intérêt variables.
Saisissez les éléments initiaux du calcul à réaliser (taux d’intérêt, la durée de l’emprunt et le montant de l’emprunt, dans notre exemple).
Saisissez les en-têtes et les lignes de la table, lesquels correspondent aux paramètres variables.
Attention, la table ainsi préparée ne doit pas être accolée aux éléments initiaux et la première donnée variable en ligne doit être située une ligne plus haut et une colonne plus à droite que la première donnée variable en colonne.
À l’intersection de cette ligne et de cette colonne, saisissez la formule de calcul puis validez.
Pour cet exemple, en A11 nous avons utilisé la fonction VPM pour calculer les mensualités de remboursement d’un emprunt sur la base de mensualités et d’un taux d’intérêt constants. Nous avons utilisé la fonction ABS pour effectuer ce calcul en valeur absolue....
Utiliser les nouvelles fonctions de tableaux dynamiques
Traditionnellement, les fonctions sur Excel affichent leur résultat dans une seule cellule. Sur cet exemple, la formule se trouvant dans la cellule B9 renvoie son résultat dans la même cellule :
En 2019, Microsoft a introduit un nouveau type de formule : les tableaux propagés ou tableaux dynamiques (ou dynamic arrays en anglais, à ne pas confondre avec les tableaux croisés dynamiques). La particularité de ces fonctions est que, contrairement aux formules classiques, le résultat des tableaux dynamiques peut remplir automatiquement plusieurs cellules. Sur cet exemple, la fonction FRACTIONNER.TEXTE se trouve dans la cellule A5, récupère le texte présent dans la cellule A2 et affiche son résultat dans la plage A5:C8, qui apparaît entourée d’un bord bleu :
Le mécanisme par lequel le résultat occupe plusieurs cellules s’appelle propagation. Il est automatique. La cellule contenant la formule est la cellule principale et les cellules adjacentes sont les cellules de déversement.
Si une cellule dans la plage de déversement est déjà occupée par une autre valeur ou formule, Excel renvoie une erreur #PROPAGATION! dans la cellule principale. Les cellules occupées doivent être libérées pour permettre à la formule de se propager correctement.
Si la cellule principale est active, la formule de la barre de formule s’affiche normalement. Si la cellule active est une cellule de déversement, la formule de la barre de formule apparaît grisée :
Les résultats qui s’affichent dans les cellules de déversement comportent des particularités :
-
Les cellules de déversement se mettent à jour automatiquement lorsque les données sources ou les paramètres de la formule changent. Cela garantit que les résultats sont toujours à jour. C’est pour cette raison que ces tableaux sont dits dynamiques.
-
Les cellules de déversement ne sont pas modifiables ou effaçables individuellement, car elles sont contrôlées par la formule principale.
-
Elles ne sont pas copiables individuellement non plus.
Les résultats des tableaux dynamiques peuvent être utilisés avec d’autres fonctions Excel, comme SOMME, MOYENNE ou INDEX, pour effectuer des calculs supplémentaires sur les données déversées.
-
Pour référencer une cellule individuelle, procédez comme d’habitude.
-
Pour référencer toute la plage de déversement, utilisez la référence de la cellule principale suivie de # (par exemple : A5#).
-
À ce jour, il n’est pas possible de référencer une plage de données partielle à l’intérieur de la plage de déversement.
Sur cet exemple, la cellule B10 contient le résultat de la formule =B5+B6+B7+B8 (chaque cellule est référencée individuellement) ; la cellule B11 contient la formule =SOMME(B5:B8) mais celle-ci ne fonctionne pas car les cellules B5 à B8 constituent une plage partielle de la plage de déversement A5:C8.
En 2021 il existait six fonctions qui renvoyaient des tableaux dynamiques : UNIQUE, TRIER, TRIERPAR, FILTRE, SEQUENCE et TABLEAU.ALEA. En 2024, de nouvelles fonctions de tableaux dynamiques ont été ajoutées, dont FRACTIONNER.TEXTE qui a été présentée dans la section Utiliser des fonctions de texte et celles qui sont présentées dans la suite de cette section.
Fonctions ASSEMB.H et ASSEMB.V
Les fonctions ASSEMB.H et ASSEMB.V permettent respectivement...