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 (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)
Convertir un numéro de série en année.
AUJOURDHUI()
Renvoyer le numéro de série de la date du jour.
DATE(année;mois;jour)...
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...
Utiliser une fonction de recherche
La fonction 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 cherche dans la colonne correspondant à no_index_col. |
|
table_matrice |
Est le tableau à partir duquel les données vont être récupérées. Cet argument peut être les références d’une plage de cellules ou le nom d’une plage de cellules. |
|
no_index_col |
Est le numéro de la colonne du tableau (Table_matrice) qui contient la valeur recherchée. La première colonne du tableau... |
Utiliser des fonctions sur le texte
Concaténer des fonctions de type texte
La fonction CONCAT permet la concaténation de texte. Vous pouvez assembler jusqu’à 254 chaînes de texte. Cette fonction remplace la fonction CONCATENER, elle est plus simple et plus courte à utiliser.
La syntaxe est la suivante :
=CONCAT(Texte1;Texte2;[Texte3]...)
Sur cet exemple, la fonction CONCAT affiche les données des cellules A2, B2 et C2 séparées par un espace et suivi du symbole monétaire € :
Si le séparateur des chaînes de caractères est identique, il est préférable d’utiliser la fonction JOINDRE.TEXTE.
Fonction JOINDRE.TEXTE
La fonction JOINDRE.TEXTE permet d’afficher le texte de plusieurs cellules les uns à la suite des autres séparés par le ou les caractères spécifiés.
La syntaxe est la suivante :
=JOINDRE.TEXTE(Délimiteur;Ignorer_vide;Texte1;[Texte2]...)
|
Délimiteur |
Pour cet argument, spécifiez le ou les caractères à utiliser pour séparer les chaines de texte. |
|
Ignorer_vide |
Saisissez VRAI pour ne pas prendre en compte les cellules vides de la plage sélectionnée ou FAUX dans le cas contraire. |
|
Texte1;Texte2... |
Sélectionnez les différentes plages de cellules contenant les données à afficher. |
Sur cet exemple, la fonction JOINDRE.TEXTE affiche...
Utiliser des variables dans une formule
La fonction LET est destinée à faciliter l’écriture de certaines formules complexes en permettant de déclarer et d’attribuer des valeurs à des variables à l’intérieur d’une formule. Vous définissez ainsi des paires de noms et de valeurs associées qui seront utilisées dans un calcul.
La fonction LET prend en charge un maximum de 126 paires.
La syntaxe est la suivante :
=LET(nom1;nom_valeur1;calcul_ou_nom2;[nom2 / valeur2];...)
|
nom1 |
Correspond au nom à attribuer à la valeur ou au calcul ; ce nom doit commencer par une lettre. |
|
nom_valeur1 |
Correspond à la valeur ou au calcul à affecter à nom1. |
|
calcul |
Correspond au calcul à réaliser à l’aide des noms et des valeurs attribuées. |
Sur cet exemple, la fonction LET permet de simplifier l’écriture de la fonction FILTRE et de modifier rapidement le nom du vendeur Hervé en Marie en ne saisissant qu’une seule fois la valeur de la variable X :
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. chapitre 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, le montant de l’emprunt, pour 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. Et nous avons utilisé la fonction ABS pour effectuer ce calcul en valeur absolue....
Utiliser une formule matricielle
Cette formule a la particularité de pouvoir effectuer plusieurs calculs et de renvoyer des résultats simples ou multiples. Une formule matricielle ne peut intervenir que sur deux (ou plus) ensembles de valeurs appelés communément arguments matriciels. Ces derniers doivent avoir le même nombre de lignes et de colonnes.
Procédez comme pour un calcul ordinaire mais, au lieu de travailler par cellule, travaillez par plage de cellules et, au lieu de valider par Entrée ou CtrlEntrée, validez par la combinaison de touches CtrlShiftEntrée.
Voyons à travers trois exemples différents, l’utilisation d’une formule matricielle.
Pour simplifier la lecture des formules, nous avons nommé les zones : Client (A2:A11), Montant (B2:B11) ; Date (C2:C11).
Les formules matricielles ont été saisies en G2, G6 et G10 :
|
G2 |
{=SOMME(SI(Client="DUPONT Antoine";1;0))} Par cette formule, nous recherchons dans la plage de cellules Client, l’occurrence "DUPONT Antoine" ; dans le cas où la condition est vérifiée, Excel ajoute 1, sinon Excel ajoute 0. Le même résultat pourrait être obtenu à l’aide de la fonction NB.SI. |
|
G6 |
{=SOMME(Montant*(Client="PETIT Thierry"))} Par cette formule, nous demandons à calculer la somme... |