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

Mak_tarmak · 23 Juin 2023

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

Cousinhub · 23 Juin 2023

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

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 · 23 Juin 2023

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.

Cousinhub · 23 Juin 2023

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 · 23 Juin 2023

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 · 23 Juin 2023

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

Mak_tarmak · 23 Juin 2023

sylvanu à dit:

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 · 23 Juin 2023

Mak_tarmak à dit:
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 · 23 Juin 2023

Cousinhub à dit:
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 · 23 Juin 2023

Lolote83 à dit:
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 · 23 Juin 2023

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

Mak_tarmak · 23 Juin 2023

Cousinhub à dit:
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 · 23 Juin 2023

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 · 23 Juin 2023

sylvanu à dit:
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 · 23 Juin 2023

Lolote83 à dit:
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 · 23 Juin 2023

sylvanu à dit:
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

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

Mak_tarmak

XLDnaute Junior

Pièces jointes

Cousinhub

sylvanu

XLDnaute Barbatruc

Pièces jointes

Cousinhub

XLDnaute Barbatruc

Pièces jointes

Cousinhub

XLDnaute Barbatruc

Lolote83

XLDnaute Barbatruc

Pièces jointes

Mak_tarmak

XLDnaute Junior

sylvanu

XLDnaute Barbatruc

Mak_tarmak

XLDnaute Junior

Mak_tarmak

XLDnaute Junior

Cousinhub

XLDnaute Barbatruc

Pièces jointes

Mak_tarmak

XLDnaute Junior

Lolote83

XLDnaute Barbatruc

Mak_tarmak

XLDnaute Junior

Cousinhub

XLDnaute Barbatruc

Mak_tarmak

XLDnaute Junior

Discussions similaires