Kobelei: Fahrplanabfrage

Bild

Betrifft: Kobelei: Fahrplanabfrage
von: ein_herz_fuer_excel
Geschrieben am: 10.05.2015 16:54:52

Hallo! Ich habe ein etwas komplexeres Problem und knobel schon ne ganze Weile daran:
Ich versuche aus Fahrplandaten eine Matrix zu erstellen, die mir ausgibt wie häufig es möglich ist unter Zeitrestriktion von A nach B zu kommen.
Die Daten sind wie folgt aufbereitet:

  • A1 Zug_ID;
    B1 Bahnhof 1;
    C1 Ankunft Bahnhof 1;
    D1 Abfahrt Bahnhof 1;
    E1 Bhf2;
    F1 An Bhf2;
    G1 Ab Bhf
    ...

  • In einer neuen Tabelle möchte mir in einer Matrix ausgeben lassen wie viele Möglichkeiten ich habe um von Bhf 1 zu Bhf 2 usw. zu gelangen.
    B1 Bhf 1 B2 Bhf 2 B3 Bhf 3 ...
    A2 Bhf 1 x
    A3 Bhf 2
    A4 Bhf 3
    ...
    Meine Idee war bisher nur für x
    
     =ZÄHLENWENNS(Blatt1!B:B; Blatt 2!A2;Blatt 1!F:F; Blatt 2!B2)  *WENN(Blatt 1!F:F > Blatt 1!D:D;1;0)  

    ... das gleiche für Kombi Bhf 1 und Bhf 3
    Ich vermute das ist nicht die galanteste Lösung, weil es ziemlich mühsam ist aus dem Blatt 1 jeweils die Spalten auszuwählen (ca. 40 Bhf).
    Es wäre SUPER wenn der ein oder andere ein paar Tipps oder Ideen hätte!
    Vielen lieben Dank schon mal im Voraus!

    Bild

    Betrifft: AW: Kobelei: Fahrplanabfrage
    von: ein_herz_fuer_excel
    Geschrieben am: 10.05.2015 17:04:26

    
    =ZÄHLENWENNS(Blatt1!B:B; Blatt 2!A2;Blatt 1!E:E; Blatt 2!B2)  *WENN(Blatt 1!F:F > Blatt 1!D:D;1;0) 


    Bild

    Betrifft: AW: Kobelei: Fahrplanabfrage
    von: Daniel
    Geschrieben am: 10.05.2015 17:09:49
    Hi
    kannst mal die Ausgangstabelle hochladen?
    wäre vielleicht hilfreich.
    und für für deine Beispieldaten ein passendes Wunschergebnis.
    Gruß Daniel

    Bild

    Betrifft: AW: Kobelei: Fahrplanabfrage
    von: Klaus M.vdT.
    Geschrieben am: 10.05.2015 22:30:24
    Hi,
    mit der Anzahl der Bahnhöfe steigt die Anzahl der Möglichkeiten exponentiell. Für 3 oder 4 Bahnhöfe ist das noch mit "Brute Force" möglich, dann wird es schnell die Grenzen von Excel sprengen.
    Mir kommt das ganze so ähnlich vor wie das "Traveling Salesman"-Problem. Hier schön erklärt:
    http://de.wikipedia.org/wiki/Problem_des_Handlungsreisenden
    Du musst das natürlich etwas abstrahieren - du suchst ja die kürzeste Zeit, nicht den kürzesten Weg. Aber von der Sache her wird das eine ähnliche Heuristik werden. Mit Excelformeln lösbar? Ich sage nein, den Traveling Salesman hat mit Formeln auch niemand gelöst bekommen. Mit VBA lösbar? Sicherlich - aber da braucht es jemanden, der ne Menge über Heuristik und Algorythmen versteht UND VBA kann.
    Grüße,
    Klaus M.vdT.

    Bild

    Betrifft: AW: Kobelei: Fahrplanabfrage
    von: Klaus M.vdT.
    Geschrieben am: 10.05.2015 22:30:24
    Hi,
    mit der Anzahl der Bahnhöfe steigt die Anzahl der Möglichkeiten exponentiell. Für 3 oder 4 Bahnhöfe ist das noch mit "Brute Force" möglich, dann wird es schnell die Grenzen von Excel sprengen.
    Mir kommt das ganze so ähnlich vor wie das "Traveling Salesman"-Problem. Hier schön erklärt:
    http://de.wikipedia.org/wiki/Problem_des_Handlungsreisenden
    Du musst das natürlich etwas abstrahieren - du suchst ja die kürzeste Zeit, nicht den kürzesten Weg. Aber von der Sache her wird das eine ähnliche Heuristik werden. Mit Excelformeln lösbar? Ich sage nein, den Traveling Salesman hat mit Formeln auch niemand gelöst bekommen. Mit VBA lösbar? Sicherlich - aber da braucht es jemanden, der ne Menge über Heuristik und Algorythmen versteht UND VBA kann.
    Grüße,
    Klaus M.vdT.

    Bild

    Betrifft: AW: Kobelei: Fahrplanabfrage
    von: ein_herz_fuer_excel
    Geschrieben am: 11.05.2015 10:24:03
    Hallo! Vielen Dank schon mal für die Antworten.
    Habe eine Beispieldatei angehängt! Konnte bisher nur die Anzahl der möglichen Verbindungen eingeben ohne zeitliche Restriktion. Da hört mein Excel-Latein anscheinend auf.
    https://www.herber.de/bbs/user/97571.xlsx

    Bild

    Betrifft: AW: Kobelei: Fahrplanabfrage
    von: ein_herz_fuer_excel
    Geschrieben am: 11.05.2015 13:38:20
    Wenn es erstmal nur für wenige Bahnhöfe gehen würde, wäre das auch schon gut!

    Bild

    Betrifft: AW: Kobelei: Fahrplanabfrage
    von: Daniel
    Geschrieben am: 11.05.2015 14:33:19
    mit Formeln könnte das kompliziert werden.
    sind UDFs (als Makro selbstgeschriebene Funktionen)erlaubt?
    Gruß Daniel

    Bild

    Betrifft: AW: Kobelei: Fahrplanabfrage
    von: ein_herz_fuer_excel
    Geschrieben am: 11.05.2015 14:56:13
    Sowas habe ich zumindest noch nie verwendet. Sollte es als Formel gehen - auch wenn kompliziert - wäre mir das lieber!
    Vielen Dank schon mal!!
    Liebe Grüße

    Bild

    Betrifft: AW: Kobelei: Fahrplanabfrage
    von: Daniel
    Geschrieben am: 11.05.2015 15:58:12
    dann wieder offen.

    Bild

    Betrifft: AW: Kobelei: Fahrplanabfrage
    von: ein_herz_fuer_excel
    Geschrieben am: 12.05.2015 16:07:05
    Wie würdest du es denn mit einem Makro lösen?
    LG

    Bild

    Betrifft: AW: Kobelei: Fahrplanabfrage
    von: Daniel
    Geschrieben am: 12.05.2015 16:13:49
    das Makro dazu habe ich inzwischen wieder gelöscht.
    Gruß Daniel

    Bild

    Betrifft: AW: Kobelei: Fahrplanabfrage
    von: Daniel
    Geschrieben am: 12.05.2015 17:09:52
    Ok ist nicht so lang
    folgenden Code in ein allgemeines Modul:

    
    Function AnzVerbindungen(Fahrplan As Range, BHF1 As String, BHF2 As String, Optional maxZeit As  _
    Date = 9999) As Long
    Dim arrFP
    Dim z As Long
    Dim s As Long
    Dim chkBHF1 As Boolean
    Dim T1 As Date
    Dim T2 As Date
    Dim Zähler As Long
    arrFP = Fahrplan.Value
    For z = 1 To UBound(arrFP, 1)
        chkBHF1 = False
        For s = 1 To UBound(arrFP, 2) Step 3
            If arrFP(z, s) = BHF1 Then
                chkBHF1 = True
                T1 = arrFP(z, s + 2)
            ElseIf arrFP(z, s) = BHF2 Then
                If chkBHF1 Then
                    T2 = arrFP(z, s + 1)
                    If T2 - T1 <= maxZeit Then Zähler = Zähler + 1
                End If
                Exit For
            End If
        Next s
    Next z
    AnzVerbindungen = Zähler
    End Function
    in Zelle B4 des Blattes Verbindungsanzahl kommt dann die Formel:
    =AnzVerbindungen(Fahrplan!$A$1:$O$21;$A4;B$3;ZEIT(1;0;0))
    welche du nach rechts und nach unten ziehst.
    den letzen Parameter ZEIT kannst du auch weglassen, dann werden alle Verbingungen gezählt, ansonsten nur die die kürzter sind als die angegebene Zeit
    der Fahrplan muss nach rechts zeitlich aufsteigend sortiert sein.
    Gruß Daniel

    Bild

    Betrifft: AW: Kobelei: Fahrplanabfrage
    von: ein_herz_fuer_excel
    Geschrieben am: 12.05.2015 17:33:39
    Ok, vielen Dank!! Ich muss mich jetzt erstmal ein bisschen zum Erstellen von Makros usw. einlesen. Und dann werde ich das ausprobieren :)

    Bild

    Betrifft: AW: Kobelei: Fahrplanabfrage
    von: ein_herz_fuer_excel
    Geschrieben am: 12.05.2015 17:35:02
    Hast du dazu eine Datei angehängt? Ich habe es leider zu spät gesehen. Könntest du sie nochmal hochladen ?

    Bild

    Betrifft: AW: Kobelei: Fahrplanabfrage
    von: Daniel
    Geschrieben am: 13.05.2015 16:25:11
    Hi
    nö.
    wechsle in den VBA-Editor (mit ALT+F11)
    füge ein neues allgemeines Modul ein. (Menü: Einfügen - Modul)
    kopiere den Code aus dem Beitrag (von Function... bis ...End Function) und füge ihn in das Codefenster des neu erstellten Moduls ein (Modul1) (das grosse Fenster, wo die beiden Comboboxen drüber stehen)
    dann kannst du in dieser Datei diese Funktion wie eine normale Excelfunktion verwenden.
    weitere Infos hier: http://www.online-excel.de/excel/singsel_vba.php?f=44
    Gruß Daniel

    Bild

    Betrifft: AW: Kobelei: Fahrplanabfrage
    von: ein_herz_fuer_excel
    Geschrieben am: 13.05.2015 17:39:22
    HA! Vielen Dank !!!!
    Genau das gleiche hatte ich gestern eigentlich auch gemacht. Aber nun funktioniert es!
    Fragt Excel hierbei nur die Direktverbindungen (also Bahnhof 1 zu Bahnhof 2 oder Bahnhof 3 zu Bahnhof 4) ab oder auch Kombinationen (also Bahnhof 1 zu Bahnhof 4 oder Bahnhof 3 zu Bahnhof 1)?
    Liebe Grüße

    Bild

    Betrifft: AW: Kobelei: Fahrplanabfrage
    von: Daniel
    Geschrieben am: 13.05.2015 17:47:51
    Hi
    ich gehe davon aus, dass eine Verbindung (eine Zugroute) in einer Zeile steht und die Bahnhöfe dort in ihrer Zeitlichen Abfolge von links nach rechts sortiert erscheinen.
    ein Treffer wird gezählt wenn:
    Start- und Zielbahnhof in der Zeile vorkommen und der Startbahnhof links vom Zielbahnhof steht.
    Gibst du zusätzlich noch eine Maximalzeit ein, wird der Treffer nur dann gezählt wenn die Differenz zwischen Abfahrt vom Startbahnhof und Ankunft am Zielbahnhof kleiner ist als die vorgegebene Zeit.
    hierbei habe ich aber auch noch keine Überprüfung für eine Zeitangabe über Mitternacht hinaus gemacht, dh die Abfahrt und Ankunft müssen am selben Kalernertag liegen.
    Gruß Daniel

    Bild

    Betrifft: AW: Kobelei: Fahrplanabfrage
    von: ein_herz_fuer_excel
    Geschrieben am: 18.05.2015 11:32:27
    Hallo Daniel,
    das ist genau das was ich gesucht habe, perfekt. Herzlichsten Dank! Ist es darüber hinaus auch möglich eine "Umsteige-Restriktion" einzubauen?
    Z.b. soll die Verbindung nur gezählt werden, wenn man maximal 5 mal umsteigen müsste.
    Grüße

    Bild

    Betrifft: AW: Kobelei: Fahrplanabfrage
    von: Daniel
    Geschrieben am: 18.05.2015 11:42:53
    Hi
    kann man sicherlich.
    Das übersteigt aber meine aktuellen Fähigkeiten und den Zeitaufwand, den ich bereit bin für dich zu leisten.
    Aber vielleicht findet sich ja jemand anders.
    Gruß Daniel

    Bild

    Betrifft: AW: Kobelei: Fahrplanabfrage
    von: ein_herz_fuer_excel
    Geschrieben am: 18.05.2015 12:54:30
    Kein Problem! Kann ich auch verstehen. Du hast mir schon sehr geholfen! Lieben Dank

    Bild

    Betrifft: AW: Kobelei: Fahrplanabfrage
    von: ein_herz_fuer_excel
    Geschrieben am: 18.05.2015 17:21:09
    Hallo Daniel,
    eine (hoffentlich letzte) Frage habe ich noch: ich habe nun versucht dein tolles Makro auf meinen richtigen Datensatz anzuwenden, der im Grunde so aussieht wie die Beispieldatei. In dem Makro Code, den du mir geschrieben hast sehe ich keinen Bezug zu einem Spaltennamen aus der Beispieldatei, weswegen ich dachte dass ich da nichts weiter anpassen muss.
    Nur in der =AnzVerbindungen(...) Formel habe ich natürlich den gesamten Fahrplan markiert.
    Leider zählt Excel nur vereinzelt mal 1 oder 2 Verbindungen. Woran könnte das liegen?
    Ich hoffe ich nerve dich nicht zu sehr, haha.
    Liebe Grüße

    Bild

    Betrifft: AW: Kobelei: Fahrplanabfrage
    von: Daniel
    Geschrieben am: 18.05.2015 17:34:16
    meine Formel geht davon aus, das im Zellbereich, den du als Fahrplan angibst, in jeder ersten Spalte der Bahnhof steht, in jeder zweiten Spalte die Abfahrtszeit und jeder dritten Spalte die Abfahrtszeit und sich das alternierend so wiederholt.
    Spaltenüberschriften sind hier unnötig.
    Die Unterschiede zwischen deiner Beispiel- und der Originaldatei kennst nur du, aber nicht ich.
    Baue die Originaldatei so auf wie die Beispieldatei, dann sollte es funktionieren.
    Gruß Daniel

    Bild

    Betrifft: AW: Kobelei: Fahrplanabfrage
    von: ein_herz_fuer_excel
    Geschrieben am: 18.05.2015 17:43:43
    Alles klar! Das wird es sein! Danke

    Bild

    Betrifft: AW: Kobelei: Fahrplanabfrage
    von: ein_herz_fuer_excel
    Geschrieben am: 20.05.2015 17:39:17
    Hallo!
    Ich habe jetzt doch nochmal eine Frage... also ich habe das Makro erfolgreich auf meinen Datensatz anwenden können. Aber du hast ja selbst geschrieben, dass die Abfrage immer nur aus der gleichen Zeile erfolgt. Weißt du wie man dahingehend eine Zeilen übergreifende Abfrage stellen kann? Dass alle Verbindungen (Zeilen) überprüft werden?!
    LG

    Bild

    Betrifft: AW: Kobelei: Fahrplanabfrage
    von: ein_herz_fuer_excel
    Geschrieben am: 12.05.2015 18:07:47
    Ich habe es als ein neues Makro eingefügt (Alt+F11 - Code eingefügt in aktuelle Arbeitsmappe - Alt+F4 - deine Formel eingefügt) aber er spukt mir dann die Fehlermeldung #Name? aus. Woran kann das liegen?

     Bild

    Beiträge aus den Excel-Beispielen zum Thema "Buttons hintereinander schalten"