Netz-Treff - Excel: Letzte x Werte summieren

#442101

Howie

10.11.2020, 19:17:04

Excel: Letzte x Werte summieren (pc.sw.office)

Hi,

ich habe mal wieder ein kleines Excel-Problem, dessen Lösung zwar funktioniert, aber leider nicht immer.
Ich möchte in einer fortlaufenden Tabelle immer die letzten 3 Werte summieren.
Dazu habe ich die Funktionen SUMME() und Bereich.Verschieben() verwendet.

Dieser Lösungsweg funktioniert so lange, wie es keine leeren Zellen bzw. Null in der Spalte gibt. Vgl. Bildanhang.
Kann ich diese Funktionen noch irgendwie mit einem Parameter bestücken, um das abzufangen? Ich habe nichts gefunden. Oder gibt es noch eine andere Lösung - außer VBA? Das kann ich nicht.

(Excel 2016)

[image]

Danke schon mal im Voraus!

--
Viele Grüße
Howie

#WirSindMehr
Für Demokratie, Frieden und Freiheit. Und gegen Gewalt, Hass und Hetze.
_____________________________________________________
"Die Zeichnung eines Kindes wird in 1000 Jahren eine
größere Antiquität sein als der teuerste Computer."

#442106

manes

Köln,
11.11.2020, 01:55:01

@ Howie

Excel: Letzte x Werte summieren

antworten

> das abzufangen? Ich habe nichts gefunden. Oder gibt es noch eine andere
> Lösung - außer VBA? Das kann ich nicht.
Käme denn VBA in Betracht? Ggf. könnte ich morgen mal was stricken.

FFPX
Manes

--
Also wenn ich ehrlich sein sollte, müsste ich lügen!

#442107

fuchsi

Niederösterreich,
11.11.2020, 08:14:22
(editiert von fuchsi, 11.11.2020, 08:15:15)

@ Howie

Excel: Letzte x Werte summieren (ed)

antworten

Excel – Letzte beschriebene Zeile ermitteln – AGGREGAT

Das kann man mit Aggregat machen. Hier z.B.: wird die letzte benutzte Zeile der Spalte A gesucht

[image]

--
mein privates Hobby. www.ffzell.at

#442108

fuchsi

Niederösterreich,
11.11.2020, 08:23:39
(editiert von fuchsi, 11.11.2020, 08:25:20)

@ fuchsi

Excel: Letzte x Werte summieren (ed)

antworten

Wenn man die Formel nur minimal ändert, erhält man die 3. höchste Zeilennummer in der noch eine Wert steht

Jetzt braucht man in der Spalte A nur noch summieren von

=AGGREGAT(14;4;(A:A<>"")*ZEILE(A:A);3)

bis

=AGGREGAT(14;4;(A:A<>"")*ZEILE(A:A);1)

und erhält die Summe der letzten 3 gültigen Werte

[image]

--
mein privates Hobby. www.ffzell.at

#442109

fuchsi

Niederösterreich,
11.11.2020, 08:50:53

@ fuchsi

Excel: Letzte x Werte summieren

antworten

Und zum Schluss noch die Summenformel

Ich habe hier der Einfach halber die Ergebnisse der Zeilenummer in eigenen Zellen belassen
Wenn man das in einer Formel haben möchte müsste man nur die Zellennummer mit der jeweiligen Aggregat Funktion ersetzen

=SUMME(INDIREKT("A"&D3&":A"&D5))

[image]

--
mein privates Hobby. www.ffzell.at

#442110

Howie

11.11.2020, 09:31:42

@ fuchsi

Wow, super!

antworten

So kann ein Tag beginnen!
Du hast mein Problemchen gelöst! :ok:

Die Funktion AGGREGAT() war mir unbekannt.

Herzlichen Dank!

#442112

fuchsi

Niederösterreich,
11.11.2020, 09:41:10
(editiert von fuchsi, 11.11.2020, 09:44:17)

@ Howie

Wow, super! (ed)

antworten

> Die Funktion AGGREGAT() war mir unbekannt.

Mit der sollte man sich mal näher beschäftigen. Die ist extrem mächtig.

Um mal die Funktion in dem beispiel zu erklären:

=AGGREGAT(14;4; BEREICH ;1)

14 ... 1 : bedeutet AGGREGAT soll den 1. größten Wert aus Bereich zurückgeben,
14 ... 3 : bedeutet AGGREGAT soll den 3. größten Wert aus Bereich zurückgeben,

Die 4 bedeutet lediglich, dass die Funktion Aggregat keine Zellen aus dem Bereich ignorieren soll. Ist z.B.: beim Durchschnitt interessant, ob leere Zellen als 0 oder ignoriert werden. In unserem Fall ist es eigentlich egal, da wir (wie später erklärt keinen eigentlichen Excel Bereich übergeben, sondern eine fertig Zeilenummernliste)

Kommen wir zum Bereich
(A:A<>"")*ZEILE(A:A)

Diese Formel gibt eine Liste aller Zeilennummern zurück, in denen was steht.
Der erste teil, ist eine Bedingung (Zeile nicht leer) und wird mit der Zeilennummer multipliziert.

Also in meinem Excel Beispiel ergibt das eine Liste 1,2,3,4,5,6,7,8,10,11,12,15,16,17,18,21

Der höchste Wert ergibt damit die letzte Zeile mit einem Wert. der dritthöchste Wert ergibt damit die 3. letzte Zeile mit einem Wert.
Wenn man die Bedingung (A:A<>"") noch genauer definiert (muss z.B.: nummerisch sein), kann man auch Zeilen mit Text damit ausschließen.

--
mein privates Hobby. www.ffzell.at

#442114

Howie

11.11.2020, 10:52:53

@ fuchsi

Wow, super!

antworten

:ok:
:clap: