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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

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 « : ») :

Excel formation - heures négatives - 01

À 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):

Excel formation - heures négatives - 02

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 :

Excel formation - heures négatives - 03

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 à « =- ») :

Excel formation - heures négatives - 04

Lorsque nous saisissons :

 -G8+G9 

Excel insère automatiquement le symbole égal (« = ») devant :

 =-G8+G9 

Aussi lorsque nous saisissons la formule « -1 :15 » :

Excel formation - heures négatives - 05

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

Excel formation - heures négatives - 06

Evidemment, à ce moment là les saisies déjà présentes vont également être modifiées !

Il suffit alors de les ressaisir :

Excel formation - heures négatives - 07

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 :

Excel formation - heures négatives - 08

Ensuite, pour harmoniser la mise en forme, nous pouvons aligner toutes les cellules sur la droite :

Excel formation - heures négatives - 09

 

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 :

Excel formation - heures négatives - 10

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]];"-";"") 

 

Excel formation - heures négatives - 11

L’histoire d’y voir un peu plus claire dans nos données, appliquons maintenant un format Heure aux cellules de la colonne :

Excel formation - heures négatives - 12

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() :

Excel formation - heures négatives - 13

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) 

 

Excel formation - heures négatives - 14

Et voilà !

 

 



Articles qui pourraient vous intéresser

Comment remplacer automatiquement des données (mots, lettres, nombres,…) sur Excel

MISE EN FORME CONDITIONNELLE SUR UN GRAPHIQUE EXCEL

COMMENT DUPLIQUER 500 FOIS (OU PLUS !) UNE FEUILLE DE CALCUL EXCEL EN 1 CLIC

COMMENT CONVERTIR DES COORDONNÉES GPS DEGRÉS MINUTES SECONDES (DMS) EN DEGRÉS DÉCIMAUX (DD) EXCEL

Commentaires

Il n'y a aucun commentaire...

Poster un commentaire

Pseudo
Commentaire

Contact - Plan du site - À propos - Contact

© Excel-Formation 2018 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.