Zählerwerte bei Nichtablesen interpolieren

Forumthread: Zählerwerte bei Nichtablesen interpolieren

Reinhard

Hallo Wissende,
als Möglichkeit das Lesen abkzukürzen habe ich die eigentliche Frage in Fett markiert.
wie hoffentlich gut in nachfolgender Tabelle ersichtlich möchte ich in C den täglichen Verbrauch per Formel berechnen lassen. Die gezeigten Werte habe ich manuell reingeschrieben.
Die Grundproblematik dabei interessiert mich in Bezug darauf dies mit einer Formel in C zu erreichen.
Eine funktionierende Vba-Lösung habe ich gebastelt und auch eine Lösung mit Hilfsspalten kriege ich hin.
Aber ohne Hilfsspalte schaffte ich es bislang nicht :-(
Irgendwie schaffe ich es trotz aller Bemühungen, jetzt mal von Zeile 4 ausgehend nicht in der Formel zu
ermitteln daß der nächsthöhere Wert die 270 ist.
Der nächstkleinere den schaffe ich herauszufinden, also die 210.
Das geht einfach mit Max($B$2:B3) und dann mit Vergleich() herauszufinden, die tagesdifferenz zu ermitteln usw.
Ich kam für den nächsthöheren Wert auf =Vergleich("*";B4:B1000...
Leider klappt das ganz und gar nicht, da kommt #NV.
Warum das so ist habe ich noch nicht herausgefunden.
Aber bringt jetzt nix. Ich stelle dazu eine andere Anfrage ein, wo ich Beispiele bastle, wo derartige
Vergleiche mal klappen, mal nicht.
Weiß jmd. eine Formel für C die ohne Hilfsspalten die dort manuelle eingetragenen Werte berechnet?
Bezogen auf die nachfolgende tabelle.

Danke ^ Gruß
Reinhard
Tabellenblatt: [Mappe1]!Tabelle1 │ A │ B │ C │ ---┼----------┼-----------┼-----------┼ 1 │ Datum │ abgelesen │ Verbrauch │ ---┼----------┼-----------┼-----------┼ 2 │ 31.12.11 │ 200 │ 0 │ ---┼----------┼-----------┼-----------┼ 3 │ 01.01.12 │ 210 │ 10 │ ---┼----------┼-----------┼-----------┼ 4 │ 02.01.12 │ │ 20 │ ---┼----------┼-----------┼-----------┼ 5 │ 03.01.12 │ │ 20 │ ---┼----------┼-----------┼-----------┼ 6 │ 04.01.12 │ 270 │ 20 │ ---┼----------┼-----------┼-----------┼ 7 │ 05.01.12 │ 274 │ 4 │ ---┼----------┼-----------┼-----------┼ 8 │ 06.01.12 │ │ 8 │ ---┼----------┼-----------┼-----------┼ 9 │ 07.01.12 │ 290 │ 8 │ ---┼----------┼-----------┼-----------┼ 10 │ 08.01.12 │ │ 0 │ ---┼----------┼-----------┼-----------┼ 11 │ 09.01.12 │ │ 0 │ ---┴----------┴-----------┴-----------┴ Zahlenformate der Zellen im gewählten Bereich: A1:A11 haben das Zahlenformat: TT.MM.JJ B1:B11,C1:C11 haben das Zahlenformat: Standard

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

AW: Zählerwerte bei Nichtablesen interpolieren

12.01.2012 22:38:47

fcs

Hallo Reinhard,
solange der Zähler nicht ausgetauscht wird kannst du mit folgender Formel arbeiten.
Die Formel ist aber nicht ganz ohne. Sie dürfte anfällig sein gegen das Löschen oder Einfügen von ganzen Zeilen.
Die Formel in Zelle C3 kannst du nach unten kopieren.
Gruß
Franz
Tabelle3

	A	B	C
1	Datum	abgelesen	Verbrauch
2	31.12.2011	200
3	01.01.2012	210	10
4	02.01.2012		20
5	03.01.2012		20
6	04.01.2012	270	20
7	05.01.2012	274	4
8	06.01.2012		8
9	07.01.2012	290	8
10	08.01.2012
11	09.01.2012

Formeln der Tabelle

Zelle	Formel
C3	=WENN(MIN(B3:B40)=0;"";WENN(B3=0;(INDEX(B4:B40;VERGLEICH(MIN(B4:B40); B4:B40;1))-MAX($B$2:B2))/(INDEX(A4:A40;VERGLEICH(MIN(B4:B40); B4:B40;1))-INDEX($A$2:A2;VERGLEICH(MAX($B$2:B2); $B$2:B2;0))); (B3-MAX($B$2:B2))/(A3-INDEX($A$2:A2;VERGLEICH(MAX($B$2:B2); $B$2:B2;0)))))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Dankeschön @ Franz ^ Erich

13.01.2012 13:01:51

Reinhard

Hallo,
die Formeln analysiere ich noch. Ich bin sehr zufrieden :-)
Gruß
Reinhard

Zählerwerte interpolieren

13.01.2012 01:33:00

Erich

Hi Reinhard,
vielleicht klappt auch das hier - musst du aber gut testen! ;-)

	A	B	C
1	Datum	abgelesen	Verbrauch
2	31.12.2011	200
3	01.01.2012	210	10
4	02.01.2012		20
5	03.01.2012		20
6	04.01.2012	270	20
7	05.01.2012	274	4
8	06.01.2012		8
9	07.01.2012	290	8
10	08.01.2012
11	09.01.2012

Formeln der Tabelle

Zelle	Formel
C3	=WENN(MIN(B3:B40)=0;"";WENN(B2=0;C2; (INDEX(B2:B19;1+VERGLEICH(MIN(B3:B39); B3:B39;1))-B2) / (VERGLEICH(MIN(B3:B39); B3:B39;1)+1-VERGLEICH(MIN(B2:B38); B2:B38;1))))

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Werte interpolieren - Lücken auffüllen

15.01.2012 10:57:56

Erich

Hi Reinhard,
speziell für dich noch ein paar Formeln:

	B	C	D	F	G	H	I
1	beliebig	monoton	streng monoton	Quelle	Test1	Test2	Test3
2				200	200	200	200
3	10	10	10	210	210	210
4	20	20	20				100
5	20	20	20
6	20	20	20	270	270	210	270
7	4	4	4	274	274	210	100
8	8	8	8
9	8	8	8	290	290	290	290
10

Formeln der Tabelle

Zelle	Formel
B3	{=WENN(ANZAHL(F3:F$99)=0;"";WENN(F2="";B2;(INDEX(F:F;MIN(WENN(F3:F$99<>"";ZEILE(F3:F$99))))-F2)/(MIN(WENN(F3:F$99<>"";ZEILE(F3:F$99)))-ZEILE(F2))))}
C3	{=WENN(ANZAHL(F3:F$99)=0;"";WENN(F2="";C2;(MIN(F3:F$99)-F2)/(MIN(WENN(F3:F$99<>"";ZEILE(F3:F$99)))-ZEILE(F2))))}
D3	=WENN(ANZAHL(F3:F$99)=0;"";WENN(F2="";D2;(MIN(F3:F$99)-F2)/(VERGLEICH(MIN(F3:F39); F$1:F39;0)-ZEILE(F2))))

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

Die drei haben unterschiedliche Voraussetzungen. Dazu stehen rechts drei Spalten mit Testwerten.
Kopiert man die Werte unter Test3 unter "Quelle", passt nur noch die Formel in B3.
Bei der Formel in D3 muss z. B. sicher sein, dass sich keine Werte wiederholen ("streng" monoton).
Dafür ist D3 dann keine Matrixformel.
Bei all diesen Formeln spielt das (Ablese-)Datum keine Rolle.
Und hier auch gleich noch ein paar Alternativen zu http://www.excelformeln.de/formeln.html?welcher=465
auch hier mit unterschiedlichen Voraussetzungen:

	A	B	C	D	E	F	G
1	Quelle	Nr. 465	beliebig, wie 465	beliebig, wie 465	monoton	monoton	streng monoton
2	-16	-16	-16	-16	-16	-16	-16
3	-15,3	-15,3	-15,3	-15,3	-15,3	-15,3	-15,3
4		-13,8667	-13,8667	-13,8667	-13,8667	-13,8667	-13,8667
5		-12,4333	-12,4333	-12,4333	-12,4333	-12,4333	-12,4333
6	-11	-11	-11	-11	-11	-11	-11
7		-0,5	-0,5	-0,5	-10,5	-10,5	-10,75
8	10	10	10	10	10	10	10
9		0	0	0	0	0	0
10	-10	-10	-10	-10	-10	-10	-10
11	-9	-9	-9	-9	-9	-9	-9
12		-6	-6	-6	-12,3333	-12,3333	-10,6667
13		-3	-3	-3	-15,6667	-15,6667	-12,3333
14	0	0	0	0	0	0	0
15

Formeln der Tabelle

Zelle	Formel
B2	=A2
C2	=A2
D2	=A2
E2	=A2
F2	=A2
G2	=A2
B3	{=WENN(ANZAHL(A3:A$99)=0;"";WENN(A3="";B2+(INDEX(A:A;MIN(WENN(A3:A$99<>"";ZEILE(A3:A$99))))-INDEX(A:A;MAX(WENN(A$2:A3<>"";ZEILE(A$2:A3)))))/(MIN(WENN(A3:A$99<>"";ZEILE(A3:A$99)))-MAX(WENN(A$2:A3<>"";ZEILE(A$2:A3)))); A3))}
C3	{=WENN(ANZAHL(A3:A$99)=0;"";WENN(A3="";C2+(INDEX(A:A;MIN(WENN(A4:A$99<>"";ZEILE(A4:A$99))))-INDEX(A:A;MAX(WENN(A$2:A2<>"";ZEILE(A$2:A2)))))/(MIN(WENN(A4:A$99<>"";ZEILE(A4:A$99)))-MAX(WENN(A$2:A2<>"";ZEILE(A$2:A2)))); A3))}
D3	{=WENN(ANZAHL(A3:A$99)=0;"";WENN(A3="";C2+(INDEX(A:A;MIN(WENN(A4:A$99<>"";ZEILE(A4:A$99))))-INDEX(A:A;VERGLEICH(2;1/(A$1:A2<>""))))/(MIN(WENN(A4:A$99<>"";ZEILE(A4:A$99)))-VERGLEICH(2;1/(A$1:A2<>""))); A3))}
E3	{=WENN(ANZAHL(A3:A$99)=0;"";WENN(A3="";E2+(MIN(A4:A$99)-MAX(A$2:A2))/(MIN(WENN(A4:A$99<>"";ZEILE(A4:A$99)))-MAX(WENN(A$2:A2<>"";ZEILE(A$2:A2)))); A3))}
F3	{=WENN(ANZAHL(A3:A$99)=0;"";WENN(A3="";F2+(MIN(A4:A$99)-MAX(A$2:A2))/(MIN(WENN(A4:A$99<>"";ZEILE(A4:A$99)))-VERGLEICH(2;1/(A$1:A2<>""))); A3))}
G3	=WENN(ANZAHL(A3:A$99)=0;"";WENN(A3="";G2+(MIN(A4:A$99)-MAX(A$2:A2))/(VERGLEICH(MIN(A4:A$99); A$3:A$99;0)-VERGLEICH(MAX(A$2:A2); A$3:A$99;0)); A3))

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

Hier sieht man, dass mit dem Wert in A8 die Monotonie gestört ist. Die Formeln E3:G3 liefern dann falsche Werte.
In Spalte B ist (fast) die Originalformel von excelformeln.de.
Schönen Sonntag noch!
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Werte interpolieren - SpielMappe

15.01.2012 12:51:58

Erich

Hi Reinhard,
hier noch die Mappe zum Spielen: https://www.herber.de/bbs/user/78456.xls
Viel Spaß!
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

@Erich, du gehst aber sehr großzügig ...

15.01.2012 21:28:27

Reinhard

