Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.04.2024 00:08:11
In Ordner nur das Speichern von Dateien erlauben
19.04.2024 21:26:15
mehrfache Texte finden
19.04.2024 19:41:34
Bei VBA Ausführung Anzeige einfrieren
19.04.2024 17:05:40
Zählen von Zeilen, in denen in bestimmten Bereich Daten sind
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1388to1392
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1388to1392
1388to1392
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Formel Mittelwert flexibler Bereich gesucht

Formel Mittelwert flexibler Bereich gesucht
03.11.2014 08:05:27
Dennis
Guten Morgen,
ich bin auf der Suche nach einer Möglichkeit einen Mittelwert aus 1 bis n Zeilen in jeweils der gleichen Spalte zu bilden. n kann für einen ganz unterschiedlichen Bereich bzw. eine unterschiedliche Anzahl an Zeilen stehen. Die erste Zeile, die berücksichtigt werden soll ist die Zelle GU6, die Anzahl der Zeilen bestimmt sich dadurch, dass in Spalte A ein Wochentag steht. Die Formel soll in Zelle GV6 stehen. Mit Hilfe der Suche und Internet-Recherche habe ich eine Matrix-Formel abgewandelt, die leider nicht funktioniert.
GV6: {=MITTELWERT(--(GU6:INDEX(GU:GU;MIN(WENN(TEXT(SPALTE($A:$G);"TTTT")=$A6:$A104;ZEILE(6:104))))>0))}
Kann jemand helfen die Formel brauchbar zu machen?
Gruß
Dennis

25
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formel Mittelwert flexibler Bereich gesucht
03.11.2014 08:57:35
{Boris}
Hi Dennis,
ich hab noch nicht so genau die Vorstellung, was für Werte in Deinen Spalten stehen.
In Spalte GU stehen ab Zeile 6 irgendwelche Zahlen - aber was genau steht in Spalte A? Datümer? Oder Wochentage als TEXT? (die Anzahl der Zeilen bestimmt sich dadurch, dass in Spalte A ein Wochentag steht - versteh ich nicht). Wo steht der gesuchte Vergleichswert? ...
Gib doch bitte mal ein paar mehr Infos.
VG, Boris

AW: Formel Mittelwert flexibler Bereich gesucht
03.11.2014 09:18:28
Dennis
Ok, hier noch ein paar weitere Infos.
In Spalte A stehen normalerweise Vornamen von meinen Trainingsschülern. Dieser Block mit Trainingsschülern wird beendet durch einen Wochentag, da dann ein neuer Block mit einer anderen Trainingsgruppe beginnt. Diese Anzahl an Schülern kann immer unterschiedlich sein, mal 2,3,4,6. Früher hatte ich immer fest 4 Leute in einer Gruppe, daher war der Bereich immer klar. Jetzt möchte ich das ganze nach Möglichkeit etwas flexibler gestalten. Nachfolgend der Aufbau der Spalte A.
Spalte A
Montag
Max
Manfred
Peter
Ulrich
Dienstag
...
Jetzt soll für die jeweilige Gruppe der Mittelwert in Spalte GV ausgegeben werden, ausgehend von den Werten in Spalte GU.

Anzeige
AW: Formel Mittelwert flexibler Bereich gesucht
03.11.2014 09:26:35
{Boris}
Hi Dennis,
Du hast eingangs beschrieben, dass die gesuchte Formel in GV6 stehen soll.
So wie ich das verstehe, brauchst Du doch aber mehrere Formeln in Spalte GV - also immer nach "Beendigung" eines Vornamens-Blocks durch irgendeinen Wochentag.
Klär mich bitte auf :-)
VG, Boris

AW: Formel Mittelwert flexibler Bereich gesucht
03.11.2014 10:06:53
Dennis
Hallo Boris,
grundsätzlich soll die Formel in Spalte GV nach unten hin solange wiederholt werden, bis der Vornamen-Block beendet ist. Die Formel soll für jede Zeile innerhalb des Vornamen-Blocks gelten.
Ich hoffe, dass es jetzt verständlich war. Sonst würde ich eine Beispiel-Datei erstellen.

Anzeige
AW: Formel Mittelwert flexibler Bereich gesucht
03.11.2014 10:08:55
{Boris}
Hi Dennis,
Sonst würde ich eine Beispiel-Datei erstellen.
Das ist grundsätzlich nie eine schlechte Idee. Und am Besten dazu händisch die Wunschergebnisse eintragen.
VG, Boris

Wochentage in Zeile ?
03.11.2014 10:33:54
WF
Hi,
mal als Teilanregung:
Die Wochentage stehen in den Zeilen
{=KKLEINSTE(WENN((A$1:A$99=TEXT(SPALTE($A:$G);"TTTT"))*ZEILE($1:$99);(A$1:A$99=TEXT(SPALTE($A:$G); "TTTT"))*ZEILE($1:$99));ZEILE(A1)) }
runterkopieren
Die Mittelwerte dann zwischen den jeweiligen aufeinanderfolgenden Zeilen-Nummern
Salut WF

AW: Wochentage in Zeile ?
03.11.2014 21:32:35
Dennis
Hallo Boris,
habe deine Formel mal umgesetzt bzw. mit eingebaut. Jetzt noch eine Frage zu deiner Frage Wochentage in Zeile? Ich kann mit deiner Aussage "Die Wochentage stehen in den Zeilen" leider nichts anfangen. Bei mir stehen die Wochentage immer in Spalte A.

Anzeige
AW: Formel Mittelwert flexibler Bereich gesucht
03.11.2014 11:55:57
Dennis
Hallo.
So hier mal die Beispiel-Datei.
Es geht um das Arbeitsblatt Anwesenheit, Zelle ab GV6 abwärts, Bezug auf Spalte GU.
https://www.herber.de/bbs/user/93503.xlsm

hier nur bezogen auf Deine Ausgangsfrage ...
03.11.2014 17:23:12
neopa
Hallo Dennis,
... was Du möglicherweise meinst, ist folgende Formel:
={MITTELWERT(GU6:INDEX(GU:GU;MIN(WENN(TEXT(SPALTE($A:$G);"TTTT")=$A6:$A104;ZEILE(6:104)))-1))}
in der Zelle GV6. Diese Formel müsste nun noch dynamisch gemacht werden bzgl. der Startzelle und ...
Aber warum überhaupt so ein "Formelungetüm"? Dem könnte man sicherlich mit einer etwas anderen Datenstruktur entgegenwirken oder möglicherweise reicht ja schon einfach MITTELWERTWENNS() bezogen auf entsprechende Angaben in Spalte GP, oder ...
Gruß Werner
.. , - ...

Anzeige
AW: hier nur bezogen auf Deine Ausgangsfrage ...
04.11.2014 12:28:24
Dennis
Hallo Werner,
vielen Dank für die Formel, habe ich einegfügt und funktioniert. Wie könnte ich die Formel denn jetzt noch dynamisch bekommen?
Was meinst du mit anderer Datenstruktur bzw. hättest du eine Idee? Bin für vieles offen.
Diese Beispieldatei ist ja nur ein Ausschnitt aus dem Original. Im Original habe ich insgesamt 12 dieser Gruppen/Vornamen-Blöcke, die eine unterschiedliche Anzahl an Namen im Block haben.

zu Deiner Nachfrage ...
04.11.2014 14:13:04
neopa
Hallo Dennis,
... ich hab jetzt Deine Tabelle nicht nochmal geöffnet. In etwa hab ich diese aber noch vor Augen.
Hinweise zur Änderung der grundsätzlichen Datenstruktur würde den Rahmen der möglichen Hilfe innerhalb des Thread sprengen.
Ein evtl. einfache Änderung für Dich wäre, wenn Du eine zusätzliche Spalte hättest, wo Du das jeweilige Datum oder den Wochentag den Trainern zuordnen könntest. Dann könnte man mit MITTELWERTWENNS() arbeiten.
Alternativ dazu, bestünde die Möglichkeit eine vereinfachten Ermittlung vorzunehmen, wenn gewährleistet wäre, wenn die auszuwertenden Zahlen ohne Leerzelle (bzw. nicht ="") vorhanden wären.
Gruß Werner
.. , - ...

Anzeige
AW: zu Deiner Nachfrage ...
04.11.2014 14:35:37
Dennis
Hallo!
Danke für deine Antwort.
Eine Zuordnung Datum /Wochentag zu einem Trainer nicht möglich, da es auch vorkommen kann, dass mehrere Trainer an einem Wochentag Training geben.
Die andere Alternative wäre sicherlich möglich. Dies ist bisher nur für die "Schönheit" so programmiert worden, da ich Anfangs ganz starr immer 6 Zeilen je Block hatte und daher nicht mehr flexibel war, wenn jemand (ein Schüler) in die Gruppe/den Block dazugekommen ist.
Eine vereinfachte Ermittlung wäre immer gut, da mir zu meinem ersten Etappenziel noch 2 Formeln fehlen würden, die diese Flexibilität berücksichtigt.
Vielleicht wäre ja die vereinfachte Ermittlung im Rahmen dieses Threads möglich?

Anzeige
mit INDEX(), VERWEIS() und ...
04.11.2014 15:27:24
neopa
Hallo,
... folgende Formel in GV6:
=WENN(GU6="";"";WENN(GU7"";GV7;MITTELWERT(INDEX(GU:GU;VERWEIS(9;1/(GU$1:GU6="");ZEILE(GU:GU))):GU6)))
Formel nach unten kopieren.
Gruß Werner
.. , - ...

AW: mit INDEX(), VERWEIS() und ...
04.11.2014 16:28:34
Dennis
Danke für die Formel, funktioniert. Aber ein Problem, wenn die Zeile ja nicht leer ist, dann soll er ja den Wert aus der Spalte daneben schreiben. Nach einer Gruppe/Vornamen-Block kommt ja eine neue Gruppe. Somit ist diese Zeile ja nicht leer, und die Formel rechnet nicht mehr korrekt.
Wie könnte man das umgehen?

das sollte gegeben sein, denn ...
05.11.2014 14:56:21
neopa
Hallo Dennis,
... leere Zellen sind nicht wirklich notwendig, es reicht wenn die Bezugszelle ein ="" enthält. Und wenigstens das dürfte doch gewährleistet oder zu gewährleisten sein. Oder?
Gruß Werner
.. , - ...

Anzeige
AW: das sollte gegeben sein, denn ...
06.11.2014 11:27:09
Dennis
Hallo Werner,
ich glaube dass du das falsch verstanden hast oder ich das nicht richtig erläutert habe.
Die leeren Zellen sind ja genau das Problem, aber umgekehrt. Wenn die Zeile darunter leer ist, dann funktioniert alles. Aber genau anderesherum ist die Problematik, und auch die Regel. Die Zeile darunter ist fast immer nicht leer, und deshalb wird immer ein falscher Wert genommen.
Anbei nochmal meine Beispiel-Datei mit einem weiteren Vornamen-Block darunter.
https://www.herber.de/bbs/user/93592.xlsm
Gruß
Dennis

Anzeige
nun stellt es sich etwas anders dar ...
06.11.2014 14:25:51
neopa
Hallo Dennis,
... ist aber noch immer mit einer Formel lösbar.
In GW6 und nach unten kopieren:
=WENN(GU6="";"";WENN(ISTTEXT(GU6);"Teilnahme"&ZEICHEN(10)&"alle %";WENN(ISTZAHL(GU7);GW7; MITTELWERT(INDEX(GU:GU;VERWEIS(9;1/(GU$1:GU6="");ZEILE(GU:GU))):GU6))))
Gruß Werner
.. , - ...

AW: nun stellt es sich etwas anders dar ...
06.11.2014 21:41:44
Dennis
Hallo.
Vielen Dank, Formel funktioniert.
Ich würde jetzt noch gerne alle Namen der Spalte A der jeweiligen Gruppe miteinander verketten. Wäre das möglich? Entweder bis in der gleichen Spalte GQ die Bezeichnung "Gruppe" auftaucht oder in Spalte A wieder ein Wochentag auftaucht.
Optimal wäre das dann, wenn die Formel wieder einfach runterkopierbar wäre.
Vielen Dank für die Unterstützung.
Schönen Abend
Dennis

Anzeige
verkette der Namen in einer Hilfspalte ...
07.11.2014 09:55:53
neopa
Hallo Dennis,
... und die jeweilige Endverkettung in die die Zielzellen einfügen. Wäre das so ok?
Gruß Werner
.. , - ...

AW: verkette der Namen in einer Hilfspalte ...
07.11.2014 10:04:24
Dennis
Ich bin über jede Möglichkeit und Hilfe glücklich. Wenn es nur über eine Hilfsspalte machbar ist, dann würde ich das so akzeptieren.
Wie kann das realisiert werden?

dann angenommen ...
07.11.2014 14:13:02
neopa
Hallo Dennis,
... Deine Hilfsspalte sei die Spalte HD (kannst Du dann natürlich auch ausblenden)
Schreibe dann dort in HD6:
=WENN((B6="")*(B5="")+ISTTEXT(B5)*ISTTEXT(B6);"";WENN((ISTTEXT(B6)+(B6=""))*ISTZAHL(B5);"end"; WENN(ISTZAHL(B6)*((A5="")+ISTTEXT(B5));A6;HD5&", "&A6)))
und kopiere diese Formel nach unten.
Und angenommen Du willst das Ergebnis in Spalte in GQ, dann in GQ6:
=WENN(HD6="";"";WENN(HD6="end";"Gruppe";INDEX(HD6:HD19;VERGLEICH("end";HD6:HD19;)-1)))
und nach unten kopieren.
Gruß Werner
.. , - ...

AW: dann angenommen ...
10.11.2014 08:39:21
Dennis
Guten Morgen Werner und alle anderen,
jetzt bin ich nach dem Wochenende auch endlich dazu gekommen den Vorschlag einzubauen. Und was soll man sagen, funktioniert perfekt. Vielen Dank.
Gruß
Dennis

AW: dann angenommen ...
11.11.2014 11:38:51
Dennis
Hallo,
im Rahmen dieses Threads habe ich folgende Formel vorgeschlagen bekommen, die auch funktioniert:
=WENN(GU6="";"";WENN(ISTTEXT(GU6);"Teilnahme"&ZEICHEN(10)&"alle %";WENN(ISTZAHL(GU7);GW7; MITTELWERT(INDEX(GU:GU;VERWEIS(9;1/(GU$1:GU6="");ZEILE(GU:GU))):GU6))))
Wenn ich jetzt die letzte Zeile kopiere und direkt darunter einfüge, dann wird die Zeile sauber eingefügt bis auf der fett markierte Bereich oben in der Formel. Die Zeilennummern passen dann nicht mehr.
Kann man da Abhilfe schaffen, wenn ja, wie?

AW: dann angenommen ...
11.11.2014 22:03:36
Dennis
Zur Ergänzung. Ich möchte später mit einer Schaltfläche mit VBA neue Zeilen einfügen bzw. kopieren und einfügen können.

AW: dann angenommen ...
14.11.2014 08:25:07
Dennis
Guten Morgen zusammen,
hat noch jemand eine Idee wie man die Formel anpassen muss, dass die Formel auch korrekt arbeitet, wenn man eine Zeile einfügt?
=WENN(GU6="";"";WENN(ISTTEXT(GU6);"Teilnahme"&ZEICHEN(10)&"alle %";WENN(ISTZAHL(GU7);GW7; MITTELWERT(INDEX(GU:GU;VERWEIS(9;1/(GU$1:GU6="");ZEILE(GU:GU))):GU6))))
Wenn ich jetzt die letzte Zeile kopiere und direkt darunter einfüge, dann wird die Zeile sauber eingefügt bis auf der fett markierte Bereich oben in der Formel. Die Zeilennummern passen dann nicht mehr.

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige