Frage von allocigar78, 27

Wie kann ich in Excel nur Werte von einem bestimmten Jahr ausgeben lassen?

ich möchte eine Kilometertabelle anfertigen und dann am Ende eines Jahres sehen wie viele km ich in welchem Jahr gefahren bin, und natürlich auch schon während des Jahres sehen wie viele ich im aktuellen Jahr habe.

Die Tabelle sieht also so aus, dass z. B. in Spalte B das Datum steht und in C die km Anzahl.

Ich brauche jetzt eine Formel die in einem anderen Feld mir nur die Werte zusammenzählt die mit XX.XX.2014 versehen sind, dann eben in einem anderen Feld nur die mit XX.XX.2015 usw.

wie kann ich das machen?

Zudem möchte ich noch wissen wie viele Fahrten ich in diesem Jahr schon habe. Das heißt in Spalte D ist also eine fortlaufende Nummerierung.

Ich möchte nun wissen wie viele Fahrten hatte ich 2014, und wie viele schon in 2015 usw.

Hilfreichste Antwort - ausgezeichnet vom Fragesteller
von DrErika, 19

Das ist eine typische Aufgabe, die mit einer Pivot-Tabelle erledigt wird. Damit kannst du die Tabelle lassen wie sie ist und eine abhängige Pivot-Analyse draufsetzen. Die brauchst du dann nur noch aktualisieren. Die Anzahl und die Kilometer pro Jahr kämen sofort und ohne Anstrengung dabei heraus.

Als einzige Erweiterung würde ich eine weitere Spalte mit =Jahr(<datum>) einfügen, dann hast du es mit dem Pivot leichter.

Kommentar von allocigar78 ,

Hi DrErika,

vielen Dank für Deine Info. Nur leider weiß ich überhaupt nicht was eine Pivot-Tabelle ist und hab somit auch noch nie etwas damit gemacht.

Das müsste doch auch über ne wenn/dann Formel zu regeln sein, oder? - schau Dir mal die Antwort von HWH001 und meinen Kommentar darunter an - vielleicht gibt es eine Lösung auf "herkömmlichem" Weg? - sollte doch für ein Programm wie Excel keine unlösbare Aufgabe stellen.... ist doch eigentlich ein ganz normaler "Standardwunsch"

Kommentar von DrErika ,

Es ist ja nicht schlimm, dass du die Pivot-Tabellen nicht kennst. Es lohnt sich aber, diese anzuschauen, weil es die ganze Analyse vereinfacht. Hier ist eine Kurzfassung: https://www.computerfrage.net/video/pivot-tabelle-erstellen-kurzfassung

Kommentar von DrErika ,

Eine ähnliche Anwendung - auch mit Pivot gelöst - findet sich hier: https://www.computerfrage.net/video/excel-pivottabelle-zum-zaehlen-von-urlaubsta...

Kommentar von DrErika ,

Und: Ja, das geht auch mit Zählenwenn, Summewenn usw. - da hab ich nur grad keinen Spaß dran, weil es mit Pivot soviel einfacher geht.

Kommentar von DrErika ,

Fügst du in Spalte 1 ein "=JAHR(B2)", kannst du in G1 2015, H1 2016 und I1 2017 eintragen. Dann kannst du mit den Formeln in G2: =SUMMEWENN($A:$A;G$1;$C:$C), H2: =SUMMEWENN($A:$A;H$1;$C:$C) und I2: =SUMMEWENN($A:$A;I$1;$C:$C) die jeweiligen Kilometersummen je Jahr errechnen lassen.

Darunter in G3, H3 und I3 könntest du mit =ZÄHLENWENN($A:$A;G$1), =ZÄHLENWENN($A:$A;H$1) und =ZÄHLENWENN($A:$A;I$1) die jeweilige Fahrtanzahl ermitteln.

Einfacher ist eine Pivot.

Kommentar von allocigar78 ,

yep! vielen Dank! nach Deinem Hinweis heute mittag auf zählenwenn und summwenn hab ich's schon gebastelt :-) passt alles super! nur das mit dem Jahr wollte nicht in eine Formel - musste ich eben, wie Du ja geschrieben hast in einer separaten Spalte anlegen - also erst mal in einer Spalte das Jahr in ne Zahl umwandeln. Dachte nach dem Hinweis von HWH001 dass das auch mit in der Zählenwenn Formel ginge.

(also =SUMMEWENN($E$4:$E$3000;JAHR($E$3:$E$3000)=2014);$G$4:$G$3000)

oder aber =SUMMEWENN($E$4:$E$3000;JAHR=2014;$G$4:$G$3000)

aber haut so leider nicht hin. Aber mit der Krücke über die versteckte Spalte geht es. Frage mich allerdings WARUM es nicht in einer Formel geht....

Piviot schaue ich mir bei Gelegenheit mal an - aber einfacher als diese 2 Formeln zu tippen kann es doch eigentlich gar nicht sein, oder? - wenn ich da erst ne Tabelle anlegen muss?

Danke Dir :-)

Antwort
von HWHOO1, 17

Hallo allocigar78,

Einfacher Vorschlag/Anregung:

genau wie im Anhang/Bild die Zeilen und Spalten anlegen, dann eventuell entsprechend modifizieren/anpassen:

Spalte B formatieren als DATUM,

Spalte C, D und E formatieren als ZAHL mit 0 Kommastellen,

Formel für D3: =WENN(C3<>"";D2+1;0) beliebig weit runterziehen,

Formel für E3: =WENN(UND(JAHR(B3)=2014;C3<>"");C3;"") beliebig weit runterziehen,

Formel für C12: =WENN(UND(JAHR(B3)=2014;C3<>"");C3;"")

Formel für D12: =SUMME(E3:E11)

für eventuelle Rückfragen nochmals posten

Viel Spass

HWHOO1

Kommentar von allocigar78 ,

Hallo HWH001,

vielen Dank für Deine Antwort. Deine Lösung ist am nächsten dran von dem was ich möchte, aber ganz so haut das noch nicht hin.

Bei Deiner Lösung müsste ich ja für jedes Jahr eine eigene versteckte Spalte anlegen.

Und die Summenformel nutzt mir natürlich nicht viel da ich ja nicht unter einer Spalte zusammenzählen will sondern die Spalte läuft ja weiter mit Einträgen auf dem nächsten Jahr usw.

Dein Bild mit Deiner Tabelle ist sehr gut um Dir zu erklären was ich möchte:

stell Dir vor in Feld B12 geht es jetzt einfach mit Einträgen aus dem Jahr 2015 weiter. Diese dürfen also für die Berechnung der Gesamt-Km 2014 nicht berücksichtigt werden.

In Feld A2 möchte ich jetzt die Gesamtkilometer für 2014 ausgegeben haben, in A3 die Anzahl der Fahrten, in A5 dann z. B. die Gesamt km für 2015 und in A6 die Anzahl der Fahrten für 2015 usw.

Antwort
von AnnaRisma, 7

Und als Ergänzung: Die meisten derartigen Probleme lassen sich leichter angehen, wenn man von vornherein mit einem technisch logischen Datumsformat arbeitet. Also so, wie man in der Mathematik immer vorgeht, vom Großen zum Kleinen. Es muss nicht unbedingt das internationale Format sein, also "2015-07-21", auch 20150721 funktioniert ganz gut. Da aber Tabellenkalkulationsprogramme auf das internationale Datumsformat ausgelegt sind, ist dessen Verwendung natürlich ratsam.

Antwort
von LittleArrow, 6

Ich möchte Dir einen einfacheren Weg vorschlagen ohne Wenn-Abfragen. Voraussetzung ist die zeilenweise Trennung der Fahrten in jedem Jahr, also z. B. erst 2014, dann darunter 2015.

Du fügst über 2014 und 2015 je zwei Leerzeilen ein (Annahme: die erste Zeile enthält bereits die erste Fahrt).

1. km-Summe:

In die zweite Leerzeile trägst Du in Spalte C in die Zelle C2, also über(!) den jeweiligen km-Zahlen, einfach eine Summenformel ein =SUMME(C3:C350), wobei C350 die letzte Zeile der Fahrten in 2014 wäre. Die erste Leerzeile dient nur der Abgrenzung von dem Davorstehenden.

Für das Jahr 2015 schreibst Du eine entsprechend modifizierte Formel in Zelle C352)  =Summe(C353:C753), wobei C753 also nur ein vorläufiges Ende der Dateneinträge wäre.

2. Fahrtenanzahl:

In die Zelle B2 links neben der ersten Summe, also über den Fahrtdaten, schreibst Du =ANZAHL2(B3:B350), dann erhältst Du die Anzahl der Einträge. Zur Kontrolle könntest Du auch die Tage mit km-Eingaben zählen mit der Formel =ANZAHL(C3:C350) in die Zelle D2 rechts neben der Summenformel. ANZAHL zählt die Werte in dem Bereich und ANZAHL2 die Zellen mit irgendeiner Eingabe. 

3. Problemzone:

Natürlich mußt Du die Bereichsgrenzen entsprechend gelegentlich aktualisieren. Aber wenn Du dabei ein bisschen aufpasst, ist dies keine große Mühe!

Kommentar von allocigar78 ,

Hallo,

vielen Dank, aber bei mir ist nichts nach Jahren getrennt, das geht fortlaufend durch - irgendwann beginnt halt immer das neue Jahr und das muss automatisch berücksichtigt werden.


ich habe aber nach dem Hinweis von DrErika mit der zählenwenn und summwenn Formel schon exakt das gefunden was ich gesucht habe - ist perfekt, perfekter geht es nicht, genau das was ich gesucht habe :-) - ich arbeite gerne mit wenn/dann Formeln, damit kann man sehr viel machen!


wie in meinem letzten Kommi bei DrErika geschrieben ist das Einzige (auch wenn ich es jetzt mit ner versteckten Spalte gelöst habe) was mich diesbezüglich noch interessieren würde, warum das mit dem Jahr nicht IN! der eigentlichen Formel zu realisieren ist (kannst Dir die Formeln oben ja mal ansehen die ich geschrieben habe)


Kommentar von LittleArrow ,

Danke für Deine Rückäußerungen!

bei mir ist nichts nach Jahren getrennt, das geht fortlaufend durch

Schwer vorstellbar, aber wenn man nicht an die erfaßten Daten rangehen will, um vor jedem Jahr zwei Leerzeilen einzufügen, dann muss es komplizierter werden, so wie von DrErika beschrieben.

Entscheidend ist Deine Zufriedenheit mit einer gefundenen Lösung:-)

Kommentar von allocigar78 ,

ich finde es nicht unbedingt komplizierter - eher einfach da es jeweils mit einer einzigen Formel gelöst wurde.

außerdem geht es ja auch nicht nur darum dass ich nicht mehr nachträglich an die Daten möchte und jeweils zwei Leerzeilen einfügen, sondern vor allem darum, dass ich diese auch zukünftig nicht tun möchte, nach jedem Jahreswechsel manuell eingreifen zu müssen, sondern der Sinn besteht eben ja genau darin, dass die Felder den Jahreswechsel eben automatisch mitbekommen

Trotzdem auch Dir noch mal danke für die Ideenbeisteuerung

Kommentar von LittleArrow ,

Gern geschehen. Danke für Deine Kommentare.

Antwort
von HWHOO1, 6

Genauere, bzw. verständlichere Tabelle: 

;)

Keine passende Antwort gefunden?

Fragen Sie die Community