Office: (Office 2013) Zählerstandsdifferenzen per Formel

Hallo,
ich suche nach einer Formel, um Zählerstandsveränderungen pro Kalenderjahr berechnen zu lassen.
Das Ausgangszahlenmaterial sieht so aus:

Es gibt also viele Daten mit Zählerständen. Dabei ist wichtig, dass es pro Jahr unterschiedlich viele Zählerstände gibt und keine Garantie besteht, dass zu einem bestimmten Datum auch genau ein Zählerstand. Es gibt also oft weder einen 1.1. oder 31.12. Eintrag.

Ich suche jetzt nach ein Formelkonstrukt (kein VBA, keine Pivottabellen), was mit zu einer in einer Zelle gegebenen Jahreszahl (z.B. "2017") die Differenz der Zählerstände pro Jahr auspuckt nach der Logik: Zählerstand am letzten Erfassten Datum des Jahres minus Zählerstand am letzten Erfassten Datum des Vorjahres.

Meine bescheidenen Künste reichen dazu leider nicht aus.

Jede Hilfe ist herzlich willkommen.

 

Du bist im falschen Forum. Bildbearbeitung ist ein anderes.
Bilder lade ich mir nicht runter, da Excel damit nichts anfangen kann.

Hochgeladene Bilder können zwar als solche in Excel importiert werden, sind jedoch bei der Lösung von Problemen nicht sehr hilfreich, da man die eigentlichen Daten nicht ohne große und zeitraubende Umwege direkt in die Tabelle übertragen kann.

Ich baue keine Datei nach, die Zeit hat schon jemand investiert.

Bilder statt Datei | Excel ist sexy!
Eine hochgeladene Arbeitsmappe erhöht die Wahrscheinlichkeit, dass Du eine Lösung für Dein Problem erhältst.
Erstelle folglich bitte eine Demomappe, aus der deine Aufgabenstellung klar erkennbar ist und lade diese Mappe mit einem aussagekräftigen Dateinamen hoch.
Der Name einer hochgeladenen Mappe wird im Beitrag automatisch angezeigt, sodass es bei Verwendung von aussagekräftigen Namen leichter fällt, sie später im Ablageordner wiederzufinden und sie gedanklich einem bestimmten Thema zuzuordnen. Namen wie Muster*, Test*, Mappe*, Beispiel*, Fehler*, Kalender*, UserForm*, Forum* usw. sind so allgemein, dass eine Zuordnung zu einem Thema unmöglich gemacht wird.
Es sollte ein aussagekräftiger Name sein.
Wenn du an Stelle einer Demomappe deine Originalmappe hochladen willst, diese aber sensible Daten enthält, kannst du diese Daten ändern. Schaue Datei
http://hajo-excel.de/gepackt/fremd/Datei_verschluesseln.zip
Bei Download beachte bitte: von unsicheren Servern file-upload lade ich keine Datei herunter (lt. Einschätzung meines Virenprogramms)

Das ist nur meine Meinung zu dem Thema.

 

Ich habe wunschgemäß eine Beispiel-Tabelle an den Startbeitrag gehängt.

 

Zählerstände

	C
2	24916,275
3
4
5
6	92969,079
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23	24419,873
24
25
26
27

Schriftart wird in dieser Tabelle nicht dargestellt

verwendete Formeln
Zelle	Formel	Bereich	N/A
C2:C27	=WENN(SUMMENPRODUKT((JAHR($A$2:A2)=JAHR(A2))*1)=1;SUMMENPRODUKT((JAHR($A$2:$A$27)=JAHR(A2))*$B$2:$B$27);"")

Zahlenformate
Zelle	Format	Inhalt
C2	'#.##0,00		24916,275
C3:C5, C7:C22, C24:C27	'#.##0,00
C6	'#.##0,00		92969,079
C23	'#.##0,00		24419,873

Zellen mit Format Standard werden nicht dargestellt

Schriftformate
Zelle	Rot	Grün	Blau	Color	Stil	Unterstreichung	Effekte	Durchgestrichen	Schriftart
C2:C27	0	0	0	0					Calibri

Zellen mit Schriftformatierung automatisch werden nicht dargestellt

http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip

http://Hajo-Excel.de/tools.htm

Add-In-Version 25.24 einschl. 64 Bit

XHTML-Tabelle zur Darstellung in Foren, einschl. Funktion zum Zurücklesen in eine Excel-Tabelle

Gruß Hajo

 

Folgende Datei in 365 oder XLWeb öffnen (interpolierte Jahresübergänge, besser, als das, was Du willst):

 

Danke Dir erstmal. Ich weiss aber nicht so ganz, was Du da berechnest. Im Beispieljahr 2017 würde ich per Hand den Stand vom 27.12.2017 nehmen (letzte Ablesung 2017) und davon den Stand vom 29.12.2016 (letzte Ablesung 2016) abziehen.
Damit komme ich auf ein Ergebnis von "872 m³". Deine Formel wirft riesige Zahlen aus. So wie ich es lese, ca. 92.969 für das Jahr 2017.

 

Auch Dir Danke für die blitzschnelle Reaktion!
Ich glaube, das bildet meinen Wunsch grob ab.

