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

Mak_tarmak

XLDnaute Junior
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
 

Pièces jointes

  • TEST_CUMUL_SEGMENTS.xlsm
    644.8 KB · Affichages: 7
Solution
Re-,
Dans ton fichier d'origine, il faut que tu nommes les deux Tableaux Structurés :
- T_Elt pour le tableau de l'onglet "ELT_Sessions"
- T_List pour le tableau de l'onglet "liste_sessions"

Dans mon fichier :

Dans le ruban "Données", tu cliques sur "Requêtes et connexion", une fenêtre va s'ouvrir sur la droite
1687524373739.png

Tu cliques sur le 1er (T_Elt), puis en maintenant la touche Contrôle appuyée, tu cliques sur le 2ème (T_List)
Clic droit, "Copier"

Tu viens dans ton fichier, "Données/Requêtes et connexions", clic droit dans la zone grise à droite (et normalement vide), "Coller"
Voilà, tu as les requêtes...
Tu cliques sur "Données/Actualiser tout"
Si la requête "T_Elt" ne se charge pas, clic droit dessus, "Charger...

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonjour Mak_tarmak,
Un essai en PJ en profitant au max de la puissance d'XL et des tableaux structurés, avec :
VB:
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.
 

Pièces jointes

  • TEST_CUMUL_SEGMENTS.xlsm
    670.5 KB · Affichages: 3
Dernière édition:

Cousinhub

XLDnaute Barbatruc
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
 

Pièces jointes

  • PQ_CUMUL_SEGMENTS.xlsm
    641.5 KB · Affichages: 3

Cousinhub

XLDnaute Barbatruc
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

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

Pièces jointes

  • Copie de MAK_TARMAK - TEST_CUMUL_SEGMENTS.zip
    881.6 KB · Affichages: 3

Mak_tarmak

XLDnaute Junior
Bonjour Mak_tarmak,
Un essai en PJ en profitant au max de la puissance d'XL et des tableaux structurés, avec :
VB:
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

XLDnaute Barbatruc
Supporter XLD
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

XLDnaute Junior
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

XLDnaute Junior
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.
 

Mak_tarmak

XLDnaute Junior
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.
 

Mak_tarmak

XLDnaute Junior
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.
 

Discussions similaires

Réponses
4
Affichages
126

Statistiques des forums

Discussions
312 886
Messages
2 093 272
Membres
105 666
dernier inscrit
MAGOUROMY