Name: Excel (versions 2019 ou Office 365) L'intégrale
Rating: 4.5 (2 reviews)

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 xcel 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)

Renvoyer le numéro de série d’une date précise.

DATEVAL(date_texte)

Convertir une date représentée sous forme de texte en numéro de série.

FIN.MOIS(date_départ;mois)

Renvoyer le numéro séquentiel de la date du 1er jour du mois précédant ou suivant la date_départ du nombre de mois indiqué.

FRACTION.ANNEE(date_début;date_fin;[base])

Renvoyer la fraction de l’année représentant le nombre de jours entre la date de début et la date de fin.

HEURE(numéro_de_série)

Convertir un numéro de série en heure.

JOUR(numéro_de_série)

Convertir un numéro de série en jour du mois.

JOURS(date_fin;date_début)

Calculer le nombre de jours entre les deux dates.

JOURS360(date_début;date_fin;[méthode])

Calculer le nombre de jours séparant deux dates sur la base d’une année...

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 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 est la colonne...

Utiliser les nouvelles fonctions de calcul

Excel 2019 propose de nouvelles fonctions de calcul. Nous vous présentons ci-après les fonctions suivantes : CONCAT,JOINDRE.TEXTE, MAX.SI.ENS, MIN.SI.ENS, SI.CONDITIONS et SI.MULTIPLE.

Fonction CONCAT

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.

Sa syntaxe est la suivante :

=CONCAT(Texte1;Texte2;[Texte3]...)

Sur cet exemple, la fonction CONCAT affiche les données des cellules A3, B3 et C3 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.

Sa syntaxe est la suivante :

=JOINDRE.TEXTE(Délimiteur;Ignorer_vide;Texte2;[Texte3]...)

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

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 images/IC-067.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 sur le bouton...

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) ; DateClient (C2:C11).

Les formules matricielles ont été saisies en G2, G6 et G9 :

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