Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1600to1604
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
Inhaltsverzeichnis

3 Formeln die bestimmte Inhalte auflisten sollen

3 Formeln die bestimmte Inhalte auflisten sollen
06.01.2018 15:57:38
Jenny
Hallo,
es würde mich freuen, wenn sich jemand etwas Zeit nimmt und mir hilft. Mit den 3 Problemen, ist leider mein Excel-Latein am Ende.
Habe eine sehr große Tabelle namens Gesamt (Bereich A1:RC17004), wobei bei weitem nicht jede Zeile soviele Einträge hat.
Ich suche eine Ausgabe der Zeilen, in denen in Spalte L eine Zahl kleiner gleich 30 steht, jedoch nicht alles was in diesen Zeilen steht, sondern:
in der ersten Spalte soll stehen, was in Spalte G steht, falls Spalte G leer ist, dann alternativ Spalte E.
In der zweiten Spalte soll stehen, was in Spalte B steht,
ab der 3. Spalte dann die Spalten M ff. bis Spalte RC.
Das ist ja hoffentlich noch einfach mit Index und der Bedingung L kleiner gleich 30, weil jetzt wird es kompliziert.
ALle Zeilen, in denen in Spalte L etwas anderes steht, aber der Text in Spalte G bzw. E identisch ist mit einer Zeile in der in Spalte L eine Zahl kleiner gleich 30 steht, sollen dann auch noch ausgegeben werden.
Ist sowas machbar? Rechenzeit ist mir egal, da ich soweieso werte einfügen will.
Bei Fragen bitte melden, ich bin leider ratlos, wie da eine sinnvolle nur 300 kb große Bsp Datei aussehen kann, wenn mir da jemand sagen kann wie seiner Meinung nach eine 300 KB große Datei aussieht, die ihm da anschaulich weiterhilft, werde ich sie natürlich noch nachliefern.
Gruß und danke
Jenny

36
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: möglich schon, allerdings ...
06.01.2018 17:41:22
...
Hallo Jenny,
... nur dann einigermaßen vertretbar, wenn nur ein Bruchteil der Zeilen Deinen Bedingung entsprechen. Denn angenommen es sind durchschnittlich nur 100 Werte (von max 460 möglichen) je Ergebniszeile und "nur" ca. 10% der vorhandenen Zeilen Deine zu listenden Ergebniszeilen, wären zumindest knapp 200.000 Formeln notwendig. Da wäre eine VBA-Lösung (aus der ich mich heraus halte) schon eine geeigneter.
Wie auch immer, eine Beispieldatei wäre schon sinnvoll.
Gruß Werner
.. , - ...
AW: möglich schon, allerdings ...
06.01.2018 17:51:11
Jenny
Hallo Werner,
sagt mir, auf welche Infos ich mich sinnvollerweise beschränken soll, dann lässt sich das mit der Datei sicher auch verwirklichen.
Also zur Info mit den Größenordungen, es sind 596 Zeilen mit Zahlen kleiner gleich 30 in Spalte L.
In diesen 596 Zeilen sehen 1680 Texte in den Spalten M bis RC, die restlichen Zellen sind leer.
Wobei sobald in einer Zeile eine Zelle (in den Spalten M ff.) leer ist, ist der Rest bis RC auch leer.
Bis RC geht es sowieso nur bei einer von all den Zeilen, bei den meisten anderen geht es vielleicht bis Spalte S oder T, weiter als das ist die Ausnahme.
Gruß
Jenny
Anzeige
AW: als Beispieldatei ausreichend wäre ...
06.01.2018 20:06:24
...
Hallo Jenny
... ein Auszug aus Deiner Tabelle mit max 30 Zeilen bzw. soviel Zeilen, dass min. 2 Ergebniszeilen je Bedingung ermittelt werden können. Und es reicht auch, Deine Spalten bis max Spalte O sowie eine grobe Einschätzung Deinerseits wie viele Ergebniszeilen denn max. zu erwarten sein könnte.
Gruß Werner
.. , - ...
AW: als Beispieldatei ausreichend wäre ...
07.01.2018 08:48:37
Jenny
Hallo Werner, ok, werde dann mal das Ganze erstellen. Zur Info schonmal es werden 1759 Zeilen insgesamt.
AW: als Beispieldatei ausreichend wäre ...
07.01.2018 09:35:13
Jenny
https://www.herber.de/bbs/user/118742.xlsx
Hallo Werner,
hier die Bsp. Datei.
Ich hoffe ich habs so umsetzen können wie du gemeint hast.
Zur Erklärung:
Ich habe um es etwas einfacher zu machen, eine Hilfsspalte D erzeugt, mit der Formel =WENN(G1="";E1;G1).
Die Zeilen 1 bis 10 sollen alle übernommen werden, weil in L1:L10 jeweils Zahlen kleiner gleich 30 stehen.
Die Zeilen 11 und 12 nicht, da L11 leer ist und in L12 eine Zahl größer 30 steht, nämlich 609.
Zeile 13 dagegen soll übernommen werden, da zwar 356 größer 30 ist, jedoch der Text in D13 auch in einer anderen Zeile steht, in der eine Zahl kleiner gleich 30 steht (Zeile 3 oder 4).
Wie bereits erwähnt zu den Ausmaßen, es sind aktuell (arbeite ja in der Zwischenzeit weiter an der Tabelle) 16874 Zeilen insgesamt, davon 588 mit Zahlen kleiner gleich 30 in Spalte L. Bei dem aktuellen Stand der Tabelle wären wie gesagt 1759 Zeilen insgesamt zu erwarten.
Gruß und vielen Dank
Jenny
Anzeige
AW: nachgefragt ...
07.01.2018 11:15:18
...
Hallo Jenny,
... Deine neuesten Angaben unterscheiden sich aber etwas von Deiner Eingangsfragestellung. Welche gilt? Sind die Daten ab Spalte M immer ohne Leerzellen gelistet oder können auch Leerzellen zwischen den Datenwerten vorhanden sein?
Gruß Werner
.. , - ...
AW: nachgefragt ...
07.01.2018 11:35:29
Jenny
Hallo Werner,
ich kann deinen Einwand jetzt nicht ganz nachvollziehen, vielleicht habe ich mich auch misverständlich ausgedrückt.
Eine Änderung habe ich ja gemacht, indem ich die Spalte E und G zusammengefasst habe zu Spalte D.
Zu den Texten ab Spalte M, da gibt es keine Leerzellen zwischendrin, sobald da eine Zelle leer ist, ist alles weitere in derselben Zeile auch leer.
Gruß Jenny
Anzeige
AW: nun dazu ...
07.01.2018 17:05:19
...
Hallo Jenny,
... da hatte ich heute Vormittag Deine Angaben zu Deiner Datei wohl zu flüchtig angesehen.
Mit einer Hilfsspalte hätte bei einer Formellösung und Deiner Datenmenge auch gearbeitet.
Meine Hilfsspalte würde jetzt z.B. in C1 so aussehen:

=WENN((ZÄHLENWENNS(D:D;D1;L:L;"1)+(L10);ZEILE();"")

Diese entsprechend nach unten kopiert ermittelt alle Zeilennummern, die zu übernehmen wären. Mittels INDEX() und KKLEINSTE() in Bezug auf die Hilfsspalte könntest Du nun alle Ergebniszellenwerte ermitteln. Aber wie eingangs bereits geschrieben würde ich bei Deiner Datenmenge auch eine VBA-Lösung den Vorzug geben. Aber wie aufgezeigt, wäre auch eine Formellösung prinzipiell möglich.
Gruß Werner
.. , - ...
Anzeige
AW: 3 Formeln die bestimmte Inhalte auflisten sollen
06.01.2018 19:31:54
onur
Über dropbox kannst du auch viel größere Dateien posten.
AW: 3 Formeln die bestimmte Inhalte auflisten sollen
07.01.2018 11:15:23
onur
Teste mal:
https://www.dropbox.com/s/bt6ck3m13deplh2/jenny.xlsm?dl=0
Der code bis "weiter" dient nur zur Erstellung der Musterdatei.
Die Datei ist nur so gross wegen der Musterdatei.
Der Code ist "winzig".
AW: 3 Formeln die bestimmte Inhalte auflisten sollen
07.01.2018 11:41:56
onur
Ignorierst du eigentlich meine Beiträge mit Absicht oder aus "Versehen"?
AW: 3 Formeln die bestimmte Inhalte auflisten sollen
07.01.2018 11:53:41
Jenny
Hallo Onur,
zugegebenermaßen habe ich mir erstmal die Frage von Werner angeschaut und darauf reagiert. Dann habe ich einen Anruf von meinen Eltern erhalten und werde mich jetzt dann um deinen Vorschlag kümmern.
Sorry ich wollte dich weder ignorieren noch verärgern.
Melde mich gleich nach dem Testen.
Gruß
Jenny
Anzeige
AW: 3 Formeln die bestimmte Inhalte auflisten sollen
07.01.2018 12:18:35
Jenny
so das Makro ist grad am ausführen.
Es gibt kleine aber feine Unterschiede zwischen deiner Tabelle und meiner.
Dank meiner mangelnden Kenntnisse weiß ich leider nicht ob diese für das Makro relevant sind.
1. Du hast Lücken in Spalte E gelassen, in meiner Tabelle ist Spalte E durchgängig gefüllt.
2. Dafür gibt es in meiner Tabelle Lücken in Spalte L, aber ich dachte, eine Überprüfung ob in Spalte L eine Zahl kleiner gleich 30 steht reicht aus. Was da ansonsten steht, bzw. ob überhaupt etwas da steht dachte ich sei irrelevant.
3. bei mir ist nicht der komplette Bereich M1:RC17007 gefüllt wie bei dir, insgesamt stehen da 47287 Texte.
Was jezt schwer für mich ist zu übrerprüfen, ob das Makro auch Zeilen übertragen hat, die in Spalte L etwas anderes als eine Zahl kleiner gleich 30 stehen hat.
Ansonsten wenn dem Makro das was ich gerade ansprach egal ist, sieht das recht gut aus.
Viele Grüße und vielen vielen Dank
Jenny
Anzeige
AW: 3 Formeln die bestimmte Inhalte auflisten sollen
07.01.2018 12:35:21
onur
"Was jezt schwer für mich ist zu übrerprüfen, ob das Makro auch Zeilen übertragen hat, die in Spalte L etwas anderes als eine Zahl kleiner gleich 30 stehen hat." - Einfach tabelle1 filtern (Spalte L 1)Spielt für den code keine rolle.
2)Ändere den Code wie folgt:

weiter:
z2 = 1
For z = 1 To 17004
If Cells(z, 12) And Cells(z, 12)  "" Then

3)Spielt auch keine Rolle, ob leer oder nicht, der code muss sie trotzdem durchsuchen.
AW: 3 Formeln die bestimmte Inhalte auflisten sollen
07.01.2018 13:30:39
Jenny
Hallo Onur,
ich kenne mich mit Makros kaum aus aber sofern mich mein Englisch nicht verlässt, heißt doch
If Cells(z, 12)  "" Then
wenn L kleiner gleich 30 und L ungleich leer. Ist L nicht automatisch ungleich leer wenn da eine Zahl kleiner gleich 30 drinsteht?
Gruß
Jenny
Anzeige
AW: 3 Formeln die bestimmte Inhalte auflisten sollen
07.01.2018 13:34:14
onur
Wenn eine zelle leer ist, ist sie laut Excel ebenso 

AW: 3 Formeln die bestimmte Inhalte auflisten sollen
07.01.2018 13:39:16
Jenny
Userbild
was hat es damit auf sich? das bekomme ich nach Ausführen des Makros bei der verbesserten Datei
Gruß
Jenny
AW: 3 Formeln die bestimmte Inhalte auflisten sollen
07.01.2018 13:40:57
onur
Das ist nur zur Zeitmessung (wie lange das makro braucht).
Wenn du die Zeile mit der msgbox deaktivierst, ist sie weg.
AW: 3 Formeln die bestimmte Inhalte auflisten sollen
07.01.2018 12:37:16
onur
Ach so ...
Button drücken nicht vergessen.
AW: 3 Formeln die bestimmte Inhalte auflisten sollen
07.01.2018 13:23:23
onur
https://www.dropbox.com/s/bt6ck3m13deplh2/jenny.xlsm?dl=0
Hier ist korrigierte/verbesserte Version - bis max 30000 Zeilen.
Für deine 17000 Zeilen braucht der code ca 78 sec.
Anzeige
AW: 3 Formeln die bestimmte Inhalte auflisten sollen
07.01.2018 13:40:19
Jenny
und was ich auch noch nicht so ganz verstehe ist, was hat es mit der Schaltfläche in Tabelle1 (2) auf sich?
AW: 3 Formeln die bestimmte Inhalte auflisten sollen
07.01.2018 13:45:05
onur
Button kannst du löschen, wird nicht benötigt.
Das ganze Blatt existiert nur, weil ich zu faul war, für dier Erstellung der Musterdatei alle Buchstaben von A bis Z zu tippen.
AW: 3 Formeln die bestimmte Inhalte auflisten sollen
07.01.2018 13:47:54
Jenny
aber das heißt ja, ich brauche in meiner Originaltabelle auch ein Blatt mit den Buchstaben von A bis Z oder?
AW: 3 Formeln die bestimmte Inhalte auflisten sollen
07.01.2018 13:50:01
onur
Nee, du hast ja ein Blatt mit den "Rohdaten", ich hingegen musste mir eins basteln.
Nur dafür wurde das gebraucht.
AW: 3 Formeln die bestimmte Inhalte auflisten sollen
07.01.2018 13:52:15
Jenny
aber du beziehst dich doch im Makro in den Zeilen

For z = 1 To 26
abc(z - 1) = Tabelle3.Cells(z, 1)
Next z
auf dieses Blatt oder nicht?
Anzeige
AW: 3 Formeln die bestimmte Inhalte auflisten sollen
07.01.2018 14:17:10
onur
Nochmal: Alles von "goto weiter" bis "weiter:" dient nur zur Erstellung der Beispieldatei.
Brauchst du nachher nicht, wenn du den code auf deine Datei überträgst.
AW: 3 Formeln die bestimmte Inhalte auflisten sollen
07.01.2018 15:08:05
onur
Ich habe mal deinen code vom code zur Erstellung des Musters getrennt.
https://www.dropbox.com/s/ytvslcobx5fm29z/jenny.xlsm?dl=0
AW: 3 Formeln die bestimmte Inhalte auflisten sollen
07.01.2018 17:01:45
Jenny
Hallo Onur,
habe den Code jetzt etwas umgeschrieben, damit es auf meine Tabelle passt:
Irgendwann bekomme ich beim Ausführen einen Fehler Laufzeitfehler 13: Typen unverträglich
und die Zeile
If Cells(z, 12)  "" Then

wird markiert.
Zu diesem Zeitpunkt wurden bereits 596 Zeilen in der Zieltabelle erstellt.
Eine Vermutung von mir liegt daran, dass aktuell am Ende der Tabelle ca. 50 Zeilen mit Text in Spalte L stehen, im normalfall wenn ich das Makro ausführe sind diese Zeilen weg, habs jetzt halt nur mal mittendrin während des Arbeitens gemacht, damit du schneller ne Antwort hast.
Sub Kleiner30()
Dim z, s, w, z2, max, z3
Tabelle12.Cells.ClearContents
z2 = 1
For z = 1 To 30000
If Tabelle1.Cells(z, 5)  "" Then
If Cells(z, 12)  "" Then
w = Tabelle1.Cells(z, 7)
Tabelle12.Cells(z2, 1) = w
If w = "" Then Tabelle12.Cells(z2, 1) = Tabelle1.Cells(z, 5)
Tabelle12.Cells(z2, 2) = Tabelle1.Cells(z, 2)
For s = 13 To 471
Tabelle12.Cells(z2, s - 10) = Tabelle1.Cells(z, s)
Next s
z2 = z2 + 1
End If
End If
Next z
max = z2
z3 = max
For z = 1 To 30000
If Tabelle1.Cells(z, 5)  "" Then
For z2 = 1 To max - 1
w = Tabelle12.Cells(z2, 1)
If w = Tabelle1.Cells(z, 5) Or w = Tabelle1.Cells(z, 7) Then
If Tabelle1.Cells(z, 12) > 30 Then
Tabelle12.Cells(z3, 1) = Tabelle1.Cells(z, 5)
Tabelle12.Cells(z3, 2) = Tabelle1.Cells(z, 2)
For s = 13 To 471
Tabelle12.Cells(z3, s - 10) = Tabelle1.Cells(z, s)
Next s
z3 = z3 + 1
End If
End If
Next z2
End If
Next z
End Sub

AW: 3 Formeln die bestimmte Inhalte auflisten sollen
07.01.2018 18:35:46
onur
Kein problem :

Dim z, s, w, z2, max, z3
Tabelle2.Cells.ClearContents
z2 = 1
For z = 1 To 30000
If Tabelle1.Cells(z, 5)  "" Then
w = Tabelle1.Cells(z, 12)
If Not IsNumeric(w) Then w = 0
If w  0 Then
usw
usw

AW: 3 Formeln die bestimmte Inhalte auflisten sollen
07.01.2018 19:11:31
Jenny
danke, aber jetzt hab ich denselben Fehler bei der Zeile
If Tabelle1.Cells(z, 12) > 30 Then

Gruß
Jenny
AW: 3 Formeln die bestimmte Inhalte auflisten sollen
07.01.2018 19:18:23
onur
Sorry, der taucht ja noch mal unten auf.

Next z
max = z2
z3 = max
For z = 1 To 30000
If Tabelle1.Cells(z, 5)  "" Then
For z2 = 1 To max - 1
w = Tabelle2.Cells(z2, 1)
If w = Tabelle1.Cells(z, 5) Or w = Tabelle1.Cells(z, 7) Then
 w = Tabelle1.Cells(z, 12)
If Not IsNumeric(w) Then w = 0
If w > 30 Then
usw

AW: 3 Formeln die bestimmte Inhalte auflisten sollen
08.01.2018 20:26:39
Jenny
Hallo Onur,
jezt sieht es besser aus.
Aber mal noch eine Frage, kann man das irgendwie auch so machen, dass die Namen der Tabellenblätter genommen werden, die ich den Blättern gegeben habe?
Wenn da jetzt z.B. im Makroeditor links oben Tabelle1 (Gesamt) steht, dass ich Gesamt als Bezeichnung nutzen kann, anstatt Tabelle1?
Hintergrund ist der, ich will das Zielblatt für die Daten nur einfügen, wenn ich das Makro brauche, damit die Tabelle nicht noch mehr überladen wird. Dann brauche ich nur die Tabelle umbenennen, wie ich sie im Makro genannt habe und fertig.
Gruß
Jenny
AW: 3 Formeln die bestimmte Inhalte auflisten sollen
09.01.2018 11:40:53
onur
Du kannst entweder den internen Namen des Blattes nehmen:
Tabelle1.Cells(x,y)
oder den von dir vergebenen Namen des Blattes:
Sheets("Gesamt").Cells(x,y)
Geht beides.
"ich will das Zielblatt für die Daten nur einfügen, wenn ich das Makro brauche, damit die Tabelle nicht noch mehr überladen wird" ist eigentlich Quatsch, da ein leeres Blatt nicht zur Überladung führt.
AW: 3 Formeln die bestimmte Inhalte auflisten sollen
09.01.2018 15:04:42
Jenny
dann hab ich mich falsch ausgedrückt mit Überladen war die Übersichtlichkeit gemeint, damit angefangen dass ohne alle Tabellenblattnamen auf meinem Laptopmonitor angezeigt werden, wenn noch ein Blatt hinzukommt muss ich anfangen, mit den Pfeilen hin und herzuswitchen, damit ich an alle Blätter komme.
AW: 3 Formeln die bestimmte Inhalte auflisten sollen
09.01.2018 15:07:27
onur
Kein problem!
Und - läuft der Code ansonsten problemlos?
AW: 3 Formeln die bestimmte Inhalte auflisten sollen
09.01.2018 16:06:21
Jenny
im Moment soweit ja,
vielen Dank für die viele Mühe. Ich weiß ja, dass das keine Arbeit von 5 Minuten ist. Danke dass du dir in deiner Freizeit so viel Zeit genommen hast.
Falls noch was ist melde ich mich nochmal.
Gruß
Jenny

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige