Live-Forum - Die aktuellen Beiträge
Datum
Titel
07.05.2024 13:27:17
Datum nach Auswahl prüfen
07.05.2024 13:11:33
Zeilenhöhe automatisch anpassen funktioniert nicht
07.05.2024 12:42:13
=cnfg_rf!G10 (versteckte Funktion)
07.05.2024 12:00:16
Unterordner und Datein auslesen - Datein beginnen mit "
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1896to1900
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
1896to1900
1896to1900
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Formel für Varianz

Formel für Varianz
03.09.2022 10:00:03
Klaus
Hallo
var.p liefert die Varianz, wenn alle Werte einzeln untereinander stehen.
In meinem Fall sind die Werte und ihre Häufigkeit gegeben.
das Ergebnis einer Klassenarbeit sieht so aus:
20 Punkte haben 5 Schüler erreicht.
18 Punkte haben 15 Schüler erreicht.
16 Punkte haben 8 Schüler erreicht.
Diese Zahlen stehen so in einer Tabelle.
Gibt es eine Excel-Funktion, die daraus die Varianz oder Standartabweichung berechnet?
Die Varianz ist 1,811
LG, Klaus

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
=STABWN(A1:A28)
03.09.2022 10:25:36
WF
.
AW: =STABWN(A1:A28)
03.09.2022 20:49:28
Thomas
Danke für Deinen Vorschlag.
Dafür muss ich wohl die Werte händisch einzeln eintragen. Das ist es aber, was ich umgehen wollte.
UDF oder xl365
04.09.2022 11:40:55
{Boris}
Hi,
mit xl365 ginge es z.B. so. Dabei stehen (bezogen auf Dein Beispiel) die jeweilige Anzahl in A2:A4 und die dazugehörigen Punkte in B2:B4:
=STABWN(VSTAPELN(SEQUENZ(A2;1;B2;0);SEQUENZ(A3;1;B3;0);SEQUENZ(A4;1;B4;0)))
Ansonsten kann das auch eine kleine UDF erledigen - so in der Art:

Option Explicit
Function mySTABW(rngAnzahl As Range, rngPunkte As Range) As Double
Dim C As Range, lngSum As Long, x As Long, y As Long, z As Long
lngSum = WorksheetFunction.Sum(rngAnzahl)
ReDim arr(lngSum)
y = 0: z = 1
For Each C In rngAnzahl
For x = 1 To C.Value
arr(y) = rngPunkte(z, 1)
y = y + 1
Next x
z = z + 1
Next C
mySTABW = WorksheetFunction.StDevP(arr)
End Function
Kannst Du dann als "normale" Funktion im Tabellenblatt aufrufen nach der Syntax:
=mySTABW(rngAnzahl;rngPunkte)
also bezogen auf das selbe Beispiel:
=mystabw(A2:A4;B2:B4)
VG, Boris
Anzeige
Hier noch eine Formellösung...
04.09.2022 19:08:45
{Boris}
Hi,
...für alle Versionen ab 2013 mit XMLFILTERN.
Die Lösung kam von Harry HS (V) in einem anderen Forum und basiert auf XMLFILTERN. Da ich mich mit der Syntax von XML nicht auskenne, kann ich das auch nicht erstellen - aber die Lösung ist TOP!
Anzahl in A2:A12 (im Beispiel hast Du nur 3 - aber ich hab es mal auf etwas mehr ausgelegt - kannst Du bei Bedarf natürlich auch noch erweitern)
Werte in B12:B12
=STABWN(WENNFEHLER(XMLFILTERN("<x><y>"&WECHSELN(TEXTVERKETTEN(;;WIEDERHOLEN(B2:B12&"-"; A2:A12));"-";"</y><y>")&"</y></x>";"//y");""))
VG, Boris
Anzeige
AW: Hier noch eine Formellösung...
04.09.2022 20:35:09
Klaus
Danke für Deine Vorschläge, Boris.
Deine UDF funktioniert.
Ich bin auf der Suche nach einer Excel-Funktion, falls es die gibt!
Zur Info: Derzeit mache ich das mit einer komplizierten Formel:
Anzahl: n1, n2, n3 (in A2:A4)
Punkte: X1, X2, X3 (in B2:B4)
Varianz = Summe(X^2) / Summe n - Mittelwert ^2 / Summe n
wobei Mittelwert = Summe (n*X) / Summe (n)
Standardabweichung = Wurzel aus Varianz
Als Excelformel für die Standardabweichung verwende ich für diesen Bereich:
=WURZEL(SUMMENPRODUKT((B2:B4)^2;A2:A4)/SUMME(A2:A4)-(SUMMENPRODUKT(B2:B4;A2:A4)/SUMME(A2:A4))^2)
oder für die ganze Spalte, also für beliebig viele Einträge in A:A für die Anzahl und in B:B für die Punkte:
=WURZEL(SUMMENPRODUKT(B:B;B:B;A:A)/SUMME(A:A)-(SUMMENPRODUKT(B:B;A:A)/SUMME(A:A))^2)
Diese Formel selbst darf natürlich nicht in den Spalten A oder B stehen, und auch sonst keine Zahlen, Texte, wie zb Spaltenüberschriften, schon!
Oder als UDF: 

Function meineStandardabweichung(rngAnzahl As Range, rngPunkte As Range) As Double
Dim Anzahlsumme As Double
Anzahlsumme = WorksheetFunction.Sum(rngAnzahl)
meineStandardabweichung = Sqr(WorksheetFunction.SumProduct(rngPunkte, rngPunkte, rngAnzahl) / Anzahlsumme - _
(WorksheetFunction.SumProduct(rngPunkte, rngAnzahl) / Anzahlsumme) ^ 2)
End Function
Die Funktion könnte dann etwa so aufgerufen werden:
=meineStandardabweichung(A:A;B:B)
Alle Zahlen in den Bereichen werden ausgewertet, Texte nicht, erzeugen auch keine Fehlermeldung.
Aber, wie gesagt, eine von Excel bereitgestellte Formel wäre wünschenswert, ebenso eine Formel für das gewichtete Mittel.
LG, Klaus
Anzeige
Dann brauchst Du LAMBDA...
04.09.2022 23:24:12
{Boris}
Hi,
...und demnach xl365, denn dort geht genau das, was Du möchtest.
Dort definierst Du einen Namen - z.B. meineSA - und unter "bezieht sich auf" gibst Du ein:
=LAMBDA(a;b;WURZEL(SUMMENPRODUKT((b)^2;a)/SUMME(a)-(SUMMENPRODUKT(b;a)/SUMME(a))^2))
Jetzt hast Du in Deiner Datei die Funktion meineSA zur Verfügung und kannst sie überall nach dieser Syntax anwenden:
=meineSA(A2:A4;B2:B4)
VG, Boris
AW: und noch eine reine Formellösung ...
06.09.2022 17:25:38
neopa
Hallo Klaus oder doch Thomas,
... denn eine derartige XL-Funktion gibt es mW nicht.
In Deiner XL-Version würde ich zunächst mit der Funktion: [als Tabelle formatieren] Deine Datenliste in eine "intelligente" Tabelle wandeln (nachfolgend habe ich dazu den momentanen Datenbereich A1:B4 als z.B. _Tab benannt ) und dafür die Formel(n) definieren und dazu auch den Namensmanager nutzen.
Damit kannst Du ohne eine Formeländerung vornehmen zu müssen, die Tabelle: _Tab jederzeit beliebig mit neuen Daten erweitern und die Ergebniszellen und oder die Datentabelle beliebig auch nachträglich verschieben.
Arbeitsblatt mit dem Namen 'Tabelle1'
 ABCDE
1AnzahlPunkte Varianz:1,81122449
2520 Standardabweichung:1,34581741
31518   
4816   
5     
6     

NameBezug
_Vari=SUMMENPRODUKT((_Tab[Punkte])^2;_Tab[Anzahl])/SUMME(_Tab[Anzahl])-(SUMMENPRODUKT(_Tab[Punkte];_Tab[Anzahl])/SUMME(_Tab[Anzahl]))^2

