Office: Datenexport wenn Kriterium Datum gleich, oder kleiner als erfüllt ist...

Hallo zusammen,

nachdem ich lange zeit mit einer Handvoll Formeln ausgekommen bin, gegebenenfalls diese ein wenig modifiziert habe, stehe ich jetzt auf dem Schlauch...(bzw. bin nicht so Formelaffin).
Ich habe einmal eine Mappe mit hochgeladen. In der Bestandsauskunft stehen die Rohdaten. Ich würde gerne in dem zweiten Blatt das Suchkriterium Datum nehmen und dann in der Bestandsauskunft alle PID' s in das Tabellenblatt Lager kopieren deren MHD in der Spalte MHD gleich, oder kleiner ist. Die restlichen Daten ziehe ich über den SVerweis.
Ich hatte schon in der ersten Zeile den XVerweis versucht, komme allerdings auch hier nicht mit weiter.
Kann mir jemand helfen?

VG Thomas

 

Hallo Thomas,

Benutze Autofilter und Kopiere den sichtbaren Bereich, Start, Bearbeiten, Suchen..., Gehe zu, Inhalte, sichtbare Zellen.
Excel hat auch einen Makrorecorder.

 

Hallo Thomas,

alternativ zum Autofilter mit und ohne VBA kannst Du hierfür auch die Power Query (PQ) Funktionalität nutzen.

 

Hallo,

wenn du Excel365/Excel2021 hast (ich nehme das an, weil du auch XVERWEIS hast)
folgende Formel in Spalte A, zB. Zelle A2:
=FILTER(Bestandsauskunft!$A$2:$A$24998;Bestandsauskunft!$V$2:$V$24998<=$T$2;"Leer")
in Zelle B2:
=WENN($A2;SVERWEIS($A2;Bestandsauskunft!$A$2:$AAA$24998;{2.5.6.7.12.16.19.20.22.30.33.34.39.40};FALSCH);"")
und diese hinunterkopieren. Die Formeln in den Spalten C:O müssen entfernt werden.(sonst #ÜBERLAUF!-Fehler).
Die Formel in Spalte P kann bleiben.

Falls du doch ein älteres Excel haben solltest, in Zelle A2 folgende Formel:
=INDEX(Bestandsauskunft!$A$2:$A$24998;AGGREGAT(15;6;(ZEILE($A$2:$A$24998)-1) / (Bestandsauskunft!$V$2:$V$24998<=$T$2); ZEILE(A1)))
und diese hinunterkopieren. Die Formel in Zelle B2 wie vorhin.

Falls der Zellbereich im Arbeitsblatt "Bestandsauskunft" doch wesentlich kleiner als A2:AAA24998 sein sollte, würde ich empfehlen, diesen entsprechend zu verkleinern. Insbesondere die Spaltenanzahl wird verkleinerbar sein, da die Formeln maximal bis Spalte 40 = Spalte AN gehen. Die Geschwindigkeit der Formeln erhöhen sich dann wesentlich.

 

Hallo,

vielen Dank für die Tipps. VBA scheidet für mich leider aus, da meine IT keinerlei Makro' s mehr zulässt. Power Query kenne ich mich leider bis dato überhaupt nicht mit aus. Aus diesen Gründen habe ich mich für den dritten Vorschlag entschieden, welcher mir schon einmal weiter hilft. Allerdings habe ich das Problem, das wenn die Datenmenge zu groß wird und ich das "Suchdatum" ändere, Excel hier sehr lange am berechnen ist (Übergrößenänderung).
Kann ich das irgendwie unterbinden?

VG Thomas

 

Hi,

wer solche Formeln in 1000 Zeilen und 15 Spalten vorhält, braucht sich nicht über lange Rechenzeiten wundern...
=WENN($A2="";"";WENN(ISTNV(SVERWEIS($A2;Bestandsauskunft!$A$2:$AAA$24998;2;FALSCH));"";SVERWEIS($A2;Bestandsauskunft!$A$2:$AAA$24998;2;FALSCH)))
Zunächst einmal könntest du deinen Rechenaufwand deutlich verringern, indem du den SVERWEIS nur einmal ausführst. Dazu kann man WENNFEHLER statt ISTNV verwenden:
=WENN($A2="";"";WENNFEHLER(SVERWEIS($A2;Bestandsauskunft!$A$2:$AAA$24998;2;FALSCH);""))
Schon hast du bis zur Hälfte deiner Rechenzeit gespart.
Dann sollte man beim Ziehen von mehreren Daten aus derselben Zeile die Zeile nur einmal berechnen. Dazu nimmt man eine Hilfszelle und berechnet dort die Zeilennummer mit VERGLEICH. Das eigentliche Ergebnis wird dann mit INDEX und der Hilfszelle ermittelt. Nehmen wir als Hilfszelle z.B. die Zelle X2. Dann kommt dort diese Formel rein
=XVERGLEICH($A2;Bestandsauskunft!$A$2:$A$24998)
In B2 kommt dann die Formel
=INDEX($X2;Bestandsauskunft!B$2:B$24998)
In den weiteren Spalten musst du das B$2:B§xxx anpassen, damit du die Daten aus den gewünschten Spalten erhältst. So wie du bisher die Spalte beim SVERWEIS anpassen musstest. Durch diesen Trick sinkt deine Rechenzeit auf fast 1/15 ab.

Wenn du jetzt noch deine Bestandsliste nach der PID sortierst, dann kannst du den XVERGLEICH binär (=sortiert) suchen lassen. Das braucht bei 25.000 Zeilen max 15 Vergleiche, während die herkömmliche Suche im Mittel die halbe Zeilenzahl (=12.500) an Vergleichen braucht. Somit brauchen diese dann nur noch etwas über 1/1.000 der bisherigen Zeit. Die Frage ist halt, ob das Sortieren möglich ist...

Generell gilt jedoch:
- keine Formeln auf Vorrat!
- zu durchsuchende Bereiche so klein wie möglich halten
Beide Forderungen können ganz einfach durch intelligente Tabellen (Strg-T-Tabellen) erreicht werden.


So, aber jetzt kommt der Clou: Du kannst deine Liste mit einer einzigen Formel erhalten:
=WENNFEHLER(SPALTENWAHL(FILTER(Bestandsauskunft!$A$2:$AN$24998;(Bestandsauskunft!$V$2:$V$24998<=$T$2)*(Bestandsauskunft!$V$2:$V$24998>0));1;2;5;6;7;12;16;19;20;22;30;33;34;39;40);"Leer")