Transformation de structure de table en VBA

Dhaulagiri

XLDnaute Nouveau
Bonjour,

Je souhaite automatiser un traitement. Je suis assez à l'aise avec la plupart des fonctions d'Excel avec les formules mais je n'ai presque jamais utilisé la programmation dans ce logiciel. J'ai déjà programmé un peu dans d'autres environnements.

Je sais que ce que je dois faire est possible mais je ne sais pas comment me lancer et souhaiterais obtenir votre aide pour pouvoir ensuite automatiser d'autres traitements du même genre, dès que j'aurai compris le principe dans Excel.

Une rapide description: je recois une table dans laquelle je dois récupérer des informations pour les intégrer dans une autre table de structure différente. Je vous joins un extrait en PJ (un onglet SOURCE et un onglet CIBLE)

Les données concernent des horaires d'autocars à différents arrêts.

Dans la source, chaque enregistrement décrit la desserte d'un arrêt par une ligne à une heure précise. Pour chaque arrêt, on a deux enregistrements: l'heure d'arrivée à l'arrêt et l'heure de départ de l'arrêt. En pratique, ce sont les mêmes mais ce ne sera peut-être pas toujours le cas. Deux exceptions: l'arrêt de départ de la ligne et l'arrêt terminus. Le premier possède uniquement une heure de départ et le second, une heure d'arrivée.

Les attributs sont les suivants:
NOM_REDUIT_ARRET: code alphanumérique unique de l'arrêt
HEURE: horaire à l'arrêt en secondes
TYPE: type d'horaire (D: heure de départ, A: heure d'arrivée)
COURSE: code de la course (dans le jargon du transport, une course correspond à une ligne d'autocar parcourue à un moment donné. Exemple: la ligne A qui part à 7h14 et qui arrive à 8h23 est une course différente de la ligne A qui part à 7h44 et arrive à 8h53)
IDAP: identifiant numérique unique de l'arrêt

Dans la cible, les attributs sont les suivants (la structure correspond au modèle Google Transit, pour ceux qui connaissent)
TRIP_ID: code de la course
ARRIVAL_TIME: heure d'arrivée à l'arrêt
DEPARTURE_TIME: heure de départ de l'arrêt
STOP_ID: identifiant de l'arrêt
STOP_SEQUENCE: numéro d'ordre de desserte de l'arrêt dans la course

Ce que je dois faire, pour chaque code arrêt unique (NOM_REDUIT_ARRET ou IDAP peu importe):
> copier la valeur de IDAP dans STOP_ID
> copier la valeur de COURSE dans TRIP_ID
> créer un numéro d'ordre dans STOP_SEQUENCE pour chaque arrêt. On commence par 1 et on incrémente tant que le TRIP_ID ne change pas. Dès qu'il change, on recommence à 1.
> tester la valeur de TYPE: si A, alors copier HEURE dans ARRIVAL_TIME. Si D, alors copier HEURE dans DEPARTURE_TIME. Pour l'instant, ce test ne sert à rien puisque les deux valeurs sont identiques mais, comme je le disais, cela pourrait changer.
> remplacer le format en secondes par un format HH:MM:SS (je sais le faire avec la formule TEMPS())

Je suis capable de faire certaines de ces opérations individuellement mais pour tout mettre en VBA, c'est une autre affaire.

A ceux qui ont bien voulu lire jusqu'au bout, je dis merci. Un autre merci à ceux qui auront fait l'effort de comprendre mon problème. Quant à ceux qui m'apporteront leur aide pour tout ou partie du problème (les petits ruisseaux font les grandes rivières), ils auront droit à 3 mercis. Non 4, soyons fous!

A bientôt,

Dhaulagiri
 

Pièces jointes

  • exempleForum.xls
    17.5 KB · Affichages: 48
  • exempleForum.xls
    17.5 KB · Affichages: 48
  • exempleForum.xls
    17.5 KB · Affichages: 49

Dhaulagiri

XLDnaute Nouveau
Re : Transformation de structure de table en VBA

Bon, j'avance au gré de mes trouvailles, en décomposant mon gros problème en petits problèmes. Je vous en fais part, histoire de remonter le sujet en même temps.

Cette première étape permet d'extraire la liste des arrêts uniques. Le problème, c'est que je veux cette liste non pas pour l'ensemble de la colonne mais en fonction des valeurs d'une autre colonne. Dit autrement: pour chaque valeur différente de la colonne B, donne-moi la liste des valeurs uniques de la colonne A. En ajoutant un test dans le code ci-dessous, ça devrait pouvoir le faire. Reste plus qu'à trouver la syntaxe...

Code:
Sub SansDoublons2003()
 Dim Cel As Range
 Dim MonDico As Object

 Set MonDico = CreateObject("Scripting.Dictionary")

  With Sheets("horaire.txt")
    For Each Cel In .Range("A2:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
       MonDico(Cel.Value) = Cel.Value
    Next Cel
  End With

 ActiveSheet.[L2].Resize(MonDico.Count, 1) = Application.Transpose(MonDico.keys)

 Set MonDico = Nothing

End Sub

Quelqu'un pourrait-il m'aider sur ce point particulier avant que je passe à un autre "petit problème"?
Merci
 

jpb388

XLDnaute Accro
Re : Transformation de structure de table en VBA

Bonjour à tous
essayes cette macro

Code:
Sub transfert()
Dim Sh_s As Worksheet, Sh_c As Worksheet
Set Sh_s = Sheets("Source")
Set Sh_c = Sheets("Cible")
Dim i As Long
For i = 2 To Sh_s.Range("a" & Rows.Count).End(xlUp).Row
    Sh_c.Cells(i, 4) = Sh_s.Cells(i, 5)
    Sh_c.Cells(i, 1) = Sh_s.Cells(i, 4)
    If Sh_c.Cells(i, 1) <> Sh_c.Cells(i - 1, 1) Then
        Sh_c.Cells(i, 5) = 1
    Else
        Sh_c.Cells(i, 5) = Sh_c.Cells(i, 5) + 1
    End If
    If Sh_s.Cells(i, 3) = "D" Then
        Sh_c.Cells(i, 3) = Format(Sh_s.Cells(i, 2), "00:00:00")
    Else
        Sh_c.Cells(i, 2) = Format(Sh_s.Cells(i, 2), "00:00:00")
    End If
Next
End Sub
 

Dhaulagiri

XLDnaute Nouveau
Re : Transformation de structure de table en VBA

Bonjour JPB,

Merci pour ton aide. Ta proposition me donne une bonne base de travail qu'il me faut maintenant "rationaliser". Par exemple, quand deux lignes ont non seulement le même TRIP_ID et le même STOP_ID, je dois les "fusionner" en une seule. Par exemple:

TRIP_IDARRIVAL_TIMEDEPARTURE_TIMESTOP_IDSTOP_SEQUENCE
006:35:0023951
006:36:0023951

Doit devenir:

TRIP_IDARRIVAL_TIMEDEPARTURE_TIMESTOP_IDSTOP_SEQUENCE
006:35:0006:36:0023951

Pour moi, c'est plus facile à dire qu'à faire...

Je précise que ton code fonctionne bien à une exception près: le format horaire me donne d'autres valeurs et avec un mauvais format. Ça, je devrais pouvoir le corriger.
 
Dernière édition:

jpb388

XLDnaute Accro
Re : Transformation de structure de table en VBA

Bonjour a tous
a tester
Code:
Sub essai()
  Dim Sh_s As Worksheet, Sh_c As Worksheet
 Set Sh_s = Sheets("Source")
 Set Sh_c = Sheets("Cible")
 Dim i As Long
 For i = 2 To Sh_s.Range("a" & Rows.Count).End(xlUp).Row
     If Sh_s.Cells(i, 5) = Sh_c.Cells(i - 1, 4) And Sh_s.Cells(i, 4) = Sh_c.Cells(i - 1, 1) Then
        Sh_c.Cells(i - 1, 3) = Format(Sh_s.Cells(i, 2), "00:00:00")
    Else
        Sh_c.Cells(i, 4) = Sh_s.Cells(i, 5)
        Sh_c.Cells(i, 1) = Sh_s.Cells(i, 4)
        If Sh_c.Cells(i, 1) <> Sh_c.Cells(i - 1, 1) Then
            Sh_c.Cells(i, 5) = 1
        Else
            Sh_c.Cells(i, 5) = Sh_c.Cells(i, 5) + 1
        End If
        If Sh_s.Cells(i, 3) = "D" Then
            Sh_c.Cells(i, 3) = Format(Sh_s.Cells(i, 2), "00:00:00")
        Else
            Sh_c.Cells(i, 2) = Format(Sh_s.Cells(i, 2), "00:00:00")
        End If
     End If
 Next
 End Sub

quand les heures se mettent mal c'est parce que les secondes >59
ex 23760=2h27mn60s et il ne peut avoir que 59s maxi
pareil pour les mn
 

Dhaulagiri

XLDnaute Nouveau
Re : Transformation de structure de table en VBA

Bonsoir,

Tout d'abord, merci JPB: ton aide m'a été très précieuse.

J'ai finalement réussi à faire ce que je voulais en adaptant le code que tu m'as donné. Il ressemble à ceci:
Code:
Sub ChargerSTOP_TIMES()
    Dim HORAIRE As Worksheet, STOP_TIMES As Worksheet
    Set HORAIRE = Sheets("horaire.txt")
    Set STOP_TIMES = Sheets("stop_times.txt")
    Dim I As Long
    Dim J As Long
    Dim K As Long
    
    For I = 2 To HORAIRE.Range("A" & Rows.Count).End(xlUp).Row
        'Copie de horaire.txt IDAP vers stop_times.txt STOP_ID
        STOP_TIMES.Cells(I, 4) = HORAIRE.Cells(I, 6)
        'Copie de horaire.txt COURSE vers stop_times.txt TRIP_ID
        STOP_TIMES.Cells(I, 1) = HORAIRE.Cells(I, 4)
        'Copie de horaire.txt HEURE vers stop_times.txt ARRIVAL_TIME ou DEPARTURE_TIME en fonction de la valeur de horaire.txt TYPE
        'Mise au format hh:mm:ss
        If HORAIRE.Cells(I, 3) = "D" Then
            STOP_TIMES.Cells(I, 3) = HORAIRE.Cells(I, 2) / 86400
            STOP_TIMES.Cells(I, 3).NumberFormat = "[$-F400]h:mm:ss AM/PM"
        Else
            STOP_TIMES.Cells(I, 2) = HORAIRE.Cells(I, 2) / 86400
            STOP_TIMES.Cells(I, 2).NumberFormat = "[$-F400]h:mm:ss AM/PM"
        End If
        'Regroupement de stop_times.txt ARRIVAL_TIME et DEPARTURE_TIME sur la même ligne pour un même stop_times.txt STOP_ID
        'Mise au format hh:mm:ss
        If STOP_TIMES.Cells(I, 4) = STOP_TIMES.Cells(I - 1, 4) Then
            STOP_TIMES.Cells(I - 1, 3) = STOP_TIMES.Cells(I, 3)
            STOP_TIMES.Cells(I - 1, 3).NumberFormat = "[$-F400]h:mm:ss AM/PM"
        End If
        'Vidage des lignes superflues
        If STOP_TIMES.Cells(I, 4) = STOP_TIMES.Cells(I - 1, 4) And STOP_TIMES.Cells(I, 3) = STOP_TIMES.Cells(I - 1, 3) Then
            Rows(I).Delete
        End If
    Next
    'Suppression des lignes vides
    For J = STOP_TIMES.UsedRange.Rows.Count To 1 Step -1
        If Application.CountA(Rows(J)) = 0 Then Rows(J).Delete
    Next J
    'Calcul de stop_times.txt STOP_SEQUENCE
    For K = 2 To HORAIRE.Range("A" & Rows.Count).End(xlUp).Row
        If STOP_TIMES.Cells(K, 1) <> STOP_TIMES.Cells(K - 1, 1) Then
            STOP_TIMES.Cells(K, 5) = 1
        Else
            STOP_TIMES.Cells(K, 5).Value = STOP_TIMES.Cells(K - 1, 5) + 1
        End If
    Next
End Sub

"HORAIRE" équivaut à "SOURCE" de l'exemple précédent et "STOP_TIMES" à "CIBLE".

Les plus aguerris trouveront mon code imparfait, poussif, redondant ou je ne sais trop quoi encore mais il fonctionne et c'est bien le principal. Il ne me reste plus qu'à injecter une ligne pour vider les tables avant de commencer le traitement et une ligne pour enregistrer tout cela dans un fichier.

En ce qui concerne la modification du format horaire: j'ai utilisé la fonction temps au départ mais le paramètre des secondes est limité à 37000 et des poussières. Du coup, seules les heures les plus matinales étaient correctement transformées et affichées. Pour les autres, une erreur #NOMBRE! était retournée. J'ai trouvé la solution sur ce forum: elle consiste à diviser le nombre de secondes par 86400 (le nombre de secondes par 24h) puis on applique au résultat le format souhaité.

Voilà. Encore vraiment merci, JPB
 
Dernière édition:

Discussions similaires

Réponses
15
Affichages
321