Microsoft Excel

Herbers Excel/VBA-Archiv

Formel Mittelwert flexibler Bereich gesucht

Betrifft: Formel Mittelwert flexibler Bereich gesucht von: Dennis Menger
Geschrieben am: 03.11.2014 08:05:27

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

  

Betrifft: AW: Formel Mittelwert flexibler Bereich gesucht von: {Boris}
Geschrieben am: 03.11.2014 08:57:35

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


  

Betrifft: AW: Formel Mittelwert flexibler Bereich gesucht von: Dennis Menger
Geschrieben am: 03.11.2014 09:18:28

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.


  

Betrifft: AW: Formel Mittelwert flexibler Bereich gesucht von: {Boris}
Geschrieben am: 03.11.2014 09:26:35

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


  

Betrifft: AW: Formel Mittelwert flexibler Bereich gesucht von: Dennis Menger
Geschrieben am: 03.11.2014 10:06:53

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.


  

Betrifft: AW: Formel Mittelwert flexibler Bereich gesucht von: {Boris}
Geschrieben am: 03.11.2014 10:08:55

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


  

Betrifft: Wochentage in Zeile ? von: WF
Geschrieben am: 03.11.2014 10:33:54

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


  

Betrifft: AW: Wochentage in Zeile ? von: Dennis Menger
Geschrieben am: 03.11.2014 21:32:35

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.


  

Betrifft: AW: Formel Mittelwert flexibler Bereich gesucht von: Dennis Menger
Geschrieben am: 03.11.2014 11:55:57

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


  

Betrifft: hier nur bezogen auf Deine Ausgangsfrage ... von: neopa C (paneo)
Geschrieben am: 03.11.2014 17:23:12

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
.. , - ...


  

Betrifft: AW: hier nur bezogen auf Deine Ausgangsfrage ... von: Dennis Menger
Geschrieben am: 04.11.2014 12:28:24

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.


  

Betrifft: zu Deiner Nachfrage ... von: neopa C (paneo)
Geschrieben am: 04.11.2014 14:13:04

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
.. , - ...


  

Betrifft: AW: zu Deiner Nachfrage ... von: Dennis Menger
Geschrieben am: 04.11.2014 14:35:37

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?


  

Betrifft: mit INDEX(), VERWEIS() und ... von: neopa C (paneo)
Geschrieben am: 04.11.2014 15:27:24

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
.. , - ...


  

Betrifft: AW: mit INDEX(), VERWEIS() und ... von: Dennis Menger
Geschrieben am: 04.11.2014 16:28:34

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?


  

Betrifft: das sollte gegeben sein, denn ... von: neopa C (paneo)
Geschrieben am: 05.11.2014 14:56:21

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
.. , - ...


  

Betrifft: AW: das sollte gegeben sein, denn ... von: Dennis Menger
Geschrieben am: 06.11.2014 11:27:09

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


  

Betrifft: nun stellt es sich etwas anders dar ... von: neopa C (paneo)
Geschrieben am: 06.11.2014 14:25:51

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
.. , - ...


  

Betrifft: AW: nun stellt es sich etwas anders dar ... von: Dennis Menger
Geschrieben am: 06.11.2014 21:41:44

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


  

Betrifft: verkette der Namen in einer Hilfspalte ... von: neopa C (paneo)
Geschrieben am: 07.11.2014 09:55:53

Hallo Dennis,

... und die jeweilige Endverkettung in die die Zielzellen einfügen. Wäre das so ok?

Gruß Werner
.. , - ...


  

Betrifft: AW: verkette der Namen in einer Hilfspalte ... von: Dennis Menger
Geschrieben am: 07.11.2014 10:04:24

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?


  

Betrifft: dann angenommen ... von: neopa C (paneo)
Geschrieben am: 07.11.2014 14:13:02

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
.. , - ...


  

Betrifft: AW: dann angenommen ... von: Dennis Menger
Geschrieben am: 10.11.2014 08:39:21

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


  

Betrifft: AW: dann angenommen ... von: Dennis Menger
Geschrieben am: 11.11.2014 11:38:51

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?


  

Betrifft: AW: dann angenommen ... von: Dennis Menger
Geschrieben am: 11.11.2014 22:03:36

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.


  

Betrifft: AW: dann angenommen ... von: Dennis Menger
Geschrieben am: 14.11.2014 08:25:07

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.


 

Beiträge aus den Excel-Beispielen zum Thema "Formel Mittelwert flexibler Bereich gesucht"