Wie schätzt man mit Excel anhand gruppierter Daten Quartile?

Question

Angenommen, ich habe die folgenden gruppierten Daten zur Masse der Hunde:

Mass         Frequency

0 to 5          13

6 to 10         28

11 to 15        47

16 to 20        21

21 to 25        11

25 to 30         6

Wie kann ich mit Excel das erste Quartil schätzen?

Ich weiß, wie man eine Ogive erstellt (mithilfe eines Streudiagramms, dessen Punkte durch eine glatte Kurve verbunden sind), und ich kann die Ogive und meine Augen (und vielleicht ein auf dem Bildschirm platziertes Lineal) verwenden, um das erste Quartil ungefähr zu ermitteln. Aber kann Excel mir eine genauere Antwort geben?

Die Funktion „Trendlinie hinzufügen“ möchte ich nicht nutzen, da es sich bei einer Trendlinie nicht um eine echte Ogive handelt (eine Trendlinie verläuft nicht durch alle Punkte).

Answer 1

Dies sind gruppierte Daten. Wenn Sie also nicht über die Rohdaten verfügen, müssen Sie etwas tun, um die Variation in Ihren Daten nachzubilden. Der Einfachheit halber können wir eine flache Verteilung annehmen – die Wahrscheinlichkeit, dass Hunde 0 oder 5 sind, ist gleich groß.

Verwenden Sie die REPT()Funktion, um eine durch Kommas getrennte Liste von Zahlen auszugeben, die jede Gruppe darstellen.

=REPT(B2&",",C2)  --- where B2 is your group upper bound and C2 is the group frequency

Unten verketten Sie alle diese Zeichenfolgen zu einer großen Zeichenfolge:

Kopieren Sie diese Zeichenfolge undInhalte einfügenalsWertein einem neuen Arbeitsblatt. Verwenden Sie dieText in SpaltenWerkzeug auf derDatenMenübandregisterkarte, um die Daten in eine Spalte pro Wert aufzuteilen. Kopieren Sie diesen gesamten Bereich, dannInhalte einfügenUndTransponierenum dies in eine vertikale Werteliste umzuwandeln. Ihre Daten sollten ungefähr so ​​aussehen:

Sie können die horizontale Zeile ruhig verwerfen – wir brauchen sie nicht. Jetzt wollen wir einige Werte in Spalte B interpolieren, und zwar mit einer Formel wie dieser:

=(5*COUNTIF($A$4:A5,A5)/COUNTIF($A$4:$A$5000,A5))+(A5-5)

Wenn wir das aufschlüsseln, haben wir:

    =(5*                         -- your groups are increments of 5
       COUNTIF($A$4:A5,A5)       -- how far down a row is within a group
       /                         
       COUNTIF($A$4:$A$5000,A5)  -- what the frequency is for that group
      )                          
      +(A5-5)                    -- add this result to the lower bound

Jetzt können Sie die QUARTILE()Funktion auf diese Liste geschätzter Werte anwenden, um Ihre Quartile zu approximieren:

=QUARTILE($B$5:$B$130,1)

Answer 2

Grafisch möchten Sie eineHistogrammwobei die „Bins“ auf Quartile eingestellt sind. Wie das geht, können Sie nachschauen.

Alternativ können Sie eine Formel verwenden. Ich werde es in Worten beschreiben und Sie können die Mathematik selbst herausfinden.

Es gibt 126 Hunde. 25 % davon sind 31,5. Sie möchten das Gewicht ermitteln, das die 31,5 leichtesten Hunde weniger wiegen würden.

In Ihrem leichtesten Behälter befinden sich 13 Hunde, die also gezählt werden. Es bleiben also 18,5 Hunde übrig, aber im nächsten Behälter sind 28 Hunde. Die Frage ist also: Wie viel wiegt der 18,5. leichteste Hund im zweiten Behälter?

Mithilfe der sogenannten linearen Interpolation können Sie schätzen, dass das unbekannte Gewicht 18,5/28 des Weges zwischen 6 und 10 Pfund beträgt. Das ist 6+4*(18,5/28).

Bei Bedarf können Sie denselben Ansatz verwenden, um die anderen Quartile zu ermitteln.

Die Sprache ist hier etwas umständlich, aber Sie verstehen, was ich meine. Ich hoffe, das hilft.

EDIT: Wenn Sie die Gewichte aller einzelnen Hunde kennen, ordnen Sie sie einfach der Reihe nach und berechnen Sie das Durchschnittsgewicht des 31. und 32. Hundes.