... mit meiner Lebenszeit um :-( :-)
Du weißt doch daß ich zum kapieren von so langen Formeln die du in 2-5 min geschrieben hast.
minimum 50 Minuten brauche und Tests um die zu kapieren.
Für jede einzelne.
Hättest du es denn nicht genug sein lassen können mit den bisherigen Lösungen?
Aber nein du MUßtest ja weiterbasteln:-(
Und ich MUß all deine Basteleien natürlich auch "untersuchen" :-(
Ich glaube das Dilemma rührt daher daß wir gleich gepolt sind Excelbezüglich.
Neugier und auch der Drang in der Richtung, eine Lösung liegt vor aber die kann man evtl. noch abändern, verbessern.
Daß es bei 100 derten von ungenutzten Spalten sehr uneffektiv ist ohne Hilfsspalten zu arbeiten sondern man den Wunsch hat alles in eine Formel zusammenzuknäulen ist uns beiden sehr klar.
Aber weil wir halt so sind wie wir sind interessieren wir uns um so Peanuts nicht.
Denn, Hilfsspalten sind langweilig, kann ja jeder :-)
Also wird das so weitergehen, und das ist gut so *gg*
Gruß
Reinhard

Infobox / Tutorial

Schritt-für-Schritt-Anleitung

Um die Zählerwerte für den Stromverbrauch in Excel zu interpolieren, kannst du eine Kombination aus Excel-Funktionen verwenden. Hier ist eine einfache Schritt-für-Schritt-Anleitung:

Tabelle erstellen: Erstelle eine Tabelle mit den Spalten „Datum“, „abgelesen“ und „Verbrauch“. Die abgelesenen Werte sollten in Spalte B stehen.

Formel in Zelle C3 eingeben: Verwende die folgende Formel, um den Verbrauch zu berechnen:

=WENN(MIN($B$3:$B$40)=0; ""; WENN(B3=0; C2; (INDEX($B$2:$B$40;VERGLEICH(MIN($B$3:$B$40); $B$3:$B$40; 1))-B2)/(VERGLEICH(MIN($B$3:$B$40); $B$3:$B$40; 1)+1-VERGLEICH(MIN($B$2:$B$38); $B$2:$B$38; 1)))

Formel nach unten kopieren: Ziehe die rechte untere Ecke der Zelle C3 nach unten, um die Formel auf die anderen Zellen in der Spalte anzuwenden.
Überprüfen der Werte: Stelle sicher, dass die interpolierten Werte korrekt sind und deinen Erwartungen entsprechen.

Häufige Fehler und Lösungen

#NV-Fehler: Wenn du einen #NV-Fehler erhältst, könnte das daran liegen, dass die Werte in Spalte B nicht korrekt sortiert sind. Stelle sicher, dass die abgelesenen Werte aufsteigend angeordnet sind.
Falsche Ergebnisse: Überprüfe, ob die Formeln richtig eingegeben wurden und keine Leerzeichen oder Tippfehler enthalten sind.
Fehlende Daten: Wenn in den abgelesenen Werten Lücken sind, kann die Interpolation fehlerhaft sein. Fülle fehlende Werte manuell ein, bevor du die Formel anwendest.

Alternative Methoden

Wenn du keine Hilfsspalten verwenden möchtest, gibt es auch andere Methoden zur Interpolation in Excel:

VBA-Skript: Du kannst ein einfaches VBA-Skript erstellen, das die Werte automatisch interpoliert, wenn die Daten in die Tabelle eingegeben werden.
Matrixformeln: Verwende Matrixformeln, um in einer einzigen Formel mehrere Berechnungen durchzuführen. Diese können jedoch komplex sein und benötigen besondere Aufmerksamkeit bei der Eingabe (STRG + SHIFT + ENTER).
Power Query: Mit Power Query kannst du Daten transformieren und interpolieren, bevor sie in die Excel-Tabelle geladen werden.

Praktische Beispiele

Hier ist ein Beispiel für eine Tabelle mit den entsprechenden Formeln:

Datum	abgelesen	Verbrauch
31.12.2011	200	0
01.01.2012	210	10
02.01.2012		20
03.01.2012		20
04.01.2012	270	20
05.01.2012	274	4
06.01.2012		8
07.01.2012	290	8
08.01.2012		0
09.01.2012		0

Die Formel in Zelle C3 sorgt dafür, dass der Verbrauch auch dann korrekt berechnet wird, wenn einige Zählerwerte nicht abgelesen werden konnten.

Tipps für Profis

Namensverwaltung: Verwende die Namensverwaltung in Excel, um Bereiche wie "abgelesen" in deiner Formel zu benennen. Das macht die Formeln übersichtlicher.
Datenvalidierung: Nutze die Datenvalidierungsfunktion, um sicherzustellen, dass nur gültige Werte in die Tabelle eingegeben werden.
Dynamische Diagramme: Erstelle Diagramme, die sich automatisch aktualisieren, wenn neue Daten hinzugefügt werden. So hast du immer einen Überblick über den Stromverbrauch.

FAQ: Häufige Fragen

1. Was ist Interpolation in Excel?
Interpolation ist eine Methode zur Schätzung von Werten zwischen zwei bekannten Werten. In Excel kannst du dies durch Formeln erreichen, die auf vorhandene Daten basieren.

2. Kann ich Hilfsspalten vermeiden?
Ja, du kannst komplexe Formeln verwenden oder VBA-Programmierung einsetzen, um die Interpolation ohne Hilfsspalten zu realisieren.

3. Welche Excel-Version benötige ich?
Die beschriebenen Methoden sind in den meisten aktuellen Excel-Versionen verfügbar, einschließlich Excel 2016, 2019 und 365.

Beliebteste Forumthreads (12 Monate)

Zählerwerte bei Nichtablesen interpolieren

Forumthread: Zählerwerte bei Nichtablesen interpolieren

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Zählerwerte interpolieren in Excel

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

Zählerwerte bei Nichtablesen interpolieren

Forumthread: Zählerwerte bei Nichtablesen interpolieren

6 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Zählerwerte interpolieren in Excel

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread