Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Spalten nach Zahlenkolonnen durchsuchen

Spalten nach Zahlenkolonnen durchsuchen
13.06.2021 14:43:06
Bernd
Hallo Forum,
ich stehe vor folgendem Problem/Aufgabe.
Ich möchte in beigefügter Beispieldatei das Tabellenblatt "Kum_GundV" durchsuchen nach Zahlenkolonnen bzw. Zahlenbereichen in den Spalten, die ungleich null sind.
Die Suche startet ab der Spalte L (Spalte 12) und ich möchte von Hand die Anzahl der Spalten angeben, die der Reihe nach von rechts nach links ab der Spalte L von oben nach unten durchsucht werden sollen.
Die Suche soll immer in der Zeile 11 einer Spalte von oben nach unten starten bis zur letzten Zeile, in der eine Zahl steht (im Beispiel stehen die letzten Zahlen in der Zeile 2510).
Es sollen die Zahlenbereiche gesucht und kopiert werden, die ungleich dem Wert null sind. Dabei spielt es keine Rolle, ob die Zahlenwerte größer oder kleiner als 0 sind.
Der Zahlenbereich beginnt immer, wenn eine gefundene Zahl größer oder kleiner als null ist und endet, sobald nach dem Zahlenbereich wieder ein Zellwert mit dem Wert 0 gefunden wird.
Diese gefundenen Zahlenbereiche sollen in das Tabellenblatt "Einzeln" ab der Zeile 12 in Zeilen transponiert und kopiert werden wie in der Beispieldatei gezeigt.
Hier habe ich die Zahlenbereiche der Spalte L (Spalte 12) und teilweise Spalte M (Spalte 13) manuell durchsucht, in die Zwischenablage kopiert und transponiert in die Zeilen des Tabellenblattes "Einzeln" eingefügt.
Ich vermute, dass man dies am ehesten mit einem Array löst, aber ich kenne mich mit Arrays nicht aus. Andere Lösungen sind natürlich auch willkommen.
Eine zusätzliche sehr wünschenswerte Optionwäre für mich: wenn in der letzten Zelle (im Beispiel Zeile 2510) einer Spalte ein Zahlenwert steht, der ungleich 0 ist, dann soll dieser letzte Zahlenbereich in der jeweiligen Spalte nicht in das Tabellenblatt "Einzeln" kopiert werden.
Hier ist die wichtige Beispieldatei
https://www.herber.de/bbs/user/146592.xlsx
Wie geht man so etwas an?
Besten Dank vorab an die Excel-Cracks, die so etwas mit vba programmieren können!
Bernd

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zielvorgaben nur teilweise nachvollziehbar ...
13.06.2021 20:11:45
neopa
Hallo Bernd,
... und zwar in Zeile 12 bis BD12. Danach müssten noch weitere Werte bis -3,796% in BP12 kommen. Gar nicht nachvollziehbar sind Deine Vorgaben ab Zeile 13.
So müsste mE in A13 ein Wert 1,599% stehen und aufhören mit -6,996% in AX13. Oder?
Wie viele Spalten sind denn insgesamt max auszuwerten? Für die im Beispiel angegebenen Spalten L:S würde eine Formel reichen.
Gruß Werner
.. , - ...
AW: Zielvorgaben nur teilweise nachvollziehbar ...
14.06.2021 09:28:36
Bernd
Hallo Werner,
Es sind 1000 Spalten. Ich habe nun im Tabellenblatt "Kum_GundV" die Spalte L gefiltert, um das nochmals zu überprüfen.
die Zeile 716 in Spalte L enthält den Wert 0,073% und die Zahlenkolonne endet in Zeile 771 mit dem Wert 3,582%.
Dieser Bereich soll/wird nun in das Tabellenblatt "Einzeln" kopiert in Zeile 12 und dann wird in Spalte L nach unten weitergesucht und man findet in Zeile 1694 dann den nächsten Wert 0,215% und die Zahlenkolonne geht bis zur Zeile 1705 mit dem Wert -3,796%. Diese Zahlenkolonne wird dann in Zeile 13 kopiert.
Der nächste Wert im Spalte L kommt dann in Zeile 2502 mit dem Wert 0,786% und die Zahlenkolonne endet in Zeile 2510 mit dem Wert 4,233%.
Diese letzte Zahlenkolonne sollte (optional auswählbar) nicht kopiert werden, da der Wert in Zeile 2510 ungleich null ist. In der Beispieldatei habe ich es aber in Zeile 14 kopiert, das stimmt (weil es ja nur eine Option sein soll und ich nicht weiß, wie aufwändig das zu programmieren ist.).
Insofern denke ich, ich habe keinen Fehler gemacht.
Ist es jetzt klarer, was ich will? Danke dir und Gruß, Bernd
P.S. mit der Funktion aggregat komme ich hier nicht weiter, weil ich ja die verschiedenen Zahlenkolonnen in einer Spalte in das Tabellenblatt "Einzeln" in verschiedene/mehrere Zeilen aufteilen will, um jede Zahlenkolonne sozusagen einzeln auswerten zu können....
Anzeige
AW: Zielvorgaben nur teilweise nachvollziehbar ...
14.06.2021 10:23:09
Werner
Hallo,
teste mal:

