Wie richten Sie Durchschnitts- und Standardabweichungsausgaben in einer Kalkulationstabelle richtig aus?

Question

Ich bin mir im Moment nicht sicher, wie ich diese Frage am besten formulieren soll, daher werde ich ein Beispiel mit Zufallszahlen verwenden. Ich beginne mit Werten, die IDs zugewiesen werden, sodass jede ID n=1, n=2, ... usw. sein kann.

ID  Value   
1   1235        
1   326     
1   567     
2   768     
2   646     
3   4367        
3   346     
3   35      
4   436     
5   3467        
5   46      
6   3467        
6   3532        
6   457     
7   3463        
7   3463
7   9328
7   2498

usw

Ich möchte in Excel/Calc den Durchschnitt und die SD berechnen, sodass die Werte richtig ausgerichtet sind (im Idealfall wären die Zellen zusammengeführt), da es eine, zwei, drei usw. Eingabezellen und eine Ausgabezelle gibt.

Beispiel Screenshot:

Was ich erreichen möchte. AVG und SD bedeuten korrekte Werte für die (zufällig) gegebenen Daten; daher sind AVG und SD richtig ausgerichtet]1

Ich möchte nämlich eine automatisierte Methode zur Berechnung des Durchschnittswerts und der SD unter Berücksichtigung unterschiedlicher n-Werte, sodass diese richtig ausgerichtet/formatiert sind.

Es muss doch einen einfachen Weg geben, aber im Moment habe ich keine Ahnung. -_-

Ich freue mich über jeden Vorschlag.

Answer 1

Dies ist nicht schwer, wenn Sie davon ausgehen, dass Column Asortiert ist, sodass wir es mit zusammenhängenden Bereichen in Column zu tun haben B (was die visuellen Aspekte Ihrer Frage nahelegen). Richten Sie Column Emit dieser Formel als Hilfsspalte ein:

• E2→=IF(A2=A3, E3, ROW())

Für jede Zeile wird dadurch die letzte Zeile des Bereichs identifiziert, in dem sich die aktuelle Zeile befindet. Anschließend können Sie die gewünschten Ergebnisse erhalten mit

• B2→=IF(A1=A2, "", AVERAGE(B2:INDIRECT("B" & E2)))
• C2→=IF(A1=A2, "", STDEV(B2:INDIRECT("B" & E2)))

(Oder verwenden Sie eine beliebige Methode zur Berechnung der Standardabweichung.) Dadurch wird geprüft, ob es sich um die erste Zeile eines Bereichs handelt. Wenn dies der Fall ist, wird mithilfe der INDIRECT()Funktion ein Bereich zwischen der aktuellen Zelle und der letzten Zelle mit demselben ID-Wert erstellt.

       

Und natürlich können Sie die Spalte ausblenden Eoder eine nicht sichtbare Spalte (z. B. Z) als Hilfsspalte verwenden. Beachten Sie, dass diese Lösung keine Arrayformeln verwendet.

Answer 2

Das ist nicht genau das, was Sie gefragt haben, aber ich würde eine Pivot-Tabelle verwenden:

(Ich habe die erste Spaltenüberschrift in „ID“ geändert und die zweite und dritte Spalte so formatiert, dass nur zwei Dezimalstellen angezeigt werden. Ansonsten ist es nur das, was im PivotTable Builder angezeigt wird.)

Sie können das gewünschte Produkt erhalten, indem Sie Folgendes eingeben:

=IF($A2=$A1,"",AVERAGEIF($A2:$A19,$A2,$B2:$B19))

in C2 und:

=IF($A2=$A1,"",STDEV.P(INDIRECT("R"&MATCH($A2,$A:$A,0)&"C2:R"&MATCH($A2,$A:$A,1)&"C2",0)))

in D2 und füllt beide Spalten aus. Das äußere WENN in jeder Formel soll den Wert nur in die erste Zeile mit einer bestimmten ID setzen. Der Rest der C2-Formel sollte unkompliziert sein: MITTELWERTWENN berechnet den Durchschnitt von Zahlen, für die ein bestimmtes Kriterium zutrifft. In diesem Fall sucht es in der ersten Spalte, wählt Zahlen mit demselben Wert wie der Wert in der aktuellen Zeile in der ersten Spalte aus und berechnet dann den Durchschnitt der entsprechenden Zahlen in der zweiten Spalte.

Leider gibt es kein „STDEVIF“ (zumindest nicht in Excel 2011 auf dem Mac, vielleicht gibt es das in dem Tabellenkalkulationsprogramm, das Sie gerade verwenden. Wenn ja, verwenden Sie es einfach anstelle von AVERAGE in der C2-Formel), Sie müssen also trickreich sein :-). Der Ansatz besteht darin, den Zellbereich zu finden, dessen Standardabweichung Sie ermitteln möchten, einen Verweis auf diese Zellen zu erstellen und diesen Verweis dann an STDEV.P zu übergeben. Der Bereich wird erstellt, indem dieErsteZeile in Spalte 1 mit dem gleichen Wert wie der Wert in der aktuellen Zeile in Spalte 1, dann finden Sie diezuletztZeile in Spalte 1 mit demselben Wert wie der Wert in der aktuellen Zeile in Spalte 1. Diese beiden Werte grenzen den oberen und unteren Teilbereich der Spalte 1 ab, den Sie verwenden möchten. Erstellen Sie also eine Referenz im Stil R1C1 in einer Zeichenfolge, verwenden Sie INDIRECT, um sie in eine tatsächliche Referenz umzuwandeln, und übergeben Sie diese dann an STDEV.P. Einfach! :-) Ok, es ist ein wenig hässlich, aber es funktioniert.

Answer 3

Eine solche Funktion ist in Excel nicht integriert. Sie müssten Zwischensummen oder Pivot-Tabellen verwenden, die jedoch nicht das gewünschte Ergebnis liefern.

Um die Tabelle mit Formeln zu erstellen, verwenden Sie die folgenden beiden Funktionen.

In C2 setzen

=IF(A2<>A1,AVERAGEIF($A$2:$A$13,A2,$B$2:$B$13),"")

In D2 eingeben und durch Drücken bestätigen.ctrl+shift+enter

=IF(A2<>A1,STDEV(IF($A$2:$A$13=A2,$B$2:$B$13)),"")

Dann kopieren Sie diese Formeln nach unten

Das IF(A2<>A1...) am Anfang bedeutet im Wesentlichen, dass nur dann etwas angezeigt werden soll, wenn sich Spalte A in dieser Zeile von der darüber liegenden unterscheidet.

Averageif funktioniert genau so, wie Sie es erwarten.

Spalte D ist eine Array-Formel. Zuerst wird die if-Anweisung für jede Zelle im Bereich ausgeführt. Das Ergebnis ist ein Array in der Art von (1,14,13,3,FALSE,FALSE...) für jede Zelle. Anschließend wird die Standardabweichung berechnet, wobei die FALSE-Werte grundsätzlich ignoriert werden sollten.

Diese Methode geht davon aus, dass die Daten nach ID sortiert sind. Die Durchschnitts- und Standardabweichungsberechnungen wären richtig, wenn sie nicht sortiert wären, aber sie würden bei jeder ID-Änderung angezeigt, nicht nur bei der ersten.