Hallo zusammen, kann Excel die letzten 4 Werte aus einer bestimmten dynamischen Zelle in Nebenzellen einfrieren/fixieren?
Ich habe ein grosses Problem, leider kriege ich es nicht selbst gelöst. Deswegen hoffe ich hier auf Hilfe :)
Also ich habe ein grosses Tabellenblatt in Excel. Darin befinden sich dynamische Werte in den Zellen L3:L10700 und M3:M10700, die sich sekündlich oder minütlich ändern bzw. aktualisieren (ohne eigenes Zutun). Nun möchte ich die letzten 4 Werte der Zellen in Nebenzellen speichern/einfrieren. Als Beispiel soll M3 dienen.
Nehmen wir an, in M3 kommt es zu folgender Zahlenreihe: Zuerst 100, 101, 102, 103 und zuletzt steht in M3 104.
Nun möchte ich, dass in N3 der "zweitletzte" Wert, in O3 der drittletzte, in P3 der viertletzte und in Q3 der fünftletzte Wert dieser Zahlenreihe gespeichert oder eingefroren wird.
Also soll schlussendlich in N3 103, in O3 102, in P3 101 und Q3 100 stehen.
Wie gesagt, die Werte ändern sich ständig automatisch. Kriegt man das per VBA irgendwie hin? Bin leider totaler VBA-Beginner, aber um eine Antwort wäre ich sehr dankbar!
Danke und Gruss
4 Antworten
Das sollte mit einem Worksheet_change-µ gehen.
Um allerdings auszuschließen, dass wiederholte gleiche Werte "verschluckt" werden, braucht es einige Klimmzüge, an denen ich wohl noch eine Weile arbeiten müsste!
2 einfachere Optionen:
- auf wiederholt gleiche Werte verzichten
- in dem Blatt wird sonst nichts eingegeben (Du könntest Dir die Werte in ein anderes Blatt holen)
in beiden Fällen würde sich die Prozedur sehr vereinfachen.
Antworte bitte in einem Kommentar zu dieser AW, ob eine dieser Optionen und ggf welche für Dich akzeptabel wäre.
und noch eine Frage: Trägt Dein Programm die Zahlen als Zahlen ein oder als Textzahlen?
Haben die Zahlen eine begrenzte Genauigkeit per se, also hinten Nullen und ab welcher Größenordnung? Beispiel:
Mit 1234,56780000000 ginge mein Konzept, mit
1234,56789012345 nicht. Könnte man das Programm ggf veranlassen, nur bis zu einer bestimmten Stellenzahl Geauigkeit zu übertragen? (ich brauche rechts 5-6 Nullen, auf die ich mich verlassen kann, die Zahl darf also allerhöchstens 9stellig sein, egal, ob
1234,56789oooooo oder 0,00123oooooo, sofern diese Bandbreite vorkäme. natürlich ginge auch
1,oooooo - 123456989,oooooo, aber die 6 Nullen am Ende dürfen nicht durch was anderes, zB eine Periode 33333...zugeschrieben sein!
Ansonsten muss ich völlig neu überlegen!
Also auf wiederholt gleiche Werte könnte ich verzichten. Das heisst wenn ein Paar Mal hintereinander bsp. der Wert 42 kommt, dass es dann nicht angezeigt wird? Ja, das wäre kein Problem!
Mir gehts in der ganzen Prozedur eher darum, dass ich eine hohe Abweichung zum Wert davor feststellen kann.
Zu 1) Meine Werte sind keine Minuswerte und haben auch keine Nachkommastelle. Nur ganze Zahlen von 1-40000.
Zu 2) Das Problem ist, dass jeder dieser Werte zeitlich anders reinkommt. Viele aktualisieren sich sekündlich, manche minütlich, wenige die länger brauchen. Für mich am besten wäre es, wenn das Makro die Zeit sekündlich durchgehen würde oder eben wenn sich die Daten ändern, die extern reinkommen.
Also im Programm kommen die Zahlen als ganze Zahlen rein. Keine Nachkommastellen!
Ich hoffe ich konnte es ein wenig genauer erklären, sonst einfach nachfragen. Dank dir!
ja, das ist eine vielversprechende Klarstellung, ich hatte nämlich Schwierigkeiten mit dem unterschiedlichen Ausleseverhalten von Vor- und Nachkommazahlen. Mein bisheriges Verfahren hängt den Timer (theoretisch 1/10 sec genau, aber das Makro braucht etwas länger) an die um viele Nullen erweiterte, zu Text umgesetzten Zahl an, so dass man an der Länge erkennen kann, ob eine neue Zahl reingekommen ist. Das gäbs also schon umsonst, weil erledigt.
Jetzt muss ich nur noch sicherstellen, ob nicht eine Formel in M3 die Zahl holt? Denn die würde in der jetzigen Fassung überschrieben, ließe sich aber ändern. Bitte nochmals Rückmeldung!
Der Bereich der gespeicherten Zahlen kann auch fast beliebig erweitert werden, die Zahl kann man noch trennen und zB drunter wieder in eine Zahl zurückverwandeln, nochmals darunter die Eingangszeit, bei Bedarf, aber leicht zu machen.
So sieht die derzeitige Fassung aus (denn ich komme heute nicht mehr dazu, das weiterzuverfolgen)
- Sub Worksheet_Change(ByVal Target As Range)
- If Len(Range("M3")) > 20 Then Exit Sub
- Range("M3") = Left(CStr(Range("M3").Value) & "00000000000000000000", 20) & Format(Timer * 10, "000000")
- Range("M3:P3").Copy Range("N3")
- End Sub
Die Auflösungsformel für den Wert in zB Spalte P ist:
=LINKS(P3;FINDEN("+";P3;1)+2)*10^-20
diejenige für die Eingangszeit:
=RECHTS(P3;6)/10/24/60/60
(Zelle auf hh:mm:ss oder hh:mm:ss,0 formatieren
PS: man könnte auch nur 15 Nullen anhängen, aber falls sich MS in der nächsten Version einfallen ließe, die Genauigkeit auf 20 Stellen zu erhöhen...
dem Kommentar zu Ninombres AW entnehme ich, dass es da noch etwas Feilen bedarf, weil die Formel vernichtet würde. (Muss man vermutlich nur in N3 eine Übernahmeformel setzen und diese genauso prüfen wie M3, anschließend aber wieder setzen. Ggf weitere Zwischenstufe nötig, muss mal sehen.
Na das Makro sieht ja schon mal sehr vielversprechend aus! Danke dir schonmal für dein Bemühen und deine Zeit!
Eben wie gesagt in M3 steht momentan =RTD("xrtd.xrtd";;"QTY";C3;A3;"ASK") Ich könnte daneben (oder irgendwo) auch eine Spalte machen, die sich auf den Wert von M3 bezieht, falls du Probleme mit der Formel hast. Also könnte ich einfach in eine andere Zelle =M3 schreiben, dann hätte ich da den Wert drin. Ich weiss aber nicht obs was bringt?
Wie gesagt ich bin absoluter VBA-Laie, du müsstest mir dann genau sagen welche Werte/Formeln ich wo einfügen soll. Damit das Ganze schlussendlich auch funktioniert :) Momentan bin ich nur in der Lage, mir im Tabellenblatt den Code anzeigen zu lassen und ein Makro da reinzukopieren :)
Option Explicit 'nicht nötig, aber generell zu empfehlen!
Folgendes kopierst Du ins Modul des betreffenden Worksheets (Kontextmenü des Blattnamens: Code anzeigen):
- Sub Worksheet_Change(ByVal Target As Range)
- 'kopiert bei Wertänderung in M3 die Werte (nur diese) des BereichsM3:P3 eins nach rechts
- If Range("M3") = Range("N3") Then Exit Sub
- Range("M3:P3").Copy
- Range("N3").PasteSpecial (xlPasteValues)
- End Sub
Das ging nur (überhaupt und so einfach), weil Du auf das Erkennen wiederholter Werte verzichtest (mein ursprüngliches Konzept wäre da gar nicht gegangen, hab ich festgestellt).
Willst du das ausserkraft setzen, kannst Du zB vor das Worksheet oder zwischendrin einen weiteren Underbar _ setzen
Den Bereich kannst Du jederzeit nach Belieben vergrößern, zB "M3:AA3"
Und denk dran:µ sind nicht reklativ!
Bei Problemen frag zurück!
sollte natürlich heißen: Makros sind nicht relativ. D.h., wenn Du oberhalb/links davon eine Zeile/Spalte einfügst oder löscht oder M3 oder N3 als Zelle verschiebst, reagiert das µ NICHT drauf, es wird weiterhin M3=N3 getestet und M3:P3 kopiert und in N3 eingefügt!
und noch zum Verständnis:
- Target wird hier nicht gebraucht (ist normalerweise die ausgewählte Zelle), wird aber vom Worksheet_Change gefordert.
- If wird normalerweise mit neuer Zeile nach then geschrieben und von Else , ggf Elseif gefolgt und mit End if abgeschlossen. Da es hier aber keine Alternative gibt, genügt die einzeilige Kurzvariante (findest Du recht selten, deshalb Erklärung. man könnte auch mit : weitere Befehle in derselben Zeile anhängen. Bei Nichterfüllen der Bedingung wird das µ mit der Zeile unterhalb fortgesetzt.
- Das Weitere sollte selbsterklärend sein.
Hi, leider erkennt das Makro die sich ändernden Werte in der Spalte M3 nicht. Das Makro funktioniert, wenn ich entweder in die Zelle M3 reinklicke und dann auf ENTER drücke oder ich per Hand irgendwelche Werte in M3 einfüge. Aber es erkennt die sich automatisch ändernden Werte aus der externen Datenverbindung nicht.
An sowas hatte ich bei der Entwicklung nicht gedacht, ich hatte tatsächlich immer nur in eine Zelle desselben Blatts was eingegeben und von dort übernommen.
Die automatische Neuberechnung hast Du eingestellt?
Es müsste eine Lösung geben, indem man i'wie -sagen wir mal sekündlich- per anderem(?) Makro in kurzen Abständen eine Aktion vornehmen lässt, die eine Änderung auslöst, da muss ich aber erst mal nachschauen, wie das geht (hatte sowas schon mal). Geht es mit manuell F9?(Neuberechnung der Datei). zB indem man den Vergleich zwischen M3 und N3 beschränkt auf eine Einzelzelle(Formel: =M3=N3) sekündlich neu berechnen lässt.
Mi nicht, aber voraussichtich am Donnerstag.kann ich das machen. Allerdings bremst so ein µ xl ziemlich aus!
Automatische Neuberechnung ist drin, mit F9 funzt auch ned. Meinst du in irgendne Zelle =M3=N3 reinschreiben? Dann kommt WAHR oder FALSCH. aber tut sich auch nix.
wird wohl nur über ein zusätzliches Aktionsmakro funktionieren?
Hab das noch nicht hingekriegt: Es tut:
- Sub InAbständenNeuBerechnen()
- Workbooks("Coll_spezµ.xlsm").Worksheets("µListe").Range("N4").Calculate
- Application.OnTime Now + TimeSerial(0, 0, 2), "InAbständenNeuBerechnen"
- End Sub
Aber das mit dem Worksheet_Change-µ zu verschränken, hab ich noch nicht hingekriegt. Und ich hab 2-Sek-Abstände genommen, weil es bie meinem langsamen Rechner sonst xl zu stark bremst.
Du hast aber was geschrieben von einigen tausend Zeilen? Sollen die alle abgeprüft werden? dann braucht es ohnehin eine andere Lösung, da wäre ein Einzelzellen-µ zu langsam, da schwebt mir auch schon was vor, aber ich hab noch keine Ahnung, ob das durchführbar ist!
Da das hier mittlerweile recht endlos geworden ist, werde ich das nächste Mal eine neue Antwort anfangen!
Erstmal danke dir wirklich für deine Zeit und dein Bemühen!
Genau, es sind wenn ich das mal so grob überschlage, vielleicht zehntausend einzelne Zellen, die überprüft werden müssten. Also eine ziemliche Menge. Ja die sollten alle sekündlich überprüft werden, wenn das überhaupt im Excel möglich ist.
mir schwebt für so viele Zellen dieses Konzept vor:
Spalte kopieren, rechts daneben als neue Spalte einfügen (also alles Folgende nach rechts verschieben), WERTE einfügen. Das sekündlich geht zwar nur mit einem guten Rechner, sollte aber gehen
(jede Sekunde mehr zählt hier, also 2-sekündlich wäre besser. Auch wegen der Datenmenge, die sich dadurch ergibt: 60 Spalten pro Minute, 3600/h, Die xlsx-Spaltenzahl reicht also grade mal für gut 4½ h (auch aus diesem Grund: 2sec oder mehr wäre erheblich besser).
Welchen Zeitumfang soll denn das erfassen? (geht denn jetzt die Aktualisierung der Datenaquisition ausreichend schnell? Muss die Acqisitionsspalte vorher neu berechnet werden? Ein Makro kann hier spaltenspezifisch neu berechnen)
Die Zellen könnten bedingt formatiert werden, so dass ein Wertewechsel optisch angezeigt würde.
Auch kann in eine Zeile die aktuelle Zeit geschrieben werden sowie über einen Eintrag 0/1 oder A/B, s/f (stopp/fortsetzen) in eine (benannte) Zelle das µ unterbrochen/ausgesetzt werden..
Voraussetzung: rechts von der betreffenden Spalte ist Platz (es könnten auch einige Spalten dazwischen sein)
Nach Abschluss müsste es möglich sein, per Formeln oder µ die Daten auf Datenwechsel hin zu komprimieren (samt Zeitzuordnung)
Wäre das für Dich vorstellbar?
Jetzt also doch keine neue AW, denn mein bisher nur als Demo-Makro angeleges VBA-Sub läuft mit wenigen Zellen (allerdings komplizierteren Funktionen) fast 5 Sekunden und ich sehe keine Chance, das für so viele Zellen noch auf ein akzeptables Tempo zu trimmen.
Tut mir leif!
Solltest Du trotz der hohen Laufzeit an der Lösung noch interessiert sein, dann melde Dich nochmal, ich stecks für heute und wahrscheinlich überhaupt auf!
Also ich könnte auch auf das Festhalten der letzten 2 Werte heruntergehen wenn das etwas zur Systemstabilisierung bzw. dem Fuktionieren beiträgt. Desweiteren könnte ich auch mit einer Verzögerung von 20 bis max. 30 Sekunden leben.
Helfen diese Parameter?
Ich habe mal die Tage überlegt wie man das auch anders lösen kann, es wäre auch möglich, die von dir angesprochene bedingte Formatierung zu benutzen. Das Problem ist nur, bei normalem Verwenden der bed. Formatierung wird ja nur erkannt bsp. wenn der jetzige Wert der Zelle M3 >100 ist dann "Rot". Nun habe ich aber viele Zellwerte, die wirklich sehr kurz bsp. auf 5000 gehen, danach aber wieder 0 oder 100 anzeigen. Das eigentliche Ziel wäre es, diese starken Abweichungen zu sehen. D.h. es müsste nun diese 5000 irgendwie für das bedingte Formatieren "aufgezeichnet/fixiert" werden damit es auch angezeigt wird.
Ich hoffe das war einigermassen verständlich :) Sonst gerne nachfragen!
muss ich mir durch den Kopf gehen lassen. Die Reduzierung auf die letzten 2 Werte bringt kaum Beschleunigung, die Verzögerung auf bis zu 30 sec wahrscheinlich schon. Das µ läuft zur Zeit ca.8 sec und wird bei vielen Werten wahrscheinlich nicht viel länger laufen - der eigentliche Hemmschuh ist VBA, weil das immer erst über den Interpreter die aufgerufene Aktion suchen muss (und von µsoft möglicherweise bewusst nicht schneller gemacht wird?). Möglicherweise ist auch dieser tausendfache Import ein Zeitfaktor, der ins Gewicht fällt. xl selbst ist sehr schnell, da Systemnah programmiert!
Wie lange das dann in Reality mit tausenden von Werten dauert, musst Du dann selber ausprobieren, ich kann keine Datei mit Tausenden von Importen anlegen.
Ich hab nur für hetue abend grade eben die Befassung mit einer anderen Frage versprochen, mal sehen, wann ich dazu komme!
im Übrigen bin ich davon ausgegangen, dass Deine Werte laufen, und nicht um einen konstanten Mittelwert pendeln:
Sollte dies der Fall sein, und Du wirklich nur an ungewöhnlich großen Abweichungen interessiert sein, wären zumindest zwei Hilfsspalten P,Q mit einer einmalig zu Wert umgesetzten Kopie von M:M (als manuelle Momentaufnahme in P:P) und der Formel in
Q3: =wenn(abs(M3-P3)>Toleranz;M3-P3;"")
übersichtlicher.
Toleranz kannst du entweder als benannten Bereich festlegen und einen gemeinsamen Wert dort eintragen oder individuell als Zahleneintrag oder als % von M3 (dann gings für alle 10.000 mit EINER Formel).
Ob das allerdings das µ beschleunigen würde, weiß ich noch nicht, ich vermute, eher nicht. (manb muss auch die Untergrenze der wiederholzeit erst eruieren und dann mit kleinem Sicherheitszuschlag festlegen. (zB 20 sec) Änderungen mit Werten, deren Änderung+Rückänderung innerhalb eines solchen Zeitraums liegt, können nicht erfasst werden, Es gibt eine spezielle Option, ohne Makro mit Hilfe von Iteration ein Maximum zu erfassen, da erinnere ich mich aber nur schwach dran - hab ich mal für eine Frage nach einem Spiel konstruiert- und die Zeit könnte man wahrscheinlich nicht festhalten (vllt als Nachkommastelle? Aber auch dann nur mit einem Kunstgriff, dessen Funktionieren mir nur nebulös vorschwebt, weil bei Erfassung eines Maximums diese Laufzahl sinkenmüsste, zB als =GANZZAHL((1-(JETZT()-HEUTE()))*86400), hinterlegt in einer Zelle. Da könnte dann die Zeit mit einer weiteren Formel ähnlicher Art wieder rückextrahiert werden, aber wie gesagt, alles noch recht nebulös)
Schreib mal bitte, was Du dazu denkst.
Erstmal ein gutes, gesundes Neues Jahr Iamiam und alle die hier so toll helfen!
DIe Toleranz-Formel sieht wirklich interessant aus, eigentlich genau an sowas hatte ich auch gedacht. Aber speicherts mir den hohen Abweicherwert auch? Ich habs mal probiert gerade, aber noch nicht so ganz raus wie ich die Toleranz justieren muss.
Bsp. hätte ich in F3 den Toleranzwert, müsste ich dann in Q3 schreiben
=wenn(abs(M3-P3)>F3;M3-P3;"")
In P3 schriebe ich M3:M3, korrekt?
In Q3 kriege ich jetzt aber immer nur das Ergebnis "" - also nichts.
So eine Lösung wäre natürlich perfekt
Nein, in P3 schreibst du den Erwartungswert bzw Du machst manuell 4-6 Momentaufnahmen der Spalte M, setzt sie als Werte i'wo rechts hin und bildest den Mittelwert.
F3 erhältst Du (mehr oder weniger zufällig). indem Du davon zeilenweise (Max()-Min())/2 nimmst (plus Sicherheitszuschlag, falls die mal alle gleich sind...). ist da allerdings eine große Abweichung dabei, wird auch die nur halbiert. Du kannst dir aber über die bedingte Formatierung große Werte anzeigen lassen.
Ob das alles noch als vertretbarer Aufwand helten kann, hängt von der Wichtigkeit der Info ab. (ich persönlich hätt's schon rein interessehalber gemacht, hab aber auch viele unbezahlte Überstunden in solche Sachen reingehängt)
Bin z.Zt auf Reisen und kann mich leider nicht drum kümmern, vllt ab Mitte nächster Woche wieder!
Okay, das mit dem Toleranzwert verstehe ich nun.Die Formel funktioniert auch und Excel erkennt nun die Verknüpfung M3 als Zahl! Und ich kann für den Toleranzwert einen Prozentsatz nehmen bzw. für meine Arbeit einfach den Wert >2.5 - das reicht mir dann, denn ich setze das Volumen in M3/P3. dann kommt meistens ein Wert zw. 0.5-2.5 raus. D.h. ich kann die Formel für alle 10000 Werte nutzen. :)
Nun habe ich eigentlich nur noch ein Problem, wie kriege ich den Mittelwert aus den letzten zwei oder drei oder vier Zellwerten aus M3 fortlaufend fixiert/gespeichert?
Gute Reise!
Wie kommen die Werte denn in die Tabelle rein? Wäre wichtig wie man eine Änderung erkennen kann.
Was soll passieren, wenn die 5 letzten Werte alle gefüllt sind? Überschreiben?
Aus meiner Sicht muss auch der letzte Wert schon übernommen werden in diese neue Liste, denn sonst ist er ja weg, wenn das Makro beginnt - außer, wenn das Makro vor der Aktualisierung schon gestartet wird. Ich gehe allerdings davon aus, dass man erst die Änderung feststellt und dann reagiert. Dafür brauche ich aber noch Input, wie der Ablauf denn aktuell funktioniert.
Ich würde es dann mit zwei Zeilen lösen:
Range("N3:Q3").Copy Destination:=Range("O3")
Range("N3").Value = Range("M3").Value
Oh, hier sollte ich wohl kommentieren :) Also ja wenn der letzte Wert weg wäre, wäre das natürlich nicht gut. Der soll auch bleiben. Genau erst kommt die Änderung, dann soll das Makro darauf reagieren :)
Ich weiß aber immer noch nicht wie die Änderung denn rein kommen? Schreibt das externe Programm rein (kann ich mir nicht vorstellen), sind das Formeln, die neu gerechnet werden oder werden die Daten über eine Datenverbindung eingelesen? Ein Makro läuft nicht einfach im Hintergrund mit, es wird manuell gestartet oder durch spezielle Ereignisse. Um das passende zu nutzen muss ich eben wissen, wie die Aktualisierung erfolgt
Also die Daten werden über eine Datenverbindung eingelesen.
Es wird wahrscheinlich nicht helfen, aber bsp. eine der Formeln lautet =RTD("xrtd.xrtd";;"QTY";C452;A452;"BID")
Das wird dann als ganze Zahl dargestellt in der Zelle
So, jetzt glaube ich, alles zu haben, was Du brauchst:Folgendes Makro (berechnet Spalte M:M neu, kopiert sie nach BA und fügt vor BA eine neue BA ein, indem sie alles nach rechts verschiebt. Dann wird N:AZ mit den ganzen Auswertewformeln neu berechnet. (zur ggf Dauer des letzten Schritts noch ganz unten was). Ich habe viele Spalten für Auswerteformeln vorgesehen, denn es müssen ja ALLE Aktionen innerhalb derselben Zeile vorgenommen werden. Makro:
- Sub WerteFesthalten_SpalteFixKopieEinfügen_inKurzenAbständen() '
- Debug.Print "1: " & Timer & " " & Format(Timer, "00000")
- If Application.CountA(Columns("XFD:XFD")) > 0 Then Application.Calculation = xlAutomatic: MsgBox "Blatt voll!": Exit Sub
- Columns("M:M").Calculate
- Columns("M:M").Select
- Selection.Copy
- Columns("BA:BA").Select 'Bezugsspalte für Formeln ist $AZ
- Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'nur xlpastevalues ist erforderlich
- Selection.Pastespecial Paste:=xlpasteformats 'falls nicht erforderlich: auskommentieren, beinhaltet aber auch Zahlenformate, die man jedoch genausogut über die Spalte davor festlegen kann.
- Application.CutCopyMode = False 'kann man sich auch sparen
- Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
- Columns("N:AZ").Calculate ' s.Anmerkung °)
- Debug.Print "2. " & Timer & " " & Format(Timer, "00000") 'aus der Differenz der jeweils 2 Zeilen im Direktbereich ergibt sich die Laufzeit in Sekunden
- Application.OnTime Now, "WerteFesthalten_SpalteFixKopieEinfügen_inKurzenAbständen" ' mit Now + TimeSerial(0, 0, 2), ...Makroname dauerts 2 Sek. länger, dafür kann man dieses 2 Sekunden-Fenster für andere Aktionen nutzen, wenn das Makro läuft
- End Sub
Das Makro hier kopieren und in ein (ggf neues) Modulblatt einfügen. Ungewollte Umbrüche entfallen dann.
Anm.°): Ich weiß nicht, wie lange die Neuberechnung des Riesenbereichs "N:AZ" bei einer großen Datei dauern wird. Xl ist normalerweise schnell, nur VBA ist langsam. Sollte das trotzdem zu lange dauern, kannst Du immer nur den gerade betrachteten Bereich mit der Maus auswählen und anstatt obiger Befehlszeile diese verwenden:
- Selection.calculate
Das sollte dann in NullKommaNix erfolgt sein. Aber kalkuliert müssen die Auswerteformeln natürlich werden. Vllt beschleunigt es auch, wenn Du Neuberechnung auf manuell stellst, ich hab wenig Erfahrung für große Dateien und externe Datenaquisition..
Formeln:
Du hast N:AY=39 Spalten dafür zur Verfügung, das sollte reichen. Wenn nicht, dann musst Du im Makro anstatt BA eben CA schreiben
Da die Spalten dauernd wandern, muss immer der Gesamtbereich ab AZ (bleibt fix, erst BA wandert weg) bis Ende aller Einträge ausgewertet werden, am einfachsten bis Spalte XFD (überraschenderweise meckert xl nicht, wenn so eine Bezugsgrenze über den Blattrand hinaus wandern würde, es bleibt einfach XFD).
Die folgenden Formeln dienen nur als Anregung, aber ich beziehe mich ggf auf feste Adressen. Standardzahl: da hattest du eine Formel, vllt aber auch:
in AF19: =MITTELWERT($AZ19:$XFD19)
daneben =SUMMENPRODUKT(($AZ19:$XFD19>$AF19*90%)*1)-SUMMENPRODUKT(($AZ19:$XFD19>$AF19*95%)*1) liefert die Anzahl aller Werte zwischen 90 unf 95% vom Bezugswert
daneben: =SUMMENPRODUKT(($AZ19:$XFD19>$AF19*95%)*1)-SUMMENPRODUKT(($AZ19:$XFD19>$AF19*100%)*1) desgl.95..100%
daneben: =SUMMENPRODUKT(($AZ19:$XFD19>$AF19*100%)*1)-SUMMENPRODUKT(($AZ19:$XFD19>$AF19*105%)*1) desgl. 100-105%
usw. Abstufungen kannst du beliebig einstellen, feiner und mehr als hier gezeigt.
Den größten Wert erhältst du mit =KGRÖSSTE($AZ19:$XFD19;1) (in AM19)
er steht in: =ADRESSE(19;VERGLEICH(AM19;$AZ19:$XFD19;0)+SPALTE($AZ19)-1)
Analog 2.-Größter etc.
etwas komplizierter die KKleinste (wegen der vielen leeren Zellen)
in AS19: =KGRÖSSTE($AZ19:$XFD19;(ANZAHL($AZ19:$XFD19)-1)+1) Das +1)-1 am Ende hebt sich zwar auf, aber damit die letzte 1 auch das K bezeichnet, hab ich das so geschrieben.
Dieser Wert steht analog zum Größten wieder in
=ADRESSE(19;VERGLEICH(AS19;$AZ19:$XFD19;0)+SPALTE(BB19)-1)
Bei hohen Ausschlägen von KGrößte bzw KKleinste kannst Du diese Werte löschen, um zu sehen, was danaxh noch kommt, aber k bis 5 (sind immerhin 20 Spalten!) sollte eigentlich reichen?
Ach ja, noch vergessen: wenn Du in M1 zB noch =Jetzt() schreibst (Format h:mm:ss reicht, am besten rechtsbündig), dann steht der Zeitpunkt der Erfassung am Ende in Zeile 1 jeder registrierten Spalte, zur späteren Info Zeile 1 zB als Titel fixieren=einfrieren). Und die Spalten ab AZ bis Ende kannst du schmal machen.
Kommt ein Spitzenwert allerdings innerhalb der Laufzeit des Makros und verschwindet auch innerhalb wieder, hast du Pech gehabt.
Ich hoffe, Du kommst damit zurecht, hab ggf am Sa nochmal ausreichend Zeit. Ich geh jetzt (3:12) schlafen!
Die Werte kommen von einem externen Programm, die sich wie gesagt ständig ändern.
Ja genau, es sollte eine kontinuierliche Überschreibung sein. Am besten wäre es, das Makro einmal starten zu lassen dass es dann solange läuft bis ich Excel schliesse.
Danke für deine Hilfe!
Noch zwei Fragen:
Beide Teilfragen hängen zusammen!