Berechnetes Feld durch Wert ersetzen und neues berechnetes Feld aufbauen

[schauan]

Hallöchen,

werden dann zum Zeitpunkt x auch die Zählerstände am Zähler zurückgesetzt oder laufen die in Realität weiter? Im letzteren Fall könnte an die in einer weiteren Spalte fortlaufen lassen und darauf ggf. eine Berechnung aufbauen.

Allerdings bleibt dann immer noch die Frage, was mit dem Grenzwert passiert. Wie gesagt, wenn ich bei mir das Datum einen Monat zurück drehe und in die Datei schaue, bekomme ich doch was anderes, oder nicht? Da müssten doch die Bezüge resp. die Formel in D auch 2 Zeilen weiter oben greifen, da wäre doch die "Reduzierung" schon eher fällig gewesen?

[Hardbopper]

Hallo André,

es handelt sich um einen geeichten Zähler, da kann ich nichts zurückdrehen. Die Werte, die bereits real geworden sind (schwarze Zahlen in Spalte D), könnten theoretisch  (in dieser Tabelle, nicht im Zähler ) noch verändert werden, was aber keine Sinn macht, weil dadurch das Gesamtbild, das den Verbrauch in den einzelnen Monaten widerspiegelt, verfälscht würde.


Mein Gedanke, die noch zu findende Formel in allen Feldern der Spalte D wirken zu lassen, beruht darauf, dass sie selbst, wenn man sie z. B. erst ab Zeile 46 einsetzt, mit dem Weiterrücken des "Untersuchungsbereichs" z.B. nach Zeile 47, nicht verschwindet, sondern weiterhin in D46 vorhanden wäre. Es muss sich also um eine Formel handeln, die die Realwerte (schwarze Zahlen) nicht im Nachhinein beeinflusst.

Hardbopper

[schauan]

Hallöchen,

dann bin ich zum einen wieder bei der Formelvariante. Du musst dazu aber auch irgendwo die Originalwerte führen und könntest dann, wie eingangs von mir geschrieben, mit WENN... entweder die Originalwerte nach Spalte D holen oder die Originaldaten um den Grenzwert reduziert eintragen.

Ansonsten kann ich auch hartnäckig sein. Ich weiß immer noch nicht, wie das Ganze ausgesehen hätte, wenn Du die Frage schon am 2. Dezember gestellt hättest. Ich frag nochmal - hättest Du dann schon im Dezember reduziert und wenn nein, wieso nicht?

Oder anders gefragt, sind Deine Werte alle 14 Tage anders? Wenn Du am 16.01. reinschaust, dann überschreibst Du bis dahin die heute noch reduzierten durch die realen Werte und machst eine neue Prognose mit den dann erst zu einem späteren Zeitpunkt zu reduzierenden Werten?

[Hardbopper]

Hallo André,

genau wie Du im letzten Absatz nachgefragt hast, läuft es ab.

Hardbopper

[Hardbopper]

Eine Bitte habe ich noch: Du sprichst immer von reduzierten Werten. Das irritiert mich deshalb, weil sie als Realwerte ja nicht zwingend reduziert, also verringert / verkleinert werden, sondern durchaus auch größer sein können als die prognostizierten.

Hardbopper

[schauan]

hallöchen,

die sind in dem Sinne reduziert dass ja irgendeine Zahl vom tatsächlich auf dem Zähler stehenden Wert abgezogen wird. Die korrigierten Werte sind ja alle geringer als Deine 4.000 und knips über den kleineren Werten, daher ist das für mich eine Reduzierung. Ich sehe jetzt in Deinem Beispiel nicht, dass nach D47 größere Werte als zuvor kommen.

Hier mal ein vereinfachter Ansatz. Spalte E wären die tatsächlichen Zählerwerte. Wenn Du in 4 Wochen reinschaust, wäre der Wert von G2 dann 40 und dieser Wert würde zur Reduzierung herangezogen. Du kannst natürlich auch noch irgendwas per Formel berechnen statt der 40.

Arbeitsblatt mit dem Namen 'Tabelle3'
	A	B	C	D	E	F	G	H
1	Datum			Wert	Zählerstand		Datum	Grenzwert
2	01.07.2019			5	5		03.01.2020	35
3	01.08.2019			10	10
4	01.09.2019			15	15
5	01.10.2019			20	20
6	01.11.2019			25	25
7	01.12.2019			30	30
8	01.01.2020			0	35
9	01.02.2020			5	40
10	01.03.2020			10	45
11	01.04.2020			15	50
12	01.05.2020			20	55

Zelle	Formel
D2	=WENN(E2>=$H$2;E2-$H$2;E2)
G2	=HEUTE()
H2	=SVERWEIS(G2;A2:E25;5;WAHR)

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg

[Hardbopper]

André, irgendwie kommen wir nicht zusammen.

Den Wert in Tabelle 3 H2 brauche ich nicht per Formel zu ermitteln. Da er in der Vergangenheit, also vor dem Datum in G2 liegt, ist er durch den Zählerstand zum 31.12.2019 von mir per Hand eingetragen worden und wird über den dynamischen Zeitstempel erfasst. Die Differenz zwischen G2 und A8 ermittle ich durch einfaches Subtrahieren.

Was ich bei Dir überhaupt nicht finde (mir aber wichtig ist), ist der wahrscheinliche Wert für den 15. 01.2020 durch Hochrechnen. Wenn ich darauf verzichte und nur Zwei-Wochen- oder Monatsschritte mache, brauche ich nur zu den entsprechenden Zeitpunkten an den Zähler zu marschieren und die Werte abzuschreiben.

Um noch einmal auf den Ausgangspunkt zurück zu kommen (ich beziehe mich jetzt wieder auf meine Tabelle): Der Wert, der in H25 berechnet wird, wird durch Verknüpfung dieser Zelle mit D46 in die Spalte der Zählerwerte übertragen. Diese Verknüpfung muss aber aufgelöst sein, bevor das Datum in G13 auf das nächste Datum weiterrückt. Ds erfolgt zwangsläufig von Hand, indem ich den Wert aus dem Verbrauchszähler übertrage. Wichtig wäre mir, dass - durch welche Methode auch immer - automatisch eine neue Verknüpfung zwischen H25 und der nächsten Zelle in D (das wäre dann D47) hergestellt wird.

Hardbopper

[shift-del]

Hardbopper, deine Erklärungen bringen mich nicht weiter.
Ich ignoriere mal die Formeln in der Tabelle und konzentriere mich auf das Wort "Hochrechnung" aka Extrapolation.
Excel bringt verschiedene Funktionen mit:

Prognostizierte Werte in einer Reihe
Erstellen einer Prognose in Excel für Windows

[Hardbopper]

Hallo André, hallo shift-del,

mein Konstrukt läuft an und für sich einwandfrei, deshalb möchte ich davon nicht ablassen. Vielleicht kommen wir auf folgendem Weg weiter:

Ich habe inzwischen zwei Makros eingebaut, "Wert zuweisen" und Verknüpfung herstellen". Beide funktionieren, sind aber noch verbesserungsbedürftig. Da ich aber nicht so der VBA-Experte bin, bitte ich um Hilfe.

Folgendes ist zu verbessern:

1. Das Makro "Wert zuweisen" erwartet das Benennen der Zelle, aus der die Formel entfernt und der Wert eingefügt werden soll. Dies ist immer die Zelle in der Spalte D, deren Wert dem Wert in $H$13 entspricht. Kann das Makro entsprechend angepasst werden, damit eine Eingabe entbehrlich wird  ?

2. Das Makro " Verknüpfung erstellen" ist durch Aufzeichnung entstanden. Es müsste so angepasst werden, dass in Spalte D in der Zelle die Verknüpfung hergestellt wird, deren Wert dem in $H$25 entspricht.

3. Die beiden Makros müssten schließlich zu einem zusammengefasst werden, damit beide Maßnahmen hintereinander ablaufen.

Kann ich da auf Eure Hilfe rechnen ?

Die verbesserte Tabelle ist als Anhang beigefügt.

Hardbopper


  Ersetzen eines berechneten Feldes durch einen Wert.xlsm (Größe: 133,35 KB / Downloads: 3)

[schauan]

Hallöchen,

hab gerade gesehen, dass Deine Werte doch nicht kleiner sind. Der Tausenderpunkt steht durch das Weglassen der Dezimalstellen so schön unter den Kommata, dass ich ihn glatt für eins gehalten habe. Wenn nur Du damit arbeitest, ist das sicher kein Problem. Der eine oder andere Andere könnte aber durchaus auch drüber stolpern


mal zur ersten Frage:

Code:

Sub Makro1()
MsgBox Columns(4).Find(What:=[h13], After:=[d1], LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Address
End Sub

Der Code steht dann auch als Prinzip für die zweite Frage.

Zusammenfassen kannst Du Deine Makros sicher, indem Du den Inhalt vom zweiten vor das End Sub des ersten setzt oder indem Du vor End Sub des ersten z.B. mit Call Makro2 das zweite aufrufst.