Clever-Excel-Forum

Hallo ihr Lieben,

ich bin heute auf ein Problem gestoßen, welches die Berechnung der Lageparameter von klassierten Daten bei der univariaten Datenanalyse betrifft.

Ich habe das Dokument erstellt, welches ich beigefügt habe. Ich möchte gerne verschiedene Lageparameter aus einer Häufigkeitstabelle berechnen (keine Urliste) und habe hierzu schon verschiedene Funktionen ausprobiert und bin leider daran komplett gescheitert. 

Aus der Liste soll, sobald ein Wert erreicht ist, bspw. der Wert von 0,5 bei der relativen Summenhäufigkeit Fi, der Median berechnet werden. Ebenso die Quartile x0,25 sowie x0,75, und der Modus.

Hat jemand von euch eine Idee, welche Formeln ich hierfür anwenden kann? 


Besten Gruß,
tommyp92
[attachment=16632]

Hallo Thomas!

Da du mit klassierten Daten arbeitest, gestaltet sich das Ganze ja um einiges schwieriger als mit einer Urliste. Im Anhang findest du mal einen ersten Versuch von mir (ich bin nicht mehr so in Statistik drin, verzeihe mir deshalb ungenaue Begriffsoperationalisierungen). Ich habe mir zunächst mal eine Hilfstabelle unter deine Ausgangsdaten gebaut. Wenn du von einem linearen Anstieg der Daten ausgehst, sollten die berechneten Werte für die Klassenelemente stimmen. Liegen diese Werte einmal vor, lassen sich problemlos alle von dir gewünschten Lageparameter berechnen (abgesehen vom Modus, da weiß ich zwar wie du auf die Modalklasse kommst und dann auch auf den Modus der Modalklasse, aber ich habe noch keine flexible Lösung in Excel gefunden -ist auch ganz schön spät gerade).

Mir ist leider keine Möglichkeit bekannt, wie du diese recht umständlichen und momentan händischen Maßnahmen mit Formeln einfach automatisieren könntest ohne die künstlichen Klassenelemente vorher zu berechnen. Vielleicht geht das einfacher mit VBA. Aber da bin ich nicht der richtige Ansprechpartner.

Ich hoffe trotzdem, dass dir meine Berechnungen etwas helfen und ansonsten hoffe ich, dass es hier andere User gibt, die dir (und mir) vielleicht noch mehr Aufschluss geben könne, wie man das kürzer und eleganter löst. Gerade bei großen Datenmengen könnte man so nicht vorgehen.

Grüße

Tommy,

ich kann dir Mittelwert, Modalwert, Median, Standardarbweichung, Varianz und Geometrisches Mittel anbieten.

Arbeitsblatt mit dem Namen 'Auswertungen'
	C	D	E	F	K	L	M
1	Klasse	Häufgkeit		Funktion	aus Häufigkeitstabelle
2	0,3	7		Mittelwert	1,51034483
3	0,6	9		Modalwert	0,6	2,7
4	0,9	6			0,6	1,8	2,7
5	1,2	4		Anzahl Modalwerte	3
6	1,5	3		Median	1,65	1,65
7	1,8	9		Standardabweichung	0,83370582	0,83370582
8	2,1	5		Varianz	0,6950654	0,6950654
9	2,4	6
10	2,7	9		Geometrisches Mittel	1,21888092	1,21888092

Name	Bezug
Häufigkeiten	=Auswertungen!$D$2:$D$10
Klassen	=Auswertungen!$C$2:$C$10

Zelle	Formel
D2	=ZÄHLENWENN(A:A;C2)
K2	=SUMMENPRODUKT(Häufigkeiten*Klassen)/SUMME(Häufigkeiten)
D3	=ZÄHLENWENN(A:A;C3)
K3	=INDEX(Klassen;VERGLEICH(MAX(Häufigkeiten);Häufigkeiten;0))
L3	=VERWEIS(MAX(Häufigkeiten);Klassen)
D4	=ZÄHLENWENN(A:A;C4)
K4	=AGGREGAT(15;6;Klassen/(Häufigkeiten=MAX(Häufigkeiten));SPALTEN($K4:K4))
L4	=AGGREGAT(15;6;Klassen/(Häufigkeiten=MAX(Häufigkeiten));SPALTEN($K4:L4))
M4	=AGGREGAT(15;6;Klassen/(Häufigkeiten=MAX(Häufigkeiten));SPALTEN($K4:M4))
D5	=ZÄHLENWENN(A:A;C5)
K5	=ZÄHLENWENN(Häufigkeiten;MAX(Häufigkeiten))
D6	=ZÄHLENWENN(A:A;C6)
K6	{=MEDIAN(WENN(SPALTE(1:1)<=Häufigkeiten;Klassen))}
L6	=AGGREGAT(17;6;Klassen/(SPALTE(1:1)<=Häufigkeiten);2)
D7	=ZÄHLENWENN(A:A;C7)
K7	{=WURZEL(SUMME(((Klassen-K2)^2)*Häufigkeiten)/SUMME(Häufigkeiten))}
L7	{=WURZEL(SUMME(((Klassen-SUMMENPRODUKT(Häufigkeiten*Klassen)/SUMME(Häufigkeiten))^2)*Häufigkeiten)/SUMME(Häufigkeiten))}
D8	=ZÄHLENWENN(A:A;C8)
K8	=K7^2
L8	{=WURZEL(SUMME(((Klassen-SUMMENPRODUKT(Häufigkeiten*Klassen)/SUMME(Häufigkeiten))^2)*Häufigkeiten)/SUMME(Häufigkeiten))^2}
D9	=ZÄHLENWENN(A:A;C9)
D10	=ZÄHLENWENN(A:A;C10)
K10	{=PRODUKT(POTENZ(Klassen;Häufigkeiten))^(1/SUMME(Häufigkeiten))}
L10	{=POTENZ(PRODUKT(POTENZ(Klassen;Häufigkeiten));1/SUMME(Häufigkeiten))}

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

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

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

... erstmal vielen herzlichen Dank!
Ich werde mal eben die Formeln einfügen. Des Weiteren versuche ich noch eine elegantere Lösung für die Quartile zu finden, wobei die Tabelle so schon super funktioniert. Und ja, die Verlinkung, da habe ich wohl auch schon halb geschlafen. Vielen Dank für den Hinweis im Dokument. 

An der Uni nutzen wir die Funktion, welche ich dem Dokument hinzugefügt habe. Gibt es denn eine Funktion, in dem Fall für Spalte I, Tabelle kl., welche den Bereich nach dem ersten Wert, welcher größer als 0,5 ist, für den Median xz abfragt? Die Berechnung des Medians habe ich in Zelle E5, Datenauswertung kl, hinzugefügt. Die Berechnung müsste halt nur nach dem Raster ablaufen, dass ein Wert eine Zelle höher, einer in der Spalte der Klassenuntergrenze usw. abgefragt wird. Somit könnte eine "elegantere" Rechnung auch analog für die Quartile erfolgen. Ist dies mit normalen Funktionen möglich oder handelt sich das Vorhaben um eine sehr umständliche Variante?

Nochmals Danke im Voraus! 

tommyp92

[attachment=16642]

(13.03.2018, 18:14)tommyp92 schrieb: [ -> ]An der Uni nutzen wir die Funktion, welche ich dem Dokument hinzugefügt habe. Gibt es denn eine Funktion, in dem Fall für Spalte I, Tabelle kl., welche den Bereich nach dem ersten Wert, welcher größer als 0,5 ist, für den Median xz abfragt?

Ich bin mir nicht ganz sicher, ob ich deine Frage richtig verstanden habe. Prinzipiell würde ich wie folgt vorgehen:


Die Formel verwendest du dann in Spalte I für die anderen Sachverhalte (die erste Bedingung natürlich angepasst, da du ja später G5+G6 > 0,5 u.s.w. verwendest). Ich hab aber nicht ganz verstanden, wovon du dann den Median berechnen willst?

Grüße