Drucken

Nachfolgend stellen wir eine Methode zur zufallsbasierten Auswahl einer Stichprobe in MS Excel durch Nutzung der Funktion "Zufallszahl" dar. Die zufallsbasierte Auswahl zur Stichprobe wird i.d.R. zur Prüfung der Wirksamkeit von Kontrollen über einen definierten Prüfungszeitraum durch die Interne Revision oder externe Prüfer genutzt. Die Anzahl der Sichprobenelemente ist dabei abhängig von der Grundgesamtheit der aufgetretenen Ereignisse im Prüfungszeitraum. So wird z.B. zur Prüfung einer mehrfach täglich stattfindenden Kontrolle (Grundgesamtheit größer als 365) die Anzahl von zufälligen 30 Stichprobenelementen gewählt, um die Wirksamkeit der Kontrolle mit einem Konfidenzniveau von 98% nachzuweisen (in 98% aller Fälle funktioniert die Kontrolle). Auf die mathematisch-statistischen Grundlagen soll an dieser Stelle nicht eingegangen werden.

 

Schritt 1

In die vorliegende Excel-Tabelle mit der Grundgesamtheit (z.B. Liste aller Programmänderungen für den Zeitraum 01. Januar 2014 bis 31. Dezember 2014) wird eine neue leere

Spalte links eingefügt, sodass sich der Inhalt einmal nach rechts verschiebt.

 

Schritt 2

Der neuen Spalte wird ein eindeutiger Name gegeben. Dies kann zum Beispiel „Zufallszahl“ sein.

 

Schritt 3

Mit der Excel-Funktion „=ZUFALLSZAHL()“  wird ein zufällige Zahl in der ersten Zelle der neu eingefügten Spalte erzeugt. Diese Formel wird für alle gefüllten Zeilen angewendet.

Screenshot

 

 

 

 

 

 

 

Die Excel-Funktion Zufallszahl gibt eine zufällige Zahl zwischen 0 und 1 aus. Exemplarisch hier mit 15 Fällen:

stichprobe excel 2

 

Schritt 4

Die Zufallszahl wird mit jeder Änderung der Daten neu generiert. Um das zu vermeiden, werden daher nun alle Zufallszahlen als "Werte" eingefügt. Dazu wird die Spalte Zufallszahl kopiert und per rechter Maustaste „Werte (W)“ oder dem Menüpunkt „Einfügen“ die Auswahl „Werte (W)“ geklickt*.

stichprobe excel 3

In der Excel-Liste werden nun nicht mehr die Formeln, sondern die Zahlen als Wert dargestellt. Dies hat den Vorteil, dass sich diese nicht verändern und die Zufallsauswahl nachgewiesen werden kann.

 

Schritt 5

Nachfolgend werden die Werte sortiert, um eine zufällige Stichprobe zu erhalten. Dazu muss auf die erste Zeile mit den Bezeichnungen ein Filter gesetzt werden. Dies erfolgt über den Reiter Start > Bearbeiten > Sortieren und Filtern > Filtern. Alternativ kann übder den Reiter Daten die Funktion „Sortieren“ gewählt werden.

Screenshot

 

Schritt 6

Als letztes werden die zufällig erzeugten Zahlen noch absteigend oder aufsteigend sortiert.

stichprobe excel 5

Ergebnis

Die ersten 15 oder 25 Einträge sind dann Bestandteil unserer Stichprobe, die Aus der Anzahl x an Grundgesamtheit lassen sich mit nur wenig Aufwand eine zufällige Anzahl an Stichproben herausziehen.

Cookies erleichtern die Bereitstellung unserer Dienste. Mit der Nutzung unserer Dienste erklären Sie sich damit einverstanden, dass wir Cookies verwenden.