Berechnung des Konfidenzintervalls in Excel

Excel wird verwendet, um verschiedene statistische Aufgaben auszuführen, von denen eine die Berechnung des Konfidenzintervalls ist, das als am besten geeigneter Ersatz für eine Punktschätzung mit einem kleinen Stichprobenumfang verwendet wird.

Wir möchten gleich darauf hinweisen, dass das Verfahren zur Berechnung des Konfidenzintervalls ziemlich kompliziert ist, aber in Excel gibt es eine Reihe von Tools, die diese Aufgabe erleichtern sollen. Werfen wir einen Blick auf sie.

Berechnung des Konfidenzintervalls

Ein Konfidenzintervall wird benötigt, um einigen statischen Daten eine Intervallschätzung zu geben. Der Hauptzweck dieser Operation besteht darin, die Unsicherheiten der Punktschätzung zu beseitigen.

Es gibt zwei Methoden zum Ausführen dieser Aufgabe in Microsoft Excel:

• Operator VERTRAUEN NORM – verwendet in Fällen, in denen die Streuung bekannt ist;
• Operator VERTRAUEN.STUDENTwenn die Varianz unbekannt ist.

Im Folgenden werden wir Schritt für Schritt beide Methoden in der Praxis analysieren.

Methode 1: TRUST.NORM-Anweisung

Diese Funktion wurde erstmals in der Excel 2010-Edition in das Arsenal des Programms eingeführt (vor dieser Version wurde sie durch den Operator „VERTRAUENSWÜRDIGE"). Der Betreiber ist in der Kategorie „Statistik“ enthalten.

Funktionsformel VERTRAUEN NORM sieht so aus:

=ДОВЕРИТ.НОРМ(Альфа;Станд_откл;Размер)

Wie wir sehen können, hat die Funktion drei Argumente:

• "Alpha" ist ein Indikator für das Signifikanzniveau, das der Berechnung zugrunde gelegt wird. Das Konfidenzniveau wird wie folgt berechnet:
  • 1-"Альфа". Dieser Ausdruck ist anwendbar, wenn der Wert "Alpha" als Koeffizient dargestellt. Zum Beispiel, 1-0,7 0,3 =, wobei 0,7 = 70 %/100 %.
  • (100-"Альфа")/100. Dieser Ausdruck wird angewendet, wenn wir das Konfidenzniveau mit dem Wert betrachten "Alpha" in Prozent. Zum Beispiel, (100-70) / 100 = 0,3.
• "Standardabweichung" – bzw. die Standardabweichung der analysierten Datenstichprobe.
• "Größe" ist die Größe der Datenstichprobe.

Hinweis: Für diese Funktion ist das Vorhandensein aller drei Argumente Voraussetzung.

Operator "VERTRAUENSWÜRDIGE", das in früheren Versionen des Programms verwendet wurde, enthält dieselben Argumente und führt dieselben Funktionen aus.

Funktionsformel VERTRAUENSWÜRDIGE wie folgt:

=ДОВЕРИТ(Альфа;Станд_откл;Размер)

In der Formel selbst gibt es keine Unterschiede, nur der Name des Operators ist anders. In Excel 2010 und späteren Editionen befindet sich dieser Operator in der Kategorie Kompatibilität. In älteren Versionen des Programms befindet es sich im Abschnitt statische Funktionen.

Die Konfidenzintervallgrenze wird durch die folgende Formel bestimmt:

X+(-)ДОВЕРИТ.НОРМ

woher Х ist der Durchschnittswert über den angegebenen Bereich.

Sehen wir uns nun an, wie man diese Formeln in der Praxis anwendet. Wir haben also eine Tabelle mit verschiedenen Daten aus 10 durchgeführten Messungen. In diesem Fall beträgt die Standardabweichung des Datensatzes 8.

Unsere Aufgabe ist es, den Wert des Konfidenzintervalls mit einem Konfidenzniveau von 95 % zu erhalten.

1. Wählen Sie zunächst eine Zelle aus, um das Ergebnis anzuzeigen. Dann klicken wir auf den Button „Funktion einfügen“ (links neben der Bearbeitungsleiste).
2. Das Fenster Funktionsassistent wird geöffnet. Durch Klicken auf die aktuelle Funktionskategorie erweitern Sie die Liste und klicken auf die Zeile darin „Statistik“.
3. Klicken Sie in der vorgeschlagenen Liste auf den Operator „VERTRAUEN NORM“, dann drücken OK.
4. Wir sehen ein Fenster mit den Einstellungen der Funktionsargumente, das wir füllen, indem wir die Taste drücken OK.
   • im Bereich "Alpha" geben das Signifikanzniveau an. Unsere Aufgabe geht von einem Konfidenzniveau von 95 % aus. Setzen wir diesen Wert in die oben betrachtete Berechnungsformel ein, erhalten wir den Ausdruck: (100-95)/100. Wir schreiben es in das Argumentfeld (oder Sie können das Ergebnis der Berechnung sofort gleich 0,05 schreiben).
   • im Bereich „std_off“ Gemäß unseren Bedingungen schreiben wir die Zahl 8.
   • Geben Sie im Feld „Größe“ die Anzahl der zu untersuchenden Elemente an. In unserem Fall wurden 10 Messungen durchgeführt, daher schreiben wir die Zahl 10.
5. Damit Sie die Funktion nicht neu konfigurieren müssen, wenn sich Daten ändern, können Sie sie automatisieren. Dazu verwenden wir die Funktion „ÜBERPRÜFEN". Setzen Sie den Zeiger in den Eingabebereich der Argumentinformationen "Größe", klicken Sie dann auf das Dreieckssymbol auf der linken Seite der Formelleiste und klicken Sie auf das Element "Mehr Funktionen…".
6. Daraufhin öffnet sich ein weiteres Fenster des Funktionsassistenten. Indem Sie eine Kategorie auswählen „Statistik“, klicken Sie auf die Funktion „ÜBERPRÜFEN", dann OK.
7. Der Bildschirm zeigt ein weiteres Fenster mit den Einstellungen der Argumente der Funktion an, die verwendet wird, um die Anzahl der Zellen in einem bestimmten Bereich zu bestimmen, die numerische Daten enthalten.

   Funktionsformel Check es ist so geschrieben: =СЧЁТ(Значение1;Значение2;...).

   Die Anzahl der verfügbaren Argumente für diese Funktion kann bis zu 255 betragen. Hier können Sie entweder bestimmte Zahlen oder Zelladressen oder Zellbereiche schreiben. Wir werden die letzte Option verwenden. Klicken Sie dazu auf den Informationseingabebereich für das erste Argument, markieren Sie dann mit gedrückter linker Maustaste alle Zellen einer der Spalten unserer Tabelle (ohne die Kopfzeile) und drücken Sie dann die Taste OK.

8. Als Ergebnis der durchgeführten Aktionen wird das Ergebnis der Berechnungen für den Operator in der ausgewählten Zelle angezeigt VERTRAUEN NORM. In unserem Problem stellte sich sein Wert als gleich heraus 4,9583603.
9. Aber das ist noch nicht das Endergebnis unserer Aufgabe. Als nächstes müssen Sie den Durchschnittswert über ein bestimmtes Intervall berechnen. Dazu müssen Sie die Funktion „HERZ"A, der die Aufgabe ausführt, den Durchschnitt über einen bestimmten Datenbereich zu berechnen.

   Die Operatorformel wird wie folgt geschrieben: =СРЗНАЧ(число1;число2;...).

   Wählen Sie die Zelle aus, in die wir die Funktion einfügen möchten, und drücken Sie die Taste „Funktion einfügen“.

10. In der Kategorie „Statistik“ Wählen Sie einen langweiligen Operator "HERZ" und klicken auf OK.
11. In Funktionsargumenten im Argumentwert "Nummer" Geben Sie den Bereich an, der alle Zellen mit den Werten aller Messungen umfasst. Dann klicken wir IN ORDNUNG.
12. Als Ergebnis der durchgeführten Aktionen wird der Durchschnittswert automatisch berechnet und in der Zelle mit der neu eingefügten Funktion angezeigt.
13. Jetzt müssen wir die CI-Grenzen (Konfidenzintervall) berechnen. Beginnen wir mit der Berechnung des Wertes der rechten Grenze. Wir wählen die Zelle aus, in der wir das Ergebnis anzeigen möchten, und führen die Addition der erhaltenen Ergebnisse mit den Operatoren „HERZ" und "VERTRAUENSNORMEN“. In unserem Fall sieht die Formel so aus: A14+A16. Drücken Sie nach der Eingabe Enter.
14. Als Ergebnis wird die Berechnung durchgeführt und das Ergebnis sofort in der Zelle mit der Formel angezeigt.
15. Dann führen wir auf ähnliche Weise die Berechnung durch, um den Wert der linken Grenze des CI zu erhalten. Nur in diesem Fall ist der Wert des Ergebnisses „VERTRAUENSNORMEN“ Sie müssen nicht addieren, sondern von dem Ergebnis subtrahieren, das Sie mit dem Operator „HERZ". In unserem Fall sieht die Formel so aus: =A16-A14.
16. Nach dem Drücken der Eingabetaste erhalten wir das Ergebnis in der angegebenen Zelle mit der Formel.

Hinweis: In den obigen Abschnitten haben wir versucht, alle Schritte und jede verwendete Funktion so detailliert wie möglich zu beschreiben. Alle vorgeschriebenen Formeln können jedoch als Teil einer großen zusammengeschrieben werden:

• Um den rechten Rand des CI zu bestimmen, sieht die allgemeine Formel wie folgt aus:

  =СРЗНАЧ(B2:B11)+ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)).

• In ähnlicher Weise müssen Sie für den linken Rand nur anstelle eines Plus ein Minus setzen:

  =СРЗНАЧ(B2:B11)-ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)).

Methode 2: TRUST.STUDENT-Operator

Machen wir uns nun mit dem zweiten Operator zur Bestimmung des Konfidenzintervalls − vertraut VERTRAUEN.STUDENT. Diese Funktion wurde vor relativ kurzer Zeit, beginnend mit der Version von Excel 2010, in das Programm eingeführt und zielt darauf ab, das KI des ausgewählten Datensatzes mithilfe der Student-Verteilung mit einer unbekannten Varianz zu bestimmen.

Funktionsformel VERTRAUEN.STUDENT wie folgt:

=ДОВЕРИТ.СТЬЮДЕНТ(Альфа;Cтанд_откл;Размер)

Analysieren wir die Anwendung dieses Operators am Beispiel derselben Tabelle. Nur kennen wir jetzt die Standardabweichung gemäß den Bedingungen des Problems nicht.

1. Wählen Sie zuerst die Zelle aus, in der wir das Ergebnis anzeigen möchten. Klicken Sie dann auf das Symbol „Funktion einfügen“ (links neben der Bearbeitungsleiste).
2. Es öffnet sich das bereits bekannte Function Wizard Fenster. Wähle eine Kategorie „Statistik“, und klicken Sie dann in der vorgeschlagenen Liste der Funktionen auf den Operator „VERTRAUENSWÜRDIGER SCHÜLER“, dann - OK.
3. Im nächsten Fenster müssen wir die Funktionsargumente einrichten:
   • Im "Alpha" Geben Sie wie bei der ersten Methode den Wert 0,05 (oder „100-95)/100“ an).
   • Kommen wir zum Argument. „std_off“. Da uns gemäß den Bedingungen des Problems sein Wert unbekannt ist, müssen wir die entsprechenden Berechnungen durchführen, bei denen der Operator „STABW.B“. Klicken Sie auf die Schaltfläche Funktion hinzufügen und dann auf das Element "Mehr Funktionen…".
   • Wählen Sie im nächsten Fenster des Funktionsassistenten den Operator „STABW.B“ in der Kategorie „Statistik“ und klicken auf OK.
   • Wir gelangen in das Einstellungsfenster der Funktionsargumente, dessen Formel wie folgt aussieht: =СТАНДОТКЛОН.В(число1;число2;...). Als erstes Argument geben wir einen Bereich an, der alle Zellen in der Spalte „Wert“ umfasst (Kopfzeile nicht mitgezählt).
   • Jetzt müssen Sie zurück zum Fenster mit den Funktionsargumenten „VERTRAUEN.SCHÜLER“. Klicken Sie dazu im Formeleingabefeld auf die gleichnamige Aufschrift.
   • Kommen wir nun zum letzten Argument „Größe“. Wie bei der ersten Methode können Sie hier entweder einfach einen Zellbereich angeben oder den Operator einfügen "PRÜFEN". Wir wählen die letzte Option.
   • Sobald alle Argumente ausgefüllt sind, klicken Sie auf die Schaltfläche OK.
4. Die ausgewählte Zelle zeigt den Wert des Konfidenzintervalls gemäß den von uns angegebenen Parametern an.
5. Als nächstes müssen wir die Werte der CI-Grenzen berechnen. Und dafür müssen Sie den Durchschnittswert für den ausgewählten Bereich erhalten. Dazu wenden wir wieder die Funktion „HERZ". Der Aktionsalgorithmus ähnelt dem in der ersten Methode beschriebenen.
6. Nach Erhalt des Wertes „HERZ"können Sie mit der Berechnung der CI-Grenzen beginnen. Die Formeln selbst unterscheiden sich nicht von denen, die mit dem „VERTRAUENSNORMEN“:
   • Rechter Rand CI = DURCHSCHNITT + VERTRAUEN DER SCHÜLER
   • Links gebundenes KI = DURCHSCHNITTLICHES VERTRAUEN DER SCHÜLER

Zusammenfassung

Das Werkzeugarsenal von Excel ist unglaublich groß, und neben den gängigen Funktionen bietet das Programm eine Vielzahl von Sonderfunktionen, die die Arbeit mit Daten erheblich erleichtern. Vielleicht erscheinen die oben beschriebenen Schritte für manche Nutzer auf den ersten Blick kompliziert. Aber nach einer detaillierten Untersuchung des Problems und der Abfolge der Aktionen wird alles viel einfacher.