Live-Forum - Die aktuellen Beiträge
Datum
Titel
18.04.2024 18:04:29
Formel fehler Wert#
18.04.2024 17:45:21
Shellkommando ausführen, stderr auslesen
18.04.2024 17:41:41
Summe: Bestimmter Bereich mit sverweis
18.04.2024 16:33:24
Worksheets variable
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1700to1704
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
1700to1704
1700to1704
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Wie Zeitpunkt-DB und Zeitraum-DB kombinieren?

Wie Zeitpunkt-DB und Zeitraum-DB kombinieren?
04.07.2019 15:27:13
Anton
Hallo liebe Excel-Freunde,
ich habe ein bittere Excelaufgabe von meiner Chefin bekommen, die mich ziemlich fordert.
Es geht um zwei Excel Tabellenblätter (eine davon mit 19000 Zeilen). In der einen Tabelle finde ich die manuellen Dateneingaben unseres Programms mit den Abfahrt- und Ankunftszeiten und auf der andere Tabelle finde ich die GPS Daten, die innerhalb der Abfahrt- und Ankunftszeiten generiert wurden. Allerdings werden in den GPS-Daten auch kleine Motoraktivitäten angezeigt, wo höchstens 10 Meter gefahren wurde.
Die GPS-Tabelle gibt mir Auskunft, wie genau der LKW gefahren ist (KM, etc).
Die Programm-Tabelle gibt mir darüber Auskunft, welcher Kunde mit dem LKW bedient wurde.
Jetzt will ich die Kunden anhand der GPS-Zeile zuordnen und habe nur die Zeiträume und die LKW-Daten. Meine Idee war bisher, dass ich irgendwie versucht habe, die Kunden zuzuordnen, sobald die GPS-Zeit höher als die Abfahrtszeit und kleiner als die Ankunftszeit war. Allerdings hat der Versuch mit den Formeln: Index, Vergleich, Summenwenns, Zählenwenns, etc. bei mir keinen Erfolg gezeigt.
FRAGE
Userbild
Ich darf leider keine echten Daten preisgeben. Daher muss ich hier mit Beispieldateien arbeiten und habe ein kleines Beispiel hier hochgeladen.
Beispiel:
https://www.herber.de/bbs/user/130723.xlsx
Über eure Vorschläge wäre ich echt dankbar.
VG Anton

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA?
04.07.2019 16:17:55
Fennek
Hallo,
ist VBA möglich?
mfg
(nach einem ersten Check sollte es mit VBA möglich sein, ob sich jemand findet, ist eine andere FRage)
AW: VBA?
04.07.2019 16:27:10
Anton
VBA ist auch kein Problem... ein paar Zeile davon verstehe ich sogar selbst. Aber wie gesagt, ich habe einfach keine Idee.
AW: VBA-Code
04.07.2019 17:17:17
Fennek
Hallo,
DAtumsformate sind tückig.
In der kleinen Beispieldatei geht das:

Sub T_2()
Kun = Sheets("Programm").Cells(1).CurrentRegion
Sheets("GPS").Activate
For i = 2 To UBound(Kun)
Tag1 = ">=" & Tg(Kun(i, 2))
Tag2 = "
Die Spalte F wurde als Datum + Zeit =B+C angelegt.
mfg
Anzeige
AW: VBA-Code
05.07.2019 10:21:47
Anton
Hallo Fennek, hallo Daniel,
danke für Eure Ergebnisse. Ich konnte erst jetzt alles lesen und testen.
Im Ergebnis des VBA-Codes ist die zweite Zeile mit dem Kunden "SALZ" falsch. Denn die Zeit passt zu "SCHMIDT".
Die Lösung mit den Formeln hat den Vorteil, dass die Zeiten ohne Kunden auch so vermerkt werden.
@Martin, @Daniel: Ich kann euch beruhigen. Ihr habt beide recht.
Ich bin im Controlling tätig und musste in der Vergangenheit für die GF einen mtl. Report mit Powerpoint erstellen. Die Ressourcen, um die Excelgrafiken und Daten im Liveupdate nach Powerpoint zu übertragen, hat meinen Rechner aufgrund von 100 Tabellenblättern, zig Formeln und 180 Powerpointfolien in die Knie gezwungen - da ging einfach nichts mehr.
Augrund dieser Erkenntnis wurde durch die GF mein Rechner mit 64 GB Arbeitsspeicher ausgestattet. Daher haben die Formeln jetzt auch nicht mal 1 Sekunde gebraucht. Zusätzlich bin ich dadurch jetzt auch der Depp :) geworden, dem man solche großen Datenbanken anvertraut und bearbeiten lässt.
Vielen Dank für Eure Beiträge
LG Anton
Anzeige
AW: Wie Zeitpunkt-DB und Zeitraum-DB kombinieren?
04.07.2019 17:53:44
Martin
Hallo Anton,
ich finde es gut und richtig, dass von Fennek eine VBA-Lösung erstellt hat. Mit Formeln wäre die Ermittlung der Daten auch möglich gewesen, aber bei 19.000 Zeilen hätte Excel zur Berechnung der Formeln entweder ewig gebraucht oder wäre in die Knie gegangen ;-)
Viele Grüße
Martin
AW: Wie Zeitpunkt-DB und Zeitraum-DB kombinieren?
04.07.2019 19:54:36
Daniel
Unsinn.
das geht schon mit Formeln, auch bei der Datenmenge, wenn man es richtig macht
1. die Kundentabelle muss nach LKW und Abfahrtszeit aufsteigend sortiert sein.
2. nach dem Sortieren in den Hilfsspalten E:F hinterlegen, welcher Zeilenbereich dem jeweiligen LKW zugeordnet ist
3. in den GPS-Daten in zwei Hilfsspalten zuerst ermitteln, in welchen Zeilenbereich die Daten des jeweiligen LKWs stehen.
4. dann mit dem SVerweis den jeweiligen Kunden auslesen.
https://www.herber.de/bbs/user/130734.xlsx
die Formeln sind etwas aufwendiger, schau dir mal das Beispiel an.
auch wenn die Formel länger aussehen, das ganze sollte auch für große Tabellen tauglich sein.
Gruß Daniel
Anzeige
...viel Spaß mit Sverweis bei knapp 20.000 Formeln
04.07.2019 20:26:39
Martin
@Daniel: Bestimmt geht es "irgendwie" mit Formeln, das habe ich auch nicht bestritten. Nur hat nicht jeder einen High-End-Rechner mit einer 4 GHz-Basistakt-CPU und insbesondere die Sverweis- und Index-Funktionen erfordern deutlich mehr Systemressourcen als die meisten anderen Formeln.
Glaub mir, ich bin nicht Erfinder dieser Tatsache:
http://www.makro-excel.de/2015/05/06/wissenswert-sverweis-funktion-und-makro-alternative
@Anton: Ich würde es begrüßen wenn du Daniels Lösungsweg mal testest und uns mitteilst wie lange die Berechnung der 19.000 Formeln unter Angabe deiner CPU-Spezifikation dauert.
Viele Grüße
Martin
Anzeige
AW: ...viel Spaß mit Sverweis bei knapp 20.000 Formeln
04.07.2019 22:44:59
Daniel
Hi Martin
du musst beim SVerweis immer darauf schauen, welche Variante verwendet wird in bezug auf den 4. Parameter.
Das Geschwindigkeitsverhältnis in der Berechnung ist wie zwischen Fußgänger (0/Falsch) und Düsenjäger (1/Wahr).
Bzw, du kannst die unterschiedlichen Arbeitsweisen der beiden Varianten auch selber ausprobieren.
Nimm dir ein klassisches Telefonbuch (also eins aus Papier) und denke dir einmal einen Namen und einmal eine Telefonnummer aus. Versuche dann beides zu finden. Wenn du nach dem Namen suchst, entspricht das dem SVerweis mit 1/Wahr (suche in sortierten Listen), wenn du nach der Nummer suchst dem SVerweis mit 0/Falsch (suche in unsortierten Listen)
oder wenn du es lieber Mathematisch magst:
Schreibe mal die Anzah der zu durchsuchenden Zeilen als 2er-Potzen auf, also in der Form x = 2^n .
dann ist die Suchzeit für den unsortieren SVerweis (0/Falsch) proportional x, die Suchzeit für den sortierten SVerweis (1/wahr) ist proportional n.
INDEX ist zunächst einmal nur ein Zellbezug und hat damit keinen Einfluss auf die Geschwindigkeit.
Gruß Daniel
Anzeige
AW: ...viel Spaß mit Sverweis bei knapp 20.000 Formeln
05.07.2019 16:15:49
Anton
Hallo Martin,
meine Antwort steht im Archivthread weiter oben...
VG Anton
Der Glauben an schnelles VBA ist NICHTWISSEN!
05.07.2019 08:36:47
EtoPHG
Hallo Martin,
Dein Hinweis "...hier VBA den Standard-Formeln vorzuziehen sei..." in Ehren.
Aber: In 99% aller XL Anwendungen sind Formel-Lösungen VBA-Lösungen leistungsmässig haushoch überlegen. So auch hier.
Wie Daniel richtig anmerkt, kann mit kleinen Eingriffen (z.B. Sortieren) die Performance bzgl. Verweisformeln und ggf. einfachen Hilfsspalten deutlich gesteigert werden. Es hängt also sehr viel von 'intelligenten' Datenstrukturen ab.
Bei Grössenordnungen von 20'000 Formeln muss ich immer Schmunzeln. Das sind keine Mengen sondern für heutige XL-Versionen ein Klacks.
Dein verlinkter VBA-Ansatz zeigt einen völlig dilettantischen Ersatz für einen SVERWEIS, der bzgl. Leistung nicht mal anssatzweise an eine Formellösung rankommt. (Ist ja im Titel der verlinkten Seite schon ersichtlich: "kleine Helferprogramme".)
Natürlich kann man mit 'falschen' Formelansätzen XL in die Knie zwingen, aber noch viel einfacher kann man das mit 'falschen' VBA-Lösungen (à la: Kann mir jemand bei dieser Schleife helfen?) Die neueren XL-Versionen erfahren immer bessere Codeoptimierungen bezgl. Array-Verarbeitung, die auch mit zig 100'000 Formeln rasend schnell werden. Siehe auch:"Excel Dynamic Arrays".
Mir ist unverständlich, dass selbsternannte XL-Pros immer noch an dem Glauben hängen "Mit VBA wird alles schneller!"
Gruess Hansueli
Anzeige

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige