Office: (Office 2010) Daten reduzieren: Mittelwert von jeweils 10 Zeilen

Hallo!

Ich habe sehr große Dateien mit 20.000 bis 250.000 Zeilen. Für die graphische Darstellung habe ich auch in Excel 2010 64bit noch große Schwierigkeiten, weil die Datenmenge einfach zu groß ist (Excel 2007 und Excel 2011 versagen gänzlich).


Nun möchte ich gerne einfach die Datenmenge reduzieren, indem ich nur zB ein Zehntel der Werte verwende. Weil es in diesen Darten aber immer wieder große Schwankungen gibt, möchte ich nicht einfach nur jeden zehnten Wert nehmen, sondern möchte gerne Mittelwerte von jeweils 10 Zeilen haben. Meine Vorstellung wäre dann in etwa so:

Tabelle 1 enthält die Ursprungsdaten (Zeile 1 und 2: Überschriften, Zeile 3 bis Zeile 120.000: Daten)
Tabelle 2 enthält die reduzierten Daten:
Zeile 1 und 2: Überschriften
Zeile 3: Mittelwerte der Zeilen 3-12 aus Tabelle 1
Zeile 4: Mittelwerte der Zeilen 13-22 aus Tabelle 1
Zeile 5: Mittelwerte der Zeilen 23-32 aus Tabelle 1 usw.

Die meisten Spalten enthalten Zahlen, allerdings enthält Spalte B Zeitangaben im Format hh:mm:ss und zwei weitere Spalten enthalten Text, für alle anderen Spalten sollen Mittelwerte gebildet werden.

Die Formel (oder zur Not das Makro) sollte auf verschiedene Dateien anwendbar sein wobei die Spalten immer identisch sind, nur die Zeilenanzahl unterscheidet sich (zum Teil erheblich), denn leider habe ich mittlerweile etwa 120 dieser großen Dateien und es werden täglich mehr...

Gibt es dafür eine Formel (wenn möglich ohne VBA)?

Gruß

MS

:)

 

Hallo MS,

In die Zelle A3 schreibst Du die Formel: =MITTELWERT(BEREICH.VERSCHIEBEN(Tabelle1!A$1;(ZEILE()-3)*10+2;0;10;1))

Bei den Spalten mit einer Zeitangabe (nicht mitteln?) oder Text wäre es dann die Formel (Spalte B):
=BEREICH.VERSCHIEBEN(Tabelle1!B$1;(ZEILE()-3)*10+2;0;1;1)

Der Formelteil (ZEILE()-3)*10+2 macht aus 3 2, aus 4 12, aus 5 22. Diese Zahl kommt zur ZeilenNr. 1 dazu so dass Du für 3 die Zeile 3, für 4 die Zeile 13, für 5 die Zeile 23 usw. erhältst. Das zweitletzte Argument (10) in der ersten Formel bewirkt, dass der Mittelwert aus 10 Werten genommen wird.

Freundliche Grüsse
Gipfelstürmer

 

Hallo ?,

hier eine Alternative ohne das Performance-Fressende BEREICH.VERSCHIEBEN().

Tabelle2

	A	B	C	D	E	F	G	H	I
1
2
3	13,7	00:00:01	JFCW	MGPV		40,98	00:00:01	JFCW	MGPV
4	58,9	00:00:02	WHRV	UDOV		57,36	00:00:11	IAWP	JUHO
5	18,5	00:00:03	OQSS	PBOU		56,92	00:00:21	SWLY	TLFY
6	87,1	00:00:04	ZXTT	TMZO		62,79	00:00:31	IQND	KEFF
7	16,2	00:00:05	YDLI	SDJE		63,26	00:00:41	UORA	PAMT
8	86,8	00:00:06	OGHO	KFUZ		59,29	00:00:51	XFLL	KRCB
9	22,4	00:00:07	YBGN	JDDK		55,24	00:01:01	BLXY	ZRHF
10	67	00:00:08	JDLZ	YGAG		59,03	00:01:11	NMTJ	WKZG
11	17,5	00:00:09	QYTK	CAKP		52,09	00:01:21	JWPS	IUMU
12	21,7	00:00:10	IRCE	CUWG		64,69	00:01:31	XNMJ	DOGC
13	85,1	00:00:11	IAWP	JUHO
14	39,6	00:00:12	ABYE	PEFN
15	96,9	00:00:13	RKQG	DIIC
16	41,1	00:00:14	JEBW	KZWT
17	71,8	00:00:15	ZQWF	WPHL
18	99,8	00:00:16	WVVS	JMWP
19	19,4	00:00:17	LEGY	TWPX
20	10,4	00:00:18	ULWE	DFCC
21	64,7	00:00:19	MGBW	KLZJ
22	44,8	00:00:20	GYQA	YMUK
23	97,8	00:00:21	SWLY	TLFY
24	45	00:00:22	CXBJ	FVHM
25	40,4	00:00:23	ELFV	GOHZ
26	49,8	00:00:24	WWBU	BFCM

Zelle	Formel
F3	=MITTELWERT(INDEX(A:A;(ZEILE(A1)-1)*10+3):INDEX(A:A;(ZEILE(A1)-1)*10+12))
G3	=INDEX(B:B;(ZEILE(A1)-1)*10+3)
H3	=INDEX(C:C;(ZEILE(B1)-1)*10+3)
I3	=INDEX(D:D;(ZEILE(C1)-1)*10+3)

Excel Tabellen im Web darstellen >>

 

Toll, vielen Dank für die schnelle Antwort! Das funktioniert!

Gruß
jkaros

 

Hallo Sepp,

ich übertrage die Formel gerade in meine großen Dateien und was ich jetzt nicht verstehe ist, warum es in den Formeln für G3, H3 und I3 lautet

G3 =INDEX(B:B;(ZEILE(A1)-1)*10+3)
H3 =INDEX(C:C;(ZEILE(B1)-1)*10+3)
I3 =INDEX(D*biggrin.gif*;(ZEILE(C1)-1)*10+3)

Warum ist immer eine Verschiebung drin von Spalte B nach A1, von C nach B1 und von D nach C1? Geht es nicht genauso, wenn es lautet:

G3 =INDEX(B:B;(ZEILE(B1)-1)*10+3)
H3 =INDEX(C:C;(ZEILE(C1)-1)*10+3)
I3 =INDEX(D*biggrin.gif*;(ZEILE(D1)-1)*10+3)

In Deiner Beispieldatei komme ich damit zum selben Ergebnis.

Ich frage deshalb, weil bei mir die Spalten anders sortiert sind
(A - Text, B - Datum, C - Zeit, D - Text, E bis S - Zahlen, T - Text, U bis X - Zahlen, Z - Text) und ich nicht weiß, wie ich die Bezüge aus Deinen Formeln korrekt auf meine Datei anzuwenden habe.

Vielen Dank aber schon jetzt für die große Hilfe!

jkaros

 

Hallo j,

in ZEILE() ist es Egal ob da A1, B1 oder AC1 steht, es wird nur die Zeilennummer zurückgegeben!




« Gruß Sepp »

 

Prima, vielen Dank!

 

Hallo,

Ich habe nun ein ähnliches Problem benötige nun allerdings eine Zusammenfassung von 120 anstatt 10 Zeilen. Nun ist das Problem das ich leider die Formel nicht umstellen kann. Ich hatte gehofft einer von euch könnte mir eventuell helfen? das wäre super! bin nämlich gestern echt verzweifelt....


Vielen Dank schonmal!


Beste Grüße


Jessi

*Smilie