Das Jahr mit der größten Jahressumme ermitteln

[Th0mas]

Guten Morgen,

die folgende Tabelle als Beispiel:



Jedes Datum in Spalte A soll eine Transaktion darstellen, Artikel C gehört daher zum 23.11.2011 und in Spalte C ist die Summe der jeweiligen Transaktion notiert.

Im Rahmen einiger Auswertungen möchte ich nun wissen, in welchem Jahr das meiste Geld ausgegeben wurde, also die maximale Summe sowie die Angabe des entsprechenden Jahres.
Wie lässt sich das - bevorzugt ohne Zusatzspalten-/tabellen bzw. Makro - ermitteln?
Analog der obigen Tabelle müsste die Formel das Jahr 2012 mit 42,98 € errechnen, da in keinem anderen Jahr mehr Ausgaben anfielen.

---
PS: In einer anderen Auswertung konnte ich die Summe eines Jahres mit den meisten Transaktionen mit

Code: Alles auswählen

=SUMMENPRODUKT(JAHR(A2:A8)=JAHR(MODALWERT(A2:A8)); C2:C8)

ermitteln, wobei

Code: Alles auswählen

=JAHR(MODALWERT(A2:A8))

das entsprechende Jahr ausgibt.
In der obigen Tabelle wäre das das Jahr 2011 mit Gesamtausgaben in Höhe von 20,93 €.

[karolus]

Hallo
Du suchst nach →Daten→Datenpilot!
http://de.openoffice.org/doc/oooauthors ... npilot.pdf

Gruß Karo

[Th0mas]

Danke für die Antwort.
Ich habe mich mal ein wenig eingelesen und den Datenpiloten ausprobiert, allerdings finde ich ihn für meine Auswertung unpassend, da ich nur einen Wert ermittelt haben möchte und in allen Beispielen eher eine Menge an Werten gesucht wird. Weiterhin scheint man eine Datenpilot-Liste explizit aktualisieren zu müssen, das Ergebnis einer Formel sehe ich dagegen sofort.

Ist es denn mit einer Formel nicht möglich, das Jahr mit der größten Jahressumme anzugeben?

[karolus]

Hallo
Mit einer (normalen Calc-)Formel mit ziemlicher Sicherheit nicht, mit einer Hillfsspalte gehts.
In D2:

Code: Alles auswählen

=SUMMENPRODUKT(JAHR(A$2:A$100)=JAHR(A2);C$2:C$100)

Die Formel füllst du nach unten soweit wie nötig.
Das Jahr mit dem maximalen Umsatz erhälst du dann mit:

Code: Alles auswählen

=INDEX(JAHR(A2:A100);VERGLEICH(MAX(D2:D100);D2:D100;0))

___
In der Zeit in der du die Formeln einträgst , bist du mit dem Erstellen des Datenpiloten plus der Gruppierung nach Jahren schon fünfmal fertig und siehst (bei einer überschaubaren Anzahl an Jahren) auch ohne weitere Formel das Jahr mit dem Maximum.

Karo

[Th0mas]

Danke für die Formeln, scheint alles zu funktionieren.

Ich habe sie allerdings ein wenig abgeändert:

Code: Alles auswählen

=WENN(A2; SUMMENPRODUKT(JAHR(A$2:A$100)=JAHR(A2); C$2:C$100); "")

Offenbar interpretiert Calc eine leere Zelle dennoch als Jahr, jedenfalls ergibt =JAHR(A4) im obigen Beispiel die Zahl 1899 und entsprechend kann es zu einem Fehler oder einer falschen Summe kommen.

Code: Alles auswählen

=JAHR(INDEX(A2:A100; VERGLEICH(MAX(D2:D100); D2:D100; 0)))

Bei deiner Formel gibt Calc den Fehler #WERT! aus, ich habe dann die einzelnen Funktionen von innen nach außen getestet und festgestellt, dass die Jahreszahl korrekt angezeigt wird, wenn die Funktion JAHR() nach außen gesetzt wird.


Wirklich glücklich bin ich mit der Lösung jedoch noch nicht, da mir der Wertebereich genau wie beim Datenpiloten so fest erscheint:
Gebe ich bei der obigen Formel oder im Datenpiloten die Zeile 100 als untere Grenze ein und ein neuer Datensatz kommt hinzu, ich füge eine neue Zeile ein, dann wird die Formel nicht übernommen bzw. der Bereich nicht automatisch erweitert - im Gegensatz zu anderen auswertenden Formeln und einer Formel in einer bedingten Formatierung. Eine kurze Recherche hat ergeben, dass sich das wohl nicht einstellen lässt, also dass z. B. beim Einfügen einer neuen Zeile eine Formel angepasst übernommen wird. Ich nehme an, dass ich den Bereich größtmöglich festlegen muss.