Name: Excel 2021
Rating: 4.75 (4 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 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...

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 pouvez utiliser la fonction TEXTE dont la syntaxe est =TEXTE(valeur;format_texte).

L’argument valeur représente une valeur numérique ou une formule dont le résultat est une valeur numérique ou bien encore une référence à une cellule contenant une valeur numérique.
L’argument format_texte...

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

Utiliser les 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 CONCAT

La fonction CONCAT (nouveauté 2019) 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 (nouveauté 2019) 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...

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 Parcourir, localisez le classeur concerné, sélectionnez-le puis cliquez sur OK.

Si les données à consolider se situent dans le classeur actif, pour chaque plage de données à consolider :

cliquez sur le bouton pour réduire la boîte de dialogue,
accédez à la feuille de calcul puis sélectionnez les cellules concernées ou saisissez le nom de la zone des données source,
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.

Sélectionnez la plage de cellules comprenant la formule de calcul jusqu’à la dernière cellule de la table.

Activez l’onglet Données, cliquez sur le bouton Analyse scénarios du groupe Prévision.

Activez l’option Table de données.

Dans la zone Cellule d’entrée en ligne, indiquez la référence de la cellule utilisée dans la formule qui correspond aux données variables situées...

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. Pour valider cette formule vous pouvez, depuis la version 2021 utiliser simplement la touche Entrée (dans les versions précédentes il fallait obligatoirement utiliser 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).

Combien de fois DUPONT Antoine apparaît-il ?

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.

À noter que le même résultat pourrait être obtenu à l’aide de la fonction NB.SI.

Quel est le montant total de PETIT Thierry ?

Par cette formule, nous demandons à calculer la somme des montants SOMME(montant correspondant au client PETIT Thierry : *(client="PETIT Thierry").

L’opérateur de multiplication * introduit le critère qui est inscrit entre parenthèses, il peut se traduire par SI ; pour notre exemple, cela donne : additionner les montants SI le client est Petit Thierry ou par ET SI dans le cas où il y aurait plusieurs critères comme ci-dessous.

Quel est le montant total de PETIT Thierry...