HACKER EXCEL POUR UTILISER DES HEURES NÉGATIVES SANS VBA !
Dans ce petit tutoriel, qui s’inscrit dans le cadre de la série de vidéos consacrée à la découverte de l’utilisation des dates et des heures dans Excel, nous allons voir comment contourner l’usage classique d’Excel pour utiliser des heures négatives.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Présentation
Comme nous l’avons déjà abordé au cours de plusieurs autres articles disponibles sur Excelformation.fr, il faut savoir qu’Excel a une manière très intelligente de gérer les heures.
Il suffit en effet de saisir un horaire dans une cellule (en utilisant simplement les deux points « : ») :
À ce moment-là, Excel a littéralement transformé notre saisie pour l’adapter au contexte en effectuant toutes ces opérations :
- Pour commencer, notre saisie va être convertie en valeur numérique (nous verrons cela juste après),
- Puis pour bien nous faire comprendre qu’il s’agit d’une heure, celui-ci va modifier le format de la cellule pour que celui-ci nous présente bien l’heure telle que nous l’avions alors saisi (le numéro de série n’apparaît plus)
- Il va également modifier la manière de présenter l’heure dans la zone de saisie, ce qui va nous permettre de facilement modifier celle-ci par la suite
p>Ne nous fions pas aux apparences, car comme nous venons de le voir, les heures dans Excel sont en réalité des valeurs numériques décimale pour lesquelles une unité correspond à 24h (donc 0,5 = 12 heures).
Pour nous en rendre compte, il suffit simplement de modifier le format de la cellule (par exemple en appliquant le format Nombre depuis le menu Accueil du Ruban):
La cellule prend alors la valeur de 0,60.
Nous pouvons également réaliser l’opération inverse, en saisissant le nombre 0,50, puis en réappliquant le format Heure :
Dés lors, nous pouvons facilement comprendre pourquoi la saisie des heures est alors impossible.
En effet, une heure est un numéro de série qui commence à zéro pour minuit et qui va jusque 1 pour minuit.
Passé ce nombre, nous changeons alors de jours pour gérer l’heure du lendemain (ou des jours suivant).
En outre lorsque nous commençons une saisie par le signe moins, alors Excel considère que nous souhaitons insérer une formule (en effet « - » est égal à « =- ») :
Lorsque nous saisissons :
-G8+G9
Excel insère automatiquement le symbole égal (« = ») devant :
=-G8+G9
Aussi lorsque nous saisissons la formule « -1 :15 » :
Excel au lieu de comprendre que nous souhaitons insérer une heure négative pense que nous faisons référence aux lignes 1 à 15 !
2. Saisie des heures négatives
Mais bien heureusement nous allons maintenant découvrir une petite astuce qui va nous permettre de saisir des heures négatives.
Il suffit pour cela de modifier le format de la cellule pour empêcher Excel de capter les saisies d’heures.
Pour cela, nous affectons alors le format Texte (après avoir sélectionné toutes les cellules de la colonne) :
Evidemment, à ce moment là les saisies déjà présentes vont également être modifiées !
Il suffit alors de les ressaisir :
Comme vous pouvez alors le constater, la cellule est maintenant alignée sur la gauche de la cellule !
Normal il s’agit maintenant d’un texte !
En tant que Texte, Excel ne va plus interpréter notre saisie et nous pouvons maintenant saisir tout types de données :
Ensuite, pour harmoniser la mise en forme, nous pouvons aligner toutes les cellules sur la droite :
3. Utiliser des heures négatives
Maintenant que nous avons saisi des heures négatives, encore faut-il pouvoir les exploiter dans nos formules !
En effet, même si nous avons transformé nos cellules en formats textes, Excel va convertir celles-ci en valeurs négatives dès lors que nous allons les exploiter dans une formule :
Logiquement, les heures négatives vont alors générer des erreurs de formules !
L’astuce consiste alors à supprimer le signe moins éventuellement présent dans la formule !
Pour cela Excel propose la fonction SUBSTITUE() qui permet de remplacer un caractère (ou plusieurs) par un ou plusieurs autres !
=[@[Cumul Départ]]+SUBSTITUE([@[Récupération Mars]];"-";"")
L’histoire d’y voir un peu plus claire dans nos données, appliquons maintenant un format Heure aux cellules de la colonne :
A présent, plus d’erreur de calcul, en revanche, nous ne sommes pas sortis d’affaire pour autant car comme vous pouvez alors le constater les résultats présentés sont évidemment erronés étant donné que nous avons purement et simplement ignoré les signes de négativité !
Pour corriger ce problème, nous allons maintenant utiliser une deuxième petite astuce, qui consiste simplement à déterminé un coefficient de multiplication à appliquer sur ces horaires :
- Si nous pouvons relever la présence d’un signe moins, alors nous multiplions la valeur de la cellule par -1,
- En revanche, lorsqu’il n’y a aucun signe négatif, alors nous multiplions simplement par 1
p>Pour cela, nous allons utiliser la fonction SI() qui permet d’effectuer une action lorsqu’une condition est remplie, et une tout autre action dans le cas contraire.
La condition est évidemment alors de rechercher si ou non la cellule contient le symbole « - ».
Et pour cela, nous utilisons la fonction TROUVE() :
Cette fonction permet de retourner la position à laquelle se trouve le texte_cherché (ici le symbole moins) au sein du texte (ici la cellule de la colonne précédente).
En revanche, lorqu’Excel n’identifie pas le texte_cherché dans la chaîne texte, celui-ci retourne alors une erreur.
Pas de problème, il nous suffit alors d’exploiter cette erreur avec la fonction ESTERREUR() pour déterminer la non existence du symbole moins :
=[@[Cumul Départ]]+SUBSTITUE([@[Récupération Mars]];"-";"")*SI(ESTERREUR(TROUVE("-";[@[Récupération Mars]]));1;-1)
Et voilà !