Mir fehlt aber jegliche Ahnung, was ich da sehe und wie ich es anpassen müsste für eine größere Tabelle und ähnliche Fälle.

Fragen:

1. Wozu dient die zusätzlich sortierte Tabelle?
2. Wie bekomme ich die Inhalte dahin und was sind das für eigenartige Formeln darin?
   =_xlfn._xlws.SORT(A2:B27)
3. Warum hast Du die Formeln "ZEILE(A2016)" verwendet, anstelle einfach die Zahleszahl "2016" hinzuschreiben?
4. Wie kommt ein normalsterblicher an so eine Formel:
   =_xlfn.LAMBDA(_xlpm.z;_xlpm.xy;_xlfn.LET(_xlpm.x;INDEX(_xlpm.xy;;1);_xlpm.y;INDEX(_xlpm.xy;;2);_xlpm.c;VERGLEICH(_xlpm.z;_xlpm.x);_xlpm.d;_xlpm.c+1;
   (INDEX(_xlpm.y;_xlpm.d)-INDEX(_xlpm.y;_xlpm.c))*(_xlpm.z-INDEX(_xlpm.x;_xlpm.c))/(INDEX(_xlpm.x;_xlpm.d)-INDEX(_xlpm.x;_xlpm.c))+INDEX(_xlpm.y;_xlpm.c)))(--("1.1."&G3);_xlfn.ANCHORARRAY(D$2))
   

 

Hallo,

oder so.

 

Gut dann habe ich die Aufgabe falsch verstanden. Wie Du auf Deine Werte komst ist mir nicht klar.

Zählerstände

	D
2
3
4
5	6062,42
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22	5093,18
23
24
25
26
27	4770,84

Schriftart wird in dieser Tabelle nicht dargestellt

verwendete Formeln
Zelle	Formel	Bereich	N/A
D2: D27	=WENN(SUMMENPRODUKT((JAHR($A$2:A2)=JAHR(A2))*1)=SUMMENPRODUKT((JAHR($A$2:$A$27)=JAHR(A2))*1);B2;"")

Zahlenformate
Zelle	Format	Inhalt
D5	'#.##0,00		6062,42
D6: D21, D23: D26	'#.##0,00
D22	'#.##0,00		5093,18
D27	'#.##0,00		4770,84

Zellen mit Format Standard werden nicht dargestellt

Schriftformate
Zelle	Rot	Grün	Blau	Color	Stil	Unterstreichung	Effekte	Durchgestrichen	Schriftart
D2: D27	0	0	0	0					Calibri

Zellen mit Schriftformatierung automatisch werden nicht dargestellt

http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip

http://Hajo-Excel.de/tools.htm

Add-In-Version 25.24 einschl. 64 Bit

XHTML-Tabelle zur Darstellung in Foren, einschl. Funktion zum Zurücklesen in eine Excel-Tabelle

Ich bin dann rauis, da Aufgabe nicht klar.

Gruß Hajo

 

1. Mutig vorwärts, gläubig aufwärts, dankbar rückwärts.
2. Welche Inhalte? Ich hatte Dir nur das Öffnen in 365 oder XLWeb erlaubt!
3. Ich hätte auch inkrementieren können, aber so geht es doch auch.
4. Interpolation 1- oder 2dimensional. =TREND() ginge übrigens auch.

 

Ich kann das gerne nochmal erklären.

Ende 2016 am 29.12. wurde der Zählerstand 5.021 m3 abgelesen als Jahresendstand.
Ende 2017 am 27.12. wurde der Zählerstand 5.893 m3 abgelesen als Jahresendstand.
Also wurden in 2017 grob 872 m3 verbraucht.

Ich suche eine Formle, die mir bei einfache Nennung der Jahreszahl als Input Ergebnisse berechnet, wie also für 2017 als Beispiel die 872,43 m3.

Die Aufgabe ist m.E. primär, die beiden richtigen Datenzeilen zu finden (Letzte Ablesungen des Jahres), um dann über die Differenz zum Ergebnis zu kommen.
Bisher mache ich das per Hand, aber soabld sich was verschiebt ist das wieder kaputt, also suche ich eine Formellösung.

 

Wenn ich als Präfix "Office 2013" hinschreibe, ist das ein Hinweis, dass ich eine Lösung in einer lokalen Office 2013 Lösung brauche.

 

Der Punkt ist leider, dass ich ja genau das manuelle Suchen der richtigen Einträge vermeiden will.
Dein Ansatz macht alles aber immerhin robuster gegen die vollmanuelle Variante, wo schon eine Zeileneinfügung alles kaputt macht.

 

a) Dann darfst Du meine Lösung mit explizit genannter Voraussetzung nicht beachten. Oder anders gesagt: Ich antworte dem Forum, nicht Dir.
b) XLWeb ist kostenlos.
c) Sehr oft sind im Profil genannte XL-Versionen tatsächlich nicht angepasst, so dass man meine Lösung dann doch gebrauchen kann. Hier halt nicht.
d) In dem Interpolations-Link gibt es auch einen Verweis zu einer Lösung für altes Excel.

 

Hallo,

dann so.