Das Clever-Excel-Forum.de - Treffen
findet vom 15. - 17. September 2017 in Thüringen / Region Großer Inselsberg statt. Hotelbuchung ab sofort möglich.


Berechnung und Verknüpfungsproblem
#1
Liebe Excel Gemeinde,
ich sitze gerade an meinem ersten größeren Excel-Projekt und stoße dabei an meine und an googles Grenzen20. Ich hoffe ihr könnt Abhilfe schaffen.Huh
Kurz zu meinem Projekt, ich würde gerne den Gasverbrauch und Temperatur in Relation setzen und über die Kerngröße Gasverbrauch pro qm etc.... eine Aufstellung führen.

Die Daten der Temperatur beziehe ich dabei vom DWD, leider als Tagesmittel brauche aber Wochenmittelwerte. Somit zum Problem mit Tabelle 1: wie kann ich Excel dazu bringen mir die Nullen zu löschen und einfach eine Aufstellung der Kalenderwochen und daneben die dazugehörigen Mittelwerte zu machen?
Nun zum Problem Nummer 2: Wie bekomme ich diese Werte in die zweite Tabelle rein, sodass sie sich aktualisieren und möglichst automatisch weiterausfüllen sobald ich die Daten vom DWD manuell eingelesen hab?
Ich denke das Problem wird erst deutlich wenn ihr die Tabelle öffnet.
Bei Fragen gerne schreiben19

Ich bin für sämtliche Ideen, Kritik, Anregung offen und dankbar15

Vielen Dank vorab


Angehängte Dateien
.xlsx   Tabelle 1.xlsx (Größe: 122,45 KB / Downloads: 3)
.xlsx   Tabelle 2.xlsx (Größe: 12,18 KB / Downloads: 2)
to top
#2
Hallo,

kurz mal zu deinem ersten Problem.

Die Kalenderwoche kannst du ab XL2010 zuverlässig mit der Fkt. KALENDERWOCHE errechnen lassen. Siehe Tabellenausschnitt.

Zur Mittelwertberechnung schau dir mal diesen Tipp von Thomas Ramel an - vielleicht hilft der dir weiter.

Tabelle1

AB
504.01.2013
605.01.2013
706.01.20131. KW
807.01.2013
1312.01.2013
1413.01.20132. KW
2120.01.20133. KW
Formeln der Tabelle
ZelleFormel
B5=WENN(KALENDERWOCHE(A5;21)<>KALENDERWOCHE(A6;21);KALENDERWOCHE(A5;21);"")
B6=WENN(KALENDERWOCHE(A6;21)<>KALENDERWOCHE(A7;21);KALENDERWOCHE(A6;21);"")
B7=WENN(KALENDERWOCHE(A7;21)<>KALENDERWOCHE(A8;21);KALENDERWOCHE(A7;21);"")
B8=WENN(KALENDERWOCHE(A8;21)<>KALENDERWOCHE(A9;21);KALENDERWOCHE(A8;21);"")
B13=WENN(KALENDERWOCHE(A13;21)<>KALENDERWOCHE(A14;21);KALENDERWOCHE(A13;21);"")
B14=WENN(KALENDERWOCHE(A14;21)<>KALENDERWOCHE(A15;21);KALENDERWOCHE(A14;21);"")
B21=WENN(KALENDERWOCHE(A21;21)<>KALENDERWOCHE(A22;21);KALENDERWOCHE(A21;21);"")

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Der Rest (.KW) wurde mit einer benutzerdefinierten Formatierung erreicht.
?mage

Misserfolg ist eine Chance, es beim
nächsten Mal besser zu machen.

(Henry Ford)
http://www.sprueche-zum-nachdenken.eu
to top
#3
Hallo WillWissen,
habe deinen Vorschlag gelesen und ausprobiert, vielen Dank dafür. Diese Lösung ist aber noch nicht ganz zielführend ich habe nochmal eine Exceldatei erstellt die wiedergibt wie ich mir das vorstellen würde falls möglich.HuhHuhHuh


Die Lösung aus dem Link hat leider auch nicht fuktioniert Huh
Wenn ihr mir helfen könntet wäre das super Angel19


Beste Grüße
Daniel


Angehängte Dateien
.xlsx   Temp.xlsx (Größe: 111,78 KB / Downloads: 3)
to top
#4
Hallo Daniel,

da wäre auch so was denkbar. Das zweite Ergebnis bringt Dir die Excel-Formel nicht, weil Du beim Eintrag in Spalte H ein Leerzeichen vergessen hast. Hinweis: Wenn Du in Spalte B die Kalenderwochen vorträgst, ohne dass Du schon in Spalte E Werte hast, stimmt die Rechnung nicht.

Arbeitsblatt mit dem Namen 'Tabelle1'
 HIJ
1KalenderwocheMittlere Wochentemperatur 
21. KW 20136,76,7
32.KW 20131,7#DIV/0!
43. KW 2013-4,1-4,1
54. KW 2013-4,1-4,1

ZelleFormel
I2=MITTELWERT(E2:E7)
J2=SUMMEWENN(B:B;H2;E:E)/ZÄHLENWENN(B:B;H2)
I3=MITTELWERT(E8:E14)
J3=SUMMEWENN(B:B;H3;E:E)/ZÄHLENWENN(B:B;H3)
I4=MITTELWERT(E15:E21)
J4=SUMMEWENN(B:B;H4;E:E)/ZÄHLENWENN(B:B;H4)
I5=MITTELWERT(E22:E28)
J5=SUMMEWENN(B:B;H5;E:E)/ZÄHLENWENN(B:B;H5)
Diese Tabelle wurde mit Tab2Html (v2.5.0) erstellt. ©Gerd alias Bamberg
   \\\|///      Hoffe, geholfen zu haben.
   ( ô ô )      Grüße, André aus G in T  
 ooO-(_)-Ooo    (Excel 97-2016)
to top
#5
Hallo André,
deine Lösung funktionert super für die Mittelwerte18
Ist es jetzt noch möglich das die Spalte H automatisch erkennt bis zu welcher Kw die Daten vorhanden sind? Ich möchte dahin, dass ich nur die Spalte Datum weiterziehen muss, die Daten vom DWD dann manuell einfüge und der Rest nur noch durch ziehen oder möglichst automatisch vervollständigt. Oder zumindest das der Computer nach der KW 52. automatisch auf 1. KW 2014 umspringt (durch ziehen)?
In der ersten Lösung gibt Excel mir die KW leider ledeglich als KW1 an ich brauche aber das Format, wie es vorhanden ist mit Jahresangabe. Da dies langfristig aufgezeichnet werden soll.

Vielen Dank und beste Grüße
Daniel
to top
#6
Hallo Daniel,

das könnte z.B. so gehen. Du brauchst dann die Zeilen einschl Spalte H nur weiter nach unten ziehen. Allerdings hat die Formel das Problem, dass sie recht viel Leistung beansprucht.

Arbeitsblatt mit dem Namen 'Tabelle1'
 HI
1Kalenderwoche und DatumMittlere Wochentemperatur
21. KW 20136,7
32. KW 20131,7
43. KW 2013-4,1
54. KW 2013-4,1

ZelleFormel
H1=B1
H2{=INDEX(B:B;VERGLEICH(1;(ZÄHLENWENN(H1:H$1;B$1:B$9999)=0)*(B$1:B$9999<>"");0))}
I2=MITTELWERT(E2:E7)
H3{=INDEX(B:B;VERGLEICH(1;(ZÄHLENWENN(H$1:H2;B$1:B$9999)=0)*(B$1:B$9999<>"");0))}
I3=MITTELWERT(E8:E14)
H4{=INDEX(B:B;VERGLEICH(1;(ZÄHLENWENN(H$1:H3;B$1:B$9999)=0)*(B$1:B$9999<>"");0))}
I4=MITTELWERT(E15:E21)
H5{=INDEX(B:B;VERGLEICH(1;(ZÄHLENWENN(H$1:H4;B$1:B$9999)=0)*(B$1:B$9999<>"");0))}
I5=MITTELWERT(E22:E28)
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Diese Tabelle wurde mit Tab2Html (v2.5.0) erstellt. ©Gerd alias Bamberg

Eine andere Variante wäre, wenn Du in Spalte H nur Zahlen einträgst und die Anzeige über die Zellformatierung einstellst:

Arbeitsblatt mit dem Namen 'Tabelle1'
 HI
1KalenderwocheMittlere Wochentemperatur
21. KW 20136,7
32. KW 20131,7
43. KW 2013-4,1
54. KW 2013-4,1

ZelleFormatWert
H2#". KW 2013"1
I20,06,7
H3#". KW 2013"2
I30,01,7
H4#". KW 2013"3
I40,0-4,12857142857143
H5#". KW 2013"4
I50,0-4,14285714285714

ZelleFormel
I2=SUMMEWENN(B:B;H2&". KW 2013";E:E)/ZÄHLENWENN(B:B;H2&". KW 2013")
I3=SUMMEWENN(B:B;H3&". KW 2013";E:E)/ZÄHLENWENN(B:B;H3&". KW 2013")
I4=SUMMEWENN(B:B;H4&". KW 2013";E:E)/ZÄHLENWENN(B:B;H4&". KW 2013")
I5=SUMMEWENN(B:B;H5&". KW 2013";E:E)/ZÄHLENWENN(B:B;H5&". KW 2013")
Diese Tabelle wurde mit Tab2Html (v2.5.0) erstellt. ©Gerd alias Bamberg
   \\\|///      Hoffe, geholfen zu haben.
   ( ô ô )      Grüße, André aus G in T  
 ooO-(_)-Ooo    (Excel 97-2016)
to top
#7
Hallo,

ich könnte mir auch eine Pivotlösung vorstellen:

Arbeitsblatt mit dem Namen 'Tabelle1'
 HIJK
9    
10Mittelwert von Temperatur   
11JahreDatumKWErgebnis
122013Jan1.KW 20136,70
13  2.KW 20131,70
14  3.KW 2013-4,13
15  4.KW 2013-4,14
16  5.KW 20135,90
17 Feb5.KW 20133,23
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Gruß
Peter
to top
#8
1000 Dank für eure Hilfe! Habs jetzt nach der Lösung von schauan gemacht18
to top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste