Automatisch fortlaufender Zellverweis

[CHH]

Liebes Forum,

zur Zeit mühe ich mich manuell ab, regelmäßig folgende Formel zu ergänzen. 
Ich habe eine Tabelle mit 51 in gleicher Reihenfolge bleibenden Positionen, die täglich weiter kopiert werden.
Aus den Daten bilde ich mir M&SD...aber das ist eigentlich nicht relevant. Es geht darum, dass ich folgende Formel
täglich ergänze. In diesem Beispiel wäre es AN3726. Mit welcher Formel schaffe ich es, dass automatisch der Bezug +51 Positionen angehangen wird, wenn bei AN neue Wertepositionen eingefügt werden.

=WENNFEHLER(MITTELWERT(AN3;AN54;AN105;AN156;AN258;AN207;AN309;AN360;AN411;AN462;AN564;AN513;AN615;AN666;AN717;AN768;AN819;AN870;AN921;AN972;AN1023;AN1074;AN1125;AN1176;AN1227;AN1278;AN1329;AN1380;AN1431;AN1482;AN1533;AN1584;AN1635;AN1686;AN1737;AN1788;AN1839;AN1890;AN1941;AN1992;AN2043;AN2094;AN2196;AN2298;AN2349;AN2400;AN2451;AN2502;AN2553;AN2604;AN2655;AN2706;AN2757;AN2859;AN2910;AN2961;AN3012;AN3063;AN3114;AN3165;AN3216;AN3267;AN3318;AN3369;AN3420;AN3471;AN3522;AN3522;AN3573;AN3624;AN3675);"")

Vielleicht kann mir jemand helfen!

Danke und liebe Grüße!

[WillWissen]

Moin,

ohne Kenntnis deiner Datei kann ich dir nur allgemein raten, deine Tabelle als intelligente Tabelle zu formatieren. Dadurch erweitern sich Formeln automatisch.

[RPP63]

Moin!
Du hast zweimal AN3522 in der Formel, daher rechnet sie falsch!  :21:
Prinzipiell empfinde ich den Tabellenaufbau als zumindest ungewöhnlich.
Um was handelt es sich bei den jeweils 50 "unnützen" Zeilen?
Da böte sich doch eher eine Hilfsspalte mit der Tagessumme or whatever mittels simpler Formel an.
Da kannst Du MITTELWERT auf diese Hilfsspalte anwenden, denn diese Funktion lässt leere Zellen oder auch den mittels Leerstring "" erzeugten Text außen vor.

Gruß Ralf

[echo]

Hallo
deine Formel auf die ganze Spalte AN:
=MITTELWERT(WENN((AN:AN<>"")*(REST(ZEILE(AN:AN);51)=3);AN:AN;""))
(Matrix-Formel : Eingabe mit STRG + Shift + Return abschließen)
LG Holger

[RPP63]

Moin Holger!
Matrixformel auf ganze Spalte?
Ich warte erst mal auf ein vernünftiges Beispiel,
lege mich aber schon mal auf eine Pivot fest.

Gruß Ralf

[CHH]

Klasse - erstmal danke für eure schnellen Antworten.
Ich erläutere das ganze nochmal genauer.

A3 - A53 (usw)sind jeweils Namen von Personen in fester Reihenfolge hinterlegt. Diesen Abschnitt kopiere ich Tag für Tag im Block weiter. Für jeden Tag werden den Personen in AN3-AN53 (usw) ein Wert zugeordnet.  Dieses fortlaufende System muss bestehen bleiben und kann nicht geändert werden(etliche weitere Bezüge in der Exel-Datei). 


Die euch vorgestellte Formel liegt weiter seitlich im Dokument und soll mir jeweils einen Mittelwert aller vorhandenen Daten für die Person berechnen. Dieser Block ist fest auf ED3-ED53 und wird nicht weiterkopiert. Hier füge ich nur händisch den fortlaufenden Bezug auf die jeweiligen Zellen ein und würde dieses gerne automatisch bekommen, wenn ich die Namensliste/Werte weiterkopiere.

[WillWissen]

Hi,

dann lad' doch mal eine Beispieldatei hoch. http://www.clever-excel-forum.de/Thread-...ng-stellen
Der Aufbau muss zwingend deinem Original gleichen; sensible Daten bitte anonymisieren. Es reichen etwa 15-20 Datensätze.

[schauan]

Hallo chh,

hast Du es denn mal mit der Formel von Echo-Holger versucht?
Du kannst den Bereich auch einschränken, z.B. auf $AN$1:$AN$16000
Für die verschiedenen Zeilen im Bereich nimmst Du verschiedene Rester, also 3, 4, 5 usw.

Ansonsten gibt es auch MITTELWERTWENN, wo Du z.B. Namen als Bedingung festlegen könntest.

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D
1	Z1	1	Z1	2,5
2	Z2	2	Z3	4,5
3	Z3	3
4	Z1	4
5	Z2	5
6	Z3	6

Zelle	Formel
D1	=MITTELWERTWENN($A$1:$A$6;C1;$B$1:$B$6)
D2	=MITTELWERTWENN($A$1:$A$6;C2;$B$1:$B$6)

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Oder eben Pivot, wie Ralf es vorgeschlagen hat.

[CHH]

Danke an alle! @schauan - dein Tip war die passende Lösung! Damit habe ich jetzt erstmal Ruhe =)

Geklappt hat es mit --> =MITTELWERTWENN($B$3:$B$20000;Tabelle1[@Name];$AN$3:$AN$10000)

[RPP63]

Geklappt hat es mit --> =MITTELWERTWENN()

Natürlich klappt es damit; natürlich auch mit der Matrixformel von Holger.
Ich verweise dennoch ausdrücklich auf meine Signatur!
Eine Einarbeitung in Pivot-Tables hätte Dir weiter geholfen als eine auf Dein spezielles Problem zugeschnittene Formel.