Blog ENI : Toute la veille numérique !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici

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  :

images/05RB06v23.png

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 :

images/11RB02V21.png

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 images/i039V21.PNG 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.

images/1310rb13.png

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.

images/05RB07v23.png

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...