Liebe Excel Gemeinde,
ich sitze gerade an meinem ersten größeren Excel-Projekt und stoße dabei an meine und an googles Grenzen:20:. Ich hoffe ihr könnt Abhilfe schaffen.
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 schreiben:19:
Ich bin für sämtliche Ideen, Kritik, Anregung offen und dankbar:15:
Vielen Dank vorab
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 | A | B |
| 5 | 04.01.2013 | |
| 6 | 05.01.2013 | |
| 7 | 06.01.2013 | 1. KW |
| 8 | 07.01.2013 | |
| 13 | 12.01.2013 | |
| 14 | 13.01.2013 | 2. KW |
| 21 | 20.01.2013 | 3. KW |
| Formeln der Tabelle |
| Zelle | Formel | | 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.
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.
Die Lösung aus dem Link hat leider auch nicht fuktioniert
Wenn ihr mir helfen könntet wäre das super
:19:
Beste Grüße
Daniel
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' |
| | H | I | J |
| 1 | Kalenderwoche | Mittlere Wochentemperatur | |
| 2 | 1. KW 2013 | 6,7 | 6,7 |
| 3 | 2.KW 2013 | 1,7 | #DIV/0! |
| 4 | 3. KW 2013 | -4,1 | -4,1 |
| 5 | 4. KW 2013 | -4,1 | -4,1 |
| Zelle | Formel |
| 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 |
Hallo André,
deine Lösung funktionert super für die Mittelwerte:18:
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
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' |
| | H | I |
| 1 | Kalenderwoche und Datum | Mittlere Wochentemperatur |
| 2 | 1. KW 2013 | 6,7 |
| 3 | 2. KW 2013 | 1,7 |
| 4 | 3. KW 2013 | -4,1 |
| 5 | 4. KW 2013 | -4,1 |
| Zelle | Formel |
| 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' |
| | H | I |
| 1 | Kalenderwoche | Mittlere Wochentemperatur |
| 2 | 1. KW 2013 | 6,7 |
| 3 | 2. KW 2013 | 1,7 |
| 4 | 3. KW 2013 | -4,1 |
| 5 | 4. KW 2013 | -4,1 |
| Zelle | Format | Wert |
| H2 | #". KW 2013" | 1 |
| I2 | 0,0 | 6,7 |
| H3 | #". KW 2013" | 2 |
| I3 | 0,0 | 1,7 |
| H4 | #". KW 2013" | 3 |
| I4 | 0,0 | -4,12857142857143 |
| H5 | #". KW 2013" | 4 |
| I5 | 0,0 | -4,14285714285714 |
| Zelle | Formel |
| 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 |
Hallo,
ich könnte mir auch eine Pivotlösung vorstellen:
| Arbeitsblatt mit dem Namen 'Tabelle1' |
| | H | I | J | K |
| 9 | | | | |
| 10 | Mittelwert von Temperatur | | | |
| 11 | Jahre | Datum | KW | Ergebnis |
| 12 | 2013 | Jan | 1.KW 2013 | 6,70 |
| 13 | | | 2.KW 2013 | 1,70 |
| 14 | | | 3.KW 2013 | -4,13 |
| 15 | | | 4.KW 2013 | -4,14 |
| 16 | | | 5.KW 2013 | 5,90 |
| 17 | | Feb | 5.KW 2013 | 3,23 |
| Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg |
1000 Dank für eure Hilfe! Habs jetzt nach der Lösung von schauan gemacht:18: