Wie kann ich in Excel anhand vom heutigen Datum die Werte vom Vorjahr (selber Wochentag) anzeigen lassen?
Hallo, ich habe folgendes Problem - ich habe zwei Pivottabellen, mit jeweils einer Datums- und einer Umsatzspalte. Nun möchte ich gerne einen Year over Year Vergleich machen, aber nicht 2.09.16 mit 2.09.15 vergleichen, sondern 02.09.16 mit 04.09.15 da dies die gleichen Wochentage (Freitag) waren - Freitag mit Mittwoch vergleichen macht bei mir hier keinen Sinn. Ich habe es immerhin schon geschafft, dass er mir in einer Zelle immer den Wert von heute anzeigt, aber muss es nun noch schaffen, dass er in einer anderen Zelle immer den Wert des Vorjahres mit eben passendem Datum zeigt. Es kommt dann noch das Problem hinzu, dass bei der Pivot beim Monatswechsel dann der gesamte Monatswert angezeigt wird - sodass ich die Formel nicht einfach nur "runterziehen" kann… Hier ein Auszug der Tabelle - leider lassen sich Datum und die Zeilen ansich nicht anders darstellen (Bei 2015 steht das Gesamtergebnis erst nach dem 31.12.) :(
3 Antworten
mit Pivot habe ich keine Erfahrung, aber
den gleichen Wochentag des Vorjahres erhältst Du mit
=HEUTE()-364 oder-schon vorbereitet fürs Folgende: =heute()-(363+1)
Das stimmt, sofern es kein Schaltjahr ist Dafür brauchen wir eine Korrektur:
=HEUTE()-(363+WENN(TAG(HEUTE()<>TAG(HEUTE()-364));1;0))
oder - auch nur ein Klammernpaar weniger-:
=HEUTE()-363-WENN(TAG(HEUTE()<>TAG(HEUTE()-364));1;0)
auch, wenns Dir logischer oder übersichtlicher erscheint:
=HEUTE()-364-WENN(TAG(HEUTE()=TAG(HEUTE()-363));1;0)
Anstatt heute() kannst Du natürlich auch jeden anderen Datumsbezug setzen.(3x)
Gegenüber dem -ebenfalls bedenkenswerten Vorschlag mit KW und Wochentag hat das hier den Vorteil, dass Du vom Gebietsschema unabhängig bist. (KW hat vier oder 5 genormte Möglichkeiten der Definition=>Hilfe)
Feiertage musst Du manuell korrigieren, da kenne ich keinerle Automatismus, der halbwegs zu bewältigen wäre. Fixe Feiertage, zB 1.Mai wandern ja in der Woche, Wochentagsfeste Feiertage (Ostermontag zB) finden an ganz anderen Zeitpunkten statt.. Sehr komplizierte Korrekturen und nur übers ganze Jahr möglich, aber da drücke ich mich jetzt...
Bei Verwendung von Tag muss es -365 heißen
=HEUTE()-364-WENN(TAG(HEUTE()=TAG(HEUTE()-365));1;0)
oder bei festem Bezug(A1)
=A1-364-WENN(TAG(A1=TAG(A1-365));1;0)
Leider gehen aus Deinem Screenshot keine Spaltenbezeichnungen hervor.
zum Auslesen ganz ohne Pivot(?) so: Du suchst den Vergleich zum Datum in Spalte heuer zB D5
Annahme:Das Vorjahr stehe mit Datum in B:B, die zugehörigen Umsatzwerte in C:C.
Dann in zB F5:
=INDEX(C:C;VERGLEICH(D5-364-WENN(TAG(D5=TAG(D5-365));1;0);B:B;0))
das kannst Du runterziehen. Beachte aber, dass B:B und C:C vollständige Spalten sind, die beim normalen Verschieben eines ausgeschnittenen Blocks nicht mitwandern!
Auslesen des Tages-Umsatzes:
=index(Datumsspalte;Vergleich(Datum;Umsatzspalte;0)
Die Zwischensumme September stört nur dann nicht, wenn September ein String ist und kein Datum
Formelbeispiel:Datum in Sp.B, Umsatz in Sp. C, für 26.8.2015:
=index(Tabelle1!C:C;Vergleich("26.8.16"*1;Tabelle1!B:B;0))
*1 macht aus einem Datumsstring in Anfz eine xl-verwertbare Datumszahl, die in B:B gefunden wird und dann wird daneben aus C:C ausgelesen
vllt solltest Du die Tage vor und nach Weihnachten besser ohne Bezug auf den Wochentag auswerten? (aber das ist ein inhaltliches und kein xl-Problem)
Puh, ich hab rumprobiert und mal rum versucht =D
Irgendwie ist was gute bei raus gekommen..... Mal schauen was DU davon hältst :P
Also, folgender Tabellen Aufbau:
Ich habe in A1 ein normales Feld in welches du dein Datum einträgst, welches du vergleichen möchtest.
Im Feld darunter also in A2, trägst du eine Zahl ein, die angibt wie viel Jahre rückwirkend du etwas vergleichen möchtest!
In der Zelle A3:A5 findet eine kleine Berechnung statt:
Zelle A3 Formel:
=WOCHENTAG(DATUM(JAHR(A1)-A2;MONAT(A1);TAG(A1)))
Zelle A4 Formel:
=WOCHENTAG(A1)
Zelle A5 Formel:
=A4-A3
In die Zelle B1 trägst du diese Formel ein:
=WENN(ISTSCHALTJAHR(DATUM(JAHR(A1)-A2;MONAT(A1);TAG(A1)));DATUM(JAHR(A1)-A2;MONAT(A1);TAG(A1)+A5);DATUM(JAHR(A1)-A2;MONAT(A1);TAG(A1)+A5))
und das richtige Datum + die Anzahl Jahre die du eingetragen hast weniger + der richtige Wochentag aus diesem Monat erscheinen.
Versuchs doch mal und melde dich dann nochmal =)
Grüße,
iMPerFekTioN
Edit: Du kannst dann halt mit der Zahleneingabe in A2 bestimmten wie viele Jahre du zurück willst!
Dann wird das richtige Datum mit dem richtigen Wochentag angezeigt wie du es ja wolltest.
Die Auslesung des Umsatzes hab ich aber noch nicht gemacht! :)
Vielen Dank zunächst für die schnelle Hilfe... es hat bis zur letzten "großen Formel" geklappt... hier kam leider ein Fehler und ich krieg es natürlich nicht hin, den zu lösen -.-
Ich habe die Formeln in anderen Zellen erstellt:
O 35 --> 02. Sep 2016
O36 --> 1 (denn ich möchte ja letztes Jahr vergleichen
O37 --> =WOCHENTAG(DATUM(JAHR(O35)-O36;MONAT(O35);TAG(O35)))
O38 --> =WOCHENTAG(O35)
O39 --> =O38-O37
=WENN(ISTSCHALTJAHR(DATUM(JAHR(O35)-O36;MONAT(O35);TAG(O35)));DATUM(JAHR(O35)-O36;MONAT(O35);TAG(O35)+O39);DATUM(JAHR(O35)-O36;MONAT(O35);TAG(O35)+O39))
Habt ihr Rat? Tausend Dank!!!
Hmmm,
bei mir klappt das alles, wenn ich deine Formeln hier aus der Antwort übernehme und in den Zellen eintrage wie du sie beschreibst.
Was bekommst du denn für einen Fehler?
auch ein schöner Vorschlag, DH!
kannte Istschaltjahr(A1) noch nicht, dürfte aber Probleme bei Datümern bis zum 28. Feb. machen?
Meinst du, Problem wenn das Anfangsdatum der 28. ist oder wenn das zurückrechnende Datum der 28. ist =D?
Aber danke, ein Lob von den hohen Tieren hier ehrt mich immer sehr :D
ich meine vom 1.1.-28.2.16, denn auch das ist Schaltjahr, muss aber nicht korrigiert werden.
(danke fürs "hohe Tier", bin abrer nur -wenn auch langjähriger- Autodidakt, der sein Wissen hier ganz wesentlich aufstocken konnte. Wie Du am "mit Pivot habe ich keine Erfahrung" ablesen kannst)
Arbeite hier nicht mit dem Datum sonder mit der Kalenderwoche und dem Wochentag, das ist eindeutig.
DH! möglicherweise der eher nachvollziehbare Vorschlag im Vergleich zur langen Formel in meiner AW hat allerdings auch Tücken (Def. Kalenderwoche ist vom Gebietsschema abhängig, ausserdem springt die um, was v.a. die Zeit nach Neujahr bzw Weihnachten schwierig auswertbar macht)
Das die KW um Neujahr umspringt ist defenitiv ein Problem, allerdings ist es für den Vergleich KW Wochentag letzten Jahr mit der aktuellen KW Wochentag unerheblich. Für die Umsatzbetrachtung sind aber auch andere Dinge interessant, je nachdem in welchem Geschäftsbereich die Umsätze generiert werden (Ferien etc.).
aus der xl-Hilfe:
Für diese Funktion werden zwei Systeme verwendet:
System 1 Die Woche, die den 1. Januar umfasst, ist die erste Kalenderwoche mit der Nummer 1.
System 2 Die Woche, die den ersten Donnerstag des Jahrs umfasst, ist die erste Kalenderwoche mit der Nummer 1. Dieses System entspricht der in ISO 8601 spezifizierten Methode, welche im Allgemeinen als europäisches Wochennummerierungssystem bezeichnet wird.
Nach ISO kann das Weihnachtsgeschäft mal in Woche 52 liegen, mal in Woche 51.
Aber ganz komme auch ich mit meiner Formel nicht um derartige Fehlzuordnungen rum.
Ich vermeide das auch eher deshalb, weil ich mir sowas ohnehin nicht merken kann, und dann weiß ich lieber ohne Hilfe, was für Fehlerquellen auftreten.
Ich wollte aber deine AW in keiner Weise abwerten sondern nur eine Alternative anbieten!
Bin nochmal auf ein Problem am 29.2.16 gestoßen, muss ich mir heute abend nochmal zu Gemüte führen!