Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1012to1016
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
Inhaltsverzeichnis

Median- Mittelwert, Varianzwenn

Median- Mittelwert, Varianzwenn
05.10.2008 15:31:00
Ferenz
Hallo Excelprofis,
habe ein Problem, dass sich auf 3 Dinge bezieht. Dies sind
Median
Mittelwert
Varianz
In Excel sind diese 3 Dinge als Funktion bereits hinterlegt. Sie sollen aber erst durch 2 Bedingungen berechnet werden.
Es geht dabei um Fußballwetten: Es gibt 3 Spalten
die Anfangsquote (Spalte A), Minute in der eine bestimmte Quote erreicht worden ist (Spalte B), Bestimmte Quote (Spalte C)
Der Median, Mittelwert und die Varianz sollen über die Spalte B berechnet werden, aber nur zu den dazugehörigen Werten von Spalte A und C.
Eine Datei habe ich beigefügt, um es sich leichter vorzustellen.
1. Bsp. suche ich zum einen die Werte für Median, Mittelwert und die Varianz unter den Kriterien
Anfangsquote: 1,4
aktuelle Quote: 11
2. Bsp. suche ich zum einen die Werte für Median, Mittelwert und die Varianz unter den Kriterien
Anfangsquote: 1,3
aktuelle Quote: 8
Mit freundlichem Gruß
Ferenz

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Median, Mittelwert, Varianz bedingt
05.10.2008 16:16:00
Erich
Hallo Ferenz,
meinst du das so?
 ABCDEFGHI
1AnfangsquoteMinaktuelle Quote      
21,4898 Analyse    
31,48911 Anfangsquoteaktuelle QuoteMedianMittelwertVarianz
41,49111 1,4119190,66666672,33333333
51,49211 1,3891,5912
61,3877      
71,3898      
81,3918      
91,3928      
101,3928      

Formeln der Tabelle
ZelleFormel
G4{=MEDIAN(WENN(($A$2:$A$10=$E4)*($C$2:$C$10=$F4); $B$2:$B$10))}
H4{=MITTELWERT(WENN(($A$2:$A$10=$E4)*($C$2:$C$10=$F4); $B$2:$B$10))}
I4{=VARIANZ(WENN(($A$2:$A$10=$E4)*($C$2:$C$10=$F4); $B$2:$B$10))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Median, Mittelwert, Varianz bedingt
05.10.2008 18:21:54
Ferenz
Hallo Erich,
Vielen Dank. Die Lösung ist wirklich sehr gut.
Freundlichen Gruß
Ferenz
#Zahl?
05.10.2008 18:26:59
Ferenz
Habe noch ein kleinen Schönheitsfehler,
Ich erstelle die Liste in allen Kombinationen von den beiden Bendingungen. Wenn jedoch die Zahl in meinem datensatz nicht vorhanden ist, dann gibt der Median mir den Fehler "Zahl" an. Wie sage ich der Matrixformel, dass er eine Null schreiben soll, wenn er keinen Daten zu findet?
Gruß Ferenz
AW: #Zahl?
05.10.2008 18:38:00
Erich
Hi Ferenz,
hier am Beispiel Median - die Wenn-Bedingung kannst du identisch auch vor die beiden anderen Werte schreiben:
 EFG
3Anfangsquoteaktuelle QuoteMedian
41,41191
51,3891,5
61,470

Formeln der Tabelle
ZelleFormel
G4{=WENN(SUMMENPRODUKT(($A$2:$A$10=$E4)*($C$2:$C$10=$F4))=0;0; MEDIAN(WENN(($A$2:$A$10=$E4)*($C$2:$C$10=$F4); $B$2:$B$10)))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Standardabweichung?
05.10.2008 20:41:00
Ferenz
Danke für die super schnelle Antwort Erich.
komisch. Ich wollte das nun für die Standardabweichung auch noch anwenden, aber das klappt nicht, egal wie ich es probiere.
freundlichen Gruß
Ferenz
AW: Standardabweichung?
05.10.2008 20:58:00
Uwe
Hi Ferenz,
also wenn ich in Erichs Forml das MEDIAN durch STABW ersetze bekomme ich ein Ergebnis, das zumindest zu stimmen SCHEINT. Was stört Dich daran?
Gruß
Uwe
(:o)
AW: Standardabweichung?
06.10.2008 20:00:47
Ferenz
Hallo Erich und Uwe,
habe festgestellt, dass die Standardabweichung bei meinem großen Datensatz der Fehler "#DIV/0!" vorkommt, sobald er weniger als 2 zugehörige Daten des aktuellen Wertes findet. Wie kann ich diesen Fehler vermeiden?
Gruß
Ferenz
Anzeige
AW: Standardabweichung und Varianz
06.10.2008 23:46:04
Erich
Hi Ferenz,
das gilt nicht nur für die Standardabweichung, auch die Varianz lässt sich für einen einzigen Wert nicht berechnen.
Deshalb ist die Bedingung in Spalte I und J leicht geändert:
 EFGHIJ
3Anfangsquoteaktuelle QuoteMedianMittelwertVarianzStAbw
41,4119190,66666672,333333331,52752523
51,3891,59121,41421356
61,37878700

Formeln der Tabelle
ZelleFormel
G4{=WENN(SUMMENPRODUKT(($A$2:$A$10=$E4)*($C$2:$C$10=$F4))=0;0; MEDIAN(WENN(($A$2:$A$10=$E4)*($C$2:$C$10=$F4); $B$2:$B$10)))}
H4{=WENN(SUMMENPRODUKT(($A$2:$A$10=$E4)*($C$2:$C$10=$F4))=0;0; MITTELWERT(WENN(($A$2:$A$10=$E4)*($C$2:$C$10=$F4); $B$2:$B$10)))}
I4{=WENN(SUMMENPRODUKT(($A$2:$A$10=$E4)*($C$2:$C$10=$F4))<2;0; VARIANZ(WENN(($A$2:$A$10=$E4)*($C$2:$C$10=$F4); $B$2:$B$10)))}
J4{=WENN(SUMMENPRODUKT(($A$2:$A$10=$E4)*($C$2:$C$10=$F4))<2;0; STABW(WENN(($A$2:$A$10=$E4)*($C$2:$C$10=$F4); $B$2:$B$10)))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Klappt Super, aber jetzt Performanceproblem
07.10.2008 23:18:08
Ferenz
Hallo Erich,
Danke für den Tipp, dachte mir schon, dass das nicht gehen kann. Deine Formel funktioniert tadellos. Ich habe nun Deine Beispiel-Formel für meinen Einsatz abgewandelt indem ich die Matrix auf die ganze Spalte erweitert habe. In der folgenden Form "...$A$:$A". Habe meine favorisierten Anfangsquoten und aktuelle Quoten mit den Formeln angewendet. Dabei brauch mein Excel 2007 gute 20 min. Wie kann ich den dem Excel sagen, dass er nur bis zur letzten Spalte in der ein Wert drin ist berechnen soll? Er soll quasi automatisch erkennen, dass er nicht über die leeren Zellen hinweg berechnen soll.
Ich hoffe da gibt es auch eine Lösung. Bin schon ganz gespannt auf Deine Antwort.
Mit freundlichem Gruß
Ferenz
Anzeige
AW: Performanceproblem
08.10.2008 21:12:14
Erich
Hallo Ferenz,
kein Wunder, dass Excel da ins Rechnen kommt - wie viele Zeilen hat ein Blatt in XL2007?...
Eine Möglichkeit, die Formeln nur für den richtigen Bereich auszuwerten,
sind Namen, die für dynamische Bereiche stehen.
SpalteA, SpalteB und SpalteC sind willkürlich gewählte Namen, du kannst besser sprechende Namen verwenden.
Zusätzlich habe ich einen Namen AnzZe definiert - das muss dann nur einmal berechnet
und kann mehrfach genutzt werden.
Wieder am Beispiel Median:
 EFG
3Anfangsquoteaktuelle QuoteMedian
41,41191
51,3891,5

Formeln der Tabelle
ZelleFormel
G4{=WENN(SUMMENPRODUKT((SpalteA=$E4)*(SpalteC=$F4))=0;0; MEDIAN(WENN((SpalteA=$E4)*(SpalteC=$F4); SpalteB)))}
G5{=WENN(SUMMENPRODUKT((SpalteA=$E5)*(SpalteC=$F5))=0;0; MEDIAN(WENN((SpalteA=$E5)*(SpalteC=$F5); SpalteB)))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen
Namen in Formeln
ZelleNameBezieht sich auf
G4AnzZe=ANZAHL2(Tabelle3!$A:$A)
G4SpalteA=BEREICH.VERSCHIEBEN(Tabelle3!$A$2;0;0;AnzZe;1)
G4SpalteB=BEREICH.VERSCHIEBEN(Tabelle3!$B$2;0;0;AnzZe;1)
G4SpalteC=BEREICH.VERSCHIEBEN(Tabelle3!$C$2;0;0;AnzZe;1)
G5AnzZe=ANZAHL2(Tabelle3!$A:$A)
G5SpalteA=BEREICH.VERSCHIEBEN(Tabelle3!$A$2;0;0;AnzZe;1)
G5SpalteB=BEREICH.VERSCHIEBEN(Tabelle3!$B$2;0;0;AnzZe;1)
G5SpalteC=BEREICH.VERSCHIEBEN(Tabelle3!$C$2;0;0;AnzZe;1)

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
Performanceproblem: Name durch Formel definieren?
09.10.2008 19:04:02
Ferenz
Hallo Erich,
Excel hat unendliche Zeilen, sowie Spalten. Die Begrenzung liegt einzig allein bei der Hardware.
Ich glaub Du hast mich jetzt abgehängt. Habe mir Deine Lösung gestern und heute angekuckt, aber bin nicht draus schlau geworden. Den ersten Absatz habe ich verstanden mit "Formeln in Tabelle".
Namen erstellen kenne ich über die Funktion "Name erstellen..." oder im Listen bzw. Tabellenbereich (Datenbank) wird den Spalten automatisch ein Name vergeben. Aber so wie ich Deinen zweiten Absatz "Namen in Formeln" verstehe hast Du der Spalte durch eine Formel den Namen vergeben.
Liege ich da richtig und wenn ja warum nicht dann über die Funktion "Name erstellen..."?
Gruß Ferenz
Anzeige
AW: Performanceproblem: Name durch Formel definieren?
09.10.2008 19:16:11
Erich
Hi Ferenz,
ja, "normalerweise" wird der Bereich, auf den sich ein Name bezieht, fest vorgegeben,
manchmal auch automatisch.
Aber hier wollen wir ja gerade erreichen, dass der Bereich, auf den sich ein Name bezieht, dynamisch ist.
Hier soll sich die Anzahl Zeilen (die Höhe) des Bereichs nach der Anzahl belegter Zeilen in Spalte A richten.
Das Einrichten der Namen geht ganz normal:
Einfügen - Namen - Definieren, den neuen Namen eintragen, dann bei
"Bezieht sich auf" die Formel eintragen, wie ich sie gepostet habe.
Zuerst musst du den Namen AnzZe definieren, da er in den drei anderen Namen gebraucht wird.
Noch eine kleine Korrektur:
Ein Blatt in Version 2007 hat 2^20 = 1.048.576 Zeilen und 2^14 = 16.384 Spalten,
also etwas weniger als unendlich...
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Performanceproblem: Name durch Formel definieren?
11.10.2008 19:27:51
Ferenz
Hallo Erich,
habe Deine Lösung an meinem Beispiel umgesetzt. Das hat geklappt. Wollte es dann auf meine Original-Datei transferieren. Das hat nicht geklappt. Ich habe dann einfach die Daten aus der Originaldatei in die Beispieldatei kopiert. Nun funktioniert es prima. Man merkt zwar immer noch, dass Excel rechnen muß. Es vergehen schon ein paar Sekunden, aber das ist akteptabel. Ist denke ich auch kein Wunder, da ich die Kalklationen noch 3 mal in der Art brauche.
Jetzt muß ich nur noch ein Verweis auf die Ergebnisse erstellen, aber das versuch ich jetzt erst mal wieder alleine.
Herzlichen Dank für Deine schnelle und kompetente Hilfe.
Ferenz
Anzeige
AW: Ergebnisse anhand 2 Suchkriterien?
11.10.2008 20:14:00
Ferenz
Hallo Erich,
ich habe mich zu früh gefreut. Das was ich vor hatte ist doch schwieriger als ich dachte.
Ich wollte nun eine Eingabemaske zur Krönung erstellen, in der ich die Anfangsquote und die aktuelle Quote eingebe und Excel mir die dazu gehörigen Ergebnisse (Mittelwert, Median, etc..). Habe feststellen müssen, das der SVERWEIS nur nach einem Suchkriterium die Ergebnisse ausspuckt.
Wie bekomme ich dass denn hin.
Gruß
Ferenz
AW: SVERWEIS mit 2 Suchkriterien
12.10.2008 00:15:00
Erich
Hi Ferenz,
schau mal hier: http://www.excelformeln.de/formeln.html?welcher=30
Da findest du ein paar Formeln dafür, mit einem Beispiel.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
AW: SVERWEIS mit 2 Suchkriterien
12.10.2008 14:08:04
Ferenz
Hallo Erich,
vielen Dank für den Link.
Hat alles geklappt.
Bin nun rundum glücklich.
Gruß Ferenz

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige