Excel 2013 - Valeur liée à la cellule précédente et à la cellule suivante

Bonjour,

C'est mon premier message. Je vais tâcher d'expliquer mon problème en joignant un fichier.

J'ai un tableau provenant de plusieurs balise gps qui donne l'heure du point de passage ("date et heure"), la localisation ("code insee") et la vitesse ("Vitesse"°. Les données sont triées par date et heure. Mes points sont donc dans l'ordre, ils reproduisent l'itinéraire suivi.

1. Je souhaiterais indiquer pour chaque ligne concernée les fois où le point traverse une nouvelle commune et à chaque fois qu'il en sort. Comme les données sont ordonnées, cela nécessite de comparer la cellule précédente et la cellule suivante. Quand il entre dans une nouvelle commune je vais faire 'code insee - entrée' et chaque fois qu'il sort 'code insee-sortie'.

2. Ensuite, la problématique se corse déjà qu'elle l'est déjà bien assez. Souvent l'entrée dans une commune n'est qu'un passage rapide : il y a peu de points GPS, la durée de passage est courte (voir champs "date et heure"), la vitesse y est plutôt rapide avec peu ou pas d'arrêt (vitesse=0). Mais parfois, le passage dans une commune montre de nombreux points GPS, une durée de passage plus longue et de nombreux ralentissements et arrêts. Les vitesses faibles et arrêts signifient que l'engin sous balise gps accomplit une tâche. J'aimerais mettre en évidence des différentes situations.

Je résume.

Si le nombre d’occurrence d'une commune (champs "code insee") est inférieur à 10 et que le nombre de fois où la vitesse est égale à 0 est inférieure 2 ALORS saisir 'commune de passage' SINON saisir 'commune de ramassage'. Je précise que la difficulté ici est que les données sont ordonnées par date et heure. On peut donc retrouver une commune à 7h du matin puis la même à 10h du matin...Etc.

Merci de votre aide pour ce problème d'analyse complexe.

Bonjour,

Le principe serait de travailler à partir des couples Insee - Date Heure et du tableau croisé associé avec en champ page les vitesses = 0.

Merci de votre retour, je vais essayer d'analyser votre méthode originale.

A première vue, j'ai beaucoup trop de "commune de ramassage" alors qu'en réalité le camion ne fait que passer dans la plupart d'entre elle.

Mais cela, il faudra peut être que je l'affine avec d'autres critères discriminants. Car la vitesse à 0 ne veut pas forcément dire ramassage ça peut aussi être un arrêt à un stop ou à un feu. Il faudrait pour s'assurer d'un ramassage qu'il y ait beaucoup d'occurence de points sur la communes et qu'il y ait beaucoup de vitesse à 0 ou à - de 5 km/h.

Avez-vous une idée sur la partie 1 du problème, à savoir indiquer l'entrée dans une commune et la sortie d'une commune?

Pour restreindre le nombre de communes de ramassage, il faut jouer sur l'arrondi de la colonne couple et actualiser le TCD.

=F2&" "&ARRONDI(D2;1)

Pour les entrées sorties, j'ai ajouté une colonne Entrée sortie et créé un TCD. Vous saurez mieux l'interpréter que moi.

Bonjour,

Merci de votre retour, je n'arrive pas atteindre le résultat souhaité.

Pour les entrées/sorties, c'est réglé en m'inspirant de votre proposition.

Maintenant, il faut que je sois plus précis pour qualifier une ligne pour déterminer si elle correspond à un passage ou à un ramassage. Je précise que chaque ligne correspond à un relevé GPS d'un camion effectuant une tournée. Dans sa tournée, il y a des communes dans lesquelles il ne fait que passer. Cela se traduit par "peu de points" dans la commune. Et il y a des communes où il effectue un ramassage donc il s'arrête (ou presque) plusieurs fois. Comme nous avons l'indication de vitesse, j'ai déjà fait un Si "vitesse <10 km/h alors ramassage possible". Mais c'est insuffisant. Une vitesse inférieure à 10 peut tout aussi bien correspondre à un ralentissement sur la voie, un céder le passage, un stop,...

Par exemple, lignes 2 et 3 ce ne peut pas être un ramassage puisque je n'ai que 2 points dans la commune ensuite le camion passe à la commune 69178. On voit qu'il ne fait que passer. En revanche, à partir de la ligne 93 pour la commune 69038, on voit que le camion ralentit plusieurs fois pour ramasser et qu'il redémarre ligne 96 puis ralentit pour ramasser ligne 97... On voit clairement qu'il y a ramassage dans cette commune, du fait qu'il y a plusieurs séquences de vitesse lente suivi d'une reprise.

Je ne sais pas comment m'y prendre. Sachant qu'il faut vraiment tenir compte de l'heure et de la commune et ne pas faire un nb si ensemble par commune pour compter les points. L'ordre de passage est important du fait qu'un camion peut passer dans une commune le matin mais ramasser plus tard dans l'après midi.

Bonjour,

J'ai transformé votre fichier en .xlsm et transformé votre tableau en tableau structuré pour y ajouter la notion de séquence.

Une séquence, c'est l'ensemble des enregistrements pour une commune depuis l'entrée jusqu'à la sortie. Le code ci-dessous permet de remplir la colonne :

    Option Explicit

Sub IdentifierLesSequences()

Dim I As Integer, SequenceEnCours As Integer
Dim AireInsee As Range, AireSequence As Range
Dim InseeEnCours As String

    Application.ScreenUpdating = False
    SequenceEnCours = 1

    Set AireInsee = Range("t_Gps[CODE_INSEE]")
    InseeEnCours = AireInsee(1)

    Set AireSequence = Range("t_Gps[Séquence]")

    For I = 1 To AireInsee.Count
        If AireInsee(I) = InseeEnCours Then
           AireSequence(I) = SequenceEnCours
        Else
          SequenceEnCours = SequenceEnCours + 1
          InseeEnCours = AireInsee(I)
          AireSequence(I) = SequenceEnCours
        End If

    Next I

    Set AireInsee = Nothing:  Set AireSequence = Nothing
    Application.ScreenUpdating = True

End Sub

J'ai transformé le TCD pour faire apparaître les temps pour les 222 séquences. Les durées vont de quelques secondes à plusieurs heures. Il faudrait voir maintenant si une règle peut en être déduite pour distinguer ce qui relève du ramassage et du passage.

Si on ne prend que les séquences avec les vitesses mini = 0 et qu'on exclut les durées autour d'une minute, on obtient 79 séquences.

Bonjour,

Ah oui! C'est exactement ce que je cherchais pouvoir dénombrer les séquences à chaque nouvelle entrée dans une commune. On y est là, merci beaucoup. J'ai appris au passage la notion de tableau structuré.

Ainsi avec la notion de séquence on peut identifier les communes de ramassage et les communes de passage. On peut dire qu'il y a ramassage si dans une séquence il y a plus de 7 occurrences de vitesse inférieure à 10 km/h.