Trouver tous les trajets possibles entre x communes

kolele

XLDnaute Nouveau
Salut à tous,

Grâce à l'outil de Catrice (https://www.excel-downloads.com/thr...nce-entre-deux-villes-via-google-maps.106712/),
Je voudrais à figer un distancier des communes du Val-de-Marne dans une table ACCESS. Pour cela, il me faut d'abord dresser la liste des trajets de toutes lesdites communes (colonne A) vers toutes les autres (colonne B), avant de les passer à la moulinette de Catrice.

J'ai réussi à trouver la liste de toutes les communes. Comment obtenir la liste de tous les trajets possibles ? Avec 3 communes (Thiais, Orly, Créteil), ça donne :
Thiais – Orly, Thiais – Créteil, Orly – Créteil. Avec 50 communes, il faudrait une formule ou une astuce (je ne touche pas trop en VBA). La seule piste que j'ai c'est un tableau croisé, avec toutes les villes en colonne, toutes les villes en lignes et à l'intersection une formule de concaténation des en-têtes, pour ensuite mettre toutes les résultats en 1 colonne et convertir ça en 2 colonnes (les fameuses colonnes A et B de Catrice). Mais c'est fastidieux : il faut modifier la formule de concaténation à chaque ligne, avant de la tirer:(.

Merci de votre aide.
 

kolele

XLDnaute Nouveau
Re : Trouver tous les trajets possibles entre x communes

Désolé, Hoerwind.
ça fait 2 heures que je cherche, en vain.:(
j'aurais appris que mon problème concerne une liste de combinaisons. Mais s'il y a énormément de posts sur le sujet, je n'ai pas trouvé comment lister mes communes en 2 colonnes.
Il y a bien le post de Nohrad et la macro de Job75, (https://www.excel-downloads.com/threads/generer-une-liste-de-combinaisons.128731/)
mais je n'arrive pas à la faire fonctionner avec 2 paramètres seulement (commune de départ ; commune d'arrivée)

Je vois bien que le sujet est rebattu, mais un petit coup de pouce ne serait pas de refus.
 

job75

XLDnaute Barbatruc
Re : Trouver tous les trajets possibles entre x communes

Bonsoir kolele, hoerwind,

Avant d'aller au lit, le coup de pouce :

Code:
Sub Combinaisons()
Dim plage As Range, d As Long, c As Long, i As Long, j As Long, n As Long, tablo()
With Sheets("Combinaisons")
  Set plage = .Range("A2", .Range("A65536").End(xlUp))
  d = plage.Count
  c = Application.Combin(d, 2)
  ReDim tablo(1 To c)
  .Range("B2:B65536").ClearContents
  For i = 1 To d - 1
    For j = i + 1 To d
      n = n + 1: tablo(n) = plage(i) & "-" & plage(j)
    Next
  Next
  .Range("B2").Resize(c) = Application.Transpose(tablo)
End With
End Sub

Bonne nuit.
 

Pièces jointes

  • Combinaisons Villes (1).xls
    35 KB · Affichages: 112

job75

XLDnaute Barbatruc
Re : Trouver tous les trajets possibles entre x communes

Re,

J'oubliais qu'il vaut mieux 2 colonnes :

Code:
Sub Combinaisons()
Dim plage As Range, d As Long, c As Long, i As Long, j As Long, n As Long, tablo()
With Sheets("Combinaisons")
  Set plage = .Range("A2", .Range("A65536").End(xlUp))
  d = plage.Count
  c = Application.Combin(d, 2)
  ReDim tablo(1 To c, 1 To 2)
  .Range("B2:C65536").ClearContents
  For i = 1 To d - 1
    For j = i + 1 To d
      n = n + 1: tablo(n, 1) = plage(i): tablo(n, 2) = plage(j)
    Next
  Next
  .Range("B2").Resize(c, 2) = tablo
End With
End Sub

Rebonne nuit.
 

Pièces jointes

  • Combinaisons Villes (2).xls
    35 KB · Affichages: 119
Dernière édition:

kolele

XLDnaute Nouveau
Re : Trouver tous les trajets possibles entre x communes

Salut à Job75, Catrice et à tous les autres,

Je me heurte à un imprévu : je croyais naïvement pouvoir faire tourner les combinaisons avec les 835 communes d'Ile de France, mais ça dépasse les 65.536 lignes !!!
Y a-t-il un moyen pour dépasser ce plafond ?
Le projet consiste à gérer sous ACCESS des frais de déplacements de profs. Je sais que mes profs ne visitent leurs étudiants qu'en Ile de France, donc une table suffisait avec tous les trajets possibles et associés à la distance en km (obtenue grâce à l'outil de Catrice : https://www.excel-downloads.com/thr...nce-entre-deux-villes-via-google-maps.106712/
).
S'il n'y avait pas moyen dans cette voie là, la moulinette de Catrice peut elle s'installer sur ACCESS pour calculer automatiquement les trajets de mes profs ?
Bien à tous
 

kolele

XLDnaute Nouveau
Re : Trouver tous les trajets possibles entre x communes

Le diner m'a donné l'occasion de préciser la question : y a t-il un moyen, dans un calcul de combinaisons, de couper la requete en plusieurs morceaux, afin d'éviter la barrière des 65.536 lignes ?
 

job75

XLDnaute Barbatruc
Re : Trouver tous les trajets possibles entre x communes

Bonsoir kolele,

On peut bien sûr se servir d"excel 2007 qui dépasse le million de lignes.

Mais on peut aussi découper le tableau et lui faire prendre des colonnes supplémentaires.

Je regarde comment programmer ça sur mon dernier fichier.

A+
 

job75

XLDnaute Barbatruc
Re : Trouver tous les trajets possibles entre x communes

Bonjour kolele, le fil, le forum,

Sur le web, j'ai trouvé 1280 communes en Ile-de-France.

J'ai défini autrement (en rouge) la variable plage, ce qui diminue la durée du calcul :

Code:
Sub Combinaisons()
Dim t#, plage, d&, c&, i&, j&, n&, tot&, tablo$(1 To 65000, 1 To 2) 'dimension à adapter
With Sheets("Combinaisons")
  With .DrawingObjects("Bouton 1")
    .Text = "Patientez..."
    .Font.ColorIndex = 3
    .Font.FontStyle = "Gras italique"
  End With
  .Range("B3", Cells(.Rows.Count, .Columns.Count)).ClearContents
  t = Timer
[COLOR="Red"]  plage = Application.Transpose(.Range("A3", .Cells(.Rows.Count, 1).End(xlUp)))[/COLOR]
  d = UBound(plage)
  c = Application.Combin(d, 2)
  For i = 1 To d - 1
    For j = i + 1 To d
      n = n + 1: tablo(n, 1) = plage(i): tablo(n, 2) = plage(j)
      If n = 65000 Or tot + n = c Then
        .Cells(3, .Columns.Count).End(xlToLeft).Offset(, 1).Resize(n, 2) = tablo 'décharge le tableau
        tot = tot + n: n = 0: Erase tablo 'réinitialise
      End If
    Next
  Next
  MsgBox Chr(10) & Format(c, "# ##0") & " combinaisons" & Chr(10) & Chr(10) & _
    "Traitement : " & Format(Timer - t, "0.00") & " s" & Chr(10)
  With .DrawingObjects("Bouton 1")
    .Text = "Combinaisons"
    .Font.ColorIndex = 5
    .Font.FontStyle = "Gras"
  End With
End With
End Sub

Edit : 3 communes ont un homonyme, on les trouvera en recherchant la parenthèse (

A+
 

Pièces jointes

  • Combinaisons Villes Ile-de-France(1).zip
    31.3 KB · Affichages: 79
Dernière édition:

kolele

XLDnaute Nouveau
Re : Trouver tous les trajets possibles entre x communes

Merci beaucoup, Job75 ! J'exploite ça tout de suite:)
Je vais intégrer ma propre table des communes de France (majuscule et sans tiret), pour que la liaison se fasse sous ACCESS.

Mon projet a fait un bond gigantesque, grace à toi.
à bientot !

kolele
 

Discussions similaires

Réponses
31
Affichages
2 K

Statistiques des forums

Discussions
311 725
Messages
2 081 944
Membres
101 849
dernier inscrit
florentMIG