Je suis face à deux méthodes différentes pour regrouper et sommer la durée en utilisant l’interrogation Google Sheets. La première méthode est simple mais nécessite un formatage manuel. Si vous utilisez la fonction d’agrégation SUM de Query pour additionner une colonne de durée, la formule renverra une erreur #VALEUR !. Voici un exemple :
FORMULE #1
=query(A1:E,"Select Sum(E)",1)
Le texte d’information (veuillez vous référer à la capture d’écran) montre la raison de l’erreur, c’est-à-dire « AVG_SUM_ONLY_NUMERIC ». Donc, évidemment, vous obtiendrez la même erreur lors du regroupement.
Avant de passer au regroupement et à la somme de la durée en utilisant Query, vous devez apprendre à résoudre l’erreur mentionnée ci-dessus. Pour additionner la durée en utilisant Query dans Google Sheets, que ce soit avec ou sans regroupement, vous devez d’abord convertir les valeurs de la colonne de durée en valeurs numériques.
Il existe deux façons de le faire dans Query. Lisez la suite pour obtenir ces deux formules.
Addition de la durée en utilisant Query – Options de formule
Formule de Query + Formatage manuel
C’est la méthode la plus simple. Si vous n’avez pas de problème pour formater la colonne de durée (colonne E) en nombre, vous pouvez suivre cette méthode.
Tout d’abord, sélectionnez la durée dans E2:E8 ou dans la plage E2:E. Ensuite, allez dans le menu Format et appliquez Nombre > Nombre.
La même formule, que j’ai partagée ci-dessus, commencera alors à fonctionner correctement. Mais la sortie sera en nombre. Cela aussi, nous pouvons le résoudre.
J’ai la formule de Query ci-dessus (formule #1) dans la cellule G2 et je vois la sortie au format nombre.
Sélectionnez simplement la cellule G3 et appliquez Format > Nombre > Durée.
C’est tout ce que vous devez faire pour additionner la durée en utilisant Query dans Google Sheets.
Formule de Query pour additionner la durée (pas de formatage manuel)
Pour ceux qui ne veulent pas formater manuellement la colonne de durée, voici la solution.
Étape 1 :
D’abord, nous devons convertir la durée en utilisant une formule. Pour convertir la durée en valeurs ou en nombres temporels, vous devez convertir les heures, les minutes et les secondes en nombres. Comment ? Cette formule générique l’explique.
Heure/24 + Minute/1440 + Seconde/86400Comment appliquer cette formule générique qui convertit la durée en nombre dans Google Sheets ? Veuillez trouver ci-dessous la formule correspondante.
Nous voulons convertir la durée dans un tableau, c’est-à-dire E2:E8. Nous devons donc envelopper la formule dans Sheets avec la fonction ArrayFormula.
=ArrayFormula(heure(E2:E8)/24+minute(E2:E8)/1440+seconde(E2:E8)/86400)Mais je ne vais pas l’utiliser dans un tableau limité comme E2:E8. Au lieu de cela, je souhaite l’utiliser dans une plage entière E2:E. La formule pour convertir la durée en valeurs temporelles (nombres) sera donc la suivante.
={"Durée";ArrayFormula(if(len(E2:E),(hour(E2:E)/24+minute(E2:E)/1440+second(E2:E)/86400),))}Dans cette formule, LEN gère les lignes vides et j’ai ajouté en plus l’en-tête de colonne « Durée » dans la première ligne.
Étape 2 :
Maintenant, au lieu de la colonne E, vous pouvez utiliser cette colonne virtuelle dans votre Query. Je veux dire, au lieu de A1:E, utilisez cette plage virtuelle.
Formule générique :
{A1:D, la colonne virtuelle E}Voici cette nouvelle (virtuelle) plage.
={A1:D,{"Durée";ArrayFormula(if(len(E2:E),(hour(E2:E)/24+minute(E2:E)/1440+second(E2:E)/86400),))}}Voici la formule de Query pour additionner la durée.
FORMULE #2 :
=Query({A1:D,{"Durée";ArrayFormula(if(len(E2:E),(hour(E2:E)/24+minute(E2:E)/1440+second(E2:E)/86400),))}},"Select Sum(Col5)",1)Notez que, lors de l’utilisation de données virtuelles dans Query, l’identificateur de colonne doit être le numéro de colonne, et non la lettre de colonne. J’ai donc remplacé Sum(E) par Sum(Col5).
La formule ci-dessus renvoie un nombre, pas une durée. Vous devez donc formater la colonne de résultat en durée en cliquant sur Format > Nombre > Durée.
Cette méthode, je l’ai expliquée dans l’un de mes tutoriels avancés sur Query – Query pour calculer les heures travaillées par semaine dans Google Sheets.
Comment regrouper et additionner la durée en utilisant Query dans Sheets
Cette partie est simple. Il s’agit de savoir comment utiliser la clause group by dans Query avec les formules ci-dessus.
Pour regrouper et additionner la durée, nous pouvons utiliser soit la formule #1, soit la formule #2 ci-dessus. Bien sûr, vous devez inclure la clause group by dans ces formules.
Voici ces deux formules pour regrouper et additionner la durée en utilisant Query.
Formule de Query avec formatage manuel
Dans cet exemple, je veux calculer la durée totale passée sur les événements « Planification » et « HSE ».
Cela signifie que la colonne B est la colonne de regroupement dans Query et la colonne E est la colonne de somme.
Tout d’abord, formatez la colonne E (la plage E2:E) en nombre.
Voici la formule qui regroupe la colonne « Événement » et additionne la « Durée ».
=query(A1:E,"Select B, Sum(E) where A is not null Group By B",1)Dans la capture d’écran suivante, j’ai souligné ces nouveaux changements par rapport à la formule #1. La plage E2:E et la plage de résultat H3:H4 ont été formatées manuellement en durée.
Formule de Query sans formatage manuel
Ici, nous pouvons utiliser sans aucun doute la formule #2. Nous devons modifier la formule 2 pour inclure le regroupement.
Veuillez vous référer à la capture d’écran ci-dessous pour comprendre les modifications que j’ai apportées.
=Query({A1:D,{"Durée";ArrayFormula(if(len(E2:E),(hour(E2:E)/24+minute(E2:E)/1440+second(E2:E)/86400),))}},"Select Col2,Sum(Col5) where Col1 is not null group by Col2",1)Ici aussi, vous devez formater la plage H3:H4 en durée.
Note : Sur l’image ci-dessus, veuillez ignorer la clause de format dans la formule. Ce n’est pas nécessaire. Elle a déjà été supprimée de ma formule ci-dessus.
MISE À JOUR IMPORTANTE
Dans toutes les formules Query ci-dessus pour l’agrégation de valeurs de durée, le cas échéant, la plage virtuelle {« Durée »;ArrayFormula(if(len(E2:E),(hour(E2:E)/24+minute(E2:E)/1440+second(E2:E)/86400),))} peut être remplacée par l’une des deux formules suivantes.
Alternative 1:
{"Durée";ArrayFormula(if(len(E2:E),heure(E2:E),))}Alternative 2:
{"Durée";ArrayFormula(if(len(E2:E),valeur(E2:E),))}Ressources supplémentaires :
- Comment utiliser DateTime dans Query dans Google Sheets.
- Google Sheets : La meilleure formule de calcul des heures supplémentaires.
- Comment filtrer les horodatages dans Query dans Google Sheets.
- Comment extraire la date d’un horodatage dans Google Sheets.
- Filtrez les colonnes vides dans Google Sheets en utilisant la formule Query.
