Live-Forum - Die aktuellen Beiträge
Datum
Titel
16.04.2024 19:22:16
Von exceltabelle in word per steuerelement temarker
16.04.2024 18:50:53
Fehlermeldung bei Code - Excel speichern mit Bez. aus Zelle
16.04.2024 17:18:01
Projektplan-Tabelle oder Gantt ohne Balken
16.04.2024 17:05:25
formatierten Text direkt in Zelle einfügen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1424to1428
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1424to1428
1424to1428
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Kobelei: Fahrplanabfrage

Kobelei: Fahrplanabfrage
10.05.2015 16:54:52
ein_herz_fuer_excel
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!

    25
    Beiträge zum Forumthread
    Beiträge zu diesem Forumthread

    Betreff
    Datum
    Anwender
    Anzeige
    AW: Kobelei: Fahrplanabfrage
    10.05.2015 17:04:26
    ein_herz_fuer_excel 
    
=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)

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

    AW: Kobelei: Fahrplanabfrage
    10.05.2015 22:30:24
    Klaus
    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.

    Anzeige
    AW: Kobelei: Fahrplanabfrage
    10.05.2015 22:30:24
    Klaus
    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.

    Anzeige
    AW: Kobelei: Fahrplanabfrage
    11.05.2015 10:24:03
    ein_herz_fuer_excel
    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

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

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

    AW: Kobelei: Fahrplanabfrage
    11.05.2015 14:56:13
    ein_herz_fuer_excel
    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

    Anzeige
    AW: Kobelei: Fahrplanabfrage
    11.05.2015 15:58:12
    Daniel
    dann wieder offen.

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

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

    AW: Kobelei: Fahrplanabfrage
    12.05.2015 17:09:52
    Daniel
    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
    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

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

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

    AW: Kobelei: Fahrplanabfrage
    13.05.2015 16:25:11
    Daniel
    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

    Anzeige
    AW: Kobelei: Fahrplanabfrage
    13.05.2015 17:39:22
    ein_herz_fuer_excel
    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

    AW: Kobelei: Fahrplanabfrage
    13.05.2015 17:47:51
    Daniel
    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

    Anzeige
    AW: Kobelei: Fahrplanabfrage
    18.05.2015 11:32:27
    ein_herz_fuer_excel
    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

    AW: Kobelei: Fahrplanabfrage
    18.05.2015 11:42:53
    Daniel
    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

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

    Anzeige
    AW: Kobelei: Fahrplanabfrage
    18.05.2015 17:21:09
    ein_herz_fuer_excel
    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

    Anzeige
    AW: Kobelei: Fahrplanabfrage
    18.05.2015 17:34:16
    Daniel
    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

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

    AW: Kobelei: Fahrplanabfrage
    20.05.2015 17:39:17
    ein_herz_fuer_excel
    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

    Anzeige
    AW: Kobelei: Fahrplanabfrage
    12.05.2015 18:07:47
    ein_herz_fuer_excel
    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?

    Links zu Excel-Dialogen

    ComboBox

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige