Stichproben

Regelmäßige Stichproben aus großen Datenreihen

Excel ab 2007 (und früher)

Messreihen geben mitunter sehr viele Einzelwerte zu Protokoll. Angenommen, ein Sensor liefert jede zehntel Sekunde einen Wert an Excel, dann sind das

  • 10 Werte in 1 Sekunde
  • 600 Werte in 1 Minute
  • 36.000 Werte in 1 Stunde
  • 864.000 Werte in 24 Stunden.

Wenn Sie eine solchen Verlauf als Diagramm darstellen wollen, dann kommen Sie mehr als schnell an die Grenzen. Auf der einen Seite können im Diagramm maximal 32.000 Werte verarbeitet werden, auf der anderen Seite stellt sich die Frage, ob die Darstellung dieser Datenmenge Sinn macht. Eine kurze Probe würde zeigen, dass diese Daten nicht differenziert darstellbar sind. Sie sehen nur einen breiten Balken.

Als Ausweg bietet sich an, die Darstellung (in diesem Beispiel) stundenweise (je Diagramm) zu machen und dann auch noch jeden 150sten Datensatz zu berücksichtigen. Die Idee scheint gut, aber wie lässt sich es realisieren, dass nur jedes Xte Datum (Messwert) verwendet wird?

Bei kleineren Datenmengen bietet sich folgende Möglichkeit an, wenn die Daten in Spalte A (beginnend in A1) stehen:

  • Schreiben Sie in B1 diese Formel:
    =REST(A1;150)=0
  • Kopieren Sie die Formel so weit nach unten, wie die Spalte A ausgewertet werden soll.
  • Filtern Sie die Daten nach den Ergebnissen in Spalte B. Es soll nur das Ergebnis WAHR angezeigt werden.

Damit wird nun nur jeder 150te Wert in Spalte A angezeigt. Das können Sie dann als Grundlage für das Diagramm verwenden. Leider gilt: Bei großen Datenmengen funktioniert das nicht, weil die Filterfunktion nur eine begrenzte Zahl der zu verarbeitenden Daten zulässt. Die Menge der Werte ist abhängig von der Excel-Version.

Sollten Sie für die gesamte Menge der angefallenen Daten den gefilterten Auszug brauchen, dann bietet sich noch diese Möglichkeit:

  • Schreiben Sie in B1 die Zahl 1 für die erste Zeile
  • Schreiben Sie in B2 die Zahl 151 für die 151te Zeile, sprich 1+150
  • Markieren Sie B1:B2
  • Ziehen Sie die Markierung mit dem Ausfüllkästchen bis zur Zeile 5.760 (das entspricht 864.000/150 also alle Datensätze/150) nach unten. Es wird automatisch immer eine Differenz von 150 gebildet und die letzte Zahl ist 149 unter der Zeilennummer der letzten Zeile mit den Messwerten.
  • Schreiben Sie nun in C1 diese Formel:
    =INDIREKT(“A“&B1)
  • Kopieren Sie diese Formel ebenfalls bis zur Zeile 5.760 nach unten. Das geht recht einfach, wenn Sie auf das Ausfüllkästchen einen Doppelklick durchführen.

In Spalte C stehen nun die Werte aus den Zellen A1, A151, A301, … ohne dass hier ein Filter angewendet worden ist. Dadurch können auch sehr große Datenmengen herausgezogen werden.

Hinweis: Gerade für die letztgenannte Methode geht es einfacher. Viel einfacher! Und das funktioniert in Excel-Versionen ab 2007 so (Versionen bis 2003 siehe hier):

  • Schreiben Sie in B1 die Zahl 1 für die erste Zeile
  • Markieren Sie die gesamte Spalte B, indem Sie auf den Spaltenkopf klicken:
    Gesamte Spalte markieren

    Gesamte Spalte markieren

  • Wählen Sie nun im rechten Bereich der Registerkarte Start den Füllbereich …

    Das Menü zum Ausfüllen von Daten

    Das Menü zum Ausfüllen von Daten

  •  … und im Menü:
    Reihe ausfüllen

    Reihe ausfüllen

  • Klicken Sie auf Reihe… Umgehend wird sich dieses Fenster auftun:
    Auswahl-Fenster zum Reihe ausfüllen

    Auswahl-Fenster zum Reihe ausfüllen

  • Geben Sie die hier gezeigten Werte ein: Inkrement 150, Endwert 864000. Also immer um 150 erhöht und bei 864.000, der Anzahl der Messdaten soll Ende sein. OK, und gut ist es. Automatisch wurden alle Werte bis exakt zur geforderten Grenze eingefügt.

In Versionen unterhalb Excel 2007 funktioniert das praktisch genau so, nur der Aufruf ist auch innerhalb der Versionen verschieden. Meistens finden Sie den Punkt beim Menü Bearbeiten | Ausfüllen und dort der Punkt Reihen… . Und Sie haben (natürlich) erheblich weniger Zeilen zur Verfügung als bei Versionen ab Excel 2007.

Vielleicht haben Sie ja einen Link zu einer Datei mit einer Datenreihe vermisst. Nun ja, wir halten es für überflüssig, eine derartig große Datei mit nicht relevanten Daten zu produzieren. Wenn Sie sich selbst solch eine Datei anlegen wollen, dann schlage ich folgendes vor:

  • F5 (GeheZu)
  • Als Ziel geben Sie A864000 ein, dann OK
  • Tragen Sie dort folgende Formel ein: =ZUFALLSBEREICH(1000;1045)
  • A864000 markieren und StrgC (Kopieren)
  • StrgShift
  • Return
  • StrgC
  • Inhalte einfügen | Werte

Damit ist die Reihe nahe beieinander stehender Zufallswerte erzeugt und Sie können damit arbeiten. 


Nachtrag im April 2016: Für die Entnahme der Stichproben-Werte in rhythmischen Intervallen gibt es eine weitere, recht elegante Möglichkeit. Sie wollen jeden 7. Wert aus beispielsweise Spalte A in einem anderen Bereich, hier Spalte C kopieren.  Nachdem Sie zuerst in A1 die Überschrift und ab A2 die Quelldaten in Spalte A eingefügt haben, schreiben Sie (wegen der Überschrift in der ersten Zeile) in C2 diese Formel:
=INDEX(A:A; (ZEILEN(C$8:C8)-1)*7+8)

Die Index-Funktion verlangt als erstes den zu durchsuchenden Bereich. Hier wird durch das erste Argument A:A die komplette Spalte A übergeben. Als zweites Argument wird die Position, hier die Zeile innerhalb des Bereichs erwartet. Die Zahl wird mit dem zweiten Argument berechnet. In Stichworten eine kurze Erklärung: Wegen der 7er-Sprünge wird als erstes die 7. Zeile des Datenbereichs bestimmt. Und das muss wegen der Überschrift dann ja A8 sein.

  • ZEILEN(C$8:C8) ergibt 1
  • ZEILEN(C$8:C8)-1 entsprechend 0
  • und 0*7+8 ergibt natürlich 8

Alles klar, die 8. Zeile im Bereich A:A ist A8 und die 7. Zeile der Daten, welche ja in A2 beginnen. So weit, so klar. Der zweite Eintrag in Spalte C muss ja den Wert von A15 (Zeile 8 plus 7er-Sprung) auslesen. Die Formel aus C2 wird herunter gezogen, dadurch ergibt sich diese Formel:
=INDEX(A:A; (ZEILEN(C$8:C9)-1)*7+8)

  • ZEILEN(C$8:C9) sind 2 Zeilen
  • ZEILEN(C$8:C9)-1 entsprechend 1
  • und 1*7+8 ergibt natürlich 15

Wiederum die erste Zeile wegen der Überschrift abgezogen bleibt die 14. Zeile der Daten, welche ja A15 ist. Und das System sich bis zur letzen Zeile in Spalte C durch.

▲ nach oben …

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,50  freuen …

Dieser Beitrag wurde unter Musterlösungen, Ohne Makro/VBA, Rechnen & Zahlen, Tipps und Tricks, Verschiedenes veröffentlicht. Setze ein Lesezeichen auf den Permalink.