Office: RIESIGE DATENMENGEN verarbeiten

Hallo Leute,

da ich gerade an meiner Abschlussarbeit arbeite und riesige Datenmengen verarbeiten möchte, bräuchte ich Eure Hilfe.

Also zu meinem Problem:

Ich habe stündliche Einstarhlungsdaten von 1973 bis jetzt, also ungefähr >300000 Datensätze. Die Datensätze sind so aufgebaut:

Spalte:

A1 --> Tag/Monat/Jahr
B1 --> Uhrzeit von 01:00 bis 23:00
E1 --> Einstrahlungswerte in W/m2

Nun möchte ich eine Übersicht mit täglichen Werten pro Jahr erstellen.
Ich stelle mir folgendes vor:

1. In der Spalte H möchte ich die Jahre von 1973 -2010 darstellen
2. In der Zeile 2 sind die Tage von 01 Jan bis zum 31 Dez dargestellt

ungefähr so:

01-Jan 02- Jan .... 31-Dec
1973 xxxxx xxxxx xxxxxx
1974 xxxxx xxxx xxxxx
1975

Was muss ich nun machen, um die Werte aus der Einstrahlungsspalte den Tag und Jahr richtig zuzuordnen und dabei die Einstrahlungswerte jeweils vom gesamten Tag addieren zu lassen.

Welche Formeln muss ich verwenden um dieses Problem zu lösen?

Irgendwie verzweifle ich schon an dieser Aufgabe.
Also ich bin jeder hilfe dankbar.

Schöne Grüße
Tauchmann

 

Hallo!

Prinzipiell mit SUMMENPRODUKT, aber da kann die Berechnung bei dieser Datenmenge einiges dauern.
(In I1 und J1 stehen echte Datümer, deren Jahr aber egal ist.)

	A	B	C	D	E	F	G	H	I	J
1	Datum	Zeit			Wert				1. Jan.	2. Jan.
2	01.01.2001	01:00			0,88461354			2001	1,0597273	0,18623358
3	01.01.2001	02:00			0,17511375			2002	0,90999394	1,29864194
4	02.01.2001	03:00			0,01247795
5	02.01.2001	04:00			0,17375563
6	01.01.2002	05:00			0,1838278
7	01.01.2002	06:00			0,72616615
8	02.01.2002	07:00			0,84163828
9	02.01.2002	08:00			0,45700366
10

Zelle	Formel
I2	=SUMMENPRODUKT((JAHR($A$2:$A$99)=$H2)*(TAG($A$2:$A$99)=TAG(I$1))*(MONAT($A$2:$A$99)=MONAT(I$1))*$E$2:$E$99)
J2	=SUMMENPRODUKT((JAHR($A$2:$A$99)=$H2)*(TAG($A$2:$A$99)=TAG(J$1))*(MONAT($A$2:$A$99)=MONAT(J$1))*$E$2:$E$99)
I3	=SUMMENPRODUKT((JAHR($A$2:$A$99)=$H3)*(TAG($A$2:$A$99)=TAG(I$1))*(MONAT($A$2:$A$99)=MONAT(I$1))*$E$2:$E$99)
J3	=SUMMENPRODUKT((JAHR($A$2:$A$99)=$H3)*(TAG($A$2:$A$99)=TAG(J$1))*(MONAT($A$2:$A$99)=MONAT(J$1))*$E$2:$E$99)

Alternativ vielleicht auch mit einer Pivot-Tabelle, wenn du zwei Hilfsspalten anlegst, in denen du einmal das Jahr und einmal Monat und Tag extrahierst.

 

Grüezi zusammen

Mit Matrixformeln wollte ich mich nicht an 300'000 Datensätze wagen.... ;-)

Die Pivot-Tabelle ist hier IMO klar zu präferieren.

Aber Hilfsspalten sind nicht notwendig; nach Jahr/Monat/Tag lässt sich das Datum in der PT trefflich gruppieren.

 

Hallo Thomas!

Wie gruppierst du das denn, so dass der gewünschte Aufbau erreicht wird?
Das hatte ich vorhin auch schon versucht, bin aber kläglich gescheitert...

 

Grüezi schatzi

Es darf nicht die komplette Spalte als Datenquelle markiert sein, sonst klappt die Gruppierung nicht.

Dann aber einfach Datum in den Zeilenbereich und Wert in den Datenbereich schieben.

Rechtsklick in die Datumsspalte der PT --> Gruppieren
und hier dann 'Jahr' und 'Tag auswählen, was das folgende Ergebnis bringt (wenn genügens Spalten für alle Tage des Jahres zur Verfügung stehen -also erst ab xl2007 möglich):

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G	H	I	J	K
1	Datum	Zeit	Dummmy1	Dummy2	Wert				01.Jan	02.Jan
2	01.01.2001	01:00:00			0.88461354			2001	1.05972729	0.18623358
3	01.01.2001	02:00:00			0.17511375			2002	0.90999395	1.29864194
4	02.01.2001	03:00:00			0.01247795
5	02.01.2001	04:00:00			0.17375563
6	01.01.2002	05:00:00			0.1838278			Summe von Wert	Datum
7	01.01.2002	06:00:00			0.72616615			Jahre	01. Jan	02. Jan
8	02.01.2002	07:00:00			0.84163828			2001	1.05972729	0.18623358
9	02.01.2002	08:00:00			0.45700366			2002	0.90999395	1.29864194
10
11

Zelle	Formel
I2	=SUMMENPRODUKT((JAHR($A$2:$A$99)=$H2)*(TAG($A$2:$A$99)=TAG(I$1))*(MONAT($A$2:$A$99)=MONAT(I$1))*$E$2:$E$99)
J2	=SUMMENPRODUKT((JAHR($A$2:$A$99)=$H2)*(TAG($A$2:$A$99)=TAG(J$1))*(MONAT($A$2:$A$99)=MONAT(J$1))*$E$2:$E$99)
I3	=SUMMENPRODUKT((JAHR($A$2:$A$99)=$H3)*(TAG($A$2:$A$99)=TAG(I$1))*(MONAT($A$2:$A$99)=MONAT(I$1))*$E$2:$E$99)
J3	=SUMMENPRODUKT((JAHR($A$2:$A$99)=$H3)*(TAG($A$2:$A$99)=TAG(J$1))*(MONAT($A$2:$A$99)=MONAT(J$1))*$E$2:$E$99)

<table><tr><td>Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></table>[/parsehtml]

 

Hallo Thomas!

Ahhh! Also in der Gruppierung BEIDES anklicken. Darauf bin ich nicht gekommen...

 

Grüezi Schatzi

Ja, da lassen sich beliebige Kombinationen (auch mit noch grösserer Aufschlüsselung) wählen... ;-)


...ich liebe Pivot-Tabellen - es gibt kaum etwas Schnelleres und Flexibleres...

 

Hallo Thomas!

Ich liebe sie auch! Keine Programmierkenntnisse nötig, nur ein bisschen Klicken und Ziehen. Nur leider wurden sie wohl von MS so gut versteckt, dass viele Excel-User gar nichts von ihrer Existenz wissen.
Mit den Möglichkeiten der Gruppierung muss ich mich wohl noch ein wenig auseinandersetzen. Das Buch "Pivot Table Data Crunching" steht zwar bei mir im Regal, aber ich hatte bisher noch keine Anwendung für die tiefergehenden Optionen...

 

Hallo,

erstmal Danke für die schnellen Antworten.
Ich werde mich einfach mal versuchen und schauen was da nun raus kommt.

Werde Euch auf den Laufenden halten.

Schone Grüße
Tauchmann

 

Hallo,

nachdem ich nun meinen gestrigen Abend mit Excel versüsst habe und trotzdem nicht angekommen bin, habe ich eine weitere Frage an Euch:

Leider berechnet mir Excel garnichts :-( (stellt mir ein Null dar, was heist das die Formel ja passt aber irgendetwas nicht stimmt). Aber ich glaube das Problem liegt an meiner Datumsformatierung der Tages- und Monatsfelder (siehe in Schatzi's Tabelle --> I1 und I2).

Wie kann ich diese Felder NUR als Tag.Monat formatieren, also ohne das AKTUELLE Jahr?

Zwar habe ich unter Zellen formatieren / Benutzerdefiniert die Vorgabe dd.mmm gemacht und wird auch im Feld angezeigt als dd.mmm , ABER in der Zeile neben fx (oberhalb der Tabele und den Buchstaben) wird 01/01/2010 angezeigt.

Wie kann man das nun ändern?

Schöne Grüße
Tauchmann

 

Hallo!

Dann arbeitest du also momentan an der Formellösung?
In der Bearbeitungszeile kann ruhig das komplette Datum stehen. Die Formel greift nur auf den Tag und den Monat dieses Datums zu.

Trotzdem würde ich eher zu einer Lösung mit Pivot-Tabelle raten.

 

Hallo,

leider kenne ich mich absolut mit diesen Pivot-Tabellen nicht aus.

Ich habe dein Beispiel mal in einer Exceltabelle versucht, irgendwie klappt die formel bei mir nicht, wird als #WERT! ausgegeben

Hast du vielleicht eine andere Idee????

 

Hallo!

Ich habe mal beide Varianten als Beispielmappe angehängt.
Hier auch nochmal der Link zum Tutorial: Pivot-Tabelle

 

Hallo,

bitte entschuldige die dummen Fragen.
EXCEL bringt mich gerade um.

Ich kann in der Fromelvariante keinen Fehler finden, trotzdem gibt Excel mir 0 raus. Ich habe mal meine sehr verkürzte Tabelle angehängt. Ich sehe einfach den Unterschied nicht und das ärgert mich gerade. Wäre es möglich mal reinzuschauen?

Aber so wie aus sieht und du es empfiehlst werde ich mich wohl in die Pivot thematik einarbeiten müssen.

Schöne Grüße
Tauchmann

 

Hallo!

Du hast einen Klammerfehler drin:

=SUMMENPRODUKT((JAHR($A$2:$A$72)=$E2)*(TAG($A$2:$A$72))=TAG(F$1))*(MONAT($A$2:$A$72)=MONAT(F$1))*$C$2:$C$72)

Die rote Klammer muss weg, dafür muss die grüne neu dazu.