Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 23:47:55
Datum ausweisen, wenn definierte Summe erreicht ist
25.04.2024 21:40:53
Excel Datenquelle aus dem Web mit Anmeldung?
25.04.2024 21:21:16
Zellkommentar in UserForm anzeigen und bearbeiten
25.04.2024 21:13:13
Kalenderwochen addieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1552to1556
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
1552to1556
1552to1556
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Mittelwert über Klassen (auto)

Mittelwert über Klassen (auto)
19.04.2017 14:52:10
dome
Hi Leute,
Gleich noch eine kurze Frage hinterher:
https://www.herber.de/bbs/user/112973.xlsx
Ich möchte eine Formel so, dass sich der Mittelwert für bestimmte Klassen berechnen lässt, ohne dass ich jeweils für jede Klasse die Zellbezüge anpassen muss.
Ich hab schon was versucht, nur leider liefert mir das nicht im Ansatz das korrekte Ergebnis..
Vielen Dank für Euren Input.
Es grüsst,
Dome

31
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: nachgefragt ...
19.04.2017 15:02:42
...
Hallo Dome,
... warum denn von I1:I8 und nicht von I1:I3 und dann I4:I8 ...
Gruß Werner
.. , - ...
AW: nachgefragt ...
19.04.2017 15:06:23
dome
Guten Tag Werner,
Das ist der springende Punkt - kann ich mir vorstellen.
Leider ist das nicht anders möglich. Das muss aufgrund einer bestimmten Geschäftspraxis zwingend so sein.
Grüsse,
Dome
AW: noch nicht eindeutig ...
19.04.2017 15:26:38
...
Hallo Dome,
... wenn es in H1 mit z.B. 7 beginnt ist die erste Auswertung trotzdem von 7:25?
Gruß Werner
.. , - ...
AW: noch nicht eindeutig ...
19.04.2017 15:35:19
dome
Hallo Werner,
Zwingend an dieser Geschäftspraxis ist auch die Tatsache, dass es immer bei 18 beginnen wird (und von da an bis 25).
Würde die 18 aber tatsächlich mal fallen, wäre die erste Klassen-Grenze wohl nicht mehr bei 25. (Das kann im Moment allerdings vernachlässigt werden.)
LG
Dome
Anzeige
AW: dann mal etwas geschraubtes ...
19.04.2017 16:04:06
...
Hallo Dome,
... es gibt sicherlich viel kürzere Lösungsformel, aber ich wollte einfach mal was anderes aufstellen:
In J1:

=MITTELWERT(INDEX(I:I;VERGLEICH(SVERWEIS(H1;{0.18;26.25;31.30;36.35;41.41};2);H:H;)):INDEX(I:I; VERGLEICH(SVERWEIS(H1;{0.25;26.30;31.35;36.40;41.41};2);H:H;))) und runter kopieren.
Gruß Werner
.. , - ...
AW: #Bezug
19.04.2017 16:21:28
dome
Hi Werner,
Das ergibt bei mir leider einen Bezugsfehler.
Ausserdem sehe ich in der Matrix den Wert 3, welchem wohl eine Nullstelle abhanden gekommen ist. Ebenso dann im zweiten Verweis.
LG
Dome
AW: also dann doch andere Daten? ...
19.04.2017 16:27:27
...
Hallo Dome,
... oder?
Sieh mal:
 GHIJ
1SOLL1843,06819%25,32302%
2 1921,70778%25,32302%
3 2024,71151%25,32302%
4 2121,79822%25,32302%
5 2267,68436%25,32302%
6 235,87115%25,32302%
7 2410,89010%25,32302%
8 256,85285%25,32302%
9 2643,61507%46,19190%
10 2778,64324%46,19190%
11 2841,65503%46,19190%
12 2985,97479%46,19190%
13 3020,41044%46,19190%
14 3129,39353%36,18735%
15 3297,92637%36,18735%
16 334,47855%36,18735%
17 3428,98039%36,18735%
18 3535,93483%36,18735%
19 3698,59503%49,36474%
20 3790,55271%49,36474%
21 3813,83331%49,36474%
22 3927,08984%49,36474%
23 4030,18269%49,36474%

Formeln der Tabelle
ZelleFormel
J1=MITTELWERT(INDEX(I:I;VERGLEICH(SVERWEIS(H1;{0.18;26.25;31.30;36.35;41.41};2); H:H;)):INDEX(I:I;VERGLEICH(SVERWEIS(H1;{0.25;26.30;31.35;36.40;41.41};2); H:H;)))

Gruß Werner
.. , - ...
Anzeige
Alternativ
20.04.2017 04:44:21
lupo1
rein mathematisch (damit schneller)
und unendlich kopierbar (keine Begrenzung) in
1 x 8er Block und
n x 5er Blöcke
J1:
=MITTELWERT(
INDEX(I:I;WENN(ZEILE(I1)<9;1;KÜRZEN(ZEILE(I2)/5)*5-1)):
INDEX(I:I;WENN(ZEILE(I1)<9;8;KÜRZEN(ZEILE(I2)/5)*5+3)))
AW: Alternativ
20.04.2017 08:28:30
Dome
Guten Morgen zusammen,
Beides sehr spannende Ansätze. Werde mir diese heute mal etwas genauer noch anschauen um zu verstehen, warum sie funktionieren. Vielen herzlichen Dank für Eure Hilfe und weiterhin einen angenehmen Tag. Ihr habt mir sehr geholfen.
Grüsse,
Dome
Neuer Input
21.04.2017 10:04:36
dome
Guten Morgen zusammen,
Habe gerade noch neuen Input zur Berechnung erhalten.
Das Ende soll variabel sein und die letzte Klasse geht immer nur bis (Ende - 1).
Habe versucht, das in der Beispielmappe darzustellen.
Mögt Ihr Euch das nochmals kurz ansehen bitte? Vielen Dank.
https://www.herber.de/bbs/user/113025.xlsx
LG
Dome
Anzeige
ANZAHL
21.04.2017 10:47:00
lupo1
J1:
=MITTELWERT(
INDEX(I:I;WENN(ZEILE(I1)<9;1;KÜRZEN(ZEILE(I2)/5)*5-1)):
INDEX(I:I;WENN(ZEILE(I1)<9;8;MIN(ANZAHL(I:I)-1;KÜRZEN(ZEILE(I2)/5)*5+3))))
wenn die Formel nur bis zum Ende der Daten gezogen wird (dann braucht man die zweite Formel-Zeile nicht zu ändern)
AW: ANZAHL
21.04.2017 11:27:48
dome
Die Daten sollen immer bis zum Ende durchgezogen sein. (hier 40)
Allerdings möchte ich das Ende auch vorverlegen können und mit dieser Formel passt es mir das Ende nicht an. (Was dann zwischen dem vorgezogenen und den 40 passiert ist mir egal, denn das wird in der weiteren Berechnung gar nicht mehr berücksichtigt. Da kann dann also stehen was will..)
Eine Alternative habe ich in der Datei angezeigt..
https://www.herber.de/bbs/user/113033.xlsx
Anzeige
AW: ANZAHL
21.04.2017 11:29:04
dome
Ich würde eben gerne die Formel nicht manuell anpassen/nach unten ziehen müssen, jedes Mal wenn ich das Ende verändere..
Was soll das denn jetzt?
21.04.2017 11:46:35
lupo1
Jetzt bist Du es doch, der manuell am Ende nur 4 statt 5 Daten zeigt. Was ist, wenn nur 3 oder weniger übrig sind?
Und die automatische Unsichtbarwerdung von starr eingefügten Formeln erledigst Du mit =WENN(ISTLEER(Ix);"";Formel). Dafür brauchst Du mich nicht.
AW: Was soll das denn jetzt?
21.04.2017 15:34:47
dome
Erstmal vielen lieben Dank für Deine Hilfe, ich weiss das sehr zu schätzen.
Die Frage ist eigentlich, wie man das Ende als Variable in die Formel bekommt, so dass ich dieses z.B. in Zelle N1 einfach ändern kann ohne jedes Mal die Formel anpassen zu müssen resp. diese manuell nach unten zu ziehen..
Anzeige
Zu ungenau spezifiziert
21.04.2017 15:41:12
lupo1
ich rege mich erst wieder, wenn alle (implizit von mir gestellten, möglicherweise aber noch mehr) Fragen beantwortet sind.
AW: Zu ungenau spezifiziert
21.04.2017 16:20:21
dome
(Wäre das Ende als Variable in der Formel mit drin würde das die Probleme lösen.)
Also, Folgendes:
In Spalte A kommen die Klassen, und zwar von 18-125 (18-25,26-30,...,116-120,121-125)
Das Ende soll variabel sein. Es muss also nicht immer bis 125 laufen, allerdings möchte ich die Formel nicht immer manuell anpassen müssen. Ich möchte also in irgendeiner Zelle das Ende eingeben können.
Wenn ich dann 65 eingebe heisst das, dass die Klassen von 18-25,26-30,...,61-65 relevant sind für die Mittelwerte. Diese sollen gebildet werden über
18-25
26-30
...
61-64 (bei 65 steht als Zahl derselbe Mittelwert, ist allerdings nicht relevant für weitere Berechnungen)
Der Rest bis 125 kann ausgeblendet werden
Wichtig ist: Der Mittelwert wird in der letzten Klasse immer bis (Ende - 1) gebildet, egal ob das Ende 32 oder 57 oder 81 ist. Ist das Ende 32, dann wird der Mittelwert über 31 gebildet. Das ist allerdings nicht relevant, da es nicht vorkommt. Die einzigen für mich relevanten Werte sind 65,70 und 80.
Ich hoffe das hat Deine Fragen beantwortet.
Ich wünsche Dir ein angenehmes Wochenende.
LG
Dome
Anzeige
AW: dafür angepasste Formel ...
23.04.2017 19:35:13
...
Hallo Dome,
... Formel weit genug nach unten kopieren:
 K
11,2563%

Formeln der Tabelle
ZelleFormel
K1=WAHL(1+(ZEILE(I1)=VERGLEICH(O$1;H:H;))+(ZEILE(I1)<VERGLEICH(O$1;H:H;)); "";MITTELWERT(INDEX(I:I;WENN(ZEILE(I1)<9;1;KÜRZEN(ZEILE(I2)/5)*5-1)):INDEX(I:I;WENN(ZEILE(I1)<9;8;MIN(VERGLEICH(O$1-1;H:H;); KÜRZEN(ZEILE(I2)/5)*5+3)))); INDEX(K:K;ZEILE()-1))

Gruß Werner
.. , - ...
Anzeige
AW: Danke!
24.04.2017 08:31:03
dome
Guten Morgen Werner,
Vielen Dank, das funktioniert super.
Jetzt stehen mal die Berechnungen und es geht an die Gestaltung.
Aus verschiedenen Gründen muss das Ganze auf Zeile 9 beginnen.
Ich versuch mich mal ein bisschen daran... ;)
LG
Dome
AW: bitte ...
24.04.2017 10:34:28
...
Hallo Dome,
... mittlerweile solltest Du herausgefunden haben, dass die aufgezeigte Formel dazu nur identisch in der 9. Zeile Deiner Ergebnisspalte eingesetzt werden muss, solange Du die Spalte K nicht löschen willst. Ansonsten ersetze in der Formel K:K durch die Kennzeichnung Deiner Spalte.
Gruß Werner
.. , - ...
Anzeige
AW: bitte ...
24.04.2017 14:46:50
dome
Hallo Werner,
Wenn ich 8 Zeilen einfüge, damit die Berechnung ab der Zeile 9 beginnt und ich die Formel nach unten ziehe, berechnet die Formel jeweils den Mittelwert über 5 Zellen. Die ersten 8 Werte (sprich der erste Mittelwert) werden nicht berücksichtigt. Es bleibt also offenbar noch mehr an der Formel (von mir) anzupassen oder irgendwas stimmt nicht ganz..
Ich hab Dir das mal in der Datei angepasst damit es erkennbar ist was ich meine.
https://www.herber.de/bbs/user/113074.xlsx
LG
Dome
AW: neu nachgefragt ...
24.04.2017 16:05:00
...
Hallo,
... da hatte ich Dich zuletzt wieder falsch interpretiert. Dort ging ich davon aus, dass Du nur die Ergebnisse beginnend ab Zeile 9 haben willst.
So wie Du es nun darstellst, ist das jetzt die letzte Änderung Deineseits?
Gruß Werner
.. , - ...
Anzeige
AW: neu nachgefragt ...
24.04.2017 16:11:25
dome
Hi Werner,
Habs gerade mit der Originaldatei verglichen. Das Enddatum steht wie dargestellt in derselben Spalte (wäre allerdings toll wäre das Feld variabel, ist es allerdings bereits so wie ich meine) und es beginnt ab Zeile 9 (aber auch hier wäre es geschickt, wäre der Start variabel, da der Modus schnell in Vergessenheit gerät und dann allenfalls nicht auffällt, dass die Formel falsche Werte ausspuckt)
LG
Dome
AW: zu Deiner letzten Feststellung ...
24.04.2017 16:31:59
...
Hallo,
... beginne ich der besseren Übersicht im thread halber, mal einen neuen "Ast".
Also die Zeile 9 könnte also doch nicht immer Deine Start-Datenzeile sein? Gibt es Deine fixe und eindeutige Überschrift zu Deinen Datenwerten?
Gruß Werner
.. , - ...
AW: zu Deiner letzten Feststellung ...
24.04.2017 16:41:54
dome
Sagen wir so: Wenn ich mal nicht mehr in der Firma bin übernimmt mein Nachfolger, der nicht weiss, dass er da nichts verschieben soll ;)
Von daher: Ja, es wäre wahrscheinlich gut, könnte man variabel beginnen. Ich habe zu Beginn wohl etwas zu egoistisch gedacht.
Ja, ich habe mir in den Zeilen 1-8 die Variablen etwas zurecht gelegt. Es macht durchaus Sinn (auch für einen Nachfolger) dies so zu übernehmen. Das Ende steht in Zelle $H$3, die Mittelwerte in derselben Spalte, also so wie in der Beispieldatei.
LG
Dome
AW: letzte Frage ist noch unbeantwortet owT
24.04.2017 16:45:28
...
Gruß Werner
.. , - ...
Nein, die gibt es nicht..
25.04.2017 07:21:38
dome
Es existiert ein Feld in welchem beschrieben ist, was an Berechnung unten folgt, allerdings ist diese Beschreibung/dieser Titel nicht eindeutig.
Ich habe die ersten 8 Zeilen fixiert, ab Zeile 9 fangen die Rechnungen an..
In den ersten 8 Zeilen sind überall die Parameter "verstreut", welche ich für die Rechnungen heranziehe.
LG
Dome
Lösung gefunden (allerdings gepröbelt)
25.04.2017 16:03:20
dome
Guten Abend Werner,
Hab mal etwas herum gespielt und mit dieser Formel funktioniert es nun:
=WAHL(1+(ZEILE(G9)=VERGLEICH(H$3;A:A;))+(ZEILE(G9)<VERGLEICH(H$3;A:A;)); "";MITTELWERT(INDEX(G:G; WENN(ZEILE(G9)<17;9;KÜRZEN(ZEILE(G12)/5)*5-3)):INDEX(G:G;WENN(ZEILE(G9)<17;16; MIN(VERGLEICH(H$3-1;A:A;); KÜRZEN(ZEILE(G12)/5)*5+1)))); INDEX(H:H;ZEILE()-1))
Allerdings ist das mehr durch Zufall als durch etwas anderes zustande gekommen. Deshalb wäre ich froh um eine kurze Erläuterung warum es funktioniert, resp. wie Du zur Lösung kommst.
Einen schönen Abend.
LG
Dome
AW: diese Lösung ist wenig flexibel, deshalb ...
25.04.2017 16:39:03
...
Hallo Dome,
... hab ich mal zwei weitere Variable in nachfolgende Formel mit einbezogen. In dieser ist nur noch die "5er" Auswertung nach H2 beibehalten.
Die Formel H9 muss weit genug nach unten kopiert werden und kann bei Bedarf aber auch noch weiter nach oben kopiert werden.
 EFGH
1  1.-Zeile12
2  anfangs bis Nr27
3  Ende Nr36
4    
5    
6    
7    
8    
9180,02345%  
10190,37570%  
11200,72795%  
12211,08020% 2,13695%
13221,43245% 2,13695%
14231,78470% 2,13695%
15242,13695% 2,13695%
16252,48920% 2,13695%
17262,84145% 2,13695%
18273,19370% 2,13695%
19283,54595% 4,25045%
20293,89820% 4,25045%
21304,25045% 4,25045%
22314,60270% 4,25045%
23324,95495% 4,25045%
24335,30720% 5,65945%
25345,65945% 5,65945%
26356,01170% 5,65945%
27366,36395% 5,65945%
28376,71620%  
29387,06845%  
30397,42070%  
31407,77295%  
32418,12520%  

Formeln der Tabelle
ZelleFormel
H9=WENNFEHLER(WAHL((ZEILE()<H$1)+(ZEILE()>VERGLEICH(H$3;E:E;))+3*(ZEILE(F9)=VERGLEICH(H$3;E:E;))+2*(ZEILE()<VERGLEICH(H$3;E:E;))*(ZEILE()>=H$1); "";MITTELWERT(INDEX(F:F;WENN(ZEILE()<=VERGLEICH(H$2;E:E;); H$1;KÜRZEN((ZEILE()-VERGLEICH(H$2;E:E;)+4)/5;)*5-4+VERGLEICH(H$2;E:E;))):INDEX(F:F;WENN(ZEILE()<=VERGLEICH(H$2;E:E;); VERGLEICH(H$2;E:E;); MIN(KÜRZEN((ZEILE()-VERGLEICH(H$2;E:E;)+4)/5;)*5+VERGLEICH(H$2;E:E;); VERGLEICH(H$3;E:E;)-1)))); INDEX(H:H;ZEILE()-1)); "überprüfe H1:H3-Werte")

Gruß Werner
.. , - ...
Eine riesige Formel.. ;)
26.04.2017 09:17:25
dome
Guten Morgen Werner,
.., aber sie funktioniert bestens. Ich danke Dir herzlich für Deine Unterstützung. Wieder ein etwas tieferes Verständnis für die INDEX() Verwendung gewonnen.
Beste Grüsse,
Dome
AW: Andere würden diese auch anders nennen ...
26.04.2017 16:31:33
...
Hallo Dome,
... z.B. "Monsterformel" ;-)
Aber wenn sie Dir hilft und ein tieferes Verständnis im Umgang mit INDEX() verschafft hat, dann ist es auch kein Monster ;-)
Mehr zu INDEX() sieh u.a. auch mal hier: http://www.online-excel.de/excel/singsel.php?f=180
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige