Registriert seit: 27.08.2018
Version(en): 360
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!
Registriert seit: 10.04.2014
Version(en): Microsoft 365, mtl. Kanal
Moin,
ohne Kenntnis deiner Datei kann ich dir nur allgemein raten, deine Tabelle als intelligente Tabelle zu formatieren. Dadurch erweitern sich Formeln automatisch.
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Registriert seit: 12.10.2014
Version(en): 365 Insider (32 Bit)
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
Gib einem Mann einen Fisch und du ernährst ihn für einen Tag.
Lehre einen Mann zu fischen und du ernährst ihn für sein Leben. (Konfuzius)
Registriert seit: 14.04.2017
Version(en): 2016/19
27.08.2018, 10:01
(Dieser Beitrag wurde zuletzt bearbeitet: 27.08.2018, 10:01 von 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
Registriert seit: 12.10.2014
Version(en): 365 Insider (32 Bit)
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
Gib einem Mann einen Fisch und du ernährst ihn für einen Tag.
Lehre einen Mann zu fischen und du ernährst ihn für sein Leben. (Konfuzius)
Registriert seit: 27.08.2018
Version(en): 360
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.
Registriert seit: 10.04.2014
Version(en): Microsoft 365, mtl. Kanal
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.
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Registriert seit: 10.04.2014
Version(en): 97-2019 (32) + 365 (64)
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.
. \\\|/// Hoffe, geholfen zu haben.
( ô ô ) Grüße, André aus G in T
ooO-(_)-Ooo (Excel 97-2019+365)
Registriert seit: 27.08.2018
Version(en): 360
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)
Registriert seit: 12.10.2014
Version(en): 365 Insider (32 Bit)
(28.08.2018, 14:29)CHH schrieb: 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.
Gib einem Mann einen Fisch und du ernährst ihn für einen Tag.
Lehre einen Mann zu fischen und du ernährst ihn für sein Leben. (Konfuzius)
