Excel - automatisch ermitteln, wie weit eine Formel ziehen?
Hallo zusammen,
ich habe eine Excel (2016) Tabelle, in der in Tabelle 1!Spalte A Werte stehen. Manchmal nur zehn, manchmal mehrere Hundert.
In Tabelle 2!Spalte A habe ich eine Berechnung stehen, die sich direkt auf die Zellen der ersten Tabelle bezieht.
Da sich nun die Anzahl der Rohwerte ständig ändert, muss ich bei der Berechnung die Formel jedes mal so weit nach unten ziehen wie ich Werte habe. Kann man dies umgehen, indem Excel automatisch die Anzahl der Werte in Tabelle 1!Spalte A ermittelt und in Tabelle 2!Spalte A die Berechnung automatisch so weit nach unten zieht wie nötig?
Vielen Dank für Eure Antworten.
LG
Tabelle 1!Spalte A ..... Tabelle 2!Spalte A
1 ..................................=Tabelle 1!A110
2 ..................................=Tabelle 1!A210
3 ..................................=Tabelle 1!A310
4 ..................................=Tabelle 1!A410
5 ..................................=Tabelle 1!A510
6 ..................................=Tabelle 1!A610
4 Antworten
wenn es nichts ausmacht, dass die Reihenfolge der gefüllten Zeilen umgedreht wird, geht es relativ einfach mit dieser Matrixformel (bezieht sich auf mein Blatt namens xl, musst Du durch Deine Tabelle1 oder sonstigen Blattnamen ersetzen)
in E1: {=INDEX(xl!A$1:A$15;KGRÖSSTE(ISTZAHL(xl!A$1:A$15)*ZEILE(xl!A$1:A$15);ZEILEN(E$1:E1)))}
runterziehen, bis nur noch Nullen (aus Leerzellen oder 0) oder -ganz unten- Fehler (aus Texten) folgen
Matrixformel! d.h., {Klammern} NICHT miteingeben, stattdessen die Formel anstatt mit Enter mit Strg+Umschalt+Enter eingeben, nach jeder Zellbearbeitung wieder! (ich empfehle die rechten Tasten, die sind nah beieinander)
Willst Du die Reihenfolge beibehalten, ginge das erwas komplizierter auch, aber da müsste ich mich selbst erst wieder reinarbeiten. Nur auf Anforderung!
Die erste Zelle kannst Du verschieben, wohin Du sie haben willst, also wo die Liste starten soll. Aber dann erst danach runterkopieren (und immer wieder {} prüfen!)
so langsam kriege ich den Eindruck, Du brauchst das für einen VBA-Kurs. Aber ich spiele mal mit.
Voraussetzung: 1. Zeile ist leer oder irrelevant, Liste beginnt jedenfalls in AA2, (sollte weit genug rechts draussen sein)
ggf vorher Leerzeile vor A1 einfügen
- Sub ComprToFilled()
- Dim C 'das as Range kannst Du Dir sparen, macht xl selber, belastet dann ganz geringfügig den arbeitsspeicher. Die Deklaration könnte aber in vielen Fällen eine große Hilfe bei der Fehlersuche sein!
- For each C in Range("A:A")
- if c.value <> "" then Range("AA9999").end(xlup).offset(1,0) = C.value
- 'altern: if isempty(c), auch ist das .value nur der Klarheit wegen dran (ist Vorgabeargument)
- next
- end sub
ich denke, das ist selbsterklärend?
Es könnte sein, dass
For each C in Range("A:A").specialcells(xlvalues)
oder ...Range("A:A").cells.specialcells(xlvalues) '??
kann ich mir nie merken
eine erhebliche Beschleunigung bewirkt.
eine weitere Beschleunigung erreicht man häufig mit
Application.screenupdating = false 'am Anfang des Subs
dann aber unbedingt vor next
on error goto Errorhandler
und vor end sub (der Doppelpunkt charakterisiert den Sprung-Zeilennamen, danach darf nichts mehr kommen in der Zeile)
Errorhandler:
application.screenupdating = true
Trockenübung, probiers selbst aus
VBA wird bei soviel Zellen tatsächlich lang brauchen. Du kannst aber mischen_ Matrixformel und kopieren mit dem Kriterium solange >0, das zu Wert umsetzen und i'wo platzieren.
Wenn ich umdrehen sage, dann wird die Reihenfolge exakt umgedreht, und wenn man das zweimal macht, stimmt sie wieder exakt (vllt Missverständnis: es wird nicht nach Wertgröße sortiert, sondern nach Zeile, solange Wert >0 - das Kriterium kann man vorgeben)
Hast Du negative oder zu erfassende 0-Werte? Mit nur positiven Werten wärs am einfaxhsten!
Wenn du mit so einer Lösung einverstanden bist, melde Dich nochmal!
Den relevanten Bereich kann man übrigens finden mit einem einfachen Zählenwenn(Spalte;">0")
Hallo Iamiam,
ah okay. Lohnt sich das mit dem kopieren bei mehreren hundert Werten?
ach okay, das hatte ich in der Tat missverstanden.
Es handelt sich bei den Rohdaten nur um positive Werte, wenn ich aber Normierungen mache, werden es auch negative und Null- Werte.
Also wenn Du dafür eine Lösung hättest, wäre das super.
LG
Bei auch nur halbwegs leistungsfähigem Rechner lohnt es.
Normierung: die machst Du ja sicher nicht in der Originalspalte A, und auf die bezieht sich der KGrösste-Teil der Formel. Zum Auslesen der normierten Daten brauchst Du nur im Index-Teil die Spalte anpassen.
(ich hab das zwar nur mit einem Demofeld mit wenigen Zeilen gemacht, aber ab 2 GB rbeitsspeicher sollte das auch mit 6000 Werten funktionieren. Die Formel kopierst Du, indem Du aufs Ausfüllkästchen doppelklickst: kopiert die Formel bis zur ersten Unterbrechung (oder auch weiter, da verhalten sich xl und LO unterschiedlich und ich vergesse immer wieder, wer wann wo...)
Hallo Iamiam,
ich hatte schon mal kommentiert, allerdings scheint der Kommentar nicht aufzutauchen:
Danke nochmal für deine Antwort.
Geht das denn mit dem mischen und der Matrixformel, ohne dass ich da manuell ziehen muss? Weil bei einer Matrixformel muss ich doch ständig strg + shift + enter drücken, oder?
Gut, einmal hin und her wäre ja gar kein Problem.
Ich habe sowohl negative Werte als auch Nullwerte.
LG
Du hast geschrieben, dass Du erst NACH DEM NORMIEREN negative und Nullwerte hast. Die Komprimierung erfolgt aber nach Spalte A, und die behältst Du mit ihren unnormierten Werten doch sicher bei??
Normierungsformeln würden überall Nullen setzen, es sei denn, Du setzt über einen Wenn-Befehl bei Nichteintrag Text rein (zB "_"). Dann kann man das wieder differenzieren. Dazu kenne ich aber Deine Datei zu wenig.
Das Kopieren einer Matrixformel (bzw einerGruppe davon) braucht kein neues Strg+Shift+Enter, egal ob Du per Copy-paste oder per Ausfüllkästchen vervielfältigst (letzteres mit Doppelklick füllt über die gesamte Listenlänge). Solange Du die Zelle nicht öffnest, bleibt sie unverändert. Öffnest du sie versehentlich, kannst Du auch mit Esc schadlos wieder rausgehen.
Da ich Deinen Hintergrund und den der Datei zuwenig kenne, kann ich Dir nicht gezielt weiterhelfen!
Näheres zu Matrixformeln: gugl mal nach Peter Haserodt, Excel, er erklärt sehr gut.
Hallo Iamiam,
also das klingt ganz okay - DIE Rechenleistung bekomm ich schon irgendwie hin.
Könntest Du mir da mit ein paar CodeSchnipseln aushelfen oder mir sagen, wie ich ans Ziel komme?
LG
wo ist denn mein Makro geblieben?. Egal, war ohnehin Änderungsbedürftig. Also nicht ein anderes (falls das noch mal auftaucht), sondern dieses mit dem eingegrenzten Bereich:
Voraussetzung: 1. Zeile ist leer oder irrelevant, Liste beginnt jedenfalls in AA2
- Sub ComprToFilled()
- Dim C
- For each C in Range("A1:A9999") 'also max. 9998 Werte, ggf verkleinern, macht das dann schneller
- if c.value <> "" then Range("AA9999").end(xlup).offset(1,0) = C.value 'alternat: if isempty(c), auch ist das ,value nur der Klarheit wegen dran (ist Vorgabeargument)
- next
- end sub
Es könnte sein, dass
For each C in Range("A:A").specialcells(xlvalues)
eine erhebliche Beschleunigung bewirkt.
eine weitere Beschleunigung erreicht man häufig mit
- Application.screenupdating = false 'am Anfang des Subs
dann aber unbedingt vor next
- on error goto Errorhandler
und vor end sub (der Doppelpunkt charakterisiert den Sprung-Zeilennamen, danach darf nichts mehr kommen in der Zeile)
- Errorhandler:
- application.screenupdating = true
Trockenübung, probiers selbst aus (müsste ich auch machen)
Hallo lamiam und Oubyi,
vielen Dank für Eure Antworten.
Selbstverständlich soll das A1*10 heißen - Flüchtigkeitsfehler, sorry.
Leider ist die Matrixformel keine Option, da es zeitlich abhängige Messwerte sind und wenn ich da die Reihenfolge ändere, käme Schmarrn bei raus.
Auch das runterziehen mit dem "Nichts-Anzeigen" ist nicht das, was ich suche. Der Hintergedanke neben der vereinfachten Auswertung ist außerdem, dass ich per Strg + A alle Zellen mit Zahlen auf einen Schlag markieren und kopieren kann. Wenn ich jetzt prophylaktisch bis Zelle A20 000 die Formel
=WENN(Tabelle1!A1="";"";Tabelle1!A1*10)
runterziehe (damit ich das nie wieder machen muss) und mit Strg+a markiere, markiert er mir bis A20 000, auch wenn dort keine Zahl angezeigt wird. Sogesehen muss ich trotzdem immer wieder gucken, wie viele Messwerte ich habe und somit immer bis zu der Zeile markieren.
Wenn einer von Euch Beiden Lust und Zeit hätte, das per VBA zu erklären, wäre das super! =)
LG
Ich habe inzwischen ein Makro gefunden, welches scheinbar funktioniert, aber es braucht allein bei einer einzigen Zelle wirklich sehr lange
Option Explicit
Dim zelle As Long
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error Resume Next
zelle = Cells(Rows.Count, 1).End(xlUp).Row
If Cells(zelle, 1) <> "" Then
Cells(zelle - 1, 2).Copy Cells(zelle, 2)
End If
End Sub
Zelle A1 muss immer belegt sein, Zelle B1 ist die Formel. Wenn ich nun bei Zelle A1 etwas reinschreibe, braucht es viele Sekunden, bis die Berechnungen fertig sind. Nicht auszudenken, was passiert, wenn es mehr Zellen sind ;-)
LG
Und der Bezug zu Tabelle 1 ist mir nicht klar - bin auf dem Gebiet VBA völlig unbewandert. Wollte nur mal einen scheinbaren gefunden Ansatz Euch präsentieren. Vielleicht hilft der Euch mir zu helfen =P
LG
Das Makro ist auch fehlerhaft. Das läuft in eine (fast) Endlosschleife. Deshalb dauert es so lange.
Ohne VBA, also Makros, bleibt Dir imho nichts anderes übrig, als die Formeln in Tabelle2 so weit nach unten zu ziehen, wie sie maximal in Tabelle1 vorkommen können, notfalls bis A10000.
Ich nehme mal an, Deine Formel in Tabelle2 soll:
=Tabelle 1!A1*10
lauten? Dann kannst Du mit:
=WENN(Tabelle1!A1="";"";Tabelle1!A1*10)
dafür sorgen, dass die Zelle in Tabelle2 leer bleibt, wenn in Tabelle1 nichts steht.
Falls Du VBA einsetzen willst, melde Dich nochmal, dann helfe ich Dir weiter.
Hallo Oubyi,
vielen Dank für Deine Antwort.
Leider ist die Formel nicht das, was ich endgültig suche (auch wenn's der richtige Weg ist).
Wenn Du das per VBA erklären könntest, wäre das super (s. meine allg. Antwort).
LG
Jetzt kommt es darauf an, WANN Du den Code einsetzen willst. Mit dem Change-Ereignis, wie in Deinem Beispielcode, würde - wenn es richtig gemacht wird - jedes Mal, wenn sich ein Wert in Tabelle1, Spalte A ändert, der Code automatisch ausgeführt. Ich weiß nicht, ob das wirklich sinnvoll wäre? Kommt drauf an, wie die Werte dort ergänzt oder geändert werden. Diesen Code könnte ich Dir auch leicht schreiben, aber ich zeige Dir hier erstmal, wie man das manuell per Button erreichen kann:
Gehe auf Entwicklertools (falls Du die noch nicht aktiviert hast, frage nochmal nach) / Einfügen/ ActiveX-Steuerelemnte --> "Befehlsschaltfläche" und füge die in Tabelle1 ein (einfach mit der Maus aufziehen). Jetzt ein Doppelklick auf den Button und diesen Code im VBA-Editor einfügen:
Private Sub CommandButton1_Click()
Dim Zelle As Range
Dim Bereich As Range
Set Bereich = Range("A1", Sheets("Tabelle1").Cells(Rows.Count, 1).End(xlUp))
For Each Zelle In Bereich
If Not Zelle.Value = "" Then
Sheets("Tabelle2").Range(Zelle.Address).Value = Zelle.Value * 10
End If
Next Zelle
End Sub
"Entwurfsmodus" ausklicken und Button drücken.
Dauert bei tausenden von Werten allerdings auch etwas länger, da jede Zelle einzeln berechnet wird.
Klappt es?
Falls weiterhin aktuell: Vor lauter Formeln vergisst man immer die in xl eingebauten Erleichterungen:
Schreibe Deine Auswerteformel in die oberste relevante Zelle und mach dann einen Doppelklick aufs Ausfüllkästchen: xl trägt die Formel nach unten ein, solange es Nachbarn gibt. (weiß jetzt nicht, was xl mit Leerzellen macht, ob es da stoppt oder weitermacht, wenn weiter unten noch was kommt. Xl und LO verhalten sich da nämlich unterschiedlich, und ausprobieren kannst du es selbst auch.
Hallo,
wie wäre es mal mit der Formel Bereich.verschieben. Du musst nur irgendwo errechnen wie groß der Bereich werden soll und dieses in die Formel eintragen.
Grüße
Hallo Fleischtester,
klingt interessant. Ich muss mich mal einlesen in die Funktion. Dürfte ich mich wohl bei Bedarf nochmal melden?
LG
Zusatz:
Ich brauche allerdings keine Summen / Multiplikationen von Bereichen, sondern einzelne Zellen, die multipliziert werden mit zB. zehn. Und es sollen genauso viele Zellen nach wie vor der Multiplikation sein.
Geht das damit? In der Dokumentation wird nur ein variabler Bereich zusammengefasst (als Summe oder ähnliches), aber nicht je Zelle eine Operation durchgeführt und die Anzahl der Zellen variabel gehalten.
Geht das damit?
LG
Hallo Fleischtester,
ich bin mir unsicher, ob Bereichverschieben das richtige ist: Ich finde nur Lösungen, wo ich über eine Spalte oder mehrere Spalten usw. einen Wert berechnen kann (Mittelwert über alle Werte etc.). Ich würde die Mittelwerte gerne aber zeilenweise berechnen und deswgen am Ende genauso viele Zeilen haben wie davor, nur eben die Mittelwerte über die Spalten hinweg.
Geht das?
Hallo,
wenn du eine unterschiedliche Anzahl von Zellen (Egal ob in Zeile, Spalte, oder als Block) berechnen möchtest ist Bereich.Verschieben richtig. Es geht auch den Rechenbereich zu verschieben. z.B. Lottozahlen der letzten 3 Wochen auswerten. (in einer fortlaufenden Liste...)
Wenn du etwas berechnen möchtest in einem festgelegten Bereich, aber es eine unterschiedliche Anzahl der Bereiche gibt, kannst du das mit 'wenn' bzw. 'Wennfehler' ausschließen.
Grüße
Hallo lamiam,
vielen Dank für Deine Antwort.
Leider ist die Matrixformel nicht das, was ich suche, da die Reihenfolge wichtig ist (sind zeitlich aufgenommene Messwerte). Außerdem sind es letzte Woche zB. 8000 Messwerte gewesen, bis ich da die Matrixformel so weit unten habe ...
Wenn Du das per VBA erklären könntest (s. meine allg. Antwort), wäre das super!
LG