Sub Schaltfläche1_Klicken()
Dim loLetzte As Long, loBis As Long
Dim loSpalte As Long, raFund As Range
Application.ScreenUpdating = False
With Worksheets("Kum_GundV")
loLetzte = .Cells(.Rows.Count, "L").End(xlUp).Row
loSpalte = .Cells(10, .Columns.Count).End(xlToLeft).Column
If .Cells(loLetzte, "L") = 0 Then
loBis = loLetzte
Else
Set raFund = .Columns("L").Find(what:="0.000%", LookIn:=xlValues, _
lookat:=xlWhole, searchdirection:=xlPrevious)
If Not raFund Is Nothing Then loBis = raFund.Row
End If
.Range(.Cells(10, 12), .Cells(loBis, loSpalte)).AutoFilter Field:=1, Criteria1:="0"
.Range("L11:L" & loBis).SpecialCells(xlCellTypeVisible).Copy
Worksheets("Einzeln").Range("A12").PasteSpecial Paste:=xlPasteAll, Transpose:=True
Application.CutCopyMode = False
.Range("L10").AutoFilter
End With
End Sub
Aktuell wird mal nur die Spalte L entsprechend kopiert. Prüfe mal, ob das Ergebnis so stimmt.
Wenn ja, dann müsste man da nur noch eine Schleife drumrum bauen.
Kann es denn sein, dass im entsprechenden Spaltenbereich auch Spalten vorhanden sind, in denen ausschließlich Nullwerte vorhanden sind?
Gruß Werner
Anzeige
AW: Zielvorgaben nur teilweise nachvollziehbar ...
14.06.2021 13:20:25
Bernd
Hallo Werner,
das Makro sucht leider nur die erste Zahlenkolonne in der Spalte L.
Wenn du die Spalte L filterst ("Leere" und "0,000%" rausfiltern), so wirst du sehen, dass in der Spalte L drei Zahlenkolonnen stehen:
1.Zahlenkolonne in Spalte L von Zeile 716 bis Zeile 771
2.Zahlenkolonne in Spalte L von Zeile 1694 bis Zeile 1705
3. Zahlenkolonne von Spalte L von Zeile 2502 bis Zeile 2510
Es können in jeder Spalte unterschiedlich viele und lange Zahlenkolonnen vorkommen; es kann auch sein, dass alle Werte einer Spalte 0,000% enthält.
Optional: geht die letzte Zahlenkolonne bis zur Zelle 2510, d.h. wenn der Wert der Zelle 2510 ungleich 0,000% ist, dann sollte diese Zahlenkolonne nicht in das Tabellenblatt "Einzeln" kopiert werden.
Das Trennungskriterium der Zahlenkolonnen ist sozusagen der Wert 0,000%. Sobald dieser in der Spalte auftaucht, ist die Zahlenkolonne "beendet".
Die einzelnen Zahlenkolonnen sollen in das Tabellenblatt "Einzeln" in eine Zeile transformiert werden.
Da die Spalte L drei Zahlenkolonnen enthält, sollen diese Zahlenkolonnen in die Zeilen 12,13 und 14 kopiert werden.
Ich hoffe, jetzt ist es klarer?
herzlichen Dank vorab!
Bernd
Anzeige
AW: Zielvorgaben nur teilweise nachvollziehbar ...
14.06.2021 13:33:13
Werner
Hallo,
bei mir nicht, siehe deine Beispielmappe.
Die Spalte M wird nicht übertragen, dort habe ich zu Testzwecken mal lauter 0 Werte eingetragen.
https://www.herber.de/bbs/user/146604.xlsm
Gruß Werner
AW: Zielvorgaben nur teilweise nachvollziehbar ...
14.06.2021 16:25:00
Bernd
Hallo Werner,
dein Marko funktioniert soweit. Das ist die gute Nachricht.
Jetzt fehlt nur noch eine Kleinigkeit (hoffe ich zumindest, dass es eine Kleinigkeit ist).
Filter bitte mal die Spalte L in deiner geposteten Beispieldatei 146604 und blende "(Leere)" und "0,000%" aus.
Du schreibst mit der Formel alle Zahlen der ersten und zweiten Zahlenkolonne der Spalte L in die Zeile 12 des Tabellenblattes "Einzeln".
Ich hätte aber gerne, dass die einzelnen Zahlenkolonnen in den Spalten in einzelne Zeilen im Tabellenblatt "Einzeln" geschrieben werden.
Um also bei Spalte L als Beispiel zu bleiben:
Die1. Zahlenkolonne in Spalte L von Zeile 716 (0,073%) bis Zeile 771(3,582%) soll nach Tabellenblatt "Einzeln" kopiert und transformiert werden und zwar in Zeile 12 des Tabellenblattes "Einzeln".
Die 2.Zahlenkolonne in Spalte L von Zeile 1694 (0,215%) bis Zeile 1705 (-3,796%) soll dann in die Zeile 13 des Tabellenblatt "Einzeln" kopiert und transformiert werden.
Die 3.Zahlenkolonne(0,786%) in Spalte L von Zeile 2502 bis Zeile 2510 (4,233%), die in Zeile 14 des Tabellenblatt "Einzeln" kopiert und transformiert werden sollte, wird nicht kopiert, da diese Zahlenkolonne nicht endet (d.h. Zeile 2510 ist mit einem Wert unglich 0,000% belegt). Das ist richtig!
Da nun in Spalte M alle Werte 0,000% sind, wird hier keine Zahlenkolonne kopiert.
Die nächste Zahlenkolonne, die dann in Spalte N gefunden wird, soll dann in Zeile 14 des Tabellenblatt "Einzeln" kopiert und transformiert werden.
Da nur ein Zahlenkolonne in Spalte N vorhanden ist, wird diese richtig kopiert.
In Spalte O sind dann wieder drei Zahlenkolonnen vorhanden (Zeile 711 bis 762), (Zeile 1005 bis 1021) und (Zeile 2382 bis 2423).
Diese müssen dann in die Zeilen 15, 16 und 17 des Tabellenblattes "Einzeln" kopiert werden usw. für die nächsten Spalten...
Hoffentlich habe ich es jetzt richtig erklärt...
Danke für deine Mühe!!!
Bernd
Anzeige
AW: für die Beispieldatei reicht eine Formel ...
14.06.2021 10:28:18
neopa
Hallo Bern,
... und zwar schon entgegen Deinen P.S . mit AGGREGAT() ;-) diese in der Formel auch gleich zweimal eingebunden.
Allerdings bist Du auf meine Hinweise zu den von mir festgestellten Abweichungen Deiner Vorgabe-Zielwerte noch nicht eingegangen.
Meine Lösung für die 8 Spalten ist mit der Formel (entsprechend weit nach rechts und unten kopiert) auch noch kein Problem, obwohl für die Spalte S insgesamt 167 Werte gelistet werden. Wenn Deine 1000 Spalten nur selten gelistet werden müssen, könnte diese auch für die 1000 auszuwertenden Spalten noch möglich sein. Wäre das für Dich eine Option?
Ich bin jetzt allerdings erst einmal bis am späteren Nachmittag offline.
Gruß Werner
.. , - ...
Anzeige
AW: Formel vergessen einzukopieren ...
14.06.2021 10:31:39
neopa
Hallo nochmal,
... hier dies nachgeholt:

=WENNFEHLER(INDEX(Kum_GundV!$A:$ALL;AGGREGAT(15;6;ZEILE(A$11:A$2510) /(INDEX(Kum_GundV!$A$11:$ALL$2510;;ZEILE())0%)/(ZEILE(A$11:A$2510)&ltMIN(AGGREGAT(14;6; ZEILE(A$11:A$2510)/(INDEX(Kum_GundV!$A$11:$ALL$2511;;ZEILE())=0);1);2510));SPALTE(A12));ZEILE());"")

Diese wie geschrieben weit genug nach rechts und unten ziehend kopieren.
Gruß Werner
.. , - ...
AW: Formel vergessen einzukopieren ...
14.06.2021 13:03:32
Bernd
Hallo Werner,
ich habe die Formel mit 2x Aggregat in das Tabellenblatt "einzeln" eingetragen.
Das Problem ist, dass nur die erste Zahlenkolonne in jeder Spalte so gezeigt wird.
Es gibt aber viele Spalten mit mehreren Zahlenkolonnnen, die durch 0,00% getrennt sind, in einer Spalte.
Ebenso kann es Spalten geben, die nur Werte von 0,00% anzeigen (danach hast du ja gefragt an anderer Stelle).
Wenn du die Spalte L fillterst (Leere rausfilter, 0,00% rausfiltern) dann sieht man die Zahlenkolonnen, da die Zeilennummer an manchen Stellen der gefilterten Spalte "springen" wie bereits erläutert. Daher kann ich mit der Formel leider nichts anfangen.
Soviel schon mal zu der Formel...das Makro muss ich mir in Ruhe heute Abend anschauen...! Ich melde mich also in jedem Fall nochmals
Danke schon mal and dieser Stelle
Bernd
Anzeige
AW: dem ist nicht so, denn ...
14.06.2021 15:51:55
neopa
Hallo Bernd,
... wenn Du denn meine Formel wirklich in Einzeln!A12 eingesetzt hättest und diese weit genug nach rechts und unten ziehend kopiert, dann erhältst Du damit in Zeile 12 exakt Deine Vorgabewerte Deiner Beispieldatei. In den darunterliegenden Zeilen die Werte erhältst Du damit die wirklich in Deiner Beispieldatei vorhanden gelistet.
Gruß Werner
.. , - ...
AW: Formel vergessen einzukopieren ...
14.06.2021 16:36:37
Bernd
Hallo Werner,
ich habe deine Formel in A12 in Tabellenblatt "Einzeln" kopiert in die Beispieldatei.
Deine Formel endet in Zeile 12 nicht im Feld BD12 (Wert 3,582%), sondern gibt weitere Werte nach rechts aus.
Genaus diese Werte sollen aber in die nächste Zeile 13, da die Zahlenkolonne immer endet (oder enden soll), wenn wieder eine Wert 0,000% in einer Spalte erscheint.
Daher stimmen dann auch die Werte in A13 (Zeile 13) nicht überein, da hier bereits die gesamten Zahlenkolonne der Spalte M aufgelistet werden in Zeile 13 nacheinander.
Deine Formel gibt also immer alle Zahlen ungleich 0,000% in einer Zeile aus, obwohl ich eigentlich gerne hätte, dass hier nach Zahlenkolonnen unterschieden wird und diese Zahlenkolonnen immer enden, wenn wieder ein Wert 0,000% nach einer Zahlenkolonne mit Zahlen ungleich 0 rauskommt.
Die Anzahl der ausgegebenen Zeilen in Tabellenblatt "Einzlen" ist also definitiv deutlich größer als die Anzahl der Spalten in Tabellenblatt Kum_GundV!
...Ich hoffe, ich habe es jetzt besser erklärt...es liegt halt meißtens am Sender (also an mir)...
Bernd
Anzeige
AW: wenn dem so sein soll ... thread offen ...
14.06.2021 17:56:01
neopa
Hallo Bernd,
... denn dann wäre eine entsprechend angepasste/erweiterte Formellösung nur vertretbar, wenn wie in der Beispieldatei max 8 Spalten auszuwerten wären. Da es aber 1000 werden können, kommt eine solche nicht mehr in Frage. Deshalb stelle ich den thread wieder offen.
Gruß Werner
.. , - ...

303 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige