Dynamischer Wochendurchschnitt - Excel-Versionen-Kompatibilität

[leiste22]

Hallo Freunde,

freue mich auf euren Input zu meinem ersten Beitrag, da ich hier leider wirklich nicht weiterkomme:

- Ich habe mir eine Formel geschrieben (in Office 365), die wunderbar funktioniert:

=+WENNFEHLER(SUMME(INDIREKT(+ADRESSE(ZEILE();SPALTE(INDEX(C4:AE4;VERGLEICH(WAHR;C4:AE4<>"";0))))&":"&+ADRESSE(ZEILE();SPALTE(INDEX(C4:AE4;VERGLEICH(WAHR;C4:AE4<>"";0)))+5)))/+ANZAHL2(INDIREKT(+ADRESSE(ZEILE();SPALTE(INDEX(C4:AE4;VERGLEICH(WAHR;C4:AE4<>"";0))))&":"&+ADRESSE(ZEILE();SPALTE(INDEX(C4:AE4;VERGLEICH(WAHR;C4:AE4<>"";0)))+5)));)

- Die Formel checkt im referenzierten Bereich von links nach rechts, wo der erste ausgefüllte Wert steht und bildet anschließend den Durchschnitt der 6 Werte nach rechts gehend. In meinem Fall ist, dass dazu da, um in einem Wochenreporting, eine automatische Berechnung des 6-Wochen-Durchschnitts zu machen, sobald eine weitere Woche hinzugefügt wird.

- Das Problem ist nur (und hier kenne ich mich nicht aus), dass ich die Datei via Sharepoint mit weiteren Kollegen teilen muss, die allerdings auf Office 2016 laufen. Hier ergeben sich zwei Fehlerarten:
a. Die Formel weist nur den ersten Wert aus und bildet keinen Durchschnitt. Außerdem wird Sie als "Matrixformel" mit geschwungenen Klammern ausgewiesen.
b. Die Formel weist eine Null aus, wenn ich die Matrix-Klammern entferne.

Weiter ist mir aufgefallen, dass wenn die Kollegen mir die Datei nach Ihrer Bearbeitung zurückschicken, dass ein @-Zeichen vor dem Bereichsbezug (C4:AE4) steht. Dann geht die Formel bei mir auch nicht mehr. Erst wenn ich das @-Zeichen wieder entferne. In der Office 2016 Version der Kollegen taucht dieses @-Zeichen nicht auf.

Ich vermute daher, dass ein Teil meiner Formel nicht mit Office 2016 kompatibel ist. Wenn ich mir den MS-Support zu den einzelnen Formeln anschaue, sind allerdings alle Funktionen auch mit MS 2016 kompatibel ausgewiesen.

Kann hier jemand weiterhelfen bzw. erklären warum es nicht klappt? Alternativ bin ich natürlich auch dankbar, wenn jemand eine alternative Formel hat, die mit allen MS Versionen funktioniert.

Vielen Dank
leiste22

[EA1950]

Hallo,

warum verwendest du eine so komplizierte Formel, wenn es auch wesentlich einfacher ginge:
Formel in Zelle AF4: =MITTELWERT(BEREICH.VERSCHIEBEN($C4;0;ZÄHLENWENN($C4:$AE4;"");1;6))
Formel in Zelle AF6; =MITTELWERT(BEREICH.VERSCHIEBEN($C6;0;ZÄHLENWENN($C6:$AE6;"");1;6))

Warum es zwischen den Excel-Versionen 2016 und 365/2019 Unterschiede bei den ARRAY/MATRIX-Funktionen gibt:

Bis zur Version Excel2016 musste man, um eine Array-Funktion eingeben zu können, zuerst den Bereich markieren, dann die Formel in die Formeleditierzeile eintippen und diesen Vorgang mit der Tastenkombination Strg+Umschalt+Eingabe abschließen. Hinterher zeigte sich diese Formel in geschweiften Klammern eingeschlossen - woran man erkannte, dass in einem gewissen Zellbereich, der auch nur aus 1 Zelle bestehen konnte, 1 (Array)-Formel enthalten ist.

Ab der Version Excel2019 bzw. Excel365 wurde dieses Modell umgestellt:
Man markiert 1 Zelle und gibt in diese eine Formel ein. Excel erkennt dann von sich aus, ob es dabei um eine Formel handelt, die noch weitere Zellen in ihrer Umgebung (rechts bzw. unterhalb) benötigt - im Excel-Slang wird dieses Verhalten als ein "verschüttetes Array" oder auch als "dynamisches Array" bezeichnet.
Dabei überprüft Excel, ob die Zellnachbarschaft zur Formelzelle genügend leere Zellen aufweist. Falls nicht, wird in der Formelzelle der Fehlerwert #ÜBERLAUF! angezeigt. War genügend Platz, werden die so okkupierten Zellen mit einem blauen Rand begrenzt, sobald man die Formelzelle aktiviert.
Man kann auf den Wert jeder einzelnen Zelle des Bereichs zugreifen wie gewohnt - man kann aber auch auf den Zellbereich insgesamt (=Matrix, Zeilenarray, Spaltenarray) zugreifen, indem man an die Zelladresse (Zelle mit der Formel = linke obere Zelle des Bereichs) das "#"-Zeichen anhängt.
Siehe dazu folgenden Microsoft-Link:
Suchergebnisse für "Versch%c3%bcttetes+Array" - Office.com (microsoft.com)

Ferner gab es auch eine Änderung beim Zugriff auf sogenannte strukturierte Tabellen mittels strukturierter Formeln - diese Änderung bzw. andere Darstellung mithilfe des "@"-Operators müsste sich - meines Wissens nach - automatisch an die jeweilige Version anpassen - sofern man den "@"-Operator korrekt eingesetzt hat(te).

[Der Steuerfuzzi]

Hallo,

Deine Formel schaut ja ganz schön wüst aus und INDIREKT würde ich zudem auch vermeiden.

Probier es mal so:

Code:

=MITTELWERT(INDEX(4:4;VERWEIS(99^9;SPALTE($C4:$AE4)/($C4:$AE4=""))+1):INDEX(4:4;VERWEIS(99^9;SPALTE($C4:$AE4)/($C4:$AE4=""))+6))

Je nachdem wie viele Formeln auf Deinem Blatt sind und je nachdem, wie lange die Berechnungszeit ist, würde ich auf volatile Funktionen verzichten. Da gehören z. B. INDIREKT oder BEREICH.VERSCHIEBEN dazu.

[leiste22]

Hallo Daniel & Anton,

vielen Dank für eure Rückmeldungen. Funktioniert beides wunderbar. Warum auch einfach, wenn es schwer geht.

Grüße
Steffen

[shift-del]

Warum schwer wenn es noch schwerer geht.

Vier Lösungsvarianten.