ZelleFormel
E1=_Vari
E2=_Vari^0,5
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
Das Anliegen....
06.09.2022 19:14:02
{Boris}
Hi Werner,
...von Klaus ist aber der originäre Grund dafür, weshalb LAMBDA ins Leben gerufen wurde: Eine eigene Funktion zu erstellen, ohne VBA zu nutzen.
Typischer kann ein Anwendungsbeispiel dafür kaum sein. Daher würde ich Klaus keine Workarounds vorschlagen sondern eher empfehlen, sich xl365 zuzulegen, sofern er öfter mit so was zu tun haben sollte.
VG, Boris
AW: vorhandene XL-Version ist schon wichtig ...
07.09.2022 11:46:03
neopa
Hallo Boris,
... und danach hat Klaus die XL-Version 2019 im Einsatz. Mein Lösungsvorschlag ist mit dieser zu realisieren.
Hinweise und Empfehlungen zu den Möglichkeiten der neuen XL-Version sind sicher auch nützlich. Ich geh jedoch immer davon aus, dass nicht jeder XL-User unabhängig von seinem Wollen bzw. Nichtwollen frei in der Entscheidung ist, mit welcher Excelversion er arbeiten kann/will. Dies zumindest dann nicht wenn er Angestellter ist.
Gruß Werner
.. , - ...
Anzeige
Darum geht es doch gar nicht...
07.09.2022 14:01:18
{Boris}
Hi,
Klaus hat letztlich folgendes Anliegen:

Ich bin auf der Suche nach einer Excel-Funktion, falls es die gibt!
Und diese "Funktion" lässt sich nun mal in xl365 mit Hilfe von LAMBDA realisieren. Da ist es doch nur logisch, dass ich ihm ein Versionsupgrade empfehle anstatt ihm ausschließlich Workarounds zu präsentieren.
Wer ne Tastatur ohne Ziffernblock nutzt, jedoch ständig und massenweise Zahlen eingeben muss, dem empfehle ich doch ne neue Tastatur (oder einen separaten Ziffernblock), anstatt ihm ausschließlich beizubringen, wie er auch ohne möglichst schnell Zahlen eingeben kann.
Dass Deine Formel funktioniert, stand nie zur Debatte.
VG, Boris
Anzeige
AW: ich meine schon ...
07.09.2022 14:19:51
neopa
Hallo Boris,
... ich schrieb in meinem Erstbeitrag im thread schon: " eine derartige XL-Funktion gibt es mW nicht".
Aber in Zusammenhang mit der vorgeschlagenen "intelligenten" Datentabelle wirkt die definierte benannte Formel im Prinzip schon auch wie eine Excelfunktion (ohne Argumente). Und auch ein (wie immer hinkender) Vergleich: Wer wie ich in einem Mehrfamilienhaus mit Gasheizung wohnt, kann nicht (allein) entscheiden, diese zeitnah gegen eine Gassparende Heizungstechnologie auszutauschen.
Gruß Werner
.. , - ...
Also der Vergleich hinkt in der Tat
07.09.2022 14:47:19
{Boris}
Hi Werner,
...sich privat eine andere Excelversion zuzulegen, ist doch eine einfache Entscheidung - da muss man doch niemanden für fragen.
Anders ist es natürlich, wenn der AG die Software zur Verfügung stellt.
Aber ich glaube, Klaus schaut hier nicht mehr vorbei ;-) - belassen wir es also dabei.
VG, Boris
Anzeige
AW: es ist bis jetzt nicht wirklich geklärt ...
07.09.2022 15:04:59
neopa
Hallo Boris,
... ob Klaus seine Fragestellung als Privatperson oder als Angestellter eingestellt hat und ob es eine seiner wenigen Excelanwendung ist oder er viel mit Excel arbeitet. Wie auch immer, Du merkst, ich habe grundsätzlich was gegen direkte oder verdeckte Produktwerbung vor allem dann, wenn es zu Gunsten von Multis geht.
Gruß Werner
.. , - ...
AW: Also der Vergleich hinkt in der Tat
08.09.2022 07:21:19
Luschi
Hallo Excel-Fan's,
da ich mit E-2019 prof+ als Standalone-Version keine Möglichkeit habe, die neumodischen Array-Funktionen von E-365 einschließlich TEXTVERKETTEN() zu nutzen, habe ich mal mein Uralt-Tafelwerk von 1970 rausgekramt:
- den Erwartungswert E(x)
- und daraus die Varianz und Standardabweichung berechnet.
https://www.herber.de/bbs/user/155048.xlsx
Gruß von Luschi
aus klein-Paris
Anzeige
AW: dieses aber ...
08.09.2022 08:24:10
neopa
Hallo Luschi,
... hatte Klaus schon in seiner Formel zusammengefasst, welche ich für eine beliebige Anzahl an auszuwertenden Daten variabel definiert und als benannte Formel bereitgestellt habe.
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige