Regelmäßige Stichproben aus großen Datenreihen

Excel ab 2007 (und früher)

Mess­rei­hen geben mitunter sehr viele Einzel­w­erte zu Pro­tokoll. Angenom­men, ein Sen­sor liefert jede zehn­tel 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 Stun­den.

Wenn Sie eine solchen Ver­lauf als Dia­gramm darstellen wollen, dann kom­men Sie mehr als schnell an die Gren­zen. Auf der einen Seite kön­nen im Dia­gramm max­i­mal 32.000 Werte ver­ar­beit­et wer­den, auf der anderen Seite stellt sich die Frage, ob die Darstel­lung dieser Daten­menge Sinn macht. Eine kurze Probe würde zeigen, dass diese Dat­en nicht dif­feren­ziert darstell­bar sind. Sie sehen nur einen bre­it­en Balken.

Als Ausweg bietet sich an, die Darstel­lung (in diesem Beispiel) stun­den­weise (je Dia­gramm) zu machen und dann auch noch jeden 150sten Daten­satz zu berück­sichti­gen. Die Idee scheint gut, aber wie lässt sich es real­isieren, dass nur jedes Xte Datum (Mess­wert) ver­wen­det wird?

Bei kleineren Daten­men­gen bietet sich fol­gende Möglichkeit an, wenn die Dat­en in Spalte A (begin­nend in A1) ste­hen:

• Schreiben Sie in B1 diese Formel:
  =REST(A1;150)=0
• Kopieren Sie die Formel so weit nach unten, wie die Spalte A aus­gew­ertet wer­den soll.
• Fil­tern Sie die Dat­en nach den Ergeb­nis­sen in Spalte B. Es soll nur das Ergeb­nis WAHR angezeigt wer­den.

Damit wird nun nur jed­er 150te Wert in Spalte A angezeigt. Das kön­nen Sie dann als Grund­lage für das Dia­gramm ver­wen­den. Lei­der gilt: Bei großen Daten­men­gen funk­tion­iert das nicht, weil die Fil­ter­funk­tion nur eine begren­zte Zahl der zu ver­ar­bei­t­en­den Dat­en zulässt. Die Menge der Werte ist abhängig von der Excel-Ver­sion.

Soll­ten Sie für die gesamte Menge der ange­fal­l­enen Dat­en 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 Aus­fül­lkästchen bis zur Zeile 5.760 (das entspricht 864.000/150 also alle Datensätze/150) nach unten. Es wird automa­tisch immer eine Dif­ferenz von 150 gebildet und die let­zte Zahl ist 149 unter der Zeilen­num­mer der let­zten Zeile mit den Mess­werten.
• Schreiben Sie nun in C1 diese Formel:
  =INDIREKT(„A”&B1)
• Kopieren Sie diese Formel eben­falls bis zur Zeile 5.760 nach unten. Das geht recht ein­fach, wenn Sie auf das Aus­fül­lkästchen einen Dop­pelk­lick durch­führen.

In Spalte C ste­hen nun die Werte aus den Zellen A1, A151, A301, … ohne dass hier ein Fil­ter angewen­det wor­den ist. Dadurch kön­nen auch sehr große Daten­men­gen her­aus­ge­zo­gen wer­den.

Hin­weis: Ger­ade für die let­zt­ge­nan­nte Meth­ode geht es ein­fach­er. Viel ein­fach­er! Und das funk­tion­iert in Excel-Ver­sio­nen ab 2007 so (Ver­sio­nen 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 Spal­tenkopf Klick­en:
  
  

  Gesamte Spalte markieren

• Wählen Sie nun im recht­en Bere­ich der Reg­is­terkarte Start den Füll­bere­ich …
  
  
  

  Das Menü zum Aus­füllen von Dat­en

•  … und im Menü:
  
  

  Rei­he aus­füllen

• Klick­en Sie auf Rei­he… Umge­hend wird sich dieses Fen­ster auf­tun:
  
  

  Auswahl-Fen­ster zum Rei­he aus­füllen

• Geben Sie die hier gezeigten Werte ein: Inkre­ment 150, Endw­ert 864000. Also immer um 150 erhöht und bei 864.000, der Anzahl der Mess­dat­en soll Ende sein. OK, und gut ist es. Automa­tisch wur­den alle Werte bis exakt zur geforderten Gren­ze einge­fügt.

In Ver­sio­nen unter­halb Excel 2007 funk­tion­iert das prak­tisch genau so, nur der Aufruf ist auch inner­halb der Ver­sio­nen ver­schieden. Meis­tens find­en Sie den Punkt beim Menü Bear­beit­en | Aus­füllen und dort der Punkt Rei­hen… . Und Sie haben (natür­lich) erhe­blich weniger Zeilen zur Ver­fü­gung als bei Ver­sio­nen ab Excel 2007.

Vielle­icht haben Sie ja einen Link zu ein­er Datei mit ein­er Daten­rei­he ver­misst. Nun ja, wir hal­ten es für über­flüs­sig, eine der­ar­tig große Datei mit nicht rel­e­van­ten Dat­en zu pro­duzieren. Wenn Sie sich selb­st solch eine Datei anle­gen wollen, dann schlage ich fol­gen­des vor:

• F5 (GeheZu)
• Als Ziel geben Sie A864000 ein, dann OK
• Tra­gen Sie dort fol­gende Formel ein: =ZUFALLSBEREICH(1000;1045)
• A864000 markieren und StrgC (Kopieren)
• StrgShift↑
• Return
• StrgC
• Inhalte ein­fü­gen | Werte

Damit ist die Rei­he nahe beieinan­der ste­hen­der Zufall­swerte erzeugt und Sie kön­nen damit arbeit­en. 

Nach­trag im April 2016: Für die Ent­nahme der Stich­proben-Werte in rhyth­mis­chen Inter­vallen gibt es eine weit­ere, recht ele­gante Möglichkeit. Sie wollen jeden 7. Wert aus beispiel­sweise Spalte A in einem anderen Bere­ich, hier Spalte C kopieren.  Nach­dem Sie zuerst in A1 die Über­schrift und ab A2 die Quell­dat­en in Spalte A einge­fügt haben, schreiben Sie (wegen der Über­schrift in der ersten Zeile) in C2 diese Formel:
=INDEX(A:A; (ZEILEN(C$8:C8)-1)*7+8)

Die Index-Funk­tion ver­langt als erstes den zu durch­suchen­den Bere­ich. Hier wird durch das erste Argu­ment A:A die kom­plette Spalte A übergeben. Als zweites Argu­ment wird die Posi­tion, hier die Zeile inner­halb des Bere­ichs erwartet. Die Zahl wird mit dem zweit­en Argu­ment berech­net. In Stich­worten eine kurze Erk­lärung: Wegen der 7^er-Sprünge wird als erstes die 7. Zeile des Daten­bere­ichs bes­timmt. Und das muss wegen der Über­schrift dann ja A8 sein.

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

Alles klar, die 8. Zeile im Bere­ich A:A ist A8 und die 7. Zeile der Dat­en, welche ja in A2 begin­nen. So weit, so klar. Der zweite Ein­trag in Spalte C muss ja den Wert von A15 (Zeile 8 plus 7^er-Sprung) ausle­sen. Die Formel aus C2 wird herunter gezo­gen, 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ür­lich 15

Wiederum die erste Zeile wegen der Über­schrift abge­zo­gen bleibt die 14. Zeile der Dat­en, welche ja A15 ist. Und das Sys­tem sich bis zur let­zen Zeile in Spalte C durch.

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

Dann würde ich mich über einen Beitrag Ihrer­seits z.B. 2,50 € freuen …