Excel: Summieren solange in der Spalte die gleiche Zahl steht wie in der davor
Hallo Community,
wieder mal eine Frage von mir zu Excel. Das ist die Ausgangssituation: Jahr Monat Tag Stunde Wert 2013 01 01 01 15 2013 01 01 02 15 2013 01 01 03 15 2013 01 02 ... 17 2013 01 03 ... 11 2013 02 01 ... 8 usw
2014 01 01 ... 3 (die ... sind nur plaätzhalter das es so weitergeht)
Was ich jetzt möchte ist, der Durchschnittswert der Werte für jeden Tag, jeden Monat und jedes Jahr.
Das heißt sowas in die Richtung wie: Summiere solange auf, wie bei Jahr 2013 Monat 01 Tag auch bei 01 ist und dann teile durch die Anzahl.
Hat jemand eine gute Idee?
Ich hoffe auf Hilfe und viele liebe Grüße,
~fastregister
3 Antworten
Mache dir eine Extraspalte Datum, Summe und Durchschnitt.
A B C D E F G H
1 Jahr Monat Tag Stunde Wert Datum Summe Durchschnitt
2 2013 1 1 1 15 =DATUM(A2;B2;C2) =WENN(F2<>F1;E2;G1+E2) =WENN(F3<>F2;G2/ZÄHLENWENN($F$2:F25;F2);"")
3 2013 1 1 2 15 =DATUM(A3;B3;C3) =WENN(F3<>F2;E3;G2+E3) =WENN(F4<>F3;G3/ZÄHLENWENN($F$2:F26;F3);"")
4 2013 1 1 3 15 =DATUM(A4;B4;C4) =WENN(F4<>F3;E4;G3+E4) =WENN(F5<>F4;G4/ZÄHLENWENN($F$2:F27;F4);"")
5 2013 1 2 1 11 =DATUM(A5;B5;C5) =WENN(F5<>F4;E5;G4+E5) =WENN(F6<>F5;G5/ZÄHLENWENN($F$2:F28;F5);"")
6 2013 2 1 1 8 =DATUM(A6;B6;C6) =WENN(F6<>F5;E6;G5+E6) =WENN(F7<>F6;G6/ZÄHLENWENN($F$2:F29;F6);"")
Die Formeln kannst du herunterziehen.
Erstmal vielen Dank für deine Antwort! :)
Geht es auch, dass ich für die Summe, bzw den Durschschnitt nur ein einziges Feld bekomme, also nur die Endsumme dieses Tages und den EndDurchschnitt?
Weißt du was ich meine? :)
Mit der Datumsvergleichsfunktion (=WENN(F6<>F5;...) kannst du die Sichtbarkeit steuern.
Poste mal die Formel, die den Fehler erzeugt. Dann kann ich dir vielleicht weiterhelfen.
Hast du mein Beispiel 1:1 kopiert? Nutze es als Musterlösung und bilde sie auf dein Problem ab.
Ich habe noch etwas im Angebot für dich:
Jahr Monat Tag Stunde Wert Datum Durchschnitt
2013 1 1 1 15 =DATUM(A2;B2;C2) =WENN(F3<>F2;SUMMEWENN($F$2:$F$1000;F2;$E$2:$E$1000)/ZÄHLENWENN($F$2:$F$1000;F2);"")
2013 1 1 2 15 =DATUM(A3;B3;C3) =WENN(F4<>F3;SUMMEWENN($F$2:$F$1000;F3;$E$2:$E$1000)/ZÄHLENWENN($F$2:$F$1000;F3);"")
2013 1 1 3 15 =DATUM(A4;B4;C4) =WENN(F5<>F4;SUMMEWENN($F$2:$F$1000;F4;$E$2:$E$1000)/ZÄHLENWENN($F$2:$F$1000;F4);"")
2013 1 2 1 11 =DATUM(A5;B5;C5) =WENN(F6<>F5;SUMMEWENN($F$2:$F$1000;F5;$E$2:$E$1000)/ZÄHLENWENN($F$2:$F$1000;F5);"")
2013 2 1 1 8 =DATUM(A6;B6;C6) =WENN(F7<>F6;SUMMEWENN($F$2:$F$1000;F6;$E$2:$E$1000)/ZÄHLENWENN($F$2:$F$1000;F6);"")Nochmal vielen Dank!
ID_JAHR ID_MONAT ID_TAG ID_STUNDE LUFTTEMPERATUR Datum Summe
2013 06 28 00 10.5 28.06.2013 10.5 (=WENN(F2<>F1;E2;G1+E2))
2013 06 28 01 10.1 28.06.2013 #WERT! (=WENN(F3<>F2;E3;G2+E3))
2013 06 28 02 10.0 28.06.2013 #WERT! (=WENN(F4<>F3;E4;G3+E4))Also so sieht es jetzt aus in Klammern die Formel.
Wenn ich die zweite Formel benutze, dann zeigt er auch wirklich erst am Ende eines Tages etwas an, aber leider ist es dann 0 :(
Nochmal DANKE!
Ich habe allerdings meinen möglichen Fehler gefunden, warum eure Formeln nicht funktionieren! Und zwar sind meine Werte so angegeben: 10.4 obwohl 10,4 gemeint ist und ich glaube Excel hat es deswegen nicht als gültiges Zahlenformat gesehen. Ich habe jetzt in den Einstellungen Dezimal und Tausendertrennzeichen (. und ,) getauscht. Aber gibt es auch ein Möglichkeit, dass Excel es dann vielleicht gleich als 10,4 schreibt? Ich lade die Daten aus einer .txt in Excel.
Wie sind die Daten denn gespeichert? Im CSV-Format? Bei mir schreibt Excel dann zum Beispiel anstelle von "1.5" "1.Mai" dahin.
Wenn ich es als txt speichere und dann in Excel öffne, kommt der Textkonvertierungs-Assistent.
Da sage ich
Getrennt > Weiter > (x) Semikolon > Weiter
Dann Klick auf den Spaltenkopf mit Spalte mit Dezimalzahlen mit . als Trenner
(x) Standard > Weitere... > Dezimaltrennzeichen . > Tausender , > OK > Fertigstellen
Ich gehe davon aus, dass in Deinem String die Leerzeichen Trennzeichen sind, d.h. dass Du die Daten in einer Tabelle wie folgt hast:
Spalte A: Jahr
Spalte B: Monat
Spalte C: Tag
Spalte D: Stunde
Spalte E: Wert
Deine Datensätze stehen z.B. in den Zeilen 3 bis 10.
Ich gebe am besten mal ein Beispiel an:
Du erhältst den Durchschnitt aller Werte für die Kombination
Jahr (Spalte A) = 2013
Monat (Spalte B) = 1
Tag (Spalte C) = 26
Stunde (Spalte D) = 12
mit der Matrixformel
=SUM(($A$3:$A$10=2013)($B$3:$B$10=1)($C$3:$C$10=26)($D$3:$D$10=12)($E$3:$E$10)) / SUM(($A$3:$A$10=2013)($B$3:$B$10=1)($C$3:$C$10=26)*($D$3:$D$10=12))
(setze zwischen die Klammerausdrücke immer ein "mal"-Sternchen - das hat dieser GF-Editor verschluckt - außer zwischen den letzten Klammerausdrücken))
Matrixformel heißt, Du musst sie einhacken und nicht wie üblich mit Enter, sondern mit STRG+Umschalt+Enter quittieren, und sie wird dann in der Formelzeile mit geschweiften Klammern angezeigt.
Wenn Du jetzt z.B. mit dem Durchschnittwert nicht bis auf Stundenebene runtergehen willst, sondern nur den Durchschnitt aller Werte für
Jahr (Spalte A) = 2013
Monat (Spalte B) = 1
Tag (Spalte C) = 26
suchst, dann lautet die Formel natürlich entsprechend:
=SUM(($A$3:$A$10=2013)($B$3:$B$10=1)($C$3:$C$10=26)($E$3:$E$10)) / SUM(($A$3:$A$10=2013)($B$3:$B$10=1)*($C$3:$C$10=26))
(wichtig: wieder als Matrixformel, d.h. mit Strg+Umschalt+Enter abschließen)
d.h. die Bedingung "Stunde = 12" wurde entfernt, d.h. es werde der Durchschnitt über alle Stundenwerte für den 26. Januar 2013 genommen.
Natürlich kannst Du die Bedingungswerte (also z.B. Stunde = 12) auch durch Zellbezüge ersetzen. Beim Runterziehen von Matrixfunktionen bleibt die Matrixeigenschaft zum Glück erhalten, d.h. Du brauchst nicht in jede einzelne Zelle zu gehen und die Formel nochmal mit Strg+Umschalt+Enter zu aktivieren.)
Ich hoffe, ich war verständlich.
Hallo tevau, ja du warst sehr verständlich! Und vielen Dank für deine Antwort!
Ich hab das ganze mal ausprobiert und leider kommt bei mir dann nur der Fehler #NAME?
Auch ist das Problem, dass Jahr, Monat, Datum wechseln und ich das ja nicht selbt raussuchen möchte (bei über 5000 Werten), sondern Excel soll automatisch, wenn der Tag wechselt, bzw das Jahr usw die Summe beenden und die Summe für den nächsten Block anfangen. Ist das möglich?
Am besten wäre es sogar, wenn die Werte in einem Sheet sind und die Formeln in dem anderen, sodass ich die Werte jederzeit ersetzen kann und Excel mir dann für jede beliebigen Werte die Durchschnittswerte angeben soll.
Nochmal vielen lieben Dank für die ausfühliche Antwort!
Ps.: Eigentlich habe ich das Datumsormat so vorliegen JJJJMMTThh, aber ich habe es in mehrere Spalten aufgeteilt (so wie du oben beschrieben hast), weil ich dachte es wäre dann leichter...
Hallo fastregister,
ich antworte auf Deinen Kommentar zu meiner ersten Antwort hier direkt auf die Frage, damit Du eine email bekommst.
Also:
Das mit der Fehlermeldung #NAME kann daran liegen dass Du vielleicht ein deutsches Excel hast, während ich ein englisches benutze. Du musst dann in der Formel die beiden SUM durch SUMME ersetzen.
Deine Trennung des Datumsformates finde ich sehr hilfreich - darauf basiert meine Methode.
Ich verstehe jetzt genauer, was Du willst, und habe meine Formel nochmal abgeändert.
Also wenn Du die Datensätze von oben nach unten chronologisch untereinander hast, könntest Du neben die o.g. Datums- und Wertspalten A bis E noch weitere Spalten anlegen, die Du wie folgst betitelst:
Spalte F: "Durchschnittwert des aktuellen Jahres seit Jahresbeginn"
In Zelle F3 dann die Formel
=SUMME(($A$3:$A3=A3)*($E$3:$E3))/ZÄHLENWENN($A$3:$A3;A3)
wieder als Matrixformel eingeben, und die dann runterziehen. Achte auf die Dollarzeichen!
Spalte G: Hilfsspalte zur Vorbereitung für den Monatsdurchschnitt.
In G3 eingeben: =A3*100+B3 und runterziehen.
Spalte H: "Durchschnittwert des aktuellen Monats seit Monatsbeginn"
In Zelle H3 dann die Formel
=SUMME(($G$3:$G3=G3)*($E$3:$E3))/ZÄHLENWENN($G$3:$G3;G3)
wieder als Matrixformel eingeben, und die dann runterziehen.
Spalte I: Hilfsspalte zur Vorbereitung für den Tagesdurchschnitt.
In I3 eingeben: =G3*100+C3 und runterziehen.
Spalte J: Durchschnittwert des aktuellen Tages seit Tagesbeginn
In Zelle J3 dann die Formel
=SUMME(($I$3:$I3=I3)*($E$3:$E3))/ZÄHLENWENN($I$3:$I3;I3)
wieder als Matrixformel eingeben, und die dann runterziehen
Wichtig: Diese Formeln würden auch als normale Formeln Ergebnisse anzeigen, aber unsinnige. Deshalb unbedingt drauf achten, sie als Matrixformel einzugeben (STRG+Umschalt+ENTER statt nur ENTER).
Kannst ja mal nachstellen und sehen, ob Dir diese oder die von Suboptimierer persönlich besser gefällt. Es gibt bei Excel ja immer viele Wege zum Ziel.
Viel Erfolg!
Nochmal vielen Dank! Ich werde es sofort morgen ausprobieren. Habe heute erstmal die Nase voll von Excel ;)
Ich habe allerdings meinen möglichen Fehler gefunden, warum eure Formeln nicht funktionieren! Und zwar sind meine Werte so angegeben: 10.4 obwohl 10,4 gemeint ist und ich glaube Excel hat es deswegen nicht als gültiges Zahlenformat gesehen. Ich habe jetzt in den Einstellungen Dezimal und Tausendertrennzeichen (. und ,) getauscht. Aber gibt es auch ein Möglichkeit, dass Excel es dann vielleicht gleich als 10,4 schreibt? Ich lade die Daten aus einer .txt in Excel.
Ich habs jetzt mal ausprobiert, aber es sieht so aus:
Warum zeigt er dort #Wert! an? Habe die Formel runtergezogen