XL 2016 VBA - Traitement de données en fonction de plusieurs critères avec Dictionary

[Mak_tarmak]

Bonjour,
Je sollicite votre aide pour utiliser la fonction Dictionary dans une macro ou une autre méthode plus judicieuse mais toujours en macro car j'ai beaucoup de données à traiter qui évoluent sans cesse.
Je manipule très mal les imbrications de données qui dépendent de plusieurs critères dans des onglets différents mais je souhaite apprendre.

Voici ma problématique :
Dans l'onglet ELT_SESSIONS, en colonne B j'ai mes codes sessions.
Dans l'onglet liste_sessions, ceux-ci se trouvent en colonne A.

Pour chaque code session dans B, je recherche l'équivalence dans A.
Si elle est trouvée, je regarde dans la colonne G de liste_sessions.
Je prends chaque valeur unique de cette colonne pour le code session concerné et pour chaque valeur unique je soustrais les date/heure de J et I pour obtenir une durée en heures avec une décimale. Ensuite, je fais la somme des durées de chaque segment unique et je la reporte dans la colonne L de ELT_SESSIONS pour chaque session.

Pour exemple, je prends le code session 6086.
Il se trouve dans la colonne B de ELT_SESSIONS mais aussi dans la colonne A de liste_sessions.
Pour ce code session, je regarde dans G et je vois qu'il y a 15 valeurs mais seulement 3 segments uniques : le 1, 2 et 2851 (il peut en avoir 10 ou plus différents)

Pour la première valeur unique 1, je soustrais 08/03/2023 17:30 Europe/Paris en J à 08/03/2023 14:00 Europe/Paris en I, cela donne 3,5 heures (les dates peuvent être converties au besoin mais je dois obtenir le résultat en heures)
Pour la deuxième valeur unique 2, je soustrais 09/03/2023 12:30 Europe/Paris en J à 09/03/2023 09:00 Europe/Paris en I, cela donne 3,5 heures
Pour la dernière valeur unique 2851, je soustrais 09/03/2023 18:00 Europe/Paris en J à 09/03/2023 13:30 Europe/Paris en I, cela donne 4,5 heures

Comme il y a 3 valeurs différentes de segments, je fais la somme des trois, soit 11,5 heures et je reportes unique 11,5 dans la colonne L de ELT_SESSIONS pour le code session concerné.

Je vous joints un fichier modèle qui sera plus parlant.

Merci d'avance,
Kad

 

[sylvanu]

Bonjour Mak_tarmak,
Un essai en PJ en profitant au max de la puissance d'XL et des tableaux structurés, avec :

Sub Calcul()
With Sheets("liste_sessions")
' On rajoute trois colonne on transforme les "fausses dates" en vraies date numérique, et on fait fin - début
    .[K1] = "Début": .[L1] = "Fin": .[M1] = "Diff"
    .[K2].FormulaR1C1 = "=IFERROR(VALUE(LEFT(Tableau15[[#This Row],[Date/heure de début]],16)),"""")"
    .[L2].FormulaR1C1 = "=IFERROR(VALUE(LEFT(Tableau15[[#This Row],[Date/heure de fin]],16)),"""")"
    .[M2].FormulaR1C1 = "=IFERROR(Tableau15[[#This Row],[Fin]]-Tableau15[[#This Row],[Début]],0)"
' On calcule le temps par session et on colle valeurs
    Dim DL%: DL = [A65500].End(xlUp).Row
    Range("L2:L" & DL).FormulaR1C1 = "=24*SUMIF(liste_sessions!C[-11],ELT_SESSIONS!RC[-10],liste_sessions!C[1])"
    Range("L2:L" & DL) = Range("L2:L" & DL).Value
' On supprime les 3 colonnes ajoutées dans liste_sessions
    .Columns("K:M").Delete Shift:=xlToLeft
End With
End Sub

J'ai "caché" le bouton en L1 ( bouton sur cellule avec même couleur)

[Edit] J'ai supposé que vos datations seront toujours du type "04/01/2023 14:00 Europe/Paris" sinon il faut reprendre les formules d'extraction datation.

 

[Cousinhub]

Bonjour,
Voici une proposition en utilisant Power Query (donc pas de macro du tout...)
Clic droit dans une cellule de la requête en colonne S (peux pas la mettre en colonne L, sinon ça empiète sur le Tableau Structuré...), je disais donc clic droit "Actualiser" pour mettre à jour.
Bonne apm
Oups, un peu à la bourre... Bjr Sylvanu

 

[Cousinhub]

Re-,
Sylvanu, je pense que tu effectues les calculs sur tous les segments, sans tenir compte qu'il y a des doublons.
Par exemple, première ligne (session 5620), il y a 6 fois la même ligne, avec le même segment (segment 1)
Pour moi, j'ai compris qu'il ne fallait en compter que les segments uniques (donc 1 seule fois, soit 03heures)
Mais c'est ma compréhension, et je peux tout à fait m'avoir gourré, tout pareil...
Bonne apm

 

[Lolote83]

Bonjour à tous
Sans macro avec quelques colonnes supplémentaires.
Pas encore regardé vos propositions
Fichier Zippé car trop volumineux.
@+ Lolote83

 

[Mak_tarmak]

Bonjour Mak_tarmak,
Un essai en PJ en profitant au max de la puissance d'XL et des tableaux structurés, avec :

Sub Calcul()
With Sheets("liste_sessions")
' On rajoute trois colonne on transforme les "fausses dates" en vraies date numérique, et on fait fin - début
    .[K1] = "Début": .[L1] = "Fin": .[M1] = "Diff"
    .[K2].FormulaR1C1 = "=IFERROR(VALUE(LEFT(Tableau15[[#This Row],[Date/heure de début]],16)),"""")"
    .[L2].FormulaR1C1 = "=IFERROR(VALUE(LEFT(Tableau15[[#This Row],[Date/heure de fin]],16)),"""")"
    .[M2].FormulaR1C1 = "=IFERROR(Tableau15[[#This Row],[Fin]]-Tableau15[[#This Row],[Début]],0)"
' On calcule le temps par session et on colle valeurs
    Dim DL%: DL = [A65500].End(xlUp).Row
    Range("L2:L" & DL).FormulaR1C1 = "=24*SUMIF(liste_sessions!C[-11],ELT_SESSIONS!RC[-10],liste_sessions!C[1])"
    Range("L2:L" & DL) = Range("L2:L" & DL).Value
' On supprime les 3 colonnes ajoutées dans liste_sessions
    .Columns("K:M").Delete Shift:=xlToLeft
End With
End Sub

J'ai "caché" le bouton en L1 ( bouton sur cellule avec même couleur)

[Edit] J'ai supposé que vos datations seront toujours du type "04/01/2023 14:00 Europe/Paris" sinon il faut reprendre les formules d'extraction datation.

Bonjour sylvanu,
Merci pour votre retour.
Vous avez bien supposé, les datations seront toujours de ce type car elles sont issues d'un rapport généré.
Par contre, votre macro ne répond pas totalement au besoin. Vous faites la somme de tous les segments pour une session donnée.
Les valeurs dans les segments (en G) sont soit 1, soit il peut y avoir plusieurs valeurs différentes dans les segments et il peut y avoir plusieurs fois la même valeur dans les segments.
Je ne garde qu'une seule ligne des valeurs différentes de segment pour faire le Diff et je fais la somme des Diff pour chaque segment différent.

 

[sylvanu]

Je ne garde qu'une seule ligne des valeurs différentes de segment pour faire le Diff et je fais la somme des Diff pour chaque segment différent.

Compris, mais dans la feuille "ELT_SESSIONS" il n'y a aucune référence au N° de segment, alors comment fait on ?
On devrait trouver dans cette feuille 5452 Segment 1 et sur la ligne du dessous 5452 segment 2 puis 5452 segment 3 ?

 

[Mak_tarmak]

Bonjour,
Voici une proposition en utilisant Power Query (donc pas de macro du tout...)
Clic droit dans une cellule de la requête en colonne S (peux pas la mettre en colonne L, sinon ça empiète sur le Tableau Structuré...), je disais donc clic droit "Actualiser" pour mettre à jour.
Bonne apm
Oups, un peu à la bourre... Bjr Sylvanu

Bonjour Cousinhub,
Merci pour votre retour.
Je ne connais Power Query que de nom.
Je ne sais même pas où voir ce que vous avez fait comme requête
Est-il possible dans une requête Power Query d'obtenir le résultat en ,5 par exemple 10:30 en 10,5 ?

 

[Mak_tarmak]

Bonjour à tous
Sans macro avec quelques colonnes supplémentaires.
Pas encore regardé vos propositions
Fichier Zippé car trop volumineux.
@+ Lolote83

Bonjour Lolote83,
Merci pour retour.
Je n'ai pas choisi l'option formule car les données de cet onglet sont récupérées dans différents rapports de plusieurs milliers de ligne et c'est plus facile pour moi de lancer un groupe de macros.
Pour traiter ce dont j'ai besoin, ce sera plus facile pour moi de l'intégrer dans une suite de macros existantes.

 

[Cousinhub]

Re-,
Bien sûr...
La V2

 

[Mak_tarmak]

Re-,
Sylvanu, je pense que tu effectues les calculs sur tous les segments, sans tenir compte qu'il y a des doublons.
Par exemple, première ligne (session 5620), il y a 6 fois la même ligne, avec le même segment (segment 1)
Pour moi, j'ai compris qu'il ne fallait en compter que les segments uniques (donc 1 seule fois, soit 03heures)
Mais c'est ma compréhension, et je peux tout à fait m'avoir gourré, tout pareil...
Bonne apm

Bonjour Cousinhub, c'est tout à fait ça. Les segments n'ayant pas tous la même durée pour les différentes sessions je dois les évaluer à chaque fois pour chaque valeur de segment unique.

 

[Lolote83]

Re bonjour à tous.
@Mak_tarmak , merci pour ton retour. Je comprends tout à fait la problématique, mais à titre d'information, peux tu nous dire si les solutions proposées donnaient des réponses justes ?
Merci de ton retour
@+ Lolote83

 

[Mak_tarmak]

Compris, mais dans la feuille "ELT_SESSIONS" il n'y a aucune référence au N° de segment, alors comment fait on ?
On devrait trouver dans cette feuille 5452 Segment 1 et sur la ligne du dessous 5452 segment 2 puis 5452 segment 3 ?

Je passe par la session en B dans ELT_SESSIONS et qui se retrouve en A dans liste_sessions, c'est ma porte d'entrée pour vérifier les segments.

 

[Cousinhub]

Re bonjour à tous.
@Mak_tarmak , merci pour ton retour. Je comprends tout à fait la problématique, mais à titre d'information, peux tu nous dire si les solutions proposées donnaient des réponses justes ?
Merci de ton retour
@+ Lolote83

Lolote,
Comme nous obtenons les mêmes résultats, je pense qu'on n'est pas loin de la vérité
Bonne apm

 

[Mak_tarmak]

Compris, mais dans la feuille "ELT_SESSIONS" il n'y a aucune référence au N° de segment, alors comment fait on ?
On devrait trouver dans cette feuille 5452 Segment 1 et sur la ligne du dessous 5452 segment 2 puis 5452 segment 3 ?

Vous pouvez filtrer par code session dans le tableau de liste_sessions pour un code session pour voir ce que cela donne si vous voulez