Comment ajouter un horodatage aux cases à cocher dans Excel
L'outil de case à cocher d'Excel vous aide à gérer vos chiffres, à suivre la progression des tâches et à afficher les données plus clairement. L'un des joyaux les moins connus d'Excel est sa capacité à produire un horodatage lorsqu'une case est cochée. Voici comment y parvenir.
Sommaire
Étape 1 : formatez votre tableau
Dans mon cas, j'ai une liste de personnes qui doivent terminer une tâche et je vais utiliser des cases à cocher horodatées pour savoir quand elles ont commencé la tâche, quand elles l'ont terminée et combien de temps il leur a fallu pour la terminer.
Après avoir nommé vos colonnes dans la ligne 1, l'étape suivante consiste à formater votre tableau afin qu'il soit un peu plus agréable à regarder et que tous les calculs soient automatiquement dupliqués. Pour ce faire, sélectionnez n'importe quelle cellule contenant des données, cliquez sur « Formater en tableau » dans l'onglet Accueil et choisissez une conception qui vous convient.
Puisque vous avez déjà nommé vos colonnes, cochez « Ma table a des en-têtes » lorsque la boîte de dialogue Créer une table apparaît, puis cliquez sur « OK ».
Le tableau est maintenant prêt pour que vous puissiez passer à l'étape suivante.
Étape 2 : définissez votre type de données temporelles
Avant d'ajouter des calculs ou des cases à cocher, vous devez indiquer à Excel que certaines colonnes (dans mon cas, les colonnes D, F et G) afficheront une heure en modifiant le type de données de ces cellules.
Si vous n'avez qu'une seule colonne nécessitant une sortie temporelle, sélectionnez simplement toutes les cellules de données de cette colonne dans votre tableau. Si vous avez plusieurs colonnes qui afficheront une heure, maintenez la touche Ctrl enfoncée pendant que vous les sélectionnez individuellement.
Si votre tableau contient des centaines de lignes, la sélection manuelle des cellules peut prendre beaucoup de temps ! Dans ce cas, sélectionnez la première cellule de données dans une colonne appropriée et appuyez sur Ctrl+Shift+Down. Ensuite, maintenez Ctrl enfoncé, sélectionnez la première cellule de données dans la colonne pertinente suivante, puis appuyez sur Ctrl+Shift+Down. Répétez ce processus jusqu'à ce que les cellules de toutes les colonnes pertinentes soient sélectionnées.
Maintenant, dans le groupe Nombre de l'onglet Accueil du ruban, cliquez sur le menu déroulant « Format du nombre » et sélectionnez « Heure ».
Par défaut, Excel affiche l'heure au format HH:MM:SS. Pour modifier ce format d'heure, cliquez à nouveau sur le menu déroulant « Format numérique » (avec les colonnes pertinentes toujours sélectionnées), cliquez sur « Plus de formats numériques » et choisissez un format d'heure différent parmi les options affichées. Alternativement, pour afficher l'heure et la date ensemble, cliquez sur « Personnalisé » dans la boîte de dialogue Format de cellule et tapez jj/mm/aaaa hh:mm:ss dans la boîte en haut.
Étape 3 : ajoutez vos cases à cocher
Il est maintenant temps d'ajouter des cases à cocher aux colonnes concernées. Sélectionnez la première cellule d'une colonne de cases à cocher dans votre tableau, puis cliquez sur « Case à cocher » dans l'onglet Insertion du ruban. Si vous ne voyez pas cette option, tapez plutôt Case à cocher dans la barre de recherche en haut de votre fenêtre, puis cliquez sur « Case à cocher ».
Ensuite, sélectionnez la cellule contenant la première case à cocher et double-cliquez sur la poignée de remplissage dans le coin inférieur droit de cette cellule pour remplir les cellules restantes de cette colonne.
Si vous avez plusieurs colonnes qui nécessitent également des cases à cocher, répétez le processus ci-dessus ou copiez et collez la colonne de cases à cocher que vous avez déjà créée.
Étape 4 : Activer les calculs itératifs
Vous êtes maintenant prêt à préparer votre feuille de calcul pour le calcul d'horodatage que vous allez ajouter à l'étape suivante.
Ce calcul sera ce qu'on appelle une formule itérative, ce qui signifie qu'il se réfère à lui-même dans la formule (également appelée référence circulaire). Si vous utilisez une formule plus simple, l'horodatage sera continuellement mis à jour à l'heure actuelle car la formule contient une fonction volatile (MAINTENANT). Et si vous n'activez pas les calculs itératifs lorsque vous utilisez la méthode décrite dans ce guide, Excel renverra une erreur.
Appuyez sur Alt > F > T pour lancer la boîte de dialogue Options Excel et cochez « Activer le calcul itératif » dans le menu Formules.
Lorsque vous cliquez sur « OK », vous ne verrez aucune modification visible dans votre feuille de calcul, mais sous la surface, elle est prête pour l'étape suivante.
Bien qu'utiles dans ce scénario, il est important de connaître certains des inconvénients des calculs itératifs. Par exemple, comme ils recalculent en boucle, ils peuvent affecter les performances globales de votre feuille de calcul. De plus, l'activation de cette fonctionnalité signifie qu'Excel n'identifiera aucune référence circulaire mal utilisée, alors gardez un œil sur cela. Enfin, si vous partagez le classeur avec des personnes utilisant une ancienne version d'Excel, les horodatages risquent de ne pas fonctionner de la même manière que sur votre version.
Étape 5 : Appliquer la formule magique
La dernière étape consiste à créer la formule pour générer un horodatage lorsqu'une case est cochée. Je vais commencer par la colonne D, qui produira un horodatage lorsque je cocherai une case dans la colonne C.
Voici la formule que j'utiliserai dans la cellule D2 :
=IF((@Started),IF((@(Start Time))="",NOW(),(@(Start Time))),"")
Bien que cela semble compliqué, une fois décomposé, cela a plus de sens.
La première fonction IF évalue si la case correspondante dans la colonne C (la colonne Démarré) est cochée :
=IF((@Started),
Excel passe ensuite à la deuxième fonction IF, qui évalue si la cellule actuelle (D2 dans la colonne Heure de début) est vide :
=IF((@Started),IF((@(Start Time))="",
Si c'est est vide, vous souhaitez que l'heure actuelle soit insérée :
=IF((@Started),IF((@(Start Time))="",NOW(),
Si c'est n'est pas vide, vous souhaitez qu'Excel conserve la cellule telle quelle :
=IF((@Started),IF((@(Start Time))="",NOW(),(@(Start Time))),
L'argument final garantira que si la case correspondante dans la colonne C n'est pas cochée, la valeur dans la colonne D sera vide :
=IF((@Started),IF((@(Start Time))="",NOW(),(@(Start Time))),"")
Lorsque vous appuyez sur Entrée, la formule sera appliquée aux cellules restantes de cette colonne.
Maintenant, copiez et collez cette formule dans la première cellule de toute autre colonne nécessitant des horodatages basés sur des cases à cocher, et ajustez les références de la formule afin qu'elle capture les colonnes correctes.
Dans mon cas, après avoir collé la formule dans la cellule F2, j'ai changé le mot « Commencé » (faisant référence à la colonne C) en « Terminé » (faisant référence à la colonne E) et « Heure de début » (faisant référence à la colonne D) en « Heure de fin ». » (faisant référence à la colonne F) :
=IF((@Completed),IF((@(End Time))="",NOW(),(@(End Time))),"")
Lorsque vous faites cela pour la première cellule, cela s'appliquera automatiquement aux autres cellules de cette colonne après avoir appuyé sur Entrée.
Enfin, pour utiliser ces données pour calculer un temps d'exécution total, j'intégrerai un simple calcul SOMME dans un calcul SIERREUR dans la cellule G2. Cela signifie que si la SOMME ne fonctionne pas parce que les cases ne sont pas cochées, la cellule correspondante dans la colonne G (Total des heures) restera vide :
=IFERROR(SUM((@(End Time))-(@(Start Time))),"")
Encore une fois, une fois que j'appuie sur Entrée, la formule sera appliquée à toutes les lignes de cette colonne.
Étape 6 : Essayez-le !
Avant d'utiliser vos cases à cocher et vos horodatages dans le monde réel, cochez certaines cases pour vous assurer que tout fonctionne comme prévu.
L'ajout de règles de mise en forme conditionnelle à vos cases à cocher peut faciliter la lecture de vos données. Par exemple, vous pouvez faire en sorte que les cellules contenant des cases à cocher deviennent vertes lorsqu'elles sont cochées, ou vous pouvez aller plus loin et formater la ligne entière.
