Regrouper en fonction de l'heure
Bonjour,
Cela fait maintenant plusieurs jours que je bloque sur ce problème:
Je dispose d'une colonne avec certaines heures, d'une autre colonne avec un montant et une autre avec la somme cumulée des montant.
Je souhaite trier pour chaque heure "précise" (de 9h00 à 15h00) associer la somme cumulée. Et lorsqu'il y a plusieurs montant pour une même heure, je souhaite prendre en compte uniquement la dernière somme cumulée.
exemple:
9:42 10
9:43 12
9:45 17
9:45 19
etc...
que je souhaite trier de la sorte sur 2 colonnes à part:
9:00 0
9:01 0
9:02 0
etc...
9:42 10
9:43 12
9:44 12
9:45 19
etc...
Voici un exemple en PJ. Il y a en gros 2 colonnes de gauches qui sont les datas et les 2 colonnes de droites qui représentent ce que je souhaite "automatisé" afin d'en faire un graphique avec en abscisse les heures (de 9h00 à 15h00) et en ordonnée la somme cumulée.
Merci de votre aide
Bonjour
Merci de préciser dans ton profil ta version Excel dont la langue importe assez peu
Problème : tu as des lignes sans horaire : qu'en fait-on ?
J'ai Excel 2016.
Par ligne sans horaire, vous entendez ligne vide ou simplement ligne sans une heure précise (par exemple pas de ligne avec 9:44) ?
Bonjour à tous,
Ton problème nécessite toute une préparation pour conditionner tes colonnes avec tes heures.
La colonne A, ce ne sont pas des heures, mais des dates avec des heures, alors que la colonne I, ce sont des heures. Donc on ne peut pas comparer.
Ensuite, tu mets des cellules fusionnées. Ce qui est une hérésie dans une base de données.
PS :je regarderai comment Chris fait en PQ
Je vous remercie pour votre aide ! C'est quasiment ce que je cherchais, en effet lorsque j'extrais les données il y a la date (inutile) qui est dans les données. Votre astuce avec l'arrondi est très bien vu, cependant comme puis-je faire pour que lorsque il n'y a pas de montant à une certaine heure, il s'affiche le montant de l'heure précédente.
Par exemple dans le fichier que vous m'avez envoyer à 9:43:00 il y a un montant 0, ce qui est vrai sauf que je souhaite avoir le montant de 9:42:00 à savoir 10 afin qu'il n'y ai pas de "saut" pour ma courbe.
Idem de 9:47:00 à 9:52:00 où à la place de 0 je souhaiterais avoir 16.
etc...
En espérant être clair.
Encore merci pour votre aide.
Salut Toto,
Salut les as,
en VBA, en double-cliquant sur la feuille pour démarrer la macro, affichage en [H:I], sur base des commentaires précédents, la colonne [H:H] étant déjà formatée en Heures.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'
Cancel = True
'
Range("A1:C" & Range("A" & Rows.Count).End(xlUp).Row).Sort key1:=[A2], order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
tTab = Range("A2:C" & Range("A" & Rows.Count).End(xlUp).Row).Value
Range("I2:I362").Value = 0
tOut = Range("H2:I362").Value
'
For x = 1 To UBound(tOut, 1)
If x > 1 Then tOut(x, 2) = tOut(x - 1, 2)
For y = 1 To UBound(tTab, 1)
If Format(TimeValue(CDate(tTab(y, 1))), "hh:mm") = Format(TimeValue(CDate(tOut(x, 1))), "hh:mm") Then tOut(x, 2) = tTab(y, 3)
Next
Next
Range("H2").Resize(UBound(tOut, 1), 2).Value = tOut
'
End Sub
A+
EDIT
Avec le fichier, c'est mieux!
Autre précision : je ne construis pas le tableau des heures : je profite de son existence en colonne [H:H] !!
C'est exactement ce que je cherchais curulis57! Merci !
Désolé du temps de réponse 78chris, en ce qui concerne les ligne sans heure (double ligne) c'est la même heure de la ligne du dessus (exemple heure en A31= heure en A30)
Si cela est possible de résoudre ce problème sans vba je suis preneur !
Avec la colonne qui va bien
C'est exactement ça ! Merci à vous !
Petite question qui va sembler bête, pourquoi y' a t-il "_xlfn" avant la fonction ? et pourquoi lorsque j'essaie d'exécuter cette même fonction pour d'autre feuille il y a l'erreur #NOM? qui apparaît (peut être pas la même version d'excel) ?
Bonjour
PS :je regarderai comment Chris fait en PQ
La voilà : PQ + TCD
Tout d'abord merci ! Pouvez-vous rapidement expliquer comment avez vous fait afin que je puisse refaire ce travail moi même (que signifie PQ par ex) je suis novice sur excel.
Merci d'avance
Bonjour
PowerQuery, requêteur intégré à Excel
De mémoire dans 2016 pour l'ouvrir : Données, connexions et requêtes pour ouvrir le volet, clic droit sur une requête, Modifier
Il y a 2 requêtes :
- Données qui récupère le tableau source, complète les horaires vides, récupère l'heure sans la date puis l'heure sans les secondes, somme par heure et récupère l'heure seule dans une colonne.
- Horaires qui établit la liste de toutes les heure-minutes comprises entre le début de la première heure et la fin de la dernière heure de Données, croise avec Données pour associer aux Heure-Minutes communes le montant puis trie
On peut voir les étapes de chaque requête dans la partie droite de l'écran POwerQuery
Un TCD récupère Horaires, effectue le cumul des montants, complété par un GCD
Petite question qui va sembler bête, pourquoi y' a t-il "_xlfn" avant la fonction ? et pourquoi lorsque j'essaie d'exécuter cette même fonction pour d'autre feuille il y a l'erreur #NOM? qui apparaît (peut être pas la même version d'excel) ?
c'est a cause de la version d'excel
tu remplaces cette formule par
{=MAX(SI($B$2:$B$100=$J2;$D$2:$D$100))}
C'est une formule matricielle, donc à valider par
Maj+Ctrl+Entrée (les 3 doigts en même temps) pour faire apparaître les accolades.
Elle doit s'afficher automatiquement entre accolades dans la barre de formule, et être re-validée de la même façon à chaque fois qu'on y touche.
en PQ, ce qui m'intéressait, c'est de voir comment on arrive à s'affranchir de ces heures "canada dry".
Il faut l'avoir fait, et s'en souvenir…
Je vous remercie pour vos réponses et votre temps ! Cela fonctionne parfaitement !